Attachment #28094 for bug #52378

View | Details | Raw Unified | Return to bug 52378
Collapse All | Expand All




package org.apache.poi.ss.formula.atp;

import org.apache.poi.ss.formula.OperationEvaluationContext;
import org.apache.poi.ss.formula.eval.ErrorEval;
import org.apache.poi.ss.formula.eval.EvaluationException;
import org.apache.poi.ss.formula.eval.NumberEval;
import org.apache.poi.ss.formula.eval.ValueEval;
import org.apache.poi.ss.formula.functions.FreeRefFunction;

/**
 * Implementation of Excel 'Analysis ToolPak' function NETWORKDAYS()<br/>
 * Returns the number of workdays given a starting and an ending date, considering an interval of holidays. A workday is any non
 * saturday/sunday date.
 * <p/>
 * <b>Syntax</b><br/>
 * <b>NETWORKDAYS</b>(<b>startDate</b>, <b>endDate</b>, holidays)
 * <p/>
 * 
 * @author jfaenomoto@gmail.com
 */
final class NetworkdaysFunction implements FreeRefFunction {

    public static final FreeRefFunction instance = new NetworkdaysFunction(ArgumentsEvaluator.instance);

    private ArgumentsEvaluator evaluator;

    /**
     * Constructor.
     * 
     * @param anEvaluator an injected {@link ArgumentsEvaluator}.
     */
    private NetworkdaysFunction(ArgumentsEvaluator anEvaluator) {
        // enforces singleton
        this.evaluator = anEvaluator;
    }

    /**
     * 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) {
        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;
        }
    }
}




import org.apache.poi.ss.formula.eval.*;
import org.apache.poi.ss.formula.functions.FreeRefFunction;
import org.apache.poi.ss.formula.functions.NumericFunction;
import org.apache.poi.ss.usermodel.DateUtil;

import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.regex.Pattern;

/**
 * Implementation of Excel 'Analysis ToolPak' function MROUND()<br/>
 *




package org.apache.poi.ss.formula.atp;

import org.apache.poi.ss.formula.OperationEvaluationContext;
import org.apache.poi.ss.formula.eval.ErrorEval;
import org.apache.poi.ss.formula.eval.EvaluationException;
import org.apache.poi.ss.formula.eval.NumberEval;
import org.apache.poi.ss.formula.eval.ValueEval;
import org.apache.poi.ss.formula.functions.FreeRefFunction;
import org.apache.poi.ss.usermodel.DateUtil;

/**
 * Implementation of Excel 'Analysis ToolPak' function WORKDAY()<br/>
 * Returns the date past a number of workdays beginning at a start date, considering an interval of holidays. A workday is any non
 * saturday/sunday date.
 * <p/>
 * <b>Syntax</b><br/>
 * <b>WORKDAY</b>(<b>startDate</b>, <b>days</b>, holidays)
 * <p/>
 * 
 * @author jfaenomoto@gmail.com
 */
final class WorkdayFunction implements FreeRefFunction {

    public static final FreeRefFunction instance = new WorkdayFunction(ArgumentsEvaluator.instance);

    private ArgumentsEvaluator evaluator;

    private WorkdayFunction(ArgumentsEvaluator anEvaluator) {
        // enforces singleton
        this.evaluator = anEvaluator;
    }

    /**
     * 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;
        }
    }

}




package org.apache.poi.ss.formula.atp;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import org.apache.poi.ss.formula.eval.AreaEvalBase;
import org.apache.poi.ss.formula.eval.EvaluationException;
import org.apache.poi.ss.formula.eval.OperandResolver;
import org.apache.poi.ss.formula.eval.StringEval;
import org.apache.poi.ss.formula.eval.ValueEval;
import org.apache.poi.ss.usermodel.DateUtil;

/**
 * Evaluator for formula arguments.
 * 
 * @author jfaenomoto@gmail.com
 */
final class ArgumentsEvaluator {

    public static final ArgumentsEvaluator instance = new ArgumentsEvaluator();

    private ArgumentsEvaluator() {
        // enforces singleton
    }

    /**
     * Evaluate a generic {@link ValueEval} argument to a double value that represents a date in POI.
     * 
     * @param arg {@link ValueEval} an argument.
     * @param srcCellRow number cell row.
     * @param srcCellCol number cell column.
     * @return a double representing a date in POI.
     * @throws EvaluationException exception upon argument evaluation.
     */
    public double evaluateDateArg(ValueEval arg, int srcCellRow, int srcCellCol) throws EvaluationException {
        ValueEval ve = OperandResolver.getSingleValue(arg, srcCellRow, (short) srcCellCol);

        if (ve instanceof StringEval) {
            String strVal = ((StringEval) ve).getStringValue();
            Double dVal = OperandResolver.parseDouble(strVal);
            if (dVal != null) {
                return dVal.doubleValue();
            }
            Calendar date = DateParser.parseDate(strVal);
            return DateUtil.getExcelDate(date, false);
        }
        return OperandResolver.coerceValueToDouble(ve);
    }

    /**
     * Evaluate a generic {@link ValueEval} argument to an array of double values that represents dates in POI.
     * 
     * @param arg {@link ValueEval} an argument.
     * @param srcCellRow number cell row.
     * @param srcCellCol number cell column.
     * @return an array of doubles representing dates in POI.
     * @throws EvaluationException exception upon argument evaluation.
     */
    public double[] evaluateDatesArg(ValueEval arg, int srcCellRow, int srcCellCol) throws EvaluationException {
        if (arg == null) {
            return new double[0];
        }

        if (arg instanceof StringEval) {
            return new double[]{ evaluateDateArg(arg, srcCellRow, srcCellCol) };
        } else if (arg instanceof AreaEvalBase) {
            List<Double> valuesList = new ArrayList<Double>();
            AreaEvalBase area = (AreaEvalBase) arg;
            for (int i = area.getFirstRow(); i <= area.getLastRow(); i++) {
                for (int j = area.getFirstColumn(); j <= area.getLastColumn(); j++) {
                    valuesList.add(evaluateDateArg(area.getValue(i, j), i, j));
                }
            }
            double[] values = new double[valuesList.size()];
            for (int i = 0; i < valuesList.size(); i++) {
                values[i] = valuesList.get(i).doubleValue();
            }
            return values;
        }
        return new double[]{ OperandResolver.coerceValueToDouble(arg) };
    }

    /**
     * Evaluate a generic {@link ValueEval} argument to a double value.
     * 
     * @param arg {@link ValueEval} an argument.
     * @param srcCellRow number cell row.
     * @param srcCellCol number cell column.
     * @return a double value.
     * @throws EvaluationException exception upon argument evaluation.
     */
    public double evaluateNumberArg(ValueEval arg, int srcCellRow, int srcCellCol) throws EvaluationException {
        if (arg == null) {
            return 0f;
        }

        return OperandResolver.coerceValueToDouble(arg);
    }
}




package org.apache.poi.ss.formula.atp;

import java.util.Calendar;
import java.util.Date;

import org.apache.poi.ss.usermodel.DateUtil;

/**
 * A calculator for workdays, considering dates as excel representations.
 * 
 * @author jfaenomoto@gmail.com
 */
public class WorkdayCalculator {

    public static final WorkdayCalculator instance = new WorkdayCalculator();

    /**
     * Constructor.
     */
    private WorkdayCalculator() {
        // enforcing singleton
    }

    /**
     * Calculate how many workdays are there between a start and an end date, as excel representations, considering a range of holidays.
     * 
     * @param start start date.
     * @param end end date.
     * @param holidays an array of holidays.
     * @return number of workdays between start and end dates, including both dates.
     */
    public int calculateWorkdays(double start, double end, double[] holidays) {
        int saturdaysPast = this.pastDaysOfWeek(start, end, Calendar.SATURDAY);
        int sundaysPast = this.pastDaysOfWeek(start, end, Calendar.SUNDAY);
        int nonWeekendHolidays = this.calculateNonWeekendHolidays(start, end, holidays);
        return (int) (end - start + 1) - saturdaysPast - sundaysPast - nonWeekendHolidays;
    }

