/* ====================================================================
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 org.apache.poi.hssf.record.formula.eval.AreaEval;
import org.apache.poi.hssf.record.formula.eval.BlankEval;
import org.apache.poi.hssf.record.formula.eval.ErrorEval;
import org.apache.poi.hssf.record.formula.eval.Eval;
import org.apache.poi.hssf.record.formula.eval.EvaluationException;
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.RefEval;
import org.apache.poi.hssf.record.formula.eval.StringEval;
import org.apache.poi.hssf.record.formula.eval.ValueEval;
/**
* Implementation for the Excel function SUMPRODUCT<p>
*
* Syntax : <br/>
* SUMPRODUCT ( array1[, array2[, array3[, ...]]])
* <table border="0" cellpadding="1" cellspacing="0" summary="Parameter descriptions">
* <tr><th>array1, ... arrayN </th><td>typically area references,
* possibly cell references or scalar values</td></tr>
* </table><br/>
*
* Let A<b>n</b><sub>(<b>i</b>,<b>j</b>)</sub> represent the element in the <b>i</b>th row <b>j</b>th column
* of the <b>n</b>th array<br/>
* Assuming each array has the same dimensions (W, H), the result is defined as:<br/>
* SUMPRODUCT = Σ<sub><b>i</b>: 1..H</sub>
* ( Σ<sub><b>j</b>: 1..W</sub>
* ( Π<sub><b>n</b>: 1..N</sub>
* A<b>n</b><sub>(<b>i</b>,<b>j</b>)</sub>
* )
* )
* </p>
* @author Josh Micich
*/
public final class Sumproduct implements Function {
public Eval evaluate(Eval[] args, int srcCellRow, short srcCellCol) {
int maxN = args.length;
if(maxN < 1) {
return ErrorEval.VALUE_INVALID;
}
Eval firstArg = args[0];
try {
if(firstArg instanceof NumericValueEval) {
return evaluateSingleProduct(args);
}
if(firstArg instanceof RefEval) {
return evaluateSingleProduct(args);
}
if(firstArg instanceof AreaEval) {
AreaEval ae = (AreaEval) firstArg;
if(ae.isRow() && ae.isColumn()) {
return evaluateSingleProduct(args);
}
return evaluateAreaSumProduct(args);
}
} catch (EvaluationException e) {
return e.getErrorEval();
}
throw new RuntimeException("Invalid arg type for SUMPRODUCT: ("
+ firstArg.getClass().getName() + ")");
}
private static Eval evaluateSingleProduct(Eval[] evalArgs) throws EvaluationException {
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(Eval arg) throws EvaluationException {
Eval eval;
if (arg instanceof RefEval) {
RefEval re = (RefEval) arg;
eval = re.getInnerValueEval();
} else {
eval = arg;
}
if (eval == null) {
throw new RuntimeException("parameter may not be null");
}
if (eval instanceof 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 instanceof ValueEval)) {
throw new RuntimeException("Unexpected value eval class ("
+ eval.getClass().getName() + ")");
}
return getProductTerm((ValueEval) eval, true);
}
private static Eval evaluateAreaSumProduct(Eval[] evalArgs) throws EvaluationException {
int maxN = evalArgs.length;
AreaEval[] args = new AreaEval[maxN];
try {
System.arraycopy(evalArgs, 0, args, 0, maxN);
} catch (ArrayStoreException e) {
// one of the other args was not an AreaRef
return ErrorEval.VALUE_INVALID;
}
AreaEval firstArg = args[0];
int height = firstArg.getHeight();
int width = firstArg.getWidth(); // 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(AreaEval areaEval) throws EvaluationException {
int height = areaEval.getHeight();
int width = areaEval.getWidth();
for (int rrIx=0; rrIx<height; rrIx++) {
for (int rcIx=0; rcIx<width; rcIx++) {
ValueEval ve = areaEval.getRelativeValue(rrIx, rcIx);
if (ve instanceof ErrorEval) {
throw new EvaluationException((ErrorEval) ve);
}
}
}
}
private static boolean areasAllSameSize(AreaEval[] args, int height, int width) {
for (int i = 0; i < args.length; i++) {
AreaEval areaEval = args[i];
// check that height and width match
if(areaEval.getHeight() != height) {
return false;
}
if(areaEval.getWidth() != width) {
return false;
}
}
return true;
}
/**
* Determines a <code>double</code> value for the specified <code>ValueEval</code>.
* @param isScalarProduct <code>false</code> for SUMPRODUCTs over area refs.
* @throws EvaluationException if <code>ve</code> represents an error value.
* <p/>
* Note - string values and empty cells are interpreted differently depending on
* <code>isScalarProduct</code>. 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, boolean isScalarProduct) throws EvaluationException {
if(ve instanceof 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 EvaluationException(ErrorEval.VALUE_INVALID);
}
return 0;
}
if(ve instanceof ErrorEval) {
throw new EvaluationException((ErrorEval)ve);
}
if(ve instanceof StringEval) {
if(isScalarProduct) {
throw new EvaluationException(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 instanceof NumericValueEval) {
NumericValueEval nve = (NumericValueEval) ve;
return nve.getNumberValue();
}
throw new RuntimeException("Unexpected value eval class ("
+ ve.getClass().getName() + ")");
}
}