Darko, nice article with lots of great detail. I thought this might give me a 4th option for my query results; Results to Text, Results to Grid, Results to File, and now... Results to Excel?!? When I got to the line "...SpreadSheetGear is commercial software that cannot be distributed without license", I popped over to
https://www.spreadsheetgear.com/downloads/purchase.aspx to learn that their licenses go for $999. I can buy an Excel license for $129.99, which got me thinking, "Why would I ever want to do this instead of just copying the query results from the SSMS grid and pasting it into a blank Excel workbook, where I can then manipulate it further?"
I was intrigued by the premise, which is why I read the article. You did a great job of documenting all of the of very detailed steps required to make this work. But I'm curious as to what you think the real-world application of this technique would be.
The solution could be incorporated in SQL Server Agent jobs.For example, when you have to generate an Excel file without starting SSMS.
Of course, there is a situation when you have to copy &paste, many times.
I met many people their job is to copy and paste query results many times daily and do some basic Excel formatting.It is easy when Excel file contains only one sheet, but when contains ten or twelve, copying and pasting does not make sense.
Of course, there is no sense to bay a license, only for consuming ‘Query To Excel’ stored procedure. If you are already licensed, for.NET developing, you might consider what I write above.
Btw, there is an option to transform a query result to HTML which is described in my article
http://www.sqlservercentral.com/articles/SQLCLR/156474/