Search in sources :

Example 26 with ValueEval

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

the class WeekdayFunc method evaluate.

/* for Var1or2ArgFunction:
	@Override
	public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0) {
	}

	@Override
	public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) {
	}
	*/
/**
	 * Perform WEEKDAY(date, returnOption) function.
	 * Note: Parameter texts are from German EXCEL-2010 help.
	 * Parameters in args[]:
	 *  args[0] serialDate
	 * EXCEL-date value
	 * Standardmaessig ist der 1. Januar 1900 die fortlaufende Zahl 1 und
	 * der 1. Januar 2008 die fortlaufende Zahl 39.448, da dieser Tag nach 39.448 Tagen
	 * auf den 01.01.1900 folgt.
	 * @return Option (optional)
	 * Bestimmt den Rueckgabewert:
		1	oder nicht angegeben Zahl 1 (Sonntag) bis 7 (Samstag). Verhaelt sich wie fruehere Microsoft Excel-Versionen.
		2	Zahl 1 (Montag) bis 7 (Sonntag).
		3	Zahl 0 (Montag) bis 6 (Sonntag).
		11	Die Zahlen 1 (Montag) bis 7 (Sonntag)
		12	Die Zahlen 1 (Dienstag) bis 7 (Montag)
		13	Die Zahlen 1 (Mittwoch) bis 7 (Dienstag)
		14	Die Zahlen 1 (Donnerstag) bis 7 (Mittwoch)
		15	Die Zahlen 1 (Freitag) bis 7 (Donnerstag)
		16	Die Zahlen 1 (Samstag) bis 7 (Freitag)
		17	Die Zahlen 1 (Sonntag) bis 7 (Samstag)
	 */
public ValueEval evaluate(ValueEval[] args, int srcRowIndex, int srcColumnIndex) {
    try {
        if (args.length < 1 || args.length > 2) {
            return ErrorEval.VALUE_INVALID;
        }
        // extract first parameter
        ValueEval serialDateVE = OperandResolver.getSingleValue(args[0], srcRowIndex, srcColumnIndex);
        double serialDate = OperandResolver.coerceValueToDouble(serialDateVE);
        if (!DateUtil.isValidExcelDate(serialDate)) {
            // EXCEL uses this and no VALUE_ERROR
            return ErrorEval.NUM_ERROR;
        }
        // (XXX 1904-windowing not respected)
        Calendar date = DateUtil.getJavaCalendar(serialDate, false);
        // => sunday = 1, monday = 2, ..., saturday = 7
        int weekday = date.get(Calendar.DAY_OF_WEEK);
        // extract second parameter
        // default value
        int returnOption = 1;
        if (args.length == 2) {
            ValueEval ve = OperandResolver.getSingleValue(args[1], srcRowIndex, srcColumnIndex);
            if (ve == MissingArgEval.instance || ve == BlankEval.instance) {
                // EXCEL uses this and no VALUE_ERROR
                return ErrorEval.NUM_ERROR;
            }
            returnOption = OperandResolver.coerceValueToInt(ve);
            if (returnOption == 2) {
                // both mean the same
                returnOption = 11;
            }
        }
        // if
        // perform calculation
        double result;
        if (returnOption == 1) {
            result = weekday;
        // value 2 is handled above (as value 11)
        } else if (returnOption == 3) {
            result = (weekday + 6 - 1) % 7;
        } else if (returnOption >= 11 && returnOption <= 17) {
            // rotate in the value range 1 to 7
            result = (weekday + 6 - (returnOption - 10)) % 7 + 1;
        } else {
            // EXCEL uses this and no VALUE_ERROR
            return ErrorEval.NUM_ERROR;
        }
        return new NumberEval(result);
    } catch (EvaluationException e) {
        return e.getErrorEval();
    }
}
Also used : Calendar(java.util.Calendar) 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 27 with ValueEval

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

the class XYNumericFunction method evaluateInternal.

private double evaluateInternal(ValueVector x, ValueVector y, int size) throws EvaluationException {
    Accumulator acc = createAccumulator();
    // error handling is as if the x is fully evaluated before y
    ErrorEval firstXerr = null;
    ErrorEval firstYerr = null;
    boolean accumlatedSome = false;
    double result = 0.0;
    for (int i = 0; i < size; i++) {
        ValueEval vx = x.getItem(i);
        ValueEval vy = y.getItem(i);
        if (vx instanceof ErrorEval) {
            if (firstXerr == null) {
                firstXerr = (ErrorEval) vx;
                continue;
            }
        }
        if (vy instanceof ErrorEval) {
            if (firstYerr == null) {
                firstYerr = (ErrorEval) vy;
                continue;
            }
        }
        // only count pairs if both elements are numbers
        if (vx instanceof NumberEval && vy instanceof NumberEval) {
            accumlatedSome = true;
            NumberEval nx = (NumberEval) vx;
            NumberEval ny = (NumberEval) vy;
            result += acc.accumulate(nx.getNumberValue(), ny.getNumberValue());
        } else {
        // all other combinations of value types are silently ignored
        }
    }
    if (firstXerr != null) {
        throw new EvaluationException(firstXerr);
    }
    if (firstYerr != null) {
        throw new EvaluationException(firstYerr);
    }
    if (!accumlatedSome) {
        throw new EvaluationException(ErrorEval.DIV_ZERO);
    }
    return result;
}
Also used : ValueEval(org.apache.poi.ss.formula.eval.ValueEval) ErrorEval(org.apache.poi.ss.formula.eval.ErrorEval) EvaluationException(org.apache.poi.ss.formula.eval.EvaluationException) NumberEval(org.apache.poi.ss.formula.eval.NumberEval)

Example 28 with ValueEval

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

the class Rate method evaluate.

