Sorting/Filtering In Grouping

In SpreadJS, the TableSheet offers powerful features for data manipulation, such as sorting and filtering.

TableSheet supports sorting in groups that works between the grouped columns and the base columns. By default, auto-sorting is enabled. To disable sorting for a grouped column, set the allowSort property to false in the grouped column configuration.

The following image shows that sort results are based on the grouping results, where the “Selling Package” grouped column is in ascending order whereas the “Year Quarter” grouped column and “Stock Item Key” base column are in descending order.

The following behavior is observed when using sort in tablesheet grouping.

The sort status is shared between the source of the group column and itself.
Only one column can be sorted at a group level.
The last sorting status before applying groupBy and after applying removeGroupBy methods are retained.

On the other hand, filtering allows users to display only the relevant data based on selected criteria. By default, auto-filter is enabled in the grouping.

Filter_TSGrouping

To disable filtering in a grouped column, set the allowFilterByValue and allowFilterByList property to false.

The following sample code disables sorting and filtering within a grouped context. This is accomplished by setting the properties allowSort, allowFilterByValue, and allowFilterByList to false. With these settings, users will be restricted from accessing the Sort/Filter dialog, preventing any sorting or filtering operations in the grouped data.

var spread, sheet;
$(document).ready(function () {
    // Initialize Spread.
    spread = new GC.Spread.Sheets.Workbook(document.getElementById('ss'), { sheetCount: 0 });
    spread.options.autoFitType = GC.Spread.Sheets.AutoFitType.cellWithHeader;

    // Initialize a data manager.
    var dataManager = spread.dataManager();
    var myTable = dataManager.addTable("myTable", {
        remote: {
            read: {
                url: 'https://demodata.grapecity.com/wwi/api/v1/stockItems'
            }
        },
        schema: {
            columns: {
                validFrom: { dataType: "date" }
            }
        }
    });

    // Initialize a tablesheet.
    sheet = spread.addSheetTab(0, "TableSheet1", GC.Spread.Sheets.SheetType.tableSheet);

    // Bind a view to the tablesheet.
    myTable.fetch().then(function () {
        var view = myTable.addView("myView", [
            { value: "stockItemKey", width: 150, caption: "Stock Item Key" },
            { value: "stockItem", width: 120, caption: "Stock Item" },
            { value: "buyingPackage", width: 120, caption: "Buying Package" },
            { value: "sellingPackage", width: 120, caption: "Selling Package" },
            { value: "unitPrice", width: 150, caption: "Unit Price" },
            { value: "taxRate", width: 120, caption: "Tax Rate" },
            { value: "validFrom", width: 200, caption: "Valid From" }
        ]);
        sheet.setDataView(view);

    });
});

function grouping() {
    // Create groupBy by single field (Selling package), with summary fields and slice fields.
    sheet.groupBy([
        {
            caption: "Selling Package", field: "sellingPackage", width: 150, style: { backColor: "#F9CA9A" },
            allowSort: false,
            allowFilterByValue: false,
            allowFilterByList: false,
            summaryFields: [
                {
                    caption: "SUM(Unit Price)",
                    formula: "=SUM([unitPrice])",
                    slice: { field: "=YEAR([@validFrom])", width: 120, style: { backColor: "#FCE3CA", formatter: "#,##0.00" } },
                    width: 150,
                    style: { backColor: "#FAD7B2", formatter: "#,##0.00" }
                }
            ]
        }
    ]);
}

function removeGroup() {
    // Remove group.
    sheet.removeGroupBy();
}