compilation problem - unrecognized token 1

[Steve]

I've run the tutorial, and a dummy of my own in similar fashion (using
named cells, and tweaking the example as little as possible).

In a leap of faith, I tweaked a big (>1k) formula workbook to be
compliant (named cells on inputs and outputs), but came out with this
compilation error (below).

I know there are blank cells in a referenced range, and I'm willing to
refactor the workbook to remove them if I can be encouraged to believe
that this could/would resolve this error. I'm unwilling to do so
without further info, however, because of how many references to the
range that would have to be updated.

I appreciate any help I can get here. FWIW, if I can resolve whatever
issues I find, it's likely that I'll be using this tool/approach in
production this quarter or next.

Here's the console log:

[java] Exception in thread "main"
org.formulacompiler.spreadsheet.SpreadsheetException$LoadError:
Unrecognized token 1
[java] at
org.formulacompiler.spreadsheet.internal.excel.xls.loader.ExcelXLSLoader.loadCell(ExcelXLSLoader.java:
181)
[java] at
org.formulacompiler.spreadsheet.internal.excel.xls.loader.ExcelXLSLoader.loadRows(ExcelXLSLoader.java:
161)
[java] at
org.formulacompiler.spreadsheet.internal.excel.xls.loader.ExcelXLSLoader.loadFrom(ExcelXLSLoader.java:
116)
[java] at
org.formulacompiler.spreadsheet.internal.loader.SpreadsheetLoaderDispatcher.loadFrom(SpreadsheetLoaderDispatcher.java:
54)
[java] at
org.formulacompiler.spreadsheet.SpreadsheetCompiler.loadSpreadsheet(SpreadsheetCompiler.java:
156)
[java] at
org.formulacompiler.spreadsheet.internal.util.EngineBuilderImpl.loadSpreadsheet(EngineBuilderImpl.java:
132)
[java] at Sc1.compile(Unknown Source)
[java] at Sc1.main(Unknown Source)
[java] Caused by: jxl.biff.formula.FormulaException: Unrecognized
token 1
[java] at
jxl.biff.formula.TokenFormulaParser.parseSubExpression(TokenFormulaParser.java:
149)
[java] at
jxl.biff.formula.TokenFormulaParser.parse(TokenFormulaParser.java:111)
[java] at
jxl.biff.formula.FormulaParser.parse(FormulaParser.java:110)
[java] at
jxl.read.biff.NumberFormulaRecord.getFormula(NumberFormulaRecord.java:
179)
[java] at
org.formulacompiler.spreadsheet.internal.excel.xls.loader.ExcelXLSLoader.loadCell(ExcelXLSLoader.java:
177)
[java] ... 7 more
[java]
[java] Java Result: 1

[Peter Arrenbrecht]

> [java] Caused by: jxl.biff.formula.FormulaException: Unrecognized
> token 1
> [java] at
> jxl.biff.formula.TokenFormulaParser.parseSubExpression(TokenFormulaParser.java:
> 149)

Hmm. This is not a problem with the compiler as such, but with the
underlying JExcelAPI library we use to parse .xls files. So, to narrow
this down, can you either post the sheet, or add a breakpoint in

> org.formulacompiler.spreadsheet.internal.excel.xls.loader.ExcelXLSLoader.loadCell(ExcelXLSLoader.java:
> 181)

and then evaluate `exprCell.getCellIndex()`. That should tell you the
cell whose formula cannot be parsed. I would then be interested in
that formula or, if you can narrow it down, the part of it that does
not work.

I've also opened a ticket that we should indicate the problematic
cells in such error messages:
https://www.formulacompiler.org/trac/ticket/27

-parren

[Steve]

Thanks, Peter.

> and then evaluate `exprCell.getCellIndex()`. That should tell you the
> cell whose formula cannot be parsed. I would then be interested in
> that formula or, if you can narrow it down, the part of it that does
> not work.

From your advice I was able to locate the error cell. It was an array
formula - which looks to be unsupported ATM.
I was able to edit the expression to be something different that will
return the same result and is also supported, so that bullet looks to
be safely dodged - thanks for the guidance for that.

However, I appear to be playing whack-a-mole with this spreadsheet
(not authored by me) and AFC, now this:

