Post-POI Formula Evaluation

["James G." <ja...@veldt.com>]

I've searched the archives and seen similar issues to the one  
described below, but was unable to find a solution.

As a test case, I've created a workbook in Excel 2004 with a single  
row. Cells A1 and B1 are empty, and cell C1 has a simple formula:  
=SUM(A1*B1). Using POI, I open this workbook and populate cells A1 and  
B1, and save the workbook as a new file.

Under POI 3.0.x (both the final and the beta), I'm able to write the  
file successfully; however when the file is opened in Excel, the value  
for C1 is not calculated.

Under POI 2.5.1 with the same procedure, when the file is opened in  
Excel after writing from POI, the value in C1 is calculated correctly.

Calculation settings in Excel do not seem to effect the outcome.  
Additionally, a macro-based solution to this problem is not preferred,  
as macros are unavailable in the forthcoming Office 2008 for Mac OS X.

I've seen this problem described before, but has anyone seen a  
solution to it?

Cheers,
James


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

Re: Post-POI Formula Evaluation

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

On Thu, 3 Jan 2008, James G. wrote:
> I experimented with the formula evaluator and was able to successfully 
> evaluate the formulas. But, there didn't seem to be a way (I may not 
> have dug deep enough) to leave the cell's value as the formula, rather 
> than the result of the formula. I'd like to leave open the ability of 
> the Excel user to update the dependent cells, and still have the formula 
> cell update it's result, after being processed by POI.

You should be able to evaluate the formula, set the value into the cell, 
but leave the formula there too. I'd suggest checking to docs again

> The other drawback to this approach, is that I will ultimately be 
> working with a much larger input file than my simple contrived example. 
> While calculating the result for all the formulas contained in the 
> workbook is not impossible, it's not necessarily desired, and would seem 
> to remove a good deal of flexibility from the input file format.

You'll only need to re-calculate the formulas for where you have changed 
the source data. The rest can be left alone

Nick

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

Re: Post-POI Formula Evaluation

["James G." <ja...@veldt.com>]

On Jan 3, 2008, at 6:19 AM, Nick Burch wrote:
>
> That is expected. You'll need to calculate the value yourself:
> 	http://poi.apache.org/hssf/eval.html

I experimented with the formula evaluator and was able to successfully  
evaluate the formulas. But, there didn't seem to be a way (I may not  
have dug deep enough) to leave the cell's value as the formula, rather  
than the result of the formula. I'd like to leave open the ability of  
the Excel user to update the dependent cells, and still have the  
formula cell update it's result, after being processed by POI.

>> The formula calculation triggered by poi 2.5.x was rather hit-and- 
>> miss, and in many cases wouldn't work properly (sometimes not  
>> calculating, often not re-calculating). With poi 3.x, you can  
>> evaluate the formulas properly, and know that they'll show the  
>> correct value every time.

The other drawback to this approach, is that I will ultimately be  
working with a much larger input file than my simple contrived  
example. While calculating the result for all the formulas contained  
in the workbook is not impossible, it's not necessarily desired, and  
would seem to remove a good deal of flexibility from the input file  
format.

Thanks for your input, Nick.
-James

Re: Post-POI Formula Evaluation

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

On Wed, 2 Jan 2008, James G. wrote:
> As a test case, I've created a workbook in Excel 2004 with a single row. 
> Cells A1 and B1 are empty, and cell C1 has a simple formula: 
> =SUM(A1*B1). Using POI, I open this workbook and populate cells A1 and 
> B1, and save the workbook as a new file.
>
> Under POI 3.0.x (both the final and the beta), I'm able to write the 
> file successfully; however when the file is opened in Excel, the value 
> for C1 is not calculated.

That is expected. You'll need to calculate the value yourself:
 	http://poi.apache.org/hssf/eval.html

> Under POI 2.5.1 with the same procedure, when the file is opened in 
> Excel after writing from POI, the value in C1 is calculated correctly.

The formula calculation triggered by poi 2.5.x was rather hit-and-miss, 
and in many cases wouldn't work properly (sometimes not calculating, often 
not re-calculating). With poi 3.x, you can evaluate the formulas properly, 
and know that they'll show the correct value every time.

Nick

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