View | Details | Raw Unified | Return to bug 44403
Collapse All | Expand All

(-)src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Mid.java (-78 / +79 lines)
Lines 1-99 Link Here
1
/*
1
/*
2
* Licensed to the Apache Software Foundation (ASF) under one or more
2
 * Licensed to the Apache Software Foundation (ASF) under one or more
3
* contributor license agreements.  See the NOTICE file distributed with
3
 * contributor license agreements.  See the NOTICE file distributed with
4
* this work for additional information regarding copyright ownership.
4
 * this work for additional information regarding copyright ownership.
5
* The ASF licenses this file to You under the Apache License, Version 2.0
5
 * The ASF licenses this file to You under the Apache License, Version 2.0
6
* (the "License"); you may not use this file except in compliance with
6
 * (the "License"); you may not use this file except in compliance with
7
* the License.  You may obtain a copy of the License at
7
 * the License.  You may obtain a copy of the License at
8
*
9
*     http://www.apache.org/licenses/LICENSE-2.0
10
*
11
* Unless required by applicable law or agreed to in writing, software
12
* distributed under the License is distributed on an "AS IS" BASIS,
13
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
14
* See the License for the specific language governing permissions and
15
* limitations under the License.
16
*/
17
/*
18
 * Created on May 15, 2005
19
 *
8
 *
9
 *     http://www.apache.org/licenses/LICENSE-2.0
10
 *
11
 * Unless required by applicable law or agreed to in writing, software
12
 * distributed under the License is distributed on an "AS IS" BASIS,
13
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
14
 * See the License for the specific language governing permissions and
15
 * limitations under the License.
20
 */
16
 */
17
21
package org.apache.poi.hssf.record.formula.functions;
18
package org.apache.poi.hssf.record.formula.functions;
22
19
23
import org.apache.poi.hssf.record.formula.eval.BlankEval;
20
import org.apache.poi.hssf.record.formula.eval.BlankEval;
24
import org.apache.poi.hssf.record.formula.eval.ErrorEval;
21
import org.apache.poi.hssf.record.formula.eval.ErrorEval;
25
import org.apache.poi.hssf.record.formula.eval.Eval;
22
import org.apache.poi.hssf.record.formula.eval.Eval;
26
import org.apache.poi.hssf.record.formula.eval.NumericValueEval;
23
import org.apache.poi.hssf.record.formula.eval.EvaluationException;
24
import org.apache.poi.hssf.record.formula.eval.OperandResolver;
27
import org.apache.poi.hssf.record.formula.eval.StringEval;
25
import org.apache.poi.hssf.record.formula.eval.StringEval;
28
import org.apache.poi.hssf.record.formula.eval.StringValueEval;
26
import org.apache.poi.hssf.record.formula.eval.StringValueEval;
29
import org.apache.poi.hssf.record.formula.eval.ValueEval;
27
import org.apache.poi.hssf.record.formula.eval.ValueEval;
30
28
31
/**
29
/**
32
 * An implementation of the MID function:
30
 * An implementation of the MID function<br/> MID returns a specific number of
33
 * Returns a specific number of characters from a text string, 
31
 * characters from a text string, starting at the specified position.<p/>
34
 * starting at the position you specify, based on the number 
32
 * 
35
 * of characters you specify.
33
 * <b>Syntax<b>:<br/> <b>MID</b>(<b>text</b>, <b>start_num</b>,
34
 * <b>num_chars</b>)<br/>
35
 * 
36
 * @author Manda Wilson &lt; wilson at c bio dot msk cc dot org &gt;
36
 * @author Manda Wilson &lt; wilson at c bio dot msk cc dot org &gt;
37
 */
37
 */
