怎样使用asp将excel中数据导入到sql server数据库中

发布网友发布时间：2022-04-27 11:19

共1个回答

热心网友时间：2023-09-14 22:01

Sub toSql()
set excelconn=server.createobject("adodb.connection")
times=Now()
days=Year(times)&Right(("0000"&Month(times)),2)&Right(("0000"&day(times)),2)
files="recordtotal/"&days&".xls"
strAddr = Server.MapPath(files) 'Excel源文件
Response.Write "源文件："&strAddr&" <br> <hr>"
excelconn.open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & strAddr '打开此文件

'建立excel记录集
set excelrs=server.createobject("adodb.recordset")
Set rs=server.createobject("adodb.recordset")
sql="select * from [Sheet1$]" '查询
excelrs.open sql,excelconn,1,1
While not excelrs.Eof
'-----------------------
a1=trim(excelrs(0))'人名
a2=trim(excelrs(1))'数量
a3=times'输入时间
sqls="select id from recordtotal where sname='"&a1&"' and datediff(d,stime,'"&a3&"')=0"
rs.open sqls,conn,1,3
If rs.eof Or rs.bof then
conn.execute"insert into recordtotal values('"&a1&"',"&a2&",'"&a3&"')"

Else
response.write "当天已有"&a1&"的记录!<br>此条导入失败。"
End If
rs.close
excelrs.Movenext
wend
excelrs.close()
set excelrs=nothing
excelconn.Close()
set excelconn=nothing

End Sub