Data Parameterization Using Apache POI With Selenium

Introduction

In testing, it is always important to test application features with different sets of data. Testing with one set of data that we use during recording or while creating a base script will not confirm the functionality of the application alone. For example: When we are doing a login test, it is extremely important to test with all possible valid and invalid credentials to ensure login functionality is working as expected.

One way to achieve testing multiple sets of data is to create individual test scripts/test cases that hold one set of hardcoded data. In this case, if we plan to test a functionality with ten different sets of data, we must create ten independent test scripts with different data. Another way is to create a base script with one set of data and manually change the data and execute the script 10 times.

Both approaches are time-consuming and practically difficult as well as impractical. The best and suggested approach to handling multiple sets of data is to have all the identified test data in any of the data sources like Excel, XML or in JSON files.

In this article, I’m going to discuss how to achieve data parameterization through Excel files using the Apache POI API.

Case Study

Peter is a newly-joined automation test developer in your Selenium project. He has performed exploratory testing on Tricentis Demowebshop application and trying to login with multiple users on the application. Can you guide Peter to complete his activity?

Here's the application URL.

Expectations

Help Peter to test the login screen with two sets of valid and one set of invalid credentials. A report in Excel with “Valid user” or “Invalid user” is generated based on the application functionality.

Sample Input Sheet:

What is the Apache POI API?

• It is an open source API of Apache Foundation

• This API contains a huge collection of packages, classes, and methods that supports handling Excel files

• This API supports both XLS and XLSX versions of Excel files by providing plenty of packages, classes and relevant methods to manipulate workbooks, sheets, etc.

• Apache POI supports both .xls and .xlsx versions of Excel formats. HSSF is used to handle .xls files and XSSF is used to handle .xlsx files

How Do You Integrate the Apache POI in Eclipse?

There are two ways using which we can load the Apache POI libraries in Eclipse:

1. Download/Add libraries to Java project from here.

2. Use this Maven dependency in pom.xml file creating a Maven project

How to Read Data Using Apache POI

1. The methods XSSFWorkbook  or HSSFWorkbook  are used to create a workbook object using FileInputStream . Workbook refers to the Excel file from where the data must be read.

2. We can create a worksheet object using the method XSSFSheet or HSSFSheet. This refers to the sheet from where the data must be read.

3. We can use the  .getmethod to work with rows and cells in the identified sheet.

How to Write Data Using Apache POI

1. The methods XSSFWorkbook or HSSFWorkbook is used to create a workbook object using FileOutputStream . Workbook refers to the Excel file to which the data must be written.

2. We can create a worksheet object using the method XSSFSheet or HSSFSheet. This refers to the sheet to where the data must be written.

3. We can use the .get method to work with rows and cells in the identified sheet.

4. The  .create method is used to create new cells and the .set method is used to insert values to cells.

5. The close method is used to close the workbook.

Selenium code to handle Excel files:

Output Excel Sheet:

Summary

In this article, we have seen how to access Excel files in Selenium using Apache POI API. This code logic will iterate three times and writes "Valid" or "Invalid" user in "Result" column of the output Excel sheet.