The weird syntax like {1,0} indicate it is an array formula . Unfortunately
POI does not support it and I don't know an easy workaround. The formula
API is pluggable, but the formula parser API isn't, this is where the PTG
exception is coming from: PTG stands for parsed token and POI cannot parse
the funny syntax in curly braces and translate it into evaluation tree.
Teaching the formula parser to respect curly braces is not a big deal. A
much harder task is to support evaluation of array formulas, i.e. to
correctly interpert them.
Have a look at Bugzilla 46989: support array formulas. The patch provides
initial support for array formulas but there is a long way to apply it in
trunk. I did worked on it but gave up because of lack of time.
Yegor
24 Ноя 2015 г. 18:51 пользователь "Tom Chiverton" <tom.chiver...@gmail.com>
написал:
> OK, so I made a dummy implementation of TREND, I think, and cells that use
> either the 2 or 3 argument version now evaluate to 0 rather than throwing
> the unsupported exception.
>
> But cells that use the weird (to my eyes) IF({1,0}, construct still throw
> "Unexpected ptg class".
> So I am wondering if there is something up elsewhere in POI that is failing
> to parse the expression.
>
> Here is my dummy TREND() and the test spread sheet:
> https://www.dropbox.com/sh/jb84qvpl4cknod5/AAA9uZs6NnFpbAu3KTck6xOAa?dl=0
>
> Could someone who knows the guts of the formula evaluator take a look ?
>
> Full stack from error
>
> Unexpected ptg class (org.apache.poi.ss.formula.ptg.ArrayPtg) at
>
> org.apache.poi.ss.formula.WorkbookEvaluator.getEvalForPtg(WorkbookEvaluator.java:663):663
> at
>
> org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:505):505
> at
>
> org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:263):263
> at
>
> org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:205):205
> at
>
> org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateFormulaCellValue(HSSFFormulaEvaluator.java:374):374
> at
>
> org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluate(HSSFFormulaEvaluator.java:202):202
>
>
> On 24 November 2015 at 14:24, Tom Chiverton <tom.chiver...@gmail.com>
> wrote:
>
> >
> > On 24 November 2015 at 14:09, Nick Burch <apa...@gagravarr.org> wrote:
> >
> >>
> >>
> http://people.apache.org/~yegor/apachecon_us2010/Evaluation_Of_Excel_Formulas_In_POI.pptx
> >> for information on how evaluation works, functions, ptgs etc, if it's
> new
> >> to you
> >
> >
> > It is new, but my Java skills are hopefully OK :-)
> >
> > Is there a full working example of loading a new function at runtime, as
> > https://poi.apache.org/spreadsheet/eval-devguide.html
> > indicates I should be able to do ?
> > The syntax there is a little different from the one on page 18 of the
> > power point ?
> >
> >
> > --
> > Tom
> >
>
>
>
> --
> Tom
>
