ASP implements the data import database in Excel
<% response.codepage=65001%> <% response.charset= "UTF-8"%> <% = Request. Form ("select") ' Get file extension ext = fileexec (Wenjian) ' To determine the file extension if ext <> "xls" then response. Write ("<script>alert (' file type is not correct, please verify!") '), window.location.href= ' index.html ';</script> ') response. End () End If Dim objconn,objrs Dim Strconn,strsql Set Objconn=server.createobject ("ADODB. Connection ") Set Objrs=server.createobject (" ADODB. Recordset ") Excelfile = Server.MapPath (Wenjian) ' for Excel 2007 strconn = ' provider=microsoft.ace.oledb.12.0; Data source= "& Excelfile &"; "&" Extended Properties=excel 8.0; "objConn.Open strconn strsql=" SELECT * FRO M [sheet1$] "Objrs.open strsql,objconn,1,1 objrs.movefirst%><!--#include file=" conn.asp "--><%" loop Excel There are records while not objrs.eof set rs = Server.CreateObject ("adodb.recordset") ' query statement sql_s = ' select * from Ceshi where l Name= ' "& 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 ' Duplicate data do not input operation if rs.eof then ' INSERT statement ' ****excel The first article will not be entered * * * sql = INSERT INTO Ceshi (lname, old, sex, Guojia, QQ) VALUES (' "& objRS (0) &" ', ' "& objRS (1) &" ', ' "& objRS (2) &" ', ' "& objRS (3) &" ", ' & objRS (4) & ' "' Execute insert conn.execute (SQL) End If Objrs.movenext rs.close Set rs = Nothing wen d ' again to a variety of off time conn.close Set conn = Nothing Objrs.close objconn.close Set objRS = Nothing Set objconn = Nothing respon Se. Write ("<script>alert successful"), window.location.href= ' index.html ';</script> ') response. End () Function fileexec (filename) fileexec = Mid (filename,instr (FileName, ".")
+1,len (filename)-instr (filename, "."))
End Function%>
To share a simplified version of the code
Wenjian=request. Form ("Floor") Fileext=mid (Wenjian,instrrev (Wenjian, ".") +1 if LCase (fileext) <> "xls" Then Response.Write "<script>alert (' file format is not correct, please upload Excel file '); window.location.hr ef= ' updatefloor.asp ';</script> ' Response.End End If set conne=server. CreateObject ("ADODB.") Connection ") connstre=" Provider=Microsoft.Jet.OLEDB.4.0;Data source= "& Server.MapPath (" &wenjian& ") & amp; "; Extended properties= ' Excel 8.0;
Hdr=yes;imex=1 '; ' Conne.open connstre sqle= "select * from [sheet1$]" Set rse = Server.CreateObject ("ADODB.")
Recordset ") Rse.open sqle,conne,1,1 ' Verify that hang=2 do and not rse.eof ' names cannot be empty if trim (rse (0)) <>" "Then Else mess= "& Hang &" line name is empty, please check! "Response. Write "<script>alert (' & Mess &"). window.location.href= ' updatefloor.asp ' </script> ' response. End () End If Rse.movenext hang=hang+1 loop rse.movefirst the Do and not rse.eof set Rst=server. CreateObject ("Adodb.recoRdset ") sqlt=" select * from Sellman "Rst.open sqlt,conn,1,3 rst.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.close set rst=nothing rse.movenext loop rse.close set rse=nothing response. Write "<script>alert (' Import succeeded! '); location.href= ' updatefloor.asp ';</script> '
In fact, simply like Access database, the Excel file open, and then read and then write to access you have to write to SQL Server in order to change the process of writing.
Look at the code:
Dim conn
Dim Conn2
Set Conn=createobject ("ADODB. Connection ")
Conn. Open "Provider=Microsoft.Jet.OLEDB.4.0; The Jet oledb:database password=;D ata 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 (sq L) 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