public void Test_HyperlinkRedefining() { 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("B5", "file://D:/temp"); Assert.Equals(4, ws.Hyperlinks.Count); int counter = 0; foreach (Hyperlink hyp in ws.Hyperlinks) { switch (++counter) { case 4: Assert.Equals("file://D:/temp", hyp.Target); Assert.Null(hyp.ReferredSheet); break; } } wb.Save("../../TestFiles/Hyperlinks_res3.xlsx"); Assert.Null(err_listener.LastError); wb.Close(); }
public void Test_HyperlinkAppendToEmptySection() { Workbook wb = editor.LoadWorkbook("../../TestFiles/Hyperlinks.xlsx"); Assert.NotNull(wb); Worksheet ws = wb.Worksheets[1]; CellsUpdatedListener listener = new CellsUpdatedListener(ws); ErrorListener err_listener = new ErrorListener(editor); ws.Hyperlinks.AddAddressHyperlink("A1", "file://D:/temp"); Assert.Equals(1, ws.Hyperlinks.Count); int counter = 0; foreach (Hyperlink hyp in ws.Hyperlinks) { switch (++counter) { case 1: Assert.Equals("file://D:/temp", hyp.Target); Assert.Null(hyp.ReferredSheet); break; } } wb.Save("../../TestFiles/Hyperlinks_res2.xlsx"); Assert.Null(err_listener.LastError); wb.Close(); }
public void TestSortLoopFormulas2Rows() { 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 = "$B$1 + 1"; ws.Range("B1").Value = 2; ws.Range("A2").Value = 1; ws.Range("B2").Formula = "$A$2 + 1"; Assert.Equals(3, ws.Range("A1").Value); Assert.Equals(2, ws.Range("B2").Value); wb.Save("../../TestFiles/Sort_formulas_simple_loop_before.xlsx"); ws.Range("A1:B2").Sort(SortOrientation.by_rows, ws.Range("A1"), SortOrder.ascending); wb.Save("../../TestFiles/Sort_formulas_simple_loop_after.xlsx"); Assert.Equals(1, ws.Range("A1").Value); Assert.Equals(2, ws.Range("B2").Value); Assert.Equals(ErrorValue.bad_reference, ws.Range("A2").Value); Assert.Equals(ErrorValue.bad_reference, ws.Range("B1").Value); Assert.Equals(0, err_listener.AllHappenedErrors.Count); }
public void Test_MissedDelayedRecalculation() { Workbook wb = editor.CreateWorkbook("3D", 2); Assert.NotNull(wb); Worksheet ws = wb.ActiveSheet as Worksheet; CellsUpdatedListener listener = new CellsUpdatedListener(ws); ErrorListener err_listener = new ErrorListener(editor); wb.Worksheets[1].Range("B2").Value = 7; wb.Worksheets[1].Range("B3:B6").Formula = "B2+4"; wb.Worksheets[1].Range("C2:E2").Formula = "B2-2"; wb.Worksheets[1].Range("C3:E6").Formula = "B2+B3-C2"; wb.Worksheets[0].Range("A20").Formula = "SUM(Sheet1:Sheet2!A4:E5)+SUM(Sheet2!A5)"; Assert.Equals("SUM(Sheet1:Sheet2!A4:E5)+SUM(Sheet2!A5)", wb.Worksheets[0].Range("A20").Formula); Assert.Equals(136, wb.Worksheets[0].Range("A20").Value); wb.Save("../../TestFiles/ws_remove.xlsx"); wb.Worksheets[1].Range("A1").DeleteRows(); Assert.Equals("SUM(Sheet1:Sheet2!A4:E5)+SUM(Sheet2!A4)", wb.Worksheets[0].Range("A20").Formula); Assert.Equals(168, wb.Worksheets[0].Range("A20").Value); // The reason of the test Assert.Null(err_listener.LastError); wb.Close(); }
public void TestUnMerge() { wb = editor.LoadWorkbook("../../TestFiles/MergedAreas.xlsx"); Assert.NotNull(wb); Worksheet ws = wb.Sheets["Лист1"] as Worksheet; CellsUpdatedListener listener = new CellsUpdatedListener(ws); ErrorListener err_listener = new ErrorListener(editor); Assert.Equals(10, ws.GetMergedAreas().Count); ws.Range("E12:E14").UnMerge(); Assert.Equals(10, ws.GetMergedAreas().Count); ws.Range("19:19").UnMerge(); Assert.Equals(8, ws.GetMergedAreas().Count); ws.Range("F13:G14").UnMerge(); Assert.Equals(6, ws.GetMergedAreas().Count); ws.Range("D1:D8").UnMerge(); Assert.Equals(3, ws.GetMergedAreas().Count); ws.Range("C14").UnMerge(); List <string> all_merged_ranges = ws.GetMergedAreas(); Assert.Equals(2, all_merged_ranges.Count); Assert.Equals("B2:B6", all_merged_ranges[0]); Assert.Equals("F3:F8", all_merged_ranges[1]); Assert.Equals(0, listener.UpdatedCellsList.Count); Assert.Equals(0, err_listener.AllHappenedErrors.Count); }
public void TestBowCycle() { wb = editor.CreateWorkbook("BowCycle"); Assert.NotNull(wb); ws = wb.ActiveWorksheet; CellsUpdatedListener listener = new CellsUpdatedListener(ws); ErrorListener err_listener = new ErrorListener(editor); ws.Range("B4").Formula = "C2 + 1"; ws.Range("E5").Formula = "D2 + 1"; ws.Range("D7").Formula = "B4+E5"; listener.Reset(); ws.Range("C2:D2").Formula = "$D$7 + 1"; // 2 cycles Assert.Equals(5, listener.UpdatedCellsList.Count); listener.Reset(); Assert.Equals(ErrorValue.bad_reference, ws.Range("C2:D2").Value); Assert.Equals(ErrorValue.bad_reference, ws.Range("B4").Value); Assert.Equals(ErrorValue.bad_reference, ws.Range("E5").Value); Assert.Equals(ErrorValue.bad_reference, ws.Range("D7").Value); ws.Range("D7").Value = 4; // Break the cycle Assert.Equals(5, listener.UpdatedCellsList.Count); listener.Reset(); Assert.Equals(5, ws.Range("C2:D2").Value); Assert.Equals(6, ws.Range("B4").Value); Assert.Equals(6, ws.Range("E5").Value); Assert.Equals(0, err_listener.AllHappenedErrors.Count); }
public void TestRangesCycle() { wb = editor.CreateWorkbook("RangesCycle"); Assert.NotNull(wb); ws = wb.ActiveWorksheet; CellsUpdatedListener listener = new CellsUpdatedListener(ws); ErrorListener err_listener = new ErrorListener(editor); // Cycle 1 ws.Range("A1:D5").Value = 10; ws.Range("D6").Formula = "SUM(A1:D5)"; Assert.Equals(200, ws.Range("D6").Value); ws.Range("E3").Formula = "MAX(C1:D6)"; Assert.Equals(200, ws.Range("E3").Value); ws.Range("B3").Formula = "COUNT(D1:E3)"; // The following 3 values could be as correct values as bad references. It depends on internal recalculation sequence and is OK. Assert.Equals(3, ws.Range("B3").Value); Assert.Equals(ErrorValue.bad_reference, ws.Range("E3").Value); Assert.Equals(193, ws.Range("D6").Value); // Cycle 2 ws.Range("A10").Value = 10; ws.Range("A11").Formula = "SUM(A10:B10)"; Assert.Equals(10, ws.Range("A11").Value); ws.Range("B11").Formula = "SUM(A10:A11)"; Assert.Equals(20, ws.Range("B11").Value); ws.Range("B10").Formula = "SUM(A11:B11)"; Assert.Equals(ErrorValue.bad_reference, ws.Range("B10").Value); Assert.Equals(ErrorValue.bad_reference, ws.Range("A11").Value); Assert.Equals(ErrorValue.bad_reference, ws.Range("B11").Value); Assert.Equals(0, err_listener.AllHappenedErrors.Count); }
public void Test_HyperlinkCopyOnRangeCopy() { 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.Range("B1:C5").CopyTo(wb.Worksheets["Лист2"].Range("A2")); Assert.Equals(0, listener.UpdatedCellsList.Count); Assert.Equals(4, ws.Hyperlinks.Count); Worksheet ws_dest = wb.Worksheets["Лист2"]; Assert.Equals(2, ws_dest.Hyperlinks.Count); // link 1 Hyperlink hyp1 = ws_dest.Hyperlinks["A4"]; Assert.Equals("http://teamlab/products/projects/tasks.aspx?prjID=18&id=1476", hyp1.Target); Assert.Equals(false, hyp1.IsLocationOrAddressType); // link 2 Hyperlink hyp2 = ws_dest.Hyperlinks["A6"]; Assert.Equals("Лист3", hyp2.ReferredSheet.Name); Assert.Equals("A1:B2", hyp2.ReferredRange); Assert.Equals(true, hyp2.IsLocationOrAddressType); Assert.Null(err_listener.LastError); wb.Close(); }
public void Test2NodesCycle() { wb = editor.CreateWorkbook("2NodesCycle"); Assert.NotNull(wb); ws = wb.ActiveWorksheet; CellsUpdatedListener listener = new CellsUpdatedListener(ws); ErrorListener err_listener = new ErrorListener(editor); ws.Range("E6").Formula = "F4+D9"; listener.Reset(); ws.Range("F4").Formula = "4+E6+C7"; Assert.Equals(2, listener.UpdatedCellsList.Count); listener.Reset(); Assert.Equals(ErrorValue.bad_reference, ws.Range("F4").Value); Assert.Equals(ErrorValue.bad_reference, ws.Range("E6").Value); ws.Range("E6").Formula = "4+C7+D9"; // Break the cycle Assert.Equals(2, listener.UpdatedCellsList.Count); listener.Reset(); Assert.Equals(4, ws.Range("E6").Value); Assert.Equals(8, ws.Range("F4").Value); Assert.Equals(0, err_listener.AllHappenedErrors.Count); }
public void TestBoundaryInsertColumnsWithMerge() { 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:E1").Merge(); ws.Range("B2:F2").Merge(); ws.Range("E3:J3").Merge(); ws.Range("F4:J4").Merge(); ws.Range("E5:F6").Merge(); ws.Range("7:9").Merge(); ws.Range("F10").InsertColumns(2); List <string> merged_areas = ws.GetMergedAreas(); Assert.Equals(6, merged_areas.Count); Assert.Equals("B1:E1", merged_areas[0]); Assert.Equals("B2:H2", merged_areas[1]); Assert.Equals("E3:L3", merged_areas[2]); Assert.Equals("H4:L4", merged_areas[3]); Assert.Equals("E5:H6", merged_areas[4]); Assert.Equals("7:9", merged_areas[5]); }
public void TestRemovedCellsRedundantUpdate() { Workbook wb = editor.CreateWorkbook("TestRemovedCellsRedundantUpdate"); 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("A2").Value = 2; ws.Range("A3").Value = 3; ws.Range("A4").Value = 4; ws.Range("A5").Value = 5; ws.Range("A6").Value = 6; ws.Range("A7").Value = 7; ws.Range("A8").Value = 8; ws.Range("A9").Value = 9; ws.Range("A10").Value = 10; ws.Range("B1:B10").Formula = "A1+3"; ws.Range("4:4").DeleteRows(); Assert.Equals(1, ws.Range("A1").Value); Assert.Equals(2, ws.Range("A2").Value); Assert.Equals(3, ws.Range("A3").Value); Assert.Equals(5, ws.Range("A4").Value); Assert.Equals(6, ws.Range("A5").Value); Assert.Equals(7, ws.Range("A6").Value); Assert.Equals(8, ws.Range("A7").Value); Assert.Equals(9, ws.Range("A8").Value); Assert.Equals(10, ws.Range("A9").Value); Assert.Equals("", ws.Range("A10").Value); Assert.Equals(4, ws.Range("B1").Value); Assert.Equals(5, ws.Range("B2").Value); Assert.Equals(6, ws.Range("B3").Value); Assert.Equals(8, ws.Range("B4").Value); Assert.Equals(9, ws.Range("B5").Value); Assert.Equals(10, ws.Range("B6").Value); Assert.Equals(11, ws.Range("B7").Value); Assert.Equals(12, ws.Range("B8").Value); Assert.Equals(13, ws.Range("B9").Value); Assert.Equals("", ws.Range("B10").Value); Assert.Equals("A1+3", ws.Range("B1").Formula); Assert.Equals("A2+3", ws.Range("B2").Formula); Assert.Equals("A3+3", ws.Range("B3").Formula); Assert.Equals("A4+3", ws.Range("B4").Formula); Assert.Equals("A5+3", ws.Range("B5").Formula); Assert.Equals("A6+3", ws.Range("B6").Formula); Assert.Equals("A7+3", ws.Range("B7").Formula); Assert.Equals("A8+3", ws.Range("B8").Formula); Assert.Equals("A9+3", ws.Range("B9").Formula); Assert.Equals("", ws.Range("B10").Formula); Assert.Equals(0, err_listener.AllHappenedErrors.Count); }
public void TestSortByColumnsDesc() { PrepareSimpleData(); CellsUpdatedListener listener = new CellsUpdatedListener(ws); ErrorListener err_listener = new ErrorListener(editor); ws.Range("A5:K6").Sort(SortOrientation.by_columns, ws.Range("C6"), SortOrder.descending); wb.Save("../../TestFiles/Sort_by_columns_desc_after.xlsx"); Assert.Equals(true, ws.Range("A5").Value); Assert.Equals(ErrorValue.bad_reference, ws.Range("A6").Value); Assert.Equals(5, ws.Range("B5").Value); Assert.Equals(ErrorValue.bad_reference, ws.Range("B6").Value); Assert.Equals("", ws.Range("C5").Value); Assert.Equals("wtf", ws.Range("C6").Value); Assert.Equals("wtf", ws.Range("D5:D6").Value); Assert.Equals("55", ws.Range("E5:E6").Value); Assert.Equals("wtf", ws.Range("F5").Value); Assert.Equals(20, ws.Range("F6").Value); Assert.Equals(5, ws.Range("G5:H6").Value); Assert.Equals(ErrorValue.division_by_zero, ws.Range("I5").Value); Assert.Equals(-58.5, ws.Range("I6").Value); Assert.Equals("", ws.Range("J5:K6").Value); Assert.Equals(0, err_listener.AllHappenedErrors.Count); }
public void TestSortSimpleFormulas() { PrepareSimpleFormulas(); CellsUpdatedListener listener = new CellsUpdatedListener(ws); ErrorListener err_listener = new ErrorListener(editor); wb.Save("../../TestFiles/Sort_formulas_before.xlsx"); ws.Range("C3:G7").Sort(SortOrientation.by_rows, ws.Range("E3"), SortOrder.ascending); wb.Save("../../TestFiles/Sort_formulas_after.xlsx"); Assert.Equals(3, ws.Range("G3").Value); Assert.Equals(2, ws.Range("G4").Value); Assert.Equals(4, ws.Range("G5").Value); Assert.Equals(5, ws.Range("G6").Value); Assert.Equals(1, ws.Range("G7").Value); Assert.Equals(0, ws.Range("E3").Value); Assert.Equals(62.7, ws.Range("D4").Value); Assert.Equals(2.53591e76, (double)ws.Range("F5").Value, 0.00001e76); Assert.Equals(ErrorValue.wrong_value_type, ws.Range("C7").Value); Assert.Equals(0, err_listener.AllHappenedErrors.Count); }
public void TestCopyWithFormulasToAnotherSheet() { PrepareSimpleFormulas(); CellsUpdatedListener listener = new CellsUpdatedListener(ws); ErrorListener err_listener = new ErrorListener(editor); ws = wb.Sheets.AddEmptySheet("Sheet2"); ws.Range("A1").Value = 10; wb.Worksheets[0].Range("C3:G7").CopyTo(ws.Range("A2")); wb.Save("../../TestFiles/Copy_to_another_sheet.xlsx"); Assert.Equals(20, ws.Range("A2").Value); Assert.Equals("55.90169944", ws.Range("C2").DispValue); Assert.Equals("5^2.5", ws.Range("C2").Formula); Assert.Equals(ErrorValue.bad_reference, ws.Range("A3").Value); Assert.Equals("#REF!+#REF!", ws.Range("A3").Formula); Assert.Equals(ErrorValue.bad_reference, ws.Range("B3").Value); Assert.Equals("A2+A3", ws.Range("B3").Formula); Assert.Equals(ErrorValue.bad_reference, ws.Range("C4").Value); Assert.Equals("-B3+A2*2", ws.Range("C4").Formula); Assert.Equals(ErrorValue.bad_reference, ws.Range("D5").Value); Assert.Equals("C4^B3", ws.Range("D5").Formula); Assert.Equals(BorderStyle.thin, ws.Range("C6").Format.Borders.Bottom.Style); Assert.Equals(0, err_listener.AllHappenedErrors.Count); }
public void TestCopyWithFormulasToAnotherWorkbook() { PrepareSimpleFormulas(); CellsUpdatedListener listener = new CellsUpdatedListener(ws); ErrorListener err_listener = new ErrorListener(editor); ws = wb.ActiveWorksheet; Workbook dest_wb = editor.CreateWorkbook("www", 2); Worksheet dest_ws = dest_wb.Worksheets[1]; dest_ws.Range("D10").Formula = "E10 * F10"; dest_ws.Range("D8").Value = 3; dest_ws.Range("E8").Value = 2; ws.Range("C3:G7").CopyTo(dest_ws.Range("E9")); Assert.Equals(21, dest_ws.Range("D10").Value); // Check formulas out of copied range update Assert.Equals(BorderStyle.none, dest_ws.Range("E9").Format.Borders.Top.Style); // Check that no format copied Assert.Equals(BorderStyle.none, dest_ws.Range("E9").Format.Borders.Left.Style); // Check that no format copied Assert.Equals(4, dest_ws.Range("E9").Value); Assert.Equals(1, dest_ws.Range("H12").Value); Assert.Equals(0, err_listener.AllHappenedErrors.Count); }
public void TestOverlappedCopy() { PrepareSimpleData(); CellsUpdatedListener listener = new CellsUpdatedListener(ws); ErrorListener err_listener = new ErrorListener(editor); ws.GetUpdatedCells(); listener.Reset(); ws.Range("C4:F14").CopyTo(ws.Range("C4:F14")); Assert.Equals(0, listener.UpdatedCellsList.Count); ws.Range("C4:F14").CopyTo(ws.Range("E2:H12")); Assert.Equals(44, listener.UpdatedCellsList.Count); wb.Save("../../TestFiles/Copy_overlapped.xlsx"); Assert.Equals(5, ws.Range("E2").Value); Assert.Equals(BorderStyle.thin, ws.Range("E2").Format.Borders.Left.Style); Assert.Equals(BorderStyle.thin, ws.Range("E2").Format.Borders.Top.Style); Assert.Equals("wtf", ws.Range("E3").Value); Assert.Equals(BorderStyle.none, ws.Range("E3").Format.Borders.Bottom.Style); Assert.Equals(20, ws.Range("E4").Value); Assert.Equals("storage", ws.Range("E12").Value); Assert.Equals(BorderStyle.thin, ws.Range("E12").Format.Borders.Bottom.Style); Assert.Equals("", ws.Range("E11").Value); Assert.Equals(456.123, ws.Range("F6").Value); Assert.Equals(0, err_listener.AllHappenedErrors.Count); }
public void TestSimpleReferencesMovingOnColumnInsert() { Workbook wb = editor.CreateWorkbook("TestSimpleReferencesMovingOnColumnInsert"); Assert.NotNull(wb); Worksheet ws = wb.ActiveSheet as Worksheet; ErrorListener err_listener = new ErrorListener(editor); CellsUpdatedListener listener = new CellsUpdatedListener(ws); ws.Range("B1:G2").Value = 5; ws.Range("D3").Formula = "COUNT(D1:G2)"; ws.Range("E3").Formula = "COUNT($E$1:$G$2)"; ws.Range("A3").Formula = "COUNT(E1:$G$2)"; listener.Reset(); ws.Range("D1").InsertColumn(); Assert.Equals("COUNT(E1:H2)", ws.Range("E3").Formula); Assert.Equals(8, ws.Range("E3").Value); Assert.Equals("COUNT($F$1:$H$2)", ws.Range("F3").Formula); Assert.Equals(6, ws.Range("F3").Value); Assert.Equals("COUNT(F1:$H$2)", ws.Range("A3").Formula); Assert.Equals(6, ws.Range("A3").Value); Assert.Equals(14, listener.UpdatedCellsList.Count); Assert.Equals(0, err_listener.AllHappenedErrors.Count); }
public void TestSimpleReferencesMovingOnRowInsert() { Workbook wb = editor.CreateWorkbook("TestSimpleReferencesMovingOnRowInsert"); Assert.NotNull(wb); Worksheet ws = wb.ActiveSheet as Worksheet; ErrorListener err_listener = new ErrorListener(editor); CellsUpdatedListener listener = new CellsUpdatedListener(ws); ws.Range("B1:C10").Value = 5; ws.Range("D5").Formula = "COUNT(B5:C10)"; ws.Range("D6").Formula = "COUNT($B$6:$C$10)"; ws.Range("D1").Formula = "COUNT(B6:$C$10)"; listener.Reset(); ws.Range("5:500").InsertRows(4); Assert.Equals("COUNT(B9:C14)", ws.Range("D9").Formula); Assert.Equals(12, ws.Range("D9").Value); Assert.Equals("COUNT($B$10:$C$14)", ws.Range("D10").Formula); Assert.Equals(10, ws.Range("D10").Value); Assert.Equals("COUNT(B10:$C$14)", ws.Range("D1").Formula); Assert.Equals(10, ws.Range("D1").Value); Assert.Equals(25, listener.UpdatedCellsList.Count); Assert.Equals(0, err_listener.AllHappenedErrors.Count); }
public void TestMergeRelativeFormulas() { Workbook wb = editor.CreateWorkbook("MergeRelativeFormulas"); Assert.NotNull(wb); Worksheet ws = wb.ActiveSheet as Worksheet; ErrorListener err_listener = new ErrorListener(editor); CellsUpdatedListener listener = new CellsUpdatedListener(ws); ws.Range("C2:C4").Merge(); ws.Range("C2").Formula = "B3+$D$3"; ws.Range("B3").Value = 1; ws.Range("D3").Value = 2; Assert.Equals(3, ws.Range("C2").Value); Assert.Equals("B3+$D$3", ws.Range("C2").Formula); ws.Range("B3:D3").Merge(); Assert.Equals("", ws.Range("C2").Value); Assert.Equals("", ws.Range("C2").Formula); Assert.Equals(0, ws.Range("B2").Value); Assert.Equals("B3+$D$3", ws.Range("B2").Formula); Assert.Equals(0, err_listener.AllHappenedErrors.Count); }
public void Test_CSV_confusing_data_loader() { Workbook wb = editor.LoadWorkbookFromCSV("../../TestFiles/CSV_confuser.csv", 1251); Assert.NotNull(wb); Worksheet ws = wb.ActiveSheet as Worksheet; CellsUpdatedListener listener = new CellsUpdatedListener(ws); ErrorListener err_listener = new ErrorListener(editor); Assert.Equals("", ws.Range("A1").Value); Assert.Equals("\"----\"", ws.Range("B1").Value); Assert.Equals("Привет, ромашки", ws.Range("C1").Value); Assert.Equals("kind of", ws.Range("D1").Value); Assert.Equals("---23-023489-065^5*&547%&376(8()*&0-9-09,", ws.Range("A2").Value); Assert.Equals("", ws.Range("B2").Value); Assert.Equals("", ws.Range("C2").Value); Assert.Equals("", ws.Range("D2").Value); Assert.Equals("", ws.Range("A3").Value); Assert.Equals("", ws.Range("B3").Value); Assert.Equals("\"", ws.Range("C3").Value); Assert.Equals("Привет%ромашки", ws.Range("D3").Value); Assert.Equals("25-Jun", ws.Range("A4").Value); Assert.Equals(19, ws.Range("B4").Value); Assert.Equals(766726, ws.Range("C4").Value); Assert.Equals(-0.000000000000558, ws.Range("B5").Value); Assert.Equals(250000000000000, ws.Range("B6").Value); Assert.Equals(",kj,", ws.Range("A7").Value); Assert.Null(err_listener.LastError); wb.Close(); }
public void TestBoundaryDeleteColumnsWithMerge() { 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:B2").Merge(); ws.Range("B3:C4").Merge(); ws.Range("B5:E5").Merge(); ws.Range("B6:F6").Merge(); ws.Range("C7:C8").Merge(); ws.Range("C9:E9").Merge(); ws.Range("C10:F10").Merge(); ws.Range("E11:E12").Merge(); ws.Range("E13:F14").Merge(); ws.Range("F15:F16").Merge(); ws.Range("17:18").Merge(); ws.Range("C:E").DeleteColumns(); List <string> merged_areas = ws.GetMergedAreas(); Assert.Equals(6, merged_areas.Count); Assert.Equals("B1:B2", merged_areas[0]); Assert.Equals("B3:B4", merged_areas[1]); Assert.Equals("B6:C6", merged_areas[2]); Assert.Equals("C13:C14", merged_areas[3]); Assert.Equals("C15:C16", merged_areas[4]); Assert.Equals("17:18", merged_areas[5]); Assert.Null(err_listener.LastError); }
public void TestSortLoopFormulasComplex() { PrepareFormulasMakingLoopAfterSorting(); CellsUpdatedListener listener = new CellsUpdatedListener(ws); ErrorListener err_listener = new ErrorListener(editor); wb.Save("../../TestFiles/Sort_formulas_loop_before.xlsx"); ws.Range("C3:G7").Sort(SortOrientation.by_rows, ws.Range("D3"), SortOrder.ascending); wb.Save("../../TestFiles/Sort_formulas_loop_after.xlsx"); Assert.Equals(ErrorValue.bad_reference, ws.Range("C3").Value); // Main loop Assert.Equals(ErrorValue.bad_reference, ws.Range("D7").Value); // Main loop Assert.Equals(ErrorValue.bad_reference, ws.Range("G7").Value); // Use cyclic error results Assert.Equals(ErrorValue.bad_reference, ws.Range("C4").Value); // References itself Assert.Equals(ErrorValue.bad_reference, ws.Range("C7").Value); // Use main loop result Assert.Equals(ErrorValue.bad_reference, ws.Range("D6").Value); // Use cyclic error results Assert.Equals(ErrorValue.bad_reference, ws.Range("E4").Value); // References itself Assert.Equals(ErrorValue.bad_reference, ws.Range("F7").Value); // Use main loop result Assert.Equals(1, ws.Range("D3").Value); // Depends on the cell that is moved from an empty cell Assert.Equals(0, err_listener.AllHappenedErrors.Count); }
public void TestBoundaryInsertRowsWithMerge() { 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:A9").Merge(); ws.Range("B5:B10").Merge(); ws.Range("C9:C15").Merge(); ws.Range("D10:D15").Merge(); ws.Range("E9:F9").Merge(); ws.Range("G10:H10").Merge(); ws.Range("I9:I10").Merge(); ws.Range("A10").InsertRows(5); List <string> merged_areas = ws.GetMergedAreas(); Assert.Equals(7, merged_areas.Count); Assert.Equals("A5:A9", merged_areas[0]); Assert.Equals("B5:B15", merged_areas[1]); Assert.Equals("C9:C20", merged_areas[2]); Assert.Equals("D15:D20", merged_areas[3]); Assert.Equals("E9:F9", merged_areas[4]); Assert.Equals("G15:H15", merged_areas[5]); Assert.Equals("I9:I15", merged_areas[6]); }
public void TestDeleteColumnsWithMerge() { 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:G1").Merge(); ws.Range("G2:L2").Merge(); ws.Range("F3:H3").Merge(); ws.Range("B4:L4").Merge(); ws.Range("B5:C5").Merge(); ws.Range("K5:L5").Merge(); ws.Range("E:I").DeleteColumns(); List <string> merged_areas = ws.GetMergedAreas(); Assert.Equals(5, merged_areas.Count); Assert.Equals("B1:D1", merged_areas[0]); Assert.Equals("E2:G2", merged_areas[1]); Assert.Equals("B4:G4", merged_areas[2]); Assert.Equals("B5:C5", merged_areas[3]); Assert.Equals("F5:G5", merged_areas[4]); }
public void TestMerge() { wb = editor.LoadWorkbook("../../TestFiles/MergedAreas.xlsx"); Assert.NotNull(wb); Worksheet ws = wb.Sheets["Лист1"] as Worksheet; CellsUpdatedListener listener = new CellsUpdatedListener(ws); ErrorListener err_listener = new ErrorListener(editor); ws.Range("I4:I6").Merge(); Assert.Equals(11, ws.GetMergedAreas().Count); Assert.Equals(11, listener.UpdatedCellsList.Count); Assert.Equals(1, ws.Range("I4").Value); Assert.Equals("H4", ws.Range("I4").Formula); Assert.Equals("", ws.Range("I5").Value); Assert.Equals("", ws.Range("I5").Formula); listener.Reset(); ws.Range("I4:I6").UnMerge(); Assert.Equals(0, listener.UpdatedCellsList.Count); listener.Reset(); ws.Range("H6:I6").Merge(); Assert.Equals(11, ws.GetMergedAreas().Count); Assert.Equals(8, listener.UpdatedCellsList.Count); Assert.Equals(3, ws.Range("H6").Value); Assert.Equals("H4+H5", ws.Range("H6").Formula); listener.Reset(); // Cyclic ws.Range("I5:J5").Merge(); Assert.Equals(12, ws.GetMergedAreas().Count); Assert.Equals(9, listener.UpdatedCellsList.Count); Assert.Equals(ErrorValue.bad_reference, ws.Range("I5").Value); Assert.Equals(ErrorValue.bad_reference, ws.Range("H5").Value); Assert.Equals(ErrorValue.bad_reference, ws.Range("K4:K6").Value); listener.Reset(); ws.Range("G13:G14").Merge(); Assert.Equals(11, ws.GetMergedAreas().Count); Assert.Equals("F12:G15", ws.GetMergedAreas()[10]); Assert.Equals(1, ws.Range("F12").Value); Assert.Equals("", ws.Range("G12").Value); Assert.Equals("", ws.Range("F13:G15").Value); ws.Range("D16:F16").Merge(); Assert.Equals(8, ws.GetMergedAreas().Count); Assert.Equals("B12:G20", ws.GetMergedAreas()[7]); Assert.Equals(1, ws.Range("B12").Value); Assert.Equals("", ws.Range("B13:G20").Value); ws.Range("E5:F5").Merge(); Assert.Equals(4, ws.GetMergedAreas().Count); Assert.Equals("A2:G8", ws.GetMergedAreas()[3]); Assert.Equals(-625, ws.Range("A2").Value); Assert.Equals("C2-25^2", ws.Range("A2").Formula); Assert.Equals(0, err_listener.AllHappenedErrors.Count); wb.Save("../../TestFiles/MergedAreas_res1.xlsx"); }
public void TestRemovedCellsWrongSubstitution() { Workbook wb = editor.CreateWorkbook("TestRemovedCellsWrongSubstitution"); Assert.NotNull(wb); Worksheet ws = wb.ActiveSheet as Worksheet; ErrorListener err_listener = new ErrorListener(editor); CellsUpdatedListener listener = new CellsUpdatedListener(ws); ws.Range("A2").Value = 2; ws.Range("A3").Value = 4; ws.Range("B3").Formula = "A2+A3"; ws.Range("D2").Value = 2; ws.Range("D3").Formula = "D2*2"; ws.Range("E3").Formula = "D2^D3"; ws.Range("C3").Formula = "D2-5"; Assert.Equals(6, ws.Range("B3").Value); Assert.Equals(4, ws.Range("D3").Value); Assert.Equals(16, ws.Range("E3").Value); Assert.Equals(-3, ws.Range("C3").Value); ws.Range("A6").Value = 1; ws.Range("A5").Formula = "A6/2"; ws.Range("B5").Formula = "A6/4+A5"; Assert.Equals(0.75, ws.Range("B5").Value); ws.Range("A1").DeleteRows(); Assert.Equals("A1+A2", ws.Range("B2").Formula); Assert.Equals(6, ws.Range("B2").Value); ws.Range("A2").Value = 5; ws.Range("A3").Value = 12; Assert.Equals(7, ws.Range("B2").Value); Assert.Equals("D1*2", ws.Range("D2").Formula); Assert.Equals(4, ws.Range("D2").Value); Assert.Equals("D1^D2", ws.Range("E2").Formula); Assert.Equals(16, ws.Range("E2").Value); Assert.Equals("D1-5", ws.Range("C2").Formula); Assert.Equals(-3, ws.Range("C2").Value); ws.Range("D1").Value = 3; Assert.Equals(6, ws.Range("D2").Value); Assert.Equals(729, ws.Range("E2").Value); Assert.Equals(-2, ws.Range("C2").Value); Assert.Equals("A5/2", ws.Range("A4").Formula); Assert.Equals(0.5, ws.Range("A4").Value); Assert.Equals("A5/4+A4", ws.Range("B4").Formula); Assert.Equals(0.75, ws.Range("B4").Value); ws.Range("A5").Value = 4; Assert.Equals(2, ws.Range("A4").Value); Assert.Equals(3, ws.Range("B4").Value); Assert.Equals(0, err_listener.AllHappenedErrors.Count); }
public void Test_Simple3DArea() { Workbook wb = editor.CreateWorkbook("3D", 5); Assert.NotNull(wb); Worksheet ws = wb.ActiveSheet as Worksheet; CellsUpdatedListener listener = new CellsUpdatedListener(ws); ErrorListener err_listener = new ErrorListener(editor); wb.Worksheets[1].SetName("Hello.Dud"); wb.Worksheets[2].SetName("Sheet#3"); wb.Worksheets[3].SetName("で音楽"); wb.Worksheets[0].Range("A1:D6").Value = 6; wb.Worksheets[1].Range("B1:E5").Value = 7; wb.Worksheets[2].Range("B3:C9").Value = 8; wb.Worksheets[3].Range("A5:K6").Value = 9; wb.Worksheets[4].Range("C6:F11").Value = 10; ws.Range("A20").Formula = "SUM(B1:D8)"; Assert.Equals(108, ws.Range("A20").Value); Assert.Equals("SUM(B1:D8)", ws.Range("A20").Formula); ws.Range("A20").Formula = "SUM(Sheet1!B1:D9)"; Assert.Equals(108, ws.Range("A20").Value); Assert.Equals("SUM(Sheet1!B1:D9)", ws.Range("A20").Formula); ws.Range("A21").Formula = "SUM(Sheet1:で音楽!B4:C5)"; Assert.Equals(102, ws.Range("A21").Value); Assert.Equals("SUM(Sheet1:で音楽!B4:C5)", ws.Range("A21").Formula); ws.Range("B20").Formula = "SUM(Hello.Dud:Sheet#3!B4:C5)"; Assert.True(err_listener.LastError is UnknownOperandError); err_listener.Reset(); ws.Range("B20").Formula = "SUM('Hello.Dud:Sheet#3'!B4:C5)"; Assert.Equals(60, ws.Range("B20").Value); Assert.Equals("SUM('Hello.Dud:Sheet#3'!B4:C5)", ws.Range("B20").Formula); wb.Worksheets["Sheet#3"].Range("A4:D6").Value = 30; Assert.Equals(108, ws.Range("A20").Value); Assert.Equals(190, ws.Range("A21").Value); Assert.Equals(148, ws.Range("B20").Value); ws.Range("3:4").DeleteRows(); Assert.Equals(72, ws.Range("A18").Value); Assert.Equals("SUM(Sheet1!B1:D7)", ws.Range("A18").Formula); Assert.Equals(178, ws.Range("A19").Value); Assert.Equals("SUM(Sheet1:で音楽!B4:C5)", ws.Range("A19").Formula); Assert.Equals(148, ws.Range("B18").Value); Assert.Equals("SUM('Hello.Dud:Sheet#3'!B4:C5)", ws.Range("B18").Formula); Assert.Null(err_listener.LastError); wb.Close(); }
public void Test_HyperlinkDeletionUponCellsDeletion() { 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.Range("B3:D3").DeleteRows(); Assert.Equals(2, ws.Hyperlinks.Count); int counter = 0; foreach (Hyperlink hyp in ws.Hyperlinks) { switch (++counter) { case 1: Assert.Equals("B4", hyp.Anchor); Assert.Equals("Лист3", hyp.ReferredSheet.Name); Assert.Equals("A1:B2", hyp.ReferredRange); Assert.Equals("", hyp.Target); break; case 2: Assert.Equals("D4", hyp.Anchor); Assert.Equals("Лист1", hyp.ReferredSheet.Name); Assert.Equals("B3", hyp.ReferredRange); Assert.Equals("", hyp.Target); break; } } ws.Hyperlinks.AddAddressHyperlink("D4", "http://microsoft.com/"); Assert.Equals(2, ws.Hyperlinks.Count); counter = 0; foreach (Hyperlink hyp in ws.Hyperlinks) { switch (++counter) { case 2: Assert.Equals("D4", hyp.Anchor); Assert.Equals("http://microsoft.com/", hyp.Target); Assert.Null(hyp.ReferredSheet); break; } } Assert.Equals(counter, ws.Hyperlinks.Count); Assert.Null(err_listener.LastError); wb.Close(); }
public void TestIncreaseDecreaseDecimal() { 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); ws.Range("B3").Format.NumberFormatId = 2; Assert.Equals("0.00", ws.Range("B3").Format.NumberFormatCode); ws.Range("B3").Format.DecreaseDecimal(); Assert.Equals("0.0", ws.Range("B3").Format.NumberFormatCode); ws.Range("B3").Format.DecreaseDecimal(); Assert.Equals("0", ws.Range("B3").Format.NumberFormatCode); Assert.Equals(1, ws.Range("B3").Format.NumberFormatId); ws.Range("B3").Format.DecreaseDecimal(); Assert.Equals("0", ws.Range("B3").Format.NumberFormatCode); Assert.Equals(1, ws.Range("B3").Format.NumberFormatId); ws.Range("B3").Format.NumberFormatCode = "--###--"; ws.Range("B3").Format.DecreaseDecimal(); Assert.Equals("--###--", ws.Range("B3").Format.NumberFormatCode); ws.Range("B3").Format.IncreaseDecimal(); Assert.Equals("--###.0--", ws.Range("B3").Format.NumberFormatCode); ws.Range("B3").Format.IncreaseDecimal(); Assert.Equals("--###.00--", ws.Range("B3").Format.NumberFormatCode); ws.Range("B3").Format.DecreaseDecimal(); Assert.Equals("--###.0--", ws.Range("B3").Format.NumberFormatCode); ws.Range("B3").Format.DecreaseDecimal(); Assert.Equals("--###--", ws.Range("B3").Format.NumberFormatCode); ws.Range("B3").Format.DecreaseDecimal(); Assert.Equals("--###--", ws.Range("B3").Format.NumberFormatCode); ws.Range("B3").Format.NumberFormatId = 37; ws.Range("B3").Format.IncreaseDecimal(); ws.Range("B3").Format.IncreaseDecimal(); Assert.Equals("#,##0.00 ;(#,##0.00)", ws.Range("B3").Format.NumberFormatCode); ws.Range("B3").Format.NumberFormatId = 47; ws.Range("B3").Format.DecreaseDecimal(); Assert.Equals(47, ws.Range("B3").Format.NumberFormatId); ws.Range("B3").Format.IncreaseDecimal(); Assert.Equals(47, ws.Range("B3").Format.NumberFormatId); ws.Range("B3").Format.NumberFormatId = 10; ws.Range("B3").Format.DecreaseDecimal(); ws.Range("B3").Format.DecreaseDecimal(); Assert.Equals(9, ws.Range("B3").Format.NumberFormatId); Assert.Null(err_listener.LastError); }
public void TestAutoColorSetAsIndex() { Workbook wb = editor.LoadWorkbook("../../TestFiles/Auto_color_as_index.xls"); Assert.NotNull(wb); Worksheet ws = wb.ActiveSheet as Worksheet; CellsUpdatedListener listener = new CellsUpdatedListener(ws); ErrorListener err_listener = new ErrorListener(editor); Assert.Null(ws.Range("A3").Format.Borders.Top.Color.Rgb); Assert.Equals(0, err_listener.AllHappenedErrors.Count); }