    /**
     * Calculate the workday past x workdays from a starting date, considering a range of holidays.
     * 
     * @param start start date.
     * @param workdays number of workdays to be past from starting date.
     * @param holidays an array of holidays.
     * @return date past x workdays.
     */
    public Date calculateWorkdays(double start, int workdays, double[] holidays) {
        Date startDate = DateUtil.getJavaDate(start);
        Calendar endDate = Calendar.getInstance();
        endDate.setTime(startDate);
        endDate.add(Calendar.DAY_OF_YEAR, workdays);
        int skippedDays = 0;
        do {
            double end = DateUtil.getExcelDate(endDate.getTime());
            int saturdaysPast = this.pastDaysOfWeek(start, end, Calendar.SATURDAY);
            int sundaysPast = this.pastDaysOfWeek(start, end, Calendar.SUNDAY);
            int nonWeekendHolidays = this.calculateNonWeekendHolidays(start, end, holidays);
            skippedDays = saturdaysPast + sundaysPast + nonWeekendHolidays;
            endDate.add(Calendar.DAY_OF_YEAR, skippedDays);
            start = end + isNonWorkday(end, holidays);
        } while (skippedDays != 0);
        return endDate.getTime();
    }

    /**
     * Calculates how many days of week past between a start and an end date.
     * 
     * @param start start date.
     * @param end end date.
     * @param dayOfWeek a day of week as represented by {@link Calendar} constants.
     * @return how many days of week past in this interval.
     */
    protected int pastDaysOfWeek(double start, double end, int dayOfWeek) {
        int pastDaysOfWeek = 0;
        int startDay = (int) Math.floor(start < end ? start : end);
        int endDay = (int) Math.floor(end > start ? end : start);
        for (; startDay <= endDay; startDay++) {
            Calendar today = Calendar.getInstance();
            today.setTime(DateUtil.getJavaDate(startDay));
            if (today.get(Calendar.DAY_OF_WEEK) == dayOfWeek) {
                pastDaysOfWeek++;
            }
        }
        return start < end ? pastDaysOfWeek : -pastDaysOfWeek;
    }

    /**
     * Calculates how many holidays in a list are workdays, considering an interval of dates.
     * 
     * @param start start date.
     * @param end end date.
     * @param holidays an array of holidays.
     * @return number of holidays that occur in workdays, between start and end dates.
     */
    protected int calculateNonWeekendHolidays(double start, double end, double[] holidays) {
        int nonWeekendHolidays = 0;
        double startDay = start < end ? start : end;
        double endDay = end > start ? end : start;
        for (int i = 0; i < holidays.length; i++) {
            if (isInARange(startDay, endDay, holidays[i])) {
                if (!isWeekend(holidays[i])) {
                    nonWeekendHolidays++;
                }
            }
        }
        return start < end ? nonWeekendHolidays : -nonWeekendHolidays;
    }

    /**
     * @param aDate a given date.
     * @return <code>true</code> if date is weekend, <code>false</code> otherwise.
     */
    protected boolean isWeekend(double aDate) {
        Calendar date = Calendar.getInstance();
        date.setTime(DateUtil.getJavaDate(aDate));
        return date.get(Calendar.DAY_OF_WEEK) == Calendar.SATURDAY || date.get(Calendar.DAY_OF_WEEK) == Calendar.SUNDAY;
    }

    /**
     * @param aDate a given date.
     * @param holidays an array of holidays.
     * @return <code>true</code> if date is a holiday, <code>false</code> otherwise.
     */
    protected boolean isHoliday(double aDate, double[] holidays) {
        for (int i = 0; i < holidays.length; i++) {
            if (Math.round(holidays[i]) == Math.round(aDate)) {
                return true;
            }
        }
        return false;
    }

    /**
     * @param aDate a given date.
     * @param holidays an array of holidays.
     * @return <code>1</code> is not a workday, <code>0</code> otherwise.
     */
    protected int isNonWorkday(double aDate, double[] holidays) {
        return isWeekend(aDate) || isHoliday(aDate, holidays) ? 1 : 0;
    }

    /**
     * @param start start date.
     * @param end end date.
     * @param aDate a date to be analyzed.
     * @return <code>true</code> if aDate is between start and end dates, <code>false</code> otherwise.
     */
    protected boolean isInARange(double start, double end, double aDate) {
        return aDate >= start && aDate <= end;
    }
}




package org.apache.poi.ss.formula.atp;

import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.regex.Pattern;

import org.apache.poi.ss.formula.eval.ErrorEval;
import org.apache.poi.ss.formula.eval.EvaluationException;

/**
 * Parser for java dates.
 * 
 * @author jfaenomoto@gmail.com
 */
public class DateParser {

    public DateParser instance = new DateParser();

    private DateParser() {
        // enforcing singleton
    }

    /**
     * Parses a date from a string.
     * 
     * @param strVal a string with a date pattern.
     * @return a date parsed from argument.
     * @throws EvaluationException exception upon parsing.
     */
    public static Calendar parseDate(String strVal) throws EvaluationException {
        String[] parts = Pattern.compile("/").split(strVal);
        if (parts.length != 3) {
            throw new EvaluationException(ErrorEval.VALUE_INVALID);
        }
        String part2 = parts[2];
        int spacePos = part2.indexOf(' ');
        if (spacePos > 0) {
            // drop time portion if present
            part2 = part2.substring(0, spacePos);
        }
        int f0;
        int f1;
        int f2;
        try {
            f0 = Integer.parseInt(parts[0]);
            f1 = Integer.parseInt(parts[1]);
            f2 = Integer.parseInt(part2);
        } catch (NumberFormatException e) {
            throw new EvaluationException(ErrorEval.VALUE_INVALID);
        }
        if (f0 < 0 || f1 < 0 || f2 < 0 || (f0 > 12 && f1 > 12 && f2 > 12)) {
            // easy to see this cannot be a valid date
            throw new EvaluationException(ErrorEval.VALUE_INVALID);
        }

        if (f0 >= 1900 && f0 < 9999) {
            // when 4 digit value appears first, the format is YYYY/MM/DD, regardless of OS settings
            return makeDate(f0, f1, f2);
        }
        // otherwise the format seems to depend on OS settings (default date format)
        if (false) {
            // MM/DD/YYYY is probably a good guess, if the in the US
            return makeDate(f2, f0, f1);
        }
        // TODO - find a way to choose the correct date format
        throw new RuntimeException("Unable to determine date format for text '" + strVal + "'");
    }

    /**
     * @param month 1-based
     */
    private static Calendar makeDate(int year, int month, int day) throws EvaluationException {
        if (month < 1 || month > 12) {
            throw new EvaluationException(ErrorEval.VALUE_INVALID);
        }
        Calendar cal = new GregorianCalendar(year, month - 1, 1, 0, 0, 0);
        cal.set(Calendar.MILLISECOND, 0);
        if (day < 1 || day > cal.getActualMaximum(Calendar.DAY_OF_MONTH)) {
            throw new EvaluationException(ErrorEval.VALUE_INVALID);
        }
        cal.set(Calendar.DAY_OF_MONTH, day);
        return cal;
    }

}




