使用大型数据表和ClosedXML的OutOfMemory错误 [英] OutOfMemory errors using large datatables with ClosedXML

问题描述

在添加大型数据表时创建工作表时遇到问题。我摆脱了内存错误。我按照建议关闭了事件跟踪以提高性能和内存消耗，但是我仍然遇到这些错误。

I have an issue creating worksheets when adding large DataTables. I am getting out of memory errors. I turned off the event tracking as suggested to improve performance and memory consumption but I am still experiencing these errors.

这里有一些示例代码：

public void SaveWorkBook(string xlFileName, DataSet dataSet)
{
    using (var xlWorkBook = new XLWorkbook(XLEventTracking.Disabled))
    {
        xlWorkBook.AddWorksheet(dataSet);
        xlWorkBook.SaveAs(xlFileName);
    }
}

public void SaveWorkBook(string xlFileName, params DataTable[] dataTables)
{           
    using (var xlWorkBook = new XLWorkbook(XLEventTracking.Disabled))
    {
        foreach (var dataTable in dataTables) xlWorkBook.AddWorksheet(dataTable);
        xlWorkBook.SaveAs(xlFileName);
    }
}

当然，问题是当DataTables很大时。像成千上万条记录或更多。我尝试对大型数据表使用另一种方法，例如使用记录数较小的临时表，然后将它们刷新到工作表，清除数据表，然后重新填充新数据。填充，冲洗，清除，重复。或 InsertData method（）

The issue of course is when the DataTables are huge. Like thousands of records or more. I tried using an alternative method to large data tables like using a temporary one with smaller records counts, then flushing them to the Worksheet, clearing the data table then refilling it with new data. Fill, flush, clear, repeat. That or the InsertData method()

问题是，每次我尝试使用选项可用，只有第一列被写入。然后降低文件的大小，我看到的是其他数据列，但是在工作表的第一个单元格中，而不是像预期的那样将这些列跨成一行。且仅适用于1行。可能是写的最后一条记录。

The issue is that every time I try to write the data table using the options available, only the first column is getting written. Then lower down the file I am seeing the other columns of data, but in the first cell of the worksheet instead of spanning the columns in a single row as they are suppose to. And only for 1 row. Probably for the last record that was written.

有人可以帮我理解它的工作原理吗？

Can someone help me understand how this works?

这里是使用 InsertData 方法的代码段。我使用dataTable作为模板创建工作表，因为它定义了所有列。然后我得到该工作表并尝试将其添加到其中。

Here is a code snippet using the InsertData method. I create the worksheet using a dataTable as a template since it has all the columns defined. Then I get that worksheet and try to add to it.

public void SaveWorkBook(string xlFileName, DataTable dataTable, string dataFileName, char delimitor)
{
    using (var xlWorkBook = new XLWorkbook(XLEventTracking.Disabled))
    {
        xlWorkBook.AddWorksheet(dataTable);

        var workSheet = xlWorkBook.Worksheets.First(ws => ws.Name == dataTable.TableName);                    

        using (var sr = new StreamReader(dataFileName))
        {
            var rowIndex = 1;

            sr.ReadLine();

            while (!sr.EndOfStream)
            {
                var line = sr.ReadLine() + string.Empty;
                workSheet.Cell(++rowIndex, 1).InsertData(line.Split(delimitor));
            }
        }

        xlWorkBook.SaveAs(xlFileName);
    }
}

您可以看到我正在使用 StreamReader 可以在我之前创建的CSV文件中一次读取1行。将整个文件加载到datable中会导致内存不足错误。因此，我一次读取了文件1行，并在创建工作表后尝试将其添加。

As you can see I am using a StreamReader to read 1 line at a time of the CSV file I created earlier. Loading the entire file into the datable causes out of memory errors. So I read the file 1 line at a time and try to add it after creating the worksheet.

有人可以对此发表一些见识吗？

Can anyone share some insight into this?

谢谢

推荐答案

这是ClosedXML中的一个已知问题。当前没有解决方案。在 https://github.com/ClosedXML/ClosedXML/issues/264

This is a known issue in ClosedXML. There is currently no solution. See the issue at https://github.com/ClosedXML/ClosedXML/issues/264

这篇关于使用大型数据表和ClosedXML的OutOfMemory错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！