很多时候我们需要把表格形式的数据转换成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操作的方法。请稍候。:)