.net - Exportar gran cantidad de datos desde XLSX-OutOfMemoryException

openxml closedxml

Question

Me estoy acercando a exportar una gran cantidad de datos (115,000 filas x 30 columnas) en formato Excel OpenXML (xlsx). Estoy usando algunas bibliotecas como DocumentFormat.OpenXML, ClosedXML, NPOI.

Con cada uno de estos, se produce OutOfMemoryException porque la representación de la hoja en la memoria provoca un aumento exponencial de la memoria.

También cerrando el archivo del documento cada 1000rows (y liberando memoria), la próxima carga provoca un aumento de la memoria.

¿Hay alguna forma más eficiente de exportar datos en xlsx sin ocupar mucha memoria?

Answer 1

Excel es capaz de abrir archivos bastante grandes, siempre que tenga suficiente memoria en su computadora. Ese es la mayoría de las veces el factor limitante ...

El 99% de las bibliotecas no se han creado para manejar grandes conjuntos de datos y terminará con errores de memoria insuficiente si intenta arrojarles demasiados datos.

Algunos de ellos, como Spout que creé, se han creado para resolver este problema. El truco es transmitir datos y evitar almacenar cosas en la memoria. No estoy seguro de qué idioma está utilizando (no PHP parece), pero puede haber una biblioteca similar para su idioma. De lo contrario, aún puede echar un vistazo a Spout, que es de código abierto, y convertirlo a su idioma.

Answer 2

OpenXML SDK es la herramienta adecuada para este trabajo, pero debe tener cuidado al usar el enfoque SAX (API simple para XML) en lugar del enfoque DOM . Del artículo de Wikipedia vinculado para SAX:

Cuando el DOM opera en el documento como un todo, los analizadores SAX operan en cada parte del documento XML secuencialmente

Esto reduce enormemente la cantidad de memoria consumida al manejar archivos grandes de Excel.

Aquí hay un buen artículo: http://polymathprogrammer.com/2012/08/06/how-to-properly-use-openxmlwriter-to-write-large-excel-files/

Adaptado de ese artículo, aquí hay un ejemplo que genera 115k filas con 30 columnas:

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

Answer 3

Parece que está usando una hoja de cálculo donde se debe usar una base de datos. Tiene sus limitaciones y este puede ser fácilmente uno de ellos. Siga leyendo solo en caso de que sea absolutamente necesario seguir con la solución existente. Sin embargo, no lo recomiendo. Porque hay una pregunta más: si Excel no puede guardar un archivo tan grande, ¿podrá abrir dicho archivo?

Entonces, si no puede cambiar a la plataforma de base de datos y las bibliotecas estándar que mencionó anteriormente no pueden procesar internamente tal cantidad de datos, entonces tal vez esté solo cuando cree XLSX grande. Me refiero, por ejemplo, a este enfoque:

1. exporta tus datos en lotes (de 1,000 o 10,000 o lo que sea que funcione) para separar archivos para cada lote

2. cree una herramienta ( vb.net (esto es lo más parecido a vba ), c# , python , java , lo que tenga bibliotecas XML sólidas) que une archivos separados en uno. Implica:

   1. extracción de XML de XLSX (generalmente file.xlsx/xl/worksheets/sheet1.xml y file.xlsx/xl/worksheets/sharedStrings.xml )
   2. pegar estas partes juntas mediante la biblioteca de manipulación XML (esto no debería bloquearse en OutOfMemoryException porque ya no está trabajando con objetos de hoja de cálculo complejos)
   3. reempaquetar los archivos de resultados a XLSX principal (puede tomar el primer archivo de salida por lotes como XLSX principal)

Le he mostrado una forma posible de lograr el resultado, pero lo evitaría. Excel nunca fue una plataforma para almacenar grandes cantidades de datos. En comparación con la tarea anterior, podría ser más fácil convencer a la gerencia de que es hora de cambiar las herramientas / procesos en esta área.