| 方案 | 優點 | 缺點 | 適用場景 |
|---|---|---|---|
| ADO.NET (OLEDB) | 無需安裝 Office、速度快、純 .NET、支援資料繫結 | 唯讀導向、無法操作格式/圖表/公式、xlsx 需 ACE Provider | 大量資料匯入、報表匯出 (純資料) |
| Microsoft.Office.Interop.Excel | 完整控制 Excel 所有功能 (格式、圖表、公式、巨集) | 需安裝 Office、COM 互通效能差、記憶體洩漏風險、伺服器端不支援 (Microsoft 明確不建議) | 桌面應用、需複雜格式/圖表的自動化報表 |
| NPOI / EPPlus / ClosedXML | 無需 Office、純 .NET、支援 xls/xlsx、格式/樣式/公式/圖表豐富、效能優異 | 需學習 API、極少數進階功能不支援 | 現代伺服器端/雲端部署首選 |
public DataTable ExcelToDataTable(string filePath, string sheetName)
{
// .xls (Excel 97-2003)
string connStr = $"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={filePath};Extended Properties='Excel 8.0;HDR=YES;IMEX=1;'";
// .xlsx (Excel 2007+) 需安裝 Microsoft Access Database Engine (ACE)
// string connStr = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={filePath};Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1;'";
string query = $"SELECT * FROM [{sheetName}$]"; // 工作表名稱加 $ 並用 [] 包圍
using (var conn = new OleDbConnection(connStr))
using (var adapter = new OleDbDataAdapter(query, conn))
{
var ds = new DataSet();
adapter.Fill(ds, sheetName);
return ds.Tables[sheetName];
}
}
關鍵參數:`HDR=YES` 第一行為標題;`IMEX=1` 混合資料型別強制以文字讀取 (避免數字欄位讀空值)。
public List GetSheetNames(string filePath)
{
var sheets = new List();
string connStr = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={filePath};Extended Properties='Excel 12.0 Xml;HDR=YES;'";
using (var conn = new OleDbConnection(connStr))
{
conn.Open();
DataTable schema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
foreach (DataRow row in schema.Rows)
{
string name = row["TABLE_NAME"].ToString();
if (name.EndsWith("$") || name.EndsWith("$'")) // 過濾系統表
sheets.Add(name.TrimEnd('$', '\''));
}
}
return sheets;
}
using Excel = Microsoft.Office.Interop.Excel;
public void CreateExcelWithInterop(string savePath, DataTable dt)
{
Excel.Application excelApp = null;
Excel.Workbook wb = null;
Excel.Worksheet ws = null;
try
{
excelApp = new Excel.Application();
excelApp.Visible = false;
excelApp.DisplayAlerts = false;
wb = excelApp.Workbooks.Add(Type.Missing);
ws = (Excel.Worksheet)wb.ActiveSheet;
ws.Name = "報表資料";
// 寫入標題列
for (int c = 0; c < dt.Columns.Count; c++)
{
ws.Cells[1, c + 1] = dt.Columns[c].ColumnName;
}
// 批次寫入資料 (陣列一次寫入,比逐 Cell 快 100 倍)
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 = ws.Range[ws.Cells[2, 1], ws.Cells[dt.Rows.Count + 1, dt.Columns.Count]];
range.Value2 = data;
// 格式化範例
((Excel.Range)ws.Rows[1]).Font.Bold = true;
((Excel.Range)ws.Rows[1]).Interior.Color = 0x0070C0; // 藍底
((Excel.Range)ws.Columns[3]).NumberFormat = "0.00%"; // 第三欄百分比
ws.Columns.AutoFit();
wb.SaveAs(savePath);
}
finally
{
// 嚴格釋放 COM 物件 (逆序)
if (ws != null) Marshal.ReleaseComObject(ws);
if (wb != null) { wb.Close(false); Marshal.ReleaseComObject(wb); }
if (excelApp != null) { excelApp.Quit(); Marshal.ReleaseComObject(excelApp); }
GC.Collect(); GC.WaitForPendingFinalizers();
}
}
// NuGet: Install-Package EPPlus
// 非商業用途免費 (EPPlus 5+ 需商業授權),或改用 NPOI/ClosedXML
using OfficeOpenXml;
using OfficeOpenXml.Style;
public void CreateExcelWithEPPlus(string filePath, DataTable dt)
{
ExcelPackage.LicenseContext = LicenseContext.NonCommercial; // 設定授權上下文
using (var package = new ExcelPackage())
{
var ws = package.Workbook.Worksheets.Add("報表資料");
// 從 DataTable 載入 (自動建立標題、資料、型別)
ws.Cells["A1"].LoadFromDataTable(dt, true);
// 樣式調整
using (var header = ws.Cells[1, 1, 1, dt.Columns.Count])
{
header.Style.Font.Bold = true;
header.Style.Fill.PatternType = ExcelFillStyle.Solid;
header.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(0x00, 0x70, 0xC0));
header.Style.Font.Color.SetColor(System.Drawing.Color.White);
}
ws.Cells[ws.Dimension.Address].AutoFitColumns();
// 儲存
package.SaveAs(new FileInfo(filePath));
}
}
| 格式需求 | NumberFormat 字串 |
|---|---|
| 文字 | @ |
| 日期 | yyyy/mm/dd |
| 數字 (千分位、兩位小數) | #,##0.00 |
| 貨幣 | ¥#,##0.00 |
| 百分比 | 0.00% |
| 科學記號 | 0.00E+00 |
.NET 操作 Excel 已從「必須安裝 Office」演進為「純 .NET 函式庫即可完整支援」。掌握 ADO.NET 快速讀取、Interop 完整自動化、NPOI/EPPlus 現代化無 Office 部署三大路線,並根據部署環境、功能需求、授權模式選擇最適方案,是 .NET 開發者處理試算表業務的核心能力。