Click here to Skip to main content
Click here to Skip to main content

Export Data to Excel Much Faster

By , 24 Dec 2009
Rate this:
Please Sign up or sign in to vote.

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Suresh Suthar
Software Developer (Senior)
India India
Still Alive.....
Follow on   LinkedIn

Comments and Discussions

 
Questioncom factory Pinmemberad.davachi26-Feb-13 22:58 
AnswerRe: com factory PinmemberSuresh Suthar26-Feb-13 23:04 
Questionhow can I export data to excel in several sheets Pinmembermaryam_v10-Dec-10 22:43 
GeneralHelp Please.... PinmemberMember 469103216-Dec-09 9:46 
GeneralRe: Help Please.... PinmemberCikaPero28-Feb-10 21:38 
GeneralCode in C# Pinmemberleonardo_josue16-Jul-09 5:41 
GeneralRe: Code in C# Pinmembersuresh suthar30-Jul-09 3:19 
GeneralRe: Code in C# Pinmemberleonardo_josue19-Aug-09 6:26 
Generalnice article PinmemberParag Raibagkar8-Jun-09 20:40 
GeneralRe: nice article Pinmembersuresh suthar9-Jun-09 19:25 
GeneralReally nice artical PinmemberSachin Pisal20-Jan-09 4:31 
GeneralRe: Really nice artical Pinmembersuresh suthar20-Jan-09 23:44 
QuestionHow can i export 4sheets? PinmemberDilum12-Jan-09 21:20 
AnswerRe: How can i export 4sheets? Pinmembersuresh suthar16-Jan-09 3:20 
Generalhelp plz Pinmemberzakbak25-Sep-08 2:49 
AnswerRe: help plz Pinmembersuresh suthar28-Sep-08 21:12 
Generalthank youuuuuuuuuuu Pinmemberzakbak25-Sep-08 2:45 
GeneralGreat utility PinmemberMember 550108310-Sep-08 3:55 
GeneralGood Efforts Pinmembersachingkulkarni18-Jun-08 2:49 
GeneralI actually like this PinmvpSacha Barber10-Jun-08 1:21 
GeneralRe: I actually like this Pinmembersuresh suthar16-Jun-08 22:41 
GeneralTry Pasting Data in HTML text format PinmemberOktay127-May-08 0:31 
General5 from me Pinmemberdnpro26-May-08 6:23 
GeneralNice effort !!! Pinmemberdnpro26-May-08 6:22 
GeneralRe: Nice effort !!! Pinmembersuresh suthar26-May-08 23:20 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140415.2 | Last Updated 25 Dec 2009
Article Copyright 2008 by Suresh Suthar
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid