利用ASP制作EXECL报表方法（二） (1)

废话少说，请看代码
runquery.asp

%26lt;%@ LANGUAGE=%26quot;VBSCRIPT%26quot; %%26gt;
%26lt;%
'DSNless connection to Access Database
strDSNPath = %26quot;PROVIDER=MSDASQL;DRIVER={Microsoft Access Driver (*.mdb)};DBQ=%26quot; %26amp; Server.MapPath(%26quot;testDB.mdb%26quot;)
%%26gt;
%26lt;!--#include file=%26quot;adovbs.inc%26quot; --%26gt;请自己COPY这个文件
%26lt;%
server.scripttimeout=1000
Response.Buffer= True

if(Request.Form(%26quot;ReturnAS%26quot;) = %26quot;Content%26quot;) then
Response.ContentType = %26quot;application/msexcel%26quot;
end if
Response.Expires = 0

dim oConn
dim oRS
dim strSQL
dim strFile

Set oConn = Server.CreateObject(%26quot;ADODB.Connection%26quot;)
Set oRS = Server.CreateObject(%26quot;ADODB.Recordset%26quot;)
strSQL = BuildSQL()

oRS.Open strSQL, strDSNPath, adOpenForwardOnly, adLockReadOnly, adCmdText
%%26gt;
%26lt;!DOCTYPE HTML PUBLIC %26quot;-//W3C//DTD HTML 4.0 Transitional//EN%26quot;%26gt;

%26lt;html%26gt;
%26lt;head%26gt;
%26lt;title%26gt;Excel Export Demo%26lt;/title%26gt;
%26lt;/head%26gt;
%26lt;body%26gt;
%26lt;%
if(Request.Form(%26quot;ReturnAS%26quot;) = %26quot;CSV%26quot;) then
CreateCSVFile()
else if(Request.Form(%26quot;ReturnAS%26quot;) = %26quot;Excel%26quot;) then
CreateXlsFile()
else if(Request.Form(%26quot;ReturnAS%26quot;) = %26quot;HTML%26quot;) then
GenHTML()
else if(Request.Form(%26quot;ReturnAS%26quot;) = %26quot;Content%26quot;) then
GenHTML()
end if
end if
end if
end if

Set oRS = Nothing
Set oConn = Nothing
Response.Flush
%%26gt;
%26lt;/body%26gt;
%26lt;/html%26gt;
%26lt;SCRIPT LANGUAGE=vbscript RUNAT=Server%26gt;
Function BuildSQL()
dim strSQL
dim strTemp

strTemp = %26quot;%26quot;
strSQL = %26quot;select year, region, sales_amt from sales%26quot;

if(Request.Form(%26quot;Year%26quot;) %26lt;%26gt; %26quot;ALL%26quot;) then
strTemp = %26quot; where Year = %26quot;
strTemp = strTemp %26amp; Request.Form(%26quot;Year%26quot;)
end if

if(Request.Form(%26quot;Region%26quot;) %26lt;%26gt; %26quot;ALL%26quot;) then
if(Len(strTemp) %26gt; 0) then
strTemp = strTemp %26amp; %26quot; and Region = %26quot;
else
strTemp = strSTL %26amp; %26quot; where Region = %26quot;
end if
strTemp = strTemp %26amp; %26quot;'%26quot;
strTemp = strTemp %26amp; Request.Form(%26quot;Region%26quot;)
strTemp = strTemp %26amp; %26quot;'%26quot;
end if

BuildSQL = strSQL %26amp; strTemp
End Function

Function GenFileName()
dim fname

fname = %26quot;File%26quot;
systime=now()
fname= fname %26amp; cstr(year(systime)) %26amp; cstr(month(systime)) %26amp; cstr(day(systime))
fname= fname%26amp; cstr(hour(systime)) %26amp; cstr(minute(systime)) %26amp; cstr(second(systime))
GenFileName = fname
End Function

