Data Driven Framework in Selenium with Apache POI

How to create Data Driven Framework in Selenium with Apache POI Library?  Yes, in this article, we will learn how to use excel files in your test automation projects for Data Driven Testing in Selenium. In order to read from an excel file and write to an excel file, we can use Apache POI Library. I will show you how to integrate Apache POI libraries into our selenium test project. 

If our tests contain not too much data, we may use the TestNG data provider, but if we have more data, we should think to use excel files or we can store the test data in a database.

Data Driven Framework in Selenium Architecture

I will go on with our Allure reporting example, which comprises of Page Object Model (POM) pattern, ExtentReports, and Allure Report features. In this post, we will add excel manipulation capability to that project. In order to do that, we will add an ExcelUtil class, and this class does all kinds of excel operations.

Here is the final snapshot of our project. You can find the project on GitHub, I will share the project repository link at the end of the article.

I will go step by step. Don’t worry, I will do my best and I hope you will get the topic without any problem. ;) At the end of the article, if you have any problems or questions, please write a comment.

Step-1: Add Apache POI Dependencies

In order to use Apache POI libraries in your project, you should add required dependencies into your pom.xml as shown below.

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>5.0.0</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.0.0</version>
</dependency>

Step-2: Create an ExcelUtil Class

In order to manipulate excel files and do excel operations, we should create an excel file and called it “ExcelUtil” under excelutils package as shown below.

In this file, I wrote all excel operation methods.

setExcelFileSheet: This method has two parameters: “Test data excel file name” and “Excel sheet name“. It creates FileInputStream and set excel file and excel sheet to excelWBook and excelWSheet variables.

getCellData: This method reads the test data from the Excel cell. We are passing row numbers and column numbers as parameters.

getRowData: This method takes row number as a parameter and returns the data of the given row number.

setCellData: This method gets an excel file, row, and column number and sets a value to that cell.

Here is the implementation of ExcelUtil Class:

package utils.excelutils;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import lombok.SneakyThrows;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.openqa.selenium.Platform;

public class ExcelUtil {
    public static final String       testDataExcelFileName = "testdata.xlsx"; //Global test data excel file
    public static final String       currentDir            = System.getProperty("user.dir");  //Main Directory of the project
    public static       String       testDataExcelPath     = null; //Location of Test data excel file
    private static      XSSFWorkbook excelWBook; //Excel WorkBook
    private static      XSSFSheet    excelWSheet; //Excel Sheet
    private static      XSSFCell     cell; //Excel cell
    private static      XSSFRow      row; //Excel row
    public static       int          rowNumber; //Row Number
    public static       int          columnNumber; //Column Number

    // This method has two parameters: "Test data excel file name" and "Excel sheet name"
    // It creates FileInputStream and set excel file and excel sheet to excelWBook and excelWSheet variables.
    @SneakyThrows
    public static void setExcelFileSheet(String sheetName) {
        //MAC or Windows Selection for excel path
        if (Platform.getCurrent().toString().equalsIgnoreCase("MAC")) {
            testDataExcelPath = currentDir + "/src/test/resources/";
        } else if (Platform.getCurrent().toString().contains("WIN")) {
            testDataExcelPath = currentDir + "\\src\\test\\resources\\";
        }
        // Open the Excel file
        FileInputStream ExcelFile = new FileInputStream(testDataExcelPath + testDataExcelFileName);
        excelWBook = new XSSFWorkbook(ExcelFile);
        excelWSheet = excelWBook.getSheet(sheetName);
    }

    //This method reads the test data from the Excel cell.
    //We are passing row number and column number as parameters.
    public static String getCellData(int RowNum, int ColNum) {
        cell = excelWSheet.getRow(RowNum).getCell(ColNum);
        DataFormatter formatter = new DataFormatter();
        return formatter.formatCellValue(cell);
    }

    //This method takes row number as a parameter and returns the data of given row number.
    public static XSSFRow getRowData(int RowNum) {
        row = excelWSheet.getRow(RowNum);
        return row;
    }

    //This method gets excel file, row and column number and set a value to the that cell.
    @SneakyThrows
    public static void setCellData(String value, int RowNum, int ColNum) {
        row = excelWSheet.getRow(RowNum);
        cell = row.getCell(ColNum);
        if (cell == null) {
            cell = row.createCell(ColNum);
            cell.setCellValue(value);
        } else {
            cell.setCellValue(value);
        }
        // Constant variables Test Data path and Test Data file name
        FileOutputStream fileOut = new FileOutputStream(testDataExcelPath + testDataExcelFileName);
        excelWBook.write(fileOut);
        fileOut.flush();
        fileOut.close();
    }
}

Step-3: Set Data Excel File Name in BaseTest Class

I should also add testDataExcelFileName in ExcelUtil class because all tests use the same excel file but their sheets are different.

Step-4: Setup Test Data in Test Class

We need to set the sheet name before starting the tests. We have to do it in related test classes because each test class may have different test data.

Step-5: Create a Test Excel File

Now, it is time to construct our test excel data file for data-driven testing. In this example, I will modify our login scenarios (tests). The first one is the “invalid username” and “invalid password” test. I will store the following variables in the LoginData sheet:

• username (invalid)
• password (invalid)
• check JavaScript error logs
• test status (automation code will update after test execution.)

For the second test, I will test the empty username and empty password case. Thus, my data will be like that:

• username (empty)
• password (empty)
• username error message  (verification)
• password error message (verification)
• test status (automation code will update after test execution.)

Here is our test data and how it looks like:

Step-6: Modify Test and Page Classes

In LoginTests class, we should start to modify our code for data-driven testing. I will explain the second test “invalidLoginTest_EmptyUserEmptyPassword“. I will use “getRowData” method for the logintoN11 operation. In order to get the first test data values (second row), we should use the below code:

All list values in the excel file start from “0”. Thus, I have to use “2” to get the second test’s data.

Now, it is time to change “loginToN11” method in LoginPage class.

As you have seen above, the loginToN11 method gets the XSSFRow variable as a parameter, and we can get the required values with

row.getCell(1).toString(): This equals to username value.

row.getCell(2).toString(): This equals a password value.

Then, in LoginTests class, I set row number (2) and test status/result column number (5). I will use these numbers to update the test status after the test is finished in TestListener Class. You can see the implementations below. First, I set the row and column numbers:

Then, in TestListener class I update the test result/status for each test status (passed, failed, skipped):

When Test PASSED:

When Test FAILED:

When Test SKIPPED:

In order to verify the login password message, I did the below modification:

Here, getCellData method gets row number as 2, the 3rd column holds the expected user name error message and the 4th column holds the expected password error message.

When we go to the verifyLoginPassword and verifyLoginUserName method’s implementation, we will see that it gets the expectedText as a parameter and verify it with the actual value.

Let’s run the data-driven framework in selenium code and check the result at both the IntelliJ console and our excel file. If all tests pass, we will see the status values as PASSED! :)

If any of the tests failed, we will see the status of the failed test as FAILED.

GitHub Project

https://github.com/swtestacademy/TestNGAllureReport/tree/data-driven-framework-apache-poi

All the best! Thanks for reading!
Onur Baskirt

Onur Baskirt

Onur Baskirt is a Software Engineering Leader with international experience in world-class companies. Now, he is a Software Engineering Lead at Emirates Airlines in Dubai.