poi-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From bugzi...@apache.org
Subject DO NOT REPLY [Bug 47339] Unexpected base token id (24)
Date Wed, 10 Jun 2009 23:56:36 GMT

Josh Micich <josh@gildedtree.com> changed:

           What    |Removed                     |Added
             Status|NEW                         |RESOLVED
         Resolution|                            |WONTFIX

--- Comment #3 from Josh Micich <josh@gildedtree.com>  2009-06-10 16:56:34 PST ---
It looks like you are using a deprecated feature of Excel ('Natural Language
References').  From version 2007 onward this style of formula reference is not
available (Excel now automatically converts old files to use simple cell

Referencing cells/ranges with labels is fragile and vulnerable to ambiguity. 
This is probably why MS has decided to steer away from it.  For example - What
happens when the label cell is moved?  Is the actual cell below or to the right
of the label cell? If the label refers to a range, how are the exact boundaries
of the range determined?

A better solution is to use proper defined names (AKA 'named ranges') which
have been around for a while.

I've closed this bug off for the moment (hoping that using defined names works
for you).

'Natural Language References' are probably not in high demand for POI
functionality, so if you want to have this supported, you'll probably need to
do a lot of the leg-work.  It might make sense to just have POI convert old
tNlr (0x18) to equivalent tRef(0x24) or tArea(0x25) PTGs (like Excel 2007
does).  Unfortunately, tNlr is not well documented.  I found a bit of
information in the OOO source code.   It seems like the tNlr field layout is
variable and governed by the second byte (first byte is 0x18).  In the OOO
source code the values of the second byte have constants called "EXC_TOK_NLR_*"
(see below).  With some quick experimentation (on an old machine with  Excel
2003) I was able to produce tNlr tokens with second byte (0x06, 0x07 and 0x0A).
 Perhaps these are all that POI would need to support. 

Here is a link to some documentation about xlformula.hxx:

Some Hex Dumps of tNlr tokens
18 07 04 00 00 80   // from the sample file - label 'a' is in A5
18 06 06 00 09 80   // label in I7, value in J7
18 0A 0D 00 03 80   25 0E 00 11 00 03 00 03 00  // range label in D14, values
in D15:D18  - note tArea(D15:D18) seems to be correctly encoded here (starting
at 0x25).

Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.

To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org

View raw message