Bug 55185 - ISNUMBER function doesn't work in if statement
Summary: ISNUMBER function doesn't work in if statement
Status: RESOLVED INVALID
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.9-FINAL
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2013-07-02 15:18 UTC by srousseau
Modified: 2013-07-09 09:28 UTC (History)
0 users


Attachments
Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description srousseau 2013-07-02 15:18:21 UTC 
With POI 3.9, I'm faces the same problem as reported in bug id #27405

Here is the code :
        HSSFWorkbook book = new HSSFWorkbook();
        HSSFSheet sheet2 = book.createSheet("ma feuille");

        HSSFRow row = sheet2.createRow(0);
        HSSFCell cell = null;

        cell = row.createCell(0);
        cell.setCellValue(10);

        cell = row.createCell(1);
        cell.setCellValue(20);

        cell = row.createCell(2);
        cell.setCellValue(30);

        cell = row.createCell(3);
        cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
        cell.setCellFormula("IF(AND(ISNUMBER(A1),ISNUMBER(B1)),(A1*B1)/(1000),\"toto\")");

Where I open the workbook, I see "toto" in cell. Then I just click in the formula for cell A3 and enter. Then it works

Stephane
Comment 1 Nick Burch 2013-07-02 15:38:39 UTC 
After changing a formula, you must trigger a recalculation to ensure the cached value is correctly stored/set in the file

See http://poi.apache.org/spreadsheet/eval.html for instructions on how to do this
Comment 2 srousseau 2013-07-02 15:58:40 UTC 
(In reply to Nick Burch from comment #1)
> After changing a formula, you must trigger a recalculation to ensure the
> cached value is correctly stored/set in the file
> 
> See http://poi.apache.org/spreadsheet/eval.html for instructions on how to
> do this

Great!!
 It works with HSSFFormulaEvaluator.evaluateAllFormulaCells(myWorkBook).

I upgrade from POI 3.0 to POI 3.9 and I didn't need to call this code. So I didn't know about "Formula Evaluation".

Thanks a lot !

Stephane
Comment 3 srousseau 2013-07-09 09:28:35 UTC 
(In reply to srousseau from comment #2)
> (In reply to Nick Burch from comment #1)
> > After changing a formula, you must trigger a recalculation to ensure the
> > cached value is correctly stored/set in the file
> > 
> > See http://poi.apache.org/spreadsheet/eval.html for instructions on how to
> > do this
> 
> Great!!
>  It works with HSSFFormulaEvaluator.evaluateAllFormulaCells(myWorkBook).
> 
> I upgrade from POI 3.0 to POI 3.9 and I didn't need to call this code. So I
> didn't know about "Formula Evaluation".
> 
> Thanks a lot !
> 
> Stephane

Hello,

I dont't know what I've changed since last Tuesday but I doesn't work anymore. I now see "toto" as result.
I'm using HSSFFormulaEvaluator.evaluateAllFormulaCells(book.getWorkbook()); and book.getWorkbook().setForceFormulaRecalculation(true); but the cell value is not refreshed. But I can see the call to evaluateFormulaCell for each formula cell.

In excel, I have to click in the cell to refresh the cell value as in the beginning.

I run Excel 2010 with HSSFWorkbook.

So, if anybody has idea ...

Regards

Stéphane Rousseau