Sign in to follow this  
Followers 0
joeloyzaga

array read and process through it

31 posts in this topic

#1 ·  Posted (edited)

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 by joeloyzaga

Share this post


Link to post
Share on other sites



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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

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

#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 by Melba23
Added code tags

Share this post


Link to post
Share on other sites

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


Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind._______My UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

does it return an array?

Share this post


Link to post
Share on other sites

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

1 person likes this

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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?

1 person likes this

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

#13 ·  Posted (edited)

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 by water
1 person likes this

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

#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?

Share this post


Link to post
Share on other sites

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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

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  
Followers 0