Best way to generate Excel from SQL Server?

I have a requirement to generate a moderately complex spreadsheet containing
multiple worksheets and formulas from within SQL Server. I'm interested in
what is the recommended way of doing this. I will need to organize the data
within a stored procedure, generate the excel output and save it in a file,
which the application, which is web-based, will ship to the client's browser
to hopefully pop up into Excel. I've looked into generating HTML and XML
output, which can be loaded into Excel and functions as a spreadsheet, but i
t
would be better if I could generate an xls file in native Excel format. That
is because the end user should be able to work with these files over time,
and I would not want to have to deal with incompatibility issues.
Other options I'm considering:
1. Analysis Services - I need to research this more
2. Reporting Services - Ditto
3. Writing a procedure in C# to take the logic outside of SQLServer and
thereby having better access to Office API.
Thanks for any recommendations.Hi Steve,
Without thinking over so much this kind of task seems very affordable
by means of DTS along with some custom tasks done with VbScript (or
something like that)
Let me know your doubts or concerns with this.
Regards,
"Steve Elliott" wrote:

> I have a requirement to generate a moderately complex spreadsheet containi
ng
> multiple worksheets and formulas from within SQL Server. I'm interested in
> what is the recommended way of doing this. I will need to organize the dat
a
> within a stored procedure, generate the excel output and save it in a file
,
> which the application, which is web-based, will ship to the client's brows
er
> to hopefully pop up into Excel. I've looked into generating HTML and XML
> output, which can be loaded into Excel and functions as a spreadsheet, but
it
> would be better if I could generate an xls file in native Excel format. Th
at
> is because the end user should be able to work with these files over time,
> and I would not want to have to deal with incompatibility issues.
> Other options I'm considering:
> 1. Analysis Services - I need to research this more
> 2. Reporting Services - Ditto
> 3. Writing a procedure in C# to take the logic outside of SQLServer and
> thereby having better access to Office API.
> Thanks for any recommendations.|||I've had a project with similar requirements.
What I did was encapsulate my complex queries in stored procedures.
I called these stored procedures via classic asp and rendered the output in
excel format.
I then created an excel macro for the user to do any requisite formatting.
"Steve Elliott" wrote:

> I have a requirement to generate a moderately complex spreadsheet containi
ng
> multiple worksheets and formulas from within SQL Server. I'm interested in
> what is the recommended way of doing this. I will need to organize the dat
a
> within a stored procedure, generate the excel output and save it in a file
,
> which the application, which is web-based, will ship to the client's brows
er
> to hopefully pop up into Excel. I've looked into generating HTML and XML
> output, which can be loaded into Excel and functions as a spreadsheet, but
it
> would be better if I could generate an xls file in native Excel format. Th
at
> is because the end user should be able to work with these files over time,
> and I would not want to have to deal with incompatibility issues.
> Other options I'm considering:
> 1. Analysis Services - I need to research this more
> 2. Reporting Services - Ditto
> 3. Writing a procedure in C# to take the logic outside of SQLServer and
> thereby having better access to Office API.
> Thanks for any recommendations.|||there are lot way ways to get report into Excel.. the simple way is just
create the query to create view then open excel sheet then import the view
into excel by using Data Menu then Getexternal Data.
hope this will help u.
Regards
S Kaliyan
"Steve Elliott" <SteveElliott@.discussions.microsoft.com> wrote in message
news:C2E9EF12-A3F4-477B-A882-113412E03845@.microsoft.com...
> I have a requirement to generate a moderately complex spreadsheet
containing
> multiple worksheets and formulas from within SQL Server. I'm interested in
> what is the recommended way of doing this. I will need to organize the
data
> within a stored procedure, generate the excel output and save it in a
file,
> which the application, which is web-based, will ship to the client's
browser
> to hopefully pop up into Excel. I've looked into generating HTML and XML
> output, which can be loaded into Excel and functions as a spreadsheet, but
it
> would be better if I could generate an xls file in native Excel format.
That
> is because the end user should be able to work with these files over time,
> and I would not want to have to deal with incompatibility issues.
> Other options I'm considering:
> 1. Analysis Services - I need to research this more
> 2. Reporting Services - Ditto
> 3. Writing a procedure in C# to take the logic outside of SQLServer and
> thereby having better access to Office API.
> Thanks for any recommendations.