loading different range of names into different tabs

Siwa · September 25, 2020

Hi autoit community, I have come to a problem.

I have a variable name excel list, and I want to load them into different tabs ( I did load them all once, and because of resource heavy and lots of graphical controls loaded with names there was a lag when scrolling ).

The number of tabs are variable and If I load all the names once, the gui will freeze again when scrolling, so I decided I can load them with tabs, and only load limited range in each tab when clicked on each tab.

I have managed to load variable tab numbers each one with limited name ranges, but on each tab click, the whole list reloads not the selected tab and makes it laggy again and screen freezes for a few seconds. I'll appreciate it if you can put me in the right direction.

This is

#NoTrayIcon
#include <Excel.au3>
#include <MsgBoxConstants.au3>
#include <Array.au3>
#include <GUIConstantsEx.au3>
#include <GUIListBox.au3>
#include <WindowsConstants.au3>
#include <FTPEx.au3>
#include <GuiButton.au3>
#include <EditConstants.au3>
#include <WinAPI.au3>
#include <WinAPIGdi.au3>
#include <ScrollBarConstants.au3>
#include <StructureConstants.au3>
#include <FontConstants.au3>
#include <GUIConstantsEx.au3>
Opt("GUIOnEventMode", 1)

Global $PreviousiIndex,$iIndex,$max_patient_Number,$Total_Patient_Groups,$tab,$tab_item,$current_tab
Global $TCS_RIGHT , $TCS_FLATBUTTONS , $TCS_BUTTONS
Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\test.xlsx",False,True)
Global $aWorkSheets = _Excel_SheetList($oWorkbook)
$oWorkbook.Sheets(2).Activate
Global $Total_Rows = _Excel_RangeRead($oWorkbook, Default , $oWorkbook.ActiveSheet.Usedrange.Columns("C:C"),1)
Global $Total_Rows_Count = Ubound($Total_Rows)
Global $Names = _Excel_RangeRead($oWorkbook, 2, "B2:C"&Ubound($Total_Rows), 2)
Global $aGroupNames[UBound($Names)][5]

CreateGUI()

