IDC资讯中心
废话少说，请看代码：
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的休正。
