Output SQL Table To Excel File?

[TrevorK]

What I would like to do, is when anyone updates a record in my table (Through the ASP page), I would like to create an Excel file with all of the table's data.

Now, I developed the query for all the data I want. But I can't seem to get it exported to Excel from SQL.

Has anyone done this before successfully?

And, in order to do the export, would I need Excel installed on the web server that is server the ASP page? The SQL server that hosts the SQL? Both?

 

[alisajid]

From my own experience, yes, you would need Excel installed (on the server) provided you wanted to create native excel documents. I have also read, and I hope I'm right on this, that the Microsoft Office "Web Components" can serve as a substitute for installing the full blown Excel App.

I can think of 2 options of the top of my head:

1. Install Excel or the Web Components, and then generate your Excel docs from your ASPs. If you use Excel you'll be creating an instance of "Excel.Application".

2. Just generate the data as an HTML page. Put it all in a table; try avoiding special formatting, keep it to plain HTML (no fonts,etc.). Save the file as a .xls file (even though it contains HTML). This will open up in Excel as an Excel doc, without any problems.

 

[bunker]

Are you running MS SQL? If so you could set up a trigger and a DTS script. Whenever the table is updated the DTS script is triggered and exports the table into an Excel spreadsheet. You shouldn't need Excel installed to do this.

 

[Zucarita9000]

I have,
Put this line at the top of the ASP page where the table is:

<%
Response.ContentType = "application/vnd.ms-excel"
%>

As soon as the page is created, it will ask you to open/save the file. This is the fastest and simplest way to create an excel file. But the users must have Excel installed. Otherwise, it wont work.

 

[TrevorK]

I need the Excel file to be created on the server - it's for an internal user to be notified everytime an external user changes it.

And I can't use DTS - because it's broken on the server and I don't have authorization to fix it, but to 'work around it'.

 

[TrevorK]

What about exporting to a CSV? would that be easier?

 

[bunker]

Originally posted by: TrevorK
What about exporting to a CSV? would that be easier?

Nothing will be easy without DTS. As a matter of fact I'm not sure there is a way to have the server create a file without it.