You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by jo...@apache.org on 2009/01/13 23:03:00 UTC
svn commit: r734252 - in /poi/trunk/src:
documentation/content/xdocs/changes.xml
documentation/content/xdocs/status.xml
java/org/apache/poi/hssf/record/formula/functions/Sumif.java
testcases/org/apache/poi/hssf/data/FormulaEvalTestData.xls
Author: josh
Date: Tue Jan 13 14:02:52 2009
New Revision: 734252
URL: http://svn.apache.org/viewvc?rev=734252&view=rev
Log:
Bugzilla 46523 - added implementation for SUMIF
Modified:
poi/trunk/src/documentation/content/xdocs/changes.xml
poi/trunk/src/documentation/content/xdocs/status.xml
poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Sumif.java
poi/trunk/src/testcases/org/apache/poi/hssf/data/FormulaEvalTestData.xls
Modified: poi/trunk/src/documentation/content/xdocs/changes.xml
URL: http://svn.apache.org/viewvc/poi/trunk/src/documentation/content/xdocs/changes.xml?rev=734252&r1=734251&r2=734252&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/changes.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/changes.xml Tue Jan 13 14:02:52 2009
@@ -37,6 +37,7 @@
<!-- Don't forget to update status.xml too! -->
<release version="3.5-beta5" date="2008-??-??">
+ <action dev="POI-DEVELOPERS" type="add">46523 - added implementation for SUMIF function</action>
<action dev="POI-DEVELOPERS" type="add">Support for reading HSSF column styles</action>
<action dev="POI-DEVELOPERS" type="fix">Hook up POIXMLTextExtractor.getMetadataTextExtractor() to the already written POIXMLPropertiesTextExtractor</action>
<action dev="POI-DEVELOPERS" type="fix">46472 - Avoid NPE in HPSFPropertiesExtractor when no properties exist</action>
Modified: poi/trunk/src/documentation/content/xdocs/status.xml
URL: http://svn.apache.org/viewvc/poi/trunk/src/documentation/content/xdocs/status.xml?rev=734252&r1=734251&r2=734252&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/status.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/status.xml Tue Jan 13 14:02:52 2009
@@ -34,6 +34,7 @@
<!-- Don't forget to update changes.xml too! -->
<changes>
<release version="3.5-beta5" date="2008-??-??">
+ <action dev="POI-DEVELOPERS" type="add">46523 - added implementation for SUMIF function</action>
<action dev="POI-DEVELOPERS" type="add">Support for reading HSSF column styles</action>
<action dev="POI-DEVELOPERS" type="fix">Hook up POIXMLTextExtractor.getMetadataTextExtractor() to the already written POIXMLPropertiesTextExtractor</action>
<action dev="POI-DEVELOPERS" type="fix">46472 - Avoid NPE in HPSFPropertiesExtractor when no properties exist</action>
Modified: poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Sumif.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Sumif.java?rev=734252&r1=734251&r2=734252&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Sumif.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Sumif.java Tue Jan 13 14:02:52 2009
@@ -1,25 +1,123 @@
-/*
-* 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.
-*/
-/*
- * Created on May 15, 2005
- *
- */
+/* ====================================================================
+ 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;
-public class Sumif extends NotImplementedFunction {
+import org.apache.poi.hssf.record.formula.eval.AreaEval;
+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.RefEval;
+import org.apache.poi.hssf.record.formula.eval.ValueEval;
+import org.apache.poi.hssf.record.formula.functions.CountUtils.I_MatchPredicate;
+
+/**
+ * Implementation for the Excel function SUMIF<p>
+ *
+ * Syntax : <br/>
+ * SUMIF ( <b>range</b>, <b>criteria</b>, sum_range ) <br/>
+ * <table border="0" cellpadding="1" cellspacing="0" summary="Parameter descriptions">
+ * <tr><th>range</th><td>The range over which criteria is applied. Also used for addend values when the third parameter is not present</td></tr>
+ * <tr><th>criteria</th><td>The value or expression used to filter rows from <b>range</b></td></tr>
+ * <tr><th>sum_range</th><td>Locates the top-left corner of the corresponding range of addends - values to be added (after being selected by the criteria)</td></tr>
+ * </table><br/>
+ * </p>
+ * @author Josh Micich
+ */
+public final class Sumif implements Function {
+
+ public Eval evaluate(Eval[] args, int srcRowIndex, short srcColumnIndex) {
+ if (args.length < 2) {
+ return ErrorEval.VALUE_INVALID;
+ }
+
+ AreaEval aeRange;
+ AreaEval aeSum;
+ try {
+ aeRange = convertRangeArg(args[0]);
+
+ switch (args.length) {
+ case 2:
+ aeSum = aeRange;
+ break;
+ case 3:
+ aeSum = createSumRange(args[2], aeRange);
+ break;
+ default:
+ return ErrorEval.VALUE_INVALID;
+ }
+ } catch (EvaluationException e) {
+ return e.getErrorEval();
+ }
+ I_MatchPredicate mp = Countif.createCriteriaPredicate(args[1], srcRowIndex, srcRowIndex);
+ double result = sumMatchingCells(aeRange, mp, aeSum);
+ return new NumberEval(result);
+ }
+
+ private static double sumMatchingCells(AreaEval aeRange, I_MatchPredicate mp, AreaEval aeSum) {
+ int height=aeRange.getHeight();
+ int width= aeRange.getWidth();
+
+ double result = 0.0;
+ 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) {
+
+ if (!mp.matches(aeRange.getRelativeValue(relRowIndex, relColIndex))) {
+ return 0.0;
+ }
+ 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;
+ }
+
+ /**
+ * @return a range of the same dimensions as aeRange using eval to define the top left corner.
+ * @throws EvaluationException if eval is not a reference
+ */
+ private static AreaEval createSumRange(Eval eval, AreaEval aeRange) throws EvaluationException {
+ if (eval instanceof AreaEval) {
+ return ((AreaEval) eval).offset(0, aeRange.getHeight()-1, 0, aeRange.getWidth()-1);
+ }
+ if (eval instanceof RefEval) {
+ return ((RefEval)eval).offset(0, aeRange.getHeight()-1, 0, aeRange.getWidth()-1);
+ }
+ throw new EvaluationException(ErrorEval.VALUE_INVALID);
+ }
+
+ private static AreaEval convertRangeArg(Eval 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);
+ }
}
Modified: poi/trunk/src/testcases/org/apache/poi/hssf/data/FormulaEvalTestData.xls
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/data/FormulaEvalTestData.xls?rev=734252&r1=734251&r2=734252&view=diff
==============================================================================
Binary files - no diff available.
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org