Tutorials123: Apache POI with TestNG

Reading data from Excel using Apache POI and supplying it to TestNG using @DataProvider is a popular data-driven testing approach in automation frameworks like Selenium.

Below are steps to be executed while reading data from excel sheet using dataprovider.

1. Include Maven Dependency

TestNG itself does not possess the inherent capability to open, navigate, or interpret a Microsoft Excel file (e.g., .xlsx).

To read data from Excel, a project must incorporate an external library (like Apache POI) as a dependency. This library acts as a data bridge that can interact with the Excel file system, understand its sheet and cell structure, and extract the content.

2. @DataProvider

The TestNG method annotated with @DataProvider is where the theoretical work of data retrieval takes place.

(a) Initialization: The Data Provider first uses the external library to locate and open the Excel file and identify the specific sheet containing the test data.

(b) Dimensionality Mapping: It then reads the sheet's dimensions to determine the number of rows (which correspond to the number of test iterations) and the number of columns (which correspond to the number of parameters/data fields required by the test method).

(c) Data Structure Creation: The Data Provider must then create a specific data structure—theoretically a two-dimensional array of objects—to hold all the extracted data. This is the format TestNG expects.

(d) Cell-by-Cell Extraction: It loops through every relevant cell in the Excel sheet:

Reads the content of each cell.
Handles different data types (e.g., converting a numeric cell value into a string if required by the test).
Populates the two-dimensional array with the processed data.

3. Include dataprovider method in @Test annotation

The final step is the consumption of the structured data by the test method.

The Contract: The test method, marked with @Test, declares that it accepts the required number of parameters and specifies the name of the Data Provider (the one that performed the Excel reading).
Injection: TestNG takes the two-dimensional array generated by the Data Provider and, for each row in the array, it runs the @Test method once, injecting the data fields from that row as input parameters.

You need the following dependencies in pom.xml

testng
apache poi
poi-ooxml (for .xlsx files)

<dependencies>
    <!-- TestNG -->
    <dependency>
        <groupId>org.testng</groupId>
        <artifactId>testng</artifactId>
        <version>7.8.0</version>
        <scope>test</scope>
    </dependency>

    <!-- Apache POI -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>5.2.3</version>
    </dependency>
</dependencies>

Full Java Code to Read Excel using Apache POI + TestNG DataProvider

In below java class, getData() is annotated with @DataProvider which has code to read data from excel sheet having name 'excelData'.

In test class, in @Test annotation dataprovider method is included by its name with attribute 'dataProvider'

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.testng.annotations.DataProvider;
import org.testng.annotations.Test;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;

public class ExcelDataProvider {

    @Test(dataProvider = "excelData")
    public void loginTest(String username, String password) {
        System.out.println("Login with Username: " + username + ", Password: " + password);
    }

    @DataProvider(name = "excelData")
    public Object[][] getData() throws IOException {
        String excelPath = "data.xlsx";  // Change path if needed

        FileInputStream fis = new FileInputStream(new File(excelPath));
        Workbook workbook = new XSSFWorkbook(fis);
        Sheet sheet = workbook.getSheetAt(0); // First sheet

        int rowCount = sheet.getPhysicalNumberOfRows();
        int colCount = sheet.getRow(0).getPhysicalNumberOfCells();

        Object[][] data = new Object[rowCount - 1][colCount]; // excluding header

        for (int i = 1; i < rowCount; i++) {
            Row row = sheet.getRow(i);
            for (int j = 0; j < colCount; j++) {
                data[i - 1][j] = row.getCell(j).toString();
            }
        }

        workbook.close();
        fis.close();

        return data;
    }
}

Important points:

Row 0 is usually treated as a header, so it's skipped in the loop.

Make sure data.xlsx is in the project root or update the path accordingly.

If we are using CSV files instead of xlsx to read data then instead of Apache POI, we can use BufferedReader class as well. Below is the example showing the same.

@DataProvider(name = "csvData")
public Object[][] readCsv() throws Exception {
    List<Object[]> data = new ArrayList<>();
    BufferedReader reader = new BufferedReader(new FileReader("data.csv"));
    String line;
    while ((line = reader.readLine()) != null) {
        String[] parts = line.split(",");
        data.add(parts);
    }
    reader.close();
    return data.toArray(new Object[0][0]);
}

Java code for read, test, and write result

Below is the java class having code to read and write in excel files. We are reading parameters: username and password from excel sheet using data provider, and giving values of parameters to loginTest()

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.testng.annotations.*;

import java.io.*;

public class ExcelReadWrite {

    Workbook workbook;
    Sheet sheet;
    String excelPath = "testdata.xlsx";

    @BeforeClass
    public void setupExcel() throws IOException {
        FileInputStream fis = new FileInputStream(new File(excelPath));
        workbook = new XSSFWorkbook(fis);
        sheet = workbook.getSheetAt(0);
    }

    @Test(dataProvider = "excelData")
    public void loginTest(String username, String password, int rowNum) {
        // Example: Assume login successful if password equals "admin123"
        String result = password.equals("admin123") ? "PASS" : "FAIL";
        writeResult(rowNum, 2, result); // Write to 3rd column
    }

    @DataProvider(name = "excelData")
    public Object[][] getData() {
        int rowCount = sheet.getPhysicalNumberOfRows();
        Object[][] data = new Object[rowCount - 1][3]; // 2 inputs + row index

        for (int i = 1; i < rowCount; i++) {
            Row row = sheet.getRow(i);
            data[i - 1][0] = row.getCell(0).toString(); // Username
            data[i - 1][1] = row.getCell(1).toString(); // Password
            data[i - 1][2] = i; // Row number (used for writing result)
        }
        return data;
    }

    public void writeResult(int rowNum, int colNum, String result) {
        Row row = sheet.getRow(rowNum);
        Cell cell = row.createCell(colNum);
        cell.setCellValue(result);
    }

    @AfterClass
    public void saveExcel() throws IOException {
        FileOutputStream fos = new FileOutputStream(new File(excelPath));
        workbook.write(fos);
        fos.close();
        workbook.close();
    }
}

Read Excel Data by Apache Poi using Data Provider in TestNG

Below are steps to be executed while reading data from excel sheet using dataprovider.

You need the following dependencies in pom.xml