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/10 23:21:29 UTC
svn commit: r693990 - 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/record/formula/eval/
testcases/org/apache/poi/hssf/record/formula/functions/
Author: josh
Date: Wed Sep 10 14:21:28 2008
New Revision: 693990
URL: http://svn.apache.org/viewvc?rev=693990&view=rev
Log:
Refactored hierarchy of MultiOperandNumericFunction. Fixed error value handling. Enabled error value check in TestFormulasFromSpreadsheet
Added:
poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/AggregateFunction.java
Removed:
poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Avedev.java
poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Average.java
poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Devsq.java
poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Large.java
poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Max.java
poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Median.java
poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Min.java
poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Product.java
poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Small.java
poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Stdev.java
poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Sum.java
poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Sumsq.java
Modified:
poi/trunk/src/java/org/apache/poi/hssf/record/formula/eval/FunctionEval.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/MultiOperandNumericFunction.java
poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/eval/TestFormulasFromSpreadsheet.java
poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/functions/TestAverage.java
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=693990&r1=693989&r2=693990&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 Wed Sep 10 14:21:28 2008
@@ -1,23 +1,20 @@
-/*
-* 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 8, 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.eval;
import java.util.HashMap;
@@ -81,15 +78,15 @@
retval[1] = new If(); // IF
retval[2] = new IsNa(); // ISNA
retval[3] = new IsError(); // ISERROR
- retval[4] = new Sum(); // SUM
- retval[5] = new Average(); // AVERAGE
- retval[6] = new Min(); // MIN
- retval[7] = new Max(); // MAX
+ retval[4] = AggregateFunction.SUM;
+ retval[5] = AggregateFunction.AVERAGE;
+ retval[6] = AggregateFunction.MIN;
+ retval[7] = AggregateFunction.MAX;
retval[8] = new Row(); // ROW
retval[9] = new Column(); // COLUMN
retval[10] = new Na(); // NA
retval[11] = new Npv(); // NPV
- retval[12] = new Stdev(); // STDEV
+ retval[12] = AggregateFunction.STDEV;
retval[13] = NumericFunction.DOLLAR;
retval[14] = new Fixed(); // FIXED
retval[15] = NumericFunction.SIN;
@@ -255,7 +252,7 @@
retval[180] = new NotImplementedFunction(); // RESTART
retval[181] = new Help(); // HELP
retval[182] = new NotImplementedFunction(); // GETBAR
- retval[183] = new Product(); // PRODUCT
+ retval[183] = AggregateFunction.PRODUCT;
retval[184] = NumericFunction.FACT;
retval[185] = new NotImplementedFunction(); // GETCELL
retval[186] = new NotImplementedFunction(); // GETWORKSPACE
@@ -291,7 +288,7 @@
retval[220] = new Days360(); // DAYS360
retval[221] = new Today(); // TODAY
retval[222] = new Vdb(); // VDB
- retval[227] = new Median(); // MEDIAN
+ retval[227] = AggregateFunction.MEDIAN;
retval[228] = new Sumproduct(); // SUMPRODUCT
retval[229] = NumericFunction.SINH;
retval[230] = NumericFunction.COSH;
@@ -331,7 +328,7 @@
retval[266] = new NotImplementedFunction(); // PRESSTOOL
retval[267] = new NotImplementedFunction(); // REGISTERID
retval[268] = new NotImplementedFunction(); // GETWORKBOOK
- retval[269] = new Avedev(); // AVEDEV
+ retval[269] = AggregateFunction.AVEDEV;
retval[270] = new Betadist(); // BETADIST
retval[271] = new Gammaln(); // GAMMALN
retval[272] = new Betainv(); // BETAINV
@@ -380,15 +377,15 @@
retval[315] = new Slope(); // SLOPE
retval[316] = new Ttest(); // TTEST
retval[317] = new Prob(); // PROB
- retval[318] = new Devsq(); // DEVSQ
+ retval[318] = AggregateFunction.DEVSQ;
retval[319] = new Geomean(); // GEOMEAN
retval[320] = new Harmean(); // HARMEAN
- retval[321] = new Sumsq(); // SUMSQ
+ retval[321] = AggregateFunction.SUMSQ;
retval[322] = new Kurt(); // KURT
retval[323] = new Skew(); // SKEW
retval[324] = new Ztest(); // ZTEST
- retval[325] = new Large(); // LARGE
- retval[326] = new Small(); // SMALL
+ retval[325] = AggregateFunction.LARGE;
+ retval[326] = AggregateFunction.SMALL;
retval[327] = new Quartile(); // QUARTILE
retval[328] = new Percentile(); // PERCENTILE
retval[329] = new Percentrank(); // PERCENTRANK
Added: 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=693990&view=auto
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/AggregateFunction.java (added)
+++ poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/AggregateFunction.java Wed Sep 10 14:21:28 2008
@@ -0,0 +1,120 @@
+/* ====================================================================
+ 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;
+
+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.ValueEvalToNumericXlator;
+
+/**
+ * @author Amol S. Deshmukh < amolweb at ya hoo dot com >
+ *
+ */
+public abstract class AggregateFunction extends MultiOperandNumericFunction {
+ private static final ValueEvalToNumericXlator DEFAULT_NUM_XLATOR =
+ new ValueEvalToNumericXlator((short) (
+ ValueEvalToNumericXlator.BOOL_IS_PARSED
+ | ValueEvalToNumericXlator.STRING_IS_PARSED
+ ));
+
+ protected final ValueEvalToNumericXlator getXlator() {
+ return DEFAULT_NUM_XLATOR;
+ }
+
+
+ /* ---------------------------------------------------------------------- */
+
+ public static final Function AVEDEV = new AggregateFunction() {
+ protected double evaluate(double[] values) {
+ return StatsLib.avedev(values);
+ }
+ };
+ public static final Function AVERAGE = new AggregateFunction() {
+ protected double evaluate(double[] values) throws EvaluationException {
+ if (values.length < 1) {
+ throw new EvaluationException(ErrorEval.DIV_ZERO);
+ }
+ return MathX.average(values);
+ }
+ };
+ public static final Function DEVSQ = new AggregateFunction() {
+ protected double evaluate(double[] values) {
+ return StatsLib.devsq(values);
+ }
+ };
+ public static final Function LARGE = new AggregateFunction() {
+ protected double evaluate(double[] ops) throws EvaluationException {
+ if (ops.length < 2) {
+ throw new EvaluationException(ErrorEval.NUM_ERROR);
+ }
+ double[] values = new double[ops.length-1];
+ int k = (int) ops[ops.length-1];
+ System.arraycopy(ops, 0, values, 0, values.length);
+ return StatsLib.kthLargest(values, k);
+ }
+ };
+ public static final Function MAX = new AggregateFunction() {
+ protected double evaluate(double[] values) {
+ return values.length > 0 ? MathX.max(values) : 0;
+ }
+ };
+ public static final Function MEDIAN = new AggregateFunction() {
+ protected double evaluate(double[] values) {
+ return StatsLib.median(values);
+ }
+ };
+ public static final Function MIN = new AggregateFunction() {
+ protected double evaluate(double[] values) {
+ return values.length > 0 ? MathX.min(values) : 0;
+ }
+ };
+ public static final Function PRODUCT = new AggregateFunction() {
+ protected double evaluate(double[] values) {
+ return MathX.product(values);
+ }
+ };
+ public static final Function SMALL = new AggregateFunction() {
+ protected double evaluate(double[] ops) throws EvaluationException {
+ if (ops.length < 2) {
+ throw new EvaluationException(ErrorEval.NUM_ERROR);
+ }
+ double[] values = new double[ops.length-1];
+ int k = (int) ops[ops.length-1];
+ System.arraycopy(ops, 0, values, 0, values.length);
+ return StatsLib.kthSmallest(values, k);
+ }
+ };
+ public static final Function STDEV = new AggregateFunction() {
+ protected double evaluate(double[] values) throws EvaluationException {
+ if (values.length < 1) {
+ throw new EvaluationException(ErrorEval.DIV_ZERO);
+ }
+ return StatsLib.stdev(values);
+ }
+ };
+ public static final Function SUM = new AggregateFunction() {
+ protected double evaluate(double[] values) {
+ return MathX.sum(values);
+ }
+ };
+ public static final Function SUMSQ = new AggregateFunction() {
+ protected double evaluate(double[] values) {
+ return MathX.sumsq(values);
+ }
+ };
+}
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=693990&r1=693989&r2=693990&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 14:21:28 2008
@@ -1,26 +1,22 @@
-/*
-* 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.
-*/
+/* ====================================================================
+ 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;
-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.NumberEval;
-import org.apache.poi.hssf.record.formula.eval.ValueEval;
import org.apache.poi.hssf.record.formula.eval.ValueEvalToNumericXlator;
/**
@@ -46,21 +42,8 @@
protected ValueEvalToNumericXlator getXlator() {
return DEFAULT_NUM_XLATOR;
}
-
- public Eval evaluate(Eval[] operands, int srcCellRow, short srcCellCol) {
- ValueEval retval = null;
- double[] values = getNumberArray(operands, srcCellRow, srcCellCol);
- if (values == null) {
- retval = ErrorEval.VALUE_INVALID;
- }
- else {
- double d = values.length > 0 ? MathX.max(values) : 0;
- retval = (Double.isNaN(d) || Double.isInfinite(d))
- ? (ValueEval) ErrorEval.NUM_ERROR
- : new NumberEval(d);
- }
-
- return retval;
+ public double evaluate(double[] values) {
+ return values.length > 0 ? MathX.max(values) : 0;
}
}
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=693990&r1=693989&r2=693990&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 14:21:28 2008
@@ -1,26 +1,22 @@
-/*
-* 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.
-*/
+/* ====================================================================
+ 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;
-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.NumberEval;
-import org.apache.poi.hssf.record.formula.eval.ValueEval;
import org.apache.poi.hssf.record.formula.eval.ValueEvalToNumericXlator;
/**
@@ -47,20 +43,7 @@
return DEFAULT_NUM_XLATOR;
}
-
- public Eval evaluate(Eval[] operands, int srcCellRow, short srcCellCol) {
- ValueEval retval = null;
- double[] values = getNumberArray(operands, srcCellRow, srcCellCol);
- if (values == null) {
- retval = ErrorEval.VALUE_INVALID;
- }
- else {
- double d = values.length > 0 ? MathX.min(values) : 0;
- retval = (Double.isNaN(d) || Double.isInfinite(d))
- ? (ValueEval) ErrorEval.NUM_ERROR
- : new NumberEval(d);
- }
-
- return retval;
+ public double evaluate(double[] values) {
+ return values.length > 0 ? MathX.min(values) : 0;
}
}
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=693990&r1=693989&r2=693990&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 14:21:28 2008
@@ -1,29 +1,24 @@
-/*
-* 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;
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.NumberEval;
-import org.apache.poi.hssf.record.formula.eval.ValueEval;
+import org.apache.poi.hssf.record.formula.eval.EvaluationException;
import org.apache.poi.hssf.record.formula.eval.ValueEvalToNumericXlator;
/**
@@ -58,21 +53,12 @@
return DEFAULT_NUM_XLATOR;
}
-
-
- public Eval evaluate(Eval[] operands, int srcCellRow, short srcCellCol) {
- ValueEval retval = null;
- double[] values = getNumberArray(operands, srcCellRow, srcCellCol);
- if (values == null) {
- retval = ErrorEval.VALUE_INVALID;
- }
- else {
- double d = StatsLib.mode(values);
- retval = (Double.isNaN(d) || Double.isInfinite(d))
- ? (ValueEval) ErrorEval.NUM_ERROR
- : new NumberEval(d);
+ 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);
}
-
- return retval;
+ return d;
}
}
Modified: poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/MultiOperandNumericFunction.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/MultiOperandNumericFunction.java?rev=693990&r1=693989&r2=693990&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/MultiOperandNumericFunction.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/MultiOperandNumericFunction.java Wed Sep 10 14:21:28 2008
@@ -1,19 +1,19 @@
-/*
-* 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.
-*/
+/* ====================================================================
+ 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;
@@ -21,6 +21,8 @@
import org.apache.poi.hssf.record.formula.eval.BlankEval;
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.NumericValueEval;
import org.apache.poi.hssf.record.formula.eval.Ref2DEval;
import org.apache.poi.hssf.record.formula.eval.RefEval;
@@ -34,203 +36,156 @@
* where the order of operands does not matter
*/
public abstract class MultiOperandNumericFunction implements Function {
- static final double[] EMPTY_DOUBLE_ARRAY = { };
-
- private static class DoubleList {
- private double[] _array;
- private int _count;
-
- public DoubleList() {
- _array = new double[8];
- _count = 0;
- }
-
- public double[] toArray() {
- if(_count < 1) {
- return EMPTY_DOUBLE_ARRAY;
- }
- double[] result = new double[_count];
- System.arraycopy(_array, 0, result, 0, _count);
- return result;
- }
-
- public void add(double[] values) {
- int addLen = values.length;
- ensureCapacity(_count + addLen);
- System.arraycopy(values, 0, _array, _count, addLen);
- _count += addLen;
- }
-
- private void ensureCapacity(int reqSize) {
- if(reqSize > _array.length) {
- int newSize = reqSize * 3 / 2; // grow with 50% extra
- double[] newArr = new double[newSize];
- System.arraycopy(_array, 0, newArr, 0, _count);
- _array = newArr;
- }
- }
-
- public void add(double value) {
- ensureCapacity(_count + 1);
- _array[_count] = value;
- _count++;
- }
- }
-
- private static final int DEFAULT_MAX_NUM_OPERANDS = 30;
-
- protected abstract ValueEvalToNumericXlator getXlator();
-
- /**
- * Maximum number of operands accepted by this function.
- * Subclasses may override to change default value.
- */
- protected int getMaxNumOperands() {
- return DEFAULT_MAX_NUM_OPERANDS;
- }
-
- /**
- * Returns a double array that contains values for the numeric cells
- * from among the list of operands. Blanks and Blank equivalent cells
- * are ignored. Error operands or cells containing operands of type
- * that are considered invalid and would result in #VALUE! error in
- * excel cause this function to return <code>null</code>.
- *
- * @param operands
- * @param srcRow
- * @param srcCol
- */
- protected double[] getNumberArray(Eval[] operands, int srcRow, short srcCol) {
- if (operands.length > getMaxNumOperands()) {
- return null;
- }
- DoubleList retval = new DoubleList();
-
- for (int i=0, iSize=operands.length; i<iSize; i++) {
- double[] temp = getNumberArray(operands[i], srcRow, srcCol);
- if (temp == null) {
- return null; // error occurred.
- }
- retval.add(temp);
- }
- return retval.toArray();
- }
-
- /**
- * Same as getNumberArray(Eval[], int, short) except that this
- * takes Eval instead of Eval[].
- * @param operand
- * @param srcRow
- * @param srcCol
- */
- protected double[] getNumberArray(Eval operand, int srcRow, short srcCol) {
-
- if (operand instanceof AreaEval) {
- AreaEval ae = (AreaEval) operand;
- DoubleList retval = new DoubleList();
- 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);
- /*
- * TODO: For an AreaEval, we are constructing a RefEval
- * per element.
- * For now this is a tempfix solution since this may
- * require a more generic fix at the level of
- * HSSFFormulaEvaluator where we store an array
- * of RefEvals as the "values" array.
- */
- RefEval re = new Ref2DEval(null, ve1);
- ValueEval ve = singleOperandEvaluate(re, srcRow, srcCol);
-
- if (ve instanceof NumericValueEval) {
- NumericValueEval nve = (NumericValueEval) ve;
- retval.add(nve.getNumberValue());
- }
- else if (ve instanceof BlankEval) {
- // note - blanks are ignored, so returned array will be smaller.
- }
- else {
- return null; // indicate to calling subclass that error occurred
- }
- }
- }
- return retval.toArray();
- }
-
- // for ValueEvals other than AreaEval
- ValueEval ve = singleOperandEvaluate(operand, srcRow, srcCol);
-
- if (ve instanceof NumericValueEval) {
- NumericValueEval nve = (NumericValueEval) ve;
- return new double[] { nve.getNumberValue(), };
- }
-
- if (ve instanceof BlankEval) {
- // ignore blanks
- return EMPTY_DOUBLE_ARRAY;
- }
- return null;
- }
-
- /**
- * Ensures that a two dimensional array has all sub-arrays present and the same length
- * @return <code>false</code> if any sub-array is missing, or is of different length
- */
- protected static final boolean areSubArraysConsistent(double[][] values) {
-
- if (values == null || values.length < 1) {
- // TODO this doesn't seem right. Fix or add comment.
- return true;
- }
-
- if (values[0] == null) {
- return false;
- }
- int outerMax = values.length;
- int innerMax = values[0].length;
- for (int i=1; i<outerMax; i++) { // note - 'i=1' start at second sub-array
- double[] subArr = values[i];
- if (subArr == null) {
- return false;
- }
- if (innerMax != subArr.length) {
- return false;
- }
- }
- return true;
- }
-
-
- protected final ValueEval singleOperandEvaluate(Eval eval, int srcRow, short srcCol) {
-
- if (eval instanceof AreaEval) {
- AreaEval ae = (AreaEval) eval;
- if (ae.contains(srcRow, srcCol)) { // circular ref!
- return ErrorEval.CIRCULAR_REF_ERROR;
- }
- if (ae.isRow()) {
- if (ae.isColumn()) {
- return ae.getRelativeValue(0, 0);
- }
- if (ae.containsColumn(srcCol)) {
- ValueEval ve = ae.getValueAt(ae.getFirstRow(), srcCol);
- ve = getXlator().attemptXlateToNumeric(ve);
- return getXlator().attemptXlateToNumeric(ve);
- }
- return ErrorEval.VALUE_INVALID;
- }
- if (ae.isColumn()) {
- if (ae.containsRow(srcRow)) {
- ValueEval ve = ae.getValueAt(srcRow, ae.getFirstColumn());
- return getXlator().attemptXlateToNumeric(ve);
- }
- return ErrorEval.VALUE_INVALID;
- }
- return ErrorEval.VALUE_INVALID;
- }
- return getXlator().attemptXlateToNumeric((ValueEval) eval);
- }
-
+ static final double[] EMPTY_DOUBLE_ARRAY = { };
+
+ private static class DoubleList {
+ private double[] _array;
+ private int _count;
+
+ public DoubleList() {
+ _array = new double[8];
+ _count = 0;
+ }
+
+ public double[] toArray() {
+ if(_count < 1) {
+ return EMPTY_DOUBLE_ARRAY;
+ }
+ double[] result = new double[_count];
+ System.arraycopy(_array, 0, result, 0, _count);
+ return result;
+ }
+
+ private void ensureCapacity(int reqSize) {
+ if(reqSize > _array.length) {
+ int newSize = reqSize * 3 / 2; // grow with 50% extra
+ double[] newArr = new double[newSize];
+ System.arraycopy(_array, 0, newArr, 0, _count);
+ _array = newArr;
+ }
+ }
+
+ public void add(double value) {
+ ensureCapacity(_count + 1);
+ _array[_count] = value;
+ _count++;
+ }
+ }
+
+ private static final int DEFAULT_MAX_NUM_OPERANDS = 30;
+
+ protected abstract ValueEvalToNumericXlator getXlator();
+
+
+ public final Eval evaluate(Eval[] args, int srcCellRow, short srcCellCol) {
+
+ double d;
+ try {
+ double[] values = getNumberArray(args);
+ d = evaluate(values);
+ } catch (EvaluationException e) {
+ return e.getErrorEval();
+ }
+
+ if (Double.isNaN(d) || Double.isInfinite(d))
+ return ErrorEval.NUM_ERROR;
+
+ return new NumberEval(d);
+ }
+
+ protected abstract double evaluate(double[] values) throws EvaluationException;
+
+
+ /**
+ * Maximum number of operands accepted by this function.
+ * Subclasses may override to change default value.
+ */
+ protected int getMaxNumOperands() {
+ return DEFAULT_MAX_NUM_OPERANDS;
+ }
+
+ /**
+ * Returns a double array that contains values for the numeric cells
+ * from among the list of operands. Blanks and Blank equivalent cells
+ * are ignored. Error operands or cells containing operands of type
+ * that are considered invalid and would result in #VALUE! error in
+ * excel cause this function to return <code>null</code>.
+ *
+ * @return never <code>null</code>
+ */
+ protected final double[] getNumberArray(Eval[] operands) throws EvaluationException {
+ if (operands.length > getMaxNumOperands()) {
+ throw EvaluationException.invalidValue();
+ }
+ DoubleList retval = new DoubleList();
+
+ for (int i=0, iSize=operands.length; i<iSize; i++) {
+ collectValues(operands[i], retval);
+ }
+ return retval.toArray();
+ }
+
+ /**
+ * Collects values from a single argument
+ */
+ private void collectValues(Eval operand, DoubleList temp) throws EvaluationException {
+
+ if (operand instanceof AreaEval) {
+ AreaEval ae = (AreaEval) operand;
+ 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);
+ /*
+ * TODO: For an AreaEval, we are constructing a RefEval
+ * per element.
+ * For now this is a tempfix solution since this may
+ * require a more generic fix at the level of
+ * HSSFFormulaEvaluator where we store an array
+ * of RefEvals as the "values" array.
+ */
+ RefEval re = new Ref2DEval(null, ve1);
+ ValueEval ve = attemptXlateToNumeric(re);
+ if (ve instanceof ErrorEval) {
+ throw new EvaluationException((ErrorEval)ve);
+ }
+ if (ve instanceof BlankEval) {
+ // note - blanks are ignored, so returned array will be smaller.
+ continue;
+ }
+ if (ve instanceof NumericValueEval) {
+ NumericValueEval nve = (NumericValueEval) ve;
+ temp.add(nve.getNumberValue());
+ } else {
+ throw new RuntimeException("Unexpected value class (" + ve.getClass().getName() + ")");
+ }
+ }
+ }
+ return;
+ }
+
+ // for ValueEvals other than AreaEval
+ ValueEval ve = attemptXlateToNumeric((ValueEval) operand);
+
+ if (ve instanceof NumericValueEval) {
+ NumericValueEval nve = (NumericValueEval) ve;
+ temp.add(nve.getNumberValue());
+ return;
+ }
+
+ if (ve instanceof BlankEval) {
+ // ignore blanks
+ return;
+ }
+ if (ve instanceof ErrorEval) {
+ throw new EvaluationException((ErrorEval)ve);
+ }
+ throw new RuntimeException("Unexpected value class (" + ve.getClass().getName() + ")");
+ }
+
+
+ protected ValueEval attemptXlateToNumeric(ValueEval ve) {
+ return getXlator().attemptXlateToNumeric(ve);
+ }
}
Modified: poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/eval/TestFormulasFromSpreadsheet.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/eval/TestFormulasFromSpreadsheet.java?rev=693990&r1=693989&r2=693990&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/eval/TestFormulasFromSpreadsheet.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/eval/TestFormulasFromSpreadsheet.java Wed Sep 10 14:21:28 2008
@@ -112,15 +112,6 @@
throw new AssertionFailedError(msg + " - actual value was null");
}
- if (expected.getCellType() == HSSFCell.CELL_TYPE_STRING) {
- String value = expected.getRichStringCellValue().getString();
- if (value.startsWith("#")) {
- // TODO - this code never called
- expected.setCellType(HSSFCell.CELL_TYPE_ERROR);
- // expected.setCellErrorValue(...?);
- }
- }
-
switch (expected.getCellType()) {
case HSSFCell.CELL_TYPE_BLANK:
assertEquals(msg, HSSFCell.CELL_TYPE_BLANK, actual.getCellType());
@@ -131,18 +122,13 @@
break;
case HSSFCell.CELL_TYPE_ERROR:
assertEquals(msg, HSSFCell.CELL_TYPE_ERROR, actual.getCellType());
- if(false) { // TODO: fix ~45 functions which are currently returning incorrect error values
- assertEquals(msg, expected.getErrorCellValue(), actual.getErrorValue());
- }
+ assertEquals(msg, ErrorEval.getText(expected.getErrorCellValue()), ErrorEval.getText(actual.getErrorValue()));
break;
case HSSFCell.CELL_TYPE_FORMULA: // will never be used, since we will call method after formula evaluation
throw new AssertionFailedError("Cannot expect formula as result of formula evaluation: " + msg);
case HSSFCell.CELL_TYPE_NUMERIC:
assertEquals(msg, HSSFCell.CELL_TYPE_NUMERIC, actual.getCellType());
TestMathX.assertEquals(msg, expected.getNumericCellValue(), actual.getNumberValue(), TestMathX.POS_ZERO, TestMathX.DIFF_TOLERANCE_FACTOR);
-// double delta = Math.abs(expected.getNumericCellValue()-actual.getNumberValue());
-// double pctExpected = Math.abs(0.00001*expected.getNumericCellValue());
-// assertTrue(msg, delta <= pctExpected);
break;
case HSSFCell.CELL_TYPE_STRING:
assertEquals(msg, HSSFCell.CELL_TYPE_STRING, actual.getCellType());
@@ -152,7 +138,7 @@
}
- protected void setUp() throws Exception {
+ protected void setUp() {
if (workbook == null) {
workbook = HSSFTestDataSamples.openSampleWorkbook(SS.FILENAME);
sheet = workbook.getSheetAt( 0 );
Modified: poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/functions/TestAverage.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/functions/TestAverage.java?rev=693990&r1=693989&r2=693990&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/functions/TestAverage.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/functions/TestAverage.java Wed Sep 10 14:21:28 2008
@@ -27,14 +27,13 @@
import org.apache.poi.hssf.record.formula.eval.ValueEval;
/**
* Tests for Excel function AVERAGE()
- *
+ *
* @author Josh Micich
*/
public final class TestAverage extends TestCase {
-
private static Eval invokeAverage(Eval[] args) {
- return new Average().evaluate(args, -1, (short)-1);
+ return AggregateFunction.AVERAGE.evaluate(args, -1, (short)-1);
}
private void confirmAverage(Eval[] args, double expected) {
@@ -48,56 +47,56 @@
assertEquals(ErrorEval.class, result.getClass());
assertEquals(expectedError.getErrorCode(), ((ErrorEval)result).getErrorCode());
}
-
+
public void testBasic() {
-
+
ValueEval[] values = {
- new NumberEval(1),
- new NumberEval(2),
- new NumberEval(3),
- new NumberEval(4),
+ new NumberEval(1),
+ new NumberEval(2),
+ new NumberEval(3),
+ new NumberEval(4),
};
-
+
confirmAverage(values, 2.5);
-
+
values = new ValueEval[] {
- new NumberEval(1),
+ new NumberEval(1),
new NumberEval(2),
BlankEval.INSTANCE,
- new NumberEval(3),
+ new NumberEval(3),
BlankEval.INSTANCE,
- new NumberEval(4),
+ new NumberEval(4),
BlankEval.INSTANCE,
};
-
+
confirmAverage(values, 2.5);
}
-
+
/**
* Valid cases where values are not pure numbers
*/
public void testUnusualArgs() {
ValueEval[] values = {
- new NumberEval(1),
- new NumberEval(2),
- BoolEval.TRUE,
- BoolEval.FALSE,
+ new NumberEval(1),
+ new NumberEval(2),
+ BoolEval.TRUE,
+ BoolEval.FALSE,
};
-
+
confirmAverage(values, 1.0);
-
+
}
// currently disabled because MultiOperandNumericFunction.getNumberArray(Eval[], int, short)
// does not handle error values properly yet
public void XtestErrors() {
ValueEval[] values = {
- new NumberEval(1),
- ErrorEval.NAME_INVALID,
- new NumberEval(3),
- ErrorEval.DIV_ZERO,
+ new NumberEval(1),
+ ErrorEval.NAME_INVALID,
+ new NumberEval(3),
+ ErrorEval.DIV_ZERO,
};
confirmAverage(values, ErrorEval.NAME_INVALID);
-
+
}
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org