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.msexcel"
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, email: poiuserunsubscribe@jakarta.apache.org
For additional commands, email: poiuserhelp@jakarta.apache.org
