Export Data to Excel Much Faster

• Download source code - 1.2 MB

Introduction

This article will help you to export data into an Excel workbook using three different ways:

1. Basic method: Copying data cell by cell.
2. Using the FileStreamWriter object: Write a stream to file.
3. Object pasting method: Copy data to an array and paste directly to an Excel workbook.

Background

In the basic method, copying the data takes a lot of time. Using the FileSteamWriter or the object pasting method, copying data is much faster as compared to the basic method.

Using the code

Code for the basic method

You have to add a reference to the Excel COM object to your application. We will have to declare two objects: Ex as Excel Application, Ws as Excel Worksheet, and set Ws as the first worksheet of the workbook.

For headings, we will have to code a loop for each column of the table to show the headings. To retrieve a column's heading, we used the Columns(Index).Caption property or the ColumnName property.

Now, for all data, we will have to code two loops: one for the row and another for the column (as shown in the code).

''Creating Excel Workbook using EarlyBinding
''(Add reference of Excel COM Object to your project
Dim Ex As New Excel.Application
Dim Ws As Excel.Worksheet
Ws = Ex.Workbooks.Add().Worksheets(1)

''>>Creating Excel Workbook using CreateObject function (LateBinding)
''Dim Ex As Object
''Dim Wb As Object
''Dim Ws As Object
''Ex = CreateObject("Excel.Application")
''Wb = Ex.Workbooks.Add
''Ws = Ex.Worksheets(1)
''Ws.Name = "All Data"

Dim Row, Col, i, j As Integer
pb.Maximum = Ds.Tables(0).Rows.Count
Row = 1
Col = 1

''For Heading
lblCount.Text = "Generating Heading." : Me.Refresh()
For i = 0 To Ds.Tables(0).Columns.Count - 1
    Ws.Cells(Row, Col) = Ds.Tables(0).Columns(i).Caption
    Ws.Cells(Row, Col).Font.Bold = True : _
      Ws.Cells(Row, Col).Font.ColorIndex = 3 : Col += 1
Next

Row = 2
Col = 1
pb1.Maximum = Ds.Tables(0).Columns.Count
lblCount.Text = "Preparing for Export Data."
For i = 0 To Ds.Tables(0).Rows.Count - 1
    '
    'FOR ALL DATA
    '

    pb1.Value = 0
    For j = 0 To Ds.Tables(0).Columns.Count - 1
        Ws.Cells(Row, Col) = Ds.Tables(0).Rows(i)(j).ToString : Col += 1
        pb1.Value += 1
    Next
    ''If data is more than 65500 then set ws to next sheet
    If Row = 65500 Then
        Row = 1
        Ws = Ex.Worksheets(2)
    End If

    Col = 1
    Row += 1
    lblCount.Text = i + 1 & " : Exported"
    lblCount.Refresh()
    pb.Value += 1
Next
pb.Value = 0
Ex.Visible = True
MsgBox(Ds.Tables(0).Rows.Count & " : Records Exported. ", _
       MsgBoxStyle.Information)
Ex.Visible = True
Ex.Quit()
Ex = Nothing
Ws = Nothing

Using the StreamWriter method

This method is a shorter and much faster way of exporting data into any type of file.

In this method, we will use the System.IO namespace and we will directly create a file path specified by the programmer, with your choice of extension like .xls or .doc, etc.

The coding starts with the file path where the new file to be created needs to be stored. Now, declare an object of IO.StreamWriter with the specified file path. In this method, each value of data row/column is added to a string, including the pipe sign "|" as a separator.

Now, the file which is created contains data in a single column with the pipe separator (CSV format).

'File name and path
Dim filePath As String = "c:\SystemIO_Exported_Data_AsOn_" & _
                         Now.ToShortDateString & ".xls"

'Stream Writer object to write the stream to file
Dim writer As New IO.StreamWriter(IO.File.Create(filePath))
Dim dRow As DataRow
Dim str As String = String.Empty

''For Heading
lblCount.Text = "Generating Heading." : Me.Refresh()
For i As Integer = 0 To Ds.Tables(0).Columns.Count - 1
    str += Ds.Tables(0).Columns.Item(i).Caption & vbTab
Next
'Write stream to file adding a new line to stream
str += vbNewLine
writer.Write(str)
writer.Flush()
pb.Maximum = Ds.Tables(0).Rows.Count + 1
For Each dRow In Ds.Tables(0).Rows

    str = ""
    For col As Integer = 0 To Ds.Tables(0).Columns.Count - 1
        Dim STR1 As String = ""
        Dim c As Char = Chr(32)
        Dim sep() As Char = " "
        Dim str2() As String
        str2 = dRow(col).ToString.Split(sep)
        For z As Integer = 0 To str2.Length - 1
            'replacing all spaces and tabs with '|' (pipe sign)
            Dim y As String = str2(z).ToString.Replace(Chr(32), _
                     " ").Replace(Chr(13), " ").Replace(Chr(10), _
                     " ").Replace(Chr(9), " ").Replace("|", " ")
            STR1 += y & " "
        Next
        str += STR1 & "| "
        pb.Value += 1
    Next
    str += vbNewLine
    writer.Write(str)
    writer.Flush()
    pb.Value = 0
Next
        'Close the stream writer object
        writer.Close()
        pb.Value = 0
        MsgBox("Data Exported Successfully.")

Object pasting method

This is another faster method to export data to Excel. In this code, we create a two dimensional array like Dim rawData(dt.Rows.Count, dt.Columns.Count - 1) As Object to contain the data of the data table.

Once the data is stored in an array, it will paste the data accordingly in an Excel worksheet using the Range().Value method of the Excel worksheet, as shown here:

If Ds.Tables.Count > 3 Then
    MsgBox("There Are More than 3 data table. " & _ 
           "Data can not be exported.", MsgBoxStyle.Information)
    Exit Sub
End If
Dim sheetIndex As Integer
Dim Ex As Object
Dim Wb As Object
Dim Ws As Object
Ex = CreateObject("Excel.Application")
Wb = Ex.workbooks.add

' Copy each DataTable as a new Sheet
For Each dt As System.Data.DataTable In Ds.Tables
    'On Error Resume Next
    Dim col, row As Integer
    ' Copy the DataTable to an object array
    Dim rawData(dt.Rows.Count, dt.Columns.Count - 1) As Object
    lblCount.Text = "Copying Columns Name." : Me.Refresh()
    ' Copy the column names to the first row of the object array
    pb1.Maximum = dt.Columns.Count + 1
    pb1.Value = 0
    For col = 0 To dt.Columns.Count - 1
        rawData(0, col) = dt.Columns(col).ColumnName.ToUpper
        pb1.Value += 1
    Next
    lblCount.Text = "Copying Data" : Me.Refresh()
    pb1.Value = 0
    ' Copy the values to the object array
    pb.Maximum = dt.Rows.Count + 1
    pb.Value = 0
    For col = 0 To dt.Columns.Count - 1
        For row = 0 To dt.Rows.Count - 1
            rawData(row + 1, col) = dt.Rows(row).ItemArray(col)
            pb.Value += 1
        Next
        pb.Value = 0
        pb1.Value += 1
    Next
    pb.Value = 0
    pb1.Value = 0
    lblCount.Text = "" : Me.Refresh()
    ' Calculate the final column letter
    Dim finalColLetter As String = String.Empty
    finalColLetter = ExcelColName(dt.Columns.Count)
    'Generate Excel Column Name (Column ID)


    sheetIndex += 1
    Ws = Wb.Worksheets(sheetIndex)
    Ws.name = dt.TableName
    Dim excelRange As String = String.Format("A1:{0}{1}", _
                               finalColLetter, dt.Rows.Count + 1)

    Ws.Range(excelRange, Type.Missing).Value2 = rawData
    Ws = Nothing
Next

Wb.SaveAs("C:\ExportedDataUsingObjectPastingMethod.xls", _
          Type.Missing, Type.Missing, Type.Missing, Type.Missing, _
          Type.Missing, Type.Missing, Type.Missing, Type.Missing, _
          Type.Missing, Type.Missing, Type.Missing)
Wb.Close(True, Type.Missing, Type.Missing)
Wb = Nothing
' Release the Application object
Ex.Quit()
Ex = Nothing
' Collect the unreferenced objects
GC.Collect()
MsgBox("Exported Successfully.", MsgBoxStyle.Information)

I also wrote a function to find the column name from an Excel worksheet:

Public Function ExcelColName(ByVal Col As Integer) As String
    If Col < 0 And Col > 256 Then
        MsgBox("Invalid Argument", MsgBoxStyle.Critical)
        Return Nothing
        Exit Function
    End If
    Dim i As Int16
    Dim r As Int16
    Dim S As String
    If Col <= 26 Then
        S = Chr(Col + 64)
    Else
        r = Col Mod 26
        i = System.Math.Floor(Col / 26)
        If r = 0 Then
            r = 26
            i = i - 1
        End If
        S = Chr(i + 64) & Chr(r + 64)
    End If
    ExcelColName = S
End Function