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-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
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-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
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-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
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

    • ajblandford
      By ajblandford
      I have embeded an Excel file in my autoit Script and want to use it as part of my GUI. I want to be able to select a range of cells and add the cell data to an edit box. Then dump that data to an array. I am using GUICtrlCreateObj to embed the spreadsheet. I cannot find any way to let my program see what cells are active so I can add that data to the edit box. 
       
      #include <WindowsConstants.au3> #include <GUIConstantsEx.au3> #include <excel.au3> $FileName = 'C:\VLog\book1.xlsx' If Not FileExists($FileName) Then MsgBox(0, "ERROR", "File not found") Exit EndIf ;Basic GUI $oExcelDoc = ObjGet($FileName) ; Get an excel Object from an existing filename If IsObj($oExcelDoc) Then $mainGUI = GUICreate("Production Room 2", 1800, 1200, 10, 10, $WS_MINIMIZEBOX + $WS_SYSMENU + $WS_CLIPCHILDREN) $GUI_ActiveX = GUICtrlCreateObj($oExcelDoc, 10, 70, @DesktopWidth - 250, @DesktopHeight - 260) $data = GUICtrlCreateEdit("", @DesktopWidth - 225, 75, 100) $btn = GUICtrlCreateButton( "GO", @DesktopWidth - 100, 75, 75) Else MsgBox(0, "", "failed") EndIf ;------------------ ;Turns off all command bars in excel to prevent user from making changes For $Bar In $oExcelDoc.CommandBars If $Bar.Enabled = True Then $Bar.Enabled = False If $Bar.Visible = True Then $Bar.Visible = False Next $oExcelDoc.Application.DisplayFormulaBar = False $oExcelDoc.Application.CommandBars("Shadow Settings").Visible = False $oExcelDoc.Application.DisplayScrollBars = True $oExcelDoc.Application.DisplayStatusBar = False GUISetState()  
    • Skeletor
      By Skeletor
      Hi Virtual People,
      My array works perfectly fine. However, what is the best practice if the line in the array doesn't have the correct amount of columns and if I can add a placeholder?

       
      For $count = 1 To _FileCountLines($FileRead1) Step 1 $string = FileReadLine($FileRead1, $count) $input = StringSplit($string, ",", 1) $value1 = $input[1] $value2 = $input[2] $value3 = $input[3] _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value2, "A1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value1, "B1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value3, "C1") Next  
    • Skeletor
      By Skeletor
      Hi Guys,
      Can anyone point me in the right direction. I'm trying to accomplished conditional formatting with arrows, but the code I have is wrong... 
      .Range("=$A3:$A4000").FormatConditions.Add = (xlCellValue, xlGreater, "=$A:$A") .IconSet(xl3Arrows) ;ActiveWorkbook.iconsets I also tried it like this:
      .Range("=$A3:$A4000").FormatConditions _ .Add(xlCellValue, xlGreater, "=$A:$A") .IconSet(xl3Arrows) ;ActiveWorkbook.iconsets  
    • MrCheese
      By MrCheese
      hi all,
      reviewing the forum, this thread is applicable: 
       
       
      I wanted to know if there is now a better way to do this?
      In essence, I load a tab delimited txt file into an array (works well). I used tab, as some fields in the original csv contains commas.
      However, I needed autoit to manipulate this array, and output it as a csv.
      IF my array contains items with a comma, without double quotes around the field, then how best do I get a csv out of this?
      My current workaround is to filewritefromarray tab delimited, then open it in excel and save as a csv. I will need to check this to see how the address fields behave that contain a comma.
       
      Any thoughts would be appreciated.
       
    • Skeletor
      By Skeletor
      Hi Guys,
      How would you use _Excel_FilterSet to filter the excel sheet from largest number to smallest number?
      _Excel_FilterSet($oWorkbook, "Sheet1", "A2:I2", 1, ">20", 1, "<40") ; OR _Excel_FilterSet($oWorkbook, "Sheet1", "A2:I2", 1, ">", 1, "<") Excel Sheet attached. Trying to filter on the last column "I2"
      Inventory.xlsx
×