Func CreateGUI()
    $hGUI = GUICreate("My GUI list",1000,700, -1, -1, BitOR($WS_MINIMIZEBOX, $WS_CAPTION, $WS_POPUP, $WS_SYSMENU, $WS_SIZEBOX))
    $tab = GUICtrlCreateTab(10, 10, 990, 790)
    $max_peolpe_Number = UBound($Names, 1)-1 ; around 150 to 200 names
    $Total_peolpe_Groups = Ceiling($max_peolpe_Number/18)
    $current_tab = 0
    Do
        $tab_item = GUICtrlCreateTabItem($current_tab)
        For $iIndex = ($current_tab*18) to (($current_tab+1)*18)
            If $iIndex < $max_patient_Number Then
                $aGroupNames[$iIndex][0] = $iIndex & "|" & $Names[$iIndex][1] & "|" & $Names[$iIndex][0]
                $aGroupNames[$iIndex][1] = GUICtrlCreateGroup($iIndex+1 & " - " & $Names[$iIndex][0] &" "& $Names[$iIndex][1], 667, 50+(($iIndex-($current_tab*18))*0.28)*110, 325, 87,-1,0x400000)
                GUICtrlSetFont(-1, 14, $FW_BOLD , $GUI_FONTNORMAL , "Arial")
                $aGroupNames[$iIndex+1][1] = GUICtrlCreateGroup($iIndex+2 & " - " & $Names[$iIndex+1][0] &" "& $Names[$iIndex+1][1], 334, 50+(($iIndex-($current_tab*18))*0.28)*110, 325, 87,-1,0x400000)
                GUICtrlSetFont(-1, 14, $FW_BOLD , $GUI_FONTNORMAL , "Arial")
                $aGroupNames[$iIndex+2][1] = GUICtrlCreateGroup($iIndex+3 & " - " & $Names[$iIndex+2][0] &" "& $Names[$iIndex+2][1], 4, 50+(($iIndex-($current_tab*18))*0.28)*110, 325, 87,-1,0x400000)
                GUICtrlSetFont(-1, 14, $FW_BOLD , $GUI_FONTNORMAL , "Arial")
                GUICtrlCreateLabel("از تاریخ", 940, 75+(($iIndex-($current_tab*18))*0.28)*110,45)
                GUICtrlSetFont(-1, 12, $FW_MEDIUM , $GUI_FONTNORMAL , "Arial")
                GUICtrlCreateLabel("از تاریخ", 605, 75+(($iIndex-($current_tab*18))*0.28)*110,45)
                GUICtrlSetFont(-1, 12, $FW_MEDIUM , $GUI_FONTNORMAL , "Arial")
                GUICtrlCreateLabel("از تاریخ", 270, 75+(($iIndex-($current_tab*18))*0.28)*110,45)
                GUICtrlSetFont(-1, 12, $FW_MEDIUM , $GUI_FONTNORMAL , "Arial")
                $aGroupNames[$iIndex][2] = GUICtrlCreateDate("2020/10/25", 850, 75+(($iIndex-($current_tab*18))*0.28)*110, 85, 20)
                $aGroupNames[$iIndex+1][2] = GUICtrlCreateDate("2020/10/25", 515, 75+(($iIndex-($current_tab*18))*0.28)*110, 85, 20)
                $aGroupNames[$iIndex+2][2] = GUICtrlCreateDate("2020/10/25", 180, 80+(($iIndex-($current_tab*18))*0.28)*110, 85, 20)
                $aGroupNames[$iIndex][3] = GUICtrlCreateEdit('', 950, 110+(($iIndex-($current_tab*18))*0.28)*110, 30, 20, $ES_CENTER + $ES_NUMBER )
                GUICtrlCreateLabel(" روز ", 900, 110+(($iIndex-($current_tab*18))*0.28)*110,30)
                GUICtrlSetFont(-1, 12, $FW_BOLD , $GUI_FONTNORMAL , "Arial")
                $aGroupNames[$iIndex+1][3] = GUICtrlCreateEdit('15', 570, 110+(($iIndex-($current_tab*18))*0.28)*110, 30, 20, $ES_CENTER + $ES_NUMBER )
                $aGroupNames[$iIndex+1][3] = GUICtrlCreateEdit('', 625, 110+(($iIndex-($current_tab*18))*0.28)*110, 30, 20, $ES_CENTER + $ES_NUMBER )
                GUICtrlCreateLabel(" روز ", 597, 110+(($iIndex-($current_tab*18))*0.28)*110,30)
                GUICtrlSetFont(-1, 12, $FW_BOLD , $GUI_FONTNORMAL , "Arial")
                $aGroupNames[$iIndex+2][3] = GUICtrlCreateEdit('', 295, 110+(($iIndex-($current_tab*18))*0.28)*110, 30, 20, $ES_CENTER + $ES_NUMBER )
                GUICtrlCreateLabel(" روز ", 260, 113+(($iIndex-($current_tab*18))*0.28)*110,30)
                GUICtrlSetFont(-1, 12, $FW_BOLD , $GUI_FONTNORMAL , "Arial")
                $aGroupNames[$iIndex][4] = GUICtrlCreateButton( "ثبت", 850, 110+(($iIndex-($current_tab*18))*0.28)*110, 50, 22)
                $aGroupNames[$iIndex+1][4] = GUICtrlCreateButton( "ثبت", 515, 110+(($iIndex-($current_tab*18))*0.28)*110, 50, 22)
                $aGroupNames[$iIndex+2][4] = GUICtrlCreateButton( "ثبت", 200, 110+(($iIndex-($current_tab*18))*0.28)*110, 50, 22)

                Local $IX = 10
                Local $IY = 10
                Dim $iRcntrls[31][3]
                For $i = 1 To 30
                    If $IX = 290 Then
                        $IX = 10
                        $IY += 40
                        If $i > 28 Then
                            $IX = 290
                            $IY -= 40
                        EndIf
                    EndIf
                    $iRcntrls[$i][0] = GUICtrlCreateGraphic($IX*3.5/7+665, $IY*3.5/7 + (55+(($iIndex-($current_tab*18))*0.28)*110), 15, 15)
                    $iRcntrls[$i][1] = GUICtrlGetHandle($iRcntrls[$i][0])
                    GUICtrlSetBkColor(-1, 0xffffff)
                    GUICtrlCreateLabel($i, $IX*3.5/7 + 2+ 665, $IY*3.5/7 + 2 + (55+(($iIndex-($current_tab*18))*0.28)*110), 12, 12)
                    GUICtrlSetBkColor(-1, 0xffffff)
                    $IX += 40
                Next

                Local $IX = 10
                Local $IY = 10
                Dim $iRcntrls[31][3]
                For $i = 1 To 30
                    If $IX = 290 Then
                        $IX = 10
                        $IY += 40
                        If $i > 28 Then
                            $IX = 290
                            $IY -= 40
                        EndIf
                    EndIf
                    $iRcntrls[$i][0] = GUICtrlCreateGraphic($IX*3.5/7+330, $IY*3.5/7 + (55+(($iIndex-($current_tab*18))*0.28)*110), 15, 15)
                    $iRcntrls[$i][1] = GUICtrlGetHandle($iRcntrls[$i][0])
                    GUICtrlSetBkColor(-1, 0xffffff)
                    GUICtrlCreateLabel($i, $IX*3.5/7 + 2+330, $IY*3.5/7 + 2 + (55+(($iIndex-($current_tab*18))*0.28)*110), 12, 12)
                    GUICtrlSetBkColor(-1, 0xffffff)
                    $IX += 40
                Next

                Local $IX = 10
                Local $IY = 10
                Dim $iRcntrls[31][3]
                For $i = 1 To 30
                    If $IX = 290 Then
                        $IX = 10
                        $IY += 40
                        If $i > 28 Then
                            $IX = 290
                            $IY -= 40
                        EndIf
                    EndIf
                    $iRcntrls[$i][0] = GUICtrlCreateGraphic($IX*3.5/7+5, $IY*3.5/7 + (55+(($iIndex-($current_tab*18))*0.28)*110), 15, 15)
                    $iRcntrls[$i][1] = GUICtrlGetHandle($iRcntrls[$i][0])
                    GUICtrlSetBkColor(-1, 0xffffff)
                    GUICtrlCreateLabel($i, $IX*3.5/7 + 2+5, $IY*3.5/7 + 2 + (55+(($iIndex-($current_tab*18))*0.28)*110), 12, 12)
                    GUICtrlSetBkColor(-1, 0xffffff)
                    $IX += 40
                Next
            $iIndex = $iIndex + 2
            EndIf
        Next
        $PreviousiIndex = $iIndex
        Global $iIndex = 0
        $iIndex = $PreviousiIndex + ($current_tab*18)
        $current_tab = $current_tab + 1
    Until $current_tab = $Total_people_Groups ; For tab
    GUICtrlCreateTabItem("")

