কিভাবে 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 আমাদেরকে এক্সেল ডকুমেন্টের 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 এ 10 মিলিয়ন কোষ প্রক্রিয়া করার সময় মেমরি খরচে একটি অপ্রত্যাশিত বৃদ্ধি লক্ষ্য করেছেন। যদিও 1 মিলিয়ন স্ট্রিংয়ের ওজন যথেষ্ট, তবে এটি এত বড় বৃদ্ধির জন্য দায়ী করা উচিত নয়। মেমরি প্রোফাইলারদের সাথে সূক্ষ্ম তদন্তের পরে, অপরাধীকে OpenXML লাইব্রেরির মধ্যে সনাক্ত করা হয়েছিল।

বিশেষত, মূল কারণটি .NET প্যাকেজ সিস্টেম.IO.প্যাকেজিং-এর একটি ত্রুটির জন্য চিহ্নিত করা যেতে পারে, যা .NET স্ট্যান্ডার্ড এবং .NET কোর সংস্করণ উভয়কেই প্রভাবিত করে৷ মজার বিষয় হল, এই সমস্যাটি ক্লাসিক .NET-এ অনুপস্থিত বলে মনে হচ্ছে, সম্ভবত অন্তর্নিহিত উইন্ডোজ বেস কোডের পার্থক্যের কারণে। শীঘ্রই, OpenXML লাইব্রেরি এটিতে ZipArchive ব্যবহার করে, যা আপনি ফাইল আপডেট করার সময় প্রতিবার MemoryStream-এ ডেটা কপি করে।

আপনি এটিকে আপডেট মোডে খুললেই এটি ঘটে, কিন্তু আপনি এটি অন্য উপায়ে করতে পারবেন না কারণ এটি .NET-এর আচরণ।

যারা এই ইস্যুতে আরও গভীরে যেতে আগ্রহী তাদের জন্য GitHub Issue #23750 এ আরও বিশদ পাওয়া যাবে।

পরবর্তীকালে, .NET সোর্স কোডের উপর পোরিং করার পরে এবং অনুরূপ চ্যালেঞ্জের মুখোমুখি সমবয়সীদের সাথে পরামর্শ করার পরে, আমি একটি সমাধানের সমাধান তৈরি করেছি। আমরা যদি ওপেন মোডে আমাদের এক্সেল ফাইলের সাথে কাজ করার জন্য SpreadsheetDocument অবজেক্ট ব্যবহার করতে না পারি - আসুন এটি আমাদের নিজস্ব প্যাকেজ অবজেক্টের সাথে তৈরি মোডে ব্যবহার করি। এটি হুডের নিচে বগি জিপআর্কাইভ ব্যবহার করবে না এবং এটি যেমন করা উচিত তেমন কাজ করবে।

(সতর্কতা: এই কোডটি এখন শুধুমাত্র 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);

মেট্রিক্স

এখন, আমাদের পরিমাপ প্রাথমিকের তুলনায় সন্তোষজনক দেখাচ্ছে।

সর্বশেষ ভাবনা

প্রাথমিকভাবে, শোকেস কোডটি সম্পূর্ণরূপে প্রদর্শনমূলক উদ্দেশ্যে কাজ করে। ব্যবহারিক অ্যাপ্লিকেশনগুলিতে, অতিরিক্ত বৈশিষ্ট্যগুলি যেমন বিভিন্ন ধরণের কোষের জন্য সমর্থন বা কোষের শৈলীগুলির প্রতিলিপি বিবেচনা করা উচিত। পূর্ববর্তী উদাহরণে প্রদর্শিত উল্লেখযোগ্য অপ্টিমাইজেশন সত্ত্বেও, বাস্তব-বিশ্বের পরিস্থিতিতে এর সরাসরি প্রয়োগ সম্ভব নয়। সাধারণত, বড় এক্সেল ফাইলগুলি পরিচালনা করার জন্য, একটি খণ্ড-ভিত্তিক পদ্ধতি বেশি উপযুক্ত।

PS: আপনি যদি অফিসের নথি তৈরির জটিলতাগুলি এড়াতে পছন্দ করেন তবে আপনাকে আমার NuGet প্যাকেজটি অন্বেষণ করতে স্বাগত জানাই, যা এই সমস্ত কার্যকারিতাকে নির্বিঘ্নে সহজ করে এবং সংহত করে৷

Freepik-এ vecstock দ্বারা ফিচার ইমেজ