Advanced Group
In addition to grouping by field values directly, SpreadJS supports advanced grouping that groups data by condition rules or formula calculated results.
Description
app.js
index.html
styles.css
Copy to CodeMine
Open the demo in new window can open the designer
The advanced group interface is as follows:
type GroupCell = DataCellBase & {
type: 'Group';
bins?: IGroupBin[];
formula?: string;
};
interface IGroupBin {
caption: string;
groupBy: string; // formula string
stopIfTrue?: boolean; // whether one record matches this rule then keep going to match other rules, default is true
alwaysVisible?: boolean; // whether this rule is visible when there is no record matches this rule, default is false
}
There are several usages for advanced grouping:
Group by rules.
Users can add multiple advanced rules to match different records.
const spread = new GC.Spread.Sheets.Workbook('ss', { sheetCount: 1 }); const salesTable = spread.dataManager().addTable('Sales', { remote: { read: { url: 'https://developer.mescius.com/spreadjs/demos/server/api/Sales' } } }); salesTable.fetch().then(() => { const reportSheet = spread.addSheetTab(0, 'report1', GC.Spread.Sheets.SheetType.reportSheet); const templateSheet = reportSheet.getTemplate(); // set value and binding for the template templateSheet.setTemplateCell(1, 0, { type: 'Group', binding: "Sales[Sales]", bins: [ // group data by sales value. {caption: "Sales <= 200", groupBy: "=([@Sales] <= 200)"}, {caption: "Sales > 200 && Sales <= 500", groupBy: "=AND([@Sales] > 200, [@Sales] <= 500)"}, {caption: "Sales > 500", groupBy: "=([@Sales] > 500)"}, ] }); templateSheet.setTemplateCell(1, 1, { type: 'Group', binding: `Sales[Salesman]`, }); templateSheet.setTemplateCell(1, 2, { type: 'Group', binding: `Sales[Sales]`, }); templateSheet.setTemplateCell(1, 3, { type: 'Group', binding: `Sales[Product]`, }); reportSheet.refresh(); });Group by formulas.
Users can also use a formula to calculate all records then group by the formula calculated result.
const spread = new GC.Spread.Sheets.Workbook('ss', { sheetCount: 1 }); const salesTable = spread.dataManager().addTable('Orders', { remote: { read: { url: 'https://demodata.mescius.io/northwind/api/v1/orders' } } }); salesTable.fetch().then(() => { const reportSheet = spread.addSheetTab(0, 'report1', GC.Spread.Sheets.SheetType.reportSheet); const templateSheet = reportSheet.getTemplate(); // set value and binding for the template templateSheet.setTemplateCell(1, 0, { type: 'Group', binding: "Orders[OrderDate]", formula: '=YEAR([@OrderDate])&"/"&MONTH([@OrderDate])' // group data by year/month }); templateSheet.setTemplateCell(1, 1, { type: 'Group', binding: `Orders[ShipName]`, }); templateSheet.setTemplateCell(1, 2, { type: 'Summary', aggregate: "Sum", binding: `Orders[Freight]`, }); reportSheet.refresh(); });
Feedback
Submit and view feedback for