How to group row_fields using aspose-cells-python 24.10.0 into one cell

I have 3 non-date columns in the input Excel file, and I want 3 PivotFieldType.ROW fields that can be explicitly grouped so that there is a select field drop-down option on the cell. This code is written in aspose.cells for Python via .Net.

Screenshot of how values should appear.

See the following sample code using Aspose.Cells for Python via .NET for your reference. I have dynamically inserted some sample values from list (2 columns) into the worksheet cells of a newly created workbook. Please refer to the code segment and write/update your own code accordingly by yourselves.

import pandas as pd
import numpy as np
import aspose.cells
from aspose.cells import Workbook, WorksheetCollection, Worksheet, CellsHelper, License
from aspose.cells.pivot import PivotFieldType, PivotTableAutoFormatType
from datetime import datetime

workbook = Workbook()
sheet = workbook.worksheets[0]

data = [
    ["Date", "Value"],
    [datetime(2024, 1, 1), 150],
    [datetime(2024, 1, 2), 200],
    [datetime(2024, 1, 3), 120],
    [datetime(2024, 2, 1), 180],
    [datetime(2024, 2, 2), 50]
]

# Populate the worksheet with data
for row_idx, row in enumerate(data):
    for col_idx, value in enumerate(row):
        sheet.cells.get(row_idx, col_idx).value = value

range = sheet.cells.create_range("A2:A6")
# Apply formatting to the range
style = workbook.create_style()
style.number = 14
range.set_style(style)

# Define the pivot table range (A1:B6)
pivot_table_range = f"A1:B{len(data)}"

# Add a pivot table to the worksheet at cell E5
pivot_index = sheet.pivot_tables.add(pivot_table_range, "E5", "PivotTable1")
pivot_table = sheet.pivot_tables[pivot_index]

# Add the "Date" field as a row field
pivot_table.add_field_to_area(PivotFieldType.ROW, 0)  # Date column
# Add the "Value" field as a data field (value field)
pivot_table.add_field_to_area(PivotFieldType.DATA, 1)  # Value column

# Get the PivotField for the Date (Row Field) and group by months
date_pivot_field = pivot_table.row_fields[0]
date_pivot_field.is_auto_sort = True 
date_pivot_field.is_auto_subtotals = True

# Group by Date (by month)
date_pivot_field.group_by(4.0, True)

# Add the "Date" field as row field after group to make it work
pivot_table.add_field_to_area(PivotFieldType.ROW, 0)  

# Enable filtering for the row field (Date)
pivot_table.row_fields[0].show_in_outline_form = True

# Set auto format style for the pivot table
pivot_table.auto_format_type = PivotTableAutoFormatType.REPORT6

# Refresh and calculate the pivot table data
pivot_table.refresh_data()
pivot_table.calculate_data()

# Save the workbook
workbook.save("e:\\test2\\out1_PivotTableGrouped1.xlsx")

Hope, this helps a bit.

Also, you may post your queries in the dedicated forum.

PS. I am working as Support developer/ Evangelist at Aspose.