很多时候我们需要把表格形式的数据转换成execl的形式呈现在用户面前，其中有好几个方法可以做到一点，我将介绍一种利用asp完成的方法，该方法允许服务器动态地创建execl报表而且不用占用任何服务器空间。该方法还允许多个用户同时收到该数据。但是该方法至少需要execl
97的支持。
废话少说，要完成这个工作最重要的是要告诉浏览器http头，就用如下代码：
<%
response.contenttype = "application/vnd.ms-excel"
%>
下面来看一个例子，假设现在有如下形式的数据：
flavor qty_baked qty_eaten qty_sold price
boston 24 2 10 0.5
jelly 24 1 12 0.5
strawberry 36 1 15 0.5
chocolate 24 2 6 0.75
maple 12 1 6 0.75
客户要求用execl的形式表现出来，并且希望其中能加上其他一些计算汇总
用如下代码：
……
<%
response.contenttype = "application/vnd.ms-excel"
set conntemp=server.createobject("adodb.connection")
cnpath="dbq=" & server.mappath("/stevesmith/data/timesheet.mdb")
conntemp.open "driver={microsoft access driver (*.mdb)}; " & cnpath
set rs=conntemp.execute("select * from donut")
%>
<table border=1>
<tr>
<%
' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
' % loop through fields names and print out the field names
' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
j = 2 'row counter
for i = 0 to rs.fields.count - 1
%>
<td><b><% = rs(i).name %></b></td>
<% next %>
<td><b>on hand (calculated)</b></td>
<td><b>gross (calculated)</b></td>
</tr>
<%
' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
' % loop through rows, displaying each field
' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
do while not rs.eof
%>
<tr>
<% for i = 0 to rs.fields.count - 1
%>
<td valign=top><% = rs(i) %></td>
<% next %>
<td>=b<%=j%>-c<%=j%>-d<%=j%></td>
<td>=d<%=j%>*e<%=j%></td>
</tr>
<%
rs.movenext
j = j + 1
loop
' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
' % make sure to close the result set and the connection object
' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
rs.close
%>
<tr bgcolor=red>
<td>totals</td>
<td>=sum(b2:b6)</td>
<td>=sum(c2:c6)</td>
<td>=sum(d2:d6)</td>
<td>n/a</td>
<td>=sum(f2:f6)</td>
<td>=sum(g2:g6)</td>
</table>
……
这样我们就实现了目的，用户可以在浏览器窗口就打开它进行简单操作，也可以保存到硬盘上进行其他操作。我还将介绍一种利用filesystemobject操作的方法。请稍候。：）
废话少说，请看代码：
runquery.asp
<%@ language="vbscript" %>
<%
'dsnless connection to access database
strdsnpath = "provider=msdasql;driver={microsoft access driver (*.mdb)};dbq=" & server.mappath("testdb.mdb")
%>
<!--#include file="adovbs.inc" --> 请自己copy这个文件
<%
server.scripttimeout=1000
response.buffer = true
if(request.form("returnas") = "content") then
response.contenttype = "application/msexcel"
end if
response.expires = 0
dim oconn
dim ors
dim strsql
dim strfile
set oconn = server.createobject("adodb.connection")
set ors = server.createobject("adodb.recordset")
strsql = buildsql()
ors.open strsql, strdsnpath, adopenforwardonly, adlockreadonly, adcmdtext
%>
<!doctype html public "-//w3c//dtd html 4.0 transitional//en">
<html>
<head>
<title>excel export demo</title>
</head>
<body>
<%
if(request.form("returnas") = "csv") then
createcsvfile()
else if(request.form("returnas") = "excel") then
createxlsfile()
else if(request.form("returnas") = "html") then
genhtml()
else if(request.form("returnas") = "content") then
genhtml()
end if
end if
end if
end if
set ors = nothing
set oconn = nothing
response.flush
%>
</body>
</html>
<script language=vbscript runat=server>
function buildsql()
dim strsql
dim strtemp
strtemp = ""
strsql = "select year, region, sales_amt from sales"
if(request.form("year") <> "all") then
strtemp = " where year = "
strtemp = strtemp & request.form("year")
end if
if(request.form("region") <> "all") then
if(len(strtemp) > 0) then
strtemp = strtemp & " and region = "
else
strtemp = strstl & " where region = "
end if
strtemp = strtemp & "'"
strtemp = strtemp & request.form("region")
strtemp = strtemp & "'"
end if
buildsql = strsql & strtemp
end function
function genfilename()
dim fname
fname = "file"
systime=now()
fname= fname & cstr(year(systime)) & cstr(month(systime)) & cstr(day(systime))
fname= fname & cstr(hour(systime)) & cstr(minute(systime)) & cstr(second(systime))
genfilename = fname
end function
function genhtml()
response.write("<div align=center><font size=+1>sales reporting</font></div>")
response.write("<table width=100% border=1 cellspacing=1 cellpadding=1>")
response.write("<tr>")
response.write(" <td>year</td>")
response.write(" <td>region</td>")
response.write(" <td>sales</td>")
response.write("</tr>")
if(ors.bof = true and ors.eof = true) then
response.write("database empty")
else
ors.movefirst
do while not ors.eof
response.write("<tr>")
response.write("<td>")
response.write(ors.fields("year").value)
response.write("</td>")
response.write("<td>")
response.write(ors.fields("region").value)
response.write("</td>")
response.write("<td>")
response.write(ors.fields("sales_amt").value)
response.write("</td>")
response.write("</tr>")
ors.movenext
loop
response.write("</table>")
end if
end function
function createcsvfile()
strfile = genfilename()
set fs = server.createobject("scripting.filesystemobject")
set a = fs.createtextfile(server.mappath(".") & "\" & strfile & ".csv",true)
if not ors.eof then
strtext = chr(34) & "year" & chr(34) & ","
strtext = strtext & chr(34) & "region" & chr(34) & ","
strtext = strtext & chr(34) & "sales" & chr(34) & ","
a.writeline(strtext)
do until ors.eof
for i = 0 to ors.fields.count-1
strtext = chr(34) & ors.fields(i) & chr(34) & ","
a.write(strtext)
next
a.writeline()
ors.movenext
loop
end if
a.close
set fs=nothing
response.write("click <a href=" & strfile & ".csv>here</a> to to get csv file")
end function
function createxlsfile()
dim xlworksheet ' excel worksheet object
dim xlapplication
set xlapplication = createobject("excel.application")
xlapplication.visible = false
xlapplication.workbooks.add
set xlworksheet = xlapplication.worksheets(1)
xlworksheet.cells(1,1).value = "year"
xlworksheet.cells(1,1).interior.colorindex = 5
xlworksheet.cells(1,2).value = "region"
xlworksheet.cells(1,2).interior.colorindex = 5
xlworksheet.cells(1,3).value = "sales"
xlworksheet.cells(1,3).interior.colorindex = 5
irow = 2
if not ors.eof then
do until ors.eof
for i = 0 to ors.fields.count-1
xlworksheet.cells(irow,i + 1).value = ors.fields(i)
xlworksheet.cells(irow,i + 1).interior.colorindex = 4
next
irow = irow + 1
ors.movenext
loop
end if
strfile = genfilename()
xlworksheet.saveas server.mappath(".") & "\" & strfile & ".xls"
xlapplication.quit ' close the workbook
set xlworksheet = nothing
set xlapplication = nothing
response.write("click <a href=" & strfile & ".xls>here</a> to get xls file")
end function
</script>
%>
main.htm
<!-- frames -->
<frameset rows="20%,*">
<frame name="request" src="request.html" marginwidth="10" marginheight="10" scrolling="auto" frameborder="yes">
<frame name="result" src="welcome.html" marginwidth="10" marginheight="10" scrolling="auto" frameborder="yes">
</frameset>
request.htm
<html>
<head>
<title>sales report demo</title>
</head>
<body>
<div align="center"><font size="+1">sales reporting</font></div>
<form action="runquery.asp" method="post" target=result>
year <select name="year">
<option value="all">all</option>
<option value="1995">1995</option>
<option value="1996">1996</option>
<option value="1997">1997</option>
<option value="1998">1998</option>
<option value="1999">1999</option>
</select>
?
region <select name="region">
<option value="all">all</option>
<option value="north">north</option>
<option value="east">east</option>
<option value="south">south</option>
<option value="west">west</option>
</select>
?
return results using
<select name="returnas">
<option value="html">html table</option>
<option value="content">content type</option>
<option value="csv">csv</option>
<option value="excel">native excel</option>
</select>
<input type="submit" name="submit" value="submit">
</form>
</body>
</html>
welcome.htm
<html>
<head>
<title>sales report demo</title>
</head>
<body>
</body>
</html>
数据库结构
testdb.mdb
表sales
year 数字
region 文本
sales_amt 货币
本文原始出处为国外一网站，并经过batman的休正。
