1

I am using poi version 3.17. In my java code I want to read a cell value which has formula and I am using evaluateInCell function of FormulaEvaluator to resolve the cell value.

I have a template excel in my resource in which cells has formulas, if I create a workbook from this and set some value, and try to read it back, the FormulaEvaluator resolve the cell and put the actual value, by this formula of those cells get replaced and updated with actual value. WHY??

I have seen the implementation of evaluateInCell function which intentionally setting the cell type as setCellType(cell, cv); 

public XSSFCell evaluateInCell(Cell cell) {
        if (cell == null) {
            return null;
        } else {
            XSSFCell result = (XSSFCell)cell;
            if (cell.getCellType() == 2) {
                CellValue cv = this.evaluateFormulaCellValue(cell);
                setCellType(cell, cv);
                setCellValue(cell, cv);
            }
            return result;
        }
    }

Why library is doing so, and removing actual formula from the cell.

jrpsbadmn
  • 45
  • 1
  • 1
  • 6
  • 1
    What you observe is exactly what also is mentioned in [FormulaEvaluator.evaluateInCell](https://poi.apache.org/apidocs/dev/org/apache/poi/ss/usermodel/FormulaEvaluator.html#evaluateInCell-org.apache.poi.ss.usermodel.Cell-). "Be aware that your cell value will be changed to hold the result of the formula. If you simply want the formula value computed for you, use [evaluateFormulaCell(Cell)](https://poi.apache.org/apidocs/dev/org/apache/poi/ss/usermodel/FormulaEvaluator.html#evaluateFormulaCell-org.apache.poi.ss.usermodel.Cell-)". So why not doing exactly this? – Axel Richter Feb 28 '20 at 13:31

1 Answers1

0

There are different functions: 

 CellType evaluateFormulaCell(Cell cell) - your cell will hold both the formula, and the result.       
 Cell evaluateInCell(Cell cell) - you cell will contain result only, formula is erased.      
 CellValue evaluate(Cell cell) - the formula is evaluated and returned. No changes in the cell.    
 void evaluateAll() = mass evaluateFormulaCell for the whole book.

Obviously, you have taken evaluateInCell for evaluateFormulaCell. evaluateInCell is very useful, and not only for counting - Use it for clean erasing of formulae.

Also, don't forget clearAllCachedResultValues() or notifyUpdateCell()/notifySetFormula(). Without those, any evaluation after any changes on the sheet can fail.

Gangnus
  • 24,044
  • 16
  • 90
  • 149