Function GenHTML()
Response.Write(%26quot;%26lt;DIV ALIGN=center%26gt;%26lt;FONT SIZE=+1%26gt;Sales Reporting%26lt;/FONT%26gt;%26lt;/DIV%26gt;%26quot;)
Response.Write(%26quot;%26lt;TABLE WIDTH=100% BORDER=1 CELLSPACING=1 CELLPADDING=1%26gt;%26quot;)
Response.Write(%26quot;%26lt;TR%26gt;%26quot;)
Response.Write(%26quot;%26lt;TD%26gt;Year%26lt;/TD%26gt;%26quot;)
Response.Write(%26quot;%26lt;TD%26gt;Region%26lt;/TD%26gt;%26quot;)
Response.Write(%26quot;%26lt;TD%26gt;Sales%26lt;/TD%26gt;%26quot;)
Response.Write(%26quot;%26lt;/TR%26gt;%26quot;)
if(oRS.BOF = True and oRS.EOF = True) then
Response.Write(%26quot;Database Empty%26quot;)
else
oRS.MoveFirst
Do While Not oRS.EOF
Response.Write(%26quot;%26lt;TR%26gt;%26quot;)
Response.Write(%26quot;%26lt;TD%26gt;%26quot;)
Response.Write(oRS.Fields(%26quot;Year%26quot;).Value)
Response.Write(%26quot;%26lt;/TD%26gt;%26quot;)
Response.Write(%26quot;%26lt;TD%26gt;%26quot;)
Response.Write(oRS.Fields(%26quot;Region%26quot;).Value)
Response.Write(%26quot;%26lt;/TD%26gt;%26quot;)
Response.Write(%26quot;%26lt;TD%26gt;%26quot;)
Response.Write(oRS.Fields(%26quot;Sales_Amt%26quot;).Value)
Response.Write(%26quot;%26lt;/TD%26gt;%26quot;)
Response.Write(%26quot;%26lt;/TR%26gt;%26quot;)
oRS.MoveNext
Loop
Response.Write(%26quot;%26lt;/TABLE%26gt;%26quot;)
End if
End Function

Function CreateCSVFile()

strFile = GenFileName()
Set fs = Server.CreateObject(%26quot;Scripting.FileSystemObject%26quot;)
Set a = fs.CreateTextFile(server.MapPath(%26quot;.%26quot;) %26amp; %26quot;\%26quot; %26amp; strFile %26amp; %26quot;.csv%26quot;,True)
If Not oRS.EOF Then
strtext = chr(34) %26amp; %26quot;Year%26quot; %26amp; chr(34) %26amp; %26quot;,%26quot;
strtext = strtext %26amp; chr(34) %26amp; %26quot;Region%26quot; %26amp; chr(34) %26amp; %26quot;,%26quot;
strtext = strtext %26amp; chr(34) %26amp; %26quot;Sales%26quot; %26amp; chr(34) %26amp; %26quot;,%26quot;
a.WriteLine(strtext)
Do Until oRS.EOF
For i = 0 To oRS.fields.Count-1
strtext = chr(34) %26amp; oRS.fields(i) %26amp; chr(34) %26amp; %26quot;,%26quot;
a.Write(strtext)
Next
a.Writeline()
oRS.MoveNext
Loop
End If
a.Close
Set fs=Nothing
Response.Write(%26quot;Click %26lt;A HRef=%26quot; %26amp; strFile %26amp; %26quot;.csv%26gt;Here%26lt;/A%26gt; to to get CSV file%26quot;)
End Function
Function CreateXlsFile()
Dim xlWorkSheet' Excel Worksheet object
Dim xlApplication

Set xlApplication = CreateObject(%26quot;Excel.application%26quot;)
xlApplication.Visible = False
xlApplication.Workbooks.Add
Set xlWorksheet = xlApplication.Worksheets(1)
xlWorksheet.Cells(1,1).Value = %26quot;Year%26quot;
xlWorksheet.Cells(1,1).Interior.ColorIndex = 5
xlWorksheet.Cells(1,2).Value = %26quot;Region%26quot;
xlWorksheet.Cells(1,2).Interior.ColorIndex = 5
xlWorksheet.Cells(1,3).Value = %26quot;Sales%26quot;
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(%26quot;.%26quot;) %26amp; %26quot;\%26quot; %26amp; strFile %26amp; %26quot;.xls%26quot;
xlApplication.Quit' Close the Workbook
Set xlWorksheet = Nothing
Set xlApplication = Nothing
Response.Write(%26quot;Click %26lt;A HRef=%26quot; %26amp; strFile %26amp; %26quot;.xls%26gt;Here%26lt;/A%26gt; to get XLS file%26quot;)
End Function
%26lt;/script%26gt;
%%26gt;

main.htm

