November 30, 2017 at 7:24 am
This is just a general question at this point.
I know that there are lots of us who use SSIS to output data to Excel, often for data-transfer purposes, where formatting is not a big issue.
But, sometimes, you want to make the Excel spreadsheet a bit prettier than the default. Maybe you want to right-size column widths, add some numeric formatting to certain columns, ...
If you are doing this, can you please respond with a brief summary of the way in which you are doing it.
(Note that I posted in the SSIS forum for a reason – I know that SSRS can do some of this, but I would like to know of SSIS solutions.)
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 30, 2017 at 8:46 am
I usually create a template that contains all the necessary formatting. Although I believe it would be possible to do some formatting with a script task, it would be extremely painful and not worth the effort...unless you need dynamic formatting.
November 30, 2017 at 8:51 am
For me, I generate a template excel file which contains all the formatting, layout etc. As part of the process I take a copy of this template and then export the data to it. It isn't perfect but it does work.
I have in the past tried using script tasks to do the formatting, or calling a macro to do the formatting but I didn't want to introduce any dependencies on the Excel COM object on the servers. so this is why I went down the template route.
November 30, 2017 at 9:00 am
FridayNightGiant - Thursday, November 30, 2017 8:51 AMFor me, I generate a template excel file which contains all the formatting, layout etc. As part of the process I take a copy of this template and then export the data to it. It isn't perfect but it does work.
I have in the past tried using script tasks to do the formatting, or calling a macro to do the formatting but I didn't want to introduce any dependencies on the Excel COM object on the servers. so this is why I went down the template route.
Thanks for the responses.
I agree about not wanting Excel interops on servers.
The template idea is one I am familiar with.
One idea, which I have not yet tried out, is including a VBA macro in the Excel template itself, possibly linked to a 'Format Me' button. Then the user opens the spreadsheet, sees the mess & clicks the button which fixes everything up. Obviously, this requires an XLSM file, but might be workable and would allow for more precise formatting than is possible using a template. Not sure it's worth the effort, though!
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 30, 2017 at 9:25 am
Not sure it's worth the effort, though!
Probably not 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply