Jump to content
Sign in to follow this  
DStraathof

Conditional select from _arraydisplay (Excel)

Recommended Posts

Hi all,

For a new test solution I'm using an Excel file with user credentials (password protected on disk) to be able to test a webportal using one of these users. Now I need to set the record I'd like to use each time I run my code.

Does anyone know how to select one of the row results from the read Excel sheet, using _arraydisplay, and pass these data back to the script to continue the rest of my code using the selected user?

#include <Array.au3>
#include <Excel.au3>

Local $oE = _Excel_Open()

Local $sWorkbook = @ScriptDir & "\Credentials.xls"
Local $oWB = _Excel_BookOpen($oE, $sWorkbook, Default, Default, "Very-Secret_PW!")

Local $aResult = _Excel_RangeRead($oWB, Default, $oWB.ActiveSheet.Usedrange.Columns("A:B"))

local $aFinal[UBound($aResult)][2]

for $1 = 0 to UBound($aResult) - 1
    $aFinal[$1][0] = $aResult[$1][0] ; Username
    $aFinal[$1][1] = $aResult[$1][1] ; Password
next


_arraydisplay($aFinal,'Final',default,default,default,'A|B')

_Excel_Close($oE)

The Excel file contains 2 columns: A=Username; B=Password.

Thanks for your help!

Share this post


Link to post
Share on other sites

Not sure what you meant by that statement, but is is pretty easy to add to the posted code. ;) 
Shout when you have questions.

Jos

Edited by Jos

SciTE4AutoIt3 Full installer Download page   - Beta files       Read before posting     How to post scriptsource   Forum etiquette  Forum Rules 
 
Live for the present,
Dream of the future,
Learn from the past.
  :)

Share this post


Link to post
Share on other sites

Just use the Excel MATCH function.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

Also in AutoIt you don't need a loop to duplicate an array: in your code you could just have used Local $aFinal = $aResult (provided you really need $aFinal at all).


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

May be you want something like that (untested) :

#include <Array.au3>
#include <Excel.au3>

Local $UserName = "UsernameIwant"

MsgBox (0,"",_SearchUser ($UserName))

Func _SearchUser ($UserName)

  Local $oE = _Excel_Open()
  Local $sWorkbook = @ScriptDir & "\Credentials.xls"
  Local $oWB = _Excel_BookOpen($oE, $sWorkbook, Default, Default, "Very-Secret_PW!")

  Local $aResult = _Excel_RangeRead($oWB, Default, $oWB.ActiveSheet.Usedrange.Columns("A:B"))
  _Excel_Close($oE)
  
  _arraydisplay($aResult,'Final',default,default,default,'A|B')
  Local $iFound = _arraySearch ($aResult, $UserName, 0, 0, 1, 2, 0, 0)
  If @error then Return SetError (1,0,-1)
  Return $aResult[$iFound][0] & "|" & $aResult[$iFound][1]
  
EndFunc

 

Edited by Nine

Share this post


Link to post
Share on other sites

Thanks all for the tips 'n' tricks. 🙂 It works!!

This is what I ended up with:

#include <Array.au3>
#include <Excel.au3>
#include <GUIConstantsEx.au3>

; Create Excel object
Local $oE = _Excel_Open()

; Open Workbook (password-protected)
Local $sWorkbook = @ScriptDir & "\Credentials.xls"
Local $oWB = _Excel_BookOpen($oE, $sWorkbook, Default, Default, "Very-Secret_PW!")

; Select range A:B for 'Username:Password'
Local $aResult = _Excel_RangeRead($oWB, Default, $oWB.ActiveSheet.Usedrange.Columns("A:B"))

; Create a GUI with various controls.
Local $hGUI = GUICreate("Gebruikers", 300, 200)

; Create a combobox control.
Local $idComboBox = GUICtrlCreateCombo("Select user", 10, 10, 185, 20)
Local $idButton_Close = GUICtrlCreateButton("Close", 210, 170, 85, 25)

for $1 = 0 to UBound($aResult) - 1
    ; Add usernames to the combobox.
    GUICtrlSetData($idComboBox, $aResult[$1][0]&"|", "Select user")
next

; Display the GUI.
GUISetState(@SW_SHOW, $hGUI)

; Loop until the user exits.
While 1
    Switch GUIGetMsg()
        Case $GUI_EVENT_CLOSE, $idButton_Close
            ExitLoop
        
        ; Read the selected value
        Case $idComboBox
            $sComboRead = GUICtrlRead($idComboBox)

    EndSwitch
WEnd

; Get the index from the Excel array to select the row for the selected user
Local $iFound = _arraySearch ($aResult, $sComboRead, 0, 0, 1, 2, 0, 0)

; Fill local variables with the correct set.
Local $sUsername = $aResult[$iFound][0]
Local $sPassword = $aResult[$iFound][1]

; Delete the previous GUI and all controls.
GUIDelete($hGUI)

; Close Excel
_Excel_Close($oE)

 

Edited by DStraathof

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  

  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...