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

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 */
}