poi-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From n...@apache.org
Subject 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...
Date Sat, 13 Sep 2008 13:48:31 GMT
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 &lt; amolweb at ya hoo dot com &gt;
- *
+ * 
  */
-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 &lt; amolweb at ya hoo dot com &gt;
@@ -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 &lt; amolweb at ya hoo dot com &gt;
- *
  */
 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 &lt; amolweb at ya hoo dot com &gt;
  *  
  */
-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 &lt; wilson at c bio dot msk cc dot org &gt;
  */
-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 &lt; wilson at c bio dot msk cc dot org &gt;
  */
-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 &lt; wilson at c bio dot msk cc dot org &gt;
+	 */
+	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 &lt; wilson at c bio dot msk cc dot org &gt;
+	 */
+	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


Mime
View raw message