📄 利用asp制作execl报表方法（二）.htm
字号:
True<BR><BR>if(Request.Form("ReturnAS") = "Content")
then<BR>Response.ContentType = "application/msexcel"<BR>end
if<BR>Response.Expires = 0<BR><BR>dim oConn<BR>dim oRS<BR>dim
strSQL<BR>dim strFile<BR><BR>Set oConn =
Server.CreateObject("ADODB.Connection")<BR>Set oRS =
Server.CreateObject("ADODB.Recordset")<BR>strSQL =
BuildSQL()<BR><BR>oRS.Open strSQL, strDSNPath, adOpenForwardOnly,
adLockReadOnly, adCmdText<BR>%><BR><!DOCTYPE HTML PUBLIC
"-//W3C//DTD HTML 4.0 Transitional//EN"></P>
<P><html><BR><head><BR><title>Excel Export
Demo</title><BR></head><BR><body><BR><%<BR>if(Request.Form("ReturnAS")
= "CSV") then<BR>CreateCSVFile()<BR>else
if(Request.Form("ReturnAS") = "Excel") then<BR>CreateXlsFile()
<BR>else if(Request.Form("ReturnAS") = "HTML")
then<BR>GenHTML()<BR>else if(Request.Form("ReturnAS") = "Content")
then<BR>GenHTML() <BR>end if<BR>end if<BR>end if<BR>end
if<BR><BR>Set oRS = Nothing<BR>Set oConn = Nothing
<BR>Response.Flush<BR>%><BR></body><BR></html><BR><SCRIPT
LANGUAGE=vbscript RUNAT=Server><BR>Function BuildSQL()<BR>dim
strSQL <BR>dim strTemp<BR><BR>strTemp = ""<BR>strSQL = "select
year, region, sales_amt from sales"<BR><BR>if(Request.Form("Year")
<> "ALL") then<BR>strTemp = " where Year = "<BR>strTemp =
strTemp & Request.Form("Year")<BR>end
if<BR><BR>if(Request.Form("Region") <> "ALL")
then<BR>if(Len(strTemp) > 0) then<BR>strTemp = strTemp & "
and Region = "<BR>else<BR>strTemp = strSTL & " where Region =
"<BR>end if<BR>strTemp = strTemp & "'"<BR>strTemp = strTemp
& Request.Form("Region")<BR>strTemp = strTemp & "'"<BR>end
if</P>
<P>BuildSQL = strSQL & strTemp<BR>End Function</P>
<P>Function GenFileName()<BR>dim fname<BR><BR>fname =
"File"<BR>systime=now()<BR>fname= fname & cstr(year(systime))
& cstr(month(systime)) & cstr(day(systime))<BR>fname=
fname & cstr(hour(systime)) & cstr(minute(systime)) &
cstr(second(systime))<BR>GenFileName = fname<BR>End Function</P>
<P>Function GenHTML()<BR>Response.Write("<DIV
ALIGN=center><FONT SIZE=+1>Sales
Reporting</FONT></DIV>")<BR>Response.Write("<TABLE
WIDTH=100% BORDER=1 CELLSPACING=1
CELLPADDING=1>")<BR>Response.Write("<TR>")<BR>Response.Write("
<TD>Year</TD>")<BR>Response.Write("
<TD>Region</TD>")<BR>Response.Write("
<TD>Sales</TD>")<BR>Response.Write("</TR>")<BR>if(oRS.BOF
= True and oRS.EOF = True) then<BR>Response.Write("Database
Empty")<BR>else<BR>oRS.MoveFirst<BR>Do While Not
oRS.EOF<BR>Response.Write("<TR>")<BR>Response.Write("<TD>")<BR>Response.Write(oRS.Fields("Year").Value)<BR>Response.Write("</TD>")<BR>Response.Write("<TD>")<BR>Response.Write(oRS.Fields("Region").Value)<BR>Response.Write("</TD>")<BR>Response.Write("<TD>")<BR>Response.Write(oRS.Fields("Sales_Amt").Value)<BR>Response.Write("</TD>")<BR>Response.Write("</TR>")<BR>oRS.MoveNext<BR>Loop<BR>Response.Write("</TABLE>")<BR>End
if<BR>End Function</P>
<P>Function CreateCSVFile()</P>
<P>strFile = GenFileName() <BR>Set fs =
Server.CreateObject("Scripting.FileSystemObject")<BR>Set a =
fs.CreateTextFile(server.MapPath(".") & "\" & strFile
& ".csv",True)<BR>If Not oRS.EOF Then<BR>strtext = chr(34)
& "Year" & chr(34) & ","<BR>strtext = strtext &
chr(34) & "Region" & chr(34) & ","<BR>strtext =
strtext & chr(34) & "Sales" & chr(34) &
","<BR>a.WriteLine(strtext)<BR>Do Until oRS.EOF <BR>For i = 0 To
oRS.fields.Count-1<BR>strtext = chr(34) & oRS.fields(i) &
chr(34) &
","<BR>a.Write(strtext)<BR>Next<BR>a.Writeline()<BR>oRS.MoveNext<BR>Loop<BR>End
If<BR>a.Close<BR>Set fs=Nothing <BR>Response.Write("Click <A
HRef=" & strFile & ".csv>Here</A> to to get CSV
file") <BR>End Function<BR>Function CreateXlsFile()<BR>Dim
xlWorkSheet ' Excel Worksheet object<BR>Dim
xlApplication<BR><BR>Set xlApplication =
CreateObject("Excel.application")<BR>xlApplication.Visible =
False<BR>xlApplication.Workbooks.Add<BR>Set xlWorksheet =
xlApplication.Worksheets(1)<BR>xlWorksheet.Cells(1,1).Value =
"Year"<BR>xlWorksheet.Cells(1,1).Interior.ColorIndex = 5
<BR>xlWorksheet.Cells(1,2).Value =
"Region"<BR>xlWorksheet.Cells(1,2).Interior.ColorIndex =
5<BR>xlWorksheet.Cells(1,3).Value =
"Sales"<BR>xlWorksheet.Cells(1,3).Interior.ColorIndex =
5<BR><BR>iRow = 2<BR>If Not oRS.EOF Then<BR>Do Until oRS.EOF
<BR>For i = 0 To oRS.fields.Count-1<BR>xlWorksheet.Cells(iRow,i +
1).Value = oRS.fields(i)<BR>xlWorkSheet.Cells(iRow,i +
1).Interior.ColorIndex = 4<BR>Next<BR>iRow = iRow +
1<BR>oRS.MoveNext<BR>Loop<BR>End If<BR>strFile =
GenFileName()<BR>xlWorksheet.SaveAs Server.MapPath(".") & "\"
& strFile & ".xls"<BR>xlApplication.Quit ' Close the
Workbook<BR>Set xlWorksheet = Nothing<BR>Set xlApplication =
Nothing<BR>Response.Write("Click <A HRef=" & strFile &
".xls>Here</A> to get XLS file") <BR>End
Function<BR></script><BR>%></P>
<P>main.htm</P>
<P><!-- frames --><BR><FRAMESET
ROWS="20%,*"><BR><FRAME NAME="Request" SRC="request.html"
MARGINWIDTH="10" MARGINHEIGHT="10" SCROLLING="auto"
FRAMEBORDER="yes"><BR><FRAME NAME="Result"
SRC="welcome.html" MARGINWIDTH="10" MARGINHEIGHT="10"
SCROLLING="auto" FRAMEBORDER="yes"><BR></FRAMESET></P>
<P>request.htm</P>
<P><html><BR><head><BR><title>Sales Report
Demo</title><BR></head></P>
<P><body></P>
<P><DIV ALIGN="center"><FONT SIZE="+1">Sales
Reporting</FONT></DIV><BR><FORM
ACTION="runquery.asp" METHOD="POST" target=Result><BR>Year
<SELECT NAME="Year"><BR><OPTION
VALUE="ALL">ALL</OPTION><BR><OPTION
VALUE="1995">1995</OPTION><BR><OPTION
VALUE="1996">1996</OPTION><BR><OPTION
VALUE="1997">1997</OPTION><BR><OPTION
VALUE="1998">1998</OPTION><BR><OPTION
VALUE="1999">1999</OPTION><BR></SELECT><BR>
<BR>Region <SELECT NAME="Region"><BR><OPTION
VALUE="ALL">ALL</OPTION><BR><OPTION
VALUE="North">North</OPTION><BR><OPTION
VALUE="East">East</OPTION><BR><OPTION
VALUE="South">South</OPTION><BR><OPTION
VALUE="West">West</OPTION><BR></SELECT><BR> <BR>Return
Results Using<BR><SELECT NAME="ReturnAS"><BR><OPTION
VALUE="HTML">HTML Table</OPTION><BR><OPTION
VALUE="Content">Content Type</OPTION><BR><OPTION
VALUE="CSV">CSV</OPTION><BR><OPTION
VALUE="Excel">Native
Excel</OPTION><BR></SELECT><BR>
<BR> <BR><INPUT TYPE="Submit" NAME="Submit"
VALUE="Submit">
<BR></FORM><BR></body><BR></html></P>
<P>welcome.htm<BR><html><BR><head><BR><title>Sales
Report Demo</title><BR></head></P>
<P><body></P>
<P></P>
<P></body><BR></html></P>
<P>数据库结构<BR>testDB.Mdb<BR>表sales<BR>year 数字<BR>Region
文本<BR>Sales_Amt 货币</P>
<P>本文原始出处为国外一网站，并经过BATMAN的休正。<BR><BR><BR></P></BLOCKQUOTE></TD></TR>
<TR>
<TD class=p4 vAlign=top width="50%">
<BLOCKQUOTE>原作者：东方蜘蛛<BR>来 源：chinaasp<BR>共有2409位读者阅读过此文<BR>【<A
href="http://bbs.aspsky.net/list.asp?boardid=1">发表评论</A>】
</BLOCKQUOTE></TD>
<TD class=p4 vAlign=top width="50%">
<P>
<LI><FONT color=#0772b1>上篇文章</FONT>：<A
href="http://www.aspsky.net/article/list.asp?id=1590">利用ASP制作EXECL报表方法（一）</A>
<BR>
<LI><FONT color=#0772b1>下篇文章</FONT>：<A
href="http://www.aspsky.net/article/list.asp?id=1592">asp.net高级教程（一）---asp.net
, 还是asp+ ？</A> </LI></TD></TR>
<TR>
<TD bgColor=#297dff class=p4 height=20 width="50%"><FONT
color=#ceffff> → 本周热门</FONT></TD>
<TD bgColor=#297dff class=p4 width="50%"><FONT color=#ceffff> →
相关文章</FONT></TD></TR>
<TR>
<TD bgColor=#586011 colSpan=2 height=1><SPACER type="block"
width="1"></TD></TR>
<TR>
<TD colSpan=2 height=7></TD></TR>
<TR>
<TD class=p4 vAlign=top width="50%">
<LI><A href="http://www.aspsky.net/article/list.asp?id=1510"
target=_top title="SQL Server 7.0 入门（一）">SQL Server 7.0
入门（...</A>[<FONT color=red>7239</FONT>]<BR>
<LI><A href="http://www.aspsky.net/article/list.asp?id=1540"
target=_top title=PHP4实际应用经验篇(1)>PHP4实际应用经验篇(1)</A>[<FONT
color=red>7135</FONT>]<BR>
<LI><A href="http://www.aspsky.net/article/list.asp?id=1536"
target=_top
title=无组件文件上传代码实例（支持多文件上传及文件和input域混合上传）>无组件文件上传代码实例（支持多文件上...</A>[<FONT
color=red>6029</FONT>]<BR>
<LI><A href="http://www.aspsky.net/article/list.asp?id=2557"
target=_top title=树型结构在ASP中的简单解决>树型结构在ASP中的简单解决</A>[<FONT
color=red>5757</FONT>]<BR>
<LI><A href="http://www.aspsky.net/article/list.asp?id=1545"
target=_top title=PHP4实际应用经验篇(6)>PHP4实际应用经验篇(6)</A>[<FONT
color=red>5599</FONT>]<BR>
<LI><A href="http://www.aspsky.net/article/list.asp?id=2563"
target=_top title=一个老个写的无组件上传>一个老个写的无组件上传</A>[<FONT
color=red>5014</FONT>]<BR>
<LI><A href="http://www.aspsky.net/article/list.asp?id=1542"
target=_top title=PHP4实际应用经验篇(3)>PHP4实际应用经验篇(3)</A>[<FONT
color=red>4731</FONT>]<BR></LI></TD>
<TD class=p4 vAlign=top width="50%">
<LI><A
href="http://www.aspsky.net/article/list.asp?id=1873">如何用asp把sql
server數據轉化為execl文件</A><BR>
<LI><A
href="http://www.aspsky.net/article/list.asp?id=1591">利用ASP制作EXECL报表方法（二）</A><BR>
<LI><A
href="http://www.aspsky.net/article/list.asp?id=1590">利用ASP制作EXECL报表方法（一）</A><BR></LI></TD></TR>
<TR>
<TD colSpan=2 height=7></TD></TR></TBODY></TABLE>
<TD bgColor=#297dff width=1> </TD></TR></TBODY></TABLE>
<TABLE border=0 cellPadding=0 cellSpacing=0 width=755>
<TBODY>
<TR>
<TD bgColor=#297dff height=1><SPACER type="block"
width="1"></TD></TR></TBODY></TABLE>
<TABLE border=0 cellPadding=0 cellSpacing=0 width=755>
<TBODY>
<TR>
<TD align=middle height=30></TD></TR></TBODY></TABLE>
<TABLE border=0 cellPadding=0 cellSpacing=0 width=755>
<TBODY>
<TR>
<TD align=middle class=p2 width="100%">
<TABLE border=0 cellPadding=0 cellSpacing=0 width=755>
<TBODY>
<TR>
<TD align=middle class=p2 width="100%">
<P align=center><A
href="http://www.aspsky.net/produce/index.asp">客户服务</A> -- <A
href="http://www.aspsky.net/aspads.asp">广告合作</A> -- <A
href="http://www.aspsky.net/about.asp">关于本站</A> -- <A
href="http://www.aspsky.net/tell.asp">联系方法</A><BR><BR>动网先锋版权所有 <FONT
face=Verdana, size=1 Arial, Helvetica, sans-serif>Copyright ©
2000-2001 <B>AspSky<FONT color=#cc0000>.Net</FONT></B>, All Rights
Reserved .</FONT>
</P></TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE></CENTER></CENTER></BODY></HTML>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -