public void Test2NodesCycle()
{
wb = editor.CreateWorkbook("2NodesCycle");
Assert.NotNull(wb);
ws = wb.ActiveWorksheet;
CellsUpdatedListener listener = new CellsUpdatedListener(ws);
ErrorListener err_listener = new ErrorListener(editor);
ws.Range("E6").Formula = "F4+D9";
listener.Reset();
ws.Range("F4").Formula = "4+E6+C7";
Assert.Equals(2, listener.UpdatedCellsList.Count);
listener.Reset();
Assert.Equals(ErrorValue.bad_reference, ws.Range("F4").Value);
Assert.Equals(ErrorValue.bad_reference, ws.Range("E6").Value);
ws.Range("E6").Formula = "4+C7+D9"; // Break the cycle
Assert.Equals(2, listener.UpdatedCellsList.Count);
listener.Reset();
Assert.Equals(4, ws.Range("E6").Value);
Assert.Equals(8, ws.Range("F4").Value);
Assert.Equals(0, err_listener.AllHappenedErrors.Count);
}
public void TestBowCycle()
{
wb = editor.CreateWorkbook("BowCycle");
Assert.NotNull(wb);
ws = wb.ActiveWorksheet;
CellsUpdatedListener listener = new CellsUpdatedListener(ws);
ErrorListener err_listener = new ErrorListener(editor);
ws.Range("B4").Formula = "C2 + 1";
ws.Range("E5").Formula = "D2 + 1";
ws.Range("D7").Formula = "B4+E5";
listener.Reset();
ws.Range("C2:D2").Formula = "$D$7 + 1"; // 2 cycles
Assert.Equals(5, listener.UpdatedCellsList.Count);
listener.Reset();
Assert.Equals(ErrorValue.bad_reference, ws.Range("C2:D2").Value);
Assert.Equals(ErrorValue.bad_reference, ws.Range("B4").Value);
Assert.Equals(ErrorValue.bad_reference, ws.Range("E5").Value);
Assert.Equals(ErrorValue.bad_reference, ws.Range("D7").Value);
ws.Range("D7").Value = 4; // Break the cycle
Assert.Equals(5, listener.UpdatedCellsList.Count);
listener.Reset();
Assert.Equals(5, ws.Range("C2:D2").Value);
Assert.Equals(6, ws.Range("B4").Value);
Assert.Equals(6, ws.Range("E5").Value);
Assert.Equals(0, err_listener.AllHappenedErrors.Count);
}
public void TestMerge()
{
wb = editor.LoadWorkbook("../../TestFiles/MergedAreas.xlsx");
Assert.NotNull(wb);
Worksheet ws = wb.Sheets["Лист1"] as Worksheet;
CellsUpdatedListener listener = new CellsUpdatedListener(ws);
ErrorListener err_listener = new ErrorListener(editor);
ws.Range("I4:I6").Merge();
Assert.Equals(11, ws.GetMergedAreas().Count);
Assert.Equals(11, listener.UpdatedCellsList.Count);
Assert.Equals(1, ws.Range("I4").Value);
Assert.Equals("H4", ws.Range("I4").Formula);
Assert.Equals("", ws.Range("I5").Value);
Assert.Equals("", ws.Range("I5").Formula);
listener.Reset();
ws.Range("I4:I6").UnMerge();
Assert.Equals(0, listener.UpdatedCellsList.Count);
listener.Reset();
ws.Range("H6:I6").Merge();
Assert.Equals(11, ws.GetMergedAreas().Count);
Assert.Equals(8, listener.UpdatedCellsList.Count);
Assert.Equals(3, ws.Range("H6").Value);
Assert.Equals("H4+H5", ws.Range("H6").Formula);
listener.Reset();
// Cyclic
ws.Range("I5:J5").Merge();
Assert.Equals(12, ws.GetMergedAreas().Count);
Assert.Equals(9, listener.UpdatedCellsList.Count);
Assert.Equals(ErrorValue.bad_reference, ws.Range("I5").Value);
Assert.Equals(ErrorValue.bad_reference, ws.Range("H5").Value);
Assert.Equals(ErrorValue.bad_reference, ws.Range("K4:K6").Value);
listener.Reset();
ws.Range("G13:G14").Merge();
Assert.Equals(11, ws.GetMergedAreas().Count);
Assert.Equals("F12:G15", ws.GetMergedAreas()[10]);
Assert.Equals(1, ws.Range("F12").Value);
Assert.Equals("", ws.Range("G12").Value);
Assert.Equals("", ws.Range("F13:G15").Value);
ws.Range("D16:F16").Merge();
Assert.Equals(8, ws.GetMergedAreas().Count);
Assert.Equals("B12:G20", ws.GetMergedAreas()[7]);
Assert.Equals(1, ws.Range("B12").Value);
Assert.Equals("", ws.Range("B13:G20").Value);
ws.Range("E5:F5").Merge();
Assert.Equals(4, ws.GetMergedAreas().Count);
Assert.Equals("A2:G8", ws.GetMergedAreas()[3]);
Assert.Equals(-625, ws.Range("A2").Value);
Assert.Equals("C2-25^2", ws.Range("A2").Formula);
Assert.Equals(0, err_listener.AllHappenedErrors.Count);
wb.Save("../../TestFiles/MergedAreas_res1.xlsx");
}
public void TestSimpleReferencesMovingOnColumnInsert()
{
Workbook wb = editor.CreateWorkbook("TestSimpleReferencesMovingOnColumnInsert");
Assert.NotNull(wb);
Worksheet ws = wb.ActiveSheet as Worksheet;
ErrorListener err_listener = new ErrorListener(editor);
CellsUpdatedListener listener = new CellsUpdatedListener(ws);
ws.Range("B1:G2").Value = 5;
ws.Range("D3").Formula = "COUNT(D1:G2)";
ws.Range("E3").Formula = "COUNT($E$1:$G$2)";
ws.Range("A3").Formula = "COUNT(E1:$G$2)";
listener.Reset();
ws.Range("D1").InsertColumn();
Assert.Equals("COUNT(E1:H2)", ws.Range("E3").Formula);
Assert.Equals(8, ws.Range("E3").Value);
Assert.Equals("COUNT($F$1:$H$2)", ws.Range("F3").Formula);
Assert.Equals(6, ws.Range("F3").Value);
Assert.Equals("COUNT(F1:$H$2)", ws.Range("A3").Formula);
Assert.Equals(6, ws.Range("A3").Value);
Assert.Equals(14, listener.UpdatedCellsList.Count);
Assert.Equals(0, err_listener.AllHappenedErrors.Count);
}
public void TestSimpleReferencesMovingOnRowInsert()
{
Workbook wb = editor.CreateWorkbook("TestSimpleReferencesMovingOnRowInsert");
Assert.NotNull(wb);
Worksheet ws = wb.ActiveSheet as Worksheet;
ErrorListener err_listener = new ErrorListener(editor);
CellsUpdatedListener listener = new CellsUpdatedListener(ws);
ws.Range("B1:C10").Value = 5;
ws.Range("D5").Formula = "COUNT(B5:C10)";
ws.Range("D6").Formula = "COUNT($B$6:$C$10)";
ws.Range("D1").Formula = "COUNT(B6:$C$10)";
listener.Reset();
ws.Range("5:500").InsertRows(4);
Assert.Equals("COUNT(B9:C14)", ws.Range("D9").Formula);
Assert.Equals(12, ws.Range("D9").Value);
Assert.Equals("COUNT($B$10:$C$14)", ws.Range("D10").Formula);
Assert.Equals(10, ws.Range("D10").Value);
Assert.Equals("COUNT(B10:$C$14)", ws.Range("D1").Formula);
Assert.Equals(10, ws.Range("D1").Value);
Assert.Equals(25, listener.UpdatedCellsList.Count);
Assert.Equals(0, err_listener.AllHappenedErrors.Count);
}
public void TestOverlappedCopy()
{
PrepareSimpleData();
CellsUpdatedListener listener = new CellsUpdatedListener(ws);
ErrorListener err_listener = new ErrorListener(editor);
ws.GetUpdatedCells();
listener.Reset();
ws.Range("C4:F14").CopyTo(ws.Range("C4:F14"));
Assert.Equals(0, listener.UpdatedCellsList.Count);
ws.Range("C4:F14").CopyTo(ws.Range("E2:H12"));
Assert.Equals(44, listener.UpdatedCellsList.Count);
wb.Save("../../TestFiles/Copy_overlapped.xlsx");
Assert.Equals(5, ws.Range("E2").Value);
Assert.Equals(BorderStyle.thin, ws.Range("E2").Format.Borders.Left.Style);
Assert.Equals(BorderStyle.thin, ws.Range("E2").Format.Borders.Top.Style);
Assert.Equals("wtf", ws.Range("E3").Value);
Assert.Equals(BorderStyle.none, ws.Range("E3").Format.Borders.Bottom.Style);
Assert.Equals(20, ws.Range("E4").Value);
Assert.Equals("storage", ws.Range("E12").Value);
Assert.Equals(BorderStyle.thin, ws.Range("E12").Format.Borders.Bottom.Style);
Assert.Equals("", ws.Range("E11").Value);
Assert.Equals(456.123, ws.Range("F6").Value);
Assert.Equals(0, err_listener.AllHappenedErrors.Count);
}
public void TestUseOfCycledValues()
{
wb = editor.CreateWorkbook("UseOfCycledValues");
Assert.NotNull(wb);
ws = wb.ActiveWorksheet;
CellsUpdatedListener listener = new CellsUpdatedListener(ws);
ErrorListener err_listener = new ErrorListener(editor);
ws.Range("F4").Value = 1;
ws.Range("E6").Formula = "F4+D9";
ws.Range("D4").Formula = "F4+E6+C7";
ws.Range("C7").Formula = "E6+1";
Assert.Equals(1, ws.Range("E6").Value);
Assert.Equals(4, ws.Range("D4").Value);
Assert.Equals(2, ws.Range("C7").Value);
listener.Reset();
ws.Range("D9").Formula = "C7+2"; // Cycle
Assert.Equals(4, listener.UpdatedCellsList.Count);
listener.Reset();
Assert.Equals(ErrorValue.bad_reference, ws.Range("D4").Value); // This uses the cycled values
Assert.Equals(ErrorValue.bad_reference, ws.Range("E6").Value);
Assert.Equals(ErrorValue.bad_reference, ws.Range("C7").Value);
Assert.Equals(ErrorValue.bad_reference, ws.Range("D9").Value);
ws.Range("F4").Value = 4; // Pull the tail
Assert.Equals(5, listener.UpdatedCellsList.Count);
listener.Reset();
Assert.Equals(ErrorValue.bad_reference, ws.Range("D4").Value); // This uses the cycled values
Assert.Equals(ErrorValue.bad_reference, ws.Range("E6").Value);
Assert.Equals(ErrorValue.bad_reference, ws.Range("C7").Value);
Assert.Equals(ErrorValue.bad_reference, ws.Range("D9").Value);
Assert.Equals(0, err_listener.AllHappenedErrors.Count);
}
public void TestCopyWithinOneSheet()
{
PrepareSimpleData();
CellsUpdatedListener listener = new CellsUpdatedListener(ws);
ErrorListener err_listener = new ErrorListener(editor);
ws.GetUpdatedCells();
listener.Reset();
ws.Range("I10").Formula = "B4+A3";
ws.Range("I10").Format.Font.Bold = true;
listener.Reset();
//wb.Save("../../TestFiles/Simple_sort_before.xlsx");
ws.Range("C4:F14").CopyTo(ws.Range("H9:H23"));
Assert.Equals(44, listener.UpdatedCellsList.Count);
wb.Save("../../TestFiles/Copy_inside_sheet_forward.xlsx");
Assert.Equals(5, ws.Range("H9").Value);
Assert.Equals(true, ws.Range("I9").Value);
Assert.Equals("wtf", ws.Range("H10").Value);
Assert.Equals("", ws.Range("I10").Value);
Assert.Equals("", ws.Range("I10").Formula);
Assert.Equals(false, ws.Range("I10").Format.Font.Bold);
Assert.Equals(BorderStyle.dotted, ws.Range("J11").Format.Borders.Bottom.Style);
Assert.Equals(BorderStyle.thin, ws.Range("H15").Format.Borders.Left.Style);
Assert.Equals("", ws.Range("K18").Value);
Assert.Equals(BorderStyle.none, ws.Range("K18").Format.Borders.Right.Style);
ws.Range("H19:K19").CopyTo(ws.Range("A14"));
Assert.Equals(BorderStyle.none, ws.Range("D14").Format.Borders.Bottom.Style);
Assert.Equals("", ws.Range("C14").Value);
Assert.Equals("storage", ws.Range("A14").Value);
Assert.Equals(BorderStyle.thin, ws.Range("A14").Format.Borders.Bottom.Style);
Assert.Equals(0, err_listener.AllHappenedErrors.Count);
}
public void TestNumberFormatSetGet()
{
Workbook wb = editor.LoadWorkbook("../../TestFiles/NumberFormat.xlsx");
Assert.NotNull(wb);
Worksheet ws = wb.ActiveSheet as Worksheet;
CellsUpdatedListener listener = new CellsUpdatedListener(ws);
ErrorListener err_listener = new ErrorListener(editor);
Assert.Less(163, ws.Range("C2").Format.NumberFormatId);
Assert.Equals(10, ws.Range("B5").Format.NumberFormatId);
Assert.Equals(14, ws.Range("B7").Format.NumberFormatId);
ws.Range("C1").Format.NumberFormatId = 9;
Assert.Equals(9, ws.Range("C1").Format.NumberFormatId);
ws.Range("B1").Format.NumberFormatId = 164;
Assert.Equals(164, ws.Range("B1").Format.NumberFormatId);
ws.Range("B1").Format.NumberFormatId = 300;
Assert.Equals(164, ws.Range("B1").Format.NumberFormatId);
ws.Range("B2").Format.NumberFormatCode = "0.00-00";
Assert.Less(163, ws.Range("B2").Format.NumberFormatId);
Assert.Equals("0.00-00", ws.Range("B2").Format.NumberFormatCode);
ws.Range("B3").Format.NumberFormatCode = "0.00-00";
Assert.Equals(ws.Range("B2").Format.NumberFormatId, ws.Range("B3").Format.NumberFormatId);
Assert.Equals(ws.Range("B2").Format.NumberFormatId, ws.Range("B3").Format.NumberFormatId);
Assert.Equals(ws.Range("B2").Format.NumberFormatCode, ws.Range("B3").Format.NumberFormatCode);
ws.Range("B3").Format.NumberFormatCode = "0.00";
Assert.Equals(2, ws.Range("B3").Format.NumberFormatId);
ws.Range("B3").Format.NumberFormatId = 40;
Assert.Equals("#,##0.00;[Red](#,##0.00)", ws.Range("B3").Format.NumberFormatCode);
ws.Range("B3").Format.NumberFormatCode = "\"$ \"0.00";
Assert.Equals("\"$ \"0.00", ws.Range("B3").Format.NumberFormatCode);
listener.Reset();
ws.Range("A1:D3").Format.NumberFormatId = 0;
Assert.Equals(12, listener.UpdatedCellsList.Count);
wb.Save("../../TestFiles/NumberFormat_result.xlsx");
Assert.Null(err_listener.LastError);
}
public void TestCellsUpdatedEvent2()
{
Workbook wb = editor.LoadWorkbook("../../TestFiles/Formula_complex_dependencies.xlsx");
Assert.NotNull(wb);
Worksheet ws = wb.Sheets["Лист1"] as Worksheet;
CellsUpdatedListener listener = new CellsUpdatedListener(ws);
ws.Range("A1").Formula = "2";
Assert.Equals(2, listener.UpdatedCellsList.Count);
Assert.Equals("A1", listener.UpdatedCellsList[0]);
Assert.Equals("B1", listener.UpdatedCellsList[1]);
listener.Reset();
ws.Range("B4").Value = 3;
Assert.Equals(7, listener.UpdatedCellsList.Count);
Assert.Equals("B1", listener.UpdatedCellsList[2]);
Assert.Equals("D1", listener.UpdatedCellsList[4]);
Assert.Equals("B2", listener.UpdatedCellsList[0]);
Assert.Equals("C2", listener.UpdatedCellsList[1]);
Assert.Equals("C3", listener.UpdatedCellsList[6]);
Assert.Equals("B4", listener.UpdatedCellsList[5]);
Assert.Equals("D4", listener.UpdatedCellsList[3]);
listener.Reset();
ws.Range("AC1").Value = 4;
Assert.Equals(6, listener.UpdatedCellsList.Count);
Assert.Equals("AC1", listener.UpdatedCellsList[0]);
Assert.Equals("AB2", listener.UpdatedCellsList[2]);
Assert.Equals("AE4", listener.UpdatedCellsList[5]);
listener.Reset();
ws.Range("B7").Value = 3;
Assert.Equals(6, listener.UpdatedCellsList.Count);
Assert.Equals("A7", listener.UpdatedCellsList[1]);
Assert.Equals("B7", listener.UpdatedCellsList[4]);
Assert.Equals("B8", listener.UpdatedCellsList[0]);
listener.Reset();
ws.Range("A13").Value = 2;
Assert.Equals(97, listener.UpdatedCellsList.Count);
listener.Reset();
ws.Range("D13").Value = 2;
Assert.Equals(97, listener.UpdatedCellsList.Count);
listener.Reset();
wb.Close();
}
public void TestSimpleSort()
{
PrepareSimpleData();
CellsUpdatedListener listener = new CellsUpdatedListener(ws);
ErrorListener err_listener = new ErrorListener(editor);
ws.GetUpdatedCells();
listener.Reset();
wb.Save("../../TestFiles/Simple_sort_before.xlsx");
ws.Range("C4:F14").Sort(SortOrientation.by_rows, ws.Range("D4"), SortOrder.ascending);
Assert.Equals(44, listener.UpdatedCellsList.Count);
wb.Save("../../TestFiles/Simple_sort_after.xlsx");
Assert.Equals(456.123, ws.Range("D5").Value);
Assert.Equals(456.124, ws.Range("F5").Value);
Assert.Equals(20, ws.Range("C8").Value);
Assert.Equals("wtf", ws.Range("D8").Value);
Assert.Equals(ErrorValue.bad_reference, ws.Range("F11").Value);
Assert.Equals(ErrorValue.bad_reference, ws.Range("E8").Value);
Assert.Equals(ErrorValue.bad_reference, ws.Range("C5").Value);
Assert.Equals(ErrorValue.bad_reference, ws.Range("C11").Value);
Assert.Equals("", ws.Range("F9").Value);
Assert.Equals("", ws.Range("E9").Value);
Assert.Equals("", ws.Range("E6").Value);
Assert.Equals("", ws.Range("E11").Value);
Assert.Equals("", ws.Range("E12").Value);
Assert.Equals("", ws.Range("D12").Value);
Assert.Equals("", ws.Range("D13").Value);
Assert.Equals("", ws.Range("C12").Value);
Assert.Equals("яяя", ws.Range("F12").Value);
Assert.Equals(true, ws.Range("E14").Value);
Assert.Equals("wtf", ws.Range("C14").Value);
Assert.Equals("storage", ws.Range("C9").Value);
Assert.Equals(0, err_listener.AllHappenedErrors.Count);
}
public void TestValueSetNull()
{
System.Console.WriteLine("TestValueSetNull()");
Workbook wb = editor.CreateWorkbook("Book1");
Assert.NotNull(wb);
Worksheet ws = wb.ActiveSheet as Worksheet;
ErrorListener err_listener = new ErrorListener(editor);
CellsUpdatedListener listener = new CellsUpdatedListener(ws);
ws.Range("A3").Formula = "A1 + A2 + B1 + B2";
Assert.Equals(1, listener.UpdatedCellsList.Count);
listener.Reset();
ws.Range("B1:B2").Formula = "A$1 + A$2";
Assert.Equals(3, listener.UpdatedCellsList.Count);
listener.Reset();
ws.Range("A1:A2").Value = 5;
Assert.Equals(5, listener.UpdatedCellsList.Count); // A1, A2, B1, B2, A3
listener.Reset();
ws.Range("B1").Value = null;
Assert.Equals(2, listener.UpdatedCellsList.Count); // B1, A3
listener.Reset();
ws.Range("A1").Value = 3;
Assert.Equals(3, listener.UpdatedCellsList.Count); // A1, B2, A3
listener.Reset();
ws.Range("B2").Value = "";
Assert.Equals(2, listener.UpdatedCellsList.Count); // B2, A3
listener.Reset();
ws.Range("A1").Value = 4;
Assert.Equals(2, listener.UpdatedCellsList.Count); // A1, A3
listener.Reset();
Assert.Equals(0, err_listener.AllHappenedErrors.Count);
wb.Close();
}
public void TestRemoveColumnsComplex()
{
Workbook wb = editor.LoadWorkbook("../../TestFiles/DeleteRowsComplex.xlsx");
Assert.NotNull(wb);
Worksheet ws = wb.ActiveSheet as Worksheet;
ErrorListener err_listener = new ErrorListener(editor);
CellsUpdatedListener listener = new CellsUpdatedListener(ws);
ws.Range("F:J").DeleteColumns();
Assert.Equals(26, listener.UpdatedCellsList.Count);
listener.Reset();
Assert.Equals("D4+E4", ws.Range("D6").Formula);
Assert.Equals(3, ws.Range("D6").Value);
Assert.Equals("E4+#REF!+E16+C16", ws.Range("E6").Formula);
Assert.Equals(ErrorValue.bad_reference, ws.Range("E6").Value);
Assert.Equals("I15*5", ws.Range("I5").Formula);
Assert.Equals(ErrorValue.bad_reference, ws.Range("I5").Value);
Assert.Equals("B16-C16^2+C14", ws.Range("B14").Formula);
Assert.Equals(-4421, ws.Range("B14").Value);
Assert.Equals("#REF!-#REF!", ws.Range("F14").Formula);
Assert.Equals(ErrorValue.bad_reference, ws.Range("F14").Value);
Assert.Equals("G11*5", ws.Range("G15").Formula);
Assert.Equals(ErrorValue.bad_reference, ws.Range("G15").Value);
Assert.Equals("G15*5", ws.Range("I15").Formula);
Assert.Equals(ErrorValue.bad_reference, ws.Range("I15").Value);
Assert.Equals("C16+1", ws.Range("B16").Formula);
Assert.Equals(68, ws.Range("B16").Value);
Assert.Equals("#REF!/5", ws.Range("F16").Formula);
Assert.Equals(ErrorValue.bad_reference, ws.Range("F16").Value);
Assert.Equals("D16+E16", ws.Range("E18").Formula);
Assert.Equals(137, ws.Range("E18").Value);
Assert.Equals("E11-#REF!/5", ws.Range("C10").Formula);
Assert.Equals(ErrorValue.bad_reference, ws.Range("C10").Value);
Assert.Equals(0, err_listener.AllHappenedErrors.Count);
}
public void TestFormulaReregistration()
{
System.Console.WriteLine("TestFormulaReregistration()");
Workbook wb = editor.CreateWorkbook("Book1");
Assert.NotNull(wb);
Worksheet ws = wb.ActiveSheet as Worksheet;
ErrorListener err_listener = new ErrorListener(editor);
CellsUpdatedListener listener = new CellsUpdatedListener(ws);
ws.Range("A3").Formula = "A1 + A2 + B1 + B2";
Assert.Equals(1, listener.UpdatedCellsList.Count);
listener.Reset();
ws.Range("B1:B2").Formula = "A$1 + A$2";
Assert.Equals(3, listener.UpdatedCellsList.Count);
listener.Reset();
ws.Range("A1:A2").Value = 5;
Assert.Equals(5, listener.UpdatedCellsList.Count);
listener.Reset();
ws.Range("B1").Formula = "A$1 + A$2"; // Reregister 1
Assert.Equals(2, listener.UpdatedCellsList.Count);
listener.Reset();
ws.Range("A1").Value = 3; // Check 1
Assert.Equals(4, listener.UpdatedCellsList.Count);
listener.Reset();
ws.Range("A3").Formula = "A1 + A2 + B1 + B2"; // Reregister 2
Assert.Equals(1, listener.UpdatedCellsList.Count);
listener.Reset();
ws.Range("A2").Value = 4; // Check 2
Assert.Equals(4, listener.UpdatedCellsList.Count);
listener.Reset();
Assert.Equals(0, err_listener.AllHappenedErrors.Count);
wb.Close();
}
public void TestAdjacentAndIntersectedReferencesMovedOnRowInsert()
{
Workbook wb = editor.CreateWorkbook("TestAdjacentReferencesMovedOnRowInsert");
Assert.NotNull(wb);
Worksheet ws = wb.ActiveSheet as Worksheet;
ErrorListener err_listener = new ErrorListener(editor);
CellsUpdatedListener listener = new CellsUpdatedListener(ws);
ws.Range("B1:C10").Value = 5;
ws.Range("D5").Formula = "SUM(B2:C4)+B$5";
ws.Range("D6").Formula = "SUM($B$5:$C$10)";
ws.Range("D4").Formula = "SUM(B6:$C$10)";
ws.Range("D3").Formula = "SUM(B4:$C5)-B1";
ws.Range("D7").Formula = "SUM(B$1:C$80)";
ws.Range("D8").Formula = "SUM(B:C)";
listener.Reset();
ws.Range("5:5").InsertRows(4);
Assert.Equals("SUM(B2:C4)+B$9", ws.Range("D9").Formula);
Assert.Equals(35, ws.Range("D9").Value);
Assert.Equals("SUM($B$9:$C$14)", ws.Range("D10").Formula);
Assert.Equals(60, ws.Range("D10").Value);
Assert.Equals("SUM(B10:$C$14)", ws.Range("D4").Formula);
Assert.Equals(50, ws.Range("D4").Value);
Assert.Equals("SUM(B4:$C9)-B1", ws.Range("D3").Formula);
Assert.Equals(15, ws.Range("D3").Value);
Assert.Equals("SUM(B$1:C$84)", ws.Range("D11").Formula);
Assert.Equals(100, ws.Range("D11").Value);
Assert.Equals("SUM(B:C)", ws.Range("D12").Formula);
Assert.Equals(100, ws.Range("D12").Value);
Assert.Equals(30, listener.UpdatedCellsList.Count);
Assert.Equals(0, err_listener.AllHappenedErrors.Count);
}
public void TestAdjacentAndIntersectedReferencesMovedOnColumnInsert()
{
Workbook wb = editor.CreateWorkbook("TestAdjacentAndIntersectedReferencesMovedOnColumnInsert");
Assert.NotNull(wb);
Worksheet ws = wb.ActiveSheet as Worksheet;
ErrorListener err_listener = new ErrorListener(editor);
CellsUpdatedListener listener = new CellsUpdatedListener(ws);
ws.Range("B1:J7").Value = 5;
ws.Range("A8").Formula = "SUM(B1:E1)";
ws.Range("A9").Formula = "SUM(B2:F2)";
ws.Range("A10").Formula = "SUM(E3:J3)";
ws.Range("A11").Formula = "SUM(F4:J4)";
ws.Range("A12").Formula = "SUM(E5:F6)";
ws.Range("A13").Formula = "SUM(7:7)";
listener.Reset();
ws.Range("F1").InsertColumns(2);
Assert.Equals("SUM(B1:E1)", ws.Range("A8").Formula);
Assert.Equals(20, ws.Range("A8").Value);
Assert.Equals("SUM(B2:H2)", ws.Range("A9").Formula);
Assert.Equals(25, ws.Range("A9").Value);
Assert.Equals("SUM(E3:L3)", ws.Range("A10").Formula);
Assert.Equals(30, ws.Range("A10").Value);
Assert.Equals("SUM(H4:L4)", ws.Range("A11").Formula);
Assert.Equals(25, ws.Range("A11").Value);
Assert.Equals("SUM(E5:H6)", ws.Range("A12").Formula);
Assert.Equals(20, ws.Range("A12").Value);
Assert.Equals("SUM(7:7)", ws.Range("A13").Formula);
Assert.Equals(45, ws.Range("A13").Value);
Assert.Equals(54, listener.UpdatedCellsList.Count);
Assert.Equals(0, err_listener.AllHappenedErrors.Count);
}
public void TestRemoveRowsComplex()
{
Workbook wb = editor.LoadWorkbook("../../TestFiles/DeleteRowsComplex.xlsx");
Assert.NotNull(wb);
Worksheet ws = wb.ActiveSheet as Worksheet;
ErrorListener err_listener = new ErrorListener(editor);
CellsUpdatedListener listener = new CellsUpdatedListener(ws);
ws.Range("A8:A12").DeleteRows();
Assert.Equals(46, listener.UpdatedCellsList.Count);
listener.Reset();
Assert.Equals("D4+E4", ws.Range("D6").Formula);
Assert.Equals(3, ws.Range("D6").Value);
Assert.Equals("E4+F4+E11+C11", ws.Range("E6").Formula);
Assert.Equals(141, ws.Range("E6").Value);
Assert.Equals("G4+#REF!+G11", ws.Range("G6").Formula);
Assert.Equals(ErrorValue.bad_reference, ws.Range("G6").Value);
Assert.Equals("N10*5", ws.Range("N5").Formula);
Assert.Equals(ErrorValue.bad_reference, ws.Range("N5").Value);
Assert.Equals("B11-C11^2+C9", ws.Range("B9").Formula);
Assert.Equals(-4421, ws.Range("B9").Value);
Assert.Equals("J13-J11", ws.Range("K9").Formula);
Assert.Equals(-261, ws.Range("K9").Value);
Assert.Equals("#REF!*5", ws.Range("L10").Formula);
Assert.Equals(ErrorValue.bad_reference, ws.Range("L10").Value);
Assert.Equals("L10*5", ws.Range("N10").Formula);
Assert.Equals(ErrorValue.bad_reference, ws.Range("N10").Value);
Assert.Equals("C11+1", ws.Range("B11").Formula);
Assert.Equals(68, ws.Range("B11").Value);
Assert.Equals("72+H15", ws.Range("H11").Formula);
Assert.Equals(72, ws.Range("H11").Value);
Assert.Equals("I11+H11", ws.Range("J11").Formula);
Assert.Equals(145, ws.Range("J11").Value);
Assert.Equals("J11/5", ws.Range("K11").Formula);
Assert.Equals(29, ws.Range("K11").Value);
Assert.Equals("D11+E11", ws.Range("E13").Formula);
Assert.Equals(137, ws.Range("E13").Value);
Assert.Equals("E4+#REF!", ws.Range("F13").Formula);
Assert.Equals(ErrorValue.bad_reference, ws.Range("F13").Value);
Assert.Equals("K11-J11", ws.Range("J13").Formula);
Assert.Equals(-116, ws.Range("J13").Value);
ws.Range("H15").Value = 12;
Assert.Equals(84, ws.Range("H11").Value);
Assert.Equals(157, ws.Range("J11").Value);
Assert.Equals(31.4, ws.Range("K11").Value);
Assert.Equals(-125.6, ws.Range("J13").Value);
Assert.Equals(-282.6, ws.Range("K9").Value);
ws.Range("C11").Value = 68;
Assert.Equals(142, ws.Range("E6").Value);
ws.Range("E4").Value = 4;
Assert.Equals(144, ws.Range("E6").Value);
Assert.Equals(5, ws.Range("D6").Value);
Assert.Equals(ErrorValue.bad_reference, ws.Range("F13").Value);
Assert.Equals(0, err_listener.AllHappenedErrors.Count);
}
public void TestTheGreatBear()
{
wb = editor.CreateWorkbook("TheGreatBear");
Assert.NotNull(wb);
ws = wb.ActiveWorksheet;
CellsUpdatedListener listener = new CellsUpdatedListener(ws);
ErrorListener err_listener = new ErrorListener(editor);
ws.Range("B9").Formula = "1";
ws.Range("D5").Formula = "B9 + 1";
ws.Range("F6").Formula = "D5 + 1";
ws.Range("H7").Formula = "F6 + 1 + K3";
ws.Range("I12").Formula = "H7 + 1";
ws.Range("K10").Formula = "I12 + 1";
listener.Reset();
ws.Range("K3").Formula = "K10 + 1"; // Cycle
Assert.Equals(4, listener.UpdatedCellsList.Count);
listener.Reset();
Assert.Equals(ErrorValue.bad_reference, ws.Range("H7").Value);
Assert.Equals(ErrorValue.bad_reference, ws.Range("I12").Value);
Assert.Equals(ErrorValue.bad_reference, ws.Range("K10").Value);
Assert.Equals(ErrorValue.bad_reference, ws.Range("K3").Value);
ws.Range("B9").Formula = "2"; // Pull the tail of The GreatBear
Assert.Equals(7, listener.UpdatedCellsList.Count);
listener.Reset();
Assert.Equals(2, ws.Range("B9").Value);
Assert.Equals(3, ws.Range("D5").Value);
Assert.Equals(4, ws.Range("F6").Value);
Assert.Equals(ErrorValue.bad_reference, ws.Range("H7").Value);
Assert.Equals(ErrorValue.bad_reference, ws.Range("I12").Value);
Assert.Equals(ErrorValue.bad_reference, ws.Range("K10").Value);
Assert.Equals(ErrorValue.bad_reference, ws.Range("K3").Value);
ws.Range("I12").Formula = "H7 + 1 + 8"; // Change formula but leave the cycle
Assert.Equals(4, listener.UpdatedCellsList.Count);
listener.Reset();
Assert.Equals(ErrorValue.bad_reference, ws.Range("H7").Value);
Assert.Equals(ErrorValue.bad_reference, ws.Range("I12").Value);
Assert.Equals(ErrorValue.bad_reference, ws.Range("K10").Value);
Assert.Equals(ErrorValue.bad_reference, ws.Range("K3").Value);
ws.Range("K10").Value = 9; // Break the cycle
Assert.Equals(4, listener.UpdatedCellsList.Count);
listener.Reset();
Assert.Equals(15, ws.Range("H7").Value);
Assert.Equals(24, ws.Range("I12").Value);
Assert.Equals(9, ws.Range("K10").Value);
Assert.Equals(10, ws.Range("K3").Value);
ws.Range("I12").Formula = "H7+1+K10"; // Append a formula after the loop has been broken
Assert.Equals(15, ws.Range("H7").Value);
Assert.Equals(25, ws.Range("I12").Value);
Assert.Equals(9, ws.Range("K10").Value);
Assert.Equals(10, ws.Range("K3").Value);
Assert.Equals(0, err_listener.AllHappenedErrors.Count);
}
