Reading Test Data from Excel/CSV

Back in the @DataProvider lesson, our test data lived inside the Java file as a hard-coded Object[][]. That's fine for 3 rows. It falls apart the moment a QA lead hands you a spreadsheet of 20 RecipeBox test recipes — titles, categories, and prep times — to bulk-test the "Add Recipe" form, and expects to edit that spreadsheet directly without touching Java code. This lesson reads that spreadsheet at runtime instead.

Option 1: Apache POI (.xlsx)

Apache POI is the standard Java library for reading and writing Microsoft Office files. Add it to your Maven pom.xml:

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

Helper: Reading Rows into Object[][]

This is the exact shape a TestNG @DataProvider needs — an Object[][] where each inner array is one test's arguments:

public class ExcelUtils {

    public static Object[][] readRecipeData(String filePath, String sheetName) throws IOException {
        FileInputStream fis = new FileInputStream(filePath);
        Workbook workbook = new XSSFWorkbook(fis);
        Sheet sheet = workbook.getSheet(sheetName);

        // Row 0 is the header, so data rows run 1..lastRowNum
        int rowCount = sheet.getLastRowNum();
        Object[][] data = new Object[rowCount][3];

        for (int i = 1; i <= rowCount; i++) {
            Row row = sheet.getRow(i);
            data[i - 1][0] = row.getCell(0).getStringCellValue();               // Title
            data[i - 1][1] = row.getCell(1).getStringCellValue();               // Category
            data[i - 1][2] = (int) row.getCell(2).getNumericCellValue();  // PrepTimeMinutes
        }

        workbook.close();
        fis.close();
        return data;
    }
}

Feeding It Into @DataProvider

public class AddRecipeTest {

    @DataProvider(name = "recipeData")
    public Object[][] getRecipeData() throws IOException {
        return ExcelUtils.readRecipeData("testdata/RecipeTestData.xlsx", "Sheet1");
    }

    @Test(dataProvider = "recipeData")
    public void testAddRecipe(String title, String category, int prepTime) {
        AddRecipePage addRecipePage = new AddRecipePage(driver);
        addRecipePage.enterTitle(title);
        addRecipePage.selectCategory(category);
        addRecipePage.enterPrepTime(prepTime);
        addRecipePage.save();

        Assert.assertTrue(addRecipePage.isRecipeSaved(title),
            "Recipe '" + title + "' did not save successfully");
    }
}

Option 2: Plain CSV (No Extra Dependency)

Not every team wants the POI dependency (it's fairly heavy) just to read some rows. If your data doesn't need Excel formulas, multiple sheets, or formatting, a plain .csv file read with BufferedReader does the same job with zero extra libraries:

Title,Category,PrepTimeMinutes
Paneer Butter Masala,Main Course,35
Masala Dosa,Breakfast,25
Chocolate Lava Cake,Dessert,20
Veg Hakka Noodles,Main Course,15

public class CsvUtils {

    public static Object[][] readRecipeCsv(String filePath) throws IOException {
        List<String[]> rows = new ArrayList<>();
        BufferedReader reader = new BufferedReader(new FileReader(filePath));

        String line;
        boolean isHeader = true;
        while ((line = reader.readLine()) != null) {
            if (isHeader) { isHeader = false; continue; } // skip header row
            rows.add(line.split(","));
        }
        reader.close();

        Object[][] data = new Object[rows.size()][3];
        for (int i = 0; i < rows.size(); i++) {
            String[] cols = rows.get(i);
            data[i][0] = cols[0];
            data[i][1] = cols[1];
            data[i][2] = Integer.parseInt(cols[2]);
        }
        return data;
    }
}