[原创]listview和treeview的组合应用小例子

烟雨厦江南

Option Explicit
Dim CNN As New ADODB.Connection
Dim RST As New ADODB.Recordset
Dim txtSQL As String
Dim iCount As Integer, i As Integer, j As Integer, k As Integer
Dim F_Key As String, S_Key As String
Dim XArray

Private Sub CommandButton1_Click()
    Unload Me
End Sub

Private Sub CommandButton2_Click()
    txtSQL = "select 产品类别,销售区域,订单类型,付款方式,订单日期,订单号码,客户名称,项目,产品规格,订单数量," _
            & "订单价格,订单基准价,出货日期,出货量,批次,出货日期基准价,业务员 from " _
            & "[sheet1$A2:Q2000]"
    RST.Open txtSQL, CNN, adOpenKeyset, adLockPessimistic
    iCount = RST.RecordCount
    On Error Resume Next
    For i = 1 To iCount
        For j = 1 To 5
            For k = 0 To j - 1
                F_Key = F_Key & RST.Fields(k) & ","
                S_Key = S_Key & RST.Fields(k) & ","
            Next k
            F_Key = Left(F_Key, Len(F_Key) - 1)
            S_Key = S_Key & RST.Fields(j)
            TreeView1.Nodes.Add F_Key, tvwChild, S_Key, RST.Fields(j), 3, 1
            S_Key = ""
            F_Key = ""
        Next j
        RST.MoveNext
    Next i
    RST.Close
End Sub

Private Sub TreeView1_NodeClick(ByVal Node As MSComctlLib.Node)
    If InStr(Node.Key, ",") = 0 Then Exit Sub
    Dim Temp
    txtSQL = ""
    Temp = Split(Node.Key, ",")
    For i = LBound(Temp) To UBound(Temp) - 1
        If XArray(i) <> "订单日期" Then
            txtSQL = txtSQL & XArray(i) & "='" & Temp(i) & "' and "
        Else
            txtSQL = txtSQL & XArray(i) & "=#" & Temp(i) & "# and "
        End If
    Next i
   
    If XArray(i) <> "订单日期" Then
        txtSQL = "SELECT 产品类别,销售区域,订单类型,付款方式,订单日期,订单号码,客户名称,项目,产品规格,订单数量," _
                & "订单价格,订单基准价,出货日期,出货量,批次,出货日期基准价,业务员 FROM [sheet1$A2:Q2000] WHERE " & txtSQL & XArray(i) & "='" & Temp(i) & "'"
    Else
        txtSQL = "SELECT 产品类别,销售区域,订单类型,付款方式,订单日期,订单号码,客户名称,项目,产品规格,订单数量," _
                & "订单价格,订单基准价,出货日期,出货量,批次,出货日期基准价,业务员 FROM [sheet1$A2:Q2000] WHERE " & txtSQL & XArray(i) & "=#" & Temp(i) & "#"
    End If
   
    RST.Open txtSQL, CNN, adOpenKeyset, adLockPessimistic
    iCount = RST.RecordCount
   
    LstvwInit
    With ListView1
        For j = UBound(Temp) + 1 To RST.Fields.Count - 1
            .ColumnHeaders.Add , , RST.Fields(j).Name, 80
        Next j
        For i = 1 To iCount
            .ListItems.Add , , RST.Fields(UBound(Temp) + 1)
            For j = UBound(Temp) + 2 To RST.Fields.Count - 1
                .ListItems(i).SubItems(j - UBound(Temp) - 1) = RST.Fields(j)
            Next j
        RST.MoveNext
        Next i
    End With
    RST.Close
End Sub

