asp实现excel中的数据导入数据库
asp实现excel中的数据导入数据库
<%Response.CodePage=65001%>
<%Response.Charset="UTF-8"%>
<%
wenjian=request.Form("select")
'获取文件扩展名
ext=FileExec(wenjian)
'判断文件扩展名
ifext<>"xls"then
response.Write("<script>alert('文件类型不对，请核实！');window.location.href='index.html';</script>")
response.End()
endif
DimobjConn,objRS
DimstrConn,strSql
setobjConn=Server.CreateObject("ADODB.Connection")
setobjRS=Server.CreateObject("ADODB.Recordset")
excelFile=server.mappath(wenjian)
'针对excel2007
strConn="Provider=Microsoft.ACE.OLEDB.12.0;DataSource="&excelFile&";"&"ExtendedProperties=Excel8.0;"
objConn.OpenstrConn
strSql="SELECT*FROM[Sheet1$]"
objRS.OpenstrSql,objConn,1,1
objRS.MoveFirst
%><!--#includefile="conn.asp"--><%
'循环excel中所有记录
whilenotobjRS.eof
setrs=Server.CreateObject("Adodb.Recordset")
'查询语句
sql_s="select*fromceshiwherelname='"&objRS(0)&"'andold='"&objRS(1)&"'andsex='"&objRS(2)&"'andguojia='"&objRS(3)&"'andQQ='"&objRS(4)&"'"
rs.opensql_s,conn,1,1
'重复的数据不做录入操作
ifrs.eofthen
'插入语句
'****excel中第一条不会被录入****
sql="insertintoceshi(lname,old,sex,guojia,QQ)values('"&objRS(0)&"','"&objRS(1)&"','"&objRS(2)&"','"&objRS(3)&"','"&objRS(4)&"')"
'执行插入
conn.execute(sql)
endif
objRS.MoveNext
rs.close
setrs=nothing
wend
'又到了各种关闭的时候
conn.close
setconn=nothing
objRS.Close
objConn.Close
setobjRS=Nothing
setobjConn=Nothing
response.Write("<script>alert('导入成功');window.location.href='index.html';</script>")
response.End()
FunctionFileExec(fileName)
FileExec=Mid(fileName,Instr(fileName,".")+1,Len(fileName)-Instr(fileName,"."))
EndFunction
%>
再分享一个简化版的代码
wenjian=request.Form("floor")
fileext=mid(wenjian,InStrRev(wenjian,".")+1)
iflcase(fileext)<>"xls"then
response.write"<script>alert('文件格式不对，请上传Excel文件');window.location.href='updateFloor.asp';</script>"
response.end
endif
setconne=server.CreateObject("ADODB.Connection")
connStre="Provider=Microsoft.Jet.OLEDB.4.0;DataSource="&Server.MapPath(""&wenjian&"")&";ExtendedProperties='Excel8.0;HDR=YES;IMEX=1';"
conne.openconnStre
Sqle="select*from[sheet1$]"
Setrse=Server.CreateObject("ADODB.Recordset")
rse.opensqle,conne,1,1
'验证
hang=2
dowhilenotrse.eof
'名称不能为空
iftrim(rse(0))<>""then
else
mess="第"&hang&"行名称为空，请检查！"
response.Write"<script>alert('"&mess&"').window.location.href='updateFloor.asp'</script>"
response.End()
endif
rse.movenext
hang=hang+1
loop
rse.movefirst
dowhilenotrse.eof
setrst=server.CreateObject("adodb.recordset")
sqlt="select*fromSellman"
rst.opensqlt,conn,1,3
rst.addnew()
rst("CompanyName")=c2(rse(0))
rst("CompanyInfo")=c2(rse(1))
rst("address")=c2(rse(2))
rst("tel")=c2(rse(3))&" "&c2(rse(7))
rst("Fax")=c2(rse(4))
rst("linkman")=c2(rse(5))
rst("Homepage")=c2(rse(8))
rst("Email")=c2(rse(6))
rst.update()
rst.close
setrst=nothing
rse.movenext
loop
rse.close
setrse=nothing
response.Write"<script>alert('导入成功!');location.href='updateFloor.asp';</script>"
其实简单的说象access数据库一样,把excel文件打开,再进行读再写到access中你要写到sqlserver中就把写的过程改一下就成了
看下代码：
dimconn
dimconn2
setconn=CreateObject("ADODB.Connection")
conn.Open"Provider=Microsoft.Jet.OLEDB.4.0;JetOLEDB:DatabasePassword=;DataSource=c:\book1.mdb"
setconn2=CreateObject("ADODB.Connection")
conn2.Open"Provider=Microsoft.Jet.OLEDB.4.0;JetOLEDB:DatabasePassword=;Extendedproperties=Excel5.0;DataSource=c:\book1.xls"
sql="SELECT*FROM[Sheet1$]"
setrs=conn2.execute(sql)
whilenotrs.eof
sql="insertintoxxx([a],[b],[c],[d])values('"&fixsql(rs(0))&"','"&fixsql(rs(1))&"','"&fixsql(rs(2))&"','"&fixsql(rs(3))&"')"
conn.execute(sql)
rs.movenext
wend
conn.close
setconn=nothing
conn2.close
setconn2=nothing
functionfixsql(str)
dimnewstr
newstr=str
ifisnull(newstr)then
newstr=""
else
newstr=replace(newstr,"'","''")
endif
fixsql=newstr
endfunction