poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Greg Woolsey <greg.wool...@gmail.com>
Subject Re: Cell Calculations by reference to other cells in hidden sheets within the same workbook
Date Wed, 10 Apr 2019 21:00:46 GMT
>From the Javadoc for evaluate(Cell):

"This method should be preferred over evaluateInCell() when the call should
not modify the contents of the original cell."

You need to use the result of this call, a CellValue instance, like the POI
example page shows, as this call doesn't save the formula evaluation result
in the cell.

Or, you can use

evaluateFormulaCell(Cell)

instead. As the Javadoc says, this method updates the cached value but
leaves the cell as a formula cell. Use the returned CellType to know which
cell getter to use when reading the calculated value.

Or you can call evaluateAll() which performs evaluateFormulaCell() for all
formula cells in the workbook at once, and then read the updated results.

It looks like you are just confusing the various methods and their
behaviors.

On Wed, Apr 10, 2019 at 1:52 PM Kumar Thotapally <kthotapally@emmes.com>
wrote:

> Greg,
>
> Here is my code snippet and its output:
>
>
> sheet.getRow(2).getCell(5).setCellValue(examDate); // 04/10/2019
>
> sheet.getRow(2).getCell(7).setCellValue(birthDate); // 01/01/1980
>         // output computed AGE value from cell B5 - Expected result: 39
>                           FormulaEvaluator evaluator =
> wb.getCreationHelper().createFormulaEvaluator();
>                           Cell c = sheet.getRow(4).getCell(1);
>                           CellValue cellValue = evaluator.evaluate(c);
>                           evaluator.evaluate(c);
>                           System.out.println("Cell Value (String) : "+
> cellValue.toString());
>                           System.out.println("Cell Value (Number) : "+
> cellValue.getNumberValue());
>                           System.out.println("Formula : " +
> c.getCellFormula());
>                           System.out.println("CachedFormulaResultType : " +
> c.getCachedFormulaResultType());
>                           System.out.println("Numeric value : " +
> c.getNumericCellValue());
>
> And the output is :
>
> Cell Value (String) : org.apache.poi.ss.usermodel.CellValue [#VALUE!]
> Cell Value (Number) : 0.0
> Formula : 'Unformatted Values'!AD13
> CachedFormulaResultType : NUMERIC
> Numeric value : 0.0
>
> 'Unformatted Values' is the name of a hidden sheet in the workbook
>
>
>
> --
> Sent from: http://apache-poi.1045710.n5.nabble.com/POI-User-f2280730.html

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message