Index: src/java/org/apache/poi/hssf/record/formula/eval/FunctionEval.java
===================================================================
--- src/java/org/apache/poi/hssf/record/formula/eval/FunctionEval.java (revision 927787)
+++ src/java/org/apache/poi/hssf/record/formula/eval/FunctionEval.java (working copy)
@@ -94,6 +94,7 @@
retval[37] = BooleanFunction.OR;
retval[38] = BooleanFunction.NOT;
retval[39] = NumericFunction.MOD;
+ retval[48] = TextFunction.TEXT;
retval[56] = FinanceFunction.PV;
retval[57] = FinanceFunction.FV;
Index: src/java/org/apache/poi/hssf/record/formula/functions/TextFunction.java
===================================================================
--- src/java/org/apache/poi/hssf/record/formula/functions/TextFunction.java (revision 927787)
+++ src/java/org/apache/poi/hssf/record/formula/functions/TextFunction.java (working copy)
@@ -17,6 +17,12 @@
package org.apache.poi.hssf.record.formula.functions;
+import java.text.DateFormat;
+import java.text.DecimalFormat;
+import java.text.NumberFormat;
+import java.text.SimpleDateFormat;
+import java.util.Calendar;
+import java.util.GregorianCalendar;
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.EvaluationException;
@@ -28,6 +34,7 @@
/**
* @author Amol S. Deshmukh < amolweb at ya hoo dot com >
* @author Josh Micich
+ * @author Stephen Wolke (smwolke at geistig.com)
*/
public abstract class TextFunction implements Function {
@@ -41,6 +48,11 @@
ValueEval ve = OperandResolver.getSingleValue(arg, srcCellRow, srcCellCol);
return OperandResolver.coerceValueToInt(ve);
}
+
+ protected static final double evaluateDoubleArg(ValueEval arg, int srcCellRow, int srcCellCol) throws EvaluationException {
+ ValueEval ve = OperandResolver.getSingleValue(arg, srcCellRow, srcCellCol);
+ return OperandResolver.coerceValueToDouble(ve);
+ }
public final ValueEval evaluate(ValueEval[] args, int srcCellRow, int srcCellCol) {
try {
@@ -206,6 +218,86 @@
}
};
+ /**
+ * An implementation of the TEXT function
+ * TEXT returns a number value formatted with the given
+ * number formatting string. This function is not a complete implementation of
+ * the Excel function. This function implements decimal formatting
+ * with the Java class DecimalFormat. For date formatting this function uses
+ * the SimpleDateFormat class.
+ *
+ * Syntax:
TEXT(value, format_text)
+ *
+ */
+ public static final Function TEXT = new Fixed2ArgFunction() {
+
+ public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) {
+ double s0;
+ String s1;
+ try {
+ s0 = evaluateDoubleArg(arg0, srcRowIndex, srcColumnIndex);
+ s1 = evaluateStringArg(arg1, srcRowIndex, srcColumnIndex);
+ } catch (EvaluationException e) {
+ return e.getErrorEval();
+ }
+ if (s1.matches("[\\d,\\#,\\.,\\$,\\,]+")) {
+ NumberFormat formatter = new DecimalFormat(s1);
+ return new StringEval(formatter.format(s0));
+ } else if (s1.indexOf("/") == s1.lastIndexOf("/") && s1.indexOf("/") >=0 && !s1.contains("-")) {
+ double wholePart = Math.floor(s0);
+ double decPart = s0 - wholePart;
+ if (wholePart * decPart == 0) {
+ return new StringEval("0");
+ }
+ String[] parts = s1.split(" ");
+ String[] fractParts;
+ if (parts.length == 2) {
+ fractParts = parts[1].split("/");
+ } else {
+ fractParts = s1.split("/");
+ }
+
+ if (fractParts.length == 2) {
+ double minVal = 1.0;
+ double currDenom = Math.pow(10 , fractParts[1].length()) - 1d;
+ double currNeum = 0;
+ for (int i = (int)(Math.pow(10, fractParts[1].length())- 1d); i > 0; i--) {
+ for(int i2 = (int)(Math.pow(10, fractParts[1].length())- 1d); i2 > 0; i2--){
+ if (minVal >= Math.abs((double)i2/(double)i - decPart)) {
+ currDenom = i;
+ currNeum = i2;
+ minVal = Math.abs((double)i2/(double)i - decPart);
+ }
+ }
+ }
+ NumberFormat neumFormatter = new DecimalFormat(fractParts[0]);
+ NumberFormat denomFormatter = new DecimalFormat(fractParts[1]);
+ if (parts.length == 2) {
+ NumberFormat wholeFormatter = new DecimalFormat(parts[0]);
+ String result = wholeFormatter.format(wholePart) + " " + neumFormatter.format(currNeum) + "/" + denomFormatter.format(currDenom);
+ return new StringEval(result);
+ } else {
+ String result = neumFormatter.format(currNeum + (currDenom * wholePart)) + "/" + denomFormatter.format(currDenom);
+ return new StringEval(result);
+ }
+ } else {
+ return ErrorEval.VALUE_INVALID;
+ }
+ } else {
+ try {
+ DateFormat dateFormatter = new SimpleDateFormat(s1);
+ Calendar cal = new GregorianCalendar(1899, 11, 30, 0, 0, 0);
+ cal.add(Calendar.DATE, (int)Math.floor(s0));
+ double dayFraction = s0 - Math.floor(s0);
+ cal.add(Calendar.MILLISECOND, (int) Math.round(dayFraction * 24 * 60 * 60 * 1000));
+ return new StringEval(dateFormatter.format(cal.getTime()));
+ } catch (Exception e) {
+ return ErrorEval.VALUE_INVALID;
+ }
+ }
+ }
+ };
+
private static final class SearchFind extends Var2or3ArgFunction {
private final boolean _isCaseSensitive;