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

The Tetris Proof

MySQL VS MsSQL Syntax | Code Factory

The future of voice assistants: a personal digital clone?? Part 2

🤖 Quiz Chatbot Project from Front-end Perspective

Alpha Leak Sunday: Automating Moving Average strategies

Don’t do right the wrong thing

Odoo ERP Can Do Magic In Event Management

Introducing ‘Made With GraphQL’

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

Database Testing using Selenium WebDriver.

Automation Testing (with Java, Selenium)

Setting up a Selenium Pipeline In Azure DevOps

See what you test in Selenium with the lemoncheesecake Python test framework