Raphael Roth Raphael Roth - 15 days ago 9
Scala Question

Reading result of Formula Cells in Apache POI 3.15

I try to read the values from a cell as a String (as one would see it in Excel). I reads from a xlsx (

XSSFWorkbook
) using Apache POI 3.15.

My goal is e.g. to omit decimal point and trailing zeros if the cell contains an integer. This works for
CellType.NUMERIC
:

val dataFormatter = new DataFormatter(true) // set emulateCsv to true
val stringValue = dataFormatter.formatCellValue(cell)


If I use the same code for
CellType.FORMULA
cell (e.g. a cell which references another "integer" cell), it just gives me the formula as a string instead of its computed value.

How can I get value of the formula-cell as displayed in Excel displays?

Answer

You need to "evaluate" cells in order to get the result of formulas. This is not done automatically by POI as it can be a heavy operation and often will not be necessary.

See http://poi.apache.org/spreadsheet/eval.html for details, basically you create a FormulaEvaluator and retrieve a CellValue for the Cell in question

FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
...
CellValue cellValue = evaluator.evaluate(cell);