在 ssis 脚本任务中格式化 excel 目标列

2021-12-30 00:00:00 excel etl sql-server ssis script-task

是否可以在生成之前在 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:WindowsMicrosoft.NETFrameworkv2.0.50727 和(sql server data tools dll 目录)C:Program FilesMicrosoft SQL Server100DTSBinn(如果使用 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:WindowsMicrosoft.NETFrameworkv2.0.50727 and (sql server data tools dll directory) C:Program FilesMicrosoft SQL Server100DTSBinn (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 属性

相关文章