Interface IPivotField

public interface IPivotField

Represents a field in a PivotTable report.

An IPivotField is a member of the IPivotFields collection and describes how a source field is used in a PivotTable report, including hidden fields. Use this interface to access field metadata, change field orientation, configure sorting and filtering, and work with row, column, page, or data fields.


 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.setOrientation(PivotFieldOrientation.RowField);

Method Summary

Modifier and Type

Method

Description

void

autoSort(SortOrder Order)

Establishes automatic field-sorting rules for a PivotTable field.

void

autoSort(SortOrder Order, String Field)

Establishes automatic field-sorting rules for a PivotTable report.

void

clearAllFilters()

Deletes all filters currently applied to this PivotField.

void

clearLabelFilter()

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

void

clearManualFilter()

Clears manual filtering from the PivotField.

void

clearValueFilter()

Clears all value filters from this pivot field.

boolean

getAllItemsVisible()

Gets whether all items in the PivotField are visible.

String

getAutoSortField()

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

SortOrder

getAutoSortOrder()

Gets the automatic sort order of this PivotTable field.

String

getBaseField()

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

String

getBaseItem()

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

ICalculatedItems

getCalculatedItems()

Gets the collection of calculated items for this IPivotField.

PivotFieldCalculation

getCalculation()

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

int

getCurrentPage()

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

boolean

getDragToColumn()

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

boolean

getDragToData()

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

boolean

getDragToHide()

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

boolean

getDragToPage()

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

boolean

getDragToRow()

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

boolean

getEnableMultiplePageItems()

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

String

getFormula()

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

ConsolidationFunction

getFunction()

Gets the summary function used by this PivotTable field.

boolean

getLayoutBlankLine()

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

boolean

getLayoutCompactRow()

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

LayoutFormType

getLayoutForm()

Gets the way the specified PivotTable items appear.

SubtotalLocationType

getLayoutSubtotalLocation()

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

String

getName()

Gets the name of the object.

String

getNumberFormat()

Gets the number format code for the pivot field.

PivotFieldOrientation

getOrientation()

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

IPivotFilters

getPivotFilters()

Gets the pivot filter collection for this IPivotField.

IPivotItems

getPivotItems()

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

int

getPosition()

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

boolean

getRepeatLabels()

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

boolean

getShowAllItems()

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

String

getSourceName()

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

EnumSet<SubtotalType>

getSubtotals()

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

void

group(PivotFieldCustomGroupOptions options)

Creates a custom group from PivotItems in the PivotField.

void

group(PivotFieldDateGroupOptions options)

Groups the PivotField by date or time units.

void

group(PivotFieldNumberGroupOptions options)

Groups the PivotField into numeric ranges.

void

setBaseField(String value)

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

void

setBaseItem(String value)

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

void

setCalculation(PivotFieldCalculation value)

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

void

setCurrentPage(int value)

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

void

setDragToColumn(boolean value)

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

void

setDragToData(boolean value)

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

void

setDragToHide(boolean value)

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

void

setDragToPage(boolean value)

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

void

setDragToRow(boolean value)

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

void

setEnableMultiplePageItems(boolean value)

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

void

setFormula(String value)

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

void

setFunction(ConsolidationFunction value)

Sets the function used to summarize the PivotTable field.

void

setLayoutBlankLine(boolean value)

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

void

setLayoutCompactRow(boolean value)

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

void

setLayoutForm(LayoutFormType value)

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

void

setLayoutSubtotalLocation(SubtotalLocationType value)

Sets where subtotals are displayed for this PivotTable field.

void

setName(String value)

Sets the name of the object.

void

setNumberFormat(String value)

Sets the number format code for the pivot field.

void

setOrientation(PivotFieldOrientation value)

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

void

setPosition(int value)

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

void

setRepeatLabels(boolean value)

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

void

setShowAllItems(boolean value)

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

void

setSubtotals(EnumSet<SubtotalType> value)

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

void

ungroup()

Removes grouping from the pivot field.

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.


 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.


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

Interface IPivotField

Method Summary

Method Details

getAllItemsVisible

getAutoSortField

getAutoSortOrder

getBaseField

setBaseField

getBaseItem

setBaseItem

getCalculation

setCalculation

getCurrentPage

setCurrentPage

getDragToColumn

setDragToColumn

getDragToData

setDragToData

getDragToHide

setDragToHide

getDragToPage

setDragToPage

getDragToRow

setDragToRow

getEnableMultiplePageItems

setEnableMultiplePageItems

getFormula

setFormula

getFunction

setFunction

getLayoutBlankLine

setLayoutBlankLine

getLayoutCompactRow

setLayoutCompactRow

getLayoutForm

setLayoutForm

getLayoutSubtotalLocation

setLayoutSubtotalLocation

getName

setName

getNumberFormat

setNumberFormat

getOrientation

setOrientation

getPosition

setPosition

getRepeatLabels

setRepeatLabels

getShowAllItems

setShowAllItems

getSourceName

getSubtotals

setSubtotals

autoSort

autoSort

clearAllFilters

group

group

group

ungroup

clearManualFilter

getPivotItems

getCalculatedItems

getPivotFilters

clearLabelFilter

clearValueFilter