Q: Data to Excel From Web

External Since: Oct 28, 2003 Posts: 2

I am working on a project and am having some trouble making a design
decision. I was hoping someone here has run into my problem before.

The application is all web based running on IIS, using ASP, ADO and MS SQL
Server as our DBMS. Users enter invoice information, and the data is stored
in the database. Later management can come along and run reports on the
data, one of which will do a comparitive analysis between the entered data
and some predetermined baseline data in the database. One of the mgmt.
requirements is to be able to export the data to excel, so it can be
manipulated.

This is where I get jammed up. I am not sure how to handle this
functionality. I am thinking, I will gather the report data, and the
comparative data, and dump it into a recordset, and then allow the user to
dump the data from the recordset using the .COPYFROMRECORDSET method on the
Excel object. I am still researching if this is possible from my web server.

Any thoughts?

BV.

External Since: Aug 13, 2003 Posts: 257

On Tue, 28 Oct 2003 21:39:49 -0500, BenignVanilla wrote:

> I am working on a project and am having some trouble making a design
> decision. I was hoping someone here has run into my problem before.
>
> The application is all web based running on IIS, using ASP, ADO and MS SQL
> Server as our DBMS. Users enter invoice information, and the data is stored
> in the database. Later management can come along and run reports on the
> data, one of which will do a comparitive analysis between the entered data
> and some predetermined baseline data in the database. One of the mgmt.
> requirements is to be able to export the data to excel, so it can be
> manipulated.
>
> This is where I get jammed up. I am not sure how to handle this
> functionality. I am thinking, I will gather the report data, and the
> comparative data, and dump it into a recordset, and then allow the user to
> dump the data from the recordset using the .COPYFROMRECORDSET method on the
> Excel object. I am still researching if this is possible from my web server.

- If you have Excel on the IIS server you can instantiate an Excel object
(com object), copy the data to a worksheet and save as xls. This is memory
intensive and not suitable if you get many requests simultaneously. Use
this method if you need formatting.

- You can you use the recordset and save as a csv file.

- You can use SQL Server's DTS functionality. An easy way is to run a SQL
statement that creates a table with the results and run a precofigured dts
package to dump the table into Excel spreadsheet. That dts can be created
using the dts wizard. Or use the dts designer and run a dts dynamically
using a parameterized query.

- Use third party spreadsheet objects that can export to xls format. They
are less intensive than Excel because they can run as a single dll.

--
Karim
Recommended host: <a style='text-decoration: underline;' href="http://www.cheapesthosting.com" target="_blank">http://www.cheapesthosting.com</a> - Affordable hosting since
1998