%26lt;!-- frames --%26gt;
%26lt;FRAMESETROWS=%26quot;20%,*%26quot;%26gt;
%26lt;FRAME NAME=%26quot;Request%26quot; SRC=%26quot;request.html%26quot; MARGINWIDTH=%26quot;10%26quot; MARGINHEIGHT=%26quot;10%26quot; SCROLLING=%26quot;auto%26quot; FRAMEBORDER=%26quot;yes%26quot;%26gt;
%26lt;FRAME NAME=%26quot;Result%26quot; SRC=%26quot;welcome.html%26quot; MARGINWIDTH=%26quot;10%26quot; MARGINHEIGHT=%26quot;10%26quot; SCROLLING=%26quot;auto%26quot; FRAMEBORDER=%26quot;yes%26quot;%26gt;
%26lt;/FRAMESET%26gt;

request.htm

%26lt;html%26gt;
%26lt;head%26gt;
%26lt;title%26gt;Sales Report Demo%26lt;/title%26gt;
%26lt;/head%26gt;

%26lt;body%26gt;

%26lt;DIV ALIGN=%26quot;center%26quot;%26gt;%26lt;FONT SIZE=%26quot;+1%26quot;%26gt;Sales Reporting%26lt;/FONT%26gt;%26lt;/DIV%26gt;
%26lt;FORM ACTION=%26quot;runquery.asp%26quot; METHOD=%26quot;POST%26quot; target=Result%26gt;
Year %26lt;SELECT NAME=%26quot;Year%26quot;%26gt;
%26lt;OPTION VALUE=%26quot;ALL%26quot;%26gt;ALL%26lt;/OPTION%26gt;
%26lt;OPTION VALUE=%26quot;1995%26quot;%26gt;1995%26lt;/OPTION%26gt;
%26lt;OPTION VALUE=%26quot;1996%26quot;%26gt;1996%26lt;/OPTION%26gt;
%26lt;OPTION VALUE=%26quot;1997%26quot;%26gt;1997%26lt;/OPTION%26gt;
%26lt;OPTION VALUE=%26quot;1998%26quot;%26gt;1998%26lt;/OPTION%26gt;
%26lt;OPTION VALUE=%26quot;1999%26quot;%26gt;1999%26lt;/OPTION%26gt;
%26lt;/SELECT%26gt;
%26amp;nbsp;
Region%26lt;SELECT NAME=%26quot;Region%26quot;%26gt;
%26lt;OPTION VALUE=%26quot;ALL%26quot;%26gt;ALL%26lt;/OPTION%26gt;
%26lt;OPTION VALUE=%26quot;North%26quot;%26gt;North%26lt;/OPTION%26gt;
%26lt;OPTION VALUE=%26quot;East%26quot;%26gt;East%26lt;/OPTION%26gt;
%26lt;OPTION VALUE=%26quot;South%26quot;%26gt;South%26lt;/OPTION%26gt;
%26lt;OPTION VALUE=%26quot;West%26quot;%26gt;West%26lt;/OPTION%26gt;
%26lt;/SELECT%26gt;
%26amp;nbsp;
Return Results Using
%26lt;SELECT NAME=%26quot;ReturnAS%26quot;%26gt;
%26lt;OPTION VALUE=%26quot;HTML%26quot;%26gt;HTML Table%26lt;/OPTION%26gt;
%26lt;OPTION VALUE=%26quot;Content%26quot;%26gt;Content Type%26lt;/OPTION%26gt;
%26lt;OPTION VALUE=%26quot;CSV%26quot;%26gt;CSV%26lt;/OPTION%26gt;
%26lt;OPTION VALUE=%26quot;Excel%26quot;%26gt;Native Excel%26lt;/OPTION%26gt;
%26lt;/SELECT%26gt;
%26amp;nbsp;
%26amp;nbsp;
%26lt;INPUT TYPE=%26quot;Submit%26quot; NAME=%26quot;Submit%26quot; VALUE=%26quot;Submit%26quot;%26gt;
%26lt;/FORM%26gt;
%26lt;/body%26gt;
%26lt;/html%26gt;

welcome.htm
%26lt;html%26gt;
%26lt;head%26gt;
%26lt;title%26gt;Sales Report Demo%26lt;/title%26gt;
%26lt;/head%26gt;

%26lt;body%26gt;



%26lt;/body%26gt;
%26lt;/html%26gt;

数据库结构
testDB.Mdb
表sales
year数字
Region文本
Sales_Amt 货币

本文原始出处为国外一网站，并经过BATMAN的休正。