public void TestFormatOnRender()
{
var report = new TestReport();
IXLWorksheet ws = report.Workbook.AddWorksheet("Test");
IXLRange range = ws.Range(1, 1, 30, 30);
ws.Cell(1, 1).Value = "''{sf:Format(p:FormatTest:Date, dd.MM.yyyy)}";
ws.Cell(1, 2).Value = "''{sf:Format(p:FormatTest:Date, d, p:FormatTest:InvariantFormat)}";
ws.Cell(1, 3).Value = "''{sf:Format(p:FormatTest:Date, d, p:FormatTest:UsFormat)}";
ws.Cell(1, 4).Value = "''{sf:Format(p:FormatTest:Date, d, p:FormatTest:RuFormat)}";
ws.Cell(1, 5).Value = "''{sf:Format(p:FormatTest:Date, d, RU)}";
ws.Cell(1, 6).Value = "''{sf:Format(p:FormatTest:Date, d, [string]ru-RU)}";
ws.Cell(1, 7).Value = "''{sf:Format(p:FormatTest:Date, d, en)}";
ws.Cell(1, 8).Value = "''{sf:Format(p:FormatTest:Date, d, \"en-US\")}";
ws.Cell(1, 9).Value = "''{sf:Format(p:FormatTest:Date, d, [int]25)}"; // ru
ws.Cell(1, 10).Value = "''{sf:Format(p:FormatTest:Date, d, [int]1049)}"; // ru-RU
ws.Cell(1, 11).Value = "''{sf:Format(p:FormatTest:Date, d, [int]9)}"; // en
ws.Cell(1, 12).Value = "''{sf:Format(p:FormatTest:Date, d, [int]1033)}"; // en-US
ws.Cell(1, 13).Value = "''{sf:Format(p:FormatTest:Date, d, [int]127)}"; // Invariant
var panel = new ExcelPanel(range, report, report.TemplateProcessor);
panel.Render();
ExcelAssert.AreWorkbooksContentEquals(TestHelper.GetExpectedWorkbook(nameof(SystemFunctionsTest), "TestFormatOnRender"), ws.Workbook);
//report.Workbook.SaveAs("test.xlsx");
}
public void TestGetNearestNamedPanelTest()
{
XLWorkbook wb = new XLWorkbook();
IXLWorksheet ws = wb.AddWorksheet("Test");
var excelReport = Substitute.For <object>();
var templateProcessor = Substitute.For <ITemplateProcessor>();
IXLRange range = ws.Range(1, 1, 3, 4);
range.AddToNamed("Parent", XLScope.Worksheet);
IXLNamedRange namedRange = ws.NamedRange("Parent");
IXLRange childRange = ws.Range(2, 1, 3, 4);
IXLRange childOfChildRange = ws.Range(3, 1, 3, 4);
childOfChildRange.AddToNamed("ChildOfChild", XLScope.Worksheet);
IXLNamedRange childOfChildNamedRange = ws.NamedRange("ChildOfChild");
IExcelPanel childOfChildPanel = new ExcelNamedPanel(childOfChildNamedRange, excelReport, templateProcessor);
IExcelPanel childPanel = new ExcelPanel(childRange, excelReport, templateProcessor);
IExcelPanel parentPanel = new ExcelNamedPanel(namedRange, excelReport, templateProcessor);
MethodInfo method = typeof(ExcelNamedPanel).GetMethod("GetNearestNamedParent", BindingFlags.Instance | BindingFlags.NonPublic);
Assert.IsNull(method.Invoke(childOfChildPanel, null));
childOfChildPanel.Parent = childPanel;
Assert.IsNull(method.Invoke(childOfChildPanel, null));
childPanel.Parent = parentPanel;
Assert.AreSame(parentPanel, method.Invoke(childOfChildPanel, null));
}
public void TestPanelRenderEvents()
{
var report = new TestReport();
IXLWorksheet ws = report.Workbook.AddWorksheet("Test");
IXLRange range = ws.Range(1, 1, 1, 2);
ws.Cell(1, 1).Value = "{p:StrParam}";
ws.Cell(1, 2).Value = "{p:IntParam}";
var panel = new ExcelPanel(range, report, report.TemplateProcessor)
{
BeforeRenderMethodName = "TestExcelPanelBeforeRender",
AfterRenderMethodName = "TestExcelPanelAfterRender",
};
panel.Render();
Assert.AreEqual(range, panel.ResultRange);
Assert.AreEqual(2, ws.CellsUsed(XLCellsUsedOptions.Contents).Count());
Assert.IsTrue((bool)ws.Cell(1, 1).Value);
Assert.AreEqual(11d, ws.Cell(1, 2).Value);
//report.Workbook.SaveAs("test.xlsx");
}
public void TestCallReportMethod()
{
var report = new TestRep();
var panel = new ExcelPanel(Substitute.For <IXLRange>(), report, Substitute.For <ITemplateProcessor>());
MethodInfo method = panel.GetType().GetMethod("CallReportMethod", BindingFlags.Instance | BindingFlags.NonPublic);
Assert.AreEqual($"Call {nameof(TestRep.Method1)}", method.Invoke(panel, new object[] { nameof(TestRep.Method1), null }));
Assert.AreEqual($"Call {nameof(TestRep.Method1)}", method.Invoke(panel, new object[] { nameof(TestRep.Method1), new object[] { } }));
ExceptionAssert.ThrowsBaseException <TargetParameterCountException>(() => method.Invoke(panel, new object[] { nameof(TestRep.Method1), new object[] { "Bad param" } }));
Assert.AreEqual($"Call {nameof(TestRep.Method2)}; params: str; 10", method.Invoke(panel, new object[] { nameof(TestRep.Method2), new object[] { "str", 10 } }));
Assert.AreEqual($"Call {nameof(TestRep.Method3)}; params: str; 10; str2", method.Invoke(panel, new object[] { nameof(TestRep.Method3), new object[] { "str", 10, "str2" } }));
ExceptionAssert.ThrowsBaseException <TargetParameterCountException>(() => method.Invoke(panel, new object[] { nameof(TestRep.Method3), new object[] { "str", 10 } }));
var eventArgs = new PanelBeforeRenderEventArgs();
Assert.IsFalse(eventArgs.IsCanceled);
Assert.IsNull(method.Invoke(panel, new object[] { nameof(TestRep.Method4), new object[] { eventArgs } }));
Assert.IsTrue(eventArgs.IsCanceled);
ExceptionAssert.ThrowsBaseException <AmbiguousMatchException>(() => method.Invoke(panel, new object[] { nameof(TestRep.Method5), new object[] { eventArgs } }));
ExceptionAssert.ThrowsBaseException <MethodNotFoundException>(() => method.Invoke(panel, new object[] { "Method6", null }), $"Cannot find public instance method \"Method6\" in type \"{report.GetType().Name}\"");
ExceptionAssert.ThrowsBaseException <MethodNotFoundException>(() => method.Invoke(panel, new object[] { "BadMethod", null }), $"Cannot find public instance method \"BadMethod\" in type \"{report.GetType().Name}\"");
ExceptionAssert.ThrowsBaseException <ArgumentException>(() => method.Invoke(panel, new object[] { null, null }));
ExceptionAssert.ThrowsBaseException <ArgumentException>(() => method.Invoke(panel, new object[] { string.Empty, null }));
ExceptionAssert.ThrowsBaseException <ArgumentException>(() => method.Invoke(panel, new object[] { " ", null }));
}
public void TestRemoveAllNamesRecursive()
{
XLWorkbook wb = new XLWorkbook();
IXLWorksheet ws = wb.AddWorksheet("Test");
var excelReport = Substitute.For <object>();
var templateProcessor = Substitute.For <ITemplateProcessor>();
IXLRange range = ws.Range(1, 1, 3, 4);
range.AddToNamed("Parent", XLScope.Worksheet);
IXLNamedRange namedRange = ws.NamedRange("Parent");
IExcelNamedPanel parentPanel = new ExcelNamedPanel(namedRange, excelReport, templateProcessor);
IXLRange childRange1 = ws.Range(1, 1, 1, 4);
childRange1.AddToNamed("Child", XLScope.Worksheet);
IXLNamedRange namedChildRange = ws.NamedRange("Child");
IExcelNamedPanel childPanel1 = new ExcelNamedPanel(namedChildRange, excelReport, templateProcessor);
childPanel1.Parent = parentPanel;
IXLRange childRange2 = ws.Range(2, 1, 3, 4);
IExcelPanel childPanel2 = new ExcelPanel(childRange2, excelReport, templateProcessor);
childPanel2.Parent = parentPanel;
parentPanel.Children = new List <IExcelPanel> {
childPanel1, childPanel2
};
IXLRange childOfChild1Range = ws.Range(1, 1, 1, 4);
childOfChild1Range.AddToNamed("ChildOfChild1", XLScope.Worksheet);
IXLNamedRange namedChildOfChild1RangeRange = ws.NamedRange("ChildOfChild1");
IExcelNamedPanel childOfChild1Panel = new ExcelNamedPanel(namedChildOfChild1RangeRange, excelReport, templateProcessor);
childOfChild1Panel.Parent = childPanel1;
childPanel1.Children = new List <IExcelPanel> {
childOfChild1Panel
};
IXLRange childOfChild2Range = ws.Range(3, 1, 3, 4);
childOfChild2Range.AddToNamed("ChildOfChild2", XLScope.Worksheet);
IXLNamedRange namedChildOfChild2RangeRange = ws.NamedRange("ChildOfChild2");
IExcelNamedPanel childOfChild2Panel = new ExcelNamedPanel(namedChildOfChild2RangeRange, excelReport, templateProcessor);
childOfChild2Panel.Parent = childPanel2;
childPanel2.Children = new List <IExcelPanel> {
childOfChild2Panel
};
ExcelNamedPanel.RemoveAllNamesRecursive(parentPanel);
Assert.AreEqual(0, ws.NamedRanges.Count());
//wb.SaveAs("test.xlsx");
}
public void TestOuterMethodCallCrash()
{
var report = new TestReport();
IXLWorksheet ws = report.Workbook.AddWorksheet("Test");
IXLRange range = ws.Range(1, 1, 1, 1);
ws.Cell(1, 1).Value = "''{m:Format2(m:Counter(), \"d\")}";
var panel = new ExcelPanel(range, report, report.TemplateProcessor);
ExceptionAssert.Throws <MethodNotFoundException>(() => panel.Render(), "Could not find public method \"Format2\" in type \"TestReport\" and all its parents. MethodCallTemplate: Format2(m:Counter(), \"d\")");
}
public void TestSimplePanelExpansion()
{
var report = new TestReport();
IXLWorksheet ws = report.Workbook.AddWorksheet("Test");
IXLRange simplePanelRange = ws.Range(1, 1, 3, 5);
var simplePanel = new ExcelPanel(simplePanelRange, report, report.TemplateProcessor);
IXLRange dataPanelRange = ws.Range(2, 2, 2, 5);
dataPanelRange.AddToNamed("d_Data", XLScope.Worksheet);
ws.Cell(2, 2).Value = "{di:Name}";
ws.Cell(2, 3).Value = "{di:Date}";
ws.Cell(2, 4).Value = "{di:Sex}";
ws.Cell(2, 5).Value = "{di:Sum}";
var dataPanel = new ExcelDataSourcePanel("m:DataProvider:GetIEnumerable()", ws.NamedRange("d_Data"), report, report.TemplateProcessor)
{
Parent = simplePanel,
};
IXLRange totalsPanelRange = ws.Range(3, 2, 3, 5);
totalsPanelRange.AddToNamed("t_Totals", XLScope.Worksheet);
ws.Cell(3, 2).Value = "{Max(di:Name)}";
ws.Cell(3, 3).Value = "{Min(di:Date)}";
ws.Cell(3, 4).Value = "{Max(di:Sex)}";
ws.Cell(3, 5).Value = "{Sum(di:Sum)}";
var totalsPanel = new ExcelTotalsPanel("m:DataProvider:GetIEnumerable()", ws.NamedRange("t_Totals"), report, report.TemplateProcessor)
{
Parent = simplePanel,
};
simplePanel.Children = new[] { dataPanel, totalsPanel };
simplePanel.Render();
Assert.AreEqual(ws.Range(1, 1, 5, 5), simplePanel.ResultRange);
ExcelAssert.AreWorkbooksContentEquals(TestHelper.GetExpectedWorkbook(nameof(PanelRenderTest),
nameof(TestSimplePanelExpansion)), ws.Workbook);
//report.Workbook.SaveAs("test.xlsx");
}
public void TestExpandSimplePanel_ChildAcrossWidth_ChildCenter_NoShift()
{
var report = new TestReport();
IXLWorksheet ws = report.Workbook.AddWorksheet("Test");
IXLRange parentRange = ws.Range(2, 2, 4, 5);
parentRange.AddToNamed("ParentRange", XLScope.Worksheet);
parentRange.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
parentRange.Style.Border.OutsideBorderColor = XLColor.Black;
IXLRange childRange = ws.Range(3, 2, 3, 5);
childRange.AddToNamed("ChildRange", XLScope.Worksheet);
childRange.Style.Border.OutsideBorder = XLBorderStyleValues.Dashed;
childRange.Style.Border.OutsideBorderColor = XLColor.Blue;
ws.Cell(2, 2).Value = "{p:StrParam}";
ws.Cell(3, 3).Value = "{di:Name}";
ws.Cell(3, 4).Value = "{di:Date}";
ws.Cell(3, 5).Value = "{di:Sum}";
var parentPanel = new ExcelPanel(parentRange, report, report.TemplateProcessor);
var childPanel = new ExcelDataSourcePanel("m:DataProvider:GetIEnumerable()", ws.NamedRange("ChildRange"), report, report.TemplateProcessor)
{
Parent = parentPanel,
ShiftType = ShiftType.NoShift,
};
parentPanel.Children = new[] { childPanel };
parentPanel.Render();
Assert.AreEqual(ws.Range(2, 2, 5, 5), parentPanel.ResultRange);
//report.Workbook.SaveAs("test.xlsx");
}
public void TestMakePanelsHierarchy()
{
var wb = new XLWorkbook();
IXLWorksheet ws = wb.AddWorksheet("Test");
IXLRange panel1Range = ws.Range(1, 1, 4, 4);
IXLRange panel2Range = ws.Range(1, 1, 2, 4);
panel2Range.AddToNamed("Panel2", XLScope.Worksheet);
IXLRange panel3Range = ws.Range(2, 1, 2, 4);
panel3Range.AddToNamed("Panel3", XLScope.Workbook);
IXLRange panel4Range = ws.Range(5, 1, 6, 5);
IXLRange panel5Range = ws.Range(6, 1, 6, 5);
panel5Range.AddToNamed("Panel5", XLScope.Worksheet);
IXLRange panel6Range = ws.Range(3, 1, 4, 4);
IXLRange panel7Range = ws.Range(10, 10, 10, 10);
IXLRange panel8Range = ws.Range(8, 9, 9, 10);
panel8Range.AddToNamed("Panel8", XLScope.Worksheet);
var panel1 = new ExcelPanel(panel1Range, new object(), Substitute.For <ITemplateProcessor>());
var panel2 = new ExcelDataSourcePanel("Stub", ws.NamedRange("Panel2"), new object(),
Substitute.For <ITemplateProcessor>());
var panel3 = new ExcelDataSourcePanel("Stub", wb.NamedRange("Panel3"), new object(),
Substitute.For <ITemplateProcessor>());
var panel4 = new ExcelPanel(panel4Range, new object(), Substitute.For <ITemplateProcessor>());
var panel5 = new ExcelDataSourceDynamicPanel("Stub", ws.NamedRange("Panel5"), new object(),
Substitute.For <ITemplateProcessor>());
var panel6 = new ExcelPanel(panel6Range, new object(), Substitute.For <ITemplateProcessor>());
var panel7 = new ExcelPanel(panel7Range, new object(), Substitute.For <ITemplateProcessor>());
var panel8 = new ExcelTotalsPanel("Stub", ws.NamedRange("Panel8"), new object(),
Substitute.For <ITemplateProcessor>());
IDictionary <string, (IExcelPanel, string)> panelsFlatView = new Dictionary <string, (IExcelPanel, string)>
{
public void TestMove()
{
XLWorkbook wb = new XLWorkbook();
IXLWorksheet ws = wb.AddWorksheet("Test");
var excelReport = Substitute.For <object>();
var templateProcessor = Substitute.For <ITemplateProcessor>();
IXLRange range = ws.Range(1, 1, 4, 5);
range.AddToNamed("parentRange", XLScope.Worksheet);
IXLNamedRange namedParentRange = ws.NamedRange("parentRange");
IXLRange childRange1 = ws.Range(1, 1, 2, 5);
IXLRange childRange2 = ws.Range(3, 1, 4, 5);
childRange2.AddToNamed("childRange2", XLScope.Worksheet);
IXLNamedRange namedChildRange = ws.NamedRange("childRange2");
IXLRange childOfChildRange1 = ws.Range(2, 1, 2, 5);
childOfChildRange1.AddToNamed("childOfChildRange1", XLScope.Worksheet);
IXLNamedRange childOfChildNamedRange = ws.NamedRange("childOfChildRange1");
IXLRange childOfChildRange2 = ws.Range(4, 1, 4, 5);
var panel = new ExcelNamedPanel(namedParentRange, excelReport, templateProcessor)
{
Children = new List <IExcelPanel>
{
new ExcelPanel(childRange1, excelReport, templateProcessor)
{
Children = new List <IExcelPanel>
{
new ExcelDataSourcePanel("fn:DataSource:Method()", childOfChildNamedRange, excelReport, templateProcessor)
}
},
new ExcelNamedPanel(namedChildRange, excelReport, templateProcessor)
{
Children = new List <IExcelPanel>
{
new ExcelPanel(childOfChildRange2, excelReport, templateProcessor)
}
},
}
};
IExcelPanel globalParent = new ExcelPanel(ws.Range(1, 1, 8, 10), excelReport, templateProcessor);
panel.Children.First().Children.First().Parent = panel.Children.First();
panel.Children.Last().Children.First().Parent = panel.Children.Last();
panel.Children.ToList().ForEach(c => c.Parent = panel);
panel.Parent = globalParent;
panel.Move(ws.Cell(5, 6));
Assert.AreEqual(ws.Cell(5, 6), panel.Range.FirstCell());
Assert.AreEqual(ws.Cell(8, 10), panel.Range.LastCell());
Assert.AreEqual("parentRange", ((IExcelNamedPanel)panel).Name);
Assert.AreSame(globalParent, panel.Parent);
Assert.AreEqual(2, panel.Children.Count());
Assert.AreEqual(ws.Cell(5, 6), panel.Children.First().Range.FirstCell());
Assert.AreEqual(ws.Cell(6, 10), panel.Children.First().Range.LastCell());
Assert.IsInstanceOf <ExcelPanel>(panel.Children.First());
Assert.IsNotInstanceOf <IExcelNamedPanel>(panel.Children.First());
Assert.AreSame(panel, panel.Children.First().Parent);
Assert.AreEqual(ws.Cell(7, 6), panel.Children.Last().Range.FirstCell());
Assert.AreEqual(ws.Cell(8, 10), panel.Children.Last().Range.LastCell());
Assert.AreEqual("childRange2", ((IExcelNamedPanel)panel.Children.Last()).Name);
Assert.AreSame(panel, panel.Children.First().Parent);
Assert.AreEqual(1, panel.Children.First().Children.Count());
Assert.AreEqual(ws.Cell(6, 6), panel.Children.First().Children.First().Range.FirstCell());
Assert.AreEqual(ws.Cell(6, 10), panel.Children.First().Children.First().Range.LastCell());
Assert.IsInstanceOf <ExcelDataSourcePanel>(panel.Children.First().Children.First());
Assert.AreEqual("childOfChildRange1", ((IExcelNamedPanel)panel.Children.First().Children.First()).Name);
Assert.AreSame(panel.Children.First(), panel.Children.First().Children.First().Parent);
Assert.AreEqual(1, panel.Children.Last().Children.Count());
Assert.AreEqual(ws.Cell(8, 6), panel.Children.Last().Children.First().Range.FirstCell());
Assert.AreEqual(ws.Cell(8, 10), panel.Children.Last().Children.First().Range.LastCell());
Assert.IsInstanceOf <ExcelPanel>(panel.Children.Last().Children.First());
Assert.IsNotInstanceOf <IExcelNamedPanel>(panel.Children.Last().Children.First());
Assert.AreSame(panel.Children.Last(), panel.Children.Last().Children.First().Parent);
Assert.AreEqual(3, ws.NamedRanges.Count());
panel.Move(ws.Cell(6, 6));
Assert.AreEqual(ws.Cell(6, 6), panel.Range.FirstCell());
Assert.AreEqual(ws.Cell(9, 10), panel.Range.LastCell());
Assert.IsNull(panel.Parent);
Assert.AreEqual(2, panel.Children.Count());
Assert.AreEqual(ws.Cell(6, 6), panel.Children.First().Range.FirstCell());
Assert.AreEqual(ws.Cell(7, 10), panel.Children.First().Range.LastCell());
Assert.IsInstanceOf <ExcelPanel>(panel.Children.First());
Assert.IsNotInstanceOf <IExcelNamedPanel>(panel.Children.First());
Assert.AreSame(panel, panel.Children.First().Parent);
Assert.AreEqual(ws.Cell(8, 6), panel.Children.Last().Range.FirstCell());
Assert.AreEqual(ws.Cell(9, 10), panel.Children.Last().Range.LastCell());
Assert.AreEqual("childRange2", ((IExcelNamedPanel)panel.Children.Last()).Name);
Assert.AreSame(panel, panel.Children.First().Parent);
Assert.AreEqual(1, panel.Children.First().Children.Count());
Assert.AreEqual(ws.Cell(7, 6), panel.Children.First().Children.First().Range.FirstCell());
Assert.AreEqual(ws.Cell(7, 10), panel.Children.First().Children.First().Range.LastCell());
Assert.IsInstanceOf <ExcelDataSourcePanel>(panel.Children.First().Children.First());
Assert.AreEqual("childOfChildRange1", ((IExcelNamedPanel)panel.Children.First().Children.First()).Name);
Assert.AreSame(panel.Children.First(), panel.Children.First().Children.First().Parent);
Assert.AreEqual(1, panel.Children.Last().Children.Count());
Assert.AreEqual(ws.Cell(9, 6), panel.Children.Last().Children.First().Range.FirstCell());
Assert.AreEqual(ws.Cell(9, 10), panel.Children.Last().Children.First().Range.LastCell());
Assert.IsInstanceOf <ExcelPanel>(panel.Children.Last().Children.First());
Assert.IsNotInstanceOf <IExcelNamedPanel>(panel.Children.Last().Children.First());
Assert.AreSame(panel.Children.Last(), panel.Children.Last().Children.First().Parent);
Assert.AreEqual(3, ws.NamedRanges.Count());
//wb.SaveAs("test.xlsx");
}
public void TestCopy()
{
var wb = new XLWorkbook();
IXLWorksheet ws = wb.AddWorksheet("Test");
var excelReport = Substitute.For <object>();
var templateProcessor = Substitute.For <ITemplateProcessor>();
IXLRange range = ws.Range(1, 1, 3, 4);
range.AddToNamed("Parent", XLScope.Worksheet);
IXLNamedRange namedRange = ws.NamedRange("Parent");
IXLRange childRange = ws.Range(2, 1, 3, 4);
childRange.AddToNamed("Child", XLScope.Worksheet);
IXLNamedRange namedChildRange = ws.NamedRange("Child");
IXLRange childOfChildRange = ws.Range(3, 1, 3, 4);
childOfChildRange.AddToNamed("ChildOfChild", XLScope.Worksheet);
IXLNamedRange namedChildOfChildRange = ws.NamedRange("ChildOfChild");
var panel = new ExcelNamedPanel(namedRange, excelReport, templateProcessor)
{
Children = new List <IExcelPanel>
{
new ExcelNamedPanel(namedChildRange, excelReport, templateProcessor)
{
Children = new List <IExcelPanel>
{
new ExcelDataSourcePanel("fn:DataSource:Method()", namedChildOfChildRange, excelReport, templateProcessor)
{
RenderPriority = 30,
Type = PanelType.Horizontal,
ShiftType = ShiftType.Row,
BeforeRenderMethodName = "BeforeRenderMethod3",
AfterRenderMethodName = "AfterRenderMethod3",
BeforeDataItemRenderMethodName = "BeforeDataItemRenderMethodName",
AfterDataItemRenderMethodName = "AfterDataItemRenderMethodName",
GroupBy = "2,4",
}
},
RenderPriority = 20,
ShiftType = ShiftType.Row,
BeforeRenderMethodName = "BeforeRenderMethod2",
AfterRenderMethodName = "AfterRenderMethod2",
}
},
RenderPriority = 10,
Type = PanelType.Horizontal,
ShiftType = ShiftType.NoShift,
BeforeRenderMethodName = "BeforeRenderMethod1",
AfterRenderMethodName = "AfterRenderMethod1",
};
IExcelNamedPanel copiedPanel = (IExcelNamedPanel)panel.Copy(ws.Cell(5, 5));
Assert.AreSame(excelReport, copiedPanel.GetType().GetField("_report", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(copiedPanel));
Assert.AreSame(templateProcessor, copiedPanel.GetType().GetField("_templateProcessor", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(copiedPanel));
Assert.IsTrue(Regex.IsMatch(copiedPanel.Name, @"Parent_[0-9a-f]{32}"));
Assert.AreEqual(ws.Cell(5, 5), copiedPanel.Range.FirstCell());
Assert.AreEqual(ws.Cell(7, 8), copiedPanel.Range.LastCell());
Assert.AreEqual(10, copiedPanel.RenderPriority);
Assert.AreEqual(PanelType.Horizontal, copiedPanel.Type);
Assert.AreEqual(ShiftType.NoShift, copiedPanel.ShiftType);
Assert.AreEqual("BeforeRenderMethod1", copiedPanel.BeforeRenderMethodName);
Assert.AreEqual("AfterRenderMethod1", copiedPanel.AfterRenderMethodName);
Assert.IsNull(copiedPanel.Parent);
Assert.AreEqual(1, copiedPanel.Children.Count);
Assert.AreSame(excelReport, copiedPanel.Children.First().GetType().GetField("_report", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(copiedPanel.Children.First()));
Assert.AreSame(templateProcessor, copiedPanel.Children.First().GetType().GetField("_templateProcessor", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(copiedPanel.Children.First()));
Assert.IsTrue(Regex.IsMatch(((IExcelNamedPanel)copiedPanel.Children.First()).Name, @"Parent_[0-9a-f]{32}_Child"));
Assert.AreEqual(ws.Cell(6, 5), copiedPanel.Children.First().Range.FirstCell());
Assert.AreEqual(ws.Cell(7, 8), copiedPanel.Children.First().Range.LastCell());
Assert.AreEqual(20, copiedPanel.Children.First().RenderPriority);
Assert.AreEqual(PanelType.Vertical, copiedPanel.Children.First().Type);
Assert.AreEqual(ShiftType.Row, copiedPanel.Children.First().ShiftType);
Assert.AreEqual("BeforeRenderMethod2", copiedPanel.Children.First().BeforeRenderMethodName);
Assert.AreEqual("AfterRenderMethod2", copiedPanel.Children.First().AfterRenderMethodName);
Assert.AreSame(copiedPanel, copiedPanel.Children.First().Parent);
Assert.AreEqual(1, copiedPanel.Children.First().Children.Count);
Assert.AreSame(excelReport, copiedPanel.Children.First().Children.First().GetType().GetField("_report", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(copiedPanel.Children.First().Children.First()));
Assert.AreSame(templateProcessor, copiedPanel.Children.First().Children.First().GetType().GetField("_templateProcessor", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(copiedPanel.Children.First().Children.First()));
Assert.IsTrue(Regex.IsMatch(((IExcelNamedPanel)copiedPanel.Children.First().Children.First()).Name, @"Parent_[0-9a-f]{32}_Child_ChildOfChild"));
Assert.IsInstanceOf <ExcelDataSourcePanel>(copiedPanel.Children.First().Children.First());
Assert.AreEqual(ws.Cell(7, 5), copiedPanel.Children.First().Children.First().Range.FirstCell());
Assert.AreEqual(ws.Cell(7, 8), copiedPanel.Children.First().Children.First().Range.LastCell());
Assert.AreEqual(30, copiedPanel.Children.First().Children.First().RenderPriority);
Assert.AreEqual(PanelType.Horizontal, copiedPanel.Children.First().Children.First().Type);
Assert.AreEqual(ShiftType.Row, copiedPanel.Children.First().Children.First().ShiftType);
Assert.AreEqual("BeforeRenderMethod3", copiedPanel.Children.First().Children.First().BeforeRenderMethodName);
Assert.AreEqual("AfterRenderMethod3", copiedPanel.Children.First().Children.First().AfterRenderMethodName);
Assert.AreEqual("BeforeDataItemRenderMethodName", ((ExcelDataSourcePanel)copiedPanel.Children.First().Children.First()).BeforeDataItemRenderMethodName);
Assert.AreEqual("AfterDataItemRenderMethodName", ((ExcelDataSourcePanel)copiedPanel.Children.First().Children.First()).AfterDataItemRenderMethodName);
Assert.AreEqual("2,4", ((ExcelDataSourcePanel)copiedPanel.Children.First().Children.First()).GroupBy);
Assert.AreSame(copiedPanel.Children.First(), copiedPanel.Children.First().Children.First().Parent);
namedRange.Delete();
namedChildRange.Delete();
copiedPanel.Delete();
copiedPanel.Children.First().Delete();
IExcelPanel globalParent = new ExcelPanel(ws.Range(1, 1, 20, 20), excelReport, templateProcessor);
range = ws.Range(1, 1, 3, 4);
range.AddToNamed("Parent", XLScope.Worksheet);
namedRange = ws.NamedRange("Parent");
IXLRange childRange1 = ws.Range(1, 1, 1, 4);
childRange1.AddToNamed("Child", XLScope.Worksheet);
namedChildRange = ws.NamedRange("Child");
IXLRange childRange2 = ws.Range(2, 1, 3, 4);
childOfChildRange = ws.Range(3, 1, 3, 4);
childOfChildRange.AddToNamed("ChildOfChild", XLScope.Worksheet);
namedChildOfChildRange = ws.NamedRange("ChildOfChild");
panel = new ExcelNamedPanel(namedRange, excelReport, templateProcessor)
{
Parent = globalParent,
Children = new List <IExcelPanel>
{
new ExcelNamedPanel(namedChildRange, excelReport, templateProcessor),
new ExcelPanel(childRange2, excelReport, templateProcessor)
{
Children = new List <IExcelPanel>
{
new ExcelNamedPanel(namedChildOfChildRange, excelReport, templateProcessor)
},
RenderPriority = 10,
Type = PanelType.Horizontal,
ShiftType = ShiftType.NoShift,
BeforeRenderMethodName = "BeforeRenderMethod",
AfterRenderMethodName = "AfterRenderMethod",
},
},
};
copiedPanel = (IExcelNamedPanel)panel.Copy(ws.Cell(5, 5));
Assert.IsTrue(Regex.IsMatch(copiedPanel.Name, @"Parent_[0-9a-f]{32}"));
Assert.AreEqual(ws.Cell(5, 5), copiedPanel.Range.FirstCell());
Assert.AreEqual(ws.Cell(7, 8), copiedPanel.Range.LastCell());
Assert.AreSame(globalParent, copiedPanel.Parent);
Assert.AreEqual(2, copiedPanel.Children.Count);
Assert.IsTrue(Regex.IsMatch(((IExcelNamedPanel)copiedPanel.Children.First()).Name, @"Parent_[0-9a-f]{32}_Child"));
Assert.AreEqual(ws.Cell(5, 5), copiedPanel.Children.First().Range.FirstCell());
Assert.AreEqual(ws.Cell(5, 8), copiedPanel.Children.First().Range.LastCell());
Assert.AreSame(copiedPanel, copiedPanel.Children.First().Parent);
Assert.IsInstanceOf <ExcelPanel>(copiedPanel.Children.Last());
Assert.IsNotInstanceOf <ExcelNamedPanel>(copiedPanel.Children.Last());
Assert.AreEqual(ws.Cell(6, 5), copiedPanel.Children.Last().Range.FirstCell());
Assert.AreEqual(ws.Cell(7, 8), copiedPanel.Children.Last().Range.LastCell());
Assert.AreEqual(10, copiedPanel.Children.Last().RenderPriority);
Assert.AreEqual(PanelType.Horizontal, copiedPanel.Children.Last().Type);
Assert.AreEqual(ShiftType.NoShift, copiedPanel.Children.Last().ShiftType);
Assert.AreEqual("BeforeRenderMethod", copiedPanel.Children.Last().BeforeRenderMethodName);
Assert.AreEqual("AfterRenderMethod", copiedPanel.Children.Last().AfterRenderMethodName);
Assert.AreSame(copiedPanel, copiedPanel.Children.Last().Parent);
Assert.AreEqual(1, copiedPanel.Children.Last().Children.Count);
Assert.IsTrue(Regex.IsMatch(((IExcelNamedPanel)copiedPanel.Children.Last().Children.First()).Name, @"ChildOfChild_[0-9a-f]{32}"));
Assert.AreEqual(ws.Cell(7, 5), copiedPanel.Children.Last().Children.First().Range.FirstCell());
Assert.AreEqual(ws.Cell(7, 8), copiedPanel.Children.Last().Children.First().Range.LastCell());
Assert.AreSame(copiedPanel.Children.Last(), copiedPanel.Children.Last().Children.First().Parent);
namedRange.Delete();
namedChildRange.Delete();
namedChildOfChildRange.Delete();
copiedPanel.Delete();
copiedPanel.Children.First().Delete();
copiedPanel.Children.Last().Children.First().Delete();
globalParent = new ExcelPanel(ws.Range(1, 1, 7, 7), excelReport, templateProcessor);
range = ws.Range(1, 1, 3, 4);
range.AddToNamed("Parent", XLScope.Worksheet);
namedRange = ws.NamedRange("Parent");
childRange = ws.Range(1, 1, 1, 4);
childRange.AddToNamed("Child", XLScope.Worksheet);
namedChildRange = ws.NamedRange("Child");
panel = new ExcelNamedPanel(namedRange, excelReport, templateProcessor)
{
Parent = globalParent,
Children = new List <IExcelPanel> {
new ExcelNamedPanel(namedChildRange, excelReport, templateProcessor)
},
};
copiedPanel = (IExcelNamedPanel)panel.Copy(ws.Cell(5, 5));
Assert.IsTrue(Regex.IsMatch(copiedPanel.Name, @"Parent_[0-9a-f]{32}"));
Assert.AreEqual(ws.Cell(5, 5), copiedPanel.Range.FirstCell());
Assert.AreEqual(ws.Cell(7, 8), copiedPanel.Range.LastCell());
Assert.IsNull(copiedPanel.Parent);
Assert.AreEqual(1, copiedPanel.Children.Count);
Assert.IsTrue(Regex.IsMatch(((IExcelNamedPanel)copiedPanel.Children.First()).Name, @"Parent_[0-9a-f]{32}_Child"));
Assert.AreEqual(ws.Cell(5, 5), copiedPanel.Children.First().Range.FirstCell());
Assert.AreEqual(ws.Cell(5, 8), copiedPanel.Children.First().Range.LastCell());
Assert.AreSame(copiedPanel, copiedPanel.Children.First().Parent);
copiedPanel = (IExcelNamedPanel)panel.Copy(ws.Cell(5, 5), false);
Assert.IsTrue(Regex.IsMatch(copiedPanel.Name, @"Parent_[0-9a-f]{32}"));
Assert.AreEqual(ws.Cell(5, 5), copiedPanel.Range.FirstCell());
Assert.AreEqual(ws.Cell(7, 8), copiedPanel.Range.LastCell());
Assert.IsNull(copiedPanel.Parent);
Assert.AreEqual(0, copiedPanel.Children.Count);
ExceptionAssert.Throws <ArgumentNullException>(() => panel.Copy(null));
//wb.SaveAs("test.xlsx");
}
/// <summary>
/// Render report based on <paramref name="reportTemplate"/> and report object passed in the constructor
/// </summary>
/// <param name="reportTemplate">Excel report template</param>
/// <param name="worksheets">List of worksheets that must be rendered</param>
/// <exception cref="ArgumentNullException">Thrown when <paramref name="reportTemplate"/> is null</exception>
/// <exception cref="InvalidOperationException"></exception>
public XLWorkbook Render(XLWorkbook reportTemplate, IXLWorksheet[] worksheets = null)
{
if (reportTemplate == null)
{
throw new ArgumentNullException(nameof(reportTemplate), ArgumentHelper.NullParamMessage);
}
if (SystemVariableProvider == null)
{
throw new Exception($"Property {nameof(SystemVariableProvider)} cannot be null");
}
InitTemplateProcessor();
InitDataItemValueProvider();
SystemVariableProvider.RenderDate = DateTime.Now;
OnBeforeReportRender(new ReportRenderEventArgs {
Workbook = reportTemplate
});
if (worksheets == null || !worksheets.Any())
{
worksheets = reportTemplate.Worksheets.ToArray();
}
if (!worksheets.Any())
{
return(reportTemplate);
}
IList <IXLNamedRange> workbookPanels = GetPanelsNamedRanges(reportTemplate.NamedRanges);
foreach (IXLWorksheet ws in worksheets)
{
SystemVariableProvider.SheetName = ws.Name;
SystemVariableProvider.SheetNumber = ws.Position;
OnBeforeWorksheetRender(new WorksheetRenderEventArgs {
Worksheet = ws
});
if (!ws.CellsUsed(XLCellsUsedOptions.Contents).Any())
{
continue;
}
IList <IXLNamedRange> worksheetPanels = GetPanelsNamedRanges(ws.NamedRanges);
foreach (IXLNamedRange workbookPanel in workbookPanels)
{
if (workbookPanel.Ranges.First().Worksheet == ws &&
!worksheetPanels.Any(p => p.Name.Trim().Equals(workbookPanel.Name.Trim(), StringComparison.CurrentCultureIgnoreCase)))
{
worksheetPanels.Add(workbookPanel);
}
}
IDictionary <string, (IExcelPanel, string)> panelsFlatView = GetPanelsFlatView(worksheetPanels);
IExcelPanel rootPanel = new ExcelPanel(GetRootRange(ws, worksheetPanels), Report, TemplateProcessor);
MakePanelsHierarchy(panelsFlatView, rootPanel);
rootPanel.Render();
OnAfterWorksheetRender(new WorksheetRenderEventArgs {
Worksheet = ws
});
}
return(reportTemplate);
}
public void TestHierarchicalPanelRender()
{
var report = new TestReport();
IXLWorksheet ws = report.Workbook.AddWorksheet("Test");
IXLRange range1 = ws.Range(1, 1, 10, 8);
ws.Cell(1, 1).Value = "Panel1: {p:IntParam}";
ws.Cell(10, 8).Value = "Panel1: {p:IntParam}";
var panel1 = new ExcelPanel(range1, report, report.TemplateProcessor);
IXLRange range2 = ws.Range(3, 1, 8, 2);
ws.Cell(3, 1).Value = "Panel2: {p:IntParam}";
var panel2 = new ExcelPanel(range2, report, report.TemplateProcessor)
{
Parent = panel1
};
IXLRange range3 = ws.Range(1, 3, 6, 5);
ws.Cell(1, 3).Value = "Panel3: {p:IntParam}";
range3.AddToNamed("NamedPanel1");
var panel3 =
new ExcelNamedPanel(ws.Workbook.NamedRange("NamedPanel1"), report, report.TemplateProcessor)
{
Parent = panel1
};
IXLRange range4 = ws.Range(5, 6, 9, 8);
ws.Cell(5, 6).Value = "Panel4: {p:IntParam}";
range4.AddToNamed("NamedPanel2", XLScope.Worksheet);
var panel4 =
new ExcelNamedPanel(ws.NamedRange("NamedPanel2"), report, report.TemplateProcessor)
{
Parent = panel1
};
IXLRange range5 = ws.Range(4, 1, 5, 2);
ws.Cell(4, 1).Value = "Panel5: {p:IntParam}";
var panel5 = new ExcelPanel(range5, report, report.TemplateProcessor)
{
Parent = panel2
};
IXLRange range6 = ws.Range(6, 1, 8, 2);
ws.Cell(6, 1).Value = "Panel6: {p:IntParam}";
range6.AddToNamed("NamedPanel3");
var panel6 =
new ExcelNamedPanel(ws.Workbook.NamedRange("NamedPanel3"), report, report.TemplateProcessor)
{
Parent = panel2
};
IXLRange range7 = ws.Range(6, 1, 6, 2);
ws.Cell(6, 2).Value = "Panel7: {p:IntParam}";
var panel7 = new ExcelPanel(range7, report, report.TemplateProcessor)
{
Parent = panel6
};
IXLRange range8 = ws.Range(7, 1, 7, 2);
ws.Cell(7, 2).Value = "Panel8: {p:IntParam}";
range8.AddToNamed("NamedPanel4", XLScope.Worksheet);
var panel8 =
new ExcelNamedPanel(ws.NamedRange("NamedPanel4"), report, report.TemplateProcessor)
{
Parent = panel6
};
IXLRange range9 = ws.Range(1, 3, 6, 5);
ws.Cell(6, 5).Value = "Panel9: {p:IntParam}";
range9.AddToNamed("NamedPanel5", XLScope.Worksheet);
var panel9 =
new ExcelNamedPanel(ws.NamedRange("NamedPanel5"), report, report.TemplateProcessor)
{
Parent = panel3
};
IXLRange range10 = ws.Range(3, 3, 4, 5);
ws.Cell(4, 5).Value = "Panel10: {p:IntParam}";
var panel10 = new ExcelPanel(range10, report, report.TemplateProcessor)
{
Parent = panel9
};
IXLRange range11 = ws.Range(5, 6, 9, 8);
ws.Cell(6, 6).Value = "Panel11: {p:IntParam}";
var panel11 = new ExcelPanel(range11, report, report.TemplateProcessor)
{
Parent = panel4
};
IXLRange range12 = ws.Range(8, 6, 9, 8);
ws.Cell(9, 8).Value = "Panel12: {p:IntParam}";
range12.AddToNamed("NamedPanel6");
var panel12 =
new ExcelNamedPanel(ws.Workbook.NamedRange("NamedPanel6"), report, report.TemplateProcessor)
{
Parent = panel11
};
panel1.Children = new[] { panel2, panel3, panel4 };
panel2.Children = new[] { panel5, panel6 };
panel3.Children = new[] { panel9 };
panel6.Children = new[] { panel7, panel8 };
panel4.Children = new[] { panel11 };
panel9.Children = new[] { panel10 };
panel11.Children = new[] { panel12 };
ws.Cell(11, 8).Value = "Outside panel: {p:IntParam}";
panel1.Render();
Assert.AreEqual(range1, panel1.ResultRange);
Assert.AreEqual(14, ws.CellsUsed(XLCellsUsedOptions.Contents).Count());
Assert.AreEqual("Panel1: 10", ws.Cell(1, 1).Value);
Assert.AreEqual("Panel1: 10", ws.Cell(10, 8).Value);
Assert.AreEqual("Panel2: 10", ws.Cell(3, 1).Value);
Assert.AreEqual("Panel3: 10", ws.Cell(1, 3).Value);
Assert.AreEqual("Panel4: 10", ws.Cell(5, 6).Value);
Assert.AreEqual("Panel5: 10", ws.Cell(4, 1).Value);
Assert.AreEqual("Panel6: 10", ws.Cell(6, 1).Value);
Assert.AreEqual("Panel7: 10", ws.Cell(6, 2).Value);
Assert.AreEqual("Panel8: 10", ws.Cell(7, 2).Value);
Assert.AreEqual("Panel9: 10", ws.Cell(6, 5).Value);
Assert.AreEqual("Panel10: 10", ws.Cell(4, 5).Value);
Assert.AreEqual("Panel11: 10", ws.Cell(6, 6).Value);
Assert.AreEqual("Panel12: 10", ws.Cell(9, 8).Value);
Assert.AreEqual("Outside panel: {p:IntParam}", ws.Cell(11, 8).Value);
Assert.AreEqual(0, ws.Workbook.NamedRanges.Count());
Assert.AreEqual(0, ws.NamedRanges.Count());
Assert.AreEqual(1, ws.Workbook.Worksheets.Count);
//report.Workbook.SaveAs("test.xlsx");
}
public void TestPanelRender()
{
var report = new TestReport();
IXLWorksheet ws = report.Workbook.AddWorksheet("Test");
IXLRange range = ws.Range(1, 1, 5, 5);
ws.Cell(1, 1).Value = "{p:StrParam}";
ws.Cell(1, 2).Value = "{p:IntParam}";
ws.Cell(1, 3).Value = "{p:DateParam}";
ws.Cell(1, 4).Value = "{P:BoolParam}";
ws.Cell(1, 5).Value = "{p:TimeSpanParam}";
ws.Cell(2, 1).Value = " { p:StrParam } ";
ws.Cell(2, 2).Value = "Plain text";
ws.Cell(2, 3).Value = "{Plain text}";
ws.Cell(2, 4).Value = " { m:Format ( p:DateParam ) } ";
ws.Cell(2, 5).Value = "''{m:Format(p:DateParam)}";
ws.Cell(3, 1).Value = "Int: { p:IntParam }. Str: {p:ComplexTypeParam.StrParam}. FormattedDate: {M:Format(p:DateParam)}. NullProp: {p:NullProp}";
ws.Cell(3, 2).Value = "''{m:Format(m:DateTime:AddDays(p:ComplexTypeParam.IntParam), \"yyyy-MM-dd\")}";
ws.Cell(3, 3).Value = "''{sf:Format(m:AddDays(p:DateParam, 5), ddMMyyyy)}";
ws.Cell(3, 4).Value = "''{m:Format(m:AddDays(p:DateParam, -2), dd.MM.yyyy)}";
ws.Cell(3, 5).Value = "''{sf:Format(m:AddDays(p:DateParam, [int]-3), \"dd.MM.yyyy HH:mm:ss\")}";
ws.Cell(4, 1).Value = "{m:TestReport:Counter()}";
ws.Cell(4, 2).Value = "{ m:TestReport : Counter ( ) }";
ws.Cell(4, 3).Value = "{m:Counter()}";
ws.Cell(4, 4).FormulaA1 = "=$B$1+A$4";
ws.Cell(5, 1).Value = "{p:ExpandoObj.StrProp}";
ws.Cell(5, 2).Value = "{p:ExpandoObj.DecimalProp}";
ws.Cell(5, 3).Value = "{p:NullProp}";
ws.Cell(6, 1).Value = "{p:StrParam}";
ws.Cell(6, 2).Value = "{m:Counter()}";
ws.Cell(7, 1).Value = "Plain text outside range";
var panel = new ExcelPanel(range, report, report.TemplateProcessor);
panel.Render();
Assert.AreEqual(range, panel.ResultRange);
Assert.AreEqual(24, ws.CellsUsed(XLCellsUsedOptions.Contents).Count());
Assert.AreEqual("String parameter", ws.Cell(1, 1).Value);
Assert.AreEqual(10d, ws.Cell(1, 2).Value);
Assert.AreEqual(new DateTime(2017, 10, 25), ws.Cell(1, 3).Value);
Assert.AreEqual(true, ws.Cell(1, 4).Value);
Assert.AreEqual(TimeSpan.FromHours(20), ws.Cell(1, 5).Value);
Assert.AreEqual(" String parameter ", ws.Cell(2, 1).Value);
Assert.AreEqual("Plain text", ws.Cell(2, 2).Value);
Assert.AreEqual("{Plain text}", ws.Cell(2, 3).Value);
Assert.AreEqual(20171025d, ws.Cell(2, 4).Value);
Assert.AreEqual("20171025", ws.Cell(2, 5).Value);
Assert.AreEqual("Int: 10. Str: Complex type string parameter. FormattedDate: 20171025. NullProp: ", ws.Cell(3, 1).Value);
Assert.AreEqual("0001-01-12", ws.Cell(3, 2).Value);
Assert.AreEqual("30102017", ws.Cell(3, 3).Value);
Assert.AreEqual("23.10.2017", ws.Cell(3, 4).Value);
Assert.AreEqual("22.10.2017 00:00:00", ws.Cell(3, 5).Value);
Assert.AreEqual(1d, ws.Cell(4, 1).Value);
Assert.AreEqual(2d, ws.Cell(4, 2).Value);
Assert.AreEqual(3d, ws.Cell(4, 3).Value);
Assert.AreEqual(11d, ws.Cell(4, 4).Value);
Assert.IsTrue(ws.Cell(4, 5).IsEmpty());
Assert.AreEqual("ExpandoStr", ws.Cell(5, 1).Value);
Assert.AreEqual(5.56d, ws.Cell(5, 2).Value);
Assert.IsTrue(ws.Cell(5, 3).IsEmpty());
Assert.AreEqual("{p:StrParam}", ws.Cell(6, 1).Value);
Assert.AreEqual("{m:Counter()}", ws.Cell(6, 2).Value);
Assert.AreEqual("Plain text outside range", ws.Cell(7, 1).Value);
Assert.AreEqual(0, ws.NamedRanges.Count());
Assert.AreEqual(0, ws.Workbook.NamedRanges.Count());
Assert.AreEqual(1, ws.Workbook.Worksheets.Count);
//report.Workbook.SaveAs("test.xlsx");
}
public void TestDelete()
{
// Deleting with moving cells up
XLWorkbook wb = InitWorkBookForDeleteRangeTest();
IXLWorksheet ws = wb.Worksheet("Test");
IXLRange range = ws.NamedRange("TestRange").Ranges.ElementAt(0);
var excelReport = Substitute.For <object>();
var templateProcessor = Substitute.For <ITemplateProcessor>();
var panel = new ExcelPanel(range, excelReport, templateProcessor);
panel.Delete();
IXLCell rangeStartCell = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "RangeStart");
IXLCell rangeEndCell = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "RangeEnd");
IXLCell belowCell1 = ws.Cells().Single(c => c.Value.ToString() == "BelowCell_1");
IXLCell belowCell2 = ws.Cells().Single(c => c.Value.ToString() == "BelowCell_2");
IXLCell rightCell1 = ws.Cells().Single(c => c.Value.ToString() == "RightCell_1");
IXLCell rightCell2 = ws.Cells().Single(c => c.Value.ToString() == "RightCell_2");
IXLCell aboveCell1 = ws.Cells().Single(c => c.Value.ToString() == "AboveCell_1");
IXLCell aboveCell2 = ws.Cells().Single(c => c.Value.ToString() == "AboveCell_2");
IXLCell leftCell1 = ws.Cells().Single(c => c.Value.ToString() == "LeftCell_1");
IXLCell leftCell2 = ws.Cells().Single(c => c.Value.ToString() == "LeftCell_2");
Assert.IsNull(rangeStartCell);
Assert.IsNull(rangeEndCell);
Assert.AreEqual(8, ws.CellsUsed(XLCellsUsedOptions.Contents).Count());
Assert.AreEqual(belowCell1, ws.Cell(6, 6));
Assert.AreEqual(belowCell2, ws.Cell(10, 8));
Assert.AreEqual(rightCell1, ws.Cell(7, 8));
Assert.AreEqual(rightCell2, ws.Cell(5, 8));
Assert.AreEqual(aboveCell1, ws.Cell(5, 6));
Assert.AreEqual(aboveCell2, ws.Cell(5, 4));
Assert.AreEqual(leftCell1, ws.Cell(7, 4));
Assert.AreEqual(leftCell2, ws.Cell(10, 4));
// Deleting with moving the row up
wb = InitWorkBookForDeleteRangeTest();
ws = wb.Worksheet("Test");
range = ws.NamedRange("TestRange").Ranges.ElementAt(0);
panel = new ExcelPanel(range, excelReport, templateProcessor)
{
ShiftType = ShiftType.Row
};
panel.Delete();
rangeStartCell = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "RangeStart");
rangeEndCell = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "RangeEnd");
belowCell1 = ws.Cells().Single(c => c.Value.ToString() == "BelowCell_1");
belowCell2 = ws.Cells().Single(c => c.Value.ToString() == "BelowCell_2");
rightCell1 = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "RightCell_1");
rightCell2 = ws.Cells().Single(c => c.Value.ToString() == "RightCell_2");
aboveCell1 = ws.Cells().Single(c => c.Value.ToString() == "AboveCell_1");
aboveCell2 = ws.Cells().Single(c => c.Value.ToString() == "AboveCell_2");
leftCell1 = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "LeftCell_1");
leftCell2 = ws.Cells().Single(c => c.Value.ToString() == "LeftCell_2");
Assert.IsNull(rangeStartCell);
Assert.IsNull(rangeEndCell);
Assert.IsNull(leftCell1);
Assert.IsNull(rightCell1);
Assert.AreEqual(6, ws.CellsUsed(XLCellsUsedOptions.Contents).Count());
Assert.AreEqual(belowCell1, ws.Cell(6, 6));
Assert.AreEqual(belowCell2, ws.Cell(6, 8));
Assert.AreEqual(rightCell2, ws.Cell(5, 8));
Assert.AreEqual(aboveCell1, ws.Cell(5, 6));
Assert.AreEqual(aboveCell2, ws.Cell(5, 4));
Assert.AreEqual(leftCell2, ws.Cell(6, 4));
// Deleting with moving cells left
wb = InitWorkBookForDeleteRangeTest();
ws = wb.Worksheet("Test");
range = ws.NamedRange("TestRange").Ranges.ElementAt(0);
panel = new ExcelPanel(range, excelReport, templateProcessor)
{
Type = PanelType.Horizontal
};
panel.Delete();
rangeStartCell = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "RangeStart");
rangeEndCell = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "RangeEnd");
belowCell1 = ws.Cells().Single(c => c.Value.ToString() == "BelowCell_1");
belowCell2 = ws.Cells().Single(c => c.Value.ToString() == "BelowCell_2");
rightCell1 = ws.Cells().Single(c => c.Value.ToString() == "RightCell_1");
rightCell2 = ws.Cells().Single(c => c.Value.ToString() == "RightCell_2");
aboveCell1 = ws.Cells().Single(c => c.Value.ToString() == "AboveCell_1");
aboveCell2 = ws.Cells().Single(c => c.Value.ToString() == "AboveCell_2");
leftCell1 = ws.Cells().Single(c => c.Value.ToString() == "LeftCell_1");
leftCell2 = ws.Cells().Single(c => c.Value.ToString() == "LeftCell_2");
Assert.IsNull(rangeStartCell);
Assert.IsNull(rangeEndCell);
Assert.AreEqual(8, ws.CellsUsed(XLCellsUsedOptions.Contents).Count());
Assert.AreEqual(belowCell1, ws.Cell(10, 6));
Assert.AreEqual(belowCell2, ws.Cell(10, 8));
Assert.AreEqual(rightCell1, ws.Cell(7, 5));
Assert.AreEqual(rightCell2, ws.Cell(5, 8));
Assert.AreEqual(aboveCell1, ws.Cell(5, 6));
Assert.AreEqual(aboveCell2, ws.Cell(5, 4));
Assert.AreEqual(leftCell1, ws.Cell(7, 4));
Assert.AreEqual(leftCell2, ws.Cell(10, 4));
// Deleting with moving the column left
wb = InitWorkBookForDeleteRangeTest();
ws = wb.Worksheet("Test");
range = ws.NamedRange("TestRange").Ranges.ElementAt(0);
panel = new ExcelPanel(range, excelReport, templateProcessor)
{
Type = PanelType.Horizontal, ShiftType = ShiftType.Row
};
panel.Delete();
rangeStartCell = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "RangeStart");
rangeEndCell = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "RangeEnd");
belowCell1 = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "BelowCell_1");
belowCell2 = ws.Cells().Single(c => c.Value.ToString() == "BelowCell_2");
rightCell1 = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "RightCell_1");
rightCell2 = ws.Cells().Single(c => c.Value.ToString() == "RightCell_2");
aboveCell1 = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "AboveCell_1");
aboveCell2 = ws.Cells().Single(c => c.Value.ToString() == "AboveCell_2");
leftCell1 = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "LeftCell_1");
leftCell2 = ws.Cells().Single(c => c.Value.ToString() == "LeftCell_2");
Assert.IsNull(rangeStartCell);
Assert.IsNull(rangeEndCell);
Assert.IsNull(aboveCell1);
Assert.IsNull(belowCell1);
Assert.AreEqual(6, ws.CellsUsed(XLCellsUsedOptions.Contents).Count());
Assert.AreEqual(belowCell2, ws.Cell(10, 5));
Assert.AreEqual(rightCell1, ws.Cell(7, 5));
Assert.AreEqual(rightCell2, ws.Cell(5, 5));
Assert.AreEqual(aboveCell2, ws.Cell(5, 4));
Assert.AreEqual(leftCell1, ws.Cell(7, 4));
Assert.AreEqual(leftCell2, ws.Cell(10, 4));
// Deleting without any shift
wb = InitWorkBookForDeleteRangeTest();
ws = wb.Worksheet("Test");
range = ws.NamedRange("TestRange").Ranges.ElementAt(0);
panel = new ExcelPanel(range, excelReport, templateProcessor)
{
ShiftType = ShiftType.NoShift
};
panel.Delete();
rangeStartCell = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "RangeStart");
rangeEndCell = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "RangeEnd");
belowCell1 = ws.Cells().Single(c => c.Value.ToString() == "BelowCell_1");
belowCell2 = ws.Cells().Single(c => c.Value.ToString() == "BelowCell_2");
rightCell1 = ws.Cells().Single(c => c.Value.ToString() == "RightCell_1");
rightCell2 = ws.Cells().Single(c => c.Value.ToString() == "RightCell_2");
aboveCell1 = ws.Cells().Single(c => c.Value.ToString() == "AboveCell_1");
aboveCell2 = ws.Cells().Single(c => c.Value.ToString() == "AboveCell_2");
leftCell1 = ws.Cells().Single(c => c.Value.ToString() == "LeftCell_1");
leftCell2 = ws.Cells().Single(c => c.Value.ToString() == "LeftCell_2");
Assert.IsNull(rangeStartCell);
Assert.IsNull(rangeEndCell);
Assert.AreEqual(XLBorderStyleValues.None, range.FirstCell().Style.Border.TopBorder);
Assert.AreEqual(XLBorderStyleValues.None, range.LastCell().Style.Border.BottomBorder);
Assert.AreEqual(8, ws.CellsUsed(XLCellsUsedOptions.Contents).Count());
Assert.AreEqual(belowCell1, ws.Cell(10, 6));
Assert.AreEqual(belowCell2, ws.Cell(10, 8));
Assert.AreEqual(rightCell1, ws.Cell(7, 8));
Assert.AreEqual(rightCell2, ws.Cell(5, 8));
Assert.AreEqual(aboveCell1, ws.Cell(5, 6));
Assert.AreEqual(aboveCell2, ws.Cell(5, 4));
Assert.AreEqual(leftCell1, ws.Cell(7, 4));
Assert.AreEqual(leftCell2, ws.Cell(10, 4));
//wb.SaveAs("test.xlsx");
}
public void TestCopy()
{
XLWorkbook wb = new XLWorkbook();
IXLWorksheet ws = wb.AddWorksheet("Test");
var excelReport = Substitute.For <object>();
var templateProcessor = Substitute.For <ITemplateProcessor>();
IXLRange range = ws.Range(1, 1, 3, 4);
IXLRange childRange = ws.Range(2, 1, 3, 4);
IXLRange childOfChildRange = ws.Range(3, 1, 3, 4);
var panel = new ExcelPanel(range, excelReport, templateProcessor)
{
BeforeRenderMethodName = "BeforeMethod",
AfterRenderMethodName = "AfterRender",
Type = PanelType.Horizontal,
ShiftType = ShiftType.Row,
RenderPriority = 1,
Children = new List <IExcelPanel>
{
new ExcelPanel(childRange, excelReport, templateProcessor)
{
BeforeRenderMethodName = "BeforeMethod_child",
AfterRenderMethodName = "AfterRender_child",
Type = PanelType.Vertical,
ShiftType = ShiftType.NoShift,
RenderPriority = 2,
Children = new List <IExcelPanel>
{
new ExcelPanel(childOfChildRange, excelReport, templateProcessor)
{
BeforeRenderMethodName = "BeforeMethod_child_child",
AfterRenderMethodName = "AfterRender_child_child",
Type = PanelType.Horizontal,
ShiftType = ShiftType.Row,
RenderPriority = 3,
}
}
}
}
};
IExcelPanel copiedPanel = panel.Copy(ws.Cell(5, 5));
Assert.AreSame(excelReport, copiedPanel.GetType().GetField("_report", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(copiedPanel));
Assert.AreSame(templateProcessor, copiedPanel.GetType().GetField("_templateProcessor", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(copiedPanel));
Assert.AreEqual(ws.Cell(5, 5), copiedPanel.Range.FirstCell());
Assert.AreEqual(ws.Cell(7, 8), copiedPanel.Range.LastCell());
Assert.IsNull(copiedPanel.Parent);
Assert.AreEqual(panel.BeforeRenderMethodName, copiedPanel.BeforeRenderMethodName);
Assert.AreEqual(panel.AfterRenderMethodName, copiedPanel.AfterRenderMethodName);
Assert.AreEqual(panel.Type, copiedPanel.Type);
Assert.AreEqual(panel.ShiftType, copiedPanel.ShiftType);
Assert.AreEqual(panel.RenderPriority, copiedPanel.RenderPriority);
Assert.AreEqual(1, copiedPanel.Children.Count());
Assert.AreSame(excelReport, copiedPanel.Children.First().GetType().GetField("_report", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(copiedPanel.Children.First()));
Assert.AreSame(templateProcessor, copiedPanel.GetType().GetField("_templateProcessor", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(copiedPanel.Children.First()));
Assert.AreEqual(ws.Cell(6, 5), copiedPanel.Children.First().Range.FirstCell());
Assert.AreEqual(ws.Cell(7, 8), copiedPanel.Children.First().Range.LastCell());
Assert.AreSame(copiedPanel, copiedPanel.Children.First().Parent);
Assert.AreEqual(panel.Children.First().BeforeRenderMethodName, copiedPanel.Children.First().BeforeRenderMethodName);
Assert.AreEqual(panel.Children.First().AfterRenderMethodName, copiedPanel.Children.First().AfterRenderMethodName);
Assert.AreEqual(panel.Children.First().Type, copiedPanel.Children.First().Type);
Assert.AreEqual(panel.Children.First().ShiftType, copiedPanel.Children.First().ShiftType);
Assert.AreEqual(panel.Children.First().RenderPriority, copiedPanel.Children.First().RenderPriority);
Assert.AreEqual(1, copiedPanel.Children.First().Children.Count());
Assert.AreSame(excelReport, copiedPanel.Children.First().Children.First().GetType().GetField("_report", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(copiedPanel.Children.First().Children.First()));
Assert.AreSame(templateProcessor, copiedPanel.Children.First().Children.First().GetType().GetField("_templateProcessor", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(copiedPanel.Children.First().Children.First()));
Assert.AreEqual(ws.Cell(7, 5), copiedPanel.Children.First().Children.First().Range.FirstCell());
Assert.AreEqual(ws.Cell(7, 8), copiedPanel.Children.First().Children.First().Range.LastCell());
Assert.AreSame(copiedPanel.Children.First(), copiedPanel.Children.First().Children.First().Parent);
Assert.AreEqual(panel.Children.First().Children.First().BeforeRenderMethodName, copiedPanel.Children.First().Children.First().BeforeRenderMethodName);
Assert.AreEqual(panel.Children.First().Children.First().AfterRenderMethodName, copiedPanel.Children.First().Children.First().AfterRenderMethodName);
Assert.AreEqual(panel.Children.First().Children.First().Type, copiedPanel.Children.First().Children.First().Type);
Assert.AreEqual(panel.Children.First().Children.First().ShiftType, copiedPanel.Children.First().Children.First().ShiftType);
Assert.AreEqual(panel.Children.First().Children.First().RenderPriority, copiedPanel.Children.First().Children.First().RenderPriority);
IExcelPanel globalParent = new ExcelPanel(ws.Range(1, 1, 20, 20), excelReport, templateProcessor);
range = ws.Range(1, 1, 3, 4);
IXLRange childRange1 = ws.Range(1, 1, 1, 4);
IXLRange childRange2 = ws.Range(2, 1, 3, 4);
childOfChildRange = ws.Range(3, 1, 3, 4);
panel = new ExcelPanel(range, excelReport, templateProcessor)
{
Parent = globalParent,
Children = new List <IExcelPanel>
{
new ExcelPanel(childRange1, excelReport, templateProcessor),
new ExcelPanel(childRange2, excelReport, templateProcessor)
{
Children = new List <IExcelPanel>
{
new ExcelPanel(childOfChildRange, excelReport, templateProcessor)
}
},
},
};
copiedPanel = panel.Copy(ws.Cell(5, 5));
Assert.AreEqual(ws.Cell(5, 5), copiedPanel.Range.FirstCell());
Assert.AreEqual(ws.Cell(7, 8), copiedPanel.Range.LastCell());
Assert.AreSame(globalParent, copiedPanel.Parent);
Assert.AreEqual(2, copiedPanel.Children.Count());
Assert.AreEqual(ws.Cell(5, 5), copiedPanel.Children.First().Range.FirstCell());
Assert.AreEqual(ws.Cell(5, 8), copiedPanel.Children.First().Range.LastCell());
Assert.AreSame(copiedPanel, copiedPanel.Children.First().Parent);
Assert.AreEqual(ws.Cell(6, 5), copiedPanel.Children.Last().Range.FirstCell());
Assert.AreEqual(ws.Cell(7, 8), copiedPanel.Children.Last().Range.LastCell());
Assert.AreSame(copiedPanel, copiedPanel.Children.Last().Parent);
Assert.AreEqual(1, copiedPanel.Children.Last().Children.Count());
Assert.AreEqual(ws.Cell(7, 5), copiedPanel.Children.Last().Children.First().Range.FirstCell());
Assert.AreEqual(ws.Cell(7, 8), copiedPanel.Children.Last().Children.First().Range.LastCell());
Assert.AreSame(copiedPanel.Children.Last(), copiedPanel.Children.Last().Children.First().Parent);
globalParent = new ExcelPanel(ws.Range(1, 1, 7, 7), excelReport, templateProcessor);
range = ws.Range(1, 1, 3, 4);
childRange1 = ws.Range(1, 1, 1, 4);
panel = new ExcelPanel(range, excelReport, templateProcessor)
{
Parent = globalParent,
Children = new List <IExcelPanel> {
new ExcelPanel(childRange1, excelReport, templateProcessor)
},
};
copiedPanel = panel.Copy(ws.Cell(5, 5));
Assert.AreEqual(ws.Cell(5, 5), copiedPanel.Range.FirstCell());
Assert.AreEqual(ws.Cell(7, 8), copiedPanel.Range.LastCell());
Assert.IsNull(copiedPanel.Parent);
Assert.AreEqual(1, copiedPanel.Children.Count());
Assert.AreEqual(ws.Cell(5, 5), copiedPanel.Children.First().Range.FirstCell());
Assert.AreEqual(ws.Cell(5, 8), copiedPanel.Children.First().Range.LastCell());
Assert.AreSame(copiedPanel, copiedPanel.Children.First().Parent);
globalParent = new ExcelPanel(ws.Range(1, 1, 7, 8), excelReport, templateProcessor);
panel.Parent = globalParent;
copiedPanel = panel.Copy(ws.Cell(5, 5), false);
Assert.AreEqual(ws.Cell(5, 5), copiedPanel.Range.FirstCell());
Assert.AreEqual(ws.Cell(7, 8), copiedPanel.Range.LastCell());
Assert.AreSame(globalParent, copiedPanel.Parent);
Assert.AreEqual(0, copiedPanel.Children.Count());
//wb.SaveAs("test.xlsx");
}
