Jump to content
Sign in to follow this  
Jags

TreeView Populated from Excel or Array

Recommended Posts

Jags

Looking to build a TreeView dynamically from an Array (Array built from excel sheet)

Have not made the excel sheet yet, so I'm open to suggestions on layout so as to make it easiest as possible to get it into a treeview.

Thinking each row could be a item to add with the columns defining the placement in the trees, Like:

Cells(1,1) = "Level 1 Parent Name"

Cells(1,2) = "Level 2 Parent Name"

Cells(1,3) = "Level 3 Parent Name"

Cells(1,4) = "Add this item"

Cells(1,5) = ""

When value = ""  then

     Create entire branch or any part of it that doesn't already exist.

advance to next row

For processing speed I'd probably want to work with an array, like:

$aExcelData = _ExcelReadSheetToArray($oExcel,2,1,$LastUsedRow,$LastUsedCol)

Can someone help me with the loop?  or maybe point to tool that may do this?

Share this post


Link to post
Share on other sites
water

I use the following example to create a treeview.

Func _AD_GetOUTreeView($sAD_OU, $hAD_TreeView)

    Local $sSeparator = "\", $aAD_Temp, $sAD_Line, $iAD_Level
    Local $aAD_OUs = _AD_GetAllOUs($sAD_OU, $sSeparator)
    If @error <> 0 Then Return SetError(@error, @extended, 0)
    Local $aAD_TreeView[$aAD_OUs[0][0] + 1][3] = [[$aAD_OUs[0][0], 3]]
    For $i = 1 To $aAD_OUs[0][0]
        $aAD_Temp = StringSplit($aAD_OUs[$i][0], $sSeparator)
        $aAD_TreeView[$i][0] = StringFormat("%" & $aAD_Temp[0] - 1 & "s", "") & "#" & $aAD_Temp[$aAD_Temp[0]]
        $aAD_TreeView[$i][1] = $aAD_OUs[$i][1]
    Next
    _GUICtrlTreeView_BeginUpdate($hAD_TreeView)
    Local $ahAD_Node[50]
    For $iAD_Index = 1 To $aAD_TreeView[0][0]
        $sAD_Line = StringSplit(StringStripCR($aAD_TreeView[$iAD_Index][0]), @TAB)
        $iAD_Level = StringInStr($sAD_Line[1], "#")
        If $iAD_Level = 0 Then ExitLoop
        If $iAD_Level = 1 Then
            $ahAD_Node[$iAD_Level] = _GUICtrlTreeView_Add($hAD_TreeView, 0, StringMid($sAD_Line[1], $iAD_Level + 1))
            $aAD_TreeView[$iAD_Index][2] = $ahAD_Node[$iAD_Level]
        Else
            $ahAD_Node[$iAD_Level] = _GUICtrlTreeView_AddChild($hAD_TreeView, $ahAD_Node[$iAD_Level - 1], StringMid($sAD_Line[1], $iAD_Level + 1))
            $aAD_TreeView[$iAD_Index][2] = $ahAD_Node[$iAD_Level]
        EndIf
    Next
    _GUICtrlTreeView_EndUpdate($hAD_TreeView)
    Return $aAD_TreeView

EndFunc   ;==>_AD_GetOUTreeView

The data to display starts after the "'#" sign.

The number of spaces before the "#" denotes the level in the treeview. The root has zero spaces, all entries on the first level one etc.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
Jags

Thanks water,

But, I'm very new and don't understand how to apply that solution.  Maybe some more basic building blocks would help me get this on my own.

For example:

if $x = "This Particular Existing TreeView Item String"

How do I search the tree for $x and return its location so I can add a child?

Share this post


Link to post
Share on other sites
water

My function creates the TreeView from an array in one go.

The array could look like this example:
 

"#Root"

" #Level1 - First entry"

"  #Level1.1 - First entry"

"   #Level1.1.1 - First entry"

"  #Level1.1 - Second entry"

"  #Level1.1 - Third entry"

" #Level1 - Second entry"

So you would need to create the entries in Excel top-down.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
Jags

I'm missing _AD_GetAllOUs

Share this post


Link to post
Share on other sites
water

I've stripped down the example. The data for the TreeView is stored in an array on line 42.

#include <GUIConstantsEx.au3>
#include <WindowsConstants.au3>
#include <GuiTreeView.au3>
#include <Array.au3>

$iSelected = _TreeView()
Exit

Func _TreeView()
    Local $Msg, $hSelection
    Local $sTitle = "Treeview Test"
    Local $hGUI = GUICreate($sTitle, 743, 683, -1, -1)
    Local $hTree = GUICtrlCreateTreeView(6, 6, 600, 666, -1, $WS_EX_CLIENTEDGE)
    Local $bExit = GUICtrlCreateButton("Exit", 624, 8, 97, 33)
    Local $bExpand = GUICtrlCreateButton("Expand", 624, 56, 97, 33)
    Local $bCollapse = GUICtrlCreateButton("Collapse", 624, 104, 97, 33)
    Local $bSelect = GUICtrlCreateButton("Select", 624, 152, 97, 33)
    Local $aTreeView = _TV_Populate($hTree)
    GUISetState(@SW_SHOW)
    While 1
        $Msg = GUIGetMsg()
        Switch $Msg
            Case $GUI_EVENT_CLOSE, $bExit
                Exit
            Case $bExpand
                _GUICtrlTreeView_Expand($hTree)
            Case $bCollapse
                _GUICtrlTreeView_Expand($hTree, 0, False)
            Case $bSelect
                $hSelection = _GUICtrlTreeView_GetSelection($hTree)
                For $i = 1 To $aTreeView[0][0]
                    If $hSelection = $aTreeView[$i][2] Then ExitLoop
                Next
                Return $aTreeView[$i][1]
        EndSwitch
    WEnd
EndFunc   ;==>_TreeView

Func _TV_Populate($hTree)

    ; Test data
    Local $aTreeView[6][3] = [[5, 3], ["#Root", "ID1"], [" #Level1 Entry 1", "ID2"], [" #Level1 Entry 2", "ID3"], ["  #Level 2 Entry 1", "ID4"], [" #Level1 Entry 3", "ID5"]]
    _GUICtrlTreeView_BeginUpdate($hTree)
    Local $ahNode[50]
    For $iIndex = 1 To $aTreeView[0][0]
        $iLevel = StringInStr($aTreeView[$iIndex][0], "#")
        If $iLevel = 0 Then ExitLoop
        If $iLevel = 1 Then
            $ahNode[$iLevel] = _GUICtrlTreeView_Add($hTree, 0, StringMid($aTreeView[$iIndex][0], $iLevel + 1))
            $aTreeView[$iIndex][2] = $ahNode[$iLevel]
        Else
            $ahNode[$iLevel] = _GUICtrlTreeView_AddChild($hTree, $ahNode[$iLevel - 1], StringMid($aTreeView[$iIndex][0], $iLevel + 1))
            $aTreeView[$iIndex][2] = $ahNode[$iLevel]
        EndIf
    Next
    _GUICtrlTreeView_EndUpdate($hTree)
    Return $aTreeView

EndFunc   ;==>_TV_Populate

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
kylomas

Jags,

Building on waters technique...

#include <array.au3>
#include <GUIConstantsEx.au3>
#include <WindowsConstants.au3>
#include <GuiTreeView.au3>

; arrays used to populate treeview controls
;
;  [0] - parent name ('0' is root level)
;  [1] - element name
;
; note - If the parent does not exist the element will be skipped.
;        If a child is named the same as a previous parent it cannot become a parent (see example in TreeView #2).

Local $aTV1[100][2] = [ _
        ['0', '01'], _
        ['0', '02'], _
        ['0', '03'], _
        ['0', '04'], _
        ['0', '05'], _
        ['01', '01-01'], _
        ['01', '01-02'], _
        ['03', '03-01'], _
        ['03', '03-02'], _
        ['03', '03-03'], _
        ['04', '04-01'], _
        ['01', '01-03'], _
        ['01', '01-04'], _
        ['0', '06'], _
        ['04-01', '04-01-01'], _
        ['04-01', '04-01-02'], _
        ['04-01-02', '04-01-02-01'], _
        ['09', '09-01'], _
        ['01-03', '01-03-01'], _
        ['01-03-01', '01-03-01-01'], _
        ['01-03-01', '01-03-01-02'], _
        ['01-03-01', '01-03-01-03'], _
        ['01-03-01-02', '01-03-01-02-01'], _
        ['04', '04-02'] _
        ]

local $aTV2[100][2] = [ _
        ['0','AA'], _
        ['0','Aa'], _
        ['0','BB'], _
        ['0','bb'], _
        ['AA','AA-01'], _
        ['AA','AA-02'], _
        ['BB','BB-01'], _
        ['bb','bb-01'], _
        ['Aa','Aa-01'], _
        ['bb','bb-02'], _
        ['bb-01','bb-01-01'], _
        ['bb-01','bb-01-02'], _
        ['bb-01-02','bB-01-02-01'], _
        ['bb-01-02-01','bb-01-02-01-01'], _
        ['bb-01-02','bb-01'], _
        ['bb-01','added to first bb-01'], _
        ['aA','aA-01'] _
                        ]

Local $gui010 = GUICreate('TreeView Example',500,500)
Local $tv010 = GUICtrlCreateTreeView(10, 30, 200, 400)
guictrlcreatelabel('TreeView #1',10,10,100,20)
guictrlsetfont(-1,8.5,600)
Local $tv011 = GUICtrlCreateTreeView(290, 30, 200, 400)
guictrlcreatelabel('TreeView #2',290,10,100,20)
guictrlsetfont(-1,8.5,600)
_pop_treeview($tv010,$aTV1)
_pop_treeview($tv011,$aTV2)
GUISetState()

While 1
    Switch GUIGetMsg()
        Case $gui_event_close
            Exit
    EndSwitch
WEnd

func _pop_treeview($hTV,$array)

    ; populate the treeview control
    ;
    ; note - _My_GUICtrlTreeView_FindItem is a replacement for UDF
    ;        _GUICtrlTreeView_FindItem to accomodate case sensitivity

    _GUICtrlTreeView_BeginUpdate($hTV)
    For $1 = 0 To UBound($array) - 1
        if $array[$1][0] = '' then exitloop
        If $array[$1][0] = '0' Then
            _GUICtrlTreeView_Add($hTV, 0, $array[$1][1])
        Else
            $hitem = _My_GUICtrlTreeView_FindItem($hTV, $array[$1][0])
            If $hitem <> 0 Then
                _GUICtrlTreeView_AddChild($hTV, $hitem, $array[$1][1])
            Else
                ConsoleWrite('! Skipping ' & $array[$1][0] & ' cannot find parent' & @LF)
            endif
        EndIf
    Next
    _GUICtrlTreeView_EndUpdate($hTV)
    _GUICtrlTreeView_Expand($hTV)
endfunc

Func _My_GUICtrlTreeView_FindItem($hWnd, $sText, $fInStr = False, $hStart = 0)

    If Not IsHWnd($hWnd) Then $hWnd = GUICtrlGetHandle($hWnd)

    If $hStart = 0 Then $hStart = _GUICtrlTreeView_GetFirstItem($hWnd)
    While $hStart <> 0x00000000
        Local $sItem = _GUICtrlTreeView_GetText($hWnd, $hStart)
        Switch $fInStr
            Case False
                If $sItem == $sText Then Return $hStart
            Case True
                If StringInStr($sItem, $sText) Then Return $hStart
        EndSwitch
        $hStart = _GUICtrlTreeView_GetNext($hWnd, $hStart)
    WEnd
EndFunc   ;==>_GUICtrlTreeView_FindItem

kylomas


Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

  • Similar Content

    • Zein
      By Zein
      #include "..\Include\Array.au3" #include "..\Include\File.au3" #include "..\Include\AutoItConstants.au3" Local $aRetArray Local $sFilePath = "n.csv" _FileReadToArray($sFilePath, $aRetArray, ",") ; _FileReadToArray($sFilePath, $aRetArray, $FRTA_COUNT, ",") _ArrayDisplay($aRetArray, "Original", Default, 8) The above code shows two versions of _FileReadToArray and both don't work as expected.
      The first one doesn't use the comma as a delimiter. (so I get a single column array)  I tried adding "Default" between $aRetArray and "," then it told me it had an incorrect number of parameters. 

      I looked again at the documentation:
       
      #include <File.au3> _FileReadToArray ( $sFilePath, ByRef $vReturn [, $iFlags = $FRTA_COUNT [, $sDelimiter = ""]] )
      And I with or without the flags params I should be getting a 2D array due to my file being a csv. 
      I then tried a regular flag, $FRTA_COUNT, and it tells me that I'm using a variable $FRTA_COUNT while it's not declared. Tried putting in 1 instead and it told me again, incorrect number of params. 

       
    • nooneclose
      By nooneclose
      I need to perform a subtotal in excel and I would like to automate this process using Autoit if possible like always any and all help will be greatly appreciated. 
      I can not find a good example but the two from Microsoft. Here is one of the two from msdn.microsoft.com/en-us/vba/excel-vba/articles/range-subtotal-method-excel
      I do not really understand how to translate this into AutoIt, but I gave it a try and here is what I have.
      $OpenRange      = "A1:E200" $xlSum          = -4157 $Added_Array[2] = [2, 3] $OpenRange.Subtotal("B1", $xlSum, $Added_Array, True, False, True) I just need to perform a subtotal on a range based on a header called department, and then perform a sum on the results.
    • nooneclose
      By nooneclose
      How to use _Excel_RangeSort to sort my excel file by three different headers Column A1, B1, and C1 have headers on which I want to sort by. The headers on which I want to sort are department, employee type, and name.
      I still really new to AutoIt so I do not actually know how to properly start this line or lines of code, to be honest. The example code is the best I can do.
      _Excel_RangeSort($OpenWorkbook, Default, "A1:C1", "1:1", $xlDescending, Default, $xlYes, Default, $xlSortRows) I just need to sort by those three headers in that order of department, employee type, and name, plus in descending order.
       
      any and all help would be greatly appreciated.  Thank you!
    • ternal
      By ternal
      Hi,
      Recently I have had the need to do a sort and then do a second sort while the item of the first sort stays the same ( double sorting , first on column x then while column x is the same sort column y).
      I did not put much efffort into error checking but so far I did not need it.
      For my applications so far it works perfectly however if someone is willing I want to test this extensivly.
      If anyone has big lists of random stuff to sort could you try this out please?
      #include <Array.au3> ; #FUNCTION# ==================================================================================================================== ; Name ..........: _ArraySort_Double ; Description ...: ; Syntax ........: _ArraySort_Double (Byref $array[, $first_index = Default[, $second_index = Default[, $ascending = Default]]]) ; Parameters ....: $array - 2d array to sort. ; $first_index - [optional] first column to sort. Default is 0. ; $second_index - [optional] second column to sort. Default is 1. ; $ascending - [optional] ascending/descending. Default is 1. ; Return values .: 1 if no errors occured , -1 if errors occured ; Author ........: Ternal ; Remarks .......: Needs excessive testing. ; Related .......: _arraysort() ; =============================================================================================================================== Func _ArraySort_Double (byref $array, $first_index = Default, $second_index = Default, $ascending = Default) Local $temp_value Local $counter = 1 If UBound($array, $UBOUND_DIMENSIONS) <> 2 Then MsgBox(0, "error", "error") return -1 EndIf If $first_index = Default Then $first_index = 0 If $second_index = Default Then $second_index = 1 If $ascending = Default Then $ascending = 1 _ArraySort($array, $ascending, 0, 0, $first_index); you can alter settings of primary sort here If @error Then MsgBox(0, "error", @error) return -1 EndIf $temp_value = $array[0][$first_index] For $x = 1 to UBound($array, 1) - 1 If Mod( $x, 10000) = 0 Then ConsoleWrite("at " & $x & " of a total : " & UBound($array, 1) & @CRLF) If $array[$x][$first_index] = $temp_value Then $counter+= 1 If $x = UBound($array, 1) - 1 Then; do last line here(if last line is not a new item) _ArraySort($array, $ascending, $x - $counter, $x, $second_index);you can alter settings of secondary sort here(don't forget to place line 34 the exact same) If @error Then MsgBox(0, "error", @error) return -1 EndIf EndIf Else If $counter > 0 Then ;at least 2 of the same _ArraySort($array, $ascending, $x - $counter, $x - 1, $second_index);you can alter settings of secondary sort here(don't forget to place line 29 the exact same) If @error Then MsgBox(0, "error", @error) return -1 EndIf $counter = 1 EndIf EndIf $temp_value = $array[$x][$first_index] Next Return 1 EndFunc Kind regards, Ternal
    • Daniza
      By Daniza
      It works fine on my Excel 2007, but after I Emailed My Area Manager he told me after he Enable Macro Security nothing happen's, can someone test this to run on Excel 2016? My AM can't provide me more information 

      Thank You in Advance,
      Please see attachment.
      <snip>
×