Interface IPivotTable

public interface IPivotTable

Represents a PivotTable report on a worksheet.

An IPivotTable is a member of the IPivotTables collection and is used to summarize and analyze data from a IPivotCache on a worksheet.


 worksheet.getRange("A1:B4").setValue(new Object[][] {
     {"Name", "Value"},
     {"A", 100},
     {"B", 200},
     {"A", 100}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "PivotTable1");
 pivotTable.setName("SalesPivot");

Method Summary

Modifier and Type

Method

Description

IPivotField

addDataField(IPivotField field, String caption, ConsolidationFunction function)

Adds a data field to the PivotTable report.

void

clearAllFilters()

Deletes all filters currently applied to the pivot table.

void

clearTable()

Clears the PivotTable.

boolean

getAllowMultipleFilters()

Gets whether pivot fields in the pivot table can have multiple filters applied at the same time.

ICalculatedFields

getCalculatedFields()

Gets the collection of calculated fields in this PivotTable report.

IPivotFields

getColumnFields()

Gets the collection of fields that are currently shown as column fields in the PivotTable report.

boolean

getColumnGrand()

Gets whether the PivotTable report shows grand totals for columns.

IRange

getColumnRange()

Gets the range that contains the column area in the PivotTable report.

int

getCompactRowIndent()

Gets the indent increment for pivot items when compact row layout is enabled.

IRange

getDataBodyRange()

Gets the range that contains the data area of the PivotTable report.

IPivotFields

getDataFields()

Gets the collection of fields that are currently shown as data fields in the PivotTable report.

boolean

getDeferLayoutUpdate()

Gets whether layout updates for the PivotTable are deferred.

boolean

getDisplayContextTooltips()

Gets whether tooltips are displayed for PivotTable cells.

boolean

getDisplayErrorString()

Gets whether the PivotTable report displays a custom error string in cells that contain errors.

boolean

getDisplayFieldCaptions()

Gets whether filter buttons and pivot field captions for rows and columns are displayed in the grid.

boolean

getDisplayMemberPropertyTooltips()

Gets whether member properties are displayed in PivotTable tooltips.

boolean

getDisplayNullString()

Gets whether the PivotTable report displays a custom string in cells that contain null values.

String

getErrorString()

Gets the string displayed in cells that contain errors when getDisplayErrorString() is true.

String

getGrandTotalName()

Gets the text label displayed in the grand total row heading or grand total column heading of the PivotTable report.

boolean

getHasAutoFormat()

Gets a value indicating whether the PivotTable report automatically fits column widths when it is refreshed.

LayoutRowType

getLayoutRowDefault()

Gets the default row layout type that is applied when PivotFields are added to the PivotTable for the first time.

boolean

getMergeLabels()

Gets whether the specified PivotTable report uses merged cells for outer-row items, column items, subtotals, and grand total labels.

String

getName()

Gets the name of the PivotTable.

String

getNullString()

Gets the string displayed in PivotTable cells that contain null values when getDisplayNullString() is true.

Order

getPageFieldOrder()

Gets the order in which page fields are added to the PivotTable report layout.

IPivotFields

getPageFields()

Gets the collection of fields that are currently displayed as page fields in the PivotTable.

int

getPageFieldWrapCount()

Gets the number of page fields displayed in each column or row of the PivotTable report.

IRange

getPageRange()

Gets the range that contains the page area in the PivotTable report.

IPivotCache

getPivotCache()

Gets the IPivotCache used by this PivotTable report.

IPivotAxis

getPivotColumnAxis()

Gets the IPivotAxis object that represents the entire column axis of the PivotTable report.

IPivotFields

getPivotFields()

Gets the collection of PivotTable fields in the PivotTable report.

IPivotFormulas

getPivotFormulas()

Gets the collection of formulas for the specified PivotTable report.

IPivotAxis

getPivotRowAxis()

Gets the IPivotAxis object that represents the entire row axis of the PivotTable report.

IPivotFields

getRowFields()

Gets the collection of fields that are currently shown as row fields in the PivotTable report.

boolean

getRowGrand()

Gets whether the PivotTable report shows grand totals for rows.

IRange

getRowRange()

Gets the range that includes the row area in the PivotTable report.

boolean

getShowTableStyleColumnHeaders()

Gets whether column headers are displayed in the PivotTable.

boolean

getShowTableStyleColumnStripes()

Gets whether banded columns are displayed in the PivotTable style.

boolean

getShowTableStyleLastColumn()

Gets whether the last column style is displayed in the PivotTable.

boolean

getShowTableStyleRowHeaders()

Gets whether row headers are displayed in the PivotTable style.

boolean

getShowTableStyleRowStripes()

Gets whether banded rows are displayed in the PivotTable so that even rows are formatted differently from odd rows.

ITableStyle

getStyle()

Gets the style applied to the pivot table.

IRange

getTableRange1()

Gets the range that contains the entire PivotTable report, excluding page fields.

IRange

getTableRange2()

Gets the range that contains the entire PivotTable report, including page fields.

String

getTableStyle()

Gets the style used in the body of the PivotTable report.

String

getTag()

Gets the string saved with the PivotTable report.

IPivotValueCell

pivotValueCell(int rowline, int columnline)

Retrieves the IPivotValueCell at the specified row and column position in the PivotTable data area.

boolean

refresh()

Refreshes the PivotTable report from its source data.

void

repeatAllLabels(PivotFieldRepeatLabels repeat)

Sets whether to repeat item labels for all pivot fields in this pivot table.

void

setAllowMultipleFilters(boolean allowMultipleFilters)

Sets whether pivot fields in the pivot table can have multiple filters applied at the same time.

void

setColumnGrand(boolean value)

Sets whether the PivotTable report shows grand totals for columns.

void

setCompactRowIndent(int value)

Sets the indent increment for pivot items when compact row layout is enabled.

void

setDeferLayoutUpdate(boolean value)

Sets whether layout updates for the PivotTable are deferred.

void

setDisplayContextTooltips(boolean value)

Sets whether tooltips are displayed for PivotTable cells.

void

setDisplayErrorString(boolean value)

Sets whether the PivotTable report displays a custom error string in cells that contain errors.

void

setDisplayFieldCaptions(boolean displayFieldCaptions)

Sets whether filter buttons and pivot field captions for rows and columns are displayed in the grid.

void

setDisplayMemberPropertyTooltips(boolean value)

Sets whether member properties are displayed in PivotTable tooltips.

void

setDisplayNullString(boolean value)

Sets whether the PivotTable report displays a custom string in cells that contain null values.

void

setErrorString(String value)

Sets the string displayed in cells that contain errors when getDisplayErrorString() is true.

void

setGrandTotalName(String value)

Sets the text label displayed in the grand total row heading or grand total column heading of the PivotTable report.

void

setHasAutoFormat(boolean value)

Sets a value indicating whether the PivotTable report automatically fits column widths when it is refreshed.

void

setLayoutRowDefault(LayoutRowType value)

Sets the default layout row type for IPivotField objects when they are added to the IPivotTable for the first time.

void

setMergeLabels(boolean value)

Sets whether the specified PivotTable report uses merged cells for outer-row items, column items, subtotals, and grand total labels.

void

setName(String value)

Sets the name of the PivotTable.

void

setNullString(String value)

Sets the string displayed in PivotTable cells that contain null values when getDisplayNullString() is true.

void

setPageFieldOrder(Order value)

Sets the order in which page fields are added to the PivotTable report layout.

void

setPageFieldWrapCount(int value)

Sets the number of page fields displayed in each column or row of the PivotTable report.

void

setRowAxisLayout(LayoutRowType rowLayout)

Sets the row axis layout for all existing PivotFields in the pivot table.

void

setRowGrand(boolean value)

Sets whether the PivotTable report shows grand totals for rows.

void

setShowTableStyleColumnHeaders(boolean value)

Sets whether column headers are displayed in the PivotTable.

void

setShowTableStyleColumnStripes(boolean value)

Sets whether banded columns are displayed in the PivotTable style.

void

setShowTableStyleLastColumn(boolean value)

Sets whether the last column style is displayed in the PivotTable.

void

setShowTableStyleRowHeaders(boolean value)

Sets whether row headers are displayed in the PivotTable style.

void

setShowTableStyleRowStripes(boolean value)

Sets whether banded rows are displayed in the PivotTable so that even rows are formatted differently from odd rows.

void

setStyle(ITableStyle style)

Sets the style applied to the pivot table.

void

setTableStyle(String value)

Sets the style used in the body of the PivotTable report.

void

setTag(String value)

Sets the custom text metadata associated with the PivotTable report.

void

subtotalLocation(SubtotalLocationType location)

Changes the subtotal location for all existing pivot fields.

void

update()

Updates the PivotTable report layout and displayed results.

Method Details

getColumnGrand

boolean getColumnGrand()

Gets whether the PivotTable report shows grand totals for columns.

This property indicates whether a grand total is displayed for the column area of the PivotTable report. The default value is true.


 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");
 pivotTable.getPivotFields().get("Category").setOrientation(PivotFieldOrientation.ColumnField);
 pivotTable.getPivotFields().get("Amount").setOrientation(PivotFieldOrientation.DataField);
 boolean showColumnGrand = pivotTable.getColumnGrand();

Returns:: true if the PivotTable report shows grand totals for columns; otherwise, false.

setColumnGrand

void setColumnGrand(boolean value)

Sets whether the PivotTable report shows grand totals for columns.

This property indicates whether a grand total is displayed for the column area of the PivotTable report. The default value is true.


 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");
 pivotTable.getPivotFields().get("Category").setOrientation(PivotFieldOrientation.ColumnField);
 pivotTable.getPivotFields().get("Amount").setOrientation(PivotFieldOrientation.DataField);
 pivotTable.setColumnGrand(true);

Parameters:: value - Whether the PivotTable report shows grand totals for columns.

getColumnFields

IPivotFields getColumnFields()

Gets the collection of fields that are currently shown as column fields in the PivotTable report.

The returned collection is read-only and contains the fields whose orientation is set to column fields.


 worksheet.getRange("A1:C5").setValue(new Object[][] {
     {"Category", "Region", "Amount"},
     {"Beverages", "East", 120},
     {"Beverages", "West", 80},
     {"Snacks", "East", 95},
     {"Snacks", "West", 110}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:C5"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("E1"), "SalesPivot");
 pivotTable.getPivotFields().get("Category").setOrientation(PivotFieldOrientation.ColumnField);
 IPivotFields columnFields = pivotTable.getColumnFields();

Returns:: The collection of fields that are currently shown as column fields in the PivotTable report.

getColumnRange

IRange getColumnRange()

Gets the range that contains the column area in the PivotTable report.

The returned range covers the cells used for column fields in the current PivotTable layout.


 worksheet.getRange("A1:C5").setValue(new Object[][] {
     {"Category", "Region", "Amount"},
     {"Beverages", "East", 120},
     {"Beverages", "West", 80},
     {"Snacks", "East", 95},
     {"Snacks", "West", 110}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:C5"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("E1"), "SalesPivot");
 pivotTable.getPivotFields().get("Category").setOrientation(PivotFieldOrientation.ColumnField);
 pivotTable.addDataField(pivotTable.getPivotFields().get("Amount"), "Sum of Amount", ConsolidationFunction.Sum);
 IRange range = pivotTable.getColumnRange();
 String address = range.getAddress();

Returns:: The range that contains the column area in the PivotTable report.

getCompactRowIndent

int getCompactRowIndent()

Gets the indent increment for pivot items when compact row layout is enabled.

This value controls how much each nested pivot item is indented when the PivotTable report uses compact row layout.


 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");
 pivotTable.getPivotFields().get("Category").setOrientation(PivotFieldOrientation.RowField);
 pivotTable.setCompactRowIndent(2);
 int indent = pivotTable.getCompactRowIndent();

Returns:: The indent increment for pivot items when compact row layout is enabled.

setCompactRowIndent

void setCompactRowIndent(int value)

Sets the indent increment for pivot items when compact row layout is enabled.

This value controls how much each nested pivot item is indented when the PivotTable report uses compact row layout.


 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");
 pivotTable.getPivotFields().get("Category").setOrientation(PivotFieldOrientation.RowField);
 pivotTable.setCompactRowIndent(2);

Parameters:: value - The indent increment for pivot items when compact row layout is enabled.

getDataBodyRange

IRange getDataBodyRange()

Gets the range that contains the data area of the PivotTable report.

The returned range represents the cells between the header area and the insert row.


 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");
 pivotTable.getPivotFields().get("Category").setOrientation(PivotFieldOrientation.RowField);
 pivotTable.addDataField(pivotTable.getPivotFields().get("Amount"), "Sum of Amount", ConsolidationFunction.Sum);
 IRange range = pivotTable.getDataBodyRange();

Returns:: The range that contains the data area of the PivotTable report.

getDataFields

IPivotFields getDataFields()

Gets the collection of fields that are currently shown as data fields in the PivotTable report.

The returned collection is read-only and contains the fields placed in the Values area of the PivotTable.


 worksheet.getRange("A1:B4").setValue(new Object[][] {
     {"Category", "Amount"},
     {"Fruit", 100},
     {"Fruit", 80},
     {"Vegetables", 60}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "SalesPivot");
 pivotTable.addDataField(pivotTable.getPivotFields().get("Amount"), "Sum of Amount", ConsolidationFunction.Sum);
 IPivotFields dataFields = pivotTable.getDataFields();
 String caption = dataFields.get(0).getName();

Returns:: The collection of fields that are currently shown as data fields in the PivotTable report.

getDeferLayoutUpdate

boolean getDeferLayoutUpdate()

Gets whether layout updates for the PivotTable are deferred.

When this property is true, layout changes can be accumulated and then applied by calling update().


 worksheet.getRange("A1:C5").setValue(new Object[][] {
     {"Category", "Product", "Amount"},
     {"Beverages", "Tea", 100},
     {"Beverages", "Coffee", 200},
     {"Snacks", "Biscuits", 150}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:C5"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("E1"), "PivotTable1");
 pivotTable.setDeferLayoutUpdate(true);
 boolean deferred = pivotTable.getDeferLayoutUpdate();

Returns:: true if layout updates for the PivotTable are deferred; otherwise, false.

setDeferLayoutUpdate

void setDeferLayoutUpdate(boolean value)

Sets whether layout updates for the PivotTable are deferred.

When this property is true, layout changes can be accumulated and then applied by calling update().


 worksheet.getRange("A1:C5").setValue(new Object[][] {
     {"Category", "Product", "Amount"},
     {"Beverages", "Tea", 100},
     {"Beverages", "Coffee", 200},
     {"Snacks", "Biscuits", 150}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:C5"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("E1"), "PivotTable1");
 pivotTable.setDeferLayoutUpdate(true);

Parameters:: value - Whether layout updates for the PivotTable are deferred.

getDisplayContextTooltips

boolean getDisplayContextTooltips()

Gets whether tooltips are displayed for PivotTable cells.

This property indicates whether context tooltips are shown for cells in the PivotTable report.


 worksheet.getRange("A1:B4").setValue(new Object[][] {
     {"Name", "Amount"},
     {"A", 100},
     {"B", 200},
     {"A", 150}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "SalesPivot");
 boolean displayContextTooltips = pivotTable.getDisplayContextTooltips();

Returns:: true if context tooltips are displayed for PivotTable cells; otherwise, false.

setDisplayContextTooltips

void setDisplayContextTooltips(boolean value)

Sets whether tooltips are displayed for PivotTable cells.

This property indicates whether context tooltips are shown for cells in the PivotTable report.


 worksheet.getRange("A1:B4").setValue(new Object[][] {
     {"Name", "Amount"},
     {"A", 100},
     {"B", 200},
     {"A", 150}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "SalesPivot");
 pivotTable.setDisplayContextTooltips(true);

Parameters:: value - Whether tooltips are displayed for PivotTable cells.

getDisplayErrorString

boolean getDisplayErrorString()

Gets whether the PivotTable report displays a custom error string in cells that contain errors.

When this property is true, the PivotTable uses the string specified by getErrorString() for cells that contain errors. The default value is false.


 worksheet.getRange("A1:B4").setValue(new Object[][] {
     {"Category", "Amount"},
     {"A", 10},
     {"B", 20},
     {"A", 15}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "PivotTable1");
 pivotTable.setDisplayErrorString(true);
 boolean displayErrorString = pivotTable.getDisplayErrorString();

Returns:: true if the PivotTable report displays a custom error string in cells that contain errors; otherwise, false.

setDisplayErrorString

void setDisplayErrorString(boolean value)
Sets whether the PivotTable report displays a custom error string in cells that contain errors.
When this property is true, the PivotTable uses the string specified by getErrorString() for cells that contain errors. The default value is false.
```
 worksheet.getRange("A1:B4").setValue(new Object[][] {
     {"Category", "Amount"},
     {"A", 10},
     {"B", 20},
     {"A", 15}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "PivotTable1");
 pivotTable.setDisplayErrorString(true);
 
```
Parameters:

value - Whether the PivotTable report displays a custom error string in cells that contain errors.

getDisplayMemberPropertyTooltips

boolean getDisplayMemberPropertyTooltips()

Gets whether member properties are displayed in PivotTable tooltips.

When this property is true, tooltips can include member property information for PivotTable items.


 worksheet.getRange("A1:B4").setValue(new Object[][] {
     {"Category", "Amount"},
     {"A", 100},
     {"B", 200},
     {"A", 150}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "SalesPivot");
 pivotTable.setDisplayMemberPropertyTooltips(true);
 boolean displayMemberPropertyTooltips = pivotTable.getDisplayMemberPropertyTooltips();

Returns:: true if member properties are displayed in PivotTable tooltips; otherwise, false.

setDisplayMemberPropertyTooltips

void setDisplayMemberPropertyTooltips(boolean value)

Sets whether member properties are displayed in PivotTable tooltips.

When this property is true, tooltips can include member property information for PivotTable items.


 worksheet.getRange("A1:B4").setValue(new Object[][] {
     {"Category", "Amount"},
     {"A", 100},
     {"B", 200},
     {"A", 150}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "SalesPivot");
 pivotTable.setDisplayMemberPropertyTooltips(true);

Parameters:: value - Whether member properties are displayed in PivotTable tooltips.

getDisplayNullString

boolean getDisplayNullString()

Gets whether the PivotTable report displays a custom string in cells that contain null values.

When this property is true, the PivotTable uses the string specified by getNullString() for cells that contain null values. The default value is true.


 worksheet.getRange("A1:B4").setValue(new Object[][] {
     {"Name", "Value"},
     {"A", 100},
     {"B", null},
     {"A", 200}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "PivotTable1");
 boolean displayNullString = pivotTable.getDisplayNullString();

Returns:: true if the PivotTable report displays a custom string in cells that contain null values; otherwise, false.

setDisplayNullString

void setDisplayNullString(boolean value)
Sets whether the PivotTable report displays a custom string in cells that contain null values.
When this property is true, the PivotTable uses the string specified by getNullString() for cells that contain null values. The default value is true.
```
 worksheet.getRange("A1:B4").setValue(new Object[][] {
     {"Name", "Value"},
     {"A", 100},
     {"B", null},
     {"A", 200}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "PivotTable1");
 pivotTable.setDisplayNullString(true);
 
```
Parameters:

value - Whether the PivotTable report displays a custom string in cells that contain null values.

getErrorString

String getErrorString()

Gets the string displayed in cells that contain errors when getDisplayErrorString() is true.

The default value is an empty string ( ""). Use setErrorString(String) to specify the custom error text shown by the PivotTable for error cells.


 worksheet.getRange("A1:B4").setValue(new Object[][] {
     {"Category", "Amount"},
     {"A", 10},
     {"B", 20},
     {"A", 15}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "PivotTable1");
 pivotTable.setDisplayErrorString(true);
 pivotTable.setErrorString("Invalid");
 String errorString = pivotTable.getErrorString();

Returns:: The string displayed in cells that contain errors when getDisplayErrorString() is true.

setErrorString

void setErrorString(String value)

Sets the string displayed in cells that contain errors when getDisplayErrorString() is true.

The default value is an empty string ( ""). Use setErrorString(String) to specify the custom error text shown by the PivotTable for error cells.


 worksheet.getRange("A1:B4").setValue(new Object[][] {
     {"Category", "Amount"},
     {"A", 10},
     {"B", 20},
     {"A", 15}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "PivotTable1");
 pivotTable.setDisplayErrorString(true);
 pivotTable.setErrorString("Invalid");

Parameters:: value - The string displayed in cells that contain errors when getDisplayErrorString() is true.

getGrandTotalName

String getGrandTotalName()

Gets the text label displayed in the grand total row heading or grand total column heading of the PivotTable report.

The default label is "Grand Total".


 worksheet.getRange("A1:B4").setValue(new Object[][] {
     {"Region", "Sales"},
     {"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");
 String grandTotalName = pivotTable.getGrandTotalName();

Returns:: The text label displayed in the grand total row heading or grand total column heading of the PivotTable report.

setGrandTotalName

void setGrandTotalName(String value)

Sets the text label displayed in the grand total row heading or grand total column heading of the PivotTable report.

The default label is "Grand Total".


 worksheet.getRange("A1:B4").setValue(new Object[][] {
     {"Region", "Sales"},
     {"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");
 pivotTable.setGrandTotalName("Sample");

Parameters:: value - The text label displayed in the grand total row heading or grand total column heading of the PivotTable report.

getLayoutRowDefault

LayoutRowType getLayoutRowDefault()

Gets the default row layout type that is applied when PivotFields are added to the PivotTable for the first time.

This setting determines the initial row layout used for newly added PivotFields, such as compact, outline, or tabular layout.


 worksheet.getRange("A1:C4").setValue(new Object[][] {
     {"Region", "Product", "Amount"},
     {"East", "Apple", 120},
     {"West", "Apple", 80},
     {"East", "Orange", 90}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:C4"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("E1"), "SalesPivot");
 pivotTable.setLayoutRowDefault(LayoutRowType.TabularRow);
 LayoutRowType layoutType = pivotTable.getLayoutRowDefault();

Returns:: The default row layout type that is applied when PivotFields are added to the PivotTable for the first time.

setLayoutRowDefault

void setLayoutRowDefault(LayoutRowType value)

Sets the default layout row type for IPivotField objects when they are added to the IPivotTable for the first time.

This setting applies to newly added PivotFields. To change the layout of existing PivotFields at the same time, use setRowAxisLayout(LayoutRowType).


 worksheet.getRange("A1:C5").setValue(new Object[][] {
     {"Region", "Product", "Amount"},
     {"East", "Apple", 100},
     {"East", "Pear", 80},
     {"West", "Apple", 120},
     {"West", "Pear", 90}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:C5"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("E1"), "SalesPivot");
 pivotTable.setLayoutRowDefault(LayoutRowType.TabularRow);
 pivotTable.getPivotFields().get("Region").setOrientation(PivotFieldOrientation.RowField);

Parameters:: value - The default layout row type to apply when a PivotField is added to the PivotTable for the first time.

getMergeLabels

boolean getMergeLabels()

Gets whether the specified PivotTable report uses merged cells for outer-row items, column items, subtotals, and grand total labels.

Use this property to determine whether repeated labels in the PivotTable layout are displayed by merging adjacent cells.


 worksheet.getRange("A1:C5").setValue(new Object[][] {
     {"Region", "Product", "Amount"},
     {"East", "Apple", 100},
     {"East", "Pear", 200},
     {"West", "Apple", 150}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:C5"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("E1"), "SalesPivot");
 pivotTable.getPivotFields().get("Region").setOrientation(PivotFieldOrientation.RowField);
 pivotTable.getPivotFields().get("Product").setOrientation(PivotFieldOrientation.RowField);
 pivotTable.getPivotFields().get("Amount").setOrientation(PivotFieldOrientation.DataField);
 pivotTable.setMergeLabels(true);
 boolean mergeLabels = pivotTable.getMergeLabels();

Returns:: true if the specified PivotTable report uses merged cells for outer-row items, column items, subtotals, and grand total labels; otherwise, false.

setMergeLabels

void setMergeLabels(boolean value)

Sets whether the specified PivotTable report uses merged cells for outer-row items, column items, subtotals, and grand total labels.

Use this property to set whether repeated labels in the PivotTable layout are displayed by merging adjacent cells.


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

Parameters:: value - Whether the specified PivotTable report uses merged cells for outer-row items, column items, subtotals, and grand total labels.

getName

String getName()

Gets the name of the PivotTable.

The name identifies the PivotTable on the worksheet and can be used to reference the PivotTable programmatically.


 worksheet.getRange("A1:C4").setValue(new Object[][] {
     {"Region", "Product", "Amount"},
     {"East", "Apple", 120},
     {"West", "Apple", 80},
     {"East", "Orange", 90}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:C4"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("E1"), "SalesPivot");
 String name = pivotTable.getName();

Returns:: The PivotTable name.

setName

void setName(String value)

Sets the name of the PivotTable.

The name identifies the PivotTable on the worksheet and can be used to reference the PivotTable programmatically.


 worksheet.getRange("A1:C4").setValue(new Object[][] {
     {"Region", "Product", "Amount"},
     {"East", "Apple", 120},
     {"West", "Apple", 80},
     {"East", "Orange", 90}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:C4"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("E1"), "SalesPivot");
 pivotTable.setName("Sample");

Parameters:: value - The PivotTable name.

getNullString

String getNullString()

Gets the string displayed in PivotTable cells that contain null values when getDisplayNullString() is true.

The default value is an empty string ( "").


 worksheet.getRange("A1:B4").setValue(new Object[][] {
     {"Name", "Value"},
     {"A", 100},
     {"B", null},
     {"A", 200}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "SalesPivot");
 pivotTable.setDisplayNullString(true);
 pivotTable.setNullString("(blank)");
 String nullString = pivotTable.getNullString();

Returns:: The string displayed in PivotTable cells that contain null values when getDisplayNullString() is true.

setNullString

void setNullString(String value)

Sets the string displayed in PivotTable cells that contain null values when getDisplayNullString() is true.

The default value is an empty string ( "").


 worksheet.getRange("A1:B4").setValue(new Object[][] {
     {"Name", "Value"},
     {"A", 100},
     {"B", null},
     {"A", 200}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "SalesPivot");
 pivotTable.setDisplayNullString(true);
 pivotTable.setNullString("(blank)");

Parameters:: value - The string displayed in PivotTable cells that contain null values when getDisplayNullString() is true.

getPageFieldOrder

Order getPageFieldOrder()

Gets the order in which page fields are added to the PivotTable report layout.

The returned value is one of the Order constants: Order.DownThenOver or Order.OverThenDown. The default value is Order.DownThenOver.


 worksheet.getRange("A1:C5").setValue(new Object[][] {
     {"Region", "Product", "Amount"},
     {"East", "Apple", 100},
     {"East", "Pear", 80},
     {"West", "Apple", 90},
     {"West", "Pear", 70}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:C5"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("E1"), "SalesPivot");
 pivotTable.getPivotFields().get("Region").setOrientation(PivotFieldOrientation.PageField);
 pivotTable.setPageFieldOrder(Order.OverThenDown);
 Order order = pivotTable.getPageFieldOrder();

Returns:: The order in which page fields are added to the PivotTable report layout.

setPageFieldOrder

void setPageFieldOrder(Order value)

Sets the order in which page fields are added to the PivotTable report layout.

Use Order.DownThenOver to place page fields down each column before moving to the next column, or Order.OverThenDown to place page fields across each row before moving to the next row. The default value is Order.DownThenOver.


 worksheet.getRange("A1:C5").setValue(new Object[][] {
     {"Region", "Product", "Amount"},
     {"East", "Apple", 100},
     {"East", "Pear", 80},
     {"West", "Apple", 90},
     {"West", "Pear", 70}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:C5"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("E1"), "SalesPivot");
 pivotTable.getPivotFields().get("Region").setOrientation(PivotFieldOrientation.PageField);
 pivotTable.setPageFieldOrder(Order.OverThenDown);

Parameters:: value - The page field layout order. Use Order.DownThenOver or Order.OverThenDown; if null is specified, it is treated as Order.DownThenOver.

getPageFields

IPivotFields getPageFields()

Gets the collection of fields that are currently displayed as page fields in the PivotTable.

The returned collection is read-only and contains the fields whose orientation is set to the page area of the PivotTable report.


 worksheet.getRange("A1:C5").setValue(new Object[][] {
     {"Region", "Product", "Amount"},
     {"East", "Apple", 100},
     {"East", "Pear", 80},
     {"West", "Apple", 90},
     {"West", "Pear", 70}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:C5"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("E1"), "SalesPivot");
 pivotTable.getPivotFields().get("Region").setOrientation(PivotFieldOrientation.PageField);
 IPivotFields pageFields = pivotTable.getPageFields();

Returns:: The collection of fields that are currently displayed as page fields in the PivotTable.

getPageFieldWrapCount

int getPageFieldWrapCount()

Gets the number of page fields displayed in each column or row of the PivotTable report.

This value determines how page fields are wrapped in the report filter area when the PivotTable contains multiple page fields. Use setPageFieldWrapCount(int) to change the layout.


 worksheet.getRange("A1:D5").setValue(new Object[][] {
     {"Region", "Product", "Quarter", "Amount"},
     {"East", "Apple", "Q1", 120},
     {"East", "Pear", "Q2", 95},
     {"West", "Apple", "Q1", 80},
     {"West", "Pear", "Q2", 110}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:D5"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("F1"), "SalesPivot");
 pivotTable.getPivotFields().get("Region").setOrientation(PivotFieldOrientation.PageField);
 pivotTable.getPivotFields().get("Quarter").setOrientation(PivotFieldOrientation.PageField);
 pivotTable.setPageFieldWrapCount(1);
 int wrapCount = pivotTable.getPageFieldWrapCount();

Returns:: The number of page fields displayed in each column or row of the PivotTable report.

setPageFieldWrapCount

void setPageFieldWrapCount(int value)

Sets the number of page fields displayed in each column or row of the PivotTable report.

This value determines how page fields are wrapped in the report filter area when the PivotTable contains multiple page fields. Use setPageFieldWrapCount(int) to change the layout.


 worksheet.getRange("A1:D5").setValue(new Object[][] {
     {"Region", "Product", "Quarter", "Amount"},
     {"East", "Apple", "Q1", 120},
     {"East", "Pear", "Q2", 95},
     {"West", "Apple", "Q1", 80},
     {"West", "Pear", "Q2", 110}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:D5"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("F1"), "SalesPivot");
 pivotTable.getPivotFields().get("Region").setOrientation(PivotFieldOrientation.PageField);
 pivotTable.getPivotFields().get("Quarter").setOrientation(PivotFieldOrientation.PageField);
 pivotTable.setPageFieldWrapCount(1);

Parameters:: value - The number of page fields displayed in each column or row of the PivotTable report.

getPageRange

IRange getPageRange()

Gets the range that contains the page area in the PivotTable report.

The page area is the report filter area that displays fields whose orientation is set to page fields. This method returns null if the PivotTable does not currently have a page area.


 worksheet.getRange("A1:C5").setValue(new Object[][] {
     {"Region", "Product", "Amount"},
     {"East", "Apple", 100},
     {"East", "Pear", 80},
     {"West", "Apple", 90},
     {"West", "Pear", 70}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:C5"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("E1"), "SalesPivot");
 pivotTable.getPivotFields().get("Region").setOrientation(PivotFieldOrientation.PageField);
 IRange pageRange = pivotTable.getPageRange();

Returns:: The range that contains the page area in the PivotTable report.

getPivotColumnAxis

IPivotAxis getPivotColumnAxis()

Gets the IPivotAxis object that represents the entire column axis of the PivotTable report.

Use this axis to work with the column-side structure of a PivotTable, such as the pivot lines displayed across the top of the report.


 worksheet.getRange("A1:C5").setValue(new Object[][] {
     {"Category", "Region", "Amount"},
     {"Beverages", "East", 120},
     {"Beverages", "West", 80},
     {"Snacks", "East", 95},
     {"Snacks", "West", 110}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:C5"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("E1"), "SalesPivot");
 pivotTable.getPivotFields().get("Category").setOrientation(PivotFieldOrientation.ColumnField);
 IPivotAxis columnAxis = pivotTable.getPivotColumnAxis();
 IPivotLines lines = columnAxis.getPivotLines();

Returns:: The IPivotAxis object that represents the entire column axis of the PivotTable report.

getPivotRowAxis

IPivotAxis getPivotRowAxis()

Gets the IPivotAxis object that represents the entire row axis of the PivotTable report.

Use this axis to work with the row-side structure of a PivotTable, such as the pivot lines displayed down the left side of the report.


 worksheet.getRange("A1:C5").setValue(new Object[][] {
     {"Category", "Region", "Amount"},
     {"Beverages", "East", 120},
     {"Beverages", "West", 80},
     {"Snacks", "East", 95},
     {"Snacks", "West", 110}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:C5"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("E1"), "SalesPivot");
 pivotTable.getPivotFields().get("Category").setOrientation(PivotFieldOrientation.RowField);
 IPivotAxis rowAxis = pivotTable.getPivotRowAxis();
 IPivotLines lines = rowAxis.getPivotLines();

Returns:: The IPivotAxis object that represents the entire row axis of the PivotTable report.

getRowFields

IPivotFields getRowFields()

Gets the collection of fields that are currently shown as row fields in the PivotTable report.

The returned IPivotFields collection is read-only and contains the fields whose orientation is set to row fields.


 worksheet.getRange("A1:C5").setValue(new Object[][] {
     {"Region", "Product", "Amount"},
     {"East", "Apple", 120},
     {"East", "Pear", 80},
     {"West", "Apple", 95},
     {"West", "Pear", 110}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:C5"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("E1"), "SalesPivot");
 pivotTable.getPivotFields().get("Region").setOrientation(PivotFieldOrientation.RowField);
 pivotTable.getPivotFields().get("Product").setOrientation(PivotFieldOrientation.RowField);
 pivotTable.getPivotFields().get("Amount").setOrientation(PivotFieldOrientation.DataField);
 IPivotFields rowFields = pivotTable.getRowFields();
 String firstFieldName = rowFields.get(0).getName();

Returns:: The IPivotFields collection currently shown as row fields; see IPivotFields.

getRowGrand

boolean getRowGrand()

Gets whether the PivotTable report shows grand totals for rows.

Use this method to determine whether the PivotTable displays a grand total row for the row fields.


 worksheet.getRange("A1:B4").setValue(new Object[][] {
     {"Region", "Amount"},
     {"East", 120},
     {"West", 150},
     {"East", 90}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "SalesPivot");
 pivotTable.getPivotFields().get("Region").setOrientation(PivotFieldOrientation.RowField);
 pivotTable.getPivotFields().get("Amount").setOrientation(PivotFieldOrientation.DataField);
 boolean showRowGrand = pivotTable.getRowGrand();

Returns:: true if the PivotTable report shows grand totals for rows; otherwise, false.

setRowGrand

void setRowGrand(boolean value)

Sets whether the PivotTable report shows grand totals for rows.

Use this method to set whether the PivotTable displays a grand total row for the row fields.


 worksheet.getRange("A1:B4").setValue(new Object[][] {
     {"Region", "Amount"},
     {"East", 120},
     {"West", 150},
     {"East", 90}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "SalesPivot");
 pivotTable.getPivotFields().get("Region").setOrientation(PivotFieldOrientation.RowField);
 pivotTable.getPivotFields().get("Amount").setOrientation(PivotFieldOrientation.DataField);
 pivotTable.setRowGrand(value);

Parameters:: value - Whether the PivotTable report shows grand totals for rows.

getRowRange

IRange getRowRange()

Gets the range that includes the row area in the PivotTable report.

The returned range represents the cells used by row fields and row items in the current PivotTable layout.


 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");
 pivotTable.getPivotFields().get("Category").setOrientation(PivotFieldOrientation.RowField);
 IRange rowRange = pivotTable.getRowRange();

Returns:: The range that includes the row area in the PivotTable report.

getShowTableStyleColumnHeaders

boolean getShowTableStyleColumnHeaders()

Gets whether column headers are displayed in the PivotTable.

This property indicates whether the PivotTable shows its column header area using the current table style settings.


 worksheet.getRange("A1:D5").setValue(new Object[][] {
     {"Category", "Product", "Amount", "Region"},
     {"Beverages", "Tea", 100, "East"},
     {"Beverages", "Coffee", 200, "West"},
     {"Snacks", "Chips", 150, "East"},
     {"Snacks", "Nuts", 120, "West"}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:D5"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("F1"), "SalesPivot");
 pivotTable.getPivotFields().get("Category").setOrientation(PivotFieldOrientation.ColumnField);
 pivotTable.getPivotFields().get("Amount").setOrientation(PivotFieldOrientation.DataField);
 boolean showColumnHeaders = pivotTable.getShowTableStyleColumnHeaders();

Returns:: true if column headers are displayed in the PivotTable; otherwise, false.

setShowTableStyleColumnHeaders

void setShowTableStyleColumnHeaders(boolean value)

Sets whether column headers are displayed in the PivotTable.

This property indicates whether the PivotTable shows its column header area using the current table style settings.


 worksheet.getRange("A1:D5").setValue(new Object[][] {
     {"Category", "Product", "Amount", "Region"},
     {"Beverages", "Tea", 100, "East"},
     {"Beverages", "Coffee", 200, "West"},
     {"Snacks", "Chips", 150, "East"},
     {"Snacks", "Nuts", 120, "West"}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:D5"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("F1"), "SalesPivot");
 pivotTable.getPivotFields().get("Category").setOrientation(PivotFieldOrientation.ColumnField);
 pivotTable.getPivotFields().get("Amount").setOrientation(PivotFieldOrientation.DataField);
 pivotTable.setShowTableStyleColumnHeaders(value);

Parameters:: value - Whether column headers are displayed in the PivotTable.

getShowTableStyleColumnStripes

boolean getShowTableStyleColumnStripes()

Gets whether banded columns are displayed in the PivotTable style.

When this property is true, even columns are formatted differently from odd columns to make the PivotTable easier to read.


 worksheet.getRange("A1:B4").setValue(new Object[][] {
     {"Category", "Amount"},
     {"Fruit", 100},
     {"Drink", 200},
     {"Fruit", 150}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "SalesPivot");
 pivotTable.setShowTableStyleColumnStripes(true);
 boolean showColumnStripes = pivotTable.getShowTableStyleColumnStripes();

Returns:: true if banded columns are displayed in the PivotTable style; otherwise, false.

setShowTableStyleColumnStripes

void setShowTableStyleColumnStripes(boolean value)

Sets whether banded columns are displayed in the PivotTable style.

When this property is true, even columns are formatted differently from odd columns to make the PivotTable easier to read.


 worksheet.getRange("A1:B4").setValue(new Object[][] {
     {"Category", "Amount"},
     {"Fruit", 100},
     {"Drink", 200},
     {"Fruit", 150}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "SalesPivot");
 pivotTable.setShowTableStyleColumnStripes(true);

Parameters:: value - Whether banded columns are displayed in the PivotTable style.

getShowTableStyleLastColumn

boolean getShowTableStyleLastColumn()

Gets whether the last column style is displayed in the PivotTable.

This property indicates whether the PivotTable style is applied to the last column of the PivotTable report.


 worksheet.getRange("A1:B4").setValue(new Object[][] {
     {"Category", "Amount"},
     {"Fruit", 100},
     {"Drink", 200},
     {"Fruit", 150}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "SalesPivot");
 pivotTable.getPivotFields().get("Category").setOrientation(PivotFieldOrientation.RowField);
 pivotTable.getPivotFields().get("Amount").setOrientation(PivotFieldOrientation.DataField);
 pivotTable.setShowTableStyleLastColumn(true);
 boolean showLastColumnStyle = pivotTable.getShowTableStyleLastColumn();

Returns:: true if the last column style is displayed in the PivotTable; otherwise, false.

setShowTableStyleLastColumn

void setShowTableStyleLastColumn(boolean value)

Sets whether the last column style is displayed in the PivotTable.

This property indicates whether the PivotTable style is applied to the last column of the PivotTable report.


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

Parameters:: value - Whether the last column style is displayed in the PivotTable.

getShowTableStyleRowHeaders

boolean getShowTableStyleRowHeaders()

Gets whether row headers are displayed in the PivotTable style.

Use this method to determine whether the current PivotTable style shows row header formatting.


 worksheet.getRange("A1:B4").setValue(new Object[][] {
     {"Category", "Amount"},
     {"Fruit", 100},
     {"Drink", 200},
     {"Fruit", 150}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "SalesPivot");
 pivotTable.setShowTableStyleRowHeaders(true);
 boolean showRowHeaders = pivotTable.getShowTableStyleRowHeaders();

Returns:: true if row headers are displayed in the PivotTable style; otherwise, false.

setShowTableStyleRowHeaders

void setShowTableStyleRowHeaders(boolean value)

Sets whether row headers are displayed in the PivotTable style.

Use this method to set whether the current PivotTable style shows row header formatting.


 worksheet.getRange("A1:B4").setValue(new Object[][] {
     {"Category", "Amount"},
     {"Fruit", 100},
     {"Drink", 200},
     {"Fruit", 150}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "SalesPivot");
 pivotTable.setShowTableStyleRowHeaders(true);

Parameters:: value - Whether row headers are displayed in the PivotTable style.

getShowTableStyleRowStripes

boolean getShowTableStyleRowStripes()

Gets whether banded rows are displayed in the PivotTable so that even rows are formatted differently from odd rows.

When this property is true, the PivotTable style applies alternating row formatting to improve readability.


 worksheet.getRange("A1:B4").setValue(new Object[][] {
     {"Category", "Amount"},
     {"Beverages", 120},
     {"Snacks", 95},
     {"Beverages", 80}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "SalesPivot");
 pivotTable.getPivotFields().get("Category").setOrientation(PivotFieldOrientation.RowField);
 pivotTable.getPivotFields().get("Amount").setOrientation(PivotFieldOrientation.DataField);
 boolean showRowStripes = pivotTable.getShowTableStyleRowStripes();

Returns:: true if banded rows are displayed in the PivotTable so that even rows are formatted differently from odd rows; otherwise, false.

setShowTableStyleRowStripes

void setShowTableStyleRowStripes(boolean value)

Sets whether banded rows are displayed in the PivotTable so that even rows are formatted differently from odd rows.

When this property is true, the PivotTable style applies alternating row formatting to improve readability.


 worksheet.getRange("A1:B4").setValue(new Object[][] {
     {"Category", "Amount"},
     {"Beverages", 120},
     {"Snacks", 95},
     {"Beverages", 80}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "SalesPivot");
 pivotTable.getPivotFields().get("Category").setOrientation(PivotFieldOrientation.RowField);
 pivotTable.getPivotFields().get("Amount").setOrientation(PivotFieldOrientation.DataField);
 pivotTable.setShowTableStyleRowStripes(value);

Parameters:: value - Whether banded rows are displayed in the PivotTable so that even rows are formatted differently from odd rows.

getTableRange1

IRange getTableRange1()

Gets the range that contains the entire PivotTable report, excluding page fields.

Use this method to access the rendered PivotTable area without including any page field region that is displayed separately from the main report.


 worksheet.getRange("A1:B4").setValue(new Object[][] {
     {"Category", "Amount"},
     {"Fruit", 100},
     {"Fruit", 200},
     {"Vegetable", 150}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "SalesPivot");
 pivotTable.getPivotFields().get("Category").setOrientation(PivotFieldOrientation.RowField);
 pivotTable.getPivotFields().get("Amount").setOrientation(PivotFieldOrientation.DataField);
 IRange range = pivotTable.getTableRange1();
 String address = range.toString();

Returns:: The range that contains the entire PivotTable report, excluding page fields.

getTableRange2

IRange getTableRange2()

Gets the range that contains the entire PivotTable report, including page fields.

Use this method to retrieve the full displayed PivotTable area, including filter areas, rather than only the main report body.


 worksheet.getRange("A1:B4").setValue(new Object[][] {
     {"Category", "Amount"},
     {"Beverages", 120},
     {"Beverages", 80},
     {"Snacks", 50}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D2"), "SalesPivot");
 pivotTable.getPivotFields().get("Category").setOrientation(PivotFieldOrientation.RowField);
 pivotTable.getPivotFields().get("Amount").setOrientation(PivotFieldOrientation.DataField);
 IRange range = pivotTable.getTableRange2();

Returns:: The range that contains the entire PivotTable report, including page fields.

getTableStyle

String getTableStyle()

Gets the style used in the body of the PivotTable report.

The default value is a null string, which indicates that no style is applied.


 worksheet.getRange("A1:B4").setValue(new Object[][] {
     {"Category", "Amount"},
     {"Beverages", 120},
     {"Beverages", 80},
     {"Snacks", 95}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "SalesPivot");
 pivotTable.setTableStyle("PivotStyleMedium9");
 String styleName = pivotTable.getTableStyle();

Returns:: The style used in the body of the PivotTable report.

setTableStyle

void setTableStyle(String value)

Sets the style used in the body of the PivotTable report.

The default value is a null string, which indicates that no style is applied.


 worksheet.getRange("A1:B4").setValue(new Object[][] {
     {"Category", "Amount"},
     {"Beverages", 120},
     {"Beverages", 80},
     {"Snacks", 95}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "SalesPivot");
 pivotTable.setTableStyle("PivotStyleMedium9");

Parameters:: value - The style used in the body of the PivotTable report.

getTag

String getTag()

Gets the string saved with the PivotTable report.

Use this property to retrieve custom text metadata associated with the PivotTable report. To store a value, use setTag(String).


 worksheet.getRange("A1:B4").setValue(new Object[][] {
     {"Region", "Amount"},
     {"East", 120},
     {"West", 80},
     {"East", 90}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "SalesPivot");
 pivotTable.setTag("Monthly sales summary");
 String tag = pivotTable.getTag();

Returns:: The string saved with the PivotTable report.

setTag

void setTag(String value)

Sets the custom text metadata associated with the PivotTable report.

The value is saved with the PivotTable report.


 worksheet.getRange("A1:B4").setValue(new Object[][] {
     {"Region", "Amount"},
     {"East", 120},
     {"West", 80},
     {"East", 90}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "SalesPivot");
 pivotTable.setTag("Monthly sales summary");

Parameters:: value - The custom text metadata to associate with the PivotTable report.

addDataField

IPivotField addDataField(IPivotField field, String caption, ConsolidationFunction function)

Adds a data field to the PivotTable report.

Use this method to add a source field to the values area and specify the summary function used for that data field.


 Object[][] sourceData = {
     {"Region", "Product", "Amount"},
     {"East", "Apple", 120},
     {"West", "Apple", 80},
     {"East", "Orange", 90}
 };
 worksheet.getRange("A1:C4").setValue(sourceData);
 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.addDataField(pivotTable.getPivotFields().get("Amount"), "Sum of Amount", ConsolidationFunction.Sum);

Parameters:: field - The unique field on the server. If the source data is OLAP, this field is a cube field; otherwise, it is a PivotTable field.; caption - The label used in the PivotTable report to identify the added data field.; function - The function performed in the added data field.
Returns:: The added IPivotField.

getCalculatedFields

ICalculatedFields getCalculatedFields()

Gets the collection of calculated fields in this PivotTable report.

Use the returned ICalculatedFields collection to access or add calculated fields for the current PivotTable.


 worksheet.getRange("A1:B4").setValue(new Object[][] {
     {"Product", "Amount"},
     {"Apple", 120},
     {"Orange", 90},
     {"Apple", 80}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "SalesPivot");
 ICalculatedFields calculatedFields = pivotTable.getCalculatedFields();
 calculatedFields.add("DoubleAmount", "=Amount*2");

Returns:: The ICalculatedFields collection in this PivotTable report; see ICalculatedFields.

clearAllFilters

void clearAllFilters()

Deletes all filters currently applied to the pivot table.

This method removes filters in the pivot table's filter collection, clears manual filtering, and resets all pivot fields in the Report Filter area to their default item.


 Object[][] sourceData = {
     {"Product", "Country", "Amount"},
     {"Apple", "Canada", 120},
     {"Apple", "France", 150},
     {"Banana", "Canada", 90}
 };
 worksheet.getRange("A1:C4").setValue(sourceData);
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:C4"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("E1"), "PivotTable1");
 IPivotField fieldProduct = pivotTable.getPivotFields().get("Product");
 fieldProduct.setOrientation(PivotFieldOrientation.RowField);
 IPivotField fieldAmount = pivotTable.getPivotFields().get("Amount");
 fieldAmount.setOrientation(PivotFieldOrientation.DataField);
 fieldProduct.getPivotFilters().add(PivotFilterType.CaptionContains, "pp");
 pivotTable.clearAllFilters();

clearTable

void clearTable()

Clears the PivotTable.

Clearing a PivotTable removes all fields and deletes any filtering and sorting applied to the report. This method resets the PivotTable to the state it had immediately after creation, before any fields were added.


 Object[][] sourceData = {
     {"Product", "Region", "Amount"},
     {"Apple", "East", 120},
     {"Pear", "West", 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"), "PivotTable1");
 pivotTable.getPivotFields().get("Region").setOrientation(PivotFieldOrientation.RowField);
 pivotTable.addDataField(pivotTable.getPivotFields().get("Amount"), "Sum of Amount", ConsolidationFunction.Sum);
 pivotTable.clearTable();

getPivotCache

IPivotCache getPivotCache()

Gets the IPivotCache used by this PivotTable report.

The returned cache represents the source data cache associated with the PivotTable.


 worksheet.getRange("A1:B4").setValue(new Object[][] {
     {"Product", "Sales"},
     {"Apple", 100},
     {"Orange", 200},
     {"Pear", 150}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "SalesPivot");
 IPivotCache cache = pivotTable.getPivotCache();

Returns:: The IPivotCache used by this PivotTable report.

getPivotFields

IPivotFields getPivotFields()

Gets the collection of PivotTable fields in the PivotTable report.

The returned IPivotFields collection includes both visible and hidden fields from the PivotTable data source. You can use this collection to access individual fields and configure how they are used in the report layout.


 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");
 IPivotFields pivotFields = pivotTable.getPivotFields();
 pivotFields.get("Category").setOrientation(PivotFieldOrientation.RowField);

Returns:: The IPivotFields collection in the PivotTable report; see IPivotFields.

pivotValueCell

IPivotValueCell pivotValueCell(int rowline, int columnline)

Retrieves the IPivotValueCell at the specified row and column position in the PivotTable data area.

Use this method to access a calculated value cell in a PivotTable report and then inspect its value or related IPivotCell information.


 worksheet.getRange("A1:B3").setValue(new Object[][] {
     {"Region", "Amount"},
     {"East", 100},
     {"West", 200}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B3"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"));
 pivotTable.getPivotFields().get("Region").setOrientation(PivotFieldOrientation.RowField);
 pivotTable.getPivotFields().get("Amount").setOrientation(PivotFieldOrientation.DataField);
 IPivotValueCell valueCell = pivotTable.pivotValueCell(0, 0);
 Object value = valueCell.getValue();

Parameters:: rowline - The row position in the data area.; columnline - The column position in the data area.
Returns:: The IPivotValueCell at the specified position.

refresh

boolean refresh()

Refreshes the PivotTable report from its source data.

Use this method after the source data has changed and you need the PivotTable to reflect the latest data.

This method performs the following actions:

Refreshes the underlying IPivotCache.
Synchronizes IPivotFields with the refreshed cache.
Recalculates and updates the PivotTable report on the worksheet.

If only the PivotTable layout or display settings have changed and the source data has not changed, use update() instead.


 worksheet.getRange("A1:B4").setValue(new Object[][] {
     {"Product", "Amount"},
     {"Apple", 100},
     {"Apple", 200},
     {"Orange", 150}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "PivotTable1");
 pivotTable.getPivotFields().get("Product").setOrientation(PivotFieldOrientation.RowField);
 pivotTable.getPivotFields().get("Amount").setOrientation(PivotFieldOrientation.DataField);
 worksheet.getRange("B2").setValue(120);
 boolean refreshed = pivotTable.refresh();

Returns:: true if the refresh succeeds.
Throws:: IllegalStateException - if the source sheet of the PivotTable cannot be found.

repeatAllLabels

void repeatAllLabels(PivotFieldRepeatLabels repeat)

Sets whether to repeat item labels for all pivot fields in this pivot table.

This setting is applied to all existing pivot fields in the pivot table.


 worksheet.getRange("A1:C5").setValue(new Object[][] {
     {"Category", "Product", "Amount"},
     {"Beverages", "Tea", 100},
     {"Beverages", "Coffee", 200},
     {"Snacks", "Chips", 150},
     {"Snacks", "Nuts", 120}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:C5"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("E1"), "SalesPivot");
 pivotTable.getPivotFields().get("Category").setOrientation(PivotFieldOrientation.RowField);
 pivotTable.getPivotFields().get("Product").setOrientation(PivotFieldOrientation.RowField);
 pivotTable.repeatAllLabels(PivotFieldRepeatLabels.RepeatLabels);

Parameters:: repeat - Specifies whether to repeat item labels for all pivot fields in the pivot table.

setRowAxisLayout

void setRowAxisLayout(LayoutRowType rowLayout)

Sets the row axis layout for all existing PivotFields in the pivot table.

Use this method to apply a single LayoutRowType to the current row fields at once. To set the default layout for PivotFields added later, use setLayoutRowDefault(LayoutRowType).


 worksheet.getRange("A1:C5").setValue(new Object[][] {
     {"Region", "Product", "Amount"},
     {"East", "Apple", 120},
     {"West", "Apple", 80},
     {"East", "Orange", 90},
     {"West", "Orange", 110}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:C5"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("E1"), "SalesPivot");
 pivotTable.getPivotFields().get("Region").setOrientation(PivotFieldOrientation.RowField);
 pivotTable.getPivotFields().get("Product").setOrientation(PivotFieldOrientation.RowField);
 pivotTable.getPivotFields().get("Amount").setOrientation(PivotFieldOrientation.DataField);
 pivotTable.setRowAxisLayout(LayoutRowType.TabularRow);

Parameters:: rowLayout - The row axis layout type to apply to all existing PivotFields.

subtotalLocation

void subtotalLocation(SubtotalLocationType location)

Changes the subtotal location for all existing pivot fields.

Changing the subtotal location has an immediate visual effect only for fields in outline form, but the setting is also applied to fields in tabular form.

If the row axis layout is LayoutRowType.TabularRow, the location must be SubtotalLocationType.Bottom.


 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"));
 pivotTable.getPivotFields().get("Category").setOrientation(PivotFieldOrientation.RowField);
 pivotTable.getPivotFields().get("Amount").setOrientation(PivotFieldOrientation.DataField);
 pivotTable.subtotalLocation(SubtotalLocationType.Bottom);

Parameters:: location - The subtotal location to apply to all existing pivot fields.
Throws:: IllegalArgumentException - if the row axis layout is LayoutRowType.TabularRow and location is not SubtotalLocationType.Bottom.

update

void update()

Updates the PivotTable report layout and displayed results.

A common use case is to enable setDeferLayoutUpdate(boolean), make multiple layout changes, and then call this method once to apply them.

This method does not refresh the PivotCache from the source data. If the source data has changed and the PivotCache must be refreshed first, call refresh() instead.


 worksheet.getRange("A1:C5").setValue(new Object[][] {
     {"Category", "Product", "Amount"},
     {"Beverages", "Tea", 100},
     {"Beverages", "Coffee", 200},
     {"Snacks", "Biscuits", 150}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:C5"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("E1"), "PivotTable1");
 pivotTable.setDeferLayoutUpdate(true);
 pivotTable.getPivotFields().get("Category").setOrientation(PivotFieldOrientation.RowField);
 pivotTable.getPivotFields().get("Amount").setOrientation(PivotFieldOrientation.DataField);
 pivotTable.update();

getStyle

ITableStyle getStyle()

Gets the style applied to the pivot table.

This method returns the current ITableStyle associated with the pivot table. If no style has been applied, this method returns null.


 worksheet.getRange("A1:B4").setValue(new Object[][] {
     {"Name", "Amount"},
     {"A", 100},
     {"B", 200},
     {"A", 150}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "SalesPivot");
 pivotTable.setStyle(workbook.getTableStyles().get("PivotStyleMedium3"));
 ITableStyle style = pivotTable.getStyle();
 String styleName = style.getName();

Returns:: The style applied to the pivot table.

setStyle

void setStyle(ITableStyle style)

Sets the style applied to the pivot table.

This method Sets the current ITableStyle associated with the pivot table. If no style has been applied, this method Sets null.


 worksheet.getRange("A1:B4").setValue(new Object[][] {
     {"Name", "Amount"},
     {"A", 100},
     {"B", 200},
     {"A", 150}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("D1"), "SalesPivot");
 pivotTable.setStyle(workbook.getTableStyles().get("PivotStyleMedium3"));
 String styleName = style.getName();

Parameters:: style - The style applied to the pivot table.

getPivotFormulas

IPivotFormulas getPivotFormulas()

Gets the collection of formulas for the specified PivotTable report.

Use this method to access the IPivotFormulas collection that contains the custom formulas defined for the PivotTable, such as formulas created by calculated items.


 worksheet.getRange("A1:C4").setValue(new Object[][] {
     {"Product", "Category", "Amount"},
     {"Apple", "Fruit", 120},
     {"Banana", "Fruit", 80},
     {"Bran", "Snack", 60}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:C4"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("E1"), "SalesPivot");
 pivotTable.getPivotFields().get("Product").setOrientation(PivotFieldOrientation.RowField);
 pivotTable.getPivotFields().get("Amount").setOrientation(PivotFieldOrientation.DataField);
 pivotTable.getPivotFields().get("Category").setOrientation(PivotFieldOrientation.ColumnField);
 pivotTable.getPivotFields().get("Product").getCalculatedItems().add("TotalFruit", "=Product[Apple] + Product[Banana]");
 IPivotFormulas formulas = pivotTable.getPivotFormulas();
 IPivotFormula formula = formulas.get(0);

Returns:: The collection of formulas for the specified PivotTable report.

getAllowMultipleFilters

boolean getAllowMultipleFilters()

Gets whether pivot fields in the pivot table can have multiple filters applied at the same time.

Use this property to determine whether the pivot table allows a single pivot field to keep more than one filter condition simultaneously.


 worksheet.getRange("A1:C5").setValue(new Object[][] {
     {"Category", "Product", "Amount"},
     {"Fruit", "Apple", 120},
     {"Fruit", "Banana", 90},
     {"Drink", "Tea", 60},
     {"Drink", "Coffee", 150}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:C5"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("E1"), "SalesPivot");
 pivotTable.setAllowMultipleFilters(true);
 boolean allowMultipleFilters = pivotTable.getAllowMultipleFilters();

Returns:: true if pivot fields in the pivot table can have multiple filters applied at the same time; otherwise, false.

setAllowMultipleFilters

void setAllowMultipleFilters(boolean allowMultipleFilters)

Sets whether pivot fields in the pivot table can have multiple filters applied at the same time.

Use this property to set whether the pivot table allows a single pivot field to keep more than one filter condition simultaneously.


 worksheet.getRange("A1:C5").setValue(new Object[][] {
     {"Category", "Product", "Amount"},
     {"Fruit", "Apple", 120},
     {"Fruit", "Banana", 90},
     {"Drink", "Tea", 60},
     {"Drink", "Coffee", 150}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:C5"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("E1"), "SalesPivot");
 pivotTable.setAllowMultipleFilters(true);

Parameters:: allowMultipleFilters - Whether pivot fields in the pivot table can have multiple filters applied at the same time.

getHasAutoFormat

boolean getHasAutoFormat()

Gets a value indicating whether the PivotTable report automatically fits column widths when it is refreshed.

The default value is true.


 worksheet.getRange("A1:B4").setValue(new Object[][] {
     {"Product", "Amount"},
     {"Apple", 100},
     {"Orange", 200},
     {"Apple", 150}
 });
 IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:B4"));
 IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("E1"), "SalesPivot");
 boolean hasAutoFormat = pivotTable.getHasAutoFormat();

Returns:: true if the PivotTable report automatically fits column widths when refreshed; otherwise, false.

setHasAutoFormat

void setHasAutoFormat(boolean value)

Sets a value indicating whether the PivotTable report automatically fits column widths when it is refreshed.