Parsing cell with formula obscure the current value type

[robmv]

I am importing data and don't really have an use for formulas, I need to validate the data type of a cell, and currently if the spreadsheet is using formulas, the calculated result type of it is lost. for example, the cell:

<c r="B3" s="1" t="n">
  <f aca="false">E3</f>
  <v>30</v>
</c>

If I am correct, the calculated type is NUMBER t="n" and the value is 30 <v>30</v>, and the formula is E3 (just an simple test). At:

fastexcel/fastexcel-reader/src/main/java/org/dhatim/fastexcel/reader/RowSpliterator.java

Line 194 in b6df0f7

CellType cellType = (formula != null) ? CellType.FORMULA : definedType;

and:

fastexcel/fastexcel-reader/src/main/java/org/dhatim/fastexcel/reader/RowSpliterator.java

Line 316 in b6df0f7

CellType cellType = formula == null ? CellType.STRING : CellType.FORMULA;

but not sure much about this one:

fastexcel/fastexcel-reader/src/main/java/org/dhatim/fastexcel/reader/RowSpliterator.java

Lines 337 to 338 in b6df0f7

	case "str":
	return CellType.FORMULA;

If the cell has a formula, the parsed Cell will have FORMULA as its type, the knowledge that the calculated result stored on the cell from that formula is of type NUMBER is lost.

Reading the number value fails because it expects to not have a formula:

fastexcel/fastexcel-reader/src/main/java/org/dhatim/fastexcel/reader/Cell.java

Lines 79 to 82 in b6df0f7

	public BigDecimal asNumber() {
	requireType(CellType.NUMBER);
	return (BigDecimal) value;
	}

I think CellType should not have an entry for FORMULA, it is not an exclusive thing, for example a cell can contain a value of type NUMBER but being calculated by a formula. Probably is better to have something like:

class Cell ... {
    ...
    public boolean isFormula() {
        return formula != null;
    }
    ...
}

This could be breaking change, Maybe if a non breaking change is needed, an option can be added to ignore formula definitions when parsing. Until a new major release fix this problem, removing FORMULA as an exclusive type of a cell.

I maybe wrong, but my usage of fastexcel-reader is to import data generated by users, and if I lose the data types of the data because of the formulas, I can't import it not being sure there are numbers on one column or strings that resemble numbers.

[robmv]

I found a workaround. Instead of trusting the value of getType() just use getValue() and check if it is an instance of BigDecimal or String. If a date is needed, if it is a BigDecimal use asDate().

It is interesting that asDate() is the only one of the value converter functions that checks for type == CellType.NUMBER || type == CellType.FORMULA.

As I said on the original text of the issue, IMHO if CellType.FORMULA goes away, the diference of asNumber() requiring only NUMBER and asDate() accepting both NUMBER and FORMULA goes away.