You can modify the settings of the pivot table added in a worksheet by referring to the following tasks:

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

Add field function

In order to add field function in a pivot table, refer to the following example code.

// Change or set data field's summarize function.
field_Amount.setFunction(ConsolidationFunction.Average);

Filter Pivot Table

In order to execute the filter operation on a pivot table, refer to the following example code.

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

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

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

// Apply row field filter.
field_Product.getPivotItems().get("Apple").setVisible(false);
field_Product.getPivotItems().get("Beans").setVisible(false);
field_Product.getPivotItems().get("Orange").setVisible(false);

// Apply page filter.
field_Country.getPivotItems().get("United States").setVisible(false);
field_Country.getPivotItems().get("Canada").setVisible(false);

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

Manage Grand Total Visibility Settings

The Grand total in pivot table helps in analyzing the total sum of the data in the pivot table. You can display or hide the grand total for the row or column field by setting the visibility of ColumnGrand and RowGrand properties of the IPivotTable interface. These properties take boolean values and are set to true by default. For example, if you want to display the grand total only for rows, then set the RowGrand method to true and ColumnGrand to false.

Refer to the following example code to manage the visibility settings of the grand total field.

// Set the PivotTable report to show grand totals for columns & rows
pivottable.setColumnGrand(true);
pivottable.setRowGrand(true);

Change Row Axis Layout

The display of pivot table can be changed to any desired layout using the LayoutRowType enumeration. The following options are provided by this enumeration:

• CompactRow (default layout)
• OutlineRow
• TabularRow

Refer to the following example code to set the row axis layout of the pivot table to TabularRow.

// Set the PivotTable LayoutRowType to Tabular Row
pivottable.setRowAxisLayout(LayoutRowType.TabularRow);

Change Pivot Table Layout

The different layouts of a pivot table makes it more flexible and convenient to analyse its data. DsExcel supports the following pivot table layouts:

• Compact form
• Outline form
• Tabular form

In addition to these, you can also choose to insert blank rows, set the position of subtotals, show all items or to repeat any item in the pivot table layouts.

Refer to the following example code to set the layout of pivot table and additional options.

// Set pivot table layout
field_Category.setLayoutForm(LayoutFormType.Tabular);
field_Category.setLayoutBlankLine(true);

field_Country.setLayoutForm(LayoutFormType.Outline);
field_Country.setLayoutCompactRow(false);

// Set subtotal location
field_Country.setLayoutSubtotalLocation(SubtotalLocationType.Bottom);
field_Country.setShowAllItems(true);

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");

Refresh Pivot Table

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

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

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

// change pivot cache's source data.
worksheet.getRange("D8").setValue(3000);

// sync cache's data to pivot table.
worksheet.getPivotTables().get(0).refresh();

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

Apply Different Calculations on a Pivot Field

In DsExcel, you can add a pivot table field to a pivot table multiple times by applying various calculation functions on it. These functions include sum, average, min, max, count etc. The final pivot table output will contain multiple data fields based on the calculations applied over the pivot table field.

Refer to the following example code to add a pivot table field as multiple data fields by applying different calculation functions.

// 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.RowField);

// Sum function on Amount field
IPivotField field_Amount = pivottable.getPivotFields().get("Amount");
pivottable.addDataField(field_Amount, "sum amount", ConsolidationFunction.Sum);

// Count function on Amount field
IPivotField field_Amount2 = pivottable.getPivotFields().get("Amount");
pivottable.addDataField(field_Amount2, "count amount", ConsolidationFunction.Count);

The output of above example code when viewed in Excel, looks like below:

Calculated Fields

Calculated fields in pivot table refer to the data fields created by applying additional logic or formula on existing data fields of the underlying data source. These fields are especially useful when summary functions and custom calculations do not generate the desired output. For instance, an employee database of a company holds data about existing salary and performance rating of each employee. At year end, one can easily calculate the raised salary of employees by creating calculated field using salary and the rating field.

In DsExcel, the getCalculatedFields method represents the collection of all calculated fields in a particular pivot table. You can use add method of the ICalculatedFields interface to create a new calculated field in the pivot table. The Add method accepts string parameters of field name and formula to generate the calculated field. To remove a calculated field from the collection you can use the remove method which takes target field name as its parameter.

Refer to the following code to create a calculated field in the pivot table:

IWorksheet calculatedFieldSheet = workbook.getWorksheets().add();
calculatedFieldSheet.setName("CalculatedField");

// Add pivot table.
IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:F71"));
IPivotTable calculatedFieldTable = calculatedFieldSheet.getPivotTables().add(pivotCache, calculatedFieldSheet.getRange("A1"));
calculatedFieldTable.getPivotFields().get("Product").setOrientation(PivotFieldOrientation.RowField);
calculatedFieldTable.getPivotFields().get("Amount").setOrientation(PivotFieldOrientation.DataField);

