POI/HSSF Conditional Formula problem

[Iain Brown <ir...@pimsconsulting.com>]

I am trying to write an Excel workbook with 2 worksheets from a servlet using 
POI 2. The second sheet is linked to the first with conditional formulas – if 
a cell is filled they take the value from that cell, otherwise they take a 
default value from a different cell. Simplified code is shown below :

//response content type set to "application/vnd.ms-excel"
  void CreateTestSheet( ServletOutputStream out){
    HSSFWorkbook wb = new HSSFWorkbook( );

    HSSFSheet sheet = wb.createSheet("input");
    // input row 1- A1 has the input value I want to test
    HSSFRow row = sheet.createRow((short)0);
    HSSFCell cell = row.createCell((short)0);
    cell.setCellValue(1);
    // input row 2 - A2 has default value
    row = sheet.createRow((short)1);
    cell = row.createCell((short)0);
    cell.setCellValue(999);
    // output row 3
    // A1 setup with isnumber, A3 with <
    row = sheet.createRow(2);
    cell = row.createCell((short)0);
    cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cell.setCellFormula("IF(ISNUMBER(A1),A1,A2)");
    cell = row.createCell((short)2);
    cell.setCellFormula("if( a1<3,a1,A2)");

      try{
    wb.write(out);
      }
    catch (IOException e) {System.err.println("Error in write 
xl: "+e.getMessage()); }
   }

The workbook seems to display fine, and I can edit cells OK. However, the 
formula containing “isnumber(..)” show the contents of cell A2 regardless of 
what was initially in A1 (blank, number, or cell not actually created), 
initially 999. The formula in c3 behaves as you'd expect when A1 changes.

If I change A1 in Excel, A3 doesn't change, but C3 does.
If I change A2 both A3 and C3 change. 
If I click the equals sign & edit the formula in A3 it starts to work as 
expected.

This behaviour is the same even if I reference using the sheet name, or 
reference from another sheet.

Have I missed something out or is this a feature/bug?

Any help gratefully received

Regards

Iain



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