CODEWITHSUNDEEP
.netclosedxml
kjana83
kjana83

Reputation: 398

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

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

Upvotes: 6

Views: 8264

Answers (3)

Adel Mourad
Adel Mourad

Reputation: 1547

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

Upvotes: 4

Oliver
Oliver

Reputation: 77

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.

Upvotes: 1

Francois Botha
Francois Botha

Reputation: 4839

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

Upvotes: 15

Related Questions