z_excel.asp
这一页的功能是将z_file.asp传送来的表名获取,同时将此excel表中的数据导入到access数据库中的users表中.字段对应.
z_excel.asp全部代码如下
____________代码开始_________
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<title>添加产品价格入库</title>
</head>
<%
dim Filename
dim FilePath
dim Excel
dim Data
Data="Databases/data.mdb"
Filename=trim(request("excel_name"))
FilePath="excel/"
Excel=FilePath+Filename
dim conn '定义一个连接变量
dim conn2 '定义第二个连接变量
'On Error Resume Next
Server.ScriptTimeOut = 999999 '超时时间
set conn=CreateObject("ADODB.Connection")
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Database Password=;Data Source="&Server.MapPath(Data) '要导入的数据库名称
set conn2=CreateObject("ADODB.Connection")
conn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Database Password=;Extended properties=Excel 5.0;Data Source="&Server.MapPath(Excel) '要导入的EXCEL表名称Fang.xls
'-----------------------------------------------------------------------------------------------------------------
'这里要说明的是，数据库和Excel表和该文件必须在网站同一目录里
'-----------------------------------------------------------------------------------------------------------------
sql_ex= "SELECT * FROM [Sheet1$]" '要导入的Excel数据里面的表的名称，后面一定要加$
set rs = conn2.execute(sql_ex)
while not rs.eof
sql = "insert into users([userid],[password]) values('"& fixsql(rs(0)) &"','"& fixsql(rs(1)) &"')"
'其中的zhenya是要导入的目标表，其后的country，mobile是zhenya表中的字段名，但是要和后面的fixsql（rs（数字））要对应！
'response.write sql
'response.end
conn.execute(sql)
rs.movenext
Response.Write "正在插入 "&sql&"<Br>"
'在这里输出执行的语句，可以去掉的！
Response.Flush
wend
conn.close
set conn = nothing
conn2.close
set conn2 = Nothing
If Err = 0 Then
Response.Write "导入成功"
Else
Response.Write "导入失败!"
End If
function fixsql(str)
dim newstr
newstr = str
if isnull(newstr) then
newstr = ""
else
newstr = replace(newstr,"'","''")
end if
fixsql = newstr
end Function
%>
<body>
</body>
</html>
________________代码结束_______________
z_excel.asp的第二种导入数据库的方式.以下代码可以实现的功能是比对ACCESS数据库中和EXCEL表中的数据,如果数据和重复,则不重复添加.
完整个代码如下:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<title>添加产品价格入库</title>
</head>
<%
dim Filename
dim FilePath
dim Excel
dim Data
Data="Databases/data.mdb"
Filename=trim(request("excel_name"))
FilePath="excel/"
Excel=FilePath+Filename
On Error Resume Next
response.Write(Excel)
response.Write(Data)
dim conn
dim conn2
set conn=CreateObject("ADODB.Connection")
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Database Password=;Extended properties=Excel 5.0;Data Source="&Server.MapPath(Excel)
set conn2=CreateObject("ADODB.Connection")
conn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Database Password=;Data Source="&Server.MapPath(Data)
Set rs=Server.CreateObject("ADODB.Recordset")
sql="Select * from [Sheet1$]"
rs.Open sql,conn,1,1
Set dbrs=Server.CreateObject("ADODB.Recordset")
Do While Not rs.eof
sql="select * From users where [userid]='"+rs("userid")+"'"
dbrs.Open sql,conn2,1,3
If dbrs.eof Then
dbrs.addnew
dbrs("userid")=rs("userid")
dbrs("password")=rs("password")
dbrs.update
dbrs.close
End If
rs.MoveNext
Loop
rs.close
%>
<body>
</body>
</html>