/* * 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. */ namespace HH.WMS.Utils.NPOI.SS.Formula.Functions { using System; using HH.WMS.Utils.NPOI.SS.Formula.Eval; using HH.WMS.Utils.NPOI.SS.Formula; /* * Implementation for the Excel function SUMPRODUCT

* * Syntax :
* SUMPRODUCT ( array1[, array2[, array3[, ...]]]) * * *
array1, ... arrayN typically area references, * possibly cell references or scalar values

* * Let An(i,j) represent the element in the ith row jth column * of the nth array
* Assuming each array has the same dimensions (W, H), the result Is defined as:
* SUMPRODUCT = Σi: 1..H * ( Σj: 1..W * ( Πn: 1..N * An(i,j) * ) * ) * * @author Josh Micich */ public class Sumproduct : Function { [Serializable] private class EvalEx : Exception { private ErrorEval _error; public EvalEx(ErrorEval error) { _error = error; } public ErrorEval GetError() { return _error; } } public ValueEval Evaluate(ValueEval[] args, int srcCellRow, int srcCellCol) { int maxN = args.Length; if (maxN < 1) { return ErrorEval.VALUE_INVALID; } ValueEval firstArg = args[0]; try { if (firstArg is NumericValueEval) { return EvaluateSingleProduct(args); } if (firstArg is RefEval) { return EvaluateSingleProduct(args); } if (firstArg is TwoDEval) { TwoDEval ae = (TwoDEval)firstArg; if (ae.IsRow && ae.IsColumn) { return EvaluateSingleProduct(args); } return EvaluateAreaSumProduct(args); } } catch (EvalEx e) { return e.GetError(); } throw new Exception("Invalid arg type for SUMPRODUCT: (" + firstArg.GetType().Name + ")"); } private ValueEval EvaluateSingleProduct(ValueEval[] evalArgs) { int maxN = evalArgs.Length; double term = 1D; for (int n = 0; n < maxN; n++) { double val = GetScalarValue(evalArgs[n]); term *= val; } return new NumberEval(term); } private static double GetScalarValue(ValueEval arg) { ValueEval eval; if (arg is RefEval) { RefEval re = (RefEval)arg; eval = re.InnerValueEval; } else { eval = arg; } if (eval == null) { throw new ArgumentException("parameter may not be null"); } if (eval is AreaEval) { AreaEval ae = (AreaEval)eval; // an area ref can work as a scalar value if it is 1x1 if (!ae.IsColumn || !ae.IsRow) { throw new EvaluationException(ErrorEval.VALUE_INVALID); } eval = ae.GetRelativeValue(0, 0); } if (!(eval is ValueEval)) { throw new ArgumentException("Unexpected value eval class (" + eval.GetType().Name + ")"); } return GetProductTerm((ValueEval)eval, true); } private ValueEval EvaluateAreaSumProduct(ValueEval[] evalArgs) { int maxN = evalArgs.Length; AreaEval[] args = new AreaEval[maxN]; try { Array.Copy(evalArgs, 0, args, 0, maxN); } catch (Exception) { // one of the other args was not an AreaRef return ErrorEval.VALUE_INVALID; } AreaEval firstArg = args[0]; int height = firstArg.LastRow - firstArg.FirstRow + 1; int width = firstArg.LastColumn - firstArg.FirstColumn + 1; // TODO - junit // first check dimensions if (!AreasAllSameSize(args, height, width)) { // normally this results in #VALUE!, // but errors in individual cells take precedence for (int i = 1; i < args.Length; i++) { ThrowFirstError(args[i]); } return ErrorEval.VALUE_INVALID; } double acc = 0; for (int rrIx = 0; rrIx < height; rrIx++) { for (int rcIx = 0; rcIx < width; rcIx++) { double term = 1D; for (int n = 0; n < maxN; n++) { double val = GetProductTerm(args[n].GetRelativeValue(rrIx, rcIx), false); term *= val; } acc += term; } } return new NumberEval(acc); } private static void ThrowFirstError(TwoDEval areaEval) { int height = areaEval.Height; int width = areaEval.Width; for (int rrIx = 0; rrIx < height; rrIx++) { for (int rcIx = 0; rcIx < width; rcIx++) { ValueEval ve = areaEval.GetValue(rrIx, rcIx); if (ve is ErrorEval) { throw new EvaluationException((ErrorEval)ve); } } } } private static bool AreasAllSameSize(TwoDEval[] args, int height, int width) { for (int i = 0; i < args.Length; i++) { TwoDEval areaEval = args[i]; // check that height and width match if (areaEval.Height != height) { return false; } if (areaEval.Width != width) { return false; } } return true; } /** * Determines a double value for the specified ValueEval. * @param IsScalarProduct false for SUMPRODUCTs over area refs. * @throws EvalEx if ve represents an error value. *

* Note - string values and empty cells are interpreted differently depending on * isScalarProduct. For scalar products, if any term Is blank or a string, the * error (#VALUE!) Is raised. For area (sum)products, if any term Is blank or a string, the * result Is zero. */ private static double GetProductTerm(ValueEval ve, bool IsScalarProduct) { if (ve is BlankEval || ve == null) { // TODO - shouldn't BlankEval.INSTANCE be used always instead of null? // null seems to occur when the blank cell Is part of an area ref (but not reliably) if (IsScalarProduct) { throw new EvalEx(ErrorEval.VALUE_INVALID); } return 0; } if (ve is ErrorEval) { throw new EvalEx((ErrorEval)ve); } if (ve is StringEval) { if (IsScalarProduct) { throw new EvalEx(ErrorEval.VALUE_INVALID); } // Note for area SUMPRODUCTs, string values are interpreted as zero // even if they would Parse as valid numeric values return 0; } if (ve is NumericValueEval) { NumericValueEval nve = (NumericValueEval)ve; return nve.NumberValue; } throw new Exception("Unexpected value eval class (" + ve.GetType().Name + ")"); } } }