You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@poi.apache.org by bu...@apache.org on 2014/05/07 20:43:16 UTC
[Bug 56499] New: IFERROR fails when written to spreadsheet
https://issues.apache.org/bugzilla/show_bug.cgi?id=56499
Bug ID: 56499
Summary: IFERROR fails when written to spreadsheet
Product: POI
Version: 3.10
Hardware: PC
Status: NEW
Severity: normal
Priority: P2
Component: HSSF
Assignee: dev@poi.apache.org
Reporter: rick@wisefam.com
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.
--
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 56499] IFERROR fails when written to spreadsheet
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=56499
Rick <ri...@wisefam.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEEDINFO |NEW
--- Comment #7 from Rick <ri...@wisefam.com> ---
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.
--
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 56499] IFERROR fails when written to spreadsheet
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=56499
Rick <ri...@wisefam.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
CC| |rick@wisefam.com
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
[Bug 56499] IFERROR fails when written to spreadsheet
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=56499
--- Comment #5 from Rick <ri...@wisefam.com> ---
Created attachment 31610
--> https://issues.apache.org/bugzilla/attachment.cgi?id=31610&action=edit
Resulting spreadsheet
--
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 56499] IFERROR fails when written to spreadsheet
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=56499
--- Comment #3 from Nick Burch <ap...@gagravarr.org> ---
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
--
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 56499] IFERROR fails when written to spreadsheet
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=56499
--- Comment #2 from Rick <ri...@wisefam.com> ---
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().
--
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 56499] IFERROR fails when written to spreadsheet
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=56499
Nick Burch <ap...@gagravarr.org> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEW |NEEDINFO
--- Comment #1 from Nick Burch <ap...@gagravarr.org> ---
Are you re-evaluating the formula in POI before you write the file out?
--
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 56499] IFERROR fails when written to spreadsheet
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=56499
--- Comment #6 from Rick <ri...@wisefam.com> ---
Created attachment 31611
--> https://issues.apache.org/bugzilla/attachment.cgi?id=31611&action=edit
Example code
--
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 56499] IFERROR fails when written to spreadsheet
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=56499
--- Comment #4 from Rick <ri...@wisefam.com> ---
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.
--
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