testevaluationcache.java

来自「EXCEL read and write」· Java 代码 · 共 611 行 · 第 1/2 页

		confirmLog(ms, new String[] { "hit A1 46", });

		// change a low level cell
		ms.setCellValue("D1", 10);
		confirmLog(ms, new String[] {
				"clear D1 10",
				"clear1 C1 54",
				"clear2 B1 8",
				"clear3 A1 46",
				"clear2 B2 46",
		});
		confirmEvaluate(ms, "A1", 42);
		confirmLog(ms, new String[] {
			"start A1 MAX(B1:B2)",
				"start B1 C2-C1",
					"hit C2 62",
					"start C1 SUM(D1:E2)",
						"hit D1 10", "hit E1 13", "hit D2 14", "hit E2 15",
					"end C1 52",
				"end B1 10",
				"start B2 B3*C1-C2",
					"hit B3 2",
					"hit C1 52",
					"hit C2 62",
				"end B2 42",
			"end A1 42",
		});

		// Reset and try changing an intermediate value
		ms = createMediumComplex();
		confirmEvaluate(ms, "A1", 46);
		ms.getAndClearLog();

		ms.setCellValue("B3", 3); // B3 is in the middle of the dependency tree
		confirmLog(ms, new String[] {
				"clear B3 3",
				"clear1 B2 46",
				"clear2 A1 46",
		});
		confirmEvaluate(ms, "A1", 100);
		confirmLog(ms, new String[] {
			"start A1 MAX(B1:B2)",
				"hit B1 8",
				"start B2 B3*C1-C2",
					"hit B3 3",
					"hit C1 54",
					"hit C2 62",
				"end B2 100",
			"end A1 100",
		});
	}

	public void testMediumComplexWithDependencyChange() {

		// Changing an intermediate formula
		MySheet ms = createMediumComplex();
		confirmEvaluate(ms, "A1", 46);
		ms.getAndClearLog();
		ms.setCellFormula("B2", "B3*C2-C3"); // used to be "B3*C1-C2"
		confirmLog(ms, new String[] {
			"clear B2 46",
			"clear1 A1 46",
		});

		confirmEvaluate(ms, "A1", 91);
		confirmLog(ms, new String[] {
			"start A1 MAX(B1:B2)",
				"hit B1 8",
				"start B2 B3*C2-C3",
					"hit B3 2",
					"hit C2 62",
					"start C3 SUM(D3:E4)",
						"hit D3 16", "hit E3 17", 
//						"value D4 #BLANK#", "value E4 #BLANK#",
					"end C3 33",
				"end B2 91",
			"end A1 91",
		});

		//----------------
		// Note - From now on the demonstrated POI behaviour is not optimal
		//----------------

		// Now change a value that should no longer affect B2
		ms.setCellValue("D1", 11);
		confirmLog(ms, new String[] {
			"clear D1 11",
			"clear1 C1 54",
			// note there is no "clear2 B2 91" here because B2 doesn't depend on C1 anymore
			"clear2 B1 8",
			"clear3 A1 91",
		});

		confirmEvaluate(ms, "B2", 91);
		confirmLog(ms, new String[] {
			"hit B2 91",  // further confirmation that B2 was not cleared due to changing D1 above
		});

		// things should be back to normal now
		ms.setCellValue("D1", 11);
		confirmLog(ms, new String[] {  });
		confirmEvaluate(ms, "B2", 91);
		confirmLog(ms, new String[] {
			"hit B2 91",
		});
	}

	/**
	 * verifies that when updating a plain cell, depending (formula) cell cached values are cleared
	 * only when the plain cell's value actually changes
	 */
	public void testRedundantUpdate() {
		MySheet ms = new MySheet();

		ms.setCellValue("B1", 12);
		ms.setCellValue("C1", 13);
		ms.setCellFormula("A1", "B1+C1");

		// evaluate twice to confirm caching looks OK
		ms.evaluateCell("A1");
		ms.getAndClearLog();
		confirmEvaluate(ms, "A1", 25);
		confirmLog(ms, new String[] {
			"hit A1 25",
		});

		// Make redundant update, and check re-evaluation
		ms.setCellValue("B1", 12); // value didn't change
		confirmLog(ms, new String[] {});
		confirmEvaluate(ms, "A1", 25);
		confirmLog(ms, new String[] {
			"hit A1 25",
		});

		ms.setCellValue("B1", 11); // value changing
		confirmLog(ms, new String[] {
			"clear B1 11",
			"clear1 A1 25",	// expect consuming formula cached result to get cleared
		});
		confirmEvaluate(ms, "A1", 24);
		confirmLog(ms, new String[] {
			"start A1 B1+C1",
			"hit B1 11",
			"hit C1 13",
			"end A1 24",
		});
	}

	/**
	 * Changing any input to a formula may cause the formula to 'use' a different set of cells.
	 * Functions like INDEX and OFFSET make this effect obvious, with functions like MATCH
	 * and VLOOKUP the effect can be subtle.  The presence of error values can also produce this
	 * effect in almost every function and operator.
	 */
	public void testSimpleWithDependencyChange() {

		MySheet ms = new MySheet();

		ms.setCellFormula("A1", "INDEX(C1:E1,1,B1)");
		ms.setCellValue("B1", 1);
		ms.setCellValue("C1", 17);
		ms.setCellValue("D1", 18);
		ms.setCellValue("E1", 19);
		ms.clearAllCachedResultValues();
		ms.getAndClearLog();

		confirmEvaluate(ms, "A1", 17);
		confirmLog(ms, new String[] {
			"start A1 INDEX(C1:E1,1,B1)",
			"value B1 1",
			"value C1 17",
			"end A1 17",
		});
		ms.setCellValue("B1", 2);
		ms.getAndClearLog();

		confirmEvaluate(ms, "A1", 18);
		confirmLog(ms, new String[] {
			"start A1 INDEX(C1:E1,1,B1)",
			"hit B1 2",
			"value D1 18",
			"end A1 18",
		});

		// change C1. Note - last time A1 evaluated C1 was not used
		ms.setCellValue("C1", 15);
		ms.getAndClearLog();
		confirmEvaluate(ms, "A1", 18);
		confirmLog(ms, new String[] {
			"hit A1 18",
		});

		// but A1 still uses D1, so if it changes...
		ms.setCellValue("D1", 25);
		ms.getAndClearLog();
		confirmEvaluate(ms, "A1", 25);
		confirmLog(ms, new String[] {
			"start A1 INDEX(C1:E1,1,B1)",
			"hit B1 2",
			"hit D1 25",
			"end A1 25",
		});
	}

	public void testBlankCells() {


		MySheet ms = new MySheet();

		ms.setCellFormula("A1", "sum(B1:D4,B5:E6)");
		ms.setCellValue("B1", 12);
		ms.clearAllCachedResultValues();
		ms.getAndClearLog();

		confirmEvaluate(ms, "A1", 12);
		confirmLog(ms, new String[] {
			"start A1 SUM(B1:D4,B5:E6)",
			"value B1 12",
			"end A1 12",
		});
		ms.setCellValue("B6", 2);
		ms.getAndClearLog();

		confirmEvaluate(ms, "A1", 14);
		confirmLog(ms, new String[] {
			"start A1 SUM(B1:D4,B5:E6)",
			"hit B1 12",
			"hit B6 2",
			"end A1 14",
		});
		ms.setCellValue("E4", 2);
		ms.getAndClearLog();

		confirmEvaluate(ms, "A1", 14);
		confirmLog(ms, new String[] {
			"hit A1 14",
		});
		
		ms.setCellValue("D1", 1);
		ms.getAndClearLog();

		confirmEvaluate(ms, "A1", 15);
		confirmLog(ms, new String[] {
			"start A1 SUM(B1:D4,B5:E6)",
			"hit B1 12",
			"hit D1 1",
			"hit B6 2",
			"end A1 15",
		});
	}
	
	private static void confirmEvaluate(MySheet ms, String cellRefText, double expectedValue) {
		ValueEval v = ms.evaluateCell(cellRefText);
		assertEquals(NumberEval.class, v.getClass());
		assertEquals(expectedValue, ((NumberEval)v).getNumberValue(), 0.0);
	}

	private static void confirmLog(MySheet ms, String[] expectedLog) {
		String[] actualLog = ms.getAndClearLog();
		int endIx = actualLog.length;
		PrintStream ps = System.err;
		if (endIx != expectedLog.length) {
			ps.println("Log lengths mismatch");
			dumpCompare(ps, expectedLog, actualLog);
			throw new AssertionFailedError("Log lengths mismatch");
		}
		for (int i=0; i< endIx; i++) {
			if (!actualLog[i].equals(expectedLog[i])) {
				String msg = "Log entry mismatch at index " + i;
				ps.println(msg);
				dumpCompare(ps, expectedLog, actualLog);
				throw new AssertionFailedError(msg);
			}
		}

	}

	private static void dumpCompare(PrintStream ps, String[] expectedLog, String[] actualLog) {
		int max = Math.max(actualLog.length, expectedLog.length);
		ps.println("Index\tExpected\tActual");
		for(int i=0; i<max; i++) {
			ps.print(i + "\t");
			printItem(ps, expectedLog, i);
			ps.print("\t");
			printItem(ps, actualLog, i);
			ps.println();
		}
		ps.println();
		debugPrint(ps, actualLog);
	}

	private static void printItem(PrintStream ps, String[] ss, int index) {
		if (index < ss.length) {
			ps.print(ss[index]);
		}
	}

	private static void debugPrint(PrintStream ps, String[] log) {
		for (int i = 0; i < log.length; i++) {
			ps.println('"' + log[i] + "\",");

		}
	}
}