您现在的位置：程式碼之家 >> 編程語言 > >> .NET 網頁程式設計 >> ASP.NET 基礎 >> ASP.NET 基礎

ASP.NET Excel 匯出六大實戰方案：OleDb 建表寫入、NPOI/EPPlus 記憶體串流、大數據分頁、樣式格式化完整比較

編輯：ASP.NET 基礎

方案對照表

方案	核心技術	優點	缺點	適用場景
1. OleDb 建表	Microsoft.ACE.OLEDB.12.0 + CREATE TABLE/INSERT	真實 .xls，支援舊版 Excel	需安裝驅動、32/64 位坑、效能差、不支援 .xlsx 新特性	舊系統維護、必須產 .xls
2. NPOI (HSSF/XSSF)	Apache POI 移植，純 Managed	無 Office 安裝、支援 .xls/.xlsx、樣式豐富、記憶體可控	大資料需 SXSSF 串流、API 複雜	通用首選、中大型專案
3. EPPlus	OpenXML SDK 封裝，功能最豐富	語法優雅、樣式/圖表/樞紐分析表完整、效能好	v5+ 商業授權、僅 .xlsx	新專案、複雜報表、團隊預算允許
4. ClosedXML	OpenXML 封裝，API 更直覺	語法極簡、MIT 授權、效能佳	功能較 EPPlus 少	輕量報表、開源專案
5. GridView/HtmlTable 渲染	Control.RenderControl + Response.ContentType	零依賴、幾行代碼	無樣式控制、數字被轉科學記號、大資料 OOM	極簡臨時匯出、內部工具
6. CSV 純文字	StringBuilder + Response.BinaryWrite	最快、最省記憶體、跨平台	無樣式、無多工作表、編碼易亂碼	百萬列以上、僅供程式讀取

方案一：OleDb 建表寫入（原文重構版）

缺點：需安裝 Access Database Engine、連接字串 32/64 位必須與 IIS 應用程式池一致、逐列 INSERT 極慢。


public void ExportByOleDb(DataTable dt, string fileName)
{
    string path = Server.MapPath($"~/Temp/{fileName}.xls");
    if (File.Exists(path)) File.Delete(path);
    
    string connStr = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={path};Extended Properties='Excel 8.0;HDR=YES;'";
    using (var conn = new OleDbConnection(connStr))
    using (var cmd = conn.CreateCommand())
    {
        conn.Open();
        // 動態建表
        var cols = dt.Columns.Cast()
            .Select(c => $"[{c.ColumnName}] {(c.DataType == typeof(string) ? "TEXT" : c.DataType == typeof(DateTime) ? "DATETIME" : "DOUBLE")}");
        cmd.CommandText = $"CREATE TABLE [Sheet1] ({string.Join(",", cols)})";
        cmd.ExecuteNonQuery();
        
        // 批次參數化插入（仍然逐列）
        cmd.CommandText = $"INSERT INTO [Sheet1] VALUES ({string.Join(",", dt.Columns.Cast().Select((_,i)=>"?"))})";
        foreach (DataRow row in dt.Rows)
        {
            cmd.Parameters.Clear();
            foreach (var val in row.ItemArray) cmd.Parameters.AddWithValue("", val ?? DBNull.Value);
            cmd.ExecuteNonQuery();
        }
    }
    // 輸出下載...
}

方案二：NPOI 記憶體串流匯出（推薦標準版）


public void ExportByNPOI(DataTable dt, string fileName)
{
    IWorkbook workbook = new XSSFWorkbook(); // .xlsx；.xls 用 new HSSFWorkbook()
    ISheet sheet = workbook.CreateSheet("Data");
    
    // 樣式：標題列
    ICellStyle headerStyle = workbook.CreateCellStyle();
    headerStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
    headerStyle.FillPattern = FillPattern.SolidForeground;
    IFont headerFont = workbook.CreateFont(); headerFont.Boldweight = (short)FontBoldWeight.Bold;
    headerStyle.SetFont(headerFont);
    
    // 樣式：日期格式
    ICellStyle dateStyle = workbook.CreateCellStyle();
    dateStyle.DataFormat = workbook.CreateDataFormat().GetFormat("yyyy-MM-dd HH:mm:ss");
    
    // 寫入標題
    IRow headerRow = sheet.CreateRow(0);
    for (int i = 0; i < dt.Columns.Count; i++)
    {
        headerRow.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
        headerRow.GetCell(i).CellStyle = headerStyle;
    }
    
    // 寫入資料
    for (int r = 0; r < dt.Rows.Count; r++)
    {
        IRow row = sheet.CreateRow(r + 1);
        for (int c = 0; c < dt.Columns.Count; c++)
        {
            var cell = row.CreateCell(c);
            object val = dt.Rows[r][c];
            if (val == null || val == DBNull.Value) { cell.SetCellValue(""); continue; }
            
            switch (val)
            {
                case DateTime dtVal: cell.SetCellValue(dtVal); cell.CellStyle = dateStyle; break;
                case double dVal: cell.SetCellValue(dVal); break;
                case int iVal: cell.SetCellValue(iVal); break;
                case bool bVal: cell.SetCellValue(bVal); break;
                default: cell.SetCellValue(val.ToString()); break;
            }
        }
    }
    
    // 自動欄寬
    for (int i = 0; i < dt.Columns.Count; i++) sheet.AutoSizeColumn(i);
    
    // 記憶體串流輸出
    using (var ms = new MemoryStream())
    {
        workbook.Write(ms);
        ms.Position = 0;
        Response.Clear();
        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        Response.AddHeader("Content-Disposition", $"attachment;filename={fileName}.xlsx");
        Response.BinaryWrite(ms.ToArray());
        Response.End();
    }
}

