Rest Assured with Apache POI

Reading Time: 3 minutes

Hi folks,

In this blog, we will try to integrate Apache POI with our Rest Assured Project. Moreover, we will explore how to read data from an external excel file. So, let’s get going.

Why should we do it?

  • The main aim to use Apache POI is to enhance our Data-Driven Framework.
  • Data-Driven Framework is an automation testing framework in which input values are read from data files and stored into variables in test scripts.
  • Input data in data-driven framework can be stored in single or multiple data sources like .xls, .xml, .csv, etc.
  • In conclusion, this is where Apache POI flaunts its use case. Therefore, it enhances our DDT framework to use excel files.

What actually is Apache POI?

  • Apache POI is an open-source library developed and distributed by Apache Software Foundation to design or modify Microsoft Office files using the Java program.
  • It is a popular API that allows working around excel files using Java Programs.
  • In short, you can read and write MS Excel files using Java.
  • Hence, Apache POI is your Java Excel solution. Moving on, let’s see how to set it up with our maven project.

How to setup Apache POI with Rest Assured?


Firstly, we need to add these Apache POI dependency into our POM.xml



Post reloading our maven project, Apache POI should get configured automatically.


After this, we need to create a separate class to read the data from the file, here we are using a .xlxs file. Please refer to the code snippet below for a better understanding.

package com.knoldus;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class GetExcel {

    public static String getData(int page, int row, int col) throws FileNotFoundException {
        String excelPath = "src/test/resources/testData.xlsx"; // path where the xlsx file is saved
        FileInputStream excelFile = new FileInputStream(excelPath);
        XSSFWorkbook workBook = null; // initializing it null so the exception could be caught.
        try {
            workBook = new XSSFWorkbook(excelFile);
        } catch (IOException e) {
        assert workBook != null; // asserting that workbook is not empty
        XSSFSheet sheet = workBook.getSheetAt(page); // to get on the sheet which contains data
        XSSFCell cell = sheet.getRow(row).getCell(col); // to get on the cell from which we have to extract data

        // to typecast the cell data to string using DataFormatter
        DataFormatter formatter = new DataFormatter();
        return formatter.formatCellValue(cell);

Using Excel data in our test scenarios.

Since we have the part where we need to read data sorted, we can move ahead and call this GetExcel class in our main test class. The reference code is given below.

package com.knoldus;

import io.restassured.RestAssured;
import io.restassured.http.Method;
import io.restassured.response.Response;
import io.restassured.specification.RequestSpecification;
import org.testng.Assert;
import org.testng.Reporter;
import org.testng.annotations.Test;
import org.json.simple.*;

public class ReadFile extends GetExcel {

    @Test(priority = 1) //TestNG annotation
    void postNewEmployees() throws FileNotFoundException {
        RestAssured.baseURI = "";
        RequestSpecification httpRequest = RestAssured.given();

        //Here we created data through json object that we can send along with POST request
        JSONObject requestParams = new JSONObject();
        requestParams.put(GetExcel.getData(0, 0, 0), GetExcel.getData(0, 1, 0));
        requestParams.put(GetExcel.getData(0, 0, 1), GetExcel.getData(0, 1, 1));
        requestParams.put(GetExcel.getData(0, 0, 2), GetExcel.getData(0, 1, 2));

        // specifying headers here, stating that the request body is json
        httpRequest.header("Content-Type", "application/json");

        // add the json body to the request payload

        // Sending POST request
        Response response = httpRequest.request(Method.POST, "/api/v1/create");

        //capturing response body to perform validations
        String responseBody = response.getBody().asString();
        Reporter.log(responseBody); // to log the response

        // to capture response code through getStatusCode method.
        int status = response.getStatusCode();

        Assert.assertEquals(status, 200);
        Assert.assertEquals(responseBody.contains("Someone1"), true);
        Assert.assertEquals(responseBody.contains("25000"), true);
        Assert.assertEquals(responseBody.contains("25"), true);

Thank you. I hope you may have found this useful.



Written by 

Sparsh is a QA Consultant having experience of more than 2 years. He is familiar with the core concepts of manual and automation, Karate, Cypress, Gatling, Rest Assured and Selenium are the tools that Sparsh is familiar with. He is always eager to learn new and advanced concepts in order to upskill himself.