Comment générer de grands ensembles de données dans .NET pour Excel avec OpenXML

• Importance des rapports Excel
• Approche courante pour générer des fichiers Excel
• Passer de grands ensembles de données dans Excel
• Fuites de mémoire inattendues : résoudre l’énigme
• Dernières pensées

Importance des rapports Excel

Dans les grandes entreprises, la génération de rapports Excel est devenue un processus indispensable pour gérer et analyser efficacement de vastes ensembles de données. Ces rapports sont cruciaux pour suivre les mesures de performance, les dossiers financiers et les statistiques opérationnelles, offrant des informations précieuses qui orientent la prise de décision stratégique.

Dans de tels environnements, les outils d'automatisation qui génèrent ces fichiers jouent un rôle central dans la rationalisation de la création de rapports et en garantissant l'exactitude. À l’approche de 2024, la capacité de générer des fichiers Excel devrait être une tâche simple et courante, n’est-ce pas ?

Approche commune pour générer des fichiers Excel

Pour générer un fichier Excel avec votre propre jeu de données, nous utiliserons la bibliothèque OpenXML . La première chose à faire est d'installer cette bibliothèque dans votre projet :

 dotnet add package DocumentFormat.OpenXml

Après avoir installé la bibliothèque nécessaire et créé notre modèle de fichier Excel nommé « Test.xlsx », nous avons ajouté ce code à notre application :

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

Et voici comment utiliser le code ci-dessus :

 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étrique

Dans ce tableau, nous avons essayé de tester notre fonction avec différents nombres de lignes. Comme prévu, l'augmentation du nombre de lignes entraînera une diminution des performances. Pour résoudre ce problème, nous pouvons essayer une autre approche.

Transmission de grands ensembles de données dans Excel

L'approche démontrée ci-dessus est simple et suffisante pour les petits ensembles de données. Cependant, comme l'illustre le tableau, le traitement de grands ensembles de données peut être considérablement lent. Cette méthode implique des manipulations DOM, qui sont intrinsèquement lentes. Dans de tels cas, l’approche SAX (Simple API for XML) devient inestimable. Comme son nom l'indique, SAX nous permet de travailler directement avec le XML du document Excel, offrant ainsi une solution plus efficace pour gérer de grands ensembles de données.

Changer le code du premier exemple par celui-ci :

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

Explication : Ce code lit les éléments XML d'un fichier Excel source un par un et copie ses éléments dans une nouvelle feuille. Après quelques manipulations des données, il supprime l'ancienne feuille et enregistre la nouvelle.

Métrique

Comme vous pouvez le constater, la vitesse de traitement d'un grand nombre de lignes a considérablement augmenté. Cependant, nous sommes désormais confrontés à un problème de mémoire auquel nous devons remédier.

Fuites de mémoire inattendues : résoudre l’énigme

Un observateur averti aurait peut-être remarqué une augmentation inattendue de la consommation de mémoire lors du traitement de 10 millions de cellules dans Excel. Même si le poids d'un million de cordes est considérable, il ne devrait pas représenter une augmentation aussi substantielle. Après une enquête minutieuse avec des profileurs de mémoire, le coupable a été identifié au sein de la bibliothèque OpenXML.

Plus précisément, la cause première peut être attribuée à une faille dans le package .NET System.IO.Packaging, affectant à la fois les versions .NET Standard et .NET Core. Il est intéressant de noter que ce problème semble absent dans .NET classique, probablement en raison de différences dans le code Windows Base sous-jacent. En peu de temps, la bibliothèque OpenXML utilise ZipArchive, qui copie les données dans MemoryStream à chaque fois que vous mettez à jour le fichier.

Cela se produit uniquement si vous l'ouvrez en mode mise à jour, mais vous ne pouvez pas le faire autrement car c'est le comportement de .NET lui-même.

Pour ceux qui souhaitent approfondir ce problème, de plus amples détails peuvent être trouvés sur GitHub Issue #23750 .

Par la suite, après avoir étudié le code source de .NET et consulté des pairs confrontés à des défis similaires, j'ai conçu une solution de contournement. Si nous ne pouvons pas utiliser l'objet SpreadsheetDocument pour travailler avec notre fichier Excel en mode Ouvrir, utilisons-le en mode Création avec notre propre objet Package. Il n'utilisera pas le buggy ZipArchive sous le capot et fonctionnera comme il se doit.

(Attention : ce code ne fonctionne désormais qu'avec OpenXML v.2.19.0 et versions antérieures).

Changez notre code comme suit :

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

Et utilisez-le comme ceci :

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

Métrique

Désormais, nos mesures semblent satisfaisantes par rapport aux premières.

Dernières pensées

Initialement, le code présenté sert à des fins purement démonstratives. Dans les applications pratiques, des fonctionnalités supplémentaires telles que la prise en charge de différents types de cellules ou la réplication de styles de cellules doivent être prises en compte. Malgré les optimisations significatives démontrées dans l’exemple précédent, son application directe dans des scénarios réels pourrait ne pas être réalisable. En règle générale, pour gérer des fichiers Excel volumineux, une approche basée sur des morceaux est plus adaptée.

PS : Si vous préférez éviter de vous plonger dans les subtilités de la génération de documents bureautiques, vous pouvez explorer mon package NuGet, qui simplifie et intègre toutes ces fonctionnalités de manière transparente.

Image vedette par vecstock sur Freepik