How to Export List View Items to Excel

Before I will discuss our topic on how to export list view items to excel, I will first share a short description of what is an Excel.

Excel is a spreadsheet application included in the Microsoft Office Suite of applications. It's made up of rows and columns that can be manipulated to perform various features like calculation, graphing tools, pivot tables and a macro programming language called Visual Basic for applications. It is a useful powerful tool where most people working in offices rely on. They use this application to answer statistical, engineering and financial needs.

Now, In this tutorial I will share you how to include a command to export list view items to excel in your Visual Basic project. Adding this feature in your application can help users to easily move the data in your application to excel.

Follow these few steps on how to export list view items to excel.


Step 1. Create a new Visual Basic project and name it export_excel_app. Grab a list view and a button from your toolbox (View > Toolbox). Add at least 3 columns in your list view and change the text of your button to Export to Excel. You may do the same UI as you can see below.

Step 2. Go to your form code by double clicking your form in your Design tab and go to form_load event. 

Paste this code inside form_load event to load items in your list view.

   'load items
        Dim lst As ListViewItem
        lst = lstview.Items.Add("101")
        lst.SubItems.Add("John")
        lst.SubItems.Add("Male")
        lst = lstview.Items.Add("102")
        lst.SubItems.Add("Marry")
        lst.SubItems.Add("Female")
        lst = lstview.Items.Add("103")
        lst.SubItems.Add("Peter")
        lst.SubItems.Add("Male")
        lst = lstview.Items.Add("104")
        lst.SubItems.Add("Sarah")
        lst.SubItems.Add("Female")
        lst = lstview.Items.Add("105")
        lst.SubItems.Add("Magdalene")

        lst.SubItems.Add("Female")

Step 2. Go to your Export to Excel button class name and select Click event from the declaration.

Paste this code inside your export to excel button under click event.

   Try
            Me.Cursor = Cursors.WaitCursor
            Dim ExcelApp As Object, ExcelBook As Object
            Dim ExcelSheet As Object
            Dim i As Integer
            Dim j As Integer
            'create object of excel
            ExcelApp = CreateObject("Excel.Application")
            ExcelBook = ExcelApp.WorkBooks.Add
            ExcelSheet = ExcelBook.WorkSheets(1)
            With ExcelSheet
                For i = 1 To Me.lstview.Items.Count
                    .cells(i, 1) = Me.lstview.Items(i - 1).Text
                    For j = 1 To lstview.Columns.Count - 1
                        .cells(i, j + 1) = Me.lstview.Items(i - 1).SubItems(j).Text
                    Next
                Next
            End With
            ExcelApp.Visible = True
            ExcelSheet = Nothing
            ExcelBook = Nothing
            ExcelApp = Nothing
            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Information)
        End Try

Step 3. Now hit F5  to run and test your program. See sample exported data below.