Reading Excel .xlsx data using Apache POI

AbstractTestDataController.java

package excel.core;

import lombok.Data;
import lombok.experimental.Accessors;

/**
 * Purpose: Required variables/methods for any TestDataControllers.
 *
 * @author Shahid Karim
 * @version 1.0
 * @since 4/15/2022 - 11:01 PM
 */
@Data
@Accessors(chain = true)
public abstract class AbstractTestDataController {
    // ~@Variables
    public TestDataLoader testDataLoader;

    // ~@Methods
    public abstract String getTabName();

    public abstract String getExportName();
}

DRTestDataController.java

package excel.impl;

import excel.core.AbstractTestDataController;

/**
 * Purpose: Sample Implementation of AbstractTestDataController
 *
 * @author Shahid Karim
 * @version 1.0
 * @since 4/12/2022 - 5:16 PM
 */
public class DRTestDataController extends AbstractTestDataController {
    // ~@Methods
    @Override
    public String getTabName() {
        return "DR";
    }

    @Override
    public String getExportName() {
        return "DR TE Excel Data";
    }
}

ExportController.java

package excel.core;

import excel.impl.DRTestDataController;

/**
 * Purpose: Container class for all TestDataControllers.
 *
 * @author Shahid Karim
 * @version 1.0
 * @since 4/12/2022 - 5:13 PM
 */
public class ExportController {
    // ~@Variables
    DRTestDataController drTestDataController = new DRTestDataController();

    private AbstractTestDataController setup(AbstractTestDataController testDataController) {
        return testDataController.setTestDataLoader(
                new TestDataLoader(
                        testDataController.getExportName(),
                        testDataController.getTabName(),
                        "SkynetDataContext.get(CURRENT_FILE_PATH)")
        );
    }

    // ~@Methods
    public AbstractTestDataController withDRTestDataController() {
        return setup(drTestDataController);
    }
}

ExportTest.java

import excel.core.TestDataLoader;
import org.junit.jupiter.api.Test;

/**
 * Purpose: Tests the TestDataLoader.
 *
 * @author Shahid Karim
 * @version 1.0
 * @since 4/12/2022 - 5:14 PM
 */
public class ExportTest {
    // ~@Methods
    @Test
    public void exportTest() {
        String exportName = "Exported Excel Data";
        String filePath = "src/test/resources/excel/PageOne.xlsx";
        String tabName = "Excel";
        TestDataLoader testDataLoader = new TestDataLoader(exportName, tabName, filePath);

        // Even with 5500 rows, it only takes 2 seconds to get all rows.
        testDataLoader
                .verifyTableRowCountIs(5500)
                .withRow(1)
                .withColumn("AID")
                .verifyCellValueIs("180")
                .withColumn("TT")
                .verifyCellValueIs("Abs")
                .withColumn("TT 1")
                .verifyCellValueIs("Rel")
                .withColumn("TT 2")
                .verifyCellValueIs("A")
                .withRow(2)
                .withColumn("AID")
                .verifyCellValueIs("181")
                .withColumn("TT")
                .verifyCellValueIs("Rel")
                .withColumn("TT 1")
                .verifyCellValueIs("Abs")
                .withColumn("TT 2")
                .verifyCellValueIs("V")
                .withRow(5500)
                .withColumn("AID")
                .verifyCellValueIs("5679");
    }
}

TestDataLoader.java

package excel.core;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.assertj.core.api.Assertions;

import java.io.File;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * Purpose: Reads Excel Data from .xlsx files using Apache POI.
 *
 * @author Shahid Karim
 * @version 1.0
 * @since 4/12/2022 - 5:02 PM
 */
public class TestDataLoader {
    // ~@Variables
    private final String mapFormat = "%-40s%s%n";
    private final String listFormat = "%-20s%s%n";

    Integer rowIndex;
    Integer columnIndex;
    String columnName;

    String exportName;
    String tabName;
    File excelFile;

    XSSFWorkbook workbook;
    XSSFSheet sheet;

    Map<String, Integer> columnIndexMap = new LinkedHashMap<>();

    // ~@Constructors
    public TestDataLoader(String exportName, String tabName, String filePath) {
        this.exportName = exportName;
        this.tabName = tabName;
        this.excelFile = new File(filePath);
        if(excelFile.exists()) {
            try {
                workbook = new XSSFWorkbook(excelFile);
                sheet = workbook.getSheet(tabName);
            } catch(Exception e) {
                throw new RuntimeException("Unable to open file: " + filePath + " or tab does not exist: " + tabName);
            }
        } else {
            throw new RuntimeException("File does not exist: " + filePath);
        }
    }

    // ~@Methods
    public TestDataLoader withRow(Integer rowIndex) {
        this.rowIndex = rowIndex;
        return this;
    }

    public TestDataLoader withColumn(String columnName) {
        this.columnName = columnName;
        this.columnIndex = fromValue(columnName);
        return this;
    }

