利用ASP制作EXECL报表方法(一)

很多时候我们需要把表格形式的数据转换成EXECL的形式呈现在用户面前,其中有好几个方法可以做到一点,我将介绍一种利用ASP完成的方法,该方法允许服务器动态地创建EXECL报表而且不用占用任何服务器空间。该方法还允许多个用户同时收到该数据。但是该方法至少需要EXECL 97的支持。
废话少说,要完成这个工作最重要的是要告诉浏览器HTTP头,就用如下代码:

1   
2Response.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的形式表现出来,并且希望其中能加上其他一些计算汇总

用如下代码:
……

1   
2Response.ContentType = "application/vnd.ms-excel"   
3  
4set conntemp=server.createobject("adodb.connection")   
5cnpath="DBQ=" & server.mappath("/stevesmith/data/timesheet.mdb")   
6conntemp.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & cnpath   
7set RS=conntemp.execute("select * from donut")   
1<table border="1">
2<tr>   

' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
' % Loop through Fields Names and print out the Field Names
' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
j = 2 'row counter
For i = 0 to RS.Fields.Count - 1

1<td><b>```
2 = RS(i).Name 
3```</b></td>   

Next

1<td><b>On Hand (calculated)</b></td>
2<td><b>Gross (calculated)</b></td>
3</tr>   

' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
' % Loop through rows, displaying each field
' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
Do While Not RS.EOF

1<tr>   

For i = 0 to RS.Fields.Count - 1

1<td valign="TOP">```
2 = RS(i) 
3```</td>   

Next

 1<td>=b```
 2=j
 3```-c```
 4=j
 5```-d```
 6=j
 7```</td>
 8<td>=d```
 9=j
10```*e```
11=j
12```</td>
13</tr>   

RS.MoveNext
j = j + 1
Loop
' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
' % Make sure to close the Result Set and the Connection object
' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
RS.Close

1<tr bgcolor="RED">
2<td>Totals</td>
3<td>=SUM(B2:B6)</td>
4<td>=SUM(C2:C6)</td>
5<td>=SUM(D2:D6)</td>
6<td>n/a</td>
7<td>=SUM(F2:F6)</td>
8<td>=SUM(G2:G6)</td>
9</tr></table>

……
这样我们就实现了目的,用户可以在浏览器窗口就打开它进行简单操作,也可以保存到硬盘上进行其他操作。我还将介绍一种利用filesystemobject操作的方法。请稍候。:)

Published At
Categories with Web编程
Tagged with
comments powered by Disqus