ASP实现将Excel表格数据批量导入到SQLServer数据库

zhx730621 2018-12-03 02:47:30

编写了一个将Excel表格数据批量导入到SQLServer数据库程序，如果Excel表格在本地计算机，程序会提示“500 - 内部服务器错误。
您查找的资源存在问题，因而无法显示。“错误，但是如果将Excel表格放在服务器端，程序就会正常执行，

好像问题出在这句
strAddr=server.MapPath("../data/计量巡查/" &nowyear&"/"&nowmonth&"/"&wjmc)

把这句话改成strAddr=server.MapPath("../data/计量巡查/2018/12/123456.xls") 就可以正常上传

请问我程序是否哪里写得有问题，请各位高手帮忙解决





<!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" xml:lang="zh-cn" lang="zh-cn">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312" />
<title>记录上传</title>
</head>
<script language="JavaScript" >
function CloseWin()
{
var ua=navigator.userAgent
var ie=navigator.appName=="Microsoft Internet Explorer"?true:false
if(ie)
{
var IEversion=parseFloat(ua.substring(ua.indexOf("MSIE ")+5,ua.indexOf(";",ua.indexOf("MSIE "))))
if(IEversion < 5.5)
{
var str = ' <object id=noTipClose classid="clsid:ADB880A6-D8FF-11CF-9377-00AA003B7A11">'
str += ' <param name="Command" value="Close"> </object>';
document.body.insertAdjacentHTML("beforeEnd", str);
document.all.noTipClose.Click();
}
else
{
window.opener =null;
window.close();
}
}
else
{
window.close()
}
}
</script>
<body style="font-size:12px">
<%
Dim Upload,path,tempCls
'===============================================================================
set Upload=new AnUpLoad '创建类实例
Upload.SingleSize=1024*1024*1024 '设置单个文件最大上传限制,按字节计；默认为不限制
Upload.MaxSize=1024*1024*1024 '设置最大上传限制,按字节计；默认为不限制
Upload.Exe="bmp|rar|pdf|jpg|gif|doc|xls" '设置合法扩展名,以|分割,忽略大小写
Upload.openProcesser=false '禁止进度条功能，如果启用，需配合客户端程序
Upload.GetData() '获取并保存数据,必须调用本方法
'===============================================================================
if Upload.ErrorID>0 then '判断错误号,如果myupload.Err<=0表示正常
response.write Upload.Description '如果出现错误,获取错误描述
else
if Upload.files(-1).count>0 then '这里判断你是否选择了文件
nowyear=Year(Upload.forms("jlxcrq"))
nowmonth=Month(Upload.forms("jlxcrq"))
jlxcbm=Upload.forms("jlxcbm")
jlxccjks=Upload.forms("jlxccjks")
jlxcdd=Upload.forms("jlxcdd")
jlxcrq=Upload.forms("jlxcrq")
jlxcy1=Upload.forms("jlxcy1")
jlxcy2=Upload.forms("jlxcy2")
set rs_zsh=server.CreateObject("ADODB.recordset")
sql_zsh = "Select * from lsh where zylb ='计量巡查'"
rs_zsh.open sql_zsh,conn,1,1
myfileno = Csng(rs_zsh("zslsh"))
rs_zsh.close
set rs_zsh=nothing
If myfileno > 0 And myfileno < 10 Then
fileno = "0000" & CStr(myfileno)
End If
If myfileno > 9 And myfileno < 100 Then
fileno = "000" & CStr(myfileno)
End If
If myfileno > 99 And myfileno < 1000 Then
fileno = "00" & CStr(myfileno)
End If
If myfileno > 999 And myfileno < 10000 Then
fileno = "0" &CStr(myfileno)
End If
If myfileno > 9999 And myfileno < 100000 Then
fileno =CStr(myfileno)
End If
If nowmonth < 10 Then nowmonth = "0" & nowmonth
jlxcdbh=nowyear & nowmonth & fileno
path=server.mappath("../data/计量巡查/" &nowyear&"/"&nowmonth) '文件保存路径(这里是files文件夹)
'保存第一个文件(以新文件名保存)
wjmc="计量巡查记录表("&Upload.forms("jlxcbm")&Upload.forms("jlxccjks")&")计量巡查字第"&jlxcdbh&"号巡查日期"&Upload.forms("jlxcrq")
set tempCls=Upload.files("file1")
tempCls.SaveToFile path,wjmc
wjcfdz="../data/计量巡查/" &nowyear&"/"&nowmonth&"/"&tempCls.FileName
else
response.Write "您没有上传任何文件！"
end if
end if
set Upload=nothing '销毁类实例
strAddr=server.MapPath("../data/计量巡查/" &nowyear&"/"&nowmonth&"/"&wjmc)
jlxcyf=nowyear&"-"&nowmonth
jlxchgl=""
jlxcsl=""
jlxchgsl=""
jlxcbhgsl=""
set excelconn=server.createobject("adodb.connection")
excelconn.open "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Database Password=;Extended properties=Excel 8.0;Data Source="&+strAddr
set rs=server.CreateObject("ADODB.recordset")
sql="select * from [Sheet1$]"
rs.open sql,excelconn,1,1
rs.movefirst
rs.move 2
//rs.movenext
jlxchgsl=rs(18)
rs.movenext
jlxcbhgsl=rs(18)
jlxcsl=jlxchgsl+jlxcbhgsl
jlxchgl=Round((jlxchgsl/jlxcsl)*100, 2)
set rs_jlxcjltz_add=server.CreateObject("ADODB.recordset")
sql_jlxcjltz_add="{call jiliangxunchajilu_add('"&jlxcdbh&"','"&jlxcyf&"','"&jlxcrq&"','"&jlxcy1&"','"&jlxcy2&"','"&jlxcbm&"','"&jlxccjks&"','"&jlxcdd&"','"&jlxcsl&"','"&jlxchgsl&"','"&jlxcbhgsl&"','"&jlxchgl&"','"&wtclsbmc&"','"&wjcfdz&"')}"
rs_jlxcjltz_add.open sql_jlxcjltz_add,conn
rs.move 3
zgs=0
qrs=0
for i=1 to jlxcbhgsl
wtclsbwg=null
wtclsbbz=null
wtclsbxs=null
wtclsbcf=null
wtclsbzj=null
wtclsbcz=null
wtclsbqrjg=null
if rs(3)="√" then
wtclsbwg="0"
end if
if rs(3)="×" then
wtclsbwg="1"
end if
if rs(4)="√" then
wtclsbbz="0"
end if
if rs(4)="×" then
wtclsbbz="1"
end if
if rs(5)="√" then
wtclsbxs="0"
end if
if rs(5)="×" then
wtclsbxs="1"
end if
if rs(6)="√" then
wtclsbcf="0"
end if
if rs(6)="×" then
wtclsbcf="1"
end if
if rs(7)="√" then
wtclsbzj="0"
end if
if rs(7)="×" then
wtclsbzj="1"
end if
if rs(8)="√" then
wtclsbcz="0"
end if
if rs(8)="×" then
wtclsbcz="1"
end if
if rs(13)<>"" then
zgcss=zgcss+1
end if
if rs(13)<>"" then
wtclsbwcrq=CDate(rs(16))
else
wtclsbwcrq=""
end if
if rs(17)="OK" then
wtclsbqrjg="1"
zgqrs=zgqrs+1
end if
wtbh=jlxcdbh&"-"&i
set rs_jlxcwtms_add=server.CreateObject("ADODB.recordset")
sql_jlxcwtms_add="{call jiliangxunwentimiaoshu_add('"&wtbh&"','"&jlxcyf&"','"&jlxcrq&"','"&jlxcy1&"','"&jlxcy2&"','"&jlxcbm&"','"&jlxccjks&"','"&jlxcdd&"','"&rs(1)&"','"&rs(2)&"','"&wtclsbwg&"','"&wtclsbbz&"','"&wtclsbxs&"','"&wtclsbcf&"','"&wtclsbzj&"','"&wtclsbcz&"','"&rs(9)&"','"&rs(12)&"','"&rs(13)&"','"&wtclsbwcrq&"','"&wtclsbqrjg&"')}"
rs_jlxcwtms_add.open sql_jlxcwtms_add,conn
rs.movenext
Next
set rs_jlxczgwc_modi=server.CreateObject("ADODB.recordset")
sql_jlxczgwc_modi="{call jiliangxunchazhenggaiwancheng_modi('"&jlxcdbh&"','"&zgcss&"','"&zgqrs&"')}"
rs_jlxczgwc_modi.open sql_jlxczgwc_modi,conn
caozuo="添加计量巡查记录：计量巡查记录编号："&jlxcdbh&"到计量巡查台帐中"
call rizi_add
rs.close()
set rs=nothing
excelconn.Close()
set excelconn=nothing
conn.close()
set conn=nothing
%>
<script language="javascript">
if (confirm("数据保存成功！是否进行新的添加？"))
{window.location.href='jiliangxunchajiluluru.asp'}
else
{window.location.href='../Main.asp'}
</script>

</body>
</html>

...全文