From 9f121af2d0f56b21bf59791e3102287c45772c13 Mon Sep 17 00:00:00 2001 From: Josh Micich Date: Wed, 10 Sep 2008 23:37:22 +0000 Subject: [PATCH] fixed special cases of MODE function git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@694065 13f79535-47bb-0310-9956-ffa450edef68 --- .../eval/ValueEvalToNumericXlator.java | 26 ++-- .../formula/functions/AggregateFunction.java | 5 +- .../hssf/record/formula/functions/Maxa.java | 4 +- .../hssf/record/formula/functions/Mina.java | 6 +- .../hssf/record/formula/functions/Mode.java | 117 +++++++++++++++--- .../record/formula/functions/StatsLib.java | 30 ----- .../poi/hssf/data/FormulaEvalTestData.xls | Bin 156160 -> 156160 bytes .../formula/functions/TestStatsLib.java | 59 +++++---- 8 files changed, 147 insertions(+), 100 deletions(-) diff --git a/src/java/org/apache/poi/hssf/record/formula/eval/ValueEvalToNumericXlator.java b/src/java/org/apache/poi/hssf/record/formula/eval/ValueEvalToNumericXlator.java index 37f0b9b4b1..bb04e411b1 100644 --- a/src/java/org/apache/poi/hssf/record/formula/eval/ValueEvalToNumericXlator.java +++ b/src/java/org/apache/poi/hssf/record/formula/eval/ValueEvalToNumericXlator.java @@ -23,11 +23,9 @@ package org.apache.poi.hssf.record.formula.eval; */ 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 @@ public final class ValueEvalToNumericXlator { } if (eval instanceof BoolEval) { - return ((flags & BOOL_IS_PARSED) > 0) - ? (NumericValueEval) eval - : xlateBlankEval(); + return eval; } if (eval instanceof StringEval) { @@ -135,17 +131,13 @@ public final class ValueEvalToNumericXlator { * 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()); } } diff --git a/src/java/org/apache/poi/hssf/record/formula/functions/AggregateFunction.java b/src/java/org/apache/poi/hssf/record/formula/functions/AggregateFunction.java index f04e64070b..0758686228 100644 --- a/src/java/org/apache/poi/hssf/record/formula/functions/AggregateFunction.java +++ b/src/java/org/apache/poi/hssf/record/formula/functions/AggregateFunction.java @@ -28,10 +28,7 @@ import org.apache.poi.hssf.record.formula.eval.ValueEvalToNumericXlator; */ 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); diff --git a/src/java/org/apache/poi/hssf/record/formula/functions/Maxa.java b/src/java/org/apache/poi/hssf/record/formula/functions/Maxa.java index 7ae722889e..95c4c8c911 100644 --- a/src/java/org/apache/poi/hssf/record/formula/functions/Maxa.java +++ b/src/java/org/apache/poi/hssf/record/formula/functions/Maxa.java @@ -27,9 +27,7 @@ import org.apache.poi.hssf.record.formula.eval.ValueEvalToNumericXlator; 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 )); diff --git a/src/java/org/apache/poi/hssf/record/formula/functions/Mina.java b/src/java/org/apache/poi/hssf/record/formula/functions/Mina.java index b3965eefd4..7344936310 100644 --- a/src/java/org/apache/poi/hssf/record/formula/functions/Mina.java +++ b/src/java/org/apache/poi/hssf/record/formula/functions/Mina.java @@ -27,10 +27,8 @@ import org.apache.poi.hssf.record.formula.eval.ValueEvalToNumericXlator; 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) { diff --git a/src/java/org/apache/poi/hssf/record/formula/functions/Mode.java b/src/java/org/apache/poi/hssf/record/formula/functions/Mode.java index 64f017e8e2..c236ccee62 100644 --- a/src/java/org/apache/poi/hssf/record/formula/functions/Mode.java +++ b/src/java/org/apache/poi/hssf/record/formula/functions/Mode.java @@ -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() + ")"); } } diff --git a/src/java/org/apache/poi/hssf/record/formula/functions/StatsLib.java b/src/java/org/apache/poi/hssf/record/formula/functions/StatsLib.java index 8ebccfd950..e78f38caaa 100644 --- a/src/java/org/apache/poi/hssf/record/formula/functions/StatsLib.java +++ b/src/java/org/apache/poi/hssf/record/formula/functions/StatsLib.java @@ -60,36 +60,6 @@ public final class StatsLib { 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 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; diff --git a/src/testcases/org/apache/poi/hssf/data/FormulaEvalTestData.xls b/src/testcases/org/apache/poi/hssf/data/FormulaEvalTestData.xls index acf9c8b166c6232dcf06d900cd4db20613b26621..f046382eccd46b3855e3edc51fc1652731ee0f1e 100644 GIT binary patch delta 2624 zcmaJ@4Nz3q6+Y+QyUXtKv!FzFQCDQx?5<*5Kwz;U{7BTMwt`w4O-YH7JhGp{Y`ITn)5cf^{%Y!gWO@1lONV7h1VYZk9I;VlkJ28s+^41A01xko z(msz*-fR=vWqbGLp|K)5VYdv}hRz#--CFhd`1pGGkuVlB(4Z z0q+FP4kAoB zhp_nq!t#HT?J`2$R|to%p?t=~8UuZAREMpsfXCkva0DE*fA~H23YwVm;Q!a-9$3IY zt9lux34r?=!gDZvK-2E7`axPHJWu)&Vl+S)yh`_CF!y^~pb}<!C5MQsW{ zSnSaT*DWP*V_l|bJ4URh>ncc1+my~sNm}`)#iaaWQ#w{^gX`ZMvFK*fCWXn!zeTN(@At^B8gE!Gy1sd%nG9&zyPPQuW z?%_Nyt8{AJUwc__wdUwf7q*lvuKlc=w!rGvwsmK7rT?uvogXam1Y-WRCSH{Gc)4s0 z!_i_ZO;7$=NoSOFCk&Ih65J*njea@9gwsW>0`?Lhyh^%c!m;8EG3b$IJU!wGn9Rf(eSbNAgBYprsz9%acX2K+EJ4r8@V-cxX9N?7pfQ+_&!G`Ku!CD5 zk50r&XRgkB2aot}FvL;0I}nD2IT$!NI88|JG>p@G zo3JNQm5%)CC}v?$jtt^-Y?H4A@eR_h2QiliV_RhEF`UnbQI0ho!>M>qb|1qZav$lw z?sc>;?>q4N0l1de5UT)OKd zCdVXAKD9fPO*sb9XQUS*`zst{=8zqPVN1PLl9_-EaX{S;SU_Z0JA- zy5+ka6rAgqfA7Gt{BxRlWGALkqsZ$-Uxlif;B1QC_7c;8hEsdzBWRq@+zG@jVp!!Bxy;MBjUrg8Y8zx`yMF z{_|^CBLq!V@4AM=OuVq&D_Jkj4wW1iOws)zGy&x{DhTLvrZdUC6wVf>y zH`L0P+1V&o=9atctb|p%r751Zh>K*=TjSX!cEG`I`>;g6Gm~|)Nf~(=zRbJ{nP~+R z@*jQ@BP|7m`Pqr7S*iJHS!sC#H{p{eWG6oBdp2*PZ({z$EcJND>F(LghMHFx_1qU& XE}H!}cZ5CwxwdL~rXGBe z{P$ZQ*7?5<`I=#_dc|l~|E)I+p>c*m6su+%Mg+N7_1Om~jDZPi5datjCmN$3nr96$ z5y^;<1xMM?h_i-?LlOq#pqN>mYJ9NNf(OVr>!HdOV{910oJTccB3A#m>~Z6uEdBbG z5mzGAn7Cfm8_U)E7K3`z694L$7~ZGTCiu-`R*uIqb^FY?xaeqKCGBi~ho(O)9CdQh~*vKK_VqZo#7VQ>trbdvZ#{KXWV z9HIQ*5#N9jLTGDiAfE#aS_n@-!2?UZ@B41rI(VAwW26`Yq3{B=5-<#nHo`hs67)up zcSO)jezmyVZdncUNc$L!B2NrF|FEft$}Q+oKPaEdd)p3x|DWYc5k3C=m)?;bdnFb5 zA>!4nomFJF?A$8pITchO9@SGhM+xk*``@laX*O3)W%X_97gY&LyWg(rs((S`udB0I zrCt4^Iz@p`8`bFDn~1F1oyqS@)em-i$V{urU~k&g;+hR){IwTiwr5w+Yo+~As>lnA$h)Doo&+NIdT_1(Cwr$d)@)M`s)@>B zTDU{aX4H+Yb1ONU?fz%#emRLIv9iN9|M)*_!^riZ(&-`7!6M4$djD_#=yb4v zP3^tmV5=)tYi|ZGdX1KOs&|f(SJI@u(VMOOjO@>QGZZ-2=#RSjN*wPitl(l_C{E#F z5`8QYNYo#S$%+JtdYpodqEL?oyj2nhiQoZ=F6wa_ZziOL8}Pyy+#tFWa58nqNtlcq zw9H93dZZG_ZW0SJ@SJj)z@+Ke#p+LMhDc>r zo>B?%;!ZuK(tVmz`Q*4#cs4*&8pRs{EWmbgGk{z0vRHKja}@7eGsN#s;40-j zB`@YAF2FwV+)4a_Qb>05Nu1BUGG%xQT|9wA#p9{pqB;mwOX;)@DyBTi6%ofAXN0!{ zCkDgb-+>Nhju#yrRAt~1tP>yOWwLP6JJH7LBw9)ctF;$9(Pxa$`O|@5O>7F`1A1`l zsBoMc=^)R?c)1JHut*%~!mQw`cW3~=Bm+w9#_ym{ zw=t#o4HOW^6%o?V@5DDaf2Tlbv3sTn(rgb`X=~R{Lz7b zAX86lnXSEl5Bno^_5pg?8pS8EtVF&qtF&eVGlhpZ!x@xmt+8x8