We help IT Professionals succeed at work.
Get Started
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
Why Experts Exchange?
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
Jim Murphy
Programmer at Smart IT Solutions
When asked, what has been your best career decision?
Deciding to stick with EE.
Mohamed Asif
Technical Department Head
Being involved with EE helped me to grow personally and professionally.
Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question
Connect with Certified Experts to gain insight and support on specific technology challenges including:
- Troubleshooting
- Research
- Professional Opinions
Did You Know?
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE