More efficient way to write data table to excel?

Question

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. This is the heavy part of the function:

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, newSheet 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, taking 10 minutes or even longer. Is there any way to make it run faster?

---

Edit: The program analyses a lot of data and makes a lot of sheets, and I can't make the user do anything differently. I must use only Microsoft Excel. This sheet's table always has 42 columns, but the number of rows varies according to how much data the program receives, approximately 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.

Answer 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

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;
}

takes less than a second, which is awesome :)

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