Exception in thread "main" java.util.EmptyStackException
at java.util.Stack.peek(Stack.java:79)
at java.util.Stack.pop(Stack.java:61)
at
jxl.biff.formula.BuiltInFunction.getOperands(BuiltInFunction.java:98)
at
jxl.biff.formula.TokenFormulaParser.addOperator(TokenFormulaParser.java:
450)
at
jxl.biff.formula.TokenFormulaParser.parseSubExpression(TokenFormulaParser.java:
369)
at
jxl.biff.formula.TokenFormulaParser.parse(TokenFormulaParser.java:111)
at jxl.biff.formula.FormulaParser.parse(FormulaParser.java:110)
at
jxl.read.biff.NumberFormulaRecord.getFormula(NumberFormulaRecord.java:
179)
at
org.formulacompiler.spreadsheet.internal.excel.xls.loader.ExcelXLSLoader.loadCell(ExcelXLSLoader.java:
177)
at
org.formulacompiler.spreadsheet.internal.excel.xls.loader.ExcelXLSLoader.loadRows(ExcelXLSLoader.java:
161)
at
org.formulacompiler.spreadsheet.internal.excel.xls.loader.ExcelXLSLoader.loadFrom(ExcelXLSLoader.java:
116)
at
org.formulacompiler.spreadsheet.internal.loader.SpreadsheetLoaderDispatcher.loadFrom(SpreadsheetLoaderDispatcher.java:
54)
at
org.formulacompiler.spreadsheet.SpreadsheetCompiler.loadSpreadsheet(SpreadsheetCompiler.java:
156)
at
org.formulacompiler.spreadsheet.internal.util.EngineBuilderImpl.loadSpreadsheet(EngineBuilderImpl.java:
132)
at Sc1.compile(Unknown Source)
at Sc1.main(Unknown Source)

I checked that one too, found out it uses MDETERM (properly, it
appears: =MDETERM(R38:T40) ).

I can't tell from the Limitations section whether this expression is
supposed to be supported.

Are we hosed here?

Oh, and of course I'd post the workbook but it's "company
confidential".

[Steve]

I got through compilation OK by bulldozing unsupported functions just
to see what I'm up against (not too bad - just MDETERM and MINVERSE).
Per the 'contributing' section, I assume if no other options work I
can implement these myself.

