www-lib-markdown/jstest/spreadsheet/ExpressionSetTests.js

class ExpressionSetTests extends BaseTest {
	test_simpleMath() {
		const grid = new SpreadsheetGrid(1, 1);
		grid.cells[0][0].originalValue = CellValue.fromCellString('=7*3');
		const expressionSet = new CellExpressionSet(grid);
		expressionSet.calculateCells();
		const expected = CellValue.fromValue(21);
		const actual = grid.cells[0][0].outputValue;
		this.assertEqual(expected, actual);
	}

	test_reference() {
		const grid = new SpreadsheetGrid(3, 1);
		grid.cells[0][0].originalValue = CellValue.fromValue(123);
		grid.cells[1][0].originalValue = CellValue.fromValue(3);
		grid.cells[2][0].originalValue = CellValue.fromCellString('=A1*B1');
		const expressionSet = new CellExpressionSet(grid);
		expressionSet.calculateCells();
		const expected = CellValue.fromValue(369);
		const actual = grid.cells[2][0].outputValue;
		this.assertEqual(expected, actual);
	}

	test_infixPriority() {
		const grid = new SpreadsheetGrid(1, 1);
		grid.cells[0][0].originalValue = CellValue.fromCellString('=4*9/3+7-4');
		const expressionSet = new CellExpressionSet(grid);
		expressionSet.calculateCells();
		const expected = CellValue.fromValue(15);
		const actual = grid.cells[0][0].outputValue;
		this.assertEqual(expected, actual);
	}

	test_filledFormula() {
		const grid = new SpreadsheetGrid(3, 3);
		grid.cells[0][0].originalValue = CellValue.fromValue(4);
		grid.cells[1][0].originalValue = CellValue.fromValue(5);
		grid.cells[2][0].originalValue = CellValue.fromCellString('=A1*B1 FILL');
		grid.cells[0][1].originalValue = CellValue.fromValue(6);
		grid.cells[1][1].originalValue = CellValue.fromValue(7);
		grid.cells[0][2].originalValue = CellValue.fromValue(8);
		grid.cells[1][2].originalValue = CellValue.fromValue(9);
		const expressionSet = new CellExpressionSet(grid);
		expressionSet.calculateCells();
		this.assertEqual(CellValue.fromValue(20), grid.cells[2][0].outputValue);
		this.assertEqual(CellValue.fromValue(42), grid.cells[2][1].outputValue);
		this.assertEqual(CellValue.fromValue(72), grid.cells[2][2].outputValue);
	}

	test_dependencies() {
		const grid = new SpreadsheetGrid(2, 4);
		grid.cells[0][0].originalValue = CellValue.fromValue(1);
		grid.cells[1][0].originalValue = CellValue.fromCellString('=A1+B2');
		grid.cells[0][1].originalValue = CellValue.fromValue(2);
		grid.cells[1][1].originalValue = CellValue.fromCellString('=A2+B3');
		grid.cells[0][2].originalValue = CellValue.fromValue(3);
		grid.cells[1][2].originalValue = CellValue.fromCellString('=A3+B4');
		grid.cells[0][3].originalValue = CellValue.fromValue(4);
		grid.cells[1][3].originalValue = CellValue.fromCellString('=A4');
		const expressionSet = new CellExpressionSet(grid);
		expressionSet.calculateCells();
		this.assertEqual(CellValue.fromValue(10), grid.cells[1][0].outputValue);
		this.assertEqual(CellValue.fromValue(9), grid.cells[1][1].outputValue);
		this.assertEqual(CellValue.fromValue(7), grid.cells[1][2].outputValue);
		this.assertEqual(CellValue.fromValue(4), grid.cells[1][3].outputValue);
	}

	_test_simple_formula(expected, formula) {
		const grid = new SpreadsheetGrid(1, 1);
		grid.cells[0][0].originalValue = CellValue.fromCellString(formula);
		const expressionSet = new CellExpressionSet(grid);
		expressionSet.calculateCells();
		const actual = grid.cells[0][0].outputValue;
		const exp = (expected instanceof CellValue) ? expected : CellValue.fromValue(expected);
		this.assertEqual(exp.type, actual.type);
		this.assertEqual(exp.decimals, actual.decimals);
		this.assertEqual(exp.formattedValue, actual.formattedValue);
		this.assertEqual(exp.value, actual.value);
	}

	test_func_abs() {
		this._test_simple_formula(3, '=ABS(-3)');
		this._test_simple_formula(4, '=ABS(4)');
	}

	test_func_and() {
		this._test_simple_formula(false, '=AND(FALSE, FALSE)');
		this._test_simple_formula(false, '=AND(FALSE, TRUE)');
		this._test_simple_formula(false, '=AND(TRUE, FALSE)');
		this._test_simple_formula(true, '=AND(TRUE, TRUE)');
		this._test_simple_formula(true, '=AND(TRUE, TRUE, TRUE, TRUE)');
		this._test_simple_formula(false, '=AND(TRUE, TRUE, TRUE, FALSE)');
	}

