How to log testing results to xlsx (Excel) file with Selenium WebDriver?

Question

I have a certain test automation script written in Java with Selenium WebDriver. Currently, I log the testing results to the console and manually copy them to the required document. Is there a way I can log results directly to the xlsx (Excel) file? I use Java programming language with Selenium WebDriver.

Answer 1

There are several ways you can achieve what you want to do.

Basically you want to write the result of your tests in a file.

For this you can use spreadsheet (XLSX) or CSV or even simple text files.

To write your data in a XLSX file you can use Apache POI. Here is a reference link to help with this.

You can use OpenCSV library to write the data to a CSV file. Here is a reference link.

Or you can use Log4j to log all event's and steps executed in the code.

Answer 2

First step would be log everything to file on disk (text .csv file - comma separated values). Then you can import it into excel with few clicks.

Here is some instruction on how to work with .csv.

If you want to generate excel files you could use something more advanced like Apache POI.

But that's just the foundation. The proper way would be use something like log4j. Once you have static class Logger, you can use log.info() instead of System.out.println(). You can obviously combine this with ideas above (either .csv file or .xls files). And you only need change implementation of Logger class if you want to switch from .csv to .xls files

The question is: do you really need Excel file as a place where you log each automated run? After a few months you will have hundreds of files and noone will read them.

Answer 3

Yes. You can log results directly to the xlsx (Excel) file using Apache POI is used. You can check here for reference.

Following method is used for write data in xlsx file pragmatically after executing each testcase it just updates the status for that test case.

private void writeXLSXCellValue(int rowId, String status, int sheetID, String fileName) throws InvalidFormatException {
    try(InputStream stream = new FileInputStream(fileName)){

        XSSFWorkbook workbook =  new XSSFWorkbook(stream);
        sheet = workbook.getSheetAt(sheetID);
        column = sheet.getRow(rowId).getCell(Constant.statusColumnIndexSN);
        column.setCellType(Cell.CELL_TYPE_STRING);
        column.setCellValue(status);
        
        try(OutputStream outputStream = new FileOutputStream(fileName)){
            workbook.write(outputStream);
        }
        

    }
    catch (FileNotFoundException exception)
    {
        exception.printStackTrace();
    }
    catch (IOException e) {
        e.printStackTrace();
    }
}