array read and process through it

[joeloyzaga]

DOT.xls

I am an automated tester and need to read an excel spreadsheet which has multiple worksheets. I would like to be able to load the NAMED worksheet into an array and then read down this array and for each row and column I need to process the entry. by processing I mean that I also will have created an array from another worksheet and will look that up using the array entry being processed (will deal with that later). So what di I want?

read a nominated worksheet from a .xls into an array (this array/worksheet can have many rows and columns so the array is multidimensional but not known how many they are. can this be done?

Joe

i will attach the spreadsheet

Edited February 18, 2013 by joeloyzaga

[water]

Please have a look at the Excel UDF that comes with AutoIt. You will need functions _ExcelSheetActivate and _ExcelReadSheetToArray.

[joeloyzaga]

its mainly being able to open a worksheet by name......

[water]

Use _ExcelBookOpen to open the workbook then _ExcelSheetActivate to switch to the sheet by name. All other _Excel* functions work with the active worksheet you just switched to.

[joeloyzaga]

tried this but it doesn't work

but I also want to read down the spreadsheet and get the worksheet names to then read the worksheet

expandcollapse popup

#Region ;**** Directives created by AutoIt3Wrapper_GUI ****
#AutoIt3Wrapper_Outfile_type=a3x
#EndRegion ;**** Directives created by AutoIt3Wrapper_GUI ****
#include-once
#include <Excel.au3>
#include <Excel.au3>
$oExcel = _ExcelBookOpen("F:\datafiles\DOT\JLauto\Resources\DOT.xls")
$result = ReadSheet($oExcel, "ObjectRepository")
MsgBox(0,"title",$result)
_ExcelBookClose($oExcel)
Func ReadSheet($excel, $sheet)
Return String2DSplit(ExcelSheetToClip($excel, $sheet))
EndFunc   ;==>ReadSheet

Func String2DSplit($string, $rowDelimiter = @CRLF, $columnDelimiter = " ")
$lines = StringSplit(StringStripWS($string, 3), $rowDelimiter, 1)
$columnsNum = _StringCount($lines[1], $columnDelimiter) + 1
Dim $result[$lines[0]][$columnsNum] = [[0]]
For $i = 1 To $lines[0]
$columns = StringSplit($lines[$i], $columnDelimiter)
For $j = 1 To $columns[0]
$result[$i - 1][$j - 1] = $columns[$j]
Next
Next
Return $result
EndFunc   ;==>String2DSplit

Func _StringCount($string, $substring)
Local $i, $count = 0
For $i = 1 To StringLen($string)
If StringMid($string, $i, StringLen($substring)) = $substring Then $count = $count + 1
Next
Return $count
EndFunc   ;==>_StringCount

Func ExcelSheetToClip($excel, $sheet)
_ExcelSheetActivate($excel, $sheet)
ClipPut("")
Send("^{HOME}^a^c")
Do
Sleep(100)
Until ClipGet()
Return ClipGet()
EndFunc   ;==>ExcelSheetToClip

Edited February 18, 2013 by Melba23
Added code tags

[Melba23]

joeloyzaga,

When you post code please use Code tags - put [autoit] before and [/autoit] after your posted code. Then you get a scrolling box and syntax colouring as you can see above now I have added the tags.

M23

[water]

To get a list of the worksheet names use function _ExcelSheetList.

[joeloyzaga]

does it return an array?

[water]

Sure. For details please have a look in the help file.

[joeloyzaga]

so to re-cap

1.I open a spreadsheet and get the list of worksheets into an array i then parse that array to ensure there is a worksheet called "ObjectRepository" and create an array containing the data from this worksheet.

2.I then read the worksheet name that corresponds to the type of regression test I'm doing (for this instance lets use "Smoketest-test-KPI") into an array. Read down this array and process each cell for each row in left to right top to bottom order.

3.whenever I see a value inside a parenthesis I open that worksheet using the row number and process that row-this can be recursive.

4.Any values within quotes are actual calls to functions defined as VBScript and should be called via autoit (yet to figure out how...).

so where have I got up to with the info supplied?

kind of think point 1 but have not got all the code I need - and how slow would this be - would there be a smarter way?

[water]

It's not really clear to me what you want. I answered your questions but it seems this didn't clear the fog.

Let's do it the other way round. I ask questions and as soon as we have an answer we create the needed code. Then go on to the next question.

First question:

Do the names of the worksheets change (this means you have to read the sheet names and then process the returned array) or are the names fixed?

[joeloyzaga]

the names change as they are specific to the application to be tested - the names coincide with the title of the web page or the sub function to be performed

[water]

OK.

Then we need the following code:

#include <Excel.au3>
$oExcel = _ExcelBookOpen("F:\datafiles\DOT\JLauto\Resources\DOT.xls")
If @error Then Exit MsgBox(16, "Error", "_ExcelBookOpen returned error " & @error)
$aSheets = _ExcelSheetList($oExcel)
If @error Then Exit MsgBox(16, "Error", "_ExcelSheetList returned error " & @error)

Now we have an array with all sheet names. Which sheet should be processed? Edited February 19, 2013 by water

[joeloyzaga]

I made error - there will always be an "ObjectRepository" sheet but this should be sheet1. then I prompt for the application under test which lets say correspondes to the value "Smoketest-test-KPI" which is one of the worksheets and this is the one to process. within each cell being processed left to right rows 1 to max row. there may be other sheets to process if the cell contains "(" ")" e.g

(Authentication,4) which denotes Authentication worksheet row 4 to process.

this is like 4 answers but processing can be recursive

attaching the spreadsheet so you can see

DOT.xls

[water]

Something like this. The user is prompted for a sheet name then the sheet is read into an array. Processing of the array should then be done in function _ProcessSheet.

#include <Excel.au3>
$oExcel = _ExcelBookOpen("F:\datafiles\DOT\JLauto\Resources\DOT.xls")
If @error Then Exit MsgBox(16, "Error", "_ExcelBookOpen returned error " & @error)
$aSheets = _ExcelSheetList($oExcel)
If @error Then Exit MsgBox(16, "Error", "_ExcelSheetList returned error " & @error)
While 1
    $sSheet2Process = InputBox("Prompt", "Please enter the name of the sheet to process", "")
    If @error = 1 Then ExitLoop ; Cancel was pressed
    _ExcelSheetActivate($oExcel, $sSheet2Process) ; Activate the sheet
    If @error Then
        MsgBox(16, "Error", "_ExcelSheetActivate returned error " & @error)
    Else
        _ExcelReadSheetToArray($oExcel) ; Read the whole sheet into an array
        If @error Then
            MsgBox(16, "Error", "_ExcelSheetActivate returned error " & @error)
        Else
            _ProcessSheet()
        EndIf
    EndIf
WEnd

Func _ProcessSheet()

EndFunc   ;==>_ProcessSheet

[joeloyzaga]

that looks really good - give me a day to try it out - just did and its great - step 2 now?

[water]

Now function _ProcessSheet needs to be filled.

The code I've added loops through all the cells of the sheet. You have to add your processing there.

#include <Excel.au3>
Global $aSheetData
$oExcel = _ExcelBookOpen("F:\datafiles\DOT\JLauto\Resources\DOT.xls")
If @error Then Exit MsgBox(16, "Error", "_ExcelBookOpen returned error " & @error)
$aSheets = _ExcelSheetList($oExcel)
If @error Then Exit MsgBox(16, "Error", "_ExcelSheetList returned error " & @error)
While 1
    $sSheet2Process = InputBox("Prompt", "Please enter the name of the sheet to process", "")
    If @error = 1 Then ExitLoop ; Cancel was pressed
    _ExcelSheetActivate($oExcel, $sSheet2Process) ; Activate the sheet
    If @error Then
        MsgBox(16, "Error", "_ExcelSheetActivate returned error " & @error)
    Else
        $aSheetData = _ExcelReadSheetToArray($oExcel) ; Read the whole sheet into an array
        If @error Then
            MsgBox(16, "Error", "_ExcelSheetActivate returned error " & @error)
        Else
            _ProcessSheet($aSheetData)
        EndIf
    EndIf
WEnd

Func _ProcessSheet(ByRef $aSheetData)
    ; Process the data in the active sheet
    For $iRow = 1 To $aSheetData[0][0]
        For $iCol = 0 To $aSheetData[0][1] - 1
            If $aSheetData[$iRow][$iCol] = ... <== Code to process the entry needs to be added here
        Next
    Next
EndFunc   ;==>_ProcessSheet

[joeloyzaga]

thats beautiful - I'll add some processing and post back. I have the issue that while processing sheet A I read to process sheet B (which is like a sub function e.g. - process name detail that is part of the web page but separate input - is there a way to process worksheet and then if needed call another and so forth? - just a wish otherwise I guess I'ii load all subsheets into an array along with the sheet A data to be processed...) but first I'll process sheet A.

