前言

最近經歷了一次數據量比較大的導出，也做了各種優化嘗試，這里稍記錄一下

一、DataTable =>EXCEL

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text;
using System.Threading.Tasks;
using DataTable = System.Data.DataTable;
using Microsoft.Office.Interop.Excel;namespace ClassLibrary
{public class ExcelHelper{/// <summary>/// dt輸出到新的Excel/// </summary>public static void OutPut(DataTable dt,string Path, string Filename){// 初始化 Excel 應用程序Application excelApp = new Application();Workbook workbook = null;Worksheet worksheet = null;try{// 設置可見性和默認路徑excelApp.Visible = false; // 隱藏應用程序窗口if (!Directory.Exists(Path)) // 驗證文件夾是否存在{Directory.CreateDirectory(Path); // 若不存在，則創建新文件夾}// 添加一個新的工作簿workbook = excelApp.Workbooks.Add();// 獲取第一個工作表worksheet = (Worksheet)workbook.Sheets[1];//列名for (int i = 0; i < dt.Columns.Count; i++){worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;}int rows = 2;//當前行// 獲取 DataTable 列數和行數int rowCount = dt.Rows.Count;int colCount = dt.Columns.Count;// 構建數據部分的二維數組if (dt.Rows.Count > 0){object[,] dataValues = new object[rowCount, colCount];for (int r = 0; r < rowCount; r++){for (int c = 0; c < colCount; c++){dataValues[r, c] = dt.Rows[r][c];}}// 定義目標范圍（從 (2, 2) 開始）Range startCell = (Range)worksheet.Cells[2, 1]; // 起始單元格Range endCell = (Range)worksheet.Cells[2 + rowCount - 1, 1 + colCount - 1]; // 結束單元格Range targetRange = worksheet.get_Range(startCell, endCell);// 寫入數據targetRange.Value = dataValues;// 自動調整列寬worksheet.Columns.AutoFit();}//寬度自適應workbook.SaveAs(Filename);workbook.Close(false);}catch (Exception ex){return;}finally{if (worksheet != null) Marshal.ReleaseComObject(worksheet);if (workbook != null) Marshal.ReleaseComObject(workbook);if (excelApp != null){excelApp.Quit();Marshal.ReleaseComObject(excelApp);}GC.Collect();GC.WaitForPendingFinalizers();}}}
}

.netframework4.8,支持的Microsoft.Office.Interop.Excel。
將DataTable轉換為二維數組，劃定范圍，一次性塞進去。
比逐單元格賦值效率更高

二、DBReader =>Excel （NPOI）

using NPOI.SS.UserModel;
using NPOI.XSSF.Streaming;
using System.Data;
using System.Data.Common;
public class ExcelHelper
{public static async Task<bool> OutPut(DbDataReader reader, string filePath){try{const int maxRowsPerSheet = 1_000_000;  // 每個Sheet最大行數const int bufferSize = 1000;            // 行緩存大小（優化內存）// 初始化流式工作簿（關鍵內存優化）using var workbook = new SXSSFWorkbook(null, bufferSize, true);int sheetIndex = 1;ISheet currentSheet = workbook.CreateSheet($"Sheet_{sheetIndex}");// 創建標題行IRow headerRow = currentSheet.CreateRow(0);for (int i = 0; i < reader.FieldCount; i++){headerRow.CreateCell(i).SetCellValue(reader.GetName(i));}int rowIndex = 1;  // 數據行從1開始（0是標題行）while (reader.Read()){// 達到最大行數時切換Sheetif (rowIndex >= maxRowsPerSheet){// 正確調用無參數FlushRows（NPOI 2.7.3+）((SXSSFSheet)currentSheet).FlushRows();  // 刷新當前Sheet[^1]sheetIndex++;currentSheet = workbook.CreateSheet($"Sheet_{sheetIndex}");rowIndex = 1;  // 新Sheet重置行索引}// 創建數據行IRow dataRow = currentSheet.CreateRow(rowIndex);// 寫入所有列數據（類型安全處理）for (int col = 0; col < reader.FieldCount; col++){var cell = dataRow.CreateCell(col);// 根據數據類型安全寫入if (reader.IsDBNull(col)){cell.SetCellValue((string)null);}else{switch (Type.GetTypeCode(reader.GetFieldType(col))){case TypeCode.String:cell.SetCellValue(reader.GetString(col));break;case TypeCode.DateTime:cell.SetCellValue(reader.GetDateTime(col));break;case TypeCode.Int16:cell.SetCellValue(reader.GetInt16(col));break;case TypeCode.Int32:cell.SetCellValue(reader.GetInt32(col));break;case TypeCode.Int64:cell.SetCellValue(reader.GetInt64(col));break;// case TypeCode.Decimal:// cell.SetCellValue(reader.GetDecimal(col));// break;case TypeCode.Double:cell.SetCellValue(reader.GetDouble(col));break;case TypeCode.Single:cell.SetCellValue(reader.GetDouble(col));break;case TypeCode.Boolean:cell.SetCellValue(reader.GetBoolean(col));break;default:cell.SetCellValue(reader.GetValue(col).ToString());break;}}}rowIndex++;}// 最終寫入文件（使用異步提升性能）using var fs = new FileStream(filePath, FileMode.Create, FileAccess.Write, FileShare.None,81920, true);// 分塊寫入策略var writeTask = Task.Run(() => workbook.Write(fs, false));// 進度刷新控制while (!writeTask.IsCompleted){if (fs.Position % (10 * bufferSize) == 0){await fs.FlushAsync(); // 異步刷新緩沖區}await Task.Delay(50); // 減少CPU占用}await writeTask;return true;}catch (Exception ex){return false;}}
}

using Microsoft.Data.SqlClient;
using (DbDataReader reader = await cmd.ExecuteReaderAsync())
{return  await ExcelHelper.OutPut(reader, extra);
}
//查詢數據后直接塞進去就行

DBReader=>DataTable=>json=>datable =>excel 通過API 傳輸過于占用資源，
優化：
DBReader=>excel ,直接輸出到服務器共享文件夾。
流輸出效率更改、省略傳輸轉換、異步內存清除、百萬條分Sheet兼容2007。

三、分頁查詢 DbReader=>Excel (MiniExcel)

using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using MiniExcelLibs;
namespace ExcelExport
{public class ExcelHelper{public void GetData(){SqlConnection sqlCon;try{var sheets = new Dictionary<string, object>();string excelFilePath = @"D:\Excel\DataFile.xlsx";string connectionString = ConnectionString;SqlCommand sqlcmd;sqlCon = new SqlConnection(connectionString);sqlCon.Open();sqlcmd = new SqlCommand(sql, sqlCon);//先取頁數 =符合條件總條數/每頁條數 maxLoopCounter = Convert.ToInt32(sqlcmd.ExecuteScalar());for (int i = 1; i <= maxLoopCounter; i++) //循環查詢每頁數據{string sheetName = "Sheet" + i.ToString();//  string sheetName = "Sheet5" ;sqlCon = new SqlConnection(connectionString);sqlCon.Open();string sql = "";sqlcmd = new SqlCommand(sql, sqlCon);sheets.Add(sheetName, sqlcmd.ExecuteReader());//按頁寫入sheet}MiniExcel.SaveAs(excelFilePath, sheets);}catch (Exception exception){}}}
}

SELECT *
FROM BigDataTable
ORDER BY CreateDate DESC
OFFSET @PageIndex * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY; 

可惜的是FETCH 只支持SQL2012+,低版本就只有轉存實體表的方式：

declare @ntile_value int =1000000 //一百萬一頁
SELECT id, NTILE(@ntile_value) OVER (ORDER BY id) AS page  INTO temp_BigTable FROM BigTable;
--使用 NTILE 添加頁碼字段->寫入新表
--個人覺得建表、清表、表占用判斷，也挺麻煩

總結：

服務器流導出更快，使用NPOI、和MiniExcel等都是異曲同工。數據量百萬級，我覺得應用層分頁更好，數據更大那數據庫分頁后傳輸更好。Interop作為微軟官方com組件確實更方便，但跨平臺就不行了。總的來說NPOI確實各方面更適用一些。