Click here to Skip to main content
15,868,099 members
Articles / Desktop Programming / Windows Forms

Export Data to Excel Much Faster

Rate me:
Please Sign up or sign in to vote.
2.84/5 (31 votes)
24 Dec 2009CPOL2 min read 141.1K   3.3K   58   42
Export data to Excel using three methods: a basic method and two advanced methods which are very fast.

Image 1

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

VB
''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).

VB
'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:

VB
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:

VB
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)


Written By
Software Developer (Senior)
India India
Still Alive.....

Comments and Discussions

 
Suggestion2D array method: use range resize instead of finding the column name Pin
jkluge1-Mar-21 2:59
jkluge1-Mar-21 2:59 
QuestionExcellent and extremely useful Pin
glilly115-Oct-15 5:55
glilly115-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
revanth.crazy4-Nov-14 1:25 
QuestionHelp Plsss.. Pin
Gerbie De Guzman10-Aug-14 18:27
Gerbie De Guzman10-Aug-14 18:27 
Questioncom factory Pin
ad.davachi26-Feb-13 22:58
ad.davachi26-Feb-13 22:58 
AnswerRe: com factory Pin
Suresh Suthar26-Feb-13 23:04
professionalSuresh Suthar26-Feb-13 23:04 
Questionhow can I export data to excel in several sheets Pin
maryam_v10-Dec-10 22:43
maryam_v10-Dec-10 22:43 
GeneralHelp Please.... Pin
Member 469103216-Dec-09 9:46
Member 469103216-Dec-09 9:46 
GeneralRe: Help Please.... Pin
CikaPero28-Feb-10 21:38
CikaPero28-Feb-10 21:38 
GeneralCode in C# Pin
leonardo_josue16-Jul-09 5:41
leonardo_josue16-Jul-09 5:41 
GeneralRe: Code in C# Pin
Suresh Suthar30-Jul-09 3:19
professionalSuresh Suthar30-Jul-09 3:19 
GeneralRe: Code in C# Pin
leonardo_josue19-Aug-09 6:26
leonardo_josue19-Aug-09 6:26 
Generalnice article Pin
Parag Raibagkar8-Jun-09 20:40
Parag Raibagkar8-Jun-09 20:40 
GeneralRe: nice article Pin
Suresh Suthar9-Jun-09 19:25
professionalSuresh Suthar9-Jun-09 19:25 
GeneralReally nice artical Pin
sacp.net20-Jan-09 4:31
sacp.net20-Jan-09 4:31 
GeneralRe: Really nice artical Pin
Suresh Suthar20-Jan-09 23:44
professionalSuresh Suthar20-Jan-09 23:44 
QuestionHow can i export 4sheets? Pin
Dilum12-Jan-09 21:20
Dilum12-Jan-09 21:20 
How can i export 4sheets?
AnswerRe: How can i export 4sheets? Pin
Suresh Suthar16-Jan-09 3:20
professionalSuresh Suthar16-Jan-09 3:20 
Generalhelp plz Pin
zakbak25-Sep-08 2:49
zakbak25-Sep-08 2:49 
AnswerRe: help plz Pin
Suresh Suthar28-Sep-08 21:12
professionalSuresh Suthar28-Sep-08 21:12 
Generalthank youuuuuuuuuuu Pin
zakbak25-Sep-08 2:45
zakbak25-Sep-08 2:45 
GeneralGreat utility Pin
Member 550108310-Sep-08 3:55
Member 550108310-Sep-08 3:55 
GeneralGood Efforts Pin
sachingkulkarni18-Jun-08 2:49
sachingkulkarni18-Jun-08 2:49 
GeneralI actually like this Pin
Sacha Barber10-Jun-08 1:21
Sacha Barber10-Jun-08 1:21 

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.