C# 資料匯出 Excel 完整方案：ASP.NET 與 WinForms 多種實作比較

前言

資料匯出 Excel 是企業應用的剛需功能。本文系統整理 ASP.NET Web 與 WinForms 桌面應用的多種匯出方案，比較 Response.OutputStream、NPOI、EPPlus、ClosedXML、COM Interop 等技術優劣，提供選型指南與最佳實踐。

ASP.NET Web 匯出方案

方案一：HTML 表格輸出 (最簡單，相容性佳)

public void ExportHtmlTable(DataTable dt, string fileName)
{
    HttpContext.Current.Response.Clear();
    HttpContext.Current.Response.Buffer = true;
    HttpContext.Current.Response.Charset = "UTF-8";
    HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
    HttpContext.Current.Response.AppendHeader("Content-Disposition", 
        $"attachment;filename={fileName}.xls");
    HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
    
    StringWriter sw = new StringWriter();
    HtmlTextWriter hw = new HtmlTextWriter(sw);
    
    // 建立 GridView 繫結資料
    GridView gv = new GridView();
    gv.DataSource = dt;
    gv.DataBind();
    gv.RenderControl(hw);
    
    HttpContext.Current.Response.Write(sw.ToString());
    HttpContext.Current.Response.End();
}

優點：無第三方依賴、支援樣式、瀏覽器直接開啟
缺點：實為 HTML 偽裝 Excel、大數據效能差、無法公式/圖表

方案二：CSV 格式輸出 (效能最佳，純文字)

public void ExportCsv(DataTable dt, string fileName)
{
    var response = HttpContext.Current.Response;
    response.Clear();
    response.Buffer = true;
    response.Charset = "UTF-8";
    response.ContentEncoding = Encoding.GetEncoding("GB2312"); // 中文相容
    response.AppendHeader("Content-Disposition", $"attachment;filename={fileName}.csv");
    response.ContentType = "application/csv";
    
    StringBuilder sb = new StringBuilder();
    
    // 標題列
    for (int i = 0; i < dt.Columns.Count; i++)
    {
        sb.Append(dt.Columns[i].ColumnName);
        if (i < dt.Columns.Count - 1) sb.Append(",");
    }
    sb.AppendLine();
    
    // 資料列
    foreach (DataRow row in dt.Rows)
    {
        for (int i = 0; i < dt.Columns.Count; i++)
        {
            string value = row[i]?.ToString()?.Replace("\"", "\"\"") ?? "";
            if (value.Contains(",") || value.Contains("\"") || value.Contains("\n"))
                value = $"\"{value}\"";
            sb.Append(value);
            if (i < dt.Columns.Count - 1) sb.Append(",");
        }
        sb.AppendLine();
    }
    
    response.Write(sb.ToString());
    response.End();
}

方案三：NPOI 產生真正 .xls/.xlsx (推薦)

// NuGet: Install-Package NPOI
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel; // .xlsx
using NPOI.HSSF.UserModel; // .xls

public void ExportNpoi(DataTable dt, string fileName, bool isXlsx = true)
{
    IWorkbook workbook = isXlsx ? new XSSFWorkbook() : new HSSFWorkbook();
    ISheet sheet = workbook.CreateSheet("Sheet1");
    
    // 樣式定義
    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);
    
    // 標題列
    IRow headerRow = sheet.CreateRow(0);
    for (int i = 0; i < dt.Columns.Count; i++)
    {
        ICell cell = headerRow.CreateCell(i);
        cell.SetCellValue(dt.Columns[i].ColumnName);
        cell.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++)
        {
            ICell cell = row.CreateCell(c);
            object value = dt.Rows[r][c];
            SetCellValue(cell, value);
        }
    }
    
    // 自動欄寬
    for (int i = 0; i < dt.Columns.Count; i++)
        sheet.AutoSizeColumn(i);
    
    // 輸出
    var response = HttpContext.Current.Response;
    response.Clear();
    response.ContentType = isXlsx ? 
        "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" : 
        "application/vnd.ms-excel";
    response.AppendHeader("Content-Disposition", $"attachment;filename={fileName}.{(isXlsx ? "xlsx" : "xls")}");
    
    using (MemoryStream ms = new MemoryStream())
    {
        workbook.Write(ms);
        response.BinaryWrite(ms.ToArray());
    }
    response.End();
}

private void SetCellValue(ICell cell, object value)
{
    switch (value)
    {
        case null: cell.SetCellValue(""); break;
        case DateTime dt: cell.SetCellValue(dt); break;
        case bool b: cell.SetCellValue(b); break;
        case double d: cell.SetCellValue(d); break;
        case int i: cell.SetCellValue(i); break;
        case decimal dec: cell.SetCellValue((double)dec); break;
        default: cell.SetCellValue(value.ToString()); break;
    }
}

方案四：EPPlus / ClosedXML (現代 .xlsx 專用，API 友善)

// NuGet: Install-Package EPPlus
// 或: Install-Package ClosedXML
using OfficeOpenXml; // EPPlus
// using ClosedXML.Excel; // ClosedXML

public void ExportEpPlus(DataTable dt, string fileName)
{
    ExcelPackage.LicenseContext = LicenseContext.NonCommercial; // EPPlus 5+ 需設定
    
    using (var package = new ExcelPackage())
    {
        var ws = package.Workbook.Worksheets.Add("Sheet1");
        
        // 從 DataTable 載入 (EPPlus 核心功能)
        ws.Cells["A1"].LoadFromDataTable(dt, true, TableStyles.Light1);
        
        // 自動欄寬
        ws.Cells.AutoFitColumns();
        
        var response = HttpContext.Current.Response;
        response.Clear();
        response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        response.AppendHeader("Content-Disposition", $"attachment;filename={fileName}.xlsx");
        response.BinaryWrite(package.GetAsByteArray());
        response.End();
    }
}

