Click here to Skip to main content
11,430,402 members (77,323 online)
Click here to Skip to main content

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

, 14 Jan 2007
Rate this:
Please Sign up or sign in to vote.
This is the fastest method for exporting normal text data to excel. You even don't need excel installed in your machine.

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

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

No Biography provided

Comments and Discussions

 
GeneralNot very fast... Pin
John Crocker15-Jan-07 0:35
memberJohn Crocker15-Jan-07 0:35 
GeneralRe: Not very fast... Pin
Subramanian V. B.15-Jan-07 0:57
memberSubramanian V. B.15-Jan-07 0:57 
GeneralRe: Not very fast... Pin
gg423721-Aug-09 7:03
membergg423721-Aug-09 7:03 

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 | Terms of Use | Mobile
Web03 | 2.8.150428.2 | Last Updated 15 Jan 2007
Article Copyright 2007 by Subramanian V. B.
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid