萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> ASP.NET 程式設計

ASP.NET 匯出 Excel 六大經典方案完整比較與程式碼範例

前言

ASP.NET 匯出 Excel 是企業系統常見需求。本文整理六種主流方案:NPOI、EPPlus、ClosedXML、Open XML SDK、HTML Table 偽裝、COM Interop,比較效能、相依性、授權、功能支援度,並提供可直接複製的核心程式碼。

方案比較總覽

方案相依性授權需安裝 Office支援 .xlsx效能(大量資料)推薦指數
NPOI單一 DLLApache 2.0否是優 (SXSSF 串流)★★★★★
EPPlus 5+單一 DLLPolyform Noncommercial / 商業授權否是優★★★★☆
ClosedXML單一 DLLMIT否是良★★★★☆
Open XML SDK官方 SDKMIT否是極佳 (底層)★★★★☆
HTML Table無無否否 (.xls)差 (記憶體)★★☆☆☆
COM InteropExcel.exeOffice 授權是是極差 (進程重)★☆☆☆☆

一、NPOI 方案 (推薦首選,支援 .xls/.xlsx、串流模式)

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

public byte[] ExportWithNPOI(DataTable dt, string sheetName = "Sheet1")
{
    IWorkbook workbook = new XSSFWorkbook(); // .xlsx,若需 .xls 用 new HSSFWorkbook()
    ISheet sheet = workbook.CreateSheet(sheetName);

    // 標題列
    IRow headerRow = sheet.CreateRow(0);
    for (int i = 0; i < dt.Columns.Count; i++)
    {
        headerRow.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
        // 樣式範例
        var style = workbook.CreateCellStyle();
        var font = workbook.CreateFont(); font.Boldweight = (short)FontBoldWeight.Bold; style.SetFont(font);
        headerRow.Cells[i].CellStyle = style;
    }

    // 資料列
    for (int r = 0; r < dt.Rows.Count; r++)
    {
        IRow row = sheet.CreateRow(r + 1);
        for (int c = 0; c < dt.Columns.Count; c++)
            row.CreateCell(c).SetCellValue(dt.Rows[r][c]?.ToString() ?? "");
    }

    // 自動欄寬
    for (int i = 0; i < dt.Columns.Count; i++) sheet.AutoSizeColumn(i);

    using (MemoryStream ms = new MemoryStream())
    {
        workbook.Write(ms);
        return ms.ToArray();
    }
}

// 大量資料 (超過 10 萬列) 請改用 SXSSFWorkbook (串流寫入,低記憶體)
// IWorkbook workbook = new SXSSFWorkbook(1000); // 保留 1000 列在記憶體,其餘刷入磁碟

二、EPPlus 方案 (語法優雅、功能豐富,商業專案需購買授權)

// NuGet: Install-Package EPPlus (v5 以下免費,v5+ 需商業授權)
using OfficeOpenXml;
using OfficeOpenXml.Style;

public byte[] ExportWithEPPlus(DataTable dt)
{
    ExcelPackage.LicenseContext = LicenseContext.NonCommercial; // 或 Commercial
    using (var pkg = new ExcelPackage())
    {
        var ws = pkg.Workbook.Worksheets.Add("Data");
        ws.Cells["A1"].LoadFromDataTable(dt, true, TableStyles.Light1); // 一行搞定含樣式
        ws.Cells.AutoFitColumns();
        return pkg.GetAsByteArray();
    }
}

三、ClosedXML 方案 (語法最接近 VBA、MIT 授權)

// NuGet: Install-Package ClosedXML
using ClosedXML.Excel;

public byte[] ExportWithClosedXML(DataTable dt)
{
    using (var wb = new XLWorkbook())
    {
        var ws = wb.Worksheets.Add(dt, "Data"); // DataTable 直接轉工作表
        ws.Columns().AdjustToContents();
        using (var ms = new MemoryStream())
        {
            wb.SaveAs(ms);
            return ms.ToArray();
        }
    }
}

四、Open XML SDK 方案 (零相依、效能極致、程式碼較繁瑣)

// NuGet: Install-Package DocumentFormat.OpenXml
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

