Working with formulas in excel using Apache POI in java

Java Apache POI tutorial:

In this post, we will see how to set and evaluate formulas in excel using Apache POI.

Set formula for a cell:

For setting a formula for cell, you need to use following code:

We will use same code which we have used to write CountriesDetails.xlsx in previous post and add row for total population and evaluate it using formula.

Java program:

When you run above program, you will get following output:

Lets see content of CountriesDetails.xlsx now.

We have calculated total population using sum(c2:c5) in c7.

Formula evaluators:

When you update any cell which has impact on formula, you need to reevaluate the cell.
Apache poi API has provided FormulaEvalutor class to evaluate formula.

It has four method to evaluate formula:
1) public void evaluateAll()
It evaluates all cell which presents in workbook and it recalculates all formula and save the result and formula cells remains formula cells too. If cell do not have formula, it remains the same.

2) CellValue evaluate()
It evaluates formula cell and returns its cell value, it does not change original content of cell.

3) int evaluateFormulaCell(Cell cell)
It evaluates formula cell and returns its cell type. It saves calculated value. Cell remains as formula cell.

4) Cell evaluateInCell(Cell cell)
It evaluates formulas and return Cell object . It updates formula cell with latest value and cell will no longer formula cell.

Example for evaluate method:

When you run above file, you will get following output:

Add Comment