Raphael Roth - 5 months ago 51

Scala Question

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`

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`

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