Question

怎么把excel的表格导入asp

Answer 1

<%
set upload=new upload_5xSoft
set file=upload.file("file1")
formPath="../upload1/"
if file.filename="" then
response.Write "<script language='javascript'>alert('你没选择文件！');history.go(-1);</script>"
else
if file.filesize>1000 then
fileExt=lcase(right(file.filename,3))
if fileExt<>"xls" then
Response.Write"文件类型非法"
Response.end
end if
end if
randomize
ranNum=int(9000000*rnd)+10000
filename=formPath&year(now)&month(now)&day(now)&hour(now)&minute(now)&second(now)&ranNum&"."&fileExt
if file.FileSize>0 then
file.SaveAs Server.mappath(FileName)
end if
connstr = "driver={microsoft Access driver (*.mdb)}; dbq=" & server.mappath("../../data/#data.mdb")
set conn3 = server.createobject("ADODB.CONNECTION")
conn3.open connstr
set rs=server.createobject("adodb.recordset")
rs.Open "select * from User where UserName='"&Session("user")&"' " ,conn3,3,3
rs("log")=cstr(FileName)
rs.update
rs.close
conn3.close

'这里用来写导入数据库的程序
kl=FileName
op=len(kl)
op2= mid(kl,3,op-2)
strAddr = Server.MapPath(""&kl)
set excelconn=server.createobject("adodb.connection")
'strAddr = file
''输出源文件名
excelconn.open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & strAddr
''建立excel记录集
set rs2=server.createobject("adodb.recordset")
set rs3= Server.CreateObject("ADODB.Recordset")
set rs3 = excelconn.OpenSchema(20)
op=rs3("TABLE_NAME")
sql="select * from "+"["+ op+"]"
rs2.open sql,excelconn,1,1
'找到表
connstr = "driver={microsoft Access driver (*.mdb)}; dbq=" & server.mappath("../../data/#data1.mdb")
set conn = server.createobject("ADODB.CONNECTION")
conn.open connstr
strSQL="Delete * From Sheet1 Where user='"& Session("user")&"'"
conn.execute strSQL
connstr = "driver={microsoft Access driver (*.mdb)}; dbq=" & server.mappath("../../data/#data1.mdb")
set conn = server.createobject("ADODB.CONNECTION")
'删除以前的信息
conn.open connstr
set rs4= Server.CreateObject("ADODB.Recordset")
rs4.open "select * from Sheet1",conn,3,3
if rs4.Fields.Count-3 <> rs2.Fields.Count-1 then
response.write "要上传的表格格式不对，不能导入统一数据库！"
response.end
else

'导入过程

for j = 1 to rs2.recordCount

rs4.addnew
for i = 0 to rs2.Fields.Count-1

rs4.Fields("user")=Session("user")

rs4.Fields(i+1)=rs2.Fields(i)
next
rs2.movenext

next

end if

' ............'

kl=""

strSQL="delete FROM Sheet1 where isnull(品名) = true"
conn.execute strSQL
conn.close
response.write "成功 "
response.redirect "../../index.asp"
end if
%>