asp将excel导入数据库文件

<%@ Language=VBScript %>
<%
Dim conn, cmd, xls, sheet, row, col, data
Dim filePath, sql

' 设置文件路径
filePath = "C:\path\to\your\file.xlsx"

' 连接数据库
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB;Data Source=your_server;Initial Catalog=your_database;Integrated Security=SSPI;"

' 打开Excel文件
Set xls = CreateObject("Excel.Application")
xls.Workbooks.Open filePath
Set sheet = xls.Worksheets(1)

' 构建SQL插入语句
sql = "INSERT INTO your_table (column1, column2, column3) VALUES (?, ?, ?)"

' 执行插入操作
Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = conn
cmd.CommandText = sql
cmd.Parameters.Append cmd.CreateParameter("@column1", adVarChar, adParamInput, 50)
cmd.Parameters.Append cmd.CreateParameter("@column2", adVarChar, adParamInput, 50)
cmd.Parameters.Append cmd.CreateParameter("@column3", adVarChar, adParamInput, 50)

row = 2 ' 数据从第二行开始
Do While Not sheet.Cells(row, 1).Value = ""
    cmd.Parameters("@column1").Value = sheet.Cells(row, 1).Value
    cmd.Parameters("@column2").Value = sheet.Cells(row, 2).Value
    cmd.Parameters("@column3").Value = sheet.Cells(row, 3).Value
    cmd.Execute
    row = row + 1
Loop

' 关闭连接
xls.Workbooks.Close
xls.Quit
Set xls = Nothing
cmd.Parameters.Clear
cmd.ActiveConnection = Nothing
Set cmd = Nothing
conn.Close
Set conn = Nothing

Response.Write "数据导入成功！"
%>