I’ve a bunch of jobs in the spread sheet and i need to verify a particular column in the spreadsheet for all the jobs whether it contains the specified value or not. And i dont even have the count of jobs…
Please help me out to automate this scenario using Egg Plant…
If the spreadsheet containing the data is Excel running on the Eggplant machine, you can use AppleScript to access it, as described in this example. Basically, here’s the code that reads the spreadsheet (depending on the version of Excel you may need to change “usedrange” to “used range” as two words):
do applescript {{
tell application "Microsoft Excel"
open "Macintosh HD:path:to:your:spreadsheet.xls"
get value of the usedrange of worksheet 1
end tell
}}
put the result into fullData -- a list of lists
If the spreadsheet is not Excel on the Mac you can’t use AppleScript to access it, so you’ll need to export the data in CSV (comma-separated value) format. Then you can use code like this to read the CSV file and split it into a list of lists:
set fullData to ()
repeat with each line of file "/path/to/datafile.csv"
insert it split by comma nested into fullData
end repeat
If items in the file are separated by tabs rather than commas, change the word “comma” to “tab”.
Either way, fullData is now a list, with one item per row. Each of those items is also a list, with one item per column. So, to verify that all of the values in column 3 are numbers between 1 and 7, for example, you could now do this:
repeat with each item of fullData
if item 3 of it is not between 1 and 7 then
logError "Invalid data in column 3"
end if
end repeat
Here i dont have the option to export my spread sheet data into CSV file. I forgot to mention one thing …i.e. am running a webapplication on IE 7.0 so my spread sheet is displayed in the browser.
Am trying to verify my data by capturing the image on the particular column and verifying whether that image is displayed under the column or not. Here also i’ve another problem i.e. i’ve a bunch of items displayed in the spreadsheet in more than a page and i’ve to click on the navigation link to go to the next page. Hence i dont know the count of items displayed in the spreadsheet.
OK, just tried this as a user first: click on a spreadsheet in a browser (which should select the column), and then type control/command c (PC/Mac). Then try to paste. Does this work for you? On my online spreadsheet, each cell was seperated by an “0d” character, so I have to split it with each <0d> character (see below) to parse.
So if I wanted to to this in a test, grabbing all the data from column F, for example, I’d do:
Click “F” # pre-captured image, or use the TiG if it can match the font
Typecommand “c” # Mac, use control c for PC
put remoteClipboard() into myData
split myData by <0d>
repeat with each item of myData
# do something here
log it
end repeat