Fastest method for exporting large volume of data to Excel in VB .net

• Download source files - 1 Kb

Introduction

Using the below code you can export text data ( not formatted data ) to excel.

The Code:

Imports System.Data.SqlClient
Imports System.IO

Public Class Export
    Public Sub ExportToExcel(ByVal FileName As String, ByVal SavePath As String, ByVal objDataReader As DataTable)
        Dim i As Integer
        Dim sb As New System.Text.StringBuilder
        Try
            Dim intColumn, intColumnValue As Integer
            Dim row As DataRow
            For intColumn = 0 To objDataReader.Columns.Count - 1
                sb.Append(objDataReader.Columns(intColumn).ColumnName)
                If intColumnValue <> objDataReader.Columns.Count - 1 Then
                    sb.Append(vbTab)
                End If
            Next
            sb.Append(vbCrLf)
            For Each row In objDataReader.Rows
                For intColumnValue = 0 To objDataReader.Columns.Count - 1
                    sb.Append(StrConv(IIf(IsDBNull(row.Item(intColumnValue)), "", row.Item(intColumnValue)), VbStrConv.ProperCase))
                    If intColumnValue <> objDataReader.Columns.Count - 1 Then
                        sb.Append(vbTab)
                    End If
                Next
                sb.Append(vbCrLf)
            Next
            SaveExcel(SavePath & "\" & FileName & ".xls", sb)
        Catch ex As Exception
            Throw
        Finally
            objDataReader = Nothing
            sb = Nothing
        End Try
    End Sub

    Private Sub SaveExcel(ByVal fpath As String, ByVal sb As System.Text.StringBuilder)
        Dim fsFile As New FileStream(fpath, FileMode.Create, FileAccess.Write)
        Dim strWriter As New StreamWriter(fsFile)
        Try
            With strWriter
                .BaseStream.Seek(0, SeekOrigin.End)
                .WriteLine(sb)
                .Close()
            End With
        Catch e As Exception
            Throw
        Finally
            sb = Nothing
            strWriter = Nothing
            fsFile = Nothing
        End Try
    End Sub
End Class