ASP to Excel

[Ben Moen]

(also appears in microsoft.public.inetserver.iis)...

I am running into problems with something that seems to be
a popular requirement - porting data from an asp page to
an Excel workbook to allow users to manipulate data. I
have a few different methods working, but also with some
issues. I want to know if these are technical
limitations, or if there is another direction to take such
as web query...

I have successfully used the "Response.ContentType
= "application/vnd.ms-excel" in ASP to stream everything
in the page as an Excel document. However, when I use
this, simple SUM() formulas do not work in the resulting
Excel worksheet. The worksheet does not recognize results
from my dataset as true number types.

In response to a previous post, I tried using another
method - constructing, and manipulating a worksheet object
using ActiveX. This allows the user to play with the
resulting worksheet, however, they would be required to
change their default IE settings. This is a corporate
intranet app, and requiring our entire user base to tweak
their browser settings is going to be unreasonable.

I am trying to stay away from writing out distinct Excel
files on the server for performance, and security
concerns. As I understand it, web query will generate a
physical file to download. Can web query instead stream
content to a browser? Any other suggestions?

THANKS,

Ben

 

[Tim Williams]

Ben,

It's possible to pass explicit formatting instructions to Excel

Here's an example:

<table name="sheet1">
<tr><td STYLE="vnd.ms-excel.numberformat:0.000">22</td></tr>
<tr><td STYLE="vnd.ms-excel.numberformat:0.000">10.67</td></tr>
<tr><td>=SUM(A1:A2)</td></tr>
</table>


Tim.

 

[Chris Leonard]

Ben.

I've used webqueries quite a few times and found them extremely powerful.

One largely unknown thing is you can add conditioning underneath your query
template, here is one I created as a quick example.

WEB
1
c:/excel/examples/test.htm

Selection=AllTables
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False

One other nice way to get information from the web is to use the XMLHTTP
object, seach in google for this as it's a nice easy way to get info into a
specific cell anywhere on the sheet using minimal VB

Hope this helps.

Chris