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 2018/10/18 14:26:20 UTC
[Bug 62834] New: Regression: FormulaEvaluator.evaluateInCell()
throws exception
https://bz.apache.org/bugzilla/show_bug.cgi?id=62834
Bug ID: 62834
Summary: Regression: FormulaEvaluator.evaluateInCell() throws
exception
Product: POI
Version: 4.0.0-FINAL
Hardware: PC
Status: NEW
Severity: normal
Priority: P2
Component: XSSF
Assignee: dev@poi.apache.org
Reporter: maarten_coene@yahoo.com
Target Milestone: ---
Created attachment 36199
--> https://bz.apache.org/bugzilla/attachment.cgi?id=36199&action=edit
xslx file having a problem in the A3 cell
Hi,
we have an xlsx file for which the FormulaEvaluator.evaluateInCell() throws an
exception for one particular cell. This used to work with Apache POI v3.17 and
now fails after upgrading to v4.0.0.
The file is attached (problem.xlsx), the problematic cell is A3, the other
cells are ok. The issue can be reproduced with the following code:
Workbook wb = WorkbookFactory.create(new File(/path/to/problem.xlsx));
Sheet sheet = wb.getSheet("problem");
FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
evaluator.evaluateInCell(sheet.getRow(1).getCell(0)); // A2 -> OK
evaluator.evaluateInCell(sheet.getRow(2).getCell(0)); // A3 -> FAIL
The stacktrace is:
Exception in thread "main" java.lang.IllegalStateException: Cannot get a
FORMULA value from a STRING cell
at
org.apache.poi.xssf.usermodel.XSSFCell.typeMismatch(XSSFCell.java:1075)
at
org.apache.poi.xssf.usermodel.XSSFCell.getCellFormula(XSSFCell.java:483)
at
org.apache.poi.xssf.usermodel.XSSFCell.getCellFormula(XSSFCell.java:470)
at
org.apache.poi.xssf.usermodel.XSSFSheet.onDeleteFormula(XSSFSheet.java:4639)
at
org.apache.poi.xssf.usermodel.XSSFCell.setCellType(XSSFCell.java:969)
at
org.apache.poi.ss.formula.BaseFormulaEvaluator.setCellType(BaseFormulaEvaluator.java:218)
at
org.apache.poi.ss.formula.BaseFormulaEvaluator.evaluateInCell(BaseFormulaEvaluator.java:140)
at
org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateInCell(XSSFFormulaEvaluator.java:85)
at
org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateInCell(XSSFFormulaEvaluator.java:34)
kind regards,
Maarten Coene
--
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 62834] Regression: FormulaEvaluator.evaluateInCell() throws
exception
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=62834
--- Comment #3 from Greg Woolsey <gr...@gmail.com> ---
And finally, the problem is a strangely formed XLSX file - it has, in its
sheet1.xml, the following:
<row r="3" spans="1:1" x14ac:dyDescent="0.25">
<c r="A3" s="1" t="str">
<f t="shared" ref="A3:A5" si="0">A2</f>
<v>a value</v>
</c>
</row>
<row r="4" spans="1:1" x14ac:dyDescent="0.25">
<c r="A4" s="1" t="s">
<v>1</v>
</c>
</row>
<row r="5" spans="1:1" x14ac:dyDescent="0.25">
<c r="A5" s="1" t="str">
<f t="shared" si="0"/>
<v>another value</v>
</c>
</row>
Note how A3 defines itself as a shared formula covering A3:A5 with id=0. A5
references this properly, but A4 defines itself as a plain string cell, not a
formula.
Excel seems to handle this just fine, allowing cells to override a shared
formula definition. Looks like POI needs to follow that behavior.
I wonder where else this might cause trouble.
--
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 62834] Regression: FormulaEvaluator.evaluateInCell() throws
exception
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=62834
--- Comment #5 from Maarten Coene <ma...@yahoo.com> ---
Thanks for fixing this so fast! :-)
cheers,
Maarten
--
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 62834] Regression: FormulaEvaluator.evaluateInCell() throws
exception
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=62834
--- Comment #2 from Greg Woolsey <gr...@gmail.com> ---
Hmm. Along the way I noticed this call flow:
XSSFSheet.onDeleteFormula(XSSFCell)
calls XSSFCell.getCellFormula()
calls XSSFCell.getCellFormula(XSSFEvaluationWorkbook)
with a null parameter, so it creates a new one, but only when it needs to call
convertSharedFormula().
Looks like the next step is to see just how bad it will be to get the actual
evaluation object from the formula evaluator down into that context. I suspect
that's where things really go wrong, using a different evaluation instance than
the one in use higher in the call stack.
I'm not a fan of passing nulls, but since part of the code already expects that
possible input, getCellFormula(), I suppose it isn't too awful. Better ideas
welcome.
That's not the bug here, though, that's just something that will bite someone
down the road if it isn't handled. I'm implementing passing the needed value
from the FormulaEvaluator, and adding JavaDoc about invalidating evaluator
instances for other callers, without changing the public API for now.
--
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 62834] Regression: FormulaEvaluator.evaluateInCell() throws
exception
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=62834
Greg Woolsey <gr...@gmail.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
OS| |All
--- Comment #1 from Greg Woolsey <gr...@gmail.com> ---
Thanks for the simple test case! I've added it as a failing unit test to my
local build, and will commit it for regression testing once I figure out what's
going on.
One interesting point of note in the stack trace, the line in
XSSFSheet.onDeleteFormula(cell)
is only reached for shared formulas valid for more than one cell. I don't know
much about those (yet), but it seems plausible that calling evaluateInCell()
for the "master" formula cell somehow doesn't update the formula references
properly in this case.
That code comes from #58106, which was committed after 3.17 was released, so
that seems like a likely place to start looking. I'll keep digging, as I'm
likely to need this also once my work project can upgrade to 4.0.
--
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 62834] Regression: FormulaEvaluator.evaluateInCell() throws
exception
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=62834
Greg Woolsey <gr...@gmail.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEW |RESOLVED
Resolution|--- |FIXED
--- Comment #4 from Greg Woolsey <gr...@gmail.com> ---
Fixed in r1844295.
--
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