Package io.keikai.axyra.sheets

Class PivotTableView

java.lang.Object
io.keikai.axyra.sheets.PivotTableView
public final class PivotTableView extends Object
A view of one pivot table: its definition (fields, filters, sorts, layout, style) and the refresh() that writes its output into the sheet. Obtain it with Sheet.pivotTable(int) or Sheet.pivotTable(String); create a pivot with Sheet.addPivotTable(String, String, String). 

 try (Workbook wb = Workbook.create()) {
     Sheet s = wb.createSheet("Data");
     s.range("A1:B3").setValues(new CellValue[][] {
         {CellValue.text("Region"), CellValue.text("Sales")},
         {CellValue.text("East"), CellValue.number(100)},
         {CellValue.text("West"), CellValue.number(250)}});
     int index = s.addPivotTable("Pivot1", "D1", "Data!A1:B3");
     PivotTableView pv = s.pivotTable(index);
     pv.addRowField("Region");
     pv.addDataField("Sales", AggregateFunction.SUM);
     pv.refresh();                                          // writes the output cells
     CellValue[][] out = s.range(pv.location()).values();   // the whole output range
 }

Definition and output. Mutators change only the pivot's definition in the engine, at once. They do not change any cell. Call refresh() to write the new output into the sheet before you read its cells: a cell read never refreshes a pivot. A pivot created or changed through the API and not refreshed since is refreshed automatically before the workbook is saved (every Workbook.save* method) or rendered (Workbook.renderPdf, renderSheet*, renderRange*, Sheet.renderChartToPng(int, int, int), Sheet.renderChartToSvg(int, int, int)). That automatic refresh replaces the output the file carried; when it cannot read the source range, the save or render goes on without it. A mutator that returns false or throws still marks the pivot for this automatic refresh. A pivot read from a file and never changed keeps the output the file carried. Workbook.setTimelineRange(java.lang.String, java.time.LocalDate, java.time.LocalDate) and Workbook.setSlicerSelection(java.lang.String, java.lang.String...) refresh the pivots they filter at once.

Snapshot. Getters read a snapshot of the definition taken when the view was created, by reload(), and after each successful mutator called through this view. Changes made through another view of the same pivot appear only after reload(). itemFilter(java.lang.String) and pageFilter(java.lang.String) always read the engine. The view is bound to the pivot's index on its sheet; a pivot removed or inserted before it moves that index.

Fields. Field placement follows the Excel model (ECMA-376 Part 1 §18.10): source columns go to the row, column or filter (page) axes, or, with an AggregateFunction, to the values area. The same source column may appear several times in the values area ("Sum of Sales" and "Count of Sales"). Methods that take a position, from, to or dataField use 0-based positions within one area; a negative position is treated as past the end.

Saving. A changed pivot is written from the model on the next save, not from the bytes read from the file, and its cache is marked refreshOnLoad.

