You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@poi.apache.org by Avik Sengupta <av...@itellix.com> on 2003/08/25 20:00:02 UTC
Re: 'NO IDEA SHARED FORMULA EXP PTG' appears when read copy/paste formula HSSF
Joji,
OK, here goes
Shared formula records are stored as one Formula and SharedFormula record for the first cell in a range, and then FormulaRecords with an ExpPtg for all other cells that just reference the original record.
The creation of the correct string happens in
public String toFormulaString(Workbook book) in o.a.p.record.formula.ExpPtg.java
You have to find a reference to the FormulaRecordAggregate out of the book, from which you will get the FormulaRecord and the SharedFormulaRecord (thats the very difficult bit.. will require some thinking, since hackish solutions will not be committed..) . You will then have to do a toFormulaString on the formula record, but you will have to make an allowance for the range difference ie, the the formula record will be for A1, but if you are in A10, you will have to change the relative references. For this last bit, you might have to refactor the Reference code .. which is duplicated unfortunately at two places... (so this is the slightly difficult bit)
To do all this, the first thing you need to do is read the specs, either in the excel book (if you have it... if you are in Bangalore, i can lend it you for some time....) , or in the OpenOffice documentation..
Hope that helps. This is something we really need to do, so it would be great if you could help.
Shout if you need anything else.
Regards
-
Avik
PS. If you are doing this, once you have a better fix on what needs to be done, lets discuss on the list whether to do it for 2.0 or 3.0
-------Original Message-------
> From: Danny Mui <danny@muibros.com>
> Subject: Re: Bugzilla Bug 20321 'NO IDEA SHARED FORMULA EXP PTG' appears when read copy/paste formula HSSF
> Sent: 25 Aug 2003 21:04:38
>
> The write way to fix this is to undo the "optimization" of the shared
> formula. Instead of referencing an EXP ptg, we should expand the
> formula in full when reading the file (sheet.fillfields). My first cut
> with shared formulas was to just allow it to be read. Didn't realize
> people messed with the shared formula fields.
>
> Having the Excel Developer's SDK book is a real help for doing this but
> using the BiffViewer and reading hex dumps can work as well.
>
> Joji John wrote:
>
> >
> >
> >Yes, The problem is excatly what you are describing. I would like to know
> >if I can work with somebody to fix this problem and thus contribute to this
> >project. I require some directions to fix this bug.
> >
> >Thanks and Regards,
> >Joji John,
> >
> >
> >
> >
> > Danny Mui
> > m> cc:
> > Subject: Re: Bugzilla Bug 20321 "NO IDEA SHARED FORMULA
> > 08/22/2003 10:15 EXP PTG" appears when read copy/paste formula HSSF
> > PM
> > Please respond to
> > "POI Developers
> > List"
> >
> >
> >
> >
> >
> >The bug in question occurs when you try to modify a cell in the
> >copy/pasted formula group. If you're simply reading/writing AROUND
> >these cells you should be fine. In the future we should break up the
> >shared formula to be regular formulas.
> >
> >If this isn't what you're doing, please submit a new bug report with
> >example code (simplest amount of code possible) and the excel file that
> >duplicates this problem.
> >
> >Joji John wrote:
> >
> >
> >
> >>
> >>Hi,
> >>I am using HSSF to read microsoft excel files. Now, we have a situation
> >>same as the one described in Bug # 20321. I had taken the latest poi jar
> >>file poi-bin-2.0-pre3-20030728.zip updated at 28-Jul-2003 11:56 and
> >>
> >>
> >found
> >
> >
> >>the problem persisting. Do we have some information on any possible fix to
> >>this defect?
> >>
> >>Thanks and Regards,
> >>Joji John,
> >>IBM Software Labs,
> >>Airport Road,
> >>Bangalore 560 017
> >>Phone: 91 80 5044974
> >>Email: jjoji@in.ibm.com
> >>
> >>
> >>
> >>---------------------------------------------------------------------
> >>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
> >
> >
> >
> >
> >
> >---------------------------------------------------------------------
> >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
-------Original Message-------