Exportar grande quantidade de dados do XLSX – OutOfMemoryException

Estou me aproximando para exportar uma grande quantidade de dados (115.000 linhas x 30 colunas) no formato Excel OpenXML (xlsx). Eu estou usando algumas bibliotecas como DocumentFormat.OpenXML, ClosedXML, NPOI.

Com cada um desses, OutOfMemoryException é lançado porque a representação da planilha na memory causa um aumento de memory exponencial.

Também fechando o arquivo de documento a cada 1000 passos (e liberando memory), o próximo carregamento faz com que a memory aumente.

Existe uma maneira mais eficiente de exportar dados no xlsx sem ocupar muita memory?

O SDK do OpenXML é a ferramenta certa para esse trabalho, mas você precisa ter cuidado ao usar a abordagem SAX (Simple API for XML) em vez da abordagem DOM . Do artigo da Wikipédia para SAX:

Onde o DOM opera no documento como um todo, os analisadores SAX operam em cada parte do documento XML seqüencialmente

Isso reduz enormemente a quantidade de memory consumida ao lidar com arquivos grandes do Excel.

Há um bom artigo sobre isso aqui – http://polymathprogrammer.com/2012/08/06/how-to-properly-use-openxmlwriter-to-write-large-excel-files/

Adaptado desse artigo, aqui está um exemplo que gera 115k linhas com 30 colunas:

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

O Excel é capaz de abrir arquivos muito grandes, contanto que você tenha memory suficiente no seu computador. Na maioria das vezes é o fator limitante …

99% das bibliotecas lá fora não foram criadas para lidar com um grande dataset e você acabará com erros de falta de memory se tentar lançar muitos dados neles.

Alguns deles, como o Spout que criei, foram criados para resolver esse problema. O truque é transmitir dados e evitar armazenar coisas na memory. Não tenho certeza de qual idioma você está usando (não parece o PHP), mas pode haver uma biblioteca semelhante para o seu idioma. Se não, você ainda pode dar uma olhada no Spout – é open-source – e convertê-lo em seu idioma.

Parece que você está usando uma planilha onde um database deve ser usado. Tem suas limitações e isso pode ser facilmente uma delas. Leia mais apenas no caso, se você absolutamente precisa ficar com a solução existente. No entanto, não recomendo. Porque há mais uma pergunta: se o Excel não puder salvar um arquivo tão grande, ele poderá abrir esse arquivo?

Então, se você não pode mudar para a plataforma de database e bibliotecas padrão que você mencionou acima são internamente incapazes de processar tal quantidade de dados, então talvez você esteja por conta própria ao criar grandes XLSX. Quero dizer, por exemplo, esta abordagem:

  1. exportar seus dados em lotes (de 1.000 ou 10.000 ou o que quer que funcione) para separar arquivos para cada lote
  2. crie uma ferramenta ( vb.net (esta é a mais próxima de vba ), c # , python , java , o que tiver bibliotecas XML sólidas) que une arquivos separados em um. Envolve:

    1. extrair XML do XLSX (normalmente file.xlsx\xl\worksheets\sheet1.xml e file.xlsx\xl\worksheets\sharedStrings.xml )
    2. colando essas partes juntas pela biblioteca de manipulação de XML (isso não deve falhar em OutOfMemoryException porque você não está mais trabalhando com objects de planilha complexos)
    3. reempacotando arquivos de resultados de volta ao XLSX principal (você pode pegar o primeiro arquivo de saída em lote como XLSX principal)

Eu mostrei a você a maneira possível de conseguir o resultado, mas eu evitaria isso. O Excel nunca foi uma plataforma para armazenar grandes quantidades de dados. Comparado com a tarefa acima, poderia ser mais fácil convencer o gerenciamento de que é hora de mudar as ferramentas / processos nessa área.