ASP.NET 讀取 Excel 至 DataTable 完整元件（支援多工作表、自動偵測、OLEDB 參數化）

核心類別：ExcelHelper

public class ExcelHelper {
    /// 
取得檔案所有工作表名稱
    public ArrayList GetWorkSheets(string filePath) {
        var sheets = new ArrayList();
        using (var conn = new OleDbConnection(GetConnectionString(filePath))) {
            conn.Open();
            var schema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            if (schema == null) throw new Exception("無法取得 Excel 結構描述");

            foreach (DataRow row in schema.Rows) {
                string name = row["TABLE_NAME"].ToString();
                // 移除結尾 $ 符號
                int idx = name.IndexOf('$');
                if (idx > 0) name = name.Substring(0, idx);
                // 移除單引號包裹
                if (name.StartsWith("'") && name.EndsWith("'"))
                    name = name.Substring(1, name.Length - 2);
                else if (name.StartsWith("'"))
                    name = name.Substring(1);

                if (!sheets.Contains(name)) sheets.Add(name);
            }
        }
        return sheets;
    }

    /// 
讀取指定工作表資料
    public DataTable GetData(string filePath, string sheetName) {
        var dt = new DataTable();
        using (var conn = new OleDbConnection(GetConnectionString(filePath))) {
            using (var adapter = new OleDbDataAdapter($"SELECT * FROM [{sheetName}$]", conn)) {
                conn.Open();
                adapter.FillSchema(dt, SchemaType.Mapped);
                adapter.Fill(dt);
            }
        }
        dt.TableName = sheetName;
        return dt;
    }

    /// 
讀取第一個工作表
    public DataTable GetFirstSheetData(string filePath) {
        var sheets = GetWorkSheets(filePath);
        if (sheets.Count == 0) throw new Exception("Excel 無工作表");
        return GetData(filePath, sheets[0].ToString());
    }

    private string GetConnectionString(string filePath) {
        if (!File.Exists(filePath)) throw new FileNotFoundException("Excel 檔案不存在", filePath);
        return $"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={filePath};Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1;\"";
    }
}

使用範例

var helper = new ExcelHelper();

// 1. 列出工作表
foreach (string sheet in helper.GetWorkSheets(@"D:\Data\Sales.xls")) {
    Console.WriteLine(sheet);
}

// 2. 讀取指定工作表
DataTable dt = helper.GetData(@"D:\Data\Sales.xls", "Sheet1");
gvData.DataSource = dt;
gvData.DataBind();

// 3. 直接讀第一張表
DataTable dt2 = helper.GetFirstSheetData(@"D:\Data\Sales.xls");

關鍵細節解析

OleDbSchemaGuid.Tables 取得結構描述

• 回傳 DataTable 欄位：TABLE_NAME、TABLE_TYPE 等。
• TABLE_NAME 格式：Sheet1$、'Sheet 2'$、_FilterDatabase（系統表，需過濾）。

工作表名稱清理邏輯

// 原始：'Sheet 2'$ 或 Sheet1$
// 步驟 1: 移除 $ 之後內容
// 步驟 2: 移除首尾單引號
// 結果：Sheet 2 / Sheet1

IMEX=1 重要性

混合型別欄位（前幾列數字、後面文字），預設 OLEDB 掃描前 8 列決定型別，導致文字讀成 null。IMEX=1 強制全部以文字讀取，再由上層轉型。

FillSchema + Fill 雙重填充

• FillSchema：建立 DataTable 欄位結構、資料型別、主鍵資訊。
• Fill：填入實際資料。
• 順序不可顛倒，否則型別推斷可能不準。

進階擴充

支援 .xlsx (ACE 12.0)

private string GetConnectionString(string filePath) {
    string ext = Path.GetExtension(filePath).ToLower();
    if (ext == ".xlsx")
        return $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={filePath};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1;\"";
    return $"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={filePath};Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1;\"";
}

大量資料分批讀取 (避免記憶體暴增)

public IEnumerable ReadInBatches(string filePath, string sheetName, int batchSize = 1000) {
    using (var conn = new OleDbConnection(GetConnectionString(filePath))) {
        using (var cmd = new OleDbCommand($"SELECT * FROM [{sheetName}$]", conn)) {
            conn.Open();
            using (var reader = cmd.ExecuteReader()) {
                var dt = new DataTable();
                dt.Load(reader); // 這裡仍會一次載入，真正分批需用 reader 手動讀取
                yield return dt;
            }
        }
    }
}

真正串流讀取建議改用 ExcelDataReader 或 EPPlus 逐列處理。

常見錯誤排查

部署檢查清單

1. 伺服器安裝 Microsoft Access Database Engine 2010/2016 Redistributable。
2. IIS 應用程式集區 啟用 32-bit 應用程式 = True（若安裝 32-bit ACE）。
3. 應用程式身分識別對 Excel 檔案資料夾具備 讀取權限。
4. web.config 目標框架 .NET Framework 4.0+。