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

1 回答

• 1

  您可以使用 Microsoft.Interop.Excel 库并使用 NumberFormat 属性将 EntireColumn 格式更改为datetime .

  注意：您必须将 Microsoft.Office.Interop.Excel.dll 文件添加到以下目录（.Net Framework dll目录） C:\Windows\Microsoft.NET\Framework\v2.0.50727 和（sql server data tools dll目录） C:\Program Files\Microsoft SQL Server\100\DTS\Binn （如果使用vs 2005和sql 2008），然后在脚本中添加此dll作为参考任务

  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
  

  References

  • Format an Excel column (or cell) as Text in C#?看看所有答案，不仅仅是被接受的答案

  • Interop.Excel - Set date format

  • Range.NumberFormat Property

  回复于 2024-04-28T01:19:10+08:00