Exporte gran cantidad de datos de XLSX – OutOfMemoryException

Me estoy acercando para 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 esto, OutOfMemoryException se lanza porque la representación de la hoja en la memoria provoca un aumento exponencial de la memoria.

También cerrando el archivo de documentos cada 1000 filas (y liberando la memoria), la siguiente carga aumenta la memoria.

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

OpenXML SDK es la herramienta adecuada para este trabajo, pero debe tener cuidado de utilizar el enfoque SAX (API simple para XML) en lugar del enfoque DOM . Del artículo enlazado de wikipedia 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 cuando se manejan grandes archivos de Excel.

Hay un buen artículo aquí – 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 attributes; OpenXmlWriter writer; document.AddWorkbookPart(); WorksheetPart workSheetPart = document.WorkbookPart.AddNewPart(); 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(); // 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(); // 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; } 

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

El 99% de las bibliotecas no se han creado para manejar un gran conjunto de datos y usted terminará con errores de falta de memoria si intenta arrojar demasiados datos sobre ellos.

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á usando (no parece PHP), pero puede haber una biblioteca similar para su idioma. De lo contrario, aún puedes echar un vistazo a Spout, es de código abierto, y convertirlo a tu idioma.

Parece que estás 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. Lea más a continuación solo en caso de que necesite absolutamente atenerse a 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?

Por lo tanto, si no puede cambiar a la plataforma de la base de datos y las bibliotecas estándar que mencionó anteriormente son internamente incapaces de procesar dicha cantidad de datos, entonces quizás esté solo cuando cree XLSX grande. Me refiero, por ejemplo, a este enfoque:

  1. Exporte sus datos en lotes (de 1,000 o 10,000 o lo que sea que funcione) para separar los archivos de cada lote
  2. crea una herramienta ( vb.net (esto es lo más cercano a vba ), c # , python , java , lo que sea que tenga bibliotecas XML sólidas) que une archivos separados en uno. Implica:

    1. extraer XML de XLSX (típicamente file.xlsx\xl\worksheets\sheet1.xml y file.xlsx\xl\worksheets\sharedStrings.xml )
    2. pegar estas partes mediante la biblioteca de manipulación XML (esto no debería colgarse en OutOfMemoryException porque ya no se trabaja con objetos complejos de hojas de cálculo)
    3. volver a empaquetar los archivos de resultados en XLSX principal (puede tomar el primer archivo de salida por lotes como XLSX principal)

Te he mostrado una forma posible de lograr el resultado, pero evitaría eso. 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 administración de que es hora de cambiar las herramientas / procesos en esta área.