Read Excel worksheet into DataTable using ClosedXML

Question

I would like to read the contents of an Excel worksheet into a C# DataTable. The Excel worksheet could have a variable numbers of columns and rows. The first row in the Excel worksheet will always contain the column names but other rows may be blank.

All of the suggestions I have seen here in SO all assume the presence of Microsoft.ACE.OLEDB. I do not have this library installed on my system as when I try some of these solutions I get this error.

Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.

Strange considering I have Office 2016 installed.

For this reason I was hoping to use the ClosedXML library via Nuget but I do not see any examples in their wiki of reading an Excel worksheet to a DataTable in C#.

Answer 1

This is example is not mine. I cannot remember where I got it from as it was in my archives. However, this works for me. The only issue I ran into was with blank cells. According to a dicussion on the ClosedXML GitHUb wiki page it has something to do with Excel not tracking empty cells that are not bounded by data. I found that if I added data to the cells and then removed the same data the process worked.

public static DataTable ImportExceltoDatatable(string filePath, string sheetName)
{
  // Open the Excel file using ClosedXML.
  // Keep in mind the Excel file cannot be open when trying to read it
  using (XLWorkbook workBook = new XLWorkbook(filePath))
  {
    //Read the first Sheet from Excel file.
    IXLWorksheet workSheet = workBook.Worksheet(1);

    //Create a new DataTable.
    DataTable dt = new DataTable();

    //Loop through the Worksheet rows.
    bool firstRow = true;
    foreach (IXLRow row in workSheet.Rows())
    {
      //Use the first row to add columns to DataTable.
      if (firstRow)
      {
        foreach (IXLCell cell in row.Cells())
        {
          dt.Columns.Add(cell.Value.ToString());
        }
        firstRow = false;
      }
      else
      {
        //Add rows to DataTable.
        dt.Rows.Add();
        int i = 0;

        foreach (IXLCell cell in row.Cells(row.FirstCellUsed().Address.ColumnNumber, row.LastCellUsed().Address.ColumnNumber))
        {
          dt.Rows[dt.Rows.Count - 1][i] = cell.Value.ToString();
          i++;
        }
      }
    }

    return dt;
  }
}

Need to add

using System.Data;
using ClosedXML.Excel;

As well as the ClosedXML nuget package

For other datetime data type... this could be helpful... reference

if (cell.Address.ColumnLetter=="J") // Column with date datatype
 {
    DateTime dtime = DateTime.FromOADate(double.Parse(cell.Value.ToString()));
                     dt.Rows[dt.Rows.Count - 1][i] = dtime;
 }
 else
 {
      dt.Rows[dt.Rows.Count - 1][i] = cell.Value.ToString();
 }