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

(-)src/java/org/apache/poi/hssf/record/formula/eval/FunctionEval.java (+1 lines)
Lines 94-99 Link Here
94
		retval[37] = BooleanFunction.OR;
94
		retval[37] = BooleanFunction.OR;
95
		retval[38] = BooleanFunction.NOT;
95
		retval[38] = BooleanFunction.NOT;
96
		retval[39] = NumericFunction.MOD;
96
		retval[39] = NumericFunction.MOD;
97
		retval[48] = TextFunction.TEXT;
97
98
98
		retval[56] = FinanceFunction.PV;
99
		retval[56] = FinanceFunction.PV;
99
		retval[57] = FinanceFunction.FV;
100
		retval[57] = FinanceFunction.FV;
(-)src/java/org/apache/poi/hssf/record/formula/functions/TextFunction.java (+92 lines)
Lines 17-22 Link Here
17
17
18
package org.apache.poi.hssf.record.formula.functions;
18
package org.apache.poi.hssf.record.formula.functions;
19
19
20
import java.text.DateFormat;
21
import java.text.DecimalFormat;
22
import java.text.NumberFormat;
23
import java.text.SimpleDateFormat;
24
import java.util.Calendar;
25
import java.util.GregorianCalendar;
20
import org.apache.poi.hssf.record.formula.eval.BoolEval;
26
import org.apache.poi.hssf.record.formula.eval.BoolEval;
21
import org.apache.poi.hssf.record.formula.eval.ErrorEval;
27
import org.apache.poi.hssf.record.formula.eval.ErrorEval;
22
import org.apache.poi.hssf.record.formula.eval.EvaluationException;
28
import org.apache.poi.hssf.record.formula.eval.EvaluationException;
Lines 28-33 Link Here
28
/**
34
/**
29
 * @author Amol S. Deshmukh < amolweb at ya hoo dot com >
35
 * @author Amol S. Deshmukh < amolweb at ya hoo dot com >
30
 * @author Josh Micich
36
 * @author Josh Micich
37
 * @author Stephen Wolke (smwolke at geistig.com)
31
 */
38
 */
32
public abstract class TextFunction implements Function {
39
public abstract class TextFunction implements Function {
33
40
Lines 41-46 Link Here
41
		ValueEval ve = OperandResolver.getSingleValue(arg, srcCellRow, srcCellCol);
48
		ValueEval ve = OperandResolver.getSingleValue(arg, srcCellRow, srcCellCol);
42
		return OperandResolver.coerceValueToInt(ve);
49
		return OperandResolver.coerceValueToInt(ve);
43
	}
50
	}
51
	
52
	protected static final double evaluateDoubleArg(ValueEval arg, int srcCellRow, int srcCellCol) throws EvaluationException {
53
		ValueEval ve = OperandResolver.getSingleValue(arg, srcCellRow, srcCellCol);
54
		return OperandResolver.coerceValueToDouble(ve);
55
	}
44
56
45
	public final ValueEval evaluate(ValueEval[] args, int srcCellRow, int srcCellCol) {
57
	public final ValueEval evaluate(ValueEval[] args, int srcCellRow, int srcCellCol) {
46
		try {
58
		try {
Lines 206-211 Link Here
206
		}
218
		}
207
	};
219
	};
208
220
221
	/**
222
	 * An implementation of the TEXT function<br/>
223
	 * TEXT returns a number value formatted with the given
224
	 * number formatting string. This function is not a complete implementation of
225
	 * the Excel function.  This function implements decimal formatting
226
	 * with the Java class DecimalFormat.  For date formatting this function uses
227
	 * the SimpleDateFormat class.<p/>
228
	 *
229
	 * <b>Syntax<b>:<br/> <b>TEXT</b>(<b>value</b>, <b>format_text</b>)<br/>
230
	 *
231
	 */
232
	public static final Function TEXT = new Fixed2ArgFunction() {
233
234
		public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) {
235
			double s0;
236
			String s1;
237
			try {
238
				s0 = evaluateDoubleArg(arg0, srcRowIndex, srcColumnIndex);
239
				s1 = evaluateStringArg(arg1, srcRowIndex, srcColumnIndex);
240
			} catch (EvaluationException e) {
241
				return e.getErrorEval();
242
			}
243
			if (s1.matches("[\\d,\\#,\\.,\\$,\\,]+")) {
244
			NumberFormat formatter = new DecimalFormat(s1);
245
			return new StringEval(formatter.format(s0));
246
			} else if (s1.indexOf("/") == s1.lastIndexOf("/") && s1.indexOf("/") >=0 && !s1.contains("-")) {
247
				double wholePart = Math.floor(s0);
248
				double decPart = s0 - wholePart;
249
				if (wholePart * decPart == 0) {
250
					return new StringEval("0");
251
				}
252
				String[] parts = s1.split(" ");
253
				String[] fractParts;
254
				if (parts.length == 2) {
255
					fractParts = parts[1].split("/");
256
				} else {
257
					fractParts = s1.split("/");
258
				}
259
260
				if (fractParts.length == 2) {
261
					double minVal = 1.0;
262
					double currDenom = Math.pow(10 ,  fractParts[1].length()) - 1d;
263
					double currNeum = 0;
264
					for (int i = (int)(Math.pow(10,  fractParts[1].length())- 1d); i > 0; i--) {
265
						for(int i2 = (int)(Math.pow(10,  fractParts[1].length())- 1d); i2 > 0; i2--){
266
							if (minVal >=  Math.abs((double)i2/(double)i - decPart)) {
267
								currDenom = i;
268
								currNeum = i2;
269
								minVal = Math.abs((double)i2/(double)i  - decPart);
270
							}
271
						}
272
					}
273
					NumberFormat neumFormatter = new DecimalFormat(fractParts[0]);
274
					NumberFormat denomFormatter = new DecimalFormat(fractParts[1]);
275
					if (parts.length == 2) {
276
						NumberFormat wholeFormatter = new DecimalFormat(parts[0]);
277
						String result = wholeFormatter.format(wholePart) + " " + neumFormatter.format(currNeum) + "/" + denomFormatter.format(currDenom);
278
						return new StringEval(result);
279
					} else {
280
						String result = neumFormatter.format(currNeum + (currDenom * wholePart)) + "/" + denomFormatter.format(currDenom);
281
						return new StringEval(result);
282
					}
283
				} else {
284
					return ErrorEval.VALUE_INVALID;
285
				}
286
			} else {
287
				try {
288
					DateFormat dateFormatter = new SimpleDateFormat(s1);
289
					Calendar cal = new GregorianCalendar(1899, 11, 30, 0, 0, 0);
290
					cal.add(Calendar.DATE, (int)Math.floor(s0));
291
					double dayFraction = s0 - Math.floor(s0);
292
					cal.add(Calendar.MILLISECOND, (int) Math.round(dayFraction * 24 * 60 * 60 * 1000));
293
					return new StringEval(dateFormatter.format(cal.getTime()));
294
				} catch (Exception e) {
295
					return ErrorEval.VALUE_INVALID;
296
				}
297
			}
298
		}
299
	};
300
	
209
	private static final class SearchFind extends Var2or3ArgFunction {
301
	private static final class SearchFind extends Var2or3ArgFunction {
210
302
211
		private final boolean _isCaseSensitive;
303
		private final boolean _isCaseSensitive;

Return to bug 49026