GUISetState(@SW_SHOW)

test.rar

Edited September 25, 2020 by Siwa

Siwa · September 25, 2020

I'll appreciate I a lot if anyone can help with this.

Many Many thanks in advance. 🙏

zeenmakr · September 26, 2020

is this a complete code?

there is no while loop

CreateGUI()
While Sleep(10)
WEnd

missed closing func

Func CreateGUI()
;...
EndFunc

plus var $Total_people_Groups isn't defined

Melba23 · September 26, 2020

Siwa,

It looks to me as if you are running into the same problem as in your previous script - too many controls in the one GUI. The fact that you are trying to separate them into separate tabs does reduce the total number of controls you are creating.

As I mentioned before, I suggest using a simple ListView to display the data with a separate section holding the user-adjustable controls which can then be applied to the ListView contents. I will try and come up with something simple to illustrate this concept.

M23

Melba23 · September 26, 2020

Siwa,

Looking at your earlier script (much easier to follow than this one!) I see you were trying to create a series of identical groups which each hold a date control, a button, a label and an edit control. But every other group there is also a block of 28 graphic controls (which I suspect are the root of your problem as they are notoriously resource heavy). I imagine that the user is expected to manipulate each of these controls to set the condition for each of the groups - is that correct? If so, what is the function of the block of graphics as it is only present for every other group? And while the function of the date control and the input seems reasonably clear, what should happen when the button is pressed? A separate function is called?

