User GuideFAQHow to export DataTable to Excel file in C# and VB.NET
How to export DataTable to Excel file in C# and VB.NET
EasyXLS™ library allows you to export a DataTable to an Excel file. The data in cells can be formatted using predefined formats or user-defined formats.
EasyXLS can be successfully used inclusively to export large Excel files having big volume of data with fast exporting time.
The source code samples can be integrated in:
- ASP.NET web pages - Windows applications - Windows Forms (WinForms) - Console applications - Windows service applications - ASP.NET MVC web applications
EasyXLS permits you to export Excel files without Excel installed, without Interop or any other additional software installed.
' Create and populate the datatableDim dt As DataTable = New DataTable
Dim columns As DataColumn() = {New DataColumn("ID"), New DataColumn("Value")}
Dim row1 As Object() = {"1", "Value1"}
Dim row2 As Object() = {"2", "Value2"}
Dim row3 As Object() = {"3", "Value3"}
dt.Columns.AddRange(columns)
dt.Rows.Add(row1)
dt.Rows.Add(row2)
dt.Rows.Add(row3)
' Create a dataset that keeps the datatableDim ds As DataSet = New DataSet
ds.Tables.Add(dt)
' Export the dataset and its datatable to Excel fileDim workbook As EasyXLS.ExcelDocument = New EasyXLS.ExcelDocument
workbook.easy_WriteXLSXFile_FromDataSet( _
"C:\Samples\DataTable to Excel.xlsx", _
ds, _
New EasyXLS.ExcelAutoFormat(EasyXLS.Constants.Styles.AUTOFORMAT_EASYXLS1), _
"DataTable")
' Dispose memory
workbook.Dispose()
ds.Dispose()
Export DataTable to Excel file with multiple sheets
EasyXLS offers the option to insert multiple DataTables into one worksheet or into multiple worksheets. The DataTable can be added into a specified worksheet. The column names of the DataTable can be optionally included.
The below source code sample shows how to add a DataTable with a nice formatting into an Excel file with two worksheets.
// Create and populate the datatable
DataTable dt = new DataTable();
DataColumn[] columns = { new DataColumn("ID"), new DataColumn("Value") };
Object[] row1 = { "1", "Value1" };
Object[] row2 = { "2", "Value2" };
Object[] row3 = { "3", "Value3" };
dt.Columns.AddRange(columns);
dt.Rows.Add(row1);
dt.Rows.Add(row2);
dt.Rows.Add(row3);
// Create a dataset that keeps the datatable
DataSet ds = new DataSet();
ds.Tables.Add(dt);
// Create an instance of the class that exports Excel files
ExcelDocument workbook = new ExcelDocument();
// Create a worksheet
workbook.easy_addWorksheet("First sheet");
// Create another worksheet
workbook.easy_addWorksheet("Second sheet");
// Get the second sheet
ExcelWorksheet worksheet = (ExcelWorksheet)workbook.easy_getSheet("Second sheet");
// Insert DataTable with column names included
worksheet.easy_insertDataSet(ds, new ExcelAutoFormat(Styles.AUTOFORMAT_EASYXLS1), true);
// Export Excel file
workbook.easy_WriteXLSXFile("C:\\Samples\\DataTable to Excel.xlsx");
// Dispose memory
workbook.Dispose();
ds.Dispose();
' Create and populate the datatableDim dt As New DataTable()
Dim columns As DataColumn() = {New DataColumn("ID"), New DataColumn("Value")}
Dim row1 As Object() = {"1", "Value1"}
Dim row2 As Object() = {"2", "Value2"}
Dim row3 As Object() = {"3", "Value3"}
dt.Columns.AddRange(columns)
dt.Rows.Add(row1)
dt.Rows.Add(row2)
dt.Rows.Add(row3)
' Create a dataset that keeps the datatableDim ds As New DataSet()
ds.Tables.Add(dt)
' Create an instance of the class that exports Excel filesDim workbook As New ExcelDocument()
' Create a worksheet
workbook.easy_addWorksheet("First sheet")
' Create another worksheet
workbook.easy_addWorksheet("Second sheet")
' Get the second sheetDim worksheet As ExcelWorksheet = workbook.easy_getSheet("Second sheet")
' Insert DataTable with column names included
worksheet.easy_insertDataSet(ds, New ExcelAutoFormat(Styles.AUTOFORMAT_EASYXLS1), True)
' Export Excel file
workbook.easy_WriteXLSXFile("C:\\Samples\\DataTable to Excel.xlsx")
' Dispose memory
workbook.Dispose()
ds.Dispose()
Formatting cells
EasyXLS™ enables you to format cells, rows and columns in order to set the fonts and colors.
