How to Export Data from Excel to DataTable with Customization in C#

How to Export Data from Excel to DataTable with Customization in C#

Exporting data from Excel to other formats lets users visualize the data according to their requirement. Syncfusion Excel Library (XlsIO) provides support to export worksheet data to data tables, collection objects, and nested class objects for handling the data efficiently.

Exporting data from Excel to other formats lets users visualize the data according to their requirement. Syncfusion Excel Library (XlsIO) provides support to export worksheet data to data tablescollection objects, and nested class objects for handling the data efficiently.

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

Getting started

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

Create a New Console Application

*Step 2: *Then, install the Syncfusion.XlsIO.WinForms NuGet package as a reference to the application from NuGet.org.

Install NuGet Package

Step 3: Now, include the following namespaces in the Program.cs file.

Export to DataTable

The project is ready! Let’s add the code that exports data from Excel to a DataTable. The ExportDataTable() 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.

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.
  • 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.

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

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 will be triggered when exporting data from each cell. This event helps in performing the following actions using the 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.

c# excel file formats xlsio

What is Geek Coin

What is GeekCash, Geek Token

Best Visual Studio Code Themes of 2021

Bootstrap 5 Tutorial - Bootstrap 5 Crash Course for Beginners

Nest.JS Tutorial for Beginners

Hello Vue 3: A First Look at Vue 3 and the Composition API

4 Easy Steps to Export Excel Files to JSON Using C#

In this blog, we are going to see how an Excel file can be converted to a JSON stream in C# using the Syncfusion XlsIO library.

Data Validation in Excel

What is Data Validation in Excel. Data validation is a feature in Excel used to control what a user can enter into a cell. Learn more about it with Examples.

How to use C Header Files to separate a program into multiple files

C Header Files. How to use C Header Files to separate a program into multiple files

Dicey Issues in C/C++

C/C++ problems. If you are familiar with C/C++then you must have come across some unusual things and if you haven’t, then you are about to. The below codes are checked twice before adding, so feel free to share this article with your friends.

C++ File Handling Example | File Handling In C++ Tutorial

C++ File Handling Example | File Handling In C++ Tutorial. File handling operations include create a file, open a file, read, write, and delete a file.