Analyzing your prompt, please hold on...
An error occurred while retrieving the results. Please refresh the page and try again.
PivotField.group() method (automatic grouping by data type) and PivotField.group(PivotFieldGroupOptions) (custom grouping options).The parameterless PivotField.group() overload inspects the data type of the field and groups it automatically. A field that contains only dates (and blanks) is grouped by months. A field that contains only numbers (and blanks) is grouped into numeric ranges using an automatic interval.
import jpype
import asposecells
jpype.startJVM()
from asposecells.api import Workbook
# Load the sample workbook
wb = Workbook("sampleGroupPivotFieldsInPivotTable.xlsx")
# Access the second worksheet and its pivot table
pt = wb.getWorksheets().get(1).getPivotTables().get(0)
# Group the first row field automatically.
# The field contains dates, so it is grouped by months.
field = pt.getRowFields().get(0)
field.group()
# Calculate the pivot table data and save the output file
pt.calculateData()
wb.save("outputAutoGroupPivotField.xlsx")
jpype.shutdownJVM()
The PivotFieldGroupOptions class provides custom grouping configuration. For date fields, set StartDate, EndDate, and GroupTypes. For numeric fields, set StartNumber, EndNumber, and Interval. The NewPivotField property controls whether grouping creates new pivot fields alongside the original (see the Note section at the end of this article).
The following example groups the date row field of the sample pivot table by months and quarters between a start and end date. The code loads the sample Excel file, performs the grouping, calculates the pivot table data, and saves the grouped result. The screenshot shows the effect: the date row field is now grouped by quarters and months.
Note that the order of the values in options.GroupTypes is ignored. No matter in which order the group types are listed, the grouped fields are always displayed from the largest period to the smallest: years, quarters, months, days, hours, minutes, seconds. In this example the months are listed before the quarters in options.GroupTypes, yet the quarters are displayed as the outer level and the months as the inner level.
import jpype
import asposecells
jpype.startJVM()
from asposecells.api import Workbook, PivotFieldGroupOptions, PivotGroupByType, DateTime
# Load the sample workbook
wb = Workbook("sampleGroupPivotFieldsInPivotTable.xlsx")
# Access the second worksheet and its pivot table
pt = wb.getWorksheets().get(1).getPivotTables().get(0)
# Group the first row field (a date field) by months and quarters
options = PivotFieldGroupOptions()
options.setStartDate(DateTime(2008, 1, 1))
options.setEndDate(DateTime(2008, 9, 5))
options.setGroupTypes([PivotGroupByType.MONTHS, PivotGroupByType.QUARTERS])
pt.getRowFields().get(0).group(options)
# Calculate the pivot table data and save the output file
pt.calculateData()
wb.save("outputGroupPivotFieldsInPivotTable.xlsx")
jpype.shutdownJVM()

FieldTwo of the sample pivot table is initially a data (value) field. Because only row or column fields can be grouped, the code first calls pivotTable.addFieldToArea(PivotFieldType.ROW, "FieldTwo") to move FieldTwo to the row area, refreshes the pivot, then groups the numeric field into the ranges 0-1, 2-3 and 4-5 using StartNumber = 0, EndNumber = 5 and Interval = 2.
import jpype
import asposecells
jpype.startJVM()
from asposecells.api import Workbook, PivotFieldType, PivotFieldGroupOptions
# Load the sample workbook
wb = Workbook("sampleGroupPivotFieldsInPivotTable.xlsx")
# Access the second worksheet and its pivot table
pt = wb.getWorksheets().get(1).getPivotTables().get(0)
# FieldTwo is a data field: move it to the row area first,
# because only row/column fields can be grouped
pt.addFieldToArea(PivotFieldType.ROW, "FieldTwo")
pt.calculateData()
# Group the numeric field into the ranges 0-1, 2-3, 4-5
options = PivotFieldGroupOptions()
options.setStartNumber(0)
options.setEndNumber(5)
options.setInterval(2)
pt.getRowFields().get(1).group(options)
# Calculate the pivot table data and save the output file
pt.calculateData()
wb.save("outputNumberGroupPivotField.xlsx")
jpype.shutdownJVM()
PivotField.groupBy() method overloads are deprecated and removed from the API surface. PivotField.group() and PivotField.group(PivotFieldGroupOptions) are the replacements.PivotField.group() methods follow the same behavior as Excel. The PivotFieldGroupOptions.NewPivotField property exists for compatibility with older versions: it defaults to false for numeric grouping and true for date-time grouping; set it to false if you want date grouping to replace the original pivot field instead of creating new pivot fields.PivotFieldGroupOptions.Interval property takes effect only for numeric grouping and for date grouping by days (for example, grouping dates into 7-day periods); it is ignored when dates are grouped by months, quarters or years.Analyzing your prompt, please hold on...
An error occurred while retrieving the results. Please refresh the page and try again.