Test Data With Excel


Excel spreadsheet format has been the most popular data format and Microsoft has definitely created so many jobs and livelihood with just Excel (as part of its Office Suite). As a non-programmer, there are many awesome cool features in Excel that one would appreciate. A friend of mine says “What can you NOT do with excel?”. Yeah I know ! We are not talking about “how” perfectly excel does, but can it do or not.

As a programmer, I am not a big fan of excel because it is heavy to convert into a data structure and also manipulating is a little cumbersome. Most programmers like free and efficient, lightweight formats like json, xml, yml, csv and so on. But all said, excel still exists in billions of projects and environments and more because “Data” has been collected in excel over years. So whether we like it or not, we have to deal with excel for many years to come [Whether Microsoft excel or Open office excel or whatever]

In this section, we will see how we can programmatically access excel through Ruby and subsequently use it in our scenarios. We will take an example of a Data-driven scenario where our data resides in Excel workbook.


  1. Add gem rubyXL to the project 
  2. Focus on specific lines of code that are extremely valuable to understand
  3. Perform CRUD (create, read , update and delete) operations on an excel workbook
  4. Apply it to an Web UI Automation scenario
  5. Complete Cucumber Scenario and step Definitions

Add gem:

1) Gemfile:

Add rubyXL gem to your Gemfile by typing “gem rubyXL” as below


2) Bundler

Run “bundle install” or “bundle update” from Tools – Bundler – Install if you are using RubyMine OR open the project root directory from command prompt and run “bundle update”





3) Env.rb

Add the rubyXL module to your environment so that it gets loaded and the Module is available for use



Now we ready to start coding using rubyXL gem

CRUD operations:

It is extremely difficult to explain every line of code, however I will try my best to explain the important bits. The entire code is however posted at the bottom of this page with the cucumber feature and the code behind aka. step definitions. The code is pretty simple to understand in most cases as it is Ruby and readable right?


Create a workbook, worksheet and add a cell and write the file to filesystem. The insert_cell takes the position coordinates and value that the cell needs to be populated with.


The delete operation applies to a cell, row and a column and ultimately we can delete the workbook from the file system (though deleting a file from file system is not related to Excel)

1) Deleting first row from sheet1

2) Deleting first column from sheet1

3) Deleting first cell from sheet1


The Read operation is the MOST IMPORTANT operation on an excel sheet as most of the times our test data is NOT necessarily generated as part of test script, but test script consumes the test data (either from multiple systems) or data that is manually entered OR data collected over years in a maintenance project. Below are some of the important code snippets that will be helpful. I believe the 6th capability where we can convert the rows into hashes, where hash_keys are column headers and values are corresponding row values is an extremely valuable one. The same code in Java would have been at least 10 or more lines…haha

1) Total # of worksheets and print the name of each worksheet

2) Access Sheet1 and print the # of rows

3) Access first row and print its size (# of columns)

4) Access all cells in first row and print them

5) Access entire sheet data and print

6) Convert rows into hashes (with keys as column headers)

WebUI Automation Scenario:

Let’s apply this to an Web UI Automation Scenario (see the last scenario in the Entire Cucumber Feature section below). Reflecting back on the Web Element Operations section we discussed as part of Basic Tutorial, and Data Tables , we have seen how we populated values to fill a web form. We will use the same example now to pass data through an Excel sheet. Some of us might ask why do we want to do this when we can already do with scenario outline in cucumber. The reason again is because we might have data already existing in Excel workbooks and also because we can collaborate with folks who might NOT have programming experience especially data analysts who have to focus on data than programming and they would prefer maintaining data in Excels.

We will read the rows in excel sheet and fill the web form with two row data sets and continue with our operations after populating the data. Now with being able to read Excel data and integrating that with Web UI Automation scenarios, we have already created a conduit through which we can pass test data.


Entire Cucumber Feature:

The below cucumber scenario explains each of the operations in a much English readable format and this might help you connect the dots. The file “Sample.xlsx” looks as below just in case you would want to populate it with hand. Otherwise, feel free to download it here and place it in your “features/support/Sample.xlsx” location.


Step Definitions:


1) If you run then entire feature, then “Random.xlsx” should get created with the first scenario, then it should get deleted in the second scenario. How to Run cucumber and Ruby is already posted in Basic tutorial with the video at the bottom of those sections.

2) From the 3rd scenario (Read operations), all the information is output to the console, so it should be readable in terms of what the script is doing. I tried my best to bake in as many print statements as possible, however let me know if you believe more statements are required.

Known Issues:

While running the scenarios, the browser might pop up and close. That is totally fine because we have a pre-condition for every scenario i.e. launching a browser and post-condition i.e. closing the browser. It shouldn’t affect the output in any way.

Next section talks about Test Data with JSON