Home  Products  Purchase  Downloads  Demos  Forums  Blogs  Ticket  Wiki  API  Corporate


com.aspose.cells
Class PivotTable

java.lang.Object
  extended by com.aspose.cells.PivotTable

public class PivotTable
extends java.lang.Object

Represents a PivotTable report on a worksheet.


Method Summary
 void addCalculatedField(java.lang.String name, java.lang.String formula, boolean dragToDataArea)
          Adds a calclulated field to pivot field.
 int addFieldToArea(byte fieldType, int baseFieldIndex)
          Adds the field to the specific area.
 int addFieldToArea(byte fieldType, PivotField pivotField)
          Adds the field to the specific area.
 boolean displayErrorString()
          Indicates whether the PivotTable report displays a custom string in cells that contain errors.
 boolean displayImmediateItems()
          Indicates whether items in the row and column areas are visible when the data area of the PivotTable is empty.
 boolean displayNullString()
          Indicates whether the PivotTable report displays a custom string in cells that contain null values.
 boolean enableDrilldown()
          Gets whether drilldown is enabled.
 boolean enableFieldDialog()
          Indicates whether the PivotTable Field dialog box is available when the user double-clicks the PivotTable field.
 boolean enableFieldList()
          Gets whether enable to display the field list for the PivotTable.
 boolean enableWizard()
          Indicates whether the PivotTable Wizard is available.
 short getAutoFormatType()
          Gets the PivotTable atuo format type.
 PivotFields getBaseFields()
          Returns a PivotFields object that includes all fields in the PivotTable report.
 PivotFields getColumnFields()
          Returns a PivotFields object that are currently shown as column fields.
 CellArea getColumnRange()
          Returns a CellArea object that represents the range that contains the column area in the PivotTable report.
 CellArea getDataBordyRange()
          Returns a CellArea object that represents the range that contains the data area in the list between the header row and the insert row.
 PivotField getDataField()
          Gets a PivotField object that represents all the data fields in a PivotTable.
 PivotFields getDataFields()
          Returns a PivotFields object that are currently shown as data fields.
 java.lang.String getErrorString()
          Gets the string displayed in cells that contain errors when the DisplayErrorString property is true.The default value is an empty string.
 PivotFields getFields(byte fieldType)
          Gets the specific fields by the field type.
 java.lang.String getName()
          Gets the name of the PivotTable
 java.lang.String getNullString()
          Gets the string displayed in cells that contain null values when the DisplayNullString property is true.The default value is an empty string.
 byte getPageFieldOrder()
          Gets the order in which page fields are added to the PivotTable report's layout.
 PivotFields getPageFields()
          Returns a PivotFields object that are currently shown as page fields.
 int getPageFieldWrapCount()
          Gets the number of page fields in each column or row in the PivotTable report.
 PivotFields getRowFields()
          Returns a PivotFields object that are currently shown as row fields.
 CellArea getRowRange()
          Returns a CellArea object that represents the range that contains the row area in the PivotTable report.
 CellArea getTableRange1()
          Returns a CellArea object that represents the range containing the entire PivotTable report, but doesn't include page fields.
 CellArea getTableRange2()
          Returns a CellArea object that represents the range containing the entire PivotTable report, includes page fields.
 java.lang.String getTag()
          Gets a string saved with the PivotTable report.
 boolean isAutoFormat()
          Indicates whether the PivotTable report is automatically formatted.
 boolean isColumnGrand()
          Indicates whether the PivotTable report shows grand totals for columns.
 boolean isRowGrand()
          Indicates whether the PivotTable report shows grand totals for rows.
 boolean isSelected()
          Indicates whether the PivotTable is selected.
 boolean manualUpdate()
          Indicates whether the PivotTable report is recalculated only at the user's request.
 boolean mergeLabels()
          Indicates whether the specified PivotTable report's outer-row item, column item, subtotal, and grand total labels use merged cells.
 void move(int row, int column)
          Moves the PivotTable to a different location in the worksheet.
 void move(java.lang.String destCellName)
          Moves the PivotTable to a different location in the worksheet.
 boolean preserveFormatting()
          Indicates whether formatting is preserved when the PivotTable is refreshed or recalculated.
 boolean printTitles()
          Indicates whether the print titles for the worksheet are set based on the PivotTable report.
 boolean saveData()
          Indicates whether data for the PivotTable report is saved with the workbook.
 void setAutoFormat(boolean isAutoFormat)
          Sets whether the PivotTable report is automatically formatted.
 void setAutoFormatType(short autoFormatType)
          Sets the PivotTable atuo format type.Only effect when isAutoFormat is true.
 void setAutoGroupField(int baseFieldIndex)
          Sets auto field group by the PivotTable.
 void setAutoGroupField(PivotField pivotField)
          Sets auto field group by the PivotTable.
 void setColumnGrand(boolean isColumnGrand)
          Sets whether the PivotTable report shows grand totals for columns.
 void setDisplayErrorString(boolean displayErrorString)
          Sets whether the PivotTable report displays a custom string in cells that contain errors.
 void setDisplayImmediateItems(boolean displayImmediateItems)
          Sets indicates whether items in the row and column areas are visible when the data area of the PivotTable is empty.
 void setDisplayNullString(boolean displayNullString)
          Sets whether the PivotTable report displays a custom string in cells that contain null values.
 void setEnableDrilldown(boolean enableDrilldown)
          Sets whether drilldown is enabled.
 void setEnableFieldDialog(boolean enableFieldDialog)
          Indicates whether the PivotTable Field dialog box is available when the user double-clicks the PivotTable field.
 void setEnableFieldList(boolean enableFieldList)
          Sets whether enable to display the field list for the PivotTable.
 void setEnableWizard(boolean enableWizard)
          Sets whether the PivotTable Wizard is available.
 void setErrorString(java.lang.String errorString)
          Returns or sets the string displayed in cells that contain errors.
 void setManualGroupField(int baseFieldIndex, java.lang.Object startVal, java.lang.Object endVal, java.util.ArrayList<java.lang.Byte> groupByList, java.lang.Number intervalNum)
          Sets manual field group by the PivotTable.
 void setManualGroupField(PivotField pivotField, java.lang.Object startVal, java.lang.Object endVal, java.util.ArrayList<java.lang.Byte> groupByList, java.lang.Number intervalNum)
          Sets manual field group by the PivotTable.
 void setManualUpdate(boolean manualUpdate)
          Sets whether the PivotTable report is recalculated only at the user's request.
 void setMergeLabels(boolean mergeLabels)
          Sets whether the specified PivotTable report's outer-row item, column item, subtotal, and grand total labels use merged cells.
 void setName(java.lang.String name)
          Sets the name of the PivotTable.
 void setNullString(java.lang.String nullString)
          Returns or sets the string displayed in cells that contain null values when the DisplayNullString property is true.
 void setPageFieldOrder(byte pageFieldOrder)
          Sets the order in which page fields are added to the PivotTable report's layout.
 void setPageFieldWrapCount(int pageFieldWrapCount)
          Sets the number of page fields in each column or row in the PivotTable report.
 void setPreserveFormatting(boolean preserveFormatting)
          Sets whether formatting is preserved when the PivotTable is refreshed or recalculated.
 void setPrintTitles(boolean printTitles)
          Sets whether the print titles for the worksheet are set based on the PivotTable report.
 void setRowGrand(boolean isRowGrand)
          Sets whether the PivotTable report shows grand totals for rows.
 void setSaveData(boolean saveData)
          Sets whether data for the PivotTable report is saved with the workbook.
 void setSelected(boolean isSelected)
          Sets whether the PivotTable is selected.Default is false.
 void setSubtotalHiddenPageItems(boolean subtotalHiddenPageItems)
          Sets whether hidden page field items in the PivotTable report;
 void setTag(java.lang.String tag)
          Sets a string saved with the PivotTable report.
 void setUngroup(int baseFieldIndex)
          Sets ungroup by the PivotTable.
 void setUngroup(PivotField pivotField)
          Sets ungroup by the PivotTable.
 boolean subtotalHiddenPageItems()
          Indicates whether hidden page field items in the PivotTable report are included in row and column subtotals, block totals, and grand totals.
 
