如何读取用asp读取excel 数据

我有一个execle表,什么都空的,我想知道,怎么读取里面的字段对应的一列数据不要给我发什么rs("号码")什么的,这个绝对不管用,都是垃圾在网上找的,根本不能用。我是数... 我有一个execle表,什么都空的,我想知道,怎么读取里面的字段对应的一列数据不要给我发什么rs("号码")什么的,这个绝对不管用,都是垃圾在网上找的,根本不能用。我是数据库已经连接上去了。 展开
 我来答
huang1986111
2010-10-20 · TA获得超过144个赞
知道小有建树答主
回答量:214
采纳率:0%
帮助的人:173万
展开全部
给你发一个我自己读取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"

'新建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>
升本专家
2010-10-20 · TA获得超过633个赞
知道小有建树答主
回答量:725
采纳率:65%
帮助的人:274万
展开全部
很简单,把execel当数据库用就行了。
Set Conn = Server.CreateObject("ADODB.Connection")
Driver = "Driver={Microsoft Excel Driver (*.xls)};"
DBPath = "DBQ=" & Server.MapPath( "fenshi.xls" )
Conn.Open Driver & DBPath
这是asp链接数据库的文件。

set rs=server.CreateObject("adodb.recordset")
Sql="select * from [Sheet1$]"
rs.open sql,conn,1,1

这是打开表的文件。
已赞过 已踩过<
你对这个回答的评价是?
评论 收起
woshifotuo
2010-10-20 · TA获得超过701个赞
知道小有建树答主
回答量:249
采纳率:0%
帮助的人:267万
展开全部
我在项目中对Excel操作的方法:打开excel模板,写入数据,之后打开或保存
--------------------------------------------------------------------
using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.IO;
using System.Text;
using System.Reflection;

/// <summary>
/// PrintExcel 的摘要说明
/// </summary>
public class PrintExcel : System.Web.UI.Page
{
public PrintExcel()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
public void ShowExcel(DataTable table,string dataTemplate)
{
HttpContext.Current.Response.Charset = "GB2312";
HttpContext.Current.Response.ContentEncoding = Encoding.UTF8;//注意编码
string fileName = DateTime.Now.ToString("yyyyMMddHHmmss") + "_Allowance.xls";
HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, Encoding.UTF8).ToString());
HttpContext.Current.Response.ContentType = "application/ms-excel";//image/JPEG;text/HTML;image/GIF;vnd.ms-excel/msword
DateTime start = DateTime.Now;
Excel.Application app = new Excel.ApplicationClass();
app.Visible = false;
app.DisplayInfoWindow = false;
Excel.Workbooks books = app.Workbooks;
Excel.Workbook book = books.Open(Server.MapPath(dataTemplate), Missing.Value, true, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value);
Excel.Worksheet sheet = (Excel.Worksheet)book.Sheets[1];

DateTime end = DateTime.Now;

try
{
int currentRow = 1;
string[] data = new string[table.Columns.Count];
foreach (DataRow row in table.Rows)
{
for (int i = 0; i < row.ItemArray.Length; i++)
{
data[i] = row.ItemArray[i].ToString();
}
currentRow++;

sheet.Cells[currentRow, 1] = String.Join("|", data);
Excel.Range range = (Excel.Range)sheet.Cells[currentRow, 1];
range.TextToColumns(Missing.Value, Excel.XlTextParsingType.xlDelimited, Excel.XlTextQualifier.xlTextQualifierNone,
false, false, false, false, false, true, "|", Missing.Value, Missing.Value, Missing.Value, Missing.Value);
((Excel.Range)sheet.Cells[currentRow, 1]).AddComment(row.RowError);

}

sheet.UsedRange.Columns.AutoFit();
Excel.Borders borders = ((Excel.Borders)sheet.UsedRange.Borders);
borders.LineStyle = Excel.XlLineStyle.xlContinuous;
borders.Weight = Excel.XlBorderWeight.xlThin;
////冻结储存格
//((Excel.Range)sheet.Cells[3, 4]).Select();
//((Excel.Range)sheet.Cells[1, 1]).Select();
//app.ActiveWindow.FreezePanes = true;

//string fileName = DateTime.Now.ToString("yyyyMMddHHmmss") + "_Allowance.xls";
book.SaveAs(Server.MapPath("../Download/" + fileName), Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlExclusive, Excel.XlSaveConflictResolution.xlLocalSessionChanges,
Missing.Value, Missing.Value, Missing.Value, Missing.Value);
string filePath = Server.MapPath("../Download/" + fileName);
book.Close(true, Missing.Value, Missing.Value);
books.Close();
System.Web.HttpContext.Current.Response.Clear();
// System.Web.HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename="+dataTemplate);
System.Web.HttpContext.Current.Response.WriteFile(filePath);
//HttpContext.Current.Response.ContentType = "application/ms-excel";
System.Web.HttpContext.Current.Response.End();
}
finally
{
//book.Close(true, Missing.Value, Missing.Value);
// books.Close();
app.Quit();
foreach (System.Diagnostics.Process excelProcess in System.Diagnostics.Process.GetProcessesByName("EXCEL"))
{
if (excelProcess.StartTime > start && excelProcess.StartTime < end)
{
excelProcess.Kill();
}
}
}
}
/// <summary>
/// 将GridView数据导出到Excel
/// </summary>
/// <param name="gv">网格名称(如GridView1)</param>
/// <param name="FileType">要导出的文件类型(Excel:application/ms-excel)</param>
/// <param name="FileName">要保存的文件名</param>
public static void GridViewToExcel(Control gv, string FileType, string FileName)
{
HttpContext.Current.Response.Charset = "GB2312";
HttpContext.Current.Response.ContentEncoding = Encoding.UTF8;//注意编码
HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, Encoding.UTF8).ToString());
HttpContext.Current.Response.ContentType = FileType;//image/JPEG;text/HTML;image/GIF;vnd.ms-excel/msword
gv.Page.EnableViewState = false;
StringWriter tw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(tw);
gv.RenderControl(hw);
HttpContext.Current.Response.Write(tw.ToString());
HttpContext.Current.Response.End();
}
}
希望对你有帮助,祝你好运!!!
已赞过 已踩过<
你对这个回答的评价是?
评论 收起
收起 更多回答(1)
推荐律师服务: 若未解决您的问题,请您详细描述您的问题,通过百度律临进行免费专业咨询

为你推荐:

下载百度知道APP,抢鲜体验
使用百度知道APP,立即抢鲜体验。你的手机镜头里或许有别人想知道的答案。
扫描二维码下载
×

类别

我们会通过消息、邮箱等方式尽快将举报结果通知您。

说明

0/200

提交
取消

辅 助

模 式