Configure Pivot Table Fields

This topic explains how to configure the fields in your pivot table to suit your data analysis needs. Learn how to manage field levels, rename fields, expand or collapse field groups, sort fields, and control the visibility of the field list and headers.

Configure Pivot Table Fields

The fields of a pivot table can be configured using the methods of the IPivotCaches interface and IPivotTables interface, as shown in the example code shared below.

Object sourceData = new Object[][] { { "Order ID", "Product", "Category", "Amount", "Date", "Country" },
        { 1, "Carrots", "Vegetables", 4270, new GregorianCalendar(2018, 0, 6), "United States" },
        { 2, "Broccoli", "Vegetables", 8239, new GregorianCalendar(2018, 0, 7), "United Kingdom" },
        { 3, "Banana", "Fruit", 617, new GregorianCalendar(2018, 0, 8), "United States" },
        { 4, "Banana", "Fruit", 8384, new GregorianCalendar(2018, 0, 10), "Canada" },
        { 5, "Beans", "Vegetables", 2626, new GregorianCalendar(2018, 0, 10), "Germany" },
        { 6, "Orange", "Fruit", 3610, new GregorianCalendar(2018, 0, 11), "United States" },
        { 7, "Broccoli", "Vegetables", 9062, new GregorianCalendar(2018, 0, 11), "Australia" },
        { 8, "Banana", "Fruit", 6906, new GregorianCalendar(2018, 0, 16), "New Zealand" },
        { 9, "Apple", "Fruit", 2417, new GregorianCalendar(2018, 0, 16), "France" },
        { 10, "Apple", "Fruit", 7431, new GregorianCalendar(2018, 0, 16), "Canada" },
        { 11, "Banana", "Fruit", 8250, new GregorianCalendar(2018, 0, 16), "Germany" },
        { 12, "Broccoli", "Vegetables", 7012, new GregorianCalendar(2018, 0, 18), "United States" },
        { 13, "Carrots", "Vegetables", 1903, new GregorianCalendar(2018, 0, 20), "Germany" },
        { 14, "Broccoli", "Vegetables", 2824, new GregorianCalendar(2018, 0, 22), "Canada" },
        { 15, "Apple", "Fruit", 6946, new GregorianCalendar(2018, 0, 24), "France" }, };

IWorksheet worksheet = workbook.getWorksheets().get(0);
worksheet.getRange("A1:F16").setValue(sourceData);
worksheet.getRange("A:F").setColumnWidth(15);
IPivotCache pivotcache = workbook.getPivotCaches().create(worksheet.getRange("A1:F16"));
IPivotTable pivottable = worksheet.getPivotTables().add(pivotcache, worksheet.getRange("H7"), "pivottable1");
worksheet.getRange("D2:D16").setNumberFormat("$#,##0.00");
worksheet.getRange("I9:O11").setNumberFormat("$#,##0.00");
worksheet.getRange("H:O").setColumnWidth(12);
        
// config pivot table's fields
IPivotField field_Category = pivottable.getPivotFields().get("Category");
field_Category.setOrientation(PivotFieldOrientation.RowField);

IPivotField field_Product = pivottable.getPivotFields().get("Product");
field_Product.setOrientation(PivotFieldOrientation.ColumnField);

IPivotField field_Amount = pivottable.getPivotFields().get("Amount");
field_Amount.setOrientation(PivotFieldOrientation.DataField);

IPivotField field_Country = pivottable.getPivotFields().get("Country");
field_Country.setOrientation(PivotFieldOrientation.PageField);

Manage Pivot Field Level

In order to manage the field level of a pivot table, refer to the following example code.

// Product in level 1.
IPivotField field_product = pivottable.getPivotFields().get("Product");
field_product.setOrientation(PivotFieldOrientation.RowField);

// Category in level 2.
IPivotField field_category = pivottable.getPivotFields().get("Category");
field_category.setOrientation(PivotFieldOrientation.RowField);

IPivotField field_Amount = pivottable.getPivotFields().get(3);
field_Amount.setOrientation(PivotFieldOrientation.DataField);

// Category will be in level 1 and product will be in level 2.
field_product.setPosition(1);
field_category.setPosition(0);

Rename Pivot Table Fields

Sometimes, the pivot table fields are not easily comprehendible and hence can be renamed to meaningful and easily understandable names.

Refer to the following example code to rename the pivot table fields.

// config pivot table's fields
IPivotField field_Date = pivottable.getPivotFields().get("Date");
field_Date.setOrientation(PivotFieldOrientation.PageField);

IPivotField field_Category = pivottable.getPivotFields().get("Category");
field_Category.setOrientation(PivotFieldOrientation.RowField);

IPivotField field_Product = pivottable.getPivotFields().get("Product");
field_Product.setOrientation(PivotFieldOrientation.ColumnField);

IPivotField field_Amount = pivottable.getPivotFields().get("Amount");
field_Amount.setOrientation(PivotFieldOrientation.DataField);
field_Amount.setNumberFormat("$#,##0.00");

IPivotField field_Country = pivottable.getPivotFields().get("Country");
field_Country.setOrientation(PivotFieldOrientation.RowField);

// Renaming DataField "Sum of Amount" to "Amount Total"
pivottable.getDataFields().get(0).setName("Amount Total");

Expand or Collapse Pivot Table Fields

DsExcel provides setShowDetail method in IPivotItem interface which allows you to expand or collapse the outline of pivot table fields. The default value of the method is True which shows the expanded state of pivot table fields. However, it can be set to False to display the collapsed state.

Refer to the following example code to set collapsed state of two pivot table fields.

