Bug 63068

Summary: Unifying Cell.setCellFormula behavior
Product: POI Reporter: gallon.fizik <gallon.fizik>
Component: SS CommonAssignee: POI Developers List <dev>
Severity: enhancement CC: gallon.fizik
Priority: P2    
Version: 4.0.x-dev   
Target Milestone: ---   
Hardware: PC   
OS: All   

Description gallon.fizik@gmail.com 2019-01-07 22:01:30 UTC
Motivation: in a simple testcase:
Cell cell = getInstance();
cell.setCellErrorValue(FormulaError.NUM.getCode()); // some non-trivial value


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.
Comment 1 gallon.fizik@gmail.com 2019-01-26 10:14:20 UTC
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.