Working with Pivot Tables

29 Nov 202424 minutes to read

You can easily arrange and summarize complex data in a Pivot Table.

Creation and manipulation of pivot tables is supported in Excel 2007 and later formats (i.e., *.xlsx), along with preserving existing pivot tables.

NOTE

Creation and manipulation of pivot tables is not supported in Excel 2003 format (i.e., *.xls). It is only possible to preserve the existing pivot table in this format.

Create, Edit and Remove

Create

Steps to create a simple pivot table:

• Add pivot cache
• Add pivot table
• Add row and column fields
• Add data fields

Pivot tables do not take data directly from the source data, but take from the pivot cache that memorizes a snapshot of the data. The IPivotCache interface caches the data that needs to be summarized.

The data in worksheet is added to the pivot cache as follows.

C# [Cross-platform]

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

C# [Windows-specific]

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

VB.NET [Windows-specific]

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

IPivotTable represents a single pivot table object created from the cache. It has properties that customizes the pivot table. The following code creates a blank pivot table.

C# [Cross-platform]

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

C# [Windows-specific]

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

VB.NET [Windows-specific]

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

The pivot table should be populated with required fields. The IPivotField represents a single field in the pivot table, which includes row, column, and data field axes.

C# [Cross-platform]

//Add Pivot table fields (row and column fields)
pivotTable.Fields[2].Axis = PivotAxisTypes.Row;
pivotTable.Fields[6].Axis = PivotAxisTypes.Row;
pivotTable.Fields[3].Axis = PivotAxisTypes.Column;

C# [Windows-specific]

//Add Pivot table fields (row and column fields)
pivotTable.Fields[2].Axis = PivotAxisTypes.Row;
pivotTable.Fields[6].Axis = PivotAxisTypes.Row;
pivotTable.Fields[3].Axis = PivotAxisTypes.Column;

VB.NET [Windows-specific]

'Add Pivot table fields (row and column fields)
pivotTable.Fields(2).Axis = PivotAxisTypes.Row
pivotTable.Fields(6).Axis = PivotAxisTypes.Row
pivotTable.Fields(3).Axis = PivotAxisTypes.Column

The IPivotDataFields represents a collection of data fields in the pivot table. The data field is added with the required subtotal function using the PivotSubtotalTypes enumeration. The following code explains how to configure a pivot field as a data field.

C# [Cross-platform]

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

C# [Windows-specific]

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

VB.NET [Windows-specific]

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

The following code example illustrates how to create a pivot table with existing data in the worksheet.

C# [Cross-platform]

