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", }); }
private static MySheet CreateMediumComplex() { MySheet ms = new MySheet(); // plain data in D1:F3 ms.SetCellValue("D1", 12); ms.SetCellValue("E1", 13); ms.SetCellValue("D2", 14); ms.SetCellValue("E2", 15); ms.SetCellValue("D3", 16); ms.SetCellValue("E3", 17); ms.SetCellFormula("C1", "SUM(D1:E2)"); ms.SetCellFormula("C2", "SUM(D2:E3)"); ms.SetCellFormula("C3", "SUM(D3:E4)"); ms.SetCellFormula("B1", "C2-C1"); ms.SetCellFormula("B2", "B3*C1-C2"); ms.SetCellFormula("B3", "2"); ms.SetCellFormula("A1", "MAX(B1:B2)"); ms.SetCellFormula("A2", "MIN(B3,D2:F2)"); ms.SetCellFormula("A3", "B3*C3"); // Clear all the logging from the above initialisation ms.GetAndClearLog(); ms.ClearAllCachedResultValues(); return(ms); }
public void TestBlankCellChangedToValueCell() { TestCases.CultureShim.SetCurrentCulture("en-US"); MySheet ms = new MySheet(); ms.SetCellFormula("A1", "B1+2.2"); ms.SetCellValue("B1", 1.5); ms.ClearAllCachedResultValues(); ms.ClearCell("B1"); ms.GetAndClearLog(); ConfirmEvaluate(ms, "A1", 2.2); ConfirmLog(ms, new String[] { "start A1 B1+2.2", "end A1 2.2", }); ms.SetCellValue("B1", 0.4); ConfirmLog(ms, new String[] { "changeFromBlank B1 0.4", "clear A1", }); ConfirmEvaluate(ms, "A1", 2.6); ConfirmLog(ms, new String[] { "start A1 B1+2.2", "hit B1 0.4", "end A1 2.6", }); }
public void TestBlankCellChangedToValueCell() { System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US"); MySheet ms = new MySheet(); ms.SetCellFormula("A1", "B1+2.2"); ms.SetCellValue("B1", 1.5); ms.ClearAllCachedResultValues(); ms.ClearCell("B1"); ms.GetAndClearLog(); ConfirmEvaluate(ms, "A1", 2.2); ConfirmLog(ms, new String[] { "start A1 B1+2.2", "end A1 2.2", }); ms.SetCellValue("B1", 0.4); ConfirmLog(ms, new String[] { "changeFromBlank B1 0.4", "clear A1", }); ConfirmEvaluate(ms, "A1", 2.6); ConfirmLog(ms, new String[] { "start A1 B1+2.2", "hit B1 0.4", "end A1 2.6", }); }
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", }); }
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", }); }
private static void ConfirmLog(MySheet ms, String[] expectedLog) { String[] actualLog = ms.GetAndClearLog(); int endIx = actualLog.Length; if (endIx != expectedLog.Length) { Console.Error.WriteLine("Log Lengths mismatch"); DumpCompare(expectedLog, actualLog); throw new AssertFailedException("Log Lengths mismatch"); } for (int i = 0; i < endIx; i++) { if (!actualLog[i].Equals(expectedLog[i])) { String msg = "Log entry mismatch at index " + i; Console.Error.WriteLine(msg); DumpCompare(expectedLog, actualLog); throw new AssertFailedException(msg); } } }
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", }); }
public void TestMediumComplex() { MySheet ms = CreateMediumComplex(); // completely fresh evaluation ConfirmEvaluate(ms, "A1", 46); ConfirmLog(ms, new String[] { "start A1 MAX(B1:B2)", "start B1 C2-C1", "start C2 SUM(D2:E3)", "value D2 14", "value E2 15", "value D3 16", "value E3 17", "end C2 62", "start C1 SUM(D1:E2)", "value D1 12", "value E1 13", "hit D2 14", "hit E2 15", "end C1 54", "end B1 8", "start B2 B3*C1-C2", "value B3 2", "hit C1 54", "hit C2 62", "end B2 46", "end A1 46", }); // simple cache hit - immediate re-evaluation with no changes ConfirmEvaluate(ms, "A1", 46); 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", }); }