Unable to get values from excel

Feedback & Reviews Bugs Report
1

Hi I have a teste case where I write values on excel. However I’m trying to get the result of the formulas in the same excel and I’m unable to. When I open the excel outside the test both values (inserted and calculated) show. So why can´t the test retrieve them?

This is the test:
import static com.kms.katalon.core.testdata.TestDataFactory.findTestData
import static com.kms.katalon.core.testobject.ObjectRepository.findTestObject
import java.awt.Robot as Robot
import java.awt.event.KeyEvent as KeyEvent
import org.apache.poi.ss.usermodel.Cell as Cell
import org.apache.poi.ss.usermodel.CellValue as CellValue
import org.apache.poi.ss.usermodel.FormulaEvaluator as FormulaEvaluator
import org.apache.poi.xssf.usermodel.XSSFSheet as XSSFSheet
import org.apache.poi.xssf.usermodel.XSSFWorkbook as XSSFWorkbook
import com.kms.katalon.core.webservice.keyword.WSBuiltInKeywords as WS
import groovy.json.JsonSlurper as JsonSlurper
import internal.GlobalVariable as GlobalVariable

‘Allow Key input.’
Robot robot = new Robot()

‘Open excel.’
FileInputStream file1 = new FileInputStream(new File(‘C:\…test.xlsm’))

XSSFWorkbook workbook1 = new XSSFWorkbook(file1)

XSSFSheet sheet0 = workbook1.getSheetAt(0)

‘Write data to excel’
Cell cell1 = sheet0.getRow(10).getCell(2)

cell1.setCellValue(datNas1aPesSegura)

robot.keyPress(KeyEvent.VK_ENTER)

robot.keyRelease(KeyEvent.VK_ENTER)

Cell cell2 = sheet0.getRow(11).getCell(2)

cell2.setCellValue(datNas2aPesSegura)

robot.keyPress(KeyEvent.VK_ENTER)

robot.keyRelease(KeyEvent.VK_ENTER)

Cell cell3 = sheet0.getRow(10).getCell(4)

cell3.setCellValue(sex1aPesSegura)

robot.keyPress(KeyEvent.VK_ENTER)

robot.keyRelease(KeyEvent.VK_ENTER)

Cell cell4 = sheet0.getRow(11).getCell(4)

cell4.setCellValue(sex2aPesSegura)

robot.keyPress(KeyEvent.VK_ENTER)

robot.keyRelease(KeyEvent.VK_ENTER)

Cell cell5 = sheet0.getRow(10).getCell(6)

cell5.setCellValue(cap1aPesSegura)

robot.keyPress(KeyEvent.VK_ENTER)

robot.keyRelease(KeyEvent.VK_ENTER)

Cell cell6 = sheet0.getRow(11).getCell(6)

cell6.setCellValue(cap2aPesSegura)

robot.keyPress(KeyEvent.VK_ENTER)

robot.keyRelease(KeyEvent.VK_ENTER)

Cell cell7 = sheet0.getRow(3).getCell(1)

cell7.setCellValue(GlobalVariable.user_UNW)

robot.keyPress(KeyEvent.VK_ENTER)

robot.keyRelease(KeyEvent.VK_ENTER)

robot.keyPress(KeyEvent.VK_F9)

robot.keyRelease(KeyEvent.VK_F9)

sheet0.forceFormulaRecalculation

workbook1.setForceFormulaRecalculation(true)

FileOutputStream outFile1 = new FileOutputStream(new File(‘C:\…test.xlsm’))

workbook1.write(outFile1)

outFile1.close()

FileInputStream file2 = new FileInputStream(new File(‘C:\…test.xlsm’))

XSSFWorkbook workbook2 = new XSSFWorkbook(file2)

XSSFSheet sheet1 = workbook2.getSheetAt(0)

robot.keyPress(KeyEvent.VK_F9)

robot.keyRelease(KeyEvent.VK_F9)

‘Read data from excel’

premium_PremioTotalFraccionadoTrimestral = sheet1.getRow(68).getCell(4).getRawValue()

System.out.println(premium_PremioTotalFraccionadoTrimestral)

premium_PremioTotalFraccionadoSemestral = sheet1.getRow(69).getCell(4).getRawValue()

System.out.println(premium_PremioTotalFraccionadoSemestral)

file2.close()

FileOutputStream outFile2 = new FileOutputStream(new File(‘C:\…test.xlsm’))

workbook2.write(outFile2)

outFile2.close()

When I run this is the outcome:

image
image1061×90 33.8 KB

Can you help me?
Thanks in advance

2

No, you are unable to. MS Excel evaluates the formulas. Katalon is not Excel. Katalon does not understand the formulas.

If you want to utilise the result calculated by formulas defined in the excel files, do the following:

  1. You open the excel book, open the worksheet, then the “formulas” are evaluated by Excel app, and the results are present in the worksheet.
  2. Operate excel to save the worksheet into a CSV file. The values as formula-evaluation-result will be saved into CSV as String or Number type (not as Formula).
  3. Your Katalon Studio project reads the CSV file as input. Then the values as formular-evaluation-result in CSV will be available to Katalon scripts.
  4. If you want to automate Excel operation (start Excel app, open a workbook, evaluate formulas, save worksheet into CSV), you should try PowerShell. See Open Local File - #5 by kazurayam