WPF 应用程序将数据表写入 Excel 的更有效方法
本文关键字:有效 方法 Excel 应用程序 数据表 WPF | 更新日期: 2023-09-27 18:34:19
在我的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 是DataTable
,newSheet
是我写入的 Excel 工作表,applyRowBorderStyle()
为行中的所有单元格添加边框。当数据表很大时,它运行得非常慢,需要 10 分钟甚至更长时间。有没有办法让它运行得更快?
编辑:该程序分析了大量数据并制作了大量工作表,我无法让用户做任何不同的事情。我必须只使用 Microsoft Excel。此工作表的表始终有 42 列,但行数根据程序接收的数据量而变化,大约 500 行。"applyRowBorderStyle"将使代码运行得更快一些,但不符合要求。我真的希望有另一种方法可以让它运行得更快。
找到了答案! 这是 iv'e 编写的函数,以及我使用的参考: http://www.codeproject.com/Articles/21519/Fast-Exporting-from-DataSet-to-Excel
using System.Data;
using Excel = Microsoft.Office.Interop.Excel;
private void FastDtToExcel(DataTable dt, Excel.Worksheet sheet, int firstRow, int firstCol, int lastRow, int lastCol)
{
Excel.Range top = sheet.Cells[firstRow, firstCol];
Excel.Range bottom = sheet.Cells[lastRow, lastCol];
Excel.Range all = (Range)sheet.get_Range(top, bottom);
string[,] arrayDT = new string[dt.Rows.Count, dt.Columns.Count];
//loop rows and columns
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();
//insert value in worksheet
all.Value2 = arrayDT;
}
不到一秒钟,这真是太棒了:)
我一直发现让数据表变得 excel 的最有效方法是将数据表转换为 adodb.recordset。
重要的部分是使用 excels CopyFromRecordSet Method
objWorksheet.Range("A1")。CopyFromRecordset(ConvertToRecordset(dt))
刚刚进行了几次比较,以下是结果。
50k 条记录
数据表到 excel = 1 分 6 秒
数据表到 RS 到 Excel = 2 秒
250k 条记录
数据表到 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