worksheet.getRange("F1:K16").setValue(sourceData);
worksheet.getRange("F:K").setColumnWidth(15);
IPivotCache pivotcache = workbook.getPivotCaches().create(worksheet.getRange("F1:K16"));
IPivotTable pivottable = worksheet.getPivotTables().add(pivotcache, worksheet.getRange("A1"), "pivottable1");
        
// Config pivot table's fields
IPivotField field_Date = pivottable.getPivotFields().get("Date");
field_Date.setOrientation(PivotFieldOrientation.PageField);

IPivotField field_Category = pivottable.getPivotFields().get("Category");
field_Category.setOrientation(PivotFieldOrientation.ColumnField);

IPivotField field_Country = pivottable.getPivotFields().get("Country");
field_Country.setOrientation(PivotFieldOrientation.RowField);

IPivotField field_Product = pivottable.getPivotFields().get("Product");
field_Product.setOrientation(PivotFieldOrientation.RowField);

IPivotField field_Amount = pivottable.getPivotFields().get("Amount");
field_Amount.setOrientation(PivotFieldOrientation.DataField);
field_Amount.setNumberFormat("$#,##0.00");

// Set not to show Canandian and German details.
field_Country.getPivotItems().get("Canada").setShowDetail(false);
field_Country.getPivotItems().get("Germany").setShowDetail(false);

worksheet.getRange("A:I").getEntireColumn().autoFit();

//save to an excel file
workbook.save("SetShowDetail.xlsx");

Sort Pivot Table Fields

DsExcel supports sorting data fields in a pivot table by using autoSort method and defining ascending or descending as its sort order.

You can also retrieve the name of data field used to sort the specified PivotTable field by using autoSortField method and its sorting order by using autoSortOrder method. The position of an item in its field can also be set or retrieved by using the setPosition or getPosition method of IPivotItem interface.

Refer to the following example code to sort 'Product' field in a pivot table.

// Sort the product items
field_Product.autoSort(SortOrder.Descending);

Modify Pivot Table

In order to modify a pivot table, refer to the following example code.

// Defining source data
Object sourceData = new Object[][] 
{ 
  { "Order ID", "Product", "Category", "Amount", "Date", "Country" },
  { 1, "Carrots", "Vegetables", 4270, new GregorianCalendar(2018, 0, 6),"United States" },
  { 2, "Broccoli", "Vegetables", 8239, new GregorianCalendar(2018, 0, 7),"United Kingdom" },
  { 3, "Banana", "Fruit", 617, new GregorianCalendar(2018, 0, 8),"United States" },
  { 4, "Banana", "Fruit", 8384, new GregorianCalendar(2018, 0, 10),"Canada" },
  { 5, "Beans", "Vegetables", 2626, new GregorianCalendar(2018, 0, 10),"Germany" },
  { 6, "Orange", "Fruit", 3610, new GregorianCalendar(2018, 0, 11),"United States" },
  { 7, "Broccoli", "Vegetables", 9062, new GregorianCalendar(2018, 0, 11),"Australia" },
  { 8, "Banana", "Fruit", 6906, new GregorianCalendar(2018, 0, 16),"New Zealand" },
  { 9, "Apple", "Fruit", 2417, new GregorianCalendar(2018, 0, 16),"France" },
  { 10, "Apple", "Fruit", 7431, new GregorianCalendar(2018, 0, 16),"Canada" },
  { 11, "Banana", "Fruit", 8250, new GregorianCalendar(2018, 0, 16),"Germany" },
  { 12, "Broccoli", "Vegetables", 7012, new GregorianCalendar(2018, 0, 18),"United States" },
  { 13, "Carrots", "Vegetables", 1903, new GregorianCalendar(2018, 0, 20),"Germany" },
  { 14, "Broccoli", "Vegetables", 2824, new GregorianCalendar(2018, 0, 22),"Canada" },
  { 15, "Apple", "Fruit", 6946, new GregorianCalendar(2018, 0, 24),"France" }, 
};
        
// Initialize workbook and fetch the default worksheet
Workbook workbook = new Workbook();
IWorksheet worksheet = workbook.getWorksheets().get(0);
        
// Assigning data to the range
worksheet.getRange("A1:F16").setValue(sourceData);
        
// Creating pivot table and modifying it
IPivotCache pivotcache = workbook.getPivotCaches().create(worksheet.getRange("A1:F16"));
IPivotTable pivottable = worksheet.getPivotTables().add(pivotcache, 
worksheet.getRange("I2"), "pivottable1");
        
worksheet.getRange("D2:D16").setNumberFormat("$#,##0.00");
worksheet.getRange("J4:J17, J9:J33").setNumberFormat("$#,##0.00");

// Configure pivot table's fields
IPivotField field_Product = pivottable.getPivotFields().get(1);
field_Product.setOrientation(PivotFieldOrientation.RowField);

IPivotField field_Category = pivottable.getPivotFields().get(2);
field_Category.setOrientation(PivotFieldOrientation.RowField);

IPivotField field_Amount = pivottable.getPivotFields().get(3);
field_Amount.setOrientation(PivotFieldOrientation.DataField);

// Modify subtotals for pivot field.
field_Category.setSubtotals(EnumSet.of(SubtotalType.Sum, SubtotalType.Count, 
SubtotalType.Average,SubtotalType.Max, SubtotalType.Min, SubtotalType.CountNums, 
SubtotalType.StdDev, SubtotalType.StdDevP,SubtotalType.Var, SubtotalType.VarP));
        
worksheet.getRange("E:E").setColumnWidth(12);
worksheet.getRange("J:J").setColumnWidth(20);