萬盛學電腦網

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

.NET 操作 Excel 完整實戰:ADO.NET 讀取、Interop 建立與格式化、NPOI/EPPlus 現代替代方案

三大主流技術路線比較

方案優點缺點適用場景
ADO.NET (OLEDB) 無需安裝 Office、速度快、純 .NET、支援資料繫結 唯讀導向、無法操作格式/圖表/公式、xlsx 需 ACE Provider 大量資料匯入、報表匯出 (純資料)
Microsoft.Office.Interop.Excel 完整控制 Excel 所有功能 (格式、圖表、公式、巨集) 需安裝 Office、COM 互通效能差、記憶體洩漏風險、伺服器端不支援 (Microsoft 明確不建議) 桌面應用、需複雜格式/圖表的自動化報表
NPOI / EPPlus / ClosedXML 無需 Office、純 .NET、支援 xls/xlsx、格式/樣式/公式/圖表豐富、效能優異 需學習 API、極少數進階功能不支援 現代伺服器端/雲端部署首選

方案一:ADO.NET 讀取 Excel (OLEDB Provider)

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` 混合資料型別強制以文字讀取 (避免數字欄位讀空值)。

取得工作表名稱 (需 Interop 或開啟連線取得 Schema)

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

方案二:Interop 建立、寫入、格式化 Excel (桌面端專用)

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();
    }
}

方案三:EPPlus (現代 .NET Core/5+ 首選)

// 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

選擇建議與遷移策略

  • 新專案 / 伺服器端 / 容器化部署:直接使用 NPOI (免費、成熟、支援 .xls/.xlsx) 或 ClosedXML (API 更直觀)。
  • 既有 Interop 專案維護:確保 `Marshal.ReleaseComObject` 完整釋放,並在 `finally` 區塊呼叫 `GC.Collect()`;考慮逐步重構為 NPOI/EPPlus。
  • 純資料大量匯入/匯出:ADO.NET OLEDB 最快,但需目標機器安裝 ACE Provider。
  • 複雜圖表/樞紐分析/巨集:僅 Interop 能完整支援,且必須在安裝 Office 的桌面/工作站執行。

總結

.NET 操作 Excel 已從「必須安裝 Office」演進為「純 .NET 函式庫即可完整支援」。掌握 ADO.NET 快速讀取、Interop 完整自動化、NPOI/EPPlus 現代化無 Office 部署三大路線,並根據部署環境、功能需求、授權模式選擇最適方案,是 .NET 開發者處理試算表業務的核心能力。

copyright © 萬盛學電腦網 all rights reserved