在SSIS脚本任务中格式化Excel目标列 [英] Format excel destination column in ssis script task
问题描述
是否可以在生成sis之前在ssis中的excel目标中对列进行格式化?我在想脚本任务吗?我想在Excel电子表格中将列的格式设置为日期/时间格式
Is it possible to format a column in an excel destination in ssis before generating it? I'm thinking a script task? I want to format a column to be date/time format within the excel spreadsheet
推荐答案
您可以使用Microsoft.Interop.Excel
库,并使用NumberFormat
属性将EntireColumn
格式更改为日期时间.
You can use Microsoft.Interop.Excel
library and use NumberFormat
property to change EntireColumn
format to datetime.
注意:您必须将Microsoft.Office.Interop.Excel.dll
文件添加到以下目录(.Net Framework dll目录)C:\Windows\Microsoft.NET\Framework\v2.0.50727
和(sql服务器数据工具dll目录)C:\Program Files\Microsoft SQL Server\100\DTS\Binn
(如果使用vs 2005和sql 2008 ),然后将此dll作为参考添加到您的脚本任务中
Note: you have to add Microsoft.Office.Interop.Excel.dll
file to the following directories (.Net Framework dll directory) C:\Windows\Microsoft.NET\Framework\v2.0.50727
and (sql server data tools dll directory) C:\Program Files\Microsoft SQL Server\100\DTS\Binn
(if using vs 2005 and sql 2008) and then add this dll as a reference in your script task
Imports Microsoft.Interop.Excel
Public Sub Main()
Dim m_XlApp = New Excel.Application
Dim m_xlWrkbs As Excel.Workbooks = m_XlApp.Workbooks
Dim m_xlWrkb As Excel.Workbook
m_xlWrkb = m_xlWrkbs.Open("D:\1.xlsx")
Dim m_XlWrkSheet As Excel.Worksheet = m_xlWrkb.Worksheets(1)
m_XlWrkSheet.Columns(1).NumberFormat = "HH:mm:ss"
'OR
'ExcelWorksheet.Cells(1,1).EntireColumn.NumberFormat = "HH:mm:ss"
m_xlWrkb.Save()
m_xlWrkb.Close(SaveChanges:=True)
Marshal.ReleaseComObject(m_xlWrkb)
Marshal.ReleaseComObject(m_xlWrkbs)
m_XlApp.Quit()
Marshal.ReleaseComObject(m_XlApp)
Dts.TaskResult = ScriptResults.Success
End Sub
参考
- 将Excel列(或单元格)的格式设置为用C#编写文本? 查看所有答案,而不仅仅是接受的答案
- Interop.Excel-设置日期格式
- Range.NumberFormat属性
- Format an Excel column (or cell) as Text in C#? Look at all answers, not aonly the accepted one
- Interop.Excel - Set date format
- Range.NumberFormat Property
这篇关于在SSIS脚本任务中格式化Excel目标列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!