萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> 程式語言綜合

ADO.NET 讀取 Excel 完整教學:連接字串、工作表引用、動態 Schema 發現

最簡單範例:ASP.NET WebForms 讀取 Excel 繫結 GridView

string xlsPath = Server.MapPath("~/App_Data/SomeFile.xls"); // 絕對路徑
string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                 "Extended Properties=\"Excel 8.0;\";" +
                 "Data Source=" + xlsPath;

string sql = "SELECT * FROM [Sheet1$]";

var ds = new DataSet();
using (var da = new OleDbDataAdapter(sql, connStr)) {
    da.Fill(ds);
}
GridView1.DataSource = ds.Tables[0];
GridView1.DataBind();

五大必知細節

1. Extended Properties 版本對應

Excel 檔案Extended Properties 值
97/2000/2002/2003 (.xls)Excel 8.0
5.0/95 (.xls)Excel 5.0
4.0 (.xls)Excel 4.0
3.0 (.xls)Excel 3.0
2007+ (.xlsx/.xlsm)Excel 12.0 Xml (需 ACE Provider)

2. 路徑必須為絕對物理路徑

Server.MapPath、Path.GetFullPath、HttpContext.Current.Request.PhysicalApplicationPath 等取得。相對路徑在 IIS/服務帳戶下會失效。

3. 工作表/範圍引用規則

  • 整張表:[Sheet1$] — 方括號 + 工作表名 + 美元符號。
  • 指定範圍:[Sheet1$A1:C10]。
  • 命名範圍:[MyNamedRange] — 無需 $,全活頁簿唯一。
  • 常見錯誤:缺 [] →「FROM 子句語法錯誤」;缺 $ →「找不到物件」;名稱錯誤 →「不是有效名稱」。
  • 資料起始點:引用 [Sheet1$] 時,從最左上非空儲存格開始;精確控制請用範圍或命名範圍。

4. 欄位名稱來源 (HDR)

// 預設 HDR=YES:第一列為欄位名,空白欄位自動命名 F1, F2...
// HDR=NO:第一列為資料,所有欄位命名 F1, F2...
string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                 "Extended Properties=\"Excel 8.0;HDR=NO\";" +  // 注意雙層引號跳脫
                 "Data Source=" + xlsPath;

5. 混合型別欄位讀取失敗 (IMEX)

現象:前幾列為數值,後面出現文字 → 文字列讀成 DBNull。

string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                 "Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\";" + // 強制視為文字
                 "Data Source=" + xlsPath;
  • IMEX=1 告訴驅動程式將混合型別欄位視為文字,避免資料遺失。
  • 搭配 TypeGuessRows=0 掃描全表 (較慢但最準確)。

進階:動態發現工作表與欄位結構

場景:上傳的 Excel 檔案結構不固定,需自動讀取所有工作表/欄位。

步驟 1:取得工作表清單

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

using (var conn = new OleDbConnection(connStr)) {
    conn.Open();
    // 取得所有「系統表」(工作表)
    DataTable schema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
        new object[] { null, null, null, "SYSTEM_TABLE" });

    foreach (DataRow row in schema.Rows) {
        string sheetName = row["TABLE_NAME"].ToString(); // 如 "Sheet1$"
        Console.WriteLine($"發現工作表: {sheetName}");
    }
}

步驟 2:取得指定工作表欄位定義

DataTable colSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns,
    new object[] { null, null, "Sheet1$", null });

foreach (DataRow col in colSchema.Rows) {
    Console.WriteLine($"欄位: {col["COLUMN_NAME"]}, 型別: {col["DATA_TYPE"]}, 序數: {col["ORDINAL_POSITION"]}");
}

步驟 3:動態建構查詢並填充 DataSet

var ds = new DataSet();
using (var adapter = new OleDbDataAdapter()) {
    foreach (string sheet in sheetNames) {
        adapter.SelectCommand = new OleDbCommand($"SELECT * FROM [{sheet}]", conn);
        string tableName = sheet.TrimEnd('$').Replace("'", "");
        try { adapter.Fill(ds, tableName); } catch { /* 略過圖表表等 */ }
    }
}
// ds 現在包含所有工作表資料,可依名稱存取:ds.Tables["Sheet1"]

完整 ASP.NET 範例:上傳 → 動態讀取 → 顯示

<asp:FileUpload ID="fuExcel" runat="server" />
<asp:Button ID="btnImport" Text="匯入" OnClick="btnImport_Click" runat="server" />
<asp:GridView ID="gvData" runat="server" />

protected void btnImport_Click(object sender, EventArgs e) {
    if (!fuExcel.HasFile) return;
    string ext = Path.GetExtension(fuExcel.FileName).ToLower();
    if (ext != ".xls" && ext != ".xlsx") { ShowMsg("僅支援 .xls/.xlsx"); return; }

    string savePath = Server.MapPath($"~/App_Data/Uploads/{Guid.NewGuid()}{ext}");
    fuExcel.SaveAs(savePath);

    string provider = ext == ".xls" ? "Microsoft.Jet.OLEDB.4.0" : "Microsoft.ACE.OLEDB.12.0";
    string extProps = ext == ".xls" ? "Excel 8.0" : "Excel 12.0 Xml";
    string connStr = $"Provider={provider};Data Source={savePath};Extended Properties=\"{extProps};HDR=YES;IMEX=1\";";

    var ds = new DataSet();
    using (var conn = new OleDbConnection(connStr)) {
        conn.Open();
        var tables = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
            new object[] { null, null, null, "SYSTEM_TABLE" });
        using (var adapter = new OleDbDataAdapter()) {
            foreach (DataRow row in tables.Rows) {
                string sheet = row["TABLE_NAME"].ToString();
                if (!sheet.EndsWith("$")) continue;
                adapter.SelectCommand = new OleDbCommand($"SELECT * FROM [{sheet}]", conn);
                try { adapter.Fill(ds, sheet.TrimEnd('$')); } catch { }
            }
        }
    }
    gvData.DataSource = ds.Tables.Count > 0 ? ds.Tables[0] : null;
    gvData.DataBind();
}

總結檢核表

  • [ ] Provider 版本與檔案格式對應 (.xls→Jet 4.0, .xlsx→ACE 12.0+)
  • [ ] 絕對物理路徑
  • [ ] 方括號 + $ 引用工作表
  • [ ] HDR=YES/NO 依實際標頭列決定
  • [ ] IMEX=1 處理混合型別欄位
  • [ ] 64 位元環境安裝對應 Provider 或設定 Prefer 32-bit
  • [ ] 大檔案考慮 OpenXML SDK / EPPlus 串流處理

掌握以上要點,即可在 ASP.NET / WinForms / Console 等任何 .NET 應用中穩健讀取 Excel 資料。

copyright © 萬盛學電腦網 all rights reserved