Methods inherited from class java.lang.Object
equals, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait
 

Method Detail

addFieldToArea

public int addFieldToArea(byte fieldType,
                          int baseFieldIndex)
Adds the field to the specific area.

Parameters:
fieldType - the fields area type.
baseFieldIndex - the field index in the base fields.
Returns:
the field position in the specific fields.
See Also:
addFieldToArea(byte, PivotField)

addFieldToArea

public int addFieldToArea(byte fieldType,
                          PivotField pivotField)
Adds the field to the specific area.

Parameters:
fieldType - the fields area type.It could be one of the following values:
PivotFieldType.ROW
PivotFieldType.COLUMN
PivotFieldType.DATA
PivotFieldType.PAGE
pivotField - the field in the base fields.
Returns:
the field position in the specific fields.

addCalculatedField

public void addCalculatedField(java.lang.String name,
                               java.lang.String formula,
                               boolean dragToDataArea)
Adds a calclulated field to pivot field.

Parameters:
name - The name of the calculated field
formula - The formula of the calculated field.
dragToDataArea - drag this field to data area immediately if true.

getFields

public PivotFields getFields(byte fieldType)
Gets the specific fields by the field type.

Parameters:
fieldType - the field type.
Returns:
the specific fields object.
See Also:
addFieldToArea(byte, PivotField)

