Como gerar grandes conjuntos de dados em .NET para Excel com OpenXML

• Importância dos relatórios do Excel
• Abordagem comum para gerar arquivos Excel
• Passando grandes conjuntos de dados no Excel
• Vazamentos inesperados de memória: desvendando o Enigma
• Pensamentos finais

Importância dos relatórios do Excel

Em grandes empresas, a geração de relatórios em Excel tornou-se um processo indispensável para gerenciar e analisar extensos conjuntos de dados com eficiência. Esses relatórios são cruciais para monitorar métricas de desempenho, registros financeiros e estatísticas operacionais, oferecendo insights valiosos que orientam a tomada de decisões estratégicas.

Nesses ambientes, as ferramentas de automação que geram esses arquivos desempenham um papel fundamental na agilização da criação de relatórios e na garantia da precisão. À medida que avançamos para 2024, a capacidade de gerar arquivos Excel deve ser uma tarefa fácil e comum, certo?

Abordagem comum para geração de arquivos Excel

Para gerar um arquivo Excel com seu próprio conjunto de dados, utilizaremos a biblioteca OpenXML . A primeira coisa que você deve fazer é instalar esta biblioteca em seu projeto:

 dotnet add package DocumentFormat.OpenXml

Depois de instalar a biblioteca necessária e criar nosso arquivo Excel de modelo chamado “Test.xlsx”, adicionamos este código ao nosso aplicativo:

 // this custom type is for your input data public class DataSet { public List<DataRow> Rows { get; set; } } // this row will contain number of our row and info about each cell public class DataRow { public int Index { get; set; } public Dictionary<string, string> Cells { get; set; } } private void SetValuesToExcel(string filePath, DataSet dataSet) { if (string.IsNullOrWhiteSpace(filePath)) { throw new FileNotFoundException($"File not found at this path: {filePath}"); } using (SpreadsheetDocument document = SpreadsheetDocument.Open(filePath, true)) { //each excel document has XML-structure, //so we need to go deeper to our sheet WorkbookPart wbPart = document.WorkbookPart; //feel free to pass sheet name as parameter. //here we'll just use the default one Sheet theSheet = wbPart.Workbook .Descendants<Sheet>() .FirstOrDefault(s => s.Name.Value.Trim() == "Sheet1"); //next element in hierarchy is worksheetpart //we need to dive deeper to SheetData object WorksheetPart wsPart = (WorksheetPart)(wbPart.GetPartById(theSheet.Id)); Worksheet worksheet = wsPart.Worksheet; SheetData sheetData = worksheet.GetFirstChild<SheetData>(); //iterating through our data foreach (var dataRow in dataSet.Rows) { //getting Row element from Excel's DOM var rowIndex = dataRow.Index; var row = sheetData .Elements<Row>() .FirstOrDefault(r => r.RowIndex == rowIndex); //if there is no row - we'll create new one if (row == null) { row = new Row { RowIndex = (uint)rowIndex }; sheetData.Append(row); } //now we need to iterate though each cell in the row foreach (var dataCell in dataRow.Cells) { var cell = row.Elements<Cell>() .FirstOrDefault(c => c.CellReference.Value == dataCell.Key); if (cell == null) { cell = new Cell { CellReference = dataCell.Key, DataType = CellValues.String }; row.AppendChild(cell); } cell.CellValue = new CellValue(dataCell.Value); } } //after all changes in Excel DOM we need to save it wbPart.Workbook.Save(); } }

E é assim que usar o código acima:

 var filePath = "Test.xlsx"; // number of rows that we want to add to our Excel file var testRowsCounter = 100; // creating some data for it var dataSet = new DataSet(); dataSet.Rows = new List<DataRow>(); string alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"; for (int i = 0; i < testRowsCounter; i++) { var row = new DataRow { Cells = new Dictionary<string, string>(), Index = i + 1 }; for (int j = 0; j < 10; j++) { row.Cells.Add($"{alphabet[j]}{i+1}", Guid.NewGuid().ToString()); } dataSet.Rows.Add(row); } //passing path to our file and data object SetValuesToExcel(filePath, dataSet);

Métricas

Nesta tabela, tentamos testar nossa função com vários números de linhas. Como esperado – o aumento do número de linhas levará à diminuição do desempenho. Para corrigir isso, podemos tentar outra abordagem.

