Raphael Roth Raphael Roth - 5 months ago 51
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 (

) 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

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

If I use the same code for
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?


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);