public void Test_Wrong3DReferences()
{
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("B21").Formula = "SUM(Hello.Dudd!B4,Sheet1!B1)";
Assert.True(err_listener.LastError is FormulaFormatError);
err_listener.Reset();
ws.Range("B21").Formula = "Hello.Dud!#NAME?";
Assert.True(err_listener.LastError is FormulaFormatError);
err_listener.Reset();
ws.Range("B21").Formula = "Hello.Dud!#VALUE!";
Assert.True(err_listener.LastError is FormulaFormatError);
err_listener.Reset();
ws.Range("B21").Formula = "SUM(Hello.Dud!#REF! : Hello.Dud!B5)";
Assert.Null(err_listener.LastError);
Assert.Equals(ErrorValue.bad_reference, ws.Range("B21").Value);
ws.Range("B21").Formula = "Hello.Dud!#REF!";
Assert.Null(err_listener.LastError);
Assert.Equals(ErrorValue.bad_reference, ws.Range("B21").Value);
Assert.Equals("Hello.Dud!#REF!", ws.Range("B21").Formula);
ws.Range("B21").Formula = "SUM(Hello.Dud!#REF!,Hello.Dud!B5)";
Assert.Null(err_listener.LastError);
Assert.Equals(ErrorValue.bad_reference, ws.Range("B21").Value);
Assert.Null(err_listener.LastError);
wb.Close();
}
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_3DAreaReferenceOperators()
{
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;
wb.Save("../../TestFiles/3DRefSimple.xlsx");
ws.Range("B21").Formula = "SUM(('Hello.Dud:Sheet#3'!B4:C5,Sheet1!B1:D9) Sheet1:Sheet5!C:C)";
Assert.True(err_listener.LastError is FormulaFormatError);
err_listener.Reset();
ws.Range("B21").Formula = "SUM('Hello.Dud:Sheet#3'!B4:C5 : Sheet1!B1:D9)";
Assert.True(err_listener.LastError is FormulaFormatError);
err_listener.Reset();
ws.Range("B21").Formula = "SUM(Hello.Dud!B4:C5 : Sheet1!B1:D9)";
Assert.True(err_listener.LastError is FormulaFormatError);
err_listener.Reset();
ws.Range("B21").Formula = "SUM(Hello.Dud!B1:C5 : Hello.Dud!B4:D9)";
Assert.Null(err_listener.LastError);
Assert.Equals(105, ws.Range("B21").Value);
wb.Worksheets["Hello.Dud"].Range("B1").Value = 2;
Assert.Equals(100, ws.Range("B21").Value);
Assert.Null(err_listener.LastError);
wb.Close();
}
public void Test_Simple3DRef()
{
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,B2:D8)";
Assert.Equals(96, ws.Range("A20").Value);
Assert.Equals("SUM(B1,B2:D8)", ws.Range("A20").Formula);
ws.Range("A20").Formula = "SUM(Sheet1!B1:D7,Sheet1!D8)";
Assert.Equals(108, ws.Range("A20").Value);
Assert.Equals("SUM(Sheet1!B1:D7,Sheet1!D8)", ws.Range("A20").Formula);
ws.Range("A21").Formula = "SUM(Sheet1:で音楽!B4)";
Assert.Equals(21, ws.Range("A21").Value);
Assert.Equals("SUM(Sheet1:で音楽!B4)", ws.Range("A21").Formula);
ws.Range("B20").Formula = "SUM(Sheet#3!B5)";
Assert.True(err_listener.LastError is UnknownOperandError);
err_listener.Reset();
ws.Range("B20").Formula = "SUM('Sheet#3'!B5)";
Assert.Equals(8, ws.Range("B20").Value);
Assert.Equals("SUM('Sheet#3'!B5)", ws.Range("B20").Formula);
wb.Worksheets["Sheet#3"].Range("B4").Value = 30;
Assert.Equals(108, ws.Range("A20").Value);
Assert.Equals(43, ws.Range("A21").Value);
Assert.Equals(8, ws.Range("B20").Value);
Assert.Null(err_listener.LastError);
wb.Close();
}
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 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();
}