getColumnFields

public PivotFields getColumnFields()
Returns a PivotFields object that are currently shown as column fields.

Returns:
a PivotFields object.

getRowFields

public PivotFields getRowFields()
Returns a PivotFields object that are currently shown as row fields.

Returns:
a PivotFields object.

getPageFields

public PivotFields getPageFields()
Returns a PivotFields object that are currently shown as page fields.

Returns:
a PivotFields object.

getDataFields

public PivotFields getDataFields()
Returns a PivotFields object that are currently shown as data fields.

Returns:
a PivotFields object.

getBaseFields

public PivotFields getBaseFields()
Returns a PivotFields object that includes all fields in the PivotTable report.

Returns:
a PivotFields object.

getColumnRange

public CellArea getColumnRange()
Returns a CellArea object that represents the range that contains the column area in the PivotTable report. Read-only.

Returns:
a CellArea object.

getRowRange

public CellArea getRowRange()
Returns a CellArea object that represents the range that contains the row area in the PivotTable report. Read-only.

Returns:
a CellArea object.

getDataBordyRange

public CellArea getDataBordyRange()
Returns a CellArea object that represents the range that contains the data area in the list between the header row and the insert row. Read-only.

Returns:
a CellArea object.

getDataField

public PivotField getDataField()
Gets a PivotField object that represents all the data fields in a PivotTable. Read-only.It would be init only when there are two or more data fields in the DataPiovtField. It only use to add DataPivotField to the PivotTable row/column area . Default is in row area.

Returns:
a PivotField object that represents all the data fields in a PivotTable.

getTableRange1

public CellArea getTableRange1()
Returns a CellArea object that represents the range containing the entire PivotTable report, but doesn't include page fields. Read-only.

Returns:
a CellArea object.

getTableRange2

public CellArea getTableRange2()
Returns a CellArea object that represents the range containing the entire PivotTable report, includes page fields. Read-only.

Returns:
a CellArea object.

move

public void move(int row,
                 int column)
Moves the PivotTable to a different location in the worksheet.

Parameters:
row - row index.
column - column index.
Throws:
java.lang.IllegalArgumentException - if one of the indexes is out of range.

move

public void move(java.lang.String destCellName)
Moves the PivotTable to a different location in the worksheet.

Parameters:
destCellName - the dest cell name.
Throws:
java.lang.IllegalArgumentException - if the cell name is invalid.

isColumnGrand

public boolean isColumnGrand()
Indicates whether the PivotTable report shows grand totals for columns.

Returns:
whether the PivotTable report shows grand totals for columns.

setColumnGrand

