Send table to Excel

I'm building a standard HTML table using ASP (I know this isn't an ASP
group but bare with me). This page is called RESULTS.ASP. Below the
table is a button which allows users to send the contents of the table
to Excel for analysis, sorting, graphs ect..

This all works fine, except that I think my method of doing this is
badly wrong.

Basically to make this work, when the user clicks the button, I
redirect to a page called:

EXPORT_TO_EXCEL.ASP

This ASP page re-runs the query so that it can output the results to
Excel. I have copied the contents of this page below.

The problem is, its not very efficient re-running the query in
EXPORT_TO_EXCEL.ASP to get data that has already been returned in
RESULTS.ASP. So I'm wondering if I could somehow pass the table I've
already created in RESULTS.ASP to instead EXPORT_TO_EXCEL.ASP instead.

Does anyone know if this is possible?

TIA,

Colin


================================================== =========================

<%

Response.ContentType = "application/vnd.ms-excel"

Set conn = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.Recordset")

conn.open "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=(ovms.mdb)"

rs.Open Session("SQLfromLastQuery"),conn

'-- make data table for excel.

If Not rs.EOF Then

rs.MoveFirst
response.write "<table bgcolor='eeeeee'>"
response.write " <tr>"

For n = 0 to rs.Fields.Count -1
response.write " <td><b>" & rs.Fields(n).Name & "</b></td>"
Next

response.write " </tr>"


rs.MoveFirst

Do While Not rs.EOF
response.write " <tr>"
For n = 0 to rs.Fields.Count -1
response.write " <td>" & rs.Fields(n).Value & "</td>"
Next
rs.MoveNext
response.write " </tr>"
Loop
response.write "</table>"

Else

response.write "No data were found."

End If

%>

================================================== =========================

Colin Steadman

On 9 Feb 2004 08:31:57 -0800, Colin Steadman wrote:

> I'm building a standard HTML table using ASP (I know this isn't an ASP
> group but bare with me). This page is called RESULTS.ASP. Below the
> table is a button which allows users to send the contents of the table
> to Excel for analysis, sorting, graphs ect..
>
> This all works fine, except that I think my method of doing this is
> badly wrong.
>
> Basically to make this work, when the user clicks the button, I
> redirect to a page called:
>
> EXPORT_TO_EXCEL.ASP
>
> This ASP page re-runs the query so that it can output the results to
> Excel. I have copied the contents of this page below.
>
> The problem is, its not very efficient re-running the query in
> EXPORT_TO_EXCEL.ASP to get data that has already been returned in
> RESULTS.ASP. So I'm wondering if I could somehow pass the table I've
> already created in RESULTS.ASP to instead EXPORT_TO_EXCEL.ASP instead.

I am not understanding your requirement clearly. Why don't you put what
EXPORT_TO_EXCEL.ASP is doing in results.asp?


--
Karim
http://www.cheapesthosting.com - Receive 99% spam free emails

Colin Steadman wrote:
> I'm building a standard HTML table using ASP. This page is RESULTS.ASP.
> ...when the user clicks the button, I redirect to EXPORT_TO_EXCEL.ASP
> ...This ASP page re-runs the query so that it can output the results to
> Excel. I have copied the contents of this page below.
> The problem is, its not very efficient re-running the query in
> EXPORT_TO_EXCEL.ASP to get data that has already been returned in
> RESULTS.ASP. So I'm wondering if I could somehow pass the table I've
> already created in RESULTS.ASP to instead EXPORT_TO_EXCEL.ASP instead.


How do you pass the table to Excel?

"Colin Steadman" <> wrote in message
news: om...
> I'm building a standard HTML table using ASP (I know this isn't an ASP
> group but bare with me). This page is called RESULTS.ASP. Below the
> table is a button which allows users to send the contents of the table
> to Excel for analysis, sorting, graphs ect..
>
> This all works fine, except that I think my method of doing this is
> badly wrong.
>
> Basically to make this work, when the user clicks the button, I
> redirect to a page called:
>
> EXPORT_TO_EXCEL.ASP
>
> This ASP page re-runs the query so that it can output the results to
> Excel. I have copied the contents of this page below.
>
> The problem is, its not very efficient re-running the query in
> EXPORT_TO_EXCEL.ASP to get data that has already been returned in
> RESULTS.ASP. So I'm wondering if I could somehow pass the table I've
> already created in RESULTS.ASP to instead EXPORT_TO_EXCEL.ASP instead.
>
> Does anyone know if this is possible?

The first thoughts here are:

1) How big are your queries? If you are getting 50-75 results from a table
with 10000 items, you really don't have to worry about "efficiency" here
(unless you are using an MS Access database and might get multiple hits at
the same time or are using a 486-50 with 16mb of ram for your web server)

and

2) How many people are going to be requesting the table be exported to
excel? The big thing here is: Almost any solution is going to use up more
of your system resources... if you have 1000 visitors a day and 1 in 100 ask
for this feature, then you are pretty much wasting resources trying to find
a more "efficient" solution

So what can you do?

There are a few solutions to this scenario, among them:
1) Create an excel spreadsheet with every query and then give them a link to
just "Click to download this table in Excel format". The big disadvantage
to this is: If hardly anybody uses this feature then you are wasting
resources and disk space... and you have to come up with a good cleanup
script to get rid of old files and decide how long you'll let them remain
available on the server for download

