Attachment #28157 for bug #52462

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

Lines 15-20 Link Here

(-)src/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java (-1 / +2 lines)
15		15
16	import org.apache.poi.ss.formula.eval.ValueEval;	16	import org.apache.poi.ss.formula.eval.ValueEval;
17	import org.apache.poi.ss.formula.functions.FreeRefFunction;	17	import org.apache.poi.ss.formula.functions.FreeRefFunction;
		18	import org.apache.poi.ss.formula.functions.Sumifs;
18	import org.apache.poi.ss.formula.udf.UDFFinder;	19	import org.apache.poi.ss.formula.udf.UDFFinder;
19	import org.apache.poi.ss.formula.OperationEvaluationContext;	20	import org.apache.poi.ss.formula.OperationEvaluationContext;
20	import org.apache.poi.ss.formula.eval.NotImplementedException;	21	import org.apache.poi.ss.formula.eval.NotImplementedException;
Lines 150-156 Link Here
150	r(m, "RTD", null);	151	r(m, "RTD", null);
151	r(m, "SERIESSUM", null);	152	r(m, "SERIESSUM", null);
152	r(m, "SQRTPI", null);	153	r(m, "SQRTPI", null);
153	r(m, "SUMIFS", null);	154	r(m, "SUMIFS", Sumifs.instance);
154	r(m, "TBILLEQ", null);	155	r(m, "TBILLEQ", null);
155	r(m, "TBILLPRICE", null);	156	r(m, "TBILLPRICE", null);
156	r(m, "TBILLYIELD", null);	157	r(m, "TBILLYIELD", null);




/*
 *  ====================================================================
 *    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.OperationEvaluationContext;
import org.apache.poi.ss.formula.eval.*;
import org.apache.poi.ss.formula.functions.CountUtils.I_MatchPredicate;

/**
 * Implementation for the Excel function SUMIFS<p>
 *
 * Syntax : <br/>
 *  SUMIFS ( <b>sum_range</b>, <b>criteria_range1</b>, <b>criteria1</>,
 *  [<b>criteria_range2</b>,  <b>criteria2</b>], …) <br/>
 *    <ul>
 *      <li><b>sum_range</b> Required. One or more cells to sum, including numbers or names, ranges,
 *      or cell references that contain numbers. Blank and text values are ignored.</li>
 *      <li><b>criteria1_range</b> Required. The first range in which
 *      to evaluate the associated criteria.</li>
 *      <li><b>criteria1</b> Required. The criteria in the form of a number, expression,
 *        cell reference, or text that define which cells in the criteria_range1
 *        argument will be added</li>
 *      <li><b> criteria_range2, criteria2, …</b>    Optional. Additional ranges and their associated criteria.
 *      Up to 127 range/criteria pairs are allowed.
 *    </ul>
 * </p>
 */
public final class Sumifs implements FreeRefFunction {
    public static final FreeRefFunction instance = new Sumifs();

	public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) {
        if(args.length < 3 || args.length % 2 == 0) {
            return ErrorEval.VALUE_INVALID;
        }

		try {
            AreaEval sumRange = convertRangeArg(args[0]);

            AreaEval[] ae = new AreaEval[(args.length -1)/2];
            I_MatchPredicate[] mp = new I_MatchPredicate[ae.length];
            for(int i = 1, k=0; i < args.length; i += 2, k++){
                ae[k] = convertRangeArg(args[i]);
                mp[k] = Countif.createCriteriaPredicate(args[i+1], ec.getRowIndex(), ec.getColumnIndex());
            }

            double result = sumMatchingCells(ae, mp, sumRange);
            return new NumberEval(result);
		} catch (EvaluationException e) {
			return e.getErrorEval();
		}
	}


    private static double sumMatchingCells(AreaEval[] ranges, I_MatchPredicate[] predicates, AreaEval aeSum) {
        int height = aeSum.getHeight();
        int width = aeSum.getWidth();

        double result = 0.0;
        for (int r = 0; r < height; r++) {
            for (int c = 0; c < width; c++) {

                boolean matches = true;
                for(int i = 0; i < ranges.length; i++){
                    AreaEval aeRange = ranges[i];
                    I_MatchPredicate mp = predicates[i];

                    if (!mp.matches(aeRange.getRelativeValue(r, c))) {
                        matches = false;
                        break;
                    }

                }

                if(matches) { // count only if all the predicate match
                    result += accumulate(aeSum, r, c);
                }
            }
        }
        return result;
    }

	private static double accumulate(AreaEval aeSum, int relRowIndex,
			int relColIndex) {

		ValueEval addend = aeSum.getRelativeValue(relRowIndex, relColIndex);
		if (addend instanceof NumberEval) {
			return ((NumberEval)addend).getNumberValue();
		}
		// everything else (including string and boolean values) counts as zero
		return 0.0;
	}

	private static AreaEval convertRangeArg(ValueEval eval) throws EvaluationException {
		if (eval instanceof AreaEval) {
			return (AreaEval) eval;
		}
		if (eval instanceof RefEval) {
			return ((RefEval)eval).offset(0, 0, 0, 0);
		}
		throw new EvaluationException(ErrorEval.VALUE_INVALID);
	}

}




