OpenXML के साथ Excel के लिए .NET में बड़े डेटासेट कैसे उत्पन्न करें

• एक्सेल रिपोर्टिंग का महत्व
• एक्सेल फ़ाइलें बनाने का सामान्य तरीका
• एक्सेल में बड़े डेटासेट पास करना
• अप्रत्याशित मेमोरी लीक: रहस्य से पर्दा उठना
• अंतिम विचार

एक्सेल रिपोर्टिंग का महत्व

बड़ी उद्यम कंपनियों में, व्यापक डेटासेट को कुशलतापूर्वक प्रबंधित करने और विश्लेषण करने के लिए एक्सेल रिपोर्ट तैयार करना एक अनिवार्य प्रक्रिया बन गई है। ये रिपोर्ट प्रदर्शन मीट्रिक, वित्तीय रिकॉर्ड और परिचालन सांख्यिकी को ट्रैक करने के लिए महत्वपूर्ण हैं, जो रणनीतिक निर्णय लेने में मदद करने वाली मूल्यवान अंतर्दृष्टि प्रदान करती हैं।

ऐसे माहौल में, इन फ़ाइलों को बनाने वाले स्वचालन उपकरण रिपोर्ट निर्माण को सुव्यवस्थित करने और सटीकता सुनिश्चित करने में महत्वपूर्ण भूमिका निभाते हैं। जैसे-जैसे हम 2024 में आगे बढ़ रहे हैं, एक्सेल फ़ाइलें बनाने की क्षमता एक आसान और सामान्य कार्य होना चाहिए, है ना?

एक्सेल फ़ाइलें बनाने का सामान्य तरीका

अपने खुद के डेटासेट के साथ एक एक्सेल फ़ाइल बनाने के लिए, हम OpenXML लाइब्रेरी का उपयोग करेंगे। सबसे पहले आपको इस लाइब्रेरी को अपने प्रोजेक्ट में इंस्टॉल करना चाहिए:

 dotnet add package DocumentFormat.OpenXml

आवश्यक लाइब्रेरी स्थापित करने और “Test.xlsx” नामक हमारी टेम्पलेट एक्सेल फ़ाइल बनाने के बाद, हमने अपने एप्लिकेशन में यह कोड जोड़ा:

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

मेट्रिक्स

इस तालिका में, हमने विभिन्न पंक्तियों की संख्या के साथ अपने फ़ंक्शन का परीक्षण करने का प्रयास किया। जैसा कि अपेक्षित था - पंक्तियों की संख्या बढ़ाने से प्रदर्शन में कमी आएगी। इसे ठीक करने के लिए, हम दूसरा तरीका आज़मा सकते हैं।

एक्सेल में बड़े डेटासेट पास करना

ऊपर दिखाया गया तरीका सीधा और छोटे डेटासेट के लिए पर्याप्त है। हालाँकि, जैसा कि तालिका में दिखाया गया है, बड़े डेटासेट को संसाधित करना काफी धीमा हो सकता है। इस विधि में DOM हेरफेर शामिल है, जो स्वाभाविक रूप से धीमा है। ऐसे मामलों में, SAX (XML के लिए सरल API) दृष्टिकोण अमूल्य हो जाता है। जैसा कि नाम से पता चलता है, 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); }

स्पष्टीकरण : यह कोड स्रोत एक्सेल फ़ाइल से XML तत्वों को एक-एक करके पढ़ता है और उसके तत्वों को एक नई शीट पर कॉपी करता है। डेटा में कुछ हेरफेर करने के बाद, यह पुरानी शीट को हटा देता है और नई शीट को सहेजता है।

मेट्रिक्स

जैसा कि आप देख सकते हैं, बड़ी संख्या में पंक्तियों को संसाधित करने की गति में उल्लेखनीय वृद्धि हुई है। हालाँकि, अब हमारे पास एक मेमोरी समस्या है जिसे हमें संबोधित करने की आवश्यकता है।

