Pivot Table Options in Excel

14 Oct 202411 minutes to read

Show or hide the field list

To show or hide the pivot table field list pane, use the ShowFieldList property.

//Enable ShowFieldList
options.ShowFieldList = false;
//Enable ShowFieldList
options.ShowFieldList = false;
'Enable ShowFieldList
options.ShowFieldList = False

Header caption

The RowHeaderCaption and ColumnHeaderCaption properties allows to edit the respective pivot table headers. The header caption can be enabled or disabled using the DisplayFieldCaptions property.

//Enable header captions
options.RowHeaderCaption = "Payment Dates";
options.ColumnHeaderCaption = "Payments";
//Enable header captions
options.RowHeaderCaption = "Payment Dates";
options.ColumnHeaderCaption = "Payments";
'Enable header captions
options.RowHeaderCaption = "Payment Dates"
options.ColumnHeaderCaption = "Payments"

Grand total

XlsIO provides an equivalent API to perform grand totals with the properties as follows.

//Enable GrandTotals
pivotTable.ColumnGrand = false;
pivotTable.RowGrand = true;
//Enable GrandTotals
pivotTable.ColumnGrand = false;
pivotTable.RowGrand = true;
'Enable GrandTotals
pivotTable.ColumnGrand = False
pivotTable.RowGrand = False

Show or hide collapse button

You can also show or hide the Collapse button that appears in the fields of the pivot table, when more than one item exists in a field. The following code example illustrates how to do this.

//Enable ShowDrillIndicators
pivotTable.ShowDrillIndicators = true;
//Enable ShowDrillIndicators
pivotTable.ShowDrillIndicators = true;
'Enable ShowDrillIndicators
pivotTable.ShowDrillIndicators = True

Display field caption and filter option

The filter buttons and field names in the pivot table can be shown or hidden, as in the following code.

//Enable DisplayFieldCaption
pivotTable.DisplayFieldCaptions = true;
//Enable DisplayFieldCaption
pivotTable.DisplayFieldCaptions = true;
'Enable DisplayFieldCaption
pivotTable.DisplayFieldCaptions = True

Repeating row label on each page

You can set the row label on each page while printing, and the header can be viewed on each page.

//Enable RepeatItemsOnEachPrintedPage
pivotTable.RepeatItemsOnEachPrintedPage = true;
//Enable RepeatItemsOnEachPrintedPage
pivotTable.RepeatItemsOnEachPrintedPage = true;
'Enable RepeatItemsOnEachPrintedPage
pivotTable.RepeatItemsOnEachPrintedPage = True

Repeat Labels

You can repeat labels for row or column fields when the pivot table layout is set to tabular or outline layout forms.

Specific Pivot Field

The following code illustrates how to set the repeat labels option to a specific pivot field.

//Set repeat labels option to a specific pivot field
pivotTable.Fields[0].RepeatLabels = true;
//Set repeat labels option to a specific pivot field
pivotTable.Fields[0].RepeatLabels = true;
‘Set repeat labels option to a specific pivot field
pivotTable.Fields(0).RepeatLabels = True

All Pivot Fields

The following code illustrates how to set the repeat labels option to all the pivot fields.

//Set repeat labels option to all the pivot fields
pivotTable.Options.RepeatAllLabels(true);
//Set repeat labels option to all the pivot fields
pivotTable.Options.RepeatAllLabels(true);
‘Set repeat labels option to all the pivot fields
pivotTable.Options.RepeatAllLabels(True)

Show Values Row

To show values in rows in a pivot table, use the ShowValuesRow property. The following code illustrates how to show values row option in the pivot table.

//Set the show values row option in pivot table.
pivotTable.Options.ShowValuesRow = true;
//Set the show values row option in pivot table.
pivotTable.Options.ShowValuesRow = true;
'Set the show values row option in pivot table.
pivotTable.Options.ShowValuesRow = true

Other pivot table operations

Adding calculated field in the existing pivot table

Calculated field is a special type of database field that perform calculations by using the contents of other fields in the pivot table with the given formula. The formula can contain operators and expressions as in other worksheet formulas. You can use constants and refer to the data from the PivotTable.

You can read and create the calculated fields in the existing pivot table. The following are the Excel restrictions when using the formula:

  • Formula cannot contain cell references or defined names.
  • Formula cannot contain Worksheet functions that require cell references.
  • Formula cannot use array functions.

The calculated field in XlsIO can be achieved using the following code sample.

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

  //Add calculated field to the first pivot table
  IPivotField field = pivotTable.CalculatedFields.Add("Percent", "Sales/Total*100");

  string fileName = "PivotTableCalculate.xlsx";

  //Saving the workbook as stream
  FileStream stream = new FileStream(fileName, FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Open("PivotTable.xlsx");
  IWorksheet sheet = workbook.Worksheets[1];
  IPivotTable pivotTable = sheet.PivotTables[0];

  //Add calculated field to the first pivot table
  IPivotField field = pivotTable.CalculatedFields.Add("Percent", "Sales/Total*100");

  workbook.SaveAs("PivotTableCalculate.xlsx");
  excelEngine.ThrowNotSavedOnDestroy = false;
}
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 sheet As IWorksheet = workbook.Worksheets(1)
  Dim pivotTable As IPivotTable = sheet.PivotTables(0)

  ' Add calculated field to the first pivot table
  Dim field As IPivotField = pivotTable.CalculatedFields.Add("Percent", "Sales/Total*100")

  workbook.SaveAs("PivotTableCalculate.xlsx")
  excelEngine.ThrowNotSavedOnDestroy = False
End Using

The formula can also be set to the IPivotField property as follows.

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 sheet = workbook.Worksheets[1];
	IPivotTable pivotTable = sheet.PivotTables[0];

	//Add calculated field to the first pivot table
	IPivotField field = pivotTable.CalculatedFields.Add("Percent", "Units/3000*100");

	//Set Field Formula
	field.Formula = "Units/3000*200";

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

	//Dispose streams
	outputStream.Dispose();
	inputStream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Open("PivotTable.xlsx");
  IWorksheet sheet = workbook.Worksheets[0];
  IPivotTable pivotTable = sheet.PivotTables[0];

  //Add calculated field to the first pivot table
  IPivotField field = pivotTable.CalculatedFields.Add("Percent", "Sales/Total*100");

  //Set Field Formula
  field.Formula = "Sales/Total*200";

  workbook.SaveAs("PivotTable.xlsx");
  excelEngine.ThrowNotSavedOnDestroy = false;
}
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 sheet As IWorksheet = workbook.Worksheets(0)
  Dim pivotTable As IPivotTable = sheet.PivotTables(0)

  Dim field As IPivotField = pivotTable.CalculatedFields.Add("Percent", "Sales/Total*100")

  'Set Field Formula
  field.Formula = "Sales/Total*200"

  workbook.SaveAs("PivotTable.xlsx")
  excelEngine.ThrowNotSavedOnDestroy = False
End Using

A complete working example to add calculated field in pivot table in C# is present on this GitHub page.