none
How to export Listview and Charts created from Listview to a MS Excel Worksheet? RRS feed

  • Question

  • Hello,

    I need to export data contained on a listview and a chart created from that data to an Excel Worksheet. I am using Visual Studio 2018 and I don't have any idea about how to open Excel and Export the data from Windows Form Listview.

    I hope you can guide me through this.

    Many thanks in advance!

    Saturday, September 29, 2018 9:42 PM

Answers

  • Hi,

    try the code:

        Private Sub DoExport(ByVal listView As ListView, ByVal strFileName As String)
            Dim rowNum As Integer = listView.Items.Count
            Dim columnNum As Integer = listView.Items(0).SubItems.Count
            Dim rowIndex As Integer = 1
            Dim columnIndex As Integer = 0
    
            If rowNum = 0 OrElse String.IsNullOrEmpty(strFileName) Then
                Return
            End If
    
            If rowNum > 0 Then
                Dim xlApp As Microsoft.Office.Interop.Excel.Application = New Microsoft.Office.Interop.Excel.ApplicationClass()
    
                If xlApp Is Nothing Then
                    MessageBox.Show("Unable to create excel object, maybe your system does not have excel installed")
                    Return
                End If
    
                xlApp.DefaultFilePath = ""
                xlApp.DisplayAlerts = True
                xlApp.SheetsInNewWorkbook = 1
                Dim xlBook As Microsoft.Office.Interop.Excel.Workbook = xlApp.Workbooks.Add(True)
    
                For Each dc As ColumnHeader In listView.Columns
                    columnIndex += 1
                    xlApp.Cells(rowIndex, columnIndex) = dc.Text
                Next
    
                For i As Integer = 0 To rowNum - 1
                    rowIndex += 1
                    columnIndex = 0
    
                    For j As Integer = 0 To columnNum - 1
                        columnIndex += 1
                        xlApp.Cells(rowIndex, columnIndex) = Convert.ToString(listView.Items(i).SubItems(j).Text) & vbTab
                    Next
                Next
    
                xlBook.SaveAs(strFileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing)
                xlApp = Nothing
                xlBook = Nothing
                MessageBox.Show("OK")
            End If
        End Sub

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Alexanderkng Sunday, October 14, 2018 2:16 AM
    Sunday, September 30, 2018 8:56 AM

All replies

  • Hi,

    try the code:

        Private Sub DoExport(ByVal listView As ListView, ByVal strFileName As String)
            Dim rowNum As Integer = listView.Items.Count
            Dim columnNum As Integer = listView.Items(0).SubItems.Count
            Dim rowIndex As Integer = 1
            Dim columnIndex As Integer = 0
    
            If rowNum = 0 OrElse String.IsNullOrEmpty(strFileName) Then
                Return
            End If
    
            If rowNum > 0 Then
                Dim xlApp As Microsoft.Office.Interop.Excel.Application = New Microsoft.Office.Interop.Excel.ApplicationClass()
    
                If xlApp Is Nothing Then
                    MessageBox.Show("Unable to create excel object, maybe your system does not have excel installed")
                    Return
                End If
    
                xlApp.DefaultFilePath = ""
                xlApp.DisplayAlerts = True
                xlApp.SheetsInNewWorkbook = 1
                Dim xlBook As Microsoft.Office.Interop.Excel.Workbook = xlApp.Workbooks.Add(True)
    
                For Each dc As ColumnHeader In listView.Columns
                    columnIndex += 1
                    xlApp.Cells(rowIndex, columnIndex) = dc.Text
                Next
    
                For i As Integer = 0 To rowNum - 1
                    rowIndex += 1
                    columnIndex = 0
    
                    For j As Integer = 0 To columnNum - 1
                        columnIndex += 1
                        xlApp.Cells(rowIndex, columnIndex) = Convert.ToString(listView.Items(i).SubItems(j).Text) & vbTab
                    Next
                Next
    
                xlBook.SaveAs(strFileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing)
                xlApp = Nothing
                xlBook = Nothing
                MessageBox.Show("OK")
            End If
        End Sub

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Alexanderkng Sunday, October 14, 2018 2:16 AM
    Sunday, September 30, 2018 8:56 AM
  • Hi Alex, I tried the code and found some issues: #1 - When i first pasted the code, it showed up as a wrong code because none of the properties, fields, etc., were defined. I defined them one by one until the code didn't show errors. #2 - After doing that, I tried to adapt the code to a button, on which I had to declared two variables: Listview, and strFileName; and also attach values to them. #3 - I ran the code and it showed an error at: Dim xlBook As Microsoft.Office.Interop.Excel.Workbook = xlApp.Workbooks.Add(True) Error says: Exception not found System.NullReferenceException: 'Object variable or With block variable not set' Do you have any idea about what I could do to solve this to effectively export results to Excel? Thanks for your time and help!
    Sunday, September 30, 2018 5:09 PM
  • Hi,

    I am sorry for the late reply.

    1.try to modify the code:

    Dim xlApp As Microsoft.Office.Interop.Excel.Application = New Microsoft.Office.Interop.Excel.Application()

    2.add dll

    all the code for my verification

    Public Class Form1
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            ListView1.View = View.Details
            ListView1.Columns.Add("", 0)
            ListView1.Columns.Add("列1", 80)
            ListView1.Columns.Add("列2", 160)
            Dim p As ListViewItem() = New ListViewItem(1) {}
            p(0) = New ListViewItem(New String() {"", "aaaa", "bbbb"})
            p(1) = New ListViewItem(New String() {"", "cccc", "dddd"})
            ListView1.Items.AddRange(p)
        End Sub
        Private Sub DoExport(ByVal listView As ListView, ByVal strFileName As String)
            Dim rowNum As Integer = listView.Items.Count
            Dim columnNum As Integer = listView.Items(0).SubItems.Count
            Dim rowIndex As Integer = 1
            Dim columnIndex As Integer = 0
    
            If rowNum = 0 OrElse String.IsNullOrEmpty(strFileName) Then
                Return
            End If
    
            If rowNum > 0 Then
                Dim xlApp As Microsoft.Office.Interop.Excel.Application = New Microsoft.Office.Interop.Excel.Application
    
                If xlApp Is Nothing Then
                    MessageBox.Show("Unable to create excel object, maybe your system does not have excel installed")
                    Return
                End If
    
                xlApp.DefaultFilePath = ""
                xlApp.DisplayAlerts = True
                xlApp.SheetsInNewWorkbook = 1
                Dim xlBook As Microsoft.Office.Interop.Excel.Workbook = xlApp.Workbooks.Add(True)
    
                For Each dc As ColumnHeader In listView.Columns
                    columnIndex += 1
                    xlApp.Cells(rowIndex, columnIndex) = dc.Text
                Next
    
                For i As Integer = 0 To rowNum - 1
                    rowIndex += 1
                    columnIndex = 0
    
                    For j As Integer = 0 To columnNum - 1
                        columnIndex += 1
                        xlApp.Cells(rowIndex, columnIndex) = Convert.ToString(listView.Items(i).SubItems(j).Text) & vbTab
                    Next
                Next
    
                xlBook.SaveAs(strFileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing)
                xlApp = Nothing
                xlBook = Nothing
                Dim procs As Process() = Process.GetProcessesByName("excel")
    
                For Each pro As Process In procs
                    pro.Kill()
                Next
                MessageBox.Show("OK")
            End If
        End Sub
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            DoExport(ListView1, "D:\test.xlsx")
        End Sub
    End Class
    

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, October 10, 2018 9:18 AM