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 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", }); }
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", }); }
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", }); }