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/12/08 18:19:09 UTC
svn commit: r888490 - in /poi/trunk/src: documentation/content/xdocs/
java/org/apache/poi/hssf/record/formula/eval/
java/org/apache/poi/hssf/record/formula/functions/
testcases/org/apache/poi/hssf/record/formula/functions/
Author: josh
Date: Tue Dec 8 17:19:09 2009
New Revision: 888490
URL: http://svn.apache.org/viewvc?rev=888490&view=rev
Log:
Bugzilla 48343 - added implementation of SUBTOTAL function (patch from Paul Tomlin)
Added:
poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Subtotal.java
poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/functions/TestSubtotal.java
Modified:
poi/trunk/src/documentation/content/xdocs/status.xml
poi/trunk/src/java/org/apache/poi/hssf/record/formula/eval/FunctionEval.java
poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/functions/AllIndividualFunctionEvaluationTests.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=888490&r1=888489&r2=888490&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/status.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/status.xml Tue Dec 8 17:19:09 2009
@@ -34,6 +34,7 @@
<changes>
<release version="3.7-SNAPSHOT" date="2010-??-??">
+ <action dev="POI-DEVELOPERS" type="add">48343 - added implementation of SUBTOTAL function</action>
</release>
<release version="3.6" date="2009-12-14">
<action dev="POI-DEVELOPERS" type="fix">48332 - fixed XSSFSheet autoSizeColumn() to tolerate empty RichTextString</action>
Modified: poi/trunk/src/java/org/apache/poi/hssf/record/formula/eval/FunctionEval.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/record/formula/eval/FunctionEval.java?rev=888490&r1=888489&r2=888490&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/record/formula/eval/FunctionEval.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/record/formula/eval/FunctionEval.java Tue Dec 8 17:19:09 2009
@@ -205,6 +205,7 @@
retval[342] = NumericFunction.RADIANS;
retval[343] = NumericFunction.DEGREES;
+ retval[344] = new Subtotal();
retval[345] = new Sumif();
retval[346] = new Countif();
retval[347] = new Countblank();
Added: poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Subtotal.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Subtotal.java?rev=888490&view=auto
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Subtotal.java (added)
+++ poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Subtotal.java Tue Dec 8 17:19:09 2009
@@ -0,0 +1,83 @@
+package org.apache.poi.hssf.record.formula.functions;
+
+import org.apache.poi.hssf.record.formula.eval.ErrorEval;
+import org.apache.poi.hssf.record.formula.eval.EvaluationException;
+import org.apache.poi.hssf.record.formula.eval.OperandResolver;
+import org.apache.poi.hssf.record.formula.eval.ValueEval;
+import org.apache.poi.ss.formula.eval.NotImplementedException;
+
+/**
+ * Implementation for the Excel function SUBTOTAL<p>
+ *
+ * <b>Syntax :</b> <br/>
+ * SUBTOTAL ( <b>functionCode</b>, <b>ref1</b>, ref2 ... ) <br/>
+ * <table border="1" cellpadding="1" cellspacing="0" summary="Parameter descriptions">
+ * <tr><td><b>functionCode</b></td><td>(1-11) Selects the underlying aggregate function to be used (see table below)</td></tr>
+ * <tr><td><b>ref1</b>, ref2 ...</td><td>Arguments to be passed to the underlying aggregate function</td></tr>
+ * </table><br/>
+ * </p>
+ *
+ * <table border="1" cellpadding="1" cellspacing="0" summary="Parameter descriptions">
+ * <tr><th>functionCode</th><th>Aggregate Function</th></tr>
+ * <tr align='center'><td>1</td><td>AVERAGE</td></tr>
+ * <tr align='center'><td>2</td><td>COUNT</td></tr>
+ * <tr align='center'><td>3</td><td>COUNTA</td></tr>
+ * <tr align='center'><td>4</td><td>MAX</td></tr>
+ * <tr align='center'><td>5</td><td>MIN</td></tr>
+ * <tr align='center'><td>6</td><td>PRODUCT</td></tr>
+ * <tr align='center'><td>7</td><td>STDEV</td></tr>
+ * <tr align='center'><td>8</td><td>STDEVP *</td></tr>
+ * <tr align='center'><td>9</td><td>AVERAGE</td></tr>
+ * <tr align='center'><td>10</td><td>VAR *</td></tr>
+ * <tr align='center'><td>11</td><td>VARP *</td></tr>
+ * <tr align='center'><td>101-111</td><td>*</td></tr>
+ * </table><br/>
+ * * Not implemented in POI yet. Functions 101-111 are the same as functions 1-11 but with
+ * the option 'ignore hidden values'.
+ * <p/>
+ *
+ * @author Paul Tomlin < pault at bulk sms dot com >
+ */
+public class Subtotal implements Function {
+
+ private static Function findFunction(int functionCode) throws EvaluationException {
+ switch (functionCode) {
+ case 1: return AggregateFunction.AVERAGE;
+ case 2: return new Count();
+ case 3: return new Counta();
+ case 4: return AggregateFunction.MAX;
+ case 5: return AggregateFunction.MIN;
+ case 6: return AggregateFunction.PRODUCT;
+ case 7: return AggregateFunction.STDEV;
+ case 8: throw new NotImplementedException("STDEVP");
+ case 9: return AggregateFunction.SUM;
+ case 10: throw new NotImplementedException("VAR");
+ case 11: throw new NotImplementedException("VARP");
+ }
+ if (functionCode > 100 && functionCode < 112) {
+ throw new NotImplementedException("SUBTOTAL - with 'exclude hidden values' option");
+ }
+ throw EvaluationException.invalidValue();
+ }
+
+ public ValueEval evaluate(ValueEval[] args, int srcRowIndex, int srcColumnIndex) {
+ int nInnerArgs = args.length-1; // -1: first arg is used to select from a basic aggregate function
+ if (nInnerArgs < 1) {
+ return ErrorEval.VALUE_INVALID;
+ }
+
+ Function innerFunc;
+ try {
+ ValueEval ve = OperandResolver.getSingleValue(args[0], srcRowIndex, srcColumnIndex);
+ int functionCode = OperandResolver.coerceValueToInt(ve);
+ innerFunc = findFunction(functionCode);
+ } catch (EvaluationException e) {
+ return e.getErrorEval();
+ }
+
+ ValueEval[] innerArgs = new ValueEval[nInnerArgs];
+ System.arraycopy(args, 1, innerArgs, 0, nInnerArgs);
+
+ return innerFunc.evaluate(innerArgs, srcRowIndex, srcColumnIndex);
+ }
+}
Modified: poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/functions/AllIndividualFunctionEvaluationTests.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/functions/AllIndividualFunctionEvaluationTests.java?rev=888490&r1=888489&r2=888490&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/functions/AllIndividualFunctionEvaluationTests.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/functions/AllIndividualFunctionEvaluationTests.java Tue Dec 8 17:19:09 2009
@@ -48,6 +48,7 @@
result.addTestSuite(TestPmt.class);
result.addTestSuite(TestOffset.class);
result.addTestSuite(TestRowCol.class);
+ result.addTestSuite(TestSubtotal.class);
result.addTestSuite(TestSumif.class);
result.addTestSuite(TestSumproduct.class);
result.addTestSuite(TestStatsLib.class);
Added: poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/functions/TestSubtotal.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/functions/TestSubtotal.java?rev=888490&view=auto
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/functions/TestSubtotal.java (added)
+++ poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/functions/TestSubtotal.java Tue Dec 8 17:19:09 2009
@@ -0,0 +1,55 @@
+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.NumberEval;
+import org.apache.poi.hssf.record.formula.eval.ValueEval;
+
+import junit.framework.TestCase;
+
+/**
+ * Tests for {@link Subtotal}
+ *
+ * @author Paul Tomlin
+ */
+public final class TestSubtotal extends TestCase {
+ private static final int FUNCTION_AVERAGE = 1;
+ private static final int FUNCTION_COUNT = 2;
+ private static final int FUNCTION_MAX = 4;
+ private static final int FUNCTION_MIN = 5;
+ private static final int FUNCTION_PRODUCT = 6;
+ private static final int FUNCTION_STDEV = 7;
+ private static final int FUNCTION_SUM = 9;
+
+ private static final double[] TEST_VALUES0 = {
+ 1, 2,
+ 3, 4,
+ 5, 6,
+ 7, 8,
+ 9, 10
+ };
+
+ private static void confirmSubtotal(int function, double expected) {
+ ValueEval[] values = new ValueEval[TEST_VALUES0.length];
+ for (int i = 0; i < TEST_VALUES0.length; i++) {
+ values[i] = new NumberEval(TEST_VALUES0[i]);
+ }
+
+ AreaEval arg1 = EvalFactory.createAreaEval("C1:D5", values);
+ ValueEval args[] = { new NumberEval(function), arg1 };
+
+ ValueEval result = new Subtotal().evaluate(args, 0, 0);
+
+ assertEquals(NumberEval.class, result.getClass());
+ assertEquals(expected, ((NumberEval) result).getNumberValue(), 0.0);
+ }
+
+ public void testBasics() {
+ confirmSubtotal(FUNCTION_SUM, 55.0);
+ confirmSubtotal(FUNCTION_AVERAGE, 5.5);
+ confirmSubtotal(FUNCTION_COUNT, 10.0);
+ confirmSubtotal(FUNCTION_MAX, 10.0);
+ confirmSubtotal(FUNCTION_MIN, 1.0);
+ confirmSubtotal(FUNCTION_PRODUCT, 3628800.0);
+ confirmSubtotal(FUNCTION_STDEV, 3.0276503540974917);
+ }
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org