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.
Are you re-evaluating the formula in POI before you write the file out?
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().
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
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.
Created attachment 31610 [details] Resulting spreadsheet
Created attachment 31611 [details] Example code
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.
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: [EXTERNALNAME] .options = 0 .ix = 0 .name = IFERROR org.apache.poi.ss.formula.ptg.ErrPtg [#REF!]. [/EXTERNALNAME] * When the file is edited and saved in Excel it is saved as follows: [NAME] .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 = [/NAME] * The external name is currently built in InternalWorkbook.getNameXPtg()
Created attachment 33560 [details] Testcase to produce the incorrect file