Determine if a Spreadsheet Cell contains Numeric or Text data

While working with some text data from a spreadsheet, I needed to read different kinds of SKU data. In some cases it would be a number such as 1313, while in others it might be a mix of text and numbers such as OU812.

Reding the value is normally done with .getNumericCellValue() or .getNumericCellValue(), but what happens when you don't know what the next value would be?

To overcome that, it's possible to the use .getCellType() and adjust the read based on type. Using some previous code, reading values from a spreadsheet and populating a Global List would look like the following:

for (loop = 1; loop <=rowCount; loop++) {
    //Assign spreadsheet columns to variables
    String cellType=sheet.getRow(loop).getCell(0).getCellType()
    //Determine if the cell contains Text or Numeric data
    if (cellType=="1"){
        String cellTextValue=sheet.getRow(loop).getCell(0).getStringCellValue()
        GlobalVariable.inventorySKU[loop]=cellTextValue
    }else{
        long cellIntValue=sheet.getRow(loop).getCell(0).getNumericCellValue()
        GlobalVariable.inventorySKU[loop]=cellIntValue
    }
}

In this case, 1 is String and 0 is Integer or Numeric. Based on the length of the SKU, I used long to store the value.

Additionally, in order to use the Integer value within a form, it needs to be converted to a String using:
String.valueOf(GlobalVariable.inventorySKU[loop])) such as:

WebUI.setText(findTestObject('Object/input-SKU'), String.valueOf(GlobalVariable.inventorySKU[loop]))

Apache POI – Cells

Other articles of interest:

Leave a Reply

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

Recent Comments
  • Output status messages and test information by writing to the Log File Viewer in Katalon Studio (4)
    • Don Pedro: That is correct. You will need to add KeywordUtil.markFailed to change the Failures count. Other commands in the library are: KeywordUtil.markPassed KeywordUtil.markFailed KeywordUtil.markError KeywordUtil.markWarning
    • Nadim: This is really helpful … the only problem when log.logFailed executed it doesn’t update the results Failures count … still show Failures: 0
  • Working with Dates and Date Formatting in Katalon Studio (7)
    • Sharma: I want to choose date from any of the 12 months but I am not abel to do the same.
  • Securely storing passwords and login details with Set Encrypted Text in Katalon Studio (2)
    • sowmya: hi can u please help me how to read data from text file in katalon studio?
  • 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.
  • 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 =...
  • 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 🙂