vb.net 将列表视图项目导出到带有列表视图标题的 Excel 工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20939250/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
export the listview items to excel sheet with listview header
提问by Rabeea Qabaha
I have this code to export the data in listview to excel sheet, but this code export data without the header of list view.
我有此代码将列表视图中的数据导出到 Excel 表，但此代码导出没有列表视图标题的数据。
How can I edit this code to show the header of the listview?
如何编辑此代码以显示列表视图的标题？
Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
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 ListView1.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
采纳答案by SomeNickName
You can get each column text by using this code:
您可以使用以下代码获取每列文本：
Dim columns As New List(Of String)
Dim columncount As Integer = ListView1.Columns.Count - 1
For i As Integer = 0 To columncount
columns.Add(ListView1.Columns(i).Text)
Next
For Each columnname In columns
MessageBox.Show(columnname)
Next
回答by Jeff
Before you enter the loop to export your data you need to iterate the ColumnHeaderCollectionin the ListView
在你进入循环来导出数据，你需要迭代ColumnHeaderCollection在ListView
For i = 0 To ListView1.Columns.Count - 1
sheet.Cells(1, i + 1) = ListView1.Items(i).Name
Next
回答by WIRAD
SaveFileDialog1.Title = "Save Excel File"
SaveFileDialog1.Filter = "Excel Files (*.xlsx)|*.xlsx"
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 = 2
Dim col As Integer = 1
'////////////////////////////////////////////////////////////////////////
Dim rowhead As Integer = 1
Dim colhead As Integer = 1
Dim columns As New List(Of String)
Dim columncount As Integer = LvCOCONFIRMATION.Columns.Count - 1
For i As Integer = 0 To columncount
sheet.Cells(rowhead, colhead) = LvCOCONFIRMATION.Columns(i).Text
colhead = colhead + 1
Next
'////////////////////////////////////////////////////////////////////////
For Each item As ListViewItem In LvCOCONFIRMATION.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)
