Skip to content

Instantly share code, notes, and snippets.

@h1ddengames

h1ddengames/AbstractTestDataController.java

Last active April 16, 2022 06:22
Show Gist options
  • Save h1ddengames/ffc23441b5ef46114e6912291f9e17ea to your computer and use it in GitHub Desktop.
Save h1ddengames/ffc23441b5ef46114e6912291f9e17ea to your computer and use it in GitHub Desktop.
Download ZIP
Reading Excel .xlsx data using Apache POI
Raw
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();
}
Raw
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";
}
}
Raw
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);
}
}
Raw
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");
}
}
Raw
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();
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment