APACHE POI EXCEL INGESTION: WORKBOOK MATRICES, FORMATTING & DATAPROVIDERS

Architect high-capacity, multi-sheet test matrix automation in REST Assured: Apache POI integration (XSSFWorkbook & HSSFWorkbook), DataFormatter cell coercion, FormulaEvaluator processing, blank/null cell handling, dynamic header-to-column key mapping, Map<String, String> row streaming, and TestNG @DataProvider binding.

πŸ“‘ 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.

1. Apache POI Architecture & REST Assured Data Pipeline Topography
The end-to-end architectural flow of Excel spreadsheet ingestion, showing how physical workbook files are parsed into sheet rows, normalized through DataFormatter and FormulaEvaluator, and injected into TestNG execution threads.
Apache POI Ingestion & TestNG DataProvider Pipeline Architecture
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      β”‚
β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜      β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜
Architectural Standard: Never extract cell values using cell.getStringCellValue() directly on unknown cells. If a numeric ID or status code like 200 is stored, POI throws IllegalStateException. Always normalize values through org.apache.poi.ss.usermodel.DataFormatter to guarantee safe String extraction across all cell types.
Real-Time Example 1: Robust Excel Utility Class (ExcelReader.java)
A production-ready Apache POI utility class. Reads any .xlsx spreadsheet by sheet name, normalizes cell types via DataFormatter, handles null/blank cells safely, and outputs a clean two-dimensional Object[][] array.
Java (ExcelReader.java Standard OOXML Utility)
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);
        }
    }
}
Utility Design Highlights: 1. DataFormatter: Formats numeric cells (e.g., 200 or 16.0) as clean Strings ("200", "16") without decimals. 2. MissingCellPolicy.CREATE_NULL_AS_BLANK: Prevents NullPointerExceptions on unpopulated matrix cells. 3. try-with-resources: Closes FileInputStream and XSSFWorkbook automatically to avoid OS file handle leaks.
Real-Time Example 2: Ingesting Tabular Excel Rows via TestNG @DataProvider
Connecting 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.
Java (Connecting Excel DataProvider to REST Assured)
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);
    }
}
Execution Output (Driven by StudentRegistrationMatrix.xlsx): [EXCEL DDT] Verified TC_01: Nikita Sharma (Track: ENTERPRISE_AUTOMATION) βž” HTTP 201 (SUCCESS) [EXCEL DDT] Verified TC_02: Aarav Deshmukh (Track: FULLSTACK_SDET) βž” HTTP 201 (SUCCESS) [EXCEL DDT] Verified TC_03: Rohan Verma (Track: CLOUD_DEVOPS_QA) βž” HTTP 201 (SUCCESS) [EXCEL DDT] Verified TC_04: Negative Zero Credits (Track: FULLSTACK_SDET) βž” HTTP 422 (UNPROCESSABLE) PASSED: testStudentRegistrationFromExcel (4/4 scenarios successfully processed from Excel)
Real-Time Example 3: Header-to-Value Mapping via Map<String, String> Ingestion
Decoupling method signatures from column positions. Maps each spreadsheet row into a Map<String, String> using column headers as keys. Adding, removing, or reordering Excel columns no longer breaks the Java test method parameter signature.
Java (Map<String, String> Flexible Header-Driven Ingestion)
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);
    }
}
Map Ingestion Telemetry: [MAP DDT] TC_01 - Processed candidate 'Nikita Sharma' βž” Status: 201 [MAP DDT] TC_02 - Processed candidate 'Aarav Deshmukh' βž” Status: 201 [MAP DDT] TC_03 - Processed candidate 'Rohan Verma' βž” Status: 201 Spreadsheet columns can be reordered or new informative columns added without changing test method signatures.
Real-Time Example 4: Evaluating Dynamic Formulas via FormulaEvaluator
Handling calculated spreadsheet cells. Uses Apache POI's FormulaEvaluator to compute cell formulas (e.g. =SUM(B2:C2), string concatenations, or discount calculations) at runtime before passing data into REST Assured.
Java (FormulaEvaluator Dynamic Formula Resolution)
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");
        }
    }
}
Formula Resolution Output: [*] Raw Formula Expression : A2*B2 [*] Evaluated Numeric Value : 24000.0 [*] Evaluated String Value : 24000 FormulaEvaluator evaluated the dynamic formula into scalar data without requiring manual Java arithmetic.
Real-Time Example 5: High-Concurrency Parallel Excel Execution (parallel = true)
Running Excel-driven test suites concurrently. Combines @DataProvider(parallel = true) with thread-safe data mapping to execute multiple Excel rows simultaneously across TestNG worker threads.
Java (Parallel Concurrent Excel DataProvider Execution)
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);
    }
}
Parallel Concurrency Telemetry (4 Concurrent Threads): [Thread-24] START: Executing TC_01 for candidate 'Nikita Sharma' [Thread-25] START: Executing TC_02 for candidate 'Aarav Deshmukh' [Thread-26] START: Executing TC_03 for candidate 'Rohan Verma' [Thread-27] START: Executing TC_04 for candidate 'Negative Zero Credits' [Thread-24] COMPLETE: TC_01 finished successfully. [Thread-26] COMPLETE: TC_03 finished successfully. [Thread-25] COMPLETE: TC_02 finished successfully. [Thread-27] COMPLETE: TC_04 finished successfully. 4 spreadsheet rows processed concurrently in ~130ms total execution time.
Real-Time Example 6: Multi-Sheet Scoping & Environment Matrix Selection
Structuring test suites across multiple tabs in a single workbook. Dynamically selects specific sheets (Dev, QA, Staging, ProdSmoke) based on environment flags passed from the command line or CI configuration.
Java (Multi-Sheet Scoping & Dynamic Environment Selection)
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);
    }
}
Multi-Sheet Telemetry: $ mvn test -Denv=Staging [*] DataProvider loading sheet 'StagingData' for TARGET ENV: [STAGING] [ENV TEST] Dispatched to: https://staging-api.nikita-infotech.com/v1/health βž” Expect: 200 (Tag: STAGE_PASS) [ENV TEST] Dispatched to: https://staging-api.nikita-infotech.com/v1/catalog βž” Expect: 200 (Tag: STAGE_CATALOG) Environment-specific datasets isolated within distinct workbook tabs, eliminating redundant spreadsheet files.
Real-Time Example 7: Transforming Excel Rows into Strongly-Typed Java POJOs
Combining Apache POI with Jackson Databind. Converts extracted Excel row Maps into strongly-typed Java POJOs using ObjectMapper.convertValue(), bridging spreadsheet fixtures into typed domain models for REST Assured serialization.
Java (Mapping Excel Rows to Strongly-Typed POJO Models)
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);
    }
}
POJO Transformation Output: Row 1 Map converted to StudentExcelModel("Nikita Sharma", "nikita@domain.com", "ENTERPRISE_AUTOMATION", 16) [POJO EXCEL] Registered: Nikita Sharma (Credits: 16) successfully. Row 2 Map converted to StudentExcelModel("Aarav Deshmukh", "aarav@domain.com", "FULLSTACK_SDET", 12) [POJO EXCEL] Registered: Aarav Deshmukh (Credits: 12) successfully. Connects tabular Excel rows directly to strongly-typed POJOs with compile-time validation.
Real-Time Example 8: Debugging POI Exceptions & File Handle Leak Triage
Troubleshooting common Apache POI errors: diagnosing IllegalStateException: Cannot get a STRING value from a NUMERIC cell, handling EmptyFileException, preventing memory leaks, and managing cell type mismatches.
Java (Triage: Cell Type Coercion & Stream Leaks)
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!
    // ==========================================================================
}
Triage Diagnostic Breakdown: Failure: IllegalStateException: Cannot get a STRING value from a NUMERIC cell. Root Cause: Excel stores digits (200, 16.0, 411015) under CellType.NUMERIC. Fix Applied: DataFormatter.formatCellValue(cell) checks cell type and returns "200". File Handle Safety: Enclosing streams in try-with-resources eliminates OS workspace lockups.
Real-Time Example 9: Apache POI Excel Ingestion Reference Matrix
A comprehensive reference summary pairing Apache POI classes, methods, cell types, TestNG DataProvider integration patterns, and operational best practices for API test automation architects.
Apache POI Excel Specification Reference Matrix
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.            β”‚
β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜
Engineering Best Practice: Apache POI spreadsheet ingestion bridges business test matrices into automated REST Assured test suites. By combining DataFormatter cell normalization, dynamic Map row mapping, FormulaEvaluator resolution, and parallel TestNG DataProviders, you build resilient, high-throughput data-driven testing frameworks that empower cross-functional teams to manage comprehensive test datasets with ease.