Apache POI Selenium & Data-Driven Testing in Selenium

Apache POI Selenium

Data-driven testing keeps test data outside the code: one login test runs against every row of a spreadsheet, and adding a case means adding a row, not writing Java. Apache POI is the library Selenium frameworks use to read and write Excel. This guide builds a reusable Excel reader that returns each row as a map, wires it into a TestNG DataProvider, and covers CSV and XML — with the pitfalls that break most first attempts.

What Is Apache POI?

An open-source Java API for Microsoft Office formats. For Excel it has two families: HSSF for old .xls files and XSSF for .xlsx. Code against the common interfaces and let WorkbookFactory pick the right one.

<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi-ooxml</artifactId>
  <version>5.2.5</version>   <!-- check Maven Central for the latest -->
</dependency>

The Test Data Sheet

src/test/resources/testdata/users.xlsx, sheet Login, with a header row:

Using the header names as keys means tests read row.get("username") instead of fragile column numbers — reorder the columns and nothing breaks. The run column lets testers switch rows off without deleting them.

A Reusable Excel Reader ⭐

import org.apache.poi.ss.usermodel.*;
import java.io.InputStream;
import java.util.*;

public final class ExcelReader {

    public static List<Map<String, String>> readSheet(String resource, String sheetName) {
        try (InputStream in = ExcelReader.class.getResourceAsStream(resource);
             Workbook workbook = WorkbookFactory.create(in)) {       // .xls or .xlsx

            Sheet sheet = workbook.getSheet(sheetName);
            if (sheet == null) throw new IllegalArgumentException("No sheet named " + sheetName);

            DataFormatter formatter = new DataFormatter();           // numbers, dates, booleans → text
            Row header = sheet.getRow(0);
            List<Map<String, String>> rows = new ArrayList<>();

            for (int r = 1; r <= sheet.getLastRowNum(); r++) {
                Row row = sheet.getRow(r);
                if (row == null) continue;                            // skip completely empty rows
                Map<String, String> data = new LinkedHashMap<>();
                for (int c = 0; c < header.getLastCellNum(); c++) {
                    String key = formatter.formatCellValue(header.getCell(c));
                    String value = formatter.formatCellValue(row.getCell(c));   // null cell → ""
                    data.put(key, value.trim());
                }
                rows.add(data);
            }
            return rows;
        } catch (Exception e) {
            throw new RuntimeException("Could not read " + resource, e);
        }
    }
}

Why DataFormatter?

cell.getStringCellValue() throws IllegalStateException on a numeric cell — a password like 12345 or a PIN breaks the test. getNumericCellValue() returns 12345.0. DataFormatter.formatCellValue() returns exactly what Excel displays, for any cell type, and an empty string for missing cells — which removes most POI errors in one line.

Feeding the Data Into Tests

public class LoginDataTest extends BaseTest {

    @DataProvider(name = "loginData")
    public Object[][] loginData() {
        return ExcelReader.readSheet("/testdata/users.xlsx", "Login").stream()
                .filter(row -> "Y".equalsIgnoreCase(row.get("run")))
                .map(row -> new Object[]{ row })
                .toArray(Object[][]::new);
    }

    @Test(dataProvider = "loginData")
    public void loginOutcome(Map<String, String> row) {
        new LoginPage(driver).login(row.get("username"), row.get("password"));
        Assert.assertTrue(driver.getPageSource().contains(row.get("expected")),
                "Row for " + row.get("username"));
    }
}

Each enabled row becomes one test run, reported separately. Add @DataProvider(parallel = true) to run rows concurrently — only if your driver is ThreadLocal and rows don't share data. More: TestNG DataProvider.

Writing Results Back to Excel

