Practical 7: Data-Driven Testing with Apache POI

Get access to whole semester resourcesSemester Pass

Chapter Ten

Syllabus topic Module 1, "Data-Driven Testing Using Excel Integration", "Tool: Selenium WebDriver + Apache POI Write a program to update 10 student records in an Excel file and validate changes on the web application. Perform data reading, writing, and verification using Apache POI library."

Pages 71 to 76 of 162

Aim

To keep the data for a test in an Excel file, to update ten student records on the web application from that file, to validate every change on the application, and to write each result back into the file, using Apache POI for the reading and the writing.

What you need to know before you start

Data-driven testing separates the test's steps from the test's data. The steps are written once, in code: open the record, change it, save it, check it. The data, which record and what to change it to, lives in a table outside the code. Ten records, or a thousand, are the same program run over more rows. A tester can add a case without touching Java at all.

Apache POI is the Java library that reads and writes Microsoft Office files. For Excel's modern .xlsx format its classes start with XSSF: XSSFWorkbook is a whole file. POI's quick guide shows the interface names that work for both old and new formats: a Workbook holds Sheets, a Sheet holds Rows, and a Row holds Cells. Rows and cells are numbered from 0, so row 0 is the heading row and Excel's row 2 is POI's row 1.

A cell has a type. A cell holding 75 is numeric, and POI returns numbers as double, so reading it naively gives 75.0. POI's quick guide offers DataFormatter, which returns the text exactly as Excel would display it, 75. That one class prevents the commonest mistake in this practical.

Reading, writing and verification are MU's three words:

Read each row of updates from the workbook.
Apply it on the web page, and verify on the page that the change took, or that the page refused it when it should.
Write the result into the same row, and save the workbook.

The pages under test

practice/students.php lists the ten records. Each row has an Edit link, and the table rows carry ids of the form row-101, which makes each record easy to find:

<?php
require 'inc/store.php';
$students = load_records('students');
$title = 'Student Records';
include 'inc/top.php';
?>
<h1>Student Records</h1>
<?php if (isset($_GET['updated'])): ?>
  <p id="message" class="success">Record <?= (int) $_GET['updated'] ?> updated.</p>
<?php endif; ?>
<table id="students">
  <tr><th>Roll</th><th>Name</th><th>Course</th><th>City</th><th>Marks</th><th></th></tr>
<?php foreach ($students as $s): ?>
  <tr id="row-<?= $s['roll'] ?>">
    <td><?= $s['roll'] ?></td>
    <td><?= htmlspecialchars($s['name']) ?></td>
    <td><?= htmlspecialchars($s['course']) ?></td>
    <td><?= htmlspecialchars($s['city']) ?></td>
    <td><?= $s['marks'] ?></td>
    <td><a href="edit-student.php?roll=<?= $s['roll'] ?>">Edit</a></td>
  </tr>
<?php endforeach; ?>
</table>
<?php include 'inc/bottom.php'; ?>

practice/edit-student.php edits one record's city and marks, and refuses marks that are not a whole number from 0 to 100:

<?php
require 'inc/store.php';
$students = load_records('students');
$roll = (int) ($_GET['roll'] ?? 0);
$index = null;
foreach ($students as $i => $s) {
    if ($s['roll'] === $roll) { $index = $i; }
}
if ($index === null) {
    http_response_code(404);
    exit('No student with roll number ' . $roll);
}
$error = '';
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $city = trim($_POST['city'] ?? '');
    $marks = $_POST['marks'] ?? '';
    if ($city === '') {
        $error = 'City cannot be empty.';
    } elseif (!ctype_digit($marks) || (int) $marks > 100) {
        $error = 'Marks must be a whole number from 0 to 100.';
    } else {
        $students[$index]['city'] = $city;
        $students[$index]['marks'] = (int) $marks;
        save_records('students', $students);
        header('Location: students.php?updated=' . $roll);
        exit;
    }
}
$s = $students[$index];
$title = 'Edit Student';
include 'inc/top.php';
?>
<h1>Edit Student <?= $s['roll'] ?></h1>
<p>Name: <span id="name"><?= htmlspecialchars($s['name']) ?></span></p>
<?php if ($error !== ''): ?>
  <p id="error" class="error"><?= htmlspecialchars($error) ?></p>
<?php endif; ?>
<form method="post">
  <label>City <input type="text" name="city" id="city" value="<?= htmlspecialchars($s['city']) ?>"></label>
  <label>Marks <input type="text" name="marks" id="marks" value="<?= $s['marks'] ?>"></label>
  <p><button type="submit" id="save">Save</button></p>
