Export ListView to Excel Spreadsheet

Reverend Jim 7 Tallied Votes 9K Views Share

Several people have asked how to export data to Excel. This code snippet shows how to export the data from a listview in details mode to a new Excel spreadsheet. Take note of the comments in the header to avoid having orphaned Excel.exe tasks eat up your system memory.

Begginnerdev commented: Nice post Jim. +9
Imports Microsoft.Office.Interop
Imports System.Windows.Forms

'                                                                                       
'  Name:                                                                                
'                                                                                       
'    ExcelExportListView                                                                
'                                                                                       
'  Description:                                                                         
'                                                                                       
'    Sample code to show how to open and manipulate an Excel spreadsheet from within a  
'    VB application.                                                                    
'                                                                                       
'  Notes:                                                                               
'                                                                                       
'    It is very important to not reference objects directly through other objects. This 
'    is often referred to as the double-dot restriction. For example, when you create   
'    a local variable to refer to the active worksheet, first create a local variable   
'    to refer to the active workbook, then generate the worksheet reference through     
'    that variable. That is do                                                          
'                                                                                       
'       Dim book As Excel.Workbook = xls.ActiveWorkbook                                 
'       Dim sheet As Excel.Worksheet = book.ActiveSheet                                 
'                                                                                       
'    rather than                                                                        
'                                                                                       
'       Dim sheet As ExcelWorksheet = xls.ActiveWorkbook.ActiveSheet                    
'                                                                                       
'    If you do the latter then you will end up with one orphan Excel.exe task for each  
'    time you run the application.                                                      
'                                                                                       
'  Audit:                                                                               
'                                                                                       
'    2013-06-20  rj  original code                                                      
'                                                                                       

Public Class Form1

    Private Sub btnPopulate_Click(sender As System.Object, e As System.EventArgs) Handles btnPopulate.Click

        'Add a few test items to the listview

        lvwToExport.Items.Add(New ListViewItem({"one", "two", "three"}))
        lvwToExport.Items.Add(New ListViewItem({"four", "five", "six"}))
        lvwToExport.Items.Add(New ListViewItem({"seven", "eight", "nine"}))
        lvwToExport.Items.Add(New ListViewItem({"ten", "eleven", "twelve"}))
        lvwToExport.Items.Add(New ListViewItem({"thirteen", "fourteen", "fifteen"}))

    End Sub

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

        'Export the listview to an Excel spreadsheet

        SaveFileDialog1.Title = "Save Excel File"
        SaveFileDialog1.Filter = "Excel files (*.xls)|*.xls|Excel Files (*.xlsx)|*.xslx"
        SaveFileDialog1.ShowDialog()

        'exit if no file selected

        If SaveFileDialog1.FileName = "" Then
            Exit Sub
        End If

        'create objects to interface to Excel

        Dim xls As New Excel.Application
        Dim book As Excel.Workbook
        Dim sheet As Excel.Worksheet

        'create a workbook and get reference to first worksheet

        xls.Workbooks.Add()
        book = xls.ActiveWorkbook
        sheet = book.ActiveSheet

        'step through rows and columns and copy data to worksheet

        Dim row As Integer = 1
        Dim col As Integer = 1

        For Each item As ListViewItem In lvwToExport.Items
            For i As Integer = 0 To item.SubItems.Count - 1
                sheet.Cells(row, col) = item.SubItems(i).Text
                col = col + 1
            Next
            row += 1
            col = 1
        Next

        'save the workbook and clean up

        book.SaveAs(SaveFileDialog1.FileName)
        xls.Workbooks.Close()
        xls.Quit()

        releaseObject(sheet)
        releaseObject(book)
        releaseObject(xls)

    End Sub

    Private Sub releaseObject(ByVal obj As Object)

        'Release an automation object

        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try

    End Sub

End Class
ReyJEnriquez 0 Junior Poster in Training

great:-)

gayzlein 0 Newbie Poster

good job, thanks

pginczek 0 Newbie Poster

Thanks for the code, but how can I add the Headers to the exported file?

Reverend Jim 4,678 Hi, I'm Jim, one of DaniWeb's moderators. Moderator Featured Poster

You should be able to figure that out from the code. You can take

sheet.Cells(row, col) = item.SubItems(i).Text

and put headers/text in any cell you want. Just change the values for row and col to select a specific cell and replace

item.SubItems(i).Text

with the string you want in the header cell.

du_1 0 Newbie Poster

hi, Jim

Thanks for the great work you've done for the forum.Exactly what I need.
If I got any other problem ,I'll get back to you.
cheers.

pginczek 0 Newbie Poster

Hello again, thanks for the help. It works fine :)
But I have another question, as u can see in the picture some numbers are not shown as they should. You have an idea why its so?
Thanks again. !

170406a55b521806294cab2e8948e592

Reverend Jim 4,678 Hi, I'm Jim, one of DaniWeb's moderators. Moderator Featured Poster

The link to the picture is broken.

pginczek 0 Newbie Poster
pginczek 0 Newbie Poster

got it
replace this

item.SubItems(i).Text

with this

CDbl(item.SubItems(i).Text)
mark jayson 0 Newbie Poster

what is the reference needed to apply the Excel.application??

ddanbe 2,724 Professional Procrastinator Featured Poster

@mark jayson
I'm assuming you are using VS2015. You won't find the reference Jim mentioned here, just add the reference MicroSoft Office 16,0 Object Library and as by wonder, Jim's reference is there again.

Mark Adrian 0 Newbie Poster

Thanks! Big help! :)

daryl_7 0 Newbie Poster

how to add title or caption in exported data in listview, example

                                                    Title
                                                    address
                                                    contact
items in listview
                                                                                                                 name and  Signature
daryl_7 0 Newbie Poster

how about another listview will exported to sheet2

Reverend Jim 4,678 Hi, I'm Jim, one of DaniWeb's moderators. Moderator Featured Poster

I no longer have MS Office installed so I can't test an example to post. Anyone else?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.