How do you Export Data from a ListViewItem to Excel using VB.NET?

How do you Export Data from a ListViewItem to Excel using VB.NET?

I need a way to export all of the Data into Excel, along with Formatting the Row Headers, etc.
If someone has the code or a great resource to walk you through this process I would be most grateful.

Thanks for your time,
Who is Participating?
Jorge PaulinoConnect With a Mentor IT Pro/DeveloperCommented:
Hi Wallace,

Try this:
  Public Sub ExportToExcel(ByVal lvName As ListView)
        Dim objExcelApplication As New Excel.Application
        Dim objExcelSheet As New Excel.Worksheet
        Dim objExcelBook As Excel.Workbook
           If objExcelApplication Is Nothing Then
                MsgBox("It was not possible to open Microsoft Excel", MsgBoxStyle.Critical, strMessageTitle)
                Exit Sub
            End If
            Dim oldCI As System.Globalization.CultureInfo = System.Threading.Thread.CurrentThread.CurrentCulture
            System.Threading.Thread.CurrentThread.CurrentCulture = New System.Globalization.CultureInfo("en-US")
            objExcelBook = objExcelApplication.Workbooks.Add
            objExcelSheet = objExcelBook.Worksheets(1)
            Dim lvRows As Int16
            Dim lvColumns As Byte
           For lvColumns = 1 To lvName.Columns.Count
                objExcelSheet.Cells(1, lvColumns) = lvName.Columns(lvColumns - 1).Text
                objExcelSheet.Cells(1, lvColumns).Font.Bold = True
              For lvRows = 1 To lvName.Items.Count
                For lvColumns = 1 To lvName.Columns.Count
                    If lvColumns = 1 Then
                      objExcelSheet.Cells(lvRows + 1, lvColumns) = lvName.Items(lvRows - 1).Text.ToString
                      objExcelSheet.Cells(lvRows + 1, lvColumns) = lvName.Items(lvRows - 1).SubItems(lvColumns - 1).Text.ToString
                    End If
            objExcelSheet.Cells.WrapText = False
            objExcelApplication.Visible = True
            System.Threading.Thread.CurrentThread.CurrentCulture = oldCI
        Catch COMEx As System.Runtime.InteropServices.COMException
            MsgBox("Microsoft Excel not ready", MsgBoxStyle.Critical, strMessageTitle)
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical, strMessageTitle)
            objExcelSheet = Nothing
            objExcelApplication = Nothing
        End Try
    End Sub

Open in new window

wally_davisAuthor Commented:
Hi James,
I have a Button control click event that I want to use.
How could I pass the lvName as DataType "ListView" using what I currently have now?
"Private Sub btnExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExport.Click"

Thank you!!
wally_davisAuthor Commented:
It looks like I would use what you have and then make a call to it right?
The new generation of project management tools

With’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Jorge PaulinoConnect With a Mentor IT Pro/DeveloperCommented:
You can use it like this:

Private Sub btnExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExport.Click
  Call ExportToExcel(ListView1)
End Sub
wally_davisAuthor Commented:
ok, I'm geting close here.

I did what you said but type this instead because it said "ListView1 was not declared" and "ListView is a type and cannot be used as an expression":

I typed:
Dim lvName as New ListView ' <-- in order to satisfy the next line.
Call ExportToExcel(lvName) ' and I also tried (Sender) but once it hits here it opens the Excel Spreadsheet but then nothing gets written to it.
Jorge PaulinoConnect With a Mentor IT Pro/DeveloperCommented:
>> How do you Export Data from a ListViewItem to Excel using VB.NET?

You have a listview with data filled, right ? If this is correct then you should replace the ListView1 with the name of your listview.

Call ExportToExcel(YourListViewName)

Or you only want to export an item ?
wally_davisAuthor Commented:
Duh, don't I feel stupid. Yes, I have an LVI with the Design name of lstQueryResults. I'm adding that in right now and will test again.
wally_davisAuthor Commented:
I'm impressed. Your a God send. I ran it and it worked Purrrfectly...!
Your code, compared to that of others (and not to take away from other good and great programmers),  is very short (the way code should be written) and works like a champ.
People like you inspire me to want to become better at programming. I mean, this is great. I can step through the code, and watch how it works, etc.

Thank you James. P.S. Any good books or websites you could recommend would be wonderful. I'm always looking for additional resources to better myself.
Jorge PaulinoIT Pro/DeveloperCommented:
Well it's not the complex code that works better than the easy one. I always try to improve my code (new function, functionalities, etc) and I like the code easy to understand and debug.

I personally don't like books! You spend a lot of time reading and you read things that you already know, so I try to go to some web sites and read then update. I like: ' I subscribe the questions that I like to know and I follow the discussion 'Technical articles, msdn magazine, etc.

and some times:

Glad i could help you and thanks for the grade and for the words!
All Courses

From novice to tech pro — start learning today.