Excel formula evaluation problem

[Senan Postaci]

Hello,

Currently, We are using POI 3.2 Final 2008-10-19 release to generate Excel
Spreadsheet files and want to set an HSSFCell as a formula cell.
When a formula is set to a cell, Excel can not evaluate some of the formulas
(but not all) initially as the document opened. However,
problem can be solved by double clicking on the cell and then pressing Enter
without changing anything, Excel evaluates the formula.
This problem does not occur in OpenOffice.

To make things clear, we want to show the problematic formula:

In cell H3 we want to CONCATENATE strings in cells F3 and G3, if F3 is not
empty, else just write the value of G3 to H3.
The formula we want to use is "=IF(F3<>"";CONCATENATE(F3;" ";G3);G3)".
However, if F3 is not empty, in H3 cell #VALUE! error is displayed when the
document opened.
After double clicking on cell H3, then pressing Enter without changing anything,
the formula is evaluated.

Why can Excel not evaluate the some of the formulas generated by POI initially?
 
We will be very pleased if you help us about this problem as soon as possible.

Thanks in advance.

Senan Postaci


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

[Josh Micich]

Hello Senan,

> ... However,
> problem can be solved by double clicking on the cell and then pressing Enter
> without changing anything, Excel evaluates the formula.

This is symptomatic of POI encoding a wrong token operand class
somewhere in the formula.

There have been a few bugs fixed recently which are related to this.
For example:
https://issues.apache.org/bugzilla/show_bug.cgi?id=44675
https://issues.apache.org/bugzilla/show_bug.cgi?id=44677
https://issues.apache.org/bugzilla/show_bug.cgi?id=45060

Try out version 3.5-FINAL as your problem may have been solved already.

Unfortunately there are some still unfixed bugs too:
https://issues.apache.org/bugzilla/show_bug.cgi?id=45752
https://issues.apache.org/bugzilla/show_bug.cgi?id=48043

If you still are getting the same error with 3.5-FINAL, please add a
comment to bug 48043 giving details of the offending formula.

cheers,
josh

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