public byte[] ExportWithOpenXml(DataTable dt)
{
    using (var ms = new MemoryStream())
    using (var doc = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook))
    {
        WorkbookPart wbPart = doc.AddWorkbookPart();
        wbPart.Workbook = new Workbook();
        WorksheetPart wsPart = wbPart.AddNewPart<WorksheetPart>();
        wsPart.Worksheet = new Worksheet(new SheetData());

        Sheets sheets = wbPart.Workbook.AppendChild(new Sheets());
        sheets.Append(new Sheet { Id = wbPart.GetIdOfPart(wsPart), SheetId = 1, Name = "Data" });

        SheetData sheetData = wsPart.Worksheet.GetFirstChild<SheetData>();

        // Header
        Row headerRow = new Row();
        foreach (DataColumn col in dt.Columns)
            headerRow.Append(new Cell { CellValue = new CellValue(col.ColumnName), DataType = CellValues.String });
        sheetData.Append(headerRow);

        // Data
        foreach (DataRow dr in dt.Rows)
        {
            Row row = new Row();
            foreach (var val in dr.ItemArray)
                row.Append(new Cell { CellValue = new CellValue(val?.ToString() ?? ""), DataType = CellValues.String });
            sheetData.Append(row);
        }

        wbPart.Workbook.Save();
    }
    return ms.ToArray();
}

五、HTML Table 偽裝法 (最簡單、相容舊版 IE、不支援 .xlsx 進階功能)

public void ExportHtmlTable(HttpResponse response, DataTable dt, string fileName)
{
    response.Clear();
    response.Buffer = true;
    response.Charset = "UTF-8";
    response.ContentEncoding = Encoding.UTF8;
    response.ContentType = "application/vnd.ms-excel";
    response.AddHeader("Content-Disposition", $"attachment;filename={HttpUtility.UrlEncode(fileName, Encoding.UTF8)}.xls");

    response.Write("<table border='1'><tr>");
    foreach (DataColumn col in dt.Columns) response.Write($"<th>{col.ColumnName}</th>");
    response.Write("</tr>");

    foreach (DataRow row in dt.Rows)
    {
        response.Write("<tr>");
        foreach (var val in row.ItemArray) response.Write($"<td>{HttpUtility.HtmlEncode(val?.ToString() ?? "")}</td>");
        response.Write("</tr>");
    }
    response.Write("</table>");
    response.Flush();
    response.End();
}

缺點:實為 HTML,Excel 開啟會警告、不支援公式/樣式/多工作表、大量資料記憶體暴增。

六、COM Interop 方案 (僅限伺服器安裝 Office、極不推薦用於 Web)

// 需參考 Microsoft.Office.Interop.Excel
// 僅供參考,Web 伺服器嚴禁使用 (非執行緒安全、記憶體洩漏、需互動式桌面)
public void ExportWithInterop(DataTable dt, string filePath)
{
    var excel = new Microsoft.Office.Interop.Excel.Application { Visible = false, DisplayAlerts = false };
    var wb = excel.Workbooks.Add();
    var ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.ActiveSheet;

    for (int c = 0; c < dt.Columns.Count; c++)
        ws.Cells[1, c + 1] = dt.Columns[c].ColumnName;

    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];

    ws.Range[ws.Cells[2, 1], ws.Cells[dt.Rows.Count + 1, dt.Columns.Count]].Value = data;
    ws.Columns.AutoFit();

    wb.SaveAs(filePath);
    wb.Close();
    excel.Quit();
    // 必須釋放 COM 物件
    System.Runtime.InteropServices.Marshal.ReleaseComObject(ws);
    System.Runtime.InteropServices.Marshal.ReleaseComObject(wb);
    System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
}

七、統一回傳給前端的 Helper

public void SendExcel(HttpResponse response, byte[] content, string fileName)
{
    response.Clear();
    response.Buffer = true;
    response.Charset = "UTF-8";
    response.ContentEncoding = Encoding.UTF8;
    response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    response.AddHeader("Content-Disposition", $"attachment;filename={HttpUtility.UrlEncode(fileName, Encoding.UTF8)}.xlsx");
    response.BinaryWrite(content);
    response.Flush();
    response.End(); // 或 HttpContext.Current.ApplicationInstance.CompleteRequest();
}

八、選型建議

  • 一般專案、開源、無預算:NPOI (含 SXSSF 大數據) 或 ClosedXML。
  • 企業專案、預算充足、需複雜圖表/樣條/樞紐分析:EPPlus 商業授權。
  • 極致效能、零第三方相依、團隊熟悉 Open XML:Open XML SDK。
  • 快速原型、資料量小、不需樣式:HTML Table 法。
  • 絕對避免:Web 伺服器使用 COM Interop。
copyright © 萬盛學電腦網 all rights reserved