How do I read rows of an Excel table in a test script?

Queries related to T-Plan Robot
Post Reply
Posts: 2
Joined: Thu Oct 07, 2010 2:39 pm
First Name: Cheryle
Last Name: Gannaway
Company: Siemens

How do I read rows of an Excel table in a test script?

Post by cheryle.gannaway.ext » Thu Oct 07, 2010 7:38 pm

We are drawing lines in our application, so we have a start point and an end point. There are many start and end points represented by numbers in two columns of an Excel spreadsheet.

Is there a good way to code the test script so that the test run through using the the data in row 1, then row 2, etc?


Posts: 74
Joined: Thu Oct 29, 2009 5:43 pm
First Name: Robert
Last Name: Pes
Company: T-Plan

Re: How do I read rows of an Excel table in a test script?

Post by rpes » Fri Oct 08, 2010 10:18 am

The following script shows how to iterate over lines of an Excel file (.xls). It also demonstrates how to convert geometrical points of the "x,y" format to the one accepted by the Mouse command. The example presumes that drawing is realized by mouse drags; change it to whatever is applicable to your AUT.

Code: Select all

# Source Excel file (.xls) with points (locations).
# We presume that the first two columns of the first sheet
# contain start and end points in form of "x,y" such as:
# Start Point | End Point
# 10,10       | 10,20
# 10,20       | 20,20
Var DATA_XLS="/tmp/points.xls"

# Variables for the start and end points
Var start=0,0 end=0,0

# Draw procedure
procedure draw {
  # Specify your drawing code here.
  # The example commands below convert the points like "100,100"
  # to the Mouse compatible format of "x:100,y:100" and
  # perform a Mouse drag from the start point to the end one.
  # Replace it with your own functionality if necessary.
  String replace "{start}" string="," replacement=",y:"
  Var start="x:{_STRING}"
  String replace "{end}" string="," replacement=",y:"
  Var end="x:{_STRING}"

  Mouse drag from="{start}" to="{end}"

# Open the Excel file 
Excel open file="{DATA_XLS}" 

# Iterate over all data lines except the first one with column names
for (index=2; {index}<{_EXCEL_SHEET_ROWS}+1;index={index}+1) {
  # Read the start point from column 1
  Excel select row={index} column="1"
  Var start={_EXCEL_CELL_VALUE}

  # Read the end point from column 2
  Excel select row={index} column="2"

  # Call the draw procedure

Post Reply

Who is online

Users browsing this forum: No registered users and 2 guests