This works great! But on closer examination, you’ll notice that the value returned is the saved result of the last formula evaluation in the spreadsheet, meaning that it will always return the same (in this case) Mon Apr 28... value. What should you do if you want a dynamic value from the formula, e.g. when the program is run tomorrow?

The answer is to use POI’s HSSFFormulaEvaluator. The API evaluateFormulaCell() does the work of dynamically evaluating a cell formula and saving the result in the cell record. The code below shows how this works, and sleeps for one second in order to prove that the date returned in the example is in fact not pre-calculated:

So we’re done, right? Not yet, unfortunately. If you run the code above, you’ll encounter this exception:

Exception in thread "main" java.lang.RuntimeException: Cannot Parse, sorry : Found reference to named range "SEMIVOLATILE", but that named range wasn't defined! @ 22 [Formula String was: 'NOW(ATTR(semiVolatile))']
at org.apache.poi.hssf.model.FormulaParser.Abort(FormulaParser.java:129)
at org.apache.poi.hssf.model.FormulaParser.Ident(FormulaParser.java:335)
at org.apache.poi.hssf.model.FormulaParser.Factor(FormulaParser.java:522)
at org.apache.poi.hssf.model.FormulaParser.Term(FormulaParser.java:660)
at org.apache.poi.hssf.model.FormulaParser.Expression(FormulaParser.java:708)
at org.apache.poi.hssf.model.FormulaParser.Arguments(FormulaParser.java:486)
at org.apache.poi.hssf.model.FormulaParser.function(FormulaParser.java:358)
at org.apache.poi.hssf.model.FormulaParser.Ident(FormulaParser.java:280)
at org.apache.poi.hssf.model.FormulaParser.Factor(FormulaParser.java:522)
at org.apache.poi.hssf.model.FormulaParser.Term(FormulaParser.java:660)
at org.apache.poi.hssf.model.FormulaParser.Expression(FormulaParser.java:708)
at org.apache.poi.hssf.model.FormulaParser.Arguments(FormulaParser.java:486)
at org.apache.poi.hssf.model.FormulaParser.function(FormulaParser.java:358)
at org.apache.poi.hssf.model.FormulaParser.Ident(FormulaParser.java:280)
at org.apache.poi.hssf.model.FormulaParser.Factor(FormulaParser.java:522)
at org.apache.poi.hssf.model.FormulaParser.Term(FormulaParser.java:660)
at org.apache.poi.hssf.model.FormulaParser.Expression(FormulaParser.java:708)
at org.apache.poi.hssf.model.FormulaParser.parse(FormulaParser.java:812)
at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.internalEvaluate(HSSFFormulaEvaluator.java:361)
at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateFormulaCell(HSSFFormulaEvaluator.java:241)
at net.chipkillmar.poi_excel_functions.Example.main(Example.java:23)

Output Listing 1: Exception thrown executing Code Listing 2.

This is a cryptic way of saying that the NOW() function isn’t implemented in POI. It turns out that it’s fairly easy to understand why this exception happens and to fix it, though.

Apache has some good online documentation for developers here about formula evaluation. In a nutshell, formula strings are parsed into Reverse Polish Notation tokens. Each RPN token for an Excel function is mapped to a class in POI, for example the token for the NOW() function corresponds to the class org.apache.poi.hssf.record.formula.functions.Now.

The other key point is that all Excel formula function classes implement the interface org.apache.poi.hssf.record.formula.functions.Function. This interface has a single method, Eval evaluate(Eval[] evals, int srcCellRow, short srcCellCol), where all the heavy lifting happens.

The existing Now function class implementation in POI clearly reveals that an actual implementation will be needed for us to use it:

In theory we should be done at this point if it isn’t for what appears to be a small bug in org.apache.poi.hssf.record.formula.AbstractFunctionPtg that we’ll need to fix. The bug is that the metadata used to describe NOW() has the function accepting one parameter, when it should take none:

In summary, it’s possible to achieve dynamic formula evaluation of Excel functions with POI. However, as the POI documentation states up front, and we’ve seen here, some functions haven’t been implemented yet. Fortunately, POI has a good design for formula function implementations in place, so it’s not terribly hard to do it yourself. And with some luck, future releases of POI will incorporate function implementations from the developer community and perhaps obtain full coverage of the Excel function set.