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