从XLSX中导出大量数据 - OutOfMemoryException（内存溢出异常）

Question

从XLSX中导出大量数据 - OutOfMemoryException（内存溢出异常）

.netopenxmlxlsxclosedxml

8

我即将要导出一个大量的数据（115,000 行 x 30 列）到 Excel OpenXML 格式（xlsx）。我正在使用一些库，如 DocumentFormat.OpenXML、ClosedXML 和 NPOI。

然而，使用每个库时都会抛出 OutOfMemoryException 异常，因为在内存中表示工作表会导致指数级的内存增加。

即使在每1000行关闭文档文件（并释放内存），下一次加载仍会导致内存增加。

有没有更高效的方法来导出 xlsx 数据而不占用太多内存？

- Gianluigi Liguori

可能是以 .xls 格式保存的 HTML 表格？ - Rubens Farias

不，我需要纯净的xlsx文件！ - Gianluigi Liguori

3个回答

1

Excel可以打开相当大的文件，只要您的计算机有足够的内存。这通常是限制因素...

99%的库都没有被构建来处理大数据集，如果您试图向它们投入过多的数据，您将遇到内存不足错误。

其中一些库，例如Spout，已经被创建来解决这个问题。诀窍是流式传输数据并避免在内存中存储东西。我不确定您正在使用哪种语言（似乎不是PHP），但您的语言中可能有类似的库。如果没有，您仍然可以查看Spout - 它是开源的 - 并将其转换为您的语言。

- Adrien

我同意你的观点，问题在于流数据或指向文件（避免在内存中加载完整的工作表表示）。你的Spout非常有趣，但不幸的是，我使用的语言是C#，移植对我来说太昂贵了。 - Gianluigi Liguori

@GianluigiLiguori - 或许有一种方法可以安装PHP并直接使用该库。 - miroxlav

显然，我正在寻找本地的.NET解决方案。 - Gianluigi Liguori

你试过这个吗：http://www.microsoft.com/en-us/download/details.aspx?id=5124？它是由微软开发的，似乎支持大型电子表格。 - Adrien

是的，它会导致相同的问题：ClosedXML本身在底层使用OpenXML。 - Gianluigi Liguori

-1

看起来你正在使用电子表格，而应该使用数据库。 它有其限制，这很容易成为其中之一。只有在您绝对需要坚持现有解决方案的情况下才继续阅读。但是，我不建议这样做。因为还有一个问题：如果Excel无法保存如此大的文件，它能否打开这样的文件？

因此，如果您无法切换到数据库平台，并且上述标准库在内部无法处理如此大量的数据，则创建大型XLSX时可能需要自己动手。我的意思是例如采用以下方法：

将您的数据分批（1,000或10,000或任何有效数字）导出到每个批次的单独文件中
创建一个工具（vb.net（这是最接近vba），c#，python，java，无论哪种语言都有可靠的XML库），将单独的文件合并为一个文件。它包括：
1. 从XLSX中提取XML（通常为file.xlsx\xl\worksheets\sheet1.xml和file.xlsx\xl\worksheets\sharedStrings.xml）
2. 通过XML操作库将这些部分粘合在一起（这不应该因为您不再使用复杂的电子表格对象而崩溃OutOfMemoryException）
3. 将结果文件重新打包回主XLSX（您可以将第一个批处理输出文件作为主XLSX）

我已经向您展示了实现结果的可能方法，但我会避免这样做。 Excel从来不是存储大量数据的平台。与上述任务相比，说服管理层改变该领域的工具/流程可能更容易些。

- miroxlav

这个答案似乎成为了踩的宠物。 - miroxlav

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- petelids · Accepted Answer

OpenXML SDK是处理此任务的正确工具，但您需要小心使用SAX（用于XML的简单API）方法，而不是DOM方法。从SAX的维基百科文章中得知：

“DOM操作整个文档，而SAX解析器按顺序处理XML文档的每个部分。”

这大大减少了处理大型Excel文件时消耗的内存量。

这里有一篇很好的文章 - http://polymathprogrammer.com/2012/08/06/how-to-properly-use-openxmlwriter-to-write-large-excel-files/ 从那篇文章中改编出一个示例，输出了115k行和30列。

public static void LargeExport(string filename)
{
    using (SpreadsheetDocument document = SpreadsheetDocument.Create(filename, SpreadsheetDocumentType.Workbook))
    {
        //this list of attributes will be used when writing a start element
        List<OpenXmlAttribute> attributes;
        OpenXmlWriter writer;

        document.AddWorkbookPart();
        WorksheetPart workSheetPart = document.WorkbookPart.AddNewPart<WorksheetPart>();

        writer = OpenXmlWriter.Create(workSheetPart);            
        writer.WriteStartElement(new Worksheet());
        writer.WriteStartElement(new SheetData());

        for (int rowNum = 1; rowNum <= 115000; ++rowNum)
        {
            //create a new list of attributes
            attributes = new List<OpenXmlAttribute>();
            // add the row index attribute to the list
            attributes.Add(new OpenXmlAttribute("r", null, rowNum.ToString()));

            //write the row start element with the row index attribute
            writer.WriteStartElement(new Row(), attributes);

            for (int columnNum = 1; columnNum <= 30; ++columnNum)
            {
                //reset the list of attributes
                attributes = new List<OpenXmlAttribute>();
                // add data type attribute - in this case inline string (you might want to look at the shared strings table)
                attributes.Add(new OpenXmlAttribute("t", null, "str"));
                //add the cell reference attribute
                attributes.Add(new OpenXmlAttribute("r", "", string.Format("{0}{1}", GetColumnName(columnNum), rowNum)));

                //write the cell start element with the type and reference attributes
                writer.WriteStartElement(new Cell(), attributes);
                //write the cell value
                writer.WriteElement(new CellValue(string.Format("This is Row {0}, Cell {1}", rowNum, columnNum)));

                // write the end cell element
                writer.WriteEndElement();
            }

            // write the end row element
            writer.WriteEndElement();
        }

        // write the end SheetData element
        writer.WriteEndElement();
        // write the end Worksheet element
        writer.WriteEndElement();
        writer.Close();

        writer = OpenXmlWriter.Create(document.WorkbookPart);
        writer.WriteStartElement(new Workbook());
        writer.WriteStartElement(new Sheets());

        writer.WriteElement(new Sheet()
        {
            Name = "Large Sheet",
            SheetId = 1,
            Id = document.WorkbookPart.GetIdOfPart(workSheetPart)
        });

        // End Sheets
        writer.WriteEndElement();
        // End Workbook
        writer.WriteEndElement();

        writer.Close();

        document.Close();
    }
}

//A simple helper to get the column name from the column index. This is not well tested!
private static string GetColumnName(int columnIndex)
{
    int dividend = columnIndex;
    string columnName = String.Empty;
    int modifier;

    while (dividend > 0)
    {
        modifier = (dividend - 1) % 26;
        columnName = Convert.ToChar(65 + modifier).ToString() + columnName;
        dividend = (int)((dividend - modifier) / 26);
    }

    return columnName;
}