// Add calculated field.
calculatedFieldTable.getCalculatedFields().add("Tax", "=IF(Amount > 1000, 3% * Amount, 0)");

// Set calculated field as data field.
calculatedFieldTable.getPivotFields().get("Tax").setOrientation(PivotFieldOrientation.DataField);

calculatedFieldTable.getDataFields().get("Sum of Amount").setNumberFormat("$#,##0_);($#,##0)");
calculatedFieldTable.getDataFields().get("Sum of Tax").setNumberFormat("$#,##0_);($#,##0)");

Calculated Items

Calculated items are pivot table items that use custom formulas containing constants or refer to other items in the pivot table. These items can be added to the row or column field area of the pivot table but do not exist in the source data.

In DsExcel, ICalculatedItems interface represents the collection of calculated items in a particular pivot table. You can fetch this collection of pivot items by using IPivotField.getCalculatedItems method. To add calculated items to a pivot table, the ICalculatedItems interface provides add method which accepts name and formula of the item as parameters. You can also use IPivotItem.setFormula method for setting the formula of a calculated item. To remove a calculated item from the ICalculatedItems collection, you can use remove method which accepts name of the target field as parameter. Pivot cache manages all the calculated items, hence changing a calculated item affects all pivot tables using same cache in the current workbook. Also, any kind of addition, deletion or change in calculated item triggers the pivot table update.

Refer to the following code to create calculated items in the pivot table:

// Get the calculated item for the specified field
ICalculatedItems countryCalcItems = calculatedItemTable.getPivotFields().get("Country").getCalculatedItems();
ICalculatedItems productCalcItems = calculatedItemTable.getPivotFields().get("Product").getCalculatedItems();

// add some calculated items using formulas
countryCalcItems.add("Oceania", "=Australia+NewZealand");
countryCalcItems.add("America", "=Canada");
IPivotItem myPivotItem = countryCalcItems.add("Europe", "=France");
        
// Change the formula of the calculated item
myPivotItem.setFormula("=France+Germany");
        
// Add calculated item using constant value
productCalcItems.add("IPhone 13", "=2500");
        
// Get the calculatedItems count
System.out.println("Calculated Items count: " + countryCalcItems.getCount());
        
// Remove a calculated item
countryCalcItems.remove("America");

Show Value As

While analyzing spreadsheet data, instead of comparing exact values, you may want to compare the values in terms of calculations. For instance, there are many ways to evaluate performance of a sales employee. You can compare his sales with target, sales as a percentage of total sales or sales in comparison to previous month's sale etc. To achieve these calculations easily, DsExcel provides "Show Value As" option which allows you to perform custom calculations in a pivot table by using several predefined formulas such as “% of Parent Total” or “% of Grand Total”.

DsExcel Java provides setCalculation method of IPivotField interface which accepts values from PivotFieldCalculation enumeration for setting the predefined calculations. You can also set the base field and base field item to perform these calculations using setBaseField and setBaseItem methods respectively.

Refer to the following example code which demonstrates the value as percent of Australia.

IPivotTable percentOfTable = percentOfSheet.getPivotTables().add(pivotCache, percentOfSheet.getRange("A1"));
percentOfTable.getPivotFields().get("Category").setOrientation(PivotFieldOrientation.RowField);
percentOfTable.getPivotFields().get("Product").setOrientation(PivotFieldOrientation.RowField);
percentOfTable.getPivotFields().get("Country").setOrientation(PivotFieldOrientation.ColumnField);
percentOfTable.getPivotFields().get("Amount").setOrientation(PivotFieldOrientation.DataField);

// set show value as, base field, base item.
IPivotField percentOfTableDataField = percentOfTable.getDataFields().get("Sum of Amount");
percentOfTableDataField.setCalculation(PivotFieldCalculation.PercentOf);
percentOfTableDataField.setBaseField("Country");
percentOfTableDataField.setBaseItem("Australia");

percentOfSheet.getRange("A:I").autoFit();

Defer Layout Update

In case of huge amount of data, the performance of a pivot table might get affected while updating its layout by adding or moving fields in the different areas of a pivot table.

DsExcel provides setDeferLayoutUpdate method which improves the performance of a pivot table by deferring its layout updates. When set to true, the pivot table is recalculated only after all the fields are added or moved instead of getting recalculated after each change. You can choose to update the pivot table output after making all the changes by calling the update method.

Refer to the following example code to defer layout updates to a pivot table.

// Defer layout update
pivottable.setDeferLayoutUpdate(true);

// 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);

// Update the pivottable.
pivottable.update();

Use Pivot Table Options

DsExcel supports the following layout and formatting options in a pivot table:

• Merging cells with outer-row item, column item, subtotal and grand total labels
• Indentation of Pivot table items when compact row layout form is set
• Ordering page fields in pivot table layout. It can be either DownThenOver (default value) or OverThenDown.
• Defining number of page fields in each column or row in the pivot table output
• Displaying custom string in cells which contain errors
• Displaying custom string in cells which contain null values

