poi-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From bugzi...@apache.org
Subject [Bug 62307] New: XSSFCell.getNumericCellValue() behaves differently when formula is set or not set
Date Tue, 17 Apr 2018 09:11:39 GMT
https://bz.apache.org/bugzilla/show_bug.cgi?id=62307

            Bug ID: 62307
           Summary: XSSFCell.getNumericCellValue() behaves differently
                    when formula is set or not set
           Product: POI
           Version: 4.0-dev
          Hardware: PC
            Status: NEW
          Severity: normal
          Priority: P2
         Component: XSSF
          Assignee: dev@poi.apache.org
          Reporter: gallon.fizik@gmail.com
  Target Milestone: ---

Javadoc for Cell.getNumericValue() says:
* For strings we throw an exception. For blank cells we return a 0.
* For formulas or error cells we return the precalculated value;
* </p>
* @return the value of the cell as a number
* @throws IllegalStateException if the cell type returned by {@link
#getCellType()} is {@link CellType#STRING}

E.g., javadoc doesn't specify a behavior for boolean cells.

@Override
public double getNumericCellValue() {
    CellType cellType = getCellType();
    switch(cellType) {
        case BLANK:
            return 0.0;
        case FORMULA:
            // fall-through
        case NUMERIC:
            if(_cell.isSetV()) {
               String v = _cell.getV();
               if (v.isEmpty()) {
                   return 0.0;
               }
               try {
                  return Double.parseDouble(v);
               } catch(NumberFormatException e) {
                  throw typeMismatch(CellType.NUMERIC, CellType.STRING, false);
               }
            } else {
               return 0.0;
            }
        default:
            throw typeMismatch(CellType.NUMERIC, cellType, false);
    }
}

Consequences:
1. BLANKs always return 0, regardless of the formula.
2. If formula is set, ANY value type is parsed as double, i.e. numerics,
booleans and errors. If the string value representation is not parsable as
double, NumberFormatException is thrown. 
3. If formula is not set, then string, boolean and error types result in
IllegalStateException.

Although this logic corresponds to javadoc (except for booleans), it seems
really weird that it behaves in such different ways depending on the formula.
If this is really intended behavior, then soory to bother you. Otherwise, I
propose a fix:

@Override
    public double getNumericCellValue() {
        CellType valueType = getCellType() == CellType.FORMULA ?
getCachedFormulaResultType() : getCellType();

        switch(valueType) {
            case BLANK:
                return 0.0;
            case STRING:
                throw typeMismatch(CellType.NUMERIC, CellType.STRING, false);
            case ERROR:
                // Errors are stored as strings, so we cannot parse them
directly.
                // We could return an error code, but it's confusing.
                throw typeMismatch(CellType.NUMERIC, CellType.ERROR, false);
            case BOOLEAN:
                // fall-through
            case NUMERIC:
                if(_cell.isSetV()) {
                    String v = _cell.getV();
                    if (v.isEmpty()) {
                        return 0.0;
                    }
                    // 'Cannot get NUMERIC from NUMERIC' seems weird. I propose
to replace it with
                    // IllegalStateException("Invalid string representation of
a numeric: " + v)
                    // Or let the exception propagate. Anyway, if the internal
value representation is broken, means 
                    // that something is really wrong in POI code.

                    return Double.parseDouble(v);

                } else {
                    return 0.0;
                }
            default: // accounts for _NONE and FORMULA
                throw new IllegalStateException("this should never happen");
        }
    }

I will be able to provide a set of test cases later, if necessary.

-- 
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org


Mime
View raw message