	test_func_average() {
		this._test_simple_formula(4, '=AVERAGE(4, 6, 2, 4)');
		this._test_simple_formula(4, '=AVERAGE(4, 6, 2, "foo", 4)');
	}

	test_func_ceiling() {
		this._test_simple_formula(4, '=CEILING(3.1)');
		this._test_simple_formula(3, '=CEILING(3)');
		this._test_simple_formula(-3, '=CEILING(-3.1)');
	}

	test_func_exp() {
		this._test_simple_formula(2.718281828459045, '=EXP(1)');
	}

	test_func_floor() {
		this._test_simple_formula(3, '=FLOOR(3.1)');
		this._test_simple_formula(3, '=FLOOR(3)');
		this._test_simple_formula(-4, '=FLOOR(-3.1)');
	}

	test_func_if() {
		this._test_simple_formula(6, '=IF(FALSE, 4, 6)');
		this._test_simple_formula(4, '=IF(TRUE, 4, 6)');
	}

	test_func_ifs() {
		this._test_simple_formula(1, '=IFS(TRUE, 1, FALSE, 2, FALSE, 3, FALSE, 4, 5)');
		this._test_simple_formula(2, '=IFS(FALSE, 1, TRUE, 2, FALSE, 3, FALSE, 4, 5)');
		this._test_simple_formula(3, '=IFS(FALSE, 1, FALSE, 2, TRUE, 3, FALSE, 4, 5)');
		this._test_simple_formula(4, '=IFS(FALSE, 1, FALSE, 2, FALSE, 3, TRUE, 4, 5)');
		this._test_simple_formula(5, '=IFS(FALSE, 1, FALSE, 2, FALSE, 3, FALSE, 4, 5)');
	}

	test_func_ln() {
		this._test_simple_formula(1, '=LN(2.718281828459045)');
	}

	test_func_log() {
		this._test_simple_formula(3, '=LOG(1000, 10)');
		this._test_simple_formula(6, '=LOG(64, 2)');
	}

	test_func_lower() {
		this._test_simple_formula('mixed', '=LOWER("MiXeD")');
	}

	test_func_max() {
		this._test_simple_formula(8, '=MAX(4, 8, 5, 2)');
	}

	test_func_min() {
		this._test_simple_formula(2, '=MIN(4, 8, 5, 2)');
	}

	test_func_mod() {
		this._test_simple_formula(1, '=MOD(37, 4)');
	}

	test_func_not() {
		this._test_simple_formula(false, '=NOT(TRUE)');
		this._test_simple_formula(true, '=NOT(FALSE)');
	}

	test_func_or() {
		this._test_simple_formula(false, '=OR(FALSE, FALSE)');
		this._test_simple_formula(true, '=OR(FALSE, TRUE)');
		this._test_simple_formula(true, '=OR(TRUE, FALSE)');
		this._test_simple_formula(true, '=OR(TRUE, TRUE)');
		this._test_simple_formula(true, '=OR(FALSE, FALSE, FALSE, TRUE)');
	}

	test_func_power() {
		this._test_simple_formula(8, '=POWER(2, 3)');
	}

	test_func_round() {
		this._test_simple_formula(3, '=ROUND(3.1)');
		this._test_simple_formula(4, '=ROUND(3.5)');
		this._test_simple_formula(4, '=ROUND(4)');
		this._test_simple_formula(-3, '=ROUND(-3.1)');
		this._test_simple_formula(-3, '=ROUND(-3.5)');
		this._test_simple_formula(-4, '=ROUND(-3.9)');
		this._test_simple_formula(3.1, '=ROUND(3.1415926535, 1)');
		this._test_simple_formula(3.14, '=ROUND(3.1415926535, 2)');
		this._test_simple_formula(30, '=ROUND(31.415926535, -1)');
	}

	test_func_sqrt() {
		this._test_simple_formula(4, '=SQRT(16)');
	}

	test_func_substitute() {
		this._test_simple_formula('cot sot on the mot', '=SUBSTITUTE("cat sat on the mat", "at", "ot")');
		this._test_simple_formula('cot sot on the mot', '=SUBSTITUTE("cAt saT on the mat", "at", "ot")');
		this._test_simple_formula('cot sot on the mot', '=SUBSTITUTE("c.*t s.*t on the m.*t", ".*t", "ot")');
	}

	test_func_sum() {
		this._test_simple_formula(15, '=SUM(1, 2, 3, 4, 5)');
	}

	test_func_upper() {
		this._test_simple_formula('MIXED', '=UPPER("mIxEd")');
	}

