Excel does not evaluate a valid formula until I press F2

[Fernando Colombo <da...@gmail.com>]

Hello!

My program is using POI 3.5 to generate the attached XLS file. Notice
that the cell T5 has the following formula:

    IF(AND(ISNUMBER(H5);ISNUMBER(Q5));H5/Q5-1;"")

When I open it on Excel 2007, it shows nothing on that cell. When I
click on it, the formula bar shows correctly:

    =IF(AND(ISNUMBER(H5);ISNUMBER(Q5));H5/Q5-1;"")

And still, nothing is displayed in the cell space. I suspected that
the value was hidden by formats, then I changed font color, cell
background, but still nothing happens.
When I simply type F2 to edit cell content, then the above formula is
itself displayed on cell space, with the preceding equals symbol and
colored cell references, just like an ordinary correct formula. Notice
that I didn't change anything with Excel. Just pressed F2. Then I just
type Enter to confirm, and voila! Excel evaluates and shows the
expected value! After I save the XLS and reopen it, that cell is
correctly evaluated. Other cells in different rows but with a similar
formula are still incorrect, until I press F2-Enter on each of them.

You can see that the same XLS has many other formulas on many other
cells and sheets. The only formulas that fail are the ones on columns
T, U, V and W, which are similar to the above one. It seems that POI
is generating a file with malformed formulas, despite the fact they
are correct and Excel can understand and evaluate them after I type
F2, Enter. The code that creates that formula is the following:

            HSSFSheet sheet = ...;
            HSSFRow r = sheet.createRow(4);
            HSSFCell c = r.createCell('T'-'A');
            c.setCellFormula("IF(AND(ISNUMBER(H5);ISNUMBER(Q5));H5/Q5-1;\"\")");

Any ideas?

Thantks in advance!
darth.colombo

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