Post reply

I want to format an Excel spreadsheet from SSIS

  • October 25, 2011 at 6:55 am

    #390687

    After exporting to an Excel spreadsheet, I want to do some formatting, like I did in DTS with an ActiveX script (number format, column width, etc...). I tried the same in SSIS with an ActiveX task and can delete a row and Bold the header row, but I'm not having much success with number formatting. So I figure I need to use a Script Task and VB.Net.

    My (first) question is, do I need to install the Office Interop dll so I can use

    Imports Microsoft.Office.Interop.Excel when Excel is installed on the server running the package? If not, what do I need to include in my script Imports?

    When exporting to Excel I currently use a formatted template copied in place before exporting. And now being able to Bold the header and delete the first row of formatted dummy data I can produce a decent spreadsheet. But I'm trying to figure out the "right" way.

    Thanks all.

  • October 25, 2011 at 7:00 am

    #1399079

    Yes, you would need to install the Excel stuff on the server in order to manipulate the Excel spreadsheet to the degree you would like.

    Personally, I don't like this approach as it clutters up your server with client software. I would rather go down the avenue of having the formatting code built into the spreadsheet, so that the user just has to click on a 'Format me' (or whatever) button after opening up the sheet in Excel for the first time.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • October 25, 2011 at 7:13 am

    #1399088

    Personally, I don't like this approach as it clutters up your server with client software.

    Yes, I thought about that. In my searching I got the impression it was client software. That's why I was wondering if it wasn't necessary if Excel were installed on the server. I'm not going to install it on my sql server. Bad precedent. Folks around here will have to size their own columns.

    Thanks.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply