如何将Excel数据导入到ListView控件中？

ListView控件可以显示多列数据，与excel工作表单元格具有相似性。

如下图所示为Excel中的数据

现在要将Excel数据导入到ListView控件中，形成如下图所示的结果

可以使用如下的代码：

Declare Function GetOpenFileName Lib "comdlg32.dll" Alias _
     "GetOpenFileNameA" (lpofn As OPENFILENAME) As Long
 Type OPENFILENAME
     lStructSize As Long
     hwndOwner As Long
     hInstance As Long
     lpstrFilter As String
     lpstrCustomFilter As String
     nMaxCustFilter As Long
     nFilterIndex As Long
     lpstrFile As String
     nMaxFile As Long
     lpstrFileTitle As String
     nMaxFileTitle As Long
     lpstrInitialDir As String
     lpstrTitle As String
     flags As Long
     nFileOffset As Integer
     nFileExtension As Integer
     lpstrDefExt As String
     lCustData As Long
     lpfnHook As Long
     lpTemplateName As String
End Type
Function GetFileName() As String
    Dim sOFN As OPENFILENAME
    With sOFN
        .lStructSize = Len(sOFN)
        '设置打开文件对话框中的文件筛选字符串对
        .lpstrFilter = "Excel文件(*.xl*)" & Chr(0) & "*.xl*" & Chr(0) & "Word文件(*.do*)" _
        & Chr(0) & "*.do*" & Chr(0) & "PPT文件(*.pp*)" & Chr(0) & "*.pp*" & Chr(0) & "所有文件(*.*)" & Chr(0) & "*.*" _
        & Chr(0) & Chr(0)
        '设置文件完整路径和文件名的缓冲区
        .lpstrFile = Space(1024)
        '设置文件完整路径和文件名的最大字符数,一定要比lpstrFile参数指定的字符数多1，用于存储结尾Null字符。
        .nMaxFile = 1025
    End With
    i = GetOpenFileName(sOFN)
    If i <> 0 Then
        With sOFN
            sFileName = Trim(.lpstrFile)
            GetFileName = Left(sFileName, Len(sFileName) - 1)
        End With
    Else
        GetFileName = ""
    End If
    Debug.Print GetFileName, Len(GetFileName)
End Function
Sub QQ1722187970()
    Const xlToLeft = -4159
    Const xlUp = -4162
    Dim oExcel
    Dim oWB
    Dim oWK
    Dim oCH As ColumnHeader
    Dim oLI As ListItem
    Dim oLSI As ListSubItem
    Dim sFileName As String
    '选择要导入的excel文件
    sFileName = GetFileName
    If Len(sFileName) Then
        Set oExcel = CreateObject("Excel.Application")
        With oExcel
            oExcel.Visible = True
            Set oWB = .workbooks.open(sFileName)
            Set oWK = oWB.worksheets(1)
            With oWK
                '读取总列数
                iCol = .cells(1, 256).End(xlToLeft).Column
                 With ListView1
                    .View = lvwReport
                    '先创建列标题
                    For j = 1 To iCol
                        .ColumnHeaders.Add , , oWK.cells(1, j)
                    Next j
                 End With
                 For i = 2 To .range("a65536").End(xlUp).row
                    '先创建第一列项目
                   Set oLI = ListView1.ListItems.Add(, , oWK.cells(i, 1))
                   '后创建其它列项目
                   With oLI
                        For j = 2 To iCol
                            .ListSubItems.Add , , oWK.cells(i, j)
                        Next j
                   End With
                 Next i
            End With
            oWB.Close (False)
            .quit
        End With
        Set oExcel = Nothing
    End If
End Sub

上述代码首先使用API函数获取选中的文件的文件完整路径，然后用Excel打开后读取数据到ListView控件中。

上述代码即可在VB 6.0中使用，也可以在VBA中使用。