How to read entire worksheet data in excel into DataTable using ClosedXml

Question

Excel Worksheet contains 60K records. Needs to be read and store into DataTable. Currently reading row by row. Is there any other better way using ClosedXml.

DataTable dt = new DataTable();
var wb = new XLWorkbook(fileName, XLEventTracking.Disabled);
using (wb)
{
    var ws = wb.Worksheet(1);
    using (ws)
    {                    
        var headerRow = ws.Row(3);
        int lastColumn = 32;
        foreach (var col in headerRow.Cells(true))
        {
            dt.Columns.Add(col.Value.ToString());
        }
        foreach (var row in ws.Rows().Skip(3))
        {
            var dr = dt.NewRow();
            for (int index = 0; index < lastColumn; index++)
            {
                dr[index] = row.Cell(index + 1).Value;
            }
            dt.Rows.Add(dr);
        }
    }
}

Algie

I could post it as an answer but I feel ashamed because this question is so beat up here on stack overflow. The bottom line is, if you use Microsoft.Ace.OleDb provider, you can work with Excel just like any Sql Database. You just do, dataAdapter.Fill(myTable) and you done. This is good example https://mcmap.net/q/1620584/-reading-an-excel-file-from-c – Stingy

Answer 1

You could use:

using (var wb = new XLWorkbook(fileName, XLEventTracking.Disabled))
{
    var ws = wb.Worksheet(1);
    DataTable dataTable = ws.RangeUsed().AsTable().AsNativeDataTable();
    /* Process data table as you wish */
}

Beaudette

Answer 2

The answers above did not work for me due to some exceptions thrown from "AsNativeDataTable();" while reading some cells that have invalid data or some issue.

I could not control nor manage/skip the exceptions thrown and the entire process is stopped.

The solution is here

public static DataTable ExcelToDatatable_ClosedXML(System.IO.FileStream fileStream, string SheetName)
{
    //Offical way: DataTable dataTable = ws.RangeUsed().AsTable().AsNativeDataTable(); 
    //But cant manage and fix exceptions inside there

    try
    {
        DataTable dt = new DataTable();

        using (XLWorkbook workBook = new XLWorkbook(fileStream))
        {
            //Read the first Sheet from Excel file.
            IXLWorksheet workSheet = workBook.Worksheets.Where(x => x.Name.ToLower() == SheetName.ToLower()).FirstOrDefault();

            //Consider the first row as container column names
            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())
                    {
                        string val = string.Empty;

                        try
                        {
                            val = cell.Value.ToString();
                        }
                        catch { }

                        dt.Rows[dt.Rows.Count - 1][i] = val;
                        i++;
                    }
                }
            }
        }

        return dt;
    }
    catch
    {
        return null;
    }
}

Usage

string filePath = HostingEnvironment.MapPath("~/Content/Storage/ProductsExcelImports/sample1.xlsx");
System.IO.FileStream fileStream = new System.IO.FileStream(filePath, System.IO.FileMode.Open);
var dt = ExcelToDatatable_ClosedXML(fileStream, "Sheet1");

Thanks

Ubangishari

Answer 3

What Francois suggested is not working for me. I had to do the following:

using (var wb = new XLWorkbook(fileName, XLEventTracking.Disabled))
{
    var dt = wb.Worksheet("worksheetName").Table(0).AsNativeDataTable();
    /* Process data table as you wish */
}

This is assuming that you only have one table in the worksheet.

Hinny