POI workbook evaluation with unregistered UDF's

[Javen O'Neal <ja...@gmail.com>]

I have an EXCEL2007 (OOXML) macro-enabled workbook that contains VBA
user-defined functions that may be in some cell formulas. I want to
copy the cell formula from one cell and paste the formula into another
cell (bug 58348). I will shift the cell references within the formula
by the distance between the cells, so the formula result may differ.

The problem arises when the formula contains a user-defined function
that I have not registered with POI. Here's the stack trace (line
numbers may not match current repository due to local modifications):
Name 'ExtractElement' is completely unknown in the current workbook
org.apache.poi.ss.formula.FormulaParseException: Name 'ExtractElement'
is completely unknown in the current workbook
    at org.apache.poi.ss.formula.FormulaParser.function(FormulaParser.java:947)
    at org.apache.poi.ss.formula.FormulaParser.parseNonRange(FormulaParser.java:556)
    at org.apache.poi.ss.formula.FormulaParser.parseRangeable(FormulaParser.java:436)
    at org.apache.poi.ss.formula.FormulaParser.parseRangeExpression(FormulaParser.java:257)
    at org.apache.poi.ss.formula.FormulaParser.parseSimpleFactor(FormulaParser.java:1143)
    at org.apache.poi.ss.formula.FormulaParser.percentFactor(FormulaParser.java:1103)
    at org.apache.poi.ss.formula.FormulaParser.powerFactor(FormulaParser.java:1090)
    at org.apache.poi.ss.formula.FormulaParser.Term(FormulaParser.java:1450)
    at org.apache.poi.ss.formula.FormulaParser.additiveExpression(FormulaParser.java:1570)
    at org.apache.poi.ss.formula.FormulaParser.concatExpression(FormulaParser.java:1554)
    at org.apache.poi.ss.formula.FormulaParser.comparisonExpression(FormulaParser.java:1511)
    at org.apache.poi.ss.formula.FormulaParser.intersectionExpression(FormulaParser.java:1492)
    at org.apache.poi.ss.formula.FormulaParser.unionExpression(FormulaParser.java:1472)
    at org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:1612)
    at org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:153)
    at org.apache.poi.xssf.usermodel.XSSFCell.setFormula(XSSFCell.java:535)
    at org.apache.poi.xssf.usermodel.XSSFCell.setCellFormula(XSSFCell.java:514)

Because these workbooks are edited by humans and later reread by POI,
it's possible that the user defines their own VBA macro in the
workbook that I do not know the name or signature of. As such, I'm not
able to rewrite the VBA in Java in a FreeRefFunction and register it
with the workbook's DefaultUDFFinder, per
https://poi.apache.org/spreadsheet/user-defined-functions.html

I am okay leaving the cell unevaluated (and any other cell that
depends on the formula result), since this can be re-evaluated when
the workbook is opened in Excel.

Right now XSSFCell.setCellFormula always runs the formula through the
formula parser, which throws the FormulaParseException above.

QUESTION: Do any solutions to this problem currently exist in POI? If
not, what's the preferred fix for this? I'm thinking of something
along the lines of setting workbook formula evaluation mode from
automatic to manual mode. If manual mode is set, setCellFormula will
not call parseFormula.

Javen O'Neal

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org

Re: POI workbook evaluation with unregistered UDF's

[Nick Burch <ap...@gagravarr.org>]

On Sun, 20 Sep 2015, Javen O'Neal wrote:
> Right now XSSFCell.setCellFormula always runs the formula through the
> formula parser, which throws the FormulaParseException above.

That's our only way of sanity checking the formula before we write it, to 
try to catch people making mistakes, and to keep things consistent between 
HSSF and XSSF. (HSSF has to sanity check as part of turning it into the 
Ptgs to write into the file)

> QUESTION: Do any solutions to this problem currently exist in POI? If 
> not, what's the preferred fix for this? I'm thinking of something along 
> the lines of setting workbook formula evaluation mode from automatic to 
> manual mode. If manual mode is set, setCellFormula will not call 
> parseFormula.

It'll have to be a XSSF-specific fix, as HSSF needs to do the parsing in 
order to get the ptgs for the file.


We've had a few requests over the years (in bugs, on the list, and on 
stackoverflow) for a way to copy a formula to a new cell "excel-like" with 
the non-$ references updated for the new location.

I wonder if the fix would be to add this missing functionality in, then 
have an option on that to skip / unchange unknown function names? (It 
might actually be easied to do the copy in the Ptg space, rather than 
Formula text space, as with the ptgs you could check for non-absolute 
references and update just them. That'd mean you'd need a ways to flag to 
skip these functions / leave them unchanged or something)

Nick

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org