poi-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ye...@apache.org
Subject svn commit: r1234305 - in /poi/trunk: src/documentation/content/xdocs/ src/java/org/apache/poi/ss/formula/atp/ src/java/org/apache/poi/ss/formula/functions/ src/testcases/org/apache/poi/ss/formula/functions/ test-data/spreadsheet/
Date Sat, 21 Jan 2012 11:50:51 GMT
Author: yegor
Date: Sat Jan 21 11:50:49 2012
New Revision: 1234305

URL: http://svn.apache.org/viewvc?rev=1234305&view=rev
Log:
Added implementation for SUMIFS(), see Bugzilla 52462

Added:
    poi/trunk/src/java/org/apache/poi/ss/formula/functions/Sumifs.java
    poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestSumifs.java
    poi/trunk/test-data/spreadsheet/sumifs.xls   (with props)
Modified:
    poi/trunk/src/documentation/content/xdocs/status.xml
    poi/trunk/src/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java

Modified: poi/trunk/src/documentation/content/xdocs/status.xml
URL: http://svn.apache.org/viewvc/poi/trunk/src/documentation/content/xdocs/status.xml?rev=1234305&r1=1234304&r2=1234305&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/status.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/status.xml Sat Jan 21 11:50:49 2012
@@ -34,6 +34,7 @@
 
     <changes>
         <release version="3.8-beta6" date="2012-??-??">
+           <action dev="poi-developers" type="add">52462 - Added implementation for
SUMIFS()</action>
            <action dev="poi-developers" type="add">POIXMLPropertiesTextExtractor support
for extracting custom OOXML properties as text</action>
            <action dev="poi-developers" type="fix">52449 - Support writing XWPF documents
with glossaries (Glossaries are not yet supported, but can now be written out again without
changes)</action>
            <action dev="poi-developers" type="fix">52446 - Handle files which have
been truncated by a few bytes in NPropertyTable</action>

Modified: poi/trunk/src/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java?rev=1234305&r1=1234304&r2=1234305&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java Sat Jan 21 11:50:49
2012
@@ -15,6 +15,7 @@ import java.util.Map;
 
 import org.apache.poi.ss.formula.eval.ValueEval;
 import org.apache.poi.ss.formula.functions.FreeRefFunction;
+import org.apache.poi.ss.formula.functions.Sumifs;
 import org.apache.poi.ss.formula.udf.UDFFinder;
 import org.apache.poi.ss.formula.OperationEvaluationContext;
 import org.apache.poi.ss.formula.eval.NotImplementedException;
@@ -48,6 +49,10 @@ public final class AnalysisToolPak imple
     }
 
     public FreeRefFunction findFunction(String name) {
+        // functions that are available in Excel 2007+ have a prefix _xlfn.
+        // if you save such a .xlsx workbook as .xls
+        if(name.startsWith("_xlfn.")) name = name.substring(6);
+
         return _functionsByName.get(name.toUpperCase());
     }
 
@@ -150,7 +155,7 @@ public final class AnalysisToolPak imple
         r(m, "RTD", null);
         r(m, "SERIESSUM", null);
         r(m, "SQRTPI", null);
-        r(m, "SUMIFS", null);
+        r(m, "SUMIFS", Sumifs.instance);
         r(m, "TBILLEQ", null);
         r(m, "TBILLPRICE", null);
         r(m, "TBILLYIELD", null);

