0

I am downloading an excel from an application with the help of selenium. When I am trying to get one value from that excel cell, it is returning as: 0.0

while that field value is : 3,662,772 (This is a result of some formula)

Can someone point me, what I am doing wrong here?

Code:

  Decimalformat decimalformat = new DecimalFormat("0.#");
  FileInputStream fsIP= new FileInputStream(new File("src/test/resources/test.xlsx"));
  XSSFWorkbook wb = new XSSFWorkbook(fsIP);
  XSSFSheet worksheet = wb.getSheetAt(0);
  Double myvalue = Double.valueOf(decimalFormat.format(sheet.getRow(1).getCell(1).getNumericCellValue()))

Output (When excel is not saved manually) : 0.0

When I manually save the excel file and try to fetch the value again from above code, it returns the correct value.

Output (When excel is saved manually) : 3662772

I tried to evaulate the formula:

        CellReference cellReference = new CellReference("P212");
        Row row = sheet.getRow(cellReference.getRow());
        Cell cell = row.getCell(cellReference.getCol());
        System.out.println("1:" + cell);
        System.out.println("2: " + 
         cell.getCachedFormulaResultTypeEnum());
        System.out.println("3:" + cell.getNumericCellValue());
        evaluator.evaluateFormulaCellEnum(cell);

Output:

java.lang.RuntimeException: Invalid ValueEval type passed for conversion: (class org.apache.poi.ss.formula.eval.RefListEval)

Apache POI Version : 3.17

Update:

After reading the comments, it narrowed down that the issue is coming because :

  1. It is a formula value when fetched without evaluating, giving the cached value which i think is 0.0
  2. To get the value, I will need to evaluate the formula, which when I used, is failing.
QualityMatters
  • 895
  • 11
  • 31
  • what does `sheet.getRow(1).getCell(1).getNumericCellValue()` return? – f1sh Mar 03 '22 at 10:12
  • 0.0, while its actual value is : 3,662,772 – QualityMatters Mar 03 '22 at 10:12
  • Is it really getNumericCellValue() that returns 0.0 or is it the conversion to Double that returns zero? – Joakim Danielson Mar 03 '22 at 10:28
  • After manually saving (ctrl+ s) in excel, same code is returning the correct value. – QualityMatters Mar 03 '22 at 10:30
  • can you please share the decimalFormat declaration code? – Vasanth Subramanian Mar 03 '22 at 10:49
  • Added :Decimalformat decimalformat = new DecimalFormat("0.#"); – QualityMatters Mar 03 '22 at 10:51
  • @magicmn if thats the case, then how next time it is returning correct value? I dont think, its related to cell. – QualityMatters Mar 03 '22 at 11:35
  • 2
    Looks as if the formula not got evaluated before read. Do that as described in https://poi.apache.org/components/spreadsheet/eval.html. Or do using `DataFormatter` together with `FormulaEvaluator` while reading as shown here: https://stackoverflow.com/questions/3819633/how-to-get-the-formatted-value-of-a-number-for-a-cell-in-apache-poi/66241266#66241266. – Axel Richter Mar 03 '22 at 15:00
  • The cell type is a formula and the value is a sum of almost 15 fields. When I am trying to evaluate it with the help of formulaEvaluator, it is failing. – QualityMatters Mar 04 '22 at 09:46
  • @QualityMatters: Then you are lost. The cell currently seems not to have any other value than 0. So the only possibility is evaluating the formula. Either by opening in Excel - then Excel GUI evaluates the formula and that was what you had seen already - or by using `apache poi`'s `FormulaEvaluator`. You could ask a new question about why the evaluation of that special formula fails using `apache poi`. But then please do mention that special formula and what `apache poi` version used and **how** it fails exactly (stacktrace). – Axel Richter Mar 04 '22 at 10:16
  • I checked other questions and it looks like we dont have any answer if formula evaluator fails. And I am using apache poi as a dependency in my project which is coming from core project which is being used by more then 10 teams. Upgrading it would might impact at several places. – QualityMatters Mar 04 '22 at 10:21
  • "Upgrading it would might impact at several places.": And that's why never upgrading? Well that's exactly the reason why projects die. Nobody is willing to make the effort to hold them up to date in all of their dependencies. Then sooner or later that really is impossible. And then the project counts so much shortcomings and vulnerabilities that nobody can use it anymore. – Axel Richter Mar 04 '22 at 10:58
  • It didn't work with latest apache version 5.0.0. I will have to think something else, like writing vb macro and then calling it from java. – QualityMatters Mar 04 '22 at 13:08
  • 1
    Can you create a small simple Excel file, with one of the formulas in it that fails to evaluate? – Gagravarr Mar 04 '22 at 15:53
  • Its quite complex excel for a financial firm. I will try to replicate and upload. – QualityMatters Mar 04 '22 at 16:06

0 Answers0