			if (dVal != null) {
				return dVal.doubleValue();
			}
			Calendar date = DateParser.parseDate(strVal);
			return DateUtil.getExcelDate(date, false);
		}
		return OperandResolver.coerceValueToDouble(ve);
	}

	private static Calendar parseDate(String strVal) throws EvaluationException {
		String[] parts = Pattern.compile("/").split(strVal);
		if (parts.length != 3) {
			throw new EvaluationException(ErrorEval.VALUE_INVALID);
		}
		String part2 = parts[2];
		int spacePos = part2.indexOf(' ');
		if (spacePos > 0) {
			// drop time portion if present
			part2 = part2.substring(0, spacePos);
		}
		int f0;
		int f1;
		int f2;
		try {
			f0 = Integer.parseInt(parts[0]);
			f1 = Integer.parseInt(parts[1]);
			f2 = Integer.parseInt(part2);
		} catch (NumberFormatException e) {
			throw new EvaluationException(ErrorEval.VALUE_INVALID);
		}
		if (f0<0 || f1<0 || f2<0 || (f0>12 && f1>12 && f2>12)) {
			// easy to see this cannot be a valid date
			throw new EvaluationException(ErrorEval.VALUE_INVALID);
		}

		if (f0 >= 1900 && f0 < 9999) {
			// when 4 digit value appears first, the format is YYYY/MM/DD, regardless of OS settings
			return makeDate(f0, f1, f2);
		}
		// otherwise the format seems to depend on OS settings (default date format)
		if (false) {
			// MM/DD/YYYY is probably a good guess, if the in the US
			return makeDate(f2, f0, f1);
		}
		// TODO - find a way to choose the correct date format
		throw new RuntimeException("Unable to determine date format for text '" + strVal + "'");
	}

	/**
	 * @param month 1-based
	 */
	private static Calendar makeDate(int year, int month, int day) throws EvaluationException {
		if (month < 1 || month > 12) {
			throw new EvaluationException(ErrorEval.VALUE_INVALID);
		}
		Calendar cal = new GregorianCalendar(year, month-1, 1, 0, 0, 0);
		cal.set(Calendar.MILLISECOND, 0);
		if (day <1 || day>cal.getActualMaximum(Calendar.DAY_OF_MONTH)) {
			throw new EvaluationException(ErrorEval.VALUE_INVALID);
		}
		cal.set(Calendar.DAY_OF_MONTH, day);
		return cal;
	}

	private static int evaluateIntArg(ValueEval arg, int srcCellRow, int srcCellCol) throws EvaluationException {
		ValueEval ve = OperandResolver.getSingleValue(arg, srcCellRow, (short) srcCellCol);
		return OperandResolver.coerceValueToInt(ve);




/*
 * ==================================================================== Licensed to the Apache Software Foundation (ASF) under one or more
 * contributor license agreements. See the NOTICE file distributed with this work for additional information regarding copyright ownership.
 * The ASF licenses this file to You under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance
 * with the License. You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0 Unless required by applicable law or
 * agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 * KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.
 * ====================================================================
 */

       http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.
==================================================================== */

package org.apache.poi.ss.formula.atp;

import java.util.HashMap;


    private final Map<String, FreeRefFunction> _functionsByName = createFunctionsMap();


    private AnalysisToolPak() {
        // enforce singleton
    }

    public FreeRefFunction findFunction(String name) {
        return _functionsByName.get(name.toUpperCase());
    }

    private Map<String, FreeRefFunction> createFunctionsMap() {

        r(m, "MDURATION", null);
        r(m, "MROUND", MRound.instance);
        r(m, "MULTINOMIAL", null);
        r(m, "NETWORKDAYS", NetworkdaysFunction.instance);
        r(m, "NOMINAL", null);
        r(m, "OCT2BIN", null);
        r(m, "OCT2DEC", null);

        r(m, "TBILLPRICE", null);
        r(m, "TBILLYIELD", null);
        r(m, "WEEKNUM", null);
        r(m, "WORKDAY", WorkdayFunction.instance);
        r(m, "XIRR", null);
        r(m, "XNPV", null);
        r(m, "YEARFRAC", YearFrac.instance);




package org.apache.poi.ss.formula.atp;

import static java.util.Calendar.DECEMBER;
import static java.util.Calendar.JANUARY;
import static java.util.Calendar.MARCH;
import static java.util.Calendar.NOVEMBER;
import static java.util.Calendar.OCTOBER;
import static org.apache.poi.ss.formula.eval.ErrorEval.NAME_INVALID;
import static org.apache.poi.ss.formula.eval.ErrorEval.VALUE_INVALID;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import junit.framework.TestCase;

import org.apache.poi.ss.formula.OperationEvaluationContext;
import org.apache.poi.ss.formula.TwoDEval;
import org.apache.poi.ss.formula.eval.AreaEval;
import org.apache.poi.ss.formula.eval.AreaEvalBase;
import org.apache.poi.ss.formula.eval.NumericValueEval;
import org.apache.poi.ss.formula.eval.StringEval;
import org.apache.poi.ss.formula.eval.ValueEval;

/**
 * @author jfaenomoto@gmail.com
 */
public class NetworkdaysFunctionTest extends TestCase {

    private static final SimpleDateFormat formatter = new SimpleDateFormat("yyyy/MM/dd");

    private static final String STARTING_DATE = formatter.format(new Date(108, OCTOBER, 1));

    private static final String END_DATE = formatter.format(new Date(109, MARCH, 1));

    private static final String FIRST_HOLIDAY = formatter.format(new Date(108, NOVEMBER, 26));

    private static final String SECOND_HOLIDAY = formatter.format(new Date(108, DECEMBER, 4));

    private static final String THIRD_HOLIDAY = formatter.format(new Date(109, JANUARY, 21));

    private static final OperationEvaluationContext EC = new OperationEvaluationContext(null, null, 1, 1, 1, null);

    public void testFailWhenNoArguments() {
        assertEquals(VALUE_INVALID, NetworkdaysFunction.instance.evaluate(new ValueEval[0], null));
    }

    public void testFailWhenLessThan2Arguments() {
        assertEquals(VALUE_INVALID, NetworkdaysFunction.instance.evaluate(new ValueEval[1], null));
    }

    public void testFailWhenMoreThan3Arguments() {
        assertEquals(VALUE_INVALID, NetworkdaysFunction.instance.evaluate(new ValueEval[4], null));
    }

    public void testFailWhenArgumentsAreNotDates() {
        assertEquals(VALUE_INVALID, NetworkdaysFunction.instance.evaluate(new ValueEval[]{ new StringEval("Potato"),
                new StringEval("Cucumber") }, EC));
    }

    public void testFailWhenStartDateAfterEndDate() {
        assertEquals(NAME_INVALID, NetworkdaysFunction.instance.evaluate(new ValueEval[]{ new StringEval(END_DATE.toString()),
                new StringEval(STARTING_DATE.toString()) }, EC));
    }

    public void testReturnNetworkdays() {
        assertEquals(108, (int) ((NumericValueEval) NetworkdaysFunction.instance.evaluate(new ValueEval[]{
                new StringEval(STARTING_DATE.toString()), new StringEval(END_DATE.toString()) }, EC)).getNumberValue());
    }

    public void testReturnNetworkdaysWithAHoliday() {
        assertEquals(107, (int) ((NumericValueEval) NetworkdaysFunction.instance.evaluate(new ValueEval[]{
                new StringEval(STARTING_DATE.toString()), new StringEval(END_DATE.toString()), new StringEval(FIRST_HOLIDAY.toString()) },
                EC)).getNumberValue());
    }

    public void testReturnNetworkdaysWithManyHolidays() {
        assertEquals(105, (int) ((NumericValueEval) NetworkdaysFunction.instance.evaluate(new ValueEval[]{
                new StringEval(STARTING_DATE.toString()), new StringEval(END_DATE.toString()),
                new MockAreaEval(FIRST_HOLIDAY, SECOND_HOLIDAY, THIRD_HOLIDAY) }, EC)).getNumberValue());
    }

    private class MockAreaEval extends AreaEvalBase {

        private List<ValueEval> holidays;

        public MockAreaEval(String... holidays) {
            this(0, 0, 0, holidays.length - 1);
            this.holidays = new ArrayList<ValueEval>();
            for (String holiday : holidays) {
                this.holidays.add(new StringEval(holiday));
            }
        }

        protected MockAreaEval(int firstRow, int firstColumn, int lastRow, int lastColumn) {
            super(firstRow, firstColumn, lastRow, lastColumn);
        }

        @Override
        public ValueEval getRelativeValue(int relativeRowIndex, int relativeColumnIndex) {
            return this.holidays.get(relativeColumnIndex);
        }

        public AreaEval offset(int relFirstRowIx, int relLastRowIx, int relFirstColIx, int relLastColIx) {
            return null;
        }

        public TwoDEval getColumn(int columnIndex) {
            return null;
        }

        public TwoDEval getRow(int rowIndex) {
            return null;
        }

    }
}




package org.apache.poi.ss.formula.atp;

import static java.util.Calendar.APRIL;
import static java.util.Calendar.DECEMBER;
import static java.util.Calendar.JANUARY;
import static java.util.Calendar.MAY;
import static java.util.Calendar.NOVEMBER;
import static java.util.Calendar.OCTOBER;
import static java.util.Calendar.SEPTEMBER;
import static org.apache.poi.ss.formula.eval.ErrorEval.NUM_ERROR;
import static org.apache.poi.ss.formula.eval.ErrorEval.VALUE_INVALID;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import junit.framework.TestCase;

import org.apache.poi.ss.formula.OperationEvaluationContext;
import org.apache.poi.ss.formula.TwoDEval;
import org.apache.poi.ss.formula.eval.AreaEval;
import org.apache.poi.ss.formula.eval.AreaEvalBase;
import org.apache.poi.ss.formula.eval.NumberEval;
import org.apache.poi.ss.formula.eval.StringEval;
import org.apache.poi.ss.formula.eval.ValueEval;
import org.apache.poi.ss.usermodel.DateUtil;

/**
 * @author jfaenomoto@gmail.com
 */
public class WorkdayFunctionTest extends TestCase {

    private static final SimpleDateFormat formatter = new SimpleDateFormat("yyyy/MM/dd");

    private static final String STARTING_DATE = formatter.format(new Date(108, OCTOBER, 1));

    private static final String FIRST_HOLIDAY = formatter.format(new Date(108, NOVEMBER, 26));

    private static final String SECOND_HOLIDAY = formatter.format(new Date(108, DECEMBER, 4));

    private static final String THIRD_HOLIDAY = formatter.format(new Date(109, JANUARY, 21));

    private static final String RETROATIVE_HOLIDAY = formatter.format(new Date(108, SEPTEMBER, 29));

    private static final OperationEvaluationContext EC = new OperationEvaluationContext(null, null, 1, 1, 1, null);

    public void testFailWhenNoArguments() {
        assertEquals(VALUE_INVALID, WorkdayFunction.instance.evaluate(new ValueEval[0], null));
    }

    public void testFailWhenLessThan2Arguments() {
        assertEquals(VALUE_INVALID, WorkdayFunction.instance.evaluate(new ValueEval[1], null));
    }

    public void testFailWhenMoreThan3Arguments() {
        assertEquals(VALUE_INVALID, WorkdayFunction.instance.evaluate(new ValueEval[4], null));
    }

    public void testFailWhenArgumentsAreNotDatesNorNumbers() {
        assertEquals(VALUE_INVALID, WorkdayFunction.instance.evaluate(
                new ValueEval[]{ new StringEval("Potato"), new StringEval("Cucumber") }, EC));
    }

    public void testReturnWorkdays() {
        assertEquals(new Date(109, APRIL, 30), DateUtil.getJavaDate(((NumberEval) WorkdayFunction.instance.evaluate(new ValueEval[]{
                new StringEval(STARTING_DATE.toString()), new NumberEval(151) }, EC)).getNumberValue()));
    }

    public void testReturnWorkdaysWithDaysTruncated() {
        assertEquals(new Date(109, APRIL, 30), DateUtil.getJavaDate(((NumberEval) WorkdayFunction.instance.evaluate(new ValueEval[]{
                new StringEval(STARTING_DATE.toString()), new NumberEval(151.99999) }, EC)).getNumberValue()));
    }

    public void testReturnRetroativeWorkday() {
        assertEquals(new Date(108, SEPTEMBER, 23), DateUtil.getJavaDate(((NumberEval) WorkdayFunction.instance.evaluate(new ValueEval[]{
                new StringEval(STARTING_DATE.toString()), new NumberEval(-5), new StringEval(RETROATIVE_HOLIDAY.toString()) }, EC))
                .getNumberValue()));
    }

    public void testReturnNetworkdaysWithManyHolidays() {
        assertEquals(new Date(109, MAY, 5), DateUtil.getJavaDate(((NumberEval) WorkdayFunction.instance.evaluate(new ValueEval[]{
                new StringEval(STARTING_DATE.toString()), new NumberEval(151),
                new MockAreaEval(FIRST_HOLIDAY, SECOND_HOLIDAY, THIRD_HOLIDAY) }, EC)).getNumberValue()));
    }

    private class MockAreaEval extends AreaEvalBase {

        private List<ValueEval> holidays;

        public MockAreaEval(String... holidays) {
            this(0, 0, 0, holidays.length - 1);
            this.holidays = new ArrayList<ValueEval>();
            for (String holiday : holidays) {
                this.holidays.add(new StringEval(holiday));
            }
        }

        protected MockAreaEval(int firstRow, int firstColumn, int lastRow, int lastColumn) {
            super(firstRow, firstColumn, lastRow, lastColumn);
        }

        @Override
        public ValueEval getRelativeValue(int relativeRowIndex, int relativeColumnIndex) {
            return this.holidays.get(relativeColumnIndex);
        }

        public AreaEval offset(int relFirstRowIx, int relLastRowIx, int relFirstColIx, int relLastColIx) {
            return null;
        }

        public TwoDEval getColumn(int columnIndex) {
            return null;
        }

        public TwoDEval getRow(int rowIndex) {
            return null;
        }

    }
}




package org.apache.poi.ss.formula.atp;

import static java.util.Calendar.DECEMBER;
import static java.util.Calendar.SATURDAY;

import java.util.Date;

import junit.framework.TestCase;

import org.apache.poi.ss.usermodel.DateUtil;

/**
 * @author jfaenomoto@gmail.com
 */
public class WorkdayCalculatorTest extends TestCase {

    public void testCalculateWorkdaysShouldReturnJustWeekdaysWhenNoWeekend() {
        final double A_MONDAY = DateUtil.getExcelDate(new Date(111, DECEMBER, 12));
        final double A_FRIDAY = DateUtil.getExcelDate(new Date(111, DECEMBER, 16));
        assertEquals(5, WorkdayCalculator.instance.calculateWorkdays(A_MONDAY, A_FRIDAY, new double[0]));
    }

    public void testCalculateWorkdaysShouldReturnAllDaysButNoSaturdays() {
        final double A_WEDNESDAY = DateUtil.getExcelDate(new Date(111, DECEMBER, 14));
        final double A_SATURDAY = DateUtil.getExcelDate(new Date(111, DECEMBER, 18));
        assertEquals(3, WorkdayCalculator.instance.calculateWorkdays(A_WEDNESDAY, A_SATURDAY, new double[0]));
    }

    public void testCalculateWorkdaysShouldReturnAllDaysButNoSundays() {
        final double A_SUNDAY = DateUtil.getExcelDate(new Date(111, DECEMBER, 11));
        final double A_THURSDAY = DateUtil.getExcelDate(new Date(111, DECEMBER, 15));
        assertEquals(4, WorkdayCalculator.instance.calculateWorkdays(A_SUNDAY, A_THURSDAY, new double[0]));
    }

    public void testCalculateWorkdaysShouldReturnAllDaysButNoHolidays() {
        final double A_MONDAY = DateUtil.getExcelDate(new Date(111, DECEMBER, 12));
        final double A_FRIDAY = DateUtil.getExcelDate(new Date(111, DECEMBER, 16));
        final double A_WEDNESDAY = DateUtil.getExcelDate(new Date(111, DECEMBER, 14));
        assertEquals(4, WorkdayCalculator.instance.calculateWorkdays(A_MONDAY, A_FRIDAY, new double[]{ A_WEDNESDAY }));
    }

    public void testCalculateWorkdaysShouldIgnoreWeekendHolidays() {
        final double A_FRIDAY = DateUtil.getExcelDate(new Date(111, DECEMBER, 16));
        final double A_SATURDAY = DateUtil.getExcelDate(new Date(111, DECEMBER, 17));
        final double A_SUNDAY = DateUtil.getExcelDate(new Date(111, DECEMBER, 18));
        final double A_WEDNESDAY = DateUtil.getExcelDate(new Date(111, DECEMBER, 21));
        assertEquals(4, WorkdayCalculator.instance.calculateWorkdays(A_FRIDAY, A_WEDNESDAY, new double[]{ A_SATURDAY, A_SUNDAY }));
    }

    public void testPastDaysOfWeekShouldReturn0Past0Saturdays() {
        final double A_WEDNESDAY = DateUtil.getExcelDate(new Date(111, DECEMBER, 7));
        final double A_FRIDAY = DateUtil.getExcelDate(new Date(111, DECEMBER, 9));
        assertEquals(0, WorkdayCalculator.instance.pastDaysOfWeek(A_WEDNESDAY, A_FRIDAY, SATURDAY));
    }

    public void testPastDaysOfWeekShouldReturn1Past1Saturdays() {
        final double A_WEDNESDAY = DateUtil.getExcelDate(new Date(111, DECEMBER, 7));
        final double A_SUNDAY = DateUtil.getExcelDate(new Date(111, DECEMBER, 11));
        assertEquals(1, WorkdayCalculator.instance.pastDaysOfWeek(A_WEDNESDAY, A_SUNDAY, SATURDAY));
    }

    public void testPastDaysOfWeekShouldReturn2Past2Saturdays() {
        final double A_THURSDAY = DateUtil.getExcelDate(new Date(111, DECEMBER, 8));
        final double A_MONDAY = DateUtil.getExcelDate(new Date(111, DECEMBER, 19));
        assertEquals(2, WorkdayCalculator.instance.pastDaysOfWeek(A_THURSDAY, A_MONDAY, SATURDAY));
    }

    public void testPastDaysOfWeekShouldReturn1BeginningFromASaturday() {
        final double A_SATURDAY = DateUtil.getExcelDate(new Date(111, DECEMBER, 10));
        final double A_SUNDAY = DateUtil.getExcelDate(new Date(111, DECEMBER, 11));
        assertEquals(1, WorkdayCalculator.instance.pastDaysOfWeek(A_SATURDAY, A_SUNDAY, SATURDAY));
    }

    public void testPastDaysOfWeekShouldReturn1EndingAtASaturday() {
        final double A_THURSDAY = DateUtil.getExcelDate(new Date(111, DECEMBER, 8));
        final double A_SATURDAY = DateUtil.getExcelDate(new Date(111, DECEMBER, 10));
        assertEquals(1, WorkdayCalculator.instance.pastDaysOfWeek(A_THURSDAY, A_SATURDAY, SATURDAY));
    }
}




package org.apache.poi.ss.formula.atp;

import static java.util.Calendar.OCTOBER;

import java.util.Calendar;
import java.util.Date;

import junit.framework.TestCase;

import org.apache.poi.ss.formula.eval.ErrorEval;
import org.apache.poi.ss.formula.eval.EvaluationException;

/**
 * @author jfaenomoto@gmail.com
 */
public class DateParserTest extends TestCase {

    public void testFailWhenNoDate() {
        try {
            DateParser.parseDate("potato");
            fail("Shouldn't parse potato!");
        } catch (EvaluationException e) {
            assertEquals(ErrorEval.VALUE_INVALID, e.getErrorEval());
        }
    }

    public void testFailWhenLooksLikeDateButItIsnt() {
        try {
            DateParser.parseDate("potato/cucumber/banana");
            fail("Shouldn't parse this thing!");
        } catch (EvaluationException e) {
            assertEquals(ErrorEval.VALUE_INVALID, e.getErrorEval());
        }
    }

    public void testFailWhenIsInvalidDate() {
        try {
            DateParser.parseDate("13/13/13");
            fail("Shouldn't parse this thing!");
        } catch (EvaluationException e) {
            assertEquals(ErrorEval.VALUE_INVALID, e.getErrorEval());
        }
    }

    public void testShouldParseValidDate() throws EvaluationException {
        Calendar aDate = Calendar.getInstance();
        aDate.setTime(new Date(84, OCTOBER, 20));
        assertEquals(aDate, DateParser.parseDate("1984/10/20"));
    }

    public void testShouldIgnoreTimestamp() throws EvaluationException {
        Calendar aDate = Calendar.getInstance();
        aDate.setTime(new Date(84, OCTOBER, 20));
        assertEquals(aDate, DateParser.parseDate("1984/10/20 12:34:56"));
    }

}

Return to bug 52378

Lines 91-157 Link Here

(-)src/java/org/apache/poi/ss/formula/atp/YearFrac.java (-56 / +1 lines)
91	if (dVal != null) {	91	if (dVal != null) {
92	return dVal.doubleValue();	92	return dVal.doubleValue();
93	}	93	}
94	Calendar date = parseDate(strVal);	94	Calendar date = DateParser.parseDate(strVal);
95	return DateUtil.getExcelDate(date, false);	95	return DateUtil.getExcelDate(date, false);
96	}	96	}
97	return OperandResolver.coerceValueToDouble(ve);	97	return OperandResolver.coerceValueToDouble(ve);
98	}	98	}
99		99
100	private static Calendar parseDate(String strVal) throws EvaluationException {
101	String[] parts = Pattern.compile("/").split(strVal);
102	if (parts.length != 3) {
103	throw new EvaluationException(ErrorEval.VALUE_INVALID);
104	}
105	String part2 = parts[2];
106	int spacePos = part2.indexOf(' ');
107	if (spacePos > 0) {
108	// drop time portion if present
109	part2 = part2.substring(0, spacePos);
110	}
111	int f0;
112	int f1;
113	int f2;
114	try {
115	f0 = Integer.parseInt(parts[0]);
116	f1 = Integer.parseInt(parts[1]);
117	f2 = Integer.parseInt(part2);
118	} catch (NumberFormatException e) {
119	throw new EvaluationException(ErrorEval.VALUE_INVALID);
120	}
121	if (f0<0 \|\| f1<0 \|\| f2<0 \|\| (f0>12 && f1>12 && f2>12)) {
122	// easy to see this cannot be a valid date
123	throw new EvaluationException(ErrorEval.VALUE_INVALID);
124	}
125
126	if (f0 >= 1900 && f0 < 9999) {
127	// when 4 digit value appears first, the format is YYYY/MM/DD, regardless of OS settings
128	return makeDate(f0, f1, f2);
129	}
130	// otherwise the format seems to depend on OS settings (default date format)
131	if (false) {
132	// MM/DD/YYYY is probably a good guess, if the in the US
133	return makeDate(f2, f0, f1);
134	}
135	// TODO - find a way to choose the correct date format
136	throw new RuntimeException("Unable to determine date format for text '" + strVal + "'");
137	}
138
139	/**
140	* @param month 1-based
141	*/
142	private static Calendar makeDate(int year, int month, int day) throws EvaluationException {
143	if (month < 1 \|\| month > 12) {
144	throw new EvaluationException(ErrorEval.VALUE_INVALID);
145	}
146	Calendar cal = new GregorianCalendar(year, month-1, 1, 0, 0, 0);
147	cal.set(Calendar.MILLISECOND, 0);
148	if (day <1 \|\| day>cal.getActualMaximum(Calendar.DAY_OF_MONTH)) {
149	throw new EvaluationException(ErrorEval.VALUE_INVALID);
150	}
151	cal.set(Calendar.DAY_OF_MONTH, day);
152	return cal;
153	}
154
155	private static int evaluateIntArg(ValueEval arg, int srcCellRow, int srcCellCol) throws EvaluationException {	100	private static int evaluateIntArg(ValueEval arg, int srcCellRow, int srcCellCol) throws EvaluationException {
156	ValueEval ve = OperandResolver.getSingleValue(arg, srcCellRow, (short) srcCellCol);	101	ValueEval ve = OperandResolver.getSingleValue(arg, srcCellRow, (short) srcCellCol);
157	return OperandResolver.coerceValueToInt(ve);	102	return OperandResolver.coerceValueToInt(ve);

Line 0 Link Here

(-)src/java/org/apache/poi/ss/formula/atp/NetworkdaysFunction.java (+66 lines)
		1	package org.apache.poi.ss.formula.atp;
		2
		3	import org.apache.poi.ss.formula.OperationEvaluationContext;
		4	import org.apache.poi.ss.formula.eval.ErrorEval;
		5	import org.apache.poi.ss.formula.eval.EvaluationException;
		6	import org.apache.poi.ss.formula.eval.NumberEval;
		7	import org.apache.poi.ss.formula.eval.ValueEval;
		8	import org.apache.poi.ss.formula.functions.FreeRefFunction;
		9
		10	/**
		11	* Implementation of Excel 'Analysis ToolPak' function NETWORKDAYS()<br/>
		12	* Returns the number of workdays given a starting and an ending date, considering an interval of holidays. A workday is any non
		13	* saturday/sunday date.
		14	* <p/>
		15	* <b>Syntax</b><br/>
		16	* <b>NETWORKDAYS</b>(<b>startDate</b>, <b>endDate</b>, holidays)
		17	* <p/>
		18	*
		19	* @author jfaenomoto@gmail.com
		20	*/
		21	final class NetworkdaysFunction implements FreeRefFunction {
		22
		23	public static final FreeRefFunction instance = new NetworkdaysFunction(ArgumentsEvaluator.instance);
		24
		25	private ArgumentsEvaluator evaluator;
		26
		27	/**
		28	* Constructor.
		29	*
		30	* @param anEvaluator an injected {@link ArgumentsEvaluator}.
		31	*/
		32	private NetworkdaysFunction(ArgumentsEvaluator anEvaluator) {
		33	// enforces singleton
		34	this.evaluator = anEvaluator;
		35	}
		36
		37	/**
		38	* Evaluate for NETWORKDAYS. Given two dates and a optional date or interval of holidays, determines how many working days are there
		39	* between those dates.
		40	*
		41	* @return {@link ValueEval} for the number of days between two dates.
		42	*/
		43	public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) {
		44	if (args.length < 2 \|\| args.length > 3) {
		45	return ErrorEval.VALUE_INVALID;
		46	}
		47
		48	int srcCellRow = ec.getRowIndex();
		49	int srcCellCol = ec.getColumnIndex();
		50
		51	double start, end;
		52	double[] holidays;
		53	try {
		54	start = this.evaluator.evaluateDateArg(args[0], srcCellRow, srcCellCol);
		55	end = this.evaluator.evaluateDateArg(args[1], srcCellRow, srcCellCol);
		56	if (start > end) {
		57	return ErrorEval.NAME_INVALID;
		58	}
		59	ValueEval holidaysCell = args.length == 3 ? args[2] : null;
		60	holidays = this.evaluator.evaluateDatesArg(holidaysCell, srcCellRow, srcCellCol);
		61	return new NumberEval(WorkdayCalculator.instance.calculateWorkdays(start, end, holidays));
		62	} catch (EvaluationException e) {
		63	return ErrorEval.VALUE_INVALID;
		64	}
65	}
66	}

Lines 21-32 Link Here

(-)src/java/org/apache/poi/ss/formula/atp/MRound.java (-5 lines)
21	import org.apache.poi.ss.formula.eval.*;	21	import org.apache.poi.ss.formula.eval.*;
22	import org.apache.poi.ss.formula.functions.FreeRefFunction;	22	import org.apache.poi.ss.formula.functions.FreeRefFunction;
23	import org.apache.poi.ss.formula.functions.NumericFunction;	23	import org.apache.poi.ss.formula.functions.NumericFunction;
24	import org.apache.poi.ss.usermodel.DateUtil;
25		24
26	import java.util.Calendar;
27	import java.util.GregorianCalendar;
28	import java.util.regex.Pattern;
29
30	/**	25	/**
31	* Implementation of Excel 'Analysis ToolPak' function MROUND()<br/>	26	* Implementation of Excel 'Analysis ToolPak' function MROUND()<br/>
32	*	27	*

Line 0 Link Here

(-)src/java/org/apache/poi/ss/formula/atp/ArgumentsEvaluator.java (+100 lines)
		1	package org.apache.poi.ss.formula.atp;
		2
		3	import java.util.ArrayList;
		4	import java.util.Calendar;
		5	import java.util.List;
		6
		7	import org.apache.poi.ss.formula.eval.AreaEvalBase;
		8	import org.apache.poi.ss.formula.eval.EvaluationException;
		9	import org.apache.poi.ss.formula.eval.OperandResolver;
		10	import org.apache.poi.ss.formula.eval.StringEval;
		11	import org.apache.poi.ss.formula.eval.ValueEval;
		12	import org.apache.poi.ss.usermodel.DateUtil;
		13
		14	/**
		15	* Evaluator for formula arguments.
		16	*
		17	* @author jfaenomoto@gmail.com
		18	*/
		19	final class ArgumentsEvaluator {
		20
		21	public static final ArgumentsEvaluator instance = new ArgumentsEvaluator();
		22
		23	private ArgumentsEvaluator() {
		24	// enforces singleton
		25	}
		26
		27	/**
		28	* Evaluate a generic {@link ValueEval} argument to a double value that represents a date in POI.
		29	*
		30	* @param arg {@link ValueEval} an argument.
		31	* @param srcCellRow number cell row.
		32	* @param srcCellCol number cell column.
		33	* @return a double representing a date in POI.
		34	* @throws EvaluationException exception upon argument evaluation.
		35	*/
		36	public double evaluateDateArg(ValueEval arg, int srcCellRow, int srcCellCol) throws EvaluationException {
		37	ValueEval ve = OperandResolver.getSingleValue(arg, srcCellRow, (short) srcCellCol);
		38
		39	if (ve instanceof StringEval) {
		40	String strVal = ((StringEval) ve).getStringValue();
		41	Double dVal = OperandResolver.parseDouble(strVal);
		42	if (dVal != null) {
		43	return dVal.doubleValue();
		44	}
		45	Calendar date = DateParser.parseDate(strVal);
		46	return DateUtil.getExcelDate(date, false);
		47	}
		48	return OperandResolver.coerceValueToDouble(ve);
		49	}
		50
		51	/**
		52	* Evaluate a generic {@link ValueEval} argument to an array of double values that represents dates in POI.
		53	*
		54	* @param arg {@link ValueEval} an argument.
		55	* @param srcCellRow number cell row.
		56	* @param srcCellCol number cell column.
		57	* @return an array of doubles representing dates in POI.
		58	* @throws EvaluationException exception upon argument evaluation.
		59	*/
		60	public double[] evaluateDatesArg(ValueEval arg, int srcCellRow, int srcCellCol) throws EvaluationException {
		61	if (arg == null) {
		62	return new double[0];
		63	}
		64
65	if (arg instanceof StringEval) {
66	return new double[]{ evaluateDateArg(arg, srcCellRow, srcCellCol) };
67	} else if (arg instanceof AreaEvalBase) {
68	List<Double> valuesList = new ArrayList<Double>();
69	AreaEvalBase area = (AreaEvalBase) arg;
70	for (int i = area.getFirstRow(); i <= area.getLastRow(); i++) {
71	for (int j = area.getFirstColumn(); j <= area.getLastColumn(); j++) {
72	valuesList.add(evaluateDateArg(area.getValue(i, j), i, j));
73	}
74	}
75	double[] values = new double[valuesList.size()];
76	for (int i = 0; i < valuesList.size(); i++) {
77	values[i] = valuesList.get(i).doubleValue();
78	}
79	return values;
80	}
81	return new double[]{ OperandResolver.coerceValueToDouble(arg) };
82	}
83
84	/**
85	* Evaluate a generic {@link ValueEval} argument to a double value.
86	*
87	* @param arg {@link ValueEval} an argument.
88	* @param srcCellRow number cell row.
89	* @param srcCellCol number cell column.
90	* @return a double value.
91	* @throws EvaluationException exception upon argument evaluation.
92	*/
93	public double evaluateNumberArg(ValueEval arg, int srcCellRow, int srcCellCol) throws EvaluationException {
94	if (arg == null) {
95	return 0f;
96	}
97
98	return OperandResolver.coerceValueToDouble(arg);
99	}
100	}

Lines 1-20 Link Here

(-)src/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java (-20 / +12 lines)
1	/* ====================================================================	1	/*
2	Licensed to the Apache Software Foundation (ASF) under one or more	2	* ==================================================================== Licensed to the Apache Software Foundation (ASF) under one or more
3	contributor license agreements. See the NOTICE file distributed with	3	* contributor license agreements. See the NOTICE file distributed with this work for additional information regarding copyright ownership.
4	this work for additional information regarding copyright ownership.	4	* The ASF licenses this file to You under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance
5	The ASF licenses this file to You under the Apache License, Version 2.0	5	* with the License. You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0 Unless required by applicable law or
6	(the "License"); you may not use this file except in compliance with	6	* agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
7	the License. You may obtain a copy of the License at	7	* KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.
		8	* ====================================================================
		9	*/
8		10
9	http://www.apache.org/licenses/LICENSE-2.0
10
11	Unless required by applicable law or agreed to in writing, software
12	distributed under the License is distributed on an "AS IS" BASIS,
13	WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
14	See the License for the specific language governing permissions and
15	limitations under the License.
16	==================================================================== */
17
18	package org.apache.poi.ss.formula.atp;	11	package org.apache.poi.ss.formula.atp;
19		12
20	import java.util.HashMap;	13	import java.util.HashMap;
Lines 50-62 Link Here
50		43
51	private final Map<String, FreeRefFunction> _functionsByName = createFunctionsMap();	44	private final Map<String, FreeRefFunction> _functionsByName = createFunctionsMap();
52		45
53
54	private AnalysisToolPak() {	46	private AnalysisToolPak() {
55	// enforce singleton	47	// enforce singleton
56	}	48	}
57		49
58	public FreeRefFunction findFunction(String name) {	50	public FreeRefFunction findFunction(String name) {
59	return _functionsByName.get(name);	51	return _functionsByName.get(name.toUpperCase());
60	}	52	}
61		53
62	private Map<String, FreeRefFunction> createFunctionsMap() {	54	private Map<String, FreeRefFunction> createFunctionsMap() {
Lines 140-146 Link Here
140	r(m, "MDURATION", null);	132	r(m, "MDURATION", null);
141	r(m, "MROUND", MRound.instance);	133	r(m, "MROUND", MRound.instance);
142	r(m, "MULTINOMIAL", null);	134	r(m, "MULTINOMIAL", null);
143	r(m, "NETWORKDAYS", null);	135	r(m, "NETWORKDAYS", NetworkdaysFunction.instance);
144	r(m, "NOMINAL", null);	136	r(m, "NOMINAL", null);
145	r(m, "OCT2BIN", null);	137	r(m, "OCT2BIN", null);
146	r(m, "OCT2DEC", null);	138	r(m, "OCT2DEC", null);
Lines 163-169 Link Here
163	r(m, "TBILLPRICE", null);	155	r(m, "TBILLPRICE", null);
164	r(m, "TBILLYIELD", null);	156	r(m, "TBILLYIELD", null);
165	r(m, "WEEKNUM", null);	157	r(m, "WEEKNUM", null);
166	r(m, "WORKDAY", null);	158	r(m, "WORKDAY", WorkdayFunction.instance);
167	r(m, "XIRR", null);	159	r(m, "XIRR", null);
168	r(m, "XNPV", null);	160	r(m, "XNPV", null);
169	r(m, "YEARFRAC", YearFrac.instance);	161	r(m, "YEARFRAC", YearFrac.instance);

Line 0 Link Here

(-)src/testcases/org/apache/poi/ss/formula/atp/NetworkdaysFunctionTest.java (+118 lines)
		1	package org.apache.poi.ss.formula.atp;
		2
		3	import static java.util.Calendar.DECEMBER;
		4	import static java.util.Calendar.JANUARY;
		5	import static java.util.Calendar.MARCH;
		6	import static java.util.Calendar.NOVEMBER;
		7	import static java.util.Calendar.OCTOBER;
		8	import static org.apache.poi.ss.formula.eval.ErrorEval.NAME_INVALID;
		9	import static org.apache.poi.ss.formula.eval.ErrorEval.VALUE_INVALID;
		10
		11	import java.text.SimpleDateFormat;
		12	import java.util.ArrayList;
		13	import java.util.Date;
		14	import java.util.List;
		15
		16	import junit.framework.TestCase;
		17
		18	import org.apache.poi.ss.formula.OperationEvaluationContext;
		19	import org.apache.poi.ss.formula.TwoDEval;
		20	import org.apache.poi.ss.formula.eval.AreaEval;
		21	import org.apache.poi.ss.formula.eval.AreaEvalBase;
		22	import org.apache.poi.ss.formula.eval.NumericValueEval;
		23	import org.apache.poi.ss.formula.eval.StringEval;
		24	import org.apache.poi.ss.formula.eval.ValueEval;
		25
		26	/**
		27	* @author jfaenomoto@gmail.com
		28	*/
		29	public class NetworkdaysFunctionTest extends TestCase {
		30
		31	private static final SimpleDateFormat formatter = new SimpleDateFormat("yyyy/MM/dd");
		32
		33	private static final String STARTING_DATE = formatter.format(new Date(108, OCTOBER, 1));
		34
		35	private static final String END_DATE = formatter.format(new Date(109, MARCH, 1));
		36
		37	private static final String FIRST_HOLIDAY = formatter.format(new Date(108, NOVEMBER, 26));
		38
		39	private static final String SECOND_HOLIDAY = formatter.format(new Date(108, DECEMBER, 4));
		40
		41	private static final String THIRD_HOLIDAY = formatter.format(new Date(109, JANUARY, 21));
		42
		43	private static final OperationEvaluationContext EC = new OperationEvaluationContext(null, null, 1, 1, 1, null);
		44
		45	public void testFailWhenNoArguments() {
		46	assertEquals(VALUE_INVALID, NetworkdaysFunction.instance.evaluate(new ValueEval[0], null));
		47	}
		48
		49	public void testFailWhenLessThan2Arguments() {
		50	assertEquals(VALUE_INVALID, NetworkdaysFunction.instance.evaluate(new ValueEval[1], null));
		51	}
		52
		53	public void testFailWhenMoreThan3Arguments() {
		54	assertEquals(VALUE_INVALID, NetworkdaysFunction.instance.evaluate(new ValueEval[4], null));
		55	}
		56
		57	public void testFailWhenArgumentsAreNotDates() {
		58	assertEquals(VALUE_INVALID, NetworkdaysFunction.instance.evaluate(new ValueEval[]{ new StringEval("Potato"),
		59	new StringEval("Cucumber") }, EC));
		60	}
		61
		62	public void testFailWhenStartDateAfterEndDate() {
		63	assertEquals(NAME_INVALID, NetworkdaysFunction.instance.evaluate(new ValueEval[]{ new StringEval(END_DATE.toString()),
		64	new StringEval(STARTING_DATE.toString()) }, EC));
65	}
66
67	public void testReturnNetworkdays() {
68	assertEquals(108, (int) ((NumericValueEval) NetworkdaysFunction.instance.evaluate(new ValueEval[]{
69	new StringEval(STARTING_DATE.toString()), new StringEval(END_DATE.toString()) }, EC)).getNumberValue());
70	}
71
72	public void testReturnNetworkdaysWithAHoliday() {
73	assertEquals(107, (int) ((NumericValueEval) NetworkdaysFunction.instance.evaluate(new ValueEval[]{
74	new StringEval(STARTING_DATE.toString()), new StringEval(END_DATE.toString()), new StringEval(FIRST_HOLIDAY.toString()) },
75	EC)).getNumberValue());
76	}
77
78	public void testReturnNetworkdaysWithManyHolidays() {
79	assertEquals(105, (int) ((NumericValueEval) NetworkdaysFunction.instance.evaluate(new ValueEval[]{
80	new StringEval(STARTING_DATE.toString()), new StringEval(END_DATE.toString()),
81	new MockAreaEval(FIRST_HOLIDAY, SECOND_HOLIDAY, THIRD_HOLIDAY) }, EC)).getNumberValue());
82	}
83
84	private class MockAreaEval extends AreaEvalBase {
85
86	private List<ValueEval> holidays;
87
88	public MockAreaEval(String... holidays) {
89	this(0, 0, 0, holidays.length - 1);
90	this.holidays = new ArrayList<ValueEval>();
91	for (String holiday : holidays) {
92	this.holidays.add(new StringEval(holiday));
93	}
94	}
95
96	protected MockAreaEval(int firstRow, int firstColumn, int lastRow, int lastColumn) {
97	super(firstRow, firstColumn, lastRow, lastColumn);
98	}
99
100	@Override
101	public ValueEval getRelativeValue(int relativeRowIndex, int relativeColumnIndex) {
102	return this.holidays.get(relativeColumnIndex);
103	}
104
105	public AreaEval offset(int relFirstRowIx, int relLastRowIx, int relFirstColIx, int relLastColIx) {
106	return null;
107	}
108
109	public TwoDEval getColumn(int columnIndex) {
110	return null;
111	}
112
113	public TwoDEval getRow(int rowIndex) {
114	return null;
115	}
116
117	}
118	}

Line 0 Link Here

(-)src/testcases/org/apache/poi/ss/formula/atp/WorkdayFunctionTest.java (+122 lines)
		1	package org.apache.poi.ss.formula.atp;
		2
		3	import static java.util.Calendar.APRIL;
		4	import static java.util.Calendar.DECEMBER;
		5	import static java.util.Calendar.JANUARY;
		6	import static java.util.Calendar.MAY;
		7	import static java.util.Calendar.NOVEMBER;
		8	import static java.util.Calendar.OCTOBER;
		9	import static java.util.Calendar.SEPTEMBER;
		10	import static org.apache.poi.ss.formula.eval.ErrorEval.NUM_ERROR;
		11	import static org.apache.poi.ss.formula.eval.ErrorEval.VALUE_INVALID;
		12
		13	import java.text.SimpleDateFormat;
		14	import java.util.ArrayList;
		15	import java.util.Calendar;
		16	import java.util.Date;
		17	import java.util.List;
		18
		19	import junit.framework.TestCase;
		20
		21	import org.apache.poi.ss.formula.OperationEvaluationContext;
		22	import org.apache.poi.ss.formula.TwoDEval;
		23	import org.apache.poi.ss.formula.eval.AreaEval;
		24	import org.apache.poi.ss.formula.eval.AreaEvalBase;
		25	import org.apache.poi.ss.formula.eval.NumberEval;
		26	import org.apache.poi.ss.formula.eval.StringEval;
		27	import org.apache.poi.ss.formula.eval.ValueEval;
		28	import org.apache.poi.ss.usermodel.DateUtil;
		29
		30	/**
		31	* @author jfaenomoto@gmail.com
		32	*/
		33	public class WorkdayFunctionTest extends TestCase {
		34
		35	private static final SimpleDateFormat formatter = new SimpleDateFormat("yyyy/MM/dd");
		36
		37	private static final String STARTING_DATE = formatter.format(new Date(108, OCTOBER, 1));
		38
		39	private static final String FIRST_HOLIDAY = formatter.format(new Date(108, NOVEMBER, 26));
		40
		41	private static final String SECOND_HOLIDAY = formatter.format(new Date(108, DECEMBER, 4));
		42
		43	private static final String THIRD_HOLIDAY = formatter.format(new Date(109, JANUARY, 21));
		44
		45	private static final String RETROATIVE_HOLIDAY = formatter.format(new Date(108, SEPTEMBER, 29));
		46
		47	private static final OperationEvaluationContext EC = new OperationEvaluationContext(null, null, 1, 1, 1, null);
		48
		49	public void testFailWhenNoArguments() {
		50	assertEquals(VALUE_INVALID, WorkdayFunction.instance.evaluate(new ValueEval[0], null));
		51	}
		52
		53	public void testFailWhenLessThan2Arguments() {
		54	assertEquals(VALUE_INVALID, WorkdayFunction.instance.evaluate(new ValueEval[1], null));
		55	}
		56
		57	public void testFailWhenMoreThan3Arguments() {
		58	assertEquals(VALUE_INVALID, WorkdayFunction.instance.evaluate(new ValueEval[4], null));
		59	}
		60
		61	public void testFailWhenArgumentsAreNotDatesNorNumbers() {
		62	assertEquals(VALUE_INVALID, WorkdayFunction.instance.evaluate(
		63	new ValueEval[]{ new StringEval("Potato"), new StringEval("Cucumber") }, EC));
		64	}
65
66	public void testReturnWorkdays() {
67	assertEquals(new Date(109, APRIL, 30), DateUtil.getJavaDate(((NumberEval) WorkdayFunction.instance.evaluate(new ValueEval[]{
68	new StringEval(STARTING_DATE.toString()), new NumberEval(151) }, EC)).getNumberValue()));
69	}
70
71	public void testReturnWorkdaysWithDaysTruncated() {
72	assertEquals(new Date(109, APRIL, 30), DateUtil.getJavaDate(((NumberEval) WorkdayFunction.instance.evaluate(new ValueEval[]{
73	new StringEval(STARTING_DATE.toString()), new NumberEval(151.99999) }, EC)).getNumberValue()));
74	}
75
76	public void testReturnRetroativeWorkday() {
77	assertEquals(new Date(108, SEPTEMBER, 23), DateUtil.getJavaDate(((NumberEval) WorkdayFunction.instance.evaluate(new ValueEval[]{
78	new StringEval(STARTING_DATE.toString()), new NumberEval(-5), new StringEval(RETROATIVE_HOLIDAY.toString()) }, EC))
79	.getNumberValue()));
80	}
81
82	public void testReturnNetworkdaysWithManyHolidays() {
83	assertEquals(new Date(109, MAY, 5), DateUtil.getJavaDate(((NumberEval) WorkdayFunction.instance.evaluate(new ValueEval[]{
84	new StringEval(STARTING_DATE.toString()), new NumberEval(151),
85	new MockAreaEval(FIRST_HOLIDAY, SECOND_HOLIDAY, THIRD_HOLIDAY) }, EC)).getNumberValue()));
86	}
87
88	private class MockAreaEval extends AreaEvalBase {
89
90	private List<ValueEval> holidays;
91
92	public MockAreaEval(String... holidays) {
93	this(0, 0, 0, holidays.length - 1);
94	this.holidays = new ArrayList<ValueEval>();
95	for (String holiday : holidays) {
96	this.holidays.add(new StringEval(holiday));
97	}
98	}
99
100	protected MockAreaEval(int firstRow, int firstColumn, int lastRow, int lastColumn) {
101	super(firstRow, firstColumn, lastRow, lastColumn);
102	}
103
104	@Override
105	public ValueEval getRelativeValue(int relativeRowIndex, int relativeColumnIndex) {
106	return this.holidays.get(relativeColumnIndex);
107	}
108
109	public AreaEval offset(int relFirstRowIx, int relLastRowIx, int relFirstColIx, int relLastColIx) {
110	return null;
111	}
112
113	public TwoDEval getColumn(int columnIndex) {
114	return null;
115	}
116
117	public TwoDEval getRow(int rowIndex) {
118	return null;
119	}
120
121	}
122	}