Send table to Excel

[Colin Steadman]

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

%>

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

 

[Karim]

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?

 

[mscir]

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?

 

[Augustus]

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

 

[Mark Parnell]

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).

 

[Karim]

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.

 

[Disco Octopus]

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 bollocks).....

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

'-- =================================
'-- 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

%>

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

 

[Toby A Inkster]

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

Try:

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

 

[Beauregard T. Shagnasty]

Quoth the raven named Disco Octopus:

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

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

 

[Colin Steadman]

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

 

[Leif K-Brooks]

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

Without the quotes, unless you want the user to see "chr(13) & chr(10)".

 

[Colin Steadman]

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)

It is an Access database, and your estimates above are strangely
accurate. Except for the server spec, the server this is running on
is a beast! Access was chosen for speed of developement (our DBA's
were to busy doing real work to help setup the tables I needed).

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

Probably not that many exports now I think about it! Most users are
only interested in finding vehicles that match a customer request.
And I cant believe the management folk work that hard that they would
be endlessly exporting data to Excel.

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

Like you say, this probably would be to wasteful give the ratio of
queries to exports.

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

Is it possible to save whole tables into a session variables? This
really would be resource hungry, but its an interesting idea and I
might have a go just to see if it works.

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

Eeck! I think I'd be biting off more than I could chew if I tried
this. I've only been doing this with the help of two or three
reference guides for a year or so, on and off.

I think I might go with Mark Parnells suggestion (thread below). He
suggests offering a choice of output to HTML or Excel before the query
is submitted. After what you have said it really would be to much
work for to little gain to do anything else.

Thanks for the advice (+ other contributors).

Regards,

Colin

 

[Colin Steadman]

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).

This seems like a nice rock solid suggestion, Augustus is probably
right, doing anything else would be a waste of time.

Thanks,

Colin

 

[Greg Griffiths]

check out http://www.greggriffiths.org/webdev/both/excel/ which covers this.