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-unsubscr...@poi.apache.org
For additional commands, e-mail: user-h...@poi.apache.org