Refer to the following example code to set various layout and format options in a pivot table.

pivottable.setPageFieldOrder(Order.OverThenDown);
pivottable.setPageFieldWrapCount(2);
pivottable.setCompactRowIndent(2);

pivottable.setErrorString("Error");
pivottable.setNullString("Empty");

pivottable.setDisplayErrorString(true);
pivottable.setDisplayNullString(true);

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

Retrieve Pivot Table Ranges

The structure of a pivot table report is comprised of different ranges. In order to retrieve a specific range of pivot table, it is important to understand the structure of a pivot table.

 As can be observed from the above screenshot, the structure of a pivot table can be explained as:

• PivotRowAxis: The row axis area of a pivot table contains fields which group the table's data by rows
• PivotColumnAxis: The column axis area of a pivot table contains fields which break the table's data into different categories by columns.
• Pivot Cell: Any cell in a pivot table
• Row PivotLine: Any row in the row axis area of a pivot table
• Column PivotLine: Any column in the column axis area of a pivot table

DsExcel provides API to retrieve the detailed ranges of a pivot table to apply any operation or style on them to make the result more readable and distinguishable. Detailed pivot table ranges which can be retrieved are:

• Different types of pivot cells like subtotals, grand totals, data fields, pivot fields, values, blank cells
• Different types of pivot lines like subtotal, grand total, regular or blank line
• Entire row or column axis
• Whole page area
• Entire pivot table report including page fields
• A value in any range of pivot table
• The position of any element or pivot line

Refer to the following example code to get a specific range and set its style in a pivot table report.

// Get detail range and set style.
for (IPivotLine item : pivottable.getPivotRowAxis().getPivotLines()) {
    if (item.getLineType() == PivotLineType.Subtotal) {
        item.getPivotLineCells().get(0).getRange().getInterior().setColor(Color.GetGreenYellow());
    }
}

The output of above code example when viewed in Excel, looks like below: 

Get Pivot Table Data

DsExcel Java provides GETPIVOTDATA function which queries the pivot table to fetch data as per the specified parameters. The main advantage of using this function is that it ensures that the correct data is returned, even if the pivot table layout has changed.

Syntax

=GETPIVOTDATA(data_field, pivot_table, [field1, item1, field2, item2],…)

GETPIVOTDATA function can be implemented to return a single cell value or a dynamic array depending on the parameters we are passing. To retrieve a single cell value, name of the data field and pivot table are mandatory parameters. While the third parameter which is a combination of field names and item names, is optional. However, for retrieving a dynamic array, all three parameters are required and the item name supports array like {“Canada”, “US”, “France”) or a range reference like A1:A3. Also, you must use IRange.setFormula2 for GETPIVOTDATA function to return a dynamic array which is spilled across a range. For ease of use, you can also automatically generate GETPIVOTDATA function by using the IRange.generateGetPivotDataFunction method. However, the generateGetPivotDataFunction method returns null when the IRange object is not a single cell.

Refer to the following example code for GETPIVOTDATA function returning a single cell:

IWorksheet worksheet2 = workbook.getWorksheets().add();
worksheet.getRange("H25").setFormula(worksheet.getRange("G6").generateGetPivotDataFunction(worksheet2.getRange("A1")));
worksheet2.getRange("H24").setFormula("=GETPIVOTDATA(\"Amount\",Sheet1!$A$1,\"Category\",\"Mobile\",\"Country\",\"Australia\")");

Refer to the following example code for GETPIVOTDATA function returning a dynamic array:

// Here, Formula2 is used along with GETPIVOTDATA to fetch the multiple values
worksheet.getRange("H10").setFormula2("=GETPIVOTDATA(\"Amount\",$A$1,\"Category\",\"Consumer Electronics\",\"Country\",{\"Canada\",\"Germany\",\"France\"})");

Set Conditional Formatting

Refer to the following example code to set conditional formatting in last row of a pivot table report by setting cell color when the values are above average.

// set condional format to the last row
int rowCount = pivottable.getDataBodyRange().getRowCount();
IAboveAverage averageCondition = pivottable.getDataBodyRange().getRows().get(rowCount - 1).getFormatConditions()
        .addAboveAverage();
averageCondition.setAboveBelow(AboveBelow.AboveAverage);
averageCondition.getInterior().setColor(Color.GetPink());

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

Disable Automatic Grouping of Date/Time Columns

The Date/Time columns in a pivot table are grouped together by default. DsExcel allows you to disable this grouping by setting setAutomaticGroupDateTimeInPivotTable method to false before creating the pivot cache while creating a pivot table.

When AutomaticGroupDateTimeInPivotTable = False	When AutomaticGroupDateTimeInPivotTable = True (default)

// Set false to group date/time fields in PivotTable automatically
workbook.getOptions().getData().setAutomaticGroupDateTimeInPivotTable(false);

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");