    private void printVerificationData(String expectedValue, String actualValue) {
        System.out.printf(mapFormat, "Verifying cell with row index:", rowIndex);
        System.out.printf(mapFormat, "Verifying cell with column index:", columnIndex);
        System.out.printf(mapFormat, "Verifying cell with column name:", columnName);
        System.out.printf(mapFormat, "Verifying cell with expected value:", expectedValue);
        System.out.printf(mapFormat, "Verifying cell with actual value:", actualValue);
        System.out.println();
    }

    public TestDataLoader verifyCellValueIs(String expectedValue) {
        List<String> row = getRow(rowIndex);
        String actualValue = row.get(columnIndex);
        printVerificationData("[EXACTLY MATCHES]: " + expectedValue, actualValue);
        Assertions.assertThat(actualValue)
                .as("The value in the cell: [%s] does not match the expected value: [%s].", actualValue, expectedValue)
                .isEqualTo(expectedValue);
        return this;
    }

    public TestDataLoader verifyCellValueContains(String expectedValue) {
        List<String> row = getRow(rowIndex);
        String actualValue = row.get(columnIndex);
        printVerificationData("[CONTAINS]: " + expectedValue, actualValue);
        Assertions.assertThat(actualValue)
                .as("The value in the cell: [%s] does not contain the expected value: [%s].", actualValue, expectedValue)
                .contains(expectedValue);
        return this;
    }

    public TestDataLoader verifyCellValueMatchesPattern(String regex) {
        List<String> row = getRow(rowIndex);
        String actualValue = row.get(columnIndex);
        printVerificationData("[MATCHES REGEX]: " + regex, actualValue);
        Assertions.assertThat(actualValue)
                .as("The value in the cell: [%s] does not match the expected regex: [%s].", actualValue, regex)
                .matches(regex);
        return this;
    }

    // Visible row number in Excel - 1 since column header row is not counted.
    public TestDataLoader verifyTableRowCountIs(int expectedValue) {
        List<List<String>> rows = getAllRows(true);
        Assertions.assertThat(rows.size())
                .as("The amount of rows do not match the expected value.")
                .isEqualTo(expectedValue);
        return this;
    }

    public List<String> getRow(int rowIndex) {
        List<String> row = new ArrayList<>();
        for(Cell cell : sheet.getRow(rowIndex)) {
            row.add(cell.toString());
        }
        return row;
    }

    public List<List<String>> getAllRows(boolean excludeHeader) {
        List<List<String>> rows = new ArrayList<>();
        for(Row currentRow : sheet) {
            List<String> row = new ArrayList<>();
            for(Cell cell : currentRow) {
                row.add(cell.toString());
            }
            rows.add(row);
        }
        if(excludeHeader) {
            rows.remove(0);
        }
        return rows;
    }

    public Map<String, Integer> getColumnIndexMap() {
        if(columnIndexMap.size() != 0) {
            System.out.println("Using cached column index map values.");
            return columnIndexMap;
        }

        System.out.println("Caching new column index map values.");
        Map<String, Integer> duplicateColumnNames = new LinkedHashMap<>();
        List<String> columnNames = getRow(0);
        for(int i = 0; i < columnNames.size(); i++) {
            String columnName = columnNames.get(i);
            if(columnIndexMap.containsKey(columnName)) {
                String duplicatedName = columnName + " " + duplicateColumnNames.merge(columnName, 1, Integer::sum);
                System.out.println("Key: [" + columnName + "] exists. Updating Key to: [" + duplicatedName + "]");
                columnIndexMap.put(duplicatedName, i);
            } else {
                columnIndexMap.put(columnName, i);
            }
        }
        return columnIndexMap;
    }

    public void forceUpdateColumnIndexMap() {
        System.out.println("Clearing map cache.");
        columnIndexMap.clear();
        getColumnIndexMap();
    }

    public Integer fromValue(String columnName) {
        return getColumnIndexMap().get(columnName);
    }

    public void printColumnIndexMap() {
        Map<String, Integer> map = getColumnIndexMap();
        System.out.println();
        System.out.println(exportName + " Column Index Map:");
        System.out.printf(mapFormat, "[ column name ]", "[ column index ]");
        System.out.printf(mapFormat, "---------------", "----------------");
        map.forEach((key, value) ->
                System.out.printf(mapFormat, "[" + key + "]", value));
        System.out.println();
    }

    public void printRow(int rowIndex) {
        System.out.printf(listFormat, "ROW " + rowIndex + ": ", getRow(rowIndex));
        System.out.println();
    }

    public void printRowWithHeader(int rowIndex) {
        System.out.printf(listFormat, "HEADER: ", getRow(0));
        System.out.printf(listFormat, "ROW " + rowIndex + ": ", getRow(rowIndex));
        System.out.println();
    }

    public void printAllRows(boolean excludeHeader) {
        List<List<String>> allRows = getAllRows(excludeHeader);
        for(int i = 0; i < allRows.size(); i++) {
            List<String> innerList = allRows.get(i);
            System.out.printf(listFormat, "ROW " + i + ": ", innerList);
        }
        System.out.println();
    }
}