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

(-)src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Substitute.java (-1 / +93 lines)
Lines 20-25 Link Here
20
 */
20
 */
21
package org.apache.poi.hssf.record.formula.functions;
21
package org.apache.poi.hssf.record.formula.functions;
22
22
23
public class Substitute extends NotImplementedFunction {
23
import org.apache.poi.hssf.record.formula.eval.ErrorEval;
24
import org.apache.poi.hssf.record.formula.eval.Eval;
25
import org.apache.poi.hssf.record.formula.eval.NumericValueEval;
26
import org.apache.poi.hssf.record.formula.eval.StringEval;
27
import org.apache.poi.hssf.record.formula.eval.StringValueEval;
28
import org.apache.poi.hssf.record.formula.eval.ValueEval;
24
29
30
/**
31
 * An implementation of the SUBSTITUTE function:
32
 * Substitutes text in a text string with new text, some number of times.
33
 * @author Manda Wilson < wilson at c bio dot msk cc dot org >
34
 */
35
public class Substitute extends TextFunction {
36
	private static final int REPLACE_ALL = -1;
37
	
38
	/**
39
	 *Substitutes text in a text string with new text, some number of times.
40
	 * 
41
	 * @see org.apache.poi.hssf.record.formula.eval.Eval
42
	 */
43
    public Eval evaluate(Eval[] operands, int srcCellRow, short srcCellCol) {		
44
    	Eval retval = null;
45
        String oldStr = null;
46
        String searchStr = null;
47
        String newStr = null;
48
        int numToReplace = REPLACE_ALL;
49
        
50
        switch (operands.length) {
51
	        default:
52
	            retval = ErrorEval.VALUE_INVALID;
53
	        case 4:
54
	        	ValueEval fourthveval = singleOperandEvaluate(operands[3], srcCellRow, srcCellCol);
55
	        	if (fourthveval instanceof NumericValueEval) {
56
	        		NumericValueEval numToReplaceEval = (NumericValueEval) fourthveval;
57
	        		// NOTE: it is safe to cast to int here
58
	                // because in Excel =SUBSTITUTE("teststr","t","T",1.9) 
59
	                // returns Teststr 
60
	                // so 1.9 must be truncated to 1
61
		        	numToReplace = (int) numToReplaceEval.getNumberValue();
62
	        	} else {
63
	        		retval = ErrorEval.VALUE_INVALID;
64
	        	}
65
	        case 3:	
66
	        	// first operand is text string containing characters to replace
67
	            // second operand is text to find
68
	            // third operand is replacement text
69
	            ValueEval firstveval = singleOperandEvaluate(operands[0], srcCellRow, srcCellCol);
70
	            ValueEval secondveval = singleOperandEvaluate(operands[1], srcCellRow, srcCellCol);
71
	            ValueEval thirdveval = singleOperandEvaluate(operands[2], srcCellRow, srcCellCol);
72
	            if (firstveval instanceof StringValueEval
73
	            	&& secondveval instanceof StringValueEval
74
	            	&& thirdveval instanceof StringValueEval) {
75
	            	
76
	                StringValueEval oldStrEval = (StringValueEval) firstveval;
77
	                oldStr = oldStrEval.getStringValue();
78
	                
79
	                StringValueEval searchStrEval = (StringValueEval) secondveval;
80
	               	searchStr = searchStrEval.getStringValue();
81
	                
82
	               	StringValueEval newStrEval = (StringValueEval) thirdveval;
83
	               	newStr = newStrEval.getStringValue();
84
	            } else {
85
	            	retval = ErrorEval.VALUE_INVALID;
86
	            }
87
	    }
88
	        
89
        if (retval == null) {
90
			if (numToReplace != REPLACE_ALL && numToReplace < 1) {
91
				retval = ErrorEval.VALUE_INVALID;
92
			} else if (searchStr.length() == 0) {
93
				retval = new StringEval(oldStr);
94
			} else {
95
				StringBuffer strBuff = new StringBuffer();
96
				int startIndex = 0;
97
				int nextMatch = -1;
98
				for (int leftToReplace = numToReplace; 
99
					(leftToReplace > 0 || numToReplace == REPLACE_ALL) 
100
						&& (nextMatch = oldStr.indexOf(searchStr, startIndex)) != -1;
101
					leftToReplace--) {
102
					// store everything from end of last match to start of this match
103
					strBuff.append(oldStr.substring(startIndex, nextMatch));
104
					strBuff.append(newStr);
105
					startIndex = nextMatch + searchStr.length();
106
				}
107
				// store everything from end of last match to end of string
108
				if (startIndex < oldStr.length()) {
109
					strBuff.append(oldStr.substring(startIndex));
110
				}
111
				retval = new StringEval(strBuff.toString());
112
			}
113
        } 
114
		return retval;
115
    }
116
    
25
}
117
}

Return to bug 44099