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 py module openpyxl 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 behave Scenario and step Definitions

Add openpyxl:

1) requirements.text

Add openpyxl to requirements.text by typing “openpyxl==2.2.4” as below

openpyxl

 

2) pip install

Run “pip install -r requirements.text” from the root of the project

 

Now we ready to start coding using openpyxl

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. 

Create/Update:

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.

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 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) Create worksheet and print all worksheet names

2) Access Sheet1 and print the # of rows

 

 

3) Access entire sheet data and print

6) Convert rows into dict (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 behave 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 behave. 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 behave Feature:

The below behave 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 “python_behave_selenium” root location.

SampleXlsx

Step Definitions:

Output:

1) If you run then entire feature, then “Random.xlsx” should get created with the first scenario. 

 

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.