Sales Report

Open the demo in new window can open the designer

You can use the ‘Group' and 'Summary’ cell types to generate the sales report in the following steps.

• Define the sales table in the DataManager.
• Set the style for the template.
• Bind the data column to the template and use the ‘Group' and 'Summary’ cell types.

const spread = new GC.Spread.Sheets.Workbook('ss', { sheetCount: 1 });
spread.options.scrollByPixel = true;
spread.options.scrollbarMaxAlign = true;
const records = [
    ['East', 'John', 'Banana', 180],
    ['East', 'Mike', 'Banana', 200],
    ['East', 'Emma', 'Banana', 210],
    ['East', 'Sophia', 'Banana', 316],
    ['East', 'Jennifer', 'Banana', 130],
    ['North', 'Kristen', 'Banana', 410.0],
    ['North', 'Mia', 'Banana', 290.0],
    ['North', 'Bella', 'Banana', 342.0],
    ['North', 'Eva', 'Banana', 214.0],
    ['East', 'John', 'Strawberry', 230.0],
    ['East', 'Mike', 'Strawberry', 641.0],
    ['East', 'Emma', 'Strawberry', 234.0],
    ['East', 'Sophia', 'Strawberry', 625.0],
    ['East', 'Jennifer', 'Strawberry', 241.0],
    ['North', 'Kristen', 'Strawberry', 195.0],
    ['North', 'Mia', 'Strawberry', 569.0],
    ['North', 'Bella', 'Strawberry', 698.0],
    ['North', 'Eva', 'Strawberry', 214.0],
    ['East', 'John', 'Watermelon', 147.0],
    ['East', 'Mike', 'Watermelon', 489.0],
    ['East', 'Emma', 'Watermelon', 347.0],
    ['East', 'Sophia', 'Watermelon', 652.0],
    ['East', 'Jennifer', 'Watermelon', 471.0],
    ['North', 'Kristen', 'Watermelon', 287.0],
    ['North', 'Mia', 'Watermelon', 349.0],
    ['North', 'Bella', 'Watermelon', 163.0],
    ['North', 'Eva', 'Watermelon', 841.0],
    ['East', 'John', 'Chips', 292.0],
    ['East', 'Mike', 'Chips', 514.0],
    ['East', 'Emma', 'Chips', 256.0],
    ['East', 'Sophia', 'Chips', 148.0],
    ['East', 'Jennifer', 'Chips', 486.0],
    ['North', 'Kristen', 'Chips', 285.0],
    ['North', 'Mia', 'Chips', 741.0],
    ['North', 'Bella', 'Chips', 249.0],
    ['North', 'Eva', 'Chips', 105.0],
    ['East', 'John', 'Cookie', 554.0],
    ['East', 'Mike', 'Cookie', 311.0],
    ['East', 'Emma', 'Cookie', 186.0],
    ['East', 'Sophia', 'Cookie', 654.0],
    ['East', 'Jennifer', 'Cookie', 247.0],
    ['North', 'Kristen', 'Cookie', 143.0],
    ['North', 'Mia', 'Cookie', 617.0],
    ['North', 'Bella', 'Cookie', 214.0],
    ['North', 'Eva', 'Cookie', 324.0],
];
const columns = ['Region', 'Salesman', 'Product', 'Sales'];
const salesTable = spread.dataManager().addTable('Sales', {
    data: records.map((x) => {
        const record = {};
        columns.forEach((c, i) => record[c] = x[i]);
        return record;
    })
});

salesTable.fetch().then(() => {
    const reportSheet = spread.addSheetTab(0, 'Sales Report', GC.Spread.Sheets.SheetType.reportSheet);
    const templateSheet = reportSheet.getTemplate();

    // set style for the template
    templateSheet.defaults.colWidth = 100;
    const headerStyle = new GC.Spread.Sheets.Style();
    headerStyle.backColor = '#91ACDA';
    headerStyle.foreColor = '#424242';
    headerStyle.hAlign = GC.Spread.Sheets.HorizontalAlign.center;
    headerStyle.font = '12px Maine';
    const dataStyle = new GC.Spread.Sheets.Style();
    dataStyle.foreColor = '#424242';
    dataStyle.hAlign = GC.Spread.Sheets.HorizontalAlign.center;
    dataStyle.vAlign = GC.Spread.Sheets.VerticalAlign.center;
    dataStyle.font = '12px Maine';
    const border = new GC.Spread.Sheets.LineBorder('#E0E0E0', 1);
    dataStyle.borderBottom = border;
    dataStyle.borderTop = border;
    dataStyle.borderLeft = border;
    dataStyle.borderRight = border;
    const dataStyle1 = dataStyle.clone();
    dataStyle1.backColor = '#FFF8E1';
    const dataStyle2 = dataStyle.clone();
    dataStyle2.backColor = '#FFE082';
    templateSheet.getRange('A1:D1').setStyle(headerStyle);
    templateSheet.getRange('A2:D4').setStyle(dataStyle);
    templateSheet.getRange('C3:D3').setStyle(dataStyle1);
    templateSheet.getRange('B4:D4').setStyle(dataStyle2);
    [[1, 0, 3, 1], [1, 1, 2, 1], [3, 1, 1, 2]].forEach(x => templateSheet.addSpan(...x));
    [[2, 2], [3, 1]].forEach(x => {
        templateSheet.setValue(x[0], x[1], 'Total');
        const style = templateSheet.getStyle(...x).clone();
        style.hAlign = GC.Spread.Sheets.HorizontalAlign.left;
        templateSheet.setStyle(x[0], x[1], style);
    });
    [1, 2, 3].forEach(r => {
        const style = templateSheet.getStyle(r, 3).clone();
        style.hAlign = GC.Spread.Sheets.HorizontalAlign.right;
        templateSheet.setStyle(r, 3, style);
    });
    templateSheet.setValue(3, 1, 'Total');

    // set binding for the template
    columns.forEach((columnName, i) => {
        templateSheet.setValue(0, i, columnName);
        if (i < 3) {
            templateSheet.setTemplateCell(1, i, {
                type:'Group',
                binding: `Sales[${columnName}]`,
            });
        }
        else {
            [1, 2, 3].forEach(c => {
                templateSheet.setTemplateCell(c, i, {
                    type: 'Summary',
                    aggregate: 'Sum',
                    binding: `Sales[${columnName}]`,
                });
            })
        }
    });

    reportSheet.refresh();
});