재무 보고서

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

다음 단계에 따라 '그룹' 셀 유형, 필터 및 고정을 사용하여 재무 보고서를 생성할 수 있습니다.

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

const spread = new GC.Spread.Sheets.Workbook('ss', { sheetCount: 1 });
spread.options.scrollByPixel = true;
spread.options.scrollbarMaxAlign = true;
const records = [
    [2021, 'Q1', 'USA', 236047, 328554],
    [2021, 'Q2', 'USA', 373060, 238136],
    [2021, 'Q3', 'USA', 224132, 300822],
    [2021, 'Q4', 'USA', 269305, 315337],
    [2022, 'Q1', 'USA', 265397, 279008],
    [2022, 'Q2', 'USA', 214079, 206019],
    [2022, 'Q3', 'USA', 370191, 238294],
    [2022, 'Q4', 'USA', 266843, 242323],
    [2021, 'Q1', 'Japan', 350156, 370834],
    [2021, 'Q2', 'Japan', 369399, 247324],
    [2021, 'Q3', 'Japan', 278834, 237385],
    [2021, 'Q4', 'Japan', 264277, 245048],
    [2022, 'Q1', 'Japan', 203006, 295389],
    [2022, 'Q2', 'Japan', 276987, 215804],
    [2022, 'Q3', 'Japan', 330315, 330443],
    [2022, 'Q4', 'Japan', 307477, 262512],
    [2021, 'Q1', 'Korea', 229432, 330368],
    [2021, 'Q2', 'Korea', 321904, 279114],
    [2021, 'Q3', 'Korea', 230496, 219257],
    [2021, 'Q4', 'Korea', 254328, 361880],
    [2022, 'Q1', 'Korea', 272263, 355419],
    [2022, 'Q2', 'Korea', 214079, 231510],
    [2022, 'Q3', 'Korea', 238392, 237430],
    [2022, 'Q4', 'Korea', 294097, 257680],
    [2021, 'Q1', 'China', 238175, 266070],
    [2021, 'Q2', 'China', 202721, 353563],
    [2021, 'Q3', 'China', 253279, 312586],
    [2021, 'Q4', 'China', 211847, 306970],
    [2022, 'Q1', 'China', 369314, 315718],
    [2022, 'Q2', 'China', 201224, 368630],
    [2022, 'Q3', 'China', 239792, 255108],
    [2022, 'Q4', 'China', 271096, 297354],
    [2021, 'Q1', 'India', 236047, 328554],
    [2021, 'Q2', 'India', 373060, 238136],
    [2021, 'Q3', 'India', 224132, 300822],
    [2021, 'Q4', 'India', 269305, 315337],
    [2022, 'Q1', 'India', 265397, 279008],
    [2022, 'Q2', 'India', 214079, 206019],
    [2022, 'Q3', 'India', 370191, 238294],
    [2022, 'Q4', 'India', 266843, 242323],
];
const columns = ['Year', 'Quarter', 'Country'];
const budTable = spread.dataManager().addTable('Bud', {
    data: records.map((x) => {
        const record = {};
        columns.forEach((c, i) => record[c] = x[i]);
        record['Bud'] = x[3]
        return record;
    })
});
const actTable = spread.dataManager().addTable('Act', {
    data: records.map((x) => {
        const record = {};
        columns.forEach((c, i) => record[c] = x[i]);
        record['Act'] = x[4]
        return record;
    })
});

