poi-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Eric Ladner <elad...@longbeach.goldinc.com>
Subject Re: Formula Parsing Redux
Date Wed, 28 Jan 2004 05:03:13 GMT
On 27 Jan 2004, Avik Sengupta wrote:

> No problems about a broken record, lots of things slip thru the cracks
> among open source developers... so reminders are always good so long as
> they are not prefixed with a "bozo..." :))
> I am not to sure about your stuff about getSheetReferences ... are you
> sure you are looking at the latest version.. coz this patch
> (http://nagoya.apache.org/bugzilla/show_bug.cgi?id=21444) changed the
> FormulaParser and toFormulaString implementations to take in a workbook
> object rather than SheetReferences. 

Yep.  Looking at the latest version for sure.  Even with that patch, you 
still have a chicken-egg problem of how do you get the workbook to send to 
the formula parser.  The way it's written now, it's got to have either the 
workbook to get the SheetReferences, or a copy of the SheetReferences for 
3DRefs to work (by 3DRefs, I mean a formula like this:  
=Sheet1!A1+Sheet1!A2) otherwise the formula parser blows up.

See http://nagoya.apache.org/bugzilla/show_bug.cgi?id=21923

I can rework this patch on 2.0 if you like, but with Paul's refactoring of 
the FormulaParser to take a workbook instead of SheetReferences, I think 
that the FormulaParser has been backed into a corner.


> But that does not necessarily solve your problem. I must admit that I
> would tend to agree with most of the other POI developers that we should
> NOT expose low level objects to high level. However, I think the patch
> solves the workbook access problem without any encapsulation havoc. 
> I also have a vague feeling that probably we also support external sheet
> references, but I could be confused as to what you mean. So the best
> would be to send over the testcase that you talk about.. maybe add it to
> bugzilla? That would be really helpful. 

I have a test case.  I'll submit it to bugzilla and get back to you.

> Related to all this is shared formula, which also needs access to the
> workbook. Given the changes above, I have a fair idea how that can be
> implemented without exposing workbook at a high level. So if sheet
> references arent done yet, we could do it similarly. (Of course, i am
> currently only talking theoretically, one never knows till one sees the
> code :)
> Regards
> -
> Avik
> On Sun, 2004-01-25 at 09:49, Eric Ladner wrote:
> > Sorry to sound like a broken record.. Again.
> > 
> > Ok.. Point your attention to the formula parser, specifically the 
> > public constructor FormulaParser(String, Workbook) and the two 
> > toFormulaString methods (SheetReferences, List) and (SheetReferences,
> > Ptgs[]) respecfully.
> > 
> > Here's my problem.
> > 
> > When dealing with a HSSFWorkbook, AFAIK, it's impossible to support
> > formula parsing when formulas reference another sheet in the workbook
> > because of the private functions restricting access to both the 
> > internal Workbook that the HSSFWorkbook contains and the SheetReferences
> > that.  You'll recall that there was a patch some time back that
> > converted a lot of the formula parser's methods to use SheetReferences
> > instead of the Workbook, but SheetReferences has the same problem in 
> > that you can't call getSheetReferences on a HSSFWorkbook (the method
> > doesn't exist), and getSheetReferences can't be called on the 
> > Workbook itself if you can't do a getWorkbook() on it's HSSFWorkbook
> > since it's private.
> > 
> > I created and submitted a patch that allowed HSSFWorkbook to grab 
> > a copy of the SheetReferences, but it was shot down for fear of
> > exposing too much of the internal Workbook guts, which I don't have
> > a problem with.  
> > 
> > Is there something I'm overlooking here?  Is nobody else out there
> > parsing formula's with references to other sheets?  I have an example
> > spreadsheet and a little tiny test case if anybody's interested.
> > 
> > Probably the most obvious way to fix this is to allow access from
> > the formula parser to the internal workbook so it can gather the data
> > it needs to support external sheet references.
> > 
> > Thoughts?  Comments?
> > 
> > Thanks,
> > 
> > Eric
> > 
> > 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: poi-dev-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: poi-dev-help@jakarta.apache.org

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

View raw message