reading formulae String containing named cells as operands

[Steve Widmar <sw...@irf.com>]

Ok, I give up... (enough to ask for help, anyway):

Ultimately, I'm trying to read, then write xls formulae where said formulae use named cells as operands ...
... For now just trying to read... 
... specifically, reading the text right of the equals sign in each formula cell... 
not even down to the individual token level.

Heres an example to try to illustrate my goal:
1. open up new .xls 
2. name 2 cells, any two cells, 'a' and 'b' (eg: using Insert/Name/Define menu option)
and give them numeric values - doesnt matter what, say a is 3 and b is 4.
3. in a 3rd cell, create a formula =a+b (of course, xls shows 7, given my sample values)
4. using the magic that is poi, 
(eg hacking from the 'Event Api' section of http://jakarta.apache.org/poi/hssf/how-to.html, 
 and/or org.apache.poi.hssf.dev.FormulaViewer)
try to System.out.println() '=a+b' (or 'a+b')


FormulaRecord.getParsedExpression() returns null List,  
and 
org.apache.poi.hssf.dev.FormulaViewer gets:
Whoops!
java.lang.ClassCastException: org.apache.poi.hssf.record.UnknownRecord

So.

I've done a few hours of archive googling and javadoc/example reading / hacking...
(eg: http://www.mail-archive.com/poi-user@jakarta.apache.org/msg01190.html)

... is what i'm trying to do impossible with current/past/future poi, or 
is it possible and i just havent RTFM/RTFS'd enough?

(if its the former, i will cheerfully move this over to poi-dev and volunteer to roll up sleeves an try to make it happen...
 in fact I'll go search amazon right now for a used copy of Microsoft Excel 97 Developer's Kit (ISBN: 1-57231-498-2))

oh yeah:
i'm developing using xl 2000 on w2k
have tried with jakarta-poi-1.10.0-dev-bin and jakarta-poi-1.5.1-final-bin, same results.

Re: reading formulae String containing named cells as operands

[Avik Sengupta <av...@apache.org>]

There are a few existing bugs in the reading of formulas containing
names. Part of my TODO list :(

On Mon, 2003-06-16 at 00:11, Steve Widmar wrote:
> Ok, I give up... (enough to ask for help, anyway):
> 
> Ultimately, I'm trying to read, then write xls formulae where said formulae use named cells as operands ...
> ... For now just trying to read... 
> ... specifically, reading the text right of the equals sign in each formula cell... 
> not even down to the individual token level.
> 
> Heres an example to try to illustrate my goal:
> 1. open up new .xls 
> 2. name 2 cells, any two cells, 'a' and 'b' (eg: using Insert/Name/Define menu option)
> and give them numeric values - doesnt matter what, say a is 3 and b is 4.
> 3. in a 3rd cell, create a formula =a+b (of course, xls shows 7, given my sample values)
> 4. using the magic that is poi, 
> (eg hacking from the 'Event Api' section of http://jakarta.apache.org/poi/hssf/how-to.html, 
>  and/or org.apache.poi.hssf.dev.FormulaViewer)
> try to System.out.println() '=a+b' (or 'a+b')
> 
> 
> FormulaRecord.getParsedExpression() returns null List,  
> and 
> org.apache.poi.hssf.dev.FormulaViewer gets:
> Whoops!
> java.lang.ClassCastException: org.apache.poi.hssf.record.UnknownRecord
> 
> So.
> 
> I've done a few hours of archive googling and javadoc/example reading / hacking...
> (eg: http://www.mail-archive.com/poi-user@jakarta.apache.org/msg01190.html)
> 
> ... is what i'm trying to do impossible with current/past/future poi, or 
> is it possible and i just havent RTFM/RTFS'd enough?
> 
> (if its the former, i will cheerfully move this over to poi-dev and volunteer to roll up sleeves an try to make it happen...
>  in fact I'll go search amazon right now for a used copy of Microsoft Excel 97 Developer's Kit (ISBN: 1-57231-498-2))
> 
> oh yeah:
> i'm developing using xl 2000 on w2k
> have tried with jakarta-poi-1.10.0-dev-bin and jakarta-poi-1.5.1-final-bin, same results.
-- 
Avik Sengupta <av...@apache.org>