Method Details

• getAllItemsVisible

  boolean getAllItemsVisible()

  Gets whether all items in the PivotField are visible.

  Returns false when manual filtering has been applied by hiding one or more items. Use clearManualFilter() to restore visibility to all items.

  
   worksheet.getRange("A1:B4").setValue(new Object[][] {
       {"Product", "Amount"},
       {"Example Speaker", 4270},
       {"Example Camera", 8239},
       {"Example Appliance", 617}
   });
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
   IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "PivotTable1");
   IPivotField productField = pivotTable.getPivotFields().get("Product");
   productField.setOrientation(PivotFieldOrientation.RowField);
   productField.getPivotItems().get("Example Camera").setVisible(false);
   boolean allItemsVisible = productField.getAllItemsVisible();
   

  Returns:

  true if no manual filtering is applied and all items in the field are visible; otherwise, false.

• getAutoSortField

  String getAutoSortField()

  Gets the name of the data field used to sort this PivotTable field automatically.

  If automatic sorting is configured by autoSort(SortOrder,String), this method returns the sort key field name that was specified for that rule.

  
   worksheet.getRange("A1:C5").setValue(new Object[][] {
       {"Product", "Region", "Amount"},
       {"Apple", "East", 120},
       {"Orange", "West", 80},
       {"Apple", "West", 150},
       {"Orange", "East", 90}
   });
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:C5"));
   IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("E1"), "PivotTable1");
   IPivotField regionField = pivotTable.getPivotFields().get("Region");
   regionField.setOrientation(PivotFieldOrientation.RowField);
   IPivotField amountField = pivotTable.getPivotFields().get("Amount");
   pivotTable.addDataField(amountField, "Sum of Amount", ConsolidationFunction.Sum);
   regionField.autoSort(SortOrder.Descending, amountField.getSourceName());
   String autoSortField = regionField.getAutoSortField();
   

  Returns:

  The name of the data field used for automatic sorting.

• getAutoSortOrder

  SortOrder getAutoSortOrder()

  Gets the automatic sort order of this PivotTable field.

  If automatic sorting is configured by autoSort(SortOrder) or autoSort(SortOrder,String), this method returns the SortOrder applied to the field.

  
   worksheet.getRange("A1:C5").setValue(new Object[][] {
       {"Product", "Region", "Amount"},
       {"Apple", "East", 120},
       {"Orange", "West", 80},
       {"Apple", "West", 150},
       {"Orange", "East", 90}
   });
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:C5"));
   IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("E1"), "PivotTable1");
   IPivotField regionField = pivotTable.getPivotFields().get("Region");
   regionField.setOrientation(PivotFieldOrientation.RowField);
   regionField.autoSort(SortOrder.Descending);
   SortOrder autoSortOrder = regionField.getAutoSortOrder();
   

  Returns:

  The automatic sort order of the PivotTable field.

• getBaseField

  String getBaseField()

  Gets the name of the base field used for a custom calculation.

  This property is valid only for data fields. Use this property together with getCalculation() when the data field is configured to show values by a custom calculation such as PivotFieldCalculation.PercentOf.

  
   Object[][] sourceData = {
       {"Product", "Country", "Amount"},
       {"Bike", "Australia", 100},
       {"Bike", "Canada", 80}
   };
   worksheet.getRange("A1:C3").setValue(sourceData);
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:C3"));
   IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("E1"));
   pivotTable.getPivotFields().get("Product").setOrientation(PivotFieldOrientation.RowField);
   pivotTable.getPivotFields().get("Country").setOrientation(PivotFieldOrientation.ColumnField);
   pivotTable.getPivotFields().get("Amount").setOrientation(PivotFieldOrientation.DataField);
   IPivotField dataField = pivotTable.getDataFields().get("Sum of Amount");
   dataField.setCalculation(PivotFieldCalculation.PercentOf);
   dataField.setBaseField("Country");
   String baseField = dataField.getBaseField();
   

  Returns:

  The name of the base field used for the custom calculation.

• setBaseField

  void setBaseField(String value)

  Sets the name of the base field used for a custom calculation.

  This property is valid only for data fields. Use this property together with getCalculation() when the data field is configured to show values by a custom calculation such as PivotFieldCalculation.PercentOf.

  
   Object[][] sourceData = {
       {"Product", "Country", "Amount"},
       {"Bike", "Australia", 100},
       {"Bike", "Canada", 80}
   };
   worksheet.getRange("A1:C3").setValue(sourceData);
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:C3"));
   IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("E1"));
   pivotTable.getPivotFields().get("Product").setOrientation(PivotFieldOrientation.RowField);
   pivotTable.getPivotFields().get("Country").setOrientation(PivotFieldOrientation.ColumnField);
   pivotTable.getPivotFields().get("Amount").setOrientation(PivotFieldOrientation.DataField);
   IPivotField dataField = pivotTable.getDataFields().get("Sum of Amount");
   dataField.setCalculation(PivotFieldCalculation.PercentOf);
   dataField.setBaseField("Country");
   

  Parameters:

  value - The name of the base field used for the custom calculation.

• getBaseItem

  String getBaseItem()

  Gets the item in the base field for a custom calculation.

  This property is valid only for data fields. Use this property together with getBaseField() and getCalculation() to determine which item in the base field is used by a custom calculation such as PivotFieldCalculation.PercentOf.

  
   Object[][] sourceData = {
       {"Product", "Country", "Amount"},
       {"Bike", "Australia", 100},
       {"Bike", "Canada", 80}
   };
   worksheet.getRange("A1:C3").setValue(sourceData);
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:C3"));
   IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("E1"));
   pivotTable.getPivotFields().get("Product").setOrientation(PivotFieldOrientation.RowField);
   pivotTable.getPivotFields().get("Country").setOrientation(PivotFieldOrientation.ColumnField);
   pivotTable.getPivotFields().get("Amount").setOrientation(PivotFieldOrientation.DataField);
   IPivotField dataField = pivotTable.getDataFields().get("Sum of Amount");
   dataField.setCalculation(PivotFieldCalculation.PercentOf);
   dataField.setBaseField("Country");
   dataField.setBaseItem("Australia");
   String baseItem = dataField.getBaseItem();
   

  Returns:

  The item name in the base field that is used for the custom calculation.

