public void TestParsingAtLoad()
{
Workbook wb = editor.LoadWorkbook("../../TestFiles/Shared_formulas.xlsx");
Assert.NotNull(wb);
Worksheet ws = wb.ActiveSheet as Worksheet;
ErrorListener err_listener = new ErrorListener(editor);
CellsUpdatedListener listener = new CellsUpdatedListener(ws);
ws.Range("A1").Value = 2;
Assert.Equals(7, ws.Range("A6").Value);
Assert.Equals("A5+1", ws.Range("A6").Formula);
Assert.Equals(10, ws.Range("A9").Value);
Assert.Equals("A8+1", ws.Range("A9").Formula);
Assert.Equals(11, ws.Range("B4").Value);
Assert.Equals("A4+A5", ws.Range("B4").Formula);
Assert.Equals(21, ws.Range("B9").Value);
Assert.Equals("A9+A10", ws.Range("B9").Formula);
Assert.Equals(21, ws.Range("A11").Value);
Assert.Equals("A10+A9", ws.Range("A11").Formula);
Assert.Equals(53, ws.Range("B11").Value);
Assert.Equals("B10+B9", ws.Range("B11").Formula);
Assert.Equals(427, ws.Range("M11").Value);
Assert.Equals("M10+M9", ws.Range("M11").Formula);
Assert.Equals(23, ws.Range("C9").Value);
Assert.Equals("B9+2", ws.Range("C9").Formula);
Assert.Equals(25, ws.Range("D9").Value);
Assert.Equals("C9+2", ws.Range("D9").Formula);
Assert.Equals(43, ws.Range("M9").Value);
Assert.Equals("L9+2", ws.Range("M9").Formula);
Assert.Equals(54, ws.Range("C10").Value);
Assert.Equals("B11+1", ws.Range("C10").Formula);
Assert.Equals(78, ws.Range("D10").Value);
Assert.Equals("C11+1", ws.Range("D10").Formula);
Assert.Equals(384, ws.Range("M10").Value);
Assert.Equals("L11+1", ws.Range("M10").Formula);
Assert.Equals(0, err_listener.AllHappenedErrors.Count);
}
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 Test_HyperlinkAppendToExistentOnes()
{
Workbook wb = editor.LoadWorkbook("../../TestFiles/Hyperlinks.xlsx");
Assert.NotNull(wb);
Worksheet ws = wb.ActiveSheet as Worksheet;
CellsUpdatedListener listener = new CellsUpdatedListener(ws);
ErrorListener err_listener = new ErrorListener(editor);
ws.Hyperlinks.AddAddressHyperlink("A1", "www.ya.ru");
ws.Hyperlinks.AddLocationHyperlink("B1", wb.Worksheets[1], "ZZ55");
Assert.Equals(6, ws.Hyperlinks.Count);
int counter = 0;
foreach (Hyperlink hyp in ws.Hyperlinks)
{
switch (++counter)
{
case 5:
Assert.Equals("www.ya.ru", hyp.Target);
Assert.Null(hyp.ReferredSheet);
break;
case 6:
Assert.Equals("Лист2", hyp.ReferredSheet.Name);
Assert.Equals("ZZ55", hyp.ReferredRange);
Assert.Equals("", hyp.Target);
break;
}
}
wb.Save("../../TestFiles/Hyperlinks_res1.xlsx");
Assert.Null(err_listener.LastError);
wb.Close();
}
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 TestDoubleRangeReferenceCut_Columns()
{
Workbook wb = editor.CreateWorkbook("TestDoubleRangeReferenceCut_Columns");
Assert.NotNull(wb);
Worksheet ws = wb.ActiveSheet as Worksheet;
ErrorListener err_listener = new ErrorListener(editor);
CellsUpdatedListener listener = new CellsUpdatedListener(ws);
ws.Range("B1:G10").Value = 5;
ws.Range("A1").Formula = "COUNT(B1:G10)";
ws.Range("A2").Formula = "COUNT(B1:G10)";
ws.Range("E:M").DeleteColumns();
Assert.Equals("COUNT(B1:D10)", ws.Range("A1").Formula);
Assert.Equals(30, ws.Range("A1").Value);
Assert.Equals("COUNT(B1:D10)", ws.Range("A2").Formula);
Assert.Equals(30, ws.Range("A2").Value);
Assert.Equals(0, err_listener.AllHappenedErrors.Count);
}
public void TestRemovedCellsRedundantUpdate_Columns()
{
Workbook wb = editor.CreateWorkbook("TestRemovedCellsRedundantUpdate_Columns");
Assert.NotNull(wb);
Worksheet ws = wb.ActiveSheet as Worksheet;
ErrorListener err_listener = new ErrorListener(editor);
CellsUpdatedListener listener = new CellsUpdatedListener(ws);
ws.Range("A1").Value = 1;
ws.Range("B1").Value = 2;
ws.Range("C1").Value = 3;
ws.Range("D1").Value = 4;
ws.Range("E1").Value = 5;
ws.Range("A2:E2").Formula = "A1+3";
ws.Range("C:C").DeleteColumns();
Assert.Equals(1, ws.Range("A1").Value);
Assert.Equals(2, ws.Range("B1").Value);
Assert.Equals(4, ws.Range("C1").Value);
Assert.Equals(5, ws.Range("D1").Value);
Assert.Equals("", ws.Range("E1").Value);
Assert.Equals(4, ws.Range("A2").Value);
Assert.Equals(5, ws.Range("B2").Value);
Assert.Equals(7, ws.Range("C2").Value);
Assert.Equals(8, ws.Range("D2").Value);
Assert.Equals("", ws.Range("E2").Value);
Assert.Equals("A1+3", ws.Range("A2").Formula);
Assert.Equals("B1+3", ws.Range("B2").Formula);
Assert.Equals("C1+3", ws.Range("C2").Formula);
Assert.Equals("D1+3", ws.Range("D2").Formula);
Assert.Equals("", ws.Range("E2").Formula);
Assert.Equals(0, err_listener.AllHappenedErrors.Count);
}
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 TestDoubleRangeReferenceCut()
{
Workbook wb = editor.CreateWorkbook("TestWrongRangeReferenceCut");
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("D1").Formula = "COUNT(B1:C10)";
ws.Range("D2").Formula = "COUNT(B1:C10)";
ws.Range("8:13").DeleteRows();
Assert.Equals("COUNT(B1:C7)", ws.Range("D1").Formula);
Assert.Equals(14, ws.Range("D1").Value);
Assert.Equals("COUNT(B1:C7)", ws.Range("D2").Formula);
Assert.Equals(14, ws.Range("D2").Value);
Assert.Equals(0, err_listener.AllHappenedErrors.Count);
}
public void TestBoundaryDeleteRowsWithMerge()
{
wb = editor.CreateWorkbook("book1");
Assert.NotNull(wb);
Worksheet ws = wb.ActiveSheet as Worksheet;
CellsUpdatedListener listener = new CellsUpdatedListener(ws);
ErrorListener err_listener = new ErrorListener(editor);
ws.Range("A5:A10").Merge();
ws.Range("B5:B15").Merge();
ws.Range("C5:C9").Merge();
ws.Range("D5:D16").Merge();
ws.Range("E9:E20").Merge();
ws.Range("F10:F20").Merge();
ws.Range("G15:G20").Merge();
ws.Range("H16:H20").Merge();
ws.Range("I10:I15").Merge();
ws.Range("J10:J16").Merge();
ws.Range("K9:K15").Merge();
ws.Range("L9:L16").Merge();
ws.Range("A10:A15").DeleteRows();
List <string> merged_areas = ws.GetMergedAreas();
Assert.Equals(9, merged_areas.Count);
Assert.Equals("A5:A9", merged_areas[0]);
Assert.Equals("B5:B9", merged_areas[1]);
Assert.Equals("C5:C9", merged_areas[2]);
Assert.Equals("D5:D10", merged_areas[3]);
Assert.Equals("E9:E14", merged_areas[4]);
Assert.Equals("F10:F14", merged_areas[5]);
Assert.Equals("G10:G14", merged_areas[6]);
Assert.Equals("H10:H14", merged_areas[7]);
Assert.Equals("L9:L10", merged_areas[8]);
Assert.Null(err_listener.LastError);
}
public void TestSortLoopFormulasComplexByCol()
{
PrepareFormulasMakingLoopAfterSorting();
CellsUpdatedListener listener = new CellsUpdatedListener(ws);
ErrorListener err_listener = new ErrorListener(editor);
ws.Range("C3:G7").Sort(SortOrientation.by_columns, ws.Range("C4"), SortOrder.ascending);
wb.Save("../../TestFiles/Sort_formulas_loop_bycol_after.xlsx");
Assert.Equals(ErrorValue.bad_reference, ws.Range("D5").Value); // References itself
Assert.Equals(ErrorValue.bad_reference, ws.Range("D5").Value); // Use cyclic error result
Assert.Equals(ErrorValue.bad_reference, ws.Range("C4").Value); // Use cyclic error result
Assert.Equals(ErrorValue.bad_reference, ws.Range("G3").Value); // Use cyclic error result
Assert.Equals(ErrorValue.bad_reference, ws.Range("E7").Value); // Use cyclic error result
Assert.Equals("", ws.Range("C3").Value);
Assert.Equals(true, ws.Range("D3").Value);
Assert.Equals(3.3, ws.Range("E3").Value);
Assert.Equals("", ws.Range("F3").Value);
Assert.Equals(5.5, ws.Range("D4").Value);
Assert.Equals(0, ws.Range("E4").Value);
Assert.Equals(-61.6, ws.Range("F4").Value);
Assert.Equals(1, ws.Range("G4").Value);
Assert.Equals("", ws.Range("C5").Value);
Assert.Equals("", ws.Range("F5").Value);
Assert.Equals(5.5, ws.Range("G5").Value);
Assert.Equals("", ws.Range("C6").Value);
Assert.Equals(true, ws.Range("D6").Value);
Assert.Equals("$D$7^8-56*$A$1", ws.Range("E6").Value);
Assert.Equals("", ws.Range("F6").Value);
Assert.Equals(8, ws.Range("G6").Value);
Assert.Equals("", ws.Range("C7:D7").Value);
Assert.Equals(-300.1, ws.Range("F7").Value);
Assert.Equals(4.3, ws.Range("G7").Value);
Assert.Equals(0, err_listener.AllHappenedErrors.Count);
}
public void TestFormatCopiedToo()
{
wb = editor.CreateWorkbook("Book1");
Assert.NotNull(wb);
ws = wb.ActiveWorksheet;
CellsUpdatedListener listener = new CellsUpdatedListener(ws);
ErrorListener err_listener = new ErrorListener(editor);
ws.Range("A1").Formula = "B1+C1";
ws.Range("A2").Formula = "B2-C2";
ws.Range("B1:B2").Value = 500;
ws.Range("C1:C2").Value = 0.00005;
ws.Range("A1").Format.Font.Bold = true;
ws.Range("A1:A2").Sort(SortOrientation.by_rows, ws.Range("A1:A2"), SortOrder.ascending);
Assert.Equals(499.99995, ws.Range("A1").Value);
Assert.Equals(500.00005, ws.Range("A2").Value);
Assert.Equals(false, ws.Range("A1").Format.Font.Bold);
Assert.Equals(true, ws.Range("A2").Format.Font.Bold);
Assert.Equals(0, err_listener.AllHappenedErrors.Count);
}
public void TestSimpleRangeDependency()
{
System.Console.WriteLine("SimpleRangeDependency");
Workbook wb = editor.CreateWorkbook("SimpleRangeDependency");
Assert.NotNull(wb);
Worksheet ws = wb.ActiveSheet as Worksheet;
ErrorListener err_listener = new ErrorListener(editor);
CellsUpdatedListener listener = new CellsUpdatedListener(ws);
ws.Range("A1:B3").Value = "5";
ws.Range("C3").Formula = "SUM(A1:B3)";
Assert.Equals(30, ws.Range("C3").Value);
ws.Range("B2").Value = "6";
Assert.Equals(31, ws.Range("C3").Value);
ws.Range("B2:B3").Value = "7";
Assert.Equals(34, ws.Range("C3").Value);
Assert.Equals(0, err_listener.AllHappenedErrors.Count);
wb.Close();
}
public void TestUpdatingValuesOutOfRange()
{
PrepareSimpleFormulas();
CellsUpdatedListener listener = new CellsUpdatedListener(ws);
ErrorListener err_listener = new ErrorListener(editor);
ws.Range("B5").Formula = "C5+E5";
ws.Range("B6").Formula = "D6+E6";
ws.Range("D2").Formula = "D3-F3-G5--F7";
ws.Range("E2").Formula = "G3+D4+C5+F7+F5+G6";
ws.Range("F2").Formula = "E4+E5+E6-F6-D3-C4-C3";
ws.Range("C3:G7").Sort(SortOrientation.by_rows, ws.Range("E3"), SortOrder.ascending);
Assert.Equals(ErrorValue.wrong_value_type, ws.Range("B5").Value);
Assert.Equals(82, ws.Range("B6").Value);
Assert.Equals(81, ws.Range("D2").Value);
Assert.Equals(ErrorValue.wrong_value_type, ws.Range("E2").Value);
Assert.Equals(-30.2, ws.Range("F2").Value);
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 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 TestRemovedCellsReferencesUpdate()
{
Workbook wb = editor.CreateWorkbook("RemovedCellsReferencesUpdate");
Assert.NotNull(wb);
Worksheet ws = wb.ActiveSheet as Worksheet;
ErrorListener err_listener = new ErrorListener(editor);
CellsUpdatedListener listener = new CellsUpdatedListener(ws);
ws.Range("A1:D1").Value = 5;
ws.Range("A2").Formula = "A1 + B1";
ws.Range("B2").Formula = "A2 + B1";
ws.Range("C2").Formula = "(D1^(D3+1))";
ws.Range("D2").Formula = "--+-A1+5";
ws.Range("A3").Formula = "A2-B2";
ws.Range("A4").Formula = "A3%";
Assert.Equals(10, ws.Range("A2").Value);
Assert.Equals(15, ws.Range("B2").Value);
Assert.Equals(5, ws.Range("C2").Value);
Assert.Equals(0, ws.Range("D2").Value);
Assert.Equals(-5, ws.Range("A3").Value);
Assert.Equals(-0.05, ws.Range("A4").Value);
ws.Range("A1").DeleteRows();
Assert.Equals("#REF!+#REF!", ws.Range("A1").Formula);
Assert.Equals("A1+#REF!", ws.Range("B1").Formula);
Assert.Equals("(#REF!^(D2+1))", ws.Range("C1").Formula); // Test that non-existent subjects have been updated too
Assert.Equals("--+-#REF!+5", ws.Range("D1").Formula);
Assert.Equals("A1-B1", ws.Range("A2").Formula);
Assert.Equals("A2%", ws.Range("A3").Formula);
Assert.Equals(0, err_listener.AllHappenedErrors.Count);
}
public void Test3NodesCycleAtFormulaSet()
{
wb = editor.CreateWorkbook("3NodesCycle");
Assert.NotNull(wb);
ws = wb.ActiveSheet as Worksheet;
CellsUpdatedListener listener = new CellsUpdatedListener(ws);
ErrorListener err_listener = new ErrorListener(editor);
ws.Range("A2").Formula = "A1 + 1";
ws.Range("B2").Formula = "A2 + 1";
ws.Range("A1").Formula = "B2 + 1"; // Cycle
Assert.Equals(ErrorValue.bad_reference, ws.Range("A1").Value);
Assert.Equals(ErrorValue.bad_reference, ws.Range("B2").Value);
Assert.Equals(ErrorValue.bad_reference, ws.Range("A2").Value);
ws.Range("B2").Formula = "2 + 1"; // Break the cycle
Assert.Equals(4, ws.Range("A1").Value);
Assert.Equals(3, ws.Range("B2").Value);
Assert.Equals(5, ws.Range("A2").Value);
Assert.Equals(0, err_listener.AllHappenedErrors.Count);
}
public void TestDeleteRowsWithMerge()
{
wb = editor.CreateWorkbook("book1");
Assert.NotNull(wb);
Worksheet ws = wb.ActiveSheet as Worksheet;
CellsUpdatedListener listener = new CellsUpdatedListener(ws);
ErrorListener err_listener = new ErrorListener(editor);
ws.Range("B1:C15").Merge();
ws.Range("A30:D45").Merge();
ws.Range("E20:F35").Merge();
ws.Range("G5:H40").Merge();
ws.Range("I15:K20").Merge();
ws.Range("A10:A25").DeleteRows();
List <string> merged_areas = ws.GetMergedAreas();
Assert.Equals(4, merged_areas.Count);
Assert.Equals("B1:C9", merged_areas[0]);
Assert.Equals("A14:D29", merged_areas[1]);
Assert.Equals("E10:F19", merged_areas[2]);
Assert.Equals("G5:H24", merged_areas[3]);
}
public void Test1NodeCycle()
{
wb = editor.CreateWorkbook("1NodeCycle");
Assert.NotNull(wb);
ws = wb.ActiveWorksheet;
CellsUpdatedListener listener = new CellsUpdatedListener(ws);
ErrorListener err_listener = new ErrorListener(editor);
ws.Range("E6").Formula = "$E$6";
Assert.Equals(1, listener.UpdatedCellsList.Count);
listener.Reset();
Assert.Equals(ErrorValue.bad_reference, ws.Range("E6").Value);
ws.Range("E6").Formula = "4+C7+D9"; // Break the cycle
Assert.Equals(1, listener.UpdatedCellsList.Count);
listener.Reset();
Assert.Equals(4, ws.Range("E6").Value);
Assert.Equals(0, err_listener.AllHappenedErrors.Count);
}
public void TestIncreaseDecreaseDecimalFromGeneral()
{
Workbook wb = editor.CreateWorkbook("Красная Заря");
Assert.NotNull(wb);
Worksheet ws = wb.ActiveSheet as Worksheet;
CellsUpdatedListener listener = new CellsUpdatedListener(ws);
ErrorListener err_listener = new ErrorListener(editor);
ws.Range("B3").Value = 64.444;
Assert.Equals(0, ws.Range("B3").Format.NumberFormatId);
Assert.Equals("64.444", ws.Range("B3").DispValue);
ws.Range("B3").Format.DecreaseDecimal();
Assert.Equals("64.44", ws.Range("B3").DispValue);
Assert.Equals("0.00", ws.Range("B3").Format.NumberFormatCode);
ws.Range("B3").Format.NumberFormatId = 0; // General
ws.Range("B3").Format.IncreaseDecimal();
Assert.Equals("64.4440", ws.Range("B3").DispValue);
Assert.Equals("0.0000", ws.Range("B3").Format.NumberFormatCode);
ws.Range("B3").Value = 64.4440000059;
ws.Range("B3").Format.NumberFormatId = 0; // General
Assert.Equals("64.44400001", ws.Range("B3").DispValue);
ws.Range("B3").Format.DecreaseDecimal();
Assert.Equals("64.4440000", ws.Range("B3").DispValue);
Assert.Equals("0.0000000", ws.Range("B3").Format.NumberFormatCode);
ws.Range("B3").Value = 4.44400000099;
ws.Range("B3").Format.NumberFormatId = 0; // General
ws.Range("B3").Format.IncreaseDecimal();
Assert.Equals("4.4440000010", ws.Range("B3").DispValue);
Assert.Equals("0.0000000000", ws.Range("B3").Format.NumberFormatCode);
ws.Range("B3").Value = -64.4440000059;
ws.Range("B3").Format.NumberFormatId = 0; // General
Assert.Equals("-64.44400001", ws.Range("B3").DispValue);
ws.Range("B3").Format.DecreaseDecimal();
Assert.Equals("-64.4440000", ws.Range("B3").DispValue);
Assert.Equals("0.0000000", ws.Range("B3").Format.NumberFormatCode);
ws.Range("B3").Format.NumberFormatId = 0; // General
ws.Range("B3").Format.IncreaseDecimal();
Assert.Equals("-64.444000006", ws.Range("B3").DispValue);
Assert.Equals("0.000000000", ws.Range("B3").Format.NumberFormatCode);
ws.Range("B3").Value = 595656;
ws.Range("B3").Format.NumberFormatId = 0; // General
Assert.Equals("595656", ws.Range("B3").DispValue);
ws.Range("B3").Format.DecreaseDecimal();
Assert.Equals("595656", ws.Range("B3").DispValue);
Assert.Equals("0", ws.Range("B3").Format.NumberFormatCode);
ws.Range("B3").Format.NumberFormatId = 0; // General
ws.Range("B3").Format.IncreaseDecimal();
Assert.Equals("595656.0", ws.Range("B3").DispValue);
Assert.Equals("0.0", ws.Range("B3").Format.NumberFormatCode);
Assert.Null(err_listener.LastError);
}
public void TestNumberFormatApply()
{
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.Equals("0.00", ws.Range("B2").DispValue);
Assert.Equals("123.00", ws.Range("C2").DispValue);
Assert.Equals("1,234,560,000,000.00", ws.Range("D2").DispValue);
Assert.Equals("123.46", ws.Range("E2").DispValue);
Assert.Equals("(25.78)", ws.Range("F2").DispValue);
Assert.Equals("(.26)", ws.Range("G2").DispValue);
Assert.Equals(".26", ws.Range("H2").DispValue);
Assert.Equals("sales Text", ws.Range("I2").DispValue);
Assert.Equals("-", ws.Range("B3").DispValue);
Assert.Equals("123.00", ws.Range("C3").DispValue);
Assert.Equals("1,234,560,000,000.00", ws.Range("D3").DispValue);
Assert.Equals("123.46", ws.Range("E3").DispValue);
Assert.Equals("-25.78", ws.Range("F3").DispValue);
Assert.Equals("-0.26", ws.Range("G3").DispValue);
Assert.Equals("0.26", ws.Range("H3").DispValue);
Assert.Equals("Text", ws.Range("I3").DispValue);
Assert.Equals("0.00000E+00", ws.Range("B4").DispValue);
Assert.Equals("1.23000E+02", ws.Range("C4").DispValue);
Assert.Equals("1.23456E+12", ws.Range("D4").DispValue);
Assert.Equals("1.23456E+02", ws.Range("E4").DispValue);
Assert.Equals("-2.57800E+01", ws.Range("F4").DispValue);
Assert.Equals("-2.56000E-01", ws.Range("G4").DispValue);
Assert.Equals("2.56000E-01", ws.Range("H4").DispValue);
Assert.Equals("Text", ws.Range("I4").DispValue);
Assert.Equals("0.00%", ws.Range("B5").DispValue);
Assert.Equals("12300.00%", ws.Range("C5").DispValue);
Assert.Equals("123456000000000.00%", ws.Range("D5").DispValue);
Assert.Equals("12345.60%", ws.Range("E5").DispValue);
Assert.Equals("-2578.00%", ws.Range("F5").DispValue);
Assert.Equals("-25.60%", ws.Range("G5").DispValue);
Assert.Equals("25.60%", ws.Range("H5").DispValue);
Assert.Equals("Text", ws.Range("I5").DispValue);
Assert.Equals("0-0", ws.Range("B6").DispValue);
Assert.Equals("0-0", ws.Range("C6").DispValue);
Assert.Equals("123456-0", ws.Range("D6").DispValue);
Assert.Equals("0-0", ws.Range("E6").DispValue);
Assert.Equals("0-0", ws.Range("F6").DispValue);
Assert.Equals("0-0", ws.Range("G6").DispValue);
Assert.Equals("0-0", ws.Range("H6").DispValue);
Assert.Equals("Text", ws.Range("I6").DispValue);
Assert.Equals("03-29-10", ws.Range("B7").DispValue);
Assert.Equals("05-02-00", ws.Range("C7").DispValue);
Assert.Equals("#####", ws.Range("D7").DispValue);
Assert.Equals("05-02-00", ws.Range("E7").DispValue);
Assert.Equals("#####", ws.Range("F7").DispValue);
Assert.Equals("#####", ws.Range("G7").DispValue);
Assert.Equals("12-30-99", ws.Range("H7").DispValue);
Assert.Equals("Text", ws.Range("I7").DispValue);
Assert.Equals("29 March 2010 г.", ws.Range("B8").DispValue);
Assert.Equals("2010------03---50---48---29", ws.Range("B9").DispValue);
Assert.Equals("2010------03---50---48.456---29", ws.Range("B10").DispValue);
Assert.Null(err_listener.LastError);
}
public void TestRangesReferencesOverDeletedColumns()
{
Workbook wb = editor.LoadWorkbook("../../TestFiles/RangesReferencesOverDeletedRows.xlsx");
Assert.NotNull(wb);
Worksheet ws = wb.ActiveSheet as Worksheet;
ErrorListener err_listener = new ErrorListener(editor);
CellsUpdatedListener listener = new CellsUpdatedListener(ws);
// The action
ws.Range("D:E").DeleteColumns();
wb.Save("../../TestFiles/RangesReferencesOverDeletedColumns_editor_result.xlsx");
// The action effect
Assert.Equals("SUM(D14:E14)", ws.Range("C1").Formula);
Assert.Equals(2, ws.Range("C1").Value);
Assert.Equals("MAX(C8:C9)", ws.Range("C5").Formula);
Assert.Equals(ErrorValue.bad_reference, ws.Range("C5").Value);
Assert.Equals("MAX(D14:E14)", ws.Range("D5").Formula);
Assert.Equals(2, ws.Range("D5").Value);
Assert.Equals("SUM(D5:D14)", ws.Range("E5").Formula);
Assert.Equals(124, ws.Range("E5").Value);
Assert.Equals("SUM(C:C)", ws.Range("F5").Formula);
Assert.Equals(ErrorValue.bad_reference, ws.Range("F5").Value);
Assert.Equals("B8*C8", ws.Range("B9").Formula);
Assert.Equals(2, ws.Range("B9").Value);
Assert.Equals("C8*#REF!", ws.Range("C9").Formula);
Assert.Equals(ErrorValue.bad_reference, ws.Range("C9").Value);
Assert.Equals("D8*E8", ws.Range("D9").Formula);
Assert.Equals(30, ws.Range("D9").Value);
Assert.Equals("E8*F8", ws.Range("E9").Formula);
Assert.Equals(42, ws.Range("E9").Value);
Assert.Equals("F8*G8", ws.Range("F9").Formula);
Assert.Equals(56, ws.Range("F9").Value);
Assert.Equals("G8*H8", ws.Range("G9").Formula);
Assert.Equals(0, ws.Range("G9").Value);
Assert.Equals("SUM(B8:C9)", ws.Range("B10").Formula);
Assert.Equals(ErrorValue.bad_reference, ws.Range("B10").Value);
Assert.Equals("SUM(C8:C9)", ws.Range("C10").Formula);
Assert.Equals(ErrorValue.bad_reference, ws.Range("C10").Value);
Assert.Equals("SUM(D8:E9)", ws.Range("D10").Formula);
Assert.Equals(83, ws.Range("D10").Value);
Assert.Equals("SUM(E8:F9)", ws.Range("E10").Formula);
Assert.Equals(111, ws.Range("E10").Value);
Assert.Equals("SUM(F8:G9)", ws.Range("F10").Formula);
Assert.Equals(71, ws.Range("F10").Value);
Assert.Equals("SUM(G8:H9)", ws.Range("G10").Formula);
Assert.Equals(8, ws.Range("G10").Value);
Assert.Equals("SUM(C1)", ws.Range("D12").Formula);
Assert.Equals(2, ws.Range("D12").Value);
Assert.Equals("SUM(D10:E14)", ws.Range("F12").Formula);
Assert.Equals(199, ws.Range("F12").Value);
Assert.Equals("SUM(E5:F5,D15)", ws.Range("G14").Formula);
Assert.Equals(ErrorValue.bad_reference, ws.Range("G14").Value);
Assert.Equals("SUM(#REF!)", ws.Range("C16").Formula);
Assert.Equals(ErrorValue.bad_reference, ws.Range("C16").Value);
Assert.Equals("AVERAGE(C5:C10)", ws.Range("C17").Formula);
Assert.Equals(ErrorValue.bad_reference, ws.Range("C17").Value);
Assert.Equals("SUM(D15:D17)", ws.Range("E17").Formula);
Assert.Equals(27, ws.Range("E17").Value);
Assert.Equals("AVERAGE(C1:C5)", ws.Range("F17").Formula);
Assert.Equals(ErrorValue.bad_reference, ws.Range("F17").Value);
Assert.Equals("SUM(C10)", ws.Range("D18").Formula);
Assert.Equals(ErrorValue.bad_reference, ws.Range("D18").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);
}
public void Test_NameRangesSimpleDependencies()
{
ErrorListener err_listener = new ErrorListener(editor);
Workbook wb = editor.LoadWorkbook("../../TestFiles/NameRanges_simple.xlsx");
Assert.NotNull(wb);
Assert.Null(err_listener.LastError);
Worksheet ws = wb.ActiveSheet as Worksheet;
CellsUpdatedListener listener = new CellsUpdatedListener(ws);
ws.Range("A1").Value = 2;
Assert.Equals(8, ws.Range("C2").Value);
wb.DefinedNames.AddName("CDNAME", "Лист1!C2:D3");
Assert.True(err_listener.LastError is FormulaFormatError);
err_listener.Reset();
wb.DefinedNames.AddName("CDNAME", "Лист1!$C$2:$D$3");
ws.Range("A2").Formula = "SUM(CDNAME,C1)";
Assert.Equals(11, ws.Range("A2").Value);
ws.Range("A1").Value = 1;
Assert.Equals(9, ws.Range("A2").Value);
ws.Range("A3").Formula = "SUM((two,four))";
Assert.Equals(6, ws.Range("A3").Value);
ws.Range("A1").DeleteRows();
Assert.Equals("six", ws.Range("C1").Formula);
Assert.Equals("SUM(CDNAME,#REF!)", ws.Range("A1").Formula);
Assert.Equals("SUM((two,four))", ws.Range("A2").Formula);
Assert.Equals(ErrorValue.bad_reference, ws.Range("C1").Value);
Assert.Equals(ErrorValue.bad_reference, ws.Range("A1").Value);
Assert.Equals(ErrorValue.bad_reference, ws.Range("A2").Value);
int counter = 0;
foreach (DefinedName def_name in wb.DefinedNames)
{
switch (++counter)
{
case 1:
Assert.Equals("Лист1!$C$1:$D$2", def_name.Formula);
Assert.Equals("CDNAME", def_name.Name);
break;
case 2:
Assert.Equals("Лист1!#REF!", def_name.Formula);
Assert.Equals("four", def_name.Name);
break;
case 3:
Assert.Equals("two+four", def_name.Formula);
Assert.Equals("six", def_name.Name);
break;
case 4:
Assert.Equals("Лист1!#REF!", def_name.Formula);
Assert.Equals("two", def_name.Name);
break;
}
}
wb.Save("../../TestFiles/NameRanges_simple_res.xlsx");
Assert.Null(err_listener.LastError);
wb.Close();
}
public void TestIncreaseDecreaseDecimalFromGeneralBigSmall()
{
Workbook wb = editor.CreateWorkbook("TestIncreaseDecreaseDecimalFromGeneralBigSmall");
Assert.NotNull(wb);
Worksheet ws = wb.ActiveSheet as Worksheet;
CellsUpdatedListener listener = new CellsUpdatedListener(ws);
ErrorListener err_listener = new ErrorListener(editor);
ws.Range("B3").Value = 0.0001234567891123456;
ws.Range("B3").Format.NumberFormatId = 0; // General
ws.Range("B3").Format.IncreaseDecimal();
Assert.Equals("0.0001234568", ws.Range("B3").DispValue);
Assert.Equals("0.0000000000", ws.Range("B3").Format.NumberFormatCode);
ws.Range("B3").Value = 0.000123;
ws.Range("B3").Format.NumberFormatId = 0; // General
ws.Range("B3").Format.DecreaseDecimal();
Assert.Equals("0.00012", ws.Range("B3").DispValue);
Assert.Equals("0.00000", ws.Range("B3").Format.NumberFormatCode);
ws.Range("B3").Value = 0.0001234567891123456;
ws.Range("B3").Format.NumberFormatId = 0; // General
ws.Range("B3").Format.DecreaseDecimal();
Assert.Equals("0.00012346", ws.Range("B3").DispValue);
Assert.Equals("0.00000000", ws.Range("B3").Format.NumberFormatCode);
ws.Range("B3").Value = 0.0000123456789112345;
ws.Range("B3").Format.NumberFormatId = 0; // General
Assert.Equals("1.23457E-05", ws.Range("B3").DispValue);
ws.Range("B3").Format.IncreaseDecimal();
Assert.Equals("1.234568E-05", ws.Range("B3").DispValue);
Assert.Equals("0.000000E+00", ws.Range("B3").Format.NumberFormatCode);
ws.Range("B3").Value = 9.234567891123E-45;
ws.Range("B3").Format.NumberFormatId = 0; // General
Assert.Equals("9.23457E-45", ws.Range("B3").DispValue);
ws.Range("B3").Format.DecreaseDecimal();
Assert.Equals("9.2346E-45", ws.Range("B3").DispValue);
Assert.Equals("0.0000E+00", ws.Range("B3").Format.NumberFormatCode);
ws.Range("B3").Value = 9.234567891123E-145;
ws.Range("B3").Format.NumberFormatId = 0; // General
Assert.Equals("9.2346E-145", ws.Range("B3").DispValue);
ws.Range("B3").Format.DecreaseDecimal();
Assert.Equals("9.235E-145", ws.Range("B3").DispValue);
Assert.Equals("0.000E+00", ws.Range("B3").Format.NumberFormatCode);
ws.Range("B3").Value = 9.23E-145;
ws.Range("B3").Format.NumberFormatId = 0; // General
Assert.Equals("9.23E-145", ws.Range("B3").DispValue);
ws.Range("B3").Format.DecreaseDecimal();
Assert.Equals("9.2E-145", ws.Range("B3").DispValue);
Assert.Equals("0.0E+00", ws.Range("B3").Format.NumberFormatCode);
ws.Range("B3").Value = 123456789112345.0;
ws.Range("B3").Format.NumberFormatId = 0; // General
Assert.Equals("1.23457E+14", ws.Range("B3").DispValue);
ws.Range("B3").Format.IncreaseDecimal();
Assert.Equals("1.234568E+14", ws.Range("B3").DispValue);
Assert.Equals("0.000000E+00", ws.Range("B3").Format.NumberFormatCode);
ws.Range("B3").Value = 100000000000005.0;
ws.Range("B3").Format.NumberFormatId = 0; // General
Assert.Equals("1E+14", ws.Range("B3").DispValue);
ws.Range("B3").Format.IncreaseDecimal();
Assert.Equals("1.0E+14", ws.Range("B3").DispValue);
Assert.Equals("0.0E+00", ws.Range("B3").Format.NumberFormatCode);
ws.Range("B3").Value = 9.23E145;
ws.Range("B3").Format.NumberFormatId = 0; // General
Assert.Equals("9.23E+145", ws.Range("B3").DispValue);
ws.Range("B3").Format.IncreaseDecimal();
Assert.Equals("9.230E+145", ws.Range("B3").DispValue);
Assert.Equals("0.000E+00", ws.Range("B3").Format.NumberFormatCode);
ws.Range("B3").Value = 100000000.25;
ws.Range("B3").Format.NumberFormatId = 0; // General
Assert.Equals("100000000.3", ws.Range("B3").DispValue);
ws.Range("B3").Format.IncreaseDecimal();
Assert.Equals("100000000.25", ws.Range("B3").DispValue);
Assert.Equals("0.00", ws.Range("B3").Format.NumberFormatCode);
ws.Range("B3").Value = 1000000000.25;
ws.Range("B3").Format.NumberFormatId = 0; // General
Assert.Equals("1000000000", ws.Range("B3").DispValue);
ws.Range("B3").Format.DecreaseDecimal();
Assert.Equals("1000000000", ws.Range("B3").DispValue);
Assert.Equals("0", ws.Range("B3").Format.NumberFormatCode);
ws.Range("B3").Format.IncreaseDecimal();
Assert.Equals("1000000000.3", ws.Range("B3").DispValue);
Assert.Equals("0.0", ws.Range("B3").Format.NumberFormatCode);
ws.Range("B3").Value = 10000000000.25;
ws.Range("B3").Format.NumberFormatId = 0; // General
Assert.Equals("10000000000", ws.Range("B3").DispValue);
ws.Range("B3").Format.IncreaseDecimal();
Assert.Equals("10000000000.3", ws.Range("B3").DispValue);
Assert.Equals("0.0", ws.Range("B3").Format.NumberFormatCode);
ws.Range("B3").Value = 100000000000.25;
ws.Range("B3").Format.NumberFormatId = 0; // General
Assert.Equals("1E+11", ws.Range("B3").DispValue);
ws.Range("B3").Format.IncreaseDecimal();
Assert.Equals("1.0E+11", ws.Range("B3").DispValue);
Assert.Equals("0.0E+00", ws.Range("B3").Format.NumberFormatCode);
Assert.Null(err_listener.LastError);
}
public void TestRangesReferencesOverDeletedRows()
{
Workbook wb = editor.LoadWorkbook("../../TestFiles/RangesReferencesOverDeletedRows.xlsx");
Assert.NotNull(wb);
Worksheet ws = wb.ActiveSheet as Worksheet;
ErrorListener err_listener = new ErrorListener(editor);
CellsUpdatedListener listener = new CellsUpdatedListener(ws);
// Verify correct load:
Assert.Equals("SUM(F14:G14)", ws.Range("C1").Formula);
Assert.Equals(2, ws.Range("C1").Value);
Assert.Equals("SUM(F14,G14)", ws.Range("D1").Formula);
Assert.Equals(2, ws.Range("D1").Value);
Assert.Equals("MAX(C8:D9)", ws.Range("C5").Formula);
Assert.Equals(12, ws.Range("C5").Value);
Assert.Equals("SUM(COUNT(B5:C10)*7,F8:G14)", ws.Range("D5").Formula);
Assert.Equals(333, ws.Range("D5").Value);
Assert.Equals("MIN(1:1)", ws.Range("E5").Formula);
Assert.Equals(2, ws.Range("E5").Value);
Assert.Equals("MAX(F14:G14)", ws.Range("F5").Formula);
Assert.Equals(2, ws.Range("F5").Value);
Assert.Equals("SUM(E5:F14)", ws.Range("G5").Formula);
Assert.Equals(211, ws.Range("G5").Value);
Assert.Equals("SUM(C:D)", ws.Range("H5").Formula);
Assert.Equals(1019.0416666666666, (double)ws.Range("H5").Value, 1e-10);
Assert.Equals("SUM(C1:D1)", ws.Range("F12").Formula);
Assert.Equals(4, ws.Range("F12").Value);
Assert.Equals("SUM(F10:G14)", ws.Range("H12").Formula);
Assert.Equals(201, ws.Range("H12").Value);
Assert.Equals("SUM(G5:H5,D15:F15)", ws.Range("I14").Formula);
Assert.Equals(1245.0416666666665, (double)ws.Range("I14").Value, 1e-10);
Assert.Equals("SUM(D5:E16)", ws.Range("C16").Formula);
Assert.Equals(496, ws.Range("C16").Value);
Assert.Equals("AVERAGE(C5:D10)", ws.Range("C17").Formula);
Assert.Equals(53.75, ws.Range("C17").Value);
Assert.Equals("SUM(D15:F17)", ws.Range("G17").Formula);
Assert.Equals(72, ws.Range("G17").Value);
Assert.Equals("AVERAGE(C1:D5)", ws.Range("H17").Formula);
Assert.Equals(87.25, ws.Range("H17").Value);
Assert.Equals("AVERAGE(5,D8:F17,C1:D1,G13)", ws.Range("D18").Formula);
Assert.Equals(14.291666666666666, (double)ws.Range("D18").Value, 1e-10);
Assert.Equals("SUM(B8:C13)", ws.Range("E18").Formula);
Assert.Equals(45, ws.Range("E18").Value);
Assert.Equals("SUM(C10:C10)", ws.Range("F18").Formula);
Assert.Equals(23, ws.Range("F18").Value);
// The action
ws.Range("8:13").DeleteRows();
wb.Save("../../TestFiles/RangesReferencesOverDeletedRows_editor_result.xlsx");
// The action effect
Assert.Equals("SUM(F8:G8)", ws.Range("C1").Formula);
Assert.Equals(2, ws.Range("C1").Value);
Assert.Equals("SUM(F8,G8)", ws.Range("D1").Formula);
Assert.Equals(2, ws.Range("D1").Value);
Assert.Equals("MAX(#REF!)", ws.Range("C5").Formula);
Assert.Equals(ErrorValue.bad_reference, ws.Range("C5").Value);
Assert.Equals("SUM(COUNT(B5:C7)*7,F8:G8)", ws.Range("D5").Formula);
Assert.Equals(2, ws.Range("D5").Value);
Assert.Equals("MIN(1:1)", ws.Range("E5").Formula);
Assert.Equals(2, ws.Range("E5").Value);
Assert.Equals("MAX(F8:G8)", ws.Range("F5").Formula);
Assert.Equals(2, ws.Range("F5").Value);
Assert.Equals("SUM(E5:F8)", ws.Range("G5").Formula);
Assert.Equals(6, ws.Range("G5").Value);
Assert.Equals("SUM(C:D)", ws.Range("H5").Formula);
Assert.Equals(ErrorValue.bad_reference, ws.Range("H5").Value);
Assert.Equals("SUM(G5:H5,D9:F9)", ws.Range("I8").Formula);
Assert.Equals(ErrorValue.bad_reference, ws.Range("I8").Value);
Assert.Equals("SUM(D5:E10)", ws.Range("C10").Formula);
Assert.Equals(28, ws.Range("C10").Value);
Assert.Equals("AVERAGE(C5:D7)", ws.Range("C11").Formula);
Assert.Equals(ErrorValue.bad_reference, ws.Range("C11").Value);
Assert.Equals("SUM(D9:F11)", ws.Range("G11").Formula);
Assert.Equals(72, ws.Range("G11").Value);
Assert.Equals("AVERAGE(C1:D5)", ws.Range("H11").Formula);
Assert.Equals(ErrorValue.bad_reference, ws.Range("H11").Value);
Assert.Equals("AVERAGE(5,D8:F11,C1:D1,#REF!)", ws.Range("D12").Formula);
Assert.Equals(ErrorValue.bad_reference, ws.Range("D12").Value);
Assert.Equals("SUM(#REF!)", ws.Range("E12").Formula);
Assert.Equals(ErrorValue.bad_reference, ws.Range("E12").Value);
Assert.Equals("SUM(#REF!)", ws.Range("F12").Formula);
Assert.Equals(ErrorValue.bad_reference, ws.Range("F12").Value);
Assert.Equals(0, err_listener.AllHappenedErrors.Count);
}
public void TestGeneralApply()
{
Workbook wb = editor.CreateWorkbook("temp");
Assert.NotNull(wb);
Worksheet ws = wb.ActiveSheet as Worksheet;
CellsUpdatedListener listener = new CellsUpdatedListener(ws);
ErrorListener err_listener = new ErrorListener(editor);
ws.Range("B3").Value = 64.444555666777111;
Assert.Equals(0, ws.Range("B3").Format.NumberFormatId);
Assert.Equals("64.44455567", ws.Range("B3").DispValue);
ws.Range("B3").Value = 164.4555000222111;
Assert.Equals("164.4555", ws.Range("B3").DispValue);
ws.Range("B3").Value = 64.4555000222111;
Assert.Equals("64.45550002", ws.Range("B3").DispValue);
ws.Range("B3").Value = 64.444555007;
Assert.Equals("64.44455501", ws.Range("B3").DispValue);
ws.Range("B3").Value = 8.41245678912E+50;
Assert.Equals("8.41246E+50", ws.Range("B3").DispValue);
ws.Range("B3").Value = 8.41245678912E+98;
Assert.Equals("8.41246E+98", ws.Range("B3").DispValue);
ws.Range("B3").Value = 8.41245678912E+99;
Assert.Equals("8.4125E+99", ws.Range("B3").DispValue);
ws.Range("B3").Value = 8.41245678912E+250;
Assert.Equals("8.4125E+250", ws.Range("B3").DispValue);
ws.Range("B3").Value = 8234567890;
Assert.Equals("8234567890", ws.Range("B3").DispValue);
ws.Range("B3").Value = 82345678901;
Assert.Equals("82345678901", ws.Range("B3").DispValue);
ws.Range("B3").Value = 823456789012;
Assert.Equals("8.23457E+11", ws.Range("B3").DispValue);
ws.Range("B3").Value = 8234;
Assert.Equals("8234", ws.Range("B3").DispValue);
ws.Range("B3").Value = 8234.567890123456;
Assert.Equals("8234.56789", ws.Range("B3").DispValue);
ws.Range("B3").Value = 0.112345678911234;
Assert.Equals("0.112345679", ws.Range("B3").DispValue);
ws.Range("B3").Value = 0.000123456789;
Assert.Equals("0.000123457", ws.Range("B3").DispValue);
ws.Range("B3").Value = 0.0000123456789;
Assert.Equals("1.23457E-05", ws.Range("B3").DispValue);
ws.Range("B3").Value = 1.234567891E-99;
Assert.Equals("1.2346E-99", ws.Range("B3").DispValue);
ws.Range("B3").Value = 1.234567891E-98;
Assert.Equals("1.23457E-98", ws.Range("B3").DispValue);
ws.Range("B3").Value = 7.234567891E-300;
Assert.Equals("7.2346E-300", ws.Range("B3").DispValue);
ws.Range("B3").Value = 0;
Assert.Equals("0", ws.Range("B3").DispValue);
Assert.Null(err_listener.LastError);
}
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 TestRangeRef()
{
Range r = var_data_sheet.Range("F5");
object value = r.Value;
Assert.Equals(10.5, value);
value = var_data_sheet.Range("$F5").Value;
Assert.Equals(10.5, value);
value = var_data_sheet.Range("$F$5").Value;
Assert.Equals(10.5, value);
value = var_data_sheet.Range("F$5").Value;
Assert.Equals(10.5, value);
Assert.True(var_data_sheet.Range("F$5").IsSingleCell);
Assert.True(var_data_sheet.Range("F$5:f5").IsSingleCell);
Assert.False(var_data_sheet.Range("F$5:f6").IsSingleCell);
Assert.False(var_data_sheet.Range("$F:f").IsSingleCell);
Assert.False(var_data_sheet.Range("A1:FF55").IsSingleCell);
value = var_data_sheet.Range("A2:G2").Value;
Assert.Equals(true, value);
value = var_data_sheet.Range("$A2:G$2").Value;
Assert.Equals(true, value);
value = var_data_sheet.Range("A$2:$G2").Value;
Assert.Equals(true, value);
value = var_data_sheet.Range("$A$2:$G$2").Value;
Assert.Equals(true, value);
value = var_data_sheet.Range("2:2").Value;
Assert.Equals(null, value);
value = var_data_sheet.Range("A:AA").Value;
Assert.Equals(null, value);
ErrorListener err_listener = new ErrorListener(editor);
value = var_data_sheet.Range("A:2").Value;
Assert.True(err_listener.LastError is AVSOfficeEWSEditor.Editor.Events.WrongReferenceFormatError);
err_listener.Reset();
value = var_data_sheet.Range("2:A2").Value;
Assert.True(err_listener.LastError is AVSOfficeEWSEditor.Editor.Events.WrongReferenceFormatError);
err_listener.Reset();
value = var_data_sheet.Range("A2-A2").Value;
Assert.True(err_listener.LastError is AVSOfficeEWSEditor.Editor.Events.WrongReferenceFormatError);
err_listener.Reset();
value = var_data_sheet.Range("A2:").Value;
Assert.True(err_listener.LastError is AVSOfficeEWSEditor.Editor.Events.WrongReferenceFormatError);
err_listener.Reset();
value = var_data_sheet.Range("Плохая_ссылка:#REF!").Value;
Assert.True(err_listener.LastError is AVSOfficeEWSEditor.Editor.Events.WrongReferenceFormatError);
err_listener.Reset();
value = var_data_sheet.Range(":#REF!").Value;
Assert.True(err_listener.LastError is AVSOfficeEWSEditor.Editor.Events.WrongReferenceFormatError);
err_listener.Reset();
value = var_data_sheet.Range(":A2").Value;
Assert.True(err_listener.LastError is AVSOfficeEWSEditor.Editor.Events.WrongReferenceFormatError);
err_listener.Reset();
}
public void TestSortByColumns_C14()
{
PrepareSimpleData();
CellsUpdatedListener listener = new CellsUpdatedListener(ws);
ErrorListener err_listener = new ErrorListener(editor);
ws.Range("C4:F14").Sort(SortOrientation.by_columns, ws.Range("C14"), SortOrder.ascending);
wb.Save("../../TestFiles/Sort_by_columns_after_C14.xlsx");
Assert.Equals(5, ws.Range("E4").Value);
Assert.Equals(5, ws.Range("C4").Value);
Assert.Equals(true, ws.Range("D4").Value);
Assert.Equals(true, ws.Range("F4").Value);
Assert.Equals(5, ws.Range("E5").Value);
Assert.Equals("wtf", ws.Range("C5").Value);
Assert.Equals("", ws.Range("D5").Value);
Assert.Equals(true, ws.Range("F5").Value);
Assert.Equals(ErrorValue.bad_reference, ws.Range("E6").Value);
Assert.Equals(20, ws.Range("C6").Value);
Assert.Equals("wtf", ws.Range("D6").Value);
Assert.Equals(ErrorValue.bad_reference, ws.Range("F6").Value);
Assert.Equals(4, ws.Range("E7").Value);
Assert.Equals(20, ws.Range("C7").Value);
Assert.Equals(4, ws.Range("D7").Value);
Assert.Equals(4, ws.Range("F7").Value);
Assert.Equals(456.124, ws.Range("E8").Value);
Assert.Equals(ErrorValue.bad_reference, ws.Range("C8").Value);
Assert.Equals(456.123, ws.Range("D8").Value);
Assert.Equals(ErrorValue.bad_reference, ws.Range("F8").Value);
Assert.Equals(ErrorValue.bad_reference, ws.Range("E9").Value);
Assert.Equals(ErrorValue.bad_reference, ws.Range("C9").Value);
Assert.Equals(ErrorValue.bad_reference, ws.Range("D9").Value);
Assert.Equals("", ws.Range("F9").Value);
Assert.Equals("яяя", ws.Range("E10").Value);
Assert.Equals("", ws.Range("C10").Value);
Assert.Equals("", ws.Range("D10").Value);
Assert.Equals("", ws.Range("F10").Value);
Assert.Equals(5, ws.Range("E11").Value);
Assert.Equals(5, ws.Range("C11").Value);
Assert.Equals(500, ws.Range("D11").Value);
Assert.Equals("", ws.Range("F11").Value);
Assert.Equals(5, ws.Range("E12").Value);
Assert.Equals(5, ws.Range("C12").Value);
Assert.Equals(500, ws.Range("D12").Value);
Assert.Equals(true, ws.Range("F12").Value);
Assert.Equals("", ws.Range("E13").Value);
Assert.Equals("", ws.Range("C13").Value);
Assert.Equals("", ws.Range("D13").Value);
Assert.Equals("", ws.Range("F13").Value);
Assert.Equals("", ws.Range("E14").Value);
Assert.Equals("storage", ws.Range("C14").Value);
Assert.Equals(false, ws.Range("D14").Value);
Assert.Equals("", ws.Range("F14").Value);
Assert.Equals(0, err_listener.AllHappenedErrors.Count);
}
