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