</form>
<?php include 'inc/bottom.php'; ?>

Practical 7: Data-Driven Testing with Apache POI

The records start as the ten in seed/students.json, printed in Practical 1. After this practical changes them, localhost:8080/reset.php puts them back.

Step 1: the workbook of updates

The workbook has one row per update. Expected says what the application should do with it: nine updates should be saved, and the last, marks of 105, should be refused. A data-driven test must include the rows that should fail. Result and Remark are left empty for the program to fill.

Roll	New city	New marks	Expected
101	Pune	75	updated
102	Mumbai	68	updated
103	Thane	61	updated
104	Nashik	85	updated
105	Pune	52	updated
106	Thane	79	updated
107	Mumbai	93	updated
108	Nashik	66	updated
109	Pune	57	updated
110	Mumbai	105	rejected

You can type this into Excel or Google Sheets and save it as student-updates.xlsx in the project folder. Or let POI make it, which is the writing half of the practical in its simplest form. Create practicals/MakeUpdates.java:

package practicals;

import java.io.FileOutputStream;

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class MakeUpdates {
    public static void main(String[] args) throws Exception {
        String[] heading = {"Roll", "New city", "New marks", "Expected", "Result", "Remark"};
        Object[][] updates = {
            {101, "Pune", 75, "updated"}, {102, "Mumbai", 68, "updated"},
            {103, "Thane", 61, "updated"}, {104, "Nashik", 85, "updated"},
            {105, "Pune", 52, "updated"}, {106, "Thane", 79, "updated"},
            {107, "Mumbai", 93, "updated"}, {108, "Nashik", 66, "updated"},
            {109, "Pune", 57, "updated"}, {110, "Mumbai", 105, "rejected"},
        };
        try (Workbook book = new XSSFWorkbook()) {
            Sheet sheet = book.createSheet("Updates");
            Row top = sheet.createRow(0);
            for (int c = 0; c < heading.length; c++) {
                top.createCell(c).setCellValue(heading[c]);
            }
            for (int r = 0; r < updates.length; r++) {
                Row row = sheet.createRow(r + 1);
                row.createCell(0).setCellValue((Integer) updates[r][0]);   // a number cell
                row.createCell(1).setCellValue((String) updates[r][1]);    // a text cell
                row.createCell(2).setCellValue((Integer) updates[r][2]);
                row.createCell(3).setCellValue((String) updates[r][3]);
            }
            try (FileOutputStream out = new FileOutputStream("student-updates.xlsx")) {
                book.write(out);
            }
            System.out.println("Wrote student-updates.xlsx: " + updates.length + " updates on sheet "
                    + sheet.getSheetName());
        }
    }
}

Practical 7: Data-Driven Testing with Apache POI

Wrote student-updates.xlsx: 10 updates on sheet Updates

try (...) closes the workbook and the file even if something goes wrong halfway, and a workbook that is never closed can leave the file incomplete.

Why every cell is read through DataFormatter

Two small facts decide whether a POI program works on the first try, and this program shows both on the workbook just made. Create practicals/CellTypes.java:

package practicals;

import java.io.FileInputStream;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class CellTypes {
    public static void main(String[] args) throws Exception {
        try (Workbook book = new XSSFWorkbook(new FileInputStream("student-updates.xlsx"))) {
            Row first = book.getSheet("Updates").getRow(1);
            Cell marks = first.getCell(2);
            System.out.println("Cell type of New marks: " + marks.getCellType());
            System.out.println("getNumericCellValue():  " + marks.getNumericCellValue());
            System.out.println("DataFormatter:          " + new DataFormatter().formatCellValue(marks));

            Cell result = first.getCell(4);            // nobody has written a Result yet
            System.out.println("Result cell object:     " + result);
            System.out.println("DataFormatter on it:    [" + new DataFormatter().formatCellValue(result) + "]");
        }
    }
}

Cell type of New marks: NUMERIC
getNumericCellValue():  75.0
DataFormatter:          75
Result cell object:     null
DataFormatter on it:    []

The marks were written as a number, so the cell's type is NUMERIC, and POI hands back every number as a double: 75.0. Put that into the web page's marks box and the page, which wants a whole number, refuses it. DataFormatter returns 75, exactly what Excel shows. And the Result cell, which nothing has written yet, does not exist: getCell(4) returned null. DataFormatter turns that into an empty string where calling any method on null would have thrown NullPointerException.

Step 2: read, update, verify, write

The main program. For every row it reads the update, makes it through the edit page, checks the students page, and writes what happened into the Result and Remark cells. Create UpdateStudents.java, in the same practicals folder:

