Data Driven Framework in Selenium WebDriver Using Apache POI

How can you utilize a Data-Driven Framework in Selenium with Apache POI?

The Data Driven Framework is a widely utilized Automation Testing Framework in today’s market. Automated testing using a data-driven framework employs a method where the data set for testing is established in an Excel sheet, then incorporated into automation testing tools for use in testing software.

While Selenium WebDriver is excellent for automating interactions with web applications, it lacks native functionality for reading and writing operations on Excel files.

As a workaround, we utilize third-party APIs such as Apache POI.

What knowledge will this guide impart:

• Understanding of data-driven framework in Selenium WebDriver with excel
• Methods to read and write data from an excel sheet using Selenium WebDriver and Apache POI

What is Apache POI?

Apache POI (Poor Obfuscation Implementation) is a Java API that facilitates reading, writing, and modifying office files. This API is frequently used for data-driven testing with Selenium.

There exist various methods of establishing a Data-Driven Framework, each differing in the development and maintenance efforts required.

Constructing a Data-Driven Framework in Selenium using POI leads to lessened maintenance, enhanced test coverage, and increased return on investment.

Suggested readings:

• The Most Popular Test Automation Frameworks
• Data-Driven Testing in SoapUI Pro

Why Utilize Data-driven Testing?

In some instances, numerous data sets must be used to verify a software feature. Manually running the same test with different data sets can be time-consuming, prone to errors, and tedious.

Let’s understand this situation with an instance.

Imagine we must test a login/Register/ Any form with many input fields using 100 different data sets.

You would have three different testing approaches:

1) Craft 100 scripts for each data set and execute each test consecutively.
2) Alter the data in the script and execute it repeatedly.
3) Load the data from the Excel sheet and execute the script multiple times with varying data.

The first two approaches are tedious and time consuming, resulting in a low return on investment. It’s more effective to adopt the third approach.

In the third approach, we use the Data Driven framework, where all our data is stored in an Excel sheet from which it is imported to test the application’s features.

=> Interested in diving deeper into the Data-Driven Framework? Check out our detailed article here.

What’s Needed to Utilize a Data-Driven Framework?

To take this approach, we need to have Eclipse and TestNG correctly configured.

Once that’s finished, we will examine:

• The different interfaces of Apache POI.
• Integration of Apache POI in Eclipse.
• Reading Data from the Excel Sheet.
• Writing data to the Excel Sheet.
• The benefits of using Apache POI with Selenium.

Interface in POI

A standout feature of Apache POI is its ability to support reading and writing operations on both .xls and .xlsx files.

Here are some interfaces of POI:

• XSSFWorkbook: Reflects an xlsx file’s workbook.
• HSSFWorkbook: Reflects an xls file’s workbook.
• XSSFSheet: Reflects an XLSX file’s sheet.
• HSSFSheet: Reflects an XLS file’s sheet.
• XSSFRow: Reflects a row on an XLSX file’s sheet.
• HSSFRow: Reflects a row on an XLS file’s sheet.
• XSSFCell: Reflects a cell in an XLSX file’s row.
• HSSFCell: Reflects a cell in an XLS file’s row.

The available cell properties:

• CELL_TYPE_BLANK: Indicates a blank cell.
• CELL_TYPE_BOOLEAN: Indicates a Boolean cell (true or false).
• CELL_TYPE_ERROR: Indicates a cell with an error value.
• CELL_TYPE_FORMULA: Indicates a cell with a formula result.
• CELL_TYPE_NUMERIC: Indicates a cell with numeric data.
• CELL_TYPE_STRING: Indicates a cell with a string.

Steps to Utilize Selenium with Apache POI

Let’s compile an automation script to test the login procedure of web-based software.

In this approach, LinkedIn will be used as the example.

We take data from an Excel sheet, utilize it to log into the application, and after executing, we record the result in the Excel sheet.

For this process, we require the following software installed on our system:

• Java JDK 1.7+
• Eclipse IDE
• TestNG
• Selenium libraries
• Microsoft Office / Open Office

Step #1)

First, we need to set up Eclipse to work with Apache POI.

Download jar files for Apache POI.

Step #2)

Unzip the jar file, append the subsequent jars to your project, and configure them:

• dom4j-1.6.1.jar
• poi-3.10-FINAL-20140208.jar
• poi-ooxml-3.10-FINAL-20140208.jar
• poi-ooxml-schemas-3.10-FINAL-20140208.jar
• xmlbeans-2.3.0.jar

Step #3)

After setting up the necessary jars, create an Excel sheet, input some data, and save it as TestData.xlsx in your chosen location.

Step #4)

Now, follow the example code below to pull data from the Excel sheet and use it to login to linkedin.com.

//Sample code here

Step #5)

Right-click on the test case class and select Run as -> TestNG Test.

Apache POI will now retrieve data from the Excel sheet and use that data to log into the application. Now we’ve seen how to retrieve data from an Excel sheet, let’s see how to write data to it.

//Sample code here

Note:If you encounter issues during this process, consider checking the following points:

• Make sure all relevant jar files have been added to the project and correctly configured.
• Ensure necessary software is installed correctly.
• Correct use of an interface with respect to the Excel file, like HSSF for .xls and XSSF for .xlsx.
• Check for valid row and column indexes.
• Ensure the Excel file is closed before execution.
• Ensure the use of proper classes for the Excel file. (XSSF for .xlsx files and HSSF for .xls files).

Benefits of Employing the Data-Driven Framework

• Enhanced test coverage.
• Reusability of the code.
• Maintenance is reduced.
• Quicker Execution.
• Improved error handling.

Conclusion

Importing and exporting to and from a file is crucial to the software testing process. Apache POI is a vital tool in doing so for Selenium Test Automation.

Integration of Selenium with Apache POI allows you to run your script multiple times with varying datasets, with any data stored in a single location. This saves time and cuts down on maintenance efforts for the test script.

About the author:This is a guest post by Vivek, an engineer specializing in QA Automation.

Have questions about implementing a data-driven testing framework in Selenium WebDriver using Apache POI? Share your questions in the comments section below.