poi-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From David Fisher <dfis...@jmlafferty.com>
Subject Re: POI Formulas
Date Tue, 01 Apr 2008 16:05:33 GMT
Hi Josh,

I think this has been a good discussion to bring out the different  
challenges in supporting formulas. Particularly the challenges  
involved in supporting other party's non-Microsoft extensions to  
Excel. There are different levels to this support.

(1) Assuring that the formulas are not damaged in handling by POI.

(2) Assuring that the formulas will re-evaluate when the workbook is  
opened in Excel subsequent to being handled by POI.

(3) Evaluating formulas within POI to the extent that all of  
Microsoft's built-in functions are supported.

(4) Evaluating formulas within POI to the extent that with some  
necessary limitations to OS - Add-in functions are supported.

Gareth reports:
> I have switched to the latest build in svn and I am now also getting
> #NAME?... When I click on the formula in Excel and click the green  
> tick to
> enter it it evaluates the formula correctly. Is there a way to
> programitically force excel to evaluate the formula?


Which puts him at (2) - needing to know how to set the workbook to  
recalculate all formulas as soon as it is re-opened. So, what is the  
POI-foo for that?

I think that your work towards (3) has been excellent! Thanks!

I don't think that (4) is worth the effort as there is (a) necessary  
OS limitations and (b) there are at least two ways that Add-ins exist  
- as VBA functions and as C#/.Net assemblies - I am ignorant of the  
details, I have someone else working on an Add-in, we are converting.  
I am curious about Java/C# integration but in a way that is really off  
topic.

What I am really looking for is making sure that when we handle a  
workbook we are not making it more difficult to handle than it was  
before we handle it. If the file automatically recalculates before  
handling then it still automatically recalculates after handling.

Regards,
Dave

On Apr 1, 2008, at 10:39 AM, Josh Micich wrote:

> On Tue, 1 Apr 2008, poijava wrote:
>> ... When I click on the formula in Excel and click the green tick
>> to enter it it evaluates the formula correctly. ...
>
> Excel tends to 'fix' incorrectly coded formulas if it can understand
> enough.  In this case, Excel is probably replacing the NamePtg with
> NameXPtg and adding all of the SUPBOOK related stuff.
>
>
> On Tue, Apr 1, 2008 at 6:57 AM, Nick Burch <nick@torchbox.com> wrote:
>> ...
>> Your best bet is probably to re-calculate in poi:
>>        http://poi.apache.org/hssf/eval.html
>> ...
>
> Unfortunately, that won't work yet.  Another part of add-in function
> logic that needs to be implemented is *evaluation*' (i.e. code under
> HSSFFormulaEvaluator).  That is scheduled to get done before the
> functionality to write new calls to add-in functions.  Both
> enhancements will be needed before this will work
>
> -josh
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
> For additional commands, e-mail: dev-help@poi.apache.org
>
>


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


Mime
View raw message