1. setCellType() should be deprecated. Any value/format conversions should be
performed outside of the cell through its public interface.
While it's still available, we'll have to stick with conversions. In the
next list, for any previous type not specified explicitly, the default value
for the new type will be set.
1.0. In all cases except BLANK, (ignore formula and handle only values) OR
(remove formula)? I vote for ignoring the formula. We operate on values here,
not formula.
1.1. _NONE: throw IllegalArgumentException
1.2. BLANK: as is. Remove formula and value, keep style, comments etc.
Should evolve into a setBlank() method someday.
1.3. BOOLEAN. Default value: false.
1.3.1 Strings: equalsIgnoreCase("true") ? true : false
1.3.2. Numbers: getNumericValue() != 0
1.4. NUMERIC. Default value: 0.
1.4.1. Strings: Double.parseString, default value if fails.
1.4.2. Bools: getBooleanValue ? 1 : 0.
1.5. STRING. Default value: "" (empty string).
1.5.1. Bools: getBooleanValue ? "TRUE" : "FALSE" (defined as static
constants, at least in XSSF).
1.5.2. Numbers: Double.toString(getNumericValue()).
1.5. FORMULA. Throw IllegalArgumentException. Setting cell type to formula
makes no sense.
2. setCellFormula()
2.1. setCellFormula(!null). In our reference, Excel, it's seemingly
impossible to set a formula without Excel immediately evaluating it, even in
manual calculation mode.
I tried to tinker with unpacked XML. It's possible to set value type in
a formula cell to another type, and it's possible to remove the value (in this
case, Excel immediately converts it to 0 on load).
Therefore, I vote for keeping old value, if any. If the cell was blank,
set value to 0. State when formula is set and value is not should be illegal.
2.2. setCellFormula(null) shall keep the value by all means, that's what
Excel does.
A method removeFormula() should be added to Cell. Calling
setCellFormula with null shall be discouraged by throwing an
IllegalArgumentException telling user to call removeFormula().
3. getValueType() should be added as a transition. Implementation: return
getCellType() == FORMULA ? getCachedFormulaResultType() : getCellType().
Possible values will be BLANK, BOOLEAN, NUMERIC, STRING, ERROR.
4. get*Value should throw IllegalStateException if stored value type doesn't
match the method.
5. Usage of {byte getErrorCellValue()} and setErrorCellValue(byte) should be
discouraged in favor of using FormulaError.
Note: writing a cell with a CIRCULAR_REF to xlsx produces a corrupt file. I
don't have a good soultion for this. Scanning all cells for a circular ref and
handling it somehow is clumsy and costly.
6. setCellValue() should ignore the formula, if any, and simply write the
value, changing the underlying storage, if necessary. Previous value shall be
discarded.
That's it... Hope it helps us get a skinny yet complete interface and
consistent implementations.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
