Data Driven Framework in Selenium with Apache POI in 2021

How to create Data Driven Framework in Selenium with Apache POI Library?  In this article, I will describe 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, it 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.

data driven framework in selenium

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.

excel utility in selenium

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 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.

ExcelUtil definition

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.

Setup 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:

test data

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 testinvalidLoginTest_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:

getRowData

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.

login with excel data

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 finished in TestListener Class. You can see the implementations below. First, I set the row and column numbers:

set row and column

save test results

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

When Test PASSED:

test listener success

When Test FAILED:

test listener failed

When Test SKIPPED:

test listener skipped

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

test verification

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! I love you guys! Happy testing.
Onur Baskirt

16 thoughts on “Data Driven Framework in Selenium with Apache POI in 2021”

  1. Hocam merhaba,
    Öncelikle proje cok güzel olmuş elinize sağlık.

    Bu projede sadece test annotation yazan methodların hepsi çalışıyor senaryoya ayıramadım. Ben excelden inputları alıp adım adım test koşmak istiyorum. Örneğin 3 tane senaryom olacak ilk senaryom sadece n11 sitesini acacak,2.senaryom login olcak,3. senaryom sepete birşey ekleyecek.. Ben sadece n11 sitesini açan senaryoyu koşmak istediğimde diğer 2 senaryoyu çalıştırmayacak..Bu çalıştırılan senaryonun sonucunu extendReport ta gosterecek..Bunu yapmak için nasıl bir yol izlemeliyim.

    Reply
  2. Hello, I am not able to run above code, get following error, please provide me solution to rectify this error
    Error occurred during initialization of VM
    agent library failed to init: instrument
    Error opening zip file or JAR manifest missing : C:\Users\genius\.m2\repository/org/aspectj/aspectjweaver/1.8.10/aspectjweaver-1.8.10.jar

    Reply
    • I have just updated the code with the latest libraries. It ran on MAC flawlessly. I hope it will run on your machine too. If not, please check your security settings and try to get the required dependencies. Your problem is a configurational problem. You could not download all dependencies properly as I understood.

      Reply
  3. Hello Baskirt, I have used below code to write the result in Excel, but on the wb.write(fileOut); taking 6 to 7 second . Why ?

    try {
    row = sh.getRow(RowNum);
    cell = row.getCell(ColNum);

    if (cell == null) {
    cell = row.createCell(ColNum);
    cell.setCellValue(Result);

    } else {
    // Cell = Row.createCell(ColNum+2);
    cell.setCellValue(Result);
    }

    FileOutputStream fileOut = new FileOutputStream(Path);

    wb.write(fileOut);
    fileOut.flush();
    fileOut.close();

    } catch (Exception e) {
    throw (e);
    }

    Reply
  4. Hi,
    My code is running in local but not in jenkins. from Jenkins its not able to fetch test data file and always getting as null. i checked for case sensitive but it didnot worked. Please suggest

    Reply

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.