public void WithHeaders()
{
LoadTemplate("91_plaindata.xlsx");
using (var subtotal = new Subtotal(_rng))
{
var summaries = new [] { new SubtotalSummaryFunc("sum", 7), };
subtotal.AddGrandTotal(summaries);
subtotal.GroupBy(2, summaries, true);
subtotal.GroupBy(3, summaries, true);
subtotal.AddHeaders(2);
subtotal.AddHeaders(3);
}
CompareWithGauge(_workbook, "Subtotal_WithHeaders.xlsx");
}
private void Process(IXLRange root, GroupTag[] groups, bool summaryAbove, SubtotalSummaryFunc[] funcs, bool disableGrandTotal)
{
var groupRow = root.LastRow();
// DoGroups
var level = 0;
var r = root.Offset(0, 0, root.RowCount() - 1, root.ColumnCount());
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);
}
subtotal.GroupBy(g.Column, g.DisableSubtotals ? new SubtotalSummaryFunc[0] : funcs, g.PageBreaks, labFormat);
g.Level = ++level;
}
foreach (var g in groups.Where(x => x.IsWithHeader).OrderBy(x => x.Column))
{
subtotal.AddHeaders(g.Column);
}
var 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);
}
private void Process(ProcessingContext context, GroupTag[] groups, bool summaryAbove, SummaryFuncTag[] summaries, bool disableGrandTotal)
{
var root = context.Range;
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, groups))
{
if (TotalLabel != null)
{
subtotal.TotalLabel = TotalLabel;
}
if (GrandLabel != null)
{
subtotal.GrandLabel = GrandLabel;
}
if (!disableGrandTotal)
{
var total = subtotal.AddGrandTotal(summaries);
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 && summaries.Length == 0)
{
subtotal.ScanForGroups(g.Column);
}
else
{
subtotal.GroupBy(g.Column, g.DisableSubtotals ? new SummaryFuncTag[0] : summaries, 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);
}