ASP to implement data import database in Excel _ Application techniques

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