You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@poi.apache.org by Eric Ladner <el...@goldinc.com> on 2004/01/25 05:19:45 UTC
Formula Parsing Redux
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
Re: Formula Parsing Redux
Posted by Eric Ladner <el...@longbeach.goldinc.com>.
See: http://nagoya.apache.org/bugzilla/show_bug.cgi?id=26483
It's just some examples around what we're talking about here. I'm sure
you saw all the bugzilla spam also. ;)
E
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.
>
> 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.
>
> 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
Re: Formula Parsing Redux
Posted by Eric Ladner <el...@longbeach.goldinc.com>.
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.
Eric
>
> 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
Re: Formula Parsing Redux
Posted by Avik Sengupta <av...@itellix.com>.
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.
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.
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