Row Layout Sales Report

Open the demo in new window can open the designer

You can create a row-layout 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' cell type.
• Set the row layout setting by the setLayoutSetting API.
• Switch to PaginatedPreview to view the report.

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, 'Row Layout Sales Report', GC.Spread.Sheets.SheetType.reportSheet);
    const templateSheet = reportSheet.getTemplate();

    templateSheet.defaults.colWidth = 100;
    templateSheet.defaults.rowHeight = 30;
    const headerStyle = new GC.Spread.Sheets.Style();
    headerStyle.backColor = '#80CBC4';
    headerStyle.hAlign = GC.Spread.Sheets.HorizontalAlign.center;
    headerStyle.vAlign = GC.Spread.Sheets.VerticalAlign.center;
    headerStyle.foreColor = '#424242';
    headerStyle.font = '12px "Open Sans"';
    const dataStyle = new GC.Spread.Sheets.Style();
    dataStyle.hAlign = GC.Spread.Sheets.HorizontalAlign.center;
    dataStyle.vAlign = GC.Spread.Sheets.VerticalAlign.center;
    dataStyle.foreColor = '#424242';
    dataStyle.font = '12px "Open Sans"';
    const border = new GC.Spread.Sheets.LineBorder('#e0e0e0', 1);
    dataStyle.borderBottom = border;
    dataStyle.borderTop = border;
    dataStyle.borderLeft = border;
    dataStyle.borderRight = border;
    templateSheet.setValue(0, 0, 'Row Layout Sales Report');
    templateSheet.addSpan(0, 0, 1, 4);
    const headerStyle1 = headerStyle.clone();
    headerStyle1.font = '16px "Open Sans"';
    templateSheet.setStyle(0, 0, headerStyle1);
    templateSheet.setRowHeight(0, 50);

    columns.forEach((columnName, i) => {
        templateSheet.setValue(1, i, columnName);
        templateSheet.setStyle(1, i, headerStyle);
        templateSheet.setStyle(2, i, dataStyle);
        templateSheet.setTemplateCell(2, i, {
            type: 'Group',
            binding: `Sales[${columnName}]`,
        });
    });
    templateSheet.setTemplateCell(0, 0, {
        type: 'Static',
        canBreakWhenPaging: false,
    });

    templateSheet.setLayoutSetting({
        type: 'RowLayout',
        dataRange: 'A3:D3',
        rowCount: 25,
        repeatRows: {
            start: 1,
            end: 1,
        }
    });

    reportSheet.renderMode('PaginatedPreview');
});