public void setColumnGrand(boolean isColumnGrand)
Sets whether the PivotTable report shows grand totals for columns.

Parameters:
isColumnGrand - whether the PivotTable report shows grand totals for columns.

isRowGrand

public boolean isRowGrand()
Indicates whether the PivotTable report shows grand totals for rows.

Returns:
whether the PivotTable report shows grand totals for rows.

setRowGrand

public void setRowGrand(boolean isRowGrand)
Sets whether the PivotTable report shows grand totals for rows.

Parameters:
isRowGrand - whether the PivotTable report shows grand totals for rows.

displayNullString

public boolean displayNullString()
Indicates whether the PivotTable report displays a custom string in cells that contain null values.

Returns:
whether displays a custom string in cells that contain null values.

setDisplayNullString

public void setDisplayNullString(boolean displayNullString)
Sets whether the PivotTable report displays a custom string in cells that contain null values.

Parameters:
displayNullString - whether displays a custom string in cells that contain null values.

getNullString

public java.lang.String getNullString()
Gets the string displayed in cells that contain null values when the DisplayNullString property is true.The default value is an empty string.

Returns:
the string displayed in cells that contain null values

setNullString

public void setNullString(java.lang.String nullString)
Returns or sets the string displayed in cells that contain null values when the DisplayNullString property is true.

Parameters:
nullString - the string displayed in cells that contain null values.

displayErrorString

public boolean displayErrorString()
Indicates whether the PivotTable report displays a custom string in cells that contain errors.

Returns:
whether displays a custom string in cells that contain errors.

setDisplayErrorString

public void setDisplayErrorString(boolean displayErrorString)
Sets whether the PivotTable report displays a custom string in cells that contain errors.

Parameters:
displayErrorString - whether displays a custom string in cells that contain errors.

getErrorString

public java.lang.String getErrorString()
Gets the string displayed in cells that contain errors when the DisplayErrorString property is true.The default value is an empty string.

Returns:
the string displayed in cells that contain errors

setErrorString

public void setErrorString(java.lang.String errorString)
Returns or sets the string displayed in cells that contain errors. when the DisplayErrorString property is true.

Parameters:
errorString - the string displayed in cells that contain errors.

isAutoFormat

public boolean isAutoFormat()
Indicates whether the PivotTable report is automatically formatted.

Returns:
whether the PivotTable report is automatically formatted.

setAutoFormat

public void setAutoFormat(boolean isAutoFormat)
Sets whether the PivotTable report is automatically formatted.

Parameters:
isAutoFormat - whether the PivotTable report is automatically formatted.

getAutoFormatType

public short getAutoFormatType()
Gets the PivotTable atuo format type.

Returns:
the PivotTable atuo format type.
See Also:
PivotTableAutoFormatType

setAutoFormatType

public void setAutoFormatType(short autoFormatType)
Sets the PivotTable atuo format type.Only effect when isAutoFormat is true.

Parameters:
autoFormatType - the PivotTable atuo format type.
See Also:
PivotTableAutoFormatType

mergeLabels

public boolean mergeLabels()
Indicates whether the specified PivotTable report's outer-row item, column item, subtotal, and grand total labels use merged cells.

Returns:
whether the specified PivotTable report's item labels use merged cells.

setMergeLabels

public void setMergeLabels(boolean mergeLabels)
Sets whether the specified PivotTable report's outer-row item, column item, subtotal, and grand total labels use merged cells.

Parameters:
mergeLabels - whether the specified PivotTable report's item labels use merged cells.

preserveFormatting

public boolean preserveFormatting()
Indicates whether formatting is preserved when the PivotTable is refreshed or recalculated.

Returns:
whether formatting is preserved when the PivotTable is refreshed or recalculated.

setPreserveFormatting

public void setPreserveFormatting(boolean preserveFormatting)
Sets whether formatting is preserved when the PivotTable is refreshed or recalculated.

Parameters:
preserveFormatting - whether formatting is preserved when the PivotTable is refreshed or recalculated.

enableDrilldown

public boolean enableDrilldown()
Gets whether drilldown is enabled.

