'以下是listview控件中的数据导出到excel文件的一个过程,可以参考 Dim ExcelApp As Object '应用程序 Dim ExcelBook As Object '工作簿 Dim ExcelSheet As Object '工作表Public Sub Dcsj(LV As ListView, Bcwjb As String) Dim L As Long 'lv的列 Dim H As Long 'lv的行 Set ExcelApp = CreateObject("Excel.Application") Set ExcelBook = ExcelApp.Workbooks.add Set ExcelSheet = ExcelBook.Worksheets(1) For L = 1 To LV.ColumnHeaders.Count ExcelSheet.Cells(1, L) = LV.ColumnHeaders(L).Text Next L For H = 1 To LV.ListItems.Count If IsDate(LV.ListItems(H).Text) = True Then ExcelSheet.Cells(H + 1, 1).NumberFormatLocal = "yyyy-m-d" Else ExcelSheet.Cells(H + 1, 1).NumberFormatLocal = "@" End If ExcelSheet.Cells(H + 1, 1) = LV.ListItems(H).Text For L = 2 To LV.ColumnHeaders.Count Select Case LV.ColumnHeaders(L).Alignment Case lvwColumnLeft '左 If IsDate(LV.ListItems(H).SubItems(L - 1)) = True Then ExcelSheet.Cells(H + 1, L).NumberFormatLocal = "yyyy-m-d" Else ExcelSheet.Cells(H + 1, L).NumberFormatLocal = "@" End If Case lvwColumnCenter ExcelSheet.Cells(H + 1, L).NumberFormatLocal = "0" Case lvwColumnRight '居中'右 ExcelSheet.Cells(H + 1, L).NumberFormatLocal = "0.00_-" End Select ExcelSheet.Cells(H + 1, L) = LV.ListItems(H).SubItems(L - 1) Next L ExcelSheet.Range(ExcelSheet.Cells(H + 1, 1), ExcelSheet.Cells(H + 1, L - 1)).Borders.LineStyle = xlContinuous Next H ExcelSheet.SaveAs Bcwjb ExcelApp.Quit Set ExcelSheet = Nothing Set ExcelBook = Nothing Set ExcelApp = Nothing MsgBox "数据导出完毕!", vbInformation End Sub
Dim ExcelApp As Object '应用程序
Dim ExcelBook As Object '工作簿
Dim ExcelSheet As Object '工作表Public Sub Dcsj(LV As ListView, Bcwjb As String)
Dim L As Long 'lv的列
Dim H As Long 'lv的行
Set ExcelApp = CreateObject("Excel.Application")
Set ExcelBook = ExcelApp.Workbooks.add
Set ExcelSheet = ExcelBook.Worksheets(1)
For L = 1 To LV.ColumnHeaders.Count
ExcelSheet.Cells(1, L) = LV.ColumnHeaders(L).Text
Next L
For H = 1 To LV.ListItems.Count
If IsDate(LV.ListItems(H).Text) = True Then
ExcelSheet.Cells(H + 1, 1).NumberFormatLocal = "yyyy-m-d"
Else
ExcelSheet.Cells(H + 1, 1).NumberFormatLocal = "@"
End If
ExcelSheet.Cells(H + 1, 1) = LV.ListItems(H).Text
For L = 2 To LV.ColumnHeaders.Count
Select Case LV.ColumnHeaders(L).Alignment
Case lvwColumnLeft '左
If IsDate(LV.ListItems(H).SubItems(L - 1)) = True Then
ExcelSheet.Cells(H + 1, L).NumberFormatLocal = "yyyy-m-d"
Else
ExcelSheet.Cells(H + 1, L).NumberFormatLocal = "@"
End If
Case lvwColumnCenter
ExcelSheet.Cells(H + 1, L).NumberFormatLocal = "0"
Case lvwColumnRight '居中'右
ExcelSheet.Cells(H + 1, L).NumberFormatLocal = "0.00_-"
End Select
ExcelSheet.Cells(H + 1, L) = LV.ListItems(H).SubItems(L - 1)
Next L
ExcelSheet.Range(ExcelSheet.Cells(H + 1, 1), ExcelSheet.Cells(H + 1, L - 1)).Borders.LineStyle = xlContinuous
Next H
ExcelSheet.SaveAs Bcwjb
ExcelApp.Quit
Set ExcelSheet = Nothing
Set ExcelBook = Nothing
Set ExcelApp = Nothing
MsgBox "数据导出完毕!", vbInformation
End Sub