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 could be two possible scenarios when exporting data from Excel worksheets to DataTables: the data could be strongly typed or non-strongly typed. In both cases, you can perform Excel to DataTable conversion accordingly. Let’s have a look at how to deal with either of the above-mentioned scenarios.
Convert Strongly-Typed Excel Data to DataTable in C#
Strongly typed data means the values in a single column belong to a particular data type. For such cases, you can export Excel data to DataTable in C# using the following steps.
- Use Workbook class to load the Excel file.
- Get the worksheet you want to export in a Worksheet object.
- Use Worksheet.Cells.ExportDataTable(int, int, int, int, bool) method to export data to a DataTable object.
- Use DataTable as the data source.
The following code sample shows how to export Excel to DataTable.
Convert Non-Strongly Typed Excel Data to DataTable in C#
Now, let’s have a look at the other case when values in the worksheet are not strongly typed. It means they do not belong to a particular data type. The following are the steps to export Excel data to DataTable in this case.
- Use Workbook class to load the Excel file.
- Select the worksheet you want to export in a Worksheet object.
- Use Worksheet.Cells.ExportDataTableAsString(int, int, int, int, bool) method to export data to a DataTable object.
- Use DataTable as the data source.
The following code sample shows how to export non-strongly typed data from Excel to DataTable in C#.
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 have learned how to export or convert Excel data to ADO.NET DataTables using C#. The step-by-step tutorial and code samples have shown how to deal with strongly typed and non-strongly typed data in the worksheets. You can explore more about the C# Excel API using documentation.