Returns:
whether drilldown is enabled.

setEnableDrilldown

public void setEnableDrilldown(boolean enableDrilldown)
Sets whether drilldown is enabled.

Parameters:
enableDrilldown - whether drilldown is enabled.

enableFieldDialog

public boolean enableFieldDialog()
Indicates whether the PivotTable Field dialog box is available when the user double-clicks the PivotTable field.

Returns:
whether the PivotTable Field dialog box is available.

setEnableFieldDialog

public void setEnableFieldDialog(boolean enableFieldDialog)
Indicates whether the PivotTable Field dialog box is available when the user double-clicks the PivotTable field.

Parameters:
enableFieldDialog - whether the PivotTable Field dialog box is available.

enableFieldList

public boolean enableFieldList()
Gets whether enable to display the field list for the PivotTable.

Returns:
whether enable to display the field list for the PivotTable.

setEnableFieldList

public void setEnableFieldList(boolean enableFieldList)
Sets whether enable to display the field list for the PivotTable.

Parameters:
enableFieldList - whether enable to display the field list for the PivotTable.

enableWizard

public boolean enableWizard()
Indicates whether the PivotTable Wizard is available.

Returns:
whether the PivotTable Wizard is available.

setEnableWizard

public void setEnableWizard(boolean enableWizard)
Sets whether the PivotTable Wizard is available.

Parameters:
enableWizard - whether the PivotTable Wizard is available.

subtotalHiddenPageItems

public boolean subtotalHiddenPageItems()
Indicates whether hidden page field items in the PivotTable report are included in row and column subtotals, block totals, and grand totals. The default value is False.

Returns:
whether hidden page field items

setSubtotalHiddenPageItems

public void setSubtotalHiddenPageItems(boolean subtotalHiddenPageItems)
Sets whether hidden page field items in the PivotTable report;

Parameters:
subtotalHiddenPageItems - whether hidden page field items

manualUpdate

public boolean manualUpdate()
Indicates whether the PivotTable report is recalculated only at the user's request.

Returns:
whether the PivotTable report is recalculated only at the user's request.

setManualUpdate

public void setManualUpdate(boolean manualUpdate)
Sets whether the PivotTable report is recalculated only at the user's request.

Parameters:
manualUpdate - whether the PivotTable report is recalculated only at the user's request.

getName

public java.lang.String getName()
Gets the name of the PivotTable

Returns:
the name of the PivotTable.

setName

public void setName(java.lang.String name)
Sets the name of the PivotTable.

Parameters:
name - the name of the PivotTable.

getPageFieldOrder

public byte getPageFieldOrder()
Gets the order in which page fields are added to the PivotTable report's layout.

Returns:
the order in which page fields are added to the PivotTable report's layout.
See Also:
setPageFieldOrder(byte)

setPageFieldOrder

public void setPageFieldOrder(byte pageFieldOrder)
Sets the order in which page fields are added to the PivotTable report's layout.

Parameters:
pageFieldOrder - the order in which page fields are added to the PivotTable report's layout. It could be one of the following values:
OrderType.DOWN_THEN_OVER
OrderType.OVER_THEN_DOWN
Throws:
java.lang.IllegalArgumentException - if page field order type is not valid.

getPageFieldWrapCount

public int getPageFieldWrapCount()
Gets the number of page fields in each column or row in the PivotTable report.

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

setPageFieldWrapCount

public void setPageFieldWrapCount(int pageFieldWrapCount)
Sets the number of page fields in each column or row in the PivotTable report.

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

getTag

public java.lang.String getTag()
Gets a string saved with the PivotTable report.

Returns:
a string saved with the PivotTable report.

setTag

public void setTag(java.lang.String tag)
Sets a string saved with the PivotTable report.

Parameters:
tag - a string saved with the PivotTable report.

saveData

public boolean saveData()
Indicates whether data for the PivotTable report is saved with the workbook.

Returns:
whether data for the PivotTable report is saved with the workbook.

setSaveData

public void setSaveData(boolean saveData)
Sets whether data for the PivotTable report is saved with the workbook.

