Export DataSet To Excel in C# .Net Core – OpenXml
In this post, we will see how to convert or export DataSet (DataTable) to an Excel file using OpenXml and C# code (Web API, .Net Core). OpenXML SDK is a free, open-source NuGet package from Microsoft that provides tools to work with Office Word, Excel, and PowerPoint documents programmatically.
We will cover, how to:
- Generating one or more Sheets in a single Excel file depending on DataTables in a DataSet
- Customizing cell style (Stylesheet): Cell Formats (Fonts, Border, Fill) and Alignments (Wrap Text, Vertical, Horizontal)
- Resize each Column’s width
Steps to generate excel sheet
1. Install OpenXml SDK Nuget Package
Create a new Web API project and add the OpenXml package.
2. Create API to download excel file
Add the below Get API method in the Controller class, so that the generated excel file can be downloaded. The Swagger tool will be used to test this API.
[HttpGet]
public IActionResult DownloadExcelFile()
{
DataSet dataSet = new DataSet();
//Configure DB connection string in appsetting.json
using (SqlConnection connection = new SqlConnection(_conn))
{
/* for multi Sheet either enter multiple 'Select' statement as inline query
or get multiple result set from Stored Procedure from DB*/
SqlDataAdapter adapter = new SqlDataAdapter
{
SelectCommand = new SqlCommand("Select * from Products", connection)
};
adapter.Fill(dataSet);
}
return File(Helper.ExportToExcelDownload(dataSet), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "YourFileName.xls");
} 3. Export to Excel C# Code
Create a static class called “Helper.cs” and add a static method to generate one or multiple sheets in the excel file from DataSet.
Simply, copy & paste the entire code given below. That’s it, done.
Just call ExportToExcelDownload() method and pass the DataSet result as input. It will create one or more Sheets based on the number of DataTables in the DataSet and return the fully formed Excel file as bytes.
Download this Project (configure DB and inline query before running this project).
Also, read how to change the Cell format – explained below.
Make sure all the necessary namespaces are added.
Scroll down to the output screenshot
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System;
using System.Data;
using System.Globalization;
using System.IO; public static class Helper
{
private const string noRecordsToDisplay = "No records to display";
public static byte[] ExportToExcelDownload(DataSet dataSet)
{
byte[] byteResult = null;
if (dataSet == null) { return byteResult; }
if (dataSet.Tables.Count > 0)
{
using (MemoryStream stream = new MemoryStream())
{
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook))
{
// Add a WorkbookPart to the document.
WorkbookPart workbookpart = AddWorkbookPart(spreadsheetDocument);
AddSheet(spreadsheetDocument, out Sheets sheets, out uint currentSheetID);
AddNewPartStyle(workbookpart);
int rowIndexCount = 1;
foreach (DataTable dt in dataSet.Tables)
{
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new Worksheet();
Columns columns = SetDefaultColumnWidth();
worksheetPart.Worksheet.Append(columns);
SheetData sheetData = new SheetData();
worksheetPart.Worksheet.AppendChild(sheetData);
// Append a new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = currentSheetID,
Name = string.IsNullOrWhiteSpace(dt.TableName) ? "Sheet" + currentSheetID : dt.TableName
};
if (dt.Rows.Count == 0)
{
//if table rows count is 0, create Excel Sheet with default message
CreateDefaultWithMessage(rowIndexCount, sheetData);
}
else
{
int numberOfColumns = dt.Columns.Count;
string[] excelColumnNames = new string[numberOfColumns];
//Create Header
Row SheetrowHeader = CreateHeader(rowIndexCount, dt, numberOfColumns, excelColumnNames);
sheetData.Append(SheetrowHeader);
++rowIndexCount;
//Create Body
rowIndexCount = CreateBody(rowIndexCount, dt, sheetData, excelColumnNames);
}
sheets.Append(sheet);
++currentSheetID;
rowIndexCount = 1;
}
workbookpart.Workbook.Save();
// Close the document.
//spreadsheetDocument.Close();
}
stream.Flush();
stream.Position = 0;
byteResult = new byte[stream.Length];
stream.Read(byteResult, 0, byteResult.Length);
}
}
return byteResult;
}
//Customize column width
private static Columns SetDefaultColumnWidth()
{
Columns columns = new Columns();
//width of 1st Column
columns.Append(new Column() { Min = 1, Max = 1, Width = 25, CustomWidth = true });
//with of 2st Column
columns.Append(new Column() { Min = 2, Max = 2, Width = 50, CustomWidth = true });
//set column width from 3rd to 400 columns
columns.Append(new Column() { Min = 3, Max = 400, Width = 10, CustomWidth = true });
return columns;
}
private static void AddNewPartStyle(WorkbookPart workbookpart)
{
WorkbookStylesPart stylePart = workbookpart.AddNewPart<WorkbookStylesPart>();
stylePart.Stylesheet = GenerateStylesheet();
stylePart.Stylesheet.Save();
}
private static void AddSheet(SpreadsheetDocument spreadsheetDocument, out Sheets sheets, out uint currentSheetID)
{
sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild(new Sheets());
currentSheetID = 1;
}
private static WorkbookPart AddWorkbookPart(SpreadsheetDocument spreadsheetDocument)
{
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
return workbookpart;
}
private static void CreateDefaultWithMessage(int rowIndexCount, SheetData sheetData)
{
Row Sheetrow = new Row() { RowIndex = Convert.ToUInt32(rowIndexCount) };
Cell cellHeader = new Cell() { CellReference = "A1", CellValue = new CellValue(noRecordsToDisplay), DataType = CellValues.String };
cellHeader.StyleIndex = 1;
Sheetrow.Append(cellHeader);
sheetData.Append(Sheetrow);
}
private static int CreateBody(int rowIndexCount, DataTable dt, SheetData sheetData, string[] excelColumnNames)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
Row Sheetrow = new Row() { RowIndex = Convert.ToUInt32(rowIndexCount) };
for (int j = 0; j < dt.Columns.Count; j++)
{
// insert value in cell with dataType (String, Int, decimal, datatime)
Sheetrow.Append(GetCellWithDataType(excelColumnNames[j] + rowIndexCount, dt.Rows[i][j], dt.Columns[j].DataType));
}
sheetData.Append(Sheetrow);
++rowIndexCount;
}
return rowIndexCount;
}
private static Row CreateHeader(int rowIndexCount, DataTable dt, int numberOfColumns, string[] excelColumnNames)
{
Row SheetrowHeader = new Row() { RowIndex = Convert.ToUInt32(rowIndexCount) };
for (int n = 0; n < numberOfColumns; n++)
{
excelColumnNames[n] = GetExcelColumnName(n);
Cell cellHeader = new Cell() { CellReference = excelColumnNames[n] + rowIndexCount, CellValue = new CellValue(dt.Columns[n].ColumnName), DataType = CellValues.String };
cellHeader.StyleIndex = 2;
SheetrowHeader.Append(cellHeader);
}
return SheetrowHeader;
}
private static string GetExcelColumnName(int columnIndex)
{
if (columnIndex < 26)
{
return ((char)('A' + columnIndex)).ToString();
}
char firstChar = (char)('A' + (columnIndex / 26) - 1);
char secondChar = (char)('A' + (columnIndex % 26));
return string.Format(CultureInfo.CurrentCulture, "{0}{1}", firstChar, secondChar);
}
private static Stylesheet GenerateStylesheet()
{
Fonts fonts = GenerateFonts();
Fills fills = GenerateFills();
Borders borders = GenerateBorders();
CellFormats cellFormats = GenerateCellFormats();
Column column = GenerateColumnProperty();
Stylesheet styleSheet = new Stylesheet(fonts, fills, borders, cellFormats, column);
return styleSheet;
}
private static Column GenerateColumnProperty()
{
return new Column
{
Width = 100,
CustomWidth = true
};
}
private static CellFormats GenerateCellFormats()
{
CellFormats cellFormats = new CellFormats(
// default - Cell StyleIndex = 0
new CellFormat(new Alignment() { WrapText = true, Vertical = VerticalAlignmentValues.Top }),
// default2 - Cell StyleIndex = 1
new CellFormat(new Alignment() { WrapText = true, Vertical = VerticalAlignmentValues.Top }) { FontId = 0, FillId = 0, BorderId = 1, ApplyBorder = true },
// header - Cell StyleIndex = 2
new CellFormat(new Alignment() { WrapText = true, Vertical = VerticalAlignmentValues.Top }) { FontId = 1, FillId = 0, BorderId = 1, ApplyFill = true },
// DateTime DataType - Cell StyleIndex = 3
new CellFormat(new Alignment() { Vertical = VerticalAlignmentValues.Top }) { FontId = 0, FillId = 0, BorderId = 1, ApplyBorder = true, NumberFormatId = 15, ApplyNumberFormat = true },
// int,long,short DataType - Cell StyleIndex = 4
new CellFormat(new Alignment() { WrapText = true, Vertical = VerticalAlignmentValues.Top }) { FontId = 0, FillId = 0, BorderId = 1, ApplyBorder = true, NumberFormatId = 1 },
// decimal DataType - Cell StyleIndex = 5
new CellFormat(new Alignment() { WrapText = true, Vertical = VerticalAlignmentValues.Top }) { FontId = 0, FillId = 0, BorderId = 1, ApplyBorder = true, NumberFormatId = 2 }
);
return cellFormats;
}
private static Borders GenerateBorders()
{
Borders borders = new Borders(
// index 0 default
new Border(),
// index 1 black border
new Border(
new LeftBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin },
new RightBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin },
new TopBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin },
new BottomBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin },
new DiagonalBorder())
);
return borders;
}
private static Fills GenerateFills()
{
Fills fills = new Fills(
// Index 0
new Fill(new PatternFill() { PatternType = PatternValues.None }),
// Index 1
new Fill(new PatternFill() { PatternType = PatternValues.Gray125 }),
// Index 2 - header
new Fill(new PatternFill(new ForegroundColor { Rgb = new HexBinaryValue() { Value = "66666666" } }) { PatternType = PatternValues.Solid })
);
return fills;
}
private static Fonts GenerateFonts()
{
Fonts fonts = new Fonts(
// Index 0 - default
new Font(
new FontSize() { Val = 10 },
new FontName() { Val = "Arial Unicode" }
),
// Index 1 - header
new Font(
new FontSize() { Val = 10 },
new Bold()//,
//new Color() { Rgb = "FFFFFF" }
));
return fonts;
}
private static Cell GetCellWithDataType(string cellRef, object value, Type type)
{
if (type == typeof(DateTime))
{
Cell cell = new Cell()
{
DataType = new EnumValue<CellValues>(CellValues.Number),
StyleIndex = 3
};
if (value != DBNull.Value)
{
System.Globalization.CultureInfo cultureinfo = new System.Globalization.CultureInfo("en-US");
DateTime valueDate = (DateTime)value;
string valueString = valueDate.ToOADate().ToString(cultureinfo);
CellValue cellValue = new CellValue(valueString);
cell.Append(cellValue);
}
return cell;
}
if (type == typeof(long) || type == typeof(int) || type == typeof(short))
{
Cell cell = new Cell() { CellReference = cellRef, CellValue = new CellValue(value.ToString()), DataType = CellValues.Number };
cell.StyleIndex = 4;
return cell;
}
if (type == typeof(decimal))
{
Cell cell = new Cell() { CellReference = cellRef, CellValue = new CellValue(value.ToString()), DataType = CellValues.Number };
cell.StyleIndex = 5;
return cell;
}
else
{
Cell cell = new Cell() { CellReference = cellRef, CellValue = new CellValue(value.ToString()), DataType = CellValues.String };
cell.StyleIndex = 1;
return cell;
}
}
} Run the application and test export to excel functionality from Swagger.
Execute the Get Excel API
The below screenshot shows a successful API response code-200 with downloadable Excel file –SampleExcel.xls
Output
You might receive a ‘trust source’ warning popup message while opening the downloaded file. Just click on the ‘Yes’ button to open the excel file.
That’s all about generating the multi-sheet excel file from DataSet.
Now, we will see how to format Header or Body Cell like wrapping text, changing fonts, filling colors to a cell, resizing the column width, and applying borders from code.
How to work with style Index in Open XML?
To apply different styles to a cell, create a set of Cell formats or ‘StyleIndex’ and assign that particular format while creating a Cell. For example, if you want to fill color for a Header Cell then you need to define two indexes or formats –
- Style Index 0 – ‘no fill’ as default (white color) which applies to common body Cell
- Style Index 1 – fill with light grey color for Header Cell
While creating a Header apply ‘Index 1’ to the cell property and for normal Cells use ‘Index 0’.
So in this way, all Cell styles or formats are defined in this sample code. The below screenshots shows the code flow of Cell StyleIndex.
Cell StyleIndex Code Flow – Explanation
1. Create Cell Formats – StyleIndex
First, create different Cell formats. We have created six cell formats, refer GenerateCellFormats() method in the example above:
- StyleIndex = 0 – Default without border
- StyleIndex = 1 – Default with border
- StyleIndex = 2 – for Header cell
- StyleIndex = 3 – DateTime data type cell
- StyleIndex = 4 – Integer data type cell
- StyleIndex = 5 – decimal data type cell
Apply the correct style index when creating a cell. Please find the following lines in the code:
a) CellFormats GenerateCellFormats() – Method
// header – Cell StyleIndex = 2
new CellFormat(new Alignment() { WrapText = true, Vertical = VerticalAlignmentValues.Top }) { FontId = 1, FillId = 0, BorderId = 1, ApplyFill = true },
b) apply style index to the header cell
cellHeader.StyleIndex = 2;
2. Create Indexes for Font, Border, Fill
FontId, FillId and BorderId are defined with some index value, in each CellFormats. So, you have to define a set of styles for each attribute.
i. Defining Font Index
Refer GenerateFonts() method in code – defines two indexes: one for the normal cell (default) and another for the header cell. See the highlighted text above – FontId = 1
ii. Define Borders
Refer GenerateBorders() method in code – defines two indexes: one without borderline and another with borderline. See the highlighted text above – BorderId = 1,
iii. Define Fill Colors
Refer GenerateFills() method in code – defines three indexes.
See the highlighted text above – FillId = 0. It applies the default color to a cell.
How to Customize Column Width – OpenXml
Refer SetDefaultColumnWidth() method in code – defines width size up to 400 columns. ‘Min’ & ‘Max’ is a column range. As shown in the screenshot below, the first column’s Min & Max value is 1 which means the width size 25 is applicable only to the 1st column.
In the same way, width size 50 is for the 2nd column.
If you want to assign the same width value to more than one column then increase the Max property value. Width column size 10 is applied from the 3rd to 400th columns in the code.
Hope you liked it! Happy Coding!! Thanks 😉
What a fantastic and detailed explanation. It is the best tutorial explaining Excel sheet formatting with OpenXml.