public void ScanForGroupsTest() { void AssertGroup(SubtotalGroup group, string expectedAddress, string expectedTitle) { group.Range.RangeAddress.ToString().Should().Be(expectedAddress); group.Level.Should().Be(1); group.GroupTitle.Should().Be(expectedTitle); } _workbook = new XLWorkbook(); var sheet = _workbook.AddWorksheet("test"); sheet.Range("A2:A5").Value = "val1"; sheet.Range("A6:A9").Value = "val2"; sheet.Range("A10:A13").Value = "val3"; sheet.Range("B2:B4").Value = "val4"; sheet.Range("B5:B11").Value = "val5"; sheet.Range("B12:B13").Value = "val6"; sheet.Range("C2:C3").Value = "val7"; sheet.Range("C4:C6").Value = "val8"; sheet.Range("C7:C8").Value = "val9"; sheet.Range("C9:C12").Value = "val7"; sheet.Range("C13:C13").Value = "val8"; _rng = sheet.Range("A2:C13"); using (var subtotal = new Subtotal(_rng)) { var groups = subtotal.ScanForGroups(1); groups.Length.Should().Be(3); AssertGroup(groups[0], "A2:C5", "val1"); AssertGroup(groups[1], "A6:C9", "val2"); AssertGroup(groups[2], "A10:C13", "val3"); groups = subtotal.ScanForGroups(2); groups.Length.Should().Be(5); AssertGroup(groups[0], "A2:C4", "val4"); AssertGroup(groups[1], "A5:C5", "val5"); AssertGroup(groups[2], "A6:C9", "val5"); AssertGroup(groups[3], "A10:C11", "val5"); AssertGroup(groups[4], "A12:C13", "val6"); groups = subtotal.ScanForGroups(3); groups.Length.Should().Be(9); AssertGroup(groups[0], "A2:C3", "val7"); AssertGroup(groups[1], "A4:C4", "val8"); AssertGroup(groups[2], "A5:C5", "val8"); AssertGroup(groups[3], "A6:C6", "val8"); AssertGroup(groups[4], "A7:C8", "val9"); AssertGroup(groups[5], "A9:C9", "val7"); AssertGroup(groups[6], "A10:C11", "val7"); AssertGroup(groups[7], "A12:C12", "val7"); AssertGroup(groups[8], "A13:C13", "val8"); } }
public void ScanForGroupsTest() { LoadTemplate("9_plaindata.xlsx"); SubtotalGroup[] groups; using (var subtotal = new Subtotal(_rng)) groups = subtotal.ScanForGroups(2); groups.Length.Should().Be(3); groups[0].Range.RangeAddress.ToString().Should().Be("C3:I26"); groups[0].Level.Should().Be(1); groups[0].GroupTitle.Should().Be("Central"); groups[1].Range.RangeAddress.ToString().Should().Be("C27:I38"); groups[1].Level.Should().Be(1); groups[1].GroupTitle.Should().Be("East"); groups[2].Range.RangeAddress.ToString().Should().Be("C39:I44"); groups[2].Level.Should().Be(1); groups[2].GroupTitle.Should().Be("West"); }
private void Process(IXLRange root, GroupTag[] groups, bool summaryAbove, SubtotalSummaryFunc[] funcs, bool disableGrandTotal) { var groupRow = root.LastRow(); // DoGroups var level = 0; var rows = root.RowCount() - 1; var columns = root.ColumnCount(); if (rows <= 0 || columns <= 0) { return; } var r = root.Offset(0, 0, rows, columns); using (var subtotal = new Subtotal(r, summaryAbove)) { if (TotalLabel != null) { subtotal.TotalLabel = TotalLabel; } if (GrandLabel != null) { subtotal.GrandLabel = GrandLabel; } if (!disableGrandTotal) { var total = subtotal.AddGrandTotal(funcs); total.SummaryRow.Cell(2).Value = total.SummaryRow.Cell(1).Value; total.SummaryRow.Cell(1).Value = null; level++; } foreach (var g in groups.OrderBy(x => x.Column)) { Func <string, string> labFormat = null; if (!string.IsNullOrEmpty(g.LabelFormat)) { labFormat = title => string.Format(LabelFormat, title); } if (g.MergeLabels == MergeMode.Merge2 && funcs.Length == 0) { subtotal.ScanForGroups(g.Column); } else { subtotal.GroupBy(g.Column, g.DisableSubtotals ? new SubtotalSummaryFunc[0] : funcs, g.PageBreaks, labFormat); } g.Level = ++level; } _maxLevel = level; foreach (var g in groups.Where(x => x.IsWithHeader).OrderBy(x => x.Column)) { subtotal.AddHeaders(g.Column); } Dictionary <int, GroupTag> gr; if (disableGrandTotal) { gr = groups.ToDictionary(x => x.Level, x => x); } else { gr = groups.Union(new[] { new GroupTag { Column = 1, Level = 1 } }) .ToDictionary(x => x.Level, x => x); } foreach (var subGroup in subtotal.Groups.OrderBy(x => x.Column).Reverse()) { var groupTag = gr[subGroup.Level]; FormatHeaderFooter(subGroup, groupRow); GroupRender(subGroup, groupTag); } } // Rem DoDeleteSpecialRow root.LastRow().Delete(XLShiftDeletedCells.ShiftCellsUp); }