Create Excel from DataTable in C#, VB.NET

3 mins read

Syncfusion® Excel (XlsIO) library is a .NET Excel library used to create, read, and edit Excel documents. It also converts Excel documents to PDF files. Using this library, you can create Excel from a DataTable in C# and VB.NET.

XlsIO supports importing or exporting data between a worksheet and a DataTable in C# and VB.NET. It allows the creation of Excel from a DataTable, which can be achieved through the ImportDataTable method, only if the DataTable is filled with data.

Steps to create Excel from a DataTable, programmatically:

Step 1: Create a new C# console application project.

Create Console application in Visual Studio

Create a new C# console application

Step 2: Install the Syncfusion.XlsIO.WinForms NuGet package as a reference to your .NET Framework applications from the NuGet.org.

Add XlsIO reference to the project

Install NuGet package

Step 3: Include following namespaces in the Program.cs file.

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.

// 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 the 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.

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.

Create Excel from DataTable

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.

Create Excel from DataTable in C#, VB.NET

Steps to create Excel from a DataTable, programmatically:

See Also:

Access denied