wpf 将数据表写入excel的更有效方法？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27504142/
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
More efficient way to write data table to excel?
提问by Yona
In my WPFapplication, I have a huge data table (System.Data.DataTable) that I need to write to a sheet in an excel document. The heavy part of the function:
在我的WPF应用程序中，我有一个巨大的数据表 ( System.Data.DataTable)，我需要将其写入 Excel 文档中的工作表。函数的重头部分：
for (; i < dt.Rows.Count; i++)
{
for (int colNum = 0; colNum < dt.Columns.Count; colNum++)
newSheet.Cells[i + rowNumber, colNum + 1] = dt.Rows[i][colNum].ToString();
applyRowBorderStyle(newSheet, i + rowNumber, dt.Columns.Count);
}
dt is the DataTable, neewSheet is the excel sheet i write to, and applyRowBorderStyle(..) adds borders to all the cells in the row. It runs very slowly when the data table is big, might take 10 minutes or even more. Is there any way to make it run faster?
dt 是数据表，neewSheet 是我写入的 Excel 表， applyRowBorderStyle(..) 为行中的所有单元格添加边框。当数据表很大时，它运行非常缓慢，可能需要 10 分钟或更长时间。有什么办法可以让它运行得更快吗？
Edit: The program analyses a lot of data and makes a lot of sheets, and I can't make the user do anything. I must use only Microsoft Excel. This sheets table always has 42 columns, but the number of lines changes according to how much data did the program receive, ~500 lines. "applyRowBorderStyle" will make the code run a bit faster, but doesn't meet the requirements.. I really hope there is another way to make it run faster..
编辑：程序分析了大量数据并制作了大量表格，我无法让用户做任何事情。我必须只使用 Microsoft Excel。这个 sheet 表总是有 42 列，但行数根据程序接收的数据量而变化，大约 500 行。“applyRowBorderStyle”会使代码运行得更快一点，但不符合要求..我真的希望有另一种方法让它运行得更快..
回答by Yona
found the answer!here's the function iv'e wrote, and the reference I used: http://www.codeproject.com/Articles/21519/Fast-Exporting-from-DataSet-to-Excel
找到了答案！这是 iv'e 编写的函数，以及我使用的参考：http: //www.codeproject.com/Articles/21519/Fast-Exporting-from-DataSet-to-Excel
private void FastDtToExcel(System.Data.DataTable dt, Worksheet sheet, int firstRow, int firstCol, int lastRow, int lastCol)
{
Range top = sheet.Cells[firstRow, firstCol];
Range bottom = sheet.Cells[lastRow, lastCol];
Range all = (Range)sheet.get_Range(top, bottom);
string[,] arrayDT = new string[dt.Rows.Count, dt.Columns.Count];
for (int i = 0; i < dt.Rows.Count; i++)
for (int j = 0; j < dt.Columns.Count; j++)
arrayDT[i, j] = dt.Rows[i][j].ToString();
all.Value2 = arrayDT;
}
takes less than a second, which is awesome :)
不到一秒钟，这太棒了:)
回答by Sean Wessell
I've always found the most efficientway to get a datatable to excel is to convert the datatable to a adodb.recordset.
The important piece is using excels CopyFromRecordSet Method
objWorksheet.Range("A1").CopyFromRecordset(ConvertToRecordset(dt))
Just ran a couple comparisons and below are the results.
我一直发现将数据表转换为 Excel的最有效方法是将数据表转换为 adodb.recordset。
重要的是使用
excels CopyFromRecordSet 方法objWorksheet.Range("A1").CopyFromRecordset(ConvertToRecordset(dt))
刚刚进行了几次比较，下面是结果。
50k records
50k 条记录
Datatable to excel = 1 minutes 6 seconds
Datatable to RS to Excel = 2 seconds
数据表到 Excel = 1 分钟 6 秒
数据表到 RS 到 Excel = 2 秒
250k records
25 万条记录
Datatable to excel = 5 minutes 29 seconds
Datatable to RS to Excel = 10 seconds
The below is obviously written in vb.net so you'll need to convert the code to C# for your application but hope it helps.
数据表到 excel = 5 分 29 秒
数据表到 RS 到 Excel = 10 秒
以下显然是用 vb.net 编写的，因此您需要将代码转换为 C# 以供您的应用程序使用，但希望它有所帮助。
Public Class Form1
Private dt As New DataTable
Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
dt.Columns.Add("header1")
dt.Columns.Add("header2")
dt.Columns.Add("header3")
dt.Columns.Add("header4")
For i = 0 To 250000
dt.Rows.Add({i, i, i, i})
Next
End Sub
Private Sub DataTableConvBtn_Click(sender As System.Object, e As System.EventArgs) Handles DataTableConvBtn.Click
Dim starttime = Now.ToString
Dim objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Dim objWorkbook = objExcel.Workbooks.Add()
Dim objWorksheet = objWorkbook.Worksheets(1)
objWorksheet.Range("A1").CopyFromRecordset(ConvertToRecordset(dt))
Dim endtime = Now.ToString
MsgBox(starttime & vbCrLf & endtime)
End Sub
Public Shared Function ConvertToRecordset(ByVal inTable As DataTable) As ADODB.Recordset
Dim result As ADODB.Recordset = New ADODB.Recordset()
result.CursorLocation = ADODB.CursorLocationEnum.adUseClient
Dim resultFields As ADODB.Fields = result.Fields
Dim inColumns As System.Data.DataColumnCollection = inTable.Columns
For Each inColumn As DataColumn In inColumns
resultFields.Append(inColumn.ColumnName, TranslateType(inColumn.DataType), inColumn.MaxLength, ADODB.FieldAttributeEnum.adFldIsNullable, Nothing)
Next
result.Open(System.Reflection.Missing.Value, System.Reflection.Missing.Value, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic)
For Each dr As DataRow In inTable.Rows
result.AddNew(System.Reflection.Missing.Value, System.Reflection.Missing.Value)
For columnIndex As Integer = 0 To inColumns.Count - 1
resultFields(columnIndex).Value = dr(columnIndex)
Next
Next
Return result
End Function
Shared Function TranslateType(ByVal columnType As Type) As ADODB.DataTypeEnum
Select Case columnType.UnderlyingSystemType.ToString()
Case "System.Boolean"
Return ADODB.DataTypeEnum.adBoolean
Case "System.Byte"
Return ADODB.DataTypeEnum.adUnsignedTinyInt
Case "System.Char"
Return ADODB.DataTypeEnum.adChar
Case "System.DateTime"
Return ADODB.DataTypeEnum.adDate
Case "System.Decimal"
Return ADODB.DataTypeEnum.adCurrency
Case "System.Double"
Return ADODB.DataTypeEnum.adDouble
Case "System.Int16"
Return ADODB.DataTypeEnum.adSmallInt
Case "System.Int32"
Return ADODB.DataTypeEnum.adInteger
Case "System.Int64"
Return ADODB.DataTypeEnum.adBigInt
Case "System.SByte"
Return ADODB.DataTypeEnum.adTinyInt
Case "System.Single"
Return ADODB.DataTypeEnum.adSingle
Case "System.UInt16"
Return ADODB.DataTypeEnum.adUnsignedSmallInt
Case "System.UInt32"
Return ADODB.DataTypeEnum.adUnsignedInt
Case "System.UInt64"
Return ADODB.DataTypeEnum.adUnsignedBigInt
End Select
Return ADODB.DataTypeEnum.adVarChar
End Function
Private Sub DtToExcelBtn_Click(sender As System.Object, e As System.EventArgs) Handles DtToExcelBtn.Click
Dim starttime = Now.ToString
Dim objExcel = CreateObject("Excel.Application")
Dim objWorkbook = objExcel.Workbooks.Add()
Dim objWorksheet = objWorkbook.Worksheets(1)
Dim i = 1
Dim rownumber = 1
objExcel.Visible = True
Do While (i < dt.Rows.Count)
Dim colNum As Integer = 0
Do While (colNum < dt.Columns.Count)
objWorksheet.Cells((i + rownumber), (colNum + 1)) = dt.Rows(i)(colNum).ToString
colNum = (colNum + 1)
Loop
i = (i + 1)
Loop
Dim endtime = Now.ToString
MsgBox(starttime & vbCrLf & endtime)
End Sub
End Class
