How to import Excel file to DataTable in C# and VB.NET
EasyXLS™ library allows you to import Excel data to DataTable. The data can be imported from an Excel sheet or from the active Excel sheet. The entire sheet data or only data from ranges of cells can be imported.
EasyXLS can be successfully used to also import large Excel files having big volume of data with fast importing time.
The source code samples can be integrated in:
- ASP.NET web pages - Windows applications - Windows Forms (WinForms) - Console applications - Windows service applications - ASP.NET MVC web applications
EasyXLS permits you to import Excel files without Excel being installed, without OLEDB, without Interop or any other additional software installed.
Concept in action
The below example shows how to import Excel to DataTable in C# and VB.NET.
// Create an instance of the class that imports Excel files
ExcelDocument workbook = new ExcelDocument();
// Import Excel file to DataTable
DataSet ds = workbook.easy_ReadXLSXActiveSheet_AsDataSet("C:\\Samples\\Excel to DataTable.xlsx");
DataTable dataTable = ds.Tables;
// Display imported DataTable valuesfor (int row=0; row < dataTable.Rows.Count; row++)
for (int column=0; column < dataTable.Columns.Count; column++)
Console.WriteLine("At row " + (row + 1) + ", column " + (column + 1) + " the value is '" +
dataTable.Rows[row].ItemArray[column] + "'");
' Create an instance of the class that imports Excel filesDim workbook As New ExcelDocument
' Import Excel file to DataTableDim ds As DataSet = workbook.easy_ReadXLSXActiveSheet_AsDataSet("C:\Samples\Excel to DataTable.xlsx")
Dim dataTable As DataTable = ds.Tables(0)
' Display imported DataTable valuesFor row As Integer = 0 To dataTable.Rows.Count - 1
For column As Integer = 0 To dataTable.Columns.Count - 1
Console.WriteLine("At row " & (row + 1) & ", column " & (column + 1) & _
" the value is '" & dataTable.Rows(row).ItemArray(column) & "'")
EasyXLS enables you to import Excel data to DataTable either from the entire sheet or from a range of cells. Importing only a range of cells is a very useful option especially for large Excel files because it reduces the speed of the import process.
In order to import multiple cell ranges at once from Excel sheet, the range parameter must be passed to the method as union of ranges (multiple ranges separated by comma).
All the methods that allow importing Excel to DataSet have parameters that permit importing only ranges of cells.
Import XLSX, XLSB, XLSM and XLS files to DataTable