How to Export Data from Excel to DataTable with Customization in C#
Tags: csharp, dotnet, excel, productivity
Published: 2021-06-02 10:44:37

---
title: How to Export Data from Excel to DataTable with Customization in C#
published: true
date: 2021-05-26 10:00:21 UTC
tags: csharp, dotnet, excel, productivity
canonical_url: https://www.syncfusion.com/blogs/post/export-data-from-excel-to-datatable-with-customization-in-csharp.aspx
cover_image: https://www.syncfusion.com/blogs/wp-content/uploads/2021/05/Export-Data-from-Excel-to-Data-Tables-with-Customization-in-C-672x372.png
---

Exporting data from Excel to other formats lets users visualize the data according to their requirement. Syncfusion [Excel Library](https://www.syncfusion.com/excel-framework/net "Link to Excel Library") (XlsIO) provides support to export worksheet data to [data tables](https://help.syncfusion.com/file-formats/xlsio/working-with-data#exporting-from-worksheet-to-data-table "Link to Exporting from Worksheet to Data Table documentation"), [collection objects](https://help.syncfusion.com/file-formats/xlsio/working-with-data#exporting-from-worksheet-to-collection-objects "Link to Exporting from Worksheet to Collection Objects documentation"), and [nested class objects](https://help.syncfusion.com/file-formats/xlsio/working-with-data#export-data-from-excel-to-nested-class-objects "Link to Export data from Excel to Nested Class Objects documentation") for handling the data efficiently.

In this blog, you will learn the steps to export data from:

- [Excel to DataTable](#export-to-data-table).
- [Excel to DataTable with customization](#export-to-data-table--with-customization).

## Getting started

**Step 1:** Create a new C# console application in Visual Studio.

![Create a New Console Application](https://www.syncfusion.com/blogs/wp-content/uploads/2021/05/Create-a-New-Console-Application.png)<figcaption>Create a New Console Application</figcaption>

**Step 2:** Then, install the [Syncfusion.XlsIO.WinForms](https://www.nuget.org/packages/Syncfusion.XlsIO.WinForms/ "Link to Syncfusion.XlsIO.WinForms NuGet package") NuGet package as a reference to the application from [NuGet.org](https://www.nuget.org/ "Link to NuGet Gallery").

![Install NuGet Package](https://www.syncfusion.com/blogs/wp-content/uploads/2021/05/Install-NuGet-Package.png)<figcaption>Install NuGet Package</figcaption>

**Step 3:**  Now, include the following namespaces in the _Program.cs_ file.

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

## Export to DataTable

The project is ready! Let’s add the code that exports data from Excel to a DataTable. The [ExportDataTable()](https://help.syncfusion.com/cr/file-formats/Syncfusion.XlsIO.IWorksheet.html#Syncfusion_XlsIO_IWorksheet_ExportDataTable_Syncfusion_XlsIO_IRange_Syncfusion_XlsIO_ExcelExportDataTableOptions_ "Link to ExportDataTable(IRange, ExcelExportDataTableOptions) method of Excel library") method in XlsIO allows you to do this:

**Step 1:** Open or create an Excel spreadsheet using the Excel engine in XlsIO. This is the source spreadsheet from which we are going to export data to a DataTable. Here, we are opening an existing Excel document with data.

```csharp
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;

FileStream inputStream = new FileStream("Sample.xlsx", FileMode.Open, FileAccess.Read);
IWorkbook workbook = application.Workbooks.Open(inputStream);

```

**Step 2:**  Then, add the code to export the data from Excel to a DataTable. There are two overloads to perform this operation:

- **ExportDataTable (IRange dataRange, ExcelExportDataTableOptions options)**: This overload allows you to specify the range of data to be exported along with the export options. The following code example exports data from the specified range to the DataTable.
```csharp
//Export data from the specified range to the DataTable.
DataTable customersTable = worksheet.ExportDataTable(worksheet.UsedRange, ExcelExportDataTableOptions.ColumnNames);

```
- **ExportDataTable (int firstRow, int firstColumn, int maxRows, int maxColumns, ExcelExportDataTableOptions options)**: This overload allows you to specify the rows and columns of the data to be exported along with the export options.  We should define the start and end rows and columns in this method.
```csharp
//Export data from the first row and first column, up to the 10th row and 10th column of the Excel worksheet to the data table.
DataTable customersTable = worksheet.ExportDataTable(1, 1, 10, 10, ExcelExportDataTableOptions.ColumnNames);

```

The following complete code snippet illustrates how to export data from Excel to a DataTable.

```csharp
static void Main(string[] args)
{
    using (ExcelEngine excelEngine = new ExcelEngine())
    {
        IApplication application = excelEngine.Excel;
        application.DefaultVersion = ExcelVersion.Xlsx;
        FileStream inputStream = new FileStream("Sample.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);

        //Save the workbook as stream.
        FileStream stream = new FileStream("ExportToDT.xlsx", FileMode.Create, FileAccess.ReadWrite);
        workbook.SaveAs(stream);
        stream.Dispose();
    }
}

```

## Export to DataTable with customization

So, we have learned to export the data from Excel to a DataTable. Now, let’s focus on customizing the data during the export. The following are different customization options available when exporting the data to a DataTable:

- Modify the value of a cell in the DataTable.
- Skip specific rows.
- Stop the data export.

The modifications made will only be reflected in the DataTable, and the data in the Excel spreadsheet will remain the same.

The [ExportDataTableEvent](https://help.syncfusion.com/cr/file-formats/Syncfusion.XlsIO.Implementation.WorksheetImpl.ExportDataTableEventHandler.html "Link to Delegate WorksheetImpl.ExportDataTableEventHandler") will be triggered when exporting data from each cell. This event helps in performing the following actions using the [ExportDataTableActions](https://help.syncfusion.com/cr/file-formats/Syncfusion.XlsIO.ExportDataTableActions.html "Link to Enum ExportDataTableActions") enumeration:

- Default: Exports worksheet data to the DataTable without any action.
- SkipRows: Exports worksheet data to the DataTable by skipping specific rows.
- StopExporting: Stops exporting the data from the Excel worksheet to the DataTable.

The following steps show how to export data from an Excel worksheet to a DataTable with customization:

**Step 1:** Open or create an Excel document using the Excel engine in XlsIO. Here, we are opening an existing Excel document with data.

**Note:** Follow the [Getting Started](#getting-started) section to create a project with the [NuGet packages](https://help.syncfusion.com/file-formats/xlsio/nuget-packages-required "Link to NuGet Packages Required").

```csharp
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;

FileStream inputStream = new FileStream("Sample.xlsx", FileMode.Open, FileAccess.Read);
IWorkbook workbook = application.Workbooks.Open(inputStream);

```

**Step 2:** Then, create an event handler method in the class.

```csharp
private void ExportDataTable_EventAction(ExportDataTableEventArgs e)
{
   //Event handler code should be placed here.
}

```

**Step 3:** Now, bind the event handler with [_ExportDataTableEvent_](https://help.syncfusion.com/cr/file-formats/Syncfusion.XlsIO.Implementation.WorksheetImpl.ExportDataTableEventHandler.html "Link to Delegate WorksheetImpl.ExportDataTableEventHandler") in [IWorksheet](https://help.syncfusion.com/cr/file-formats/Syncfusion.XlsIO.IWorksheet.html "Link to Interface IWorksheet").

```csharp
//Event to choose an action while exporting data from Excel to DataTable.
//The event will be triggered at this line while exporting data from each cell in the Excel worksheet.
worksheet.ExportDataTableEvent += ExportDataTable_EventAction();

```

**Step 4:** Finally, export worksheet data using the **ExportDataTable** method.

```csharp
//Read data from the worksheet and exports to the DataTable.
DataTable customersTable = worksheet.ExportDataTable(worksheet.UsedRange, ExcelExportDataTableOptions.ColumnNames);

```

The following is the complete code example of how to export data from an Excel worksheet to a DataTable by triggering an event to customize the export.

```csharp
static void Main(string[] args)
{
    using (ExcelEngine excelEngine = new ExcelEngine())
    {
        IApplication application = excelEngine.Excel;
        application.DefaultVersion = ExcelVersion.Xlsx;
        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 DataTable.
        //The event will be triggered at this line while exporting data from each cell in the Excel worksheet.
        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();
    }
}

```

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

```csharp
private void ExportDataTable_EventAction(ExportDataTableEventArgs e)
{
    if (e.ExcelValue != null && e.ExcelValue.ToString() == "Owner")
    {
        //Skips the row to export into the DataTable if the Excel cell value is “Owner”.
        e.ExportDataTableAction = ExportDataTableActions.SkipRow;    
    }
    else if (e.DataTableColumnIndex == 0 && e.ExcelRowIndex == 5 && e.ExcelColumnIndex == 1)
    {
        //Stops the export based on the condition.
        e.ExportDataTableAction = ExportDataTableActions.StopExporting;   
    }
    else if (e.ExcelValue != null && e.ExcelValue.ToString() == "Mexico D.F.")
    {
        //Replaces the cell value in the DataTable without affecting the Excel document.
        e.DataTableValue = "Mexico";     
    }
}

```

## GitHub samples

For more information, refer to the [export data from Excel to a DataTable with customization](https://github.com/SyncfusionExamples/export-data-from-excel-to-datatable-with-customization "Link to export data from Excel to a data table with customization demo on GitHub") demo project.

## Conclusion

As you can see, the Syncfusion [Excel Library](https://www.syncfusion.com/excel-framework/net "Link to Excel Library") (XlsIO) provides support to export data from Excel to DataTable with customization options. Take a moment to peruse the [documentation](https://help.syncfusion.com/file-formats/xlsio/working-with-data#importing-data-to-worksheets "Link to Importing Data to Worksheets documentation"), where you’ll find other options and features, all with accompanying code samples. Using the library, you can also export Excel data to [PDF](https://help.syncfusion.com/file-formats/xlsio/excel-to-pdf-conversion "Link to Excel to PDF Conversion"), [image](https://help.syncfusion.com/file-formats/xlsio/worksheet-to-image-conversion "Link to Worksheet to Image conversion"), [CSV](https://www.syncfusion.com/kb/9092/convert-excel-file-to-csv-in-c-vb-net "Link to Convert Excel file to CSV in C#, VB.NET"), [TSV](https://www.syncfusion.com/kb/8556/does-xlsio-support-tsv-files "Link to Does XlsIO support TSV files?"), [HTML](https://help.syncfusion.com/file-formats/xlsio/working-with-excel-worksheet#save-worksheet-as-html "Link to Save Worksheet as HTML"), [collections of objects](https://help.syncfusion.com/file-formats/xlsio/working-with-data#exporting-from-worksheet-to-clr-objects "Link to working with data"), [ODS](https://help.syncfusion.com/file-formats/xlsio/excel-to-ods-conversion "Link to Excel to ODS Conversion") and [JSON](https://help.syncfusion.com/file-formats/xlsio/excel-to-json-conversion "Link to Excel to JSON Conversion") file formats, and more.

If you are new to our Excel Library, it is highly recommended that you follow our [Getting Started guide](https://help.syncfusion.com/file-formats/xlsio/getting-started "Link to Getting Started - Create Excel File in C# and VB.NET documentation").

Are you already a Syncfusion user? You can download the [product setup](https://www.syncfusion.com/account/downloads/studio/ "Link to license and download page of Essential Studio"). If you’re not yet a Syncfusion user, you can download a [free, 30-day trial](https://www.syncfusion.com/downloads/fileformats/confirm "Link to the free evaluation of the Syncfusion Essential Studio").

If you have any questions about these features, please let us know in the comments below. You can also contact us through our [support forum](https://www.syncfusion.com/forums "Link to the Syncfusion support forum"), [Direct-Trac](https://www.syncfusion.com/support/directtrac "Link to the Syncfusion support system Direct Trac"), or [feedback portal](https://www.syncfusion.com/feedback/ "Link to Syncfusion Feedback Portal"). We are happy to assist you!

## Related blogs

1. [6 Easy Ways to Export Data to Excel in C#](https://www.syncfusion.com/blogs/post/6-easy-ways-to-export-data-to-excel-in-c-sharp.aspx "Link to 6 Easy Ways to Export Data to Excel in C# blog")
2. [How to Export Data from SQL Server to Excel Table in C#](https://dev.to/syncfusion/how-to-export-data-from-sql-server-to-excel-table-in-c-fo7 "Link to How to Export Data from SQL Server to Excel Table in C# blog")
3. [Export Data from Collection to Excel and Group It in C# ](https://dev.to/karthickramasamy08/export-data-from-collection-to-excel-and-group-it-in-c-1j65-temp-slug-6071060 "Link to Export Data from Collection to Excel and Group It in C# blog")
4. [Export Data to a Predefined Excel Template in C#](https://dev.to/sureshmohan/export-data-to-a-predefined-excel-template-in-c-dp6 "Link to Export Data to a Predefined Excel Template in C# blog")
5. [Easy Steps to Export HTML Tables to an Excel Worksheet in C#](https://dev.to/syncfusion/easy-steps-to-export-html-tables-to-an-excel-worksheet-in-c-4je1 "Link to Easy Steps to Export HTML Tables to an Excel Worksheet in C# blog")