// ClosedXML 版本 (語法更直觀)
public void ExportClosedXml(DataTable dt, string fileName)
{
    using (var wb = new XLWorkbook())
    {
        var ws = wb.Worksheets.Add(dt, "Sheet1");
        ws.Columns().AdjustToContents();
        
        var response = HttpContext.Current.Response;
        response.Clear();
        response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        response.AppendHeader("Content-Disposition", $"attachment;filename={fileName}.xlsx");
        
        using (var ms = new MemoryStream())
        {
            wb.SaveAs(ms);
            response.BinaryWrite(ms.ToArray());
        }
        response.End();
    }
}

WinForms 桌面應用匯出方案

方案一：NPOI/EPPlus/ClosedXML (無需安裝 Office，推薦)

同 ASP.NET 核心邏輯，僅輸出改為檔案儲存：

public void ExportToFile(DataTable dt, string filePath)
{
    using (var wb = new XLWorkbook())
    {
        wb.Worksheets.Add(dt, "Sheet1");
        wb.Worksheets.First().Columns().AdjustToContents();
        wb.SaveAs(filePath);
    }
    
    MessageBox.Show($"匯出完成：{filePath}");
}

方案二：COM Interop (需安裝 Excel，功能最完整)

using Excel = Microsoft.Office.Interop.Excel;

public void ExportWithInterop(DataTable dt, string filePath)
{
    Excel.Application excel = null;
    Excel.Workbook workbook = null;
    Excel.Worksheet worksheet = null;
    
    try
    {
        excel = new Excel.Application();
        excel.Visible = false;
        excel.DisplayAlerts = false;
        
        workbook = excel.Workbooks.Add();
        worksheet = (Excel.Worksheet)workbook.ActiveSheet;
        worksheet.Name = "Report";
        
        // 寫入標題
        for (int c = 0; c < dt.Columns.Count; c++)
        {
            worksheet.Cells[1, c + 1] = dt.Columns[c].ColumnName;
            Excel.Range headerCell = (Excel.Range)worksheet.Cells[1, c + 1];
            headerCell.Font.Bold = true;
            headerCell.Interior.ColorIndex = 15;
        }
        
        // 批次寫入資料 (陣列方式，效能佳)
        object[,] data = new object[dt.Rows.Count, dt.Columns.Count];
        for (int r = 0; r < dt.Rows.Count; r++)
            for (int c = 0; c < dt.Columns.Count; c++)
                data[r, c] = dt.Rows[r][c];
        
        Excel.Range range = worksheet.get_Range("A2", 
            worksheet.Cells[dt.Rows.Count + 1, dt.Columns.Count]);
        range.Value2 = data;
        
        // 格式化
        range.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
        range.Columns.AutoFit();
        
        workbook.SaveAs(filePath);
    }
    finally
    {
        // 正確釋放 COM 物件 (關鍵！)
        ReleaseComObject(worksheet);
        ReleaseComObject(workbook);
        if (excel != null)
        {
            excel.Quit();
            ReleaseComObject(excel);
        }
        GC.Collect();
        GC.WaitForPendingFinalizers();
    }
}

private void ReleaseComObject(object obj)
{
    try
    {
        if (obj != null)
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
    }
    catch { }
    finally { obj = null; }
}

技術選型比較表

大量資料效能優化技巧

1. 分批寫入：避免一次建立巨大物件圖
2. 陣列批次寫入：COM Interop 使用 Range.Value2 = object[,]
3. 停用自動計算：excel.Calculation = XlCalculation.xlCalculationManual
4. 停用畫面更新：excel.ScreenUpdating = false
5. 使用 StringBuilder：CSV/HTML 字串組裝
6. 串流輸出：NPOI/EPPlus 寫入 MemoryStream 而非記憶體陣列

常見問題與解決方案

現代化架構建議

// 介面抽象，支援多種實作
public interface IExcelExporter
{
    byte[] Export(DataTable data, ExportOptions options);
    void ExportToFile(DataTable data, string path, ExportOptions options);
}

public class ExportOptions
{
    public string SheetName { get; set; } = "Sheet1";
    public bool AutoFitColumns { get; set; } = true;
    public bool IncludeHeaders { get; set; } = true;
    public Dictionary ColumnFormats { get; set; }
}

// 工廠模式選擇實作
public static class ExcelExporterFactory
{
    public static IExcelExporter Create(ExportEngine engine)
    {
        return engine switch
        {
            ExportEngine.NPOI => new NpoiExporter(),
            ExportEngine.EPPlus => new EpPlusExporter(),
            ExportEngine.ClosedXML => new ClosedXmlExporter(),
            ExportEngine.Csv => new CsvExporter(),
            _ => throw new ArgumentException("不支援的引擎")
        };
    }
}

總結

Excel 匯出技術選型取決於：部署環境 (有無 Office)、檔案格式需求 (.xls/.xlsx/.csv)、功能複雜度 (樣式/公式/圖表)、資料量大小。現代專案首推 ClosedXML 或 EPPlus：無 Office 相依、API 直覺、支援 .NET Core、效能優異。大量資料用 CSV 或 NPOI SXSSF 串流模式。COM Interop 僅在必須使用 Excel 獨有功能 (巨集、樞紐分析、圖表) 時採用，並嚴格管理 COM 物件釋放。