https://bz.apache.org/bugzilla/show_bug.cgi?id=65475
Bug ID: 65475
Summary: SUMIF function's difference between POI and Micro
Excel
Product: POI
Version: 5.0.0-FINAL
Hardware: PC
Status: NEW
Severity: normal
Priority: P2
Component: XSSF
Assignee: [email protected]
Reporter: [email protected]
Target Milestone: ---
e.g. There is a formula cell contains SUMIF function. IF it's addend is
#N/A,Micro Excel would result #N/A while in Apache POI it can still get a
numberical result because POI would replace a non numberical addend with zero.
So, why not just alter the Sumif class in org.apache.poi.ss.formula.functions
package as follows:
private static ValueEval eval(int srcRowIndex, int srcColumnIndex, ValueEval
arg1, AreaEval aeRange, AreaEval aeSum) {
I_MatchPredicate mp = Countif.createCriteriaPredicate(arg1,
srcRowIndex, srcColumnIndex);
if (mp == null) {
return NumberEval.ZERO;
} else {
try {
double result = sumMatchingCells(aeRange, mp, aeSum);
return new NumberEval(result);
} catch (EvaluationException var) {
return var.getErrorEval();
}
}
}
private static double sumMatchingCells(AreaEval aeRange, I_MatchPredicate
mp, AreaEval aeSum) throws EvaluationException {
int height = aeRange.getHeight();
int width = aeRange.getWidth();
double result = 0.0D;
for(int r = 0; r < height; ++r) {
for(int c = 0; c < width; ++c) {
result += accumulate(aeRange, mp, aeSum, r, c);
}
}
return result;
}
private static double accumulate(AreaEval aeRange, I_MatchPredicate mp,
AreaEval aeSum, int relRowIndex, int relColIndex) throws EvaluationException {
if (!mp.matches(aeRange.getRelativeValue(relRowIndex, relColIndex))) {
return 0.0D;
} else {
ValueEval addend = aeSum.getRelativeValue(relRowIndex,
relColIndex);
if (addend instanceof NumberEval) {
return ((NumberEval) addend).getNumberValue();
} else {
throw new EvaluationException(ErrorEval.NA);
}
}
}
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
