ASP.NET 實現 Excel 上傳導入 SQL Server 完整指南

核心三步驟

1. 前端上傳：將 Excel 存至伺服器

<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button ID="BtnUpload" runat="server" Text="上傳" OnClick="BtnUpload_Click" />

protected void BtnUpload_Click(object sender, EventArgs e) {
    if (FileUpload1.HasFile) {
        string path = @"E:\Temp\" + FileUpload1.FileName;
        FileUpload1.SaveAs(path);  // 實際部署請加例外處理
        // 後續導入邏輯...
    }
}

2. 結構對齊：SQL Server 表結構需與 Excel 完全一致

• 欄位順序、名稱、型別逐一對應。
• Excel 第一列作為標題（HDR=YES）。
• 工作表名稱需精確（如 Teenie$，尾碼 $ 必備）。

3. 核心導入 SQL：OPENROWSET

INSERT INTO EmployeesCheck
SELECT * FROM OPENROWSET(
    'MICROSOFT.JET.OLEDB.4.0',
    'Excel 8.0;HDR=YES;DATABASE=E:\\Temp\\' + FileUpload1.FileName,
    'Teenie$'
);

• EmployeesCheck：目標表。
• Teenie$：Excel 工作表名 + $。

必須啟用：Ad Hoc Distributed Queries

SQL Server 2005+ 預設關閉，需執行：

EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'Ad Hoc Distributed Queries', 1; RECONFIGURE;

完整後端範例

protected void BtnImport_Click(object sender, EventArgs e) {
    if (!FileUpload1.HasFile) return;

    string filePath = Server.MapPath("~/Uploads/") + FileUpload1.FileName;
    FileUpload1.SaveAs(filePath);

    string connStr = ConfigurationManager.ConnectionStrings["SqlConn"].ConnectionString;
    using (SqlConnection conn = new SqlConnection(connStr)) {
        string sql = $@"
            INSERT INTO EmployeesCheck
            SELECT * FROM OPENROWSET(
                'MICROSOFT.JET.OLEDB.4.0',
                'Excel 8.0;HDR=YES;DATABASE={filePath}',
                'Teenie$'
            )";
        SqlCommand cmd = new SqlCommand(sql, conn);
        conn.Open();
        cmd.ExecuteNonQuery();
    }
    // 導入後可刪除暫存檔
}

限制與替代方案

企業級建議

• 使用 EPPlus、NPOI、ClosedXML 等純 .NET 程式庫讀取 Excel，再批次 SqlBulkCopy 寫入，脫離 OLEDB 依賴，支援 .xlsx、動態對應欄位、資料驗證。
• 前端加檔案型別、大小驗證；後端交易包裹，失敗回滾並記錄錯誤行。