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