• setBaseItem

  void setBaseItem(String value)

  Sets the item in the base field for a custom calculation.

  This property is valid only for data fields. Use this property together with getBaseField() and getCalculation() to determine which item in the base field is used by a custom calculation such as PivotFieldCalculation.PercentOf.

  
   Object[][] sourceData = {
       {"Product", "Country", "Amount"},
       {"Bike", "Australia", 100},
       {"Bike", "Canada", 80}
   };
   worksheet.getRange("A1:C3").setValue(sourceData);
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:C3"));
   IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("E1"));
   pivotTable.getPivotFields().get("Product").setOrientation(PivotFieldOrientation.RowField);
   pivotTable.getPivotFields().get("Country").setOrientation(PivotFieldOrientation.ColumnField);
   pivotTable.getPivotFields().get("Amount").setOrientation(PivotFieldOrientation.DataField);
   IPivotField dataField = pivotTable.getDataFields().get("Sum of Amount");
   dataField.setCalculation(PivotFieldCalculation.PercentOf);
   dataField.setBaseField("Country");
   dataField.setBaseItem("Australia");
   

  Parameters:

  value - The item name in the base field that is used for the custom calculation.

• getCalculation

  PivotFieldCalculation getCalculation()

  Gets a PivotFieldCalculation value that represents the type of calculation performed by the specified field.

  This property is valid only for data fields.

  
   Object[][] sourceData = {
       {"Product", "Country", "Amount"},
       {"Bike", "Australia", 100},
       {"Bike", "Canada", 80}
   };
   worksheet.getRange("A1:C3").setValue(sourceData);
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:C3"));
   IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("E1"));
   pivotTable.getPivotFields().get("Product").setOrientation(PivotFieldOrientation.RowField);
   pivotTable.getPivotFields().get("Country").setOrientation(PivotFieldOrientation.ColumnField);
   pivotTable.getPivotFields().get("Amount").setOrientation(PivotFieldOrientation.DataField);
   IPivotField dataField = pivotTable.getDataFields().get("Sum of Amount");
   dataField.setCalculation(PivotFieldCalculation.PercentOf);
   PivotFieldCalculation calculation = dataField.getCalculation();
   

  Returns:

  A PivotFieldCalculation value that indicates the type of calculation performed by the field.

• setCalculation

  void setCalculation(PivotFieldCalculation value)

  Sets a PivotFieldCalculation value that specifies how the data field is calculated in the PivotTable.

  This property is valid only for data fields. Use it to change the data field from the default calculation to a custom calculation such as PivotFieldCalculation.PercentOf.

  
   Object[][] sourceData = {
       {"Product", "Country", "Amount"},
       {"Bike", "Australia", 100},
       {"Bike", "Canada", 80}
   };
   worksheet.getRange("A1:C3").setValue(sourceData);
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:C3"));
   IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("E1"));
   pivotTable.getPivotFields().get("Product").setOrientation(PivotFieldOrientation.RowField);
   pivotTable.getPivotFields().get("Country").setOrientation(PivotFieldOrientation.ColumnField);
   pivotTable.getPivotFields().get("Amount").setOrientation(PivotFieldOrientation.DataField);
   IPivotField dataField = pivotTable.getDataFields().get("Sum of Amount");
   dataField.setCalculation(PivotFieldCalculation.PercentOf);
   

  Parameters:

  value - The PivotFieldCalculation value that specifies the calculation type for the data field.

• getCurrentPage

  int getCurrentPage()

  Gets the index of the current page item displayed for the page field.

  This method is valid only for page fields. The returned index is zero-based. A value of -1 indicates that the page field is showing All or multiple items.

  
   worksheet.getRange("A1:B4").setValue(new Object[][] {
       {"Region", "Amount"},
       {"East", 100},
       {"West", 200},
       {"East", 150}
   });
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
   IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "SalesPivot");
   IPivotField pageField = pivotTable.getPivotFields().get("Region");
   pageField.setOrientation(PivotFieldOrientation.PageField);
   int currentPage = pageField.getCurrentPage();
   

  Returns:

  The zero-based index of the current page item for the page field, or -1 if the page field is showing All or multiple items.

• setCurrentPage

  void setCurrentPage(int value)

  Sets the index of the current page item displayed for the page field.

  This method is valid only for page fields. The returned index is zero-based. A value of -1 indicates that the page field is showing All or multiple items.

  
   worksheet.getRange("A1:B4").setValue(new Object[][] {
       {"Region", "Amount"},
       {"East", 100},
       {"West", 200},
       {"East", 150}
   });
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
   IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "SalesPivot");
   IPivotField pageField = pivotTable.getPivotFields().get("Region");
   pageField.setOrientation(PivotFieldOrientation.PageField);
   pageField.setCurrentPage(0);
   

  Parameters:

  value - The zero-based index of the current page item for the page field, or -1 if the page field is showing All or multiple items.

• getDragToColumn

  boolean getDragToColumn()

  Gets whether the specified field can be dragged to the column position.

  This property indicates whether the field can be moved into the column area of a PivotTable report. The default value is true. Use setDragToColumn(boolean) to change this setting.

  
   worksheet.getRange("A1:C4").setValue(new Object[][] {
       {"Region", "Product", "Amount"},
       {"East", "Apple", 100},
       {"West", "Pear", 200},
       {"East", "Pear", 150}
   });
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:C4"));
   IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("E1"), "SalesPivot");
   IPivotField field = pivotTable.getPivotFields().get("Product");
   field.setDragToColumn(false);
   boolean canDragToColumn = field.getDragToColumn();
   

  Returns:

  true if the field can be dragged to the column position; otherwise, false.

• setDragToColumn

  void setDragToColumn(boolean value)

  Sets whether the specified field can be dragged to the column position.

  This property indicates whether the field can be moved into the column area of a PivotTable report. The default value is true. Use this method to change this setting.

  
   worksheet.getRange("A1:C4").setValue(new Object[][] {
       {"Region", "Product", "Amount"},
       {"East", "Apple", 100},
       {"West", "Pear", 200},
       {"East", "Pear", 150}
   });
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:C4"));
   IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("E1"), "SalesPivot");
   IPivotField field = pivotTable.getPivotFields().get("Product");
   field.setDragToColumn(false);
   

  Parameters:

  value - true if the field can be dragged to the column position; otherwise, false.

• getDragToData

  boolean getDragToData()

  Gets whether the specified field can be dragged to the data position.

  This property indicates whether the field can be moved into the data area of a PivotTable report. The default value is true. Use setDragToData(boolean) to change this setting.

  
   worksheet.getRange("A1:C4").setValue(new Object[][] {
       {"Region", "Product", "Amount"},
       {"East", "Apple", 100},
       {"West", "Pear", 200},
       {"East", "Pear", 150}
   });
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:C4"));
   IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("E1"), "SalesPivot");
   IPivotField field = pivotTable.getPivotFields().get("Amount");
   field.setDragToData(false);
   boolean canDragToData = field.getDragToData();
   

  Returns:

  true if the field can be dragged to the data position; otherwise, false.

