因需要，用ASP编写了一个EXCEL上传程序，并且把上传的EXCEL数据导入到已经建好的ACCESS

发布网友发布时间：2022-05-13 23:25

共1个回答

热心网友时间：2023-11-06 20:36

upload.asp

<html>
<head>
<title>上传文件</title>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<link href="css/css.css" rel="stylesheet" type="text/css" />
</head>
<script>
if (top.location==self.location)
{

top.location="index.asp"
}
</script>
<body leftmargin="0" topmargin="0">
<table width="100%" border=0 cellspacing=0 cellpadding=0>
<form name="form" method="post" action="readxls.asp" enctype="multipart/form-data">

<tr>

<td>
<input name="file1" type=file class=input-text size="30">
<input type=submit name="submit" value="导入数据" style="width:70px; height:19px;" >

</td>
</tr>
</form>
</table>
</body>
</html>

--------------------------------------
-------上传并且写入库-----------
readxls.asp

<%@LANGUAGE="VBSCRIPT" CODEPAGE="936"%>


<html xmlns=" http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312" />
<title>EXECL数据导入</title>
<style type="text/css">

</style>
</head>
<body>
<%
session.CodePage=936
Server.ScriptTimeOut=600000
set upload=new upload_5xsoft
set file=upload.file("file1")
if file.fileSize>120000 then
%>
<script>
alert("您选择的文件过大!");
window.close();
</script>
<% end if
if file.fileSize>0 then

filename=year(now)&month(now)&day(now)&hour(now)&minute(now)&second(now)

filename=filename+"."

filenameend=file.filename

filenameend=split(filenameend,".")

if filenameend(1)="xls" then

filename=filename&filenameend(1)

file.saveAs Server.mappath("uploadfile/"&filename)

else

response.write "数据格式不对！"

response.write "<a href=upload.asp>返回</a>"
response.end()

end if

set file=nothing
else

response.write "文件不能为空！"

response.write "<a href=upload.asp>返回</a>"
response.end()
End if
set upload=nothing

'上传XLS文件结束，下面从上传的XLS文件中读取数据写入到SQL数据库
strAddr=server.MapPath("uploadfile/"&filename)
set excelconn=server.createobject("adodb.connection")

excelconn.open "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = "+strAddr+";Extended Properties='Excel 8.0;HDR=NO;IMEX=1'"
set rs=server.CreateObject("adodb.recordset")
set rs1=server.CreateObject("adodb.recordset")
sql="select * from [Sheet1$]"
rs.open sql,excelconn,1,1
if not(rs.bof and rs.eof) then
rs.movenext
do while not rs.eof

sql1="select * from [Fees_Asse_T] where id is null"

rs1.open sql1,conn,1,3

rs1.addnew

rs1("Cate_Code")=rs(0)

rs1("Asse_Code")=rs(1)

rs1("Asse_Name")=rs(2)

rs1("Categories")=rs(3)
rs1.update
rs1.close
rs.movenext
loop
end if
rs.close()

set rs=nothing
set rs1=nothing

excelconn.Close()

set excelconn=nothing

conn.close()
set conn=nothing
%>
<script>
alert("数据导入成功！");
window.parent.document.location.reload();
history.back();
</script>
</body>
</html>