C# 使用 OleDb 讀取 Excel 資料完整實作（支援 .xls/.xlsx、多工作表、空白列過濾）

核心概念

透過 Microsoft.Jet.OLEDB.4.0 (.xls) 或 Microsoft.ACE.OLEDB.12.0 (.xlsx) Provider，將 Excel 檔案視為資料來源，使用 SQL 語法查詢工作表資料。注意：

• 需安裝 Access Database Engine (32/64 位元需與專案目標平台一致)。
• HDR=YES 表示第一列為標題；IMEX=1 強制混合型別欄位以文字讀取，避免數值/日期截斷。

取得所有工作表名稱

private string[] GetExcelSheetNames(string filePath)
{
    string ext = Path.GetExtension(filePath).ToLower();
    string connStr = ext == ".xls"
        ? "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1;'"
        : "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;'";

    using (var conn = new OleDbConnection(connStr))
    {
        conn.Open();
        // 取得結構描述資料表
        var schema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        if (schema == null) return Array.Empty();

        var sheets = new List();
        foreach (DataRow row in schema.Rows)
        {
            string name = row["TABLE_NAME"].ToString();
            // 過濾系統表、無效名稱（需以 $ 結尾且包含 $）
            if (name.Contains("$") && name.Replace("'", "").EndsWith("$"))
                sheets.Add(name.Substring(0, name.Length - 1)); // 移除尾端 $
        }
        return sheets.ToArray();
    }
}

讀取所有工作表資料合併為單一 DataTable

private DataTable ReadAllSheets(string filePath)
{
    string ext = Path.GetExtension(filePath).ToLower();
    string connStr = ext == ".xls"
        ? $"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={filePath};Extended Properties='Excel 8.0;HDR=YES;IMEX=1;'"
        : $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={filePath};Extended Properties='Excel 12.0;HDR=YES;IMEX=1;'";

    var sheetNames = GetExcelSheetNames(filePath);
    var result = new DataTable();

    using (var conn = new OleDbConnection(connStr))
    using (var adapter = new OleDbDataAdapter())
    {
        conn.Open();
        foreach (string sheet in sheetNames)
        {
            // 工作表名稱需加上 $ 並用 [] 括起來
            string sql = $"SELECT * FROM [{sheet}$]";
            adapter.SelectCommand = new OleDbCommand(sql, conn);
            adapter.Fill(result);
        }
    }

    // 移除全空白列（依前 9 欄判斷）
    result = RemoveBlankRows(result, 9);
    return result;
}

移除空白列與清理 DBNull

private DataTable RemoveBlankRows(DataTable dt, int checkColCount)
{
    if (dt == null || dt.Rows.Count == 0) return dt;

    // 由後往前刪除，避免索引位移
    for (int i = dt.Rows.Count - 1; i >= 0; i--)
    {
        bool allEmpty = true;
        for (int c = 0; c < checkColCount && c < dt.Columns.Count; c++)
        {
            var val = dt.Rows[i][c];
            if (val != DBNull.Value && val.ToString().Trim().Length > 0)
            {
                allEmpty = false; break;
            }
        }
        if (allEmpty) dt.Rows[i].Delete();
    }
    dt.AcceptChanges();

    // 將剩餘 DBNull 替換為空字串（限字串欄位）
    foreach (DataRow row in dt.Rows)
    {
        for (int c = 0; c < dt.Columns.Count; c++)
        {
            if (row[c] == DBNull.Value && dt.Columns[c].DataType == typeof(string))
                row[c] = string.Empty;
        }
    }
    dt.AcceptChanges();
    return dt;
}

完整呼叫範例

string file = @"C:\Import\Data.xlsx";
var data = ReadAllSheets(file);
Console.WriteLine($"共讀取 {data.Rows.Count} 筆資料，{data.Columns.Count} 欄位");
// 後續可用 SqlBulkCopy 寫入資料庫或進行業務邏輯處理

常見問題排查

參考資源

原文連結：https://www.bianceng.cn/Programming/csharp/201410/45598.htm