[Bug 63068] New: Unifying Cell.setCellFormula behavior

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

[Bug 63068] New: Unifying Cell.setCellFormula behavior

Bugzilla from bugzilla@apache.org
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: [hidden email]
          Reporter: [hidden email]
                CC: [hidden email]
  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: [hidden email]
For additional commands, e-mail: [hidden email]