public static void writeResult(Path file, String sheetName, int rowIndex, int colIndex, String value)
        throws IOException {
    Workbook workbook;
    try (InputStream in = Files.newInputStream(file)) {
        workbook = WorkbookFactory.create(in);
    }
    Sheet sheet = workbook.getSheet(sheetName);
    Row row = Optional.ofNullable(sheet.getRow(rowIndex)).orElseGet(() -> sheet.createRow(rowIndex));
    row.createCell(colIndex).setCellValue(value);
    try (OutputStream out = Files.newOutputStream(file)) {
        workbook.write(out);
    }
    workbook.close();
}

• Write to a copy in target/, not the source file in src/test/resources — otherwise every run changes committed test data.
• Close Excel first — an open file is locked on Windows and the write fails.
• Don't write from parallel threads into the same workbook; collect results and write once at the end. Most teams put results in the HTML report instead.

CSV Test Data

CSV is simpler to edit and review in Git. But splitting lines on commas breaks as soon as a value contains a comma. We ran a plain split(",") reader on this row:

"doe, jane",pa55,Products
→ {username="doe, password= jane", expected=pa55}      ← columns shifted

Use a CSV library such as OpenCSV (com.opencsv:opencsv), which handles quotes and embedded commas:

try (CSVReaderHeaderAware reader = new CSVReaderHeaderAware(new FileReader("src/test/resources/testdata/users.csv"))) {
    Map<String, String> row;
    while ((row = reader.readMap()) != null) {       // each row as header → value
        rows.add(row);
    }
}

XML Test Data or Configuration

<config>
  <entry key="baseUrl">https://www.saucedemo.com</entry>
  <entry key="browser">chrome</entry>
  <entry key="timeout">10</entry>
</config>

static Map<String, String> readXml(String path) throws Exception {
    Document doc = DocumentBuilderFactory.newInstance().newDocumentBuilder().parse(new File(path));
    NodeList entries = doc.getElementsByTagName("entry");
    Map<String, String> map = new LinkedHashMap<>();
    for (int i = 0; i < entries.getLength(); i++) {
        Element e = (Element) entries.item(i);
        map.put(e.getAttribute("key"), e.getTextContent().trim());
    }
    return map;
}
// → {baseUrl=https://www.saucedemo.com, browser=chrome, timeout=10}   (verified)

This uses only the JDK (JAXP), so no dependency is needed.

Which Format to Use

Keep secrets (real passwords, tokens) out of all of these — read them from environment variables or your CI's credential store.

See what happens in the browser for each data row in the Selenium WebDriver Visualizer. Framework context: Page Object Model & Framework Types.

From Real Projects

In my framework, test data lived in external Excel sheets and was read with the Apache POI library, so the same Selenium script could run with many data sets and non-technical team members could update the data without touching code. Keep the data file simple and validate what you read — an empty cell or an unexpected type is one of the most common causes of a confusing failure. On Canolog, every department — sales, inventory, finance, service and parts — connects to the others, so a change in one module had to be checked in the flows that depend on it. Validate every value you read from Excel before using it in a test.

📚 Official documentation: Selenium documentation · ISTQB Glossary of testing terms

FAQs

What is Apache POI used for in Selenium?

Reading test data from Excel (and occasionally writing results) so tests can run once per data row — data-driven testing.

How do you read a cell value safely?

Use DataFormatter.formatCellValue(cell); it returns the displayed text for any cell type and an empty string for missing cells.

Why does getStringCellValue() throw IllegalStateException?

The cell is numeric (or a formula/boolean). Use DataFormatter, or check getCellType() first.

XSSF or HSSF?

XSSF for .xlsx, HSSF for .xls. WorkbookFactory.create() handles both.

How does Excel data reach a TestNG test?

A reader returns rows (ideally as maps), a @DataProvider converts them to Object[][], and the test runs once per row.

Should I read CSV with split(",")?

Only for data that can never contain commas or quotes; otherwise use OpenCSV or another CSV parser.

Related

• TestNG Cheat Sheet
• Build a Framework From Scratch
• The Complete Automation Framework Guide