asp实现excel中的数据导入数据库

本文给大家汇总介绍了使用asp实现将Excel中数据导入到数据库中的方法，需要的朋友可以参考一下

asp实现excel中的数据导入数据库

1. <%Response.CodePage=65001%>
2. <%Response.Charset=UTF-8%>
3. <%
4. wenjian=request.Form(select)
6. '获取文件扩展名
7. ext=FileExec(wenjian)
8. '判断文件扩展名
9. ifext<>xlsthen
10. response.Write(<script>alert('文件类型不对，请核实！');window.location.href='index.html';</script>)
11. response.End()
12. endif
14. DimobjConn,objRS
15. DimstrConn,strSql
17. setobjConn=Server.CreateObject(ADODB.Connection)
18. setobjRS=Server.CreateObject(ADODB.Recordset)
20. excelFile=server.mappath(wenjian)
21. '针对excel2007
22. strConn=Provider=Microsoft.ACE.OLEDB.12.0;DataSource=&excelFile&;&ExtendedProperties=Excel8.0;
23. objConn.OpenstrConn
25. strSql=SELECT*FROM[Sheet1$]
27. objRS.OpenstrSql,objConn,1,1
28. objRS.MoveFirst
30. %><!--#includefile=conn.asp--><%
31. '循环excel中所有记录
32. whilenotobjRS.eof
34. setrs=Server.CreateObject(Adodb.Recordset)
35. '查询语句
36. sql_s=select*fromceshiwherelname='&objRS(0)&'andold='&objRS(1)&'andsex='&objRS(2)&'andguojia='&objRS(3)&'andQQ='&objRS(4)&'
37. rs.opensql_s,conn,1,1
38. '重复的数据不做录入操作
39. ifrs.eofthen
40. '插入语句
41. '****excel中第一条不会被录入****
42. sql=insertintoceshi(lname,old,sex,guojia,QQ)values('&objRS(0)&','&objRS(1)&','&objRS(2)&','&objRS(3)&','&objRS(4)&')
43. '执行插入
44. conn.execute(sql)
45. endif
46. objRS.MoveNext
47. rs.close
48. setrs=nothing
49. wend
51. '又到了各种关闭的时候
52. conn.close
53. setconn=nothing
54. objRS.Close
55. objConn.Close
56. setobjRS=Nothing
57. setobjConn=Nothing
59. response.Write(<script>alert('导入成功');window.location.href='index.html';</script>)
60. response.End()
62. FunctionFileExec(fileName)
63. FileExec=Mid(fileName,Instr(fileName,.)+1,Len(fileName)-Instr(fileName,.))
64. EndFunction
65. %>

再分享一个简化版的代码

1. wenjian=request.Form(floor)
2. fileext=mid(wenjian,InStrRev(wenjian,.)+1)
3. iflcase(fileext)<>xlsthen
4. response.write<script>alert('文件格式不对，请上传Excel文件');window.location.href='updateFloor.asp';</script>
5. response.end
6. endif
7. setconne=server.CreateObject(ADODB.Connection)
8. connStre=Provider=Microsoft.Jet.OLEDB.4.0;DataSource=&Server.MapPath(&wenjian&)&;ExtendedProperties='Excel8.0;HDR=YES;IMEX=1';
9. conne.openconnStre
10. Sqle=select*from[sheet1$]
11. Setrse=Server.CreateObject(ADODB.Recordset)
12. rse.opensqle,conne,1,1
13. '验证
14. hang=2
15. dowhilenotrse.eof
16. '名称不能为空
17. iftrim(rse(0))<>then
18. else
19. mess=第&hang&行名称为空，请检查！
20. response.Write<script>alert('&mess&').window.location.href='updateFloor.asp'</script>
21. response.End()
22. endif
23. rse.movenext
24. hang=hang+1
25. loop
26. rse.movefirst
27. dowhilenotrse.eof
28. setrst=server.CreateObject(adodb.recordset)
29. sqlt=select*fromSellman
30. rst.opensqlt,conn,1,3
31. rst.addnew()
32. rst(CompanyName)=c2(rse(0))
33. rst(CompanyInfo)=c2(rse(1))
34. rst(address)=c2(rse(2))
35. rst(tel)=c2(rse(3))&&c2(rse(7))
36. rst(Fax)=c2(rse(4))
37. rst(linkman)=c2(rse(5))
38. rst(Homepage)=c2(rse(8))
39. rst(Email)=c2(rse(6))
40. rst.update()
41. rst.close
42. setrst=nothing
43. rse.movenext
44. loop
45. rse.close
46. setrse=nothing
47. response.Write<script>alert('导入成功!');location.href='updateFloor.asp';</script>

其实简单的说象access 数据库一样,把excel文件打开,再进行读再写到access中你要写到sqlserver中就把写的过程改一下就成了

看下代码：

1. dimconn
2. dimconn2
3. setconn=CreateObject(ADODB.Connection)
4. conn.OpenProvider=Microsoft.Jet.OLEDB.4.0;JetOLEDB:DatabasePassword=;DataSource=c:/book1.mdb
6. setconn2=CreateObject(ADODB.Connection)
7. conn2.OpenProvider=Microsoft.Jet.OLEDB.4.0;JetOLEDB:DatabasePassword=;Extendedproperties=Excel5.0;DataSource=c:/book1.xls
10. sql=SELECT*FROM[Sheet1$]
11. setrs=conn2.execute(sql)
12. whilenotrs.eof
13. sql=insertintoxxx([a],[b],[c],[d])values('&fixsql(rs(0))&','&fixsql(rs(1))&','&fixsql(rs(2))&','&fixsql(rs(3))&')
14. conn.execute(sql)
15. rs.movenext
16. wend
18. conn.close
19. setconn=nothing
20. conn2.close
21. setconn2=nothing
23. functionfixsql(str)
24. dimnewstr
25. newstr=str
26. ifisnull(newstr)then
27. newstr=
28. else
29. newstr=replace(newstr,','')
30. endif
31. fixsql=newstr
32. endfunction