You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by ce...@apache.org on 2014/10/27 13:29:32 UTC
svn commit: r1634515 - in /poi/trunk/src: java/org/apache/poi/ss/formula/atp/
java/org/apache/poi/ss/formula/functions/
testcases/org/apache/poi/ss/formula/functions/
Author: cedricwalter
Date: Mon Oct 27 12:29:32 2014
New Revision: 1634515
URL: http://svn.apache.org/r1634515
Log:
Bug 57150: Added EOMONTH function
Added:
poi/trunk/src/java/org/apache/poi/ss/formula/functions/EOMonth.java
poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/RefValueImplementation.java
poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestEOMonth.java
Modified:
poi/trunk/src/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java
poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestEDate.java
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=1634515&r1=1634514&r2=1634515&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 Mon Oct 27 12:29:32 2014
@@ -28,6 +28,7 @@ import org.apache.poi.ss.formula.functio
import org.apache.poi.ss.formula.functions.Dec2Hex;
import org.apache.poi.ss.formula.functions.Delta;
import org.apache.poi.ss.formula.functions.EDate;
+import org.apache.poi.ss.formula.functions.EOMonth;
import org.apache.poi.ss.formula.functions.FactDouble;
import org.apache.poi.ss.formula.functions.FreeRefFunction;
import org.apache.poi.ss.formula.functions.Hex2Dec;
@@ -117,7 +118,7 @@ public final class AnalysisToolPak imple
r(m, "DURATION", null);
r(m, "EDATE", EDate.instance);
r(m, "EFFECT", null);
- r(m, "EOMONTH", null);
+ r(m, "EOMONTH", EOMonth.instance);
r(m, "ERF", null);
r(m, "ERFC", null);
r(m, "FACTDOUBLE", FactDouble.instance);
Added: poi/trunk/src/java/org/apache/poi/ss/formula/functions/EOMonth.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/functions/EOMonth.java?rev=1634515&view=auto
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/functions/EOMonth.java (added)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/functions/EOMonth.java Mon Oct 27 12:29:32 2014
@@ -0,0 +1,81 @@
+/* ====================================================================
+ 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.functions;
+
+import java.util.Calendar;
+import java.util.Date;
+import java.util.GregorianCalendar;
+
+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.usermodel.DateUtil;
+
+/**
+ * Implementation for the Excel EOMONTH() function.<p/>
+ * <p/>
+ * EOMONTH() returns the date of the last day of a month..<p/>
+ * <p/>
+ * <b>Syntax</b>:<br/>
+ * <b>EOMONTH</b>(<b>start_date</b>,<b>months</b>)<p/>
+ * <p/>
+ * <b>start_date</b> is the starting date of the calculation
+ * <b>months</b> is the number of months to be added to <b>start_date</b>,
+ * to give a new date. For this new date, <b>EOMONTH</b> returns the date of
+ * the last day of the month. <b>months</b> may be positive (in the future),
+ * zero or negative (in the past).
+ */
+public class EOMonth implements FreeRefFunction {
+
+ public static final FreeRefFunction instance = new EOMonth();
+
+ @Override
+ public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) {
+ if (args.length != 2) {
+ return ErrorEval.VALUE_INVALID;
+ }
+
+ try {
+ double startDateAsNumber = NumericFunction.singleOperandEvaluate(args[0], ec.getRowIndex(), ec.getColumnIndex());
+ int months = (int) NumericFunction.singleOperandEvaluate(args[1], ec.getRowIndex(), ec.getColumnIndex());
+
+ // Excel treats date 0 as 1900-01-00; EOMONTH results in 1900-01-31
+ if (startDateAsNumber >= 0.0 && startDateAsNumber < 1.0) {
+ startDateAsNumber = 1.0;
+ }
+
+ Date startDate = DateUtil.getJavaDate(startDateAsNumber, false);
+
+ Calendar cal = new GregorianCalendar();
+ cal.setTime(startDate);
+ cal.set(cal.get(Calendar.YEAR), cal.get(Calendar.MONTH), cal.get(Calendar.DAY_OF_MONTH), 0, 0, 0);
+ cal.set(Calendar.MILLISECOND, 0);
+
+ cal.add(Calendar.MONTH, months + 1);
+ cal.set(Calendar.DAY_OF_MONTH, 1);
+ cal.add(Calendar.DAY_OF_MONTH, -1);
+
+ return new NumberEval(DateUtil.getExcelDate(cal.getTime()));
+ } catch (EvaluationException e) {
+ return e.getErrorEval();
+ }
+ }
+
+}
Added: poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/RefValueImplementation.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/RefValueImplementation.java?rev=1634515&view=auto
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/RefValueImplementation.java (added)
+++ poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/RefValueImplementation.java Mon Oct 27 12:29:32 2014
@@ -0,0 +1,68 @@
+/* ====================================================================
+ 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.functions;
+
+import org.apache.poi.ss.formula.eval.AreaEval;
+import org.apache.poi.ss.formula.eval.RefEval;
+import org.apache.poi.ss.formula.eval.ValueEval;
+
+final class RefEvalImplementation implements RefEval {
+
+ private final ValueEval value;
+
+ public RefEvalImplementation(ValueEval value) {
+ this.value = value;
+ }
+
+ @Override
+ public AreaEval offset(int relFirstRowIx, int relLastRowIx,
+ int relFirstColIx, int relLastColIx) {
+ throw new UnsupportedOperationException();
+ }
+
+ @Override
+ public ValueEval getInnerValueEval(int sheetIndex) {
+ return value;
+ }
+
+ @Override
+ public int getNumberOfSheets() {
+ return 1;
+ }
+
+ @Override
+ public int getFirstSheetIndex() {
+ return 0;
+ }
+
+ @Override
+ public int getLastSheetIndex() {
+ return 0;
+ }
+
+ @Override
+ public int getRow() {
+ throw new UnsupportedOperationException();
+ }
+
+ @Override
+ public int getColumn() {
+ throw new UnsupportedOperationException();
+ }
+
+}
Modified: poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestEDate.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestEDate.java?rev=1634515&r1=1634514&r2=1634515&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestEDate.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestEDate.java Mon Oct 27 12:29:32 2014
@@ -31,42 +31,7 @@ import org.apache.poi.ss.formula.eval.Va
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.ErrorConstants;
-public class TestEDate extends TestCase{
-
- private final class RefEvalImplementation implements RefEval {
- private final ValueEval value;
-
- public RefEvalImplementation(ValueEval value) {
- super();
- this.value = value;
- }
-
- public AreaEval offset(int relFirstRowIx, int relLastRowIx,
- int relFirstColIx, int relLastColIx) {
- throw new UnsupportedOperationException();
- }
-
- public ValueEval getInnerValueEval(int sheetIndex) {
- return value;
- }
-
- public int getNumberOfSheets() {
- return 1;
- }
- public int getFirstSheetIndex() {
- return 0;
- }
- public int getLastSheetIndex() {
- return 0;
- }
-
- public int getRow() {
- throw new UnsupportedOperationException();
- }
- public int getColumn() {
- throw new UnsupportedOperationException();
- }
- }
+public class TestEDate extends TestCase {
public void testEDateProperValues() {
// verify some border-case combinations of startDate and month-increase
Added: poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestEOMonth.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestEOMonth.java?rev=1634515&view=auto
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestEOMonth.java (added)
+++ poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestEOMonth.java Mon Oct 27 12:29:32 2014
@@ -0,0 +1,136 @@
+/* ====================================================================
+ 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.functions;
+
+import java.util.Calendar;
+import java.util.Date;
+
+import junit.framework.TestCase;
+import org.apache.poi.ss.formula.OperationEvaluationContext;
+
+import org.apache.poi.ss.formula.eval.BlankEval;
+import org.apache.poi.ss.formula.eval.ErrorEval;
+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;
+import org.apache.poi.ss.usermodel.ErrorConstants;
+
+public class TestEOMonth extends TestCase{
+
+ private static final double BAD_DATE = -1.0;
+
+ private static final double DATE_1900_01_01 = 1.0;
+ private static final double DATE_1900_01_31 = 31.0;
+ private static final double DATE_1900_02_28 = 59.0;
+ private static final double DATE_1902_09_26 = 1000.0;
+ private static final double DATE_1902_09_30 = 1004.0;
+ private static final double DATE_2034_06_09 = 49104.0;
+ private static final double DATE_2034_06_30 = 49125.0;
+ private static final double DATE_2034_07_31 = 49156.0;
+
+ private final FreeRefFunction eOMonth = EOMonth.instance;
+ private final OperationEvaluationContext ec = new OperationEvaluationContext(null, null, 0, 0, 0, null);
+
+ public void testEOMonthProperValues() {
+ // verify some border-case combinations of startDate and month-increase
+ checkValue(DATE_1900_01_01, 0, DATE_1900_01_31);
+ checkValue(DATE_1900_01_01, 1, DATE_1900_02_28);
+ checkValue(DATE_1902_09_26, 0, DATE_1902_09_30);
+ checkValue(DATE_2034_06_09, 0, DATE_2034_06_30);
+ checkValue(DATE_2034_06_09, 1, DATE_2034_07_31);
+ }
+
+ public void testEOMonthBadDateValues() {
+ checkValue(0.0, -2, BAD_DATE);
+ checkValue(0.0, -3, BAD_DATE);
+ checkValue(DATE_1900_01_31, -1, BAD_DATE);
+ }
+
+ private void checkValue(double startDate, int monthInc, double expectedResult) {
+ NumberEval result = (NumberEval) eOMonth.evaluate(new ValueEval[] {new NumberEval(startDate), new NumberEval(monthInc)}, ec);
+ assertEquals(expectedResult, result.getNumberValue());
+ }
+
+ public void testEOMonthZeroDate() {
+ NumberEval result = (NumberEval) eOMonth.evaluate(new ValueEval[] {new NumberEval(0), new NumberEval(0)}, ec);
+ assertEquals("0 startDate is 1900-01-00", DATE_1900_01_31, result.getNumberValue());
+
+ result = (NumberEval) eOMonth.evaluate(new ValueEval[] {new NumberEval(0), new NumberEval(1)}, ec);
+ assertEquals("0 startDate is 1900-01-00", DATE_1900_02_28, result.getNumberValue());
+ }
+
+ public void testEOMonthInvalidArguments() {
+ ValueEval result = eOMonth.evaluate(new ValueEval[] {new NumberEval(DATE_1902_09_26)}, ec);
+ assertTrue(result instanceof ErrorEval);
+ assertEquals(ErrorConstants.ERROR_VALUE, ((ErrorEval) result).getErrorCode());
+
+ result = eOMonth.evaluate(new ValueEval[] {new StringEval("a"), new StringEval("b")}, ec);
+ assertTrue(result instanceof ErrorEval);
+ assertEquals(ErrorConstants.ERROR_VALUE, ((ErrorEval) result).getErrorCode());
+ }
+
+ public void testEOMonthIncrease() {
+ checkOffset(new Date(), 2);
+ }
+
+ public void testEOMonthDecrease() {
+ checkOffset(new Date(), -2);
+ }
+
+ private void checkOffset(Date startDate, int offset) {
+ NumberEval result = (NumberEval) eOMonth.evaluate(new ValueEval[] {new NumberEval(DateUtil.getExcelDate(startDate)), new NumberEval(offset)}, ec);
+ Date resultDate = DateUtil.getJavaDate(result.getNumberValue());
+ Calendar instance = Calendar.getInstance();
+ instance.setTime(startDate);
+ instance.add(Calendar.MONTH, offset);
+ instance.add(Calendar.MONTH, 1);
+ instance.set(Calendar.DAY_OF_MONTH, 1);
+ instance.add(Calendar.DAY_OF_MONTH, -1);
+ instance.set(Calendar.HOUR_OF_DAY, 0);
+ instance.set(Calendar.MINUTE, 0);
+ instance.set(Calendar.SECOND, 0);
+ instance.set(Calendar.MILLISECOND, 0);
+ assertEquals(instance.getTime(), resultDate);
+ }
+
+ public void testBug56688() {
+ NumberEval result = (NumberEval) eOMonth.evaluate(new ValueEval[] {new NumberEval(DATE_1902_09_26), new RefEvalImplementation(new NumberEval(0))}, ec);
+ assertEquals(DATE_1902_09_30, result.getNumberValue());
+ }
+
+ public void testRefEvalStartDate() {
+ NumberEval result = (NumberEval) eOMonth.evaluate(new ValueEval[] {new RefEvalImplementation(new NumberEval(DATE_1902_09_26)), new NumberEval(0)}, ec);
+ assertEquals(DATE_1902_09_30, result.getNumberValue());
+ }
+
+ public void testEOMonthBlankValueEval() {
+ NumberEval evaluate = (NumberEval) eOMonth.evaluate(new ValueEval[] {BlankEval.instance, new NumberEval(0)}, ec);
+ assertEquals("Blank is handled as 0", DATE_1900_01_31, evaluate.getNumberValue());
+ }
+
+ public void testEOMonthBlankRefValueEval() {
+ NumberEval result = (NumberEval) eOMonth.evaluate(new ValueEval[] {new RefEvalImplementation(BlankEval.instance), new NumberEval(1)}, ec);
+ assertEquals("Blank is handled as 0",
+ DATE_1900_02_28, result.getNumberValue());
+
+ result = (NumberEval) eOMonth.evaluate(new ValueEval[] {new NumberEval(1), new RefEvalImplementation(BlankEval.instance)}, ec);
+ assertEquals("Blank is handled as 0",
+ DATE_1900_01_31, result.getNumberValue());
+ }
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org