Search in sources :

Example 1 with EvaluationException

use of org.apache.poi.ss.formula.eval.EvaluationException in project poi by apache.

the class CalculateMortgageFunction method evaluate.

@Override
public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) {
    // verify that we have enough data
    if (args.length != 3) {
        return ErrorEval.VALUE_INVALID;
    }
    // declare doubles for values
    double principal, rate, years, result;
    try {
        // extract values as ValueEval
        ValueEval v1 = OperandResolver.getSingleValue(args[0], ec.getRowIndex(), ec.getColumnIndex());
        ValueEval v2 = OperandResolver.getSingleValue(args[1], ec.getRowIndex(), ec.getColumnIndex());
        ValueEval v3 = OperandResolver.getSingleValue(args[2], ec.getRowIndex(), ec.getColumnIndex());
        // get data as doubles
        principal = OperandResolver.coerceValueToDouble(v1);
        rate = OperandResolver.coerceValueToDouble(v2);
        years = OperandResolver.coerceValueToDouble(v3);
        result = calculateMortgagePayment(principal, rate, years);
        System.out.println("Result = " + result);
        checkValue(result);
    } catch (EvaluationException e) {
        return e.getErrorEval();
    }
    return new NumberEval(result);
}
Also used : ValueEval(org.apache.poi.ss.formula.eval.ValueEval) EvaluationException(org.apache.poi.ss.formula.eval.EvaluationException) NumberEval(org.apache.poi.ss.formula.eval.NumberEval)

Example 2 with EvaluationException

use of org.apache.poi.ss.formula.eval.EvaluationException in project poi by apache.

the class Complex method evaluate.

public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval real_num, ValueEval i_num, ValueEval suffix) {
    ValueEval veText1;
    try {
        veText1 = OperandResolver.getSingleValue(real_num, srcRowIndex, srcColumnIndex);
    } catch (EvaluationException e) {
        return e.getErrorEval();
    }
    double realNum = 0;
    try {
        realNum = OperandResolver.coerceValueToDouble(veText1);
    } catch (EvaluationException e) {
        return ErrorEval.VALUE_INVALID;
    }
    ValueEval veINum;
    try {
        veINum = OperandResolver.getSingleValue(i_num, srcRowIndex, srcColumnIndex);
    } catch (EvaluationException e) {
        return e.getErrorEval();
    }
    double realINum = 0;
    try {
        realINum = OperandResolver.coerceValueToDouble(veINum);
    } catch (EvaluationException e) {
        return ErrorEval.VALUE_INVALID;
    }
    String suffixValue = OperandResolver.coerceValueToString(suffix);
    if (suffixValue.length() == 0) {
        suffixValue = DEFAULT_SUFFIX;
    }
    if (suffixValue.equals(DEFAULT_SUFFIX.toUpperCase(Locale.ROOT)) || suffixValue.equals(SUPPORTED_SUFFIX.toUpperCase(Locale.ROOT))) {
        return ErrorEval.VALUE_INVALID;
    }
    if (!(suffixValue.equals(DEFAULT_SUFFIX) || suffixValue.equals(SUPPORTED_SUFFIX))) {
        return ErrorEval.VALUE_INVALID;
    }
    StringBuffer strb = new StringBuffer("");
    if (realNum != 0) {
        if (isDoubleAnInt(realNum)) {
            strb.append((int) realNum);
        } else {
            strb.append(realNum);
        }
    }
    if (realINum != 0) {
        if (strb.length() != 0) {
            if (realINum > 0) {
                strb.append("+");
            }
        }
        if (realINum != 1 && realINum != -1) {
            if (isDoubleAnInt(realINum)) {
                strb.append((int) realINum);
            } else {
                strb.append(realINum);
            }
        }
        strb.append(suffixValue);
    }
    return new StringEval(strb.toString());
}
Also used : ValueEval(org.apache.poi.ss.formula.eval.ValueEval) StringEval(org.apache.poi.ss.formula.eval.StringEval) EvaluationException(org.apache.poi.ss.formula.eval.EvaluationException)

Example 3 with EvaluationException

use of org.apache.poi.ss.formula.eval.EvaluationException in project poi by apache.

