Bug 27405 - isnumber() formula always evaluates to false in if statement
Summary: isnumber() formula always evaluates to false in if statement
Status: RESOLVED FIXED
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 2.0-FINAL
Hardware: PC Windows XP
: P3 normal with 1 vote (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2004-03-03 11:39 UTC by Iain Brown
Modified: 2008-05-05 19:25 UTC (History)
0 users



Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Iain Brown 2004-03-03 11:39:06 UTC
isnumber(), isblank(), istext(), all evaluate correctly when used in cell 
formulas on their own. However if they are placed in if statements, ( if( 
isnumber(a1),a1,a2) for example) the result is always false (i.e. the second 
term).

This is still the case in version 2.5

The following code fragment illustrates the problem, creating sheet with 4 
cells, B1 & B2 initially set to 1 and 999, and B5 & B6 containing isnumber
(b1), and if(isnumber(b1), b1, b2) respectively.

B5 always reflects the state of b1, but b6 always shows the value in b2. 
Editing the formula in excel "fixes" the problem.

    HSSFWorkbook wb = new HSSFWorkbook( );
    HSSFSheet sheet = wb.createSheet("input");
    // input row 0
    HSSFRow row = sheet.createRow((short)0);
    HSSFCell cell=row.createCell((short)0);
    cell = row.createCell((short) 1);
    cell.setCellValue(1);
    // input row 1
    row = sheet.createRow((short)1);
    cell = row.createCell((short)1);
    cell.setCellValue(999);

    int rno = 4;
    row = sheet.createRow(rno);
    cell = row.createCell((short)1);
    cell.setCellFormula("isnumber(b1)");
    cell = row.createCell((short)3);
    cell.setCellFormula("IF(ISNUMBER(b1),b1,b2)");

    try{
      FileOutputStream fileOut = new FileOutputStream("c:\\workbook.xls");
      wb.write(fileOut);
      }
    catch (IOException e) {System.err.println("Error in write 
xl: "+e.getMessage()); }
Comment 1 Adam Mara 2004-06-04 15:25:27 UTC
I added the patch described in bug report 24925 and it fixed this problem.
Comment 2 Josh Micich 2008-05-05 19:25:54 UTC
Adam's fix (for bug 24925) probably did the trick.
Verified OK from 2.5.1 onwards.