用asp将excel表数据导入access数据库的代码

 

<center>
<%
Dim conn
Dim StrConn
Dim rs
Dim Sql
'将excel表当作数据库打开
Set conn=Server.CreateObject("ADODB.Connection")
conn.Open"Driver={Microsoft Excel Driver (*.xls)};DriverId=790; DBQ="& Server.MapPath("../#bijiadata/company.xls")   '指定excel文件的路径

Set rs = Server.CreateObject("ADODB.Recordset")

Sql="select usrname,usrpwd,question,answer,sj from [company$]" '指定excel的表名（后面必须有$符号)以及字段名，如果是所有字段名可用*代替

rs.Open Sql,conn,2,2

'打开数据库
Set sqlconn=Server.CreateObject("ADODB.Connection")

sqlconn.Open"provider=microsoft.jet.oledb.4.0;data source="& Server.MapPath("../#bijiadata/company.mdb")

sqlconn.execute("delete * from company")

Set rst = Server.CreateObject("ADODB.Recordset")

Sql2="select usrname,usrpwd,question,answer,sj from company"

rst.Open Sql2,sqlconn,1,3

if rst.Fields.Count-1=rs.Fields.Count-1 then  '如果字段个数据相同则

for i=0 to rs.Fields.Count-1
  if rs(i).name<>rst(i).name then
    response.write "操作错误，excel表的字段名与access数据库的字段名不对应"
  else
    response.write"excel的字段名："&rs(i).name&"    access数据库的字段名："&rst(i).name&" " '输出  excel表与access数据库表的对应字段名，进行对比

while not rs.eof and not rs.bof '将记录一条一条导入
rst.addnew
for j=0 to rs.Fields.Count-1
rst(j)=rs(j)
next
rst.update

rs.movenext
wend
end if
next
response.write" <font color=red>成功将excel表中的数据导入到access数据库[/red]</br>"
else
response.write "操作错误，excel表的字段个数据与access数据库的字段个数不相等"

end if   '结束字段个数据判断

%>

导入的access数据库的数据如下：

<table border="1" align="center" cellpadding="4" cellspacing="4" bordercolordark="#FFFFFF">
<tr>
<%
rst.close

Set rst = Server.CreateObject("ADODB.Recordset")

Sql2="select id,usrname,usrpwd,question,answer,sj from company"

rst.Open Sql2,sqlconn,1,1

for i=0 to rst.Fields.Count-1

%>
<td nowrap bgcolor="#999999"> <div align="center"><font color="#FFFFFF"><%=rst(i).Name%>[/red]</div></td>
<%

next

%>
</tr>
<%

do while Not rst.EOF

%>
<tr>
<%

for i=0 to rst.Fields.Count-1

%>
<td nowrap> <div align="center"><%=rst(i)%> </div></td>
<%

next

%>
</tr>
<%

rst.MoveNext

Loop

%>
</table>

<%
sqlconn.execute("delete * from company")
response.write"access数据库里的数据已清除"
%>
</center>

♡ 点赞 0

☆ 收藏 0

留言区（共0条）