Many users are familiar with spreadsheets, so you may find Microsoft Excel to be a convenient tool for storing and maintaining sets of values to be used for data driven testing. But how do you get the data from an Excel worksheet into a form that can be read by your Eggplant script?
One way is to export the data from the spreadsheet into a tab-delimited text file and have your script read that file. While this works, it means that each time values are updated you will have to remember to manually export the data again, being sure to export the correct set of data into the correct file each time. This process involves a manual step, and is prone to error. Fortunately, there is a better way.
Using SenseTalk’s ability to call AppleScript, it is possible to have Eggplant read the data directly out of an Excel spreadsheet on your Mac (note: you will need to have Microsoft Office X installed on your Mac for this to work). Here is an example script that does this:
do AppleScript {{
tell application "Microsoft Excel"
open workbook workbook "Titanium:Users:doug:Eggplant:MyData.xls"
get value of the used range of worksheet 1
end tell
}}
put the result into fullData -- a list of lists
-- convert to a list of records, using the first row titles as the keys
put item 1 of fullData into header -- get the column titles
delete item 1 of fullData
repeat with each row of fullData
set record to (:)
repeat with n=1 to the number of items in header
set property (item n of header) of record to item n of row
end repeat
insert record after recordList
end repeat
This script begins with a “do AppleScript” command that retrieves the data from the Excel worksheet. The first line of the AppleScript code is a “tell” command that identifies “Microsoft Excel” as the application we want to communicate with. The script then tells Excel to open a particular worksheet and get the “usedRange” from that worksheet. Note that the full file path of the worksheet is specified beginning with the machine name (“Titanium”) and using ":"s to separate components of the path rather than the usual "/"s – this is the way that Excel on the Mac expects to see the path.
When Excel is asked for the “usedRange” it returns just the part of the worksheet where values have been entered. The value returned by AppleScript’s “get” command is retrieved using SenseTalk’s “the result” function and stored into a variable called fullData. The data is returned as a list of lists, with the outer list being the rows and each inner list containing the values for the columns in that row.
At this point the values have all been retrieved from Excel. The next part of the script shows a technique for converting the data into a list of records. Each record is a property list containing values identified by keys. The keys are derived from the column headings in the first row of the worksheet. So this technique will work for any set of data where the first row contains the names of the columns and subsequent rows contain the data values.
Having the data organized as property lists generally makes it much nicer to work with. For example, suppose your spreadsheet contained information about different users like this:
User Name Full Name Password
fred Fred Flintstone wilma
barney Barney Rubble betty
pebbles Pebbles Flintstone bambam
then you might go on to use the recordList something like this:
sort recordList by the "User Name" of each
repeat with each user in recordList
typeText user's "User Name" & return
typeText user's password
verifyFullName the "Full Name" of user
end repeat
Note that if your column titles contain more than one word, you will need to put quotes around the key when accessing that property from the property list.
Caveats
The simple AppleScript code presented above worked fine in my tests using simple string and number values. When the worksheet included date values, they were not returned correctly. I was able to get it to work with dates by using the following more complex AppleScript code:
do AppleScript {{
tell application "Microsoft Excel"
open "Titanium:Users:doug:Eggplant:MyData.xls"
set theRange to value of the usedRange of worksheet 1
set newData to {}
repeat with aRow from 1 to count of theRange
set newRow to {}
repeat with aCell from 1 to count of item 1 of theRange
set newRow to newRow & text of cell aCell of row aRow of worksheet 1
end repeat
copy newRow to the end of newData
end repeat
get newData
end tell
}}
put the result into fullData
This took noticeably longer to run than the original version, but succeeded in returning the date values using the text representation displayed in the cells of the worksheet.
Both versions of AppleScript code shown above worked for me, using an older version of Microsoft Office. I understand that Excel 2004 includes significant changes to its AppleScript support dictionary, so some modifications may be needed to work with that version.
