asp excel导入mysql_用ASP上传Excel 表格并导入到数据库

set upload=new upload_5xSoft

set file=upload.file("file1")

formPath="../upload1/"

if file.filename="" then

response.Write ""

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

%>