Beranda>Tutorial Pemrograman Jaringan>tutorial ASP

ASP mengimplementasikan Impor Data di Excel ke database

Penulis：Eve Cole Waktu Pembaruan：2025-03-17 07:00:03

Artikel ini merangkum metode menggunakan ASP untuk mengimpor data dari Excel ke dalam database.

ASP mengimplementasikan Impor Data di Excel ke database

  1. <%Response.codepage = 65001%>
  2. <%Response.charset = UTF-8%>
  3. <%
  4. wenjian = request.form (pilih)
  6. 'Dapatkan ekstensi file
  7. ext = fileexec (wenjian)
  8. 'Hakim ekstensi file
  9. IFEXT <> xlsthen
  10. response.write (<script> alert ('Jenis file salah, harap verifikasi!'); window.location.href = 'index.html'; </ptript>)
  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. 'Untuk Excel2007
  22. strconn = provider = microsoft.ace.oledb.12.0; dataSource = & excelfile &; & extendedProperties = excel8.0;
  23. objconn.openstrconn
  25. strsql = pilih*dari [sheet1 $]
  27. objrs.openstrsql, objconn, 1,1
  28. objrs.movefirst
  30. %> <!-#includeFile = conn.asp-> <%
  31. 'Loop semua catatan di Excel
  32. whilenotobjrs.eof
  34. setRs = server.CreateObject (adodb.recordset)
  35. 'Pernyataan kueri
  36. sql_s = pilih*fromceshiwhereelname = '& objrs (0) &' andold = '& objrs (1) &' andsex = '& objrs (2) &' andguojia = '& objrs (3) &' andqq = '& objrs (4) &' '
  37. rs.opensql_s, Conn, 1,1
  38. 'Jangan masukkan data duplikat
  39. ifrs.eofthen
  40. 'Sisipkan pernyataan
  41. '**** Item pertama di Excel tidak akan dimasukkan ****
  42. sql = insertIntoceshi (lname, old, sex, guojia, qq) nilai ('& objrs (0) &', '& objrs (1) &', '& objrs (2) &', '& objrs (3) &', '& objrs (3) & ',' & objrs (4) & ')
  43. 'Jalankan penyisipan
  44. Conn.execute (SQL)
  45. endif
  46. objrs.movenext
  47. rs.close
  48. setRs = tidak ada
  49. pergi ke
  51. 'Saatnya tutup lagi
  52. Conn.Close
  53. setConn = tidak ada
  54. objrs.close
  55. objconn.close
  56. setobjrs = tidak ada
  57. setobjconn = tidak ada
  59. response.write (<script> alert ('impor berhasil'); window.location.href = 'index.html'; </script>)
  60. response.end ()
  62. FunctionFileExec (nama file)
  63. Fileexec = mid (fileName, instr (fileName ,.)+1, len (nama file) -instr (nama file ,.)))
  64. Fungsi endfungsi
  65. %>

Bagikan versi kode yang disederhanakan lainnya

  1. wenjian = request.form (lantai)
  2. fileExt = mid (wenjian, instrrev (wenjian,.)+1)
  3. iflcase (fileext) <> xlsthen
  4. response.write <script> alert ('Format file tidak benar, silakan unggah file excel'); window.location.href = 'updatefloor.asp'; </script>
  5. respons.end
  6. endif
  7. setConne = server.CreateObject (adodb.connection)
  8. connstre = penyedia = microsoft.jet.oledb.4.0; datasource = & server.mappath (& wenjian &) &; extendedProperties = 'excel8.0; hdr = yes; iMex = 1';
  9. conne.openconnstre
  10. Sqle = pilih*dari [sheet1 $]
  11. SetRse = server.CreateObject (adodb.recordset)
  12. rse.opensqle, conne, 1,1
  13. 'memeriksa
  14. hang = 2
  15. dowhilenottrse.eof
  16. 'Nama itu tidak bisa kosong
  17. iftrim (rse (0)) <> lalu
  18. kalau tidak
  19. Mess = Nama & Hang & Line kosong, silakan periksa!
  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. lingkaran
  26. rse.movefirst
  27. dowhilenottrse.eof
  28. setRST = server.CreateObject (adodb.recordset)
  29. SQLT = Pilih*Fromsellman
  30. rst.opensqlt, Conn, 1,3
  31. rsst.addnew ()
  32. RST (CompanyName) = C2 (RSE (0))
  33. RST (CompanyInfo) = C2 (RSE (1))
  34. RST (alamat) = C2 (RSE (2))
  35. RST (Tel) = C2 (RSE (3)) && C2 (RSE (7))
  36. RST (faks) = C2 (RSE (4))
  37. RST (LinkMan) = C2 (RSE (5))
  38. RST (beranda) = C2 (RSE (8))
  39. RST (email) = C2 (RSE (6))
  40. rsst.update ()
  41. rsst.close
  42. setRST = tidak ada
  43. rse.movenext
  44. lingkaran
  45. rse.close
  46. settrse = tidak ada
  47. response.write <script> alert ('diimpor berhasil!'); location.href = 'updatefloor.asp'; </script>

Faktanya, sederhananya seperti database Access, buka file Excel, baca dan tuliskan untuk mengakses.

Lihat kodenya:

  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 = Pilih*dari [Sheet1 $]
  11. setRs = conn2.execute (sql)
  12. whilenotrs.eof
  13. sql = insertIntOxxx ([a], [b], [c], [d]) nilai ('& fixsql (rs (0)) &', '& fixsql (rs (1)) &', '& fixsql (rs (2 )) & ',' & fixsql (rs (3)) & ')
  14. Conn.execute (SQL)
  15. rs.movenext
  16. pergi ke
  18. Conn.Close
  19. setConn = tidak ada
  20. Conn2.close
  21. setConn2 = tidak ada
  23. functionfixsql (str)
  24. Dimnewstr
  25. newsstr = str
  26. Ifisnull (Newstr) lalu
  27. newsstr =
  28. kalau tidak
  29. newsstr = ganti (newstr, ',' ')
  30. endif
  31. fixsql = newstr
  32. fungsi endfungsi

Artikel Terkait