ASP 透過 Jet OLEDB 讀取 Excel 並批次匯入 SQL Server

連線字串

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Data\Cert.xls;Extended Properties="Excel 8.0"

工作表命名規則

SQL 查詢時工作表名稱需加 $ 符號並用中括號括住：
SELECT * FROM [Sheet1$]

完整匯入範例

Sub ImportExcel(strFile, strSheet, objConn, strKind)
    Dim objXlsConn, rsXls, rsSql, cmd, sql, maxId
    Set objXlsConn = Server.CreateObject("ADODB.Connection")
    objXlsConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile & ";Extended Properties='Excel 8.0'"
    Set rsXls = Server.CreateObject("ADODB.Recordset")
    rsXls.Open "SELECT * FROM [" & strSheet & "$]", objXlsConn, 1, 1

    Set cmd = Server.CreateObject("ADODB.Command")
    cmd.ActiveConnection = objConn

    Do While Not rsXls.EOF
        ' 取得下一個 ID
        Set rsSql = objConn.Execute("SELECT MAX(Id) AS MaxId FROM Certificate")
        If Not rsSql.EOF And Not IsNull(rsSql("MaxId")) Then
            maxId = CLng(rsSql("MaxId")) + 1
        Else
            maxId = 1
        End If
        rsSql.Close

        ' 寫入 SQL Server
        sql = "INSERT INTO Certificate VALUES (" & maxId & ", '" & rsXls(0) & "', '" & rsXls(1) & "', '" & rsXls(2) & "', '" & Split(strKind,"-")(0) & "', '" & rsXls(3) & "', '" & rsXls(4) & "', '" & FormatDateTime(Date,2) & " " & Time & "')"
        cmd.CommandText = sql
        cmd.Execute

        rsXls.MoveNext
    Loop

    Response.Write "共匯入 " & rsXls.RecordCount & " 筆憑證資料。"
    rsXls.Close: objXlsConn.Close
End Sub

注意事項

• Excel 欄位標題列必須在第一列，資料從第二列開始。
• 混合型別欄位（數字/文字混雜）易導致 Jet 猜測錯誤，建議預先在 Excel 設定欄位格式為「文字」。
• 大量資料建議使用 BULK INSERT 或 OPENROWSET 直接在 SQL Server 端匯入，效能更佳。