Artikel ini merangkum metode menggunakan ASP untuk mengimpor data dari Excel ke dalam database.
ASP mengimplementasikan Impor Data di Excel ke database
- <%Response.codepage = 65001%>
- <%Response.charset = UTF-8%>
- <%
- wenjian = request.form (pilih)
- 'Dapatkan ekstensi file
- ext = fileexec (wenjian)
- 'Hakim ekstensi file
- IFEXT <> xlsthen
- response.write (<script> alert ('Jenis file salah, harap verifikasi!'); window.location.href = 'index.html'; </ptript>)
- response.end ()
- endif
- Dimobjconn, objrs
- Dimstrconn, strsql
- setoBjConn = server.createObject (adodb.connection)
- setObjrs = server.CreateObject (adodb.recordset)
- excelfile = server.mappath (wenjian)
- 'Untuk Excel2007
- strconn = provider = microsoft.ace.oledb.12.0; dataSource = & excelfile &; & extendedProperties = excel8.0;
- objconn.openstrconn
- strsql = pilih*dari [sheet1 $]
- objrs.openstrsql, objconn, 1,1
- objrs.movefirst
- %> <!-#includeFile = conn.asp-> <%
- 'Loop semua catatan di Excel
- whilenotobjrs.eof
- setRs = server.CreateObject (adodb.recordset)
- 'Pernyataan kueri
- sql_s = pilih*fromceshiwhereelname = '& objrs (0) &' andold = '& objrs (1) &' andsex = '& objrs (2) &' andguojia = '& objrs (3) &' andqq = '& objrs (4) &' '
- rs.opensql_s, Conn, 1,1
- 'Jangan masukkan data duplikat
- ifrs.eofthen
- 'Sisipkan pernyataan
- '**** Item pertama di Excel tidak akan dimasukkan ****
- sql = insertIntoceshi (lname, old, sex, guojia, qq) nilai ('& objrs (0) &', '& objrs (1) &', '& objrs (2) &', '& objrs (3) &', '& objrs (3) & ',' & objrs (4) & ')
- 'Jalankan penyisipan
- Conn.execute (SQL)
- endif
- objrs.movenext
- rs.close
- setRs = tidak ada
- pergi ke
- 'Saatnya tutup lagi
- Conn.Close
- setConn = tidak ada
- objrs.close
- objconn.close
- setobjrs = tidak ada
- setobjconn = tidak ada
- response.write (<script> alert ('impor berhasil'); window.location.href = 'index.html'; </script>)
- response.end ()
- FunctionFileExec (nama file)
- Fileexec = mid (fileName, instr (fileName ,.)+1, len (nama file) -instr (nama file ,.)))
- Fungsi endfungsi
- %>
Bagikan versi kode yang disederhanakan lainnya
- wenjian = request.form (lantai)
- fileExt = mid (wenjian, instrrev (wenjian,.)+1)
- iflcase (fileext) <> xlsthen
- response.write <script> alert ('Format file tidak benar, silakan unggah file excel'); window.location.href = 'updatefloor.asp'; </script>
- respons.end
- endif
- setConne = server.CreateObject (adodb.connection)
- connstre = penyedia = microsoft.jet.oledb.4.0; datasource = & server.mappath (& wenjian &) &; extendedProperties = 'excel8.0; hdr = yes; iMex = 1';
- conne.openconnstre
- Sqle = pilih*dari [sheet1 $]
- SetRse = server.CreateObject (adodb.recordset)
- rse.opensqle, conne, 1,1
- 'memeriksa
- hang = 2
- dowhilenottrse.eof
- 'Nama itu tidak bisa kosong
- iftrim (rse (0)) <> lalu
- kalau tidak
- Mess = Nama & Hang & Line kosong, silakan periksa!
- response.write <script> alert ('& mess &'). window.location.href = 'updatefloor.asp' </script>
- response.end ()
- endif
- rse.movenext
- hang = hang+1
- lingkaran
- rse.movefirst
- dowhilenottrse.eof
- setRST = server.CreateObject (adodb.recordset)
- SQLT = Pilih*Fromsellman
- rst.opensqlt, Conn, 1,3
- rsst.addnew ()
- RST (CompanyName) = C2 (RSE (0))
- RST (CompanyInfo) = C2 (RSE (1))
- RST (alamat) = C2 (RSE (2))
- RST (Tel) = C2 (RSE (3)) && C2 (RSE (7))
- RST (faks) = C2 (RSE (4))
- RST (LinkMan) = C2 (RSE (5))
- RST (beranda) = C2 (RSE (8))
- RST (email) = C2 (RSE (6))
- rsst.update ()
- rsst.close
- setRST = tidak ada
- rse.movenext
- lingkaran
- rse.close
- settrse = tidak ada
- 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:
- dimconn
- dimconn2
- setConn = createObject (adodb.connection)
- conn.openprovider = microsoft.jet.oledb.4.0; jetoledb: databasepassword =; DataSource = c: /book1.mdb
- setConn2 = createObject (adodb.connection)
- conn2.openprovider = microsoft.jet.oledb.4.0; jetoledb: databasepassword =; extendedProperties = excel5.0; dataSource = c: /book1.xls
- SQL = Pilih*dari [Sheet1 $]
- setRs = conn2.execute (sql)
- whilenotrs.eof
- sql = insertIntOxxx ([a], [b], [c], [d]) nilai ('& fixsql (rs (0)) &', '& fixsql (rs (1)) &', '& fixsql (rs (2 )) & ',' & fixsql (rs (3)) & ')
- Conn.execute (SQL)
- rs.movenext
- pergi ke
- Conn.Close
- setConn = tidak ada
- Conn2.close
- setConn2 = tidak ada
- functionfixsql (str)
- Dimnewstr
- newsstr = str
- Ifisnull (Newstr) lalu
- newsstr =
- kalau tidak
- newsstr = ganti (newstr, ',' ')
- endif
- fixsql = newstr
- fungsi endfungsi