OpenXML을 사용하여 Excel용 .NET에서 대규모 데이터 세트를 생성하는 방법

• 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 요소를 하나씩 읽고 해당 요소를 새 시트에 복사합니다. 데이터를 일부 조작한 후 이전 시트를 삭제하고 새 시트를 저장합니다.

측정항목

보시다시피, 많은 수의 행을 처리하는 속도가 크게 향상되었습니다. 그러나 이제 해결해야 할 메모리 문제가 있습니다.

예상치 못한 메모리 누수: 수수께끼 풀기

통찰력 있는 관찰자는 Excel에서 천만 개의 셀을 처리하는 동안 메모리 소비가 예기치 않게 급증하는 것을 발견했을 것입니다. 100만 개의 현의 무게는 상당하지만, 그렇게 큰 증가를 설명해서는 안 됩니다. 메모리 프로파일러를 사용하여 꼼꼼하게 조사한 결과 OpenXML 라이브러리 내에서 범인이 식별되었습니다.

특히 근본 원인은 .NET Standard 및 .NET Core 버전 모두에 영향을 미치는 .NET 패키지 System.IO.Packaging의 결함으로 추적될 수 있습니다. 흥미롭게도 이 문제는 기본 Windows 기본 코드의 차이로 인해 클래식 .NET에서는 없는 것 같습니다. 곧 OpenXML 라이브러리는 파일을 업데이트할 때마다 MemoryStream의 데이터를 복사하는 ZipArchive를 사용합니다.

업데이트 모드로 열 경우에만 발생하는데, .NET 자체의 동작이기 때문에 다른 방법으로는 할 수 없습니다.

이 문제에 대해 더 자세히 알아보는 데 관심이 있는 경우 GitHub 문제 #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 파일을 처리하는 경우 청크 기반 접근 방식이 더 적합합니다.

추신: Office 문서 생성의 복잡성을 자세히 알아보고 싶지 않다면 이러한 모든 기능을 원활하게 단순화하고 통합하는 NuGet 패키지를 살펴보세요.

Freepik에서 vecstock이 제공하는 특집 이미지