C# 讀取 Excel 完整實戰：OLEDB 連線、資料型別陷阱與進階查詢技巧

為什麼選擇 OLEDB 讀取 Excel？

在無法安裝 Office（伺服器環境）或需高效能批次讀取時，Microsoft.Jet.OLEDB.4.0 / Microsoft.ACE.OLEDB.12.0 提供輕量、無相依性的解決方案。將 Excel 檔案視為資料來源，以 SQL 語法查詢工作表，程式碼簡潔且效能穩定。

環境準備與連線字串詳解

1. 提供者選擇

2. 連線字串關鍵屬性

// 基礎範本 (.xls)
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\test.xls;Extended Properties="Excel 8.0;HDR=YES;IMEX=1"

// 進階範本 (.xlsx)
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\test.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1"

核心讀取程式碼（含資源釋放最佳實務）

public DataTable ReadExcelSheet(string filePath, string sheetName, bool hasHeader = true)
{
    string provider = filePath.EndsWith(".xlsx") 
        ? "Microsoft.ACE.OLEDB.12.0" 
        : "Microsoft.Jet.OLEDB.4.0";
    
    string extProps = $"Excel {(filePath.EndsWith(".xlsx") ? "12.0 Xml" : "8.0")};HDR={(hasHeader ? "YES" : "NO")};IMEX=1;ReadOnly=True";
    string connStr = $"Provider={provider};Data Source={filePath};Extended Properties=\"{extProps}\"";

    using (var conn = new OleDbConnection(connStr))
    using (var cmd = new OleDbCommand($"SELECT * FROM [{sheetName}$]", conn))
    using (var adapter = new OleDbDataAdapter(cmd))
    {
        var dt = new DataTable();
        conn.Open();
        adapter.Fill(dt);
        return dt;
    }
}

實戰場景與解決方案

1. 不知道工作表名稱？動態取得所有工作表

public List GetSheetNames(string filePath)
{
    var sheets = new List();
    string provider = filePath.EndsWith(".xlsx") ? "Microsoft.ACE.OLEDB.12.0" : "Microsoft.Jet.OLEDB.4.0";
    string connStr = $"Provider={provider};Data Source={filePath};Extended Properties=\"Excel 8.0;HDR=YES\"";

    using (var conn = new OleDbConnection(connStr))
    {
        conn.Open();
        // 取得結構描述：TABLE 類型即為工作表
        DataTable schema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
        foreach (DataRow row in schema.Rows)
        {
            string sheetName = row["TABLE_NAME"].ToString().TrimEnd('$'); // 移除尾端 $
            if (!sheetName.EndsWith("_FilterDatabase")) // 排除 Excel 內建篩選表
                sheets.Add(sheetName);
        }
    }
    return sheets;
}

2. 只需特定欄位或範圍？SQL 查詢語法

// 指定欄位：SELECT [欄位1], [欄位2] FROM [Sheet1$]
// 指定範圍：SELECT * FROM [Sheet1$A1:D100]  (A1:D100 為儲存格範圍)
// 指定整欄：SELECT * FROM [Sheet1$A:D]      (A 到 D 欄)
// 別名支援：SELECT [姓名] AS Name, [年齡] AS Age FROM [Sheet1$]

3. 常見資料型別陷阱與對策

進階：大檔案分批讀取避免記憶體暴衝

public IEnumerable ReadExcelInBatches(string filePath, string sheetName, int batchSize = 10000)
{
    string provider = filePath.EndsWith(".xlsx") ? "Microsoft.ACE.OLEDB.12.0" : "Microsoft.Jet.OLEDB.4.0";
    string connStr = $"Provider={provider};Data Source={filePath};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1;ReadOnly=True\"";

    using (var conn = new OleDbConnection(connStr))
    {
        conn.Open();
        // 先取得總筆數
        using (var countCmd = new OleDbCommand($"SELECT COUNT(*) FROM [{sheetName}$]", conn))
        {
            int total = (int)countCmd.ExecuteScalar();
            for (int offset = 0; offset < total; offset += batchSize)
            {
                string sql = $"SELECT * FROM [{sheetName}$] LIMIT {batchSize} OFFSET {offset}"; // ACE 支援 LIMIT/OFFSET
                // Jet 不支援 LIMIT，需用 TOP + WHERE 主鍵 > 上一批最大值 等替代方案
                using (var adapter = new OleDbDataAdapter(sql, conn))
                {
                    var dt = new DataTable();
                    adapter.Fill(dt);
                    yield return dt;
                }
            }
        }
    }
}

替代方案快速比較

總結檢核清單

• [ ] 根據副檔名選對 Provider (Jet vs ACE)
• [ ] 連線字串加上 IMEX=1;ReadOnly=True
• [ ] 使用 using 確保連線/命令/配接器正確釋放
• [ ] 動態取得工作表名稱避免硬編碼
• [ ] 針對長數字、日期、混合型別欄位做後續轉換驗證
• [ ] 大檔案採用分批讀取或改用串流式庫

掌握 OLEDB 讀取 Excel 的核心參數與陷阱，能讓您在企業級匯入匯出功能中游刃有餘。根據專案需求靈活選擇最適合的庫，才是長久之計。