• setDragToData

  void setDragToData(boolean value)

  Sets whether the specified field can be dragged to the data position.

  This property indicates whether the field can be moved into the data area of a PivotTable report. The default value is true. Use this method to change this setting.

  
   worksheet.getRange("A1:C4").setValue(new Object[][] {
       {"Region", "Product", "Amount"},
       {"East", "Apple", 100},
       {"West", "Pear", 200},
       {"East", "Pear", 150}
   });
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:C4"));
   IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("E1"), "SalesPivot");
   IPivotField field = pivotTable.getPivotFields().get("Amount");
   field.setDragToData(false);
   

  Parameters:

  value - true if the field can be dragged to the data position; otherwise, false.

• getDragToHide

  boolean getDragToHide()

  Gets whether the field can be hidden by being dragged off the PivotTable report.

  This property indicates whether users can remove the field from the PivotTable report by dragging it away. The default value is true. Use setDragToHide(boolean) to change this setting.

  
   worksheet.getRange("A1:C4").setValue(new Object[][] {
       {"Region", "Product", "Amount"},
       {"East", "Apple", 100},
       {"West", "Pear", 200},
       {"East", "Pear", 150}
   });
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:C4"));
   IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("E1"), "SalesPivot");
   IPivotField field = pivotTable.getPivotFields().get("Product");
   field.setDragToHide(false);
   boolean canDragToHide = field.getDragToHide();
   

  Returns:

  true if the field can be hidden by being dragged off the PivotTable report; otherwise, false.

• setDragToHide

  void setDragToHide(boolean value)

  Sets whether the field can be hidden by being dragged off the PivotTable report.

  This property indicates whether users can remove the field from the PivotTable report by dragging it away. The default value is true. Use this method to change this setting.

  
   worksheet.getRange("A1:C4").setValue(new Object[][] {
       {"Region", "Product", "Amount"},
       {"East", "Apple", 100},
       {"West", "Pear", 200},
       {"East", "Pear", 150}
   });
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:C4"));
   IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("E1"), "SalesPivot");
   IPivotField field = pivotTable.getPivotFields().get("Product");
   field.setDragToHide(false);
   

  Parameters:

  value - true if the field can be hidden by being dragged off the PivotTable report; otherwise, false.

• getDragToPage

  boolean getDragToPage()

  Gets whether the field can be dragged to the page position.

  The default value is true.

  
   worksheet.getRange("A1:C4").setValue(new Object[][] {
       {"Region", "Product", "Amount"},
       {"East", "Apple", 100},
       {"West", "Pear", 200},
       {"East", "Pear", 150}
   });
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:C4"));
   IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("E1"), "SalesPivot");
   IPivotField field = pivotTable.getPivotFields().get("Region");
   field.setDragToPage(false);
   boolean canDragToPage = field.getDragToPage();
   

  Returns:

  true if the field can be dragged to the page position; otherwise, false.

• setDragToPage

  void setDragToPage(boolean value)

  Sets whether the field can be dragged to the page position.

  The default value is true.

  
   worksheet.getRange("A1:C4").setValue(new Object[][] {
       {"Region", "Product", "Amount"},
       {"East", "Apple", 100},
       {"West", "Pear", 200},
       {"East", "Pear", 150}
   });
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:C4"));
   IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("E1"), "SalesPivot");
   IPivotField field = pivotTable.getPivotFields().get("Region");
   field.setDragToPage(false);
   

  Parameters:

  value - true if the field can be dragged to the page position; otherwise, false.

• getDragToRow

  boolean getDragToRow()

  Gets whether the field can be dragged to the row position.

  This property determines whether the field is allowed to be moved to the row area in a PivotTable. The default value is true.

  
   worksheet.getRange("A1:C4").setValue(new Object[][] {
       {"Region", "Product", "Amount"},
       {"East", "Apple", 100},
       {"West", "Pear", 200},
       {"East", "Pear", 150}
   });
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:C4"));
   IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("E1"), "SalesPivot");
   IPivotField field = pivotTable.getPivotFields().get("Region");
   boolean canDragToRow = field.getDragToRow();
   

  Returns:

  true if the field can be dragged to the row position; otherwise, false.

• setDragToRow

  void setDragToRow(boolean value)

  Sets whether the field can be dragged to the row position.

  This property determines whether the field is allowed to be moved to the row area in a PivotTable. The default value is true.

  
   worksheet.getRange("A1:C4").setValue(new Object[][] {
       {"Region", "Product", "Amount"},
       {"East", "Apple", 100},
       {"West", "Pear", 200},
       {"East", "Pear", 150}
   });
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:C4"));
   IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("E1"), "SalesPivot");
   IPivotField field = pivotTable.getPivotFields().get("Region");
   field.setDragToRow(true);
   

  Parameters:

  value - true if the field can be dragged to the row position; otherwise, false.

• getEnableMultiplePageItems

  boolean getEnableMultiplePageItems()

  Gets whether the field can have multiple items selected in the page field.

  This property applies to PivotTable fields that are placed in the page area. The default value is false.

  
   worksheet.getRange("A1:B4").setValue(new Object[][] {
       {"Date", "Amount"},
       {"2024-01", 120},
       {"2024-02", 150},
       {"2024-03", 90}
   });
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
   IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "SalesPivot");
   IPivotField pageField = pivotTable.getPivotFields().get("Date");
   pageField.setOrientation(PivotFieldOrientation.PageField);
   boolean allowMultipleItems = pageField.getEnableMultiplePageItems();
   

  Returns:

  true if the field can have multiple items selected in the page field; otherwise, false.

• setEnableMultiplePageItems

  void setEnableMultiplePageItems(boolean value)

  Sets whether the field can have multiple items selected in the page field.

  This property applies to PivotTable fields that are placed in the page area. The default value is false.

  
   worksheet.getRange("A1:B4").setValue(new Object[][] {
       {"Date", "Amount"},
       {"2024-01", 120},
       {"2024-02", 150},
       {"2024-03", 90}
   });
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
   IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "SalesPivot");
   IPivotField pageField = pivotTable.getPivotFields().get("Date");
   pageField.setOrientation(PivotFieldOrientation.PageField);
   pageField.setEnableMultiplePageItems(true);
   

  Parameters:

  value - true if the field can have multiple items selected in the page field; otherwise, false.

• getFormula

  String getFormula()

  Gets the formula of the PivotTable field in A1-style notation and in the language of the macro.

  This property returns the formula text associated with the field.

  
   worksheet.getRange("A1:B4").setValue(new Object[][] {
       {"Product", "Amount"},
       {"Apple", 100},
       {"Banana", 200},
       {"Orange", 150}
   });
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
   IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "SalesPivot");
   IPivotField field = pivotTable.getCalculatedFields().add("TotalAmount", "=Amount*2");
   String formula = field.getFormula();
   

  Returns:

  The formula string in A1-style notation and in the language of the macro. Returns null if no formula is associated with the field.