/*
 *  ====================================================================
 *    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 junit.framework.AssertionFailedError;
import junit.framework.TestCase;
import org.apache.poi.ss.formula.OperationEvaluationContext;
import org.apache.poi.ss.formula.eval.*;

/**
 * Test cases for SUMIFS()
 *
 * @author Yegor Kozlov
 */
public final class TestSumifs extends TestCase {

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

	private static ValueEval invokeSumifs(ValueEval[] args, OperationEvaluationContext ec) {
		return new Sumifs().evaluate(args, EC);
	}
	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);
	}

    private static void confirm(double expectedResult, ValueEval[] args) {
        confirmDouble(expectedResult, invokeSumifs(args, EC));
    }

    /**
     *  Example 1 from
     *  http://office.microsoft.com/en-us/excel-help/sumifs-function-HA010047504.aspx
     */
	public void testExample1() {
        // mimic test sample from http://office.microsoft.com/en-us/excel-help/sumifs-function-HA010047504.aspx
        ValueEval[] a2a9 = new ValueEval[] {
                new NumberEval(5),
                new NumberEval(4),
                new NumberEval(15),
                new NumberEval(3),
                new NumberEval(22),
                new NumberEval(12),
                new NumberEval(10),
                new NumberEval(33)
        };

        ValueEval[] b2b9 = new ValueEval[] {
                new StringEval("Apples"),
                new StringEval("Apples"),
                new StringEval("Artichokes"),
                new StringEval("Artichokes"),
                new StringEval("Bananas"),
                new StringEval("Bananas"),
                new StringEval("Carrots"),
                new StringEval("Carrots"),
        };

        ValueEval[] c2c9 = new ValueEval[] {
                new NumberEval(1),
                new NumberEval(2),
                new NumberEval(1),
                new NumberEval(2),
                new NumberEval(1),
                new NumberEval(2),
                new NumberEval(1),
                new NumberEval(2)
        };

        ValueEval[] args;
        // "=SUMIFS(A2:A9, B2:B9, "=A*", C2:C9, 1)"
        args = new ValueEval[]{
                EvalFactory.createAreaEval("A2:A9", a2a9),
                EvalFactory.createAreaEval("B2:B9", b2b9),
                new StringEval("A*"),
                EvalFactory.createAreaEval("C2:C9", c2c9),
                new NumberEval(1),
        };
        confirm(20.0, args);

        // "=SUMIFS(A2:A9, B2:B9, "<>Bananas", C2:C9, 1)"
        args = new ValueEval[]{
                EvalFactory.createAreaEval("A2:A9", a2a9),
                EvalFactory.createAreaEval("B2:B9", b2b9),
                new StringEval("<>Bananas"),
                EvalFactory.createAreaEval("C2:C9", c2c9),
                new NumberEval(1),
        };
        confirm(30.0, args);

	}

    /**
     *  Example 2 from
     *  http://office.microsoft.com/en-us/excel-help/sumifs-function-HA010047504.aspx
     */
    public void testExample2() {
    }

    /**
     *  Example 3 from
     *  http://office.microsoft.com/en-us/excel-help/sumifs-function-HA010047504.aspx
     */
    public void testExample3() {
    }

    /**
     *  Example 4 from
     *  http://office.microsoft.com/en-us/excel-help/sumifs-function-HA010047504.aspx
     */
    public void testExample4() {
    }

    /**
     *  Example 5 from
     *  http://office.microsoft.com/en-us/excel-help/sumifs-function-HA010047504.aspx
     */
    public void testExample5() {
    }

    /**
     * Test passing invalid or wrong arguments
     */
    public void testInvalidArguments() {
    }
}

Return to bug 52462

Line 0 Link Here

