给你发一个我自己读取EXECL文件的例子程序,你看看
<!--#include file="../inc/session.asp"-->
<!--#include file="../inc/conn.asp"-->
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<link href="../css/bodystyle.css" rel="stylesheet" type="text/css">
<style type="text/css">
body,div{font-size:12px;}
div,table{margin:0 auto;}
</style>
<title>EXECL数据批量导入</title>
</head>
<body>
<%
'===========================================
' 函数功能:execl数据批量导入
' 作 者:wangsdong
' 网 站: http://www.aspprogram.cn
' 文章为作者原创,转载请注明文章出处、保留作
' 者信息,谢谢支持!
' 原理:将excel中的数据导入到access
'===========================================
action = request.QueryString("action")
if action="import" then
'连接excel数据库
Dim xlsconn,excelpath,xlsbook,xlsheet,xlsConnStr
'取上传到服务器的EXECL文件文件名
xlsbook=replace(request.Form("xlsbook"),"'","")
'得到EXECL文件载服务器的相对路径
excelpath = server.mappath("../../upload/datafile/"&xlsbook)
xlsheet=replace(request.Form("xlsheet"),"'","")
if xlsheet="" then
xlsheet="Sheet1"
end if
'新建CONN
'set conn = connOpen()
'定义EXECL连接字符串
xlsConnStr="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &excelpath& ";Extended Properties=Excel 8.0"
注释==
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties= " "Excel 8.0;HDR=Yes;IMEX=1 " " "
"Provider=Mic"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
rosoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
解决方法,在EXCEL表中,把有数字,汉字的那列,数据前,加上 ' 单引号,强制转换下,就可以读出了.
关键是IMEX=1
===
'新建EXECL连接对象
Set xlsconn = server.CreateObject("adodb.connection")
'打开EXECL对象连接
xlsconn.open xlsConnStr
'response.End()
'定义查询语句
'XlsSqlStr = "Select * from ["&xlsbook&"$]"
XlsSqlStr = "Select * from ["&xlsheet&"$]"
'新建EXECL记录集对象
Set xlsrs = Server.CreateObject("Adodb.RecordSet")
'set xlsrs = xlsconn.execute(XlsSqlStr)
'打开记录集
xlsrs.open XlsSqlStr,xlsconn,1,1
i=0
If xlsrs.eof Then
'导入的是空文件
response.Write("导入的是空文件")
response.End()
else
'导入以前先清空以前的产品数据
conn.execute("delete * from [proxls]")
ddd = now()
Do While not xlsrs.eof
type_name = trim(xlsrs("产品类别"))
if type_name <> "" then
title = trim(xlsrs("产品名称"))
canshu = trim(xlsrs("电压等级"))
price = trim(xlsrs("产品价格"))
'Formatnumber(-6665.8999,3,-1,-1,0)
'(6665.900)
'第一个参数(-6665.8999)指定了要进行格式化的数字。
'第二个参数(3)指定了小数点后显示的位数。
'第三个参数(-1)指定了是否显示前导的零。
'第四个参数(-1)指定是否对负数使用括号。
'最后一个参数(0)指定是否显示分隔符
if not isnumeric(price) then
price = "0.00"
else
price = Formatnumber(price,2,-1,-1,0)
end if
adddate = trim(xlsrs("调整日期"))
if adddate = "" then adddate = year(ddd)&"-"&month(ddd)&"-"&day(ddd)
content = trim(xlsrs("单位"))
if content <> "" then
else
content = " "
end if
sqlStr = "insert into [proxls] (type_name,title,canshu,price,adddate,content) values ('"&type_name&"','"&title&"','"&canshu&"','"&price&"','"&adddate&"','"&content&"')"
conn.execute(sqlStr)
'response.Write(sqlStr & "<br />")
i=i+1
end if
xlsrs.MoveNext
Loop
End If
'关闭EXECL记录集
xlsrs.close
'这个EXECL链接,不关删除不了文件
xlsconn.close
'关闭CONN
conn.close()
set conn = nothing
set xlsconn = nothing
'删除服务器中的EXECL文件
dim fso
set fso = server.CreateObject("scripting.filesystemobject")
if fso.FileExists(excelpath) then
fso.DeleteFile excelpath,true
end if
set fso = nothing
response.write("<script language='javascript'>alert('^_^ 批量导入成功,总共导入了 "&i&" 条数据,请返回');window.location='execlimport.asp';</script>")
else
%>
<!--
if not isnumeric(price) then
price = "0.00"
else
if price = 0 then
price = "0.00"
else
priceArr = split(price,".")
if priceArr(0) = "" then priceArr(0) = "0"
priceArr(1) = round(("0."&priceArr(1)),2)
if len(priceArr(1)) = 2 then priceArr(1) = priceArr(1) & "0"
price = priceArr(0) & priceArr(1)
end if
end if
-->
<table cellspacing=1 cellpadding="2" width="800" border="0" class="a2" align="center">
<form action="?action=import" method="post" name="form1" id="form1" onSubmit="return check();">
<tr class="a1"><td colspan="2" height="25" align="left">execl数据批量导入 <!--a href="help.asp" target="_blank">查看批量上传帮助</a--></td></tr>
<tr class="a3"><td width="105" height="30">Excel数据文件:</td>
<td width="684"><input name="xlsbook" id="xlsbook" type="hidden"><iframe marginwidth=0 framespacing=0 marginheight=0 frameborder=0 width="266" height=25 scrolling="no" src="excel-aspupload.asp"></iframe></td></tr>
<tr class="a3"><td height="30">Excel工作表名称:</td><td><input name="xlsheet" type="text" size="20" /> (注:默认Sheet1,就可以不填)</td></tr>
<tr class="a3"><td></td><td align="left" height="20"><input type="submit" value="开始导入" /></td></tr>
</form>
</table>
<script language="javascript">
function check(){
var xlsbook=document.getElementById('xlsbook');
var mesg = "";
var pattern = "";
if (xlsbook.value == ''){
mesg += '请先上传Excel文件!\n\n';
}
//pattern =/^\d{4}(\-|\/|\.)\d{1,2}\1\d{1,2}$/;
//if (!pattern.test(pubdate)) mesg+="请输入公示日期,格式为 年-月-日,并且大于等于当前日期!\n\n"
if (mesg != ""){
mesg = "---------------------------------------\n\n" + mesg;
mesg = mesg + "\n----------------------------------------";
alert(mesg);
return false;
}else{
return true;
}
}
</script>
<%
end if
%>
</body>
</html>