MS Excel spreadsheets are widely used to keep the small, medium, or large-sized data. In various cases, spreadsheets act as a database to store the application data. In such situations, you may need to read the data stored in the Excel files from within your web or desktop applications. For such cases, this article covers how to convert Excel data to DataTables in C#.
- C# API to Export Excel Data to DataTable
- Excel to DataTable in C#
- Convert Strongly-Typed Excel Data to DataTable
- Convert Non-Strongly Typed Excel Data to DataTable
- Get a Free License
C# Excel to DataTable Conversion API
Aspose.Cells for .NET is a class library that lets you implement Excel automation features within the .NET applications. In addition, the API allows you to export data from Excel worksheets to ADO.NET DataTable within a few steps. You can either download the API or install it via NuGet.
PM> Install-Package Aspose.Cells
Convert Excel to DataTable in C#
There are two scenarios when exporting data from Excel worksheets to DataTables: strongly typed data and non‑strongly typed data. Both can be handled with the API. Below is a guide for each case.
Convert Strongly-Typed Excel Data to DataTable in C#
Strongly typed data means each column contains values of a single data type. Follow these steps to export such data:
- Use Workbook class to load the Excel file.
- Get the worksheet you want to export in a Worksheet object.
- Call Worksheet.Cells.ExportDataTable(int, int, int, int, bool) to create a DataTable.
- Use the resulting DataTable as the data source.
The following code sample shows how to export Excel to DataTable.
{{ gist aspose-com-gists 550f80c22e22768be6c5418bbdd0c89b “export-excel-to-datatable.cs” }}
Convert Non-Strongly Typed Excel Data to DataTable in C#
When worksheet values are not strongly typed, use these steps:
- Use Workbook class to load the Excel file.
- Select the worksheet you want to export in a Worksheet object.
- Call Worksheet.Cells.ExportDataTableAsString(int, int, int, int, bool) to create a DataTable.
- Use the resulting DataTable as the data source.
The following code sample shows how to export non‑strongly typed data from Excel to DataTable.
{{ gist aspose-com-gists 550f80c22e22768be6c5418bbdd0c89b “export-excel-to-datatable-non-strongly.cs” }}
Get a Free License
You can get a free temporary license in order to try the API without evaluation limitations.
Conclusion
In this article, you learned how to export or convert Excel data to ADO.NET DataTables using C#. The step‑by‑step tutorial and code samples demonstrate handling both strongly typed and non‑strongly typed worksheet data. Explore more about the C# Excel API in the documentation.