Parameters:
saveData - whether data for the PivotTable report is saved with the workbook.

printTitles

public boolean printTitles()
Indicates whether the print titles for the worksheet are set based on the PivotTable report. The default value is false.

Returns:
whether the print titles for the worksheet are set based on the PivotTable report.

setPrintTitles

public void setPrintTitles(boolean printTitles)
Sets whether the print titles for the worksheet are set based on the PivotTable report. The default value is false.

Parameters:
printTitles - whether the print titles for the worksheet are set based on the PivotTable report.

displayImmediateItems

public boolean displayImmediateItems()
Indicates whether items in the row and column areas are visible when the data area of the PivotTable is empty. The default value is true.

Returns:
whether items in the row and column areas are visible when the data area of the PivotTable is empty.

setDisplayImmediateItems

public void setDisplayImmediateItems(boolean displayImmediateItems)
Sets indicates whether items in the row and column areas are visible when the data area of the PivotTable is empty. The default value is true.

Parameters:
displayImmediateItems - whether items in the row and column areas are visible when the data area of the PivotTable is empty.

isSelected

public boolean isSelected()
Indicates whether the PivotTable is selected.

Returns:
whether the PivotTable is selected.

setSelected

public void setSelected(boolean isSelected)
Sets whether the PivotTable is selected.Default is false.

Parameters:
isSelected - whether the PivotTable is selected.

setAutoGroupField

public void setAutoGroupField(int baseFieldIndex)
                       throws java.lang.Exception
Sets auto field group by the PivotTable.

Parameters:
baseFieldIndex - The row or column field index in the base fields.
Throws:
java.lang.Exception - Throws IllegalArgumentException.

setAutoGroupField

public void setAutoGroupField(PivotField pivotField)
                       throws java.lang.Exception
Sets auto field group by the PivotTable.

Parameters:
pivotField - The row or column field in the specific fields.
Throws:
java.lang.Exception - Throws IllegalArgumentException.

setManualGroupField

public void setManualGroupField(int baseFieldIndex,
                                java.lang.Object startVal,
                                java.lang.Object endVal,
                                java.util.ArrayList<java.lang.Byte> groupByList,
                                java.lang.Number intervalNum)
                         throws java.lang.Exception
Sets manual field group by the PivotTable.

Parameters:
baseFieldIndex - The row or column field index in the base fields.
startVal - Specifies the starting value for date or numeric grouping. Object type must be Date or Number.
endVal - Specifies the ending value for date or numeric grouping. Object type must be Date or Number.
groupByList - Specifies the grouping type list. Specified by PivotTableGroupType.
intervalNum - Specifies the interval number group by in days or numeric grouping. The number of days must be positive integer of nonzero.
Throws:
java.lang.Exception - Throws IllegalArgumentException or ParseException.

setManualGroupField

public void setManualGroupField(PivotField pivotField,
                                java.lang.Object startVal,
                                java.lang.Object endVal,
                                java.util.ArrayList<java.lang.Byte> groupByList,
                                java.lang.Number intervalNum)
                         throws java.lang.Exception
Sets manual field group by the PivotTable.

Parameters:
pivotField - The row or column field in the specific fields.
startVal - Specifies the starting value for date or numeric grouping. Object type must be Date or Number.
endVal - Specifies the ending value for date or numeric grouping. Object type must be Date or Number.
groupByList - Specifies the grouping type list. Specified by PivotTableGroupType.
intervalNum - Specifies the interval number group by in days or numeric grouping. The number of days must be positive integer of nonzero.
Throws:
java.lang.Exception - Throws IllegalArgumentException or ParseException.

setUngroup

public void setUngroup(int baseFieldIndex)
Sets ungroup by the PivotTable.

Parameters:
baseFieldIndex - The row or column field index in the base fields.
Throws:
java.lang.Exception - Throws IllegalArgumentException.

setUngroup

public void setUngroup(PivotField pivotField)
Sets ungroup by the PivotTable.

Parameters:
pivotField - The row or column field in the specific fields.
Throws:
java.lang.Exception - Throws IllegalArgumentException.