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 <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
