首页 > > 网络编程 > Asp编程 >

利用ASP制作EXECL报表方法

2008-02-23 09:41:18来源：互联网阅读 ()

很多时候我们需要把表格形式的数据转换成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")
%>
 请自己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