Test Data with Excel

Context:

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.

Agenda:

  1. Add apache POI to maven pom.xml
  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

Pre-requisites:

  1. You have completed the section “Set up Env” and have your environment ready with JDK, maven and IntelliJ
  2. You have read and understood HTML DOM
  3. You were able to comprehend “Test Data” post
  4. You downloaded the project code base for class8 under – https://github.com/machzqcq/CucumberJVMExamples and imported into IntelliJ and the structure should look as below

class8_project_structure

If imported into eclipse, the structure looks as below

class8_eclipse_project_structure

Apache POI

Add the below GAV to the pom.xml file [It is already present in Class8 if you downloaded the entire project

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/Update:

Create a workbook, worksheet and add a cell and write the file to filesystem.

Delete:

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)

Read:

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 3rd scenario 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.

Read excel workbook

Read Excel Sheet

Reading rows and cells and printing

Convert each row into Hasmap (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 first scenario creates a file “Random.xlsx” and fills random data in it, then later it prints all the values and then deletes one row and one cell. Ultimately again prints the values to see that it was indeed deleted
  • The second scenario will read the file “Sample.xlsx” and print values
  • The third scenario will convert the rows into HashMaps
  • The fourth scenario uses the data from excel and fills it on a web form using Selenium webdriver

Hooks

Since the first 3 scenarios are purely excel related, we do not want the browser to pop up , hence I tagged only the last one scenario with @web, so that Before() and After() blocks work only with @web i.e. for the last scenario only

Step Definitions

Execution Output

Eclipse and IntelliJ outputs should  be the same. The way to execute in eclipse is also pretty much the same as in IntelliJ

IntelliJ Output (How to execute is explained in previous post)

The first screenshot will show that the text in excel is being output, the second screenshot will show that the scenarios passed

class8_intellij_execution_output

class8_intellij_output

Command Line Ouput

class8_cmd_output

CI Server Output

class8_ci_output

class8_ci_output1