Export Data to Excel Much Faster






2.84/5 (31 votes)
Export data to Excel using three methods: a basic method and two advanced methods which are very fast.
Introduction
This article will help you to export data into an Excel workbook using three different ways:
- Basic method: Copying data cell by cell.
- Using the
FileStreamWriter
object: Write a stream to file. - 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