All Downloads are FREE. Search and download functionalities are using the official Maven repository.

org.apache.poi.hssf.record.formula.functions.TextFunction Maven / Gradle / Ivy

There is a newer version: 5.2.5
Show newest version
/* ====================================================================
   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 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;
import org.apache.poi.hssf.record.formula.eval.NumberEval;
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.ValueEval;

/**
 * @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 {

	protected static final String EMPTY_STRING = "";

	protected static final String evaluateStringArg(ValueEval eval, int srcRow, int srcCol) throws EvaluationException {
		ValueEval ve = OperandResolver.getSingleValue(eval, srcRow, srcCol);
		return OperandResolver.coerceValueToString(ve);
	}
	protected static final int evaluateIntArg(ValueEval arg, int srcCellRow, int srcCellCol) throws EvaluationException {
		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 {
			return evaluateFunc(args, srcCellRow, srcCellCol);
		} catch (EvaluationException e) {
			return e.getErrorEval();
		}
	}

	protected abstract ValueEval evaluateFunc(ValueEval[] args, int srcCellRow, int srcCellCol) throws EvaluationException;

	/* ---------------------------------------------------------------------- */

	private static abstract class SingleArgTextFunc extends Fixed1ArgFunction {

		protected SingleArgTextFunc() {
			// no fields to initialise
		}
		public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0) {
			String arg;
			try {
				arg = evaluateStringArg(arg0, srcRowIndex, srcColumnIndex);
			} catch (EvaluationException e) {
				return e.getErrorEval();
			}
			return evaluate(arg);
		}
		protected abstract ValueEval evaluate(String arg);
	}

	public static final Function LEN = new SingleArgTextFunc() {
		protected ValueEval evaluate(String arg) {
			return new NumberEval(arg.length());
		}
	};
	public static final Function LOWER = new SingleArgTextFunc() {
		protected ValueEval evaluate(String arg) {
			return new StringEval(arg.toLowerCase());
		}
	};
	public static final Function UPPER = new SingleArgTextFunc() {
		protected ValueEval evaluate(String arg) {
			return new StringEval(arg.toUpperCase());
		}
	};
	/**
	 * An implementation of the TRIM function:
	 * Removes leading and trailing spaces from value if evaluated operand
	 *  value is string.
	 * Author: Manda Wilson < wilson at c bio dot msk cc dot org >
	 */
	public static final Function TRIM = new SingleArgTextFunc() {
		protected ValueEval evaluate(String arg) {
			return new StringEval(arg.trim());
		}
	};

	/**
	 * 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 static final Function MID = new Fixed3ArgFunction() { public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1, ValueEval arg2) { String text; int startCharNum; int numChars; try { text = evaluateStringArg(arg0, srcRowIndex, srcColumnIndex); startCharNum = evaluateIntArg(arg1, srcRowIndex, srcColumnIndex); numChars = evaluateIntArg(arg2, srcRowIndex, srcColumnIndex); } catch (EvaluationException e) { return e.getErrorEval(); } int startIx = startCharNum - 1; // convert to zero-based // Note - for start_num arg, blank/zero causes error(#VALUE!), // but for num_chars causes empty string to be returned. if (startIx < 0) { return ErrorEval.VALUE_INVALID; } if (numChars < 0) { return ErrorEval.VALUE_INVALID; } int len = text.length(); if (numChars < 0 || startIx > len) { return new StringEval(""); } int endIx = Math.min(startIx + numChars, len); String result = text.substring(startIx, endIx); return new StringEval(result); } }; private static final class LeftRight extends Var1or2ArgFunction { private static final ValueEval DEFAULT_ARG1 = new NumberEval(1.0); private final boolean _isLeft; protected LeftRight(boolean isLeft) { _isLeft = isLeft; } public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0) { return evaluate(srcRowIndex, srcColumnIndex, arg0, DEFAULT_ARG1); } public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) { String arg; int index; try { arg = evaluateStringArg(arg0, srcRowIndex, srcColumnIndex); index = evaluateIntArg(arg1, srcRowIndex, srcColumnIndex); } catch (EvaluationException e) { return e.getErrorEval(); } if(index < 0) { return ErrorEval.VALUE_INVALID; } String result; if (_isLeft) { result = arg.substring(0, Math.min(arg.length(), index)); } else { result = arg.substring(Math.max(0, arg.length()-index)); } return new StringEval(result); } } public static final Function LEFT = new LeftRight(true); public static final Function RIGHT = new LeftRight(false); public static final Function CONCATENATE = new Function() { public ValueEval evaluate(ValueEval[] args, int srcRowIndex, int srcColumnIndex) { StringBuilder sb = new StringBuilder(); for (int i=0, iSize=args.length; i * 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; public SearchFind(boolean isCaseSensitive) { _isCaseSensitive = isCaseSensitive; } public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) { try { String needle = TextFunction.evaluateStringArg(arg0, srcRowIndex, srcColumnIndex); String haystack = TextFunction.evaluateStringArg(arg1, srcRowIndex, srcColumnIndex); return eval(haystack, needle, 0); } catch (EvaluationException e) { return e.getErrorEval(); } } public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1, ValueEval arg2) { try { String needle = TextFunction.evaluateStringArg(arg0, srcRowIndex, srcColumnIndex); String haystack = TextFunction.evaluateStringArg(arg1, srcRowIndex, srcColumnIndex); // evaluate third arg and convert from 1-based to 0-based index int startpos = TextFunction.evaluateIntArg(arg2, srcRowIndex, srcColumnIndex) - 1; if (startpos < 0) { return ErrorEval.VALUE_INVALID; } return eval(haystack, needle, startpos); } catch (EvaluationException e) { return e.getErrorEval(); } } private ValueEval eval(String haystack, String needle, int startIndex) { int result; if (_isCaseSensitive) { result = haystack.indexOf(needle, startIndex); } else { result = haystack.toUpperCase().indexOf(needle.toUpperCase(), startIndex); } if (result == -1) { return ErrorEval.VALUE_INVALID; } return new NumberEval(result + 1); } } /** * Implementation of the FIND() function.

* * Syntax:
* FIND(find_text, within_text, start_num)

* * FIND returns the character position of the first (case sensitive) occurrence of * find_text inside within_text. The third parameter, * start_num, is optional (default=1) and specifies where to start searching * from. Character positions are 1-based.

* * Author: Torstein Tauno Svendsen ([email protected]) */ public static final Function FIND = new SearchFind(true); /** * Implementation of the FIND() function.

* * Syntax:
* SEARCH(find_text, within_text, start_num)

* * SEARCH is a case-insensitive version of FIND() */ public static final Function SEARCH = new SearchFind(false); }