方案三：NPOI SXSSF 大數據串流（百萬列不 OOM）


public void ExportBigDataByNPOI(IEnumerable rows, string fileName)
{
    // SXSSFWorkbook 只保留滾動窗口在記憶體，其餘刷入磁碟暫存
    var workbook = new SXSSFWorkbook(100); // 保留 100 列在記憶體
    var sheet = workbook.CreateSheet("BigData");
    // ... 樣式建立同標準版 ...
    
    int rowIdx = 0;
    var headerRow = sheet.CreateRow(rowIdx++);
    // 寫標題...
    
    foreach (var dr in rows)
    {
        var row = sheet.CreateRow(rowIdx++);
        // 寫資料...
        if (rowIdx % 1000 == 0) sheet.FlushRows(100); // 定期刷盤
    }
    
    using (var ms = new MemoryStream())
    {
        workbook.Write(ms);
        // 輸出...
    }
    workbook.Dispose(); // 清理暫存檔
}

方案四：EPPlus 現代優雅寫法（需授權）


public void ExportByEPPlus(DataTable dt, string fileName)
{
    using (var pkg = new ExcelPackage())
    {
        var ws = pkg.Workbook.Worksheets.Add("Report");
        // LoadFromDataTable 一行搞定：含標題、型別推斷、自動欄寬
        ws.Cells["A1"].LoadFromDataTable(dt, true, TableStyles.Medium9);
        
        // 進階：欄位格式化
        ws.Column(3).Style.Numberformat.Format = "yyyy-MM-dd HH:mm:ss";
        ws.Column(4).Style.Numberformat.Format = "#,##0.00";
        
        // 輸出
        Response.Clear();
        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        Response.AddHeader("Content-Disposition", $"attachment;filename={fileName}.xlsx");
        Response.BinaryWrite(pkg.GetAsByteArray());
        Response.End();
    }
}

方案五：GridView 快速匯出（零依賴、極簡）


public void ExportByGridView(DataTable dt, string fileName)
{
    var gv = new GridView { DataSource = dt, AutoGenerateColumns = true };
    gv.DataBind();
    
    Response.Clear();
    Response.ContentType = "application/vnd.ms-excel";
    Response.AddHeader("Content-Disposition", $"attachment;filename={fileName}.xls");
    Response.Charset = "UTF-8";
    Response.ContentEncoding = Encoding.UTF8;
    
    using (var sw = new StringWriter())
    using (var hw = new HtmlTextWriter(sw))
    {
        gv.RenderControl(hw);
        Response.Write(sw.ToString());
    }
    Response.End();
}
// 需在頁面類別覆寫：
public override void VerifyRenderingInServerForm(Control control) { }

方案六：CSV 高效能匯出（百萬列秒級）


public void ExportCsv(DataTable dt, string fileName)
{
    Response.Clear();
    Response.ContentType = "text/csv";
    Response.AddHeader("Content-Disposition", $"attachment;filename={fileName}.csv");
    Response.ContentEncoding = Encoding.GetEncoding("GB2312"); // Excel 相容中文
    
    var sb = new StringBuilder();
    // BOM 防 UTF-8 亂碼
    Response.Write("\uFEFF");
    
    // 標題
    sb.AppendLine(string.Join(",", dt.Columns.Cast().Select(c => Quote(c.ColumnName))));
    // 資料
    foreach (DataRow row in dt.Rows)
    {
        sb.AppendLine(string.Join(",", row.ItemArray.Select(v => Quote(v?.ToString() ?? ""))));
        if (sb.Length > 100000) { Response.Write(sb.ToString()); sb.Clear(); } // 分塊輸出
    }
    Response.Write(sb.ToString());
    Response.End();
}

private string Quote(string s) => $"\"{s.Replace("\"", "\"\"")}\"";

統一下載輸出輔助方法


protected void DownloadFile(byte[] content, string fileName, string contentType)
{
    Response.Clear();
    Response.Buffer = true;
    Response.ContentType = contentType;
    Response.AddHeader("Content-Disposition", $"attachment;filename={HttpUtility.UrlEncode(fileName, Encoding.UTF8)}");
    Response.AddHeader("Content-Length", content.Length.ToString());
    Response.BinaryWrite(content);
    Response.Flush();
    Response.End(); // 或 HttpContext.Current.ApplicationInstance.CompleteRequest();
}

選型決策樹

資料量 < 5 萬列、需豐富樣式/圖表？ → EPPlus (預算允許) / NPOI / ClosedXML
資料量 5萬~100萬、記憶體敏感？ → NPOI SXSSF 串流模式
資料量 > 100萬、僅供程式處理？ → CSV 分塊輸出
極簡內部工具、無樣式需求？ → GridView RenderControl / CSV
舊系統強制 .xls？ → NPOI HSSFWorkbook / OleDb (最後手段)

常見坑點避雷

數字被科學記號：NPOI/EPPlus 設定 `CellStyle.DataFormat = "@"` (文字格式) 或匯出前轉字串
日期顯示亂碼：統一 `yyyy-MM-dd HH:mm:ss` 格式，勿依賴預設
中文檔名下載亂碼：`HttpUtility.UrlEncode(fileName, Encoding.UTF8)` + `Response.HeaderEncoding = Encoding.UTF8`
IIS 回應緩衝區限制：大檔案分塊 `Response.Flush()`、關閉 `Response.Buffer`
EPPlus 5+ 授權：商業專案購買授權或改用 EPPlus 4.5.3.3 (LGPL) / ClosedXML / NPOI