Export from Excel Document

14 Oct 202424 minutes to read

Excel to DataTable

XlsIO allows to export the sheet data to a DataTable by using the ExportDataTable() method. This method provides various options that allows to export data with specific requirement 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 on how to export data from Excel to Data grid using DataTable.

C# [Cross-platform]

using (ExcelEngine excelEngine = new ExcelEngine())
{
	IApplication application = excelEngine.Excel;
	application.DefaultVersion = ExcelVersion.Xlsx;
	FileStream inputStream = new FileStream(Path.GetFullPath(@"Data/InputTemplate.xlsx"), FileMode.Open, FileAccess.Read);
	IWorkbook workbook = application.Workbooks.Open(inputStream);
	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);                

	//Dispose streams
	inputStream.Dispose();
}

//XlsIO supports binding of exported data table to data grid in Windows Forms, WPF, ASP.NET and ASP.NET MVC platforms alone.

C# [Windows-specific]

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);

  //Binding exported DataTable to data grid, likewise it can binded to any 
  //user interface control which supports binding
  DataGrid dataGrid = new DataGrid();
  dataGrid.DataSource = customersTable;

  workbook.SaveAs("ExportToGrid.xlsx");
}

VB.NET [Windows-specific]

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)

  'Binding exported DataTable to data grid, likewise it can binded to any 
  'user interface control which supports binding
  Dim dataGrid As DataGrid = New DataGrid
  dataGrid.DataSource = customersTable

  workbook.SaveAs("ExportToGrid.xlsx")
End Using

A complete working example to export data from Excel to DataTable in C# is present on this GitHub page.

Excel to Data Table with an Event

Sometimes there may be a need to control the data export from Excel to a data table. XlsIO provides an event ExportDataTableEvent to trigger while exporting data from an Excel worksheet to a data table. This event helps to perform the following actions through the ExportDataTableActions enumeration.

• Default - Exports worksheet data to the data table without any action.
• SkipRows - Exports worksheet data to the data table by skipping a specific row(s).
• StopExporting - Stops exporting the data from 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 to a data table by triggering an event.

C# [Cross-platform]

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2016;
  FileStream inputStream = new FileStream("Sample.xlsx", FileMode.Open, FileAccess.Read);
  IWorkbook workbook = application.Workbooks.Open(inputStream);
  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 as stream
  FileStream stream = new FileStream("ExportToDT.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(stream);
  stream.Dispose();
}

//XlsIO supports binding of exported data table to data grid in Windows Forms, WPF, ASP.NET and ASP.NET MVC platforms alone

C# [Windows-specific]

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);

  //Binding 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");
}

VB.NET [Windows-specific]

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)

  'Binding 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 Using

The following code is the event handler for the above code.

C# [Cross-platform]

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;
}

C# [Windows-specific]

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;
}

VB.NET [Windows-specific]

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 Sub

Excel to Collection Objects 

XlsIO allows to export the sheet data to a Collection Objects by using the ExportData<T>() method.

The following code example illustrates on how to export Excel data into Collection Objects using ExportData<T>.

C# [Cross-platform]

using (ExcelEngine excelEngine = new ExcelEngine())
{
	IApplication application = excelEngine.Excel;
	application.DefaultVersion = ExcelVersion.Xlsx;
	FileStream inputStream = new FileStream(Path.GetFullPath(@"Data/InputTemplate.xlsx"), FileMode.Open, FileAccess.Read);
	IWorkbook workbook = application.Workbooks.Open(inputStream);
	IWorksheet worksheet = workbook.Worksheets[0];

	//Export worksheet data into Collection Objects
	List<Report> collectionObjects = worksheet.ExportData<Report>(1, 1, 10, 3);

	//Dispose streams
	inputStream.Dispose();
}

C# [Windows-specific]

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");
}

VB.NET [Windows-specific]

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 Using

The following code snippet provides supporting class for the above code. Here, the attributes DisplayNameAttribute and Bindable are used.

• DisplayNameAttribute - to match the column headers with set of properties while exporting.
• BindableAttribute - to skip a property while exporting.

C# [Cross-platform]

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()
  {

  }
}

C# [Windows-specific]

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()
  {

  }
}

VB.NET [Windows-specific]

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 Class

A 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 to export worksheet data to nested class objects. A new overload to the existing [ExportData()](https://help.syncfusion.com/cr/document-processing/Syncfusion.XlsIO.Implementation.WorksheetImpl.html#Syncfusion_XlsIO_Implementation_WorksheetImpl_ExportData__1_System_Int32_System_Int32_System_Int32_System_Int32_System_Collections_Generic_Dictionary_System_String_System_String__) method helps to achieve this requirement by mapping column headers with class properties.

Let’s consider the input Excel document has the data as shown in the below screenshot.

The following code example illustrates how to export data from Excel to nested class objects with column headers mapping collection.

C# [Cross-platform]

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;
                FileStream inputStream = new FileStream(Path.GetFullPath(@"Data/InputTemplate.xlsx"), FileMode.Open, FileAccess.Read);
                IWorkbook workbook = application.Workbooks.Open(inputStream);
                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);

                //Dispose streams
                inputStream.Dispose();
            }
        }
    }
    //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()
        {

        }
    }
}

C# [Windows-specific]

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()
    {

    }
  }
}

VB.NET [Windows-specific]

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 Namespace

A complete working example to export data from Excel to nested class in C# is present on this GitHub page.