Read Excel Data by Apache Poi using Data Provider in 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.

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

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

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();
    }
}