如何使用listview标题将listview项导出到excel表。 [英] how to export the listview items to excel sheet with listview header.

问题描述

我正在尝试将vb 6.0中listview的数据导出到带有listview标头的excel表。



我的代码是： -

I am trying to export the data of listview in vb 6.0 to excel sheet with listview header.

My code is:-

Private Sub cmdExport_Click()

'genaral
Dim objExcel As New Excel.Application

Dim objExcelSheet As Excel.Worksheet
'-----------------------------------

'check whether data is thre
If LstLog.ListItems.count > 0 Then
    objExcel.Workbooks.Add
    Set objExcelSheet = objExcel.Worksheets.Add


    For Col = 1 To LstLog.ColumnHeaders.count
        objExcelSheet.Cells(1, Col).Value = LstLog.ColumnHeaders(Col)
    Next

    For Row = 2 To LstLog.ListItems.count
        For Col = 1 To LstLog.ColumnHeaders.count
        If Col = 1 Then
                objExcelSheet.Cells(Row, Col).Value = LstLog.ListItems(Row).Text
        Else
                objExcelSheet.Cells(Row, Col).Value = LstLog.ListItems(Row).SubItems(Col - 1)
        End If
        Next
    Next

    objExcelSheet.Columns.AutoFit
    CommonDialog1.ShowOpen
    A = CommonDialog1.FileName

    objExcelSheet.SaveAs A & ".xls"
    MsgBox "Export Completed", vbInformation, Me.Caption

    objExcel.Workbooks.Open A & ".xls"
    objExcel.Visible = True
    'objExcel.Quit
Else
    MsgBox "No data to export", vbInformation, Me.Caption
End If

End Sub

但是当我使用此代码将数据导出到excel表时，列表视图项的第一行将被列表视图标题替换..



请帮助。

But when I export the data to excel sheet with this code the first row of list view item is replace by list view header..

Please help.

推荐答案

替换

Replace

For Row = 2 To LstLog.ListItems.count
    For Col = 1 To LstLog.ColumnHeaders.count
    If Col = 1 Then
            objExcelSheet.Cells(Row, Col).Value = LstLog.ListItems(Row).Text
    Else
            objExcelSheet.Cells(Row, Col).Value = LstLog.ListItems(Row).SubItems(Col - 1)
    End If
    Next
Next

带

with

For Row = 2 To LstLog.ListItems.count
    For Col = 1 To LstLog.ColumnHeaders.count
    If Col = 1 Then
            objExcelSheet.Cells(Row, Col).Value = LstLog.ListItems(Row-1).Text
    Else
            objExcelSheet.Cells(Row, Col).Value = LstLog.ListItems(Row-1).SubItems(Col - 1)
    End If
    Next
Next

标题没有覆盖第一个数据条目。代码只是跳过列表视图中的第一个数据条目。



如果导出的数据中没有显示最后一项，则更改

Headers weren't overriding the first data entry. The code was just skipping over the first data entry in the listview.

if last item does not show up in the exported data then change

For Row = 2 To LstLog.ListItems.count

to

to

For Row = 2 To LstLog.ListItems.count + 1

Public Sub export_me_to_excel(ByVal list As ListView)
    Try
        Dim objExcel As New Excel.Application
        Dim bkWorkBook As Workbook
        Dim shWorkSheet As Worksheet
        Dim chartRange As Excel.Range


        Dim i As Integer
        Dim j As Integer

        objExcel = New Excel.Application
        bkWorkBook = objExcel.Workbooks.Add
        shWorkSheet = CType(bkWorkBook.ActiveSheet, Worksheet)
        shWorkSheet.DisplayRightToLeft = True

        chartRange = shWorkSheet.Range("a1", "e2")
        chartRange.Merge()
        chartRange.FormulaR1C1 = xlval

        chartRange.HorizontalAlignment = 2
        chartRange.VerticalAlignment = 2

        For i = 0 To list.Columns.Count - 1
            shWorkSheet.Cells(5, i + 1) = list.Columns(i).Text
            shWorkSheet.Columns.AutoFit()
            shWorkSheet.Columns.HorizontalAlignment = Excel.Constants.xlCenter

            'shWorkSheet.Range(newcell).BorderAround2(Excel.XlLineStyle.xlContinuous, XlBorderWeight.xlMedium, XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic)
        Next
        For i = 0 To list.Items.Count - 1
            For j = 0 To list.Items(i).SubItems.Count - 1
                shWorkSheet.Cells(i + 6, j + 1) = list.Items(i).SubItems(j).Text
                shWorkSheet.Columns.AutoFit()
                shWorkSheet.Columns.HorizontalAlignment = Excel.Constants.xlCenter
                'shWorkSheet.Columns.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic)

            Next
        Next

        objExcel.Visible = True
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try
End Sub

这篇关于如何使用listview标题将listview项导出到excel表。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！