How to input the content from the excel file to GUICtrlCreateTreeView.

[KenzoIT]

Hi all,

I want to input the content from the excel file to GUICtrlCreateTreeView.

Test.xls is located in drive D:\Test.xls

I spend a lot of time to research the code but I don't know how to do it.

Can you instruct me ?

Many thanks !

Import Excle.au3

[Juvigy]

Check out the functions

_ExcelBookOpen()
_ExcelReadArray()

After that you just need to populate the listview from the array in the way you desire.

[KenzoIT]

Hi Juvigy,

I did follow your instruction but it have problems when i click "insert excel"

The "test.xls" is located D:/test.xls that will open when i click "insert excel"

GUISetState()

While 1

Switch GUIGetMsg()

Case $GUI_EVENT_CLOSE

ExitLoop

Case $insert

$sfile = ShellExecute("D:/test.xls")

$excel = _ExcelBookOpen($sfile)

$array1=_Excelreadarray($excel,1,1,3,1)

EndSwitch

WEnd

Pls, help me

Thanks !

[GodlessSinner]

$sfile = ShellExecute("D:/test.xls")

$sfile = "D:/test.xls"

[KenzoIT]

I can not install WFHidder on PC due to detected by Kaspersky.

Who help me how to write this code ?

[GodlessSinner]

expandcollapse popup

#include <Excel.au3>
#include <GUIConstantsEx.au3>
#include <WindowsConstants.au3>
#include <TreeViewConstants.au3>
#include <GuiListView.au3>
#include <Array.au3>

Global $hGui = GUICreate("AutoIT",893, 600,-1,-1), $oExcel
Global $hLabel = GUICtrlCreateLabel("", 55, 10, 80, 20)
Local $treeview, $monitor, $backup, $listview
Local $gs1, $gs2, $gs3, $g2
Local $insert, $excel

Global $sFilePath1 = "D:\test.xls" ;This file should already exist
GUICtrlSetColor(-1, 0x0000CC)
GUICtrlSetFont(-1, 11.5, 600)


$treeview = GUICtrlCreateTreeView(10, 35, 170, 520, BitOR($TVS_HASBUTTONS, $TVS_HASLINES, $TVS_LINESATROOT, $TVS_DISABLEDRAGDROP, $TVS_SHOWSELALWAYS), $WS_EX_CLIENTEDGE)
$monitor = GUICtrlCreateTreeViewItem("Monitor System", $treeview)
    GUICtrlSetColor(-1, 0x0000C0)
$gs1=GUICtrlCreateTreeViewItem("GS1",$monitor)
    GUICtrlSetColor(-1, 0x0000CC)
$gs2=GUICtrlCreateTreeViewItem("GS2",$monitor)
    GUICtrlSetColor(-1, 0x0000C0)
$gs3=GUICtrlCreateTreeViewItem("GS3",$monitor)
    GUICtrlSetColor(-1, 0x0000C0)
$g2=GUICtrlCreateTreeViewItem("G2",$monitor)
    GUICtrlSetColor(-1, 0x0000C0)
$backup = GUICtrlCreateTreeViewItem("BackupDB", $treeview)
    GUICtrlSetColor(-1, 0x0000C0)


$insert = GUICtrlCreateTreeViewItem("Insert Excel", $gs1)



    GUICtrlSetState($monitor, BitOR($GUI_EXPAND, $GUI_DEFBUTTON))
    GUICtrlSetState($backup, BitOR($GUI_EXPAND, $GUI_DEFBUTTON))
    GUICtrlSetState($gs1, BitOR($GUI_EXPAND, $GUI_DEFBUTTON))
    GUICtrlSetState($gs2, BitOR($GUI_EXPAND, $GUI_DEFBUTTON))
    GUICtrlSetState($gs3, BitOR($GUI_EXPAND, $GUI_DEFBUTTON))
    GUICtrlSetState($g2, BitOR($GUI_EXPAND, $GUI_DEFBUTTON))

    GUISetState()

     $listview = GUICtrlCreateListView("", 200, 40, 680, 515)
    _GUICtrlListView_SetExtendedListViewStyle($listview, BitOR($LVS_EX_FULLROWSELECT,$LVS_EX_GRIDLINES))
    _GUICtrlListView_AddColumn($listview, "Name", 200)
    _GUICtrlListView_AddColumn($listview, "Log", 100)
    _GUICtrlListView_AddColumn($listview, "Quota", 100)
    _GUICtrlListView_AddColumn($listview, "Date", 110)
    _GUICtrlListView_AddColumn($listview, "Status", 170)


GUISetState()


While 1
      Switch GUIGetMsg()
        Case $GUI_EVENT_CLOSE
                _ExcelBookClose($oExcel, 0)
                ExitLoop
            Case $insert
                $oExcel = _ExcelBookOpen($sFilePath1,0)
                $aArray1 = _ExcelReadSheetToArray($oExcel)
                _GUICtrlListView_AddArray($listview, $aArray1)
            EndSwitch

WEnd

Works almost as you ask! (but need some manipulations with array to shift left and lift up)

Edited November 10, 2009 by Godless

[KenzoIT]

It work well.

Many thanks for your support