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
  • Enter dates into a date picker for Chrome and Firefox (1)
    • Prasanna: Hello, How to enter a previous month FIRST day and LAST day from the system date. Thanks Prasanna
  • How To Disable the Quicken Registration Prompt (31)
    • Juani: Hi, I have a registered Quicken 2016 and now my files are being held hostage by Intuit, cannot log in with my ID unless I upgrade therefore buy a subscription, I need to access my files, please HELP. Thanks
    • David: A BIG Thank you! I regularly reinstall Windows OS’s using different hardware configs. In short, Quicken 2011 (it’s March of 2020 as I write this) has been a program I’ve been very happy with and continue to use....
    • Karen L: Today I rang Reckon Australia again, got a different guy and he talked me through the process of getting me a new product key to enter since I already had the licence and it had been extended. Reckon have only extended it by 1...
    • Karen L: I have Australian Quicken 2008 on Windows 8.1. It keeps asking me to renew my licence which Reckon did for me however the activation won’t work. I tried this to disable the registration prompt however it did not stop the...
  • Building Relationships with Developers (1)
    • Carlos Herrera: Ah yes. I have a team of software developers and it is genuinely so hard to keep them in the loop during projects. Though the more we work the better the communication becomes and we go from a trail and error process to a...
  • Integrating DevonAgent Pro with Alfred (1)
    • J. Garr: Beautiful, sweet, and simple. I love it; thanks for posting this solution.
  • Round and Round with the For..Next in Katalon Studio (1)
    • Sweet Ophaline Labador: Hello can you help me how to loop this scenario. Checking the elements is just the same. I want to check that the following function is available in www.siacargo.com: Track Shipment – clicking on this can...
  • Parsing Strings in Katalon – Split, Substring and Readlines (6)
    • Don Pedro: String tempText=”Date Month January. Revenue $1,355,721.00″ println(tempText.split(“ \$”)[1]) You need to escape the $ with \$ The [1] on the split captures characters on the right of the split [0]...
    • Prasad: Hi, I need to get the substring from the below string ‘Date Month January. Revenue $1,355,721.00’ I want to extract text after $ value. Please help Thank you
  • Working with Dates and Date Formatting in Katalon Studio (15)
    • Sangeethaa: How to get previous day’s date(Yesterday’s date)? Actually I was using today.previous() before,it was working fine till today morning.Now its not retrieving right date. Can anyone please guide me on this.
    • Don Pedro: That could be a little tricky. First, is the text/format always going to be the same? You will need to do some parsing. Separate the first but of text at 2019. After that, split the text again to get rid of Central Standard...
    • Don Pedro: It should be of the same Date type as today. You could then do some calendar trickery with Calendar /*Calendar c = Calendar.getInstance(); //c.set(Calendar.MONTH, 1) //Set the month, 0=January, 11=December println...
  • Output status messages and test information by writing to the Log File Viewer in Katalon Studio (6)
    • Saranya: Good One. Could you plz share link to access all your katalon related blogs. ThankQ
    • Rodrigo Calabretta: I’m using the KeywordUtil.markFailed or KeywordUtil.markError and my test stops is being shown as Error and If I use in the @afterTestCase the testCaseContext.getTestCaseSta tus() to show the status test case...