thanks Joe

[joeloyzaga]

expandcollapse popup

#Region ;**** Directives created by AutoIt3Wrapper_GUI ****
#AutoIt3Wrapper_Outfile_type=a3x
#EndRegion ;**** Directives created by AutoIt3Wrapper_GUI ****
#include <../lib/sda.core.au3>
#include <Excel.au3>
#include <Array.au3>
Global $aSheetData
Global $sBrowser
$sBrowser = "*firefox"
Global $oExcel
Global $aSheets
Global $sSheet2Process
$oExcel = _ExcelBookOpen("E:\datafiles\DOT\JLauto\Resources\DOT.xls")
If @error Then Exit MsgBox(16, "Error", "_ExcelBookOpen returned error " & @error)
$aSheets = _ExcelSheetList($oExcel)
If @error Then Exit MsgBox(16, "Error", "_ExcelSheetList returned error " & @error)
While 1
$sSheet2Process = InputBox("Prompt", "Please enter the name of the sheet to process", "")
If @error = 1 Then ExitLoop ; Cancel was pressed
_ExcelSheetActivate($oExcel, $sSheet2Process) ; Activate the sheet
If @error Then
     MsgBox(16, "Error", "_ExcelSheetActivate returned error " & @error)
Else
     $aSheetData = _ExcelReadSheetToArray($oExcel) ; Read the whole sheet into an array
     If @error Then
         MsgBox(16, "Error", "_ExcelSheetActivate returned error " & @error)
     Else
         _ProcessSheet($aSheetData)
     EndIf
