在 VB.Net 中将数据集导出到 Excel 的快速方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21594633/
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
Fast Way To Export DataSet To Excel in VB.Net
提问by Kris Edison
I need to export a lot (nearly a million) of data everyday from SQLServer to Excel. The data is being processed through a stored procedure then I put them on the DataSet and tried to export using this code:
我每天需要将大量（近一百万）数据从 SQLServer 导出到 Excel。数据正在通过存储过程进行处理，然后我将它们放在 DataSet 上并尝试使用以下代码导出：
` Private Sub ExportToExcel(ByVal dtTemp As System.Data.DataTable, ByVal filepath As String) Dim strFileName As String = filepath
` Private Sub ExportToExcel(ByVal dtTemp As System.Data.DataTable, ByVal filepath As String) Dim strFileName As String = filepath
Dim _excel As New Excel.Application
Dim wBook As Excel.Workbook
Dim wSheet As Excel.Worksheet
wBook = _excel.Workbooks.Add()
wSheet = wBook.ActiveSheet()
Dim dt As System.Data.DataTable = dtTemp
Dim dc As System.Data.DataColumn
Dim dr As System.Data.DataRow
Dim colIndex As Integer = 0
Dim rowIndex As Integer = 0
For Each dc In dt.Columns
colIndex = colIndex + 1
wSheet.Cells(1, colIndex) = dc.ColumnName
Next
For Each dr In dt.Rows
rowIndex = rowIndex + 1
colIndex = 0
For Each dc In dt.Columns
colIndex = colIndex + 1
wSheet.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)
Next
Next
wSheet.Columns.AutoFit()
wBook.SaveAs(strFileName)
ReleaseObject(wSheet)
wBook.Close(False)
ReleaseObject(wBook)
_excel.Quit()
ReleaseObject(_excel)
GC.Collect()
End Sub`
Is there any faster way for this? How about DataSet to Clipboard then paste it to excel?
有没有更快的方法？如何将数据集粘贴到剪贴板然后将其粘贴到 excel？
回答by Bj?rn-Roger Kringsj?
回答by Jkyle Landicho
There is a much faster way involving SQL Data Pump (Import Export DTS). You can save a DTS Package (that exports data from SQL to Excel about 1000 rows per second) then run that package using SQL Server Agent. This way, you don't have to iterate to all the rows and columns one by one and you don't need to have a VB code.
有一种更快的方法涉及 SQL 数据泵（导入导出 DTS）。您可以保存 DTS 包（以每秒大约 1000 行的速度将数据从 SQL 导出到 Excel），然后使用 SQL Server 代理运行该包。这样，您就不必一一迭代所有行和列，也不需要 VB 代码。