(One funny thing - formulacompiler seems to evaluate every expression
in the workbook, regardless whether it's referenced by the output
cells' cell chain back to input or not.)

That's the bad news. The worse news is I'm getting the following
runtime exception (full stack trace elided for clarity - it's 150+
lines, and I can provide whatever necessary) -
>
Exception in thread "main"
org.formulacompiler.compiler.CompilerException$UnsupportedExpression:
Index: 46, Size: 0
Cell containing expression is 'io1'!B19.
Referenced by cell 'io1'!B19.
>

FWIW, the expression in this cell is =MATCH(1,P19:P27,0) - and if I
replace it with its value it handles it ok, but at that point the same/
similar exception is thrown for another cell that is simply a
reference to a cell on a separate sheet. One other thing, excel
blew up in the middle of all my bulldozing. It recovered OK, but it
may be the reason somehow for this. I'll start a fresh run of all my
changes if it seems like this could be the culprit. I may do it
anyway, seeing as how much fun it was the first time :-P

I appreciate any guidance I can get on missing formula implementation,
or this funky exception.

[Peter Arrenbrecht]

On Wed, Jul 9, 2008 at 5:21 AM, Steve <steve....@gmail.com> wrote:
>
> I got through compilation OK by bulldozing unsupported functions just
> to see what I'm up against (not too bad - just MDETERM and MINVERSE).
> Per the 'contributing' section, I assume if no other options work I
> can implement these myself.

We're going to look into supporting these. But thanks for considering it!

Meanwhile, the definitive reference for what's supported is:

http://www.formulacompiler.org/doc/reference/index.htm

Using a text search on this page should quickly tell you whether a
particular function is supported.

>
> (One funny thing - formulacompiler seems to evaluate every expression
> in the workbook, regardless whether it's referenced by the output
> cells' cell chain back to input or not.)

Hmm. Good point. Right now it is only lazy about parsing required
formulas from the textual form to its own internal AST. But it has
JExcelAPI parse all formulas from Excel's internal format to textual
form.

http://www.formulacompiler.org/trac/ticket/28

>
> That's the bad news. The worse news is I'm getting the following
> runtime exception (full stack trace elided for clarity - it's 150+
> lines, and I can provide whatever necessary) -
>>
> Exception in thread "main"
> org.formulacompiler.compiler.CompilerException$UnsupportedExpression:
> Index: 46, Size: 0
> Cell containing expression is 'io1'!B19.
> Referenced by cell 'io1'!B19.
>>
>

Strange. We do support this form:

http://www.formulacompiler.org/doc/reference/lookupfuncs.htm#pagetoc__1_3

Could it be any of the cells in P19:P27 is the culprit?

> FWIW, the expression in this cell is =MATCH(1,P19:P27,0) - and if I
> replace it with its value it handles it ok, but at that point the same/
> similar exception is thrown for another cell that is simply a
> reference to a cell on a separate sheet.

Would this be one in P19:P27, or referenced from them?

> One other thing, excel
> blew up in the middle of all my bulldozing. It recovered OK, but it
> may be the reason somehow for this. I'll start a fresh run of all my
> changes if it seems like this could be the culprit. I may do it
> anyway, seeing as how much fun it was the first time :-P
>
> I appreciate any guidance I can get on missing formula implementation,
> or this funky exception.

-parren

[parren]

On Jul 9, 8:18 am, "Peter Arrenbrecht" <peter.arrenbre...@gmail.com>
wrote:

> On Wed, Jul 9, 2008 at 5:21 AM, Steve <steve.wid...@gmail.com> wrote:
>
> > I got through compilation OK by bulldozing unsupported functions just
> > to see what I'm up against (not too bad - just MDETERM and MINVERSE).
> > Per the 'contributing' section, I assume if no other options work I
> > can implement these myself.
>
> We're going to look into supporting these. But thanks for considering it!

MINVERSE is going to require adding array formula support to JExcelAPI
and AFC, as it returns an array. I've asked management whether we are
going to pursue this in the near future.
-parren

[Steve]

Peter:

Thank you, for your help.

> > I got through compilation OK by bulldozing unsupported functions just
> > to see what I'm up against (not too bad - just MDETERM and MINVERSE).
> > Per the 'contributing' section, I assume if no other options work I
> > can implement these myself.
>
> We're going to look into supporting these. But thanks for considering it!

Two things - sounds like you may or may not, and /or your support
timeline may be outside our needs.
If so, is there a 'roll your own' guide somewhere? If not, if I can
get a few guiding tips I'd be happy to post a how-to to the wiki if it
comes to that.
I guess this part should move to the contrib section...

> Meanwhile, the definitive reference for what's supported is:
> http://www.formulacompiler.org/doc/reference/index.htm
>
> Using a text search on this page should quickly tell you whether a
> particular function is supported.

Thanks - thats exactly how I figured out I was hosed with these two
functions.

> http://www.formulacompiler.org/trac/ticket/28

Very cool, thanks. Of course that prompts the question "any idea when
1.1.1 may be expected?" question is inevitable.

> Could it be any of the cells in P19:P27 is the culprit?

> Would this be one in P19:P27, or referenced from them?

I was starting to answer these specific questions, but In looking at
the behavior and at my post, I think I got a bit mixed up.

Let me start over, but still answer your questions where possible.

1) I redid the translation work, no excel blowup this time.
2) The first time AFC blows up on the file in a way I can't see is
wrong is here:
...........

Exception in thread "main"
org.formulacompiler.compiler.CompilerException$UnsupportedExpression:

MATCH must have the same type of argument in the fi
nd slot.
In expression >> MATCH( 'm1'!A6, 'db'!B1:'m1'!B218, 0.0 ) << ; error
location indicated by >>..<<.
Cell containing expression is Y11.
Referenced by cell 'm1'!Y11.
...........

Note the 2nd arg (the range) in the MATCH expression - and the 2nd
part of it (the range endpoint). It has a sheetname that is (a)
different than the sheetname for the range startpoint and (b) is not
actually in the expression (c) (though it is the name of the sheet
containing the cell) - here is the expression seen in excel:
=MATCH(A6,'db2'!$B$1:$B$218,0)

Note FWIW cell A6 is a string, and the entire single-column range
contains in each cell a string.

3) I replaced this method with its return value, and find the blowup I
reported last time:

Exception in thread "main"
org.formulacompiler.compiler.CompilerException$UnsupportedExpression:
Index: 46, Size: 0
Cell containing expression is 'io1'!B19.
Referenced by cell 'io1'!B19.

The expression in B19 is =INDEX(Q19:Q27,$P$30)

The expression in P30 is =MATCH(1,P19:P27,0)

The expressions in Q19:Q27 are all of the form ='m1'!D6
The expressions in P19:P27 are all of the form =IF(R19<P$18,1,0)

Nothing obvious, to me.

Not sure if its pertinent, but B19 is a (named) output cell.

Lots of questions, few clues. But to summarize:
My proprietary workbook has a few unsupported features (MDETERM,
MINVERSE) and it also blows up when apparently only supported features
exist.

Seems like the only forward path on the second part of the problem is
to get clearance to share the file. Let me know and I'll get started
on that.

[Steve]

> Seems like the only forward path on the second part of the problem is
> to get clearance to share the file. Let me know and I'll get started
> on that.

Yeah, there's dim chance of that at this time. Flipping it around,
are their extra debugging tools etc. available so I can dig deeper and
share what I get?
I'm already rebuilding the jarfiles, so if the answer is in the src
disti just point me there...

Cheers,
Steve

[Peter Arrenbrecht]

On Wed, Jul 9, 2008 at 4:43 PM, Steve <steve....@gmail.com> wrote:
>> > I got through compilation OK by bulldozing unsupported functions just
>> > to see what I'm up against (not too bad - just MDETERM and MINVERSE).
>> > Per the 'contributing' section, I assume if no other options work I
>> > can implement these myself.
>>
>> We're going to look into supporting these. But thanks for considering it!
>
> Two things - sounds like you may or may not, and /or your support
> timeline may be outside our needs.
> If so, is there a 'roll your own' guide somewhere? If not, if I can
> get a few guiding tips I'd be happy to post a how-to to the wiki if it
> comes to that.
> I guess this part should move to the contrib section...

Well, there is a ton of developer documentation at

http://www.formulacompiler.org/contribute/hacking/index.htm

which should get you up to speed. However, supporting array formulas
is a tough call as it entails extending the formula parser in
JExcelAPI as well as adding new concepts to the compiler in AFC. So
this unfortunately goes way beyond what we expect casual users to be
able to contribute.

Anyway, one of us is right now tackling MDETERM(), which is not an
array function.

> Very cool, thanks. Of course that prompts the question "any idea when
> 1.1.1 may be expected?" question is inevitable.

No fixed schedule, but I guess we could release once we manage to
solve your problems. ;)

Ah, this is interesting. Shall investigate.

> 3) I replaced this method with its return value, and find the blowup I
> reported last time:
> Exception in thread "main"
> org.formulacompiler.compiler.CompilerException$UnsupportedExpression:
> Index: 46, Size: 0
> Cell containing expression is 'io1'!B19.
> Referenced by cell 'io1'!B19.
>
> The expression in B19 is =INDEX(Q19:Q27,$P$30)
>
> The expression in P30 is =MATCH(1,P19:P27,0)
>
> The expressions in Q19:Q27 are all of the form ='m1'!D6
> The expressions in P19:P27 are all of the form =IF(R19<P$18,1,0)
>
> Nothing obvious, to me.

Nor, sadly, to me.

>
> Not sure if its pertinent, but B19 is a (named) output cell.
>
> Lots of questions, few clues. But to summarize:
> My proprietary workbook has a few unsupported features (MDETERM,
> MINVERSE) and it also blows up when apparently only supported features
> exist.
>
> Seems like the only forward path on the second part of the problem is
> to get clearance to share the file. Let me know and I'll get started
> on that.

> [...]

> Yeah, there's dim chance of that at this time.

That would certainly have been the most promising option. :(

> are their extra debugging tools etc. available so I can dig deeper and
share what I get?

Not exactly. We are developing a tracing mode for computed engines
right now, but this won't help in your case anyway. Can you try to
reproduce the problems in a simplified and shareable sheet? Meanwhile,
I'll be trying to reproduce this myself, too.

I appreciate your tenacity.
-parren

[Steve]

> Well, there is a ton of developer documentation at
>
> http://www.formulacompiler.org/contribute/hacking/index.htm
>

Thanks, nice.

> which should get you up to speed. However, supporting array formulas
> is a tough call as it entails extending the formula parser in
> JExcelAPI as well as adding new concepts to the compiler in AFC. So
> this unfortunately goes way beyond what we expect casual users to be
> able to contribute.

Yeah, that felt like a reach to suggest right after I posted it.

> Anyway, one of us is right now tackling MDETERM(), which is not an
> array function.

Whoo hoo!

> > Very cool, thanks. Of course that prompts the question "any idea when
> > 1.1.1 may be expected?" question is inevitable.
>
> No fixed schedule, but I guess we could release once we manage to
> solve your problems. ;)

