ADO.NET 操作 Excel 完整指南：連接字串、表引用、動態架構讀取、限制與對策

利用 Microsoft.Jet.OLEDB.4.0 Provider，ADO.NET 可像操作資料庫一樣讀寫 Excel。但 Excel 非真關聯資料庫，存在格式、公式、主鍵、型別推斷等固有限制，需掌握關鍵技巧方能得心應手。

連接字串核心參數

string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                 "Extended Properties='Excel 8.0;HDR=YES;IMEX=1';" +
                 "Data Source=" + xlsPath;

三種表引用語法

1. 整張工作表：[Sheet1$]（$ 為必需，方括號必需）
2. 指定範圍：[Sheet1$A1:C10]（精確控制讀寫區域）
3. 命名區域：[MyNamedRange]（跨工作表唯一，最彈性）

注意：[Sheet1$] 從左上角第一非空儲存格開始，到右下角最後非空儲存格結束。精確讀取請用範圍或命名區域。

欄位名稱規則

• HDR=YES：首列內容為欄名，空儲存格自動命名 F1、F2…
• HDR=NO：所有欄名為 F1、F2、F3…

五大固有限制與對策

動態架構讀取：未知結構 Excel 自動匯入

OleDbConnection conn = new OleDbConnection(connStr);
conn.Open();

// 1. 取得所有「表」（工作表+命名區域）
DataTable schema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, 
    new object[] { null, null, null, "TABLE" });

List tables = new List();
foreach (DataRow row in schema.Rows)
    tables.Add((string)row["TABLE_NAME"]);

// 2. 逐表填充 DataSet
OleDbDataAdapter da = new OleDbDataAdapter();
DataSet ds = new DataSet();
foreach (string t in tables) {
    da.SelectCommand = new OleDbCommand($"SELECT * FROM [{t}]", conn);
    da.Fill(ds, t);
}

// 3. 取得指定表欄位結構
DataTable colSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns,
    new object[] { null, null, "Sheet1$", null });
// colSchema 含 COLUMN_NAME, DATA_TYPE, ORDINAL_POSITION 等

ASP.NET 通用操作封裝

protected void DoOleSql(string sql, string xlsFile)
{
    string connStr = $"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={Server.MapPath(xlsFile)};Extended Properties='Excel 8.0;HDR=NO;IMEX=0'";
    using (OleDbConnection conn = new OleDbConnection(connStr))
    {
        conn.Open();
        using (OleDbCommand cmd = new OleDbCommand(sql, conn))
        {
            cmd.ExecuteNonQuery();
        }
    }
}

// 使用範例
DoOleSql("SELECT * FROM [Sheet1$]", "test.xls");
DoOleSql("UPDATE [Sheet1$] SET F1='333' WHERE F2='b3'", "test.xls");
DoOleSql("INSERT INTO [Sheet1$] (F1,F2) VALUES ('a','b')", "test.xls");
// 非標準結構指定範圍
DoOleSql("SELECT * FROM [Sheet1$A3:F20]", "test.xls");

常見錯誤排查

• 找不到可安裝的 ISAM：連接字串語法錯誤（引號、分號、Extended Properties 格式）
• 操作必須使用可更新的查詢：欄位引用錯誤、檔案唯讀、無寫入權限
• 無法擴充選定範圍：範圍地址超出實際資料區、合併儲存格干擾