• setFormula

  void setFormula(String value)

  Sets the formula of the PivotTable field in A1-style notation and in the language of the macro.

  This property Sets the formula text associated with the field.

  
   worksheet.getRange("A1:B4").setValue(new Object[][] {
       {"Product", "Amount"},
       {"Apple", 100},
       {"Banana", 200},
       {"Orange", 150}
   });
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
   IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "SalesPivot");
   IPivotField field = pivotTable.getCalculatedFields().add("TotalAmount", "=Amount*2");
   field.setFormula("=Amount*3");
   

  Parameters:

  value - The formula string in A1-style notation and in the language of the macro. Set null if no formula is associated with the field.

• getFunction

  ConsolidationFunction getFunction()

  Gets the summary function used by this PivotTable field.

  This property applies to data fields only. Use it to determine whether the field summarizes source values by functions such as sum, count, or average. See setFunction(ConsolidationFunction) to change the summary function.

  
   worksheet.getRange("A1:B4").setValue(new Object[][] {
       {"Category", "Amount"},
       {"Fruit", 10},
       {"Fruit", 20},
       {"Drink", 5}
   });
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
   IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "SalesPivot");
   IPivotField dataField = pivotTable.addDataField(pivotTable.getPivotFields().get("Amount"), "Sum of Amount", ConsolidationFunction.Sum);
   ConsolidationFunction function = dataField.getFunction();
   

  Returns:

  The ConsolidationFunction used to summarize the PivotTable field.

• setFunction

  void setFunction(ConsolidationFunction value)

  Sets the function used to summarize the PivotTable field.

  This method applies to data fields only. Use a ConsolidationFunction value such as ConsolidationFunction.Sum, ConsolidationFunction.Count, or ConsolidationFunction.Average to control how the field values are aggregated.

  
   worksheet.getRange("A1:B4").setValue(new Object[][] {
       {"Category", "Amount"},
       {"Fruit", 120},
       {"Fruit", 80},
       {"Drink", 50}
   });
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
   IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "SalesPivot");
   pivotTable.getPivotFields().get("Amount").setOrientation(PivotFieldOrientation.DataField);
   pivotTable.getDataFields().get("Sum of Amount").setFunction(ConsolidationFunction.Average);
   

  Parameters:

  value - The consolidation function used to summarize the PivotTable data field.

• getLayoutBlankLine

  boolean getLayoutBlankLine()

  Gets whether a blank row is inserted after this row field in a PivotTable report.

  This property applies to PivotFields placed in the row area of a PivotTable. The default value is false.

  
   worksheet.getRange("A1:B4").setValue(new Object[][] {
       {"Product", "Amount"},
       {"Apple", 100},
       {"Apple", 200},
       {"Banana", 150}
   });
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
   IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"));
   IPivotField field = pivotTable.getPivotFields().get("Product");
   field.setOrientation(PivotFieldOrientation.RowField);
   field.setLayoutBlankLine(true);
   boolean blankLine = field.getLayoutBlankLine();
   

  Returns:

  true if a blank row is inserted after this row field in the PivotTable report; otherwise, false.

• setLayoutBlankLine

  void setLayoutBlankLine(boolean value)

  Sets whether a blank row is inserted after this row field in a PivotTable report.

  This property applies to PivotFields placed in the row area of a PivotTable. The default value is false.

  
   worksheet.getRange("A1:B4").setValue(new Object[][] {
       {"Product", "Amount"},
       {"Apple", 100},
       {"Apple", 200},
       {"Banana", 150}
   });
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
   IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"));
   IPivotField field = pivotTable.getPivotFields().get("Product");
   field.setOrientation(PivotFieldOrientation.RowField);
   field.setLayoutBlankLine(true);
   

  Parameters:

  value - true if a blank row is inserted after this row field in the PivotTable report; otherwise, false.

• getLayoutCompactRow

  boolean getLayoutCompactRow()

  Gets whether this PivotField uses compact row layout when it is placed in the row area.

  When this property is true, items from multiple row PivotFields can be displayed in a single column.

  
   worksheet.getRange("A1:C5").setValue(new Object[][] {
       {"Category", "Product", "Amount"},
       {"Fruit", "Apple", 100},
       {"Fruit", "Orange", 80},
       {"Vegetables", "Carrot", 60},
       {"Vegetables", "Celery", 40}
   });
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:C5"));
   IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("E1"));
   pivotTable.getPivotFields().get("Category").setOrientation(PivotFieldOrientation.RowField);
   IPivotField field = pivotTable.getPivotFields().get("Product");
   field.setOrientation(PivotFieldOrientation.RowField);
   field.setLayoutCompactRow(true);
   boolean compactRow = field.getLayoutCompactRow();
   

  Returns:

  true if the PivotField is displayed in compact row layout when rows are selected; otherwise, false.

• setLayoutCompactRow

  void setLayoutCompactRow(boolean value)

  Sets whether this PivotField uses compact row layout when it is placed in the row area.

  When this property is true, items from multiple row PivotFields can be displayed in a single column.

  
   worksheet.getRange("A1:C5").setValue(new Object[][] {
       {"Category", "Product", "Amount"},
       {"Fruit", "Apple", 100},
       {"Fruit", "Orange", 80},
       {"Vegetables", "Carrot", 60},
       {"Vegetables", "Celery", 40}
   });
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:C5"));
   IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("E1"));
   pivotTable.getPivotFields().get("Category").setOrientation(PivotFieldOrientation.RowField);
   IPivotField field = pivotTable.getPivotFields().get("Product");
   field.setOrientation(PivotFieldOrientation.RowField);
   field.setLayoutCompactRow(true);
   

  Parameters:

  value - true if the PivotField is displayed in compact row layout when rows are selected; otherwise, false.

• getLayoutForm

  LayoutFormType getLayoutForm()

  Gets the way the specified PivotTable items appear.

  Use this property to determine whether the field is displayed in table format or outline format.

  
   worksheet.getRange("A1:B4").setValue(new Object[][] {
       {"Category", "Amount"},
       {"Beverages", 100},
       {"Beverages", 200},
       {"Snacks", 150}
   });
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
   IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "SalesPivot");
   IPivotField field = pivotTable.getPivotFields().get("Category");
   field.setOrientation(PivotFieldOrientation.RowField);
   field.setLayoutForm(LayoutFormType.Outline);
   LayoutFormType layoutForm = field.getLayoutForm();
   

  Returns:

  The layout form of the PivotTable field, such as LayoutFormType.Tabular or LayoutFormType.Outline.

