Cách tạo bộ dữ liệu lớn trong .NET cho Excel bằng OpenXML

• Tầm quan trọng của báo cáo Excel
• Cách tiếp cận phổ biến để tạo tệp Excel
• Truyền tập dữ liệu lớn trong Excel
• Rò rỉ bộ nhớ bất ngờ: làm sáng tỏ Bí ẩn
• Suy nghĩ cuối cùng

Tầm quan trọng của báo cáo Excel

Ở các công ty doanh nghiệp lớn, việc tạo báo cáo Excel đã trở thành một quy trình không thể thiếu để quản lý và phân tích các bộ dữ liệu mở rộng một cách hiệu quả. Những báo cáo này rất quan trọng để theo dõi các số liệu hiệu suất, hồ sơ tài chính và thống kê hoạt động, cung cấp những hiểu biết sâu sắc có giá trị thúc đẩy việc ra quyết định chiến lược.

Trong những môi trường như vậy, các công cụ tự động hóa tạo ra các tệp này đóng vai trò then chốt trong việc hợp lý hóa việc tạo báo cáo và đảm bảo độ chính xác. Khi chúng ta bước sang năm 2024, khả năng tạo tệp Excel sẽ là một nhiệm vụ dễ dàng và phổ biến, phải không?

Phương pháp phổ biến để tạo tệp Excel

Để tạo tệp Excel với tập dữ liệu của riêng bạn, chúng tôi sẽ sử dụng thư viện OpenXML . Điều đầu tiên bạn nên làm là cài đặt thư viện này vào dự án của mình:

 dotnet add package DocumentFormat.OpenXml

Sau khi cài đặt thư viện cần thiết và tạo tệp Excel mẫu có tên “Test.xlsx”, chúng tôi đã thêm mã này vào ứng dụng của mình:

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

Và đó là cách sử dụng đoạn mã trên:

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

Số liệu

Trong bảng này, chúng tôi đã thử kiểm tra hàm của mình với số lượng hàng khác nhau. Đúng như mong đợi - số lượng hàng tăng lên sẽ dẫn đến hiệu suất giảm. Để khắc phục điều đó, chúng ta có thể thử một cách tiếp cận khác.

Truyền bộ dữ liệu lớn trong Excel

Cách tiếp cận được trình bày ở trên là đơn giản và đủ cho các tập dữ liệu nhỏ. Tuy nhiên, như được minh họa trong bảng, việc xử lý các tập dữ liệu lớn có thể chậm đáng kể. Phương pháp này liên quan đến các thao tác DOM vốn đã chậm. Trong những trường hợp như vậy, cách tiếp cận SAX (API đơn giản cho XML) trở nên vô giá. Đúng như tên gọi, SAX cho phép chúng ta làm việc trực tiếp với XML của tài liệu Excel, cung cấp giải pháp hiệu quả hơn để xử lý các tập dữ liệu lớn.

Thay đổi mã từ ví dụ đầu tiên sang ví dụ này:

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

Giải thích : Mã này đọc từng phần tử XML từ tệp Excel nguồn và sao chép từng phần tử của nó sang một trang tính mới. Sau một số thao tác với dữ liệu, nó sẽ xóa trang tính cũ và lưu trang tính mới.

Số liệu

Như bạn có thể thấy, tốc độ xử lý một số lượng lớn hàng đã tăng lên đáng kể. Tuy nhiên, hiện tại chúng tôi có một vấn đề về bộ nhớ cần giải quyết.

Rò rỉ bộ nhớ bất ngờ: Làm sáng tỏ bí ẩn

Một người quan sát sáng suốt có thể nhận thấy mức tiêu thụ bộ nhớ tăng đột biến khi xử lý 10 triệu ô trong Excel. Mặc dù trọng lượng của 1 triệu dây là đáng kể nhưng nó không thể gây ra sự gia tăng đáng kể như vậy. Sau khi điều tra tỉ mỉ bằng các trình phân tích bộ nhớ, thủ phạm đã được xác định trong thư viện OpenXML.

Cụ thể, nguyên nhân gốc rễ có thể bắt nguồn từ một lỗ hổng trong gói .NET System.IO.Packaging, ảnh hưởng đến cả phiên bản .NET Standard và .NET Core. Điều thú vị là vấn đề này dường như không có trong .NET cổ điển, có thể là do sự khác biệt trong mã Windows Base cơ bản. Một thời gian ngắn, thư viện OpenXML sử dụng ZipArchive trong đó để sao chép dữ liệu trong MemoryStream mỗi khi bạn cập nhật tệp.

Nó chỉ xảy ra nếu bạn mở nó ở chế độ cập nhật, nhưng bạn không thể thực hiện theo cách khác vì đó là hành vi của chính .NET.

Đối với những người muốn tìm hiểu sâu hơn về vấn đề này, bạn có thể tìm thêm thông tin chi tiết tại GitHub Issue #23750 .

Sau đó, sau khi nghiên cứu kỹ mã nguồn .NET và tham khảo ý kiến của các đồng nghiệp đang gặp phải những thách thức tương tự, tôi đã nghĩ ra một giải pháp giải quyết. Nếu chúng ta không thể sử dụng đối tượng SpreadsheetDocument để làm việc với tệp Excel ở chế độ Mở - hãy sử dụng nó trong chế độ Tạo với đối tượng Gói của riêng chúng ta. Nó sẽ không sử dụng ZipArchive có nhiều lỗi và sẽ hoạt động như bình thường.

(Cảnh báo: mã này hiện chỉ hoạt động với OpenXML v.2.19.0 trở về trước).

Thay đổi mã của chúng tôi thành thế này:

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

Và sử dụng nó như thế này:

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

Số liệu

Bây giờ, các phép đo của chúng tôi có vẻ thỏa đáng so với các phép đo ban đầu.

suy nghĩ cuối cùng

Ban đầu, mã được hiển thị chỉ nhằm mục đích minh họa. Trong các ứng dụng thực tế, cần xem xét các tính năng bổ sung như hỗ trợ cho các loại ô khác nhau hoặc sao chép các kiểu ô. Mặc dù đã có những tối ưu hóa đáng kể được thể hiện trong ví dụ trước, nhưng việc áp dụng trực tiếp nó vào các tình huống thực tế có thể không khả thi. Thông thường, để xử lý các tệp Excel lớn, cách tiếp cận dựa trên chunk sẽ phù hợp hơn.

Tái bút: Nếu bạn muốn tránh đi sâu vào sự phức tạp của việc tạo tài liệu văn phòng, bạn có thể khám phá gói NuGet của tôi, gói này giúp đơn giản hóa và tích hợp tất cả các chức năng này một cách liền mạch.

Hình ảnh nổi bật của vecstock trên Freepik