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