Create Excel from DataTable in C#, VB.NET
Syncfusion Excel (XlsIO) library is a .NET Excel library used to create, read, and edit Excel documents. Also, converts Excel documents to PDF files. Using this library, you can create Excel from DataTable in C#, VB.NET.
XlsIO supports importing or exporting data between worksheet and DataTable in C# and VB.NET. It allows to create Excel from DataTable that can be achieved through ImportDataTable method, only if the DataTable is filled with data.
Steps to create Excel from DataTable, programmatically:
Step 1: Create a new C# console application project.
Create a new C# console application
Step 2: Install Syncfusion.XlsIO.WinForms NuGet package as a reference to your .NET Framework applications from the NuGet.org.
Install NuGet package
Step 3: Include following namespaces in Program.cs file.
C#
using Syncfusion.XlsIO; using System; using System.Data;
VB.NET
Imports Syncfusion.XlsIO Imports System Imports System.Data
Step 4: Add the following code snippet to create Excel from DataTable in C#, VB.NET.
C#
//Create an instance of ExcelEngine
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Initialize application
IApplication application = excelEngine.Excel;
//Set the default application version as Excel 2016
application.DefaultVersion = ExcelVersion.Excel2016;
//Create a new workbook
IWorkbook workbook = application.Workbooks.Create(1);
//Access first worksheet from the workbook instance
IWorksheet worksheet = workbook.Worksheets[0];
//Export DataTable to worksheet
//Get data from DataTable
DataTable dataTable = GetDataTable();
//Export DataTable to worksheet with column name and start range
worksheet.ImportDataTable(dataTable, true, 1, 1);
worksheet.UsedRange.AutofitColumns();
//Save the workbook to disk in xlsx format
workbook.SaveAs("Output.xlsx");
}
VB.NET
'Create an instance of ExcelEngine
Using excelEngine As ExcelEngine = New ExcelEngine()
'Initialize application
Dim application As IApplication = excelEngine.Excel
'Set the default application version as Excel 2016
application.DefaultVersion = ExcelVersion.Excel2016
'Create a new workbook
Dim workbook As IWorkbook = application.Workbooks.Create(1)
'Access first worksheet from the workbook instance
Dim worksheet As IWorksheet = workbook.Worksheets(0)
'Exporting DataTable to worksheet
'Get data from DataTable
Dim dataTable As DataTable = GetDataTable()
'Export DataTable to worksheet with column name and start range
worksheet.ImportDataTable(dataTable, True, 1, 1)
worksheet.UsedRange.AutofitColumns()
'Save the workbook to disk in xlsx format
workbook.SaveAs("Output.xlsx")
End Using
Step 5: Load the DataTable using the following simple static method.
C#
private static DataTable GetDataTable()
{
//Create a DataTable with four columns
DataTable table = new DataTable();
table.Columns.Add("Dosage", typeof(int));
table.Columns.Add("Drug", typeof(string));
table.Columns.Add("Patient", typeof(string));
table.Columns.Add("Date", typeof(DateTime));
//Add five DataRows
table.Rows.Add(25, "Indocin", "David", DateTime.Now);
table.Rows.Add(50, "Enebrel", "Sam", DateTime.Now);
table.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now);
table.Rows.Add(21, "Combivent", "Janet", DateTime.Now);
table.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now);
return table;
}
VB.NET
Private Function GetDataTable() As DataTable
'Create a DataTable with four columns
Dim table As DataTable = New DataTable
table.Columns.Add("Dosage", GetType(System.Int32))
table.Columns.Add("Drug", GetType(System.String))
table.Columns.Add("Patient", GetType(System.String))
table.Columns.Add("Date", GetType(DateTime))
'Add five DataRows
table.Rows.Add(25, "Indocin", "David", DateTime.Now)
table.Rows.Add(50, "Enebrel", "Sam", DateTime.Now)
table.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now)
table.Rows.Add(21, "Combivent", "Janet", DateTime.Now)
table.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now)
Return table
End Function
A complete working example to create Excel from DataTable can be downloaded from Create Excel From DataTable.zip.
By executing the program, you will get the output Excel file as shown below.
Output Excel document
Know more about Syncfusion Excel (XlsIO) library through the documentation, where you can find supported features like importing and exporting in DataTable, appending multiple records to worksheet using Template Markers, exporting worksheet into CLR Objects with respective code examples.
Refer here to explore the rich set of Syncfusion Excel (XlsIO) library features.
An online sample link to Export Data using collections.
See Also:
Export Excel to DataTable in C#, VB.NET
Export DataTable to Excel with formatting in C#, VB.NET
How to export DataTable with images to Excel in C#, VB.NET?
Create Excel file in Azure platform
How to create Excel 3D clustered cylinder chart in C#, VB.NET?
Starting with v16.2.0.x, if you reference Syncfusion assemblies from trial setup or from the NuGet feed, include a license key in your projects. Refer the link to learn about generating and registering Syncfusion license key in your application to use the components without trail message.
