https://bz.apache.org/bugzilla/show_bug.cgi?id=63068
Bug ID: 63068
Summary: Unifying Cell.setCellFormula behavior
Product: POI
Version: 4.0.x-dev
Hardware: PC
OS: All
Status: NEW
Severity: enhancement
Priority: P2
Component: SS Common
Assignee: [email protected]
Reporter: [email protected]
CC: [email protected]
Target Milestone: ---
Motivation: in a simple testcase:
Cell cell = getInstance();
cell.setCellErrorValue(FormulaError.NUM.getCode()); // some non-trivial value
cell.setCellFormula("\"foo\"");
System.out.println(cell.getCellType());
System.out.println(cell.getCachedFormulaResultType());
all HSSF, XSSF, SXSSF return cellType == FORMULA, but the
getCachedFormulaResultType() returns:
HSSF: NUMERIC (value == 0)
XSSF: ERROR (value == NULL (code == 0))
SXSSF: NUMERIC (value == 0)
In Excel, when a formula is set, it is immediately evaluated, even if
calculation mode is set to manual. We won't mimic this behavior (although it's
theoretically possible but is costly an may have subtle side effects). Because
we don't evaluate the formula right away, we don't know the result type (which
is in general volatile for a fixed formula). So the old value may be seen as a
(stale) "cached" value, just like between calls to the evaluator.
So the choice is to set the value to a default value for the previous value
type or simply preserve the value. My take is to preserve the value, it's least
invasive and doesn't involve implicit value changes.
So, the proposed change:
* state this strategy in Cell's javadoc
* ensure this behavior by tests
* implement
Special case: if there already was a formula set, the behavior remains the
same.
Special case: when setting a formula on a blank cell, it's value type is
converted to numeric with value 0.
Special case: if the formula cannot be set for some reason (part of an array
group or parsing error), the value shall be preserved, as well as the previous
formula.
Special case: when a cell becomes a part of an array formula group, the value
shall be preserved.
Note: I am referring to a non-null valid formula. Setting formula to null
(effectively, removing a formula) logic may is different.
Did I miss something?
Comments are very welcome.
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
