EXCEL如何表转入SQL

默认分类 | 2006-06-12 22:03:00 | 阅读 2102 次 | 评论(0)

<%Option Explicit%>


<!--#include file="conn.asp"-->
<%
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>

文章评论,共0条
游客请输入验证码
浏览28663次