Export SQL Server Data to Multiple Excel Worksheets with C#


By:   |   Updated: 2020-11-18   |   Comments   |   Related: More > Microsoft Excel Integration


Problem

As I presented in the article Export SQL Server Table to Excel with C#, there are many situations when you want to export SQL Server tables to an Excel file. If you want to export data from several tables, it is more convenient to export to the same Excel file in different worksheets, instead of using multiple Excel files, especially if the tables being exported are related. You can also export data generated by different queries, not only data from physical tables.

This article presents how you can export data from two SQL Server queries to an Excel file into specific named sheets.

Solution

Data loading into a SQL Server table and extracting data from a SQL Server table (ETL) is very important. Microsoft Excel is maybe one of the most popular programs on Windows for these tasks, due to the fact that a spreadsheet is similar to a SQL Server table and is very easy to input data in a table format, visualize data in a spreadsheet, as well as processing it for further needs.

I will use SQL Server 2019, Visual Studio 2019 and Microsoft Excel 2013. Some of the C# code will not work if you don’t use the latest .NET version (minimum 4.8). I will use the sample Northwind database as an example.

Create Excel Sheets from SQL Server Queries

The Excel file will be created on the desktop. If a previous file with the same name exists, it will be replaced.

The first query we will use to fill the Excel worksheet checks for orders of a single item:

SELECT
    SalesOrderID,
    MAX(UnitPrice) as UnitPrice, max(OrderQty) as Quantity
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
HAVING COUNT(SalesOrderID) = 1 and max(OrderQty) > 1
order by SalesOrderID

The second query gives the company organization structure: full name, job title, department, the start date, the full name of his direct manager and the manager’s job title:

Select
    employees.BusinessEntityID, employees.Name, employees.JobTitle, employees.Department,
   employees.StartDate,
    managers.name [Reports to],
    case when employees.JobTitle != managers.JobTitle then managers.JobTitle else '' end
        [Manager Job Title]
from
   (
        SELECT
            e.[BusinessEntityID],
            p.FirstName + case when p.MiddleName is not null then ' ' + p.MiddleName else '' end +
               ' ' + p.LastName [Name],
            e.[JobTitle], d.[Name] AS [Department], edh.[StartDate],
            case when e.OrganizationLevel is null then 0 else e.OrganizationLevel end
               OrganizationLevel,
            case when e.OrganizationNode is null then '/' else
               e.OrganizationNode.GetAncestor(1).ToString() end OrganizationNode
        FROM Person.Person p
            join HumanResources.Employee e on e.BusinessEntityID = p.BusinessEntityID
            JOIN [HumanResources].[EmployeeDepartmentHistory] edh
                ON e.[BusinessEntityID] = edh.[BusinessEntityID]
            JOIN [HumanResources].[Department] d ON edh.[DepartmentID] = d.[DepartmentID]
        WHERE edh.EndDate IS NULL
    ) employees
    join
   (
        SELECT
            e.[BusinessEntityID],
            p.FirstName + case when p.MiddleName is not null then ' ' + p.MiddleName else '' end +
               ' ' + p.LastName [Name],
            e.[JobTitle], d.[Name] AS [Department], edh.[StartDate],
            case when e.OrganizationLevel is null then 0 else e.OrganizationLevel end
               OrganizationLevel,
            case when e.OrganizationNode is null then '/' else e.OrganizationNode.ToString() end
               OrganizationNode
        FROM Person.Person p
            join HumanResources.Employee e on e.BusinessEntityID = p.BusinessEntityID
            JOIN [HumanResources].[EmployeeDepartmentHistory] edh
                ON e.[BusinessEntityID] = edh.[BusinessEntityID]
            JOIN [HumanResources].[Department] d ON edh.[DepartmentID] = d.[DepartmentID]
        WHERE edh.EndDate IS NULL
    ) managers
    on employees.OrganizationNode = managers.OrganizationNode
group by employees.department, employees.JobTitle, employees.OrganizationLevel,
    managers.name,
    managers.JobTitle,
    employees.[name], employees.BusinessEntityID, employees.StartDate
order by employees.OrganizationLevel,  employees.JobTitle

In the query, I am using the hierarchyid type in the query above (see this article for more details).

Once we have the queries, we export the first query to a sheet named "Single Item Sales", and the second query to a sheet named "Company Organization Structure".

When we open an Excel file, it will always have one sheet with the default name.

So, we need to add a second sheet:

xlsWorkbook.Sheets.Add(); // Add a second sheet

Next step is to create the header for the first sheet. This can be done with the following code:

// Create the header for the first sheet
xlsSingleItemOrder = (Excel.Worksheet)xlsWorkbook.Sheets[1];
xlsSingleItemOrder.Name = "Single Item Sales";
xlsSingleItemOrder.Cells[1, 1] = "Single Item Sales";
Excel.Range rangeSingleItems = xlsSingleItemOrder.get_Range("A1", "D1");
rangeSingleItems.Merge(1);
rangeSingleItems.Borders.Color = Color.Black.ToArgb();
rangeSingleItems.Interior.Color = Color.Yellow.ToArgb();
rangeSingleItems.Font.Name = "Courier New";
rangeSingleItems.Font.Size = 14;
rangeSingleItems.Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection;

The header for the second sheet is created in a similar way:

// Create the header for the second sheet
xlsManagers = (Excel.Worksheet)xlsWorkbook.Sheets[2];
xlsManagers.Name = "Company Organization Structure";
xlsManagers.Cells[1, 1] = "Company Organization Structure";
Excel.Range rangeManagers = xlsManagers.get_Range("A1", "G1");
rangeManagers.Merge(1);
rangeManagers.Borders.Color = Color.Black.ToArgb();
rangeManagers.Interior.Color = Color.Yellow.ToArgb();
rangeManagers.Font.Name = "Courier New";
rangeManagers.Font.Size = 14;
rangeManagers.Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection;

Exporting data from queries is similar to what was covered in Export SQL Server Table to Excel with C#, so I will not duplicate the code here.

Sample Code

I attached the C# project you can download used which you can use for testing and modifications as needed.

References

Next Steps
  • Try doing a computation in the Excel file with the exported data
  • Create a chart with the exported data


Last Updated: 2020-11-18


get scripts

next tip button



About the author
MSSQLTips author Mircea Dragan Mircea Dragan is a Senior Computer Scientist with a strong mathematical background with over 18 years of hands-on experience.

View all my tips





Comments For This Article





download





Recommended Reading

Insert, Update or Delete Data in SQL Server from Excel

Passing Dynamic Query Values from Excel to SQL Server

Query Folding in Power Query to Improve Performance

How to Create a Burndown Chart in Excel from Scratch

Read Excel File in SQL Server with OPENROWSET or OPENDATASOURCE














get free sql tips
agree to terms