public ValueEval evaluate(ValueEval[] args, int srcRowIndex, int srcColumnIndex) {
    if (args.length < 3) {
        //First 3 parameters are mandatory
        return ErrorEval.VALUE_INVALID;
    }
    double periods, payment, present_val, future_val = 0, type = 0, estimate = 0.1, rate;
    try {
        ValueEval v1 = OperandResolver.getSingleValue(args[0], srcRowIndex, srcColumnIndex);
        ValueEval v2 = OperandResolver.getSingleValue(args[1], srcRowIndex, srcColumnIndex);
        ValueEval v3 = OperandResolver.getSingleValue(args[2], srcRowIndex, srcColumnIndex);
        ValueEval v4 = null;
        if (args.length >= 4)
            v4 = OperandResolver.getSingleValue(args[3], srcRowIndex, srcColumnIndex);
        ValueEval v5 = null;
        if (args.length >= 5)
            v5 = OperandResolver.getSingleValue(args[4], srcRowIndex, srcColumnIndex);
        ValueEval v6 = null;
        if (args.length >= 6)
            v6 = OperandResolver.getSingleValue(args[5], srcRowIndex, srcColumnIndex);
        periods = OperandResolver.coerceValueToDouble(v1);
        payment = OperandResolver.coerceValueToDouble(v2);
        present_val = OperandResolver.coerceValueToDouble(v3);
        if (args.length >= 4)
            future_val = OperandResolver.coerceValueToDouble(v4);
        if (args.length >= 5)
            type = OperandResolver.coerceValueToDouble(v5);
        if (args.length >= 6)
            estimate = OperandResolver.coerceValueToDouble(v6);
        rate = calculateRate(periods, payment, present_val, future_val, type, estimate);
        checkValue(rate);
    } catch (EvaluationException e) {
        LOG.log(POILogger.ERROR, "Can't evaluate rate function", e);
        return e.getErrorEval();
    }
    return new NumberEval(rate);
}
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 29 with ValueEval

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

the class Sumproduct method evaluate.

public ValueEval evaluate(ValueEval[] args, int srcCellRow, int srcCellCol) {
    int maxN = args.length;
    if (maxN < 1) {
        return ErrorEval.VALUE_INVALID;
    }
    ValueEval firstArg = args[0];
    try {
        if (firstArg instanceof NumericValueEval) {
            return evaluateSingleProduct(args);
        }
        if (firstArg instanceof RefEval) {
            return evaluateSingleProduct(args);
        }
        if (firstArg instanceof TwoDEval) {
            TwoDEval ae = (TwoDEval) firstArg;
            if (ae.isRow() && ae.isColumn()) {
                return evaluateSingleProduct(args);
            }
            return evaluateAreaSumProduct(args);
        }
    } catch (EvaluationException e) {
        return e.getErrorEval();
    }
    throw new RuntimeException("Invalid arg type for SUMPRODUCT: (" + firstArg.getClass().getName() + ")");
}
Also used : NumericValueEval(org.apache.poi.ss.formula.eval.NumericValueEval) TwoDEval(org.apache.poi.ss.formula.TwoDEval) RefEval(org.apache.poi.ss.formula.eval.RefEval) NumericValueEval(org.apache.poi.ss.formula.eval.NumericValueEval) ValueEval(org.apache.poi.ss.formula.eval.ValueEval) EvaluationException(org.apache.poi.ss.formula.eval.EvaluationException)

Example 30 with ValueEval

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

the class Indirect method evaluate.

public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) {
    if (args.length < 1) {
        return ErrorEval.VALUE_INVALID;
    }
    boolean isA1style;
    String text;
    try {
        ValueEval ve = OperandResolver.getSingleValue(args[0], ec.getRowIndex(), ec.getColumnIndex());
        text = OperandResolver.coerceValueToString(ve);
        switch(args.length) {
            case 1:
                isA1style = true;
                break;
            case 2:
                isA1style = evaluateBooleanArg(args[1], ec);
                break;
            default:
                return ErrorEval.VALUE_INVALID;
        }
    } catch (EvaluationException e) {
        return e.getErrorEval();
    }
    return evaluateIndirect(ec, text, isA1style);
}
Also used : ValueEval(org.apache.poi.ss.formula.eval.ValueEval) EvaluationException(org.apache.poi.ss.formula.eval.EvaluationException)

Aggregations

ValueEval (org.apache.poi.ss.formula.eval.ValueEval)223 NumberEval (org.apache.poi.ss.formula.eval.NumberEval)101 StringEval (org.apache.poi.ss.formula.eval.StringEval)48 Test (org.junit.Test)39 EvaluationException (org.apache.poi.ss.formula.eval.EvaluationException)36 AreaEval (org.apache.poi.ss.formula.eval.AreaEval)29 NumericValueEval (org.apache.poi.ss.formula.eval.NumericValueEval)28 OperationEvaluationContext (org.apache.poi.ss.formula.OperationEvaluationContext)27 Calendar (java.util.Calendar)16 ErrorEval (org.apache.poi.ss.formula.eval.ErrorEval)13 Date (java.util.Date)11 RefEval (org.apache.poi.ss.formula.eval.RefEval)9 TwoDEval (org.apache.poi.ss.formula.TwoDEval)7 BlankEval (org.apache.poi.ss.formula.eval.BlankEval)4 NotImplementedException (org.apache.poi.ss.formula.eval.NotImplementedException)4 StringValueEval (org.apache.poi.ss.formula.eval.StringValueEval)4 ValueVector (org.apache.poi.ss.formula.functions.LookupUtils.ValueVector)4 ArrayList (java.util.ArrayList)3 AssertionFailedError (junit.framework.AssertionFailedError)3 BoolEval (org.apache.poi.ss.formula.eval.BoolEval)3