ADO.NET 讀取 Excel 完整實作：連接字串、工作表引用、動態 Schema、IMEX 技巧

基本連接與查詢

使用 Microsoft.Jet.OLEDB.4.0 Provider（適用 .xls，.xlsx 需 Microsoft.ACE.OLEDB.12.0）：

string xlsPath = Server.MapPath("~/app_data/somefile.xls");
string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                 "Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\";" +
                 "Data Source=" + xlsPath;

string sql = "SELECT * FROM [Sheet1$]";
DataSet ds = new DataSet();
using (OleDbDataAdapter da = new OleDbDataAdapter(sql, connStr))
{
    da.Fill(ds);
}
GridView1.DataSource = ds.Tables[0];
GridView1.DataBind();

Extended Properties 關鍵設定

IMEX=1 為何重要？ 當欄位前幾列為數字、後面出現文字，預設會將文字讀為 NULL；設 IMEX=1 強制全部以文字讀取，避免資料遺失。

工作表/範圍引用語法

• 整張工作表：[Sheet1$]（必須加 $ 且用 [] 包裹）
• 指定範圍：[Sheet1$A1:C10]
• 命名範圍：[MyNamedRange]（不需工作表名）

常見錯誤：漏寫 $、[]、工作表名稱有空格未加單引號（['Sheet 1$']）。

動態取得工作表清單（Schema）

using (OleDbConnection conn = new OleDbConnection(connStr))
{
    conn.Open();
    // 取得所有 TABLE 類型（工作表與命名範圍）
    DataTable schema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
        new object[] { null, null, null, "TABLE" });

    // schema.Columns 包含 TABLE_NAME, TABLE_TYPE 等
    foreach (DataRow row in schema.Rows)
    {
        string tableName = row["TABLE_NAME"].ToString(); // 如 Sheet1$, MyRange
        // 可動態建構 SELECT * FROM [tableName]
    }
}

動態讀取所有工作表資料

List<string> tableNames = new List<string>();
foreach (DataRow row in schema.Rows)
    tableNames.Add(row["TABLE_NAME"].ToString());

DataSet dsAll = new DataSet();
using (OleDbConnection conn = new OleDbConnection(connStr))
{
    conn.Open();
    foreach (string tbl in tableNames)
    {
        string sql = "SELECT * FROM [" + tbl + "]";
        using (OleDbDataAdapter da = new OleDbDataAdapter(sql, conn))
        {
            try { da.Fill(dsAll, tbl); }
            catch { /* 單表失敗不中斷整體 */ }
        }
    }
}
// dsAll.Tables[0] 對應第一個工作表，以此類推

取得欄位 Schema（Column 資訊）

DataTable colSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns,
    new object[] { null, null, "Sheet1$", null });
// 欄位：COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH 等

完整可重用輔助類別

public static class ExcelHelper
{
    public static DataSet ReadAllSheets(string filePath, bool hasHeader = true)
    {
        string ext = Path.GetExtension(filePath).ToLower();
        string provider = ext == ".xlsx" ? "Microsoft.ACE.OLEDB.12.0" : "Microsoft.Jet.OLEDB.4.0";
        string excelVer = ext == ".xlsx" ? "Excel 12.0 Xml" : "Excel 8.0";
        string hdr = hasHeader ? "YES" : "NO";

        string connStr = $@"Provider={provider};Extended Properties=""{excelVer};HDR={hdr};IMEX=1"";Data Source={filePath}";

        DataSet ds = new DataSet();
        using (OleDbConnection conn = new OleDbConnection(connStr))
        {
            conn.Open();
            DataTable schema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
                new object[] { null, null, null, "TABLE" });

            foreach (DataRow row in schema.Rows)
            {
                string tbl = row["TABLE_NAME"].ToString();
                if (tbl.EndsWith("_") || tbl.EndsWith("$'")) continue; // 略過系統表
                using (OleDbDataAdapter da = new OleDbDataAdapter($"SELECT * FROM [{tbl}]", conn))
                {
                    da.Fill(ds, tbl.Replace("$", "").Replace("'", ""));
                }
            }
        }
        return ds;
    }
}

注意事項總結

1. 64 位元環境需安裝 64-bit Access Database Engine，或專案設定 Prefer 32-bit。
2. Excel 檔案被獨佔開啟時會拋例外，建議先複製到暫存目錄再讀取。
3. 大量資料建議用 SqlBulkCopy 寫入 SQL Server，而非逐筆 Insert。
4. .xlsx 格式優先使用 EPPlus、ClosedXML、NPOI 等純 .NET 函式庫，免裝驅動、支援樣式/公式、效能更好。