Как генерировать большие наборы данных в .NET для Excel с помощью OpenXML

• Важность отчетов Excel
• Общий подход к созданию файлов Excel
• Передача больших наборов данных в Excel
• Неожиданные утечки памяти: разгадка загадки
• Последние мысли

Важность отчетов Excel

В крупных корпоративных компаниях создание отчетов Excel стало незаменимым процессом для эффективного управления и анализа обширных наборов данных. Эти отчеты имеют решающее значение для отслеживания показателей производительности, финансовых отчетов и операционной статистики, предлагая ценную информацию, которая способствует принятию стратегических решений.

В таких средах инструменты автоматизации, генерирующие эти файлы, играют ключевую роль в оптимизации создания отчетов и обеспечении точности. В 2024 году возможность создания файлов Excel должна стать простой и распространенной задачей, не так ли?

Общий подход к созданию файлов Excel

Чтобы создать файл Excel с вашим собственным набором данных, мы будем использовать библиотеку OpenXML . Первое, что вам нужно сделать, это установить эту библиотеку в свой проект:

 dotnet add package DocumentFormat.OpenXml

После установки необходимой библиотеки и создания файла шаблона Excel с именем «Test.xlsx» мы добавили в наше приложение следующий код:

 // 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(); } }

И вот как использовать приведенный выше код:

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

Метрики

В этой таблице мы попытались протестировать нашу функцию с различным количеством строк. Как и ожидалось, увеличение количества строк приведет к снижению производительности. Чтобы это исправить, мы можем попробовать другой подход.

Передача больших наборов данных в Excel

Подход, продемонстрированный выше, прост и достаточен для небольших наборов данных. Однако, как показано в таблице, обработка больших наборов данных может быть значительно медленной. Этот метод включает в себя манипуляции с DOM, которые по своей сути медленны. В таких случаях подход SAX (Simple API for XML) становится неоценимым. Как следует из названия, SAX позволяет нам работать напрямую с XML-документом Excel, обеспечивая более эффективное решение для обработки больших наборов данных.

Изменение кода из первого примера на этот:

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

Объяснение : Этот код считывает элементы XML из исходного файла Excel один за другим и копирует их на новый лист. После некоторых манипуляций с данными он удаляет старый лист и сохраняет новый.

Метрики

Как видите, скорость обработки большого количества строк существенно возросла. Однако теперь у нас есть проблема с памятью, которую нам нужно решить.

Неожиданные утечки памяти: разгадка загадки

Внимательный наблюдатель мог бы заметить неожиданный всплеск потребления памяти при обработке 10 миллионов ячеек в Excel. Хотя вес 1 миллиона строк значителен, он не должен приводить к такому существенному увеличению. После тщательного расследования с помощью профилировщиков памяти виновник был идентифицирован в библиотеке OpenXML.

В частности, основная причина может быть связана с ошибкой в пакете .NET System.IO.Packaging, затрагивающей версии .NET Standard и .NET Core. Интересно, что эта проблема отсутствует в классической .NET, вероятно, из-за различий в базовом коде Windows. Короче говоря, библиотека OpenXML использует в себе ZipArchive, который копирует данные в MemoryStream каждый раз, когда вы обновляете файл.

Это происходит, только если открыть его в режиме обновления, но по-другому сделать нельзя, потому что это поведение самого .NET.

Для тех, кто хочет глубже разобраться в этой проблеме, дополнительную информацию можно найти в GitHub Issue #23750 .

Впоследствии, тщательно изучив исходный код .NET и проконсультировавшись с коллегами, столкнувшимися с аналогичными проблемами, я разработал обходное решение. Если мы не можем использовать объект SpreadsheetDocument для работы с нашим файлом Excel в режиме открытия — давайте используем его в режиме создания с нашим собственным объектом Package. Он не будет использовать глючный ZipArchive под капотом и будет работать как надо.

(Внимание: этот код теперь работает только с OpenXML v.2.19.0 и более ранними версиями).

Измените наш код на это:

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

И используйте его так:

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

Метрики

Теперь наши измерения выглядят удовлетворительно по сравнению с первоначальными.

Последние мысли

Изначально представленный код служит чисто демонстрационным целям. В практических приложениях следует учитывать дополнительные функции, такие как поддержка различных типов ячеек или репликация стилей ячеек. Несмотря на значительную оптимизацию, продемонстрированную в предыдущем примере, ее прямое применение в реальных сценариях может оказаться неосуществимым. Обычно для обработки больших файлов Excel более подходит подход на основе фрагментов.

PS: Если вы предпочитаете не углубляться в тонкости создания офисных документов, можете изучить мой пакет NuGet, который упрощает и легко интегрирует все эти функции.

Художественное изображение от vecstock на Freepik