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