huilen.java

package com.excel2dot0.Formulas;

import java.util.ArrayList;
import java.util.Scanner;

public class Formulas {

	private static Scanner sc;

	/**
	 * The getFormulas method checks whether the given String for formulas and
	 * puts every function separated in an ArrayList.
	 * 
	 * @param data
	 *            The String containing the function and possible nested
	 *            functions.
	 * @return an ArrayList containing all the functions within the given String
	 *         -
	 */
	public static ArrayList<String> getFormulas(String data) {
		// ArrauList to store all formulas
		ArrayList<String> res = new ArrayList<String>();
		sc = new Scanner(data);
		sc.useDelimiter("\\(");
		// String to store the formula that is read
		String nextFormula = "";

		// Add each formula to the ArrayList
		while (sc.hasNext()) {
			nextFormula = sc.next();
			if (nextFormula.endsWith("AVERAGE"))
				res.add("AVERAGE");
			else if (nextFormula.endsWith("COUNT"))
				res.add("COUNT");
			else if (nextFormula.endsWith("COUNTA"))
				res.add("COUNTA");
			else if (nextFormula.endsWith("COUNTIF"))
				res.add("COUNTIF");
			else if (nextFormula.endsWith("SUMIF"))
				res.add("SUMIF");
			else if (nextFormula.endsWith("IF"))
				res.add("IF");
			else if (nextFormula.endsWith("INT"))
				res.add("INT");
			else if (nextFormula.endsWith("ISLOGICAL"))
				res.add("ISLOGICAL");
			else if (nextFormula.endsWith("ISEVEN"))
				res.add("ISEVEN");
			else if (nextFormula.endsWith("ISNUMBER"))
				res.add("ISNUMBER");
			else if (nextFormula.endsWith("LOWER"))
				res.add("LOWER");
			else if (nextFormula.endsWith("MAX"))
				res.add("MAX");
			else if (nextFormula.endsWith("MEDIAN"))
				res.add("MEDIAN");
			else if (nextFormula.endsWith("MIN"))
				res.add("MIN");
			else if (nextFormula.endsWith("MOD"))
				res.add("MOD");
			else if (nextFormula.endsWith("NOT"))
				res.add("NOT");
			else if (nextFormula.endsWith("OR"))
				res.add("OR");
			else if (nextFormula.endsWith("AND"))
				res.add("AND");
			else if (nextFormula.endsWith("POWER"))
				res.add("POWER");
			else if (nextFormula.endsWith("PRODUCT"))
				res.add("PRODUCT");
			else if (nextFormula.endsWith("PROPER"))
				res.add("PROPER");
			else if (nextFormula.endsWith("ROUNDDOWN"))
				res.add("ROUNDDOWN");
			else if (nextFormula.endsWith("ROUNDUP"))
				res.add("ROUNDUP");
			else if (nextFormula.endsWith("SIGN"))
				res.add("SIGN");
			else if (nextFormula.endsWith("SQRT"))
				res.add("SQRT");
			else if (nextFormula.endsWith("SUM"))
				res.add("SUM");
		}

		sc.close();
		return res;
	}

	public static String getFormulaSeparator(String formula) {
		return ";";
	}

	/**
	 * Method that counts the number of formulas contained in the data String.
	 * 
	 * @param data
	 *            String containing the formula.
	 * 
	 * @return count - The number of formulas in the data String
	 */
	public static int countFormulas(String data) {

		return getFormulas(data).size();
	}

	/**
	 * The getNextSingleFormula method gets the next formula from the given
	 * ArrayList of formulas.
	 * 
	 * @param subFormulas
	 *            the ArrayList with all the formulas that need to be processed.
	 * 
	 * @return the next formula from the ArrayList.
	 */

	public static String getNextSingleFormula(ArrayList<String> subFormulas) {
		String res = "";
		for (String str : subFormulas)
			if (countFormulas(str) == 1)
				res = str;
		return res;
	}

	/**
	 * The calculateSubFormula looks at the given (sub)formula and calls the
	 * right function. According to the containing substring.
	 * 
	 * @param formula
	 *            String containing the (sub)formula and the given values.
	 * @return The result of the (sub)function
	 * 
	 * @throws FormulaException
	 *             , throws and exception if anything went wrong. Such as:
	 *             Syntax Error, (#ERROR), (#DIV/0), If the values in the
	 *             formula ask to divide by zero. (#VALUE), if the formula
	 *             contains a value or a numerical value that the function
	 *             cannot process, (#NUM).
	 */

