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