OpenXML ile Excel için .NET'te Büyük Veri Kümeleri Nasıl Oluşturulur

• Excel raporlamanın önemi
• Excel dosyaları oluşturmaya yönelik ortak yaklaşım
• Excel'de büyük veri kümelerini aktarma
• Beklenmeyen bellek sızıntıları: Enigma'nın çözülmesi
• Son düşünceler

Excel Raporlamanın Önemi

Büyük kurumsal şirketlerde, Excel raporları oluşturmak, kapsamlı veri kümelerini verimli bir şekilde yönetmek ve analiz etmek için vazgeçilmez bir süreç haline geldi. Bu raporlar, performans ölçümlerini, mali kayıtları ve operasyonel istatistikleri izlemek için çok önemlidir ve stratejik karar almayı yönlendiren değerli bilgiler sunar.

Bu tür ortamlarda, bu dosyaları oluşturan otomasyon araçları, rapor oluşturmayı kolaylaştırmada ve doğruluğu sağlamada çok önemli bir rol oynar. 2024'e doğru ilerlerken, Excel dosyaları oluşturma becerisi kolay ve yaygın bir iş olmalı, değil mi?

Excel Dosyaları Oluşturmaya Genel Yaklaşım

Kendi veri kümenizle bir Excel dosyası oluşturmak için OpenXML kitaplığını kullanacağız. Yapmanız gereken ilk şey bu kütüphaneyi projenize kurmaktır:

 dotnet add package DocumentFormat.OpenXml

Gerekli kütüphaneyi kurup “Test.xlsx” isimli şablon Excel dosyamızı oluşturduktan sonra bu kodu uygulamamıza ekledik:

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

Ve yukarıdaki kodun nasıl kullanılacağı:

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

Metrikler

Bu tabloda fonksiyonumuzu çeşitli satır sayılarıyla test etmeye çalıştık. Beklendiği gibi satır sayısının artması performansın düşmesine neden olacaktır. Bunu düzeltmek için başka bir yaklaşım deneyebiliriz.

Büyük Veri Kümelerini Excel'e Aktarmak

Yukarıda gösterilen yaklaşım basit ve küçük veri kümeleri için yeterlidir. Ancak tabloda gösterildiği gibi büyük veri kümelerinin işlenmesi önemli ölçüde yavaş olabilir. Bu yöntem, doğası gereği yavaş olan DOM manipülasyonlarını içerir. Bu gibi durumlarda SAX (XML için Basit API) yaklaşımı çok değerli hale gelir. Adından da anlaşılacağı gibi SAX, büyük veri kümelerinin işlenmesinde daha etkili bir çözüm sunarak doğrudan Excel belgesinin XML'i ile çalışmamıza olanak tanır.

Kodu ilk örnekten buna değiştirmek:

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

Açıklama : Bu kod, kaynak Excel dosyasındaki XML öğelerini tek tek okur ve öğelerini yeni bir sayfaya kopyalar. Verilerde bir miktar değişiklik yapıldıktan sonra eski sayfa silinir ve yeni sayfa kaydedilir.

Metrikler

Gördüğünüz gibi çok sayıda satırı işleme hızı önemli ölçüde arttı. Ancak artık çözmemiz gereken bir hafıza sorunumuz var.

Beklenmeyen Bellek Sızıntıları: Gizemi Çözmek

Zeki bir gözlemci, Excel'de 10 milyon hücreyi işlerken bellek tüketiminde beklenmedik bir artış fark etmiş olabilir. Her ne kadar 1 milyon telin ağırlığı dikkate değer olsa da, bu kadar önemli bir artışı hesaba katmamalı. Bellek profili oluşturucularla yapılan titiz bir araştırmanın ardından suçlunun OpenXML kütüphanesinde kimliği belirlendi.

Özellikle temel neden, System.IO.Packaging .NET paketindeki hem .NET Standard hem de .NET Core sürümlerini etkileyen bir kusura kadar takip edilebilir. İlginç bir şekilde, klasik .NET'te bu sorun yok gibi görünüyor; bunun nedeni muhtemelen Windows Base kodundaki farklılıklardır. Kısaca, OpenXML kütüphanesi, dosyayı her güncellediğinizde MemoryStream'deki verileri kopyalayan ZipArchive'ı kullanır.

Bu yalnızca güncelleme modunda açarsanız gerçekleşir, ancak bunu başka bir şekilde yapamazsınız çünkü bu, .NET'in kendi davranışıdır.

Bu konuyu daha derinlemesine incelemek isteyenler için daha fazla ayrıntı GitHub Sayı #23750'de bulunabilir.

Daha sonra, .NET kaynak kodunu inceledikten ve benzer zorluklarla karşılaşan meslektaşlarıma danıştıktan sonra geçici bir çözüm geliştirdim. Açık modda Excel dosyamızla çalışmak için SpreadsheetDocument nesnesini kullanamıyorsak, bunu kendi Paket nesnemizle Oluşturma modunda kullanalım. Buggy ZipArchive'ı esasen kullanmayacak ve olması gerektiği gibi çalışacak.

(Uyarı: bu kod artık yalnızca OpenXML v.2.19.0 ve önceki sürümleriyle çalışmaktadır).

Kodumuzu şu şekilde değiştirin:

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

Ve bunu şu şekilde kullanın:

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

Metrikler

Artık ölçümlerimiz ilk ölçümlere göre tatmin edici görünüyor.

Son düşünceler

Başlangıçta, gösterilen kod tamamen tanıtım amaçlıdır. Pratik uygulamalarda, çeşitli hücre tiplerinin desteklenmesi veya hücre stillerinin kopyalanması gibi ek özellikler dikkate alınmalıdır. Önceki örnekte gösterilen önemli optimizasyonlara rağmen, bunun gerçek dünya senaryolarına doğrudan uygulanması mümkün olmayabilir. Genellikle büyük Excel dosyalarını işlemek için yığın tabanlı bir yaklaşım daha uygundur.

Not: Ofis belgeleri oluşturmanın karmaşıklıklarına dalmaktan kaçınmayı tercih ediyorsanız, tüm bu işlevleri sorunsuz bir şekilde basitleştiren ve entegre eden NuGet paketimi inceleyebilirsiniz.

Freepik'te vecstock tarafından hazırlanan Özellik Resmi