38
public class Mid extends TextFunction {
38
public class Mid extends TextFunction {
39
	/**
39
	/**
40
	 * Returns a specific number of characters from a text string, 
40
	 * Returns a specific number of characters from a text string, starting at
41
	 * starting at the position you specify, based on the number 
41
	 * the position you specify, based on the number of characters you specify.
42
	 * of characters you specify.
43
	 * 
42
	 * 
44
	 * @see org.apache.poi.hssf.record.formula.eval.Eval
43
	 * @see org.apache.poi.hssf.record.formula.eval.Eval
45
	 */
44
	 */
46
    public Eval evaluate(Eval[] operands, int srcCellRow, short srcCellCol) {		
45
	public Eval evaluate(Eval[] args, int srcCellRow, short srcCellCol) {
47
    	Eval retval = null;
46
		if (args.length != 3) {
48
        String str = null;
47
			return ErrorEval.VALUE_INVALID;
49
        int startNum = 0;
48
		}
50
        int numChars = 0;
51
        
52
        switch (operands.length) {
53
	        default:
54
	            retval = ErrorEval.VALUE_INVALID;
55
	        case 3:
56
	        	// first operand is text string containing characters to extract
57
	            // second operand is position of first character to extract
58
	            // third operand is the number of characters to return
59
	            ValueEval firstveval = singleOperandEvaluate(operands[0], srcCellRow, srcCellCol);
60
	            ValueEval secondveval = singleOperandEvaluate(operands[1], srcCellRow, srcCellCol);
61
	            ValueEval thirdveval = singleOperandEvaluate(operands[2], srcCellRow, srcCellCol);
62
	            if (firstveval instanceof StringValueEval
63
	            	&& secondveval instanceof NumericValueEval
64
	            	&& thirdveval instanceof NumericValueEval) {
65
	            	
66
	                StringValueEval strEval = (StringValueEval) firstveval;
67
	                str = strEval.getStringValue();
68
	                
69
	                NumericValueEval startNumEval = (NumericValueEval) secondveval;
70
	                // NOTE: it is safe to cast to int here
71
	                // because in Excel =MID("test", 1, 1.7) returns t 
72
	                // so 1.7 must be truncated to 1
73
	                // and =MID("test", 1.9, 2) returns te 
74
	                // so 1.9 must be truncated to 1
75
	                startNum = (int) startNumEval.getNumberValue();
76
	                
77
	                NumericValueEval numCharsEval = (NumericValueEval) thirdveval;
78
	                numChars = (int) numCharsEval.getNumberValue();
79
	                
80
	            } else {
81
	            	retval = ErrorEval.VALUE_INVALID;
82
	            }
83
	    }
84
	        
85
        if (retval == null) {
86
			if (startNum < 1 || numChars < 0) {
87
				retval = ErrorEval.VALUE_INVALID;
88
			} else if (startNum > str.length() || numChars == 0) {
89
				retval = BlankEval.INSTANCE;
90
			} else if (startNum + numChars > str.length()) {
91
				retval = new StringEval(str.substring(startNum - 1));
92
			} else {
93
				retval = new StringEval(str.substring(startNum - 1, (numChars + startNum - 1)));
94
			} 
95
        } 
96
		return retval;
97
    }
98
49
99
}
50
		String text;
51
		int startIx; // zero based
52
		int numChars;
53
54
		try {
55
			text = evaluateTextArg(args[0], srcCellRow, srcCellCol);
56
			int startCharNum = evaluateNumberArg(args[1], srcCellRow, srcCellCol);
57
			numChars = evaluateNumberArg(args[2], srcCellRow, srcCellCol);
58
			startIx = startCharNum - 1; // convert to zero-based
59
		} catch (EvaluationException e) {
60
			return e.getErrorEval();
61
		}
62
63
		int len = text.length();
64
		if (startIx < 0) {
65
			return ErrorEval.VALUE_INVALID;
66
		}
67
		if (numChars < 0) {
68
			return ErrorEval.VALUE_INVALID;
69
		}
70
		if (numChars < 0 || startIx > len) {
71
			return new StringEval("");
72
		}
73
		int endIx = startIx + numChars;
74
		if (endIx > len) {
75
			endIx = len;
76
		}
77
		String result = text.substring(startIx, endIx);
78
		return new StringEval(result);
79
80
	}
81
82
	public static int evaluateNumberArg(Eval arg, int srcCellRow, short srcCellCol) throws EvaluationException {
83
		ValueEval ev = OperandResolver.getSingleValue(arg, srcCellRow, srcCellCol);
84
		if (ev instanceof BlankEval) {
85
			// Note - for start_num arg, blank causes error(#VALUE!),
86
			// but for num_chars causes empty string to be returned.
87
			return 0;
88
		}
89
90
		return OperandResolver.coerceValueToInt(ev);
91
	}
92
93
	private static String evaluateTextArg(Eval arg, int srcCellRow, short srcCellCol) throws EvaluationException {
94
		ValueEval ev = OperandResolver.getSingleValue(arg, srcCellRow, srcCellCol);
95
		if (ev instanceof StringValueEval) {
96
			return ((StringValueEval) ev).getStringValue();
97
		}
98
		throw EvaluationException.invalidValue();
99
	}
100
}
(-)src/scratchpad/testcases/org/apache/poi/hssf/record/formula/functions/AllIndividualFunctionEvaluationTests.java (+1 lines)
Lines 35-40 Link Here
35
		result.addTestSuite(TestDate.class);
35
		result.addTestSuite(TestDate.class);
36
		result.addTestSuite(TestFinanceLib.class);
36
		result.addTestSuite(TestFinanceLib.class);
37
		result.addTestSuite(TestIndex.class);
37
		result.addTestSuite(TestIndex.class);
38
		result.addTestSuite(TestMid.class);
38
		result.addTestSuite(TestMathX.class);
39
		result.addTestSuite(TestMathX.class);
39
		result.addTestSuite(TestMatch.class);
40
		result.addTestSuite(TestMatch.class);
40
		result.addTestSuite(TestRowCol.class);
41
		result.addTestSuite(TestRowCol.class);

Return to bug 44403