Grouping in Pivot Table

14 Oct 202413 minutes to read

Date and time fields in the pivot table can be grouped using XlsIO.

XlsIO supports grouping pivot data based on the following categories.

• Date
• Month
• Year
• Quarters
• Hours
• Minutes
• Seconds

Group

The pivot fields can be grouped by using the GroupBy property in the IPivotField.FieldGroup.

The following code example illustrates how to group pivot fields based on a period.

C# [Cross-platform]

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  FileStream fileStream = new FileStream("InputTemplate.xlsx", FileMode.Open, FileAccess.Read);
  IWorkbook workbook = application.Workbooks.Open(fileStream);
  IWorksheet worksheet = workbook.Worksheets[0];
  IWorksheet pivotSheet = workbook.Worksheets.Create();

  IPivotCache cache = workbook.PivotCaches.Add(worksheet["A1:B16"]);
  pivotSheet.PivotTables.Add("PivotTable", pivotSheet["A2"], cache);
  IPivotTable pivotTable = pivotSheet.PivotTables[0];
  pivotTable.Fields[1].Axis = PivotAxisTypes.Row;
  pivotTable.Fields[1].Subtotals = PivotSubtotalTypes.None;
  var field1 = pivotTable.Fields[1].FieldGroup;


  //Apply pivot table grouping
  field1.GroupBy = PivotFieldGroupType.Seconds | PivotFieldGroupType.Years | PivotFieldGroupType.Quarters;

  pivotTable.Options.ShowFieldList = false;
  pivotTable.Options.RowLayout = PivotTableRowLayout.Tabular;

  //Saving the workbook as stream
  FileStream stream = new FileStream("PivotTableGrouping.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(stream);
  stream.Dispose();                
}

C# [Windows-specific]

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;   
  IWorkbook workbook = application.Workbooks.Open("InputTemplate.xlsx");
  IWorksheet worksheet = workbook.Worksheets[0];
  IWorksheet pivotSheet = workbook.Worksheets.Create();

  IPivotCache cache = workbook.PivotCaches.Add(worksheet["A1:B16"]);
  pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A2"], cache);
  IPivotTable pivotTable = pivotSheet.PivotTables[0];
  pivotTable.Fields[1].Axis = PivotAxisTypes.Row;
  pivotTable.Fields[1].Subtotals = PivotSubtotalTypes.None;
  var field1 = pivotTable.Fields[1].FieldGroup;

  //Apply pivot table grouping
  field1.GroupBy = PivotFieldGroupType.Seconds | PivotFieldGroupType.Years | PivotFieldGroupType.Quarters;

  pivotTable.Options.ShowFieldList = false;
  pivotTable.Options.RowLayout = PivotTableRowLayout.Tabular;             

  //Saving the workbook   
  workbook.SaveAs("PivotTableGrouping.xlsx");              
}

VB.NET [Windows-specific]

Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Open("InputTemplate.xlsx")
  Dim worksheet As IWorksheet = workbook.Worksheets(0)
  Dim pivotSheet As IWorksheet = workbook.Worksheets.Create()

  Dim cache As IPivotCache = workbook.PivotCaches.Add(worksheet("A1:B16"))
  pivotSheet.PivotTables.Add("PivotTable1", pivotSheet("A2"), cache)
  Dim pivotTable As IPivotTable = pivotSheet.PivotTables(0)
  pivotTable.Fields(1).Axis = PivotAxisTypes.Row
  pivotTable.Fields(1).Subtotals = PivotSubtotalTypes.None
  Dim field1 = pivotTable.Fields(1).FieldGroup

  'Apply pivot table grouping
  field1.GroupBy = PivotFieldGroupType.Seconds Or PivotFieldGroupType.Years Or PivotFieldGroupType.Quarters

  pivotTable.Options.ShowFieldList = False
  pivotTable.Options.RowLayout = PivotTableRowLayout.Tabular

  'Saving the workbook
  workbook.SaveAs("PivotTableGrouping.xlsx")
End Using

By executing the program, you will get the Excel file as below

Ungroup

The grouping in the pivot table can be removed by making the GroupBy property value None.

The following code example illustrates how to remove grouping from the pivot table.

C# [Cross-platform]

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  FileStream fileStream = new FileStream("InputTemplate.xlsx", FileMode.Open, FileAccess.Read);
  IWorkbook workbook = application.Workbooks.Open(fileStream);
  IWorksheet worksheet = workbook.Worksheets[0];

  IPivotTable pivotTable = worksheet.PivotTables[0];
  pivotTable.Fields[0].Axis = PivotAxisTypes.Row;
  pivotTable.Fields[0].Subtotals = PivotSubtotalTypes.None;
  var field1 = pivotTable.Fields[1].FieldGroup;

  //Remove pivot table grouping
  field1.GroupBy = PivotFieldGroupType.None;

  //Saving the workbook as stream
  FileStream stream = new FileStream("PivotTableUnGrouping.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(stream);
  stream.Dispose();                
}

C# [Windows-specific]

using (ExcelEngine excelEngine = new ExcelEngine())
 {
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;               
  IWorkbook workbook = application.Workbooks.Open("InputTemplate.xlsx");
  IWorksheet worksheet = workbook.Worksheets[0];

  IPivotTable pivotTable = worksheet.PivotTables[0];
  pivotTable.Fields[0].Axis = PivotAxisTypes.Row;
  pivotTable.Fields[0].Subtotals = PivotSubtotalTypes.None;
  var field1 = pivotTable.Fields[1].FieldGroup;

  //Remove pivot table grouping
  field1.GroupBy = PivotFieldGroupType.None;

  //Saving the workbook
  workbook.SaveAs("PivotTableUnGrouping.xlsx");                
 }

