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