Passando grandes conjuntos de dados no Excel

A abordagem demonstrada acima é direta e suficiente para pequenos conjuntos de dados. No entanto, conforme ilustrado na tabela, o processamento de grandes conjuntos de dados pode ser significativamente lento. Este método envolve manipulações de DOM, que são inerentemente lentas. Nesses casos, a abordagem SAX (Simple API for XML) torna-se inestimável. Como o nome sugere, SAX nos permite trabalhar diretamente com o XML do documento Excel, proporcionando uma solução mais eficiente para o tratamento de grandes conjuntos de dados.

Alterando o código do primeiro exemplo para este:

 using (SpreadsheetDocument document = SpreadsheetDocument.Open(filePath, true)) { WorkbookPart workbookPart = document.WorkbookPart; //we taking the original worksheetpart of our template WorksheetPart worksheetPart = workbookPart.WorksheetParts.First(); //adding the new one WorksheetPart replacementPart = workbookPart.AddNewPart<WorksheetPart>(); string originalSheetId = workbookPart.GetIdOfPart(worksheetPart); string replacementPartId = workbookPart.GetIdOfPart(replacementPart); //the main idea is read through XML of original sheet object OpenXmlReader openXmlReader = OpenXmlReader.Create(worksheetPart); //and write it to the new one with some injection of our custom data OpenXmlWriter openXmlWriter = OpenXmlWriter.Create(replacementPart); while (openXmlReader.Read()) { if (openXmlReader.ElementType == typeof(SheetData)) { if (openXmlReader.IsEndElement) continue; // write sheet element openXmlWriter.WriteStartElement(new SheetData()); // write data rows foreach (var row in dataSet.Rows) { Row r = new Row { RowIndex = (uint)row.Index }; // start row openXmlWriter.WriteStartElement(r); foreach (var rowCell in row.Cells) { Cell c = new Cell { DataType = CellValues.String, CellReference = rowCell.Key, CellValue = new CellValue(rowCell.Value) }; // cell openXmlWriter.WriteElement(c); } // end row openXmlWriter.WriteEndElement(); } // end sheet openXmlWriter.WriteEndElement(); } else { //this block is for writing all not so interesting parts of XML //but they are still are necessary if (openXmlReader.ElementType == typeof(Row) && openXmlReader.ElementType == typeof(Cell) && openXmlReader.ElementType == typeof(CellValue)) { openXmlReader.ReadNextSibling(); continue; } if (openXmlReader.IsStartElement) { openXmlWriter.WriteStartElement(openXmlReader); } else if (openXmlReader.IsEndElement) { openXmlWriter.WriteEndElement(); } } } openXmlReader.Close(); openXmlWriter.Close(); //after all modifications we switch sheets inserting //the new one to the original file Sheet sheet = workbookPart.Workbook .Descendants<Sheet>() .First(c => c.Id == originalSheetId); sheet.Id.Value = replacementPartId; //deleting the original worksheet workbookPart.DeletePart(worksheetPart); }

Explicação : Este código lê os elementos XML de um arquivo Excel de origem, um por um, e copia seus elementos para uma nova planilha. Após alguma manipulação dos dados, ele exclui a planilha antiga e salva a nova.

Métricas

Como você pode ver, a velocidade de processamento de um grande número de linhas aumentou significativamente. No entanto, agora temos um problema de memória que precisamos resolver.

Vazamentos inesperados de memória: desvendando o enigma

Um observador perspicaz pode ter notado um aumento inesperado no consumo de memória ao processar 10 milhões de células no Excel. Embora o peso de 1 milhão de cordas seja considerável, não deveria ser responsável por um aumento tão substancial. Após investigação meticulosa com criadores de perfil de memória, o culpado foi identificado na biblioteca OpenXML.

Especificamente, a causa raiz pode ser atribuída a uma falha no pacote .NET System.IO.Packaging, afetando as versões .NET Standard e .NET Core. Curiosamente, esse problema parece ausente no .NET clássico, provavelmente devido a diferenças no código subjacente do Windows Base. Em breve, a biblioteca OpenXML usa ZipArchive, que copia dados em MemoryStream sempre que você atualiza o arquivo.

Isso só acontece se você abri-lo em modo de atualização, mas não pode fazer de outra forma porque é o comportamento do próprio .NET.

Para aqueles interessados em se aprofundar neste assunto, mais detalhes podem ser encontrados em GitHub Issue #23750 .

Posteriormente, depois de me debruçar sobre o código-fonte do .NET e consultar colegas que enfrentavam desafios semelhantes, desenvolvi uma solução alternativa. Se não pudermos usar o objeto SpreadsheetDocument para trabalhar com nosso arquivo Excel no modo Open - vamos usá-lo no modo Create com nosso próprio objeto Package. Ele não usará ZipArchive com bugs sob o capô e funcionará como deveria.

(Aviso: este código funciona agora apenas com OpenXML v.2.19.0 e anteriores).

Mude nosso código para este:

 public class Builder { public async Task Build(string filePath, string sheetName, DataSet dataSet) { var workbookId = await FillData(filePath, sheetName, dataSet); await WriteAdditionalElements(filePath, sheetName, workbookId); } public async Task<string> FillData(string filePath, string sheetName, DataSet excelDataRows) { //opening our file in create mode await using var fileStream = File.Create(filePath); using var package = Package.Open(fileStream, FileMode.Create, FileAccess.Write); using var excel = SpreadsheetDocument.Create(package, SpreadsheetDocumentType.Workbook); //adding new workbookpart excel.AddWorkbookPart(); var worksheetPart = excel.WorkbookPart.AddNewPart<WorksheetPart>(); var workbookId = excel.WorkbookPart.GetIdOfPart(worksheetPart); //creating necessary worksheet and sheetdata OpenXmlWriter openXmlWriter = OpenXmlWriter.Create(worksheetPart); openXmlWriter.WriteStartElement(new Worksheet()); openXmlWriter.WriteStartElement(new SheetData()); // write data rows foreach (var row in excelDataRows.Rows.OrderBy(r => r.Index)) { Row r = new Row { RowIndex = (uint)row.Index }; openXmlWriter.WriteStartElement(r); foreach (var rowCell in row.Cells) { Cell c = new Cell { DataType = CellValues.String, CellReference = rowCell.Key }; //cell openXmlWriter.WriteStartElement(c); CellValue v = new CellValue(rowCell.Value); openXmlWriter.WriteElement(v); //cell end openXmlWriter.WriteEndElement(); } // end row openXmlWriter.WriteEndElement(); } //sheetdata end openXmlWriter.WriteEndElement(); //worksheet end openXmlWriter.WriteEndElement(); openXmlWriter.Close(); return workbookId; } public async Task WriteAdditionalElements(string filePath, string sheetName, string worksheetPartId) { //here we should add our workbook to the file //without this - our document will be incomplete await using var fileStream = File.Open(filePath, FileMode.Open, FileAccess.ReadWrite, FileShare.None); using var package = Package.Open(fileStream, FileMode.Open, FileAccess.ReadWrite); using var excel = SpreadsheetDocument.Open(package); if (excel.WorkbookPart is null) throw new InvalidOperationException("Workbook part cannot be null!"); var xmlWriter = OpenXmlWriter.Create(excel.WorkbookPart); xmlWriter.WriteStartElement(new Workbook()); xmlWriter.WriteStartElement(new Sheets()); xmlWriter.WriteElement(new Sheet { Id = worksheetPartId, Name = sheetName, SheetId = 1 }); xmlWriter.WriteEndElement(); xmlWriter.WriteEndElement(); xmlWriter.Close(); xmlWriter.Dispose(); } }

E use assim:

 var builder = new Builder(); await builder.Build(filePath, "Sheet1", dataSet);

Métricas

Agora, nossas medições parecem satisfatórias em comparação com as iniciais.

Pensamentos finais

Inicialmente, o código apresentado serve para fins puramente demonstrativos. Em aplicações práticas, devem ser considerados recursos adicionais, como suporte para vários tipos de células ou replicação de estilos de células. Apesar das otimizações significativas demonstradas no exemplo anterior, a sua aplicação direta em cenários do mundo real pode não ser viável. Normalmente, para lidar com arquivos grandes do Excel, uma abordagem baseada em blocos é mais adequada.

PS: Se você preferir evitar se aprofundar nas complexidades da geração de documentos de escritório, fique à vontade para explorar meu pacote NuGet, que simplifica e integra todas essas funcionalidades perfeitamente.

Imagem de destaque por vecstock no Freepik