SSIS 2008, Excel 2007 formatting excel columns properly (Exporting, not importing)

Question

Hi all, I'm running into some formatting issues with exporting data to an excel 2007 spreadsheet.

I have a template file that has the columns formatted correctly, but when the data is exported it doesn't get the correct formats (currency, short date, etc.)

I'm thinking I have 2 options

Have excel autorun a macro. (hate that idea)

Format the data before it gets to excel and make it string data only. (I don't like that idea either as you are going to get those irritating data conversion arrows next to each cell)

I've used the IMEX=1 trick to get around the data import issues before, but is there a special trick for exporting?

thanks,

Answer 1

I spent ages trying to figurure this out.

What you need to do is have a hidden row of data in the spreadsheet you are exporting to.

SSIS will place the values into the spreadsheet, and assume the type is that of the row immediately above. So the data row needs to be the last row in the spreadsheet, and the data in each column needs to be of the type you want it to be.

You would think there was a better way of doing this. There isn't. Sorry :-)

Answer 2

So after much formatting and gnashing of teeth, I came down to the final solution. It appears that you still need the hidden row at the top for excel to use. That determines whether the data is formatted as numeric or string. If you want to do any more formatting, then you need to add in a script task to your SSIS package. I just threw the following lines into a .net script task at the end of my processing and it fixed it right up.

Please don't puke on the DailyWTF quality code ;-)

        Dim wb As Microsoft.Office.Interop.Excel.Workbook

    excel = New Microsoft.Office.Interop.Excel.Application

    wb = excel.Workbooks.Open("c:\reports\Report.xlsx")
    'This is the long ass Accounting Format'
    wb.Worksheets("Sheet1").Columns("E:E").NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
    wb.Save()
    wb.Close()
    excel.Quit()