You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by jo...@apache.org on 2009/11/22 07:27:55 UTC
svn commit: r883041 - in
/poi/trunk/src/java/org/apache/poi/hssf/record/formula: eval/ functions/
Author: josh
Date: Sun Nov 22 06:27:55 2009
New Revision: 883041
URL: http://svn.apache.org/viewvc?rev=883041&view=rev
Log:
refactored functions taking optionally 2 or 3 parameters
Added:
poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Var2or3ArgFunction.java
Removed:
poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Find.java
Modified:
poi/trunk/src/java/org/apache/poi/hssf/record/formula/eval/FunctionEval.java
poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Days360.java
poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/If.java
poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Lookup.java
poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Match.java
poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Sumif.java
poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/TextFunction.java
Modified: poi/trunk/src/java/org/apache/poi/hssf/record/formula/eval/FunctionEval.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/record/formula/eval/FunctionEval.java?rev=883041&r1=883040&r2=883041&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/record/formula/eval/FunctionEval.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/record/formula/eval/FunctionEval.java Sun Nov 22 06:27:55 2009
@@ -109,6 +109,7 @@
retval[76] = new Rows();
retval[77] = new Columns();
retval[ID.OFFSET] = new Offset();
+ retval[82] = TextFunction.SEARCH;
retval[97] = NumericFunction.ATAN2;
retval[98] = NumericFunction.ASIN;
@@ -131,7 +132,7 @@
retval[119] = new Replace();
retval[120] = new Substitute();
- retval[124] = new Find();
+ retval[124] = TextFunction.FIND;
retval[127] = LogicalFunction.IsText;
retval[128] = LogicalFunction.IsNumber;
Modified: poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Days360.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Days360.java?rev=883041&r1=883040&r2=883041&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Days360.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Days360.java Sun Nov 22 06:27:55 2009
@@ -4,6 +4,9 @@
import java.util.GregorianCalendar;
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.ss.usermodel.DateUtil;
/**
@@ -11,14 +14,39 @@
* (twelve 30-day months), which is used in some accounting calculations. Use
* this function to help compute payments if your accounting system is based on
* twelve 30-day months.
- *
- *
+ *
* @author PUdalau
*/
-public class Days360 extends NumericFunction.TwoArg {
+public class Days360 extends Var2or3ArgFunction {
- @Override
- protected double evaluate(double d0, double d1) throws EvaluationException {
+ public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) {
+ double result;
+ try {
+ double d0 = NumericFunction.singleOperandEvaluate(arg0, srcRowIndex, srcColumnIndex);
+ double d1 = NumericFunction.singleOperandEvaluate(arg1, srcRowIndex, srcColumnIndex);
+ result = evaluate(d0, d1, false);
+ } catch (EvaluationException e) {
+ return e.getErrorEval();
+ }
+ return new NumberEval(result);
+ }
+
+ public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1,
+ ValueEval arg2) {
+ double result;
+ try {
+ double d0 = NumericFunction.singleOperandEvaluate(arg0, srcRowIndex, srcColumnIndex);
+ double d1 = NumericFunction.singleOperandEvaluate(arg1, srcRowIndex, srcColumnIndex);
+ ValueEval ve = OperandResolver.getSingleValue(arg2, srcRowIndex, srcColumnIndex);
+ Boolean method = OperandResolver.coerceValueToBoolean(ve, false);
+ result = evaluate(d0, d1, method == null ? false : method.booleanValue());
+ } catch (EvaluationException e) {
+ return e.getErrorEval();
+ }
+ return new NumberEval(result);
+ }
+
+ private static double evaluate(double d0, double d1, boolean method) {
Calendar startingDate = getStartingDate(d0);
Calendar endingDate = getEndingDateAccordingToStartingDate(d1, startingDate);
long startingDay = startingDate.get(Calendar.MONTH) * 30 + startingDate.get(Calendar.DAY_OF_MONTH);
@@ -27,13 +55,13 @@
return endingDay - startingDay;
}
- private Calendar getDate(double date) {
+ private static Calendar getDate(double date) {
Calendar processedDate = new GregorianCalendar();
processedDate.setTime(DateUtil.getJavaDate(date, false));
return processedDate;
}
- private Calendar getStartingDate(double date) {
+ private static Calendar getStartingDate(double date) {
Calendar startingDate = getDate(date);
if (isLastDayOfMonth(startingDate)) {
startingDate.set(Calendar.DAY_OF_MONTH, 30);
@@ -41,7 +69,7 @@
return startingDate;
}
- private Calendar getEndingDateAccordingToStartingDate(double date, Calendar startingDate) {
+ private static Calendar getEndingDateAccordingToStartingDate(double date, Calendar startingDate) {
Calendar endingDate = getDate(date);
endingDate.setTime(DateUtil.getJavaDate(date, false));
if (isLastDayOfMonth(endingDate)) {
@@ -52,7 +80,7 @@
return endingDate;
}
- private boolean isLastDayOfMonth(Calendar date) {
+ private static boolean isLastDayOfMonth(Calendar date) {
Calendar clone = (Calendar) date.clone();
clone.add(java.util.Calendar.MONTH, 1);
clone.add(java.util.Calendar.DAY_OF_MONTH, -1);
@@ -60,7 +88,7 @@
return date.get(Calendar.DAY_OF_MONTH) == lastDayOfMonth;
}
- private Calendar getFirstDayOfNextMonth(Calendar date) {
+ private static Calendar getFirstDayOfNextMonth(Calendar date) {
Calendar newDate = (Calendar) date.clone();
if (date.get(Calendar.MONTH) < Calendar.DECEMBER) {
newDate.set(Calendar.MONTH, date.get(Calendar.MONTH) + 1);
Modified: poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/If.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/If.java?rev=883041&r1=883040&r2=883041&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/If.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/If.java Sun Nov 22 06:27:55 2009
@@ -18,39 +18,34 @@
package org.apache.poi.hssf.record.formula.functions;
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.EvaluationException;
import org.apache.poi.hssf.record.formula.eval.OperandResolver;
import org.apache.poi.hssf.record.formula.eval.ValueEval;
/**
* @author Amol S. Deshmukh < amolweb at ya hoo dot com >
- *
*/
-public final class If implements Function {
+public final class If extends Var2or3ArgFunction {
- public ValueEval evaluate(ValueEval[] args, int srcCellRow, int srcCellCol) {
- ValueEval falseResult;
- switch (args.length) {
- case 3:
- falseResult = args[2];
- break;
- case 2:
- falseResult = BoolEval.FALSE;
- break;
- default:
- return ErrorEval.VALUE_INVALID;
- }
+ public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) {
boolean b;
try {
- b = evaluateFirstArg(args[0], srcCellRow, srcCellCol);
+ b = evaluateFirstArg(arg0, srcRowIndex, srcColumnIndex);
} catch (EvaluationException e) {
return e.getErrorEval();
}
- if (b) {
- return args[1];
+ return b ? arg1 : BoolEval.FALSE;
+ }
+
+ public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1,
+ ValueEval arg2) {
+ boolean b;
+ try {
+ b = evaluateFirstArg(arg0, srcRowIndex, srcColumnIndex);
+ } catch (EvaluationException e) {
+ return e.getErrorEval();
}
- return falseResult;
+ return b ? arg1 : arg2;
}
public static boolean evaluateFirstArg(ValueEval arg, int srcCellRow, int srcCellCol)
Modified: poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Lookup.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Lookup.java?rev=883041&r1=883040&r2=883041&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Lookup.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Lookup.java Sun Nov 22 06:27:55 2009
@@ -18,7 +18,6 @@
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.EvaluationException;
import org.apache.poi.hssf.record.formula.eval.OperandResolver;
import org.apache.poi.hssf.record.formula.eval.ValueEval;
@@ -38,24 +37,19 @@
*
* @author Josh Micich
*/
-public final class Lookup implements Function {
-
- public ValueEval evaluate(ValueEval[] args, int srcCellRow, int srcCellCol) {
- switch(args.length) {
- case 3:
- break;
- case 2:
- // complex rules to choose lookupVector and resultVector from the single area ref
- throw new RuntimeException("Two arg version of LOOKUP not supported yet");
- default:
- return ErrorEval.VALUE_INVALID;
- }
+public final class Lookup extends Var2or3ArgFunction {
+ public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) {
+ // complex rules to choose lookupVector and resultVector from the single area ref
+ throw new RuntimeException("Two arg version of LOOKUP not supported yet");
+ }
+ public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1,
+ ValueEval arg2) {
try {
- ValueEval lookupValue = OperandResolver.getSingleValue(args[0], srcCellRow, srcCellCol);
- AreaEval aeLookupVector = LookupUtils.resolveTableArrayArg(args[1]);
- AreaEval aeResultVector = LookupUtils.resolveTableArrayArg(args[2]);
+ ValueEval lookupValue = OperandResolver.getSingleValue(arg0, srcRowIndex, srcColumnIndex);
+ AreaEval aeLookupVector = LookupUtils.resolveTableArrayArg(arg1);
+ AreaEval aeResultVector = LookupUtils.resolveTableArrayArg(arg2);
ValueVector lookupVector = createVector(aeLookupVector);
ValueVector resultVector = createVector(aeResultVector);
Modified: poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Match.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Match.java?rev=883041&r1=883040&r2=883041&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Match.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Match.java Sun Nov 22 06:27:55 2009
@@ -63,37 +63,40 @@
*
* @author Josh Micich
*/
-public final class Match implements Function {
+public final class Match extends Var2or3ArgFunction {
+ public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) {
+ // default match_type is 1.0
+ return eval(srcRowIndex, srcColumnIndex, arg0, arg1, 1.0);
+ }
- public ValueEval evaluate(ValueEval[] args, int srcCellRow, int srcCellCol) {
- double match_type = 1; // default
+ public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1,
+ ValueEval arg2) {
- switch(args.length) {
- case 3:
- try {
- match_type = evaluateMatchTypeArg(args[2], srcCellRow, srcCellCol);
- } catch (EvaluationException e) {
- // Excel/MATCH() seems to have slightly abnormal handling of errors with
- // the last parameter. Errors do not propagate up. Every error gets
- // translated into #REF!
- return ErrorEval.REF_INVALID;
- }
- case 2:
- break;
- default:
- return ErrorEval.VALUE_INVALID;
+ double match_type;
+
+ try {
+ match_type = evaluateMatchTypeArg(arg2, srcRowIndex, srcColumnIndex);
+ } catch (EvaluationException e) {
+ // Excel/MATCH() seems to have slightly abnormal handling of errors with
+ // the last parameter. Errors do not propagate up. Every error gets
+ // translated into #REF!
+ return ErrorEval.REF_INVALID;
}
+ return eval(srcRowIndex, srcColumnIndex, arg0, arg1, match_type);
+ }
+
+ private static ValueEval eval(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1,
+ double match_type) {
boolean matchExact = match_type == 0;
// Note - Excel does not strictly require -1 and +1
boolean findLargestLessThanOrEqual = match_type > 0;
-
try {
- ValueEval lookupValue = OperandResolver.getSingleValue(args[0], srcCellRow, srcCellCol);
- ValueVector lookupRange = evaluateLookupRange(args[1]);
+ ValueEval lookupValue = OperandResolver.getSingleValue(arg0, srcRowIndex, srcColumnIndex);
+ ValueVector lookupRange = evaluateLookupRange(arg1);
int index = findIndexOfValue(lookupValue, lookupRange, matchExact, findLargestLessThanOrEqual);
return new NumberEval(index + 1); // +1 to convert to 1-based
} catch (EvaluationException e) {
Modified: poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Sumif.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Sumif.java?rev=883041&r1=883040&r2=883041&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Sumif.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Sumif.java Sun Nov 22 06:27:55 2009
@@ -38,32 +38,37 @@
* </p>
* @author Josh Micich
*/
-public final class Sumif implements Function {
+public final class Sumif extends Var2or3ArgFunction {
- public ValueEval evaluate(ValueEval[] args, int srcRowIndex, int srcColumnIndex) {
- if (args.length < 2) {
- return ErrorEval.VALUE_INVALID;
+ public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) {
+
+ AreaEval aeRange;
+ try {
+ aeRange = convertRangeArg(arg0);
+ } catch (EvaluationException e) {
+ return e.getErrorEval();
}
+ return eval(srcRowIndex, srcColumnIndex, arg1, aeRange, aeRange);
+ }
+
+ public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1,
+ ValueEval arg2) {
AreaEval aeRange;
AreaEval aeSum;
try {
- aeRange = convertRangeArg(args[0]);
-
- switch (args.length) {
- case 2:
- aeSum = aeRange;
- break;
- case 3:
- aeSum = createSumRange(args[2], aeRange);
- break;
- default:
- return ErrorEval.VALUE_INVALID;
- }
+ aeRange = convertRangeArg(arg0);
+ aeSum = createSumRange(arg2, aeRange);
} catch (EvaluationException e) {
return e.getErrorEval();
}
- I_MatchPredicate mp = Countif.createCriteriaPredicate(args[1], srcRowIndex, srcColumnIndex);
+ return eval(srcRowIndex, srcColumnIndex, arg1, aeRange, aeSum);
+ }
+
+ private static ValueEval eval(int srcRowIndex, int srcColumnIndex, ValueEval arg1, AreaEval aeRange,
+ AreaEval aeSum) {
+ // TODO - junit to prove last arg must be srcColumnIndex and not srcRowIndex
+ I_MatchPredicate mp = Countif.createCriteriaPredicate(arg1, srcRowIndex, srcColumnIndex);
double result = sumMatchingCells(aeRange, mp, aeSum);
return new NumberEval(result);
}
Modified: poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/TextFunction.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/TextFunction.java?rev=883041&r1=883040&r2=883041&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/TextFunction.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/TextFunction.java Sun Nov 22 06:27:55 2009
@@ -190,4 +190,72 @@
return BoolEval.valueOf(s0.equals(s1));
}
};
+
+ private static final class SearchFind extends Var2or3ArgFunction {
+
+ private final boolean _isCaseSensitive;
+
+ public SearchFind(boolean isCaseSensitive) {
+ _isCaseSensitive = isCaseSensitive;
+ }
+ public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) {
+ try {
+ String needle = TextFunction.evaluateStringArg(arg0, srcRowIndex, srcColumnIndex);
+ String haystack = TextFunction.evaluateStringArg(arg1, srcRowIndex, srcColumnIndex);
+ return eval(haystack, needle, 0);
+ } catch (EvaluationException e) {
+ return e.getErrorEval();
+ }
+ }
+ public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1,
+ ValueEval arg2) {
+ try {
+ String needle = TextFunction.evaluateStringArg(arg0, srcRowIndex, srcColumnIndex);
+ String haystack = TextFunction.evaluateStringArg(arg1, srcRowIndex, srcColumnIndex);
+ // evaluate third arg and convert from 1-based to 0-based index
+ int startpos = TextFunction.evaluateIntArg(arg2, srcRowIndex, srcColumnIndex) - 1;
+ if (startpos < 0) {
+ return ErrorEval.VALUE_INVALID;
+ }
+ return eval(haystack, needle, startpos);
+ } catch (EvaluationException e) {
+ return e.getErrorEval();
+ }
+ }
+ private ValueEval eval(String haystack, String needle, int startIndex) {
+ int result;
+ if (_isCaseSensitive) {
+ result = haystack.indexOf(needle, startIndex);
+ } else {
+ result = haystack.toUpperCase().indexOf(needle.toUpperCase(), startIndex);
+ }
+ if (result == -1) {
+ return ErrorEval.VALUE_INVALID;
+ }
+ return new NumberEval(result + 1);
+ }
+ }
+ /**
+ * Implementation of the FIND() function.<p/>
+ *
+ * <b>Syntax</b>:<br/>
+ * <b>FIND</b>(<b>find_text</b>, <b>within_text</b>, start_num)<p/>
+ *
+ * FIND returns the character position of the first (case sensitive) occurrence of
+ * <tt>find_text</tt> inside <tt>within_text</tt>. The third parameter,
+ * <tt>start_num</tt>, is optional (default=1) and specifies where to start searching
+ * from. Character positions are 1-based.<p/>
+ *
+ * @author Torstein Tauno Svendsen (torstei@officenet.no)
+ */
+ public static final Function FIND = new SearchFind(true);
+ /**
+ * Implementation of the FIND() function.<p/>
+ *
+ * <b>Syntax</b>:<br/>
+ * <b>SEARCH</b>(<b>find_text</b>, <b>within_text</b>, start_num)<p/>
+ *
+ * SEARCH is a case-insensitive version of FIND()
+ */
+ public static final Function SEARCH = new SearchFind(false);
}
Added: poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Var2or3ArgFunction.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Var2or3ArgFunction.java?rev=883041&view=auto
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Var2or3ArgFunction.java (added)
+++ poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Var2or3ArgFunction.java Sun Nov 22 06:27:55 2009
@@ -0,0 +1,46 @@
+/* ====================================================================
+ 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.ValueEval;
+
+/**
+ * Convenience base class for any function which must take two or three
+ * arguments
+ *
+ * @author Josh Micich
+ */
+abstract class Var2or3ArgFunction implements Function {
+
+ public final ValueEval evaluate(ValueEval[] args, int srcRowIndex, int srcColumnIndex) {
+ switch (args.length) {
+ case 2:
+ return evaluate(srcRowIndex, srcColumnIndex, args[0], args[1]);
+ case 3:
+ return evaluate(srcRowIndex, srcColumnIndex, args[0], args[1], args[2]);
+ }
+ return ErrorEval.VALUE_INVALID;
+ }
+
+ public abstract ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0,
+ ValueEval arg1, ValueEval arg2);
+
+ public abstract ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0,
+ ValueEval arg1);
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org