You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by ni...@apache.org on 2008/09/13 15:48:31 UTC
svn commit: r694947 [3/6] - in /poi/branches/ooxml: ./
src/documentation/content/xdocs/ src/java/org/apache/poi/hssf/extractor/
src/java/org/apache/poi/hssf/model/ src/java/org/apache/poi/hssf/record/
src/java/org/apache/poi/hssf/record/aggregates/ src...
Modified: poi/branches/ooxml/src/java/org/apache/poi/hssf/record/formula/functions/Index.java
URL: http://svn.apache.org/viewvc/poi/branches/ooxml/src/java/org/apache/poi/hssf/record/formula/functions/Index.java?rev=694947&r1=694946&r2=694947&view=diff
==============================================================================
--- poi/branches/ooxml/src/java/org/apache/poi/hssf/record/formula/functions/Index.java (original)
+++ poi/branches/ooxml/src/java/org/apache/poi/hssf/record/formula/functions/Index.java Sat Sep 13 06:48:27 2008
@@ -22,6 +22,7 @@
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.OperandResolver;
+import org.apache.poi.hssf.record.formula.eval.RefEval;
import org.apache.poi.hssf.record.formula.eval.ValueEval;
/**
@@ -51,6 +52,10 @@
return ErrorEval.VALUE_INVALID;
}
Eval firstArg = args[0];
+ if (firstArg instanceof RefEval) {
+ // convert to area ref for simpler code in getValueFromArea()
+ firstArg = ((RefEval)firstArg).offset(0, 0, 0, 0);
+ }
if(!(firstArg instanceof AreaEval)) {
// else the other variation of this function takes an array as the first argument
@@ -84,16 +89,63 @@
// too many arguments
return ErrorEval.VALUE_INVALID;
}
- return getValueFromArea(reference, rowIx, columnIx);
+ return getValueFromArea(reference, rowIx, columnIx, nArgs);
} catch (EvaluationException e) {
return e.getErrorEval();
}
}
- private static ValueEval getValueFromArea(AreaEval ae, int rowIx, int columnIx) throws EvaluationException {
+ /**
+ * @param nArgs - needed because error codes are slightly different when only 2 args are passed
+ */
+ private static ValueEval getValueFromArea(AreaEval ae, int pRowIx, int pColumnIx, int nArgs) throws EvaluationException {
+ int rowIx;
+ int columnIx;
+
+ // when the area ref is a single row or a single column,
+ // there are special rules for conversion of rowIx and columnIx
+ if (ae.isRow()) {
+ if (ae.isColumn()) {
+ rowIx = pRowIx == -1 ? 0 : pRowIx;
+ columnIx = pColumnIx == -1 ? 0 : pColumnIx;
+ } else {
+ if (nArgs == 2) {
+ rowIx = 0;
+ columnIx = pRowIx;
+ } else {
+ rowIx = pRowIx == -1 ? 0 : pRowIx;
+ columnIx = pColumnIx;
+ }
+ }
+ if (rowIx < -1 || columnIx < -1) {
+ throw new EvaluationException(ErrorEval.VALUE_INVALID);
+ }
+ } else if (ae.isColumn()) {
+ if (nArgs == 2) {
+ rowIx = pRowIx;
+ columnIx = 0;
+ } else {
+ rowIx = pRowIx;
+ columnIx = pColumnIx == -1 ? 0 : pColumnIx;
+ }
+ if (rowIx < -1 || columnIx < -1) {
+ throw new EvaluationException(ErrorEval.VALUE_INVALID);
+ }
+ } else {
+ if (nArgs == 2) {
+ // always an error with 2-D area refs
+ if (pRowIx < -1) {
+ throw new EvaluationException(ErrorEval.VALUE_INVALID);
+ }
+ throw new EvaluationException(ErrorEval.REF_INVALID);
+ }
+ // Normal case - area ref is 2-D, and both index args were provided
+ rowIx = pRowIx;
+ columnIx = pColumnIx;
+ }
+
int width = ae.getWidth();
int height = ae.getHeight();
-
// Slightly irregular logic for bounds checking errors
if (rowIx >= height || columnIx >= width) {
throw new EvaluationException(ErrorEval.REF_INVALID);
Modified: poi/branches/ooxml/src/java/org/apache/poi/hssf/record/formula/functions/Indirect.java
URL: http://svn.apache.org/viewvc/poi/branches/ooxml/src/java/org/apache/poi/hssf/record/formula/functions/Indirect.java?rev=694947&r1=694946&r2=694947&view=diff
==============================================================================
--- poi/branches/ooxml/src/java/org/apache/poi/hssf/record/formula/functions/Indirect.java (original)
+++ poi/branches/ooxml/src/java/org/apache/poi/hssf/record/formula/functions/Indirect.java Sat Sep 13 06:48:27 2008
@@ -41,7 +41,7 @@
*/
public final class Indirect implements FreeRefFunction {
- public ValueEval evaluate(Eval[] args, int srcCellRow, short srcCellCol, Workbook workbook, Sheet sheet) {
+ public ValueEval evaluate(Eval[] args, Workbook workbook, int srcCellSheet, int srcCellRow, int srcCellCol) {
// TODO - implement INDIRECT()
return ErrorEval.FUNCTION_NOT_IMPLEMENTED;
}
Modified: poi/branches/ooxml/src/java/org/apache/poi/hssf/record/formula/functions/Mode.java
URL: http://svn.apache.org/viewvc/poi/branches/ooxml/src/java/org/apache/poi/hssf/record/formula/functions/Mode.java?rev=694947&r1=694946&r2=694947&view=diff
==============================================================================
--- poi/branches/ooxml/src/java/org/apache/poi/hssf/record/formula/functions/Mode.java (original)
+++ poi/branches/ooxml/src/java/org/apache/poi/hssf/record/formula/functions/Mode.java Sat Sep 13 06:48:27 2008
@@ -1,78 +1,134 @@
-/*
-* 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 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((short) (0
- //| ValueEvalToNumericXlator.BOOL_IS_PARSED
- //| ValueEvalToNumericXlator.REF_BOOL_IS_PARSED
- //| ValueEvalToNumericXlator.EVALUATED_REF_BOOL_IS_PARSED
- //| ValueEvalToNumericXlator.STRING_IS_PARSED
- //| ValueEvalToNumericXlator.REF_STRING_IS_PARSED
- //| ValueEvalToNumericXlator.EVALUATED_REF_STRING_IS_PARSED
- //| ValueEvalToNumericXlator.STRING_TO_BOOL_IS_PARSED
- //| ValueEvalToNumericXlator.REF_STRING_TO_BOOL_IS_PARSED
- | ValueEvalToNumericXlator.STRING_IS_INVALID_VALUE
- //| ValueEvalToNumericXlator.REF_STRING_IS_INVALID_VALUE
- //| ValueEvalToNumericXlator.EVALUATED_REF_BLANK_IS_PARSED
- //| ValueEvalToNumericXlator.REF_BLANK_IS_PARSED
- //| ValueEvalToNumericXlator.BLANK_IS_PARSED
- ));
-
- /**
- * 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.
- */
- 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 = StatsLib.mode(values);
- retval = (Double.isNaN(d) || Double.isInfinite(d))
- ? (ValueEval) ErrorEval.NUM_ERROR
- : new NumberEval(d);
- }
-
- return retval;
- }
+public class Mode implements Function {
+
+ /**
+ * 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 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);
+
+ }
+
+ 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;
+ }
+ throw new RuntimeException("Unexpected value type (" + arg.getClass().getName() + ")");
+ }
}
Modified: poi/branches/ooxml/src/java/org/apache/poi/hssf/record/formula/functions/MultiOperandNumericFunction.java
URL: http://svn.apache.org/viewvc/poi/branches/ooxml/src/java/org/apache/poi/hssf/record/formula/functions/MultiOperandNumericFunction.java?rev=694947&r1=694946&r2=694947&view=diff
==============================================================================
--- poi/branches/ooxml/src/java/org/apache/poi/hssf/record/formula/functions/MultiOperandNumericFunction.java (original)
+++ poi/branches/ooxml/src/java/org/apache/poi/hssf/record/formula/functions/MultiOperandNumericFunction.java Sat Sep 13 06:48:27 2008
@@ -1,30 +1,33 @@
-/*
-* 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.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.NumericValueEval;
-import org.apache.poi.hssf.record.formula.eval.Ref2DEval;
+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.OperandResolver;
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 >
@@ -32,175 +35,166 @@
* classes that take variable number of operands, and
* where the order of operands does not matter
*/
-public abstract class MultiOperandNumericFunction extends NumericFunction {
- 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;
- }
-
-
-
+public abstract class MultiOperandNumericFunction implements Function {
+
+ private final boolean _isReferenceBoolCounted;
+ private final boolean _isBlankCounted;
+
+ protected MultiOperandNumericFunction(boolean isReferenceBoolCounted, boolean isBlankCounted) {
+ _isReferenceBoolCounted = isReferenceBoolCounted;
+ _isBlankCounted = isBlankCounted;
+ }
+
+
+ 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;
+
+ 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 ve = ae.getRelativeValue(rrIx, rcIx);
+ collectValue(ve, true, temp);
+ }
+ }
+ return;
+ }
+ if (operand instanceof RefEval) {
+ RefEval re = (RefEval) operand;
+ collectValue(re.getInnerValueEval(), true, temp);
+ return;
+ }
+ collectValue((ValueEval)operand, false, temp);
+ }
+ private void collectValue(ValueEval ve, boolean isViaReference, DoubleList temp) throws EvaluationException {
+ if (ve == null) {
+ throw new IllegalArgumentException("ve must not be null");
+ }
+ if (ve instanceof NumberEval) {
+ NumberEval ne = (NumberEval) ve;
+ temp.add(ne.getNumberValue());
+ return;
+ }
+ if (ve instanceof ErrorEval) {
+ throw new EvaluationException((ErrorEval) ve);
+ }
+ if (ve instanceof StringEval) {
+ if (isViaReference) {
+ // ignore all ref strings
+ return;
+ }
+ String s = ((StringEval) ve).getStringValue();
+ Double d = OperandResolver.parseDouble(s);
+ if(d == null) {
+ throw new EvaluationException(ErrorEval.VALUE_INVALID);
+ }
+ temp.add(d.doubleValue());
+ return;
+ }
+ if (ve instanceof BoolEval) {
+ if (!isViaReference || _isReferenceBoolCounted) {
+ BoolEval boolEval = (BoolEval) ve;
+ temp.add(boolEval.getNumberValue());
+ }
+ return;
+ }
+ if (ve == BlankEval.INSTANCE) {
+ if (_isBlankCounted) {
+ temp.add(0.0);
+ }
+ return;
+ }
+ throw new RuntimeException("Invalid ValueEval type passed for conversion: ("
+ + ve.getClass() + ")");
+ }
}
Modified: poi/branches/ooxml/src/java/org/apache/poi/hssf/record/formula/functions/NumericFunction.java
URL: http://svn.apache.org/viewvc/poi/branches/ooxml/src/java/org/apache/poi/hssf/record/formula/functions/NumericFunction.java?rev=694947&r1=694946&r2=694947&view=diff
==============================================================================
--- poi/branches/ooxml/src/java/org/apache/poi/hssf/record/formula/functions/NumericFunction.java (original)
+++ poi/branches/ooxml/src/java/org/apache/poi/hssf/record/formula/functions/NumericFunction.java Sat Sep 13 06:48:27 2008
@@ -1,98 +1,316 @@
-/*
-* 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 14, 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.AreaEval;
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.OperandResolver;
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 abstract class NumericFunction implements Function {
-
- protected static final double E = Math.E;
- protected static final double PI = Math.PI;
-
- 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_STRING_IS_PARSED
- //| ValueEvalToNumericXlator.STRING_TO_BOOL_IS_PARSED
- //| ValueEvalToNumericXlator.REF_STRING_TO_BOOL_IS_PARSED
- //| ValueEvalToNumericXlator.STRING_IS_INVALID_VALUE
- //| ValueEvalToNumericXlator.REF_STRING_IS_INVALID_VALUE
- ));
-
- private static final int DEFAULT_MAX_NUM_OPERANDS = 30;
-
- /**
- * this is the default impl of the factory(ish) method getXlator.
- * Subclasses can override this method
- * if they desire to return a different ValueEvalToNumericXlator instance
- * than the default.
- */
- protected ValueEvalToNumericXlator getXlator() {
- return DEFAULT_NUM_XLATOR;
- }
-
- protected ValueEval singleOperandEvaluate(Eval eval, int srcRow, short srcCol) {
- ValueEval retval;
- if (eval instanceof AreaEval) {
- AreaEval ae = (AreaEval) eval;
- if (ae.contains(srcRow, srcCol)) { // circular ref!
- retval = ErrorEval.CIRCULAR_REF_ERROR;
- }
- else if (ae.isRow()) {
- if (ae.containsColumn(srcCol)) {
- ValueEval ve = ae.getValueAt(ae.getFirstRow(), srcCol);
- ve = getXlator().attemptXlateToNumeric(ve);
- retval = getXlator().attemptXlateToNumeric(ve);
- }
- else {
- retval = ErrorEval.VALUE_INVALID;
- }
- }
- else if (ae.isColumn()) {
- if (ae.containsRow(srcRow)) {
- ValueEval ve = ae.getValueAt(srcRow, ae.getFirstColumn());
- retval = getXlator().attemptXlateToNumeric(ve);
- }
- else {
- retval = ErrorEval.VALUE_INVALID;
- }
- }
- else {
- retval = ErrorEval.VALUE_INVALID;
- }
- }
- else {
- retval = getXlator().attemptXlateToNumeric((ValueEval) eval);
- }
- return retval;
- }
+ static final double ZERO = 0.0;
+ static final double TEN = 10.0;
+ static final double LOG_10_TO_BASE_e = Math.log(TEN);
+
+ protected static final double singleOperandEvaluate(Eval arg, int srcCellRow, short srcCellCol) throws EvaluationException {
+ ValueEval ve = OperandResolver.getSingleValue(arg, srcCellRow, srcCellCol);
+ double result = OperandResolver.coerceValueToDouble(ve);
+ checkValue(result);
+ return result;
+ }
+
+ private static final void checkValue(double result) throws EvaluationException {
+ if (Double.isNaN(result) || Double.isInfinite(result)) {
+ throw new EvaluationException(ErrorEval.NUM_ERROR);
+ }
+ }
+
+ public final Eval evaluate(Eval[] args, int srcCellRow, short srcCellCol) {
+ double result;
+ try {
+ result = eval(args, srcCellRow, srcCellCol);
+ checkValue(result);
+ } catch (EvaluationException e) {
+ return e.getErrorEval();
+ }
+ return new NumberEval(result);
+ }
+
+ protected abstract double eval(Eval[] args, int srcCellRow, short srcCellCol) throws EvaluationException;
+
+ /* -------------------------------------------------------------------------- */
+ // intermediate sub-classes (one-arg, two-arg and multi-arg
+
+
+ public static abstract class OneArg extends NumericFunction {
+ protected OneArg() {
+ // no fields to initialise
+ }
+ protected final double eval(Eval[] args, int srcCellRow, short srcCellCol) throws EvaluationException {
+ if (args.length != 1) {
+ throw new EvaluationException(ErrorEval.VALUE_INVALID);
+ }
+ double d = singleOperandEvaluate(args[0], srcCellRow, srcCellCol);
+ return evaluate(d);
+ }
+ protected abstract double evaluate(double d) throws EvaluationException;
+ }
+
+ public static abstract class TwoArg extends NumericFunction {
+ protected TwoArg() {
+ // no fields to initialise
+ }
+ protected final double eval(Eval[] args, int srcCellRow, short srcCellCol) throws EvaluationException {
+ if (args.length != 2) {
+ throw new EvaluationException(ErrorEval.VALUE_INVALID);
+ }
+ double d0 = singleOperandEvaluate(args[0], srcCellRow, srcCellCol);
+ double d1 = singleOperandEvaluate(args[1], srcCellRow, srcCellCol);
+ return evaluate(d0, d1);
+ }
+ protected abstract double evaluate(double d0, double d1) throws EvaluationException;
+ }
+
+ public static abstract class MultiArg extends NumericFunction {
+ private final int _minArgs;
+ private final int _maxArgs;
+ protected MultiArg(int minArgs, int maxArgs) {
+ _minArgs = minArgs;
+ _maxArgs = maxArgs;
+ }
+ protected final double eval(Eval[] args, int srcCellRow, short srcCellCol) throws EvaluationException {
+ int nArgs = args.length;
+ if (nArgs < _minArgs || nArgs > _maxArgs) {
+ throw new EvaluationException(ErrorEval.VALUE_INVALID);
+ }
+ double[] ds = new double[nArgs];
+ for(int i=0; i<nArgs; i++) {
+ ds[i] = singleOperandEvaluate(args[i], srcCellRow, srcCellCol);
+ }
+ return evaluate(ds);
+ }
+ protected abstract double evaluate(double[] ds) throws EvaluationException;
+ }
+
+ /* -------------------------------------------------------------------------- */
+
+
+ public static final Function ABS = new OneArg() {
+ protected double evaluate(double d) {
+ return Math.abs(d);
+ }
+ };
+ public static final Function ACOS = new OneArg() {
+ protected double evaluate(double d) {
+ return Math.acos(d);
+ }
+ };
+ public static final Function ACOSH = new OneArg() {
+ protected double evaluate(double d) {
+ return MathX.acosh(d);
+ }
+ };
+ public static final Function ASIN = new OneArg() {
+ protected double evaluate(double d) {
+ return Math.asin(d);
+ }
+ };
+ public static final Function ASINH = new OneArg() {
+ protected double evaluate(double d) {
+ return MathX.asinh(d);
+ }
+ };
+ public static final Function ATAN = new OneArg() {
+ protected double evaluate(double d) {
+ return Math.atan(d);
+ }
+ };
+ public static final Function ATANH = new OneArg() {
+ protected double evaluate(double d) {
+ return MathX.atanh(d);
+ }
+ };
+ public static final Function COS = new OneArg() {
+ protected double evaluate(double d) {
+ return Math.cos(d);
+ }
+ };
+ public static final Function COSH = new OneArg() {
+ protected double evaluate(double d) {
+ return MathX.cosh(d);
+ }
+ };
+ public static final Function DEGREES = new OneArg() {
+ protected double evaluate(double d) {
+ return Math.toDegrees(d);
+ }
+ };
+ public static final Function DOLLAR = new OneArg() {
+ protected double evaluate(double d) {
+ return d;
+ }
+ };
+ public static final Function EXP = new OneArg() {
+ protected double evaluate(double d) {
+ return Math.pow(Math.E, d);
+ }
+ };
+ public static final Function FACT = new OneArg() {
+ protected double evaluate(double d) {
+ return MathX.factorial((int)d);
+ }
+ };
+ public static final Function INT = new OneArg() {
+ protected double evaluate(double d) {
+ return Math.round(d-0.5);
+ }
+ };
+ public static final Function LN = new OneArg() {
+ protected double evaluate(double d) {
+ return Math.log(d);
+ }
+ };
+ public static final Function LOG10 = new OneArg() {
+ protected double evaluate(double d) {
+ return Math.log(d) / LOG_10_TO_BASE_e;
+ }
+ };
+ public static final Function RADIANS = new OneArg() {
+ protected double evaluate(double d) {
+ return Math.toRadians(d);
+ }
+ };
+ public static final Function SIGN = new OneArg() {
+ protected double evaluate(double d) {
+ return MathX.sign(d);
+ }
+ };
+ public static final Function SIN = new OneArg() {
+ protected double evaluate(double d) {
+ return Math.sin(d);
+ }
+ };
+ public static final Function SINH = new OneArg() {
+ protected double evaluate(double d) {
+ return MathX.sinh(d);
+ }
+ };
+ public static final Function SQRT = new OneArg() {
+ protected double evaluate(double d) {
+ return Math.sqrt(d);
+ }
+ };
+
+ public static final Function TAN = new OneArg() {
+ protected double evaluate(double d) {
+ return Math.tan(d);
+ }
+ };
+ public static final Function TANH = new OneArg() {
+ protected double evaluate(double d) {
+ return MathX.tanh(d);
+ }
+ };
+
+
+ /* -------------------------------------------------------------------------- */
+
+ public static final Function ATAN2 = new TwoArg() {
+ protected double evaluate(double d0, double d1) throws EvaluationException {
+ if (d0 == ZERO && d1 == ZERO) {
+ throw new EvaluationException(ErrorEval.DIV_ZERO);
+ }
+ return Math.atan2(d1, d0);
+ }
+ };
+ public static final Function CEILING = new TwoArg() {
+ protected double evaluate(double d0, double d1) {
+ return MathX.ceiling(d0, d1);
+ }
+ };
+ public static final Function COMBIN = new TwoArg() {
+ protected double evaluate(double d0, double d1) throws EvaluationException {
+ if (d0 > Integer.MAX_VALUE || d1 > Integer.MAX_VALUE) {
+ throw new EvaluationException(ErrorEval.NUM_ERROR);
+ }
+ return MathX.nChooseK((int) d0, (int) d1);
+ }
+ };
+ public static final Function FLOOR = new TwoArg() {
+ protected double evaluate(double d0, double d1) throws EvaluationException {
+ if (d1 == ZERO) {
+ if (d0 == ZERO) {
+ return ZERO;
+ }
+ throw new EvaluationException(ErrorEval.DIV_ZERO);
+ }
+ return MathX.floor(d0, d1);
+ }
+ };
+ public static final Function MOD = new TwoArg() {
+ protected double evaluate(double d0, double d1) throws EvaluationException {
+ if (d1 == ZERO) {
+ throw new EvaluationException(ErrorEval.DIV_ZERO);
+ }
+ return MathX.mod(d0, d1);
+ }
+ };
+ public static final Function POWER = new TwoArg() {
+ protected double evaluate(double d0, double d1) {
+ return Math.pow(d0, d1);
+ }
+ };
+ public static final Function ROUND = new TwoArg() {
+ protected double evaluate(double d0, double d1) {
+ return MathX.round(d0, (int)d1);
+ }
+ };
+ public static final Function ROUNDDOWN = new TwoArg() {
+ protected double evaluate(double d0, double d1) {
+ return MathX.roundDown(d0, (int)d1);
+ }
+ };
+ public static final Function ROUNDUP = new TwoArg() {
+ protected double evaluate(double d0, double d1) {
+ return MathX.roundUp(d0, (int)d1);
+ }
+ };
+
+ /* -------------------------------------------------------------------------- */
+
+ public static final Function LOG = new MultiArg(1,2) {
+ protected double evaluate(double[] ds) {
+
+ double logE = Math.log(ds[0]);
+ if (ds.length == 1) {
+ return logE / LOG_10_TO_BASE_e;
+ }
+ double base = ds[1];
+ if (base == Math.E) {
+ return logE;
+ }
+ return logE / Math.log(base);
+ }
+ };
}
Modified: poi/branches/ooxml/src/java/org/apache/poi/hssf/record/formula/functions/Odd.java
URL: http://svn.apache.org/viewvc/poi/branches/ooxml/src/java/org/apache/poi/hssf/record/formula/functions/Odd.java?rev=694947&r1=694946&r2=694947&view=diff
==============================================================================
--- poi/branches/ooxml/src/java/org/apache/poi/hssf/record/formula/functions/Odd.java (original)
+++ poi/branches/ooxml/src/java/org/apache/poi/hssf/record/formula/functions/Odd.java Sat Sep 13 06:48:27 2008
@@ -22,7 +22,7 @@
* @author Amol S. Deshmukh < amolweb at ya hoo dot com >
*
*/
-public final class Odd extends NumericFunctionOneArg {
+public final class Odd extends NumericFunction.OneArg {
private static final long PARITY_MASK = 0xFFFFFFFFFFFFFFFEL;
protected double evaluate(double d) {
Modified: poi/branches/ooxml/src/java/org/apache/poi/hssf/record/formula/functions/Replace.java
URL: http://svn.apache.org/viewvc/poi/branches/ooxml/src/java/org/apache/poi/hssf/record/formula/functions/Replace.java?rev=694947&r1=694946&r2=694947&view=diff
==============================================================================
--- poi/branches/ooxml/src/java/org/apache/poi/hssf/record/formula/functions/Replace.java (original)
+++ poi/branches/ooxml/src/java/org/apache/poi/hssf/record/formula/functions/Replace.java Sat Sep 13 06:48:27 2008
@@ -1,112 +1,70 @@
-/*
-* 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.NumericValueEval;
+import org.apache.poi.hssf.record.formula.eval.EvaluationException;
import org.apache.poi.hssf.record.formula.eval.StringEval;
-import org.apache.poi.hssf.record.formula.eval.StringValueEval;
import org.apache.poi.hssf.record.formula.eval.ValueEval;
/**
- * An implementation of the REPLACE function:
- * Replaces part of a text string based on the number of characters
- * you specify, with another text string.
+ * An implementation of the Excel REPLACE() function<p/>:
+ * Replaces part of a text string based on the number of characters
+ * you specify, with another text string.<br/>
+ *
+ * <b>Syntax</b>:<br/>
+ * <b>REPLACE</b>(<b>oldText</b>, <b>startNum</b>, <b>numChars</b>, <b>newText</b>)<p/>
+ *
+ * <b>oldText</b> The text string containing characters to replace<br/>
+ * <b>startNum</b> The position of the first character to replace (1-based)<br/>
+ * <b>numChars</b> The number of characters to replace<br/>
+ * <b>newText</b> The new text value to replace the removed section<br/>
+ *
* @author Manda Wilson < wilson at c bio dot msk cc dot org >
*/
-public class Replace extends TextFunction {
+public final class Replace extends TextFunction {
+
+ protected ValueEval evaluateFunc(Eval[] args, int srcCellRow, short srcCellCol)
+ throws EvaluationException {
+ if (args.length != 4) {
+ return ErrorEval.VALUE_INVALID;
+ }
- /**
- * Replaces part of a text string based on the number of characters
- * you specify, with another text string.
- *
- * @see org.apache.poi.hssf.record.formula.eval.Eval
- */
- public Eval evaluate(Eval[] operands, int srcCellRow, short srcCellCol) {
- Eval retval = null;
- String oldStr = null;
- String newStr = null;
- int startNum = 0;
- int numChars = 0;
-
- switch (operands.length) {
- default:
- retval = ErrorEval.VALUE_INVALID;
- case 4:
- // first operand is text string containing characters to replace
- // second operand is position of first character to replace
- // third operand is the number of characters in the old string
- // you want to replace with new string
- // fourth operand is the new string
- ValueEval firstveval = singleOperandEvaluate(operands[0], srcCellRow, srcCellCol);
- ValueEval secondveval = singleOperandEvaluate(operands[1], srcCellRow, srcCellCol);
- ValueEval thirdveval = singleOperandEvaluate(operands[2], srcCellRow, srcCellCol);
- ValueEval fourthveval = singleOperandEvaluate(operands[3], srcCellRow, srcCellCol);
- if (firstveval instanceof StringValueEval
- && secondveval instanceof NumericValueEval
- && thirdveval instanceof NumericValueEval
- && fourthveval instanceof StringValueEval) {
-
- StringValueEval oldStrEval = (StringValueEval) firstveval;
- oldStr = oldStrEval.getStringValue();
-
- NumericValueEval startNumEval = (NumericValueEval) secondveval;
- // NOTE: it is safe to cast to int here
- // because in Excel =REPLACE("task", 2.7, 3, "est")
- // returns test
- // so 2.7 must be truncated to 2
- // and =REPLACE("task", 1, 1.9, "") returns ask
- // so 1.9 must be truncated to 1
- startNum = (int) startNumEval.getNumberValue();
-
- NumericValueEval numCharsEval = (NumericValueEval) thirdveval;
- numChars = (int) numCharsEval.getNumberValue();
-
- StringValueEval newStrEval = (StringValueEval) fourthveval;
- newStr = newStrEval.getStringValue();
- } else {
- retval = ErrorEval.VALUE_INVALID;
- }
- }
-
- if (retval == null) {
- if (startNum < 1 || numChars < 0) {
- retval = ErrorEval.VALUE_INVALID;
- } else {
- StringBuffer strBuff = new StringBuffer(oldStr);
- // remove any characters that should be replaced
- if (startNum <= oldStr.length() && numChars != 0) {
- strBuff.delete(startNum - 1, startNum - 1 + numChars);
- }
- // now insert (or append) newStr
- if (startNum > strBuff.length()) {
- strBuff.append(newStr);
- } else {
- strBuff.insert(startNum - 1, newStr);
- }
- retval = new StringEval(strBuff.toString());
- }
- }
- return retval;
- }
+ String oldStr = evaluateStringArg(args[0], srcCellRow, srcCellCol);
+ int startNum = evaluateIntArg(args[1], srcCellRow, srcCellCol);
+ int numChars = evaluateIntArg(args[2], srcCellRow, srcCellCol);
+ String newStr = evaluateStringArg(args[3], srcCellRow, srcCellCol);
+ if (startNum < 1 || numChars < 0) {
+ return ErrorEval.VALUE_INVALID;
+ }
+ StringBuffer strBuff = new StringBuffer(oldStr);
+ // remove any characters that should be replaced
+ if (startNum <= oldStr.length() && numChars != 0) {
+ strBuff.delete(startNum - 1, startNum - 1 + numChars);
+ }
+ // now insert (or append) newStr
+ if (startNum > strBuff.length()) {
+ strBuff.append(newStr);
+ } else {
+ strBuff.insert(startNum - 1, newStr);
+ }
+ return new StringEval(strBuff.toString());
+ }
}
Modified: poi/branches/ooxml/src/java/org/apache/poi/hssf/record/formula/functions/StatsLib.java
URL: http://svn.apache.org/viewvc/poi/branches/ooxml/src/java/org/apache/poi/hssf/record/formula/functions/StatsLib.java?rev=694947&r1=694946&r2=694947&view=diff
==============================================================================
--- poi/branches/ooxml/src/java/org/apache/poi/hssf/record/formula/functions/StatsLib.java (original)
+++ poi/branches/ooxml/src/java/org/apache/poi/hssf/record/formula/functions/StatsLib.java Sat Sep 13 06:48:27 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/branches/ooxml/src/java/org/apache/poi/hssf/record/formula/functions/Substitute.java
URL: http://svn.apache.org/viewvc/poi/branches/ooxml/src/java/org/apache/poi/hssf/record/formula/functions/Substitute.java?rev=694947&r1=694946&r2=694947&view=diff
==============================================================================
--- poi/branches/ooxml/src/java/org/apache/poi/hssf/record/formula/functions/Substitute.java (original)
+++ poi/branches/ooxml/src/java/org/apache/poi/hssf/record/formula/functions/Substitute.java Sat Sep 13 06:48:27 2008
@@ -1,30 +1,26 @@
-/*
-* 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.NumericValueEval;
+import org.apache.poi.hssf.record.formula.eval.EvaluationException;
import org.apache.poi.hssf.record.formula.eval.StringEval;
-import org.apache.poi.hssf.record.formula.eval.StringValueEval;
import org.apache.poi.hssf.record.formula.eval.ValueEval;
/**
@@ -32,86 +28,75 @@
* Substitutes text in a text string with new text, some number of times.
* @author Manda Wilson < wilson at c bio dot msk cc dot org >
*/
-public class Substitute extends TextFunction {
- private static final int REPLACE_ALL = -1;
-
- /**
- *Substitutes text in a text string with new text, some number of times.
- *
- * @see org.apache.poi.hssf.record.formula.eval.Eval
- */
- public Eval evaluate(Eval[] operands, int srcCellRow, short srcCellCol) {
- Eval retval = null;
- String oldStr = null;
- String searchStr = null;
- String newStr = null;
- int numToReplace = REPLACE_ALL;
-
- switch (operands.length) {
- default:
- retval = ErrorEval.VALUE_INVALID;
- case 4:
- ValueEval fourthveval = singleOperandEvaluate(operands[3], srcCellRow, srcCellCol);
- if (fourthveval instanceof NumericValueEval) {
- NumericValueEval numToReplaceEval = (NumericValueEval) fourthveval;
- // NOTE: it is safe to cast to int here
- // because in Excel =SUBSTITUTE("teststr","t","T",1.9)
- // returns Teststr
- // so 1.9 must be truncated to 1
- numToReplace = (int) numToReplaceEval.getNumberValue();
- } else {
- retval = ErrorEval.VALUE_INVALID;
- }
- case 3:
- // first operand is text string containing characters to replace
- // second operand is text to find
- // third operand is replacement text
- ValueEval firstveval = singleOperandEvaluate(operands[0], srcCellRow, srcCellCol);
- ValueEval secondveval = singleOperandEvaluate(operands[1], srcCellRow, srcCellCol);
- ValueEval thirdveval = singleOperandEvaluate(operands[2], srcCellRow, srcCellCol);
- if (firstveval instanceof StringValueEval
- && secondveval instanceof StringValueEval
- && thirdveval instanceof StringValueEval) {
-
- StringValueEval oldStrEval = (StringValueEval) firstveval;
- oldStr = oldStrEval.getStringValue();
-
- StringValueEval searchStrEval = (StringValueEval) secondveval;
- searchStr = searchStrEval.getStringValue();
-
- StringValueEval newStrEval = (StringValueEval) thirdveval;
- newStr = newStrEval.getStringValue();
- } else {
- retval = ErrorEval.VALUE_INVALID;
- }
- }
-
- if (retval == null) {
- if (numToReplace != REPLACE_ALL && numToReplace < 1) {
- retval = ErrorEval.VALUE_INVALID;
- } else if (searchStr.length() == 0) {
- retval = new StringEval(oldStr);
- } else {
- StringBuffer strBuff = new StringBuffer();
- int startIndex = 0;
- int nextMatch = -1;
- for (int leftToReplace = numToReplace;
- (leftToReplace > 0 || numToReplace == REPLACE_ALL)
- && (nextMatch = oldStr.indexOf(searchStr, startIndex)) != -1;
- leftToReplace--) {
- // store everything from end of last match to start of this match
- strBuff.append(oldStr.substring(startIndex, nextMatch));
- strBuff.append(newStr);
- startIndex = nextMatch + searchStr.length();
+public final class Substitute extends TextFunction {
+
+ protected ValueEval evaluateFunc(Eval[] args, int srcCellRow, short srcCellCol)
+ throws EvaluationException {
+ if (args.length < 3 || args.length > 4) {
+ return ErrorEval.VALUE_INVALID;
+ }
+
+ String oldStr = evaluateStringArg(args[0], srcCellRow, srcCellCol);
+ String searchStr = evaluateStringArg(args[1], srcCellRow, srcCellCol);
+ String newStr = evaluateStringArg(args[2], srcCellRow, srcCellCol);
+
+ String result;
+ switch (args.length) {
+ default:
+ throw new IllegalStateException("Cannot happen");
+ case 4:
+ int instanceNumber = evaluateIntArg(args[3], srcCellRow, srcCellCol);
+ if (instanceNumber < 1) {
+ return ErrorEval.VALUE_INVALID;
}
+ result = replaceOneOccurrence(oldStr, searchStr, newStr, instanceNumber);
+ break;
+ case 3:
+ result = replaceAllOccurrences(oldStr, searchStr, newStr);
+ }
+ return new StringEval(result);
+ }
+
+ private static String replaceAllOccurrences(String oldStr, String searchStr, String newStr) {
+ StringBuffer sb = new StringBuffer();
+ int startIndex = 0;
+ int nextMatch = -1;
+ while (true) {
+ nextMatch = oldStr.indexOf(searchStr, startIndex);
+ if (nextMatch < 0) {
// store everything from end of last match to end of string
- if (startIndex < oldStr.length()) {
- strBuff.append(oldStr.substring(startIndex));
- }
- retval = new StringEval(strBuff.toString());
+ sb.append(oldStr.substring(startIndex));
+ return sb.toString();
+ }
+ // store everything from end of last match to start of this match
+ sb.append(oldStr.substring(startIndex, nextMatch));
+ sb.append(newStr);
+ startIndex = nextMatch + searchStr.length();
+ }
+ }
+
+ private static String replaceOneOccurrence(String oldStr, String searchStr, String newStr, int instanceNumber) {
+ if (searchStr.length() < 1) {
+ return oldStr;
+ }
+ int startIndex = 0;
+ int nextMatch = -1;
+ int count=0;
+ while (true) {
+ nextMatch = oldStr.indexOf(searchStr, startIndex);
+ if (nextMatch < 0) {
+ // not enough occurrences found - leave unchanged
+ return oldStr;
+ }
+ count++;
+ if (count == instanceNumber) {
+ StringBuffer sb = new StringBuffer(oldStr.length() + newStr.length());
+ sb.append(oldStr.substring(0, nextMatch));
+ sb.append(newStr);
+ sb.append(oldStr.substring(nextMatch + searchStr.length()));
+ return sb.toString();
}
- }
- return retval;
- }
-
+ startIndex = nextMatch + searchStr.length();
+ }
+ }
}
Modified: poi/branches/ooxml/src/java/org/apache/poi/hssf/record/formula/functions/TextFunction.java
URL: http://svn.apache.org/viewvc/poi/branches/ooxml/src/java/org/apache/poi/hssf/record/formula/functions/TextFunction.java?rev=694947&r1=694946&r2=694947&view=diff
==============================================================================
--- poi/branches/ooxml/src/java/org/apache/poi/hssf/record/formula/functions/TextFunction.java (original)
+++ poi/branches/ooxml/src/java/org/apache/poi/hssf/record/formula/functions/TextFunction.java Sat Sep 13 06:48:27 2008
@@ -1,31 +1,29 @@
-/*
-* 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 22, 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.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.RefEval;
-import org.apache.poi.hssf.record.formula.eval.StringValueEval;
+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.OperandResolver;
+import org.apache.poi.hssf.record.formula.eval.StringEval;
import org.apache.poi.hssf.record.formula.eval.ValueEval;
/**
@@ -33,75 +31,164 @@
*
*/
public abstract class TextFunction implements Function {
-
- protected static final String EMPTY_STRING = "";
-
- protected ValueEval singleOperandEvaluate(Eval eval, int srcRow, short srcCol) {
- ValueEval retval;
- if (eval instanceof AreaEval) {
- AreaEval ae = (AreaEval) eval;
- if (ae.contains(srcRow, srcCol)) { // circular ref!
- retval = ErrorEval.CIRCULAR_REF_ERROR;
- }
- else if (ae.isRow()) {
- if (ae.containsColumn(srcCol)) {
- ValueEval ve = ae.getValueAt(ae.getFirstRow(), srcCol);
- retval = attemptXlateToText(ve);
- }
- else {
- retval = ErrorEval.VALUE_INVALID;
- }
- }
- else if (ae.isColumn()) {
- if (ae.containsRow(srcRow)) {
- ValueEval ve = ae.getValueAt(srcRow, ae.getFirstColumn());
- retval = attemptXlateToText(ve);
- }
- else {
- retval = ErrorEval.VALUE_INVALID;
- }
- }
- else {
- retval = ErrorEval.VALUE_INVALID;
- }
- }
- else {
- retval = attemptXlateToText((ValueEval) eval);
- }
- return retval;
- }
-
-
- /**
- * converts from Different ValueEval types to StringEval.
- * Note: AreaEvals are not handled, if arg is an AreaEval,
- * the returned value is ErrorEval.VALUE_INVALID
- * @param ve
- */
- protected ValueEval attemptXlateToText(ValueEval ve) {
- ValueEval retval;
- if (ve instanceof StringValueEval) {
- retval = ve;
- }
- else if (ve instanceof RefEval) {
- RefEval re = (RefEval) ve;
- ValueEval ive = re.getInnerValueEval();
- if (ive instanceof StringValueEval) {
- retval = ive;
- }
- else if (ive instanceof BlankEval) {
- retval = ive;
- }
- else {
- retval = ErrorEval.VALUE_INVALID;
- }
- }
- else if (ve instanceof BlankEval) {
- retval = ve;
- }
- else {
- retval = ErrorEval.VALUE_INVALID;
- }
- return retval;
- }
+
+ protected static final String EMPTY_STRING = "";
+
+ protected static final String evaluateStringArg(Eval eval, int srcRow, short srcCol) throws EvaluationException {
+ ValueEval ve = OperandResolver.getSingleValue(eval, srcRow, srcCol);
+ return OperandResolver.coerceValueToString(ve);
+ }
+ protected static final int evaluateIntArg(Eval arg, int srcCellRow, short srcCellCol) throws EvaluationException {
+ ValueEval ve = OperandResolver.getSingleValue(arg, srcCellRow, srcCellCol);
+ return OperandResolver.coerceValueToInt(ve);
+ }
+
+ public final Eval evaluate(Eval[] args, int srcCellRow, short srcCellCol) {
+ try {
+ return evaluateFunc(args, srcCellRow, srcCellCol);
+ } catch (EvaluationException e) {
+ return e.getErrorEval();
+ }
+ }
+
+ protected abstract ValueEval evaluateFunc(Eval[] args, int srcCellRow, short srcCellCol) throws EvaluationException;
+
+ /* ---------------------------------------------------------------------- */
+
+ private static abstract class SingleArgTextFunc extends TextFunction {
+
+ protected SingleArgTextFunc() {
+ // no fields to initialise
+ }
+ protected ValueEval evaluateFunc(Eval[] args, int srcCellRow, short srcCellCol)
+ throws EvaluationException {
+ if (args.length != 1) {
+ return ErrorEval.VALUE_INVALID;
+ }
+ String arg = evaluateStringArg(args[0], srcCellRow, srcCellCol);
+ return evaluate(arg);
+ }
+ protected abstract ValueEval evaluate(String arg);
+ }
+
+ public static final Function LEN = new SingleArgTextFunc() {
+ protected ValueEval evaluate(String arg) {
+ return new NumberEval(arg.length());
+ }
+ };
+ public static final Function LOWER = new SingleArgTextFunc() {
+ protected ValueEval evaluate(String arg) {
+ return new StringEval(arg.toLowerCase());
+ }
+ };
+ public static final Function UPPER = new SingleArgTextFunc() {
+ protected ValueEval evaluate(String arg) {
+ return new StringEval(arg.toUpperCase());
+ }
+ };
+ /**
+ * An implementation of the TRIM function:
+ * Removes leading and trailing spaces from value if evaluated operand
+ * value is string.
+ * @author Manda Wilson < wilson at c bio dot msk cc dot org >
+ */
+ public static final Function TRIM = new SingleArgTextFunc() {
+ protected ValueEval evaluate(String arg) {
+ return new StringEval(arg.trim());
+ }
+ };
+
+ /**
+ * An implementation of the MID function<br/>
+ * MID returns a specific number of
+ * characters from a text string, starting at the specified position.<p/>
+ *
+ * <b>Syntax<b>:<br/> <b>MID</b>(<b>text</b>, <b>start_num</b>,
+ * <b>num_chars</b>)<br/>
+ *
+ * @author Manda Wilson < wilson at c bio dot msk cc dot org >
+ */
+ public static final Function MID = new TextFunction() {
+
+ protected ValueEval evaluateFunc(Eval[] args, int srcCellRow, short srcCellCol)
+ throws EvaluationException {
+ if (args.length != 3) {
+ return ErrorEval.VALUE_INVALID;
+ }
+
+ String text = evaluateStringArg(args[0], srcCellRow, srcCellCol);
+ int startCharNum = evaluateIntArg(args[1], srcCellRow, srcCellCol);
+ int numChars = evaluateIntArg(args[2], srcCellRow, srcCellCol);
+ int startIx = startCharNum - 1; // convert to zero-based
+
+ // Note - for start_num arg, blank/zero causes error(#VALUE!),
+ // but for num_chars causes empty string to be returned.
+ if (startIx < 0) {
+ return ErrorEval.VALUE_INVALID;
+ }
+ if (numChars < 0) {
+ return ErrorEval.VALUE_INVALID;
+ }
+ int len = text.length();
+ if (numChars < 0 || startIx > len) {
+ return new StringEval("");
+ }
+ int endIx = Math.min(startIx + numChars, len);
+ String result = text.substring(startIx, endIx);
+ return new StringEval(result);
+ }
+ };
+
+ private static final class LeftRight extends TextFunction {
+
+ private final boolean _isLeft;
+ protected LeftRight(boolean isLeft) {
+ _isLeft = isLeft;
+ }
+ protected ValueEval evaluateFunc(Eval[] args, int srcCellRow, short srcCellCol)
+ throws EvaluationException {
+ if (args.length != 2) {
+ return ErrorEval.VALUE_INVALID;
+ }
+ String arg = evaluateStringArg(args[0], srcCellRow, srcCellCol);
+ int index = evaluateIntArg(args[1], srcCellRow, srcCellCol);
+
+ String result;
+ if (_isLeft) {
+ result = arg.substring(0, Math.min(arg.length(), index));
+ } else {
+ result = arg.substring(Math.max(0, arg.length()-index));
+ }
+ return new StringEval(result);
+ }
+ }
+
+ public static final Function LEFT = new LeftRight(true);
+ public static final Function RIGHT = new LeftRight(false);
+
+ public static final Function CONCATENATE = new TextFunction() {
+
+ protected ValueEval evaluateFunc(Eval[] args, int srcCellRow, short srcCellCol)
+ throws EvaluationException {
+ StringBuffer sb = new StringBuffer();
+ for (int i=0, iSize=args.length; i<iSize; i++) {
+ sb.append(evaluateStringArg(args[i], srcCellRow, srcCellCol));
+ }
+ return new StringEval(sb.toString());
+ }
+ };
+
+ public static final Function EXACT = new TextFunction() {
+
+ protected ValueEval evaluateFunc(Eval[] args, int srcCellRow, short srcCellCol)
+ throws EvaluationException {
+ if (args.length != 2) {
+ return ErrorEval.VALUE_INVALID;
+ }
+
+ String s0 = evaluateStringArg(args[0], srcCellRow, srcCellCol);
+ String s1 = evaluateStringArg(args[1], srcCellRow, srcCellCol);
+ return BoolEval.valueOf(s0.equals(s1));
+ }
+ };
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org