Exporting data from an Excel (XLSX, XLS) file to a DataTable programmatically is an easy task. You can configure the export options as per your business requirements. In addition, you can visualize the populated DataTable to apply further operations. However, we will use this Excel C# API to access the WorkSheets and export cell data to DataTable programmatically. So, let’s learn how to convert Excel to DataTable in C#.
In this blog post, we will cover the following points:
Excel to Datatable C# API installation
This C# SpreadSheet library is quite straightforward to set up in your .NET application. There are two ways to install this API. You can either download the API package or install it via NuGet.
Install-Package Aspose.Cells
How to Convert Excel to DataTable in C# (Step-by-Step)
Once API is installed, you can start using the features of this enterprise-level library. There is a huge stack of methods exposed by this Excel C# API.
You may follow the following steps and the code snippet to convert Excel to DataTable in C#:
- Create an object of the Workbook class and load the source XLSX file.
- Access the Worksheet that you desire to export to Datatable.
- Invoke the ExportDataTable method to export the Excel sheet to the Datatable.
Copy & paste the following code into your main file:
// Convert Excel to Datatable in C# | |
string designerFile = "sample.xlsx"; | |
// Create an object of the Workbook class and load the source XLSX file | |
Workbook excel = new Workbook(designerFile); | |
// Access the Worksheet that you desire to export to Datatable | |
Worksheet sheet = excel.Worksheets[0]; | |
// Invoke the ExportDataTable method to export Excel sheet to the Datatable | |
DataTable dt = sheet.Cells.ExportDataTable(0, 0, 3, 2); | |
// Loop through the rows and print the results | |
foreach(DataRow dataRow in dt.Rows) | |
{ | |
foreach(var item in dataRow.ItemArray) | |
{ | |
Console.WriteLine(item); | |
} | |
} |
Export Excel data to Datatable as a string
In this section, we will explore this library a little more. However, you can use the following code snippet if the data in a column is not the same data type.
- Initialize an instance of the Workbook class and load the source XLSX file.
- Get the Worksheet that you want to export to Datatable.
- Call the method ExportDataTableAsString to export an Excel sheet to Datatable as a string.
Copy & paste the following code into your main file:
// Export Excel data to Datatable as a string | |
string designerFile = "sample.xlsx"; | |
// Initialize an instance of the Workbook class and load the source XLSX file | |
Workbook excel = new Workbook(designerFile); | |
// Get the Worksheet that you want to export to Datatable | |
Worksheet sheet = excel.Worksheets[0]; | |
// Call the method ExportDataTableAsString to export Excel sheet to Datatable as a string | |
DataTable dt = sheet.Cells.ExportDataTableAsString(0, 0, 3, 2,true); | |
// Loop through the rows and print the results | |
foreach(DataRow dataRow in dt.Rows) | |
{ | |
foreach(var item in dataRow.ItemArray) | |
{ | |
Console.WriteLine(item); | |
} | |
} |
Get a Free License
You can avail a free temporary license to try the API without evaluation limitations.
Summing up
As you have seen, this Excel C# API has offered a bunch of methods to export data from Excel to the DataTable programmatically. In addition, you have learned how to convert Excel to DataTable in C#. Further, you may visit the documentation to know the other methods of this .NET Excel library.
Moreover, we suggest you follow our Getting Started guide.
Finally, conholdate.com is consistently writing new blog posts. Therefore, please stay in touch for the latest updates.
Ask a question
You can let us know about your questions or queries on our forum.
FAQs
How do I convert Excel data to a table?
You can install this Excel C# API to convert data from an Excel sheet to a DataTable programmatically. In addition, you may visit this link to get the code snippet.
What is the best way to read an Excel file in C#?
Please visit this article, it will help you read and modify the Excel sheets programmatically.