• setLayoutForm

  void setLayoutForm(LayoutFormType value)

  Sets the way the specified PivotTable items appear, in table format or in outline format.

  Use LayoutFormType.Tabular to display the field in tabular form or LayoutFormType.Outline to display it in outline form.

  
   worksheet.getRange("A1:B4").setValue(new Object[][] {
       {"Category", "Amount"},
       {"Fruit", 120},
       {"Fruit", 80},
       {"Vegetables", 95}
   });
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
   IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "SalesPivot");
   IPivotField field = pivotTable.getPivotFields().get("Category");
   field.setOrientation(PivotFieldOrientation.RowField);
   field.setLayoutForm(LayoutFormType.Outline);
   

  Parameters:

  value - The layout form to apply to the PivotTable field, such as LayoutFormType.Tabular or LayoutFormType.Outline.

• getLayoutSubtotalLocation

  SubtotalLocationType getLayoutSubtotalLocation()

  Gets the position of the PivotTable field subtotals in relation to the specified field.

  Use this method to determine whether subtotals for the field are displayed above or below the field items.

  
   worksheet.getRange("A1:B4").setValue(new Object[][] {
       {"Category", "Amount"},
       {"Beverages", 100},
       {"Beverages", 200},
       {"Snacks", 150}
   });
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
   IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "SalesPivot");
   IPivotField field = pivotTable.getPivotFields().get("Category");
   field.setOrientation(PivotFieldOrientation.RowField);
   field.setLayoutSubtotalLocation(SubtotalLocationType.Bottom);
   SubtotalLocationType location = field.getLayoutSubtotalLocation();
   

  Returns:

  A SubtotalLocationType value that indicates whether subtotals are displayed above or below the specified field.

• setLayoutSubtotalLocation

  void setLayoutSubtotalLocation(SubtotalLocationType value)

  Sets where subtotals are displayed for this PivotTable field.

  Use SubtotalLocationType.Top to display subtotals above the field items, or SubtotalLocationType.Bottom to display them below the field items.

  
   worksheet.getRange("A1:B4").setValue(new Object[][] {
       {"Category", "Amount"},
       {"Beverages", 100},
       {"Beverages", 200},
       {"Snacks", 150}
   });
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
   IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "SalesPivot");
   IPivotField field = pivotTable.getPivotFields().get("Category");
   field.setOrientation(PivotFieldOrientation.RowField);
   field.setLayoutSubtotalLocation(SubtotalLocationType.Bottom);
   

  Parameters:

  value - The subtotal location for the field.

• getName

  String getName()

  Gets the name of the object.

  Use this method to retrieve the current name of a field in a PivotTable report.

  
   worksheet.getRange("A1:B4").setValue(new Object[][] {
       {"Category", "Amount"},
       {"Fruit", 120},
       {"Drink", 50},
       {"Fruit", 80}
   });
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
   IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "SalesPivot");
   IPivotField field = pivotTable.getPivotFields().get("Category");
   String name = field.getName();
   

  Returns:

  The name of the object.

• setName

  void setName(String value)

  Sets the name of the object.

  Use this method to set the name of a field in a PivotTable report.

  
   worksheet.getRange("A1:B4").setValue(new Object[][] {
       {"Category", "Amount"},
       {"Fruit", 120},
       {"Drink", 50},
       {"Fruit", 80}
   });
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
   IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "SalesPivot");
   IPivotField field = pivotTable.getPivotFields().get("Category");
   field.setName("Product Category");
   

  Parameters:

  value - The name of the object.

• getNumberFormat

  String getNumberFormat()

  Gets the number format code for the pivot field.

  The returned string corresponds to the format code assigned by setNumberFormat(String) and is used to display the field's values in the PivotTable report.

  
   Object[][] sourceData = new Object[][] {
       {"Category", "Amount"},
       {"Fruit", 1200},
       {"Fruit", 800},
       {"Vegetables", 600}
   };
   worksheet.getRange("A1:B4").setValue(sourceData);
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
   IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "SalesPivot");
   IPivotField amountField = pivotTable.getPivotFields().get("Amount");
   amountField.setOrientation(PivotFieldOrientation.DataField);
   amountField.setNumberFormat("$#,##0.00");
   String numberFormat = amountField.getNumberFormat();
   

  Returns:

  The number format code for the pivot field.

• setNumberFormat

  void setNumberFormat(String value)

  Sets the number format code for the pivot field.

  
   Object[][] sourceData = new Object[][] {
       {"Category", "Amount"},
       {"Fruit", 1200},
       {"Fruit", 800},
       {"Vegetables", 600}
   };
   worksheet.getRange("A1:B4").setValue(sourceData);
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
   IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "SalesPivot");
   IPivotField amountField = pivotTable.getPivotFields().get("Amount");
   amountField.setOrientation(PivotFieldOrientation.DataField);
   amountField.setNumberFormat("$#,##0.00");
   

  Parameters:

  value - The number format code for the pivot field.

• getOrientation

  PivotFieldOrientation getOrientation()

  Gets the location of the field in the specified PivotTable report.

  Use this method to determine whether the field is currently placed in the row, column, page, data, or hidden area of the PivotTable.

  
   Object[][] sourceData = new Object[][] {
       {"Product", "Category", "Amount"},
       {"Apple", "Fruit", 100},
       {"Pear", "Fruit", 200}
   };
   worksheet.getRange("A1:C3").setValue(sourceData);
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:C3"));
   IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("E1"), "pivotTable1");
   IPivotField field = pivotTable.getPivotFields().get("Category");
   field.setOrientation(PivotFieldOrientation.RowField);
   PivotFieldOrientation orientation = field.getOrientation();
   

  Returns:

  The PivotFieldOrientation value that indicates the field location in the PivotTable report.

• setOrientation

  void setOrientation(PivotFieldOrientation value)

  Sets the location of the field in the specified PivotTable report.

  Use this method to place the field in a row, column, page, or data area of a PivotTable.

  
   Object[][] sourceData = {
       {"Category", "Product", "Amount"},
       {"Fruit", "Apple", 100},
       {"Fruit", "Orange", 200}
   };
   worksheet.getRange("A1:C3").setValue(sourceData);
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:C3"));
   IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("E1"), "PivotTable1");
   IPivotField pivotField = pivotTable.getPivotFields().get("Category");
   pivotField.setOrientation(PivotFieldOrientation.RowField);
   

  Parameters:

  value - The PivotFieldOrientation that specifies where the field is placed in the PivotTable report.

• getPosition

  int getPosition()

  Gets the position of the field among the fields in its current orientation.

  The position determines the order of the field within its orientation area, such as row fields, column fields, page fields, or data fields.

  
   worksheet.getRange("A1:C4").setValue(new Object[][] {
       {"Category", "Product", "Amount"},
       {"Fruit", "Apple", 100},
       {"Fruit", "Orange", 80},
       {"Snacks", "Cookie", 60}
   });
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:C4"));
   IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("E1"), "SalesPivot");
   IPivotField field = pivotTable.getPivotFields().get("Category");
   field.setOrientation(PivotFieldOrientation.RowField);
   field.setPosition(0);
   int position = field.getPosition();
   

  Returns:

  The zero-based position of the field within its current orientation.