(-)src/java/org/apache/poi/ss/formula/functions/Sumifs.java (+120 lines)
		1	/*
		2	* ====================================================================
		3	* Licensed to the Apache Software Foundation (ASF) under one or more
		4	* contributor license agreements. See the NOTICE file distributed with
		5	* this work for additional information regarding copyright ownership.
		6	* The ASF licenses this file to You under the Apache License, Version 2.0
		7	* (the "License"); you may not use this file except in compliance with
		8	* the License. You may obtain a copy of the License at
		9	*
		10	* http://www.apache.org/licenses/LICENSE-2.0
		11	*
		12	* Unless required by applicable law or agreed to in writing, software
		13	* distributed under the License is distributed on an "AS IS" BASIS,
		14	* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
		15	* See the License for the specific language governing permissions and
		16	* limitations under the License.
		17	* ====================================================================
		18	*/
		19
		20	package org.apache.poi.ss.formula.functions;
		21
		22	import org.apache.poi.ss.formula.OperationEvaluationContext;
		23	import org.apache.poi.ss.formula.eval.*;
		24	import org.apache.poi.ss.formula.functions.CountUtils.I_MatchPredicate;
		25
		26	/**
		27	* Implementation for the Excel function SUMIFS<p>
		28	*
		29	* Syntax : <br/>
		30	* SUMIFS ( <b>sum_range</b>, <b>criteria_range1</b>, <b>criteria1</>,
		31	* [<b>criteria_range2</b>, <b>criteria2</b>], …) <br/>
		32	* <ul>
		33	* <li><b>sum_range</b> Required. One or more cells to sum, including numbers or names, ranges,
		34	* or cell references that contain numbers. Blank and text values are ignored.</li>
		35	* <li><b>criteria1_range</b> Required. The first range in which
		36	* to evaluate the associated criteria.</li>
		37	* <li><b>criteria1</b> Required. The criteria in the form of a number, expression,
		38	* cell reference, or text that define which cells in the criteria_range1
		39	* argument will be added</li>
		40	* <li><b> criteria_range2, criteria2, …</b> Optional. Additional ranges and their associated criteria.
		41	* Up to 127 range/criteria pairs are allowed.
		42	* </ul>
		43	* </p>
		44	*/
		45	public final class Sumifs implements FreeRefFunction {
		46	public static final FreeRefFunction instance = new Sumifs();
		47
		48	public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) {
		49	if(args.length < 3 \|\| args.length % 2 == 0) {
		50	return ErrorEval.VALUE_INVALID;
		51	}
		52
		53	try {
		54	AreaEval sumRange = convertRangeArg(args[0]);
		55
		56	AreaEval[] ae = new AreaEval[(args.length -1)/2];
		57	I_MatchPredicate[] mp = new I_MatchPredicate[ae.length];
		58	for(int i = 1, k=0; i < args.length; i += 2, k++){
		59	ae[k] = convertRangeArg(args[i]);
		60	mp[k] = Countif.createCriteriaPredicate(args[i+1], ec.getRowIndex(), ec.getColumnIndex());
		61	}
		62
		63	double result = sumMatchingCells(ae, mp, sumRange);
		64	return new NumberEval(result);
65	} catch (EvaluationException e) {
66	return e.getErrorEval();
67	}
68	}
69
70
71	private static double sumMatchingCells(AreaEval[] ranges, I_MatchPredicate[] predicates, AreaEval aeSum) {
72	int height = aeSum.getHeight();
73	int width = aeSum.getWidth();
74
75	double result = 0.0;
76	for (int r = 0; r < height; r++) {
77	for (int c = 0; c < width; c++) {
78
79	boolean matches = true;
80	for(int i = 0; i < ranges.length; i++){
81	AreaEval aeRange = ranges[i];
82	I_MatchPredicate mp = predicates[i];
83
84	if (!mp.matches(aeRange.getRelativeValue(r, c))) {
85	matches = false;
86	break;
87	}
88
89	}
90
91	if(matches) { // count only if all the predicate match
92	result += accumulate(aeSum, r, c);
93	}
94	}
95	}
96	return result;
97	}
98
99	private static double accumulate(AreaEval aeSum, int relRowIndex,
100	int relColIndex) {
101
102	ValueEval addend = aeSum.getRelativeValue(relRowIndex, relColIndex);
103	if (addend instanceof NumberEval) {
104	return ((NumberEval)addend).getNumberValue();
105	}
106	// everything else (including string and boolean values) counts as zero
107	return 0.0;
108	}
109
110	private static AreaEval convertRangeArg(ValueEval eval) throws EvaluationException {
111	if (eval instanceof AreaEval) {
112	return (AreaEval) eval;
113	}
114	if (eval instanceof RefEval) {
115	return ((RefEval)eval).offset(0, 0, 0, 0);
116	}
117	throw new EvaluationException(ErrorEval.VALUE_INVALID);
118	}
119
120	}

Line 0 Link Here

