突破Excel百万数据导出瓶颈：全链路优化实战指南

//配置优化参数var config = new OpenXmlConfiguration{ EnableSharedStrings =false,//关闭共享字符串表 AutoFilterMode = AutoFilterMode.None, //禁用自动筛选 FillMergedCells = false//不处理合并单元格};//分页流式写入awaitMiniExcel.SaveAsAsync("output.xlsx", GetDataChunks(), configuration: config);IEnumerable<IDictionary<string,object>>GetDataChunks(){var pageSize = 50000;for(int page = 0; ; page++) { var data = QueryDatabase(page * pageSize, pageSize); if(!data.Any())yieldbreak;foreach(varitemin data) { yieldreturnnewDictionary<string,object> { ["ID"] = item.Id, ["Name"] = item.Name, ["CreateTime"] = item.CreateTime.ToString("yyyy-MM-dd") }; } }}

using(var package = newExcelPackage()){var sheet = package.Workbook.Worksheets.Add("Data");int row = 1;//批量写入头信息sheet.Cells["A1:C1"].LoadFromArrays(new[] { new[] { "ID","Name","CreateTime" } }); //分块写入（每50000行保存一次）foreach(varchunkinGetDataChunks(50000)) { sheet.Cells[row+1,1].LoadFromCollection(chunk); row +=chunk.Count;if (row % 50000==0) { package.Save(); //分段保存 sheet.Cells.ClearFormulas(); } } package.SaveAs(newFileInfo("output_epplus.xlsx"));}

[BenchmarkDotNet.Attributes.SimpleJob]publicclassExcelBenchmarks{private List<DataModel> _testData = GenerateTestData(1_000_000); [Benchmark] publicvoid MiniExcelExport() => MiniExcel.SaveAs("mini.xlsx", _testData); [Benchmark] publicvoid EPPlusExport() { usingvar pkg = newExcelPackage();var sheet = pkg.Workbook.Worksheets.Add("Data"); sheet.Cells.LoadFromCollection(_testData); pkg.SaveAs("epplus.xlsx"); } [Benchmark] publicvoid NPOIExport() { var workbook = newXSSFWorkbook();var sheet = workbook.CreateSheet("Data");for(int i = 0; i < _testData.Count; i++) { var row = sheet.CreateRow(i); row.CreateCell(0).SetCellValue(_testData[i].Id); row.CreateCell(1).SetCellValue(_testData[i].Name); } usingvar fs = newFileStream("npoi.xlsx", FileMode.Create); workbook.Write(fs); }}

// 先用 EPPlus 创建带样式的模板using(var pkg = newExcelPackage(newFileInfo("template.xlsx"))){var sheet = pkg.Workbook.Worksheets[0]; sheet.Cells["A1"].Value = "动态报表"; pkg.Save();}// 用 MiniExcel 填充大数据量var data =GetBigData();MiniExcel.SaveAsByTemplate("output.xlsx","template.xlsx", data);

publicasync Task ExportShardedDataAsync(){ var totalRecords =5_000_000;var shardSize =100_000;var shards = totalRecords /shardSize;var tasks = newList<Task>();for(int i = 0; i < shards; i++) { var start = i * shardSize; tasks.Add(Task.Run(async () =>{usingvar stream = newFileStream($"shard_{i}.xlsx", FileMode.Create); await MiniExcel.SaveAsAsync(stream, QueryData(start, shardSize)); })); } await Task.WhenAll(tasks); MergeShardFiles(shards);}privatevoidMergeShardFiles(intshardCount){usingvar merger = newExcelPackage();var mergedSheet = merger.Workbook.Worksheets.Add("Data");int row = 1;for(int i = 0; i < shardCount; i++) { var shardData = MiniExcel.Query($"shard_{i}.xlsx"); mergedSheet.Cells[row, 1].LoadFromDictionaries(shardData); row += shardData.Count(); } merger.SaveAs(newFileInfo("final.xlsx"));}

// Program.cs 全局配置AppContext.SetSwitch("System.Buffers.ArrayPool.UseShared",true);//启用共享数组池//运行时配置（runtimeconfig.template.json）{"configProperties": { "System.GC.HeapHardLimit":"0x100000000",// 4GB 内存限制"System.GC.HeapHardLimitPercent":"70","System.GC.Server":true}}

// Dapper 分页优化public IEnumerable<DataModel> GetPagedData(longcheckpoint,intsize){return_conn.Query<DataModel>(@"SELECT Id, Name, CreateTime FROM BigTable WHERE Id > @Checkpoint ORDER BY Id OFFSET 0 ROWS FETCH NEXT @Size ROWS ONLY OPTION (RECOMPILE)",//强制重新编译执行计划new { checkpoint, size });}

try{awaitExportDataAsync();}catch (MiniExcelException ex) when (ex.ErrorCode == "DISK_FULL"){awaitCleanTempFilesAsync();awaitRetryExportAsync();}catch (SqlException ex) when (ex.Number == 1205)//死锁重试{awaitTask.Delay(1000);awaitRetryExportAsync();}finally{ _semaphore.Release(); //释放信号量}

//优化分页查询var lastId = 0;while(true){var data = Query($"SELECT * FROM Table WHERE Id > {lastId} ORDER BY Id FETCH NEXT 50000 ROWS ONLY");if(!data.Any())break; lastId =data.Last().Id;}

try{//核心逻辑}catch (IOException ex) when (ex.Message.Contains("磁盘空间")) { //磁盘异常处理}catch (SqlException ex) when (ex.Number == 1205) { //数据库死锁处理}catch (Exception ex) { //通用异常处理}

//错误示例：未释放ExcelPackagevar pkg = newExcelPackage();//必须包裹在using中pkg.SaveAs("leak.xlsx");//正确用法using(var pkg = newExcelPackage()){//操作代码}

//错误示例：未正确释放资源var workbook = newXSSFWorkbook();//正确用法using(var fs = newFileStream("data.xlsx", FileMode.Create)){ workbook.Write(fs);}

try{awaitExportAsync();}catch (MiniExcelException ex) when (ex.ErrorCode == "DISK_FULL"){ _logger.LogError("磁盘空间不足: {Message}", ex.Message); awaitCleanTempFilesAsync();thrownewUserFriendlyException("导出失败，请联系管理员");}catch (DbException ex) when (ex.IsTransient){ _logger.LogWarning("数据库暂时性错误，尝试重试");awaitTask.Delay(1000);awaitRetryExportAsync();}finally{ _exportSemaphore.Release();}