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 2008/09/11 01:37:22 UTC
svn commit: r694065 - in /poi/trunk/src:
java/org/apache/poi/hssf/record/formula/eval/
java/org/apache/poi/hssf/record/formula/functions/
testcases/org/apache/poi/hssf/data/
testcases/org/apache/poi/hssf/record/formula/functions/
Author: josh
Date: Wed Sep 10 16:37:22 2008
New Revision: 694065
URL: http://svn.apache.org/viewvc?rev=694065&view=rev
Log:
fixed special cases of MODE function
Modified:
poi/trunk/src/java/org/apache/poi/hssf/record/formula/eval/ValueEvalToNumericXlator.java
poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/AggregateFunction.java
poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Maxa.java
poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Mina.java
poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Mode.java
poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/StatsLib.java
poi/trunk/src/testcases/org/apache/poi/hssf/data/FormulaEvalTestData.xls
poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/functions/TestStatsLib.java
Modified: poi/trunk/src/java/org/apache/poi/hssf/record/formula/eval/ValueEvalToNumericXlator.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/record/formula/eval/ValueEvalToNumericXlator.java?rev=694065&r1=694064&r2=694065&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/record/formula/eval/ValueEvalToNumericXlator.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/record/formula/eval/ValueEvalToNumericXlator.java Wed Sep 10 16:37:22 2008
@@ -23,11 +23,9 @@
*/
public final class ValueEvalToNumericXlator {
- public static final int STRING_IS_PARSED = 0x0001;
- public static final int BOOL_IS_PARSED = 0x0002;
- public static final int BLANK_IS_PARSED = 0x0004; // => blanks are not ignored, converted to 0
+ public static final int BLANK_IS_PARSED = 0x0001; // => blanks are not ignored, converted to 0
- public static final int REF_BOOL_IS_PARSED = 0x0008;
+ public static final int REF_BOOL_IS_PARSED = 0x0002;
private final int flags;
@@ -59,9 +57,7 @@
}
if (eval instanceof BoolEval) {
- return ((flags & BOOL_IS_PARSED) > 0)
- ? (NumericValueEval) eval
- : xlateBlankEval();
+ return eval;
}
if (eval instanceof StringEval) {
@@ -135,17 +131,13 @@
* uses the relevant flags to decode the StringEval
* @param eval
*/
- private ValueEval xlateStringEval(StringEval eval) {
+ private static ValueEval xlateStringEval(StringEval eval) {
- if ((flags & STRING_IS_PARSED) > 0) {
- String s = eval.getStringValue();
- Double d = OperandResolver.parseDouble(s);
- if(d == null) {
- return ErrorEval.VALUE_INVALID;
- }
- return new NumberEval(d.doubleValue());
+ String s = eval.getStringValue();
+ Double d = OperandResolver.parseDouble(s);
+ if(d == null) {
+ return ErrorEval.VALUE_INVALID;
}
- // else strings are errors?
- return ErrorEval.VALUE_INVALID;
+ return new NumberEval(d.doubleValue());
}
}
Modified: poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/AggregateFunction.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/AggregateFunction.java?rev=694065&r1=694064&r2=694065&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/AggregateFunction.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/AggregateFunction.java Wed Sep 10 16:37:22 2008
@@ -28,10 +28,7 @@
*/
public abstract class AggregateFunction extends MultiOperandNumericFunction {
private static final ValueEvalToNumericXlator DEFAULT_NUM_XLATOR =
- new ValueEvalToNumericXlator((short) (
- ValueEvalToNumericXlator.BOOL_IS_PARSED
- | ValueEvalToNumericXlator.STRING_IS_PARSED
- ));
+ new ValueEvalToNumericXlator(0);
protected ValueEval attemptXlateToNumeric(ValueEval ve) {
return DEFAULT_NUM_XLATOR.attemptXlateToNumeric(ve);
Modified: poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Maxa.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Maxa.java?rev=694065&r1=694064&r2=694065&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Maxa.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Maxa.java Wed Sep 10 16:37:22 2008
@@ -27,9 +27,7 @@
public final class Maxa extends MultiOperandNumericFunction {
private static final ValueEvalToNumericXlator DEFAULT_NUM_XLATOR =
new ValueEvalToNumericXlator((short) (
- ValueEvalToNumericXlator.BOOL_IS_PARSED
- | ValueEvalToNumericXlator.REF_BOOL_IS_PARSED
- | ValueEvalToNumericXlator.STRING_IS_PARSED
+ ValueEvalToNumericXlator.REF_BOOL_IS_PARSED
| ValueEvalToNumericXlator.BLANK_IS_PARSED
));
Modified: poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Mina.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Mina.java?rev=694065&r1=694064&r2=694065&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Mina.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Mina.java Wed Sep 10 16:37:22 2008
@@ -27,10 +27,8 @@
public final class Mina extends MultiOperandNumericFunction {
private static final ValueEvalToNumericXlator DEFAULT_NUM_XLATOR =
new ValueEvalToNumericXlator((short) (
- ValueEvalToNumericXlator.BOOL_IS_PARSED
- | ValueEvalToNumericXlator.REF_BOOL_IS_PARSED
- | ValueEvalToNumericXlator.STRING_IS_PARSED
- | ValueEvalToNumericXlator.BLANK_IS_PARSED
+ ValueEvalToNumericXlator.REF_BOOL_IS_PARSED
+ | ValueEvalToNumericXlator.BLANK_IS_PARSED
));
protected ValueEval attemptXlateToNumeric(ValueEval ve) {
Modified: poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Mode.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Mode.java?rev=694065&r1=694064&r2=694065&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Mode.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Mode.java Wed Sep 10 16:37:22 2008
@@ -17,35 +17,118 @@
package org.apache.poi.hssf.record.formula.functions;
+import java.util.ArrayList;
+import java.util.Arrays;
+import java.util.List;
+
+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.BoolEval;
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.StringEval;
import org.apache.poi.hssf.record.formula.eval.ValueEval;
-import org.apache.poi.hssf.record.formula.eval.ValueEvalToNumericXlator;
/**
* @author Amol S. Deshmukh < amolweb at ya hoo dot com >
- *
+ *
*/
-public class Mode extends MultiOperandNumericFunction {
- private static final ValueEvalToNumericXlator DEFAULT_NUM_XLATOR =
- new ValueEvalToNumericXlator(0);
+public class Mode implements Function {
/**
- * this is the default impl for the factory method getXlator
- * of the super class NumericFunction. Subclasses can override this method
- * if they desire to return a different ValueEvalToNumericXlator instance
- * than the default.
+ * if v is zero length or contains no duplicates, return value is
+ * Double.NaN. Else returns the value that occurs most times and if there is
+ * a tie, returns the first such value.
+ *
+ * @param v
*/
- protected ValueEval attemptXlateToNumeric(ValueEval ve) {
- return DEFAULT_NUM_XLATOR.attemptXlateToNumeric(ve);
+ public static double evaluate(double[] v) throws EvaluationException {
+ if (v.length < 2) {
+ throw new EvaluationException(ErrorEval.NA);
+ }
+
+ // very naive impl, may need to be optimized
+ int[] counts = new int[v.length];
+ Arrays.fill(counts, 1);
+ for (int i = 0, iSize = v.length; i < iSize; i++) {
+ for (int j = i + 1, jSize = v.length; j < jSize; j++) {
+ if (v[i] == v[j])
+ counts[i]++;
+ }
+ }
+ double maxv = 0;
+ int maxc = 0;
+ for (int i = 0, iSize = counts.length; i < iSize; i++) {
+ if (counts[i] > maxc) {
+ maxv = v[i];
+ maxc = counts[i];
+ }
+ }
+ if (maxc > 1) {
+ return maxv;
+ }
+ throw new EvaluationException(ErrorEval.NA);
+
}
- protected double evaluate(double[] values) throws EvaluationException {
- double d = StatsLib.mode(values);
- if (Double.isNaN(d)) {
- // TODO - StatsLib is returning NaN to denote 'no duplicate values'
- throw new EvaluationException(ErrorEval.NA);
+ public Eval evaluate(Eval[] args, int srcCellRow, short srcCellCol) {
+ double result;
+ try {
+ List temp = new ArrayList();
+ for (int i = 0; i < args.length; i++) {
+ collectValues(args[i], temp);
+ }
+ double[] values = new double[temp.size()];
+ for (int i = 0; i < values.length; i++) {
+ values[i] = ((Double) temp.get(i)).doubleValue();
+ }
+ result = evaluate(values);
+ } catch (EvaluationException e) {
+ return e.getErrorEval();
+ }
+ return new NumberEval(result);
+ }
+
+ private static void collectValues(Eval arg, List temp) throws EvaluationException {
+ if (arg instanceof AreaEval) {
+ AreaEval ae = (AreaEval) arg;
+ int width = ae.getWidth();
+ int height = ae.getHeight();
+ for (int rrIx = 0; rrIx < height; rrIx++) {
+ for (int rcIx = 0; rcIx < width; rcIx++) {
+ ValueEval ve1 = ae.getRelativeValue(rrIx, rcIx);
+ collectValue(ve1, temp, false);
+ }
+ }
+ return;
+ }
+ if (arg instanceof RefEval) {
+ RefEval re = (RefEval) arg;
+ collectValue(re.getInnerValueEval(), temp, true);
+ return;
+ }
+ collectValue(arg, temp, true);
+
+ }
+
+ private static void collectValue(Eval arg, List temp, boolean mustBeNumber)
+ throws EvaluationException {
+ if (arg instanceof ErrorEval) {
+ throw new EvaluationException((ErrorEval) arg);
+ }
+ if (arg == BlankEval.INSTANCE || arg instanceof BoolEval || arg instanceof StringEval) {
+ if (mustBeNumber) {
+ throw EvaluationException.invalidValue();
+ }
+ return;
+ }
+ if (arg instanceof NumberEval) {
+ temp.add(new Double(((NumberEval) arg).getNumberValue()));
+ return;
}
- return d;
+ throw new RuntimeException("Unexpected value type (" + arg.getClass().getName() + ")");
}
}
Modified: poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/StatsLib.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/StatsLib.java?rev=694065&r1=694064&r2=694065&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/StatsLib.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/StatsLib.java Wed Sep 10 16:37:22 2008
@@ -60,36 +60,6 @@
return r;
}
- /**
- * if v is zero length or contains no duplicates, return value
- * is Double.NaN. Else returns the value that occurs most times
- * and if there is a tie, returns the first such value.
- * @param v
- */
- public static double mode(double[] v) {
- double r = Double.NaN;
-
- // very naive impl, may need to be optimized
- if (v!=null && v.length > 1) {
- int[] counts = new int[v.length];
- Arrays.fill(counts, 1);
- for (int i=0, iSize=v.length; i<iSize; i++) {
- for (int j=i+1, jSize=v.length; j<jSize; j++) {
- if (v[i] == v[j]) counts[i]++;
- }
- }
- double maxv = 0;
- int maxc = 0;
- for (int i=0, iSize=counts.length; i<iSize; i++) {
- if (counts[i] > maxc) {
- maxv = v[i];
- maxc = counts[i];
- }
- }
- r = (maxc > 1) ? maxv : Double.NaN; // "no-dups" check
- }
- return r;
- }
public static double median(double[] v) {
double r = Double.NaN;
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=694065&r1=694064&r2=694065&view=diff
==============================================================================
Binary files - no diff available.
Modified: poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/functions/TestStatsLib.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/functions/TestStatsLib.java?rev=694065&r1=694064&r2=694065&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/functions/TestStatsLib.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/functions/TestStatsLib.java Wed Sep 10 16:37:22 2008
@@ -20,6 +20,11 @@
*/
package org.apache.poi.hssf.record.formula.functions;
+import junit.framework.AssertionFailedError;
+
+import org.apache.poi.hssf.record.formula.eval.ErrorEval;
+import org.apache.poi.hssf.record.formula.eval.EvaluationException;
+
/**
* @author Amol S. Deshmukh < amolweb at ya hoo dot com >
@@ -181,49 +186,53 @@
}
public void testMode() {
- double[] v = null;
+ double[] v;
double d, x = 0;
v = new double[] {1,2,3,4,5,6,7,8,9,10};
- d = StatsLib.mode(v);
- x = Double.NaN;
- assertEquals("mode ", x, d);
+ confirmMode(v, null);
v = new double[] {1,1,1,1,1,1,1,1,1,1};
- d = StatsLib.mode(v);
- x = 1;
- assertEquals("mode ", x, d);
+ confirmMode(v, 1.0);
v = new double[] {0,0,0,0,0,0,0,0,0,0};
- d = StatsLib.mode(v);
- x = 0;
- assertEquals("mode ", x, d);
+ confirmMode(v, 0.0);
v = new double[] {1,2,1,2,1,2,1,2,1,2};
- d = StatsLib.mode(v);
- x = 1;
- assertEquals("mode ", x, d);
+ confirmMode(v, 1.0);
v = new double[] {123.12,33.3333,2d/3d,5.37828,0.999};
- d = StatsLib.mode(v);
- x = Double.NaN;
- assertEquals("mode ", x, d);
+ confirmMode(v, null);
v = new double[] {-1,-2,-3,-4,-5,-6,-7,-8,-9,-10};
- d = StatsLib.mode(v);
- x = Double.NaN;
- assertEquals("mode ", x, d);
+ confirmMode(v, null);
v = new double[] {1,2,3,4,1,1,1,1,0,0,0,0,0};
- d = StatsLib.mode(v);
- x = 1;
- assertEquals("mode ", x, d);
+ confirmMode(v, 1.0);
v = new double[] {0,1,2,3,4,1,1,1,0,0,0,0,1};
- d = StatsLib.mode(v);
- x = 0;
- assertEquals("mode ", x, d);
+ confirmMode(v, 0.0);
+ }
+ private static void confirmMode(double[] v, double expectedResult) {
+ confirmMode(v, new Double(expectedResult));
+ }
+ private static void confirmMode(double[] v, Double expectedResult) {
+ double actual;
+ try {
+ actual = Mode.evaluate(v);
+ if (expectedResult == null) {
+ throw new AssertionFailedError("Expected N/A exception was not thrown");
+ }
+ } catch (EvaluationException e) {
+ if (expectedResult == null) {
+ assertEquals(ErrorEval.NA, e.getErrorEval());
+ return;
+ }
+ throw new RuntimeException(e);
+ }
+ assertEquals("mode", expectedResult.doubleValue(), actual);
}
+
public void testStddev() {
double[] v = null;
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org