POI Event API Question

[Mahesh Ganapathy <mg...@gmail.com>]

Hello All,
I am interested in using the Event API's in POI. While doing so, I am faced
with the issue of minimal documentation on many of the classes (I don't
blame the developers) Which record should I be using to identify a formula
based cell (alphanumeric calculations involved) and how do I calculate it's
value. If I use a FormulaRecord.sid, I can get the row and column number but
I cannot get the value. (NaN since the formula contains string
concatenation). If I use the StringRecord, I am not able to get the row and
cell numbers. Assistance would be greatly appreciated.

Re: POI Event API Question

[Nick Burch <ni...@torchbox.com>]

On Wed, 26 Mar 2008, Mahesh Ganapathy wrote:
>> However, excel ought to save the last value for a formula along with it.
>> So, if all you want is that most recent value, and don't care about
>> re-calculating it again, just grab that.
>
> Where is this stored? How do I "grab" that ? I really don't need to 
> recalculate the value.

FormulaRecord.getValue() will give it to you if it's a number / date. 
If it's a string, then there will be a second record to go along with the 
formula, holding your string. Take a look at the code relating to 
FormulaRecordAggregate to see how the usermodel handles this

Nick

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

Re: POI Event API Question

[Mahesh Ganapathy <mg...@gmail.com>]

Hello Nick,

On 3/26/08, Nick Burch <ni...@torchbox.com> wrote:
>
> On Tue, 25 Mar 2008, Mahesh Ganapathy wrote:
> > I am interested in using the Event API's in POI. While doing so, I am
> > faced with the issue of minimal documentation on many of the classes (I
> > don't blame the developers) Which record should I be using to identify a
> > formula based cell (alphanumeric calculations involved)
>
> If it's a real formula, the formula string will be in the ptgs of that
> record. If it's actually a shared formula, you'll need to grab the
> appropriate shared formula record, and get the formula from the ptgs in
> that


I did figure that out last evening but still thanks for this info. I get the
Ptgs and it is a mix of ConcatPtg, StringPtg and RefVPtg.

> how do I calculate it's value.
>
> Use the usermodel. Usermodel has a formula evaluator, which will calculate
> the value of a formula for you, based on all the other data in the
> spreadsheet. There's no shortcut for re-calculating a formula's value, you
> really do need the whole spreadsheet in memory to be able to look up other
> cells.


UserModel.FormulaEvaluator requires HSSFCell/Worksheet/Reocrd as method
parameters for calculation. As you say, this means that I need to wait
untill the entire sheet has been loaded into memory which defeats my purpose

However, excel ought to save the last value for a formula along with it.
> So, if all you want is that most recent value, and don't care about
> re-calculating it again, just grab that.


Where is this stored? How do I "grab" that ? I really don't need to
recalculate the value.

In general though, your best bet is to look at the usermodel code. See how
> that does it, then grab the same sort of records as it does. You could try
> reading the recently released microsoft documentation on the file format,
> but I generally find the poi usermodel source code much easier to
> understand, and much more comprehensive!


As you would have realised by now, my questions stem out of my ignorance on
these subjects. I wish I had the luxury of time to go through them at this
stage. I intend to in as an when time permits but for now I depend on the
expertice and knowledge of the community for guidence.

Nick
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
> Thanks,
Mahesh Ganapathy.

Re: POI Event API Question

[Nick Burch <ni...@torchbox.com>]

On Tue, 25 Mar 2008, Mahesh Ganapathy wrote:
> I am interested in using the Event API's in POI. While doing so, I am 
> faced with the issue of minimal documentation on many of the classes (I 
> don't blame the developers) Which record should I be using to identify a 
> formula based cell (alphanumeric calculations involved)

If it's a real formula, the formula string will be in the ptgs of that 
record. If it's actually a shared formula, you'll need to grab the 
appropriate shared formula record, and get the formula from the ptgs in 
that

> how do I calculate it's value.

Use the usermodel. Usermodel has a formula evaluator, which will calculate 
the value of a formula for you, based on all the other data in the 
spreadsheet. There's no shortcut for re-calculating a formula's value, you 
really do need the whole spreadsheet in memory to be able to look up other 
cells.

However, excel ought to save the last value for a formula along with it. 
So, if all you want is that most recent value, and don't care about 
re-calculating it again, just grab that.


In general though, your best bet is to look at the usermodel code. See how 
that does it, then grab the same sort of records as it does. You could try 
reading the recently released microsoft documentation on the file format, 
but I generally find the poi usermodel source code much easier to 
understand, and much more comprehensive!

Nick

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