• setPosition

  void setPosition(int value)

  Sets the position of the field among the fields in its current orientation.

  The position determines the order of the field within its orientation area, such as row fields, column fields, page fields, or data fields.

  
   worksheet.getRange("A1:C4").setValue(new Object[][] {
       {"Category", "Product", "Amount"},
       {"Fruit", "Apple", 100},
       {"Fruit", "Orange", 80},
       {"Snacks", "Cookie", 60}
   });
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:C4"));
   IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("E1"), "SalesPivot");
   IPivotField field = pivotTable.getPivotFields().get("Category");
   field.setOrientation(PivotFieldOrientation.RowField);
   field.setPosition(0);
   

  Parameters:

  value - The zero-based position of the field within its current orientation.

• getRepeatLabels

  boolean getRepeatLabels()

  Gets whether item labels are repeated in the PivotTable for the specified IPivotField.

  Use this property to determine whether repeated item labels are shown for the field in the PivotTable layout.

  
   worksheet.getRange("A1:B4").setValue(new Object[][] {
       {"Category", "Product"},
       {"Fruit", "Apple"},
       {"Fruit", "Orange"},
       {"Drink", "Tea"}
   });
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
   IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"));
   IPivotField field = pivotTable.getPivotFields().get("Category");
   field.setOrientation(PivotFieldOrientation.RowField);
   field.setRepeatLabels(true);
   boolean repeatLabels = field.getRepeatLabels();
   

  Returns:

  true if item labels are repeated; otherwise, false.

• setRepeatLabels

  void setRepeatLabels(boolean value)

  Sets whether item labels are repeated in the PivotTable for the specified IPivotField.

  Use this property to set whether repeated item labels are shown for the field in the PivotTable layout.

  
   worksheet.getRange("A1:B4").setValue(new Object[][] {
       {"Category", "Product"},
       {"Fruit", "Apple"},
       {"Fruit", "Orange"},
       {"Drink", "Tea"}
   });
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
   IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"));
   IPivotField field = pivotTable.getPivotFields().get("Category");
   field.setOrientation(PivotFieldOrientation.RowField);
   field.setRepeatLabels(true);
   

  Parameters:

  value - true if item labels are repeated; otherwise, false.

• getShowAllItems

  boolean getShowAllItems()

  Gets whether all items in the PivotTable report are displayed, even if they do not contain summary data.

  The default value is false. When this property is true, items without summarized values are still shown for the PivotField.

  
   worksheet.getRange("A1:B5").setValue(new Object[][] {
       {"Category", "Amount"},
       {"Fruit", 120},
       {"Fruit", 80},
       {"Vegetables", 95},
       {"Vegetables", 60}
   });
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B5"));
   IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "SalesPivot");
   IPivotField field = pivotTable.getPivotFields().get("Category");
   field.setShowAllItems(true);
   boolean showAllItems = field.getShowAllItems();
   

  Returns:

  true if all items in the PivotTable report are displayed, even when they do not contain summary data; otherwise, false.

• setShowAllItems

  void setShowAllItems(boolean value)

  Sets whether all items in the PivotTable report are displayed, even if they do not contain summary data.

  The default value is false. When this property is true, items without summarized values are still shown for the PivotField.

  
   worksheet.getRange("A1:B5").setValue(new Object[][] {
       {"Category", "Amount"},
       {"Fruit", 120},
       {"Fruit", 80},
       {"Vegetables", 95},
       {"Vegetables", 60}
   });
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B5"));
   IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "SalesPivot");
   IPivotField field = pivotTable.getPivotFields().get("Category");
   field.setShowAllItems(true);
   

  Parameters:

  value - true if all items in the PivotTable report are displayed, even when they do not contain summary data; otherwise, false.

• getSourceName

  String getSourceName()

  Gets the field name as it appears in the original source data.

  This value identifies the underlying source field for the PivotTable report. It can differ from the current display name returned by getName() if the field has been renamed by using setName(String).

  
   Object[][] sourceData = new Object[][] {
       {"Product", "Amount"},
       {"Apple", 100},
       {"Pear", 200}
   };
   worksheet.getRange("G1:H3").setValue(sourceData);
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("G1:H3"));
   IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("A1"), "PivotTable1");
   IPivotField field = pivotTable.getPivotFields().get("Product");
   field.setName("Item");
   String sourceName = field.getSourceName();
   

  Returns:

  The original source field name.

• getSubtotals

  EnumSet<SubtotalType> getSubtotals()

  Gets the combination of subtotal types currently shown for this PivotField.

  This method is valid only for nondata fields. Use it to inspect which SubtotalType values are enabled for the field.

  
   worksheet.getRange("A1:B4").setValue(new Object[][] {
       {"Category", "Amount"},
       {"Beverages", 100},
       {"Beverages", 200},
       {"Snacks", 150}
   });
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
   IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "pivotTable1");
   IPivotField categoryField = pivotTable.getPivotFields().get("Category");
   categoryField.setOrientation(PivotFieldOrientation.RowField);
   pivotTable.getPivotFields().get("Amount").setOrientation(PivotFieldOrientation.DataField);
   categoryField.setSubtotals(EnumSet.of(SubtotalType.Sum, SubtotalType.Count));
   EnumSet<SubtotalType> subtotals = categoryField.getSubtotals();
   

  Returns:

  The combination of subtotal types currently shown for the field.

• setSubtotals

  void setSubtotals(EnumSet<SubtotalType> value)

  Sets the combination of subtotal types currently shown for this PivotField.

  This method is valid only for nondata fields. Use it to inspect which SubtotalType values are enabled for the field.

  
   worksheet.getRange("A1:B4").setValue(new Object[][] {
       {"Category", "Amount"},
       {"Beverages", 100},
       {"Beverages", 200},
       {"Snacks", 150}
   });
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
   IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "pivotTable1");
   IPivotField categoryField = pivotTable.getPivotFields().get("Category");
   categoryField.setOrientation(PivotFieldOrientation.RowField);
   pivotTable.getPivotFields().get("Amount").setOrientation(PivotFieldOrientation.DataField);
   categoryField.setSubtotals(EnumSet.of(SubtotalType.Sum, SubtotalType.Count));
   

  Parameters:

  value - The combination of subtotal types currently shown for the field.

