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:

Leave a Reply

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

Recent Comments
  • Custom Keywords for Custom Functions (1)
    • Sarah: Thank you for this. It helped me understand these better.
  • How To Disable the Quicken Registration Prompt (26)
    • Denise Defoor: I have a Quicken SE Version 6 .. My computer crashed a few weeks ago and I finally got it up and running. Now Quicken is telling me I must registered it. What? I have had this program for 20 years. How can I disable the...
    • Greg: For me, holding the *LEFT* CTL + Shift then clicking Online, One Step Update worked. I originally tried holding the right CTL + Shift, and it didn’t work. I’m using Quicken 2006, so I don’t know if it will work...
    • Joe SR>: My monthly income is deposited into my Credit Union account. I use debit whenever possible. I write checks manually and mail them. I use Quicken 2012 off-line only. I have entered all my money and investment accounts. I...
    • Prtet: Never say never….every time I swear I will never use Quicken again, I discover that there are still no viable alternatives. Amazing that there is no decent personal finance software.
    • Joe D.: Holding CTRL + Shift keys and selecting On Line | One Step Update from the main menu worked for my Quicken 2004. I’m grateful that you wrote a synopsis (“Simply put, …”) just beneath the link to the blog...
    • Susan Long: I bought my quicken disc in the beginning and it came with a registration number. I rang the helpline and they gave me the code to put in and talked me through it. It you downloaded your version then you don’t own it...
  • Parsing Strings in Katalon – Split, Substring and Readlines (4)
    • Anjana: Hey thanks for the response It is quite strange but initializing variable for delimiter and it worked String splitFormat = ‘-‘ WebUI.println(var_CampaignDura tion.toString().split(splitFor mat)[0])...
    • Don Pedro: I’m not really sure what the difference is, but I don’t think the “-“ in the split is the same as the one in the date. String var_CampaignDuration = “05/13/2019-06/07/2019&# 8221; String[] parsedDateRange =...
    • Anjana: Hi, I tried the same thing to split the string, but does not work for me, Could you plz point out whats wrong Variable var_CampaignDuration has value “05/13/2019-06/07/2019&# 8221; String[] parsedDateRange =...
    • Ellen: Thanks for sharing!! I like your contributions to Katalon topics.
  • How to Block games by Title and Tag on Steam (2)
    • Marcus861: I made an acc just to say: Same bro
    • JACK: Thanks, same just wanted to block anime games in my discover
  • Create a new and random UUID for an API call (1)
    • Anonymous: Hi ! I found this blog few weeks ago and it’s really helping me out with my tests! Thanks a lot for the Katalon tips and tricks ! Keep up the good work 🙂
  • Working with Dates and Date Formatting in Katalon Studio (6)
    • Ajoo: Thank you for the details. How do i remove leading “0” from dates. i.e. while formatting i receive 04/21/2019, but i need 4/21/2019. (same applies for date)