更有效的方法将数据表写入excel？

In my WPF application, 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是DataTable，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。此工作表总是有42列，但行数根据程序接收的数据而变化，约为500行。 “applyRowBorderStyle”会使代码运行得更快，但不符合要求。我真的希望有另一种方法可以让它运行得更快..

2 个解决方案

#1

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

找到了答案！这是我写的函数，以及我使用的参考资料：http：//www.codeproject.com/Articles/21519/Fast-Exporting-fromANNSet-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 :)

需要不到一秒钟，这真棒:)

#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.

我总是发现获得数据表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

250k记录

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