• autoSort

  void autoSort(SortOrder Order, String Field)

  Establishes automatic field-sorting rules for a PivotTable report.

  This method sorts the items in the current PivotField by using the specified sort order and the specified sort key field. The Field argument must be the unique source name of the sort key field, as returned by getSourceName(), rather than the displayed field name.

  Example:

  
   worksheet.getRange("A1:C5").setValue(new Object[][] {
   {"Product", "Region", "Amount"},
   {"Apple", "East", 120},
   {"Orange", "West", 80},
   {"Apple", "West", 150},
   {"Orange", "East", 90}
   });
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:C5"));
   IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("E1"), "PivotTable1");
   IPivotField fieldRegion = pivotTable.getPivotFields().get("Region");
   fieldRegion.setOrientation(PivotFieldOrientation.RowField);
   pivotTable.addDataField(pivotTable.getPivotFields().get("Amount"), "Sum of Amount", ConsolidationFunction.Sum);
   fieldRegion.autoSort(SortOrder.Descending, pivotTable.getPivotFields().get("Amount").getSourceName());
   

  Parameters:

  Order - The sort order to apply.

  Field - The unique source name of the sort key field, as returned by getSourceName(); do not use the displayed field name.

• autoSort

  void autoSort(SortOrder Order)

  Establishes automatic field-sorting rules for a PivotTable field.

  Use this method to apply automatic ascending or descending sorting to the current field in a PivotTable report.

  
   worksheet.getRange("A1:C5").setValue(new Object[][] {
       {"Product", "Region", "Amount"},
       {"Apple", "East", 120},
       {"Orange", "West", 80},
       {"Apple", "West", 150},
       {"Orange", "East", 90}
   });
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:C5"));
   IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("E1"), "PivotTable1");
   IPivotField fieldRegion = pivotTable.getPivotFields().get("Region");
   fieldRegion.setOrientation(PivotFieldOrientation.RowField);
   fieldRegion.autoSort(SortOrder.Descending);
   

  Parameters:

  Order - The sort order to apply. Must not be null.

• clearAllFilters

  void clearAllFilters()

  Deletes all filters currently applied to this PivotField.

  This method removes all filters from the getPivotFilters() collection and also clears any manual filtering applied to the field. If the field is in the report filter area, the selected item is reset to the default item.

  
   Object[][] sourceData = new Object[][] {
       {"Product", "Amount"},
       {"Example Speaker", 4270},
       {"Example TV", 2626},
       {"Example Mobile", 9062}
   };
   worksheet.getRange("G1:H4").setValue(sourceData);
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("G1:H4"));
   IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("A1"), "pivottable1");
   IPivotField field = pivotTable.getPivotFields().get("Product");
   field.setOrientation(PivotFieldOrientation.RowField);
   field.getPivotFilters().add(PivotFilterType.CaptionContains, "Example");
   field.clearAllFilters();
   

• group

  void group(PivotFieldDateGroupOptions options)

  Groups the PivotField by date or time units.

  Use PivotFieldDateGroupOptions to specify the grouped range and the date or time units used to create the grouped PivotField. The field must be an uncalculated source field whose source items are date values.

  
   Workbook workbook = new Workbook();
   workbook.getOptions().getData().setAutomaticGroupDateTimeInPivotTable(false);
   IWorksheet worksheet = workbook.getWorksheets().get(0);
   worksheet.getRange("A1:C5").setValue(new Object[][] {
       {"Order Date", "Region", "Amount"},
       {java.time.LocalDateTime.of(2024, 1, 5, 0, 0), "East", 10},
       {java.time.LocalDateTime.of(2024, 1, 18, 0, 0), "West", 20},
       {java.time.LocalDateTime.of(2024, 2, 2, 0, 0), "East", 30},
       {java.time.LocalDateTime.of(2024, 2, 25, 0, 0), "West", 40}
   });
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:C5"));
   IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("E1"), "PivotTable1");
   IPivotField orderDateField = pivotTable.getPivotFields().get("Order Date");
   orderDateField.setOrientation(PivotFieldOrientation.RowField);
   pivotTable.getPivotFields().get("Amount").setOrientation(PivotFieldOrientation.DataField);
   PivotFieldDateGroupOptions options = new PivotFieldDateGroupOptions();
   options.setGroupBy(Collections.singletonList(PivotFieldDateGroupBy.Months));
   options.setAutoStart(true);
   options.setAutoEnd(true);
   orderDateField.group(options);
   String groupedFieldName = pivotTable.getRowFields().get(0).getName();
   

  Parameters:

  options - The date grouping settings. Must not be null.

  Throws:

  IllegalArgumentException - if options is null.

  UnsupportedOperationException - if this field cannot be grouped by date.

• group

  void group(PivotFieldNumberGroupOptions options)

  Groups the PivotField into numeric ranges.

  Use PivotFieldNumberGroupOptions to specify the grouped range and numeric interval used to create each generated group. The field must be an uncalculated source field whose source items are numeric values.

  
   Workbook workbook = new Workbook();
   IWorksheet worksheet = workbook.getWorksheets().get(0);
   worksheet.getRange("A1:C5").setValue(new Object[][] {
       {"Order ID", "Region", "Amount"},
       {10348, "East", 10},
       {10362, "East", 20},
       {10375, "West", 30},
       {10391, "West", 40}
   });
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:C5"));
   IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("E1"), "PivotTable1");
   IPivotField orderIdField = pivotTable.getPivotFields().get("Order ID");
   orderIdField.setOrientation(PivotFieldOrientation.RowField);
   pivotTable.getPivotFields().get("Amount").setOrientation(PivotFieldOrientation.DataField);
   PivotFieldNumberGroupOptions options = new PivotFieldNumberGroupOptions();
   options.setStart(10348d);
   options.setEnd(10407d);
   options.setAutoStart(false);
   options.setAutoEnd(false);
   options.setInterval(20);
   orderIdField.group(options);
   

  Parameters:

  options - The numeric grouping settings. Must not be null.

  Throws:

  IllegalArgumentException - if options is null.

  UnsupportedOperationException - if this field cannot be grouped into numeric ranges.

• group

  void group(PivotFieldCustomGroupOptions options)

  Creates a custom group from PivotItems in the PivotField.

  Use PivotFieldCustomGroupOptions to specify the source PivotItem names, optional display name, and whether generated group fields are automatically added to the PivotTable layout. Source names, item names, and captions are matched without case sensitivity.

  
   Workbook workbook = new Workbook();
   IWorksheet worksheet = workbook.getWorksheets().get(0);
   worksheet.getRange("A1:C5").setValue(new Object[][] {
       {"Product", "Region", "Amount"},
       {"Apple", "East", 10},
       {"Banana", "West", 20},
       {"Carrot", "East", 30},
       {"Date", "West", 40}
   });
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:C5"));
   IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("E1"), "PivotTable1");
   IPivotField productField = pivotTable.getPivotFields().get("Product");
   productField.setOrientation(PivotFieldOrientation.RowField);
   pivotTable.getPivotFields().get("Amount").setOrientation(PivotFieldOrientation.DataField);
   PivotFieldCustomGroupOptions options = new PivotFieldCustomGroupOptions();
   options.setName("Fruit Group");
   options.setItems(java.util.Arrays.asList("Apple", "Banana"));
   productField.group(options);
   

  Parameters:

  options - The custom grouping settings. Must not be null.

  Throws:

  IllegalArgumentException - if options is null.

  UnsupportedOperationException - if this field cannot be grouped.

