Export Excel to DataTable in C#, VB.NET

2 mins read

Syncfusion Excel (XlsIO) library is a .NET Excel library used to create, read, and edit Excel documents. Using this library, you can export Excel to DataTable in C#, VB.NET.

XlsIO supports importing or exporting data between worksheet and DataTable. It allows exporting data from worksheet to DataTables and CLR objects including DataTable and DynamicObjects. Different exporting options for exporting data to DataTable called ExcelExportDataTableOptions are also supported in XlsIO facilitating customized import.

Steps to export Excel to 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 the following namespaces in the Program.cs file.

using Syncfusion.XlsIO;
using System.Data;
using System.IO;
using System.Reflection;

VB.NET

Imports Syncfusion.XlsIO
Imports System.Data
Imports System.IO
Imports System.Reflection

Step 4: Add the following code snippet to open an existing Excel file and export Excel to DataTable in C#, VB.NET.

//Create an instance of ExcelEngine
using (ExcelEngine excelEngine = new ExcelEngine())
{
    //Initialize application
    IApplication application = excelEngine.Excel;
                
    //Open existing workbook with data entered
    Assembly assembly = typeof(Program).GetTypeInfo().Assembly;
    Stream fileStream = assembly.GetManifestResourceStream("ExportExcelToDataTable_CS.Sample.xlsx");
    IWorkbook workbook = application.Workbooks.Open(fileStream);
 
    //Access first worksheet from the workbook instance
    IWorksheet worksheet = workbook.Worksheets[0];
 
    //Export Excel to DataTable
    DataTable dataTable = worksheet.ExportDataTable(worksheet.UsedRange, ExcelExportDataTableOptions.ColumnNames);
 
    //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
 
    'Open existing workbook with data entered
    Dim assembly As Assembly = GetType(Module1).GetTypeInfo.Assembly
    Dim fileStream As Stream = assembly.GetManifestResourceStream("ExportExcelToDataTable_VB.Sample.xlsx")
    Dim workbook As IWorkbook = application.Workbooks.Open(fileStream)
 
    'Access first worksheet from the workbook instance
    Dim worksheet As IWorksheet = workbook.Worksheets(0)
 
    'Export Excel to DataTable
    Dim dataTable As DataTable = worksheet.ExportDataTable(worksheet.UsedRange, ExcelExportDataTableOptions.ColumnNames)
 
    'Save the workbook to disk in xlsx format
    workbook.SaveAs("Output.xlsx")
 
End Using

A complete working example to export Excel to DataTable in C#, VB.NET along with the input file used can be downloaded from Export Excel To DataTable.zip.

By executing the program, you will get the DataTable as shown below.

Export Excel to DataTable

Output DataTable

Know more about Syncfusion Excel (XlsIO) library through the documentation, where you can find supported features like importing and exporting data in DataTable, appending multiple records to worksheet using Template Markers, exporting data in 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 CLR Objects.

Export Excel to DataTable in C#, VB.NET

Steps to export Excel to DataTable, programmatically:

See Also:

Access denied