the class DStarRunner method fullfillsConditions.

/**
     * Checks a row in a database against a condition database.
     *
     * @param db Database.
     * @param row The row in the database to check.
     * @param cdb The condition database to use for checking.
     * @return Whether the row matches the conditions.
     * @throws EvaluationException If references could not be resolved or comparison
     * operators and operands didn't match.
     */
private static boolean fullfillsConditions(AreaEval db, int row, AreaEval cdb) throws EvaluationException {
    // Only one row must match to accept the input, so rows are ORed.
    // Each row is made up of cells where each cell is a condition,
    // all have to match, so they are ANDed.
    final int height = cdb.getHeight();
    for (int conditionRow = 1; conditionRow < height; ++conditionRow) {
        boolean matches = true;
        final int width = cdb.getWidth();
        for (int column = 0; column < width; ++column) {
            // columns are ANDed
            // Whether the condition column matches a database column, if not it's a
            // special column that accepts formulas.
            boolean columnCondition = true;
            ValueEval condition = null;
            // The condition to apply.
            condition = resolveReference(cdb, conditionRow, column);
            // If the condition is empty it matches.
            if (condition instanceof BlankEval)
                continue;
            // The column in the DB to apply the condition to.
            ValueEval targetHeader = resolveReference(cdb, 0, column);
            if (!(targetHeader instanceof StringValueEval)) {
                throw new EvaluationException(ErrorEval.VALUE_INVALID);
            }
            if (getColumnForName(targetHeader, db) == -1)
                // No column found, it's again a special column that accepts formulas.
                columnCondition = false;
            if (columnCondition == true) {
                // normal column condition
                // Should not throw, checked above.
                ValueEval value = resolveReference(db, row, getColumnForName(targetHeader, db));
                if (!testNormalCondition(value, condition)) {
                    matches = false;
                    break;
                }
            } else {
                // TODO: Check whether the condition cell contains a formula and return #VALUE! if it doesn't.
                if (OperandResolver.coerceValueToString(condition).isEmpty()) {
                    throw new EvaluationException(ErrorEval.VALUE_INVALID);
                }
                throw new NotImplementedException("D* function with formula conditions");
            }
        }
        if (matches == true) {
            return true;
        }
    }
    return false;
}
Also used : NotImplementedException(org.apache.poi.ss.formula.eval.NotImplementedException) NumericValueEval(org.apache.poi.ss.formula.eval.NumericValueEval) ValueEval(org.apache.poi.ss.formula.eval.ValueEval) StringValueEval(org.apache.poi.ss.formula.eval.StringValueEval) BlankEval(org.apache.poi.ss.formula.eval.BlankEval) EvaluationException(org.apache.poi.ss.formula.eval.EvaluationException) StringValueEval(org.apache.poi.ss.formula.eval.StringValueEval)

Example 4 with EvaluationException

use of org.apache.poi.ss.formula.eval.EvaluationException in project poi by apache.

the class DStarRunner method evaluate.

public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval database, ValueEval filterColumn, ValueEval conditionDatabase) {
    // Input processing and error checks.
    if (!(database instanceof AreaEval) || !(conditionDatabase instanceof AreaEval)) {
        return ErrorEval.VALUE_INVALID;
    }
    AreaEval db = (AreaEval) database;
    AreaEval cdb = (AreaEval) conditionDatabase;
    try {
        filterColumn = OperandResolver.getSingleValue(filterColumn, srcRowIndex, srcColumnIndex);
    } catch (EvaluationException e) {
        return e.getErrorEval();
    }
    int fc;
    try {
        fc = getColumnForName(filterColumn, db);
    } catch (EvaluationException e) {
        return ErrorEval.VALUE_INVALID;
    }
    if (fc == -1) {
        // column not found
        return ErrorEval.VALUE_INVALID;
    }
    // Create an algorithm runner.
    IDStarAlgorithm algorithm = null;
    switch(algoType) {
        case DGET:
            algorithm = new DGet();
            break;
        case DMIN:
            algorithm = new DMin();
            break;
        default:
            throw new IllegalStateException("Unexpected algorithm type " + algoType + " encountered.");
    }
    // Iterate over all DB entries.
    final int height = db.getHeight();
    for (int row = 1; row < height; ++row) {
        boolean matches = true;
        try {
            matches = fullfillsConditions(db, row, cdb);
        } catch (EvaluationException e) {
            return ErrorEval.VALUE_INVALID;
        }
        // Filter each entry.
        if (matches) {
            ValueEval currentValueEval = resolveReference(db, row, fc);
            // Pass the match to the algorithm and conditionally abort the search.
            boolean shouldContinue = algorithm.processMatch(currentValueEval);
            if (!shouldContinue) {
                break;
            }
        }
    }
    // Return the result of the algorithm.
    return algorithm.getResult();
}
Also used : NumericValueEval(org.apache.poi.ss.formula.eval.NumericValueEval) ValueEval(org.apache.poi.ss.formula.eval.ValueEval) StringValueEval(org.apache.poi.ss.formula.eval.StringValueEval) EvaluationException(org.apache.poi.ss.formula.eval.EvaluationException) AreaEval(org.apache.poi.ss.formula.eval.AreaEval)

Example 5 with EvaluationException

use of org.apache.poi.ss.formula.eval.EvaluationException in project poi by apache.

the class Baseifs method evaluate.

public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) {
    final boolean hasInitialRange = hasInitialRange();
    final int firstCriteria = hasInitialRange ? 1 : 0;
    if (args.length < (2 + firstCriteria) || args.length % 2 != firstCriteria) {
        return ErrorEval.VALUE_INVALID;
    }
    try {
        AreaEval sumRange = null;
        if (hasInitialRange) {
            sumRange = convertRangeArg(args[0]);
        }
        // collect pairs of ranges and criteria
        AreaEval[] ae = new AreaEval[(args.length - firstCriteria) / 2];
        I_MatchPredicate[] mp = new I_MatchPredicate[ae.length];
        for (int i = firstCriteria, k = 0; i < args.length; i += 2, k++) {
            ae[k] = convertRangeArg(args[i]);
            mp[k] = Countif.createCriteriaPredicate(args[i + 1], ec.getRowIndex(), ec.getColumnIndex());
        }
        validateCriteriaRanges(sumRange, ae);
        validateCriteria(mp);
        double result = aggregateMatchingCells(sumRange, ae, mp);
        return new NumberEval(result);
    } catch (EvaluationException e) {
        return e.getErrorEval();
    }
}
Also used : I_MatchPredicate(org.apache.poi.ss.formula.functions.CountUtils.I_MatchPredicate) EvaluationException(org.apache.poi.ss.formula.eval.EvaluationException) AreaEval(org.apache.poi.ss.formula.eval.AreaEval) NumberEval(org.apache.poi.ss.formula.eval.NumberEval)

Aggregations

EvaluationException (org.apache.poi.ss.formula.eval.EvaluationException)50 ValueEval (org.apache.poi.ss.formula.eval.ValueEval)36 NumberEval (org.apache.poi.ss.formula.eval.NumberEval)19 NumericValueEval (org.apache.poi.ss.formula.eval.NumericValueEval)10 StringEval (org.apache.poi.ss.formula.eval.StringEval)9 TwoDEval (org.apache.poi.ss.formula.TwoDEval)7 ValueVector (org.apache.poi.ss.formula.functions.LookupUtils.ValueVector)7 Calendar (java.util.Calendar)6 ErrorEval (org.apache.poi.ss.formula.eval.ErrorEval)5 RefEval (org.apache.poi.ss.formula.eval.RefEval)5 AreaEval (org.apache.poi.ss.formula.eval.AreaEval)4 StringValueEval (org.apache.poi.ss.formula.eval.StringValueEval)3 Date (java.util.Date)2 BlankEval (org.apache.poi.ss.formula.eval.BlankEval)2 BigDecimal (java.math.BigDecimal)1 DecimalFormat (java.text.DecimalFormat)1 NumberFormat (java.text.NumberFormat)1 ArrayList (java.util.ArrayList)1 Matcher (java.util.regex.Matcher)1 AssertionFailedError (junit.framework.AssertionFailedError)1