Bearpocalypse Posted August 2, 2013 Posted August 2, 2013 I have a sheet that has ticket numbers in one row and a description in another. I have my function pull from the spreadsheet and place the data into an array. I can get the information from the array and place it in my two different list boxes. The only problem I have is that the information is entered chronologically, IE ticket 1 might get paired with description 6. When I bring up the data via _arraydisplay, it all comes up correct. It's just when it's entered into the list box is when it gets sorted chronologically. Here is my code if anyone is interested in seeing it. Func f_GetTickets() GUICtrlSetData($Ticket, "") GUICtrlSetData($Desc, "") $oExcel = _ExcelBookOpen("Excel.xlsx") $oSheet = $oExcel.Worksheets(1) $oSheet.Activate ;Activate the appropriate sheet $oRange = $oSheet.UsedRange $oRange.SpecialCells($xlCellTypeLastCell).Activate $newRow = $oExcel.ActiveCell.Row ;Obtain the next open row ;$aArray = _ExcelReadSheetToArray($oExcel, 1, 1, 0, 0, True) ;Using Default Parameters, except Shifting Column (True) ;_ArrayDisplay($aArray, "Array with Column shifting") for $i = 1 To $newRow $Ticknum = _ExcelReadCell($oExcel, $i, 1) GUICtrlSetData($Ticket, $Ticknum) $Description = _ExcelReadCell($oExcel, $i, 2) GUICtrlSetData($Desc, $Description) Next _ExcelBookClose($oExcel) EndFunc
Moderators JLogan3o13 Posted August 2, 2013 Moderators Posted August 2, 2013 Can you please post an example of the spreadsheet, even if just a few rows? Not sure why you would see it differently between _ArrayDisplay and what you're inserting with GUICtrlSetData. "Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball How to get your question answered on this forum!
Bearpocalypse Posted August 2, 2013 Author Posted August 2, 2013 (edited) It seems the description(left column) is the one that is getting sorted now. I just reran and the ticket numbers came out fine. Edited August 2, 2013 by Bearpocalypse
Moderators JLogan3o13 Posted August 2, 2013 Moderators Posted August 2, 2013 I think what you're seeing is actually behavior from the GUICtrlSetData function. Notice that if you change "One" to "_One" it will come out correctly. It does not seem to sort on numbers, just text. Below is what I used, since I didn't know your GUI setup: #include <Array.au3> #include <Excel.au3> #include <GUIConstantsEx.au3> #include <WindowsConstants.au3> Local $msg GUICreate("Test", 300, 300) $Ticket = GUICtrlCreateList("", 10, 10, 100, 40) $Desc = GUICtrlCreateList("", 10, 120, 100, 40) $oExcel = _ExcelBookOpen(@DesktopDir & "\test.xls") $aArray = _ExcelReadSheetToArray($oExcel, 1, 1, 0, 0, True) ;Using Default Parameters, except Shifting Column (True) _ArrayDisplay($aArray, "Array with Column shifting") for $i = 1 To $aArray[0][0] $Ticknum = $aArray[$i][0] - You have your array, no more need to read from the excel file. $Description = $aArray[$i][1] GUICtrlSetData($Ticket, $Ticknum) GUICtrlSetData($Desc, $Description) Next GUISetState(@SW_SHOW) While 1 $msg = GUIGetMsg() Select Case $msg = $GUI_EVENT_CLOSE ExitLoop EndSelect WEnd _ExcelBookClose($oExcel) GUIDelete() "Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball How to get your question answered on this forum!
Bearpocalypse Posted August 2, 2013 Author Posted August 2, 2013 I entered what you have and I still get the sorted text. I'll toss my whole code in so you can see what I'm doing. expandcollapse popup#include <GUIConstantsEx.au3> #include <GUIListBox.au3> #include <WindowsConstants.au3> #include <Excel.au3> #include <EditConstants.au3> #include <StaticConstants.au3> #include <Array.au3> #include <GUIConstantsEx.au3> Opt("GUIOnEventMode",-1) ;Enables events, I.E. exiting application from file-exit. #Region ### START Koda GUI section ### Form= $Form1 = GUICreate("Ticket Tracker", 364, 335, 192, 124) GUISetOnEvent($gui_event_close, "f_exit") $Ticket = GUICtrlCreateList("", 24, 80, 73, 201) $Desc = GUICtrlCreateList("", 104, 80, 241, 201) $filemenu = GUICtrlCreateMenu("File") ;Creates the File menu. $Refresh = GUICtrlCreateMenuItem("Refresh", $filemenu) GUICtrlSetOnEvent(-1, "f_GetTickets") $ExitItem = GUICtrlCreateMenuItem("Exit", $filemenu) ;Creates the exit submenu under File. GUICtrlSetOnEvent(-1, "f_exit") ;calls the function to close the appplication via file-exit. $MakeTick = GUICtrlCreateInput("", 32, 16, 97, 21) $MakeDesc = GUICtrlCreateInput("", 136, 16, 169, 21) $Write = GUICtrlCreateButton("Write", 32, 40, 49, 25) GUICtrlSetOnEvent(-1, "f_write") $Clear = GUICtrlCreateButton("Clear", 80, 40, 49, 25) GUICtrlSetOnEvent(-1, "f_reset") $Delete = GUICtrlCreateButton("Remove all data", 262, 285) GUICtrlSetOnEvent(-1, "f_Delete") GUISetState(@SW_SHOW) #EndRegion ### END Koda GUI section ### ;---------------------------- Func f_GetTickets() GUICtrlSetData($Ticket, "") GUICtrlSetData($Desc, "") local $msg $oExcel = _ExcelBookOpen("Excel.xlsx") $oSheet = $oExcel.Worksheets(1) $oSheet.Activate ;Activate the appropriate sheet $oRange = $oSheet.UsedRange $oRange.SpecialCells($xlCellTypeLastCell).Activate $newRow = $oExcel.ActiveCell.Row ;Obtain the next open row $aArray = _ExcelReadSheetToArray($oExcel, 1, 1, 0, 0, True) ;Using Default Parameters, except Shifting Column (True) ;_ArrayDisplay($aArray, "Array with Column shifting") for $i = 1 To $aArray[0][0] $Ticknum = $aArray[$i][0]; - You have your array, no more need to read from the excel file. $Description = $aArray[$i][1] GUICtrlSetData($Ticket, $Ticknum) GUICtrlSetData($Desc, $Description) Next GUISetState(@SW_SHOW) While 1 $msg = GUIGetMsg() Select Case $msg = $GUI_EVENT_CLOSE ExitLoop EndSelect WEnd _ExcelBookClose($oExcel) GUIDelete() EndFunc ;---------------------------- Func f_write() $T1 = guictrlread($MakeTick) $D1 = GUICtrlRead($makedesc) $oExcel = _ExcelBookOpen("Excel.xlsx") $oSheet = $oExcel.Worksheets(1) $oSheet.Activate ;Activate the appropriate sheet $oRange = $oSheet.UsedRange $oRange.SpecialCells($xlCellTypeLastCell).Activate $newRow = $oExcel.ActiveCell.Row ;Obtain the next open row ; Read column A to an array Global $aData = _ExcelReadArray($oExcel, 1, 1, 10, 1, 1) ; Read column B to an array Global $bData = _ExcelReadArray($oExcel, 1, 2, 10, 1 ,1) ; find the last used cell in this column Global $iLastUsed = 0, $iNextRow = 0 For $n = UBound($aData) - 1 To 1 Step -1 If StringStripWS($aData[$n], 8) <> "" Then $iLastUsed = $n ExitLoop EndIf Next If $iLastUsed Then $iNextRow = $iLastUsed + 1 EndIf For $bn = UBound($bData) - 1 To 1 Step -1 If StringStripWS($bData[$bn], 8) <> "" Then $iLastUsed = $bn ExitLoop EndIf Next If $iLastUsed Then $iNextRow = $iLastUsed + 1 EndIf _ExcelWriteCell($oExcel, $T1, $iNextRow, 1) _ExcelWriteCell($oExcel, $D1, $iNextRow, 2) _ExcelBookSave($oExcel) _ExcelBookClose($oExcel) EndFunc ;---------------------------- func f_Delete() $oExcel = _ExcelBookOpen("Excel.xlsx") _ExcelColumnDelete($oExcel, 1, 1) _ExcelColumnDelete($oExcel, 1, 2) _ExcelBookSave($oExcel) _ExcelBookClose($oExcel) EndFunc ;---------------------------- Func f_exit() Exit EndFunc ;---------------------------- func f_reset() guictrlsetdata($maketick, "") GUICtrlSetData($makedesc, "") EndFunc ;---------------------------- While 1 $nMsg = GUIGetMsg() Switch $nMsg Case $GUI_EVENT_CLOSE Exit EndSwitch WEnd
Solution soonyee91 Posted August 3, 2013 Solution Posted August 3, 2013 Bearpocalpse, I face the same problem like you last time. If you use _arraydisplay() and the results are in correct order that means your array is sorted in that order. The problem you face is when you want to display in listbox it help you sort again. The best way is you disable sorting in your listbox. I believe you create your listbox with default value (default value contain sorting which will sort your item but will not affect your array) In my previous attempt. I used this code: $hListBox = GUICtrlCreateList("", 10, 20, 390, 150, BitOR($WS_BORDER, $WS_VSCROLL, $WS_HSCROLL)) you can check help file for certain listbox criteria you must use BitOr() to specify which listbox criteria you want. As you can see my lisbox criteria is I want vertical and horizontal scroll and thin line border only Hope my explaination helps you!
Bearpocalypse Posted August 3, 2013 Author Posted August 3, 2013 (edited) Soonyee91, Once again you guys prove how awesome you are. BitOr worked! Thanks so much for your help, and you too JL. Edited August 3, 2013 by Bearpocalypse
soonyee91 Posted August 4, 2013 Posted August 4, 2013 Bearpocalypse, No problem. Its my pleasure to help you.
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now