[Bug 55747] New: nested function in IF's in formulas produce #VALUE! error in excel

[bu...@apache.org]

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: dev@poi.apache.org
          Reporter: Guoshun.Wu@alcatel-sbell.com.cn

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: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org

[Bug 55747] nested function in IF's in formulas produce #VALUE! error in excel

[bu...@apache.org]

https://issues.apache.org/bugzilla/show_bug.cgi?id=55747

Guoshun Wu <Gu...@alcatel-sbell.com.cn> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |Guoshun.Wu@alcatel-sbell.co
                   |                            |m.cn
                 OS|                            |All

-- 
You are receiving this mail because:
You are the assignee for the bug.

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