Supported Functionality

Aspose.Cells Product Family
1

Does Aspose.cells support GroupBy and PivotBy cell formula functions and BYROW and BYCOL lambda functions ?
If not, are they on the roadmap?

Thanks,
Dean

2

@Moonglum

Aspose.Cells does support grouping functionality in pivot tables, which can be utilized to group data by various criteria, such as months and quarters. You can achieve this by using the setManualGroupField method on a pivot table object.

Here’s a brief example of how to group pivot fields in a pivot table:

import com.aspose.cells.*;

public class GroupPivotFieldsExample {
    public static void main(String[] args) throws Exception {
        // Load the workbook
        Workbook workbook = new Workbook("sampleGroupPivotFieldsInPivotTable.xlsx");
        
        // Access the second worksheet
        Worksheet worksheet = workbook.getWorksheets().get(1);
        
        // Access the pivot table
        PivotTable pivotTable = worksheet.getPivotTables().get(0);
        
        // Specify the start and end date time for grouping
        DateTime startDate = new DateTime(2008, 1, 1);
        DateTime endDate = new DateTime(2008, 9, 5);
        
        // Specify the group types (months and quarters)
        ArrayList<PivotGroupByType> groupTypeList = new ArrayList<>();
        groupTypeList.add(PivotGroupByType.MONTHS);
        groupTypeList.add(PivotGroupByType.QUARTERS);
        
        // Apply the grouping on the first pivot field
        pivotTable.setManualGroupField(0, startDate, endDate, groupTypeList, 1);
        
        // Refresh and calculate the pivot table
        pivotTable.setRefreshDataFlag(true);
        pivotTable.refreshData();
        pivotTable.calculateData();
        pivotTable.setRefreshDataFlag(false);
        
        // Save the modified workbook
        workbook.save("outputGroupPivotFieldsInPivotTable.xlsx");
        
        System.out.println("Pivot fields grouped successfully.");
    }
}

This code snippet demonstrates how to group pivot fields by months and quarters in a pivot table. As for the GroupBy and PivotBy functions specifically, they are not explicitly mentioned in the context, so I recommend checking the official Aspose.Cells documentation or the latest release notes for any updates regarding these functionalities and their roadmap.

If you have further questions or need more specific examples, feel free to ask!

Sources:
[1]: GroupPivotFieldsInPivotTable.java

3

@Moonglum,

Aspose.Cells currently does not support GROUPBY, PIVOTBY, BYROW, or BYCOL cell formula functions. However, we may evaluate these functions later on. Could you please tell which are the most important formula/function (out of those four functions/formulas) for your scenario/case and how do you want us to prioritize which function(s) should be supported at first? Then we may log appropriate tickets into our database.

4

ByRow and ByCol would be our highest priority.

5

@Moonglum
We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): 
CELLSNET-57271: Support BYROW and BYCOL lambda functions
CELLSNET-57270: Support GROUPBY and PIVOTBY cell formula functions

You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.