it would greatly help me devise something different to help you if I had a better idea of exactly what is supposed to happen within the script - a simple visual GUI does not offer a lot of guidance!

M23

Siwa · September 26, 2020

1 minute ago, Melba23 said:

Siwa,

Looking at your earlier script (much easier to follow than this one!) I see you were trying to create a series of identical groups which each hold a date control, a button, a label and an edit control. But every other group there is also a block of 28 graphic controls (which I suspect are the root of your problem as they are notoriously resource heavy). I imagine that the user is expected to manipulate each of these controls to set the condition for each of the groups - is that correct? If so, what is the function of the block of graphics as it is only present for every other group? And while the function of the date control and the input seems reasonably clear, what should happen when the button is pressed? A separate function is called?

it would greatly help me devise something different to help you if I had a better idea of exactly what is supposed to happen within the script - a simple visual GUI does not offer a lot of guidance!

M23

Thanks for your time @Melba23, yes, the graphics control is showing when a customer must come back, and the graphics should be clickable to be erased if any mistake happened by the operator. and when the button pressed, the dates inputted in the edit box are saved in a log, the dates are colored and gives the operator a quick view for later, and the last function of the button is to save all those changes in the excel file.

All these functions are done once, at the beginning of the month by the operator. but there might be some mistakes or some date changes later.

This is the main idea. If it's not clear tell me to explain more.

Melba23 · September 26, 2020

Siwa,

I am still unclear about the relationship between the date control and the graphics. Should the graphics reflect the date selected?

M23

Melba23 · September 26, 2020

Siwa,

Here is a very simple script to show you what I suggest might be a good way to proceed. Data is read from Excel and loaded into a ListView - yo ucan have as many items as you want as Windows does all the heavy work of scrolling. Then when you select a line the date from that line is set into the date control - you can do the same thing for any other data on the line if required. Then when the operator has finished setting the new date and inserted something into the edit you can save the new data into the ListView. A final "Save" button will save the new data to Excel - although this could be added to the earlier function if required.

#include <GUIConstantsEx.au3>
#include <WindowsConstants.au3>
#include <EditConstants.au3>
#include <GuiListView.au3>
#include <Date.au3>

Global $aNames[100][2]
Local $Y, $M, $D

; Simulate reading data from Excel
For $i = 0 To UBound($aNames) - 1
    $aNames[$i][0] = "Name:A" & $i
    $aNames[$i][1] = "Name:B" & $i
Next

Global $aGroupNames[UBound($aNames)][5]

$hGUI = GUICreate("My GUI list", 800, 500)

$cListView = GUICtrlCreateListView("Name:A|NameB|Date|Label 1|Input|Item 3|Item 4", 10, 10, 780, 300)
For $i = 0 To 6
    _GUICtrlListView_SetColumnWidth($cListView, $i, 120)
Next

; Fill list view from data
For $i = 0 To UBound($aNames) - 1
    ; Simulate reading datae from Excel
    $sNewDate = _DateAdd('d', Random(2, 28,1), _NowCalcDate())
    GUICtrlCreateListViewItem($aNames[$i][0] & "|" & $aNames[$i][1] & "|" & $sNewDate, $cListView)
Next


$cDate = GUICtrlCreateDate("", 10, 350, 120, 20)

$cSet = GUICtrlCreateButton("Set", 10, 390, 80, 20)

$cLabel = GUICtrlCreateLabel("", 100, 390, 80, 20)
GUICtrlSetBkColor($cLabel, 0xC4C4C4)

$cInput = GUICtrlCreateInput("", 200, 390, 30, 20, BitOR($ES_CENTER, $ES_NUMBER))

