판매 교차 보고서

새 창에서 데모를 열어 디자이너 열기

다음 단계에 따라 '그룹' 및 '요약' 셀 유형을 사용하여 판매 교차 보고서를 생성할 수 있습니다.

• DataManager에서 판매 표를 정의합니다.
• 템플릿의 스타일을 설정합니다.
• 데이터 열을 템플릿에 바인딩하고, '그룹' 셀 유형, ‘요약’ 셀 유형과 함께 '가로' 및 '세로' 분산 방향을 모두 사용합니다.
• 요약 셀의 수식을 추가합니다.

const spread = new GC.Spread.Sheets.Workbook('ss', { sheetCount: 1 });
spread.options.scrollByPixel = true;
spread.options.scrollbarMaxAlign = true;
const records = [
    ['East China', 'John', 'Fruits', 'Banana', 180, 6],
    ['East China', 'Mike', 'Fruits', 'Banana', 200, 21],
    ['East China', 'Emma', 'Fruits', 'Banana', 210, 16],
    ['East China', 'Sophia', 'Fruits', 'Banana', 316, 5],
    ['East China', 'Jennifer', 'Fruits', 'Banana', 130, 11],
    ['North China', 'Kristen', 'Fruits', 'Banana', 410.0, 21],
    ['North China', 'Mia', 'Fruits', 'Banana', 290.0, 2],
    ['North China', 'Bella', 'Fruits', 'Banana', 342.0, 45],
    ['North China', 'Eva', 'Fruits', 'Banana', 214.0, 21],
    ['East China', 'John', 'Fruits', 'Strawberry', 230.0, 15],
    ['East China', 'Mike', 'Fruits', 'Strawberry', 641.0, 33],
    ['East China', 'Emma', 'Fruits', 'Strawberry', 234.0, 15],
    ['East China', 'Sophia', 'Fruits', 'Strawberry', 625.0, 53],
    ['East China', 'Jennifer', 'Fruits', 'Strawberry', 241.0, 25],
    ['North China', 'Kristen', 'Fruits', 'Strawberry', 195.0, 36],
    ['North China', 'Mia', 'Fruits', 'Strawberry', 569.0, 23],
    ['North China', 'Bella', 'Fruits', 'Strawberry', 698.0, 51],
    ['North China', 'Eva', 'Fruits', 'Strawberry', 214.0, 67],
    ['East China', 'John', 'Fruits', 'Watermelon', 147.0, 36],
    ['East China', 'Mike', 'Fruits', 'Watermelon', 489.0, 26],
    ['East China', 'Emma', 'Fruits', 'Watermelon', 347.0, 27],
    ['East China', 'Sophia', 'Fruits', 'Watermelon', 652.0, 36],
    ['East China', 'Jennifer', 'Fruits', 'Watermelon', 471.0, 31],
    ['North China', 'Kristen', 'Fruits', 'Watermelon', 287.0, 43],
    ['North China', 'Mia', 'Fruits', 'Watermelon', 349.0, 21],
    ['North China', 'Bella', 'Fruits', 'Watermelon', 163.0, 35],
    ['North China', 'Eva', 'Fruits', 'Watermelon', 841.0, 36],
    ['East China', 'John', 'Snack', 'Chips', 292.0, 72],
    ['East China', 'Mike', 'Snack', 'Chips', 514.0, 13],
    ['East China', 'Emma', 'Snack', 'Chips', 256.0, 5],
    ['East China', 'Sophia', 'Snack', 'Chips', 148.0, 31],
    ['East China', 'Jennifer', 'Snack', 'Chips', 486.0, 53],
    ['North China', 'Kristen', 'Snack', 'Chips', 285.0, 13],
    ['North China', 'Mia', 'Snack', 'Chips', 741.0, 42],
    ['North China', 'Bella', 'Snack', 'Chips', 249.0, 13],
    ['North China', 'Eva', 'Snack', 'Chips', 105.0, 53],
    ['East China', 'John', 'Snack', 'Cookie', 554.0, 7],
    ['East China', 'Mike', 'Snack', 'Cookie', 311.0, 38],
    ['East China', 'Emma', 'Snack', 'Cookie', 186.0, 35],
    ['East China', 'Sophia', 'Snack', 'Cookie', 654.0, 28],
    ['East China', 'Jennifer', 'Snack', 'Cookie', 247.0, 19],
    ['North China', 'Kristen', 'Snack', 'Cookie', 143.0, 40],
    ['North China', 'Mia', 'Snack', 'Cookie', 617.0, 23],
    ['North China', 'Bella', 'Snack', 'Cookie', 214.0, 53],
    ['North China', 'Eva', 'Snack', 'Cookie', 324.0, 37],
];
const columns = ['Region', 'Salesman', 'ProductType', 'Product', 'Sales', 'Return'];
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, 'report1', 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();
    const border = new GC.Spread.Sheets.LineBorder('#BDBDBD', 1);
    headerStyle.backColor = '#91ACDA';
    headerStyle.foreColor = '#424242';
    headerStyle.hAlign = GC.Spread.Sheets.HorizontalAlign.center;
    headerStyle.vAlign = GC.Spread.Sheets.VerticalAlign.center;
    headerStyle.font = '12px Maine';
    headerStyle.borderBottom = border;
    headerStyle.borderTop = border;
    headerStyle.borderLeft = border;
    headerStyle.borderRight = border;
    const dataStyle = headerStyle.clone();
    dataStyle.backColor = undefined;

    const headerStyle1 = dataStyle.clone();
    headerStyle1.backColor = '#81C784';
    const headerStyle2 = dataStyle.clone();
    headerStyle2.backColor = '#C8E6C9';
    const dataStyle1 = dataStyle.clone();
    dataStyle1.backColor = '#FFF8E1';
    const dataStyle2 = dataStyle.clone();
    dataStyle2.backColor = '#FFE082';
    const dataStyle3 = dataStyle.clone();
    dataStyle3.backColor = '#FFC107';
    templateSheet.getRange('A1:F3').setStyle(headerStyle);
    templateSheet.getRange('C1:D1').setStyle(headerStyle1);
    templateSheet.getRange('C2:D2').setStyle(headerStyle2);
    templateSheet.getRange('A4:A5').setStyle(dataStyle);
    templateSheet.getRange('B4:D4').setStyle(dataStyle);
    templateSheet.getRange('E4:F4').setStyle(dataStyle1);
    templateSheet.getRange('B5:D5').setStyle(dataStyle1);
    templateSheet.getRange('E5:F5').setStyle(dataStyle2);
    templateSheet.getRange('A6:F6').setStyle(dataStyle3);

    [[0, 0, 3, 1], [0, 1, 3, 1], [0, 2, 1, 2], [1, 2, 1, 2], [0, 4, 2, 2], [3, 0, 2, 1], [5, 0, 1, 2]].forEach(x => templateSheet.addSpan(...x));
    [[3, 4], [4, 2], [4, 4], [5, 2], [5, 4]].forEach(x => {
        templateSheet.setFormula(x[0], x[1], 'SUM(C4)');
        templateSheet.setFormula(x[0], x[1] + 1, 'SUM(D4)');
    })
    templateSheet.setValue(0, 0, 'Region');
    templateSheet.setValue(0, 1, 'Salesman');
    templateSheet.setValue(2, 2, 'Sales');
    templateSheet.setValue(2, 3, 'Return');
    templateSheet.setValue(2, 4, 'Sales');
    templateSheet.setValue(2, 5, 'Return');
    templateSheet.setValue(0, 4, 'Total');
    templateSheet.setValue(4, 1, 'Total');
    templateSheet.setValue(5, 0, 'Total');

    // set binding for the template
    templateSheet.setTemplateCell(0, 2, {
        type: 'Group',
        binding: 'Sales[ProductType]',
        spillDirection: 'Horizontal',
    });
    templateSheet.setTemplateCell(1, 2, {
        type: 'Group',
        binding: 'Sales[Product]',
        spillDirection: 'Horizontal',
    });
    templateSheet.setTemplateCell(3, 0, {
        type: 'Group',
        binding: 'Sales[Region]',
    });
    templateSheet.setTemplateCell(3, 1, {
        type: 'Group',
        binding: 'Sales[Salesman]',
    });
    templateSheet.setTemplateCell(3, 2, {
        type: 'Summary',
        aggregate: 'Sum',
        binding: 'Sales[Sales]',
    });
    templateSheet.setTemplateCell(3, 3, {
        type: 'Summary',
        aggregate: 'Sum',
        binding: 'Sales[Return]',
    });

    reportSheet.refresh();
});