ListView控件数据导出Excel及导入回控件的实现求助

我已经在ListView控件里拿到了需要的数据，现在想把这些数据导出到文本文件或者Excel，之后还要能把它们重新加载回ListView。我知道怎么用ListBox做这俩操作，但对ListView不太熟。查资料调代码折腾好几个小时了，还是一堆错误。下面是我最新的导出Excel的代码，麻烦帮我写对应的导入代码！

我的导出代码： #

'将ListView数据导出至Excel表格
Dim SaveFileDialog1 As New SaveFileDialog
SaveFileDialog1.Title = "保存Excel文件"
SaveFileDialog1.Filter = "Excel文件 (*.xls)|*.xls|Excel文件 (*.xlsx)|*.xslx"
SaveFileDialog1.ShowDialog()
'若未选择文件则退出
If SaveFileDialog1.FileName = "" Then Exit Sub
End If ' 这里多了一个多余的End If，会报错！
'创建Excel交互对象
Dim xls As New Excel.Application
Dim book As Excel.Workbook
Dim sheet As Excel.Worksheet
Dim placeholder As String
'创建工作簿并获取首个工作表的引用
xls.Workbooks.Add()
book = xls.ActiveWorkbook
sheet = book.ActiveSheet
'遍历行和列，将数据复制至工作表
Dim row As Integer = 1
Dim col As Integer = 1
For Each item As ListViewItem In lstViewUsers.Items
    For i As Integer = 0 To item.SubItems.Count - 1
        placeholder = sheet.Cells(row, col) = item.SubItems(i).Text ' 这里赋值逻辑错误！
        col = col + 1
    Next
    row += 1
    col = 1
Next
'保存工作簿并清理资源
book.SaveAs(SaveFileDialog1.FileName)
xls.Workbooks.Close()
xls.Quit()
End Sub

先修正你的导出代码（避免报错） #

首先帮你把导出代码里的两处错误改掉，这样导出功能能正常运行：

'修正后的ListView导出Excel代码
Private Sub ExportToExcel()
    Dim SaveFileDialog1 As New SaveFileDialog
    SaveFileDialog1.Title = "保存Excel文件"
    '注意你之前把xlsx写成了xslx，这里也修正了
    SaveFileDialog1.Filter = "Excel 97-2003文件 (*.xls)|*.xls|Excel 2007+文件 (*.xlsx)|*.xlsx"
    If SaveFileDialog1.ShowDialog() <> DialogResult.OK Then Exit Sub ' 更严谨的判断方式

    '创建Excel交互对象
    Dim xls As New Excel.Application
    Dim book As Excel.Workbook = xls.Workbooks.Add()
    Dim sheet As Excel.Worksheet = book.ActiveSheet

    '遍历行和列，将数据复制至工作表
    Dim row As Integer = 1
    For Each item As ListViewItem In lstViewUsers.Items
        Dim col As Integer = 1
        For Each subItem As ListViewItem.ListViewSubItem In item.SubItems
            sheet.Cells(row, col).Value = subItem.Text
            col += 1
        Next
        row += 1
    Next

    '保存工作簿并清理资源
    book.SaveAs(SaveFileDialog1.FileName)
    book.Close()
    xls.Quit()
    '释放COM对象，避免Excel进程残留
    System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet)
    System.Runtime.InteropServices.Marshal.ReleaseComObject(book)
    System.Runtime.InteropServices.Marshal.ReleaseComObject(xls)
End Sub

对应的Excel导入回ListView的代码 #

下面是匹配的导入代码，逻辑是：选择Excel文件→打开工作表→遍历每一行数据→创建ListViewItem并添加到控件中：

Private Sub ImportFromExcelToListView()
    Dim OpenFileDialog1 As New OpenFileDialog
    OpenFileDialog1.Title = "选择Excel文件"
    OpenFileDialog1.Filter = "Excel 文件 (*.xls;*.xlsx)|*.xls;*.xlsx"
    If OpenFileDialog1.ShowDialog() <> DialogResult.OK Then Exit Sub

    '创建Excel交互对象，隐藏Excel界面避免干扰
    Dim xls As New Excel.Application With {.Visible = False}
    Dim book As Excel.Workbook = xls.Workbooks.Open(OpenFileDialog1.FileName)
    Dim sheet As Excel.Worksheet = book.ActiveSheet

    '先清空ListView现有数据（如果需要保留可删除此行）
    lstViewUsers.Items.Clear()

    '获取Excel里的有效行数和列数，避免遍历空行空列
    Dim lastRow As Integer = sheet.Cells(sheet.Rows.Count, 1).End(Excel.XlDirection.xlUp).Row
    Dim lastCol As Integer = sheet.Cells(1, sheet.Columns.Count).End(Excel.XlDirection.xlToLeft).Column

    '遍历每一行数据
    For row As Integer = 1 To lastRow
        '创建ListViewItem，第一列作为Item的主文本
        Dim listItem As New ListViewItem(sheet.Cells(row, 1).Value?.ToString())
        '添加后续列的子项
        For col As Integer = 2 To lastCol
            listItem.SubItems.Add(sheet.Cells(row, col).Value?.ToString())
        Next
        '将Item添加到ListView
        lstViewUsers.Items.Add(listItem)
    Next

    '清理资源，释放COM对象防止Excel进程残留
    book.Close()
    xls.Quit()
    System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet)
    System.Runtime.InteropServices.Marshal.ReleaseComObject(book)
    System.Runtime.InteropServices.Marshal.ReleaseComObject(xls)
End Sub

注意事项 #

• 确保你的项目已经引用了Microsoft.Office.Interop.Excel组件（右键项目→添加引用→COM→找到Microsoft Excel xx.x Object Library）
• 导入时会自动清空现有ListView数据，如果需要保留原有数据，可以去掉lstViewUsers.Items.Clear()这行
• 代码里用了?.ToString()是为了避免单元格为空时出现NullReferenceException

内容的提问来源于stack exchange，提问作者DaVince294