Promise.all([budTable.fetch(), actTable.fetch()]).then(() => {
    const reportSheet = spread.addSheetTab(0, 'Financial Report', GC.Spread.Sheets.SheetType.reportSheet);
    const templateSheet = reportSheet.getTemplate();

    // set style for the template
    templateSheet.options.gridline = {
        showVerticalGridline: false,
        showHorizontalGridline: false,
    };
    templateSheet.defaults.colWidth = 100;
    templateSheet.setRowHeight(0, 70);
    const style = new GC.Spread.Sheets.Style();
    style.foreColor = '#424242';
    style.hAlign = GC.Spread.Sheets.HorizontalAlign.left;
    style.vAlign = GC.Spread.Sheets.VerticalAlign.bottom;
    style.font = '16px Maine';
    templateSheet.getRange('A1:D1').setStyle(style);
    const style1 = style.clone();
    style1.hAlign = GC.Spread.Sheets.HorizontalAlign.center;
    style1.vAlign = GC.Spread.Sheets.VerticalAlign.center;
    style1.font = '12px Maine';
    style1.backColor = '#2F77B7';
    style1.foreColor = 'white';
    templateSheet.setStyle(2, 2, style1);
    const style2 = style1.clone();
    style2.font = 'bold 12px Maine';
    style2.backColor = '#BDD7EE';
    style2.foreColor = '#424242';
    templateSheet.setStyle(3, 2, style2);
    ['#7F7F7F', '#7F7F7F', '#C75B12', '#AB6C8E'].forEach((x, i) => {
        const newStyle = style1.clone();
        newStyle.backColor = x;
        templateSheet.setStyle(4, i, newStyle);
    });
    const border = new GC.Spread.Sheets.LineBorder('#E0E0E0', 1);
    [0, 1].forEach((i) => {
        const newStyle = style1.clone();
        newStyle.backColor = undefined;
        newStyle.foreColor = '#4472C4';
        newStyle.hAlign = GC.Spread.Sheets.HorizontalAlign.left;
        newStyle.textIndent = 1;
        newStyle.borderBottom = border;
        templateSheet.setStyle(5, i, newStyle);
    });
    [2, 3].forEach(i => {
        const newStyle = new GC.Spread.Sheets.Style();
        newStyle.font = '12px Maine';
        newStyle.foreColor = '#424242';
        newStyle.borderBottom = border;
        newStyle.hAlign = GC.Spread.Sheets.HorizontalAlign.right;
        newStyle.vAlign = GC.Spread.Sheets.VerticalAlign.bottom;
        templateSheet.setStyle(5, i, newStyle);

        [6, 7, 8].forEach(r => {
            const newStyle2 = new GC.Spread.Sheets.Style();
            newStyle2.font = 'bold 12px Maine';
            newStyle2.hAlign = GC.Spread.Sheets.HorizontalAlign.right;
            newStyle2.vAlign = GC.Spread.Sheets.VerticalAlign.bottom;
            templateSheet.setStyle(r, i, newStyle2);
        });
    });

    [[2, 2, 1, 2], [3, 2, 1, 2]].forEach(x => templateSheet.addSpan(...x));
    templateSheet.setValue(0, 0, 'FINANCIAL REPORT');
    templateSheet.setValue(0, 3, '2021 - 2022');
    templateSheet.setValue(2, 2, 'BUDGET STATISTICS');
    templateSheet.setValue(4, 2, 'BUD');
    templateSheet.setValue(4, 3, 'ACT');
    templateSheet.setValue(7, 2, 'TOTAL EST');
    templateSheet.setValue(8, 2, 'TOTAL ACTUAL');
    templateSheet.setFormula(6, 2, 'SUM(C6)');
    templateSheet.setFormula(6, 3, 'SUM(D6)');
    templateSheet.setFormula(7, 3, 'SUM(C7)');
    templateSheet.setFormula(8, 3, 'SUM(D7)');

    // set binding for the template
    ['Year', 'Quarter', 'Bud'].forEach((x, i) => {
        templateSheet.setTemplateCell(5, i, {
            type: 'Group',
            binding: `Bud[${x}]`,
        });
    });
    templateSheet.setTemplateCell(3, 2, {
        type: 'Group',
        binding: 'Bud[Country]',
        spillDirection: 'Horizontal',
    });
    templateSheet.setTemplateCell(5, 3, {
        type: 'Group',
        binding: 'Act[Act]',
        filter: {
            condition: {
                and: [
                    {
                        column: 'Country',
                        operator: 'Equal',
                        cell: 'C4',
                    },
                    {
                        column: 'Quarter',
                        operator: 'Equal',
                        cell: 'B6',
                    },
                    {
                        column: 'Year',
                        operator: 'Equal',
                        cell: 'A6'
                    },
                ]
            }
        },
    });
    [7, 8].forEach(r => {
        [2, 3].forEach(c => {
            templateSheet.setTemplateCell(r, c, {
                type: 'Static',
                context: {
                    horizontal: 'None',
                },
                pin: c === 2 ? 'Both' : 'None',
            });
        });
    });
    templateSheet.setTemplateCell(0, 2, {
        type: 'Static',
        pin: 'Both',
    });

    reportSheet.refresh();
});