jrattinger
asked on
VB.NET/SQL Server Output to Excel - (via integration services?)
Hi All,
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.
Thanks,
John
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.
Thanks,
John
Sara bhai
Create your query result and format in XML string and open in Excel.
ASKER
Can you please elaborate a bit on your answer and how it would fit into my requirements?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I was looking for more detail in the answer