VB.NET [Windows-specific]

Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Open("InputTemplate.xlsx")
  Dim worksheet As IWorksheet = workbook.Worksheets(0)

  Dim pivotTable As IPivotTable = worksheet.PivotTables(0)
  pivotTable.Fields(0).Axis = PivotAxisTypes.Row
  pivotTable.Fields(0).Subtotals = PivotSubtotalTypes.None
  Dim field1 = pivotTable.Fields(1).FieldGroup

  'Remove pivot table grouping
  field1.GroupBy = PivotFieldGroupType.None

  'Saving the workbook
  workbook.SaveAs("PivotTableUnGrouping.xlsx")
End Using

Expand or collapse

XlsIO allows you to expand and collapse the PivotFieldItems or simply the pivot table rows using IsHiddenDetails of PivotItemOptions.

The following code example illustrates how to expand or collapse rows in the pivot table.

C# [Cross-platform]

using (ExcelEngine excelEngine = new ExcelEngine())
{
	IApplication application = excelEngine.Excel;
	application.DefaultVersion = ExcelVersion.Xlsx;
	FileStream inputStream = new FileStream(Path.GetFullPath(@"Data/InputTemplate.xlsx"), FileMode.Open, FileAccess.Read);
	IWorkbook workbook = application.Workbooks.Open(inputStream);
	IWorksheet worksheet = workbook.Worksheets[0];
	IWorksheet pivotSheet = workbook.Worksheets[1];

	//Create pivot cache with the given data range
	IPivotCache cache = workbook.PivotCaches.Add(worksheet["A1:H50"]);

	//Create "PivotTable1" with the cache at the specified range
	IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cache);

	//Add pivot table fields (Row and Column fields)
	pivotTable.Fields[0].Axis = PivotAxisTypes.Row;
	pivotTable.Fields[1].Axis = PivotAxisTypes.Row;

	//Add data field
	IPivotField field = pivotTable.Fields[2];
	pivotTable.DataFields.Add(field, "Sum", PivotSubtotalTypes.Sum);

	//Initialize PivotItemOptions
	PivotItemOptions options = new PivotItemOptions();
	options.IsHiddenDetails = false;

	//Collapsing the first and second items of the first pivot field using PivotItemOptions
	(pivotTable.Fields[0] as PivotFieldImpl).AddItemOption(0, options);
	(pivotTable.Fields[0] as PivotFieldImpl).AddItemOption(1, options);

	#region Save
	//Saving the workbook
	FileStream outputStream = new FileStream(Path.GetFullPath("Output/ExpandOrCollapse.xlsx"), FileMode.Create, FileAccess.Write);
	workbook.SaveAs(outputStream);
	#endregion

	//Dispose streams
	outputStream.Dispose();
	inputStream.Dispose();
}

C# [Windows-specific]

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Open("InputTemplate.xlsx");
  IWorksheet worksheet = workbook.Worksheets[0];

  //Create pivot cache with the given data range
  IPivotCache cache = workbook.PivotCaches.Add(worksheet["A1:C13"]);

  //Create "PivotTable1" with the cache at the specified range
  IPivotTable pivotTable = worksheet.PivotTables.Add("PivotTable1", worksheet["E1"], cache);

  //Add pivot table fields (Row and Column fields)
  pivotTable.Fields[0].Axis = PivotAxisTypes.Row;
  pivotTable.Fields[1].Axis = PivotAxisTypes.Row;

  //Add data field
  IPivotField field = pivotTable.Fields[2];
  pivotTable.DataFields.Add(field, "Sum", PivotSubtotalTypes.Sum);

  //Initialize PivotItemOptions
  PivotItemOptions options = new PivotItemOptions();
  options.IsHiddenDetails = false;

  //Collapsing the first and second items of the first pivot field using PivotItemOptions
  (pivotTable.Fields[0] as PivotFieldImpl).AddItemOption(0, options);
  (pivotTable.Fields[0] as PivotFieldImpl).AddItemOption(1, options);

  workbook.SaveAs("Output.xlsx");
}

VB.NET [Windows-specific]

Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Open("InputTemplate.xlsx")
  Dim worksheet As IWorksheet = workbook.Worksheets(0)

  'Create pivot cache with the given data range
  Dim cache As IPivotCache = workbook.PivotCaches.Add(worksheet("A1:C13"))

  'Create "PivotTable1" with the cache at the specified range
  Dim pivotTable As IPivotTable = worksheet.PivotTables.Add("PivotTable1", worksheet("E1"), cache)

  'Add pivot table fields (Row and Column fields)
  pivotTable.Fields(0).Axis = PivotAxisTypes.Row
  pivotTable.Fields(1).Axis = PivotAxisTypes.Row

  'Add data field
  Dim field As IPivotField = pivotTable.Fields(2)
  pivotTable.DataFields.Add(field, "Sum", PivotSubtotalTypes.Sum)

  'Initialize PivotItemOptions
  Dim options As PivotItemOptions = New PivotItemOptions
  options.IsHiddenDetails = False

  'Collapsing the first and second items of the first pivot field using PivotItemOptions
  CType(pivotTable.Fields(0), PivotFieldImpl).AddItemOption(0, options)
  CType(pivotTable.Fields(0), PivotFieldImpl).AddItemOption(1, options)

  workbook.SaveAs("Output.xlsx")
End Using

A complete working example to expand or collapse rows in pivot table in C# is present on this GitHub page.