π Enterprise Spreadsheet Ingestion, Cell Type Normalization & Data Matrix Engines
In enterprise quality engineering environments, functional QA teams, business analysts, and domain stakeholders frequently curate extensive regression matrices, positive and negative boundary sets, and multi-tenant test configurations in Microsoft Excel spreadsheets (.xlsx and .xls). While plain CSV files lack tab separation and cell metadata, Excel workbooks provide multi-sheet scoping, color-coded categorization, and rich formula calculations. However, consuming binary OOXML spreadsheets programmatically in Java requires robust low-level stream handling. Apache POI is the gold standard library for navigating the Open Office XML document model. By coupling XSSFWorkbook with DataFormatter (to eliminate floating-point precision drift on numeric and date cells), resolving computed cells via FormulaEvaluator, and transforming rows into strongly-typed Object[][] matrices or Map<String, String> row projections, automation architects inject rich external spreadsheets directly into REST Assured test pipelines via TestNG @DataProvider factories.
APACHE POI EXCEL INGESTION & REST ASSURED DATA INJECTION TOPOGRAPHY:
βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
β MICROSOFT EXCEL WORKBOOK (src/test/resources/data/StudentTestSuite.xlsx) β
β βββ Sheet: "EnrollmentSmoke" (Fast smoke boundary cases) β
β βββ Sheet: "CreditBoundaries" (Equivalence partitioning & edge cases) β
β βββ Sheet: "SecurityRBAC" (Role permissions, token scopes, expected status codes) β
βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
β
βΌ (Passed to ExcelReader via FileInputStream)
βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
β APACHE POI OOXML PARSING ENGINE (org.apache.poi.xssf.usermodel.XSSFWorkbook) β
β βββ 1. Workbook Factory: Opens workbook via OPCPackage / File handle (Low memory footprint) β
β βββ 2. Sheet Resolver: Target sheet isolated via workbook.getSheet("CreditBoundaries") β
β βββ 3. Formula Evaluator: Calculates cell formulas (e.g., =SUM(C2:D2)) into scalar values β
β βββ 4. DataFormatter Coercion: Converts NUMERIC/DATE/BOOLEAN/FORMULA into clean Java String β
β βββ 5. Header Map Indexer: Maps Column Headers (Row 0) βββΊ Column Index (0, 1, 2... N) β
βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
β
βΌ (Transformed into TestNG Parameter Matrix)
βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
β TESTNG @DATAPROVIDER MATRIX (Object[][] or Iterator<Object[]>) β
β Row 0: Map<String, String> {"TestCase":"TC01", "StudentId":"STU-88219", "Credits":"16", ...} β
β Row 1: Map<String, String> {"TestCase":"TC02", "StudentId":"STU-88220", "Credits":"0", ...} β
β Row 2: Map<String, String> {"TestCase":"TC03", "StudentId":"STU-88221", "Credits":"35", ...} β
βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
β
ββββββββββββββββββββββββββββ΄βββββββββββββββββββββββββββ
βΌ βΌ
ββββββββββββββββββββββββββββββββββββββββββββββββ ββββββββββββββββββββββββββββββββββββββββββββββββ
β WORKER ITERATION 1 (Row 0 Injection) β β WORKER ITERATION 2 (Row 1 Injection) β
ββββββββββββββββββββββββββββββββββββββββββββββββ€ ββββββββββββββββββββββββββββββββββββββββββββββββ€
β @Test(dataProvider = "excelData") β β @Test(dataProvider = "excelData") β
β public void testApi(Map<String, String> row) β β public void testApi(Map<String, String> row) β
β β β β
β given() β β given() β
β .baseUri("https://staging-api...") β β .baseUri("https://staging-api...") β
β .body(buildPayload(row)) β β .body(buildPayload(row)) β
β .when().post("/students/enroll") β β .when().post("/students/enroll") β
β .then().statusCode(Integer.parseInt( β β .then().statusCode(Integer.parseInt( β
β row.get("ExpectedStatus"))); // 201 β β row.get("ExpectedStatus"))); // 422 β
ββββββββββββββββββββββββββββββββββββββββββββββββ ββββββββββββββββββββββββββββββββββββββββββββββββ
.xlsx spreadsheet by sheet name, normalizes cell types via DataFormatter, handles null/blank cells safely, and outputs a clean two-dimensional Object[][] array.
package com.nikita.restassured.excel;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
public class ExcelReader {
private final String filePath;
private final DataFormatter formatter;
public ExcelReader(String filePath) {
this.filePath = filePath;
this.formatter = new DataFormatter(); // Normalizes all cell values into formatted Strings
}
/**
* Reads an entire sheet into a two-dimensional Object array suitable for TestNG @DataProvider
* Skips header row (Row 0) and processes every data row.
*/
public Object[][] getSheetData(String sheetName) {
File file = new File(filePath);
try (FileInputStream fis = new FileInputStream(file);
Workbook workbook = new XSSFWorkbook(fis)) {
Sheet sheet = workbook.getSheet(sheetName);
if (sheet == null) {
throw new IllegalArgumentException("Sheet '" + sheetName + "' does not exist in workbook: " + filePath);
}
int rowCount = sheet.getLastRowNum(); // 0-based index of last row
if (rowCount == 0) {
return new Object[0][0]; // Sheet has only headers or is empty
}
Row headerRow = sheet.getRow(0);
int colCount = headerRow.getLastCellNum(); // Total number of columns
Object[][] data = new Object[rowCount][colCount];
// Iterate through data rows (skip header row 0)
for (int i = 1; i <= rowCount; i++) {
Row row = sheet.getRow(i);
for (int j = 0; j < colCount; j++) {
if (row == null) {
data[i - 1][j] = "";
continue;
}
Cell cell = row.getCell(j, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
// formatCellValue coerces numeric, date, boolean, and string cells to String
data[i - 1][j] = formatter.formatCellValue(cell).trim();
}
}
return data;
} catch (IOException e) {
throw new RuntimeException("Failed to read Excel matrix from: " + filePath, e);
}
}
}
ExcelReader to a TestNG @DataProvider. Ingests spreadsheet rows directly as typed method parameters, builds dynamic JSON request payloads, dispatches POST calls in REST Assured, and asserts outcomes.
package com.nikita.restassured.excel;
import io.restassured.http.ContentType;
import org.testng.annotations.DataProvider;
import org.testng.annotations.Test;
import java.util.HashMap;
import java.util.Map;
import static io.restassured.RestAssured.given;
import static org.hamcrest.Matchers.equalTo;
public class ExcelDataDrivenTest {
private static final String BASE_URI = "https://staging-api.nikita-infotech.com/v1";
private static final String EXCEL_PATH = "src/test/resources/data/StudentRegistrationMatrix.xlsx";
@DataProvider(name = "excelStudentProvider")
public Object[][] provideStudentExcelData() {
ExcelReader excel = new ExcelReader(EXCEL_PATH);
return excel.getSheetData("StudentRegistration");
}
@Test(dataProvider = "excelStudentProvider",
description = "Execute parameterized student registration driven by Excel rows")
public void testStudentRegistrationFromExcel(String testId, String fullName, String email,
String academicTrack, String creditsStr,
String expectedCodeStr, String expectedStatus) {
int credits = Integer.parseInt(creditsStr);
int expectedStatusCode = Integer.parseInt(expectedCodeStr);
Map<String, Object> requestBody = new HashMap<>();
requestBody.put("fullName", fullName);
requestBody.put("email", email);
requestBody.put("academicTrack", academicTrack);
requestBody.put("enrolledCredits", credits);
given()
.baseUri(BASE_URI)
.contentType(ContentType.JSON)
.body(requestBody)
.log().ifValidationFails()
.when()
.post("/students")
.then()
.log().ifValidationFails()
.statusCode(expectedStatusCode)
.body("status", equalTo(expectedStatus));
System.out.printf("[EXCEL DDT] Verified %s: %s (Track: %s) β HTTP %d (%s)%n",
testId, fullName, academicTrack, expectedStatusCode, expectedStatus);
}
}
Map<String, String> using column headers as keys. Adding, removing, or reordering Excel columns no longer breaks the Java test method parameter signature.
package com.nikita.restassured.excel;
import io.restassured.http.ContentType;
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.util.*;
import static io.restassured.RestAssured.given;
import static org.hamcrest.Matchers.equalTo;
public class ExcelMapIngestionTest {
private static final String BASE_URI = "https://staging-api.nikita-infotech.com/v1";
private static final String EXCEL_PATH = "src/test/resources/data/StudentRegistrationMatrix.xlsx";
@DataProvider(name = "excelMapProvider")
public Object[][] provideExcelDataAsMap() throws Exception {
List<Map<String, String>> rowList = new ArrayList<>();
DataFormatter formatter = new DataFormatter();
try (FileInputStream fis = new FileInputStream(new File(EXCEL_PATH));
Workbook workbook = new XSSFWorkbook(fis)) {
Sheet sheet = workbook.getSheet("StudentRegistration");
Row headerRow = sheet.getRow(0);
int colCount = headerRow.getLastCellNum();
// Extract header names
List<String> headers = new ArrayList<>();
for (int c = 0; c < colCount; c++) {
headers.add(formatter.formatCellValue(headerRow.getCell(c)).trim());
}
// Iterate data rows and map each row to header keys
for (int r = 1; r <= sheet.getLastRowNum(); r++) {
Row row = sheet.getRow(r);
if (row == null) continue;
Map<String, String> rowMap = new LinkedHashMap<>();
for (int c = 0; c < colCount; c++) {
Cell cell = row.getCell(c, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
rowMap.put(headers.get(c), formatter.formatCellValue(cell).trim());
}
rowList.add(rowMap);
}
}
// Convert List to Object[][]
Object[][] data = new Object[rowList.size()][1];
for (int i = 0; i < rowList.size(); i++) {
data[i][0] = rowList.get(i);
}
return data;
}
@Test(dataProvider = "excelMapProvider",
description = "Consume spreadsheet rows as dynamic Maps, immune to column order changes")
public void testRegistrationViaMap(Map<String, String> row) {
// Retrieve values cleanly by Excel column header name:
String testCaseId = row.get("TestId");
String name = row.get("FullName");
String email = row.get("Email");
int credits = Integer.parseInt(row.get("Credits"));
int expectedCode = Integer.parseInt(row.get("ExpectedStatusCode"));
Map<String, Object> payload = new HashMap<>();
payload.put("fullName", name);
payload.put("email", email);
payload.put("academicTrack", row.get("AcademicTrack"));
payload.put("enrolledCredits", credits);
given()
.baseUri(BASE_URI)
.contentType(ContentType.JSON)
.body(payload)
.when()
.post("/students")
.then()
.statusCode(expectedCode)
.body("status", equalTo(row.get("ExpectedStatus")));
System.out.printf("[MAP DDT] %s - Processed candidate '%s' β Status: %d%n", testCaseId, name, expectedCode);
}
}
FormulaEvaluator to compute cell formulas (e.g. =SUM(B2:C2), string concatenations, or discount calculations) at runtime before passing data into REST Assured.
package com.nikita.restassured.excel;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.testng.Assert;
import org.testng.annotations.Test;
import java.io.File;
import java.io.FileInputStream;
public class FormulaEvaluationTest {
private static final String EXCEL_PATH = "src/test/resources/data/FeeCalculationMatrix.xlsx";
@Test(description = "Verify FormulaEvaluator resolves dynamic Excel formulas into evaluated values")
public void testEvaluateExcelFormulas() throws Exception {
try (FileInputStream fis = new FileInputStream(new File(EXCEL_PATH));
Workbook workbook = new XSSFWorkbook(fis)) {
Sheet sheet = workbook.getSheet("TuitionFormulas");
// FormulaEvaluator compiles formulas dynamically using workbook cell dependencies
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
DataFormatter formatter = new DataFormatter();
// Row 1 contains: Credits (16) in Cell A2, RatePerCredit (1500) in Cell B2
// Cell C2 contains formula: =A2*B2
Row row = sheet.getRow(1);
Cell formulaCell = row.getCell(2);
Assert.assertEquals(formulaCell.getCellType(), CellType.FORMULA,
"Cell must be recognized as a formula cell!");
// 1. Evaluate formula to obtain cell value
CellValue cellValue = evaluator.evaluate(formulaCell);
double numericCalculatedFee = cellValue.getNumberValue();
// 2. Alternatively format via DataFormatter with evaluator attached:
String formattedFee = formatter.formatCellValue(formulaCell, evaluator);
System.out.println("[*] Raw Formula Expression : " + formulaCell.getCellFormula());
System.out.println("[*] Evaluated Numeric Value : " + numericCalculatedFee);
System.out.println("[*] Evaluated String Value : " + formattedFee);
// 16 * 1500 = 24000
Assert.assertEquals(numericCalculatedFee, 24000.0);
Assert.assertEquals(formattedFee, "24000");
}
}
}
@DataProvider(parallel = true) with thread-safe data mapping to execute multiple Excel rows simultaneously across TestNG worker threads.
package com.nikita.restassured.excel;
import io.restassured.http.ContentType;
import org.testng.annotations.DataProvider;
import org.testng.annotations.Test;
import java.util.Map;
import static io.restassured.RestAssured.given;
import static org.hamcrest.Matchers.equalTo;
public class ParallelExcelDataProviderTest {
private static final String BASE_URI = "https://staging-api.nikita-infotech.com/v1";
private static final String EXCEL_PATH = "src/test/resources/data/StudentRegistrationMatrix.xlsx";
// parallel = true enables concurrent execution of individual spreadsheet rows
@DataProvider(name = "parallelExcelProvider", parallel = true)
public Object[][] provideParallelExcelData() throws Exception {
ExcelMapIngestionTest mapHelper = new ExcelMapIngestionTest();
return mapHelper.provideExcelDataAsMap();
}
@Test(dataProvider = "parallelExcelProvider",
description = "Execute spreadsheet scenarios concurrently across parallel TestNG threads")
public void testParallelExcelRowExecution(Map<String, String> rowData) {
long threadId = Thread.currentThread().getId();
String studentName = rowData.get("FullName");
String testId = rowData.get("TestId");
System.out.printf("[Thread-%d] START: Executing %s for candidate '%s'%n", threadId, testId, studentName);
given()
.baseUri(BASE_URI)
.contentType(ContentType.JSON)
.body(rowData)
.when()
.post("/students/echo-test")
.then()
.statusCode(200)
.body("status", equalTo("PROCESSED"));
System.out.printf("[Thread-%d] COMPLETE: %s finished successfully.%n", threadId, testId);
}
}
Dev, QA, Staging, ProdSmoke) based on environment flags passed from the command line or CI configuration.
package com.nikita.restassured.excel;
import org.testng.annotations.DataProvider;
import org.testng.annotations.Test;
import java.lang.reflect.Method;
public class MultiSheetEnvironmentTest {
private static final String EXCEL_PATH = "src/test/resources/data/EnvironmentMatrix.xlsx";
@DataProvider(name = "environmentSheetProvider")
public Object[][] provideEnvironmentData(Method method) {
// Read target environment from JVM CLI flag: -Denv=QA or -Denv=Staging (Default: QA)
String env = System.getProperty("env", "QA").trim();
String targetSheetName;
switch (env.toUpperCase()) {
case "DEV":
targetSheetName = "DevData";
break;
case "STAGING":
targetSheetName = "StagingData";
break;
case "QA":
default:
targetSheetName = "QAData";
break;
}
System.out.printf("[*] DataProvider loading sheet '%s' for TARGET ENV: [%s]%n",
targetSheetName, env.toUpperCase());
ExcelReader reader = new ExcelReader(EXCEL_PATH);
return reader.getSheetData(targetSheetName);
}
@Test(dataProvider = "environmentSheetProvider",
description = "Execute test with dataset resolved dynamically from target environment sheet")
public void testDynamicEnvironmentSheetData(String endpointUrl, String expectedStatus, String verificationTag) {
System.out.printf("[ENV TEST] Dispatched to: %s β Expect: %s (Tag: %s)%n",
endpointUrl, expectedStatus, verificationTag);
}
}
ObjectMapper.convertValue(), bridging spreadsheet fixtures into typed domain models for REST Assured serialization.
package com.nikita.restassured.excel;
import com.fasterxml.jackson.annotation.JsonIgnoreProperties;
import com.fasterxml.jackson.annotation.JsonProperty;
import com.fasterxml.jackson.databind.ObjectMapper;
import io.restassured.http.ContentType;
import org.testng.annotations.DataProvider;
import org.testng.annotations.Test;
import java.util.Map;
import static io.restassured.RestAssured.given;
import static org.hamcrest.Matchers.equalTo;
@JsonIgnoreProperties(ignoreUnknown = true)
class StudentExcelModel {
@JsonProperty("FullName") public String fullName;
@JsonProperty("Email") public String email;
@JsonProperty("AcademicTrack") public String academicTrack;
@JsonProperty("Credits") public int credits;
@JsonProperty("ExpectedStatus")public String expectedStatus;
}
public class ExcelPojoTransformationTest {
private static final String BASE_URI = "https://staging-api.nikita-infotech.com/v1";
private static final ObjectMapper MAPPER = new ObjectMapper();
@DataProvider(name = "excelPojoProvider")
public Object[][] provideExcelPojos() throws Exception {
ExcelMapIngestionTest helper = new ExcelMapIngestionTest();
Object[][] rawMapData = helper.provideExcelDataAsMap();
Object[][] pojoData = new Object[rawMapData.length][1];
for (int i = 0; i < rawMapData.length; i++) {
@SuppressWarnings("unchecked")
Map<String, String> rowMap = (Map<String, String>) rawMapData[i][0];
// Jackson maps string map keys to annotated POJO fields automatically:
StudentExcelModel model = MAPPER.convertValue(rowMap, StudentExcelModel.class);
pojoData[i][0] = model;
}
return pojoData;
}
@Test(dataProvider = "excelPojoProvider",
description = "Execute REST Assured request using strongly-typed POJO built from Excel row")
public void testStudentRegistrationViaExcelPojo(StudentExcelModel student) {
given()
.baseUri(BASE_URI)
.contentType(ContentType.JSON)
// Jackson serializes the POJO directly to outbound JSON body:
.body(student)
.when()
.post("/students")
.then()
.statusCode(201)
.body("data.fullName", equalTo(student.fullName));
System.out.printf("[POJO EXCEL] Registered: %s (Credits: %d) successfully.%n",
student.fullName, student.credits);
}
}
IllegalStateException: Cannot get a STRING value from a NUMERIC cell, handling EmptyFileException, preventing memory leaks, and managing cell type mismatches.
package com.nikita.restassured.excel;
import org.apache.poi.ss.usermodel.*;
import org.testng.Assert;
import org.testng.annotations.Test;
public class ExcelTriageTest {
// ==========================================================================
// PITFALL 1: CALLING cell.getStringCellValue() ON A NUMERIC CELL
// SYMPTOM: java.lang.IllegalStateException:
// Cannot get a STRING value from a NUMERIC cell
//
// CAUSE: Excel formats numbers, dates, and currency as NUMERIC cell types.
// Directly invoking getStringCellValue() fails immediately on integers like 200.
//
// SOLUTION: Use DataFormatter.formatCellValue(cell) which safely inspects
// the cell type and converts numbers, dates, and booleans into clean text.
// ==========================================================================
@Test(description = "Demonstrate safe extraction on numeric cells using DataFormatter vs getStringCellValue")
public void testSafeNumericCellExtraction() {
// Mock workbook with numeric cell containing integer 200
Workbook workbook = new org.apache.poi.xssf.usermodel.XSSFWorkbook();
Sheet sheet = workbook.createSheet("Triage");
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
cell.setCellValue(200); // Stored as NUMERIC cell
// INCORRECT APPROACH:
// cell.getStringCellValue(); // Throws IllegalStateException!
// CORRECT APPROACH:
DataFormatter formatter = new DataFormatter();
String safeExtractedValue = formatter.formatCellValue(cell);
System.out.println("[+] Safely extracted numeric cell via DataFormatter: " + safeExtractedValue);
Assert.assertEquals(safeExtractedValue, "200");
}
// ==========================================================================
// PITFALL 2: UNCLOSED FILE STREAMS LOCKING EXCEL FILES IN WINDOWS / CI
// Opening new FileInputStream without closing prevents test reports from writing
// or locks files in continuous integration workspaces.
// FIX: Always enclose Workbook and FileInputStream in try-with-resources blocks!
// ==========================================================================
}
APACHE POI EXCEL INGESTION SPECIFICATION MATRIX:
βββββββββββββββββββββββββββ¬βββββββββββββββββββββββββββββββ¬βββββββββββββββββββ¬ββββββββββββββββββββββββββββββββ
β POI COMPONENT / CLASS β METHOD / FACTORY SIGNATURE β RETURN TYPE β PRIMARY OPERATIONAL PURPOSE β
βββββββββββββββββββββββββββΌβββββββββββββββββββββββββββββββΌβββββββββββββββββββΌββββββββββββββββββββββββββββββββ€
β XSSFWorkbook β new XSSFWorkbook(fileStream) β Workbook β Reads modern .xlsx OOXML filesβ
β HSSFWorkbook β new HSSFWorkbook(fileStream) β Workbook β Reads legacy .xls binary filesβ
β DataFormatter β formatter.formatCellValue(c) β String β Universal safe cell coercion β
β FormulaEvaluator β evaluator.evaluate(cell) β CellValue β Computes formulas (=SUM, etc.)β
β MissingCellPolicy β CREATE_NULL_AS_BLANK β MissingCellPolicyβ Prevents NPEs on empty cells β
β Sheet Navigation β workbook.getSheet(name) β Sheet β Isolates target tab in book β
β Row / Column Bounds β sheet.getLastRowNum() β int (0-based) β Determines data row count β
β Header Cell Extraction β row.getLastCellNum() β short / int β Determines total column count β
β Map Projection β Map<String, String> rowMap β Key-Value Row β Decouples columns from order β
β POJO Transformation β MAPPER.convertValue(row, Cls)β Typed Object β Bridges Excel rows into POJOs β
β TestNG DataProvider β @DataProvider(parallel=true) β Object[][] β Concurrent parameterized runs β
βββββββββββββββββββββββββββ΄βββββββββββββββββββββββββββββββ΄βββββββββββββββββββ΄ββββββββββββββββββββββββββββββββ€
β TOP 5 APACHE POI BEST PRACTICES: β
β 1. Always use DataFormatter: Avoid IllegalStateException by normalizing all cell types into Strings. β
β 2. Use try-with-resources on Workbooks: Prevent Windows and CI file handle locks and memory leaks. β
β 3. Prefer Map<String, String> row ingestion: Protect tests from breaking when Excel columns move. β
β 4. Leverage multi-sheet workbooks for environments: Store Dev, QA, and Staging data in separate tabs. β
β 5. Enable parallel = true on DataProviders: Speed up execution across large test spreadsheets. β
βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