From a1d0ece524542f56e6df2909e9ee696b79869219 Mon Sep 17 00:00:00 2001 From: Josh Micich Date: Sun, 22 Nov 2009 06:27:55 +0000 Subject: [PATCH] refactored functions taking optionally 2 or 3 parameters git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@883041 13f79535-47bb-0310-9956-ffa450edef68 --- .../record/formula/eval/FunctionEval.java | 3 +- .../record/formula/functions/Days360.java | 48 ++++++++++--- .../hssf/record/formula/functions/Find.java | 64 ----------------- .../poi/hssf/record/formula/functions/If.java | 33 ++++----- .../hssf/record/formula/functions/Lookup.java | 26 +++---- .../hssf/record/formula/functions/Match.java | 43 ++++++------ .../hssf/record/formula/functions/Sumif.java | 39 ++++++----- .../formula/functions/TextFunction.java | 68 +++++++++++++++++++ .../formula/functions/Var2or3ArgFunction.java | 46 +++++++++++++ 9 files changed, 223 insertions(+), 147 deletions(-) delete mode 100644 src/java/org/apache/poi/hssf/record/formula/functions/Find.java create mode 100644 src/java/org/apache/poi/hssf/record/formula/functions/Var2or3ArgFunction.java diff --git a/src/java/org/apache/poi/hssf/record/formula/eval/FunctionEval.java b/src/java/org/apache/poi/hssf/record/formula/eval/FunctionEval.java index f5a941d677..cfe88369e4 100644 --- a/src/java/org/apache/poi/hssf/record/formula/eval/FunctionEval.java +++ b/src/java/org/apache/poi/hssf/record/formula/eval/FunctionEval.java @@ -109,6 +109,7 @@ public final class FunctionEval { 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 @@ public final class FunctionEval { retval[119] = new Replace(); retval[120] = new Substitute(); - retval[124] = new Find(); + retval[124] = TextFunction.FIND; retval[127] = LogicalFunction.IsText; retval[128] = LogicalFunction.IsNumber; diff --git a/src/java/org/apache/poi/hssf/record/formula/functions/Days360.java b/src/java/org/apache/poi/hssf/record/formula/functions/Days360.java index 5087348aab..9435b0754a 100644 --- a/src/java/org/apache/poi/hssf/record/formula/functions/Days360.java +++ b/src/java/org/apache/poi/hssf/record/formula/functions/Days360.java @@ -4,6 +4,9 @@ import java.util.Calendar; 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 @@ import org.apache.poi.ss.usermodel.DateUtil; * (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 @@ public class Days360 extends NumericFunction.TwoArg { 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 @@ public class Days360 extends NumericFunction.TwoArg { 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 @@ public class Days360 extends NumericFunction.TwoArg { 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 @@ public class Days360 extends NumericFunction.TwoArg { 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); diff --git a/src/java/org/apache/poi/hssf/record/formula/functions/Find.java b/src/java/org/apache/poi/hssf/record/formula/functions/Find.java deleted file mode 100644 index 0341eac328..0000000000 --- a/src/java/org/apache/poi/hssf/record/formula/functions/Find.java +++ /dev/null @@ -1,64 +0,0 @@ -/* ==================================================================== - Licensed to the Apache Software Foundation (ASF) under one or more - contributor license agreements. See the NOTICE file distributed with - this work for additional information regarding copyright ownership. - The ASF licenses this file to You under the Apache License, Version 2.0 - (the "License"); you may not use this file except in compliance with - the License. You may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0 - - Unless required by applicable law or agreed to in writing, software - distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the License for the specific language governing permissions and - limitations under the License. -==================================================================== */ - -package org.apache.poi.hssf.record.formula.functions; - -import org.apache.poi.hssf.record.formula.eval.ErrorEval; -import org.apache.poi.hssf.record.formula.eval.EvaluationException; -import org.apache.poi.hssf.record.formula.eval.NumberEval; -import org.apache.poi.hssf.record.formula.eval.ValueEval; - -/** - * Implementation of the FIND() function.

- * - * Syntax:
- * FIND(find_text, within_text, start_num)

- * - * FIND returns the character position of the first occurrence of find_text inside - * within_text. The third parameter, start_num, is optional (default=1) - * and specifies where to start searching from. Character positions are 1-based.

- * - * @author Torstein Tauno Svendsen (torstei@officenet.no) - */ -public final class Find extends TextFunction { - - protected ValueEval evaluateFunc(ValueEval[] args, int srcCellRow, int srcCellCol) - throws EvaluationException { - - int nArgs = args.length; - if (nArgs < 2 || nArgs > 3) { - return ErrorEval.VALUE_INVALID; - } - String needle = evaluateStringArg(args[0], srcCellRow, srcCellCol); - String haystack = evaluateStringArg(args[1], srcCellRow, srcCellCol); - int startpos; - if (nArgs == 3) { - startpos = evaluateIntArg(args[2], srcCellRow, srcCellCol); - if (startpos <= 0) { - return ErrorEval.VALUE_INVALID; - } - startpos--; // convert 1-based to zero based - } else { - startpos = 0; - } - int result = haystack.indexOf(needle, startpos); - if (result == -1) { - return ErrorEval.VALUE_INVALID; - } - return new NumberEval(result + 1); - } -} diff --git a/src/java/org/apache/poi/hssf/record/formula/functions/If.java b/src/java/org/apache/poi/hssf/record/formula/functions/If.java index 1cddb23d84..3f74019844 100644 --- a/src/java/org/apache/poi/hssf/record/formula/functions/If.java +++ b/src/java/org/apache/poi/hssf/record/formula/functions/If.java @@ -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) diff --git a/src/java/org/apache/poi/hssf/record/formula/functions/Lookup.java b/src/java/org/apache/poi/hssf/record/formula/functions/Lookup.java index fd90f1da4a..6a2bafe7e6 100644 --- a/src/java/org/apache/poi/hssf/record/formula/functions/Lookup.java +++ b/src/java/org/apache/poi/hssf/record/formula/functions/Lookup.java @@ -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 @@ import org.apache.poi.hssf.record.formula.functions.LookupUtils.ValueVector; * * @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); diff --git a/src/java/org/apache/poi/hssf/record/formula/functions/Match.java b/src/java/org/apache/poi/hssf/record/formula/functions/Match.java index 139b794caa..c2208196fa 100644 --- a/src/java/org/apache/poi/hssf/record/formula/functions/Match.java +++ b/src/java/org/apache/poi/hssf/record/formula/functions/Match.java @@ -63,37 +63,40 @@ import org.apache.poi.hssf.record.formula.functions.LookupUtils.ValueVector; * * @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) { diff --git a/src/java/org/apache/poi/hssf/record/formula/functions/Sumif.java b/src/java/org/apache/poi/hssf/record/formula/functions/Sumif.java index 224e052921..8bc36ffe24 100644 --- a/src/java/org/apache/poi/hssf/record/formula/functions/Sumif.java +++ b/src/java/org/apache/poi/hssf/record/formula/functions/Sumif.java @@ -38,32 +38,37 @@ import org.apache.poi.hssf.record.formula.functions.CountUtils.I_MatchPredicate; *

* @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); } diff --git a/src/java/org/apache/poi/hssf/record/formula/functions/TextFunction.java b/src/java/org/apache/poi/hssf/record/formula/functions/TextFunction.java index 360b8db768..e624259866 100644 --- a/src/java/org/apache/poi/hssf/record/formula/functions/TextFunction.java +++ b/src/java/org/apache/poi/hssf/record/formula/functions/TextFunction.java @@ -190,4 +190,72 @@ public abstract class TextFunction implements Function { 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.

+ * + * Syntax:
+ * FIND(find_text, within_text, start_num)

+ * + * FIND returns the character position of the first (case sensitive) occurrence of + * find_text inside within_text. The third parameter, + * start_num, is optional (default=1) and specifies where to start searching + * from. Character positions are 1-based.

+ * + * @author Torstein Tauno Svendsen (torstei@officenet.no) + */ + public static final Function FIND = new SearchFind(true); + /** + * Implementation of the FIND() function.

+ * + * Syntax:
+ * SEARCH(find_text, within_text, start_num)

+ * + * SEARCH is a case-insensitive version of FIND() + */ + public static final Function SEARCH = new SearchFind(false); } diff --git a/src/java/org/apache/poi/hssf/record/formula/functions/Var2or3ArgFunction.java b/src/java/org/apache/poi/hssf/record/formula/functions/Var2or3ArgFunction.java new file mode 100644 index 0000000000..9f5483fa05 --- /dev/null +++ b/src/java/org/apache/poi/hssf/record/formula/functions/Var2or3ArgFunction.java @@ -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); +} -- 2.39.5