So generieren Sie mit OpenXML große Datensätze in .NET für Excel

• Bedeutung der Excel-Berichterstattung
• Gängiger Ansatz zum Generieren von Excel-Dateien
• Übergeben großer Datensätze in Excel
• Unerwartete Speicherlecks: Das Rätsel lösen
• Abschließende Gedanken

Bedeutung der Excel-Berichterstattung

In großen Unternehmen ist die Erstellung von Excel-Berichten zu einem unverzichtbaren Prozess geworden, um umfangreiche Datensätze effizient zu verwalten und zu analysieren. Diese Berichte sind für die Verfolgung von Leistungskennzahlen, Finanzunterlagen und Betriebsstatistiken von entscheidender Bedeutung und bieten wertvolle Erkenntnisse, die die strategische Entscheidungsfindung unterstützen.

In solchen Umgebungen spielen Automatisierungstools, die diese Dateien generieren, eine entscheidende Rolle bei der Optimierung der Berichterstellung und der Gewährleistung der Genauigkeit. Im Jahr 2024 sollte die Möglichkeit, Excel-Dateien zu generieren, eine einfache und alltägliche Aufgabe sein, oder?

Gängiger Ansatz zum Generieren von Excel-Dateien

Um eine Excel-Datei mit Ihrem eigenen Datensatz zu generieren, verwenden wir die OpenXML -Bibliothek. Als Erstes sollten Sie diese Bibliothek in Ihr Projekt installieren:

 dotnet add package DocumentFormat.OpenXml

Nachdem wir die erforderliche Bibliothek installiert und unsere Excel-Vorlagendatei mit dem Namen „Test.xlsx“ erstellt hatten, fügten wir unserer Anwendung diesen Code hinzu:

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

Und so verwenden Sie den obigen Code:

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

Metriken

In dieser Tabelle haben wir versucht, unsere Funktion mit verschiedenen Zeilenzahlen zu testen. Wie erwartet führt eine zunehmende Zeilenzahl zu einer Leistungsminderung. Um das zu beheben, können wir einen anderen Ansatz ausprobieren.

Übergeben großer Datensätze in Excel

Der oben gezeigte Ansatz ist unkompliziert und für kleine Datensätze ausreichend. Wie in der Tabelle dargestellt, kann die Verarbeitung großer Datensätze jedoch erheblich langsamer sein. Diese Methode beinhaltet DOM-Manipulationen, die von Natur aus langsam sind. In solchen Fällen ist der SAX-Ansatz (Simple API for XML) von unschätzbarem Wert. Wie der Name schon sagt, können wir mit SAX direkt mit dem XML des Excel-Dokuments arbeiten und so eine effizientere Lösung für die Verarbeitung großer Datensätze bieten.

Ändern des Codes vom ersten Beispiel wie folgt:

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

Erklärung : Dieser Code liest XML-Elemente nacheinander aus einer Excel-Quelldatei und kopiert die Elemente in ein neues Blatt. Nach einigen Datenmanipulationen löscht er das alte Blatt und speichert das neue.

Metriken

Wie Sie sehen, hat sich die Geschwindigkeit bei der Verarbeitung einer großen Anzahl von Zeilen deutlich erhöht. Allerdings haben wir jetzt ein Speicherproblem, das wir beheben müssen.

Unerwartete Speicherlecks: Das Rätsel lösen

Ein aufmerksamer Beobachter könnte beim Verarbeiten von 10 Millionen Zellen in Excel einen unerwarteten Anstieg des Speicherverbrauchs bemerkt haben. Obwohl das Gewicht von 1 Million Zeichenfolgen beträchtlich ist, sollte es nicht für einen so erheblichen Anstieg verantwortlich sein. Nach sorgfältiger Untersuchung mit Speicherprofilern wurde der Schuldige in der OpenXML-Bibliothek identifiziert.

Die Grundursache kann insbesondere auf einen Fehler im .NET-Paket System.IO.Packaging zurückgeführt werden, der sowohl die .NET Standard- als auch die .NET Core-Version betrifft. Interessanterweise scheint dieses Problem im klassischen .NET nicht aufzutreten, wahrscheinlich aufgrund von Unterschieden im zugrunde liegenden Windows Base-Code. Kurz gesagt verwendet die OpenXML-Bibliothek darin ZipArchive, das bei jeder Aktualisierung der Datei Daten in MemoryStream kopiert.

Dies geschieht nur, wenn Sie es im Aktualisierungsmodus öffnen. Sie können es aber nicht anders tun, da dies das Verhalten von .NET selbst ist.

Wer sich eingehender mit diesem Problem befassen möchte, findet weitere Einzelheiten unter GitHub-Problem Nr. 23750 .

Nachdem ich anschließend den .NET-Quellcode gründlich durchforstet und Kollegen konsultiert hatte, die vor ähnlichen Problemen standen, habe ich eine Workaround-Lösung entwickelt. Wenn wir das SpreadsheetDocument-Objekt nicht verwenden können, um mit unserer Excel-Datei im geöffneten Modus zu arbeiten, verwenden wir es im Erstellungsmodus mit unserem eigenen Package-Objekt. Es wird im Hintergrund nicht das fehlerhafte ZipArchive verwenden und wird wie vorgesehen funktionieren.

(Warnung: Dieser Code funktioniert derzeit nur mit OpenXML v.2.19.0 und früher).

Ändern Sie unseren Code wie folgt:

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

Und verwenden Sie es folgendermaßen:

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

Metriken

Jetzt sehen unsere Messungen im Vergleich zu den ursprünglichen Messungen zufriedenstellend aus.

Abschließende Gedanken

Der gezeigte Code dient zunächst nur Demonstrationszwecken. In praktischen Anwendungen sollten zusätzliche Funktionen wie die Unterstützung verschiedener Zelltypen oder die Replikation von Zellstilen in Betracht gezogen werden. Trotz der im vorherigen Beispiel gezeigten erheblichen Optimierungen ist eine direkte Anwendung in realen Szenarien möglicherweise nicht möglich. Für die Verarbeitung großer Excel-Dateien ist in der Regel ein Chunk-basierter Ansatz besser geeignet.

PS: Wenn Sie sich nicht mit den Feinheiten der Erstellung von Office-Dokumenten befassen möchten, können Sie sich gerne mein NuGet- Paket ansehen, das all diese Funktionen vereinfacht und nahtlos integriert.

Feature-Bild von vecstock auf Freepik