.NET 操作 Excel 完整實作指南 (讀取、建立、寫入、格式化)

前言

.NET 操作 Excel 主要有三種路徑：ADO.NET (適合純資料讀寫)、Microsoft.Office.Interop.Excel (功能最完整、需安裝 Office)、OpenXML/EPPlus (無需 Office、效能佳)。本文以 ADO.NET 讀取與 Interop 建立/寫入/格式化為主，提供實用範本。

一、使用 ADO.NET 讀取 Excel 回傳 DataTable

public DataTable ExcelToDataTable(string strExcelPath, string strSheetName)
{
    string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                     "Data Source=" + strExcelPath + ";" +
                     "Extended Properties=Excel 5.0;";

    string strExcel = string.Format("SELECT * FROM [{0}$]", strSheetName);
    DataSet ds = new DataSet();
    OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, strConn);
    adapter.Fill(ds, strSheetName);
    // 注意：原始碼中 conn.Close() 但 conn 未定義，應改為 adapter.Dispose() 或連線物件管理
    return ds.Tables[strSheetName];
}

參數說明：
strExcelPath - Excel 檔案完整路徑
strSheetName - 工作表名稱 (不含 $ 符號)

取得工作表名稱 (需引用 Interop)

Excel.Application excelApp = new Excel.Application();
Excel.Workbook wb = excelApp.Workbooks.Open("Excel檔案路徑", 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true);
Excel.Sheets sheets = wb.Worksheets;
Excel.Worksheet ws = (Excel.Worksheet)sheets.get_Item(1);
string sheetName = ws.Name;
wb.Close(null, null, null);
excelApp.Quit(); // 必須釋放，否則 Excel 進程殘留

二、建立新的 Excel 檔案 (Interop)

Excel.ApplicationClass excel = new Excel.ApplicationClass();
Excel._Workbook wb;
Excel._Worksheet ws;

wb = excel.Workbooks.Add(true);
object missing = System.Reflection.Missing.Value;
excel.Sheets.Add(missing, missing, 1, Excel.XlSheetType.xlWorksheet);
ws = (Excel._Worksheet)wb.ActiveSheet;
ws.Name = "新的表";

wb.SaveAs("儲存路徑", missing, missing, missing, missing, missing,
          Excel.XlSaveAsAccessMode.xlShared, missing, missing, missing, missing);
excel.Quit();

三、寫入資料與格式設定

常用格式代碼

範例：設定欄位格式與寫入 DataTable 資料

// 設定第一列字體紅色
((Excel.Range)ws.Rows[1, Type.Missing]).Font.Color = 0xFF0000;

// 設定第二欄為百分比格式
((Excel.Range)ws.Columns[2, Type.Missing]).NumberFormat = "0.00%";

// 選取範圍：第 1 列第 2 欄 到 第 1 列第 5 欄
Excel.Range rng = ws.get_Range(ws.Cells[1, 2], ws.Cells[1, 5]);

// 寫入資料 (列從 1 開始，欄從 1 開始)
int rowIdx = 2; // 從第 2 列開始 (第 1 列為標題)
foreach (DataRow dr in dt.Rows)
{
    int colIdx = 1;
    ws.Cells[rowIdx, colIdx++] = dr["欄位1"].ToString();
    ws.Cells[rowIdx, colIdx++] = dr["欄位2"].ToString();
    ws.Cells[rowIdx, colIdx++] = dr["欄位3"].ToString();
    ws.Cells[rowIdx, colIdx++] = dr["欄位4"].ToString();
    ws.Cells[rowIdx, colIdx++] = dr["欄位5"].ToString();
    ws.Cells[rowIdx, colIdx++] = dr["欄位6"].ToString();

    // 使用公式：A+B+C+D 的加總
    ws.Cells[rowIdx, colIdx++] = string.Format("=SUM(A{0}:D{0})", rowIdx);

    rowIdx++;
}

wb.Save();
wb.Close();
excel.Quit();

四、重要注意事項

1. 索引從 1 開始：Excel 的列、欄索引皆從 1 起算，非 0。
2. 釋放 COM 物件：務必呼叫 Quit() 並搭配 Marshal.ReleaseComObject，避免 Excel 進程殘留佔用記憶體。
3. 伺服器端自動化風險：Microsoft 不建議在無人值守的伺服器環境使用 Interop。大量報表建議改用 OpenXML SDK 或 EPPlus/NPOI 等純 .NET 程式庫。
4. 連接字串版本：.xls 使用 Microsoft.Jet.OLEDB.4.0 + Excel 8.0；.xlsx 使用 Microsoft.ACE.OLEDB.12.0 + Excel 12.0。

五、替代方案推薦

• EPPlus：功能豐富、支援 .xlsx、無需 Office、效能佳、NuGet 安裝即用。
• NPOI：支援 .xls/.xlsx、Apache POI 移植版、開源免費。
• ClosedXML：API 直覺、建立於 OpenXML SDK 之上。