org.apache.poi.ss.usermodel.FormulaError Java Examples

public static ErrorConstant valueOf(int errorCode) {
    if (FormulaError.isValidCode(errorCode)) {
   		switch (FormulaError.forInt(errorCode)) {
   			case NULL:  return NULL;
   			case DIV0:  return DIV_0;
   			case VALUE: return VALUE;
   			case REF:   return REF;
   			case NAME:  return NAME;
   			case NUM:   return NUM;
   			case NA:	return NA;
   			default:    break;
   		}
    }
	logger.log( POILogger.WARN, "Warning - unexpected error code (" + errorCode + ")");
	return new ErrorConstant(errorCode);
}
 

/**
 * set the error value for the cell
 *
 * @param value     error representing the error value
 *                  this value can only be 0,7,15,23,29,36 or 42
 *                  see bugzilla bug 16560 for an explanation
 */
public void setValue(FormulaError value) {
	switch(value) {
		case NULL:
		case DIV0:
		case VALUE:
		case REF:
		case NAME:
		case NUM:
		case NA:
			_value = value.getCode();
			_isError = true;
			return;
		default:
	        throw new IllegalArgumentException("Error Value can only be 0,7,15,23,29,36 or 42. It cannot be "+value.getCode()+" ("+value+")");
	}
}
 

/**
 * set a error value for the cell
 *
 * @param error the error value to set this cell to.  For formulas we'll set the
 *        precalculated value , for errors we'll set
 *        its value. For other types we will change the cell to an error
 *        cell and set its value.
 */
@SuppressWarnings("fallthrough")
public void setCellErrorValue(FormulaError error) {
    int row=_record.getRow();
    short col=_record.getColumn();
    short styleIndex=_record.getXFIndex();
    switch (_cellType) {
        default:
            setCellType(CellType.ERROR, false, row, col, styleIndex);
            // fall through
        case ERROR:
            (( BoolErrRecord ) _record).setValue(error);
            break;
        case FORMULA:
            ((FormulaRecordAggregate)_record).setCachedErrorResult(error.getCode());
            break;
    }
}
 

@Override
protected void appendValueText(StringBuilder sb) {
	if (isBoolean()) {
		sb.append("  .boolVal = ");
		sb.append(getBooleanValue());
	} else {
		sb.append("  .errCode = ");
		sb.append(FormulaError.forInt(getErrorValue()).getString());
		sb.append(" (").append(HexDump.byteToHex(getErrorValue())).append(")");
	}
}
 

/**
 * Translates an Excel internal error code into the corresponding POI ErrorEval instance
 * @param errorCode An error code listed in {@link FormulaError}
 * @throws RuntimeException If an unknown errorCode is specified
 */
public static ErrorEval valueOf(int errorCode) {
    FormulaError error = FormulaError.forInt(errorCode);
    ErrorEval eval = evals.get(error);
    if (eval != null) {
        return eval;
    } else {
        throw new RuntimeException("Unhandled error type for code " + errorCode);
    }
}
 

/**
 * Converts error codes to text.  Handles non-standard error codes OK.  
 * For debug/test purposes (and for formatting error messages).
 * @return the String representation of the specified Excel error code.
 */
public static String getText(int errorCode) {
    if(FormulaError.isValidCode(errorCode)) {
        return FormulaError.forInt(errorCode).getString();
    }
    // Give a special string, based on ~, to make clear this isn't a standard Excel error
    return "~non~std~err(" + errorCode + ")~";
}
 

public String toString() {
    StringBuffer sb = new StringBuffer();
    sb.append(getClass().getName());
    sb.append(" [");
    if (externalWorkbookNumber >= 0) {
        sb.append(" [");
        sb.append("workbook=").append(getExternalWorkbookNumber());
        sb.append("] ");
    }
    sb.append("sheet=").append(getSheetName());
    sb.append(" ! ");
    sb.append(FormulaError.REF.getString());
    sb.append("]");
    return sb.toString();
}
 

public String toFormulaString() {
    StringBuffer sb = new StringBuffer();
    if (externalWorkbookNumber >= 0) {
        sb.append('[');
        sb.append(externalWorkbookNumber);
        sb.append(']');
    }
    if (sheetName != null) {
        SheetNameFormatter.appendFormat(sb, sheetName);
    }
    sb.append('!');
    sb.append(FormulaError.REF.getString());
    return sb.toString();
}
 

private String convertCellValueToString() {

        switch (_cellType) {
            case BLANK:
                return "";
            case BOOLEAN:
                return ((BoolErrRecord) _record).getBooleanValue() ? "TRUE" : "FALSE";
            case STRING:
                int sstIndex = ((LabelSSTRecord)_record).getSSTIndex();
                return _book.getWorkbook().getSSTString(sstIndex).getString();
            case NUMERIC:
                return NumberToTextConverter.toText(((NumberRecord)_record).getValue());
            case ERROR:
                   return FormulaError.forInt(((BoolErrRecord)_record).getErrorValue()).getString();
            case FORMULA:
                // should really evaluate, but HSSFCell can't call HSSFFormulaEvaluator
                // just use cached formula result instead
                break;
            default:
                throw new IllegalStateException("Unexpected cell type (" + _cellType + ")");
        }
        FormulaRecordAggregate fra = ((FormulaRecordAggregate)_record);
        FormulaRecord fr = fra.getFormulaRecord();
        switch (CellType.forInt(fr.getCachedResultType())) {
            case BOOLEAN:
                return fr.getCachedBooleanValue() ? "TRUE" : "FALSE";
            case STRING:
                return fra.getStringValue();
            case NUMERIC:
                return NumberToTextConverter.toText(fr.getValue());
            case ERROR:
                return FormulaError.forInt(fr.getCachedErrorValue()).getString();
            default:
                throw new IllegalStateException("Unexpected formula result type (" + _cellType + ")");
        }
        
    }
 

/** Creates new ErrPtg */

    private ErrPtg(int errorCode) {
        if(!FormulaError.isValidCode(errorCode)) {
            throw new IllegalArgumentException("Invalid error code (" + errorCode + ")");
        }
        field_1_error_code = errorCode;
    }
 

/**
     * <p>
     * Returns the formatted value of a cell as a <tt>String</tt> regardless of the cell type. If the Excel number
     * format pattern cannot be parsed then the cell value will be formatted using a default format.
     * </p>
     * <p>
     * When passed a null or blank cell, this method will return an empty String (""). Formula cells will be evaluated
     * using the given {@link FormulaEvaluator} if the evaluator is non-null. If the evaluator is null, then the formula
     * String will be returned. The caller is responsible for setting the currentRow on the evaluator
     * </p>
     * <p>
     * When a ConditionalFormattingEvaluator is present, it is checked first to see if there is a number format to
     * apply. If multiple rules apply, the last one is used. If no ConditionalFormattingEvaluator is present, no rules
     * apply, or the applied rules do not define a format, the cell's style format is used.
     * </p>
     * <p>
     * The two evaluators should be from the same context, to avoid inconsistencies in cached values.
     * </p>
     *
     * @param cell
     *            The cell (can be null)
     * @param evaluator
     *            The FormulaEvaluator (can be null)
     * @param cfEvaluator
     *            ConditionalFormattingEvaluator (can be null)
     * @return a string value of the cell
     */
    public String formatCellValue(Cell cell, FormulaEvaluator evaluator, ConditionalFormattingEvaluator cfEvaluator) {
        localeChangedObservable.checkForLocaleChange();

        if (cell == null) {
            return "";
        }

        CellType cellType = cell.getCellTypeEnum();
        if (cellType == CellType.FORMULA) {
            if (evaluator == null) {
                return cell.getCellFormula();
            }
            cellType = evaluator.evaluateFormulaCellEnum(cell);
        }
        switch (cellType) {
            case NUMERIC:

//                if (DateUtil.isCellDateFormatted(cell, cfEvaluator)) {
                    return getFormattedDateString(cell, cfEvaluator);
//                }
//                return getFormattedNumberString(cell, cfEvaluator);

            case STRING:
                return cell.getRichStringCellValue().getString();

            case BOOLEAN:
                return cell.getBooleanCellValue() ? "TRUE" : "FALSE";
            case BLANK:
                return "";
            case ERROR:
                return FormulaError.forInt(cell.getErrorCellValue()).getString();
            default:
                throw new RuntimeException("Unexpected celltype (" + cellType + ")");
        }
    }
 

public static ErrPtg valueOf(int code) {
    switch(FormulaError.forInt(code)) {
        case DIV0: return DIV_ZERO;
        case NA: return N_A;
        case NAME: return NAME_INVALID;
        case NULL: return NULL_INTERSECTION;
        case NUM: return NUM_ERROR;
        case REF: return REF_INVALID;
        case VALUE: return VALUE_INVALID;
        default:
            throw new RuntimeException("Unexpected error code (" + code + ")");
    }
}
 

/**
 * set a numeric value for the cell
 *
 * @param value  the numeric value to set this cell to.  For formulas we'll set the
 *        precalculated value, for numerics we'll set its value. For other types we
 *        will change the cell to a numeric cell and set its value.
 */
@SuppressWarnings("fallthrough")
@Override
public void setCellValue(double value) {
    if(Double.isInfinite(value)) {
        // Excel does not support positive/negative infinities,
        // rather, it gives a #DIV/0! error in these cases.
        setCellErrorValue(FormulaError.DIV0.getCode());
    } else if (Double.isNaN(value)){
        // Excel does not support Not-a-Number (NaN),
        // instead it immediately generates a #NUM! error.
        setCellErrorValue(FormulaError.NUM.getCode());
    } else {
        int row=_record.getRow();
        short col=_record.getColumn();
        short styleIndex=_record.getXFIndex();

        switch (_cellType) {
            default:
                setCellType(CellType.NUMERIC, false, row, col, styleIndex);
                // fall through
            case NUMERIC:
                (( NumberRecord ) _record).setValue(value);
                break;
            case FORMULA:
                ((FormulaRecordAggregate)_record).setCachedDoubleResult(value);
                break;
        }
    }

}
 

private int translateErrorCodeToErrorTypeValue(int errorCode) {
	switch (FormulaError.forInt(errorCode)) {
		case NULL:  return 1;
		case DIV0:  return 2;
		case VALUE: return 3;
		case REF:   return 4;
		case NAME:  return 5;
		case NUM:   return 6;
		case NA:    return 7;
		default:
	        throw new IllegalArgumentException("Invalid error code (" + errorCode + ")");
	}
}
 

public String toFormulaString() {
    return FormulaError.forInt(field_1_error_code).getString();
}
 

public void setCachedErrorResult(FormulaError error) {
	setCachedErrorResult(error.getCode());
}
 

@Override
protected String getValueText() {
    return FormulaError.forInt(_value).getString();
}
 

public String toFormulaString() {
    return FormulaError.REF.getString();
}
 

public String toFormulaString(FormulaRenderingWorkbook book) {
	return ExternSheetNameResolver.prependSheetName(book, field_1_index_extern_sheet, FormulaError.REF.getString());
}
 

public String toFormulaString(FormulaRenderingWorkbook book) {
	return ExternSheetNameResolver.prependSheetName(book, field_1_index_extern_sheet, FormulaError.REF.getString());
}
 

public String toFormulaString() {
	return FormulaError.REF.getString();
}
 

private ErrorEval(FormulaError error) {
    _error = error;
    evals.put(error, this);
}
 

public String getText() {
	if(FormulaError.isValidCode(_errorCode)) {
		return FormulaError.forInt(_errorCode).getString();
	}
	return "unknown error code (" + _errorCode + ")";
}
 

/**
 * set a error value for the cell
 *
 * @param errorCode the error value to set this cell to.  For formulas we'll set the
 *        precalculated value , for errors we'll set
 *        its value. For other types we will change the cell to an error
 *        cell and set its value.
 *        For error code byte, see {@link FormulaError}.
 * @deprecated 3.15 beta 2. Use {@link #setCellErrorValue(FormulaError)} instead.
 */
public void setCellErrorValue(byte errorCode) {
    FormulaError error = FormulaError.forInt(errorCode);
    setCellErrorValue(error);
}
 

/**
 * set the error value for the cell. See {@link FormulaError} for valid codes.
 *
 * @param value     error representing the error value
 *                  this value can only be 0,7,15,23,29,36 or 42
 *                  see bugzilla bug 16560 for an explanation
 */
public void setValue(byte value) {
	setValue(FormulaError.forInt(value));
}