how to update the numeric value of a formula cell

[Jon Svede <js...@yahoo.com>]

I am using POI 3.8 Beta, though it's a week or two out of date.

I have a spreadsheet where if I open it and evaluate a cell the evaluated value 
is equal to the numeric value of the cell.  However, if I change one of the 
inputs to this cell and re-evaluate it, the numeric value and evaluated value 
are no longer equal.  The evaluated value changes but the numeric doesn't; is 
there a way to update the numeric value without explicitly setting it?

Thanks,

Jon


      

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

Re: how to update the numeric value of a formula cell

[Jon Svede <js...@yahoo.com>]

I was using the plain evaluate( Cell cell) method but I eventually figured out 
that I needed to use evaluateFormulaCell(Cell cell).  It didn't solve my issue 
though.

The issue we're having is that when we evaluate certain cells we don't get the 
same result as what Excel would produce.  In some cases it's a precision thing, 
I am talking about 1.0e-6 or greater (which for the most part is acceptable); in 
other cases though we don't get anything close to what Excel produces.  


In the case of just opening a spreadsheet and trying to evaluate cells I can use 
the stored numeric value and compare the result of the evaluate() method to see 
which cells work and which ones don't.  The "problem" arises after I change some 
"input" cells that the evaluated cells depend on.  I quoted the word "problem" 
because clearly I wasn't thinking  this through: it's not like POI is trying to 
give me bad data while secretly stashing the right answer, waiting for me to ask 
the right question, so to speak.

Essentially I have to build some tests that validate the value return by POI 
against the known value from Excel to figure out where we were get numbers that 
deviate and the start debugging from there.

Jon





----- Original Message ----
From: Nick Burch <ni...@alfresco.com>
To: POI Users List <us...@poi.apache.org>
Sent: Wed, December 29, 2010 5:54:24 PM
Subject: Re: how to update the numeric value of a formula cell

On Wed, 29 Dec 2010, Jon Svede wrote:
> I have a spreadsheet where if I open it and evaluate a cell the evaluated value 
>is equal to the numeric value of the cell.  However, if I change one of the 
>inputs to this cell and re-evaluate it, the numeric value and evaluated value 
>are no longer equal.  The evaluated value changes but the numeric doesn't; is 
>there a way to update the numeric value without explicitly setting it?

Which evaluation method are you using? You can pick to either get the result 
returned to you, or have the source cell updated - are you using the correct one 
for your needs?

Nick

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


      

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

Re: how to update the numeric value of a formula cell

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

On Wed, 29 Dec 2010, Jon Svede wrote:
> I have a spreadsheet where if I open it and evaluate a cell the 
> evaluated value is equal to the numeric value of the cell.  However, if 
> I change one of the inputs to this cell and re-evaluate it, the numeric 
> value and evaluated value are no longer equal.  The evaluated value 
> changes but the numeric doesn't; is there a way to update the numeric 
> value without explicitly setting it?

Which evaluation method are you using? You can pick to either get the 
result returned to you, or have the source cell updated - are you using 
the correct one for your needs?

Nick

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