Export Excel to DataTable in C#, VB.NET
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 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 the following namespaces in the Program.cs file.
C#
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.
C#
//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.
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.
See Also:
Create Excel from DataTable in C#, VB.NET
Export DataTable to Excel 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?
How to export Excel data to JSON format?
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.
