Data Driven with excel – 2


In the previous section , Data Drive with excel-1, we have set the stage on using object repository and data helper. In this section, we will take an example and see how we put the data-driven concepts/capability to use. We will fill a contact us form on website with different data sets. The contact form looks as below with many input fields.



  1. Describe the cucumber scenario
  2. Populate object repository
  3. Data Excel
  4. Combine cucumber scenario outline + data table to specify multiple data sets
  5. Consume Data Helper and parse data
  6. Change default data input excel
  7. Execute the scenario
  8. Observe the results
  9. Closing thoughts and Project Code

1) Describe Cucumber scenario

We will:


  1. open website
  2. Then click “Contact us” button at the right top corner, which will take us to contact form.
  3. On the contact form, select heading, set email, set order reference number, set message
  4. We will input all the necessary values and eventually click submit.

The simple cucumber scenario when written might look as below. I want to use the data from row “1” of the excel workbook and I would go with the default workbook i.e. default.xlsx.

2) Populate object repository

We will inspect the html elements and use the “id” attributes for each of the fields viz. heading, email, order reference, message and submit button. The file “lib/config/object_repository.yml” looks as below.  We are also printing the object repository values in the cucumber scenario, so on the output window or results , you can see what values were used.

3) Data Excel

We will use the data excel ‘lib/config/default.xlsx’ file and the file looks as below [We will post the entire project codebase at the end of this section in case you want to download the excel file]. We are also printing the current data set used by the scenario inside step_definitions, so that we know which data set was used.


4) Cucumber Scenario Outline

The second cucumber scenario outline uses multiple data sets and repeats the operations for each data set. This is just to show how data “drives” the test case execution.

5) Consume Data helper and parse data

We first mixin the Data Helper module with Cucumber by specifying this line in env.rb file. This will ensure that we can use the methods specified on the Data helper module within the step_definitions [which you will see in a while]

We then access the data helper method inside the step_defintion as follows:

Now we have a data_set which is a hash that contains the first data set from above excel workbook (if row_index=2). The step_definition code is posted below, so you will know the exact values of the arguments inside the code.

5) Change default input excel

If you intend to change and use data from another excel workbook, then put the excel workbook inside ‘/lib/config/data’ folder and set it in cucumber.yml file as below. Let’s say your new workbook is “beta_data.xlsx”. This file will override the “default.xlsx” and the code will use beta_data.xlsx file.

Entire Cucumber Scenario:

Step Definitions:

Execution and Results:

Right click on any of the scenarios and execute. The first scenario should use the first row values and second scenarios should run with both the row values. The results file should look as below:


Closing Thoughts and Project Code:

As we see, we have externalized data with multiple data formats, though we were a little biased towards excel since many companies still use excel. That said, we have just read data from excel into hash, after that we can do pretty much anything with the data structure. In the next section, we will use YAML, which is inbuilt into Ruby and see how easy it gets.

Feel free to check out the entire project code base from my github repo: data_driven_framework. Instructions on how to run the project is in file or if you read it directly on github project page.

It is assumed that you have Ruby, cucumber and selenium components set up on your machine already. If not, please follow the below links:

The code base contains YAML data driven examples too, however you will be able to figure out which one to use for excel.

Data Driven Framework