<form action="insert.asp" method="post" enctype="multipart/form-data" name="form1" onsubmit="b1_onclick()">
<table width="500" border="1" align="center" cellpadding="0" cellspacing="0">
<tr>
<td colspan="2" bgcolor="#999999" class="t">选择文件
</td>
</tr>
<tr>
<td colspan="2" class="t"> </td>
</tr>
<tr>
<td width="126" class="t">选择文件(excel)
</td>
<td width="368" class="t"><label>
<input name="filexls" type="file" size="35">
</label></td>
</tr>
<tr>
<td colspan="2" class="t">
<label>
<input type="submit" name="submit" value="导入数据">
</label>
<a href="1122.asp" class="t">返回</a>
</td>
</tr>
</table>
</form>
<!--#include virtual="/inc/clsdbctrl.asp"-->
<!--#include virtual="/inc/function.asp"-->
<%
dim upfile_5xsoft_stream
class upload_5xsoft
dim form,file,version
private sub class_initialize
dim istart,ifilenamestart,ifilenameend,iend,vbenter,iformstart,iformend,thefile
dim strdiv,mformname,mformvalue,mfilename,mfilesize,mfilepath,idivlen,mstr
version="任翔专用上传程序"
if request.totalbytes<1 then exit sub
set form=createobject("scripting.dictionary")
set file=createobject("scripting.dictionary")
set upfile_5xsoft_stream=createobject("adodb.stream")
upfile_5xsoft_stream.mode=3
upfile_5xsoft_stream.type=1
upfile_5xsoft_stream.open
upfile_5xsoft_stream.write request.binaryread(request.totalbytes)
vbenter=chr(13)&chr(10)
idivlen=instring(1,vbenter)+1
strdiv=substring(1,idivlen)
iformstart=idivlen
iformend=instring(iformstart,strdiv)-1
while iformstart < iformend
istart=instring(iformstart,"name=""")
iend=instring(istart+6,"""")
mformname=substring(istart+6,iend-istart-6)
ifilenamestart=instring(iend+1,"filename=""")
if ifilenamestart>0 and ifilenamestart<iformend then
ifilenameend=instring(ifilenamestart+10,"""")
mfilename=substring(ifilenamestart+10,ifilenameend-ifilenamestart-10)
istart=instring(ifilenameend+1,vbenter&vbenter)
iend=instring(istart+4,vbenter&strdiv)
if iend>istart then
mfilesize=iend-istart-4
else
mfilesize=0
end if
set thefile=new fileinfo
thefile.filename=getfilename(mfilename)
thefile.filepath=getfilepath(mfilename)
thefile.filesize=mfilesize
thefile.filestart=istart+4
thefile.formname=formname
file.add mformname,thefile
else
istart=instring(iend+1,vbenter&vbenter)
iend=instring(istart+4,vbenter&strdiv)
if iend>istart then
mformvalue=substring(istart+4,iend-istart-4)
else
mformvalue=""
end if
form.add mformname,mformvalue
end if
iformstart=iformend+idivlen
iformend=instring(iformstart,strdiv)-1
wend
end sub
private function substring(thestart,thelen)
dim i,c,stemp
upfile_5xsoft_stream.position=thestart-1
stemp=""
for i=1 to thelen
if upfile_5xsoft_stream.eos then exit for
c=ascb(upfile_5xsoft_stream.read(1))
if c > 127 then
if upfile_5xsoft_stream.eos then exit for
stemp=stemp&chr(ascw(chrb(ascb(upfile_5xsoft_stream.read(1)))&chrb(c)))
i=i+1
else
stemp=stemp&chr(c)
end if
next
substring=stemp
end function
private function instring(thestart,varstr)
dim i,j,bt,thelen,str
instring=0
str=tobyte(varstr)
thelen=lenb(str)
for i=thestart to upfile_5xsoft_stream.size-thelen
if i>upfile_5xsoft_stream.size then exit function
upfile_5xsoft_stream.position=i-1
if ascb(upfile_5xsoft_stream.read(1))=ascb(midb(str,1)) then
instring=i
for j=2 to thelen
if upfile_5xsoft_stream.eos then
instring=0
exit for
end if
if ascb(upfile_5xsoft_stream.read(1))<>ascb(midb(str,j,1)) then
instring=0
exit for
end if
next
if instring<>0 then exit function
end if
next
end function
private sub class_terminate
form.removeall
file.removeall
set form=nothing
set file=nothing
upfile_5xsoft_stream.close
set upfile_5xsoft_stream=nothing
end sub
private function getfilepath(fullpath)
if fullpath <> "" then
getfilepath = left(fullpath,instrrev(fullpath, "\"))
else
getfilepath = ""
end if
end function
private function getfilename(fullpath)
if fullpath <> "" then
getfilename = mid(fullpath,instrrev(fullpath, "\")+1)
else
getfilename = ""
end if
end function
private function tobyte(str)
dim i,icode,c,ilow,ihigh
tobyte=""
for i=1 to len(str)
c=mid(str,i,1)
icode =asc(c)
if icode<0 then icode = icode + 65535
if icode>255 then
ilow = left(hex(asc(c)),2)
ihigh =right(hex(asc(c)),2)
tobyte = tobyte & chrb("&h"&ilow) & chrb("&h"&ihigh)
else
tobyte = tobyte & chrb(ascb(c))
end if
next
end function
end class
class fileinfo
dim formname,filename,filepath,filesize,filestart
private sub class_initialize
filename = ""
filepath = ""
filesize = 0
filestart= 0
formname = ""
end sub
public function saveas(fullpath)
dim dr,errorchar,i
saveas=1
if trim(fullpath)="" or filesize=0 or filestart=0 or filename="" then exit function
if filestart=0 or right(fullpath,1)="/" then exit function
set dr=createobject("adodb.stream")
dr.mode=3
dr.type=1
dr.open
upfile_5xsoft_stream.position=filestart-1
upfile_5xsoft_stream.copyto dr,filesize
dr.savetofile fullpath,2
dr.close
set dr=nothing
saveas=0
end function
end class
%>
<%
function sqlstr(data)
if not isnull(data) then
sqlstr="'"& replace(data,"'","''") &"'"
else
sqlstr="'"& data &"'"
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 "女"
sexn=1
end select
end function
function comtypem(str)
select case str
case "竹制品"
comtypem=0
case "竹机械"
comtypem=1
end select
end function
function getclassdname(str,tablename,lang)
if not isnumeric(id) then exit function
set rs2=conn.execute ("select top 1 id from "& tablename &" where classname like '%"&str&"%'")
if not rs2.eof then
if lang<>"" then
if lang="cn" then
getclassdname=getclassdname & rs2(0)
elseif lang="en" then
getclassdname=getclassdname & rs2(0)
end if
end if
else
getclassdname=0
end if
rs2.close
end function
%>
<table width="300" border="1" align="center" cellpadding="0" cellspacing="0" bordercolor="#cccccc">
<tr>
<th bordercolor="#f1f3f8" bgcolor="#999999" class="t" scope="row"> </th>
</tr>
<tr>
<th class="t" scope="row">文件<% response.write (filenameshow) %>导入成功！</th>
</tr>
<tr>
<th class="t" scope="row"><a href="javascript:self.close()" class="t">关闭窗口</a></th>
</tr>
<tr>
<th class="t" scope="row"><a href="1122.asp" class="t">返回</a></th>
</tr>
</table>