public final class PivotTable extends Object
Pivot Tables are used to arrange and aggregate complicated data and drill down on details.
Source of data for Pivot Table can be:
Worksheet
- cell range
, named range
, table, etc.
GemBox.Spreadsheet currently supports creating only worksheet source
.
All other sources are supported through preservation (to correctly round-trip to XLSX format).
Source of the data for Pivot Table contains following information:
Pivot Table does not directly reference its data source, but instead it references a cache of a data source. Cache of a data source contains an in-memory copy of data source scheme and, optionally, data and is stored in a workbook which may contain multiple caches of various sources of data.
Cache can be updated/refreshed from a data source automatically (for example, when opening a workbook) or on request. Cache is used to provide better performance of Pivot Table functionality which requires frequent access to data records and their values and to enable sharing the same data source between multiple Pivot Tables.
GemBox.Spreadsheet represents cache with a PivotCache
type.
Cache source is represented with a PivotCacheSource
type (and its sub-types) and can be accessed from a PivotCache.getCacheSource()
method.
PivotCache
instances are stored in a ExcelFile.getPivotCaches()
collection.
PivotCache
currently doesn't store the actual cached records (it stores only scheme - fields and their properties), but instead Microsoft Excel is instructed to update the record cache from the data source when opening a workbook.
PivotTable
is created from a PivotCache
with the specified name and the specified cell in the worksheet where it is placed using the PivotTableCollection.add(PivotCache, String, String)
methods.
PivotTable
contains the PivotFields
collection which PivotField
elements can be moved
into the following Pivot Table areas:
getPageFields()
collection.
getRowFields()
collection.
getColumnFields()
collection.
getDataFields()
collection.
Each (non-calculated) PivotField
can contain zero or more PivotItem
s which are used for filtering and grouping by a specific value.
Except using fields defined in Pivot Table data source (more precisely, in getPivotCache()
) which cannot be removed, Pivot Table can also define calculated PivotField
s and calculated PivotItem
s which use a formula to specify their values.
Calculated PivotField
can be added using a PivotFieldCollection.addCalculated(String, String)
method and removed using a PivotFieldCollection.removeCalculated(String)
method. Method PivotField.isCalculated()
tells if a field is calculated or is a field from the data source.
Calculated PivotField
is automatically moved to a Values Area (getDataFields()
collection) and only Values Area can contain calculated PivotField
s.
Calculated PivotItem
can be added using a PivotItemCollection.addCalculated(String, String)
method and removed using a PivotItemCollection.removeCalculated(String)
method. Method PivotItem.isCalculated()
tells if an item is calculated.
If calculated PivotField
is added/removed in one PivotTable
, then it is also added/removed from all other PivotTable
s which reference the same getPivotCache()
.
Since adding/removing calculated PivotField
s means changing the data scheme, which is defined in PivotCache
, actual modification is made in PivotCache
and propagated to all PivotTable
s which are referencing it.
PivotField
s can be re-ordered in a specific area using the PivotAreaFieldCollection.move(int, int)
method. PivotItem
s can be re-ordered using the PivotItemCollection.move(int, int)
method.
GemBox.Spreadsheet currently does not provide functionality to read/calculate the actual data from the PivotTable
.
It provides the functionality to arrange and customize PivotField
s, PivotItem
s and PivotTable
appearance.
Because of that PivotTable
data is not rendered/written to other output formats like HTML. When output XLSX file with Pivot Table is opened in Microsoft Excel application, its data is automatically retrieved and calculated from the data source by Microsoft Excel.
Following code demonstrates how to create and customize PivotTable
using GemBox.Spreadsheet component to get the following information out of the sample records:
This information might help you decide if working for GemBox is worth it if you are a man or a woman and in which department would it be the best to work (considering department salaries of other companies).
Input workbook can be downloaded from PivotTableData.xlsx
// Load workbook from XLSX file on Desktop.
ExcelFile workbook = ExcelFile.load("PivotTableData.xlsx");
// Create pivot cache from named range 'Sheet1!PivotDataRange'.
PivotCache pivotCache = workbook.getPivotCaches().addWorksheetSource("Sheet1!PivotDataRange");
// Create pivot table using the specified pivot cache, name it 'PivotTable1' and add it to the 'Sheet1' worksheet at the cell location 'G2'.
PivotTable pivotTable = workbook.getWorksheet("Sheet1").addPivotTable(pivotCache, "PivotTable1", "G2");
// Aggregate 'Salary' values into average value and show it as a percentage of difference from average value for 'GemBox' company.
PivotField field = pivotTable.getDataFields().add("Salary");
field.setFunction(PivotFieldCalculationType.AVERAGE);
field.setNumberFormat("[$$-409]#,##0.00");
field.setShowDataAs(PivotFieldDisplayFormat.PERCENTAGE_OF_DIFFERENCE);
field.setBaseField(pivotTable.getPivotFields().get("Company"));
field.setBaseItem(field.getBaseField().getPivotItems().get("GemBox"));
// Aggregate 'Salary' values into max value and show it as a percentage of difference from max value for 'GemBox' company.
field = pivotTable.getDataFields().add("Salary");
field.setFunction(PivotFieldCalculationType.MAX);
field.setNumberFormat("[$$-409]#,##0.00");
field.setShowDataAs(PivotFieldDisplayFormat.PERCENTAGE_OF_DIFFERENCE);
field.setBaseField(pivotTable.getPivotFields().get("Company"));
field.setBaseItem(field.getBaseField().getPivotItems().get("GemBox"));
// Group rows first into 'Departments' and then into 'Companies'.
pivotTable.getRowFields().add("Department");
pivotTable.getRowFields().add("Company");
// Group columns first into 'Data' (average 'Salary' and max 'Salary) and then into 'Genders'.
pivotTable.getColumnFields().add(pivotTable.getDataPivotField());
pivotTable.getColumnFields().add("Gender");
// Do not show grand totals for columns because we are only interested in a relative difference between 'GemBox' and other companies.
pivotTable.setColumnGrandTotals(false);
// Style pivot table.
pivotTable.setBuiltInStyle(BuiltInPivotStyleName.PIVOT_STYLE_LIGHT_16);
// Save workbook to XLSX file on Desktop.
workbook.save("PivotTable.xlsx");
Modifier and Type | Method and Description |
---|---|
BuiltInPivotStyleName |
getBuiltInStyle()
Gets the built-in style used in the body of the
PivotTable . |
PivotAreaFieldCollection |
getColumnFields()
Gets the collection of all
PivotField s that are currently shown as column fields. |
String |
getColumnHeaderCaption()
Gets the
String to be displayed in column header in compact mode. |
PivotAreaFieldCollection |
getDataFields()
Gets the collection of all
PivotField s that are currently shown as data fields. |
PivotField |
getDataPivotField()
Gets a
PivotField object that represents all the data fields in this PivotTable . |
PivotTableFilter |
getFilter()
Gets the pivot table
PivotTableFilter . |
String |
getName()
Gets the name of the
PivotTable . |
PivotAreaFieldCollection |
getPageFields()
Gets the collection of all
PivotField s that are currently showing as page fields. |
PivotCache |
getPivotCache()
Gets the
PivotCache associated with this PivotTable . |
PivotFieldCollection |
getPivotFields()
Gets the collection of all (visible and hidden)
PivotField s. |
PivotAreaFieldCollection |
getRowFields()
Gets the collection of all
PivotField s that are currently showing as row fields. |
String |
getRowHeaderCaption()
Gets the
String to be displayed in row header in compact mode. |
EnumSet<PivotTableStyleOptions> |
getStyleOptions()
Gets the options to be applied to the
PivotTable style. |
ExcelCell |
getTopLeftCell()
Gets the location of this
PivotTable in the worksheet. |
boolean |
isClassicPivotTableLayout()
Sets a value indicating whether the classic layout should be used for
PivotTable display. |
boolean |
isColumnGrandTotals()
Gets a value indicating whether the
PivotTable shows grand totals for columns. |
boolean |
isFieldListSortAscending()
Gets a value indicating whether fields in the
PivotTable are sorted in non-default order in the field list. |
boolean |
isPageOverThenDown()
Gets a value indicating how the page fields are laid out when there are multiple
PivotField s in the page area. |
boolean |
isRowGrandTotals()
Gets a value indicating whether the
PivotTable shows grand totals for rows. |
void |
setBuiltInStyle(BuiltInPivotStyleName builtInStyle)
Sets the built-in style used in the body of the
PivotTable . |
void |
setClassicPivotTableLayout(boolean classicPivotTableLayout)
Gets a value indicating whether the classic layout should be used for
PivotTable display. |
void |
setColumnGrandTotals(boolean columnGrandTotals)
Sets a value indicating whether the
PivotTable shows grand totals for columns. |
void |
setColumnHeaderCaption(String columnHeaderCaption)
Sets the
String to be displayed in column header in compact mode. |
void |
setFieldListSortAscending(boolean fieldListSortAscending)
Sets a value indicating whether fields in the
PivotTable are sorted in non-default order in the field list. |
void |
setName(String value)
Gets the name of the
PivotTable . |
void |
setPageOverThenDown(boolean pageOverThenDown)
Sets a value indicating how the page fields are laid out when there are multiple
PivotField s in the page area. |
void |
setRowGrandTotals(boolean rowGrandTotals)
Sets a value indicating whether the
PivotTable shows grand totals for rows. |
void |
setRowHeaderCaption(String rowHeaderCaption)
Sets the
String to be displayed in row header in compact mode. |
void |
setStyleOptions(EnumSet<PivotTableStyleOptions> styleOptions)
Sets the options to be applied to the
PivotTable style. |
public BuiltInPivotStyleName getBuiltInStyle()
PivotTable
.PivotTable
.public PivotAreaFieldCollection getColumnFields()
PivotField
s that are currently shown as column fields.PivotField
s that are currently shown as column fields.public String getColumnHeaderCaption()
String
to be displayed in column header in compact mode.String
to be displayed in column header in compact mode.public PivotAreaFieldCollection getDataFields()
PivotField
s that are currently shown as data fields.PivotField
s that are currently shown as data fields.public PivotField getDataPivotField()
Gets a PivotField
object that represents all the data fields in this PivotTable
.
It contains non-null
value only if there are two or more data fields in the getDataFields()
.
It is only used to add pivot field to the PivotTable
row/column area.
By default, it is added in the row area (automatically, when there are two or more data fields in the getDataFields()
).
PivotField
object that represents all the data fields in this PivotTable
if there are two or more data fields in the getDataFields()
or null
otherwise.public PivotTableFilter getFilter()
PivotTableFilter
.PivotTableFilter
.public String getName()
PivotTable
.PivotTable
.public PivotAreaFieldCollection getPageFields()
PivotField
s that are currently showing as page fields.PivotField
s that are currently showing as page fields.public PivotCache getPivotCache()
PivotCache
associated with this PivotTable
.PivotCache
associated with this PivotTable
.public PivotFieldCollection getPivotFields()
PivotField
s.PivotField
s.public PivotAreaFieldCollection getRowFields()
PivotField
s that are currently showing as row fields.PivotField
s that are currently showing as row fields.public String getRowHeaderCaption()
String
to be displayed in row header in compact mode.String
to be displayed in row header in compact mode.public EnumSet<PivotTableStyleOptions> getStyleOptions()
PivotTable
style.PivotTable
style.public ExcelCell getTopLeftCell()
PivotTable
in the worksheet.PivotTable
in the worksheet.public boolean isClassicPivotTableLayout()
PivotTable
display.true
to display PivotTable
with classic layout; otherwise, false
.public boolean isColumnGrandTotals()
PivotTable
shows grand totals for columns.true
to show grand totals for columns; otherwise, false
.public boolean isFieldListSortAscending()
PivotTable
are sorted in non-default order in the field list.true
if fields in the PivotTable
are sorted in non-default order in the field list; otherwise, false
.public boolean isPageOverThenDown()
PivotField
s in the page area.true
to lay out page fields over then down; otherwise, false
.public boolean isRowGrandTotals()
PivotTable
shows grand totals for rows.true
to show grand totals for rows; otherwise, false
.public void setBuiltInStyle(BuiltInPivotStyleName builtInStyle)
PivotTable
.builtInStyle
- The built-in style used in the body of the PivotTable
.public void setClassicPivotTableLayout(boolean classicPivotTableLayout)
PivotTable
display.classicPivotTableLayout
- true
to display PivotTable
with classic layout; otherwise, false
.public void setColumnGrandTotals(boolean columnGrandTotals)
PivotTable
shows grand totals for columns.columnGrandTotals
- true
to show grand totals for columns; otherwise, false
.public void setColumnHeaderCaption(String columnHeaderCaption)
String
to be displayed in column header in compact mode.columnHeaderCaption
- The String
to be displayed in column header in compact mode.public void setFieldListSortAscending(boolean fieldListSortAscending)
PivotTable
are sorted in non-default order in the field list.fieldListSortAscending
- true
if fields in the PivotTable
are sorted in non-default order in the field list; otherwise, false
.public void setName(String value)
PivotTable
.value
- The name of the PivotTable
.IllegalArgumentException
- Value cannot be null
or empty string.public void setPageOverThenDown(boolean pageOverThenDown)
PivotField
s in the page area.pageOverThenDown
- true
to lay out page fields over then down; otherwise, false
.public void setRowGrandTotals(boolean rowGrandTotals)
PivotTable
shows grand totals for rows.rowGrandTotals
- true
to show grand totals for rows; otherwise, false
.public void setRowHeaderCaption(String rowHeaderCaption)
String
to be displayed in row header in compact mode.rowHeaderCaption
- The String
to be displayed in row header in compact mode.public void setStyleOptions(EnumSet<PivotTableStyleOptions> styleOptions)
PivotTable
style.styleOptions
- The options to be applied to the PivotTable
style.© GemBox Ltd. — All rights reserved.