asp实现excel中的数据导入数据库
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19 <% Response.CodePage=65001%>
<% Response.Charset="UTF-8" %>
<%
wenjian = request.Form("select")
'获取文件扩展名
ext = FileExec(wenjian)
'判断文件扩展名
if ext <> "xls" then
response.Write("")
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)
'针对excel 2007
strConn = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & excelFile &
";" & "Extended Properties=Excel 8.0;"
objConn.Open strConn
strSql="SELECT * FROM [Sheet1$]"
objRS.Open strSql,objConn,1,1
objRS.MoveFirst 20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41 %><%
'循环excel中所有记录
while not objRS.eof
set rs = Server.CreateObject("Adodb.Recordset")
'查询语句
sql_s = "select * from ceshi where lname='" & 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
'重复的数据不做录入操作
if rs.eof then
'插入语句
'****excel中第一条不会被录入****
sql = "insert into ceshi (lname, old, sex, guojia, QQ)values ('" &
objRS(0) & "', '" & objRS(1) & "', '" & objRS(2) & "', '" & objRS(3) & "',
'" & objRS(4) & "')"
'执行插入
conn.execute(sql)
end if
objRS.MoveNext
rs.close
set rs = nothing
wend
'又到了各种关闭的时候
conn.close
set conn = nothing
objRS.Close
objConn.Close 42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63 set objRS = Nothing
set objConn = Nothing
response.Write("")
response.End()
Function FileExec(fileName)
FileExec = Mid(fileName,Instr(fileName,".")+1,Len(fileName)-Instr(fileName,"."))
End Function
%> 64
