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();
 }

Answer 2

With this code you can read the contents of an excel sheet. You can specify the name of the sheet or the number, a dataSet will be returned with the contents of the sheet.

public static DataTable GetDataFromExcel(string path, dynamic worksheet)
        {
            //Save the uploaded Excel file.


            DataTable dt = new DataTable();
            //Open the Excel file using ClosedXML.
            using (XLWorkbook workBook = new XLWorkbook(path))
            {
                //Read the first Sheet from Excel file.
                IXLWorksheet workSheet = workBook.Worksheet(worksheet);

                //Create a 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())
                        {
                            if (!string.IsNullOrEmpty(cell.Value.ToString()))
                            {
                                dt.Columns.Add(cell.Value.ToString());
                            }
                            else
                            {
                                break;
                            }
                        }
                        firstRow = false;
                    }
                    else
                    {
                        int i = 0;
                        DataRow toInsert = dt.NewRow();
                        foreach (IXLCell cell in row.Cells(1, dt.Columns.Count))
                        {
                            try
                            {
                                toInsert[i] = cell.Value.ToString();
                            }
                            catch (Exception ex)
                            {

                            }
                            i++;
                        }
                        dt.Rows.Add(toInsert);
                    }
                }
                return dt;
            }