Как прочитать все данные рабочего листа в Excel в DataTable с помощью ClosedXml

Рабочий лист Excel содержит 60 тыс. записей. Необходимо прочитать и сохранить в DataTable. Сейчас читаю построчно. Есть ли другой лучший способ использования 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);
        }
    }
}

.net closedxml
person kjana83    schedule 05.08.2017    source источник
comment
Я мог бы опубликовать это как ответ, но мне стыдно, потому что этот вопрос так избит здесь из-за переполнения стека. Суть в том, что если вы используете провайдера Microsoft.Ace.OleDb, вы можете работать с Excel так же, как с любой базой данных Sql. Вы просто делаете, dataAdapter.Fill(myTable) и готово. Это хороший пример stackoverflow.com/a/7246529/1704458.   -  person T.S.    schedule 05.08.2017

Ответы (3)


arrow_upward
12
arrow_downward

Вы можете использовать:

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 */
}
person Francois Botha    schedule 03.11.2018

arrow_upward
3
arrow_downward

Приведенные выше ответы не сработали для меня из-за некоторых исключений, вызванных AsNativeDataTable(); при чтении некоторых ячеек с неверными данными или какой-либо проблемой.

Я не мог ни контролировать, ни управлять/пропускать исключения, и весь процесс останавливался.

Решение здесь

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

Использование

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

Спасибо

person Adel Mourad    schedule 21.10.2020

arrow_upward
1
arrow_downward

То, что предложил Франсуа, мне не подходит. Мне пришлось сделать следующее:

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

Предполагается, что у вас есть только одна таблица на листе.

person Oliver    schedule 30.07.2020