Export Data from VB 2005 (Visual Basic .Net 2005) to Microsoft Excel

– Create one form
– Place listView on form named lvwData
– Place two buttons btnExport and btnClose on form

On Form Load

 

Private Sub frmExportExcel_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        With Me.lvwData

            .View = View.Details

            .Columns.Add(“ID”, CInt(.Width * 1 / 6))

            .Columns.Add(“Name”, CInt(.Width * 1 / 5))

            .Columns.Add(“Gender”, CInt(.Width * 1 / 7))

            .Columns.Add(“Phone”, CInt(.Width * 1 / 5))

            .Columns.Add(“Address”, CInt(.Width * 1 / 4))

        End With

       

        For i As Integer = 1 To 10

            Me.lvwData.Items.Add(i.ToString)

            With Me.lvwData.Items(i – 1)

                .SubItems.Add(“Name” & i)

                .SubItems.Add(“M”)

                .SubItems.Add(“012”)

                .SubItems.Add(“Phnom Penh”)

            End With

        Next

End Sub

 

On btnExport Click

 

Private Sub btnExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExport.Click

        Dim ExcelApp As Object, ExcelBook As Object

        Dim ExcelSheet As Object

        Dim i As Integer

        Dim j As Integer

        Dim rowCount As Integer = 0

        ‘create object of excel

        ExcelApp = CreateObject(“Excel.Application”)

        ExcelBook = ExcelApp.WorkBooks.Add

        ExcelSheet = ExcelBook.WorkSheets(1)

       

        With ExcelSheet

            For i = 0 To Me.lvwData.Items.Count – 1

               

                rowCount = rowCount + 1

                .cells(rowCount, 1) = lvwData.Items(i).Text

                For j = 1 To lvwData.Columns.Count – 1

                    .cells(rowCount, j + 1) = lvwData.Items(i).SubItems(j).Text

                Next

            Next

        End With

        ExcelApp.Visible = True

        ‘set page margin

        ExcelSheet.PageSetup.PaperSize = 9

        ExcelSheet.PageSetup.LeftMargin = ExcelApp.InchesToPoints(0.3)

        ExcelSheet.PageSetup.RightMargin = ExcelApp.InchesToPoints(0.3)

        ExcelSheet.PageSetup.TopMargin = ExcelApp.InchesToPoints(0.3)

        ExcelSheet.PageSetup.BottomMargin = ExcelApp.InchesToPoints(0.3)

        ExcelSheet.PageSetup.CenterHorizontally = True

       

        ExcelSheet = Nothing

        ExcelBook = Nothing

        ExcelApp = Nothing

    End Sub

Download Source Code

Coded by: Iech Setha

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: