Search in sources :

Example 6 with NumberEval

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

the class CalendarFieldFunction method evaluate.

public final ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0) {
    double val;
    try {
        ValueEval ve = OperandResolver.getSingleValue(arg0, srcRowIndex, srcColumnIndex);
        val = OperandResolver.coerceValueToDouble(ve);
    } catch (EvaluationException e) {
        return e.getErrorEval();
    }
    if (val < 0) {
        return ErrorEval.NUM_ERROR;
    }
    return new NumberEval(getCalField(val));
}
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 7 with NumberEval

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

the class NetworkdaysFunction method evaluate.

/**
     * Evaluate for NETWORKDAYS. Given two dates and a optional date or interval of holidays, determines how many working days are there
     * between those dates.
     * 
     * @return {@link ValueEval} for the number of days between two dates.
     */
public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) {
    // NOSONAR
    if (args.length < 2 || args.length > 3) {
        return ErrorEval.VALUE_INVALID;
    }
    int srcCellRow = ec.getRowIndex();
    int srcCellCol = ec.getColumnIndex();
    double start, end;
    double[] holidays;
    try {
        start = this.evaluator.evaluateDateArg(args[0], srcCellRow, srcCellCol);
        end = this.evaluator.evaluateDateArg(args[1], srcCellRow, srcCellCol);
        if (start > end) {
            return ErrorEval.NAME_INVALID;
        }
        ValueEval holidaysCell = args.length == 3 ? args[2] : null;
        holidays = this.evaluator.evaluateDatesArg(holidaysCell, srcCellRow, srcCellCol);
        return new NumberEval(WorkdayCalculator.instance.calculateWorkdays(start, end, holidays));
    } catch (EvaluationException e) {
        return ErrorEval.VALUE_INVALID;
    }
}
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 8 with NumberEval

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

the class WorkdayFunction method evaluate.

/**
     * Evaluate for WORKDAY. Given a date, a number of days and a optional date or interval of holidays, determines which date it is past
     * number of parametrized workdays.
     * 
     * @return {@link ValueEval} with date as its value.
     */
public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) {
    if (args.length < 2 || args.length > 3) {
        return ErrorEval.VALUE_INVALID;
    }
    int srcCellRow = ec.getRowIndex();
    int srcCellCol = ec.getColumnIndex();
    double start;
    int days;
    double[] holidays;
    try {
        start = this.evaluator.evaluateDateArg(args[0], srcCellRow, srcCellCol);
        days = (int) Math.floor(this.evaluator.evaluateNumberArg(args[1], srcCellRow, srcCellCol));
        ValueEval holidaysCell = args.length == 3 ? args[2] : null;
        holidays = this.evaluator.evaluateDatesArg(holidaysCell, srcCellRow, srcCellCol);
        return new NumberEval(DateUtil.getExcelDate(WorkdayCalculator.instance.calculateWorkdays(start, days, holidays)));
    } catch (EvaluationException e) {
        return ErrorEval.VALUE_INVALID;
    }
}
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 9 with NumberEval

use of org.apache.poi.ss.formula.eval.NumberEval 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 10 with NumberEval

use of org.apache.poi.ss.formula.eval.NumberEval 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)

Aggregations

NumberEval (org.apache.poi.ss.formula.eval.NumberEval)123 ValueEval (org.apache.poi.ss.formula.eval.ValueEval)101 Test (org.junit.Test)34 StringEval (org.apache.poi.ss.formula.eval.StringEval)29 AreaEval (org.apache.poi.ss.formula.eval.AreaEval)21 EvaluationException (org.apache.poi.ss.formula.eval.EvaluationException)19 Calendar (java.util.Calendar)17 Date (java.util.Date)13 NumericValueEval (org.apache.poi.ss.formula.eval.NumericValueEval)12 ErrorEval (org.apache.poi.ss.formula.eval.ErrorEval)8 I_MatchPredicate (org.apache.poi.ss.formula.functions.CountUtils.I_MatchPredicate)5 RefEval (org.apache.poi.ss.formula.eval.RefEval)4 AssertionFailedError (junit.framework.AssertionFailedError)3 ValueVector (org.apache.poi.ss.formula.functions.LookupUtils.ValueVector)3 HSSFWorkbook (org.apache.poi.hssf.usermodel.HSSFWorkbook)2 BoolEval (org.apache.poi.ss.formula.eval.BoolEval)2 Workbook (org.apache.poi.ss.usermodel.Workbook)2 DateFormatSymbols (java.text.DateFormatSymbols)1 DecimalFormatSymbols (java.text.DecimalFormatSymbols)1 SimpleDateFormat (java.text.SimpleDateFormat)1