OpenXML を使用して .NET で Excel 用の大規模なデータセットを生成する方法

• Excelレポートの重要性
• Excelファイルを生成するための一般的なアプローチ
• Excelで大規模なデータセットを渡す
• 予期せぬメモリリーク: エニグマの解明
• 最終的な考え

Excel レポートの重要性

大規模な企業では、Excel レポートの生成は、膨大なデータセットを効率的に管理および分析するために不可欠なプロセスになっています。これらのレポートは、パフォーマンス メトリック、財務記録、運用統計を追跡するために不可欠であり、戦略的な意思決定を促進する貴重な洞察を提供します。

このような環境では、これらのファイルを生成する自動化ツールが、レポート作成の効率化と正確性の確保に極めて重要な役割を果たします。2024 年に向けて、Excel ファイルを生成する機能は簡単で一般的なタスクになるはずです。

Excel ファイルを生成するための一般的なアプローチ

独自のデータセットを使用して Excel ファイルを生成するには、 OpenXMLライブラリを使用します。まず、このライブラリをプロジェクトにインストールする必要があります。

 dotnet add package DocumentFormat.OpenXml

必要なライブラリをインストールし、「Test.xlsx」という名前のテンプレート Excel ファイルを作成した後、次のコードをアプリケーションに追加しました。

 // 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 を使用すると、Excel ドキュメントの XML を直接操作できるため、大規模なデータセットを処理するためのより効率的なソリューションが提供されます。

最初の例のコードを次のように変更します。

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

説明: このコードは、ソース Excel ファイルから XML 要素を 1 つずつ読み取り、その要素を新しいシートにコピーします。データを操作した後、古いシートを削除して新しいシートを保存します。

メトリクス

ご覧のとおり、大量の行を処理する速度が大幅に向上しました。ただし、ここで対処する必要があるメモリの問題が発生しています。

予期せぬメモリリーク：謎を解く

洞察力のある人なら、Excel で 1,000 万個のセルを処理しているときに、メモリ消費量が予想外に急増したことに気付いたかもしれません。100 万個の文字列の重みは相当なものですが、これほど大幅に増加するはずはありません。メモリ プロファイラーを使用して綿密に調査した結果、原因は OpenXML ライブラリ内にあることが判明しました。

具体的には、根本的な原因は .NET パッケージ System.IO.Packaging の欠陥にあり、.NET Standard と .NET Core の両方のバージョンに影響を及ぼしています。興味深いことに、この問題は従来の .NET では発生していないようです。これは、基盤となる Windows Base コードの違いによるものと考えられます。簡単に言うと、OpenXML ライブラリでは ZipArchive が使用され、ファイルを更新するたびに MemoryStream にデータがコピーされます。

これは更新モードで開いた場合にのみ発生しますが、これは .NET 自体の動作であるため、他の方法では実行できません。

この問題をさらに詳しく調べたい場合は、 GitHub Issue #23750で詳細を確認できます。

その後、.NET ソース コードを詳しく調べ、同様の問題に直面している同僚に相談した後、回避策を考案しました。SpreadsheetDocument オブジェクトを使用して Excel ファイルを Open モードで操作できない場合は、独自の Package オブジェクトを使用して Create モードで使用しましょう。内部でバグのある 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: Office ドキュメント生成の複雑な部分を詳しく調べたくない場合は、これらの機能をすべて簡素化してシームレスに統合する私の NuGetパッケージをぜひご覧ください。

フィーチャー画像：vecstock（Freepik）