Formatting Data Exported to Excel

• Phil Parkin

  SSC Guru

  Points: 247158

  More actions

  November 30, 2017 at 7:24 am

  #341043

  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.)

  ---

• Martin Schoombee

  SSCoach

  Points: 19135

  More actions

  November 30, 2017 at 8:46 am

  #1970052

  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.

• FridayNightGiant

  SSCertifiable

  Points: 7151

  More actions

  November 30, 2017 at 8:51 am

  #1970057

  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.

• Phil Parkin

  SSC Guru

  Points: 247158

  More actions

  November 30, 2017 at 9:00 am

  #1970062

  FridayNightGiant - Thursday, November 30, 2017 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.

  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!

  ---

• Martin Schoombee

  SSCoach

  Points: 19135

  More actions

  November 30, 2017 at 9:25 am

  #1970070

  Not sure it's worth the effort, though!

  Probably not