怎样用ASP把读出来的数据生成一个EXCEL文件,再直接下载到客户机

hansonboy 2004-05-13 04:02:33
如题
...全文
214 8 打赏 收藏 转发到动态 举报
写回复
用AI写文章
8 条回复
切换为时间正序
请发表友善的回复…
发表回复
blueice2002 2004-05-14
  • 打赏
  • 举报
 回复
上面 的代码简单，方便
blueice2002 2004-05-14
  • 打赏
  • 举报
 回复
<!--#include file='conn.asp'-->
<%
dim tablename
sql2 = request("sql")
tablename = request("tablename")
if tablename = "" then
tablename = "数据导出结果"
end if
sql = replace(sql2,";","")
filetype = lcase(request("t"))
dim dbid,IndexPageName
function HTMLEncode(fString)
if not isnull(fString) then

fString = Server.HTMLEncode(fString)
fString = Replace(fString, CHR(10) & CHR(10), "</P><P> ")
fString = Replace(fString, CHR(10), "<BR> ")
fString = Replace(fString, CHR(9), "  ")

HTMLEncode = fString
end if
end function
dim def_export_val
def_export_sep = ","
def_export_val = """"
if lcase(left(sql,6))<>"select" then
response.write "出错了。。。"
response.end
end if
Set rs = Conn.Execute(sql)

if filetype="csv" then
'response.contenttype="application/vnd.ms-excel"
response.contenttype="text/csv"
response.AddHeader "Content-Disposition", "attachment;filename="&tablename&".csv"
strLine=""
For each x in rs.fields
strLine= strLine & def_export_val & x.name & def_export_val & def_export_sep
Next
response.write strLine & vbnewline
While rs.EOF =false
strLine= ""
For each x in rs.fields
strLine= strLine & def_export_val & replace(x.value,"""","""""") & def_export_val & def_export_sep
Next
rs.MoveNext
response.write strLine & vbnewline
Wend
else
if filetype="htm" then'弹出下载html的对话框
response.contenttype="application/ms-download"
response.AddHeader "Content-Disposition", "attachment;filename="&tablename&".htm"
end if
%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<META NAME="Author" CONTENT="51windows,海娃,haiwa">
<META NAME="Description" CONTENT="Power by 51windows.Net">
<title>导出数据。。</title>
<style>
<!--
body { font-family: Verdana; font-size: 11px }
th { font-family: Verdana; font-size: 11px;padding:3px;color:#FFFFFF;background-color:#999999;}
td { font-family: Verdana; font-size: 11px;padding:3px;background-color:#EFEFEF;}
-->
</style>
</head>
<BODY style="overflow:auto;" topmargin=2 bgcolor=buttonface>
<div align="center">
<table border="0" cellpadding="0" cellspacing="1" bgcolor="#000000">
<tr>
<%
i=0
For each x in rs.fields
strLine= strLine &chr(9)&chr(9)&"<th align=""center"">"& x.name &"</th>"& vbnewline
Next
response.write strLine&chr(9)&"</tr>"& vbnewline & vbnewline
While rs.EOF =false
i=i+1
response.write chr(9)&"<tr>"& vbnewline
strLine= ""
For each x in rs.fields
strLine= strLine &chr(9)&chr(9)&"<td>"& HTMLEncode(x.value) &"</td>"& vbnewline
Next
rs.MoveNext
response.write strLine
response.write chr(9)&"</tr>"& vbnewline & vbnewline
Wend
response.write "</table>"& vbnewline
if filetype<>"htm" and filetype<>"xls" and filetype<>"txt" then
response.write "<p style='line-height:160%;'>"&i&"条记录 <a href='?tablename="&request("tablename")&"&t=htm&sql="&server.urlencode(sql2)&"'>导出HTML</a>|<a href='?tablename="&request("tablename")&"&t=csv&sql="&server.urlencode(sql2)&"'>导出EXCEL</a>"& vbnewline
end if
response.write "<p>Power by <A HREF=""http://www.51windows.Net"" target=""_blank"">51windows.Net</A>"& vbnewline
response.write "</div>"& vbnewline
response.write "</BODY>"& vbnewline
response.write "</HTML>"& vbnewline
%>

<%
end if

rs.close
conn.close
Set rs=nothing
Set conn=nothing
%>


hansonboy 2004-05-14
  • 打赏
  • 举报
 回复
是不是用流来处理的?但小弟对这方面不熟,谁能给个例子,谢了
hansonboy 2004-05-13
  • 打赏
  • 举报
 回复
谢谢csbq(cs) 我就是要你的那种方法,不过还有个问题,有什么办法可以让它下载到客户机上,而不是在IE里打开它
lonaerd 2004-05-13
  • 打赏
  • 举报
 回复
靠，给你个最简单最实用的：
<script language="javascript">
function exportExcel(atblData){

var filename='<%=date%>'+'统计报表.xls';
if (typeof(EXPORT_OBJECT)!="object"){
document.body.insertAdjacentHTML("afterBegin","<OBJECT style='display:none' classid=clsid:0002E510-0000-0000-C000-000000000046 id=EXPORT_OBJECT></Object>");
}


with (EXPORT_OBJECT){
DataType = "HTMLData";
HTMLData =atblData.outerHTML;
try{
ActiveSheet.Export(filename, 0);
alert('输出Excel完毕');
}
catch (e){
alert('输出Excel表失败，请确定已安装Excel2000(或更高版本),并且没打开同名xls文件');
}
}
}



</script>

<table border="0" width="100%" height="63" cellspacing="1" id="tblData">
<tr>
<td>dfsf</td><td>dfsf</td><td>dfsf</td>

</tr>
<tr>
<td>dfsf</td><td>dfsf</td><td>dfsf</td>

</tr>
</table>

<img border="0" src="../images/excel.gif" onclick="exportExcel(tblData);" style={cursor:hand;}>
csbq 2004-05-13
  • 打赏
  • 举报
 回复
书店管理中的程序
<%
Response.ContentType = "application/vnd.ms-excel" 这句话是关键
%>
<%
Set rs = Server.CreateObject ("ADODB.Recordset")
sql="select * from 订购图书 where 用户名 = '"& Session("UserNm") &"' order by 编号 desc"
Set rs = conn.execute(sql)
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<title>订购图书数据下载</title>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
</head>

<body topmargin="0" onLoad="javascript:window.close()">
<table width="1000" height="30" border="1" align="center" cellpadding="2" cellspacing="0" bordercolor="#000000">
<tr>
<td width="146" height="19"> <div align="left"><font color="#000000" size="2">ISBN</font></div></td>
<td width="108"><div align="left"><font color="#000000" size="2">作者</font></div></td>
<td width="111"><div align="left"><font color="#000000" size="2" face="宋体">书名</font></div></td>
<td width="84"><div align="left"><font color="#000000" size="2">分类号</font></div></td>
<td width="77"><div align="left"><font color="#000000" size="2">开本</font></div></td>
<td width="75"><div align="left"><font color="#333333" size="2">版次</font></div></td>
<td width="55"><div align="left"><font color="#000000" size="2">出版社</font></div></td>
<td width="58"><div align="left"><font color="#000000" size="2">出版日期</font></div></td>
<td width="28"><div align="left"><font size="2">定价</font></div></td>
<td width="24"><font size="2">数量</font></td>
<td width="29"><font size="2">装帧</font></td>
<td width="42"><div align="left"><font size="2">内容简介</font></div></td>
<td width="43"><font size="2">订购日期</font></td>
</tr>
</table>
<%
Do While Not rs.EOF
%>
<table width="1000" height="30" border="1" align="center" cellpadding="2" cellspacing="0" bordercolor="#000000">
<tr>
<td width="143" height="6"><font color="#000000" size="2" face="宋体">
<% = rs("ISBN") %>
</font></td>
<td width="109"><font color="#000000" size="2" face="宋体">
<% = rs("作者") %>
</font></td>
<td width="110"><font color="#000000" size="2" face="宋体">
<% = rs("书名") %>
</font></td>
<td width="85"><font color="#000000" size="2" face="宋体">
<% = rs("分类号") %>
</font></td>
<td width="79"><font color="#000000" size="2" face="宋体">
<% = rs("开本") %>
</font></td>
<td width="77"><font color="#000000" size="2" face="宋体">
<% = rs("版次") %>
</font></td>
<td width="41"><font color="#000000" size="2" face="宋体">
<% = rs("出版社") %>
</font></td>
<td width="68"><font color="#000000" size="2" face="宋体">
<% = rs("出版日期") %>
</font></td>
<td width="30"><font color="#000000" size="2" face="宋体">
<% = rs("定价") %>
</font></td>
<td width="26"><font color="#000000" size="2" face="宋体">
<% = rs("数量") %>
</font></td>
<td width="30"><font color="#000000" size="2" face="宋体">
<% = rs("装帧") %>
</font></td>
<td width="42"><font color="#000000" size="2" face="宋体">
<% = rs("内容简介") %>
</font></td>
<td width="42"><font color="#000000" size="2" face="宋体">
<% = rs("订购日期") %>
</font></td>
</tr>
</table>
<%
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
%>
</body>
</html>
hansonboy 2004-05-13
  • 打赏
  • 举报
 回复
这样好像要求客户端把IE的安全级别设到最低才可以,而且如果分页显示 就有麻烦了
postfix2 2004-05-13
  • 打赏
  • 举报
 回复
<HTML>
<BODY>
Press the button to start Excel and display quarterly data.
<SCRIPT LANGUAGE="VBScript">
Function CreateNamesArray()
' Create an array to set multiple values at once.
Dim saNames(5, 2)
saNames(0, 0) = "John"
saNames(0, 1) = "Smith"
saNames(1, 0) = "Tom"
saNames(1, 1) = "Brown"
saNames(2, 0) = "Sue"
saNames(2, 1) = "Thomas"
saNames(3, 0) = "Jane"
saNames(3, 1) = "Jones"
saNames(4, 0) = "Adam"
saNames(4, 1) = "Johnson"
CreateNamesArray = saNames
End Function
</SCRIPT>

<SCRIPT LANGUAGE="JScript">
function AutomateExcel()
{
// Start Excel and get Application object.
var oXL = new ActiveXObject("Excel.Application");

oXL.Visible = true;

// Get a new workbook.
var oWB = oXL.Workbooks.Add();
var oSheet = oWB.ActiveSheet;

// Add table headers going cell by cell.
oSheet.Cells(1, 1).Value = "First Name";
oSheet.Cells(1, 2).Value = "Last Name";
oSheet.Cells(1, 3).Value = "Full Name";
oSheet.Cells(1, 4).Value = "Salary";

// Format A1:D1 as bold, vertical alignment = center.
oSheet.Range("A1", "D1").Font.Bold = true;
oSheet.Range("A1", "D1").VerticalAlignment = -4108; //xlVAlignCenter

// Create an array to set multiple values at once.

// Fill A2:B6 with an array of values (from VBScript).
oSheet.Range("A2", "B6").Value = CreateNamesArray();

// Fill C2:C6 with a relative formula (=A2 & " " & B2).
var oRng = oSheet.Range("C2", "C6");
oRng.Formula = "=A2 & \" \" & B2";

// Fill D2:D6 with a formula(=RAND()*100000) and apply format.
oRng = oSheet.Range("D2", "D6");
oRng.Formula = "=RAND()*100000";
oRng.NumberFormat = "$0.00";

// AutoFit columns A:D.
oRng = oSheet.Range("A1", "D1");
oRng.EntireColumn.AutoFit();

// Manipulate a variable number of columns for Quarterly Sales Data.
DispalyQuarterlySales(oSheet);

// Make sure Excel is visible and give the user control
// of Excel's lifetime.
oXL.Visible = true;
oXL.UserControl = true;
}

function DispalyQuarterlySales(oWS)
{
var iNumQtrs, sMsg, iRet;

// Number of quarters to display data for.
iNumQtrs = 4;

// Starting at E1, fill headers for the number of columns selected.
var oResizeRange = oWS.Range("E1", "E1").Resize(1,iNumQtrs);
oResizeRange.Formula = "=\"Q\" & COLUMN()-4 & CHAR(10) & \"Sales\"";

// Change the Orientation and WrapText properties for the headers.
oResizeRange.Orientation = 38;
oResizeRange.WrapText = true;

// Fill the interior color of the headers.
oResizeRange.Interior.ColorIndex = 36;

// Fill the columns with a formula and apply a number format.
oResizeRange = oWS.Range("E2", "E6").Resize(5,iNumQtrs);
oResizeRange.Formula = "=RAND()*100";
oResizeRange.NumberFormat = "$0.00";

// Apply borders to the Sales data and headers.
oResizeRange = oWS.Range("E1", "E6").Resize(6,iNumQtrs);
oResizeRange.Borders.Weight = 2; // xlThin

// Add a Totals formula for the sales data and apply a border.
oResizeRange = oWS.Range("E8", "E8").Resize(1,iNumQtrs);
oResizeRange.Formula = "=SUM(E2:E6)";
// 9 = xlEdgeBottom
oResizeRange.Borders(9).LineStyle = -4119; //xlDouble
oResizeRange.Borders(9).Weight = 4; //xlThick

// Add a Chart for the selected data.
oResizeRange = oWS.Range("E2:E6").Resize(5,iNumQtrs);
var oChart = oWS.Parent.Charts.Add();
oChart.ChartWizard(oResizeRange, -4100, null, 2); // -4100 = xl3dColumn
oChart.SeriesCollection(1).XValues = oWS.Range("A2", "A6");
for (iRet = 1; iRet <= iNumQtrs; iRet++) {
oChart.SeriesCollection(iRet).Name = "=\"Q" + iRet + "\"";
}
oChart.Location(2, oWS.Name); // 2 = xlLocationAsObject

// Move the chart so as not to cover your data.
oWS.Shapes("Chart 1").Top = oWS.Rows(10).Top;
oWS.Shapes("Chart 1").Left = oWS.Columns(2).Left;
}
</SCRIPT>
<P><INPUT id=button1 type=button value="Start Excel"
onclick="AutomateExcel"></P>
</BODY>
</HTML>
ASP与Excel结合生成数据表和Chart图的代码
通过以上的知识点，我们可以构建一个ASP程序，这个程序不仅能够连接数据库提取数据，还能将这些数据输出到Excel文件中，并按照需求生成各类图表。同时，通过合理的服务器端和客户端配置，确保整个应用在Web环境下...
《Excel应用大全》示例文件 光盘文件
第1篇 Excel基本功能 • 冻结窗格 • 名称框定位 • 区域名称显示 • 多表区域设置格式 • 自动填充功能 • 数据的格式化 • 设置数字格式 • 数据类型转换 • 自定义数字格式 • 数据的填充复制 • 数据的查找替换...
ASP下操作Excel技术总结分析
ASP操作Excel技术总结 目录 一、　环境配置 二、　ASP对Excel的基本操作 三、　ASP操作Excel生成数据表 四、　ASP操作Excel生成Chart图 五、　服务器端Excel文件浏览、下载、删除方案 六、　附录 正文 一、...
Excel导入SQL数据库
Office的版本没有特殊要求，考虑到客户机配置的不确定性和下兼容特性，建议服务器端Office版本不要太高，以防止客户机下载后无法正确显示。 服务器端环境配置还有两个偶然的发现是： 1． 笔者开发机器上原来装有金山...
ASP

28,404

社区成员

356,946

社区内容

发帖
与我相关
我的任务
社区描述
ASP即Active Server Pages，是Microsoft公司开发的服务器端脚本环境。
社区管理员
  • ASP
  • 无·法
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章