using (ExcelEngine excelEngine = new ExcelEngine())
{
	IApplication application = excelEngine.Excel;
	application.DefaultVersion = ExcelVersion.Xlsx;
	FileStream inputStream = new FileStream(Path.GetFullPath(@"Data/PivotData.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[2].Axis = PivotAxisTypes.Row;
	pivotTable.Fields[6].Axis = PivotAxisTypes.Row;
	pivotTable.Fields[3].Axis = PivotAxisTypes.Column;

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

	#region Save
	//Saving the workbook
	FileStream outputStream = new FileStream(Path.GetFullPath("Output/PivotTable.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("PivotData.xlsx");
  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[2].Axis = PivotAxisTypes.Row;
  pivotTable.Fields[6].Axis = PivotAxisTypes.Row;
  pivotTable.Fields[3].Axis = PivotAxisTypes.Column;

  //Add data field
  IPivotField field = pivotTable.Fields[5];
  pivotTable.DataFields.Add(field, "Sum", PivotSubtotalTypes.Sum);
  
  workbook.SaveAs("PivotTable.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("PivotData.xlsx")
  Dim worksheet As IWorksheet = workbook.Worksheets(0)
  Dim pivotSheet As IWorksheet = workbook.Worksheets(1)

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

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

  'Add Pivot table fields (Row and Column fields)
  pivotTable.Fields(2).Axis = PivotAxisTypes.Row
  pivotTable.Fields(6).Axis = PivotAxisTypes.Row
  pivotTable.Fields(3).Axis = PivotAxisTypes.Column

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

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

A complete working example to create a pivot table in C# is present on this GitHub page.

Edit

To edit a pivot table, use the Layout function to set the pivot data on the worksheet. With this, you can easily edit the pivot table, access its values, and make other modifications.

The following code example illustrates how to edit 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[1];

	//Accessing the pivot table in the worksheet
	IPivotTable pivotTable = worksheet.PivotTables[0];

	//Layout the pivot table to set the values to the worksheet
	pivotTable.Layout();

	//Set Text in cell B2
	worksheet.Range["B2"].Text = "William";

	#region Save
	//Saving the workbook
	FileStream outputStream = new FileStream(Path.GetFullPath("Output/Output.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[1];

  //Accessing the pivot table in the worksheet
  IPivotTable pivotTable = worksheet.PivotTables[0];

  //Layout the pivot table to set the values to the worksheet
  pivotTable.Layout();

  //Set Text in cell B2
  worksheet.Range["B2"].Text = "William";

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

VB.NET [Windows-specific]

Using excelEngine As 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(1)

  ' Accessing the pivot table in the worksheet
  Dim pivotTable As IPivotTable = worksheet.PivotTables(0)

  ' Layout the pivot table to set the values to the worksheet
  pivotTable.Layout()

  ' Set text in cell B2
  worksheet.Range("B2").Text = "William"

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

A complete working example to edit a pivot table in C# is present on this GitHub page.

Remove

The Remove method can be used to remove a pivot table from the worksheet. You can either remove it by specifying the index or the pivot table name from the collection.

The following code snippet illustrates how to remove a pivot table at a specified index value using the RemoveAt method.

C# [Cross-platform]

//Removes a pivot table with the specified index from this collection
worksheet.PivotTables.RemoveAt(0);

C# [Windows-specific]

//Removes a pivot table with the specified index from this collection
worksheet.PivotTables.RemoveAt(0);

VB.NET [Windows-specific]

'Removes a pivot table with the specified index from this collection
worksheet.PivotTables.RemoveAt(0)

The following code example illustrates how to remove the pivot table using Remove method.

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];

	//Remove the pivot table
	pivotSheet.PivotTables.Remove("PivotTable1");

	#region Save
	//Saving the workbook
	FileStream outputStream = new FileStream(Path.GetFullPath("Output/Output.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[1];
  IWorksheet pivotSheet = workbook.Worksheets[1];

  //Remove the pivot table
  pivotSheet.PivotTables.Remove("PivotTable1");

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

VB.NET [Windows-specific]

Using excelEngine As 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(1)
  Dim pivotSheet As IWorksheet = workbook.Worksheets(1)

  'Remove the pivot table
  pivotSheet.PivotTables.Remove("PivotTable1")

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

A complete working example to remove a pivot table in C# is present on this GitHub page.

Styles and Formatting

A pivot table can be accessed from the IPivotTables interface, which contains the collection of pivot tables in the worksheet. You can apply styles and formatting to the pivot table using IPivotTable properties and methods.

To learn more about styling and formatting the pivot table with the Syncfusion® Excel library, click here.

Layout

When you create pivot table in XlsIO, the pivot values are not set in the worksheet cells. Pivot table layout option set the pivot values to worksheet cells. XlsIO supports the layout option for all three pivot table types.

The following code example illustrates how to layout the pivot table.

C# [Cross-platform]

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

  IPivotTable pivotTable = worksheet.PivotTables[0];
  //Layout the pivot table.
  pivotTable.Layout();

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

C# [Windows-specific]

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  IWorkbook workbook = application.Workbooks.Open("PivotTable.xlsx");
  IWorksheet worksheet = workbook.Worksheets[1];

  IPivotTable pivotTable = worksheet.PivotTables[0];
  //Layout the pivot table.
  pivotTable.Layout();

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

VB.NET [Windows-specific]

Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  Dim workbook As IWorkbook = application.Workbooks.Open("PivotTable.xlsx")
  Dim pivotSheet As IWorksheet = workbook.Worksheets(0)

  Dim pivotTable As IPivotTable = worksheet.PivotTables(0)
  'Layout the pivot table.
  pivotTable.Layout()

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

A complete working example to layout a pivot table in C# is present on this GitHub page.

To learn more about pivot table layout with the Syncfusion® Excel library, click here.

NOTE

Based on Excel behavior, when the Layout method is invoked, it automatically sets the IsRefreshOnLoad property to false. To ensure data refresh on load, it’s recommended to set IsRefreshOnLoad to true after calling Layout.

Refresh

When you update the pivot table data source, you should refresh the pivot table manually to load the new data source into it. Essential® XlsIO supports this refreshing of pivot table data source through IsRefreshOnLoad property of PivotCacheImpl.

The following code example illustrates how to dynamically refresh the data in a pivot table. In prior:

• Create the pivot table using Excel GUI.
• Specify the named range to be the data source of the pivot table.
• Make sure that the Refresh on Open option of the pivot table is selected.
• Dynamically refresh the data in the named range.

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 pivotSheet = workbook.Worksheets[0];

	//Change the range values that the Pivot Tables range refers to
	workbook.Names["PivotRange"].RefersToRange = pivotSheet.Range["A1:H25"];

	#region Save
	//Saving the workbook
	FileStream outputStream = new FileStream(Path.GetFullPath("Output/PivotTable.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("PivotTable.xlsx");
  IWorksheet pivotSheet = workbook.Worksheets[0];

  //Change the range values that the Pivot Tables range refers to
  workbook.Names["PivotRange"].RefersToRange = pivotSheet.Range["A1:D27"];

  workbook.SaveAs("PivotTable_DynamicRange.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("PivotTable.xlsx")
  Dim pivotSheet As IWorksheet = workbook.Worksheets(0)

  'Change the range values that the Pivot Tables range refers to
  workbook.Names("PivotRange").RefersToRange = pivotSheet.Range("A1:D27")

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

A complete working example to refresh a pivot table dynamically in C# is present on this GitHub page.

The following code example illustrates how to refresh the pivot table after update the cell value in pivot data source.

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];

	//Updating a new value in the pivot data
	worksheet.Range["C2"].Value = "250";

	//Accessing the pivot table 
	IPivotTable pivotTable = workbook.Worksheets[1].PivotTables[0];
	PivotTableImpl pivotTableImpl = pivotTable as PivotTableImpl;

	//Refreshing pivot cache to update the pivot table
	pivotTableImpl.Cache.IsRefreshOnLoad = true;

	#region Save
	//Saving the workbook
	FileStream outputStream = new FileStream(Path.GetFullPath("Output/RefreshPivotTable.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("Sample.xlsx");
  IWorksheet worksheet = workbook.Worksheets[0];

  //Updating a new value in the pivot data
  worksheet.Range["C2"].Value = "250";

  //Accessing the pivot table 
  IPivotTable pivotTable = worksheet.PivotTables[0];
  PivotTableImpl pivotTableImpl = pivotTable as PivotTableImpl;

  //Refreshing pivot cache to update the pivot table
  pivotTableImpl.Cache.IsRefreshOnLoad = true;

  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("Sample.xlsx")
  Dim worksheet As IWorksheet = workbook.Worksheets(0)

  'Updating a new value in the pivot data
  worksheet.Range("C2").Value = "250"

  'Accessing the pivot table
  Dim pivotTable As IPivotTable = worksheet.PivotTables(0)
  Dim pivotTableImpl As PivotTableImpl = CType(pivotTable, PivotTableImpl)

  'Refreshing pivot cache to update the pivot table
  pivotTableImpl.Cache.IsRefreshOnLoad = True

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

A complete working example to refresh a pivot table in C# is present on this GitHub page.

Sorting and Filtering

Sorting and filtering in pivot tables refer to the functionalities that allow users to organize and manipulate the data displayed within the pivot table dynamically.

Sorting

Sorting enables users to rearrange the data in the pivot table based on specified criteria, such as sorting data alphabetically, numerically, or by date. Users can sort data in ascending or descending order to analyze trends or identify patterns more easily.

Filtering

Filtering allows users to display only the specific data they want to see within the pivot table. Users can apply filters to individual fields in the pivot table to include or exclude certain values, categories, or ranges of data.

To learn more about sorting and filtering of pivot table with the Syncfusion® Excel library, click here.

Grouping

Grouping is the process of categorizing data based on common characteristics or values to facilitate analysis and visualization. It allows users to summarize and organize large datasets more effectively.

To learn more about grouping of pivot table with the Syncfusion® Excel library, click here.

See Also

• How to create Pivot Table in Excel document using C#, VB.NET?
• Sort Excel pivot table data by row and column fields in C# using XlsIO
• How to set custom format on data fields of a Pivot Table?
• Create a pivot table in Excel workbook with data exported using template markers
• How to sort pivot fields in Excel using XlsIO?
• Blog: Excel pivot table in C# and VB.NET