I am looking to have the result of a SQL query exported to Excel, formatted into a template.
Basically the user clicks on the datarow, the query executes for that particular parameter and the the user is given the option to save to Excel file.
Cheers
Bill
I would suggest that you create .csv file's in C# at runtime from the results returned by the query instead of an .xls
The .csv file can be opened in Excel so you can still use excel as the application at the end
Once the csv file is create you can open in it excel like this System.Diagnostics.Process.Start("yourExcelFile.xls");
Also please see this link hence i did not recommend you using Automation of Office
Paragraph taken from http://support.microsoft.com/kb/257757 Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when Office is run in this environment.
To do this kinda thing I'd recommend writing some VBA code behind your Excel sheet (Alt F11 will take you to the coding window)
Then write some code to call the stored procedure (The VBA sample in the below link)
http://support.microsoft.com/kb/185125
Once you have the data, it shouldn't be difficult to store it straight to a file rather than filling the sheet with it.
I would use the normal solutions to get the data out of sql server and into excel (as rows & columns), but put the returned data in a different worksheet than your template.
http://blogs.office.com/b/microsoft-excel/archive/2010/06/07/running-a-sql-stored-procedure-from-excel-no-vba.aspx
http://codebyjoshua.blogspot.com.au/2012/01/get-data-from-sql-server-stored.html
Once your data is in excel, in the template worksheet, just use links to the data sheet for each data item ('=Sheet2!A6').
hth
mcalex
