ASP將Excel資料匯入SQLServer的程式碼,有點亂，大家依需求自己找下。複製代碼代碼如下:
<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>