package practicals;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.time.Duration;

import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.chrome.ChromeDriver;
import org.openqa.selenium.support.ui.ExpectedConditions;
import org.openqa.selenium.support.ui.WebDriverWait;

public class UpdateStudents {
    static final String SITE = "http://localhost:8080/";
    static final String FILE = "student-updates.xlsx";

    public static void main(String[] args) throws Exception {
        DataFormatter text = new DataFormatter();   // cells as Excel shows them: 75, not 75.0
        WebDriver driver = new ChromeDriver();
        WebDriverWait wait = new WebDriverWait(driver, Duration.ofSeconds(10));
        int passed = 0, failed = 0;
        try (Workbook book = new XSSFWorkbook(new FileInputStream(FILE))) {
            Sheet sheet = book.getSheet("Updates");
            for (int r = 1; r <= sheet.getLastRowNum(); r++) {
                Row row = sheet.getRow(r);
                String roll = text.formatCellValue(row.getCell(0));
                String city = text.formatCellValue(row.getCell(1));
                String marks = text.formatCellValue(row.getCell(2));
                String expected = text.formatCellValue(row.getCell(3));

                // Apply the update on the web page.
                driver.get(SITE + "edit-student.php?roll=" + roll);
                WebElement cityBox = driver.findElement(By.id("city"));
                cityBox.clear();
                cityBox.sendKeys(city);
                WebElement marksBox = driver.findElement(By.id("marks"));
                marksBox.clear();
                marksBox.sendKeys(marks);
                driver.findElement(By.id("save")).click();

                // Verify on the page what actually happened. A saved record sends the
                // browser to students.php?updated=<roll>: wait for that page first.
                String result, remark;
                if (expected.equals("updated")) {
                    wait.until(ExpectedConditions.urlContains("updated=" + roll));
                    WebElement tr = driver.findElement(By.id("row-" + roll));
                    String shownCity = tr.findElement(By.xpath("td[4]")).getText();
                    String shownMarks = tr.findElement(By.xpath("td[5]")).getText();
                    boolean ok = shownCity.equals(city) && shownMarks.equals(marks);
                    result = ok ? "PASS" : "FAIL";
                    remark = "page shows " + shownCity + ", " + shownMarks;
                } else {
                    String error = wait.until(ExpectedConditions.visibilityOfElementLocated(By.id("error"))).getText();
                    result = error.startsWith("Marks must be") ? "PASS" : "FAIL";
                    remark = "page refused: " + error;
                }
                if (result.equals("PASS")) passed++; else failed++;

                // Write the result back into the same row.
                row.createCell(4).setCellValue(result);
                row.createCell(5).setCellValue(remark);
                System.out.println(roll + "  " + city + ", " + marks + "  expected " + expected + "  " + result);
            }
            try (FileOutputStream out = new FileOutputStream(FILE)) {
                book.write(out);
            }
        } finally {
            driver.quit();
        }
        System.out.println("Records processed: " + (passed + failed) + ", PASS: " + passed + ", FAIL: " + failed);
    }
}

Practical 7: Data-Driven Testing with Apache POI

101  Pune, 75  expected updated  PASS
102  Mumbai, 68  expected updated  PASS
103  Thane, 61  expected updated  PASS
104  Nashik, 85  expected updated  PASS
105  Pune, 52  expected updated  PASS
106  Thane, 79  expected updated  PASS
107  Mumbai, 93  expected updated  PASS
108  Nashik, 66  expected updated  PASS
109  Pune, 57  expected updated  PASS
110  Mumbai, 105  expected rejected  PASS
Records processed: 10, PASS: 10, FAIL: 0

Wait for the page the save leads to. A saved record sends the browser on to students.php?updated=101, and the program waits for exactly that address before it reads anything. The first version of this program did not: it clicked Save and at once opened the students page itself. Run three times in the laboratory, it failed a different row each time, 105 on one run, 103 and 106 on another, and on the third it crashed looking for an element, because it sometimes read the list before the save had reached the server. That is the flaky test Practical 6 described, met for real, and the cure is the same: wait for the page's own sign that the step is done.

How the verification finds the right values: By.id("row-" + roll) finds the table row for that student, and td[4] and td[5], searched from that row, are its fourth and fifth cells, city and marks. An XPath that starts without // searches from the element it is called on, which is what keeps the check to the right student.

Practical 7: Data-Driven Testing with Apache POI

The tenth row is the one worth pointing at in the journal. The page refused marks of 105 with its own message, which is what the row expected, so the row passes. A data-driven test that only ever feeds good data tests half the page.

