WPF应用程序更高效地将数据表写入Excel的方法是什么？

Question

在我的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"将使代码运行速度稍快，但不符合要求。我真的希望有另一种方法可以使其运行更快。

- Yona

---

在您的应用程序中，只需为用户提供复制和粘贴选项。添加一个包含复制按钮的菜单-当点击该按钮时，将选定的数据表行作为文本复制到剪贴板中，然后用户可以将其粘贴到Excel中。 - Sadique

如果您删除 applyRowBorderStyle 会怎么样？当您说“大”时，它有多大？ - Magnus

可以选择将其保存为CSV文件。这是一个简单的文件，因此无需使用Excel Interop。 - Complexity

这可能会有所帮助：http://www.codeproject.com/Articles/21519/Fast-Exporting-from-DataSet-to-Excel - Mikayil Abdullayev

请查看EPPlus库，它具有非常高的性能，在插入3万行时表现相当不错，最近的版本中，5万行也有同样的表现。 - Michael

有一个适用于Excel的ADO驱动程序。但您将无法获得格式。 - paparazzo

Answer 1

找到了答案！这是我编写的函数，以及我使用的参考资料：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;
}

小于一秒钟，这太棒了 :)

- Yona

Answer 2

I've always found the most efficient way 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.

50k records

Datatable to excel = 1 minutes 6 seconds
Datatable to RS to Excel = 2 seconds

250k records

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.

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

- Sean Wessell