You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by ye...@apache.org on 2011/12/21 12:40:49 UTC
svn commit: r1221682 - in /poi/trunk/src: documentation/content/xdocs/
java/org/apache/poi/ss/formula/atp/ testcases/org/apache/poi/ss/formula/atp/
Author: yegor
Date: Wed Dec 21 11:40:48 2011
New Revision: 1221682
URL: http://svn.apache.org/viewvc?rev=1221682&view=rev
Log:
Bugzill 52378: Support for Excel's Analysis Tool Pack functions workday and networkdays
Added:
poi/trunk/src/java/org/apache/poi/ss/formula/atp/ArgumentsEvaluator.java
poi/trunk/src/java/org/apache/poi/ss/formula/atp/DateParser.java
poi/trunk/src/java/org/apache/poi/ss/formula/atp/NetworkdaysFunction.java
poi/trunk/src/java/org/apache/poi/ss/formula/atp/WorkdayCalculator.java
poi/trunk/src/java/org/apache/poi/ss/formula/atp/WorkdayFunction.java
poi/trunk/src/testcases/org/apache/poi/ss/formula/atp/TestDateParser.java
poi/trunk/src/testcases/org/apache/poi/ss/formula/atp/TestNetworkdaysFunction.java
poi/trunk/src/testcases/org/apache/poi/ss/formula/atp/TestWorkdayCalculator.java
poi/trunk/src/testcases/org/apache/poi/ss/formula/atp/TestWorkdayFunction.java
Modified:
poi/trunk/src/documentation/content/xdocs/status.xml
poi/trunk/src/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java
poi/trunk/src/java/org/apache/poi/ss/formula/atp/MRound.java
poi/trunk/src/java/org/apache/poi/ss/formula/atp/YearFrac.java
Modified: poi/trunk/src/documentation/content/xdocs/status.xml
URL: http://svn.apache.org/viewvc/poi/trunk/src/documentation/content/xdocs/status.xml?rev=1221682&r1=1221681&r2=1221682&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/status.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/status.xml Wed Dec 21 11:40:48 2011
@@ -34,6 +34,7 @@
<changes>
<release version="3.8-beta6" date="2012-??-??">
+ <action dev="poi-developers" type="add">52378 - Support for WORKDAY and NETWORKDAYS functions</action>
<action dev="poi-developers" type="add">52349 - Merge the logic between the TEXT function and DataFormatter</action>
<action dev="poi-developers" type="fix">52349 - Correctly support excel style date format strings in the TEXT function</action>
<action dev="poi-developers" type="fix">52369 - XSSFExcelExtractor should format numeric cells based on the format strings applied to them</action>
Modified: poi/trunk/src/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java?rev=1221682&r1=1221681&r2=1221682&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java Wed Dec 21 11:40:48 2011
@@ -1,19 +1,12 @@
-/* ====================================================================
- 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.
-==================================================================== */
+/*
+ * ==================================================================== 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.
+ * ====================================================================
+ */
package org.apache.poi.ss.formula.atp;
@@ -50,13 +43,12 @@ public final class AnalysisToolPak imple
private final Map<String, FreeRefFunction> _functionsByName = createFunctionsMap();
-
private AnalysisToolPak() {
// enforce singleton
}
public FreeRefFunction findFunction(String name) {
- return _functionsByName.get(name);
+ return _functionsByName.get(name.toUpperCase());
}
private Map<String, FreeRefFunction> createFunctionsMap() {
@@ -140,7 +132,7 @@ public final class AnalysisToolPak imple
r(m, "MDURATION", null);
r(m, "MROUND", MRound.instance);
r(m, "MULTINOMIAL", null);
- r(m, "NETWORKDAYS", null);
+ r(m, "NETWORKDAYS", NetworkdaysFunction.instance);
r(m, "NOMINAL", null);
r(m, "OCT2BIN", null);
r(m, "OCT2DEC", null);
@@ -163,7 +155,7 @@ public final class AnalysisToolPak imple
r(m, "TBILLPRICE", null);
r(m, "TBILLYIELD", null);
r(m, "WEEKNUM", null);
- r(m, "WORKDAY", null);
+ r(m, "WORKDAY", WorkdayFunction.instance);
r(m, "XIRR", null);
r(m, "XNPV", null);
r(m, "YEARFRAC", YearFrac.instance);
Added: poi/trunk/src/java/org/apache/poi/ss/formula/atp/ArgumentsEvaluator.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/atp/ArgumentsEvaluator.java?rev=1221682&view=auto
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/atp/ArgumentsEvaluator.java (added)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/atp/ArgumentsEvaluator.java Wed Dec 21 11:40:48 2011
@@ -0,0 +1,117 @@
+/* ====================================================================
+ 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.
+==================================================================== */
+
+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);
+ }
+}
Added: poi/trunk/src/java/org/apache/poi/ss/formula/atp/DateParser.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/atp/DateParser.java?rev=1221682&view=auto
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/atp/DateParser.java (added)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/atp/DateParser.java Wed Dec 21 11:40:48 2011
@@ -0,0 +1,102 @@
+/* ====================================================================
+ 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.
+==================================================================== */
+
+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;
+ }
+
+}
Modified: poi/trunk/src/java/org/apache/poi/ss/formula/atp/MRound.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/atp/MRound.java?rev=1221682&r1=1221681&r2=1221682&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/atp/MRound.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/atp/MRound.java Wed Dec 21 11:40:48 2011
@@ -21,11 +21,6 @@ import org.apache.poi.ss.formula.Operati
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/>
Added: poi/trunk/src/java/org/apache/poi/ss/formula/atp/NetworkdaysFunction.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/atp/NetworkdaysFunction.java?rev=1221682&view=auto
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/atp/NetworkdaysFunction.java (added)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/atp/NetworkdaysFunction.java Wed Dec 21 11:40:48 2011
@@ -0,0 +1,84 @@
+/* ====================================================================
+ 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.
+==================================================================== */
+
+
+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;
+ }
+ }
+}
Added: poi/trunk/src/java/org/apache/poi/ss/formula/atp/WorkdayCalculator.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/atp/WorkdayCalculator.java?rev=1221682&view=auto
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/atp/WorkdayCalculator.java (added)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/atp/WorkdayCalculator.java Wed Dec 21 11:40:48 2011
@@ -0,0 +1,168 @@
+/* ====================================================================
+ 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.
+==================================================================== */
+
+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;
+ }
+}
Added: poi/trunk/src/java/org/apache/poi/ss/formula/atp/WorkdayFunction.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/atp/WorkdayFunction.java?rev=1221682&view=auto
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/atp/WorkdayFunction.java (added)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/atp/WorkdayFunction.java Wed Dec 21 11:40:48 2011
@@ -0,0 +1,78 @@
+/* ====================================================================
+ 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.
+==================================================================== */
+
+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;
+ }
+ }
+
+}
Modified: poi/trunk/src/java/org/apache/poi/ss/formula/atp/YearFrac.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/atp/YearFrac.java?rev=1221682&r1=1221681&r2=1221682&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/atp/YearFrac.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/atp/YearFrac.java Wed Dec 21 11:40:48 2011
@@ -91,67 +91,12 @@ final class YearFrac implements FreeRefF
if (dVal != null) {
return dVal.doubleValue();
}
- Calendar date = parseDate(strVal);
+ 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);
Added: poi/trunk/src/testcases/org/apache/poi/ss/formula/atp/TestDateParser.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/formula/atp/TestDateParser.java?rev=1221682&view=auto
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/ss/formula/atp/TestDateParser.java (added)
+++ poi/trunk/src/testcases/org/apache/poi/ss/formula/atp/TestDateParser.java Wed Dec 21 11:40:48 2011
@@ -0,0 +1,74 @@
+/* ====================================================================
+ 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.
+==================================================================== */
+
+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 TestDateParser 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"));
+ }
+
+}
Added: poi/trunk/src/testcases/org/apache/poi/ss/formula/atp/TestNetworkdaysFunction.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/formula/atp/TestNetworkdaysFunction.java?rev=1221682&view=auto
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/ss/formula/atp/TestNetworkdaysFunction.java (added)
+++ poi/trunk/src/testcases/org/apache/poi/ss/formula/atp/TestNetworkdaysFunction.java Wed Dec 21 11:40:48 2011
@@ -0,0 +1,135 @@
+/* ====================================================================
+ 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.
+==================================================================== */
+
+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 TestNetworkdaysFunction 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;
+ }
+
+ }
+}
Added: poi/trunk/src/testcases/org/apache/poi/ss/formula/atp/TestWorkdayCalculator.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/formula/atp/TestWorkdayCalculator.java?rev=1221682&view=auto
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/ss/formula/atp/TestWorkdayCalculator.java (added)
+++ poi/trunk/src/testcases/org/apache/poi/ss/formula/atp/TestWorkdayCalculator.java Wed Dec 21 11:40:48 2011
@@ -0,0 +1,96 @@
+/* ====================================================================
+ 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.
+==================================================================== */
+
+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 TestWorkdayCalculator 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));
+ }
+}
Added: poi/trunk/src/testcases/org/apache/poi/ss/formula/atp/TestWorkdayFunction.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/formula/atp/TestWorkdayFunction.java?rev=1221682&view=auto
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/ss/formula/atp/TestWorkdayFunction.java (added)
+++ poi/trunk/src/testcases/org/apache/poi/ss/formula/atp/TestWorkdayFunction.java Wed Dec 21 11:40:48 2011
@@ -0,0 +1,140 @@
+/* ====================================================================
+ 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.
+==================================================================== */
+
+
+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 TestWorkdayFunction 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;
+ }
+
+ }
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org