Added: poi/trunk/src/java/org/apache/poi/ss/formula/functions/Sumifs.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/functions/Sumifs.java?rev=1234305&view=auto
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/functions/Sumifs.java (added)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/functions/Sumifs.java Sat Jan 21 11:50:49
2012
@@ -0,0 +1,147 @@
+/*
+ *  ====================================================================
+ *    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.*;
+import org.apache.poi.ss.formula.functions.CountUtils.I_MatchPredicate;
+
+/**
+ * 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) {
+        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);
+
+            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
+     */
+    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();
+            }
+        }
+    }
+
+    /**
+     *
+     * @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);
+	}
+
+}

Added: poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestSumifs.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestSumifs.java?rev=1234305&view=auto
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestSumifs.java (added)
+++ poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestSumifs.java Sat Jan 21
11:50:49 2012
@@ -0,0 +1,268 @@
+/*
+ *  ====================================================================
+ *    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 junit.framework.AssertionFailedError;
+import junit.framework.TestCase;
+import org.apache.poi.hssf.HSSFTestDataSamples;
+import org.apache.poi.hssf.usermodel.*;
+import org.apache.poi.ss.formula.OperationEvaluationContext;
+import org.apache.poi.ss.formula.eval.*;
+import org.apache.poi.ss.usermodel.CellValue;
+
+/**
+ * Test cases for SUMIFS()
+ *
+ * @author Yegor Kozlov
+ */
+public final class TestSumifs extends TestCase {
+
+    private static final OperationEvaluationContext EC = new OperationEvaluationContext(null,
null, 0, 1, 0, null);
+
+	private static ValueEval invokeSumifs(ValueEval[] args, OperationEvaluationContext ec) {
+		return new Sumifs().evaluate(args, EC);
+	}
+	private static void confirmDouble(double expected, ValueEval actualEval) {
+		if(!(actualEval instanceof NumericValueEval)) {
+			throw new AssertionFailedError("Expected numeric result");
+		}
+		NumericValueEval nve = (NumericValueEval)actualEval;
+		assertEquals(expected, nve.getNumberValue(), 0);
+	}
+
+    private static void confirm(double expectedResult, ValueEval[] args) {
+        confirmDouble(expectedResult, invokeSumifs(args, EC));
+    }
+
+    /**
+     *  Example 1 from
+     *  http://office.microsoft.com/en-us/excel-help/sumifs-function-HA010047504.aspx
+     */
+	public void testExample1() {
+        // mimic test sample from http://office.microsoft.com/en-us/excel-help/sumifs-function-HA010047504.aspx
+        ValueEval[] a2a9 = new ValueEval[] {
+                new NumberEval(5),
+                new NumberEval(4),
+                new NumberEval(15),
+                new NumberEval(3),
+                new NumberEval(22),
+                new NumberEval(12),
+                new NumberEval(10),
+                new NumberEval(33)
+        };
+
+        ValueEval[] b2b9 = new ValueEval[] {
+                new StringEval("Apples"),
+                new StringEval("Apples"),
+                new StringEval("Artichokes"),
+                new StringEval("Artichokes"),
+                new StringEval("Bananas"),
+                new StringEval("Bananas"),
+                new StringEval("Carrots"),
+                new StringEval("Carrots"),
+        };
+
+        ValueEval[] c2c9 = new ValueEval[] {
+                new NumberEval(1),
+                new NumberEval(2),
+                new NumberEval(1),
+                new NumberEval(2),
+                new NumberEval(1),
+                new NumberEval(2),
+                new NumberEval(1),
+                new NumberEval(2)
+        };
+
+        ValueEval[] args;
+        // "=SUMIFS(A2:A9, B2:B9, "=A*", C2:C9, 1)"
+        args = new ValueEval[]{
+                EvalFactory.createAreaEval("A2:A9", a2a9),
+                EvalFactory.createAreaEval("B2:B9", b2b9),
+                new StringEval("A*"),
+                EvalFactory.createAreaEval("C2:C9", c2c9),
+                new NumberEval(1),
+        };
+        confirm(20.0, args);
+
+        // "=SUMIFS(A2:A9, B2:B9, "<>Bananas", C2:C9, 1)"
+        args = new ValueEval[]{
+                EvalFactory.createAreaEval("A2:A9", a2a9),
+                EvalFactory.createAreaEval("B2:B9", b2b9),
+                new StringEval("<>Bananas"),
+                EvalFactory.createAreaEval("C2:C9", c2c9),
+                new NumberEval(1),
+        };
+        confirm(30.0, args);
+
+        // a test case that returns ErrorEval.VALUE_INVALID :
+        // the dimensions of the first and second criteria ranges are different
+        // "=SUMIFS(A2:A9, B2:B8, "<>Bananas", C2:C9, 1)"
+        args = new ValueEval[]{
+                EvalFactory.createAreaEval("A2:A9", a2a9),
+                EvalFactory.createAreaEval("B2:B8", new ValueEval[] {
+                        new StringEval("Apples"),
+                        new StringEval("Apples"),
+                        new StringEval("Artichokes"),
+                        new StringEval("Artichokes"),
+                        new StringEval("Bananas"),
+                        new StringEval("Bananas"),
+                        new StringEval("Carrots"),
+                }),
+                new StringEval("<>Bananas"),
+                EvalFactory.createAreaEval("C2:C9", c2c9),
+                new NumberEval(1),
+        };
+        assertEquals(ErrorEval.VALUE_INVALID, invokeSumifs(args, EC));
+
+	}
+
+    /**
+     *  Example 2 from
+     *  http://office.microsoft.com/en-us/excel-help/sumifs-function-HA010047504.aspx
+     */
+    public void testExample2() {
+        ValueEval[] b2e2 = new ValueEval[] {
+                new NumberEval(100),
+                new NumberEval(390),
+                new NumberEval(8321),
+                new NumberEval(500)
+        };
+        // 1%	0.5%	3%	4%
+        ValueEval[] b3e3 = new ValueEval[] {
+                new NumberEval(0.01),
+                new NumberEval(0.005),
+                new NumberEval(0.03),
+                new NumberEval(0.04)
+        };
+
+        // 1%	1.3%	2.1%	2%
+        ValueEval[] b4e4 = new ValueEval[] {
+                new NumberEval(0.01),
+                new NumberEval(0.013),
+                new NumberEval(0.021),
+                new NumberEval(0.02)
+        };
+
+        // 0.5%	3%	1%	4%
+        ValueEval[] b5e5 = new ValueEval[] {
+                new NumberEval(0.005),
+                new NumberEval(0.03),
+                new NumberEval(0.01),
+                new NumberEval(0.04)
+        };
+
+        ValueEval[] args;
+
+        // "=SUMIFS(B2:E2, B3:E3, ">3%", B4:E4, ">=2%")"
+        args = new ValueEval[]{
+                EvalFactory.createAreaEval("B2:E2", b2e2),
+                EvalFactory.createAreaEval("B3:E3", b3e3),
+                new StringEval(">0.03"), // 3% in the MSFT example
+                EvalFactory.createAreaEval("B4:E4", b4e4),
+                new StringEval(">=0.02"),   // 2% in the MSFT example
+        };
+        confirm(500.0, args);
+    }
+
+    /**
+     *  Example 3 from
+     *  http://office.microsoft.com/en-us/excel-help/sumifs-function-HA010047504.aspx
+     */
+    public void testExample3() {
+        //3.3	0.8	5.5	5.5
+        ValueEval[] b2e2 = new ValueEval[] {
+                new NumberEval(3.3),
+                new NumberEval(0.8),
+                new NumberEval(5.5),
+                new NumberEval(5.5)
+        };
+        // 55	39	39	57.5
+        ValueEval[] b3e3 = new ValueEval[] {
+                new NumberEval(55),
+                new NumberEval(39),
+                new NumberEval(39),
+                new NumberEval(57.5)
+        };
+
+        // 6.5	19.5	6	6.5
+        ValueEval[] b4e4 = new ValueEval[] {
+                new NumberEval(6.5),
+                new NumberEval(19.5),
+                new NumberEval(6),
+                new NumberEval(6.5)
+        };
+
+        ValueEval[] args;
+
+        // "=SUMIFS(B2:E2, B3:E3, ">=40", B4:E4, "<10")"
+        args = new ValueEval[]{
+                EvalFactory.createAreaEval("B2:E2", b2e2),
+                EvalFactory.createAreaEval("B3:E3", b3e3),
+                new StringEval(">=40"),
+                EvalFactory.createAreaEval("B4:E4", b4e4),
+                new StringEval("<10"),
+        };
+        confirm(8.8, args);
+    }
+
+    /**
+     *  Example 5 from
+     *  http://office.microsoft.com/en-us/excel-help/sumifs-function-HA010047504.aspx
+     *
+     *  Criteria entered as reference and by using wildcard characters
+     */
+    public void testFromFile() {
+
+        HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("sumifs.xls");
+        HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
+
+        HSSFSheet example1 = wb.getSheet("Example 1");
+        HSSFCell ex1cell1 = example1.getRow(10).getCell(2);
+        fe.evaluate(ex1cell1);
+        assertEquals(20.0, ex1cell1.getNumericCellValue());
+        HSSFCell ex1cell2 = example1.getRow(11).getCell(2);
+        fe.evaluate(ex1cell2);
+        assertEquals(30.0, ex1cell2.getNumericCellValue());
+
+        HSSFSheet example2 = wb.getSheet("Example 2");
+        HSSFCell ex2cell1 = example2.getRow(6).getCell(2);
+        fe.evaluate(ex2cell1);
+        assertEquals(500.0, ex2cell1.getNumericCellValue());
+        HSSFCell ex2cell2 = example2.getRow(7).getCell(2);
+        fe.evaluate(ex2cell2);
+        assertEquals(8711.0, ex2cell2.getNumericCellValue());
+
+        HSSFSheet example3 = wb.getSheet("Example 3");
+        HSSFCell ex3cell = example3.getRow(5).getCell(2);
+        fe.evaluate(ex3cell);
+        assertEquals(8,8, ex3cell.getNumericCellValue());
+
+        HSSFSheet example4 = wb.getSheet("Example 4");
+        HSSFCell ex4cell = example4.getRow(8).getCell(2);
+        fe.evaluate(ex4cell);
+        assertEquals(3.5, ex4cell.getNumericCellValue());
+
+        HSSFSheet example5 = wb.getSheet("Example 5");
+        HSSFCell ex5cell = example5.getRow(8).getCell(2);
+        fe.evaluate(ex5cell);
+        assertEquals(625000., ex5cell.getNumericCellValue());
+
+    }
+}

Added: poi/trunk/test-data/spreadsheet/sumifs.xls
URL: http://svn.apache.org/viewvc/poi/trunk/test-data/spreadsheet/sumifs.xls?rev=1234305&view=auto
==============================================================================
Binary file - no diff available.

Propchange: poi/trunk/test-data/spreadsheet/sumifs.xls
------------------------------------------------------------------------------
    svn:mime-type = application/octet-stream



---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org


Mime
View raw message