vb.net 从 SQL Server 数据库动态导出到 Excel 工作表的 ssis 脚本任务
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17955793/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ssis script task to dynamically export from a SQL Server database to an Excel sheet
提问by user2615302
I am trying to create a SSIS package that will select all the value from a table in SQL Server to a table in Excel. The table is created on the run because it will change every time I run it. I can create the new table in the Excel sheet but I am having a lot of trouble getting the data in there.
我正在尝试创建一个 SSIS 包，该包将从 SQL Server 中的表中选择所有值到 Excel 中的表。该表是在运行时创建的，因为它会在我每次运行时更改。我可以在 Excel 工作表中创建新表，但在那里获取数据时遇到很多麻烦。
I cannot do an openrowsetquery because the company I work for will not allow it. It cannot be done through the data task flow because i don't know what the headers at the start.
我无法进行openrowset查询，因对我来说有效的公司不允许。它无法通过数据任务流完成，因为我不知道开始时的标题是什么。
I have tried some script tasks but cannot figure out how to get it to the Excel sheet
我尝试了一些脚本任务，但不知道如何将它放到 Excel 工作表中
Does anyone have any sample code or anything that will show me how to export from SQL Server to Excel dynamically?
有没有人有任何示例代码或任何东西可以告诉我如何动态地从 SQL Server 导出到 Excel？
Dim cn As New OleDbConnection
Dim sqlcn As New SqlConnection
Dim adapter As New OleDbDataAdapter
Dim dtset As New DataSet
Dim dt As New DataTable
Dim cmd As New OleDbCommand
Dim sqlcmd As New SqlCommand
Dim dr As DataRow
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Excel 8.0;Database=E:\sheet.xls;" + "HDR=Yes;Readonly=False;IMEX=0;"
sqlcn.ConnectionString = "Data Source=DB_NAME;Initial Catalog=Main;Integrated Security=True"
cn.Open()
sqlcn.Open()
Dim da As New SqlDataAdapter("Select * from Temp_Totals", sqlcn)
da.Fill(dt)
So far now I need to insert in to the Excel from the dtand I just am having trouble with that I think this will work I am not sure. If some one has a better idea I would love to hear it
到目前为止，我需要从 插入到 Excel 中dt，我只是遇到了问题，我认为这会起作用我不确定。如果有人有更好的主意，我很想听听
回答by Tim Taber
Here's a quick, down and dirty way to copy from a datatable to excel without having to iterate through each column/row of the datatable:
这是一种从数据表复制到 excel 的快速、向下和肮脏的方法，而无需遍历数据表的每一列/行：
Private Sub ExportToExcel(ByVal dt As DataTable, ByVal outputPath As String)
Dim xlApp As Application = CreateObject("Excel.Application")
Dim xlWorkbook As Workbook = xlApp.Workbooks.Add(Type.Missing)
Dim sheetIndex As Integer = 0
Dim col, row As Integer
Dim xlSheet As Worksheet
Dim rawData(dt.Rows.Count, dt.Columns.Count - 1) As Object
For col = 0 To dt.Columns.Count - 1
rawData(0, col) = dt.Columns(col).ColumnName
Next
For col = 0 To dt.Columns.Count - 1
For row = 0 To dt.Rows.Count - 1
rawData(row + 1, col) = dt.Rows(row).ItemArray(col).ToString
Next
Next
Dim finalColLetter As String = String.Empty
Dim colCharset As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
Dim colCharsetLen As Integer = colCharset.Length
If dt.Columns.Count > colCharsetLen Then
finalColLetter = colCharset.Substring((dt.Columns.Count - 1) \ colCharsetLen - 1, 1)
End If
finalColLetter += colCharset.Substring((dt.Columns.Count - 1) Mod colCharsetLen, 1)
xlSheet = CType(xlWorkbook.Sheets.Add(xlWorkbook.Sheets(sheetIndex), Type.Missing, 1, XlSheetType.xlWorksheet), Worksheet)
xlSheet.Name = dt.TableName
Dim xlRange As String = String.Format("A1:{0}{1}", finalColLetter, dt.Rows.Count + 1)
xlSheet.Range(xlRange, Type.Missing).Value2 = rawData
Dim firstrow As Range = CType(xlSheet.Rows(1, Type.Missing), Range)
firstrow.Font.Bold = True
firstrow.Select()
firstrow.AutoFilter(1, Type.Missing, XlAutoFilterOperator.xlAnd, Type.Missing, True)
xlSheet.Application.ActiveWindow.SplitRow = 1
xlSheet.Application.ActiveWindow.FreezePanes = True
xlSheet.Columns.EntireColumn.AutoFit()
xlSheet.Range("A1").Select()
xlSheet.PageSetup.Orientation = XlPageOrientation.xlLandscape
With xlSheet.PageSetup
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
.BottomMargin = 0.25
.TopMargin = 0.25
.LeftMargin = 0.25
.RightMargin = 0.25
.HeaderMargin = 0
.FooterMargin = 0
End With
firstrow = Nothing
xlSheet = Nothing
For Each xls As Worksheet In xlWorkbook.Worksheets
If xls.Name.Contains("Sheet") = True Then xls.Delete()
Next
xlWorkbook.SaveAs(outputPath, XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing)
xlWorkbook.Close(True, Type.Missing, Type.Missing)
xlWorkbook = Nothing
xlApp.Quit()
xlApp = Nothing
GC.Collect()
GC.WaitForPendingFinalizers()
End Sub
