EXCEL如何表转入SQL - icecool的博客

作者在 2006-06-12 22:03:00 发布以下内容

<%Option Explicit%>

<%
dim rssql,sql1
set rssql=server.createobject("adodb.recordset")
sql1="select*from sinfo"
rssql.open sql1,conn,1,3
%>

<html>
<head>
<title>Excel</title>
</head>
<body>
<h2 align="center">以下记录已被转到SQL数据库中</h2>
<table border="1" width="95%" align="center">
<tr align="center" bgcolor="#E6E6E6">
<td>学号</td><td>密码</td><td>姓名</td><td>班级</td><td>电话</td><td>手机</td><td>邮箱</td>
</tr>

<%
Dim db,rs,strSql
Set db = Server.CreateObject("ADODB.Connection") '建立Connection对象
db.Open "Driver={Microsoft Excel Driver (*.xls)};Dbq=" & Server.MapPath("Mark.xls")

strSql="Select * From [Sheet1$]" '打开记录集，表名一定要以"[表名$]"的格式
Set rs=db.Execute(strSql)

Do While Not rs.EOF '循环读取所有行
Response.Write "<tr align='center'>"

rssql.addnew '添加到SQL数据库
rssql("sno")=rs("学号") '此处学号,密码--等必须是excel中第一行的内容
rssql("spw")=rs("密码")
rssql("sname")=rs("姓名")
rssql("sclass")=rs("班级")
rssql("sphone")=rs("电话")
rssql("smovephone")=rs("手机")
rssql("semail")=rs("邮箱")

rssql.update

Response.Write "<td>" & rs("学号") & "</td>"
Response.Write "<td>" & rs("密码") & "</td>"
Response.Write "<td>" & rs("姓名") & "</td>"
Response.Write "<td>" & rs("班级") & "</td>"
Response.Write "<td>" & rs("电话") & "</td>"
Response.Write "<td>" & rs("手机") & "</td>"
Response.Write "<td>" & rs("邮箱") & "</td>"

Response.Write "</tr>"
rs.MoveNext
Loop

rs.Close '关闭对象
Set rs=nothing

rssql.close
set rssql=nothing

conn.close
set conn=nothing

db.Close
Set db=Nothing
%>
</table>
</body>
</html>