Group Method

Group(PivotFieldDateGroupOptions)

Groups the PivotField by date or time units.

Declaration

void Group(PivotFieldDateGroupOptions options)

Sub Group(options As PivotFieldDateGroupOptions)

Parameters

Type	Name	Description
PivotFieldDateGroupOptions	options	Date grouping settings.

Examples

Workbook workbook = new Workbook();
workbook.Options.Data.AutomaticGroupDateTimeInPivotTable = false;
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Range["A1:C5"].Value = new object[,]
{
    { "Order Date", "Region", "Amount" },
    { new DateTime(2024, 1, 5), "East", 10 },
    { new DateTime(2024, 1, 18), "West", 20 },
    { new DateTime(2024, 2, 2), "East", 30 },
    { new DateTime(2024, 2, 25), "West", 40 }
};
IPivotCache pivotCache = workbook.PivotCaches.Create(worksheet.Range["A1:C5"]);
IPivotTable pivotTable = worksheet.PivotTables.Add(pivotCache, worksheet.Range["E1"], "PivotTable1");
IPivotField orderDateField = pivotTable.PivotFields["Order Date"];
orderDateField.Orientation = PivotFieldOrientation.RowField;
pivotTable.PivotFields["Amount"].Orientation = PivotFieldOrientation.DataField;
PivotFieldDateGroupOptions options = new PivotFieldDateGroupOptions
{
    GroupBy = PivotFieldDateGroupBy.Months,
    AutoStart = true,
    AutoEnd = true
};
orderDateField.Group(options);
string groupedFieldName = pivotTable.RowFields[0].Name;

Group(PivotFieldNumberGroupOptions)

Groups the PivotField into numeric ranges.

Declaration

void Group(PivotFieldNumberGroupOptions options)

Sub Group(options As PivotFieldNumberGroupOptions)

Parameters

Type	Name	Description
PivotFieldNumberGroupOptions	options	Numeric grouping settings.

Examples

Workbook workbook = new Workbook();
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Range["A1:C5"].Value = new object[,]
{
    { "Order ID", "Region", "Amount" },
    { 10348, "East", 10 },
    { 10362, "East", 20 },
    { 10375, "West", 30 },
    { 10391, "West", 40 }
};
IPivotCache pivotCache = workbook.PivotCaches.Create(worksheet.Range["A1:C5"]);
IPivotTable pivotTable = worksheet.PivotTables.Add(pivotCache, worksheet.Range["E1"], "PivotTable1");
IPivotField orderIdField = pivotTable.PivotFields["Order ID"];
orderIdField.Orientation = PivotFieldOrientation.RowField;
pivotTable.PivotFields["Amount"].Orientation = PivotFieldOrientation.DataField;
PivotFieldNumberGroupOptions options = new PivotFieldNumberGroupOptions
{
    Start = 10348,
    End = 10407,
    AutoStart = false,
    AutoEnd = false,
    Interval = 20
};
orderIdField.Group(options);

Group(PivotFieldCustomGroupOptions)

Creates a custom group from PivotItems in the PivotField.

Declaration

void Group(PivotFieldCustomGroupOptions options)

Sub Group(options As PivotFieldCustomGroupOptions)

Parameters

Type	Name	Description
PivotFieldCustomGroupOptions	options	The custom grouping settings. Must not be null.

Examples

Workbook workbook = new Workbook();
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Range["A1:C5"].Value = new object[,]
{
    { "Product", "Region", "Amount" },
    { "Apple", "East", 10 },
    { "Banana", "West", 20 },
    { "Carrot", "East", 30 },
    { "Date", "West", 40 }
};
IPivotCache pivotCache = workbook.PivotCaches.Create(worksheet.Range["A1:C5"]);
IPivotTable pivotTable = worksheet.PivotTables.Add(pivotCache, worksheet.Range["E1"], "PivotTable1");
IPivotField productField = pivotTable.PivotFields["Product"];
productField.Orientation = PivotFieldOrientation.RowField;
pivotTable.PivotFields["Amount"].Orientation = PivotFieldOrientation.DataField;
PivotFieldCustomGroupOptions options = new PivotFieldCustomGroupOptions
{
    Name = "Fruit Group",
    Items = new List<string> { "Apple", "Banana" }
};
productField.Group(options);

Exceptions

Type	Condition
ArgumentNullException	If `options` is null.
NotSupportedException	If this field cannot be grouped.