Example #1
0
        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));
        }
Example #2
0
        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 Test_VerticalPageBreaks_WithSimplePanel_RowShift()
        {
            var          report      = new TestReport();
            IXLWorksheet ws          = report.Workbook.AddWorksheet("Test");
            IXLRange     parentRange = ws.Range(2, 2, 5, 4);

            parentRange.AddToNamed("ParentRange", XLScope.Worksheet);

            parentRange.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;

            IXLRange child1 = ws.Range(2, 3, 5, 3);

            child1.AddToNamed("ChildRange1", XLScope.Worksheet);

            IXLRange child2 = ws.Range(2, 4, 5, 4);

            child2.AddToNamed("ChildRange2", XLScope.Worksheet);

            ws.Cell(2, 2).Value = "{di:Name}";
            ws.Cell(3, 2).Value = "{di:Date}";

            ws.Cell(3, 3).Value = "{di:Field1}";
            ws.Cell(4, 3).Value = "{di:Field2}";
            ws.Cell(5, 3).Value = "{di:parent:Sum}";

            ws.Cell(2, 4).Value = "{VertPageBreak}";

            var parentPanel = new ExcelDataSourcePanel("m:DataProvider:GetIEnumerable()", ws.NamedRange("ParentRange"), report, report.TemplateProcessor)
            {
                Type      = PanelType.Horizontal,
                ShiftType = ShiftType.Row,
            };
            var childPanel1 = new ExcelDataSourcePanel("m:DataProvider:GetChildIEnumerable(di:Name)", ws.NamedRange("ChildRange1"), report, report.TemplateProcessor)
            {
                Parent    = parentPanel,
                Type      = PanelType.Horizontal,
                ShiftType = ShiftType.Row,
            };
            var childPanel2 = new ExcelNamedPanel(ws.NamedRange("ChildRange2"), report, report.TemplateProcessor)
            {
                Parent = parentPanel,
            };

            parentPanel.Children = new[] { childPanel1, childPanel2 };
            parentPanel.Render();

            Assert.AreEqual(ws.Range(2, 2, 5, 12), parentPanel.ResultRange);

            ExcelAssert.AreWorkbooksContentEquals(TestHelper.GetExpectedWorkbook(nameof(DataSourcePanelRender_WithGrouping_PageBreaks_Test),
                                                                                 "Test_VerticalPageBreaks"), ws.Workbook);

            //report.Workbook.SaveAs("test.xlsx");
        }
