public final class PivotField extends Object
PivotTable
.
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:
PivotTable.getPageFields()
collection.
PivotTable.getRowFields()
collection.
PivotTable.getColumnFields()
collection.
PivotTable.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 PivotTable.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 isCalculated()
tells if a field is calculated or is a field from the data source.
Calculated PivotField
is automatically moved to a Values Area (PivotTable.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 PivotTable.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 |
---|---|
void |
clearSort()
Removes the sort options from this pivot field.
|
void |
collapse(boolean collapsed)
Collapse/expand entire
PivotField . |
PivotField |
getBaseField()
Gets the base field used for a custom calculation.
|
PivotItem |
getBaseItem()
Gets the item in the
getBaseField() used for a custom calculation. |
PivotItemPosition |
getBaseItemPosition()
Gets the item position in the
getBaseField() used for a custom calculation. |
PivotItem |
getCurrentPageItem()
Gets the current page item showing for the page field.
|
String |
getFormula()
Gets the
PivotField 's formula in A1-style notation. |
PivotFieldCalculationType |
getFunction()
Gets the function used to summarize/aggregate this
PivotField . |
PivotFieldLayoutForm |
getLayoutForm()
Gets the layout form for this
PivotField . |
String |
getName()
Gets the custom name (label text) of this
PivotField . |
String |
getNumberFormat()
Gets the number format which indicates how to format the numeric value of this field.
|
PivotItemCollection |
getPivotItems()
Gets the collection of all (visible and hidden)
PivotItem s in this PivotField . |
PivotTable |
getPivotTable()
Gets the parent
PivotTable . |
PivotFieldDisplayFormat |
getShowDataAs()
Gets the display format for this
PivotField . |
PivotTableSortOptions |
getSortOptions()
Gets the sort options of this pivot field.
|
String |
getSourceName()
Gets the source name (cache field name) of this
PivotField . |
EnumSet<PivotFieldSubtotalTypes> |
getSubtotals()
Gets the subtotals displayed with this
PivotField . |
boolean |
isCalculated()
Gets a value indicating whether this
PivotField is a calculated field. |
boolean |
isDragToColumn()
Gets a value indicating whether this field can be dragged to the column position.
|
boolean |
isDragToData()
Gets a value indicating whether this field can be dragged to the data position.
|
boolean |
isDragToHide()
Gets a value indicating whether this field can be dragged to the hide position.
|
boolean |
isDragToPage()
Gets a value indicating whether this field can be dragged to the page position.
|
boolean |
isDragToRow()
Gets a value indicating whether this field can be dragged to the row position.
|
boolean |
isEnableMultiplePageItems()
Gets a value indicating whether this field can have multiple items selected in the page field.
|
boolean |
isInsertBlankRow()
Gets a value indicating whether to add a blank row after each item.
|
boolean |
isShowAll()
Gets a value indicating whether to show all items for this field.
|
void |
setBaseField(PivotField field)
Sets the base field used for a custom calculation.
|
void |
setBaseItem(PivotItem value)
Sets the item in the
getBaseField() used for a custom calculation. |
void |
setBaseItemPosition(PivotItemPosition value)
Sets the item position in the
getBaseField() used for a custom calculation. |
void |
setCurrentPageItem(PivotItem item)
Gets the current page item showing for the page field.
|
void |
setDragToColumn(boolean dragToColumn)
Sets a value indicating whether this field can be dragged to the column position.
|
void |
setDragToData(boolean dragToData)
Sets a value indicating whether this field can be dragged to the data position.
|
void |
setDragToHide(boolean dragToHide)
Sets a value indicating whether this field can be dragged to the hide position.
|
void |
setDragToPage(boolean dragToPage)
Sets a value indicating whether this field can be dragged to the page position.
|
void |
setDragToRow(boolean dragToRow)
Sets a value indicating whether this field can be dragged to the row position.
|
void |
setEnableMultiplePageItems(boolean enableMultiplePageItems)
Sets a value indicating whether this field can have multiple items selected in the page field.
|
void |
setFunction(PivotFieldCalculationType calculationType)
Sets the function used to summarize/aggregate this
PivotField . |
void |
setInsertBlankRow(boolean insertBlankRow)
Sets a value indicating whether to add a blank row after each item.
|
void |
setLayoutForm(PivotFieldLayoutForm layoutForm)
Sets the layout form for this
PivotField . |
void |
setName(String name)
Sets the custom name (label text) of this
PivotField . |
void |
setNumberFormat(String format)
Gets the number format which indicates how to format the numeric value of this field.
|
void |
setShowAll(boolean showAll)
Sets a value indicating whether to show all items for this field.
|
void |
setShowDataAs(PivotFieldDisplayFormat displayFormat)
Sets the display format for this
PivotField . |
void |
setSubtotals(EnumSet<PivotFieldSubtotalTypes> subtotalTypes)
Sets the subtotals displayed with this
PivotField . |
void |
sort(boolean descending)
Sorts this pivot field by the pivot items in it.
|
void |
sort(boolean descending,
PivotField sortByDataField,
PivotItem... sortByPivotItems)
Sorts this pivot field by the data field.
|
public void clearSort()
public void collapse(boolean collapsed)
PivotField
.collapsed
- true
if entire PivotField
is collapsed; otherwise, false
.public PivotField getBaseField()
Gets the base field used for a custom calculation.
This method is applicable only for data fields.
public PivotItem getBaseItem()
getBaseField()
used for a custom calculation.
This method is applicable only for data fields.
getBaseField()
used for a custom calculation.public PivotItemPosition getBaseItemPosition()
Gets the item position in the getBaseField()
used for a custom calculation.
This method is applicable only for data fields.
getBaseField()
used for a custom calculation.public PivotItem getCurrentPageItem()
Gets the current page item showing for the page field.
This method is applicable only for data fields.
public String getFormula()
PivotField
's formula in A1-style notation.PivotField
's formula in A1-style notation.public PivotFieldCalculationType getFunction()
Gets the function used to summarize/aggregate this PivotField
.
This method is applicable only for data fields.
PivotField
.public PivotFieldLayoutForm getLayoutForm()
PivotField
.PivotField
.public String getName()
PivotField
.PivotField
.public String getNumberFormat()
Gets the number format which indicates how to format the numeric value of this field.
Default value is General.
public PivotItemCollection getPivotItems()
PivotItem
s in this PivotField
.PivotItem
s in this PivotField
.public PivotTable getPivotTable()
PivotTable
.PivotTable
.public PivotFieldDisplayFormat getShowDataAs()
Gets the display format for this PivotField
.
This method is applicable only for data fields.
PivotField
.public PivotTableSortOptions getSortOptions()
public String getSourceName()
PivotField
.PivotField
.public EnumSet<PivotFieldSubtotalTypes> getSubtotals()
Gets the subtotals displayed with this PivotField
.
This method is applicable only for non-data fields.
PivotField
.public boolean isCalculated()
PivotField
is a calculated field.true
if this PivotField
is a calculated field; otherwise, false
.public boolean isDragToColumn()
true
if this field can be dragged to the column position; otherwise, false
.public boolean isDragToData()
true
if this field can be dragged to the data position; otherwise, false
.public boolean isDragToHide()
true
if this field can be dragged to the hide position; otherwise, false
.public boolean isDragToPage()
true
if this field can be dragged to the page position; otherwise, false
.public boolean isDragToRow()
true
if this field can be dragged to the row position; otherwise, false
.public boolean isEnableMultiplePageItems()
true
if this field can have multiple items selected in the page field; otherwise, false
.public boolean isInsertBlankRow()
true
if a blank row will be inserted after each item; otherwise, false
.public boolean isShowAll()
true
to show all items for this field; otherwise, false
.public void setBaseField(PivotField field)
Sets the base field used for a custom calculation.
This method is applicable only for data fields.
field
- The base field used for a custom calculation.NullPointerException
- Value can't be null
.IllegalArgumentException
- Base field is not part of the parent PivotTable
.public void setBaseItem(PivotItem value)
Sets the item in the getBaseField()
used for a custom calculation.
This method is applicable only for data fields.
Use setBaseItemPosition(com.gembox.spreadsheet.pivottables.PivotItemPosition)
method to specify PivotItemPosition.PREVIOUS
or PivotItemPosition.NEXT
item, otherwise use this method.
value
- The item in the getBaseField()
used for a custom calculation.NullPointerException
- Value can't be null
.IllegalArgumentException
- getBaseField()
is null
.IllegalArgumentException
- Base item is not part of the getBaseField()
.public void setBaseItemPosition(PivotItemPosition value)
Sets the item position in the getBaseField()
used for a custom calculation.
This method is applicable only for data fields.
Use this method to specify PivotItemPosition.PREVIOUS
or PivotItemPosition.NEXT
item, otherwise use getBaseItem()
method.
value
- The item position in the getBaseField()
used for a custom calculation.NullPointerException
- getBaseField()
is null
.public void setCurrentPageItem(PivotItem item)
Gets the current page item showing for the page field.
This method is applicable only for page fields.
If set to null
, no current page item will be defined.
item
- The current page item showing for the page field.IllegalArgumentException
- Page item is not part of this page field.public void setDragToColumn(boolean dragToColumn)
dragToColumn
- true
if this field can be dragged to the column position; otherwise, false
.public void setDragToData(boolean dragToData)
dragToData
- true
if this field can be dragged to the data position; otherwise, false
.public void setDragToHide(boolean dragToHide)
dragToHide
- true
if this field can be dragged to the hide position; otherwise, false
.public void setDragToPage(boolean dragToPage)
dragToPage
- true
if this field can be dragged to the page position; otherwise, false
.public void setDragToRow(boolean dragToRow)
dragToRow
- true
if this field can be dragged to the row position; otherwise, false
.public void setEnableMultiplePageItems(boolean enableMultiplePageItems)
enableMultiplePageItems
- true
if this field can have multiple items selected in the page field; otherwise, false
.public void setFunction(PivotFieldCalculationType calculationType)
Sets the function used to summarize/aggregate this PivotField
.
This method is applicable only for data fields.
calculationType
- The function used to summarize/aggregate this PivotField
.public void setInsertBlankRow(boolean insertBlankRow)
insertBlankRow
- true
if a blank row will be inserted after each item; otherwise, false
.public void setLayoutForm(PivotFieldLayoutForm layoutForm)
PivotField
.layoutForm
- The layout form for this PivotField
.public void setName(String name)
PivotField
.name
- The custom name (label text) of this PivotField
.IllegalArgumentException
- Value cannot be null
or empty string.public void setNumberFormat(String format)
Gets the number format which indicates how to format the numeric value of this field.
Default value is General.
If set to null
, number format will be set to default.
format
- The number format which indicates how to format the numeric value of this field.public void setShowAll(boolean showAll)
showAll
- true
to show all items for this field; otherwise, false
.public void setShowDataAs(PivotFieldDisplayFormat displayFormat)
Sets the display format for this PivotField
.
This method is applicable only for data fields.
displayFormat
- The display format for this PivotField
.public void setSubtotals(EnumSet<PivotFieldSubtotalTypes> subtotalTypes)
Sets the subtotals displayed with this PivotField
.
This method is applicable only for non-data fields.
subtotalTypes
- The subtotals displayed with this PivotField
.public void sort(boolean descending)
descending
- True to sort in descending order, false otherwise.public void sort(boolean descending, PivotField sortByDataField, PivotItem... sortByPivotItems)
Sorts this pivot field by the data field.
Pivot items can be used to specify the row (or column) that will be used to sort the pivot field.
descending
- True to sort in descending order, false otherwise.sortByDataField
- Data field that is used to sort this pivot field.sortByPivotItems
- Pivot items that specify the row (or column) that will be used to sort the pivot field.© GemBox d.o.o. — All rights reserved.