In this tutorial, we will go through the steps to learn how to convert Excel to DataTable in C#. It is quite a handy feature that is using C# read Excel file into DataTable and then use it behind some gridview for display or use it for inserting data into a database. Following are the steps to import Excel to DataTable in C# that is finally saved as XLSX.

Steps to Convert Excel to DataTable in C#

1. Add Aspose.Cells for .NET library to the project using NuGet package manager
2. Add the reference to System, System.Data and Aspose.Cells namespaces
3. Instantiate an empty Workbook object
4. Fill the first sheet with sample data that is to be exported to datatable
5. Export the contents of the filled worksheet to datatable
6. For verification, retrieve data in datatable by fetching each row in the datatable
7. Display all the fetched rows and columns in the datatable

In this process, we initialize an empty workbook and then get the reference to its first worksheet. For better understanding, sample data is filled into this worksheet that is later converted to DataTable. Once data is filled, we use WorkSheet.Cells.ExportDataTable function to create a DataTable. This function supports selecting data from any area in the worksheet along with the provision to import column headers also. After creating this DataTable, we verify its contents by displaying all the columns in each row.

Code to Import Excel to DataTable in C#

In this process, neither interop nor MS Excel is used. You can convert Excel to other formats also as described in how to export large Excel file to CSV in C#.