Adding entries to an open Excel spreadsheet during runtime

When running the API test for several thousand entries, I noted that matching up an invalid request with the correct source data could be a needle in the haystack affair. Instead, it would be better to record any errors right alongside the source data in the XLSX spreadsheet file. That can be done by writing to a new cell during runtime.

The process is only a couple of additional lines of code.

To start, a new file needs to be opened using:

FileOutputStream outFile = new FileOutputStream(new File("//Postman Testing//Data Files//pricing.xlsx")

Data can then be written to the sheet using, createCell(5).setCellValue():
The .createCell(5) refers to column 5 in the spreadsheet. I'm using columns 0-4 for source data.

sheet.getRow(loop).createCell(5).setCellValue("ERROR: Failure Message on pricing request " + loop + " " + failure_message)

At the end, all data needs to be written to the workbook:

workbook.write(outFile)

And like other files, it needs to be closed when finished:
outFile.close()

During runtime, if an error is encountered, it is written inline with the source data. When the pricing.xlsx file is opened, the error message and data used to replicate the error is all in one place.

Other articles of interest:

One Response to Adding entries to an open Excel spreadsheet during runtime

  • IanG says:

    Hi:
    Seems like nobody else has described (in detail) how Katalon can be configured to test an API by reading the first entry from a multi-row multi column XLSX spreadsheet, executing a test, writing the result to another column (or columns) in the s/sheet, then moving on to the row below … then repeat and repeat until the last row in the table is processed.

    This post and the one from Apr 8 (Data Driven Testing with Katalon …) certainly seem to have most if not all the information required, but to a non-programmer, they don’t seem to be fully joined up…

    So could you do another post maybe which takes the use case as described above, and provides a end to end worked example that people like me can build from?

    Thx in advance!

Leave a Reply

Your email address will not be published. Required fields are marked *

Recent Comments