|
很多時候我們需要把表格形式的數據轉換成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操作的方法。請稍候。：）
|
