| Summary: | Unifying Cell.setCellFormula behavior | ||
|---|---|---|---|
| Product: | POI | Reporter: | gallon.fizik <gallon.fizik> |
| Component: | SS Common | Assignee: | POI Developers List <dev> |
| Status: | RESOLVED FIXED | ||
| Severity: | enhancement | CC: | gallon.fizik |
| Priority: | P2 | ||
| Version: | 4.0.x-dev | ||
| Target Milestone: | --- | ||
| Hardware: | PC | ||
| OS: | All | ||
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. |
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.