We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. Image for the cookie policy date
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

Create Excel from DataTable in C#, VB.NET

Platform: WinForms |
Control: XlsIO |
Published Date: August 6, 2018 |
Last Revised Date: June 12, 2019

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 Console application in Visual Studio

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.

Add XlsIO reference to the project

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.

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.

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?

Note:

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.

 

2X faster development

The ultimate WinForms UI toolkit to boost your development speed.
ADD COMMENT
You must log in to leave a comment

Please sign in to access our KB

This page will automatically be redirected to the sign-in page in 10 seconds.

Up arrow icon

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

Live Chat Icon For mobile