Class ExcelPivotTable


  • public class ExcelPivotTable
    extends System.Object
    This class stores pivot table information like name, location, data source, layout & format options, totals & filters, display, printing and data options.
    • Constructor Summary

      Constructors 
      Constructor Description
      ExcelPivotTable()
      Defines an instance of the pivot table.
    • Method Summary

      Modifier and Type Method Description
      void addFieldToColumnLabels​(System.String fieldName)
      Adds the field to column labels area.
      void addFieldToReportFilter​(System.String fieldName)
      Adds the field to report filter area.
      void addFieldToRowLabels​(System.String fieldName)
      Adds the field to row labels area.
      void addFieldToValues​(System.String fieldName, System.String customName, int subtotal)
      Adds the field to values area.
      bool AllowMultipleFiltersPerField()
      Returns true if all values, including those hidden by filtering, are included when the subtotals and the grand total are calculated, false otherwise.
      ExcelPivotTable Clone()
      Creates and returns a copy of this object.
      int ColumnLabelsCount()
      Returns the number of fields in the column labels area.
      int FieldCount()
      Returns the number of fields in the pivot table.
      System.String getColumnHeaderTitle()
      Returns the value to be displayed in column header in compact mode.
      ExcelField getColumnLabelsFieldAt​(int index)
      Returns the field from the specified index in the column labels area.
      System.String getDataHeaderTitle()
      Returns the value to be displayed in value area header.
      System.String getEmptyCellValue()
      Returns the text that is displayed in a cell instead of a blank cell.
      System.String getErrorValue()
      Returns the text that is displayed in a cell instead of the error message.
      ExcelField getField​(System.String name)
      Returns the field having the specified name.
      ExcelField getFieldAt​(int index)
      Returns the field from the specified index.
      System.String getGrandTotalTitle()
      Returns the value to be displayed for grand totals.
      System.String getLocation()
      Returns the range where the pivot table is located.
      System.String getName()
      Returns the pivot table name.
      int getPageWrap()
      Returns the number of fields to display before taking up another column or row.
      ExcelField getReportFilterFieldAt​(int index)
      Returns the field from the specified index in the report filter area.
      System.String getRowHeaderTitle()
      Returns the value to be displayed in row header in compact mode.
      ExcelField getRowLabelsFieldAt​(int index)
      Returns the field from the specified index in the row labels area.
      int getRowLabelsIndent()
      Returns the indent of the rows in the row labels area when the pivot table report is in compact format.
      int getSortOrder()
      Returns the sort order of the fields in pivot table.
      System.String getSourceRange()
      Returns the data source of the pivot table.
      System.String getStyle()
      Returns the style of the pivot table.
      ExcelValueField getValueField​(System.String name)
      Returns the field having the specified name in the values area.
      ExcelValueField getValueFieldAt​(int index)
      Returns the field from the specified index in the values area.
      bool IsAutofitColumnWidthsOnUpdate()
      Returns true if the pivot table columns are adjusted automatically to fit the cell content, false if the current width is kept.
      bool IsClassicLayout()
      Returns true if the classic layout is selected, false otherwise.
      bool IsEnabledShowDetails()
      Returns true if the drilling down to detail data from the data source, and then displaying the data on a new worksheet is enabled, false otherwise.
      bool IsMergeAndCenterCellsWithLabels()
      Returns true if the cells are merged for outer row and column items, false otherwise.
      bool IsPageOverThenDown()
      Returns true if first are displayed the fields in the report filter area from left to right, false if first are displayed the fields in the report filter area from top to bottom
      bool IsPreserveCellFormattingOnUpdate()
      Returns true if the layout and format is preserved each time an operation is performed on the pivot table, false otherwise.
      bool IsUseCustomListsWhenSorting()
      Returns true if the custom lists are used when Excel sorts lists, false otherwise.
      bool PrintDrillButtons()
      Returns true if the expand/collapse buttons are visible when the pivot table is printed, false otherwise.
      bool PrintTitles()
      Returns true if the row and column field headers and column item labels are replicated on each printed page, false otherwise.
      void Refresh​(ExcelDocument xls)
      Refreshes the field names accordingly to the data on the source range.
      void removeFieldFromColumnLabels​(System.String fieldName)
      Removes the field from the column labels area.
      void removeFieldFromReportFilter​(System.String fieldName)
      Removes the field from the report filter area.
      void removeFieldFromRowLabels​(System.String fieldName)
      Removes the field from the row labels area.
      void removeValueFieldFromValues​(System.String customFieldName)
      Removes the field from the values area.
      bool RepeatRowLabels()
      Returns true if the row labels are replicated on each printed page, false otherwise.
      int ReportFilterCount()
      Returns the number of fields in the report filter area.
      int RowLabelsCount()
      Returns the number of fields in the row labels area.
      void setAllowMultipleFiltersPerField​(bool allow)
      Sets if all values, including those hidden by filtering, are included when the subtotals and the grand total are calculated.
      void setAutofitColumnWidthsOnUpdate​(bool autofit)
      Sets if the pivot table columns are adjusted automatically to fit the cell content or the current width is kept.
      void setClassicLayout​(bool isClassicLayout)
      Sets if the classic layout is selected.
      void setColumnHeaderTitle​(System.String text)
      Sets the value to be displayed in column header in compact mode.
      void setCompactForm()
      Sets the compact form for all the fields of the pivot table.
      void setDataHeaderTitle​(System.String text)
      Sets the value to be displayed in value area header.
      void setEmptyCellValue​(bool showEmptyCell, System.String value)
      Sets the text that is displayed in a cell instead of a blank cell.
      void setEnableShowDetails​(bool enable)
      Sets if the drilling down to detail data from the data source, and then displaying the data on a new worksheet is enabled.
      void setErrorValue​(bool showError, System.String value)
      Sets the text that is displayed in a cell instead of the error message.
      void setGrandTotalTitle​(System.String text)
      Sets the value to be displayed for grand totals.
      void setLocation​(int firstRow, int firstColumn, int lastRow, int lastColumn)
      Sets the location of the pivot table.
      void setLocation​(System.String range)
      Sets the location of the pivot table.
      void setMergeAndCenterCellsWithLabels​(bool mergeAndCenterCellsWithLabels)
      Sets if the cells are merged for outer row and column items.
      void setName​(System.String name)
      Sets the pivot table name.
      void setOutlineForm()
      Sets the outline form for all the fields of the pivot table.
      void setPageOverThenDown​(bool isPageOverThenDown)
      Sets if first are displayed the fields in the report filter area from left to right, or if first are displayed the fields in the report filter area from top to bottom
      void setPageWrap​(int fields)
      Sets the number of fields to display before taking up another column or row.
      void setPreserveCellFormattingOnUpdate​(bool preserve)
      Sets if the layout and format is preserved each time an operation is performed on the pivot table.
      void setPrintDrillButtons​(bool printDrillButtons)
      Sets if the expand/collapse buttons are visible when the pivot table is printed.
      void setPrintTitles​(bool printTitles)
      Sets if the row and column field headers and column item labels are replicated on each printed page.
      void setRepeatRowLabels​(bool repeatRowLabels)
      Sets if the row labels are replicated on each printed page.
      void setRowHeaderTitle​(System.String text)
      Sets the value to be displayed in row header in compact mode.
      void setRowLabelsIndent​(int chars)
      Sets the indent of the rows in the row labels area when the pivot table report is in compact format.
      void setShowColumnHeaders​(bool show)
      Sets if the column headers in the banding style are displayed.
      void setShowColumnStripes​(bool show)
      Sets if the column headers in the banding style are displayed.
      void setShowContextualTooltips​(bool show)
      Sets if the tooltips that show value, row, or column information for a field or data value are visible.
      void setShowDrillButtons​(bool show)
      Sets if the expand/collapse buttons are visible.
      void setShowGrandTotalsForColumns​(bool show)
      Sets if the pivot table contains grand totals for columns.
      void setShowGrandTotalsForRows​(bool show)
      Sets if the pivot table contains grand totals for rows.
      void setShowHeaders​(bool showHeaders)
      Sets if the captions at the top of the pivot table report and filter drop-down arrows on column and row labels are visible.
      void setShowRowHeaders​(bool show)
      Sets if the row headers in the banding style are displayed.
      void setShowRowStripes​(bool show)
      Sets if the row headers in the banding style are displayed.
      void setSortOrder​(int sortOrder)
      Sets the sort order of the fields in pivot table.
      void setSourceRange​(System.String formulaRange, ExcelDocument xls)
      Sets the data source for the pivot table.
      void setStyle​(System.String style)
      Sets the style of the pivot table.
      void setSubtotalFilteredPageItems​(bool show)
      Sets if the pivot table includes or excludes report-filtered items in subtotal.
      void setTabularForm()
      Sets the outline form for all the fields of the pivot table.
      void setUseCustomListsWhenSorting​(bool useCustomListsWhenSorting)
      Sets if the custom lists are used when Excel sorts lists.
      bool ShowColumnHeaders()
      Returns true if the column headers in the banding style are displayed, false otherwise.
      bool ShowColumnStripes()
      Returns true if each column is alternated with a lighter and darker color, false otherwise.
      bool ShowContextualTooltips()
      Returns true if the tooltips that show value, row, or column information for a field or data value are visible, false otherwise.
      bool ShowDrillButtons()
      Returns true if the expand/collapse buttons are visible, false otherwise.
      bool ShowEmptyCells()
      Returns true if a specific text is displayed in a cell instead of a blank cell, false otherwise.
      bool ShowError()
      Returns true if a specific text is displayed in a cell instead of the error message, false otherwise.
      bool ShowGrandTotalsForColumns()
      Returns true if the pivot table contains grand totals for columns, false otherwise.
      bool ShowGrandTotalsForRows()
      Returns true if the pivot table contains grand totals for rows, false otherwise.
      bool ShowHeaders()
      Returns true if the captions at the top of the pivot table report and filter drop-down arrows on column and row labels are visible, false otherwise.
      bool ShowRowHeaders()
      Returns true if the row headers in the banding style are displayed, false otherwise.
      bool ShowRowStripes()
      Returns true if each row is alternated with a lighter and darker color, false otherwise.
      bool ShowSubtotalFilteredPageItems()
      Returns true if the pivot table includes or excludes report-filtered items in subtotals, false otherwise.
      int ValuesCount()
      Returns the number of fields in the values area.