I'm trying to figure out the best way to output data from a query to an excel spreadsheet. I have an existing vb.net app that works closely with SQL Server 2005. I want the ability to simply push a button from my app that will export the data to excel.
I know that there are many different ways to do this, but I'm not sure the best path given my requirements:
- I actually have multiple queries and I would like to place the results of each in a differnet sheet in excel.
- I have specific headers and data tranlations/formats for my excel file - including the coloring of some of the rows and possibly conditional formating code
- I will be upgrading from SQL Server 2005 to 2008 in the next week.
I am open to any reasonable solution at this point. I have experimmented with Integration Services and seems to be a reasonable solution, but I have a few questions/isues. I don't quite understand how to transform some of the data from my database. I added a "Character Map", but that doesn't really seem to offer much. I want to do thing like contert varchar fields to text fields, money fields to $, float to % percentage, smalldatetime to short date,......... Also, when I tried to select the Table/fields that will be exported to excel, I recieved errors about data translations. the Errors complained about double preciesion. When I only selected varchar fields, everything worked.
If I do go down the integration services route, I have a few more questions. Should this integration services piece be a stand alone project or simply built into my existing VB.Net project? In either case, how can I call this "function"? Also, in order to make this generic, can I create parameters for my query or simply pass a query to use into the integration services project?
I am not sure but I'm hoping that there are new capabilities in SQL Server 2008 that will make some of this easier.
As I mentioned above, I'm open to any reasonable, extend able and maintainable solution.