Example #4
0
        public void TestDelete()
        {
            // Deleting with moving cells up
            XLWorkbook    wb          = InitWorkBookForDeleteRangeTest();
            IXLWorksheet  ws          = wb.Worksheet("Test");
            IXLNamedRange parentRange = ws.NamedRange("Parent");
            IXLNamedRange childRange  = ws.NamedRange("Child");

            Assert.AreEqual(2, ws.NamedRanges.Count());
            var excelReport       = Substitute.For <object>();
            var templateProcessor = Substitute.For <ITemplateProcessor>();

            var panel = new ExcelNamedPanel(parentRange, excelReport, templateProcessor)
            {
                Children = new List <IExcelPanel> {
                    new ExcelNamedPanel(childRange, 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));
            Assert.AreEqual(0, ws.NamedRanges.Count());

            // Deleting with moving the row up
            wb          = InitWorkBookForDeleteRangeTest();
            ws          = wb.Worksheet("Test");
            parentRange = ws.NamedRange("Parent");
            childRange  = ws.NamedRange("Child");
            Assert.AreEqual(2, ws.NamedRanges.Count());

            panel = new ExcelNamedPanel(parentRange, excelReport, templateProcessor)
            {
                Children = new List <IExcelPanel> {
                    new ExcelNamedPanel(childRange, 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));
            Assert.AreEqual(0, ws.NamedRanges.Count());

            // Deleting with moving cells left
            wb          = InitWorkBookForDeleteRangeTest();
            ws          = wb.Worksheet("Test");
            parentRange = ws.NamedRange("Parent");
            childRange  = ws.NamedRange("Child");
            Assert.AreEqual(2, ws.NamedRanges.Count());

            panel = new ExcelNamedPanel(parentRange, excelReport, templateProcessor)
            {
                Children = new List <IExcelPanel> {
                    new ExcelNamedPanel(childRange, 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));
            Assert.AreEqual(0, ws.NamedRanges.Count());

            // Deleting with moving the column left
            wb          = InitWorkBookForDeleteRangeTest();
            ws          = wb.Worksheet("Test");
            parentRange = ws.NamedRange("Parent");
            childRange  = ws.NamedRange("Child");
            Assert.AreEqual(2, ws.NamedRanges.Count());

            panel = new ExcelNamedPanel(parentRange, excelReport, templateProcessor)
            {
                Children = new List <IExcelPanel> {
                    new ExcelNamedPanel(childRange, 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));
            Assert.AreEqual(0, ws.NamedRanges.Count());

            // Deleting without any shift
            wb          = InitWorkBookForDeleteRangeTest();
            ws          = wb.Worksheet("Test");
            parentRange = ws.NamedRange("Parent");
            childRange  = ws.NamedRange("Child");
            Assert.AreEqual(2, ws.NamedRanges.Count());

            panel = new ExcelNamedPanel(parentRange, excelReport, templateProcessor)
            {
                Children = new List <IExcelPanel> {
                    new ExcelNamedPanel(childRange, 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, parentRange.Ranges.ElementAt(0).FirstCell().Style.Border.TopBorder);
            Assert.AreEqual(XLBorderStyleValues.None, parentRange.Ranges.ElementAt(0).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));
            Assert.AreEqual(0, ws.NamedRanges.Count());

            //wb.SaveAs("test.xlsx");
        }
Example #5
0
        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");
        }
Example #6
0
        public void TestCopyWithName()
        {
            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 childRange1 = ws.Range(1, 1, 2, 4);

            childRange1.AddToNamed("Child", XLScope.Worksheet);
            IXLNamedRange namedChildRange = ws.NamedRange("Child");

            IXLRange childRange2 = ws.Range(3, 1, 3, 4);

            IXLRange childOfChildRange1 = ws.Range(1, 1, 1, 4);

            childOfChildRange1.AddToNamed("ChildOfChild1", XLScope.Worksheet);
            IXLNamedRange namedChildOfChildRange1 = ws.NamedRange("ChildOfChild1");

            IXLRange childOfChildRange2 = ws.Range(3, 1, 3, 4);

            childOfChildRange2.AddToNamed("ChildOfChild2", XLScope.Worksheet);
            IXLNamedRange namedChildOfChildRange2 = ws.NamedRange("ChildOfChild2");

            var panel = new ExcelNamedPanel(namedRange, excelReport, templateProcessor)
            {
                Children = new List <IExcelPanel>
                {
                    new ExcelNamedPanel(namedChildRange, excelReport, templateProcessor)
                    {
                        Children = new List <IExcelPanel>
                        {
                            new ExcelNamedPanel(namedChildOfChildRange1, excelReport, templateProcessor)
                        }
                    },
                    new ExcelPanel(childRange2, excelReport, templateProcessor)
                    {
                        Children = new List <IExcelPanel>
                        {
                            new ExcelNamedPanel(namedChildOfChildRange2, excelReport, templateProcessor)
                        }
                    }
                }
            };

            IExcelNamedPanel copiedPanel = panel.Copy(ws.Cell(5, 5), "Copied");

            Assert.IsTrue(Regex.IsMatch(copiedPanel.Name, "Copied"));
            Assert.AreEqual(ws.Cell(5, 5), copiedPanel.Range.FirstCell());
            Assert.AreEqual(ws.Cell(7, 8), copiedPanel.Range.LastCell());
            Assert.IsNull(copiedPanel.Parent);

            Assert.AreEqual(2, copiedPanel.Children.Count);
            Assert.IsTrue(Regex.IsMatch(((IExcelNamedPanel)copiedPanel.Children.First()).Name, "Copied_Child"));
            Assert.AreEqual(ws.Cell(5, 5), copiedPanel.Children.First().Range.FirstCell());
            Assert.AreEqual(ws.Cell(6, 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(7, 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.First().Children.Count);
            Assert.IsTrue(Regex.IsMatch(((IExcelNamedPanel)copiedPanel.Children.First().Children.First()).Name, "Copied_Child_ChildOfChild1"));
            Assert.AreEqual(ws.Cell(5, 5), copiedPanel.Children.First().Children.First().Range.FirstCell());
            Assert.AreEqual(ws.Cell(5, 8), copiedPanel.Children.First().Children.First().Range.LastCell());
            Assert.AreSame(copiedPanel.Children.First(), copiedPanel.Children.First().Children.First().Parent);

            Assert.AreEqual(1, copiedPanel.Children.Last().Children.Count);
            Assert.IsTrue(Regex.IsMatch(((IExcelNamedPanel)copiedPanel.Children.Last().Children.First()).Name, "ChildOfChild2_[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);

            copiedPanel = panel.Copy(ws.Cell(5, 5), "Copied2", false);
            Assert.IsTrue(Regex.IsMatch(copiedPanel.Name, "Copied2"));
            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, "Copied"));
            ExceptionAssert.Throws <ArgumentException>(() => panel.Copy(ws.Cell(5, 5), null));
            ExceptionAssert.Throws <ArgumentException>(() => panel.Copy(ws.Cell(5, 5), string.Empty));
            ExceptionAssert.Throws <ArgumentException>(() => panel.Copy(ws.Cell(5, 5), " "));

            //wb.SaveAs("test.xlsx");
        }
Example #7
0
        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");
        }
Example #8
0
        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");
        }
Example #9
0
        public void TestNamedPanelRender()
        {
            var          report = new TestReport();
            IXLWorksheet ws     = report.Workbook.AddWorksheet("Test");
            IXLRange     range  = ws.Range(1, 1, 4, 5);

            range.AddToNamed("NamedPanel");

            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)}";
            ws.Cell(3, 2).Value = "''{m:Format(m:DateTime:AddDays(p:ComplexTypeParam.IntParam), \"yyyy-MM-dd\")}";
            ws.Cell(3, 3).Value = "''{m: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 = "''{m: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(5, 1).Value = "{p:StrParam}";
            ws.Cell(5, 2).Value = "{m:Counter()}";
            ws.Cell(6, 1).Value = "Plain text outside range";

            var panel = new ExcelNamedPanel(ws.Workbook.NamedRange("NamedPanel"), report, report.TemplateProcessor);

            panel.Render();

            Assert.AreEqual(range, panel.ResultRange);

            Assert.AreEqual(21, 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", 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.IsTrue(ws.Cell(4, 4).IsEmpty());
            Assert.IsTrue(ws.Cell(4, 5).IsEmpty());

            Assert.AreEqual("{p:StrParam}", ws.Cell(5, 1).Value);
            Assert.AreEqual("{m:Counter()}", ws.Cell(5, 2).Value);
            Assert.AreEqual("Plain text outside range", ws.Cell(6, 1).Value);

            Assert.AreEqual(0, ws.NamedRanges.Count());
            Assert.AreEqual(0, ws.Workbook.NamedRanges.Count());
            Assert.AreEqual(1, ws.Workbook.Worksheets.Count);

            IXLRange range2 = ws.Range(8, 1, 11, 5);

            range2.AddToNamed("NamedPanel2", XLScope.Worksheet);

            ws.Cell(8, 1).Value  = "{p:StrParam}";
            ws.Cell(8, 2).Value  = "{p:IntParam}";
            ws.Cell(8, 3).Value  = "{p:DateParam}";
            ws.Cell(8, 4).Value  = "{p:BoolParam}";
            ws.Cell(8, 5).Value  = "{p:TimeSpanParam}";
            ws.Cell(9, 1).Value  = "{ p:StrParam }";
            ws.Cell(9, 2).Value  = "Plain text";
            ws.Cell(9, 3).Value  = "{Plain text}";
            ws.Cell(9, 4).Value  = " { m:Format ( p:DateParam ) } ";
            ws.Cell(9, 5).Value  = "''{m:Format(p:DateParam)}";
            ws.Cell(10, 1).Value = "Int: { p:IntParam }. Str: {p:ComplexTypeParam.StrParam}. FormattedDate: {m:Format(p:DateParam)}";
            ws.Cell(10, 2).Value = "''{m:Format(m:DateTime:AddDays(p:ComplexTypeParam.IntParam), \"yyyy-MM-dd\")}";
            ws.Cell(10, 3).Value = "''{m:Format(m:AddDays(p:DateParam, 5), ddMMyyyy)}";
            ws.Cell(10, 4).Value = "''{m:Format(m:AddDays(p:DateParam, -2), dd.MM.yyyy)}";
            ws.Cell(10, 5).Value = "''{m:Format(m:AddDays(p:DateParam, [int]-3), \"dd.MM.yyyy HH:mm:ss\")}";
            ws.Cell(11, 1).Value = "{m:TestReport:Counter()}";
            ws.Cell(11, 2).Value = "{ m:TestReport : Counter ( ) }";
            ws.Cell(11, 3).Value = "{m:Counter()}";
            ws.Cell(12, 1).Value = "{p:StrParam}";
            ws.Cell(12, 2).Value = "{m:Counter()}";
            ws.Cell(13, 1).Value = "Plain text outside range";

            panel = new ExcelNamedPanel(ws.NamedRange("NamedPanel2"), report, report.TemplateProcessor);
            panel.Render();

            Assert.AreEqual(range2, panel.ResultRange);

            Assert.AreEqual(42, ws.CellsUsed(XLCellsUsedOptions.Contents).Count());
            Assert.AreEqual("String parameter", ws.Cell(8, 1).Value);
            Assert.AreEqual(10d, ws.Cell(8, 2).Value);
            Assert.AreEqual(new DateTime(2017, 10, 25), ws.Cell(8, 3).Value);
            Assert.AreEqual(true, ws.Cell(8, 4).Value);
            Assert.AreEqual(TimeSpan.FromHours(20), ws.Cell(8, 5).Value);
            Assert.AreEqual("String parameter", ws.Cell(9, 1).Value);
            Assert.AreEqual("Plain text", ws.Cell(9, 2).Value);
            Assert.AreEqual("{Plain text}", ws.Cell(9, 3).Value);
            Assert.AreEqual(20171025d, ws.Cell(9, 4).Value);
            Assert.AreEqual("20171025", ws.Cell(9, 5).Value);
            Assert.AreEqual("Int: 10. Str: Complex type string parameter. FormattedDate: 20171025", ws.Cell(10, 1).Value);
            Assert.AreEqual("0001-01-12", ws.Cell(10, 2).Value);
            Assert.AreEqual("30102017", ws.Cell(10, 3).Value);
            Assert.AreEqual("23.10.2017", ws.Cell(10, 4).Value);
            Assert.AreEqual("22.10.2017 00:00:00", ws.Cell(10, 5).Value);

            Assert.AreEqual(4d, ws.Cell(11, 1).Value);
            Assert.AreEqual(5d, ws.Cell(11, 2).Value);
            Assert.AreEqual(6d, ws.Cell(11, 3).Value);
            Assert.IsTrue(ws.Cell(11, 4).IsEmpty());
            Assert.IsTrue(ws.Cell(11, 5).IsEmpty());

            Assert.AreEqual("{p:StrParam}", ws.Cell(12, 1).Value);
            Assert.AreEqual("{m:Counter()}", ws.Cell(12, 2).Value);
            Assert.AreEqual("Plain text outside range", ws.Cell(13, 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");
        }
Example #10
0
        public void TestMultipleHorizontalPanelsGrouping()
        {
            var          report      = new TestReport();
            IXLWorksheet ws          = report.Workbook.AddWorksheet("Test");
            IXLRange     parentRange = ws.Range(2, 2, 7, 6);

            parentRange.AddToNamed("ParentRange", XLScope.Worksheet);

            IXLRange simpleRange1 = ws.Range(3, 3, 4, 3);

            simpleRange1.AddToNamed("simpleRange1");

            IXLRange child = ws.Range(2, 4, 7, 6);

            child.AddToNamed("ChildRange", XLScope.Worksheet);

            IXLRange childOfChild = ws.Range(2, 5, 7, 6);

            childOfChild.AddToNamed("ChildOfChildRange");

            IXLRange simpleRange2 = ws.Range(5, 6, 7, 6);

            simpleRange2.AddToNamed("simpleRange2", XLScope.Worksheet);

            simpleRange2.Style.Border.OutsideBorder      = XLBorderStyleValues.Thin;
            simpleRange2.Style.Border.OutsideBorderColor = XLColor.Orange;

            childOfChild.Range(3, 1, 6, 2).Style.Border.OutsideBorder      = XLBorderStyleValues.Thin;
            childOfChild.Range(3, 1, 6, 2).Style.Border.OutsideBorderColor = XLColor.Green;

            child.Range(2, 1, 6, 3).Style.Border.OutsideBorder      = XLBorderStyleValues.Thin;
            child.Range(2, 1, 6, 3).Style.Border.OutsideBorderColor = XLColor.Red;

            simpleRange1.Style.Border.OutsideBorder      = XLBorderStyleValues.Thin;
            simpleRange1.Style.Border.OutsideBorderColor = XLColor.Brown;

            parentRange.Style.Border.OutsideBorder      = XLBorderStyleValues.Thin;
            parentRange.Style.Border.OutsideBorderColor = XLColor.Black;

            ws.Cell(2, 2).Value = "{di:Name}";
            ws.Cell(3, 2).Value = "{di:Date}";

            ws.Cell(3, 3).Value = "{p:StrParam}";
            ws.Cell(4, 3).Value = "{di:Sum}";
            ws.Cell(5, 3).Value = "{p:IntParam}";

            ws.Cell(3, 4).Value = "{di:Field1}";
            ws.Cell(4, 4).Value = "{di:Field2}";
            ws.Cell(5, 4).Value = "{di:parent:Sum}";
            ws.Cell(6, 4).Value = "{di:parent:Contacts}";

            ws.Cell(4, 5).Value = "{di:Field1}";
            ws.Cell(5, 5).Value = "{di:Field2}";
            ws.Cell(6, 5).Value = "{di:parent:Field1}";
            ws.Cell(7, 5).Value = "{di:parent:parent:Contacts.Phone}";

            ws.Cell(5, 6).Value = "{p:DateParam}";
            ws.Cell(6, 6).Value = "{di:parent:Field2}";
            ws.Cell(7, 6).Value = "{di:parent:parent:Contacts.Fax}";

            var parentPanel = new ExcelDataSourcePanel("m:DataProvider:GetIEnumerable()", ws.NamedRange("ParentRange"), report, report.TemplateProcessor)
            {
                Type = PanelType.Horizontal,
            };
            var simplePanel1 = new ExcelNamedPanel(ws.Workbook.NamedRange("simpleRange1"), report, report.TemplateProcessor)
            {
                Parent = parentPanel,
            };
            var childPanel = new ExcelDataSourcePanel("m:DataProvider:GetChildIEnumerable(di:Name)", ws.NamedRange("ChildRange"), report, report.TemplateProcessor)
            {
                Parent = parentPanel,
                Type   = PanelType.Horizontal,
            };
            var childOfChildPanel = new ExcelDataSourcePanel("di:Children", ws.Workbook.NamedRange("ChildOfChildRange"), report, report.TemplateProcessor)
            {
                Parent = childPanel,
                Type   = PanelType.Horizontal,
            };
            var simplePanel2 = new ExcelNamedPanel(ws.NamedRange("simpleRange2"), report, report.TemplateProcessor)
            {
                Parent = childOfChildPanel,
            };

            childOfChildPanel.Children = new[] { simplePanel2 };
            childPanel.Children        = new[] { childOfChildPanel };
            parentPanel.Children       = new[] { childPanel, simplePanel1 };
            parentPanel.Render();

            Assert.AreEqual(ws.Range(2, 2, 7, 20), parentPanel.ResultRange);

            ExcelAssert.AreWorkbooksContentEquals(TestHelper.GetExpectedWorkbook(nameof(DataSourcePanelRender_WithGrouping_MixedPanels_Test),
                                                                                 nameof(TestMultipleHorizontalPanelsGrouping)), ws.Workbook);

            //report.Workbook.SaveAs("test.xlsx");
        }