$cSave = GUICtrlCreateButton("Save To Excel", 10, 450, 120, 30)

GUISetState()

; Windows handler to detect ListView selection change
GUIRegisterMsg($WM_Notify, "_WM_Notify")

While 1

    Switch GUIGetMsg()
        Case $GUI_EVENT_CLOSE
            Exit
        Case $cSet
            _Action()
        Case $cSave
            MsgBox($MB_SYSTEMMODAL, "Save", "Code to save Excel file")
    EndSwitch

WEnd

Func _Action()

    ; Get index of selected item
    $iSelected = _GUICtrlListView_GetSelectedIndices($cListView)

    _GUICtrlListView_SetItemText($cListView, $iSelected, GUICtrlRead($cDate), 2)
    _GUICtrlListView_SetItemText($cListView, $iSelected, GUICtrlRead($cInput), 4)

EndFunc

Func _WM_Notify($hWnd, $iMsg, $wParam, $lParam)

    ; Struct = $tagNMHDR and "int Item;int SubItem" from $tagNMLISTVIEW
    Local $tStruct = DllStructCreate("hwnd;uint_ptr;int_ptr;int;int", $lParam)
    If @error Then Return

    Local $hLV = DllStructGetData($tStruct, 1)
    Local $iItem = DllStructGetData($tStruct, 4)
    Local $iCode = BitAND(DllStructGetData($tStruct, 3), 0xFFFFFFFF)

    If $iCode = $LVN_ITEMCHANGED Then
        ; Read dtae and set into the date control
        $aContent = _GUICtrlListView_GetItemTextArray($cListView, $iItem)
        GUICtrlSetData($cDate, $aContent[3])
    EndIf

EndFunc

Does this look useful?

M23

Siwa · September 26, 2020

it is very light and loades much faster, but the customers may return every day, and my main problem in the graphics control in one look, can I use on of the items ( columns ) to show dates ? ( unfortunately this part is the most important part ).

And so many thanks for your time, and your help. You are a great help. 🙏

Edited September 27, 2020 by Melba23
Quote removed

Siwa · September 26, 2020

Maybe I can use your "GUIListViewEx" UDF for this purpose ?

Edited September 26, 2020 by Siwa

Melba23 · September 27, 2020

Siwa,

My GUIListViewEx UDF would seem a very good choice to use in your project as it allows you to edit all the cells of the ListView by a simple double-click. Give me a list of the columns you would want to see in the ListView and what you want displayed in them and I will set up a basic example to show you how it might work.

M23

P.S. When you reply in future, please use the "Reply to this topic" button at the top of the thread or the "Reply to this topic" editor at the bottom rather than the "Quote" button - I know what I wrote and it just pads the thread unnecessarily. Thanks in advance for your cooperation.

Siwa · September 27, 2020

Sorry for not following forum rules. I do apologize.

And big big thanks for your help, you are making my days joy-able again.

I want this columns :

- Name -Family name -Numbers(an int between 1 to 50)(only this one can be editable) -Dates(Next return date ) -Month Dates(from 1 to 31 )

The only important thing is the "Month Dates" :

- which must be colored according to the dates the customer is not supposed to return.

- not being visible by default, only can be seen when row is highlighted. ( this can make the whole GUI more clean if possible )

A dynamic search input, which shows only the rows needed ( because of future references can be easier between huge number of customers )

(The Submit Button(to apply changes and save ini) and the full month view can be seen in lower part of the GUI below the listview according to the customer.)

These all must be loaded from an ini, which I have to work on this, later.

Melba23 · September 27, 2020

Siwa,

So the date elements are not editable? Why then did you have a date control in the original GUI? The UDF allows you to have a date control in an editable element - I will add one to show you.

And I do not understand the "Month Dates" column - if you have a full date in "Dates" why do you need a further "Day of Month" indication? Colouring the element is no problem - but what is the relationship between the 2 columns? If you can provide the link logic and the required colours I can try and get them correctly displayed

M23

Edited September 27, 2020 by Melba23
Missed a "not"

Siwa · September 27, 2020

