C# 使用OpenXML SDK 大数据量下如何高效迭代并快速写入Excel
大数据量Excel导出性能优化方案
现有实现现状
当前实现的核心逻辑为:从SQL查询拉取全量数据存入内存List集合,通过两层foreach循环逐行写入Excel工作簿,小数据量场景可正常运行,数据量上升后会出现运行失效、卡顿甚至崩溃的问题,目标是找到最优的数据迭代方式,实现最快速度的Excel工作簿写入。
原始实现代码如下:
private UInt32 AddSheets(SpreadsheetDocument document, WorkbookPart workbookPart, Sheets sheets, string sheetName, UInt32 sheetNumber, List<RentalFeeReportingRecord> currentRecordList) { Dictionary<int, string> listofCoreBankingIds = new Dictionary<int, string>(); listofCoreBankingIds = currentRecordList.Select(x => new { id = x.CoreBankingId, regionName = x.RegionName }).Distinct().ToDictionary(y => y.id, y => y.regionName); foreach (var coreBankingId in listofCoreBankingIds.OrderBy(x => x.Value)) { WorksheetPart worksheetPart1 = workbookPart.AddNewPart<WorksheetPart>(); string currentCurrency = null; string regionName = null; Worksheet worksheet1 = new Worksheet(); SheetData sheetData1 = new SheetData(); Row rowInSheet1 = new Row(); Row firstRow = new Row(); Row lastRow = new Row(); rowInSheet1.Append( excelController.ConstructCell("Region", CellValues.String, 3), excelController.ConstructCell("Merchant - Terminal", CellValues.String, 3), excelController.ConstructCell("Fee Charged", CellValues.String, 3), excelController.ConstructCell("Currency", CellValues.String, 3), excelController.ConstructCell("Processing Date", CellValues.String, 3), excelController.ConstructCell("Description", CellValues.String, 3) ); sheetData1.Append(rowInSheet1); foreach (var currentRecord in currentRecordList.OrderBy(x => x.RegionName).ThenBy(z => z.Currency).ThenBy(y => y.DateProcessed)) { if (coreBankingId.Key == currentRecord.CoreBankingId) { regionName = currentRecord.RegionName; currentCurrency = currentRecord.Currency; double currentTotal = currentRecord.TotalFeeAmount; string merchant = currentRecord.MerchantRecord.MerchantID + " - " + currentRecord.MerchantTerminalRecord.terminalID; if (currentRecord.IsPinPad) { merchant = merchant + " - PIN Pad"; } string description = currentRecord.TerminalRecord.description; if (currentRecord.IsProRated) { int days = DateTime.DaysInMonth(currentRecord.DeployedDate.Year, currentRecord.DeployedDate.Month); description = currentRecord.TerminalRecord.description + " (" + currentRecord.MonthlyFee + " Fee \\" + days.ToString() + " Days) * " + currentRecord.DaysActive.ToString() + " Days Active"; } rowInSheet1 = new Row(); firstRow = new Row(); lastRow = new Row(); rowInSheet1.Append( excelController.ConstructCell(regionName, CellValues.String, 2), excelController.ConstructCell(merchant, CellValues.String, 2), excelController.ConstructCell(currentRecord.TotalFee.ToString("N2"), CellValues.String, 1), excelController.ConstructCell(currentRecord.Currency, CellValues.String, 2), excelController.ConstructCell(currentRecord.DateProcessed.ToString("MMM dd, yyyy"), CellValues.String, 2), excelController.ConstructCell(description, CellValues.String, 2) ); sheetData1.Append(rowInSheet1); } } Columns columns = excelController.AutoSize(sheetData1); worksheet1.AppendChild(columns); worksheet1.AppendChild(sheetData1); worksheetPart1.Worksheet = worksheet1; Sheet sheet1 = new Sheet() { Id = document.WorkbookPart.GetIdOfPart(worksheetPart1), SheetId = sheetNumber, Name = regionName }; sheets.Append(sheet1); sheetNumber++; } return sheetNumber; }
核心性能瓶颈
- 嵌套循环冗余遍历:外层遍历每个银行核心ID创建工作表时,内层循环对全量数据做一次完整扫描匹配,整体遍历复杂度为O(N*M)(N为不同银行核心ID数量,M为总数据量),数据量越大重复遍历的计算开销越高。
- 全量内存驻留压力过高:当前基于OpenXML DOM的写入模式需要将所有行、单元格对象全部加载到内存中,最终一次性序列化写入文件,数据量达到十万级以上时极易触发GC频繁回收、内存溢出,导致程序无响应。
- 无意义对象创建开销:循环内重复实例化
firstRow、lastRow等从未被引用的对象,额外占用内存、增加GC回收负担。 - 全量列宽计算拖慢速度:所有行写入完成后才执行整表的
AutoSize列宽计算,需要遍历每个单元格的内容长度,百万行级数据量下该计算耗时会达到分钟级。
最优实现方案
1. 数据迭代逻辑优化
- 提前对原始全量List做一次分组操作:按
CoreBankingId将数据拆分为独立分组,创建工作表时直接读取对应分组的数据,将原本O(N*M)的嵌套遍历复杂度降为O(M)的单次全量遍历。 - 在分组阶段直接完成排序:对分组内的数据按
RegionName、Currency、DateProcessed完成排序,避免后续重复执行排序操作。
2. 流式写入替代DOM全量加载
- 基于OpenXML的
OpenXmlWriter实现逐行流式写入:生成一行数据的单元格后直接序列化写入到对应的工作表部件中,无需将所有行节点驻留内存,写入完成即可释放当前行的内存占用,从根源上解决大数据量下的内存溢出问题。 - 列宽计算边写边完成:逐行写入时累计每个列的内容最大字符长度,无需写完所有数据后再回头全量扫描计算列宽,降低计算开销。
3. 冗余代码清理
- 移除循环内所有未被使用的对象实例、临时变量(如未被引用的
firstRow、lastRow、currentTotal、currentCurrency冗余变量),减少临时对象生成量。 - 复用重复的单元格构造逻辑,避免重复生成字符串对象。
内容的提问来源于stack exchange,提问作者Adil15
相关产品推荐
相关产品推荐

