在Visual Studio上使用SSIS ETL自动调整列宽 [英] Autofit Column Width using SSIS ETL on Visual Studio
问题描述
我正在Visual Studio上创建ETL,但是当我将数据导出到Excel上的表时,列似乎有点短。有没有办法从Visual Studio自动调整列宽?谢谢
I´m creating an ETL on visual studio, but when I export my data to a table on Excel, the columns seem a bit short. Is there a way to autofit the column width from visual studio? Thank you
更新1
这是我复制它的代码,还有我得到的错误
Here is the code as I copied it and also the error I get
推荐答案
您可以使用脚本任务
在 DataFlow Task
成功后执行。您必须将 Microsoft.Office.Interop.Excel
程序集添加到脚本任务引用中,并使用以下代码: (使用的Vb.Net)
You can achieve this using a script task
that execute after the DataFlow Task
success. You have to add Microsoft.Office.Interop.Excel
assembly to the script task references and use the following code: (used Vb.Net)
注意:您必须将Microsoft.Office.Interop.Excel.dll文件添加到以下目录(.Net Framework dll目录)中: \Windows\Microsoft.NET\Framework\v2.0.50727和(SQL Server数据工具dll目录)C:\Program Files\Microsoft SQL Server\100\DTS\Binn(使用vs 2005和sql 2008),然后将此dll作为引用添加到脚本任务中
Imports Microsoft.Office.Interop
Public Sub Main()
Dim strFile as String = "C:\New Folder\1.xls"
Dim m_XlApp As New Excel.Application
Dim m_xlWrkbs As Excel.Workbooks = m_XlApp.Workbooks
Dim m_xlWrkb As Excel.Workbook
m_xlWrkb = m_xlWrkbs.Open(strFile)
'Loop over all worksheets
For Each m_XlWrkSheet As Excel.Worksheet In m_xlWrkb.Worksheets
'Columns
m_XlWrkSheet.UsedRange.Columns.AutoFit()
'Rows
m_XlWrkSheet.UsedRange.Rows.AutoFit()
Next
m_xlWrkb.Save()
m_xlWrkb.Close(SaveChanges:=True)
m_XlApp.Quit()
End Sub
这篇关于在Visual Studio上使用SSIS ETL自动调整列宽的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!