(-)src/testcases/org/apache/poi/ss/formula/functions/TestSumifs.java (+146 lines)
		1	/*
		2	* ====================================================================
		3	* Licensed to the Apache Software Foundation (ASF) under one or more
		4	* contributor license agreements. See the NOTICE file distributed with
		5	* this work for additional information regarding copyright ownership.
		6	* The ASF licenses this file to You under the Apache License, Version 2.0
		7	* (the "License"); you may not use this file except in compliance with
		8	* the License. You may obtain a copy of the License at
		9	*
		10	* http://www.apache.org/licenses/LICENSE-2.0
		11	*
		12	* Unless required by applicable law or agreed to in writing, software
		13	* distributed under the License is distributed on an "AS IS" BASIS,
		14	* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
		15	* See the License for the specific language governing permissions and
		16	* limitations under the License.
		17	* ====================================================================
		18	*/
		19
		20	package org.apache.poi.ss.formula.functions;
		21
		22	import junit.framework.AssertionFailedError;
		23	import junit.framework.TestCase;
		24	import org.apache.poi.ss.formula.OperationEvaluationContext;
		25	import org.apache.poi.ss.formula.eval.*;
		26
		27	/**
		28	* Test cases for SUMIFS()
		29	*
		30	* @author Yegor Kozlov
		31	*/
		32	public final class TestSumifs extends TestCase {
		33
		34	private static final OperationEvaluationContext EC = new OperationEvaluationContext(null, null, 0, 1, 0, null);
		35
		36	private static ValueEval invokeSumifs(ValueEval[] args, OperationEvaluationContext ec) {
		37	return new Sumifs().evaluate(args, EC);
		38	}
		39	private static void confirmDouble(double expected, ValueEval actualEval) {
		40	if(!(actualEval instanceof NumericValueEval)) {
		41	throw new AssertionFailedError("Expected numeric result");
		42	}
		43	NumericValueEval nve = (NumericValueEval)actualEval;
		44	assertEquals(expected, nve.getNumberValue(), 0);
		45	}
		46
		47	private static void confirm(double expectedResult, ValueEval[] args) {
		48	confirmDouble(expectedResult, invokeSumifs(args, EC));
		49	}
		50
		51	/**
		52	* Example 1 from
		53	* http://office.microsoft.com/en-us/excel-help/sumifs-function-HA010047504.aspx
		54	*/
		55	public void testExample1() {
		56	// mimic test sample from http://office.microsoft.com/en-us/excel-help/sumifs-function-HA010047504.aspx
		57	ValueEval[] a2a9 = new ValueEval[] {
		58	new NumberEval(5),
		59	new NumberEval(4),
		60	new NumberEval(15),
		61	new NumberEval(3),
		62	new NumberEval(22),
		63	new NumberEval(12),
		64	new NumberEval(10),
65	new NumberEval(33)
66	};
67
68	ValueEval[] b2b9 = new ValueEval[] {
69	new StringEval("Apples"),
70	new StringEval("Apples"),
71	new StringEval("Artichokes"),
72	new StringEval("Artichokes"),
73	new StringEval("Bananas"),
74	new StringEval("Bananas"),
75	new StringEval("Carrots"),
76	new StringEval("Carrots"),
77	};
78
79	ValueEval[] c2c9 = new ValueEval[] {
80	new NumberEval(1),
81	new NumberEval(2),
82	new NumberEval(1),
83	new NumberEval(2),
84	new NumberEval(1),
85	new NumberEval(2),
86	new NumberEval(1),
87	new NumberEval(2)
88	};
89
90	ValueEval[] args;
91	// "=SUMIFS(A2:A9, B2:B9, "=A*", C2:C9, 1)"
92	args = new ValueEval[]{
93	EvalFactory.createAreaEval("A2:A9", a2a9),
94	EvalFactory.createAreaEval("B2:B9", b2b9),
95	new StringEval("A*"),
96	EvalFactory.createAreaEval("C2:C9", c2c9),
97	new NumberEval(1),
98	};
99	confirm(20.0, args);
100
101	// "=SUMIFS(A2:A9, B2:B9, "<>Bananas", C2:C9, 1)"
102	args = new ValueEval[]{
103	EvalFactory.createAreaEval("A2:A9", a2a9),
104	EvalFactory.createAreaEval("B2:B9", b2b9),
105	new StringEval("<>Bananas"),
106	EvalFactory.createAreaEval("C2:C9", c2c9),
107	new NumberEval(1),
108	};
109	confirm(30.0, args);
110
111	}
112
113	/**
114	* Example 2 from
115	* http://office.microsoft.com/en-us/excel-help/sumifs-function-HA010047504.aspx
116	*/
117	public void testExample2() {
118	}
119
120	/**
121	* Example 3 from
122	* http://office.microsoft.com/en-us/excel-help/sumifs-function-HA010047504.aspx
123	*/
124	public void testExample3() {
125	}
126
127	/**
128	* Example 4 from
129	* http://office.microsoft.com/en-us/excel-help/sumifs-function-HA010047504.aspx
130	*/
131	public void testExample4() {
132	}
133
134	/**
135	* Example 5 from
136	* http://office.microsoft.com/en-us/excel-help/sumifs-function-HA010047504.aspx
137	*/
138	public void testExample5() {
139	}
140
141	/**
142	* Test passing invalid or wrong arguments
143	*/
144	public void testInvalidArguments() {
145	}
146	}