excel - 将文本文件中的数据排序为树形结构

Question

我在一个包含项目列表及其相互关系的分隔文本文件中有数据，如下所示

数据集文本文件

其中每个项目 ID 都是不同的，父 wbs 列显示与父项目的关系。

我能够将上述数据导入 excel，但无法弄清楚如何在 excel 中以树格式构造它们，并确保在每个节点下，项目按其序号再次排序

For i = 3 To LastRow
            Dim found As Boolean = False
             For k = 2 To wbssht.UsedRange.Rows.Count

                If wbssht.Cells(k, 1).value = sht.Cells(i, 3).value Then
                    wbssht.Rows(k + 1).insert
                    wbssht.Cells(k + 1, 1).value = sht.Cells(i, 1).value
                    wbssht.Cells(k + 1, 3).value = wbssht.Cells(k, 3).value + 2
                    wbssht.Cells(k + 1, 2).value = Space(wbssht.Cells(k + 1, 3).value) & sht.Cells(i, 2).value
                    wbssht.Cells(k + 1, 4).value = sht.Cells(k, 3).value 'parentwbs
                    wbssht.Cells(k + 1, 5).value = sht.Cells(k, 4).value 'sequence
                    found = True
                    Exit For
                End If
            Next
            If found = False Then
                wbssht.Cells(wbssht.UsedRange.Rows.Count + 1, 1).value = sht.Cells(i, 1).value
                wbssht.Cells(wbssht.UsedRange.Rows.Count, 2).value = sht.Cells(i, 2).value
                wbssht.Cells(wbssht.UsedRange.Rows.Count, 3).value = 0
                wbssht.Cells(wbssht.UsedRange.Rows.Count, 4).value = sht.Cells(i, 3).value
                wbssht.Cells(wbssht.UsedRange.Rows.Count, 5).value = sht.Cells(i, 4).value
            End If

        Next

我正在尝试在 excel 中实现如下图所示的缩进输出结构。

预期产出

我知道它可能真的很简单，但可能需要一些不同的方法。

标签： excelvb.nettreeviewvsto

Answer 1

试试这个代码：

Private Sub TreeView1_KeyUp(KeyCode As Integer, ByVal Shift As Integer)
    
    'Checking what button is pressed.
    Select Case KeyCode
        
        'If F5 is pressed.
        Case Is = 116
            
            'Declarations.
            Dim RngList As Range
            Dim RngTarget As Range
            Dim DblNameOffset As Double
            Dim DblParentOffset As Double
            Dim DblSequenceOffset As Double
            Dim StrMarker As String
            Dim NodNode As Node
            Dim DblRow As Double
            Dim DblCounter01 As Double
            Dim DblItemMax
            Dim ObjTreeView As Object
            
            'Setting RngList as the first value in the Item ID column.
            Set RngList = Sheets("Sheet1").Range("A2")
            
            'Setting ObjTreeView.
            Set ObjTreeView = ActiveSheet.Shapes("TreeView1")
            
            'Setting StrMarker as a value that won't be in any Item ID value nor in any Sequence value.
            StrMarker = " | "
            
            'Setting the variables as offests of each column of data from the Item ID column.
            DblNameOffset = 1
            DblParentOffset = 2
            DblSequenceOffset = 3
            
            'Changing RngList to cover the whole Item ID list.
            Set RngList = RngList.Parent.Range(RngList, RngList.End(xlDown))
            
            'Setting DblItemMax as the count of Item IDs.
            DblItemMax = Excel.WorksheetFunction.CountA(RngList)
            
            'Checking that RngList does not contain any non unique value, non numeric value, blank cell.
            For Each RngTarget In RngList
                Select Case True
                    Case Is = (Excel.WorksheetFunction.CountIf(RngList, RngTarget.Value) > 1)
                        MsgBox "Non unique item ID found. The treeview will not be updated.", vbCritical + vbOKOnly, "Invalid item ID: " & RngTarget.Value
                        Exit Sub
                    Case Is = (RngTarget.Value = "")
                        MsgBox "Blank ID found. The treeview will not be updated.", vbCritical + vbOKOnly, "Invalid item ID in cell " & RngTarget.Address(False, False)
                        Exit Sub
                    Case Is = (IsNumeric(RngTarget.Value) = False)
                        MsgBox "Non numeric item ID found. The treeview will not be updated.", vbCritical + vbOKOnly, "Invalid item ID: " & RngTarget.Value
                        Exit Sub
                End Select
            Next
            
            'Clearing ObjTreeView of any previous nodes.
            ObjTreeView.OLEFormat.Object.Object.Nodes.Clear
            
            'Covering each Item ID from the smalles to the greatest.
            For DblCounter01 = 1 To DblItemMax
                
                'Setting DblRow as the number of row in RngList that contains the given Item ID.
                With Excel.WorksheetFunction
                    DblRow = .Match(.Small(RngList, DblCounter01), RngList, 0)
                End With
                
                'Setting RngTarget as the cell that contains the given Item ID.
                Set RngTarget = RngList.Cells(DblRow, 1)
                
                'Checking if the given parent name exist in RngList.
                If Excel.WorksheetFunction.CountIf(RngList, RngTarget.Offset(0, DblParentOffset).Value) = 0 Then
                    'If it doesn't exist, the new node is added with no parent node.
                    ActiveSheet.Shapes("TreeView1").OLEFormat.Object.Object.Nodes.Add , , "K" & RngTarget.Value, RngTarget.Offset(0, DblSequenceOffset) & StrMarker & RngTarget.Offset(0, DblNameOffset)
                Else
                    'If it exists, the new node is added under its parent node.
                    ActiveSheet.Shapes("TreeView1").OLEFormat.Object.Object.Nodes.Add "K" & RngTarget.Offset(0, DblParentOffset), tvwChild, "K" & RngTarget.Value, RngTarget.Offset(0, DblSequenceOffset) & StrMarker & RngTarget.Offset(0, DblNameOffset)
                End If
            Next
            
            'Sorting each node (they were added with the Sequence value at the beginning of it text).
            For Each NodNode In ActiveSheet.Shapes("TreeView1").OLEFormat.Object.Object.Nodes
                NodNode.Sorted = True
            Next
            
            'Cutting out the sequence value from the text of each node using the properly placed StrMarker.
            For Each NodNode In ActiveSheet.Shapes("TreeView1").OLEFormat.Object.Object.Nodes
                NodNode.Text = Split(NodNode.Text, StrMarker)(1)
            Next
            
    End Select
    
End Sub

它是一个私有子组件，当您在选择树视图时按 F5 时将激活该子组件。因此，您必须将其放置在树视图所在的工作表模块中。它假定您的树视图名为 TreeView1。它还假设您的列表放置在名为 Sheet1 的工作表的单元格 A1 中；这意味着在 Sheet1 的单元格 A1 中，您将找到标题“项目 ID”，而在单元格 A2 中，您将找到第一个 ID。请注意，您可以将列表放在一张表中，将树视图放在另一张表中。无论如何，您可以根据需要编辑代码本身（也许您可以更改列表地址、树视图名称或要按下以激活它的键）。其他参数也可以定制。

代码检查任何空的、非数字的、非唯一的项目 ID，如果找到它们中的任何一个，它就会自行终止。

该列表可以按任何顺序排序。无论如何，代码应该可以工作。在您的数据样本中，第一个项目 (27521) 的父名称 (18133) 在项目 ID 列中没有匹配项。在这种情况下，代码会创建一个没有父节点的节点。在任何情况下，假定任何项目 ID 都有一个项目 ID 较低的父亲。