Namespace EasyXLS.PivotTables

Class ExcelPivotTable

System.Object
EasyXLS.PivotTables.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
    addCalculatedField​(System.String name, System.String formula)
    Add a calculated field to pivot table.
    void
    addField​(ExcelField xlsField)
    Internal use only
    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.
    void
    groupDateField​(System.String fieldName, bool autoStartAt, System.DateTime startAt, bool autoEndAt, System.DateTime endAt, bool groupBySeconds, bool groupByMinutes, bool groupByHours, bool groupByDays, int numberOfDays, bool groupByMonths, bool groupByQuarters, bool groupByYears)
    Group a pivot table field of date time format.
    void
    groupNumericField​(System.String fieldName, bool autoStartAt, double startAt, bool autoEndAt, double endAt, double groupBy)
    Group a pivot table field of numeric data type.
    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 and must be a formula, that represents a range or a cell reference.
    void
    setLocation​(System.String range)
    Sets the location of the pivot table and must be a formula, that represents a range or a cell reference
    This method is overridden as setLocation_2 in COM+ version of EasyXLS.
    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.
    void
    ungroupField​(System.String fieldName)
    Ungroup a pivot table field.
    int
    ValuesCount()
    Returns the number of fields in the values area.

    Methods inherited from class System.Object

    equals, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait