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

This a quick guide on writing test results in excel sheet at the end of your automation testing.

Use Case: Writing one cucumber test scenario test results in excel sheet

Tech Stack:

• TestNG Automation Framework With Cucumber
• Java 8
• Maven
• Rest Assured for API test automation
• Selenium for UI test automation

Integration of Excel Sheet Output for Automation Tests

Apache POI: This is an open source project run by the Apache Software Foundation, and previously a sub-project of the Jakarta Project, provides pure Java libraries for reading and writing files in Microsoft Office formats, such as Word, PowerPoint and Excel.

Maven Dependancy:

Include following dependency in POM.xml file

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.17</version>
</dependency>

Note: Version can be differ with latest updates

Implementation

I have maintained separate methods for managing excel sheet within the same java class (“WriteInExcel”).

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);
    }

File name should be altered. And implement file existence check according to the use case. I didn’t want to use for my use case.

2. Create Work Book

public static void createWorkBook(){
        Workbook workbook = new XSSFWorkbook();
        CreationHelper createHelper = workbook.getCreationHelper();
        TestResultManager.setWorkBook(workbook);
    }

3. Write data in the sheet:

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);

}

This method will take sheet name and data set that should be writing to the exact sheet as parameters. Adding to rows in sheet will be differ with the data set you are planning to pass.

I maintain One class to keep getters and setters for few variables. As Im planning to add few sheets in to same work book in different tests, thought of maintaining few variables in separate class (Like workbook reference)

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;
    }

}

Above is enough for the java implementation of creating Excel file for test results. As I have use this in Cucumber Automation Test framework following steps added who interested to know how to have multiple sheets within same excel file for different tests.

Cucumber Feature structure

In one feature I have two steps for one test. Even though it’s better to have different scenarios for test cases, due to limitation of Cucumber had to implement all testing cases for one round under one feature.

    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"

Test case explanation: In my scenario, I will be calling two different APIs and get data accordingly. Then validate the response to make sure that those are mapping each other with given conditions.

I passing JSON file (location “/Users/dilshani/**/sensor.json”)with all sensors and will be taking random number of sensors (“10” limitation ) and make two calls for each with given resolution (“1440” resolution).

After this test all result will be written to excel sheet in second step.

I have three scenarios like this. So I have one feature including all testing scenarios.

@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"

End of feature, there will be one excel file with three sheets with results for these three scenarios.

Feature implementation in Java:

Using Service Hooks in Cucumber to create file, work book and close the file.

Before Hook:

@Before
public void initializeTest(Scenario scenario) throws IOException, InvalidFormatException {
    createAFile();
    createWorkBook();

}

After Hook:

@After
public void endTest(Scenario scenario) {

    try {
        WriteInExcel writeInExcel = new WriteInExcel();
        TestResultManager testResultManager = new TestResultManager();
        writeInExcel.writeToFile(testResultManager.getFileOutputStream());
    } catch (IOException e) {
        e.printStackTrace();
    }
}

Within tests, use second step after main testing step to write test results in the excel sheet.

@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();
       }

According to the testing scenario it will navigate to three different methods. This can be implement with one method.

public static void writeDataForDMALevelDataBank() throws IOException, InvalidFormatException {
    //For output
    WriteInExcel writeInExcel = new WriteInExcel();
    writeInExcel.writeDataInSheet("DMALevel",getFinalResult());
}

According to the scenario, will pass the result set and sheet name to the method which will write data in sheet.

Limitations:

• 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.

Thanks for reading and enjoy the fun of mixing few technologies together and learning a lot at once. Cheers for all techies out there !!