ASP.NET 匯出 Excel 有多種路徑,從舊式 Response.Write 輸出 HTML 表格、GridView.RenderControl、DataView 驅動 Excel Interop,到現代純 .NET 庫(NPOI、EPPlus、ClosedXML)。本文全景式比較各方案優缺點與適用場景。
public void ExportByDataSet(DataSet ds, string fileName)
{
Response.Clear();
Response.ContentEncoding = Encoding.GetEncoding("GB2312");
Response.AppendHeader("Content-Disposition", $"attachment;filename={fileName}.xls");
Response.ContentType = "application/ms-excel";
var dt = ds.Tables[0];
// 表頭
foreach (DataColumn col in dt.Columns)
Response.Write(col.ColumnName + "\t");
Response.Write("\n");
// 資料列
foreach (DataRow row in dt.Rows)
{
foreach (object val in row.ItemArray)
Response.Write(val + "\t");
Response.Write("\n");
}
Response.End();
}
public void ExportByGridView(Control gridView)
{
Response.Clear();
Response.ContentType = "application/ms-excel";
Response.AppendHeader("Content-Disposition", "attachment;filename=Export.xls");
Response.Charset = "UTF-8";
using (var sw = new StringWriter())
using (var hw = new HtmlTextWriter(sw))
{
gridView.RenderControl(hw);
Response.Write(sw.ToString());
}
Response.End();
}
// 頁面必須覆寫 VerifyRenderingInServerForm
public override void VerifyRenderingInServerForm(Control control) { }
// 需引用 Microsoft.Office.Interop.Excel
var excel = new ApplicationClass();
var wb = excel.Workbooks.Add(true);
var ws = (_Worksheet)wb.ActiveSheet;
// 寫入標題與資料...
ws.Cells[1, 1] = "標題";
wb.SaveAs(path);
wb.Close();
excel.Quit();
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel; // .xlsx
using NPOI.HSSF.UserModel; // .xls
public void ExportNPOI(DataTable dt, string filePath)
{
IWorkbook wb = new XSSFWorkbook(); // 或 HSSFWorkbook
ISheet sheet = wb.CreateSheet("Sheet1");
// 樣式:文字格式避免長數字科學記號
var textStyle = wb.CreateCellStyle();
textStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
// 表頭
var headerRow = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
headerRow.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
}
// 資料
for (int r = 0; r < dt.Rows.Count; r++)
{
var row = sheet.CreateRow(r + 1);
for (int c = 0; c < dt.Columns.Count; c++)
{
var cell = row.CreateCell(c);
var val = dt.Rows[r][c];
if (val is DateTime) cell.SetCellValue((DateTime)val);
else if (val is double || val is decimal) cell.SetCellValue(Convert.ToDouble(val));
else cell.SetCellValue(val?.ToString() ?? "");
// 長數字欄位套用文字格式
if (dt.Columns[c].ColumnName.Contains("身分證") || dt.Columns[c].ColumnName.Contains("卡號"))
cell.CellStyle = textStyle;
}
}
// 自動欄寬
for (int i = 0; i < dt.Columns.Count; i++) sheet.AutoSizeColumn(i);
using (var fs = File.Create(filePath)) wb.Write(fs);
}
using OfficeOpenXml;
ExcelPackage.LicenseContext = LicenseContext.NonCommercial; // 或 Commercial
public void ExportEPPlus(DataTable dt, string filePath)
{
using var pkg = new ExcelPackage();
var ws = pkg.Workbook.Worksheets.Add("Sheet1");
// 直接從 DataTable 載入(含型別推論)
ws.Cells["A1"].LoadFromDataTable(dt, true);
// 設定特定欄為文字格式
ws.Column(3).Style.Numberformat.Format = "@"; // 第 3 欄
pkg.SaveAs(new FileInfo(filePath));
}
using ClosedXML.Excel;
public void ExportClosedXML(DataTable dt, string filePath)
{
using var wb = new XLWorkbook();
var ws = wb.Worksheets.Add(dt, "Sheet1"); // 一行搞定
// 文字格式
ws.Column("ID").Style.NumberFormat.Format = "@";
wb.SaveAs(filePath);
}
| 方案 | 真實 .xlsx | 需安裝 Office | 跨平台 | 效能 | 樣式/公式 | 授權 | 推薦度 |
|---|---|---|---|---|---|---|---|
| Response HTML | ❌ | 否 | ✅ | 極快 | ❌ | 免費 | ★☆☆☆☆ |
| GridView.RenderControl | ❌ | 否 | ✅ | 快 | ❌ | 免費 | ★★☆☆☆ |
| Excel Interop | ✅ | ✅ | ❌ | 極慢 | ✅ | Office 授權 | ❌ 禁用 |
| NPOI | ✅ | 否 | ✅ | 快 | ✅ | Apache 2.0 | ★★★★☆ |
| EPPlus | ✅ | 否 | ✅ | 快 | ✅✅✅ | 商業 | ★★★★★ |
| ClosedXML | ✅ | 否 | ✅ | 快 | ✅✅ | MIT | ★★★★★ |
public void ExportCsv(DataTable dt, string filePath)
{
var sb = new StringBuilder();
// BOM 讓 Excel 正確識別 UTF-8
sb.Append('\uFEFF');
sb.AppendLine(string.Join(",", dt.Columns.Cast().Select(c => $"\"{c.ColumnName}\"")));
foreach (DataRow row in dt.Rows)
{
sb.AppendLine(string.Join(",", row.ItemArray.Select(v => $"\"{v?.ToString().Replace("\"", "\"\"")}\"")));
}
File.WriteAllText(filePath, sb.ToString(), Encoding.UTF8);
}
**徹底拋棄 Excel Interop**,改用 **NPOI/ClosedXML/EPPlus** 產出真正的 .xlsx 二進位檔案。ClosedXML API 最簡潔適合快速開發,EPPlus 功能最完整適合複雜報表,NPOI 歷史久支援舊 .xls。配合 NumberFormat.Format = "@" 解決長數字科學記號問題,即可專業級交付。