用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>