Today, in this article we shall cover simple use cases where we will be connecting the SQL server then we will Export Dataset To Excel file C# .NET examples.
In our last post, we saw the usage of OpenXML SDK (open-source SDK from Microsoft) to work with Office Word, Excel, and PowerPoint.
We looked at the basic read and write of an Excel file using C# in .NET or .NET Core.
Today in this article, we will cover below aspects,
Getting Started
Let’s create a .NET Core project, you can choose any project template. Here we shall use the .NET Core 3.1 Console project.
Please install the latest available OpenXML Nuget package,
PM> Install-Package Open-XML-SDK -Version 2.11.3
Or
Please install the package from Nuget Package Manager,
Connecting SQL Server
Let’s now connect the SQL server and get the data filled as DataSet.
I have already discussed how to connect to the SQL server using SQLClient or EFCore in the below article.
Please use the below method which takes Dataset as input and saves each table as a separate sheet in the Excel sheet.
public static void SaveDataSetAsExcel(DataSet dataset, string excelFilePath)
{
using (SpreadsheetDocument document = SpreadsheetDocument.Create(excelFilePath, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
foreach (DataTable table in dataset.Tables)
{
UInt32Value sheetCount = 0;
sheetCount++;
WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
var sheetData = new SheetData();
worksheetPart.Worksheet = new Worksheet(sheetData);
Sheet sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = sheetCount, Name = table.TableName };
sheets.AppendChild(sheet);
Row headerRow = new Row();
List<string> columns = new List<string>();
foreach (System.Data.DataColumn column in table.Columns)
{
columns.Add(column.ColumnName);
Cell cell = new Cell();
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(column.ColumnName);
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
foreach (DataRow dsrow in table.Rows)
{
Row newRow = new Row();
foreach (String col in columns)
{
Cell cell = new Cell();
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(dsrow[col].ToString());
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
}
workbookPart.Workbook.Save();
}
Finally, you shall see all the tables in the Dataset are saved as different tabs in the excel file.
In the above code, we are creating each sheet as a table name and its data.
References:
That’s all! Happy coding!
Does this help you fix your issue?
Do you have any better solutions or suggestions? Please sound off your comments below.
Please bookmark this page and share it with your friends. Please Subscribe to the blog to receive notifications on freshly published(2024) best practices and guidelines for software design and development.