Bug 56499 - IFERROR fails when written to spreadsheet
Summary: IFERROR fails when written to spreadsheet
Status: NEW
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.10-FINAL
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2014-05-07 18:43 UTC by Rick
Modified: 2016-02-15 20:53 UTC (History)
1 user (show)

Resulting spreadsheet (4.00 KB, application/vnd.ms-excel)
2014-05-12 17:22 UTC, Rick
Example code (1.34 KB, text/plain)
2014-05-12 17:23 UTC, Rick
Testcase to produce the incorrect file (2.47 KB, text/plain)
2016-02-15 20:53 UTC, Dominik Stadler

Note You need to log in before you can comment on or make changes to this bug.
Description Rick 2014-05-07 18:43:16 UTC
I'm building a spreadsheet and adding a formula to a cell.

If I do: new_desc_cell.setCellFormula("IF(ISERROR(VLOOKUP(A5,Desc!$a$4:$c$60,2,false)),\"\",VLOOKUP(A5,Desc!$a$4:$c$60,2,false))");
it works.

If I do: new_desc_cell.setCellFormula("IFERROR(VLOOKUP(A5,Desc!$a$4:$c$60,2,false),\"\")");
it appears to work, but when I open the spreadsheet in Excel 2010, I get a #NAME? error.  If I do a global replace of "IFERROR" with "IFERROR" (same text), the problem goes away.

I can use the first as a workaround, of course.
Comment 1 Nick Burch 2014-05-12 09:56:18 UTC
Are you re-evaluating the formula in POI before you write the file out?
Comment 2 Rick 2014-05-12 12:53:13 UTC
I have the following code:

  Cell	new_desc_cell = row.createCell(desc_col, Cell.CELL_TYPE_FORMULA);

  String	desc_lookup = "VLOOKUP(" + colnumToName(key_col) +
    (row.getRowNum() + 1) + ",Desc!$a$2:$c$"+ desc_row_count + ",2,false)";

  new_desc_cell.setCellFormula("IF(ISERROR(" + desc_lookup + "),\"\","
    + desc_lookup + ")");

The formula is at least parsing, because it throws an error if the formula has an error.  I'm not explicitly evaluating the formula.  As you can see, I worked around the problem by using IF(ISERROR()) in place of IFERROR().
Comment 3 Nick Burch 2014-05-12 13:14:55 UTC
A few things to try:
 * Write the iferror formula, save, load, see if POI sees the formula string correctly
 * Evaluate, see it it works
 * Evaluate, save, see if Excel is then happy
Comment 4 Rick 2014-05-12 16:54:34 UTC
1. Wrote the formula, reloaded it.  Got back:

Formula: IFERROR(VLOOKUP(A6,Desc!$A$2:$C$42,2,FALSE),"")
Value: Use default names - New list of Store names provided in operations

The values are correct.

2. Evaluated before write.  Got an evaluator: 
FormulaEvaluator	evaluator = row.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();

After setting cell formula, called evaluator.evaluate() for each cell.

No change to behavior.

3. After the evaluation (in step 2), wrote the spreadsheet, no change to behavior.
Comment 5 Rick 2014-05-12 17:22:30 UTC
Created attachment 31610 [details]
Resulting spreadsheet
Comment 6 Rick 2014-05-12 17:23:23 UTC
Created attachment 31611 [details]
Example code
Comment 7 Rick 2014-05-12 17:25:22 UTC
Included code that demonstrates the problem.  Running it will create an Excel workbook.  If you open it in Excel, formula in A3 is fine, but formula in A2 is broken.  If you edit the cell, and replace a couple of characters with the same characters, the cell works.
Comment 8 Dominik Stadler 2016-02-15 20:46:32 UTC
Some analysis:
* IFERROR is not part of the default set of Excel functions, but is marked as "external" in the parsed formula:
    Ptg[7]=org.apache.poi.ss.formula.ptg.FuncVarPtg [#external# nArgs=3]V

* The externalname is added by POI as follows:
    .options      = 0
    .ix      = 0
    .name    = IFERROR
org.apache.poi.ss.formula.ptg.ErrPtg [#REF!].

* When the file is edited and saved in Excel it is saved as follows:
    .option flags           = 0x038B
    .keyboard shortcut      = 0x00
    .length of the name     = 13
    .extSheetIx(1-based, 0=Global)= 0
    .sheetTabIx             = 0
    .Menu text length       = 0
    .Description text length= 0
    .Help topic text length = 0
    .Status bar text length = 0
    .NameIsMultibyte        = false
    .Name (Unicode text)    = _xlfn.IFERROR
    .Formula (nTokens=1):
       org.apache.poi.ss.formula.ptg.ErrPtg [#NAME?].
    .Menu text       = 
    .Description text= 
    .Help topic text = 
    .Status bar text = 

* The external name is currently built in InternalWorkbook.getNameXPtg()
Comment 9 Dominik Stadler 2016-02-15 20:53:43 UTC
Created attachment 33560 [details]
Testcase to produce the incorrect file