How to Read Excel File to DataTable using ClosedXML in C#

11 April 2023 | Viewed 3542 times

ClosedXML libraries used to work with Excel Files such as reading Excel data to DataTables and writing data to Excel files.

C# Code

/// <summary>
/// Reads Execl file to DataSet.
/// Each sheet will be loaded into seperate DataTable in DataSet.
/// Sheet Name will be used as DataTable Name.
/// </summary>
/// <param name="filePath">Excel file path</param>
/// <returns>Returns DataSet</returns>
public static DataSet ExcelToDataTable(string filePath)
{
DataSet dataSet = new DataSet();
using (XLWorkbook workBook = new XLWorkbook(filePath))
{
foreach (IXLWorksheet workSheet in workBook.Worksheets)
{
DataTable dt = new DataTable(worksheet.Name);

// Read First Row of Excel Sheet to add Columns to DataTable
workSheet.FirstRowUsed().CellsUsed().ToList()
.ForEach(x => { dt.Columns.Add(x.Value.ToString()); });

foreach (IXLRow row in workSheet.RowsUsed().Skip(1))
{
DataRow dr = dt.NewRow();
for (int i = 0; i < dt.Columns.Count; i++)
{
dr[i] = row.Cell(i + 1).Value.ToString();
}
dt.Rows.Add(dr);
}
dataSet.Tables.Add(dt);
}
}
return dataSet;
}


PreviousNext