"Selenium Series #13: Data-Driven Testing — Excel, CSV and TestNG DataProviders"

Series Navigation

← Part 12: Screenshots and File Downloads

→ Part 14: Cross-Browser Testing

Why Data-Driven Testing?

Without it:

@Test public void testLogin_validUser1() { login("alice@example.com", "pass1"); }
@Test public void testLogin_validUser2() { login("bob@example.com", "pass2"); }
@Test public void testLogin_invalidUser() { login("wrong@x.com", "badpass"); }
// Repeat 47 more times...

With it:

@Test(dataProvider = "loginData")
public void testLogin(String email, String password, String expected) {
    // One method — runs N times with different data
}

Reading from Excel — Apache POI

Add dependency to pom.xml:

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.0.0</version>
</dependency>

Excel file structure (src/test/resources/testdata/LoginData.xlsx):

| Email                  | Password       | Expected |
|------------------------|----------------|----------|
| tomsmith@example.com   | SecretPass123  | success  |
| wrong@example.com      | wrongpass      | failure  |
| admin@example.com      | AdminPass!     | success  |

Excel reader utility:

// src/test/java/com/yourname/selenium/utils/ExcelUtils.java
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelUtils {

    public static Object[][] readExcelData(String filePath, String sheetName) {
        try (FileInputStream fis = new FileInputStream(filePath);
             Workbook workbook = new XSSFWorkbook(fis)) {

            Sheet sheet = workbook.getSheet(sheetName);
            int rowCount = sheet.getPhysicalNumberOfRows();
            int colCount = sheet.getRow(0).getPhysicalNumberOfCells();

            // Skip header row (row 0), read data rows
            Object[][] data = new Object[rowCount - 1][colCount];

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

        } catch (IOException e) {
            throw new RuntimeException("Cannot read Excel: " + filePath, e);
        }
    }

    private static Object getCellValue(Cell cell) {
        if (cell == null) return "";
        switch (cell.getCellType()) {
            case STRING:  return cell.getStringCellValue().trim();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell))
                    return cell.getLocalDateTimeCellValue().toString();
                // Return as integer if whole number, else double
                double val = cell.getNumericCellValue();
                return (val == Math.floor(val)) ? (long) val : val;
            case BOOLEAN: return cell.getBooleanCellValue();
            case FORMULA:
                try { return cell.getStringCellValue(); }
                catch (Exception e) { return cell.getNumericCellValue(); }
            default:      return "";
        }
    }

    public static String getCellData(String filePath, String sheetName, int row, int col) {
        Object[][] data = readExcelData(filePath, sheetName);
        return String.valueOf(data[row][col]);
    }
}

Use in DataProvider:

@DataProvider(name = "loginData")
public Object[][] getLoginData() {
    String path = "src/test/resources/testdata/LoginData.xlsx";
    return ExcelUtils.readExcelData(path, "LoginData");
}

@Test(dataProvider = "loginData",
      description = "Data-driven login test from Excel")
public void testLoginFromExcel(String email, String password, String expected) {
    LoginPage loginPage = new LoginPage(getDriver()).open();
    loginPage.enterUsername(email).enterPassword(password);

    if (expected.equals("success")) {
        DashboardPage dashboard = loginPage.clickLogin();
        Assert.assertTrue(dashboard.isDashboardDisplayed());
    } else {
        loginPage.clickLoginExpectingError();
        Assert.assertTrue(loginPage.getFlashMessage().contains("invalid"));
    }
}

Reading from CSV

No extra dependency needed — use Java's built-in classes:

public static Object[][] readCSV(String filePath) {
    List<Object[]> data = new ArrayList<>();

    try (BufferedReader br = new BufferedReader(new FileReader(filePath))) {
        String line;
        boolean firstLine = true;

        while ((line = br.readLine()) != null) {
            if (firstLine) { firstLine = false; continue; } // skip header
            String[] values = line.split(",");
            // Trim whitespace and remove surrounding quotes
            for (int i = 0; i < values.length; i++) {
                values[i] = values[i].trim().replaceAll("^\"|\"$", "");
            }
            data.add(values);
        }
    } catch (IOException e) {
        throw new RuntimeException("Cannot read CSV: " + filePath, e);
    }

    return data.toArray(new Object[0][]);
}

Reading from JSON

Add Jackson dependency:

<dependency>
    <groupId>com.fasterxml.jackson.core</groupId>
    <artifactId>jackson-databind</artifactId>
    <version>2.12.3</version>
</dependency>

JSON test data (testdata/users.json):

[
  { "email": "alice@example.com", "password": "Pass123!", "role": "admin" },
  { "email": "bob@example.com",   "password": "Bob456!",  "role": "user"  }
]

public static Object[][] readJSON(String filePath) throws IOException {
    ObjectMapper mapper = new ObjectMapper();
    List<Map<String, String>> list = mapper.readValue(
        new File(filePath),
        new TypeReference<List<Map<String, String>>>() {}
    );

    Object[][] data = new Object[list.size()][];
    for (int i = 0; i < list.size(); i++) {
        Map<String, String> row = list.get(i);
        data[i] = new Object[]{ row.get("email"), row.get("password"), row.get("role") };
    }
    return data;
}

What's Next

In Part 14 we run the same test suite across Chrome, Firefox, Edge and Safari — configuring parallel cross-browser runs and handling browser-specific quirks.