Index: src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Mid.java
===================================================================
--- src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Mid.java (revision 628181)
+++ src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Mid.java (working copy)
@@ -1,99 +1,100 @@
/*
-* 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.BlankEval;
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.OperandResolver;
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 MID function:
- * Returns a specific number of characters from a text string,
- * starting at the position you specify, based on the number
- * of characters you specify.
+ * An implementation of the MID function
MID returns a specific number of
+ * characters from a text string, starting at the specified position.
+ *
+ * Syntax:
MID(text, start_num,
+ * num_chars)
+ *
* @author Manda Wilson < wilson at c bio dot msk cc dot org >
*/
public class Mid extends TextFunction {
/**
- * Returns a specific number of characters from a text string,
- * starting at the position you specify, based on the number
- * of characters you specify.
+ * Returns a specific number of characters from a text string, starting at
+ * the position you specify, based on the number of characters you specify.
*
* @see org.apache.poi.hssf.record.formula.eval.Eval
*/
- public Eval evaluate(Eval[] operands, int srcCellRow, short srcCellCol) {
- Eval retval = null;
- String str = null;
- int startNum = 0;
- int numChars = 0;
-
- switch (operands.length) {
- default:
- retval = ErrorEval.VALUE_INVALID;
- case 3:
- // first operand is text string containing characters to extract
- // second operand is position of first character to extract
- // third operand is the number of characters to return
- 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 NumericValueEval
- && thirdveval instanceof NumericValueEval) {
-
- StringValueEval strEval = (StringValueEval) firstveval;
- str = strEval.getStringValue();
-
- NumericValueEval startNumEval = (NumericValueEval) secondveval;
- // NOTE: it is safe to cast to int here
- // because in Excel =MID("test", 1, 1.7) returns t
- // so 1.7 must be truncated to 1
- // and =MID("test", 1.9, 2) returns te
- // so 1.9 must be truncated to 1
- startNum = (int) startNumEval.getNumberValue();
-
- NumericValueEval numCharsEval = (NumericValueEval) thirdveval;
- numChars = (int) numCharsEval.getNumberValue();
-
- } else {
- retval = ErrorEval.VALUE_INVALID;
- }
- }
-
- if (retval == null) {
- if (startNum < 1 || numChars < 0) {
- retval = ErrorEval.VALUE_INVALID;
- } else if (startNum > str.length() || numChars == 0) {
- retval = BlankEval.INSTANCE;
- } else if (startNum + numChars > str.length()) {
- retval = new StringEval(str.substring(startNum - 1));
- } else {
- retval = new StringEval(str.substring(startNum - 1, (numChars + startNum - 1)));
- }
- }
- return retval;
- }
+ public Eval evaluate(Eval[] args, int srcCellRow, short srcCellCol) {
+ if (args.length != 3) {
+ return ErrorEval.VALUE_INVALID;
+ }
-}
+ String text;
+ int startIx; // zero based
+ int numChars;
+
+ try {
+ text = evaluateTextArg(args[0], srcCellRow, srcCellCol);
+ int startCharNum = evaluateNumberArg(args[1], srcCellRow, srcCellCol);
+ numChars = evaluateNumberArg(args[2], srcCellRow, srcCellCol);
+ startIx = startCharNum - 1; // convert to zero-based
+ } catch (EvaluationException e) {
+ return e.getErrorEval();
+ }
+
+ int len = text.length();
+ if (startIx < 0) {
+ return ErrorEval.VALUE_INVALID;
+ }
+ if (numChars < 0) {
+ return ErrorEval.VALUE_INVALID;
+ }
+ if (numChars < 0 || startIx > len) {
+ return new StringEval("");
+ }
+ int endIx = startIx + numChars;
+ if (endIx > len) {
+ endIx = len;
+ }
+ String result = text.substring(startIx, endIx);
+ return new StringEval(result);
+
+ }
+
+ public static int evaluateNumberArg(Eval arg, int srcCellRow, short srcCellCol) throws EvaluationException {
+ ValueEval ev = OperandResolver.getSingleValue(arg, srcCellRow, srcCellCol);
+ if (ev instanceof BlankEval) {
+ // Note - for start_num arg, blank causes error(#VALUE!),
+ // but for num_chars causes empty string to be returned.
+ return 0;
+ }
+
+ return OperandResolver.coerceValueToInt(ev);
+ }
+
+ private static String evaluateTextArg(Eval arg, int srcCellRow, short srcCellCol) throws EvaluationException {
+ ValueEval ev = OperandResolver.getSingleValue(arg, srcCellRow, srcCellCol);
+ if (ev instanceof StringValueEval) {
+ return ((StringValueEval) ev).getStringValue();
+ }
+ throw EvaluationException.invalidValue();
+ }
+}
\ No newline at end of file
Index: src/scratchpad/testcases/org/apache/poi/hssf/record/formula/functions/AllIndividualFunctionEvaluationTests.java
===================================================================
--- src/scratchpad/testcases/org/apache/poi/hssf/record/formula/functions/AllIndividualFunctionEvaluationTests.java (revision 628181)
+++ src/scratchpad/testcases/org/apache/poi/hssf/record/formula/functions/AllIndividualFunctionEvaluationTests.java (working copy)
@@ -35,6 +35,7 @@
result.addTestSuite(TestDate.class);
result.addTestSuite(TestFinanceLib.class);
result.addTestSuite(TestIndex.class);
+ result.addTestSuite(TestMid.class);
result.addTestSuite(TestMathX.class);
result.addTestSuite(TestMatch.class);
result.addTestSuite(TestRowCol.class);