Export from Excel Document
24 Jul 202624 minutes to read
Excel to DataTable
XlsIO allows you to export the sheet data to a DataTable by using the ExportDataTable() method. This method provides various options that allow you to export data with specific requirements through ExcelExportDataTableOptions.
NOTE
XlsIO supports exporting of data from worksheet to data table in Windows Forms, WPF, ASP.NET, ASP.NET MVC and ASP.NET Core (2.0 onwards) platforms alone.
The following code example illustrates how to export data from an Excel sheet to a data grid using a DataTable.
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
IWorkbook workbook = application.Workbooks.Open(Path.GetFullPath(@"Data/InputTemplate.xlsx"));
IWorksheet worksheet = workbook.Worksheets[0];
//Read all data from the used range of worksheet and Export to the DataTable
DataTable customersTable = worksheet.ExportDataTable(worksheet.UsedRange, ExcelExportDataTableOptions.ColumnNames | ExcelExportDataTableOptions.ComputedFormulaValues);
IRange range = worksheet.Range["A1:B10"];
//Read specified range from the worksheet and Export to DataTable
DataTable customersTable1 = worksheet.ExportDataTable(range, ExcelExportDataTableOptions.ColumnNames | ExcelExportDataTableOptions.ComputedFormulaValues);
}
//XlsIO supports binding of exported data table to a data grid in Windows Forms, WPF, ASP.NET, and ASP.NET MVC platforms alone.using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Export3.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];
//Read data from the worksheet and Export to the DataTable
DataTable customersTable = worksheet.ExportDataTable(worksheet.UsedRange, ExcelExportDataTableOptions.ColumnNames | ExcelExportDataTableOptions.ComputedFormulaValues);
//Bind the exported DataTable to a data grid. Similarly, it can be bound to any
//user interface control that supports binding.
DataGrid dataGrid = new DataGrid();
dataGrid.DataSource = customersTable;
workbook.SaveAs("ExportToGrid.xlsx");
}Using excelEngine As ExcelEngine = New ExcelEngine()
Dim application As IApplication = excelEngine.Excel
application.DefaultVersion = ExcelVersion.Excel2013
Dim workbook As IWorkbook = application.Workbooks.Open("Sample.xlsx")
Dim sheet As IWorksheet = workbook.Worksheets(0)
'Read data from the worksheet and Export to the DataTable
Dim customersTable As DataTable = sheet.ExportDataTable(sheet.UsedRange, ExcelExportDataTableOptions.ColumnNames Or ExcelExportDataTableOptions.ComputedFormulaValues)
'Bind the exported DataTable to a data grid. Similarly, it can be bound to any
'user interface control that supports binding.
Dim dataGrid As DataGrid = New DataGrid
dataGrid.DataSource = customersTable
workbook.SaveAs("ExportToGrid.xlsx")
End UsingA complete working example to export data from Excel to DataTable in C# is present on this GitHub page.
Excel to DataTable with an Event
Sometimes you may need to control the data export from Excel to a data table. XlsIO provides an event ExportDataTableEvent that is triggered while exporting data from an Excel worksheet to a data table. This event helps perform the following actions through the ExportDataTableActions enumeration.
- Default – Exports worksheet data to the data table without any action.
- SkipRow – Exports worksheet data to the data table by skipping a specific row.
- StopExporting – Stops exporting the data from the Excel worksheet to the data table.
NOTE
XlsIO supports exporting of data from worksheet to data table in Windows Forms, WPF, ASP.NET, ASP.NET MVC and ASP.NET Core (NETStandard2.0 onwards) platforms alone.
The following code example illustrates how to export data from an Excel worksheet to a data table by triggering an event.
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2016;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];
//Event to choose an action while exporting data from Excel to data table.
worksheet.ExportDataTableEvent += ExportDataTable_EventAction();
//Read data from the worksheet and Export to the DataTable
DataTable customersTable = worksheet.ExportDataTable(worksheet.UsedRange, ExcelExportDataTableOptions.ColumnNames);
//Saving the workbook
workbook.SaveAs("ExportToDT.xlsx");
}
//XlsIO supports binding of exported data table to a data grid in Windows Forms, WPF, ASP.NET, and ASP.NET MVC platforms alone.using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2016;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];
//Event to choose an action while exporting data from Excel to data table.
worksheet.ExportDataTableEvent += ExportDataTable_EventAction;
//Read data from the worksheet and Export to the DataTable
DataTable customersTable = worksheet.ExportDataTable(worksheet.UsedRange, ExcelExportDataTableOptions.ColumnNames);
//Bind the exported data table to a data grid. It can be bound to any control that supports the data table.
DataGrid dataGrid = new DataGrid();
dataGrid.DataSource = customersTable;
workbook.SaveAs("ExportToGrid.xlsx");
}Using excelEngine As ExcelEngine = New ExcelEngine()
Dim application As IApplication = excelEngine.Excel
application.DefaultVersion = ExcelVersion.Excel2016
Dim workbook As IWorkbook = application.Workbooks.Open("Sample.xlsx")
Dim sheet As IWorksheet = workbook.Worksheets(0)
'Event to choose an action while exporting data from Excel to data table.
sheet.ExportDataTableEvent += ExportDataTable_EventAction()
'Read data from the worksheet and Export to the DataTable
Dim customersTable As DataTable = sheet.ExportDataTable(sheet.UsedRange, ExcelExportDataTableOptions.ColumnNames)
'Bind the exported data table to a data grid. It can be bound to any control that supports the data table.
Dim dataGrid As DataGrid = New DataGrid
dataGrid.DataSource = customersTable
workbook.SaveAs("ExportToGrid.xlsx")
End UsingThe following code is the event handler for the above code.
private void ExportDataTable_EventAction(ExportDataTableEventArgs e)
{
if (e.ExcelValue != null && e.ExcelValue.ToString() == "Owner")
e.ExportDataTableAction = ExportDataTableActions.SkipRow;
if (e.DataTableColumnIndex == 0 && e.ExcelRowIndex == 5 && e.ExcelColumnIndex == 1)
e.ExportDataTableAction = ExportDataTableActions.StopExporting;
if (e.ExcelValue != null && e.ExcelValue.ToString() == "Mexico D.F.")
e.DataTableValue = "Mexico";
if (e.ColumnType.ToString() == "Double" && e.ExcelValue != null)
e.DataTableValue = 30;
}private void ExportDataTable_EventAction(ExportDataTableEventArgs e)
{
if (e.ExcelValue != null && e.ExcelValue.ToString() == "Owner")
e.ExportDataTableAction = ExportDataTableActions.SkipRow;
if (e.DataTableColumnIndex == 0 && e.ExcelRowIndex == 5 && e.ExcelColumnIndex == 1)
e.ExportDataTableAction = ExportDataTableActions.StopExporting;
if (e.ExcelValue != null && e.ExcelValue.ToString() == "Mexico D.F.")
e.DataTableValue = "Mexico";
if (e.ColumnType.ToString() == "Double" && e.ExcelValue != null)
e.DataTableValue = 30;
}Private Sub ExportDataTable_EventAction(ByVal e As ExportDataTableEventArgs)
If e.ExcelValue IsNot Nothing AndAlso e.ExcelValue.ToString() = "Owner" Then
e.ExportDataTableAction = ExportDataTableActions.SkipRow
If e.DataTableColumnIndex = 0 AndAlso e.ExcelRowIndex = 5 AndAlso e.ExcelColumnIndex = 1 Then
e.ExportDataTableAction = ExportDataTableActions.StopExporting
If e.ExcelValue IsNot Nothing AndAlso e.ExcelValue.ToString() = "Mexico D.F." Then
e.DataTableValue = "Mexico"
If e.ColumnType.ToString() = "double" AndAlso e.ExcelValue IsNot Nothing Then
e.DataTableValue = 30
End SubExcel to Collection Objects
XlsIO allows you to export the sheet data to collection objects by using the ExportData<T>() method. The parameters represent the starting row, starting column, ending row, and ending column of the range to export.
The following code example illustrates how to export Excel data into collection objects using ExportData<T>.
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
IWorkbook workbook = application.Workbooks.Open(Path.GetFullPath(@"Data/InputTemplate.xlsx"));
IWorksheet worksheet = workbook.Worksheets[0];
//Export worksheet data into Collection Objects
List<Report> collectionObjects = worksheet.ExportData<Report>(1, 1, 10, 3);
}using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];
//Export worksheet data into Collection Objects
List<Report> collectionObjects = worksheet.ExportData<Report>(1, 1, 10, 3);
workbook.SaveAs("CollectionObjects.xlsx");
}Using excelEngine As ExcelEngine = New ExcelEngine()
Dim application As IApplication = excelEngine.Excel
application.DefaultVersion = ExcelVersion.Excel2013
Dim workbook As IWorkbook = application.Workbooks.Open("Sample.xlsx")
Dim worksheet As IWorksheet = workbook.Worksheets(0)
'Export worksheet data into Collection Objects
Dim collectionObjects As List(Of Report) = worksheet.ExportData(Of Report)(1, 1, 10, 3)
workbook.SaveAs("CollectionObjects.xlsx")
End UsingThe following code snippet provides the supporting class for the above code. Here, the attributes DisplayNameAttribute and Bindable are used.
- DisplayNameAttribute – matches the column headers in the worksheet with the corresponding class properties while exporting.
-
BindableAttribute – when set to
false, excludes the property from the export.
public class Report
{
[DisplayNameAttribute("Sales Person Name")]
public string SalesPerson { get; set; }
[Bindable(false)]
public string SalesJanJun { get; set; }
public string SalesJulDec { get; set; }
public Report()
{
}
}public class Report
{
[DisplayNameAttribute("Sales Person Name")]
public string SalesPerson { get; set; }
[Bindable(false)]
public string SalesJanJun { get; set; }
public string SalesJulDec { get; set; }
public Report()
{
}
}Public Class Report
Private m_SalesPerson As String
Private m_SalesJanJun As String
Private m_SalesJulDec As String
<DisplayNameAttribute("Sales Person Name")>
Public Property SalesPerson() As String
Get
Return m_SalesPerson
End Get
Set(value As String)
m_SalesPerson = Value
End Set
End Property
<Bindable(False)>
Public Property SalesJanJun() As String
Get
Return m_SalesJanJun
End Get
Set(value As String)
m_SalesJanJun = Value
End Set
End Property
Public Property SalesJulDec() As String
Get
Return m_SalesJulDec
End Get
Set(value As String)
m_SalesJulDec = Value
End Set
End Property
End ClassA complete working example to export data from Excel to collection objects in C# is present on this GitHub page.
Excel to Nested Class Objects
XlsIO allows you to export worksheet data to nested class objects. An overload of the [ExportData
Consider the following input Excel document, as shown in the screenshot below.
The following code example illustrates how to export data from Excel to nested class objects using a column header mapping collection.
using System.Collections.Generic;
using System.IO;
using Syncfusion.XlsIO;
namespace Worksheet_to_Nested_Class
{
class Program
{
static void Main(string[] args)
{
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
IWorkbook workbook = application.Workbooks.Open(Path.GetFullPath(@"Data/InputTemplate.xlsx"));
IWorksheet worksheet = workbook.Worksheets[0];
//Map column headers in worksheet with class properties.
Dictionary<string, string> mappingProperties = new Dictionary<string, string>();
mappingProperties.Add("Customer ID", "CustId");
mappingProperties.Add("Customer Name", "CustName");
mappingProperties.Add("Customer Age", "CustAge");
mappingProperties.Add("Order ID", "CustOrder.Order_Id");
mappingProperties.Add("Order Price", "CustOrder.Price");
//Export worksheet data into nested class Objects.
List<Customer> nestedClassObjects = worksheet.ExportData<Customer>(1, 1, 10, 5, mappingProperties);
}
}
}
//Customer details class
public partial class Customer
{
public int CustId { get; set; }
public string CustName { get; set; }
public int CustAge { get; set; }
public Order CustOrder { get; set; }
public Customer()
{
}
}
//Order details class
public partial class Order
{
public string Order_Id { get; set; }
public double Price { get; set; }
public Order()
{
}
}
}using Syncfusion.XlsIO;
using System.Collections.Generic;
namespace ImportFromNestedCollection
{
class Program
{
static void Main(string[] args)
{
ExportData();
}
//Main method to Export data from worksheet to nested class objects.
private static void ExportData()
{
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];
//Map column headers in worksheet with class properties.
Dictionary<string, string> mappingProperties = new Dictionary<string, string>();
mappingProperties.Add("Customer ID", "CustId");
mappingProperties.Add("Customer Name", "CustName");
mappingProperties.Add("Customer Age", "CustAge");
mappingProperties.Add("Order ID", "CustOrder.Order_Id");
mappingProperties.Add("Order Price", "CustOrder.Price");
//Export worksheet data into nested class Objects.
List<Customer> nestedClassObjects = worksheet.ExportData<Customer>(1, 1, 10, 5, mappingProperties);
workbook.SaveAs("NestedClassObjects.xlsx");
}
}
}
//Customer details class
public partial class Customer
{
public int CustId { get; set; }
public string CustName { get; set; }
public int CustAge { get; set; }
public Order CustOrder { get; set; }
public Customer()
{
}
}
//Order details class
public partial class Order
{
public int Order_Id { get; set; }
public double Price { get; set; }
public Order()
{
}
}
}Imports Syncfusion.XlsIO
Imports System.Collections.Generic
Namespace ImportFromNestedCollection
Class Program
Private Shared Sub Main(ByVal args As String())
ExportData()
End Sub
'Main method to Export data from worksheet to nested class objects.
Private Shared Sub ExportData()
Using excelEngine As ExcelEngine = New ExcelEngine()
Dim application As IApplication = excelEngine.Excel
application.DefaultVersion = ExcelVersion.Excel2013
Dim workbook As IWorkbook = application.Workbooks.Open("Sample.xlsx")
Dim worksheet As IWorksheet = workbook.Worksheets(0)
'Map column headers in worksheet with class properties.
Dim mappingProperties As Dictionary(Of String, String) = New Dictionary(Of String, String)()
mappingProperties.Add("Customer ID", "CustId")
mappingProperties.Add("Customer Name", "CustName")
mappingProperties.Add("Customer Age", "CustAge")
mappingProperties.Add("Order ID", "CustOrder.Order_Id")
mappingProperties.Add("Order Price", "CustOrder.Price")
'Export worksheet data into nested class Objects.
Dim nestedClassObjects As List(Of Customer) = worksheet.ExportData(Of Customer)(1, 1, 10, 5, mappingProperties)
workbook.SaveAs("NestedClassObjects.xlsx")
End Using
End Sub
End Class
'Customer details class
Public Partial Class Customer
Public Property CustId As Integer
Public Property CustName As String
Public Property CustAge As Integer
Public Property CustOrder As Order
Public Sub New()
End Sub
End Class
'Order details class
Public Partial Class Order
Public Property Order_Id As Integer
Public Property Price As Double
Public Sub New()
End Sub
End Class
End NamespaceA complete working example to export data from Excel to nested class in C# is present on this GitHub page.