本文给大家汇总介绍了使用asp实现将Excel中数据导入到数据库中的方法,需要的朋友可以参考一下
asp实现excel中的数据导入数据库
<% Response.CodePage=65001%><% Response.Charset="UTF-8" %><%wenjian = request.Form("select") '获取文件扩展名ext = FileExec(wenjian)'判断文件扩展名if ext <> "xls" thenresponse.Write("<script>alert('文件类型不对,请核实!');window.location.href='index.html';</script>")response.End()end if Dim objConn,objRSDim strConn,strSql set objConn=Server.CreateObject("ADODB.Connection")set objRS=Server.CreateObject("ADODB.Recordset") excelFile = server.mappath(wenjian) '针对excel 2007strConn = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & excelFile & ";" & "Extended Properties=Excel 8.0;"objConn.Open strConn strSql="SELECT * FROM [Sheet1$]" objRS.Open strSql,objConn,1,1objRS.MoveFirst %><!--#include file="conn.asp"--><%'循环excel中所有记录while not objRS.eof set rs = Server.CreateObject("Adodb.Recordset")'查询语句sql_s = "select * from ceshi where lname='" & objRS(0) & "' and old='" & objRS(1) & "' and sex='" & objRS(2) & "' and guojia='" & objRS(3) & "' and QQ='" & objRS(4) & "'"rs.open sql_s, conn, 1, 1'重复的数据不做录入操作if rs.eof then'插入语句'****excel中第一条不会被录入****sql = "insert into ceshi (lname, old, sex, guojia, QQ)values ('" & objRS(0) & "', '" & objRS(1) & "', '" & objRS(2) & "', '" & objRS(3) & "', '" & objRS(4) & "')"'执行插入conn.execute(sql)end ifobjRS.MoveNext rs.closeset rs = nothingwend '又到了各种关闭的时候conn.closeset conn = nothingobjRS.CloseobjConn.Closeset objRS = Nothingset objConn = Nothing response.Write("<script>alert('导入成功');window.location.href='index.html';</script>")response.End() Function FileExec(fileName) FileExec = Mid(fileName,Instr(fileName,".")+1,Len(fileName)-Instr(fileName,"."))End Function%>
再分享一个简化版的代码
wenjian=request.Form("floor")fileext=mid(wenjian,InStrRev(wenjian,".")+1)if lcase(fileext)<>"xls" thenresponse.write "<script>alert ('文件格式不对,请上传Excel文件');window.location.href='updateFloor.asp';</script>"response.endend ifset conne=server.CreateObject("ADODB.Connection")connStre="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath( ""&wenjian&"" )&";Extended Properties='Excel 8.0;HDR=YES;IMEX=1';"conne.open connStreSqle="select * from [sheet1$] " Set rse = Server.CreateObject("ADODB.Recordset")rse.open sqle,conne,1,1'验证hang=2do while not rse.eof'名称不能为空if trim(rse(0))<>"" thenelsemess="第"& hang &"行名称为空,请检查!"response.Write"<script>alert('"& mess &"').window.location.href='updateFloor.asp'</script>"response.End()end if rse.movenexthang=hang+1looprse.movefirstdo while not rse.eofset rst=server.CreateObject("adodb.recordset")sqlt="select * from Sellman"rst.open sqlt,conn,1,3rst.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.closeset rst=nothingrse.movenextlooprse.closeset rse=nothingresponse.Write "<script>alert('导入成功!');location.href='updateFloor.asp';</script>"
其实简单的说象access 数据库一样,把excel文件打开,再进行读再写到access中你要写到sqlserver中就把写的过程改一下就成了
看下代码:
dim conn dim conn2 set conn=CreateObject("ADODB.Connection") conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Database Password=;Data Source=c:book1.mdb" set conn2=CreateObject("ADODB.Connection") conn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Database Password=;Extended properties=Excel 5.0;Data Source=c:book1.xls" sql = "SELECT * FROM [Sheet1$]" set rs = conn2.execute(sql) while not rs.eof sql = "insert into xxx([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 set conn = nothing conn2.close set conn2 = nothing function fixsql(str) dim newstr newstr = str if isnull(newstr) then newstr = "" else newstr = replace(newstr,"'","''") end if fixsql = newstr end function
火柴人忍者3v3M0D版内置功能菜单 v5.5
下载重生细胞腾讯版 安卓版v3.5.2-bilibili-UO
下载拳皇命运体验服 最新免费版v2.31.000
下载地牢杀手无限生命版 内置修改器最新版v0.707.12
下载天天爱烹饪 安卓版1.1.1
天天爱烹饪是由润谦科技游戏厂商出品的美食烹饪餐厅模拟经营休闲
我的世界RLCraft任务汉化版 安卓版v2.9.1
我的世界RLCraft任务汉化版是一个非常有趣的整合包,游戏
光与夜之恋内测版 最新版v1.8.0803
光与夜之恋是一款女性向恋爱养成游戏,精美的画面,丰富的剧情故
光与夜之恋台服 安卓版v1.8.0803
光与夜之恋是一款高品质恋爱互动式养成游戏,超一线画师及豪华编
俄罗斯乡村模拟器3D 手机版v1.8.2
俄罗斯乡村模拟器(Russian Village Simul