	private static Object calculateSubFormula(String formula)
			throws FormulaException {
		Object formulaResult = 0;

		// Calculate the result of the formula
		if (formula.contains("SUMIF"))
			formulaResult = SumIf.sumIf(formula);
		else if (formula.contains("SUM"))
			formulaResult = Sum.sum(formula);
		else if (formula.startsWith("AVERAGE"))
			formulaResult = Average.average(formula);
		else if (formula.startsWith("IF"))
			formulaResult = If.formulaIf(formula);
		else if (formula.startsWith("MEDIAN"))
			formulaResult = Median.median(formula);
		else if (formula.startsWith("PROPER"))
			formulaResult = Proper.proper(formula);
		else if (formula.startsWith("MOD"))
			formulaResult = Mod.mod(formula);
		else if (formula.startsWith("COUNTIF"))
			formulaResult = CountIf.countIf(formula);
		else if (formula.startsWith("COUNTA"))
			formulaResult = CountA.countA(formula);
		else if (formula.startsWith("COUNT"))
			formulaResult = Count.count(formula);
		else if (formula.startsWith("INT"))
			formulaResult = Int.intFormula(formula);
		else if (formula.startsWith("ISEVEN"))
			formulaResult = IsEven.isEven(formula);
		else if (formula.startsWith("ISNUMBER"))
			formulaResult = IsNumber.isNumber(formula);
		else if (formula.startsWith("LOWER"))
			formulaResult = Lower.lower(formula);
		else if (formula.startsWith("POWER"))
			formulaResult = Power.power(formula);
		else if (formula.startsWith("PRODUCT"))
			formulaResult = Product.product(formula);
		else if (formula.startsWith("SQRT"))
			formulaResult = Sqrt.sqrt(formula);
		else if (formula.startsWith("MAX"))
			formulaResult = Max.max(formula);
		else if (formula.startsWith("MIN"))
			formulaResult = Min.min(formula);
		else if (formula.startsWith("SIGN"))
			formulaResult = Sign.sign(formula);
		else if (formula.startsWith("ROUNDDOWN"))
			formulaResult = RoundDown.roundDown(formula);
		else if (formula.startsWith("ROUNDUP"))
			formulaResult = RoundUp.roundUp(formula);
		else if (formula.startsWith("ISLOGICAL"))
			formulaResult = IsLogical.isLogical(formula);
		else if (formula.startsWith("NOT"))
			formulaResult = Not.not(formula);
		else if (formula.startsWith("OR"))
			formulaResult = Or.or(formula);
		else if (formula.startsWith("AND"))
			formulaResult = And.and(formula);

		return formulaResult;
	}

	/**
	 * Separates the nested formulas from one string and puts these in an
	 * ArrayList.
	 * 
	 * @param formula, String containing the formulas.
	 * @return an ArrayList with all of the formulas in the String.
	 * 
	 * @throws FormulaException if one of the formulas contains a syntax error.
	 */

	public static ArrayList<String> separateFormulas(String formula)
			throws FormulaException {
		ArrayList<String> formulas = getFormulas(formula);
		ArrayList<Integer> openIndices = new ArrayList<Integer>();
		ArrayList<Integer> closeIndices = new ArrayList<Integer>();
		ArrayList<String> subformulas = new ArrayList<String>();

		if (!formula.contains(")") || !formula.contains("("))
			throw new FormulaException(
					"The formula you typed contains an error.\n\nAdd \")\" or delete \"(\"",
					"#ERROR");

		if (formula.length() != 0) {
			int open = 0;
			int close = 0;

			for (int i = 0; i < formula.length(); i++) {
				if (formula.charAt(i) == '(') {
					open++;
					openIndices.add(i);
				} else if (formula.charAt(i) == ')') {
					closeIndices.add(i);
					close++;
				}
			}
			if (open > close)
				throw new FormulaException(
						"The formula you typed contains an error.\n\nAdd \")\" or delete \"(\"",
						"#ERROR");
			if (open < close)
				throw new FormulaException(
						"The formula you typed contains an error.\n\nExpecting \"(\" or delete \")\"",
						"#ERROR");

			// add the first formula
			subformulas.add(formula);
			formula = formula.replace(formulas.get(0) + "(", "");
			if (formulas.size() > 1)
				formula = formula.substring(
						formula.indexOf(formulas.get(1).charAt(0)),
						formula.lastIndexOf(')'));
			else
				formula = formula.substring(0, formula.lastIndexOf(')'));

			Scanner sc = new Scanner(formula);
			sc.useDelimiter(";");
			open = 0;
			close = 0;
			while (sc.hasNext()) {
				String next = sc.next();
				if (next.contains("("))
					open = next.length() - next.replace("(", "").length();
				if (next.contains(")"))
					close = next.length() - next.replace(")", "").length();
				if (countFormulas(next) == 1 && open == close)
					subformulas.add(next);
				else if (open > close)
					while (open > close && sc.hasNext()) {
						String nextNext = sc.next();
						if (nextNext.contains(")")) {
							next = next + ";" + nextNext;
							close += nextNext.length()
									- nextNext.replace(")", "").length();
							subformulas.add(next);
						}
						next = next + ";" + nextNext;
					}
			}
			sc.close();
		}
		return subformulas;
	}

		/**
		 * The getFormulaResult method calculates every (sub)formula separately in the string.
		 * @param data String containing the formulas.
		 * @return the result of the formula in the given String.
		 * @throws FormulaException if the string does not contain a valid formula.
		 */

	public static Object getFormulaResult(String data) throws FormulaException {
		Object formulaResult = 0;

		// When the formula is only "=", display it as String
		if (data.equals("="))
			return "=";
		// Return the entered string when it does not contain any formula
		if (countFormulas(data) == 0)
			return data;
		// Get all the nested formulas
		ArrayList<String> subFormulas = separateFormulas(data);

		// Calculate all these formulas
		for (String str : subFormulas) {
			String next = getNextSingleFormula(subFormulas);
			// Replace all same subformulas with their answer
			if (str.contains(next)) {
				for (int j = 0; j < subFormulas.size(); j++) {
					str = subFormulas.get(j).replace(next,
							calculateSubFormula(next).toString());
					subFormulas.set(j, str);
				}
			}
		}

		formulaResult = subFormulas.get(subFormulas.size() - 1);
		return formulaResult;
	}
}