Interface IPivotItem

public interface IPivotItem

Represents a pivot item in a PivotTable field.

An IPivotItem corresponds to a single item value in a IPivotField. Use this interface to work with the item's name, source name, position, visibility, expansion state, and calculated-item formula. Pivot items are obtained from the IPivotItems collection of a pivot field.


 worksheet.getRange("A1:B4").setValue(new Object[][] {
     {"Category", "Amount"},
     {"Beverages", 100},
     {"Snacks", 200},
     {"Beverages", 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);
 IPivotItem item = field.getPivotItems().get(0);
 item.setVisible(false);

Method Summary

Modifier and Type

Method

Description

String

getCaption()

Gets the display caption override for the PivotItem.

String

getFormula()

Gets the formula of the calculated item.

String

getName()

Gets the name of the pivot item.

int

getPosition()

Gets the position of this pivot item within its parent pivot field when the item is currently shown.

boolean

getShowDetail()

Gets whether the pivot item is expanded so that its detail rows or columns are visible.

String

getSourceName()

Gets the name of this PivotTable item as it appears in the original source data.

boolean

getVisible()

Gets whether the pivot item is visible.

void

setCaption(String value)

Sets the display caption override for the PivotItem.

void

setFormula(String value)

Sets the formula of the calculated item.

void

setName(String value)

Sets the name of the pivot item.

void

setPosition(int value)

Sets the position of this pivot item within its parent pivot field when the item is currently shown.

void

setShowDetail(boolean value)

Sets whether the pivot item is expanded so that its detail rows or columns are visible.

void

setVisible(boolean value)

Sets whether the pivot item is visible.

Method Details

getCaption

String getCaption()

Gets the display caption override for the PivotItem.

The caption is the text displayed in the PivotTable layout. It can differ from getName() and getSourceName() when the item has a custom label.


 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");
 pivotTable.getPivotFields().get("Product").setOrientation(PivotFieldOrientation.RowField);
 pivotTable.getPivotFields().get("Amount").setOrientation(PivotFieldOrientation.DataField);
 IPivotItem item = pivotTable.getPivotFields().get("Product").getPivotItems().get("Apple");
 item.setCaption("Fresh Apple");
 String caption = item.getCaption();

Returns:: The display caption override for the PivotItem. Returns null if no custom caption is set.

setCaption

void setCaption(String value)

Sets the display caption override for the PivotItem.

A null value clears the caption override.


 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");
 pivotTable.getPivotFields().get("Product").setOrientation(PivotFieldOrientation.RowField);
 pivotTable.getPivotFields().get("Amount").setOrientation(PivotFieldOrientation.DataField);
 IPivotItem item = pivotTable.getPivotFields().get("Product").getPivotItems().get("Apple");
 item.setCaption("Fresh Apple");
 String caption = item.getCaption();

Parameters:: value - The display caption override for the PivotItem. Use null to clear the caption override.

getName

String getName()

Gets the name of the pivot item.

This method returns the current name of the item in its parent IPivotField. The returned value can differ from getSourceName() if the item was renamed after the PivotTable was created.


 worksheet.getRange("A1:B4").setValue(new Object[][] {
     {"Product", "Amount"},
     {"Apple", 100},
     {"Banana", 80},
     {"Apple", 120}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "SalesPivot");
 IPivotField field = pivotTable.getPivotFields().get("Product");
 field.setOrientation(PivotFieldOrientation.RowField);
 String name = field.getPivotItems().get("Apple").getName();

Returns:: The current name of the pivot item.

setName

void setName(String value)

Sets the name of the pivot item.

This method Sets the current name of the item in its parent IPivotField. The returned value can differ from getSourceName() if the item was renamed after the PivotTable was created.


 worksheet.getRange("A1:B4").setValue(new Object[][] {
     {"Product", "Amount"},
     {"Apple", 100},
     {"Banana", 80},
     {"Apple", 120}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "SalesPivot");
 IPivotField field = pivotTable.getPivotFields().get("Product");
 field.setOrientation(PivotFieldOrientation.RowField);
 field.getPivotItems().get(0).setName("Orange");

Parameters:: value - The current name of the pivot item.

getPosition

int getPosition()

Gets the position of this pivot item within its parent pivot field when the item is currently shown.

This value indicates the displayed order of the item in the field.


 worksheet.getRange("A1:B4").setValue(new Object[][] {
     {"Product", "Amount"},
     {"Apple", 100},
     {"Banana", 80},
     {"Carrot", 120}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "SalesPivot");
 IPivotField field = pivotTable.getPivotFields().get("Product");
 field.setOrientation(PivotFieldOrientation.RowField);
 int position = field.getPivotItems().get("Apple").getPosition();

Returns:: The position of the pivot item in its field when the item is currently shown.

setPosition

void setPosition(int value)

Sets the position of this pivot item within its parent pivot field when the item is currently shown.

This value indicates the displayed order of the item in the field.


 worksheet.getRange("A1:B4").setValue(new Object[][] {
     {"Product", "Amount"},
     {"Apple", 100},
     {"Banana", 80},
     {"Carrot", 120}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "SalesPivot");
 IPivotField field = pivotTable.getPivotFields().get("Product");
 field.setOrientation(PivotFieldOrientation.RowField);
 field.getPivotItems().get("Apple").setPosition(0);

Parameters:: value - The position of the pivot item in its field when the item is currently shown.

getShowDetail

boolean getShowDetail()

Gets whether the pivot item is expanded so that its detail rows or columns are visible.

This property returns the current outline state of the pivot item in its parent PivotTable field.


 worksheet.getRange("A1:B4").setValue(new Object[][] {
     {"Category", "Amount"}, {"Fruit", 100}, {"Fruit", 200}, {"Drink", 50}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "PivotTable1");
 pivotTable.getPivotFields().get("Category").setOrientation(PivotFieldOrientation.RowField);
 pivotTable.getPivotFields().get("Amount").setOrientation(PivotFieldOrientation.DataField);
 pivotTable.update();
 boolean expanded = pivotTable.getPivotFields().get("Category").getPivotItems().get("Fruit").getShowDetail();

Returns:: true if the pivot item is expanded; otherwise, false.

setShowDetail

void setShowDetail(boolean value)

Sets whether the pivot item is expanded so that its detail rows or columns are visible.

This property Sets the current outline state of the pivot item in its parent PivotTable field.


 worksheet.getRange("A1:B4").setValue(new Object[][] {
     {"Category", "Amount"}, {"Fruit", 100}, {"Fruit", 200}, {"Drink", 50}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "PivotTable1");
 pivotTable.getPivotFields().get("Category").setOrientation(PivotFieldOrientation.RowField);
 pivotTable.getPivotFields().get("Amount").setOrientation(PivotFieldOrientation.DataField);
 pivotTable.update();
 pivotTable.getPivotFields().get("Category").getPivotItems().get("Fruit").setShowDetail(true);

Parameters:: value - true if the pivot item is expanded; otherwise, false.

getSourceName

String getSourceName()

Gets the name of this PivotTable item as it appears in the original source data.

This value can differ from the current item name if the item was renamed after the PivotTable was created.


 worksheet.getRange("A1:B3").setValue(new Object[][] {
     {"Product", "Amount"},
     {"Coffee", 100},
     {"Tea", 200}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B3"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "SalesPivot");
 IPivotField field = pivotTable.getPivotFields().get("Product");
 field.setOrientation(PivotFieldOrientation.RowField);
 IPivotItem item = field.getPivotItems().get("Coffee");
 String sourceName = item.getSourceName();

Returns:: The item name from the original source data.

getVisible

boolean getVisible()

Gets whether the pivot item is visible.

This method returns the current visibility state of the item in its parent PivotTable field. Use setVisible(boolean) to change the visibility of the item.


 worksheet.getRange("A1:B3").setValue(new Object[][] {
     {"Product", "Amount"},
     {"Example Speaker", 100},
     {"Example Camera", 200}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B3"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "PivotTable1");
 IPivotField productField = pivotTable.getPivotFields().get("Product");
 productField.setOrientation(PivotFieldOrientation.RowField);
 pivotTable.getPivotFields().get("Amount").setOrientation(PivotFieldOrientation.DataField);
 productField.getPivotItems().get("Example Speaker").setVisible(false);
 boolean visible = productField.getPivotItems().get("Example Speaker").getVisible();

Returns:: true if the pivot item is visible; otherwise, false.

setVisible

void setVisible(boolean value)

Sets whether the pivot item is visible.


 worksheet.getRange("A1:B3").setValue(new Object[][] {
     {"Product", "Amount"},
     {"Example Speaker", 100},
     {"Example Camera", 200}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B3"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "PivotTable1");
 IPivotField productField = pivotTable.getPivotFields().get("Product");
 productField.setOrientation(PivotFieldOrientation.RowField);
 pivotTable.getPivotFields().get("Amount").setOrientation(PivotFieldOrientation.DataField);
 productField.getPivotItems().get("Example Speaker").setVisible(false);

Parameters:: value - true if the pivot item is visible; otherwise, false.

getFormula

String getFormula()

Gets the formula of the calculated item.

Use this method to retrieve the formula text defined for a calculated item in a PivotTable field.


 worksheet.getRange("A1:C4").setValue(new Object[][] {
     {"Product", "Category", "Amount"},
     {"Apple", "Fruit", 10},
     {"Banana", "Fruit", 20},
     {"Carrot", "Vegetable", 15}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:C4"));
 IPivotTable pivotTable = pivotCache.createPivotTable(worksheet.getRange("E1"), "PivotTable1");
 pivotTable.getPivotFields().get("Product").setOrientation(PivotFieldOrientation.RowField);
 pivotTable.getPivotFields().get("Category").setOrientation(PivotFieldOrientation.ColumnField);
 pivotTable.getPivotFields().get("Amount").setOrientation(PivotFieldOrientation.DataField);
 IPivotItem item = pivotTable.getPivotFields().get("Product").getCalculatedItems().add("TotalFruit", "=Apple + Banana");
 String formula = item.getFormula();

Returns:: The formula text of the calculated item.

setFormula

void setFormula(String value)

Sets the formula of the calculated item.

Use this method to set the formula text defined for a calculated item in a PivotTable field.


 worksheet.getRange("A1:C4").setValue(new Object[][] {
     {"Product", "Category", "Amount"},
     {"Apple", "Fruit", 10},
     {"Banana", "Fruit", 20},
     {"Carrot", "Vegetable", 15}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:C4"));
 IPivotTable pivotTable = pivotCache.createPivotTable(worksheet.getRange("E1"), "PivotTable1");
 pivotTable.getPivotFields().get("Product").setOrientation(PivotFieldOrientation.RowField);
 pivotTable.getPivotFields().get("Category").setOrientation(PivotFieldOrientation.ColumnField);
 pivotTable.getPivotFields().get("Amount").setOrientation(PivotFieldOrientation.DataField);
 IPivotItem item = pivotTable.getPivotFields().get("Product").getCalculatedItems().add("TotalFruit", "=Apple + Banana");
 item.setFormula("=Apple + Carrot");

Parameters:: value - The formula text of the calculated item.