| /* |
| * ==================================================================== |
| * 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.AreaEval; |
| 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.RefEval; |
| import org.apache.poi.ss.formula.eval.ValueEval; |
| import org.apache.poi.ss.formula.functions.CountUtils.I_MatchPredicate; |
| import org.apache.poi.ss.formula.functions.Countif.ErrorMatcher; |
| |
| /** |
| * 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> |
| * |
| * @author Yegor Kozlov |
| */ |
| public final class Sumifs implements FreeRefFunction { |
| public static final FreeRefFunction instance = new Sumifs(); |
| |
| public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) { |
| // https://support.office.com/en-us/article/SUMIFS-function-c9e748f5-7ea7-455d-9406-611cebce642b |
| // COUNTIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ... |
| // need at least 3 arguments and need to have an odd number of arguments (sum-range plus x*(criteria_range, criteria)) |
| if(args.length < 3 || args.length % 2 == 0) { |
| return ErrorEval.VALUE_INVALID; |
| } |
| |
| try { |
| AreaEval sumRange = convertRangeArg(args[0]); |
| |
| // collect pairs of ranges and criteria |
| 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()); |
| } |
| |
| validateCriteriaRanges(ae, sumRange); |
| validateCriteria(mp); |
| |
| double result = sumMatchingCells(ae, mp, sumRange); |
| return new NumberEval(result); |
| } catch (EvaluationException e) { |
| return e.getErrorEval(); |
| } |
| } |
| |
| /** |
| * Verify that each <code>criteriaRanges</code> argument contains the same number of rows and columns |
| * as the <code>sumRange</code> argument |
| * |
| * @throws EvaluationException if the ranges do not match. |
| */ |
| private void validateCriteriaRanges(AreaEval[] criteriaRanges, AreaEval sumRange) throws EvaluationException { |
| for(AreaEval r : criteriaRanges){ |
| if(r.getHeight() != sumRange.getHeight() || |
| r.getWidth() != sumRange.getWidth() ) { |
| throw EvaluationException.invalidValue(); |
| } |
| } |
| } |
| |
| /** |
| * Verify that each <code>criteria</code> predicate is valid, i.e. not an error |
| * |
| * @throws EvaluationException if there are criteria which resulted in Errors. |
| */ |
| private void validateCriteria(I_MatchPredicate[] criteria) throws EvaluationException { |
| for(I_MatchPredicate predicate : criteria) { |
| |
| // check for errors in predicate and return immediately using this error code |
| if(predicate instanceof ErrorMatcher) { |
| throw new EvaluationException(ErrorEval.valueOf(((ErrorMatcher)predicate).getValue())); |
| } |
| } |
| } |
| |
| |
| /** |
| * |
| * @param ranges criteria ranges, each range must be of the same dimensions as <code>aeSum</code> |
| * @param predicates array of predicates, a predicate for each value in <code>ranges</code> |
| * @param aeSum the range to sum |
| * |
| * @return the computed value |
| */ |
| 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) { // sum only if all of the corresponding criteria specified are true for that cell. |
| 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); |
| } |
| |
| } |