Read and write Excel files from QF-Test

Reading Excel data

1. The ‘Data driver’ node

The easiest way to read Excel data is via the ‘Data driver’ node. The Excel file contains a number of data sets (one per row or also per column). The data driver reads the data sets and generates as many loops as there are data sets. In each loop all test-cases of the test-set will be executed.

The effect of this structure is that the test-case(s) will be run once for each data set.

The ‘Data driver’ node can be used within ‘Test-set’ and ‘Test-step’ nodes.

2. The ‘Excel file’ node alone

If you want to use MS Excel data in a different way you can use the ‘Excel file’ node on its own to read the data into a group variable.

In the above example the Excel data are read into a variable group called ‘data’.

You can list all variables of the group using the procedure ‘listAllMembersOfPropertyGroup’ from the package qfs.utils.variables of the QF-Test standard procedure library qfs.qft.

The syntax for accessing a single variable in a group is ${groupname:variable}.
The syntax of the variable for referencing an Excel cell is 'columnname.index', respectively 'rowname.index'. This said, the complete variable syntax for a cell, when the data sets are organized in rows, would be ${groupname:columnname.index}.
The names have to be in the first row, resp. column, of the data sheet. The index starts with the following row resp. column. All indexes are zero-based.

So, the variable referencing cell B4 would be ${data:Test-case.2}, where ‘data’ is the group name.

The ‘Excel file’ node also generates a variable for the number of data sets read, ${group:size}, and the number of rows, resp. columns, read, ${group:totalsize}. The two may differ if the file contains empty rows or columns between the data sets.
PV: insert ref here from the Manual, 37.4.4 'Excel file'.

Writing Excel data

1. Using the sample procedures

The third example in the attached test-suite copies an existing Excel  file to a new one and adds a result value to each data set.

The procedure ‘createWorkBookFromFile’ reads an existing MS Excel file into a Jython variable using the Excel API. The next procedure assigns an Excel sheet to another global Jython variable. One procedure shows how to use the Excel API in order to read a cell value. Another procedure writes a value to a cell. The last procedure writes the work book to a new Excel file. All procedures make use of global Jython variables in order to pass the complex data structures for the work book and the data sheet between the procedures.

2. In-depth details and scripting possibilities

I used the Excel API of Apache. There are far more methods to the Excel API than used here. So feel free and use the sample scripts as a basis and adapt them to your needs. I found http://poi.apache.org/spreadsheet/quick-guide quite helpful. You can use the API methods in QF-Test Server Scripts.

Explanation to the script in the first procedure:

Lines 1 and 2 import the required modules for the Excel API (org.apache.poi.ss.usermodel) and the Java file handling (java.io).

Line 4 defines ‘wrkbook’ as a global Jython variable. Thus the ‘wrkbook’ variable can also be used by the other Jython scripts.

Lines 6 reads the Excel file parameter passed to the QF-Test procedure using the method ‘lookup’ of the QF-Test module ‘rc’.

Lines 7 to 10 read the Excel file. If it does not exist an exception is thrown.

Line 12 assigns the work book to the variable using a method of the Excel API.

Line 14 uses a Java method for closing the file.