अप्रत्याशित स्मृति रिसाव: रहस्य से पर्दा उठना

एक समझदार पर्यवेक्षक ने एक्सेल में 10 मिलियन सेल को प्रोसेस करते समय मेमोरी खपत में अप्रत्याशित वृद्धि देखी होगी। हालाँकि 1 मिलियन स्ट्रिंग का वजन काफी है, लेकिन यह इतनी बड़ी वृद्धि के लिए जिम्मेदार नहीं होना चाहिए। मेमोरी प्रोफाइलर्स के साथ सावधानीपूर्वक जांच के बाद, ओपनएक्सएमएल लाइब्रेरी के भीतर अपराधी की पहचान की गई।

विशेष रूप से, मूल कारण .NET पैकेज System.IO.Packaging में दोष से पता लगाया जा सकता है, जो .NET Standard और .NET Core दोनों संस्करणों को प्रभावित करता है। दिलचस्प बात यह है कि यह समस्या क्लासिक .NET में अनुपस्थित लगती है, संभवतः अंतर्निहित Windows बेस कोड में अंतर के कारण। संक्षेप में, OpenXML लाइब्रेरी इसमें ZipArchive का उपयोग करती है, जो फ़ाइल को अपडेट करने पर हर बार MemoryStream में डेटा कॉपी करती है।

ऐसा तभी होता है जब आप इसे अपडेट मोड में खोलते हैं, लेकिन आप इसे किसी अन्य तरीके से नहीं कर सकते क्योंकि यह .NET का ही व्यवहार है।

जो लोग इस मुद्दे पर गहराई से जानने में रुचि रखते हैं, वे अधिक जानकारी GitHub Issue #23750 पर पा सकते हैं।

इसके बाद, .NET स्रोत कोड पर गहन अध्ययन करने और समान चुनौतियों का सामना कर रहे साथियों से परामर्श करने के बाद, मैंने एक वैकल्पिक समाधान तैयार किया। यदि हम ओपन मोड में अपनी एक्सेल फ़ाइल के साथ काम करने के लिए स्प्रेडशीटडॉक्यूमेंट ऑब्जेक्ट का उपयोग नहीं कर सकते हैं - तो आइए इसे अपने स्वयं के पैकेज ऑब्जेक्ट के साथ क्रिएट मोड में उपयोग करें। यह हुड के नीचे बगी ज़िपआर्काइव का उपयोग नहीं करेगा और जैसा होना चाहिए वैसा ही काम करेगा।

(चेतावनी: यह कोड अब केवल 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);

मेट्रिक्स

अब, हमारे मापन प्रारंभिक मापनों की तुलना में संतोषजनक प्रतीत होते हैं।

अंतिम विचार

प्रारंभ में, प्रदर्शित कोड विशुद्ध रूप से प्रदर्शनात्मक उद्देश्यों को पूरा करता है। व्यावहारिक अनुप्रयोगों में, विभिन्न सेल प्रकारों के लिए समर्थन या सेल शैलियों की प्रतिकृति जैसी अतिरिक्त सुविधाओं पर विचार किया जाना चाहिए। पिछले उदाहरण में प्रदर्शित महत्वपूर्ण अनुकूलन के बावजूद, वास्तविक दुनिया के परिदृश्यों में इसका प्रत्यक्ष अनुप्रयोग संभव नहीं हो सकता है। आम तौर पर, बड़ी एक्सेल फ़ाइलों को संभालने के लिए, चंक-आधारित दृष्टिकोण अधिक उपयुक्त होता है।

पुनश्च: यदि आप कार्यालय दस्तावेज़ बनाने की पेचीदगियों में उलझने से बचना चाहते हैं, तो आप मेरे NuGet पैकेज का उपयोग कर सकते हैं, जो इन सभी कार्यात्मकताओं को सरल और एकीकृत करता है।

फ़ीचर छवि vecstock द्वारा Freepik पर