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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jorge PaulinoIT 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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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?
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Jorge PaulinoIT 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 PaulinoIT 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!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.