EndIf
WEnd
Func _ProcessSheet(ByRef $aSheetData)
; Process the data in the active sheet
For $iRow = 3 To $aSheetData[0][0]
     For $iCol = 3 To $aSheetData[0][1] - 1
         If $aSheetData[$iRow][$iCol] = "" then
MsgBox(16, "nothing to process", $aSheetData[$iRow][$iCol],2)
else
;MsgBox(0, "found something to process", $aSheetData[$iRow][$iCol],2)
if StringInStr($aSheetData[$iRow][$iCol], "LaunchApp|")< 0 then
msgbox(0,"Found a url to open",$aSheetData[$iRow][$iCol],2)
_construct($sBrowser ,"http://www.google.com/")
_start()
_open("http://www.google.com")
_windowMaximize()
endif
EndIf
     Next
Next
EndFunc ;==>_ProcessSheet
_ExcelBookClose($oExcel)

tried to process the first cell and tried to assert that "LaunchApp|" exists in that cell but it doesn't find it and it should - huh?

[water]

Change line

if StringInStr($aSheetData[$iRow][$iCol], "LaunchApp|")< 0

to

if StringInStr($aSheetData[$iRow][$iCol], "LaunchApp|") > 0

StringInStr returns the position of the searchstring in the string so if found the returned value is always a positive number.