You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by jo...@apache.org on 2008/09/13 07:14:27 UTC
svn commit: r694877 - in /poi/trunk/src:
java/org/apache/poi/hssf/record/formula/eval/
java/org/apache/poi/hssf/record/formula/functions/
testcases/org/apache/poi/hssf/data/
testcases/org/apache/poi/hssf/record/formula/functions/
Author: josh
Date: Fri Sep 12 22:14:26 2008
New Revision: 694877
URL: http://svn.apache.org/viewvc?rev=694877&view=rev
Log:
Refactored TextFunctions. Some minor fixes - test cases added.
Removed:
poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Concatenate.java
poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Exact.java
poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Left.java
poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Len.java
poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Lower.java
poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Mid.java
poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Right.java
poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Trim.java
poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Upper.java
Modified:
poi/trunk/src/java/org/apache/poi/hssf/record/formula/eval/ConcatEval.java
poi/trunk/src/java/org/apache/poi/hssf/record/formula/eval/FunctionEval.java
poi/trunk/src/java/org/apache/poi/hssf/record/formula/eval/OperandResolver.java
poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Replace.java
poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Substitute.java
poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/TextFunction.java
poi/trunk/src/testcases/org/apache/poi/hssf/data/FormulaEvalTestData.xls
poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/functions/TestLen.java
poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/functions/TestMid.java
poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/functions/TestTrim.java
Modified: poi/trunk/src/java/org/apache/poi/hssf/record/formula/eval/ConcatEval.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/record/formula/eval/ConcatEval.java?rev=694877&r1=694876&r2=694877&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/record/formula/eval/ConcatEval.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/record/formula/eval/ConcatEval.java Fri Sep 12 22:14:26 2008
@@ -44,7 +44,7 @@
if (ve instanceof StringValueEval) {
StringValueEval sve = (StringValueEval) ve;
sb.append(sve.getStringValue());
- } else if (ve instanceof BlankEval) {
+ } else if (ve == BlankEval.INSTANCE) {
// do nothing
} else { // must be an error eval
throw new RuntimeException("Unexpected value type ("
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=694877&r1=694876&r2=694877&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 Fri Sep 12 22:14:26 2008
@@ -105,8 +105,8 @@
retval[28] = new Lookup(); // LOOKUP
retval[29] = new Index(); // INDEX
retval[30] = new Rept(); // REPT
- retval[31] = new Mid(); // MID
- retval[32] = new Len(); // LEN
+ retval[31] = TextFunction.MID;
+ retval[32] = TextFunction.LEN;
retval[33] = new Value(); // VALUE
retval[34] = new True(); // TRUE
retval[35] = new False(); // FALSE
@@ -185,13 +185,13 @@
retval[109] = NumericFunction.LOG;
retval[110] = new Exec(); // EXEC
retval[111] = new Char(); // CHAR
- retval[112] = new Lower(); // LOWER
- retval[113] = new Upper(); // UPPER
+ retval[112] = TextFunction.LOWER;
+ retval[113] = TextFunction.UPPER;
retval[114] = new Proper(); // PROPER
- retval[115] = new Left(); // LEFT
- retval[116] = new Right(); // RIGHT
- retval[117] = new Exact(); // EXACT
- retval[118] = new Trim(); // TRIM
+ retval[115] = TextFunction.LEFT;
+ retval[116] = TextFunction.RIGHT;
+ retval[117] = TextFunction.EXACT;
+ retval[118] = TextFunction.TRIM;
retval[119] = new Replace(); // REPLACE
retval[120] = new Substitute(); // SUBSTITUTE
retval[121] = new Code(); // CODE
@@ -394,7 +394,7 @@
retval[332] = new Tinv(); // TINV
retval[334] = new NotImplementedFunction(); // MOVIECOMMAND
retval[335] = new NotImplementedFunction(); // GETMOVIE
- retval[336] = new Concatenate(); // CONCATENATE
+ retval[336] = TextFunction.CONCATENATE;
retval[337] = NumericFunction.POWER;
retval[338] = new NotImplementedFunction(); // PIVOTADDDATA
retval[339] = new NotImplementedFunction(); // GETPIVOTTABLE
Modified: poi/trunk/src/java/org/apache/poi/hssf/record/formula/eval/OperandResolver.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/record/formula/eval/OperandResolver.java?rev=694877&r1=694876&r2=694877&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/record/formula/eval/OperandResolver.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/record/formula/eval/OperandResolver.java Fri Sep 12 22:14:26 2008
@@ -272,12 +272,7 @@
StringValueEval sve = (StringValueEval) ve;
return sve.getStringValue();
}
- if (ve instanceof NumberEval) {
- NumberEval neval = (NumberEval) ve;
- return neval.getStringValue();
- }
-
- if (ve instanceof BlankEval) {
+ if (ve == BlankEval.INSTANCE) {
return "";
}
throw new IllegalArgumentException("Unexpected eval class (" + ve.getClass().getName() + ")");
@@ -289,7 +284,7 @@
*/
public static Boolean coerceValueToBoolean(ValueEval ve, boolean stringsAreBlanks) throws EvaluationException {
- if (ve == null || ve instanceof BlankEval) {
+ if (ve == null || ve == BlankEval.INSTANCE) {
// TODO - remove 've == null' condition once AreaEval is fixed
return null;
}
@@ -297,7 +292,7 @@
return Boolean.valueOf(((BoolEval) ve).getBooleanValue());
}
- if (ve instanceof BlankEval) {
+ if (ve == BlankEval.INSTANCE) {
return null;
}
Modified: poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Replace.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Replace.java?rev=694877&r1=694876&r2=694877&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Replace.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Replace.java Fri Sep 12 22:14:26 2008
@@ -1,112 +1,70 @@
-/*
-* Licensed to the Apache Software Foundation (ASF) under one or more
-* contributor license agreements. See the NOTICE file distributed with
-* this work for additional information regarding copyright ownership.
-* The ASF licenses this file to You under the Apache License, Version 2.0
-* (the "License"); you may not use this file except in compliance with
-* the License. You may obtain a copy of the License at
-*
-* http://www.apache.org/licenses/LICENSE-2.0
-*
-* Unless required by applicable law or agreed to in writing, software
-* distributed under the License is distributed on an "AS IS" BASIS,
-* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-* See the License for the specific language governing permissions and
-* limitations under the License.
-*/
-/*
- * Created on May 15, 2005
- *
- */
+/* ====================================================================
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+==================================================================== */
+
package org.apache.poi.hssf.record.formula.functions;
import org.apache.poi.hssf.record.formula.eval.ErrorEval;
import org.apache.poi.hssf.record.formula.eval.Eval;
-import org.apache.poi.hssf.record.formula.eval.NumericValueEval;
+import org.apache.poi.hssf.record.formula.eval.EvaluationException;
import org.apache.poi.hssf.record.formula.eval.StringEval;
-import org.apache.poi.hssf.record.formula.eval.StringValueEval;
import org.apache.poi.hssf.record.formula.eval.ValueEval;
/**
- * An implementation of the REPLACE function:
- * Replaces part of a text string based on the number of characters
- * you specify, with another text string.
+ * An implementation of the Excel REPLACE() function<p/>:
+ * Replaces part of a text string based on the number of characters
+ * you specify, with another text string.<br/>
+ *
+ * <b>Syntax</b>:<br/>
+ * <b>REPLACE</b>(<b>oldText</b>, <b>startNum</b>, <b>numChars</b>, <b>newText</b>)<p/>
+ *
+ * <b>oldText</b> The text string containing characters to replace<br/>
+ * <b>startNum</b> The position of the first character to replace (1-based)<br/>
+ * <b>numChars</b> The number of characters to replace<br/>
+ * <b>newText</b> The new text value to replace the removed section<br/>
+ *
* @author Manda Wilson < wilson at c bio dot msk cc dot org >
*/
-public class Replace extends TextFunction {
+public final class Replace extends TextFunction {
+
+ protected ValueEval evaluateFunc(Eval[] args, int srcCellRow, short srcCellCol)
+ throws EvaluationException {
+ if (args.length != 4) {
+ return ErrorEval.VALUE_INVALID;
+ }
- /**
- * Replaces part of a text string based on the number of characters
- * you specify, with another text string.
- *
- * @see org.apache.poi.hssf.record.formula.eval.Eval
- */
- public Eval evaluate(Eval[] operands, int srcCellRow, short srcCellCol) {
- Eval retval = null;
- String oldStr = null;
- String newStr = null;
- int startNum = 0;
- int numChars = 0;
-
- switch (operands.length) {
- default:
- retval = ErrorEval.VALUE_INVALID;
- case 4:
- // first operand is text string containing characters to replace
- // second operand is position of first character to replace
- // third operand is the number of characters in the old string
- // you want to replace with new string
- // fourth operand is the new string
- ValueEval firstveval = singleOperandEvaluate(operands[0], srcCellRow, srcCellCol);
- ValueEval secondveval = singleOperandEvaluate(operands[1], srcCellRow, srcCellCol);
- ValueEval thirdveval = singleOperandEvaluate(operands[2], srcCellRow, srcCellCol);
- ValueEval fourthveval = singleOperandEvaluate(operands[3], srcCellRow, srcCellCol);
- if (firstveval instanceof StringValueEval
- && secondveval instanceof NumericValueEval
- && thirdveval instanceof NumericValueEval
- && fourthveval instanceof StringValueEval) {
-
- StringValueEval oldStrEval = (StringValueEval) firstveval;
- oldStr = oldStrEval.getStringValue();
-
- NumericValueEval startNumEval = (NumericValueEval) secondveval;
- // NOTE: it is safe to cast to int here
- // because in Excel =REPLACE("task", 2.7, 3, "est")
- // returns test
- // so 2.7 must be truncated to 2
- // and =REPLACE("task", 1, 1.9, "") returns ask
- // so 1.9 must be truncated to 1
- startNum = (int) startNumEval.getNumberValue();
-
- NumericValueEval numCharsEval = (NumericValueEval) thirdveval;
- numChars = (int) numCharsEval.getNumberValue();
-
- StringValueEval newStrEval = (StringValueEval) fourthveval;
- newStr = newStrEval.getStringValue();
- } else {
- retval = ErrorEval.VALUE_INVALID;
- }
- }
-
- if (retval == null) {
- if (startNum < 1 || numChars < 0) {
- retval = ErrorEval.VALUE_INVALID;
- } else {
- StringBuffer strBuff = new StringBuffer(oldStr);
- // remove any characters that should be replaced
- if (startNum <= oldStr.length() && numChars != 0) {
- strBuff.delete(startNum - 1, startNum - 1 + numChars);
- }
- // now insert (or append) newStr
- if (startNum > strBuff.length()) {
- strBuff.append(newStr);
- } else {
- strBuff.insert(startNum - 1, newStr);
- }
- retval = new StringEval(strBuff.toString());
- }
- }
- return retval;
- }
+ String oldStr = evaluateStringArg(args[0], srcCellRow, srcCellCol);
+ int startNum = evaluateIntArg(args[1], srcCellRow, srcCellCol);
+ int numChars = evaluateIntArg(args[2], srcCellRow, srcCellCol);
+ String newStr = evaluateStringArg(args[3], srcCellRow, srcCellCol);
+ if (startNum < 1 || numChars < 0) {
+ return ErrorEval.VALUE_INVALID;
+ }
+ StringBuffer strBuff = new StringBuffer(oldStr);
+ // remove any characters that should be replaced
+ if (startNum <= oldStr.length() && numChars != 0) {
+ strBuff.delete(startNum - 1, startNum - 1 + numChars);
+ }
+ // now insert (or append) newStr
+ if (startNum > strBuff.length()) {
+ strBuff.append(newStr);
+ } else {
+ strBuff.insert(startNum - 1, newStr);
+ }
+ return new StringEval(strBuff.toString());
+ }
}
Modified: poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Substitute.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Substitute.java?rev=694877&r1=694876&r2=694877&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Substitute.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Substitute.java Fri Sep 12 22:14:26 2008
@@ -1,30 +1,26 @@
-/*
-* Licensed to the Apache Software Foundation (ASF) under one or more
-* contributor license agreements. See the NOTICE file distributed with
-* this work for additional information regarding copyright ownership.
-* The ASF licenses this file to You under the Apache License, Version 2.0
-* (the "License"); you may not use this file except in compliance with
-* the License. You may obtain a copy of the License at
-*
-* http://www.apache.org/licenses/LICENSE-2.0
-*
-* Unless required by applicable law or agreed to in writing, software
-* distributed under the License is distributed on an "AS IS" BASIS,
-* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-* See the License for the specific language governing permissions and
-* limitations under the License.
-*/
-/*
- * Created on May 15, 2005
- *
- */
+/* ====================================================================
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+==================================================================== */
+
package org.apache.poi.hssf.record.formula.functions;
import org.apache.poi.hssf.record.formula.eval.ErrorEval;
import org.apache.poi.hssf.record.formula.eval.Eval;
-import org.apache.poi.hssf.record.formula.eval.NumericValueEval;
+import org.apache.poi.hssf.record.formula.eval.EvaluationException;
import org.apache.poi.hssf.record.formula.eval.StringEval;
-import org.apache.poi.hssf.record.formula.eval.StringValueEval;
import org.apache.poi.hssf.record.formula.eval.ValueEval;
/**
@@ -32,86 +28,75 @@
* Substitutes text in a text string with new text, some number of times.
* @author Manda Wilson < wilson at c bio dot msk cc dot org >
*/
-public class Substitute extends TextFunction {
- private static final int REPLACE_ALL = -1;
-
- /**
- *Substitutes text in a text string with new text, some number of times.
- *
- * @see org.apache.poi.hssf.record.formula.eval.Eval
- */
- public Eval evaluate(Eval[] operands, int srcCellRow, short srcCellCol) {
- Eval retval = null;
- String oldStr = null;
- String searchStr = null;
- String newStr = null;
- int numToReplace = REPLACE_ALL;
-
- switch (operands.length) {
- default:
- retval = ErrorEval.VALUE_INVALID;
- case 4:
- ValueEval fourthveval = singleOperandEvaluate(operands[3], srcCellRow, srcCellCol);
- if (fourthveval instanceof NumericValueEval) {
- NumericValueEval numToReplaceEval = (NumericValueEval) fourthveval;
- // NOTE: it is safe to cast to int here
- // because in Excel =SUBSTITUTE("teststr","t","T",1.9)
- // returns Teststr
- // so 1.9 must be truncated to 1
- numToReplace = (int) numToReplaceEval.getNumberValue();
- } else {
- retval = ErrorEval.VALUE_INVALID;
- }
- case 3:
- // first operand is text string containing characters to replace
- // second operand is text to find
- // third operand is replacement text
- ValueEval firstveval = singleOperandEvaluate(operands[0], srcCellRow, srcCellCol);
- ValueEval secondveval = singleOperandEvaluate(operands[1], srcCellRow, srcCellCol);
- ValueEval thirdveval = singleOperandEvaluate(operands[2], srcCellRow, srcCellCol);
- if (firstveval instanceof StringValueEval
- && secondveval instanceof StringValueEval
- && thirdveval instanceof StringValueEval) {
-
- StringValueEval oldStrEval = (StringValueEval) firstveval;
- oldStr = oldStrEval.getStringValue();
-
- StringValueEval searchStrEval = (StringValueEval) secondveval;
- searchStr = searchStrEval.getStringValue();
-
- StringValueEval newStrEval = (StringValueEval) thirdveval;
- newStr = newStrEval.getStringValue();
- } else {
- retval = ErrorEval.VALUE_INVALID;
- }
- }
-
- if (retval == null) {
- if (numToReplace != REPLACE_ALL && numToReplace < 1) {
- retval = ErrorEval.VALUE_INVALID;
- } else if (searchStr.length() == 0) {
- retval = new StringEval(oldStr);
- } else {
- StringBuffer strBuff = new StringBuffer();
- int startIndex = 0;
- int nextMatch = -1;
- for (int leftToReplace = numToReplace;
- (leftToReplace > 0 || numToReplace == REPLACE_ALL)
- && (nextMatch = oldStr.indexOf(searchStr, startIndex)) != -1;
- leftToReplace--) {
- // store everything from end of last match to start of this match
- strBuff.append(oldStr.substring(startIndex, nextMatch));
- strBuff.append(newStr);
- startIndex = nextMatch + searchStr.length();
+public final class Substitute extends TextFunction {
+
+ protected ValueEval evaluateFunc(Eval[] args, int srcCellRow, short srcCellCol)
+ throws EvaluationException {
+ if (args.length < 3 || args.length > 4) {
+ return ErrorEval.VALUE_INVALID;
+ }
+
+ String oldStr = evaluateStringArg(args[0], srcCellRow, srcCellCol);
+ String searchStr = evaluateStringArg(args[1], srcCellRow, srcCellCol);
+ String newStr = evaluateStringArg(args[2], srcCellRow, srcCellCol);
+
+ String result;
+ switch (args.length) {
+ default:
+ throw new IllegalStateException("Cannot happen");
+ case 4:
+ int instanceNumber = evaluateIntArg(args[3], srcCellRow, srcCellCol);
+ if (instanceNumber < 1) {
+ return ErrorEval.VALUE_INVALID;
}
+ result = replaceOneOccurrence(oldStr, searchStr, newStr, instanceNumber);
+ break;
+ case 3:
+ result = replaceAllOccurrences(oldStr, searchStr, newStr);
+ }
+ return new StringEval(result);
+ }
+
+ private static String replaceAllOccurrences(String oldStr, String searchStr, String newStr) {
+ StringBuffer sb = new StringBuffer();
+ int startIndex = 0;
+ int nextMatch = -1;
+ while (true) {
+ nextMatch = oldStr.indexOf(searchStr, startIndex);
+ if (nextMatch < 0) {
// store everything from end of last match to end of string
- if (startIndex < oldStr.length()) {
- strBuff.append(oldStr.substring(startIndex));
- }
- retval = new StringEval(strBuff.toString());
+ sb.append(oldStr.substring(startIndex));
+ return sb.toString();
+ }
+ // store everything from end of last match to start of this match
+ sb.append(oldStr.substring(startIndex, nextMatch));
+ sb.append(newStr);
+ startIndex = nextMatch + searchStr.length();
+ }
+ }
+
+ private static String replaceOneOccurrence(String oldStr, String searchStr, String newStr, int instanceNumber) {
+ if (searchStr.length() < 1) {
+ return oldStr;
+ }
+ int startIndex = 0;
+ int nextMatch = -1;
+ int count=0;
+ while (true) {
+ nextMatch = oldStr.indexOf(searchStr, startIndex);
+ if (nextMatch < 0) {
+ // not enough occurrences found - leave unchanged
+ return oldStr;
+ }
+ count++;
+ if (count == instanceNumber) {
+ StringBuffer sb = new StringBuffer(oldStr.length() + newStr.length());
+ sb.append(oldStr.substring(0, nextMatch));
+ sb.append(newStr);
+ sb.append(oldStr.substring(nextMatch + searchStr.length()));
+ return sb.toString();
}
- }
- return retval;
- }
-
+ startIndex = nextMatch + searchStr.length();
+ }
+ }
}
Modified: poi/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=694877&r1=694876&r2=694877&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 Fri Sep 12 22:14:26 2008
@@ -1,31 +1,29 @@
-/*
-* Licensed to the Apache Software Foundation (ASF) under one or more
-* contributor license agreements. See the NOTICE file distributed with
-* this work for additional information regarding copyright ownership.
-* The ASF licenses this file to You under the Apache License, Version 2.0
-* (the "License"); you may not use this file except in compliance with
-* the License. You may obtain a copy of the License at
-*
-* http://www.apache.org/licenses/LICENSE-2.0
-*
-* Unless required by applicable law or agreed to in writing, software
-* distributed under the License is distributed on an "AS IS" BASIS,
-* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-* See the License for the specific language governing permissions and
-* limitations under the License.
-*/
-/*
- * Created on May 22, 2005
- *
- */
+/* ====================================================================
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+==================================================================== */
+
package org.apache.poi.hssf.record.formula.functions;
-import org.apache.poi.hssf.record.formula.eval.AreaEval;
-import org.apache.poi.hssf.record.formula.eval.BlankEval;
+import org.apache.poi.hssf.record.formula.eval.BoolEval;
import org.apache.poi.hssf.record.formula.eval.ErrorEval;
import org.apache.poi.hssf.record.formula.eval.Eval;
-import org.apache.poi.hssf.record.formula.eval.RefEval;
-import org.apache.poi.hssf.record.formula.eval.StringValueEval;
+import org.apache.poi.hssf.record.formula.eval.EvaluationException;
+import org.apache.poi.hssf.record.formula.eval.NumberEval;
+import org.apache.poi.hssf.record.formula.eval.OperandResolver;
+import org.apache.poi.hssf.record.formula.eval.StringEval;
import org.apache.poi.hssf.record.formula.eval.ValueEval;
/**
@@ -33,75 +31,164 @@
*
*/
public abstract class TextFunction implements Function {
-
- protected static final String EMPTY_STRING = "";
-
- protected ValueEval singleOperandEvaluate(Eval eval, int srcRow, short srcCol) {
- ValueEval retval;
- if (eval instanceof AreaEval) {
- AreaEval ae = (AreaEval) eval;
- if (ae.contains(srcRow, srcCol)) { // circular ref!
- retval = ErrorEval.CIRCULAR_REF_ERROR;
- }
- else if (ae.isRow()) {
- if (ae.containsColumn(srcCol)) {
- ValueEval ve = ae.getValueAt(ae.getFirstRow(), srcCol);
- retval = attemptXlateToText(ve);
- }
- else {
- retval = ErrorEval.VALUE_INVALID;
- }
- }
- else if (ae.isColumn()) {
- if (ae.containsRow(srcRow)) {
- ValueEval ve = ae.getValueAt(srcRow, ae.getFirstColumn());
- retval = attemptXlateToText(ve);
- }
- else {
- retval = ErrorEval.VALUE_INVALID;
- }
- }
- else {
- retval = ErrorEval.VALUE_INVALID;
- }
- }
- else {
- retval = attemptXlateToText((ValueEval) eval);
- }
- return retval;
- }
-
-
- /**
- * converts from Different ValueEval types to StringEval.
- * Note: AreaEvals are not handled, if arg is an AreaEval,
- * the returned value is ErrorEval.VALUE_INVALID
- * @param ve
- */
- protected ValueEval attemptXlateToText(ValueEval ve) {
- ValueEval retval;
- if (ve instanceof StringValueEval) {
- retval = ve;
- }
- else if (ve instanceof RefEval) {
- RefEval re = (RefEval) ve;
- ValueEval ive = re.getInnerValueEval();
- if (ive instanceof StringValueEval) {
- retval = ive;
- }
- else if (ive instanceof BlankEval) {
- retval = ive;
- }
- else {
- retval = ErrorEval.VALUE_INVALID;
- }
- }
- else if (ve instanceof BlankEval) {
- retval = ve;
- }
- else {
- retval = ErrorEval.VALUE_INVALID;
- }
- return retval;
- }
+
+ protected static final String EMPTY_STRING = "";
+
+ protected static final String evaluateStringArg(Eval eval, int srcRow, short srcCol) throws EvaluationException {
+ ValueEval ve = OperandResolver.getSingleValue(eval, srcRow, srcCol);
+ return OperandResolver.coerceValueToString(ve);
+ }
+ protected static final int evaluateIntArg(Eval arg, int srcCellRow, short srcCellCol) throws EvaluationException {
+ ValueEval ve = OperandResolver.getSingleValue(arg, srcCellRow, srcCellCol);
+ return OperandResolver.coerceValueToInt(ve);
+ }
+
+ public final Eval evaluate(Eval[] args, int srcCellRow, short srcCellCol) {
+ try {
+ return evaluateFunc(args, srcCellRow, srcCellCol);
+ } catch (EvaluationException e) {
+ return e.getErrorEval();
+ }
+ }
+
+ protected abstract ValueEval evaluateFunc(Eval[] args, int srcCellRow, short srcCellCol) throws EvaluationException;
+
+ /* ---------------------------------------------------------------------- */
+
+ private static abstract class SingleArgTextFunc extends TextFunction {
+
+ protected SingleArgTextFunc() {
+ // no fields to initialise
+ }
+ protected ValueEval evaluateFunc(Eval[] args, int srcCellRow, short srcCellCol)
+ throws EvaluationException {
+ if (args.length != 1) {
+ return ErrorEval.VALUE_INVALID;
+ }
+ String arg = evaluateStringArg(args[0], srcCellRow, srcCellCol);
+ return evaluate(arg);
+ }
+ protected abstract ValueEval evaluate(String arg);
+ }
+
+ public static final Function LEN = new SingleArgTextFunc() {
+ protected ValueEval evaluate(String arg) {
+ return new NumberEval(arg.length());
+ }
+ };
+ public static final Function LOWER = new SingleArgTextFunc() {
+ protected ValueEval evaluate(String arg) {
+ return new StringEval(arg.toLowerCase());
+ }
+ };
+ public static final Function UPPER = new SingleArgTextFunc() {
+ protected ValueEval evaluate(String arg) {
+ return new StringEval(arg.toUpperCase());
+ }
+ };
+ /**
+ * An implementation of the TRIM function:
+ * Removes leading and trailing spaces from value if evaluated operand
+ * value is string.
+ * @author Manda Wilson < wilson at c bio dot msk cc dot org >
+ */
+ public static final Function TRIM = new SingleArgTextFunc() {
+ protected ValueEval evaluate(String arg) {
+ return new StringEval(arg.trim());
+ }
+ };
+
+ /**
+ * An implementation of the MID function<br/>
+ * MID returns a specific number of
+ * characters from a text string, starting at the specified position.<p/>
+ *
+ * <b>Syntax<b>:<br/> <b>MID</b>(<b>text</b>, <b>start_num</b>,
+ * <b>num_chars</b>)<br/>
+ *
+ * @author Manda Wilson < wilson at c bio dot msk cc dot org >
+ */
+ public static final Function MID = new TextFunction() {
+
+ protected ValueEval evaluateFunc(Eval[] args, int srcCellRow, short srcCellCol)
+ throws EvaluationException {
+ if (args.length != 3) {
+ return ErrorEval.VALUE_INVALID;
+ }
+
+ String text = evaluateStringArg(args[0], srcCellRow, srcCellCol);
+ int startCharNum = evaluateIntArg(args[1], srcCellRow, srcCellCol);
+ int numChars = evaluateIntArg(args[2], srcCellRow, srcCellCol);
+ int startIx = startCharNum - 1; // convert to zero-based
+
+ // Note - for start_num arg, blank/zero causes error(#VALUE!),
+ // but for num_chars causes empty string to be returned.
+ if (startIx < 0) {
+ return ErrorEval.VALUE_INVALID;
+ }
+ if (numChars < 0) {
+ return ErrorEval.VALUE_INVALID;
+ }
+ int len = text.length();
+ if (numChars < 0 || startIx > len) {
+ return new StringEval("");
+ }
+ int endIx = Math.min(startIx + numChars, len);
+ String result = text.substring(startIx, endIx);
+ return new StringEval(result);
+ }
+ };
+
+ private static final class LeftRight extends TextFunction {
+
+ private final boolean _isLeft;
+ protected LeftRight(boolean isLeft) {
+ _isLeft = isLeft;
+ }
+ protected ValueEval evaluateFunc(Eval[] args, int srcCellRow, short srcCellCol)
+ throws EvaluationException {
+ if (args.length != 2) {
+ return ErrorEval.VALUE_INVALID;
+ }
+ String arg = evaluateStringArg(args[0], srcCellRow, srcCellCol);
+ int index = evaluateIntArg(args[1], srcCellRow, srcCellCol);
+
+ String result;
+ if (_isLeft) {
+ result = arg.substring(0, Math.min(arg.length(), index));
+ } else {
+ result = arg.substring(Math.max(0, arg.length()-index));
+ }
+ return new StringEval(result);
+ }
+ }
+
+ public static final Function LEFT = new LeftRight(true);
+ public static final Function RIGHT = new LeftRight(false);
+
+ public static final Function CONCATENATE = new TextFunction() {
+
+ protected ValueEval evaluateFunc(Eval[] args, int srcCellRow, short srcCellCol)
+ throws EvaluationException {
+ StringBuffer sb = new StringBuffer();
+ for (int i=0, iSize=args.length; i<iSize; i++) {
+ sb.append(evaluateStringArg(args[i], srcCellRow, srcCellCol));
+ }
+ return new StringEval(sb.toString());
+ }
+ };
+
+ public static final Function EXACT = new TextFunction() {
+
+ protected ValueEval evaluateFunc(Eval[] args, int srcCellRow, short srcCellCol)
+ throws EvaluationException {
+ if (args.length != 2) {
+ return ErrorEval.VALUE_INVALID;
+ }
+
+ String s0 = evaluateStringArg(args[0], srcCellRow, srcCellCol);
+ String s1 = evaluateStringArg(args[1], srcCellRow, srcCellCol);
+ return BoolEval.valueOf(s0.equals(s1));
+ }
+ };
}
Modified: poi/trunk/src/testcases/org/apache/poi/hssf/data/FormulaEvalTestData.xls
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/data/FormulaEvalTestData.xls?rev=694877&r1=694876&r2=694877&view=diff
==============================================================================
Binary files - no diff available.
Modified: poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/functions/TestLen.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/functions/TestLen.java?rev=694877&r1=694876&r2=694877&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/functions/TestLen.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/functions/TestLen.java Fri Sep 12 22:14:26 2008
@@ -35,7 +35,7 @@
private static Eval invokeLen(Eval text) {
Eval[] args = new Eval[] { text, };
- return new Len().evaluate(args, -1, (short)-1);
+ return TextFunction.LEN.evaluate(args, -1, (short)-1);
}
private void confirmLen(Eval text, int expected) {
Modified: poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/functions/TestMid.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/functions/TestMid.java?rev=694877&r1=694876&r2=694877&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/functions/TestMid.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/functions/TestMid.java Fri Sep 12 22:14:26 2008
@@ -38,7 +38,7 @@
private static Eval invokeMid(Eval text, Eval startPos, Eval numChars) {
Eval[] args = new Eval[] { text, startPos, numChars, };
- return new Mid().evaluate(args, -1, (short)-1);
+ return TextFunction.MID.evaluate(args, -1, (short)-1);
}
private void confirmMid(Eval text, Eval startPos, Eval numChars, String expected) {
Modified: poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/functions/TestTrim.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/functions/TestTrim.java?rev=694877&r1=694876&r2=694877&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/functions/TestTrim.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/functions/TestTrim.java Fri Sep 12 22:14:26 2008
@@ -35,7 +35,7 @@
private static Eval invokeTrim(Eval text) {
Eval[] args = new Eval[] { text, };
- return new Trim().evaluate(args, -1, (short)-1);
+ return TextFunction.TRIM.evaluate(args, -1, (short)-1);
}
private void confirmTrim(Eval text, String expected) {
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org