the dates column can be erased and then be re-writen. but if I can click on a specific date and then only that period (from his last visit to us ) can be erased it would be great. ( because sometimes customers come back earlier, the must not, but they do )

The date column shows only the date when the customer must come back and it is visible by default. ( it is not an overview of the month ). For example mr.X come to the shop on 1/10/2020, and buys a service, suitable for his use for 12 days. so, he must not come back earlier then 12/10/2020. the "Date' column only shows 12/10/2020. But in "Month Date" I can see an overview of his usage of the services in a full month. ( they can use the service on a predefined table given to them ).

Melba23 · September 27, 2020

Siwa,

Fine, so you want an editable date control in the "Date" column - not a problem.

But I still have problems with your description of the "Month Date" column. How is it linked to the rest of the ListView? Does it pull its data from the Excel sheet? How is it presented - as a list of dates?

And how do you want this "full month usage" displayed? You cannot put a large graphic in the ListView itself - but you could have something of the sort you had originally in the main GUI under the ListView which could be dynamically filled depending on the selected item. Which returns to the questions above: where does this data come from and how is it presented?

M23

Siwa · September 27, 2020

the "Month Date" is filled at the beginning of the month, and later on by the operator. So On each day a list of names are represented to he operator, so he/she knows when the customer needs to come back, and at the end of the month ( or when the customer complains ) the operator can have a quick look at the dates, and down below it will be larger for quick review ( or detailed view ).

When the submit button pressed, according to the input control(that is putted in by the operator at the beginning of the month, or by the costumers request) , those days are colored, and the day after, is his/her returning date. these dates are then later saved in an ini, and are loaded later each time the program runs. the presentation of dates are better to be by numbers in a row ( 1 2 3 4 5 ... ) or anyway you suggest is best for a quick view on the list. I was thinking maybe some columns without labels (30 ) and the dates field can be colored by column number ( only when the row is selected - and by default the dates columns ( which are without any labels ) are empty and a white screen ).

The date choosing control is for the operator to set the date the costumer must come back. Like : I'm the operator and at the beginning of the month I set something like this :

today is ( 1/10/2020 )

the customer comes at 3/10/2020, he arranges a date to come back in 12 days, so his return date is 15/10/2020, he comes back maybe at 16/10/2020 and he arranges to come back in 5 days so, after 5 days ( 21/10/2020 ) he comes back, and arranges to come back after 2 days, but he comes back at 29/10/2020 ... and so on .

The "date" column depending on todays date in the PC is set to his next returning date. the "Month Date" is colored at and during the dates he has the arrangements, according to his/her schedule. and when he misses a day, the color is left white. But maybe he comes a day or 2 days earlier so, his/her program schedule must be edited accordingly ( by deleting the selected time duration( for example from 3/10/2020 to 15/10/2020 must be deleted and re putted by the submit column because he has returned on 14/10/2020) on the "Month Date" column)

Melba23 · September 28, 2020

Siwa,

Sorry, but that could be written in Klingon for all the sense it makes to me.

M23

Siwa · September 28, 2020

thanks, but, what is "Klingon"

if it means you can't help me, please just guide me how to colour the rows on specific columns ?

Edited September 28, 2020 by Siwa

Melba23 · September 28, 2020

Siwa,

"Klingon" is the language of the Klingon race in the Star Trek universe - and I used that term to mean that I have not got the least idea of what you were trying to explain in your earlier post. if you want to try again I would be happy to read it, but I cannot promise to do any better. In my opinion if I am having that much difficulty understanding your concept of how the GUI should work then you need to simplify your vision considerably. Anything that complicated is unlikely to be easy to code or display. But of course, YMMV.

As to colouring elements of the ListView I suggest you download my GUIListViewEx UDF (the link is in my sig) and look in articular at the Example_6 script which has all sorts of coloured elements. i would be very happy to help you get the colouring working - if you can provide a clear explanation of what you want to happen to alter the colour of the specific cell.

M23

Siwa · September 29, 2020

Can I explain more in the messages ?

loading different range of names into different tabs

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members