• ungroup

  void ungroup()

  Removes grouping from the pivot field.

  After grouping is removed, the pivot field uses the original source items from the PivotCache.

  
   Workbook workbook = new Workbook();
   workbook.getOptions().getData().setAutomaticGroupDateTimeInPivotTable(false);
   IWorksheet worksheet = workbook.getWorksheets().get(0);
   worksheet.getRange("A1:C5").setValue(new Object[][] {
       {"Order Date", "Region", "Amount"},
       {java.time.LocalDateTime.of(2024, 1, 5, 0, 0), "East", 10},
       {java.time.LocalDateTime.of(2024, 1, 18, 0, 0), "West", 20},
       {java.time.LocalDateTime.of(2024, 2, 2, 0, 0), "East", 30},
       {java.time.LocalDateTime.of(2024, 2, 25, 0, 0), "West", 40}
   });
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:C5"));
   IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("E1"), "PivotTable1");
   IPivotField orderDateField = pivotTable.getPivotFields().get("Order Date");
   orderDateField.setOrientation(PivotFieldOrientation.RowField);
   pivotTable.getPivotFields().get("Amount").setOrientation(PivotFieldOrientation.DataField);
   PivotFieldDateGroupOptions options = new PivotFieldDateGroupOptions();
   options.setGroupBy(Collections.singletonList(PivotFieldDateGroupBy.Months));
   orderDateField.group(options);
   orderDateField.ungroup();
   

  Throws:

  IllegalStateException - if this pivot field is not grouped.

• clearManualFilter

  void clearManualFilter()

  Clears manual filtering from the PivotField.

  This method sets the visible state of all items in the field to visible in PivotTables. For OLAP PivotTables, it also clears the hidden and visible item lists used by manual filtering.

  
   worksheet.getRange("A1:B5").setValue(new Object[][] {
       {"Region", "Amount"},
       {"East", 120},
       {"West", 80},
       {"East", 150},
       {"South", 90}
   });
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B5"));
   IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("E1"), "PivotTable1");
   IPivotField fieldRegion = pivotTable.getPivotFields().get("Region");
   fieldRegion.setOrientation(PivotFieldOrientation.RowField);
   fieldRegion.getPivotItems().get("West").setVisible(false);
   fieldRegion.clearManualFilter();
   

• getPivotItems

  IPivotItems getPivotItems()

  Gets the collection of all visible and hidden items in this PivotTable field.

  Use this collection to access individual field items, including items that are currently hidden by manual filtering.

  
   Object[][] sourceData = {
       {"Region", "Amount"},
       {"North", 100},
       {"South", 200},
       {"East", 150}
   };
   worksheet.getRange("A1:B4").setValue(sourceData);
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
   IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "salesPivot");
   IPivotField field = pivotTable.getPivotFields().get("Region");
   field.setOrientation(PivotFieldOrientation.RowField);
   IPivotItems items = field.getPivotItems();
   items.get("North").setVisible(false);
   

  Returns:

  The collection of all visible and hidden items in this field.

• getCalculatedItems

  ICalculatedItems getCalculatedItems()

  Gets the collection of calculated items for this IPivotField.

  The returned ICalculatedItems collection represents all calculated items defined for the current PivotTable field and can be used to access or add calculated items.

  
   worksheet.getRange("A1:C4").setValue(new Object[][] {
       {"Region", "Product", "Amount"},
       {"East", "Apple", 120},
       {"East", "Banana", 80},
       {"West", "Apple", 90}
   });
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:C4"));
   IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("E1"), "SalesPivot");
   pivotTable.getPivotFields().get("Region").setOrientation(PivotFieldOrientation.RowField);
   pivotTable.getPivotFields().get("Product").setOrientation(PivotFieldOrientation.ColumnField);
   pivotTable.getPivotFields().get("Amount").setOrientation(PivotFieldOrientation.DataField);
   ICalculatedItems calculatedItems = pivotTable.getPivotFields().get("Product").getCalculatedItems();
   

  Returns:

  The ICalculatedItems collection for the current PivotTable field.

• getPivotFilters

  IPivotFilters getPivotFilters()

  Gets the pivot filter collection for this IPivotField.

  The returned IPivotFilters collection contains the label, date, and value filters defined for the current pivot field and can be used to add or access filters.

  
   worksheet.getRange("A1:B4").setValue(new Object[][] {
       {"Product", "Amount"},
       {"Mi Phone", 100},
       {"Camera", 200},
       {"Mi Band", 150}
   });
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
   IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "SalesPivot");
   IPivotField productField = pivotTable.getPivotFields().get("Product");
   productField.setOrientation(PivotFieldOrientation.RowField);
   IPivotFilters filters = productField.getPivotFilters();
   filters.add(PivotFilterType.CaptionContains, "Mi");
   

  Returns:

  The IPivotFilters collection for the current pivot field.

• clearLabelFilter

  void clearLabelFilter()

  Clears all label filters or date filters from this pivot field.

  This method removes the label-based filter criteria stored in the pivot field's getPivotFilters() collection. Use this method when you want to keep other filter types, such as value filters, unchanged.

  
   Object[][] sourceData = new Object[][] {
       {"Product", "Amount"},
       {"Mi Phone", 100},
       {"Camera", 200},
       {"Mi Band", 150}
   };
   worksheet.getRange("A1:B4").setValue(sourceData);
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
   IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "SalesPivot");
   IPivotField productField = pivotTable.getPivotFields().get("Product");
   productField.setOrientation(PivotFieldOrientation.RowField);
   productField.getPivotFilters().add(PivotFilterType.CaptionContains, "Mi");
   productField.clearLabelFilter();
   

• clearValueFilter

  void clearValueFilter()

  Clears all value filters from this pivot field.

  This method removes value-based filter criteria from the pivot field's getPivotFilters() collection. Use this method when you want to clear value filters without removing label filters or manual filtering.

  
   worksheet.getRange("A1:B4").setValue(new Object[][] {
       {"Product", "Amount"},
       {"Apple", 100},
       {"Banana", 200},
       {"Apple", 150}
   });
   IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
   IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "SalesPivot");
   IPivotField productField = pivotTable.getPivotFields().get("Product");
   productField.setOrientation(PivotFieldOrientation.RowField);
   pivotTable.getPivotFields().get("Amount").setOrientation(PivotFieldOrientation.DataField);
   PivotFilterOptions options = new PivotFilterOptions();
   options.setAppliedDataField(0);
   productField.getPivotFilters().add(PivotFilterType.ValueGreaterThan, 120, null, options);
   productField.clearValueFilter();