2

I want to use POI setCellFormula to set a formula to an cell, the formula is from a excel add-in but not currently installed. After that, I'll move the excel to a PC with the add-in installed to evaluate the formulas.

However, when I try to set the formula using setCellFormula, I get an exception:

Exception in thread "main" org.apache.poi.ss.formula.FormulaParseException: Name 'MY_FUNC' is completely unknown in the current workbook at org.apache.poi.ss.formula.FormulaParser.function(FormulaParser.java:918) at org.apache.poi.ss.formula.FormulaParser.parseNonRange(FormulaParser.java:556) at org.apache.poi.ss.formula.FormulaParser.parseRangeable(FormulaParser.java:427) at org.apache.poi.ss.formula.FormulaParser.parseRangeExpression(FormulaParser.java:266) at org.apache.poi.ss.formula.FormulaParser.parseSimpleFactor(FormulaParser.java:1117) at org.apache.poi.ss.formula.FormulaParser.percentFactor(FormulaParser.java:1077) at org.apache.poi.ss.formula.FormulaParser.powerFactor(FormulaParser.java:1064) at org.apache.poi.ss.formula.FormulaParser.Term(FormulaParser.java:1424) at org.apache.poi.ss.formula.FormulaParser.additiveExpression(FormulaParser.java:1524) at org.apache.poi.ss.formula.FormulaParser.concatExpression(FormulaParser.java:1508) at org.apache.poi.ss.formula.FormulaParser.comparisonExpression(FormulaParser.java:1465) at org.apache.poi.ss.formula.FormulaParser.unionExpression(FormulaParser.java:1445) at org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:1566) at org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:174) at org.apache.poi.hssf.model.HSSFFormulaParser.parse(HSSFFormulaParser.java:72) at org.apache.poi.hssf.usermodel.HSSFCell.setCellFormula(HSSFCell.java:605)

Is there a way that I set the formula without evaluate it immediately?

codeplay
  • 610
  • 1
  • 9
  • 19

1 Answers1

3

If Excel is open then inserting a formula into a cell will cause Excel to evaluate the formula.
The only way I know of is to insert the formulastring prefixed with ' to make it a string rather than a formula.
Then the addin-itself would have to convert the strings to formulas by doing a find and replace of '= with =

Charles Williams
  • 23,121
  • 5
  • 38
  • 38
  • Thank you, I also thought abt this approach or using VB script to evaluate but I felt this is not so user friendly. – codeplay Oct 24 '11 at 10:18