ASP.NET 匯出 Excel 是企業系統常見需求。本文整理六種主流方案:NPOI、EPPlus、ClosedXML、Open XML SDK、HTML Table 偽裝、COM Interop,比較效能、相依性、授權、功能支援度,並提供可直接複製的核心程式碼。
| 方案 | 相依性 | 授權 | 需安裝 Office | 支援 .xlsx | 效能(大量資料) | 推薦指數 |
|---|---|---|---|---|---|---|
| NPOI | 單一 DLL | Apache 2.0 | 否 | 是 | 優 (SXSSF 串流) | ★★★★★ |
| EPPlus 5+ | 單一 DLL | Polyform Noncommercial / 商業授權 | 否 | 是 | 優 | ★★★★☆ |
| ClosedXML | 單一 DLL | MIT | 否 | 是 | 良 | ★★★★☆ |
| Open XML SDK | 官方 SDK | MIT | 否 | 是 | 極佳 (底層) | ★★★★☆ |
| HTML Table | 無 | 無 | 否 | 否 (.xls) | 差 (記憶體) | ★★☆☆☆ |
| COM Interop | Excel.exe | Office 授權 | 是 | 是 | 極差 (進程重) | ★☆☆☆☆ |
// 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 列在記憶體,其餘刷入磁碟
// 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();
}
}
// 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();
}
}
}
// 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();
}
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 開啟會警告、不支援公式/樣式/多工作表、大量資料記憶體暴增。
// 需參考 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);
}
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();
}