Click here to Skip to main content
13,091,108 members (51,990 online)
Click here to Skip to main content
Add your own
alternative version


57 bookmarked
Posted 23 May 2008

Export Data to Excel Much Faster

, 24 Dec 2009
Rate this:
Please Sign up or sign in to vote.
Export data to Excel using three methods: a basic method and two advanced methods which are very fast.


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.


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

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

    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
    ''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"
    pb.Value += 1
pb.Value = 0
Ex.Visible = True
MsgBox(Ds.Tables(0).Rows.Count & " : Records Exported. ", _
Ex.Visible = True
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
'Write stream to file adding a new line to stream
str += vbNewLine
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 & " "
        str += STR1 & "| "
        pb.Value += 1
    str += vbNewLine
    pb.Value = 0
        'Close the stream writer object
        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
    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
        pb.Value = 0
        pb1.Value += 1
    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) = 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

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 = Nothing
' Collect the unreferenced objects
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)
        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


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

You may also be interested in...


Comments and Discussions

QuestionExcellent and extremely useful Pin
glilly115-Oct-15 5:55
memberglilly115-Oct-15 5:55 
SuggestionThere also another efficient way of exporting data to excel Pin
Senthil Sambandam14-Nov-14 19:56
professionalSenthil Sambandam14-Nov-14 19:56 
QuestionHelp Needed : Writing datatable to multiple sheets using object pasting method Pin
revanth.crazy4-Nov-14 1:25
memberrevanth.crazy4-Nov-14 1:25 
QuestionHelp Plsss.. Pin
Gerbie De Guzman10-Aug-14 18:27
memberGerbie De Guzman10-Aug-14 18:27 
Questioncom factory Pin
ad.davachi26-Feb-13 22:58
memberad.davachi26-Feb-13 22:58 
AnswerRe: com factory Pin
Suresh Suthar26-Feb-13 23:04
memberSuresh Suthar26-Feb-13 23:04 
Questionhow can I export data to excel in several sheets Pin
maryam_v10-Dec-10 22:43
membermaryam_v10-Dec-10 22:43 
GeneralHelp Please.... Pin
Member 469103216-Dec-09 9:46
memberMember 469103216-Dec-09 9:46 
GeneralRe: Help Please.... Pin
CikaPero28-Feb-10 21:38
memberCikaPero28-Feb-10 21:38 
GeneralCode in C# Pin
leonardo_josue16-Jul-09 5:41
memberleonardo_josue16-Jul-09 5:41 
GeneralRe: Code in C# Pin
suresh suthar30-Jul-09 3:19
membersuresh suthar30-Jul-09 3:19 
GeneralRe: Code in C# Pin
leonardo_josue19-Aug-09 6:26
memberleonardo_josue19-Aug-09 6:26 
Generalnice article Pin
Parag Raibagkar8-Jun-09 20:40
memberParag Raibagkar8-Jun-09 20:40 
GeneralRe: nice article Pin
suresh suthar9-Jun-09 19:25
membersuresh suthar9-Jun-09 19:25 
GeneralReally nice artical Pin
Sachin Pisal20-Jan-09 4:31
memberSachin Pisal20-Jan-09 4:31 
GeneralRe: Really nice artical Pin
suresh suthar20-Jan-09 23:44
membersuresh suthar20-Jan-09 23:44 
QuestionHow can i export 4sheets? Pin
Dilum12-Jan-09 21:20
memberDilum12-Jan-09 21:20 
AnswerRe: How can i export 4sheets? Pin
suresh suthar16-Jan-09 3:20
membersuresh suthar16-Jan-09 3:20 
Generalhelp plz Pin
zakbak25-Sep-08 2:49
memberzakbak25-Sep-08 2:49 
AnswerRe: help plz Pin
suresh suthar28-Sep-08 21:12
membersuresh suthar28-Sep-08 21:12 
Generalthank youuuuuuuuuuu Pin
zakbak25-Sep-08 2:45
memberzakbak25-Sep-08 2:45 
GeneralGreat utility Pin
Member 550108310-Sep-08 3:55
memberMember 550108310-Sep-08 3:55 
GeneralGood Efforts Pin
sachingkulkarni18-Jun-08 2:49
membersachingkulkarni18-Jun-08 2:49 
GeneralI actually like this Pin
Sacha Barber10-Jun-08 1:21
mvpSacha Barber10-Jun-08 1:21 
GeneralRe: I actually like this Pin
suresh suthar16-Jun-08 22:41
membersuresh suthar16-Jun-08 22:41 

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

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

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