Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 459
  • Last Modified:

C# - exporting sql query to Excel with Outline View

Hi.

I have a query that returns a result that I would like to return to excel, but I would like the sheet to be displayed in the outline view, like the attachment I have added.

Any ideas on how to do that?
Sample-Outline.xlsx
0
Jasmin01
Asked:
Jasmin01
  • 5
  • 5
1 Solution
 
tlaytonCommented:
Here is a good article on adding formatting with borders etc in Excel:

Excel Borders
0
 
NorieCommented:
What code do you have so far to get the results of the query into Excel?

The outline part will be easy once you have that.
0
 
Jasmin01Author Commented:
I have the following:

public bool CreateExcel(string FilePath)
    {
        try
        {
            bool result = false;
            string databaseName;
            int rowCount = 0;
            DataSet databases;
            DataSet dataExtract;

            GemBox.Spreadsheet.ExcelFile dataFile = new ExcelFile();
            GemBox.Spreadsheet.ExcelWorksheet workSheet = dataFile.Worksheets.Add("Data Extract");

                    if ((ConnectCampaignDataBase(databaseName, UserName, Password) == true))
                    {
                        try
                        {
                            dataExtract = DSL.ReturnSQLDataset("usp_ReturnMISData", 1);
                        }
                        catch (Exception ex)
                        {
                            throw ex;
                        }

                        // Write the col headers
                        if (rowCount == 0)
                        {
                            foreach (DataColumn colHeading in dataExtract.Tables[0].Columns)
                            {
                                workSheet.Cells[rowCount, colHeading.Ordinal].Value = colHeading.ColumnName.ToString();
                                workSheet.Cells[rowCount, colHeading.Ordinal].Style.Font.Weight = ExcelFont.BoldWeight;
                            }
                            rowCount++;
                        }

                        foreach (DataRow extractRow in dataExtract.Tables[0].Rows)
                        {
                            for (int colIndex = 0; colIndex < dataExtract.Tables[0].Columns.Count; colIndex++)
                            {
                                workSheet.Cells[rowCount, colIndex].Value = extractRow[colIndex];

                            }
                            rowCount++;
                        }
                    }
                    else
                    {
                        throw new Exception("Can not connect to campaign database : " + databaseName);
                    }

                dataFile.SaveXlsx(FilePath);

            result = true;
            return result;
    }
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
NorieCommented:
What's GemBox?

By the way, is this connected to your question for a stored procedure and Excel?

If it is does that mean that question is now closed?
0
 
Jasmin01Author Commented:
It was connected to my previous question.  We just decided to do things differently, by exporting to Excel, then using Outlining to group my records together.  Can you help?
0
 
NorieCommented:
I probably could if you were using Excel itself but you seem to be using it via GemBox, which I'm not familiar with.

I did find out that it can handle outlining, which I wasn't sure about, and here's a link to some sample code.

http://www.gemboxsoftware.com/spreadsheet/features/samples/featuresamplescs

You'll find the outline part right at the bottom in ReferencingAndGroupsSample.

PS Did you ever try the code I posted in the other thread?
0
 
Jasmin01Author Commented:
Thanks.

I figured that if I add the following lines:

dataFile.Worksheets.ActiveWorksheet.Rows[2].OutlineLevel = 1;
dataFile.Worksheets.ActiveWorksheet.ViewOptions.OutlineRowButtonsBelow = false;
dataFile.SaveXlsx(FilePath);

It will group the first and second row.  I need this to be done on all the data though, I need to check if the Acc_No in the first row = Acc_no in the second row.  If it is equal, then I need to group those rows.  Obviously, there are cases where there are 4 account numbers that are the same.  In this case, I need to group all 4.  Any help on doing that?
0
 
NorieCommented:
I could come up with something that used Excel, perhaps you could adapt it for the syntax that GemBox uses.
0
 
Jasmin01Author Commented:
Please can you send me your example.  I'll try and adapt it to Gembox.
0
 
Jasmin01Author Commented:
Thanks.  I got it working with the link you gave me.
0
 
NorieCommented:
Glad you got it working, and apologies for not getting back to you.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now