No fixed schedule - I'm envious. Thanks for making it all about me :-
P

> Ah, this is interesting. Shall investigate.

Thanks.

> Nor, sadly, to me.

Bummer.

> Not exactly. We are developing a tracing mode for computed engines
> right now, but this won't help in your case anyway. Can you try to
> reproduce the problems in a simplified and shareable sheet?

I'd like to try, but it doesn't seem realistic, given the size/
complexity of the existing workbook and not knowing what is causing
it. There are > 1k formulas in just the chain from output back to
input in the part I'm focusing on, and that is 1/3 of the
functionality... needle in a haystack? Is there any way to get NDA
coverage if I were to post it privately?

> Meanwhile, I'll be trying to reproduce this myself, too.

Brave man, that sounds over and above. I guess that means I'll have
to too. Argh.

> I appreciate your tenacity.

Thanks - I appreciate your help.

[Peter Arrenbrecht]

I have reproduced this problem. We're not parsing off-sheet ranges correctly.

http://www.formulacompiler.org/trac/ticket/29

-peter

[Peter Arrenbrecht]

On Fri, Jul 11, 2008 at 10:57 AM, Peter Arrenbrecht

We have a fix for this. To try it please download

http://arrenbrecht.ch/misc/formulacompiler-1.1.0.1-hotfix.zip

which contains a bunch of updated .jar files.

Thanks,
-peter

[Steve]

Peter.

> We have a fix for this. To try it please download
>
> http://arrenbrecht.ch/misc/formulacompiler-1.1.0.1-hotfix.zip

Thanks for that. Done, and the reported issue no longer blows up...
whoo hoo...

However -

Not trying to be funny, but meanwhile I got a new version of the
workbook from engineering, and
a) it still has MINVERSE, MDETERM
b) removing known unsupported expressions like those, I still get a
similar looking problem:

......
[java] Exception in thread "main"
org.formulacompiler.compiler.CompilerException$UnsupportedExpression:
The name 'Tab1' is not defined in this spreadsheet.
in expression Tab1 <<? -Calculations!M2; error location indicated by
<<?.
[java] Cell containing expression is C14.
[... remainder of exception trace elided for clarity ...]
......

Without the sheetname, I guess - I look at C14 of the first sheet with
math (3rd of 15 total), and I see this -
=t1m!M2

I change that to its value, and a different error pops up:
......
[java] Exception in thread "main"
org.formulacompiler.compiler.CompilerException$UnsupportedExpression:
The name 'Tab1' is not defined in this spreadsheet.
in expression Tab1 <<? -I-O!B8; error location indicated by <<?.
[java] Cell containing expression is A2.
[java] Referenced by cell L2.
[java] Referenced by cell D14.
[...]
......
I hunt, and find the cell in question is 'Tab1-I-O'!B8, referenced by
't1m'!A2, which is referenced by L2, D14, and many others on t1m
sheet.

Some sheetnames have '-' symbol, others have spaces... could this be
playing a role here?

Perhaps. I change all sheetnames to letter/digit combinations (no
spaces or hyphens) and get a new error which at least makes sense,
since it finds OFFSET and that doesn't look to be supported ATM.

Recap:
1. engineering is still in flux on the underlying workbook, and those
cheeky engineers are using unsupported formulas & formula types
(MINVERSE, MDETERM, OFFSET)
2. sheetname parsing still not 100%, but that seems minor in
comparison to #1

Seems like it's best to stand down and wait until we see what will
happen with array expressions, or at least until the workbook settles
down. At that point it would be safe for me to try to substitute out
unsupported stuff with fungible, supported alternatives.

> Thanks,
> -peter

Your welcome / Thanks / Arrrgh,
Steve

[Peter Arrenbrecht]

Oh my, but this are excellent reports, at least. Thanks.

http://www.formulacompiler.org/trac/ticket/30

> Seems like it's best to stand down and wait until we see what will
> happen with array expressions, or at least until the workbook settles
> down. At that point it would be safe for me to try to substitute out
> unsupported stuff with fungible, supported alternatives.

Sounds reasonable.

Cheers,
-peter

[Vladimir Korenev]

I have added support for MDETERM. Please download
http://arrenbrecht.ch/hg/formulacompiler-1.1.0.2-bin.zip to try it.

-Vova