Private Sub UserForm_Initialize()
    XArray = Array("产品类别", "销售区域", "订单类型", "付款方式", "订单日期", "订单号码")
    TrvwInit
    With TreeView1
        .Nodes.Add , , "订单汇总", "订单汇总", 4, 1
        .Nodes.Item(1).Expanded = True
        .Nodes.Add "订单汇总", tvwChild, "CHIP", "CHIP", 4, 1
        .Nodes.Add "订单汇总", tvwChild, "POY", "POY", 4, 1
        .Nodes.Add "订单汇总", tvwChild, "FDY", "FDY", 4, 1
        .Nodes.Add "订单汇总", tvwChild, "DTY", "DTY", 4, 1
    End With
    CNN.Open "provider=microsoft.jet.oledb.4.0;extended properties='excel 8.0;hdr=yes;imex=1';data source=" _
        & ThisWorkbook.Path & "\订单信息.xls"

End Sub

Sub LstvwInit()
    With ListView1
        .ColumnHeaders.Clear
        .ListItems.Clear
        .View = lvwReport
        .FullRowSelect = True
        .Gridlines = True
        .LabelEdit = lvwManual
    End With
End Sub

Sub TrvwInit()
    With TreeView1
        .LabelEdit = tvwManual
        .ImageList = ImageList1
        .LineStyle = tvwRootLines
        .Style = tvwTreelinesPlusMinusPictureText
        .Nodes.Clear
    End With
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CNN.State = 1 Then CNN.Close
End Sub

Private Sub ListView1_ColumnClick(ByVal ColumnHeader As MSComctlLib.ColumnHeader)
Dim i%
    With ListView1
        If (ColumnHeader.Index - 1) = .SortKey Then
            If ColumnHeader.Text = "订单数量" Or ColumnHeader.Text = "出货量" Then
                For i = 1 To .ListItems.Count
                    .ListItems(i).SubItems(ColumnHeader.Index - 1) = Format(.ListItems(i).SubItems(ColumnHeader.Index - 1), "000000000.000")
                Next i
            End If
                 .SortOrder = (.SortOrder + 1) Mod 2
            If ColumnHeader.Text = "订单数量" Or ColumnHeader.Text = "出货量" Then
                For i = 1 To .ListItems.Count
                    .ListItems(i).SubItems(ColumnHeader.Index - 1) = Val(.ListItems(i).SubItems(ColumnHeader.Index - 1))
                Next i
            End If
        Else
            If ColumnHeader.Text = "订单数量" Or ColumnHeader.Text = "出货量" Then
                For i = 1 To .ListItems.Count
                    .ListItems(i).SubItems(ColumnHeader.Index - 1) = Format(.ListItems(i).SubItems(ColumnHeader.Index - 1), "000000000.000")
                Next i
            End If
            .Sorted = False
            .SortOrder = 0
            .SortKey = ColumnHeader.Index - 1
            .Sorted = True
            If ColumnHeader.Text = "订单数量" Or ColumnHeader.Text = "出货量" Then
                For i = 1 To .ListItems.Count
                    .ListItems(i).SubItems(ColumnHeader.Index - 1) = Val(.ListItems(i).SubItems(ColumnHeader.Index - 1))
                Next i
            End If
        End If
    End With
End Sub

 

G2kmet2Q.rar (54.91 KB, 下载次数: 1859)

2007-5-4 12:12 上传

点击文件名下载附件
[原创]listview和treeview的组合应用小例子

[此贴子已经被作者于2007-5-4 12:12:14编辑过]

wsri

这么好的东西怎么没人顶呢！这才叫功夫！

mcy318

这么好的东东建议加精

烟雨厦江南

重新修改了一下，增加了listview的排序功能，以及listview的数值排序功能（listview只支持按文本方式排序），附件已在一楼重新更新过了。

DJFN

学习学习！

DJFN

这个东西太强了嘛，界面也舒服！

ah2005

正用的上，顶一个！！！！！１１

zxun

不错不错,但为什么必须刷新一下才能显示下面的节点呢?

npixxf

请问为何我打开的时候显示"找不到工程或库"呢?/

nicehunnt

就喜欢这个东东，做查询界面超经典，向楼主学习，早日做出自己的东东来！

[em17]