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: dev-unsubscr...@poi.apache.org For additional commands, e-mail: dev-h...@poi.apache.org