2) Store the results in a session variable and pass them that way to the
page that creates the excel sheeet... if they don't have session cookies
enabled then just run the query again. The big disadvantage to this: if its
a small query to a small table this is a waste of your time... if its a big
query to a big table this could really suck up system resources

3) Save your table results into a seperate table. You can use a "Select
Into" to save the results of the search into a seperate table and then when
you build the table on the site or for an excel sheet, you just pull all
results from the new temporary table. The big disadvantage to this: uses up
system resources and database space... you'll again need to come up with
some kind of index or something so you can delete old tables to reduce the
clutter after alot of results have been run

On Mon, 9 Feb 2004 12:12:34 -0800, "Augustus" <>
declared in alt.html:
>
> The first thoughts here are:
>
<snip good suggestions>

One other I can think of (which again, depends on the situation as to
whether it is appropriate or not), is to give them the option in the
first place whether they want it done to HTML or Excel (you can always
still give them the option to go to Excel after they get it in HTML).

--
Mark Parnell
http://www.clarkecomputers.com.au

On Mon, 09 Feb 2004 11:25:43 -0800, mscir wrote:

> Colin Steadman wrote:
>> I'm building a standard HTML table using ASP. This page is RESULTS.ASP.
>> ...when the user clicks the button, I redirect to EXPORT_TO_EXCEL.ASP
>> ...This ASP page re-runs the query so that it can output the results to
>> Excel. I have copied the contents of this page below.
>> The problem is, its not very efficient re-running the query in
>> EXPORT_TO_EXCEL.ASP to get data that has already been returned in
>> RESULTS.ASP. So I'm wondering if I could somehow pass the table I've
>> already created in RESULTS.ASP to instead EXPORT_TO_EXCEL.ASP instead.
>
>
> How do you pass the table to Excel?

The content type will open Excel.

To the OP:

You can save the results in a session variable, hidden form field or save
to a file. If the query is fast with little traffic to your query page, you
can just run it again.


--
Karim
http://www.cheapesthosting.com/webmastertoolbox - Free Resources for
Webmasters

Colin Steadman wrote:
> I'm building a standard HTML table using ASP (I know this isn't an ASP
> group but bare with me). This page is called RESULTS.ASP. Below the
> table is a button which allows users to send the contents of the table
> to Excel for analysis, sorting, graphs ect..
>
> This all works fine, except that I think my method of doing this is
> badly wrong.
>
> Basically to make this work, when the user clicks the button, I
> redirect to a page called:
>
> EXPORT_TO_EXCEL.ASP
>
> This ASP page re-runs the query so that it can output the results to
> Excel. I have copied the contents of this page below.
>
> The problem is, its not very efficient re-running the query in
> EXPORT_TO_EXCEL.ASP to get data that has already been returned in
> RESULTS.ASP. So I'm wondering if I could somehow pass the table I've
> already created in RESULTS.ASP to instead EXPORT_TO_EXCEL.ASP instead.
>
> Does anyone know if this is possible?
>
> TIA,
>
> Colin
>
>


<SNIP ASP CODE />

You could simply select and copy the table from the resulting page, then
paste it into Excel....


OR


I am not sure how this "ASP" thing works, but you could simply make it a csv
file????

maybe like this (this is a guess BTW, first time I have even *seen* asp so
this may just be *******s).....

================================================== =========================
<%

'-- =================================
'-- Use plain text because a CSV file is just plain text.
'-- What is the content type for plain text????
'-- =================================
Response.ContentType = "text/plain"

Set conn = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.Recordset")

conn.open "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=(ovms.mdb)"

rs.Open Session("SQLfromLastQuery"),conn

'-- make data table for excel.

If Not rs.EOF Then

rs.MoveFirst

'-- =================================
'-- I assume that "\n" is a <NewLine>
'-- =================================

For n = 0 to rs.Fields.Count -1
response.write rs.Fields(n).Name

if n < rs.Fields.Count
response.write ","
endif
Next

response.write "\n"

rs.MoveFirst

Do While Not rs.EOF
For n = 0 to rs.Fields.Count -1
response.write rs.Fields(n).Value
if n < rs.Fields.Count
response.write ","
endif
Next
rs.MoveNext
response.write "\n"
Loop

Else

response.write "No data were found."

End If

%>

================================================== =========================

Disco Octopus wrote:

> '-- =================================
> '-- I assume that "\n" is a <NewLine>
> '-- =================================

Try:

CRLF = Chr(13)+Chr(10)
response.write CRLF

--
Toby A Inkster BSc (Hons) ARCS
Contact Me - http://www.goddamn.co.uk/tobyink/?page=132

Quoth the raven named Disco Octopus:

> '-- =================================
> '-- I assume that "\n" is a <NewLine>
> '-- =================================

response.write "chr(13) & chr(10)"

--
-bts
-This space intentionally left blank.

> I am not understanding your requirement clearly. Why don't you put what
> EXPORT_TO_EXCEL.ASP is doing in results.asp?


That is a good point, but they only want the option of sending results
to Excel. The intranet site manages vehicles between locations. Most
users just want to see what vehicles they have matching certain
criteria in a given location.

But others users in charge of stock management need to do other
things, and want to be able to export data to Excel so they can muck
about with it.

Colin