[Bug 63068] New: Unifying Cell.setCellFormula behavior

[bu...@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: dev@poi.apache.org
          Reporter: gallon.fizik@gmail.com
                CC: gallon.fizik@gmail.com
  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: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org

[Bug 63068] Unifying Cell.setCellFormula behavior

[bu...@apache.org]

https://bz.apache.org/bugzilla/show_bug.cgi?id=63068

gallon.fizik@gmail.com <ga...@gmail.com> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|NEW                         |RESOLVED
         Resolution|---                         |FIXED

--- Comment #1 from gallon.fizik@gmail.com <ga...@gmail.com> ---
implemented in r1852212:
* setCellFormula on a BLANK cell sets it value to 0
* setCellFormula on a non-BLANK preserves its value, also when it was a
single-cell array formula

I decided not to struggle with exception safety because within the usermodel
the create-swap idiom requires reading value and formula to be able to restore
them later.

Perhaps I will do it later on a lower level.

-- 
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