ASP将Excel数据导入到SQLServer的实现代码(3)

end if
end function
%>
<%
session.CodePage=936
Server.ScriptTimeOut=600000
set upload=new upload_5xsoft
set file=upload.file("filexls")
%>
<%
if file.fileSize>0 then
filename=year(now)&month(now)&day(now)&hour(now)&minute(now)&second(now)
filename=filename+"."
filenameend=file.filename
filenameshow=file.filename
filenameend=split(filenameend,".")
if filenameend(1)="xls" then
filename=filename&filenameend(1)
file.saveAs Server.mappath("uploadfiles/"&filename)
else
response.write "数据格式不对！"
response.write "<a href=file_upload.asp>返回"
response.end()
end if
set file=nothing
else
response.write "文件不能为空！"
response.write "<a href=file_upload.asp>返回"
response.end()
End if
set upload=nothing
'上传XLS文件结束，下面从上传的XLS文件中读取数据写入到SQL数据库
strAddr=server.MapPath("uploadfiles/"&filename)
set excelconn=server.createobject("adodb.connection")
excelconn.open "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = "+strAddr+";Extended Properties='Excel 8.0;HDR=NO;IMEX=1'"
set rs=server.CreateObject("adodb.recordset")
set rs1=server.CreateObject("adodb.recordset")
sql="select * from [Sheet1$]"
rs.open sql,excelconn,1,3
if not(rs.bof and rs.eof) then
rs.movenext
do while not rs.eof
'response.Write(rs(1))
'response.End()
sql1="select * from member"
rs1.open sql1,conn,1,3
rs1.addnew
Randomize
username=""
Do While Len(username)<8 '随机密码位数
num1=CStr(Chr((57-48)*rnd+48)) '0~9
'num2=CStr(Chr((90-65)*rnd+65)) 'A~Z
num3=CStr(Chr((122-97)*rnd+97)) 'a~z
username=username&num1&num3
loop
rs1("username")=username
rs1("password")="bb0391ec1d7bda99"'bamboo123456
if rs(0)<>"" then
rs1("company")=rs(0)
end if
if rs(1)<>"" then
rs1("realname")=rs(1)
end if
if rs(2)<>"" then
rs1("sex")=sexn(rs(2))
end if
if rs(3)<>"" then
rs1("prof")=rs(3)
end if
if rs(4)<>"" then
rs1("tel")=rs(4)
end if
if rs(5)<>"" then
rs1("mobile")=rs(5)
end if
if rs(6)<>"" then
rs1("address")=rs(6)
end if
if rs(7)<>"" then
rs1("area")=getclassdname(rs(7),"area","cn")
end if
if rs(8)<>"" then
rs1("city")=getclassdname(rs(8),"area","cn")
end if
if rs(9)<>"" then
rs1("fax")=rs(9)
end if
if rs(10)<>"" then
rs1("comtype")=comtypem(rs(10))
end if
if rs(11)<>"" then
rs1("operation")=rs(11)
end if
rs1("passed")=1
rs1("activated")=1
rs1("lastlogintime")=now()
rs1.update
rs1.close
rs.movenext
loop
end if
rs.close()
set rs=nothing
set rs1=nothing
excelconn.Close()
set excelconn=nothing
conn.close()
set conn=nothing
function sexn(str)
select case str
case "男"
sexn=0
case "女"