	test_func_xor() {
		this._test_simple_formula(false, '=XOR(FALSE, FALSE)');
		this._test_simple_formula(true, '=XOR(FALSE, TRUE)');
		this._test_simple_formula(true, '=XOR(TRUE, FALSE)');
		this._test_simple_formula(false, '=XOR(TRUE, TRUE)');
		this._test_simple_formula(true, '=XOR(FALSE, FALSE, TRUE)');
		this._test_simple_formula(false, '=XOR(TRUE, FALSE, TRUE)');
	}

	test_format() {
		this._test_simple_formula(new CellValue('2.718', 2.718281828459045, 'number', 3), '=2.718281828459045 ; number 3');
		this._test_simple_formula(new CellValue('271.83%', 2.718281828459045, 'percent', 2), '=2.718281828459045 ; percent 2');
		this._test_simple_formula(new CellValue('$2.72', 2.718281828459045, 'currency', 2), '=2.718281828459045 ; currency 2');
	}

	#iterateCharacters(formula, testFinal=false) {
		for (var i = 1; i < formula.length; i++) {
			const portion = formula.substring(0, i);
			const grid = new SpreadsheetGrid(1, 1);
			grid.cells[0][0].originalValue = CellValue.fromCellString(portion);
			const expressionSet = new CellExpressionSet(grid);
			expressionSet.calculateCells();
		}
		if (testFinal) {
			const grid = new SpreadsheetGrid(1, 1);
			grid.cells[0][0].originalValue = CellValue.fromCellString(formula);
			const expressionSet = new CellExpressionSet(grid);
			expressionSet.calculateCells();
			const actual = grid.cells[0][0].outputValue;
			if (actual === null) {
				this.fail(`Expected \"${formula}\" to evaluate to a value, got null`);
			} else if (actual.type === CellValue.TYPE_ERROR) {
				this.fail(`Expected \"${formula}\" to evaluate to a value, got error ${actual.value.message}`);
			}
		}
	}

	test_partialAndBrokenSyntax() {
		// Like `BrokenSyntaxTests`, this tries parsing a bunch of cell values
		// character by character like an author typing them in and makes sure
		// they don't throw exceptions. The `true` flag will do a simple check
		// that the final complete expression evaluates to something other than
		// `null` or an error.
		this.#iterateCharacters("123", true);
		this.#iterateCharacters("-123", true);
		this.#iterateCharacters("true", true);
		this.#iterateCharacters("false", true);
		this.#iterateCharacters("TrUe", true);
		this.#iterateCharacters("fAlSe", true);
		this.#iterateCharacters("$123.45", true);
		this.#iterateCharacters("-$123.45", true);
		this.#iterateCharacters("12.34%", true);
		this.#iterateCharacters("-12.34%", true);
		this.#iterateCharacters("'01234", true);
		this.#iterateCharacters("'  ", true);
		this.#iterateCharacters("Foo bar", true);
		this.#iterateCharacters("=====");
		this.#iterateCharacters("=1*2*3*4", true);
		this.#iterateCharacters("=1**2");
		this.#iterateCharacters("=**123");
		this.#iterateCharacters("=6+*3");
		this.#iterateCharacters("=)))");
		this.#iterateCharacters("=(3*)");
		this.#iterateCharacters("=(*3)");
		this.#iterateCharacters("=(((");
		this.#iterateCharacters("=\")");
		this.#iterateCharacters("=-123", true);
		this.#iterateCharacters("=--123");
		this.#iterateCharacters("=---123");
		this.#iterateCharacters("=-\"str\"");
		this.#iterateCharacters("=\"str\"&3", true);
		this.#iterateCharacters("=1<3", true);
		this.#iterateCharacters("=1<=3", true);
		this.#iterateCharacters("=1>3", true);
		this.#iterateCharacters("=1>=3", true);
		this.#iterateCharacters("=1==3", true);
		this.#iterateCharacters("=1!=3", true);
		this.#iterateCharacters("=>=3");
		this.#iterateCharacters("=<=3");
		this.#iterateCharacters("=!=3");
		this.#iterateCharacters("=MAX)");
		this.#iterateCharacters("=MAX(3,,)");
		this.#iterateCharacters("=MAX(\"a\",\"b\")");
		this.#iterateCharacters("=ZZ999");
		this.#iterateCharacters("=$ZZ999");
		this.#iterateCharacters("=ZZ$999");
		this.#iterateCharacters("=$ZZ$999");
		this.#iterateCharacters("=1+2", true);
		this.#iterateCharacters("=1-2", true);
		this.#iterateCharacters("=1*2", true);
		this.#iterateCharacters("=1/2", true);
		this.#iterateCharacters("=A1(1)");
		this.#iterateCharacters("=$A$1(1)");
	}
}