From bfd968deb39f8f4638e81aa60e1c2738c1c9c5ea Mon Sep 17 00:00:00 2001 From: Josh Micich Date: Sun, 22 Nov 2009 05:30:53 +0000 Subject: [PATCH] Fixed small bug in SUMIF() added junits. Also added test cases for DAYS360, some initially disabled. git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@883037 13f79535-47bb-0310-9956-ffa450edef68 --- .../hssf/record/formula/functions/Sumif.java | 2 +- .../AllIndividualFunctionEvaluationTests.java | 4 +- .../record/formula/functions/EvalFactory.java | 15 +- .../record/formula/functions/TestDays360.java | 155 ++++++++++++++++++ .../record/formula/functions/TestSumif.java | 102 ++++++++++++ 5 files changed, 270 insertions(+), 8 deletions(-) create mode 100644 src/testcases/org/apache/poi/hssf/record/formula/functions/TestDays360.java create mode 100644 src/testcases/org/apache/poi/hssf/record/formula/functions/TestSumif.java diff --git a/src/java/org/apache/poi/hssf/record/formula/functions/Sumif.java b/src/java/org/apache/poi/hssf/record/formula/functions/Sumif.java index 4f6a59ad2a..224e052921 100644 --- a/src/java/org/apache/poi/hssf/record/formula/functions/Sumif.java +++ b/src/java/org/apache/poi/hssf/record/formula/functions/Sumif.java @@ -63,7 +63,7 @@ public final class Sumif implements Function { } catch (EvaluationException e) { return e.getErrorEval(); } - I_MatchPredicate mp = Countif.createCriteriaPredicate(args[1], srcRowIndex, srcRowIndex); + I_MatchPredicate mp = Countif.createCriteriaPredicate(args[1], srcRowIndex, srcColumnIndex); double result = sumMatchingCells(aeRange, mp, aeSum); return new NumberEval(result); } diff --git a/src/testcases/org/apache/poi/hssf/record/formula/functions/AllIndividualFunctionEvaluationTests.java b/src/testcases/org/apache/poi/hssf/record/formula/functions/AllIndividualFunctionEvaluationTests.java index 8010a50bab..3e23930500 100644 --- a/src/testcases/org/apache/poi/hssf/record/formula/functions/AllIndividualFunctionEvaluationTests.java +++ b/src/testcases/org/apache/poi/hssf/record/formula/functions/AllIndividualFunctionEvaluationTests.java @@ -22,7 +22,7 @@ import junit.framework.TestSuite; /** * Direct tests for all implementors of Function. - * + * * @author Josh Micich */ public final class AllIndividualFunctionEvaluationTests { @@ -32,6 +32,7 @@ public final class AllIndividualFunctionEvaluationTests { result.addTestSuite(TestAverage.class); result.addTestSuite(TestCountFuncs.class); result.addTestSuite(TestDate.class); + result.addTestSuite(TestDays360.class); result.addTestSuite(TestFind.class); result.addTestSuite(TestFinanceLib.class); result.addTestSuite(TestIndex.class); @@ -47,6 +48,7 @@ public final class AllIndividualFunctionEvaluationTests { result.addTestSuite(TestPmt.class); result.addTestSuite(TestOffset.class); result.addTestSuite(TestRowCol.class); + result.addTestSuite(TestSumif.class); result.addTestSuite(TestSumproduct.class); result.addTestSuite(TestStatsLib.class); result.addTestSuite(TestTFunc.class); diff --git a/src/testcases/org/apache/poi/hssf/record/formula/functions/EvalFactory.java b/src/testcases/org/apache/poi/hssf/record/formula/functions/EvalFactory.java index 1c8d74f44a..433ed9df40 100644 --- a/src/testcases/org/apache/poi/hssf/record/formula/functions/EvalFactory.java +++ b/src/testcases/org/apache/poi/hssf/record/formula/functions/EvalFactory.java @@ -29,7 +29,7 @@ import org.apache.poi.hssf.record.formula.eval.ValueEval; /** * Test helper class for creating mock Eval objects - * + * * @author Josh Micich */ public final class EvalFactory { @@ -39,7 +39,7 @@ public final class EvalFactory { } /** - * Creates a dummy AreaEval + * Creates a dummy AreaEval * @param values empty (null) entries in this array will be converted to NumberEval.ZERO */ public static AreaEval createAreaEval(String areaRefStr, ValueEval[] values) { @@ -48,7 +48,7 @@ public final class EvalFactory { } /** - * Creates a dummy AreaEval + * Creates a dummy AreaEval * @param values empty (null) entries in this array will be converted to NumberEval.ZERO */ public static AreaEval createAreaEval(AreaPtg areaPtg, ValueEval[] values) { @@ -75,7 +75,7 @@ public final class EvalFactory { public static RefEval createRefEval(String refStr, ValueEval value) { return new MockRefEval(new RefPtg(refStr), value); } - + private static final class MockAreaEval extends AreaEvalBase { private final ValueEval[] _values; public MockAreaEval(AreaPtg areaPtg, ValueEval[] values) { @@ -94,10 +94,14 @@ public final class EvalFactory { return _values[oneDimensionalIndex]; } public AreaEval offset(int relFirstRowIx, int relLastRowIx, int relFirstColIx, int relLastColIx) { + if (relFirstRowIx == 0 && relFirstColIx == 0 + && relLastRowIx == getHeight()-1 && relLastColIx == getWidth()-1) { + return this; + } throw new RuntimeException("Operation not implemented on this mock object"); } } - + private static final class MockRefEval extends RefEvalBase { private final ValueEval _value; public MockRefEval(RefPtg ptg, ValueEval value) { @@ -115,5 +119,4 @@ public final class EvalFactory { throw new RuntimeException("Operation not implemented on this mock object"); } } - } diff --git a/src/testcases/org/apache/poi/hssf/record/formula/functions/TestDays360.java b/src/testcases/org/apache/poi/hssf/record/formula/functions/TestDays360.java new file mode 100644 index 0000000000..e517ac9b62 --- /dev/null +++ b/src/testcases/org/apache/poi/hssf/record/formula/functions/TestDays360.java @@ -0,0 +1,155 @@ +/* ==================================================================== + 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.hssf.record.formula.functions; + +import java.util.Calendar; +import java.util.Date; +import java.util.GregorianCalendar; + +import junit.framework.AssertionFailedError; +import junit.framework.TestCase; + +import org.apache.poi.hssf.record.formula.eval.BoolEval; +import org.apache.poi.hssf.record.formula.eval.NumberEval; +import org.apache.poi.hssf.record.formula.eval.ValueEval; +import org.apache.poi.hssf.usermodel.HSSFDateUtil; + +/** + * @author Josh Micich + */ +public final class TestDays360 extends TestCase { + + /** + * @param month 1-based + */ + private static Date makeDate(int year, int month, int day) { + + Calendar cal = new GregorianCalendar(year, month-1, day, 0, 0, 0); + cal.set(Calendar.MILLISECOND, 0); + return cal.getTime(); + } + private static Date decrementDay(Date d) { + Calendar c = new GregorianCalendar(); + c.setTimeInMillis(d.getTime()); + c.add(Calendar.DAY_OF_MONTH, -1); + return c.getTime(); + } + private static String fmt(Date d) { + Calendar c = new GregorianCalendar(); + c.setTimeInMillis(d.getTime()); + StringBuilder sb = new StringBuilder(); + sb.append(c.get(Calendar.YEAR)); + sb.append("/"); + sb.append(c.get(Calendar.MONTH)+1); + sb.append("/"); + sb.append(c.get(Calendar.DAY_OF_MONTH)); + return sb.toString(); + } + + + public void testBasic() { + confirm(120, 2009, 1, 15, 2009, 5, 15); + confirm(158, 2009, 1, 26, 2009, 7, 4); + + // same results in leap years + confirm(120, 2008, 1, 15, 2008, 5, 15); + confirm(158, 2008, 1, 26, 2008, 7, 4); + + // longer time spans + confirm(562, 2008, 8, 11, 2010, 3, 3); + confirm(916, 2007, 2, 23, 2009, 9, 9); + } + + private static void confirm(int expResult, int y1, int m1, int d1, int y2, int m2, int d2) { + confirm(expResult, makeDate(y1, m1, d1), makeDate(y2, m2, d2), false); + confirm(-expResult, makeDate(y2, m2, d2), makeDate(y1, m1, d1), false); + + } + /** + * The method parameter only makes a difference when the second parameter + * is the last day of the month that does not have 30 days. + */ + public void DISABLED_testMonthBoundaries() { + // jan + confirmMonthBoundary(false, 1, 0, 0, 2, 3, 4); + confirmMonthBoundary(true, 1, 0, 0, 1, 3, 4); + // feb + confirmMonthBoundary(false, 2,-2, 1, 2, 3, 4); + confirmMonthBoundary(true, 2, 0, 1, 2, 3, 4); + // mar + confirmMonthBoundary(false, 3, 0, 0, 2, 3, 4); + confirmMonthBoundary(true, 3, 0, 0, 1, 3, 4); + // apr + confirmMonthBoundary(false, 4, 0, 1, 2, 3, 4); + confirmMonthBoundary(true, 4, 0, 1, 2, 3, 4); + // may + confirmMonthBoundary(false, 5, 0, 0, 2, 3, 4); + confirmMonthBoundary(true, 5, 0, 0, 1, 3, 4); + // jun + confirmMonthBoundary(false, 6, 0, 1, 2, 3, 4); + confirmMonthBoundary(true, 6, 0, 1, 2, 3, 4); + // etc... + } + + + /** + * @param monthNo 1-based + * @param diffs + */ + private static void confirmMonthBoundary(boolean method, int monthNo, int...diffs) { + Date firstDayOfNextMonth = makeDate(2001, monthNo+1, 1); + Date secondArg = decrementDay(firstDayOfNextMonth); + Date firstArg = secondArg; + + for (int i = 0; i < diffs.length; i++) { + int expResult = diffs[i]; + confirm(expResult, firstArg, secondArg, method); + firstArg = decrementDay(firstArg); + } + + } + private static void confirm(int expResult, Date firstArg, Date secondArg, boolean method) { + + ValueEval ve; + if (method) { + // TODO enable 3rd arg - + ve = invokeDays360(convert(firstArg), convert(secondArg), BoolEval.valueOf(method)); + } else { + ve = invokeDays360(convert(firstArg), convert(secondArg)); + } + if (ve instanceof NumberEval) { + + NumberEval numberEval = (NumberEval) ve; + if (numberEval.getNumberValue() != expResult) { + throw new AssertionFailedError(fmt(firstArg) + " " + fmt(secondArg) + " " + method + + " wrong result got (" + numberEval.getNumberValue() + + ") but expected (" + expResult + ")"); + } + // System.err.println(fmt(firstArg) + " " + fmt(secondArg) + " " + method + " success got (" + expResult + ")"); + return; + } + throw new AssertionFailedError("wrong return type (" + ve.getClass().getName() + ")"); + } + private static ValueEval invokeDays360(ValueEval...args) { + return new Days360().evaluate(args, -1, -1); + } + private static NumberEval convert(Date d) { + return new NumberEval(HSSFDateUtil.getExcelDate(d)); + } +} + diff --git a/src/testcases/org/apache/poi/hssf/record/formula/functions/TestSumif.java b/src/testcases/org/apache/poi/hssf/record/formula/functions/TestSumif.java new file mode 100644 index 0000000000..ff611ec31e --- /dev/null +++ b/src/testcases/org/apache/poi/hssf/record/formula/functions/TestSumif.java @@ -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.hssf.record.formula.functions; + +import junit.framework.AssertionFailedError; +import junit.framework.TestCase; + +import org.apache.poi.hssf.record.formula.eval.AreaEval; +import org.apache.poi.hssf.record.formula.eval.NumberEval; +import org.apache.poi.hssf.record.formula.eval.NumericValueEval; +import org.apache.poi.hssf.record.formula.eval.StringEval; +import org.apache.poi.hssf.record.formula.eval.ValueEval; + +/** + * Test cases for SUMPRODUCT() + * + * @author Josh Micich + */ +public final class TestSumif extends TestCase { + private static final NumberEval _30 = new NumberEval(30); + private static final NumberEval _40 = new NumberEval(40); + private static final NumberEval _50 = new NumberEval(50); + private static final NumberEval _60 = new NumberEval(60); + + private static ValueEval invokeSumif(int rowIx, int colIx, ValueEval...args) { + return new Sumif().evaluate(args, rowIx, colIx); + } + private static void confirmDouble(double expected, ValueEval actualEval) { + if(!(actualEval instanceof NumericValueEval)) { + throw new AssertionFailedError("Expected numeric result"); + } + NumericValueEval nve = (NumericValueEval)actualEval; + assertEquals(expected, nve.getNumberValue(), 0); + } + + public void testBasic() { + ValueEval[] arg0values = new ValueEval[] { _30, _30, _40, _40, _50, _50 }; + ValueEval[] arg2values = new ValueEval[] { _30, _40, _50, _60, _60, _60 }; + + AreaEval arg0; + AreaEval arg2; + + arg0 = EvalFactory.createAreaEval("A3:B5", arg0values); + arg2 = EvalFactory.createAreaEval("D1:E3", arg2values); + + confirm(60.0, arg0, new NumberEval(30.0)); + confirm(70.0, arg0, new NumberEval(30.0), arg2); + confirm(100.0, arg0, new StringEval(">45")); + + } + private static void confirm(double expectedResult, ValueEval...args) { + confirmDouble(expectedResult, invokeSumif(-1, -1, args)); + } + + + /** + * test for bug observed near svn r882931 + */ + public void testCriteriaArgRange() { + ValueEval[] arg0values = new ValueEval[] { _50, _60, _50, _50, _50, _30, }; + ValueEval[] arg1values = new ValueEval[] { _30, _40, _50, _60, }; + + AreaEval arg0; + AreaEval arg1; + ValueEval ve; + + arg0 = EvalFactory.createAreaEval("A3:B5", arg0values); + arg1 = EvalFactory.createAreaEval("A2:D2", arg1values); // single row range + + ve = invokeSumif(0, 2, arg0, arg1); // invoking from cell C1 + if (ve instanceof NumberEval) { + NumberEval ne = (NumberEval) ve; + if (ne.getNumberValue() == 30.0) { + throw new AssertionFailedError("identified error in SUMIF - criteria arg not evaluated properly"); + } + } + + confirmDouble(200, ve); + + arg0 = EvalFactory.createAreaEval("C1:D3", arg0values); + arg1 = EvalFactory.createAreaEval("B1:B4", arg1values); // single column range + + ve = invokeSumif(3, 0, arg0, arg1); // invoking from cell A4 + + confirmDouble(60, ve); + } +} -- 2.39.5