最近在有朋友说要把些excel数据输入输入到access数据库中。太多了,找偶帮忙。我在网上找了段代码,试了下,没成功。结果是有的数据正常导入进去了,有的导入到数据库里显示的是空,有的确导不进去。请朋友们抽空给诊诊。代码如下:
<%
dim FileName
FileName="excel.xls" '取得文件名,来自项目经理的指定,路径固定在某个虚拟路径中
Dim conn, rs
set conn=CreateObject("ADODB.connection")
conn.Open "Driver={Microsoft Excel Driver (*.xls)};" & _
"DriverId=790;" & _
"Dbq=" & server.mappath(""&FileName&"") & ";" & _
"DefaultDir=G:/"
set rs=createobject("ADODB.recordset")
rs.Open "Select * From [Sheet1$]",conn, 2, 2
if rs.eof then
response.write "Excel表中无纪录"
else
set connDB = Server.CreateObject("ADODB.Connection")
DBPath = Server.MapPath("excel.mdb")
'RESPONSE.WRITE DBpath
connDB.Open "driver={Microsoft Access Driver (*.mdb)};dbq=" & DBPath
Set RsDB = Server.CreateObject("ADODB.Recordset")
SqlDB="Select * from excel"
RsDB.open sqlDB,connDB,1,3
do while not rs.eof '利用循环读出数据
' response.write rs(0)&" "
' response.write rs(1)&" "
' response.write rs(2)&" "
' response.write rs(3)&" "
' response.write rs(4)&" "
' response.write rs(5)&" "
' response.write rs(6)&" "
' response.write rs(7)&" "
' response.write rs(8)&" "
' response.write rs(9)&" "
' response.write rs(10)&" "
' response.write rs(11)&" "
' response.write rs(12)&" "
' response.write rs(13)&" "
' response.write rs(14)&" "
' response.write rs(15)&" "
' response.write rs(16)&" "
' response.write rs(17)&" "
' response.write rs(18)&" "
' response.write rs(19)&" "
' response.write rs(20)&" "
' response.write rs(21)&" "
' response.write rs(22)&" "
' response.write rs(23)&" "
' response.write rs(24)&" "
' response.write rs(25)&" "
' response.write rs(26)&" "
' response.write rs(27)&" "
' response.write rs(28)&" "
' response.write rs(29)&" "
' response.write rs(30)&" "
' response.write rs(31)&" "
' response.write rs(32)&" "
' response.write rs(33)&" "
' response.write rs(34)&" "
' response.write "<br>"
RsDB.addnew
RsDB("MemberID")=rs(0)
RsDB("Name")=rs(1)
RsDB("BiName")=rs(2)
RsDB("TuanTi")=rs(3)
RsDB("Mobile")=rs(4)
RsDB("Tel")=rs(5)
RsDB("ID")=rs(6)
RsDB("RuHuiDay")=rs(7)
RsDB("Sex")=rs(8)
RsDB("BirthDay")=rs(9)
RsDB("ZhiCheng")=rs(10)
RsDB("Native")=rs(11)
RsDB("Party")=rs(12)
RsDB("Nation")=rs(13)
RsDB("XueLi")=rs(14)
RsDB("DanWei")=rs(15)
RsDB("Zip")=rs(16)
RsDB("Address")=rs(17)
RsDB("recommend1")=rs(18)
RsDB("recommend2")=rs(19)
RsDB("ShenPiYiJian")=rs(20)
RsDB("YiShuZC")=rs(21)
RsDB("YiShuJianLi")=rs(22)
RsDB("ZhaoPian")=rs(23)
RsDB("Status")=rs(24)
RsDB("RegisterTime")=rs(25)
RsDB("UpdateTime")=rs(26)
RsDB("UploadTime")=rs(27)
RsDB("Level")=rs(28)
RsDB("StandBy1")=rs(29)
RsDB("StandBy2")=rs(30)
RsDB("StandBy3")=rs(31)
RsDB("StandBy4")=rs(32)
RsDB("StandBy5")=rs(33)
RsDB("StandBy6")=rs(34)
Rs.update
RsDB.movenext
rs.movenext
loop
'response.redirect FileName
end if
RsDB.movefirst
if RsDB.eof then
response.write "数据库中无记录"
else
do while not RsDB.EOF
response.write RsDB("MemberID")&" "
response.write RsDB("Name")&" "
response.write RsDB("BiName")&" "
response.write RsDB("TuanTi")&" "
response.write RsDB("Mobile")&" "
response.write RsDB("Tel")&" "
response.write RsDB("ID")&" "
response.write RsDB("RuHuiDay")&" "
response.write RsDB("Sex")&" "
response.write RsDB("BirthDay")&" "
response.write RsDB("ZhiCheng")&" "
response.write RsDB("Native")&" "
response.write RsDB("Party")&" "
response.write RsDB("Nation")&" "
response.write RsDB("XueLi")&" "
response.write RsDB("DanWei")&" "
response.write RsDB("Zip")&" "
response.write RsDB("Address")&" "
response.write RsDB("recommend1")&" "
response.write RsDB("recommend2")&" "
response.write RsDB("ShenPiYiJian")&" "
response.write RsDB("YiShuZC")&" "
response.write RsDB("YiShuJianLi")&" "
response.write RsDB("ZhaoPian")&" "
response.write RsDB("Status")&" "
response.write RsDB("RegisterTime")&" "
response.write RsDB("UpdateTime")&" "
response.write RsDB("UploadTime")&" "
response.write RsDB("Level")&" "
response.write RsDB("StandBy1")&" "
response.write RsDB("StandBy2")&" "
response.write RsDB("StandBy3")&" "
response.write RsDB("StandBy4")&" "
response.write RsDB("StandBy5")&" "
response.write RsDB("StandBy6")&" "
response.write "<br>"
RsDB.movenext
loop
end if
rs.close
set rs=nothing
set conn=nothing
%>