Step 3: read back what was written

The last step proves the writing: open the workbook again and print it. Create practicals/ReadResults.java:

package practicals;

import java.io.FileInputStream;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ReadResults {
    public static void main(String[] args) throws Exception {
        DataFormatter text = new DataFormatter();
        try (Workbook book = new XSSFWorkbook(new FileInputStream("student-updates.xlsx"))) {
            Sheet sheet = book.getSheet("Updates");
            for (Row row : sheet) {
                StringBuilder line = new StringBuilder();
                for (Cell cell : row) {
                    line.append(String.format("%-10s", text.formatCellValue(cell)));
                }
                System.out.println(line.toString().stripTrailing());
            }
        }
    }
}

Roll      New city  New marks Expected  Result    Remark
101       Pune      75        updated   PASS      page shows Pune, 75
102       Mumbai    68        updated   PASS      page shows Mumbai, 68
103       Thane     61        updated   PASS      page shows Thane, 61
104       Nashik    85        updated   PASS      page shows Nashik, 85
105       Pune      52        updated   PASS      page shows Pune, 52
106       Thane     79        updated   PASS      page shows Thane, 79
107       Mumbai    93        updated   PASS      page shows Mumbai, 93
108       Nashik    66        updated   PASS      page shows Nashik, 66
109       Pune      57        updated   PASS      page shows Pune, 57
110       Mumbai    105       rejected  PASS      page refused: Marks must be a whole number from 0 to 100.

The Result and Remark columns are now filled, in the same file the test read its data from: that file is both the test's input and its report.

Step 4: the same data, a different run

Change a row in the workbook, for example 103's city to Nashik, and run UpdateStudents again without touching the code. That is the point of data-driven testing: the test changed, the program did not. Reset the site first (reset.php) if you want every row to start from the seed.

Observations

Rows read	Updates saved and verified	Refusals verified	FAIL
10	9	1	0

The workbook's Result column holds PASS for all ten rows, and its Remark column records what the page showed for each.

Result

Ten student updates were read from an Excel workbook with Apache POI and applied on the Practice Portal by Selenium WebDriver. Nine were saved, and each was verified on the students page; the tenth, marks of 105, was refused by the page as expected. The result of every row was written back into the workbook and read back from it.

Where marks are lost

Reading a number cell as a number. You get 75.0. Use DataFormatter.

Practical 7: Data-Driven Testing with Apache POI

Forgetting row 0 is the heading. Start the loop at row 1.

Checking the page before saving, or the wrong row. Find the student's own table row and read its cells from there.

No failing data. Include at least one row the application should refuse, with the expectation written in the file.

Not closing the workbook. Use try-with-resources, or call close(). On Windows, close the file in Excel too before the program writes to it.

Hard-coding the ten records in Java. Then it is not data-driven. The data belongs in the file.

For the journal

Aim; tool (Selenium WebDriver 4.49 and Apache POI 5.5.1); the workbook's layout with its Expected column; the three programs; their output; the final contents of the workbook; the observations table; the result.

Quick revision

Data-driven testing: steps in code, data in a file; add rows, not code.
POI: Workbook, Sheet, Row, Cell. .xlsx is XSSF. Rows and cells count from 0.
Read cells with a DataFormatter and its formatCellValue(cell): it gives what Excel shows and handles null.
Write with createCell(c) on the row and setCellValue(v) on the cell, then book.write(out).
Verify on the page: find the student's row by id, then its cells.
Put expected failures in the data.
try-with-resources closes the workbook and the file.

Questions you must be able to answer

1. What is data-driven testing? Running the same test steps over many sets of data kept outside the code, so that cases are added by adding rows, not code.

2. Why does reading marks of 75 give 75.0? POI returns every numeric cell as a double. DataFormatter returns the value as Excel displays it.

3. What are Workbook, Sheet, Row and Cell? The file, one tab in it, one row of a tab, and one cell of a row. POI numbers rows and cells from 0.

4. How does the program know it is reading the right student's marks on the page? It finds the table row whose id is row- followed by the roll number, and reads the cells from that row.

5. Why is the row with marks of 105 marked PASS? Its expected result was that the page would refuse it, and the page did, with its own error message.

6. What happens if you call a method on a cell that was never created? getCell returns null, and calling a method on null throws NullPointerException. DataFormatter accepts null.

The rest of this subject

These notes are cut from the University's printed syllabus. Open the syllabus itself for the same subject.

Syllabus for Software Testing and Quality Assurance Practical

Issue