https://issues.apache.org/bugzilla/show_bug.cgi?id=55747
Bug ID: 55747
Summary: nested function in IF's in formulas produce #VALUE!
error in excel
Product: POI
Version: 3.9
Hardware: PC
Status: NEW
Severity: normal
Priority: P2
Component: HSSF
Assignee: [email protected]
Reporter: [email protected]
Created attachment 31015
--> https://issues.apache.org/bugzilla/attachment.cgi?id=31015&action=edit
The code in description produce this file.
The formula document on the HSSF website instructed to report all occurrences
of #VALUE! when
attempting to programmatically assign a formula to a cell, so here goes:
IF(A1 > 5, "big number", "small number")
This formula works, and displays correctly when opened in Excel.
#VALUE! is only displayed if the outer IF branches to an inner CONCATENATE and
it reference another cell. For example:
> =IF(ISBLANK(A1)," not blank a1",CONCATENATE(A1," - %s."))
-The cell displays #VALUE!
-but if I type it in directly in Excel, the formula works
-also, clicking in the formula bar of POI-generated formula, then hitting
Enter, fixes the formula
Here iis the sample code(in groovy):
==============================Code Start=================================
HSSFWorkbook wb = new HSSFWorkbook()
String ext ="xls";
if(wb instanceof SXSSFWorkbook) ext +="x";
Sheet sheet =wb.createSheet("Test1")
Row row =sheet.createRow(0)
CellUtil.createCell(row, 0, "Hello world.")
row = sheet.createRow(1)
Cell cell = row.createCell(0)
cell.setCellType(Cell.CELL_TYPE_FORMULA)
String refCell = "A1"
String formula = refCell
formula = String.format "IF(isblank(%s),\" not blank a1\",CONCATENATE(%s, \" -
%%s.\"))", refCell, refCell
cell.setCellFormula(formula)
wb.forceFormulaRecalculation = true
wb.write new FileOutputStream( "d:/test/test.${ext}")
==============================Code End=================================
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
