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