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 2025/04/14 04:22:16 UTC
[Bug 69650] New: Throw Exception java.lang.IllegalStateException: value changed in this methos ss.formula.EvaluationCache.getPlainValueEntry
https://bz.apache.org/bugzilla/show_bug.cgi?id=69650
Bug ID: 69650
Summary: Throw Exception java.lang.IllegalStateException: value
changed in this methos
ss.formula.EvaluationCache.getPlainValueEntry
Product: POI
Version: unspecified
Hardware: PC
Status: NEW
Severity: normal
Priority: P2
Component: XSSF
Assignee: dev@poi.apache.org
Reporter: hasmukh.ginoya@automationanywhere.com
Target Milestone: ---
My Excel file having
A1 -> Cell Value ( 1)
A2 -> Cell Value (2)
A3 -> Cell Value (3)
A4 -> =SUM(A1:A3)
I have create my logic like it reads the Value of Row A1 to A4 and also do set
cell Value like
Loop i to 1 to 4
Read Ai
Set Ai
When Loop executed at A4 then it throws exception
Caused by: java.lang.IllegalStateException: value changed
at
org.apache.poi.ss.formula.EvaluationCache.getPlainValueEntry(EvaluationCache.java:135)
~[?:?]
at
org.apache.poi.ss.formula.EvaluationTracker.acceptPlainValueDependency(EvaluationTracker.java:143)
~[?:?]
at
org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:242)
~[?:?]
at
org.apache.poi.ss.formula.WorkbookEvaluator.evaluateReference(WorkbookEvaluator.java:791)
~[?:?]
at
org.apache.poi.ss.formula.SheetRefEvaluator.getEvalForCell(SheetRefEvaluator.java:48)
~[?:?]
at
org.apache.poi.ss.formula.SheetRangeEvaluator.getEvalForCell(SheetRangeEvaluator.java:77)
~[?:?]
at
org.apache.poi.ss.formula.LazyAreaEval.getRelativeValue(LazyAreaEval.java:54)
~[?:?]
at
org.apache.poi.ss.formula.eval.AreaEvalBase.getValue(AreaEvalBase.java:143)
~[?:?]
at
org.apache.poi.ss.formula.functions.MultiOperandNumericFunction.collectValues(MultiOperandNumericFunction.java:149)
~[?:?]
at
org.apache.poi.ss.formula.functions.MultiOperandNumericFunction.getNumberArray(MultiOperandNumericFunction.java:118)
~[?:?]
at
org.apache.poi.ss.formula.functions.MultiOperandNumericFunction.evaluate(MultiOperandNumericFunction.java:81)
~[?:?]
at
org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:133)
~[?:?]
at
org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:537)
~[?:?]
at
org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:264)
~[?:?]
at
org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:205)
~[?:?]
at
org.apache.poi.xssf.usermodel.BaseXSSFFormulaEvaluator.evaluateFormulaCellValue(BaseXSSFFormulaEvaluator.java:61)
~[?:?]
at
org.apache.poi.ss.formula.BaseFormulaEvaluator.evaluateFormulaCell(BaseFormulaEvaluator.java:184)
~[?:?]
at
org.apache.poi.ss.usermodel.DataFormatter.formatCellValue(DataFormatter.java:1120)
~[?:?]
As I was Searching , then in POI document mentioned that
For performance reasons, this class keeps a cache of all previously calculated
intermediate cell values. Be sure to call clearAllCachedResultValues() if any
workbook cells are changed between calls to evaluate~ methods on this class.
So Every After Set cell I have call clearAllCachedResultValues() ,
Can you please suggest weather this is right solution or not in such case ?
--
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 69650] Throw Exception java.lang.IllegalStateException: value changed in this methos ss.formula.EvaluationCache.getPlainValueEntry
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=69650
--- Comment #3 from hasmukh ginoya <ha...@automationanywhere.com> ---
Actually the when Set cell happened that is the part of the Cell Formula and
then when we et the value of the formula cell at that time this exception
happend.
--
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 69650] Throw Exception java.lang.IllegalStateException: value changed in this methos ss.formula.EvaluationCache.getPlainValueEntry
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=69650
PJ Fanning <fa...@yahoo.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Resolution|--- |FIXED
Status|NEEDINFO |RESOLVED
--- Comment #5 from PJ Fanning <fa...@yahoo.com> ---
https://github.com/apache/poi/pull/1317
--
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 69650] Throw Exception java.lang.IllegalStateException: value changed in this methos ss.formula.EvaluationCache.getPlainValueEntry
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=69650
Dominik Stadler <do...@gmx.at> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEW |NEEDINFO
--- Comment #1 from Dominik Stadler <do...@gmx.at> ---
Can you post some self-sufficient sample code which allows to reproduce this?
There is information about formula evaluation at
https://poi.apache.org/components/spreadsheet/eval.html maybe some of it helps
to make it work?
--
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 69650] Throw Exception java.lang.IllegalStateException: value changed in this methos ss.formula.EvaluationCache.getPlainValueEntry
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=69650
--- Comment #2 from hasmukh ginoya <ha...@automationanywhere.com> ---
public String getCellValue() {
DataFormatter formatter = new DataFormatter();
String formattedCellValue = formatter.formatCellValue(cell, evaluator,
cfEvaluator);
CellType cellType = cell.getCellType();
if (CellType.NUMERIC.equals(cellType) &&
DateUtil.isCellDateFormatted(cell, cfEvaluator)) {
// get the index of the data format. Built in formats are defined
at BuiltinFormats.
if (cell.getCellStyle().getDataFormat() ==
BUILT_IN_DATE_FORMAT_INDEX) {
Date date = cell.getDateCellValue();
formattedCellValue = new
CellDateFormatter(DEFAULT_DATE_FORMAT).format(date);
}
formattedCellValue = formattedCellValue.replaceAll("\"",
StringUtils.EMPTY);
}
return formattedCellValue;
}
So when this call formatter.formatCellValue(cell, evaluator, cfEvaluator);
execute after set cell then it throws the exception Caused by:
java.lang.IllegalStateException: value changed
at
org.apache.poi.ss.formula.EvaluationCache.getPlainValueEntry(EvaluationCache.java:135)
~[?:?]
--
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 69650] Throw Exception java.lang.IllegalStateException: value changed in this methos ss.formula.EvaluationCache.getPlainValueEntry
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=69650
Matafagafo <ma...@gmail.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
CC| |matafagafo@gmail.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 69650] Throw Exception java.lang.IllegalStateException: value changed in this methos ss.formula.EvaluationCache.getPlainValueEntry
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=69650
Dominik Stadler <do...@gmx.at> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEW |NEEDINFO
--- Comment #4 from Dominik Stadler <do...@gmx.at> ---
Unfortunately your code-snippets do not allow to reproduce this. All the code
which populates the Workbook is missing, so lots of guesswork would be needed
to come up with how you actually populated the cells before.
Please try to provide a self-sufficient and fully running standalone
code-example.
And did you try to re-evaluate formulas as described in the page before access
the data?
--
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 69650] Throw Exception java.lang.IllegalStateException: value changed in this methos ss.formula.EvaluationCache.getPlainValueEntry
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=69650
hasmukh ginoya <ha...@automationanywhere.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEEDINFO |NEW
--
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