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

<formaction="insert.asp"method="post"enctype="multipart/form-data"name="form1"onSubmit="b1_onclick()">
<tablewidth="500"border="1"align="center"cellpadding="0"cellspacing="0">
<tr>
<tdcolspan="2"bgcolor="#999999"class="t">选择文件
</td>
</tr>
<tr>
<tdcolspan="2"class="t"> </td>
</tr>
<tr>
<tdwidth="126"class="t">选择文件(excel)
</td>
<tdwidth="368"class="t"><label>
<inputname="filexls"type="file"size="35">
</label></td>
</tr>
<tr>
<tdcolspan="2"class="t">
<label>
<inputtype="submit"name="Submit"value="导入数据">
</label>
<ahref="1122.asp"class="t">返回</a>
</td>
</tr>
</table>
</form>
<!--#includevirtual="/inc/clsdbctrl.asp"-->
<!--#includevirtual="/inc/function.asp"-->
<%
dimupfile_5xSoft_Stream
Classupload_5xSoft
dimForm,File,Version
PrivateSubClass_Initialize
dimiStart,iFileNameStart,iFileNameEnd,iEnd,vbEnter,iFormStart,iFormEnd,theFile
dimstrDiv,mFormName,mFormValue,mFileName,mFileSize,mFilePath,iDivLen,mStr
Version="任翔专用上传程序"
ifRequest.TotalBytes<1thenExitSub
setForm=CreateObject("Scripting.Dictionary")
setFile=CreateObject("Scripting.Dictionary")
setupfile_5xSoft_Stream=CreateObject("Adodb.Stream")
upfile_5xSoft_Stream.mode=3
upfile_5xSoft_Stream.type=1
upfile_5xSoft_Stream.open
upfile_5xSoft_Stream.writeRequest.BinaryRead(Request.TotalBytes)
vbEnter=Chr(13)&Chr(10)
iDivLen=inString(1,vbEnter)+1
strDiv=subString(1,iDivLen)
iFormStart=iDivLen
iFormEnd=inString(iformStart,strDiv)-1
whileiFormStart<iFormEnd
iStart=inString(iFormStart,"name=""")
iEnd=inString(iStart+6,"""")
mFormName=subString(iStart+6,iEnd-iStart-6)
iFileNameStart=inString(iEnd+1,"filename=""")
ifiFileNameStart>0andiFileNameStart<iFormEndthen
iFileNameEnd=inString(iFileNameStart+10,"""")
mFileName=subString(iFileNameStart+10,iFileNameEnd-iFileNameStart-10)
iStart=inString(iFileNameEnd+1,vbEnter&vbEnter)
iEnd=inString(iStart+4,vbEnter&strDiv)
ifiEnd>iStartthen
mFileSize=iEnd-iStart-4
else
mFileSize=0
endif
settheFile=newFileInfo
theFile.FileName=getFileName(mFileName)
theFile.FilePath=getFilePath(mFileName)
theFile.FileSize=mFileSize
theFile.FileStart=iStart+4
theFile.FormName=FormName
file.addmFormName,theFile
else
iStart=inString(iEnd+1,vbEnter&vbEnter)
iEnd=inString(iStart+4,vbEnter&strDiv)
ifiEnd>iStartthen
mFormValue=subString(iStart+4,iEnd-iStart-4)
else
mFormValue=""
endif
form.AddmFormName,mFormValue
endif
iFormStart=iformEnd+iDivLen
iFormEnd=inString(iformStart,strDiv)-1
wend
EndSub
PrivateFunctionsubString(theStart,theLen)
dimi,c,stemp
upfile_5xSoft_Stream.Position=theStart-1
stemp=""
fori=1totheLen
ifupfile_5xSoft_Stream.EOSthenExitfor
c=ascB(upfile_5xSoft_Stream.Read(1))
Ifc>127Then
ifupfile_5xSoft_Stream.EOSthenExitfor
stemp=stemp&Chr(AscW(ChrB(AscB(upfile_5xSoft_Stream.Read(1)))&ChrB(c)))
i=i+1
else
stemp=stemp&Chr(c)
EndIf
Next
subString=stemp
Endfunction
PrivateFunctioninString(theStart,varStr)
dimi,j,bt,theLen,str
InString=0
Str=toByte(varStr)
theLen=LenB(Str)
fori=theStarttoupfile_5xSoft_Stream.Size-theLen
ifi>upfile_5xSoft_Stream.sizethenexitFunction
upfile_5xSoft_Stream.Position=i-1
ifAscB(upfile_5xSoft_Stream.Read(1))=AscB(midB(Str,1))then
InString=i
forj=2totheLen
ifupfile_5xSoft_Stream.EOSthen
inString=0
Exitfor
endif
ifAscB(upfile_5xSoft_Stream.Read(1))<>AscB(MidB(Str,j,1))then
InString=0
ExitFor
endif
next
ifInString<>0thenExitFunction
endif
next
EndFunction
PrivateSubClass_Terminate
form.RemoveAll
file.RemoveAll
setform=nothing
setfile=nothing
upfile_5xSoft_Stream.close
setupfile_5xSoft_Stream=nothing
EndSub
PrivatefunctionGetFilePath(FullPath)
IfFullPath<>""Then
GetFilePath=left(FullPath,InStrRev(FullPath,"\"))
Else
GetFilePath=""
EndIf
Endfunction
PrivatefunctionGetFileName(FullPath)
IfFullPath<>""Then
GetFileName=mid(FullPath,InStrRev(FullPath,"\")+1)
Else
GetFileName=""
EndIf
Endfunction
PrivatefunctiontoByte(Str)
dimi,iCode,c,iLow,iHigh
toByte=""
Fori=1ToLen(Str)
c=mid(Str,i,1)
iCode=Asc(c)
IfiCode<0TheniCode=iCode+65535
IfiCode>255Then
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))
EndIf
Next
Endfunction
EndClass
ClassFileInfo
dimFormName,FileName,FilePath,FileSize,FileStart
PrivateSubClass_Initialize
FileName=""
FilePath=""
FileSize=0
FileStart=0
FormName=""
EndSub
PublicfunctionSaveAs(FullPath)
dimdr,ErrorChar,i
SaveAs=1
iftrim(fullpath)=""orFileSize=0orFileStart=0orFileName=""thenexitfunction
ifFileStart=0orright(fullpath,1)="/"thenexitfunction
setdr=CreateObject("Adodb.Stream")
dr.Mode=3
dr.Type=1
dr.Open
upfile_5xSoft_Stream.position=FileStart-1
upfile_5xSoft_Stream.copytodr,FileSize
dr.SaveToFileFullPath,2
dr.Close
setdr=nothing
SaveAs=0
endfunction
EndClass
%>
<%
functionsqlstr(data)
ifnotisnull(data)then
sqlstr="""&replace(data,""","""")&"""
else
sqlstr="""&data&"""
endif
endfunction
%>
<%
session.CodePage=936
Server.ScriptTimeOut=600000
setupload=newupload_5xsoft
setfile=upload.file("filexls")
%>
<%
iffile.fileSize>0then
filename=year(now)&month(now)&day(now)&hour(now)&minute(now)&second(now)
filename=filename+"."
filenameend=file.filename
filenameshow=file.filename
filenameend=split(filenameend,".")
iffilenameend(1)="xls"then
filename=filename&filenameend(1)
file.saveAsServer.mappath("uploadfiles/"&filename)
else
response.write"数据格式不对！"
response.write"<ahref=file_upload.asp>返回"
response.end()
endif
setfile=nothing
else
response.write"文件不能为空！"
response.write"<ahref=file_upload.asp>返回"
response.end()
Endif
setupload=nothing
"上传XLS文件结束，下面从上传的XLS文件中读取数据写入到SQL数据库
strAddr=server.MapPath("uploadfiles/"&filename)
setexcelconn=server.createobject("adodb.connection")
excelconn.open"Provider=Microsoft.Jet.OLEDB.4.0;DataSource="+strAddr+";ExtendedProperties="Excel8.0;HDR=NO;IMEX=1""
setrs=server.CreateObject("adodb.recordset")
setrs1=server.CreateObject("adodb.recordset")
sql="select*from[Sheet1$]"
rs.opensql,excelconn,1,3
ifnot(rs.bofandrs.eof)then
rs.movenext
dowhilenotrs.eof
"response.Write(rs(1))
"response.End()
sql1="select*frommember"
rs1.opensql1,conn,1,3
rs1.addnew
Randomize
username=""
DoWhileLen(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
ifrs(0)<>""then
rs1("company")=rs(0)
endif
ifrs(1)<>""then
rs1("realname")=rs(1)
endif
ifrs(2)<>""then
rs1("sex")=sexn(rs(2))
endif
ifrs(3)<>""then
rs1("prof")=rs(3)
endif
ifrs(4)<>""then
rs1("tel")=rs(4)
endif
ifrs(5)<>""then
rs1("mobile")=rs(5)
endif
ifrs(6)<>""then
rs1("address")=rs(6)
endif
ifrs(7)<>""then
rs1("area")=getclassdname(rs(7),"area","cn")
endif
ifrs(8)<>""then
rs1("city")=getclassdname(rs(8),"area","cn")
endif
ifrs(9)<>""then
rs1("fax")=rs(9)
endif
ifrs(10)<>""then
rs1("comtype")=comtypem(rs(10))
endif
ifrs(11)<>""then
rs1("operation")=rs(11)
endif
rs1("passed")=1
rs1("activated")=1
rs1("lastlogintime")=now()
rs1.update
rs1.close
rs.movenext
loop
endif
rs.close()
setrs=nothing
setrs1=nothing
excelconn.Close()
setexcelconn=nothing
conn.close()
setconn=nothing
functionsexn(str)
selectcasestr
case"男"
sexn=0
case"女"
sexn=1
endselect
endfunction
functioncomtypem(str)
selectcasestr
case"竹制品"
comtypem=0
case"竹机械"
comtypem=1
endselect
endfunction
functiongetclassdname(str,tablename,lang)
IfNotIsNumeric(id)ThenExitFunction
setrs2=conn.execute("selecttop1idfrom"&tablename&"whereclassnamelike"%"&str&"%"")
ifnotrs2.eofThen
Iflang<>""Then
Iflang="cn"Then
getclassdname=getclassdname&rs2(0)
ElseIflang="en"Then
getclassdname=getclassdname&rs2(0)
EndIf
EndIf
else
getclassdname=0
endif
rs2.close
EndFunction
%>
<tablewidth="300"border="1"align="center"cellpadding="0"cellspacing="0"bordercolor="#CCCCCC">
<tr>
<thbordercolor="#F1F3F8"bgcolor="#999999"class="t"scope="row"> </th>
</tr>
<tr>
<thclass="t"scope="row">文件<%response.write(filenameshow)%>导入成功！</th>
</tr>
<tr>
<thclass="t"scope="row"><ahref="javascript:self.close()"class="t">关闭窗口</a></th>
</tr>
<tr>
<thclass="t"scope="row"><ahref="1122.asp"class="t">返回</a></th>
</tr>
</table>