Filtering: manual item filters (setItemFilter(java.lang.String, java.lang.String...)), report (page) filters (setPageFilter(java.lang.String, java.lang.String)), caption filters (setLabelFilter(java.lang.String, io.keikai.axyra.sheets.PivotLabelFilterType, java.lang.String)), value filters (setValueFilter(java.lang.String, int, io.keikai.axyra.sheets.PivotValueFilterType, double)) and Top-N filters (setTopNFilter(java.lang.String, int, int, boolean)). Items sort by label or by a data field's aggregate (sortField(java.lang.String, boolean), sortFieldByValue(java.lang.String, boolean, int)). Values transform with setShowDataAs(int, io.keikai.axyra.sheets.PivotShowDataAsType). Virtual columns come from addCalculatedField(java.lang.String, java.lang.String), virtual items from addCalculatedItem(java.lang.String, java.lang.String, java.lang.String). Items group by date or numeric range with groupFieldByDate(java.lang.String, io.keikai.axyra.sheets.PivotDateGroupBy) and groupFieldNumeric(java.lang.String, double, double, double). Named styles come from setStyle(io.keikai.axyra.sheets.PivotStyle).

  • Method Summary

    Modifier and Type
    Method
    Description
    void
    addCalculatedField(String name, String formula)
    Defines a calculated field — a virtual source column whose value is formula evaluated over the sums of the referenced source fields within each pivot bucket (Excel's calculated-field semantics; ECMA-376 Part 1 §18.10.1.13 cacheField@formula).
    void
    addCalculatedItem(String field, String name, String formula)
    Defines a calculated item on the row/column field field — a virtual item whose per-cell value is formula evaluated over the field's other items (ECMA-376 Part 1 §18.10.1.9; e.g.
    void
    addColumnField(String sourceField)
    Adds sourceField to the column axis as the innermost level.
    void
    addDataField(String sourceField, AggregateFunction function)
    Adds sourceField to the values area with the "Sum of Sales"-style default caption.
    void
    addDataField(String sourceField, AggregateFunction function, String customName)
    Adds sourceField to the values area with a caption.
    void
    addFilterField(String sourceField)
    Adds sourceField as a report filter: Excel's Filters area, an ECMA-376 page field.
    void
    addPageField(String sourceField)
    Same as addFilterField(java.lang.String), under the OOXML "page field" name.
    void
    addRowField(String sourceField)
    Adds sourceField to the row axis as the innermost level.
    List<PivotCalculatedField>
    calculatedFields()
    The calculated-field definitions, in definition order.
    List<PivotCalculatedItem>
    calculatedItems()
    The calculated-item definitions, in definition order.
    boolean
    clearFieldSort(String field)
    Restores manual or source item order for the row/column field field.
    boolean
    clearLabelFilter(String field)
    Clears the caption (label) filter of the row/column field field.
    boolean
    clearShowDataAs(int position)
    Restores the raw aggregate for the position-th values-area field and clears its base references.
    boolean
    clearTopNFilter(String field)
    Clears the Top-N filter of the row/column field field.
    boolean
    clearValueFilter(String field)
    Clears the value filter of the row/column field field.
    List<PivotField>
    columnFields()
    The column-axis fields in nesting order, outermost first.
    List<PivotField>
    dataFields()
    The values-area fields in order.
    PivotFieldGroup
    fieldGroup(String field)
    The grouping of the row/column field field, matched ignoring case.
    PivotFieldSort
    fieldSort(String field)
    The declared sort of the row/column field field, matched ignoring case.
    List<PivotField>
    filterFields()
    The report-filter (page) fields in order.
    String
    grandTotalCaption()
    The grand-total caption override, or null when the default ("Grand Total") applies.
    boolean
    grandTotalColumn()
    Whether the grand-total column at the right is shown, as of the last reload().
    boolean
    grandTotalRow()
    Whether the grand-total row at the bottom is shown, as of the last reload().
    boolean
    groupFieldByDate(String field, PivotDateGroupBy by)
    Groups the row/column field field by a date component (ECMA-376 Part 1 §18.10.1.81 rangePr@groupBy): on refresh() the field's items become the component labels in calendar order (MONTHS -> "Jan".."Dec", QUARTERS -> "Qtr1".."Qtr4", …).
    boolean
    groupFieldByDate(String field, PivotDateGroupBy... components)
    Multi-level date grouping — Excel's Years/Quarters/Months expansion: components[0] becomes the outermost level.
    boolean
    groupFieldNumeric(String field, double start, double end, double interval)
    Groups the row/column field field into numeric bins of interval over [start, end] — Excel's label shapes ("0-9", last bin "90-100", outliers "<0" / ">100").
    int
    index()
    This pivot table's 0-based index on its sheet, fixed when the view was created.
    String[]
    itemFilter(String field)
    The currently visible items of field's manual filter: the distinct source-column items minus the hidden ones.
    PivotLabelFilter
    labelFilter(String field)
    The caption (label) filter of the row/column field field, matched ignoring case.
    PivotLayout
    layout()
    The report layout (compact, outline or tabular), as of the last reload().
    String
    location()
    The pivot's output location in A1 form, without a sheet name.
    void
    moveColumnField(int from, int to)
    Moves the column field at from to position to; see moveRowField(int, int).
    void
    moveDataField(int from, int to)
    Moves the values-area field at from to position to; see moveRowField(int, int).
    void
    moveRowField(int from, int to)
    Moves the row field at from to position to; the fields between shift by one.
    String
    name()
    The pivot table's name, as of the last reload().
    String
    pageFilter(String field)
    The selected item of the report-filter (page) field field.
    int
    refresh()
    Re-reads the source range, runs the pivot cube engine (aggregation + layout), and materializes the computed output into the sheet at the pivot anchor — row labels, column headers, values, and grand totals become ordinary cells (like Excel's Refresh).
    String
    refreshedBy()
    The user name stamped on the pivot cache the last time it was refreshed (the refreshedBy attribute of pivotCacheDefinition, ECMA-376 Part 1 §18.10.1.67), or null when the source file carries none — as for a pivot table built through this API.
    void
    reload()
    Re-reads the pivot definition from the engine into this view's snapshot.
    void
    removeCalculatedField(String name)
    Removes the calculated field name, matched ignoring ASCII case, together with every values-area placement referencing it.
    void
    removeCalculatedItem(String field, String name)
    Removes the calculated item name from field.
    void
    removeColumnField(int position)
    Removes the position-th column field; see removeRowField(int).
    void
    removeDataField(int position)
    Removes the position-th values-area field.
    void
    removeFilterField(int position)
    Removes the position-th report-filter field and clears its selected item; see removeRowField(int).
    void
    removeRowField(int position)
    Removes the position-th row field.
    List<PivotField>
    rowFields()
    The row-axis fields in nesting order, outermost first.
    void
    setColumnFieldCaption(int position, String caption)
    Renames the position-th column field for display; see setRowFieldCaption(int, java.lang.String).
    void
    setColumnFieldSubtotals(int position, boolean show)
    Shows or hides the subtotals of the position-th column field; see setRowFieldSubtotals(int, boolean).
    void
    setFilterFieldCaption(int position, String caption)
    Renames the position-th report-filter field for display; see setRowFieldCaption(int, java.lang.String).
    void
    setGrandTotalCaption(String caption)
    Sets the grand-total caption (ECMA-376 Part 1 §18.10.1.46).
    void
    setGrandTotals(boolean grandTotalRow, boolean grandTotalColumn)
    Toggles the grand-total row (bottom) and grand-total column (right).
    boolean
    setItemFilter(String field, String... visibleItems)
    Restricts field's manual item filter to visibleItems — Excel's filter dropdown with only those entries checked (ECMA-376 Part 1 §18.10.1.45 item@h).
    boolean
    setLabelFilter(String field, PivotLabelFilterType type, String value)
    Sets the caption (label) filter of the row/column field field — Excel's "Label Filters" menu (ECMA-376 Part 1 §18.10.1.32).
    void
    setLayout(PivotLayout layout)
    Sets the report layout (ECMA-376 Part 1 §18.10.1.73).
    boolean
    setPageFilter(String field, String item)
    Selects a single item on the report-filter (page) field field — Excel's single-select page filter dropdown (ECMA-376 Part 1 §18.10.1.62 pageField@item).
    void
    setRowFieldCaption(int position, String caption)
    Renames the position-th row field for display (ECMA-376 Part 1 §18.10.1.69 pivotField@name — Excel writes this attribute when a placed field is renamed in the pivot UI).
    void
    setRowFieldSubtotals(int position, boolean show)
    Shows or hides the subtotals of the position-th row field (ECMA-376 pivotField@defaultSubtotal).
    boolean
    setShowDataAs(int position, PivotShowDataAsType type)
    Sets the Show-Values-As mode of the position-th values-area field without base references.
    boolean
    setShowDataAs(int position, PivotShowDataAsType type, String baseField, String baseItem)
    Sets the Show-Values-As mode of the position-th values-area field with base references.
    boolean
    setStyle(PivotStyle style)
    Applies a named pivot style + banding flags (ECMA-376 Part 1 §18.10.1.88 pivotTableStyleInfo); Excel renders the bands on open.
    boolean
    setStyle(String name)
    Applies the style name with Excel's Design-tab default flags (PivotStyle.of(String)); null clears the style.
    boolean
    setTopNFilter(String field, int dataField, int count, boolean bottom)
    Keeps only the top (or bottom) count items of the row/column field field, ranked by their aggregate of the dataField-th values-area field: Excel's "Top 10" filter.
    boolean
    setTopPercentFilter(String field, int dataField, double percent, boolean bottom)
    Keeps the top (or bottom) items of field whose aggregates, taken in rank order, first reach percent percent of the field's total: Excel's "Top 10 ...
    boolean
    setValueFilter(String field, int dataField, PivotValueFilterType type, double value)
    Sets a single-threshold value filter on the row/column field field: each item's aggregate of the dataField-th values-area field is compared with value by type.
    boolean
    setValueFilterBetween(String field, int dataField, double low, double high)
    Hides the items of the row/column field field whose aggregate of the dataField-th values-area field is outside low <= aggregate <= high: Excel's "between" value filter.
    PivotShowDataAs
    showDataAs(int position)
    The Show-Values-As state of the position-th values-area field, read from this view's snapshot.
    boolean
    sortField(String field, boolean ascending)
    Sorts the row/column field field by item label (ECMA-376 Part 1 §18.10.1.69 sortType).
    boolean
    sortFieldByValue(String field, boolean ascending, int dataField)
    Sorts the row/column field field by its aggregate of the dataField-th values-area field (ECMA-376 Part 1 §18.10.1.72 autoSortScope).
    List<String>
    sourceFields()
    The source column names available for field placement, in source order: the cache field names, or the header row of the source range for a pivot whose cache is still empty.
    String
    sourceRef()
    The source data reference, e.g.
    PivotStyle
    style()
    The named pivot style and its banding flags, read from this view's snapshot.
    PivotTopNFilter
    topNFilter(String field)
    The Top-N filter of the row/column field field, matched ignoring case.
    boolean
    ungroupField(String field)
    Removes the grouping of field: items revert to the raw source values, and a multi-level date grouping collapses back to one level.
    PivotValueFilter
    valueFilter(String field)
    The value filter of the row/column field field, matched ignoring case.

    Methods inherited from class java.lang.Object

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

  • Method Details

    • reload

      public void reload()
      Re-reads the pivot definition from the engine into this view's snapshot. Getters other than itemFilter(java.lang.String) and pageFilter(java.lang.String) return what this call read. It does not refresh the output; see refresh().
      Throws:
      AxyraException - when the sheet no longer has a pivot at this view's index

    • index

      public int index()
      This pivot table's 0-based index on its sheet, fixed when the view was created.

    • name

      public String name()
      The pivot table's name, as of the last reload().

    • location

      public String location()
      The pivot's output location in A1 form, without a sheet name. It is the anchor cell (e.g. "H1") for a pivot created through the API and not yet refreshed. Otherwise it is the whole output range (e.g. "H1:J8"), as read from the file or as written by the last refresh(). Read from this view's snapshot.

    • sourceRef

      public String sourceRef()
      The source data reference, e.g. "Sheet1!A1:D10", as of the last reload().

    • refreshedBy

      public String refreshedBy()
      The user name stamped on the pivot cache the last time it was refreshed (the refreshedBy attribute of pivotCacheDefinition, ECMA-376 Part 1 §18.10.1.67), or null when the source file carries none — as for a pivot table built through this API.

    • sourceFields

      public List<String> sourceFields()
      The source column names available for field placement, in source order: the cache field names, or the header row of the source range for a pivot whose cache is still empty. Calculated fields are not listed; see calculatedFields().
      Returns:
      an immutable snapshot as of the last reload(); empty when the source range cannot be read

    • addRowField

      public void addRowField(String sourceField)
      Adds sourceField to the row axis as the innermost level. The name matches a source column exactly first, then ignoring ASCII case. A field already placed on another axis is not moved; it gets a second placement. The output changes on the next refresh(); a save or render refreshes it automatically.
      Throws:
      AxyraException - when sourceField is not a source column, or names a calculated field (calculated fields go only to the values area)

    • addColumnField

      public void addColumnField(String sourceField)
      Adds sourceField to the column axis as the innermost level. Name matching, placement and refresh behave as in addRowField(java.lang.String).
      Throws:
      AxyraException - when sourceField is not a source column, or names a calculated field

    • addFilterField

      public void addFilterField(String sourceField)
      Adds sourceField as a report filter: Excel's Filters area, an ECMA-376 page field. Select its item with setPageFilter(java.lang.String, java.lang.String). Name matching, placement and refresh behave as in addRowField(java.lang.String).
      Throws:
      AxyraException - when sourceField is not a source column, or names a calculated field

    • addPageField

      public void addPageField(String sourceField)
      Same as addFilterField(java.lang.String), under the OOXML "page field" name.
      Throws:
      AxyraException - when sourceField is not a source column, or names a calculated field

    • addDataField

      public void addDataField(String sourceField, AggregateFunction function)
      Adds sourceField to the values area with the "Sum of Sales"-style default caption. The same field may be added several times, with the same or another function. A calculated field from addCalculatedField(java.lang.String, java.lang.String) is accepted. The name matches exactly first, then ignoring ASCII case. The output changes on the next refresh(); a save or render refreshes it automatically.
      Throws:
      AxyraException - when sourceField is neither a source column nor a calculated field
      NullPointerException - when function is null

    • addDataField

      public void addDataField(String sourceField, AggregateFunction function, String customName)
      Adds sourceField to the values area with a caption. customName replaces the "Sum of Sales"-style default caption (ECMA-376 Part 1 §18.10.1.18 dataField@name); null or an empty string keeps the default. Otherwise the same as addDataField(String, AggregateFunction).
      Throws:
      AxyraException - when sourceField is neither a source column nor a calculated field
      NullPointerException - when function is null

    • removeRowField

      public void removeRowField(int position)
      Removes the position-th row field. The field returns to the unplaced field list and loses its manual item filter; when the same source column has another placement, this placement is deleted instead. The output changes on the next refresh(); a save or render refreshes it automatically.
      Parameters:
      position - 0-based nesting position, outermost first
      Throws:
      AxyraException - when position is negative or past the last row field

    • removeColumnField

      public void removeColumnField(int position)
      Removes the position-th column field; see removeRowField(int).
      Parameters:
      position - 0-based nesting position, outermost first
      Throws:
      AxyraException - when position is negative or past the last column field

    • removeFilterField

      public void removeFilterField(int position)
      Removes the position-th report-filter field and clears its selected item; see removeRowField(int).
      Parameters:
      position - 0-based position among the filter fields
      Throws:
      AxyraException - when position is negative or past the last filter field

    • removeDataField

      public void removeDataField(int position)
      Removes the position-th values-area field. The positions of the fields after it drop by one. A value filter, Top-N filter or by-value sort keeps its dataField number. The output changes on the next refresh(); a save or render refreshes it automatically.
      Parameters:
      position - 0-based values-area position
      Throws:
      AxyraException - when position is negative or past the last values-area field

    • moveRowField

      public void moveRowField(int from, int to)
      Moves the row field at from to position to; the fields between shift by one. The output changes on the next refresh(); a save or render refreshes it automatically.
      Parameters:
      from - 0-based current position
      to - 0-based target position
      Throws:
      AxyraException - when from or to is negative or past the last row field

    • moveColumnField

      public void moveColumnField(int from, int to)
      Moves the column field at from to position to; see moveRowField(int, int).
      Parameters:
      from - 0-based current position
      to - 0-based target position
      Throws:
      AxyraException - when from or to is negative or past the last column field

    • moveDataField

      public void moveDataField(int from, int to)
      Moves the values-area field at from to position to; see moveRowField(int, int). The field keeps its Show-Values-As setting. A value filter, Top-N filter or by-value sort keeps its dataField number, so it then reads the field now at that position.
      Parameters:
      from - 0-based current position
      to - 0-based target position
      Throws:
      AxyraException - when from or to is negative or past the last values-area field

    • rowFields

      public List<PivotField> rowFields()
      The row-axis fields in nesting order, outermost first. A field grouped by several date components appears once per component.
      Returns:
      a new list, a snapshot as of the last reload()

    • columnFields

      public List<PivotField> columnFields()
      The column-axis fields in nesting order, outermost first. A field grouped by several date components appears once per component.
      Returns:
      a new list, a snapshot as of the last reload()

    • filterFields

      public List<PivotField> filterFields()
      The report-filter (page) fields in order.
      Returns:
      a new list, a snapshot as of the last reload()

    • dataFields

      public List<PivotField> dataFields()
      The values-area fields in order. Their list positions are the position and dataField arguments of the other methods. PivotField.showSubtotals() is always true here.
      Returns:
      a new list, a snapshot as of the last reload()

    • layout

      public PivotLayout layout()
      The report layout (compact, outline or tabular), as of the last reload().

    • setLayout

      public void setLayout(PivotLayout layout)
      Sets the report layout (ECMA-376 Part 1 §18.10.1.73). The output changes on the next refresh(); a save or render refreshes it automatically.
      Throws:
      NullPointerException - when layout is null

    • grandTotalRow

      public boolean grandTotalRow()
      Whether the grand-total row at the bottom is shown, as of the last reload().

    • grandTotalColumn

      public boolean grandTotalColumn()
      Whether the grand-total column at the right is shown, as of the last reload().

    • setGrandTotals

      public void setGrandTotals(boolean grandTotalRow, boolean grandTotalColumn)
      Toggles the grand-total row (bottom) and grand-total column (right). OOXML note: the bottom row is colGrandTotals and the right column is rowGrandTotals (ECMA-376 Part 1 §18.10.1.15 / §18.10.1.73 — the attribute names are counter-intuitive). The output changes on the next refresh(); a save or render refreshes it automatically.

    • grandTotalCaption

      public String grandTotalCaption()
      The grand-total caption override, or null when the default ("Grand Total") applies. Read from this view's snapshot.

    • setGrandTotalCaption

      public void setGrandTotalCaption(String caption)
      Sets the grand-total caption (ECMA-376 Part 1 §18.10.1.46). null or an empty string restores the default ("Grand Total"). The output changes on the next refresh(); a save or render refreshes it automatically.

    • setRowFieldSubtotals

      public void setRowFieldSubtotals(int position, boolean show)
      Shows or hides the subtotals of the position-th row field (ECMA-376 pivotField@defaultSubtotal). The output changes on the next refresh(); a save or render refreshes it automatically.
      Parameters:
      position - 0-based nesting position, outermost first
      Throws:
      AxyraException - when position is negative or past the last row field

    • setColumnFieldSubtotals

      public void setColumnFieldSubtotals(int position, boolean show)
      Shows or hides the subtotals of the position-th column field; see setRowFieldSubtotals(int, boolean).
      Parameters:
      position - 0-based nesting position, outermost first
      Throws:
      AxyraException - when position is negative or past the last column field

    • setRowFieldCaption

      public void setRowFieldCaption(int position, String caption)
      Renames the position-th row field for display (ECMA-376 Part 1 §18.10.1.69 pivotField@name — Excel writes this attribute when a placed field is renamed in the pivot UI). null or an empty string restores the source column name. The output changes on the next refresh(); a save or render refreshes it automatically.
      Parameters:
      position - 0-based nesting position, outermost first
      Throws:
      AxyraException - when position is negative or past the last row field

    • setColumnFieldCaption

      public void setColumnFieldCaption(int position, String caption)
      Renames the position-th column field for display; see setRowFieldCaption(int, java.lang.String).
      Parameters:
      position - 0-based nesting position, outermost first
      Throws:
      AxyraException - when position is negative or past the last column field

    • setFilterFieldCaption

      public void setFilterFieldCaption(int position, String caption)
      Renames the position-th report-filter field for display; see setRowFieldCaption(int, java.lang.String).
      Parameters:
      position - 0-based position among the filter fields
      Throws:
      AxyraException - when position is negative or past the last filter field

    • setTopNFilter

      public boolean setTopNFilter(String field, int dataField, int count, boolean bottom)
      Keeps only the top (or bottom) count items of the row/column field field, ranked by their aggregate of the dataField-th values-area field: Excel's "Top 10" filter. It replaces the field's previous Top-N filter and AND-combines with the other filters. Ties keep source order; items with no numeric aggregate are hidden. The output changes on the next refresh(); a save or render refreshes it automatically. Use setTopPercentFilter(java.lang.String, int, double, boolean) for the percent form.
      Parameters:
      dataField - 0-based values-area position; a negative value clears the field's Top-N filter, as clearTopNFilter(java.lang.String) does
      count - number of items to keep; must be positive
      bottom - true keeps the smallest items instead of the largest
      Returns:
      false when field is not on the row or column axis, count <= 0, or dataField is past the last values-area field

    • setTopPercentFilter

      public boolean setTopPercentFilter(String field, int dataField, double percent, boolean bottom)
      Keeps the top (or bottom) items of field whose aggregates, taken in rank order, first reach percent percent of the field's total: Excel's "Top 10 ... Percent" filter. It replaces the field's previous Top-N filter. The output changes on the next refresh(); a save or render refreshes it automatically.
      Parameters:
      dataField - 0-based values-area position; a negative value clears the field's Top-N filter
      percent - percent of the total, 0 to 100 (10 means 10 %); must be positive
      bottom - true ranks from the smallest aggregate
      Returns:
      false when field is not on the row or column axis, percent <= 0, or dataField is past the last values-area field

    • clearTopNFilter

      public boolean clearTopNFilter(String field)
      Clears the Top-N filter of the row/column field field. The output changes on the next refresh(); a save or render refreshes it automatically.
      Returns:
      false when field is not on the row or column axis

    • topNFilter

      public PivotTopNFilter topNFilter(String field)
      The Top-N filter of the row/column field field, matched ignoring case. Read from this view's snapshot.
      Returns:
      the filter, or null when the field has none or is not on the row or column axis

    • setStyle

      public boolean setStyle(PivotStyle style)
      Applies a named pivot style + banding flags (ECMA-376 Part 1 §18.10.1.88 pivotTableStyleInfo); Excel renders the bands on open. Pass null to clear the style. The name is not checked against Excel's built-in list; a name Excel does not know is written as given.
      Returns:
      false when the style name is empty or blank, or the pivot no longer exists

    • setStyle

      public boolean setStyle(String name)
      Applies the style name with Excel's Design-tab default flags (PivotStyle.of(String)); null clears the style.
      Returns:
      false when name is empty or blank, or the pivot no longer exists

    • style

      public PivotStyle style()
      The named pivot style and its banding flags, read from this view's snapshot.
      Returns:
      the style, or null when none is applied

    • groupFieldByDate

      public boolean groupFieldByDate(String field, PivotDateGroupBy by)
      Groups the row/column field field by a date component (ECMA-376 Part 1 §18.10.1.81 rangePr@groupBy): on refresh() the field's items become the component labels in calendar order (MONTHS -> "Jan".."Dec", QUARTERS -> "Qtr1".."Qtr4", …). Source values must be date serials; text values keep their own label after the buckets. It replaces a previous date grouping of the field. The output changes on the next refresh(); a save or render refreshes it automatically.
      Returns:
      false when field is not on the row or column axis
      Throws:
      NullPointerException - when by is null

    • groupFieldByDate

      public boolean groupFieldByDate(String field, PivotDateGroupBy... components)
      Multi-level date grouping — Excel's Years/Quarters/Months expansion: components[0] becomes the outermost level. The expansion shows up as additional row/column levels on refresh(); re-grouping replaces a previous expansion, ungroupField(java.lang.String) collapses it. Each component becomes its own row or column level, so rowFields() lists the field once per component and fieldGroup(java.lang.String) reports the outermost component. The output changes on the next refresh(); a save or render refreshes it automatically.
      Parameters:
      components - the date components, coarsest first, e.g. YEARS, QUARTERS, MONTHS
      Returns:
      false when field is not on the row or column axis, or components is null, empty or has a repeated component
      Throws:
      NullPointerException - when an element of components is null

    • groupFieldNumeric

      public boolean groupFieldNumeric(String field, double start, double end, double interval)
      Groups the row/column field field into numeric bins of interval over [start, end] — Excel's label shapes ("0-9", last bin "90-100", outliers "<0" / ">100"). Values below start or above end fall into the outlier bins. The output changes on the next refresh(); a save or render refreshes it automatically.
      Returns:
      false when field is not on the row or column axis, interval <= 0, end <= start, or start or end is not finite

    • ungroupField

      public boolean ungroupField(String field)
      Removes the grouping of field: items revert to the raw source values, and a multi-level date grouping collapses back to one level. The output changes on the next refresh(); a save or render refreshes it automatically.
      Returns:
      false when field is not on the row or column axis or is not grouped

    • fieldGroup

      public PivotFieldGroup fieldGroup(String field)
      The grouping of the row/column field field, matched ignoring case. For a multi-level date grouping this is the outermost component. Read from this view's snapshot.
      Returns:
      the grouping, or null when the field is ungrouped or not on the row or column axis

    • setShowDataAs

      public boolean setShowDataAs(int position, PivotShowDataAsType type)
      Sets the Show-Values-As mode of the position-th values-area field without base references. Use it for the modes that need no base (percent of total, row or column, index). A mode that uses a base field or item takes ECMA-376's default: the first row field (or the first column field when there is no row field) and its first item; name the base with setShowDataAs(int, PivotShowDataAsType, String, String). Percent modes produce fractions (0.25 means 25 %). PivotShowDataAsType.NORMAL restores the raw aggregate. The output changes on the next refresh(); a save or render refreshes it automatically.
      Parameters:
      position - 0-based values-area position
      Returns:
      false when position is negative or past the last values-area field
      Throws:
      NullPointerException - when type is null

    • setShowDataAs

      public boolean setShowDataAs(int position, PivotShowDataAsType type, String baseField, String baseItem)
      Sets the Show-Values-As mode of the position-th values-area field with base references. baseField names a field placed on the row or column axis; baseItem is one of its item labels. PivotShowDataAsType.DIFFERENCE_FROM, PivotShowDataAsType.PERCENT_DIFFERENCE_FROM and PivotShowDataAsType.PERCENT_OF use both. PivotShowDataAsType.PERCENT_OF_PARENT, PivotShowDataAsType.RUNNING_TOTAL, PivotShowDataAsType.RUNNING_TOTAL_PERCENT and the rank modes use baseField as their level. A null base takes ECMA-376's default: the first row field (or first column field) and its first item. baseItem is not checked here; a label that matches no item leaves the affected cells blank after refresh. PivotShowDataAsType.NORMAL restores the raw aggregate and clears both base references. The output changes on the next refresh(); a save or render refreshes it automatically.
      Parameters:
      position - 0-based values-area position
      baseField - a row or column field name (matched exactly, then ignoring ASCII case), or null
      baseItem - an item label of the base field, or null
      Returns:
      false when position is negative or past the last values-area field, or a named baseField is not on the row or column axis
      Throws:
      NullPointerException - when type is null

    • clearShowDataAs

      public boolean clearShowDataAs(int position)
      Restores the raw aggregate for the position-th values-area field and clears its base references. The output changes on the next refresh(); a save or render refreshes it automatically.
      Parameters:
      position - 0-based values-area position
      Returns:
      false when position is negative or past the last values-area field

    • showDataAs

      public PivotShowDataAs showDataAs(int position)
      The Show-Values-As state of the position-th values-area field, read from this view's snapshot. Every mode the engine reads from a file has a PivotShowDataAsType constant.
      Parameters:
      position - 0-based values-area position
      Returns:
      the state (PivotShowDataAsType.NORMAL for a raw aggregate), or null when position is negative or past the last values-area field

    • addCalculatedField

      public void addCalculatedField(String name, String formula)
      Defines a calculated field — a virtual source column whose value is formula evaluated over the sums of the referenced source fields within each pivot bucket (Excel's calculated-field semantics; ECMA-376 Part 1 §18.10.1.13 cacheField@formula). The formula language is arithmetic over source column names: "=Sales*0.1", "=Revenue/Units", quoted names ("='Unit Price'*Units") for columns with spaces; the leading = is optional. The definition alone adds no output — place it with addDataField(String, AggregateFunction) using name, then refresh(). A save or render refreshes a changed pivot automatically.

      Grand totals re-evaluate the formula over the grand sums (they are NOT the sum of the per-row calculated values). A calculated field can only be placed in the values area, and may not reference itself or another calculated field.

      Throws:
      AxyraException - when name is blank or clashes (ignoring ASCII case) with a source column or an existing calculated field, or the formula does not parse, references itself, or references a name that is not a source column

    • removeCalculatedField

      public void removeCalculatedField(String name)
      Removes the calculated field name, matched ignoring ASCII case, together with every values-area placement referencing it. The output changes on the next refresh(); a save or render refreshes it automatically.
      Throws:
      AxyraException - when no calculated field with that name exists

    • addCalculatedItem

      public void addCalculatedItem(String field, String name, String formula)
      Defines a calculated item on the row/column field field — a virtual item whose per-cell value is formula evaluated over the field's other items (ECMA-376 Part 1 §18.10.1.9; e.g. addCalculatedItem("Region", "EastWest", "=East+West")). Duplicate references ("=East+East") double the value; the item appears after the source items and is included in grand totals. The output changes on the next refresh(); a save or render refreshes it automatically.
      Throws:
      AxyraException - when the field is not on the row/column axis, the name clashes with a source item or existing calculated item, or the formula does not parse / references unknown items

    • removeCalculatedItem

      public void removeCalculatedItem(String field, String name)
      Removes the calculated item name from field. The output changes on the next refresh(); a save or render refreshes it automatically.
      Throws:
      AxyraException - when no such calculated item exists

    • calculatedItems

      public List<PivotCalculatedItem> calculatedItems()
      The calculated-item definitions, in definition order.
      Returns:
      a new list, a snapshot as of the last reload()

    • calculatedFields

      public List<PivotCalculatedField> calculatedFields()
      The calculated-field definitions, in definition order.
      Returns:
      a new list, a snapshot as of the last reload()

    • setItemFilter

      public boolean setItemFilter(String field, String... visibleItems)
      Restricts field's manual item filter to visibleItems — Excel's filter dropdown with only those entries checked (ECMA-376 Part 1 §18.10.1.45 item@h). Passing no items, or null, clears the filter (every item visible). Values not present in the source column are ignored; when none of visibleItems occurs there, every item is hidden. Items match by exact display text. The field must be placed on the row, column or filter axis. The output changes on the next refresh(); a save or render refreshes it automatically.
      Returns:
      false when field is not a source column or not placed on the row, column or filter axis, or the source range cannot be read

    • itemFilter

      public String[] itemFilter(String field)
      The currently visible items of field's manual filter: the distinct source-column items minus the hidden ones. An unfiltered field returns every item — a filter that hides nothing is the same as no filter. It reads the engine, not this view's snapshot.
      Returns:
      the visible items in first-appearance order, or null when the field is unknown, not placed on the row, column or filter axis, or the source range cannot be read

    • setPageFilter

      public boolean setPageFilter(String field, String item)
      Selects a single item on the report-filter (page) field field — Excel's single-select page filter dropdown (ECMA-376 Part 1 §18.10.1.62 pageField@item). Pass null or an empty string to restore "(All)". The item is not checked against the source; a value absent from the source column leaves the pivot empty. The output changes on the next refresh(); a save or render refreshes it automatically.
      Returns:
      false when field is not a report-filter (page) field

    • pageFilter

      public String pageFilter(String field)
      The selected item of the report-filter (page) field field. It reads the engine, not this view's snapshot.
      Returns:
      the item, or null for "(All)"; also null when the field is unknown or not a filter field

    • setLabelFilter

      public boolean setLabelFilter(String field, PivotLabelFilterType type, String value)
      Sets the caption (label) filter of the row/column field field — Excel's "Label Filters" menu (ECMA-376 Part 1 §18.10.1.32). Items whose display label fails the condition are hidden on the next refresh(); the filter AND-combines with the manual item filter and any value filter on the same field. It replaces the field's previous label filter. Matching ignores ASCII case only; ? and * match themselves, they are not wildcards. Use clearLabelFilter(java.lang.String) to remove the filter.
      Returns:
      false when field is not on the row or column axis
      Throws:
      NullPointerException - when type is null

    • clearLabelFilter

      public boolean clearLabelFilter(String field)
      Clears the caption (label) filter of the row/column field field. The output changes on the next refresh(); a save or render refreshes it automatically.
      Returns:
      false when field is not on the row or column axis

    • labelFilter

      public PivotLabelFilter labelFilter(String field)
      The caption (label) filter of the row/column field field, matched ignoring case. Read from this view's snapshot.
      Returns:
      the filter, or null when the field has none or is not on the row or column axis

    • setValueFilter

      public boolean setValueFilter(String field, int dataField, PivotValueFilterType type, double value)
      Sets a single-threshold value filter on the row/column field field: each item's aggregate of the dataField-th values-area field is compared with value by type. Failing items, and items with no numeric aggregate, are hidden. It replaces the field's previous value filter and AND-combines with the other filters. The output changes on the next refresh(); a save or render refreshes it automatically.
      Parameters:
      dataField - 0-based values-area position
      Returns:
      false when field is not on the row or column axis, or dataField is negative or past the last values-area field
      Throws:
      IllegalArgumentException - when type is PivotValueFilterType.BETWEEN; use setValueFilterBetween(java.lang.String, int, double, double)
      NullPointerException - when type is null

    • setValueFilterBetween

      public boolean setValueFilterBetween(String field, int dataField, double low, double high)
      Hides the items of the row/column field field whose aggregate of the dataField-th values-area field is outside low <= aggregate <= high: Excel's "between" value filter. Items with no numeric aggregate are hidden. The output changes on the next refresh(); a save or render refreshes it automatically.
      Parameters:
      dataField - 0-based values-area position
      low - the inclusive lower bound
      high - the inclusive upper bound
      Returns:
      false when field is not on the row or column axis, or dataField is negative or past the last values-area field

    • clearValueFilter

      public boolean clearValueFilter(String field)
      Clears the value filter of the row/column field field. The output changes on the next refresh(); a save or render refreshes it automatically.
      Returns:
      false when field is not on the row or column axis

    • valueFilter

      public PivotValueFilter valueFilter(String field)
      The value filter of the row/column field field, matched ignoring case. Read from this view's snapshot.
      Returns:
      the filter, or null when the field has none or is not on the row or column axis

    • sortField

      public boolean sortField(String field, boolean ascending)
      Sorts the row/column field field by item label (ECMA-376 Part 1 §18.10.1.69 sortType). The output changes on the next refresh(); a save or render refreshes it automatically. Use sortFieldByValue(java.lang.String, boolean, int) to sort by a values-area aggregate.
      Returns:
      false when field is not on the row or column axis

    • sortFieldByValue

      public boolean sortFieldByValue(String field, boolean ascending, int dataField)
      Sorts the row/column field field by its aggregate of the dataField-th values-area field (ECMA-376 Part 1 §18.10.1.72 autoSortScope). Ties keep source order. The sort may be declared before the values area has fields; the position is resolved at refresh. The output changes on the next refresh(); a save or render refreshes it automatically. Use sortField(java.lang.String, boolean) to sort by label.
      Parameters:
      dataField - 0-based values-area position; a negative value sorts by label instead
      Returns:
      false when field is not on the row or column axis, or the values area has fields and dataField is past the last one

    • clearFieldSort

      public boolean clearFieldSort(String field)
      Restores manual or source item order for the row/column field field. The output changes on the next refresh(); a save or render refreshes it automatically.
      Returns:
      false when field is not on the row or column axis

    • fieldSort

      public PivotFieldSort fieldSort(String field)
      The declared sort of the row/column field field, matched ignoring case. Read from this view's snapshot.
      Returns:
      the sort, or null when none is declared and when the field is not on the row or column axis. Without a sort the items keep the order the pivot declares for them, or ascending order for a field that declares none, as Excel lists a new pivot's items

    • refresh

      public int refresh()
      Re-reads the source range, runs the pivot cube engine (aggregation + layout), and materializes the computed output into the sheet at the pivot anchor — row labels, column headers, values, and grand totals become ordinary cells (like Excel's Refresh). The previous output rectangle is cleared first, so a shrinking table leaves no stale cells. A timeline bound to this pivot narrows the source records. Formulas that read the output are marked for recalculation.

      Call it after a change and before reading output cells; a cell read does not refresh. A save or render runs it automatically for a pivot created or changed through the API. Afterwards location() returns the new output range, and the snapshot is reloaded.

      Returns:
      the number of cells written
      Throws:
      AxyraException - when the source range cannot be resolved, or the sheet no longer has a pivot at this view's index