Create Excel sheet with Test Results (Java + Apache POI + TestNG + Cucumber)

  • TestNG Automation Framework With Cucumber
  • Java 8
  • Maven
  • Rest Assured for API test automation
  • Selenium for UI test automation
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
  1. Create a file: This will create excel file in the given location in local machine.
public static void createAFile() throws IOException {
File file = new File("/Users/dilshani/**/testResult.xlsx");
boolean exists = file.exists();
if(exists){
//TODO: Implement this fragment when you are working with few work sheet or while checking for file existence.
// Not required in my use case
}else {
FileOutputStream newFile = new FileOutputStream("testResult.xlsx");
TestResultManager.setFileOutputStream(newFile);
}
public static void createWorkBook(){
Workbook workbook = new XSSFWorkbook();
CreationHelper createHelper = workbook.getCreationHelper();
TestResultManager.setWorkBook(workbook);
}
public static void writeDataInSheet(String sheetName, Map<String, Map<String,Map.Entry<String,String>>> data) {

Workbook workbook = TestResultManager.getWorkbook();

Sheet sheet = workbook.createSheet(sheetName);

// Create a Font for styling header cells
Font headerFont = workbook.createFont();
headerFont.setBold(true);
headerFont.setFontHeightInPoints((short) 14);
headerFont.setColor(IndexedColors.RED.getIndex());

// Create a CellStyle with the font
CellStyle headerCellStyle = workbook.createCellStyle();
headerCellStyle.setFont(headerFont);
// Create a Row
Row headerRow = sheet.createRow(0);

// Create cells
for (int i = 0; i < columns.length; i++) {
Cell cell = headerRow.createCell(i);
cell.setCellValue(columns[i]);
cell.setCellStyle(headerCellStyle);
}

//write data
int rowNum = 1;
for (Map.Entry<String, Map<String, Map.Entry<String, String>>> result : data.entrySet()) {
Row row = sheet.createRow(rowNum++);
String type = result.getKey();
row.createCell(0).setCellValue(type);
for (Map.Entry<String, Map.Entry<String, String>> sensor : result.getValue().entrySet()) {
Row row2 = sheet.createRow(rowNum++);
row2.createCell(1)
.setCellValue(sensor.getKey());
row2.createCell(2)
.setCellValue(sensor.getValue().getKey());
row2.createCell(3)
.setCellValue(sensor.getValue().getValue());
// System.out.println("Type -->"+type + " Sesnor -->" + sensor.getKey()+ " Result --> "+ sensor.getValue());
}
}

// Resize all columns to fit the content size
for (int i = 0; i < columns.length; i++) {
sheet.autoSizeColumn(i);
}

TestResultManager.setWorkBook(workbook);

}
public class TestResultManager {
private static Workbook workbook;

private static FileOutputStream fileOutputStream;

public static FileOutputStream getFileOutputStream() {
return fileOutputStream;
}
public static void setFileOutputStream(FileOutputStream fileOutputStream) {
TestResultManager.fileOutputStream = fileOutputStream;
}

public static void setWorkBook(Workbook workbook){
TestResultManager.workbook=workbook;
}
public static Workbook getWorkbook(){
return workbook;
}

}
    When I give the file of all available sensors for "databank-sensor" from the location "/Users/dilshani/**/sensor.json" with "10" limitation in "prod" with "1440" resolution    Then I should get test results on comparing with raw data on "databank-sensor"
@DataValidationAll
Feature: Verify data matching with Data Bank
As an user of Data Bank
I want to check Data bank data mapping with Raw Data

Scenario: Validate Data Bank & Band at once
When I give the file of all available sensors for "databank-sensor" from the location "/Users/dilshani/**/sensor.json" with "1" limitation in "prod" with "1440" resolution
Then I should get test results on comparing with raw data on "databank-sensor"
When I give the file of all available sensors for "databank-dma" from the location "/Users/dilshani/**/sensor.json" with "0" limitation in "prod" with "1440" resolution
Then I should get test results on comparing with raw data on "databank-dma"
When I give the file of all available sensors for "databand" from the location "/Users/dilshani/**/sensor.json" with "1" limitation in "prod" with "1440" resolution
Then I should get test results on comparing with raw data on "databand"
@Before
public void initializeTest(Scenario scenario) throws IOException, InvalidFormatException {
createAFile();
createWorkBook();

}
@After
public void endTest(Scenario scenario) {

try {
WriteInExcel writeInExcel = new WriteInExcel();
TestResultManager testResultManager = new TestResultManager();
writeInExcel.writeToFile(testResultManager.getFileOutputStream());
} catch (IOException e) {
e.printStackTrace();
}
}
@Then("^I should get test results on comparing with raw data on \"([^\"]*)\"$")
public void i_should_see_same_data_in_databank(String type) throws IOException {
try{
if(type.equals("databank-sensor")){
writeDataForSensorLevelDataBank();
}else if(type.equals("databank-dma")){
writeDataForDMALevelDataBank();
} else if(type.equals("databand")){
writeDataForDMALevelDataBand();
}
} catch (InvalidFormatException e) {
e.printStackTrace();
}
public static void writeDataForDMALevelDataBank() throws IOException, InvalidFormatException {
//For output
WriteInExcel writeInExcel = new WriteInExcel();
writeInExcel.writeDataInSheet("DMALevel",getFinalResult());
}
  • As I wanted to have different sheets for different scenarios, tried to implement exact use case. It was failed due to the limitation of Cucumber. Cucumber does not support feature level listers/hooks. Only support scenario level hooks. (Ref: https://github.com/cucumber/cucumber-jvm/issues/515). Hence implemented all scenarios within the same feature sake of execution.

--

--

--

Techie | Traveler | Writer

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Sandman Version 2 Whitepaper

How to quickly prototype apps with CSS Grid and CSS Variables

Containerizing Python Web Application

Top 10 Free Photography Editing Software in 2019

The Insurance That Needs Losers

Inheritance In Java

Password-based Door lock System

Alexa Device Input-Output Object-Oriented Design

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Dilshani Subasinghe

Dilshani Subasinghe

Techie | Traveler | Writer

More from Medium

Add Trendline to Excel Chart in Java

Java Annotations

Java Threads:

Exception Handling in Java