📄 admin_stu_excel.asp
字号:
<%
if session("type")<>"999" then
response.write "<br><br>对不起,您没有权限使用导入excel的权限！"
response.end
end if
'在线将EXCEL表格数据导入ACCESS
'作者:SCI)_OruA
Response.Buffer = True
Server.ScriptTimeOut = 90
Session.CodePage=936
dim sql,rstable,rs,upfile
dim rsfield,rsmdb,rsxls%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312" />
<link href="images/Style.css" rel="stylesheet" type="text/css">
<title>EXCEL>ACCESS</title>
<style type="text/css">
<!--
td {
font-size: 9pt;
color: #333333;
background-color:#ffffff;
height:20px;
}
body {
font-size: 9pt;
color: #333333;
}
.title {
color:#FFFFFF;
text-align:center;
font-size:10pt;
background-color: #A3B2CC;
}
table {
background-color:#999999;
}
#uploading {
display:none;
}
.t_td{
height:16px;
}
.word{
color:#ff0000;
font-weight: bold;
}
.border{
border:1px solid #999999;
color:#000000;
}
.STYLE1 {
color: #FFFFFF;
font-weight: bold;
}
.STYLE2 {
color: #006699;
font-weight: bold;
}
.STYLE4 {color: #FF0000}
-->
</style>
<script>
function uploadstart(){
if (document.all("file").value==""){
alert("请选择上传文件");
return;
}
document.all("uploading").style.display="inline";
document.all("upload").style.display="none";
document.all("pathtable").style.display="none";
document.all("fileform").submit();
}
</script>
</head>
<body>
<center>
<p>
<%
function HTMLEncode(fString)
fString = replace(fString, ">", ">")
fString = replace(fString, "<", "<")
fString= replace(fString, """", """)
fString = replace(fstring,"\","\\")
fString = Replace(fString, CHR(13), " ")
HTMLEncode = fString
end function
'显示错误信息
sub showerr(message)
response.write "<script>alert("""&HTMLEncode(message)&""");window.location='admin_stu_excel.asp';</script>"
response.end
end sub
'-----上传文件
if request("action")="upfile" then
Server.ScriptTimeOut=999999
dim upload,serverpath,errstr,path,savefilename
set upload=new UpFile_Class
path=Request.ServerVariables("PATH_info")
serverpath=server.MapPath(path)
serverpath=upload.getfilepath(serverPath)
path= Left(path,InStrRev(path, "\"))
upload.GetData(10240000)
upload.AllowExt="xls;txt;"
if upload.isErr then
select case upload.iserr
case 1
errstr="请选择上传文件"
case 2
errstr="上传的文件超出限制,最大10M"
end select
session("xls_path")=""
showerr errstr
else
upload.SaveToFile "file",serverpath&upload.file("file").filename
if upload.isErr then
if upload.isErr=3 then
errstr="只允许上传XLS文件"
else
errstr="上传中遇到未知错误"
end if
session("xls_path")=""
showerr errstr
else
session("xls_path")=path&upload.file("file").filename
upsucess=session("xls_path")
response.Redirect("admin_stu_excel.asp?info='"&upsucess&"'")
end if
end if
end if
if request("action")="ready" then
dim FileName
FileName=session("xls_path") '取得文件名，来自项目经理的指定，路径固定在某个虚拟路径中
set conn=CreateObject("ADODB.connection")
conn.Open "Driver={Microsoft Excel Driver (*.xls)};" & _
"DriverId=790;" & _
"Dbq=" & server.mappath(""&FileName&"")
set rs=createobject("ADODB.recordset")
rs.Open "Select * From [stuinfo$]",conn, 2, 2
if rs.eof then
response.write "Excel表中无纪录"
else
set connDB = Server.CreateObject("ADODB.Connection")
'如果更改数据库名称时，此处的数据库连接语句中的数据库名称也要更改。
DBPath = Server.MapPath("../data/yzbzcj2007.mdb")
'RESPONSE.WRITE DBpath
connDB.Open "driver={Microsoft Access Driver (*.mdb)};dbq=" & DBPath
Set RsDB = Server.CreateObject("ADODB.Recordset")
SqlDB="Select * from stuinfo"
RsDB.open sqlDB,connDB,1,3
i=0
do while not rs.eof '利用循环读出数据
RsDB.addnew
RsDB("stuidh")=rs("学号")
RsDB("stuname")=rs("姓名")
RsDB("stuxb")=rs("性别")
RsDB("folk")=rs("民族")
RsDB("birthday")=rs("出生日期")
RsDB("stunj")=rs("级部")
RsDB("stubj")=rs("班级")
RsDB("joinschool")=rs("入学日期")
RsDB("stuxj")=rs("家长姓名")
RsDB("stutel")=rs("联系电话")
RsDB("place")=rs("家庭住址")
RsDB("stujj")=rs("备注")
'response.write rs("stuidh")&" "&rs("stuname")&"<br>"
Rs.update
RsDB.movenext
i=i+1
rs.movenext
loop
response.Redirect("admin_stu_excel.asp?info1='"&i&"'")
end if
rs.close
set rs=nothing
set conn=nothing
end if
%>
<br />
</p>
<table width="90%" border="0" align="center" cellpadding="0" cellspacing="0">
<tr>
<td align="left" valign="middle" style="FILTER: progid:DXImageTransform.Microsoft.Gradient(gradientType=1,startColorStr=#3b839f,endColorStr=#eff4ff)"><span class="STYLE1">导出/导入EXCEL</span></td>
</tr>
</table>
<br />
<table width="90%" border="0" cellpadding="0" cellspacing="0">
<tr>
<td align="left"><p><span class="STYLE2">1、导出EXCEL<br />
</span><strong>为了方便管理学生信息，特设定把学生信息导出到EXCEL的功能。</strong></p>
</td>
</tr>
</table>
<br />
<table width="90%" border="0" cellpadding="0" cellspacing="0" bgcolor="e6eeef">
<tr>
<td align="left" valign="middle" bgcolor="e6eeef"><p align="center"><strong> <a href="stuinfotoexcel.asp">导出成EXCEL文件</a></strong></p></td>
</tr>
</table>
<br />
<br />
<table width="90%" border="0" cellpadding="0" cellspacing="0">
<tr>
<td align="left"><p><span class="STYLE2">2、导入EXCEL<br />
</span><strong> 为了方便输入学生信息，特设定EXCEL导入功能，此功能可以方便地对学生信息进行批量录入。<span class="STYLE4">下载：<a href="up/stuinfonullexcel.xls">EXCEL空表表样</a></span></strong></p></td>
</tr>
</table>
<p> </p>
<div id="upload">
<table width="400" border="0" cellpadding="2" cellspacing="1">
<tr>
<td align="left" class="title">第一步：上传EXCEL文件</td>
</tr>
<form action="admin_stu_excel.asp?action=upfile" method="post" id="fileform" enctype="multipart/form-data" name="fileform">
<tr>
<td align="center"><input type="file" name="file" />
<input name="upstart" type="button" id="upstart" value="上传" onclick="uploadstart();"/></td>
</tr>
</form>
</table>
</div>
<div id="uploading">
<table width="400" border="0" cellspacing="0" cellpadding="0">
<tr>
<td valign="middle" class="title" style="height:40px;">上传中...请稍候</td>
</tr>
</table>
</div>
<br />
<div id=pathtable>
<form action="admin_stu_excel.asp?action=ready" method="post">
<table width="400" border="0" cellspacing="1" cellpadding="2">
<tr>
<td align="left" class="title">第二：开始数据导入
<input name="xls_path" type="hidden" id="xls_path" value="<%=session("xls_path")%>" /></td>
</tr>
<tr>
<td align="center">
<%if request.querystring("info")<>"" then response.write "上传成功，请执行数据导入！"
if request.querystring("info1")<>"" then response.write "<FONT color=#ff0000>成功导入数据"&request.querystring("info1")&"条！</font>"%></td>
</tr>
<tr>
<td align="center"><input type="submit" name="Submit" value="数据导入" /></td>
</tr>
</table>
</form>
</div>
</center>
</body>
</html>
<%
'以下为无惧上传类,非原创
'文件上传类,无惧上传类 V2.2,作者:梁无惧
Class UpFile_Class
Dim Form,File
Dim AllowExt_ '允许上传类型(白名单)
Dim NoAllowExt_ '不允许上传类型(黑名单)
Dim IsDebug_ '是否显示出错信息
Private oUpFileStream '上传的数据流
Private isErr_ '错误的代码,0或true表示无错
Private ErrMessage_ '错误的字符串信息
Private isGetData_ '指示是否已执行过GETDATA过程
'------------------------------------------------------------------
'类的属性
Public Property Get Version
Version="无惧上传类 Version V2.0"
End Property
Public Property Get isErr '错误的代码,0或true表示无错
isErr=isErr_
End Property
Public Property Get ErrMessage '错误的字符串信息
ErrMessage=ErrMessage_
End Property
Public Property Get AllowExt '允许上传类型(白名单)
AllowExt=AllowExt_
End Property
Public Property Let AllowExt(Value) '允许上传类型(白名单)
AllowExt_=LCase(Value)
End Property
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -