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
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

    • AzgarD
      By AzgarD
      Hi guys. I know this is a newbie topic, very newbie, but i've read a lot of stuff and still don't get it. I just need to copy something from Excel cell, paste this in other program, copy something in this program and paste in other Excel cell. Something like...
      Copy A2 Use some WindowActivate and MouseMove stuff and CTRL+C (not a problem) Go back to the Excel sheet Paste that content in C2 Then Copy A3 Use some WindowActivate and MouseMove stuff and CTRL+C (not a problem) Go back to the Excel sheet Paste that content in C3 ... And it goes on The problem is, how can i "communicate" with Excel and do this row change? Like A2 to C2 and A3 to C3 ... In a efficient way that can be done like hundreds of times.
      Very newbie question but still not understanding this.
       
      Ty guys.
    • RHolmes
      By RHolmes
      I wrote a script a few months ago that was working at the time. When I tried it today it wouldn't retrieve any controls.  The application successfully launches, but I cannot figure out how to retrieve the control from the window that is launched. I'm on a windows 10 machine using AutoIt v3.3.14.2. The only thing I can think of that has changed is windows updates? Code is below, any help is greatly appreciated.    ; Notes: ; HandleError( handleToCheck, MsgToLogOnFailure, terminateAutoItOnFail ) : function that simply checks the handle and quits AutoIt if not present ; all of this works well FileChangeDir( $CLIENT_APPLICATION_DIR ); Run( "Client.exe" ) Local $hClient = WinWaitActive( $CLIENT_TITLE, "", 10 ) $terminateOnFail = 1 HandleError( $hClient, "LaunchClient::Error: Failed to launch client. Either timed-out or failed.", $terminateOnFail ) LogToFile( "Client launched, waiting for system to ready." ) Sleep( 5000 ) ; this part does not work ; $SYSTEM_INDICATOR is a global variable. I have tried these values: "SystemIndicatorWindow" (Text), "Qt5QWindowIcon101" (ClassNN), and ; "[CLASS:Qt5QWindowIcon; INSTANCE:101]" Local $hStatusIndicator = ControlGetHandle( $hClient, "", $SYSTEM_INDICATOR ) HandleError( $hStatusIndicator, "CheckStatus::Error: couldn't retrieve control: " & $SYSTEM_INDICATOR, $terminateOnFail ) This is what the spy reveals: 

       
      Edit: I just tried this code and it works for notepad++.
      FileChangeDir( "C:\Program Files\Notepad++\" ); Run( "notepad++.exe" ) Local $hNotePad = WinWaitActive( "new 1 - Notepad++", "", 10 ) If $hNotePad = 0 Or $hNotePad = -1 Then MsgBox( $MB_SYSTEMMODAL, "Error", "Error getting app handle." ) EndIf Sleep( 1000 ) Local $hNewFileBtn = ControlGetHandle( $hNotePad, "", "[CLASS:ToolbarWindow32; INSTANCE:1]" ) If $hNewFileBtn = 0 Or $hNewFileBtn = -1 Then MsgBox( $MB_SYSTEMMODAL, "Error", "Error getting button handle." ) EndIf MsgBox( $MB_SYSTEMMODAL, "Success", "Success." )  
    • rcmaehl
      By rcmaehl
      A UDF with Extended Functions for Window Management
       
      Notes:
      Fixes WinGetClassList's barbaric returning of a @LF separated string instead of an array.
       
      Potential Uses:
      Automating applications that change their controls' handles/classes on each launch (e.g. half of Cisco's programs)
       
      Functions:
      _WinGetClassList
      _WinGetClassNNList
      _WindowGetHandleList
      _WindowGetHandleListFromPos
       
      Download: 
      WindowEx.zip  (v0.4)
       
      Changelog:
      10/04/2016 (v0.4): _WinGetClassNNList Fixed : Not Returning an Index when using $2D_ARRAY _WinGetClassNNList Fixed : Not Properly returning $aArray[x][1] on Classes with instances > 9 when using $2D_ARRAY 10/03/2016 (v0.3): _WinGetClassList Added : Exactly the same as WinGetClassList but returns a more civilized Array _WinGetClassNNList Added : Returns Classes and their instances in either a 1D or 2D array depending on Flags _WindowGetHandleList Renamed: _WinGetHandleList SCRIPT BREAKING! _WindowGetHandleListFromPos Renamed: _WinGetHandleListFromPos SCRIPT BREAKING! 10/01/2016 (v0.2): WindowsExConstants.au3 Added : Flags in _WindowGetHandleListFromPos _WindowGetHandleListFromPos Removed: ConsoleWrite left in during debug _WindowGetHandleListFromPos Added : Flag for if part of a Control is at $X, $Y return it as well. 10/01/2016 (v0.1): _WindowGetHandleList Added : Retrieves the handles of classes from a window. _WindowGetHandleListFromPos Added : Retrieves the handles of classes at a specific position from a window. Known and Reported Bugs:
      None reported To Do:
      To Be Decided. Opinions welcome! Upcoming Changes:
      To Be Decided.
    • VollachR
      By VollachR
      Hi,
      I'm looking for a way to take a number value from a Row2 of a 2D array and according to this check if files that appear in rows 3-11 in the array exists.
      For example, if the number in Row2 is 5 I need to check for the files in Row 3-6 only, if it is 6 than rows 3-7 and so on.
      I thought on using a FOR loop but I have very little experience with those.
      Can you suggest the best way to do what I need?
      BTW, the files in Rows 3-11 will usually have blank value for any row above the number in Row2 (e.g. Row2 = 5 so Rows3-6 will have values but 8-11 be empty), The values I need are in Column 1 of the array, the name of the key from the INI file that the array was created from is in Column 0.
      Full Example:
      Row2 of Array:
      Col0 = Games# - Col1 = 5
      Rows3-6
      Col0 = Exe2 - Col1 = Path To File
      Col0 = Exe3 - Col1 = Path To File
      Col0 = Exe4 - Col1 = Path To File
      Col0 = Exe5 - Col1 = Path To File
      I need that if Row2 is 5 to check these above for rows if the file exists, if it was 6 then the next row as well and so on up until number 10 in Row2 as it can't go above 10.
      So basically for whatever number in Row2 from 2-10 need to check 1-9 rows from 3-11 to see if the files in Col1 exists and if any of them don't exist it should call a function that shows an error message.
      I'm pretty sure I have the first line of the for look correct:
      For $i = 1 To $aAIO[2][1] Just not sure how to continue from there, also not sure if $i should be equal 1 or 2.
      Help will be appreciated.
    • FMS
      By FMS
      Hello,
      I'm trying to get data from twitter to an array and so far I found an Twitter UDF whish lookes very intresting but couldn't get it to work.
      It lookes not supported any more(2010) and buggy when i read all te replies.
      More around this subject (autoit and twitter) i couldn't find on this forum.
      Is there sombody who know's a good way to get live data from twitter to an array inside autoit?
      (I kinda doubt that this isn't tackled before)
      In the end I was hoping to get all tweets from date to date from an specific subject inside a 2D array to work whit.
×