SSIS 2008，Excel 2007格式excel列正确（导出，不导入） [英] SSIS 2008, Excel 2007 formatting excel columns properly (Exporting, not importing)

问题描述

我有一个格式化问题导出数据到excel 2007电子表格。我有一个模板文件，列的格式正确，但是当数据导出，它没有得到正确的格式（货币，短日期等）。

我想我有2个选项

具有excel自动运行宏。 （讨厌这个想法）

在数据获得excel之前格式化数据，并使其成为字符串数据。 （我不喜欢这个想法，因为你会得到每个单元格旁边令人烦恼的数据转换箭头）

我已经使用了IMEX = 1的技巧以前有数据导入问题，但是是否有一个特殊的技巧来导出？

谢谢，

解决方案

所以经过很多格式化和咬牙切齿，我来到了最后的解决方案。看来，您仍然需要顶部的隐藏行才能使用。这将决定数据是否被格式化为数字或字符串。如果你想做更多的格式化，那么你需要添加一个脚本任务到您的SSIS包。我在处理结束时只将以下行放入.net脚本任务，并将其修正。

请不要在DailyWTF质量代码中; - ）

  Dim wb As Microsoft.Office.Interop.Excel.Workbook 
 
 excel = New Microsoft.Office.Interop.Excel.Application 
 
 wb = excel.Workbooks.Open（c：\reports\Report.xlsx）
'这是长屁股会计格式'
 wb.Worksheets（Sheet1）。列（E：E）。NumberFormat =_（$ *＃，## 0.00 _）; _（$ *（＃，## 0.00） _（$ * - ?? _）; _（@_）
 wb.Save（）
 wb.Close（）
 excel.Quit（）
  

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,

解决方案

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

这篇关于SSIS 2008，Excel 2007格式excel列正确（导出，不导入）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！