고객 주문 보고서

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

다음 단계에 따라 '목록' 셀 유형, 필터, 그리고 사용자 정의한 세로 부모 항목을 사용하여 고객 주문 마스터-세부 정보 보고서를 생성할 수 있습니다.

• DataManager에서 Customers, Orders, Employees 및 Shippers 표를 정의합니다.
• 템플릿의 스타일을 설정합니다.
• 데이터 열을 템플릿에 바인딩하고 '목록' 셀 유형을 사용하여 셀의 사용자 정의 세로 부모 항목을 설정합니다.

const spread = new GC.Spread.Sheets.Workbook('ss', { sheetCount: 1 });
spread.options.scrollByPixel = true;
spread.options.scrollbarMaxAlign = true;
const customersTable = spread.dataManager().addTable('Customers', {
    remote: { read: { url: 'https://demodata.mescius.io/northwind/api/v1/customers' } }
});
const ordersTable = spread.dataManager().addTable('Orders', {
    remote: { read: { url: 'https://demodata.mescius.io/northwind/api/v1/orders' } }
});
const employeesTable = spread.dataManager().addTable('Employees', {
    remote: { read: { url: 'https://demodata.mescius.io/northwind/api/v1/employees' } }
});
const shippersTable = spread.dataManager().addTable('Shippers', {
    remote: { read: { url: 'https://demodata.mescius.io/northwind/api/v1/shippers' } }
});

// load the data from the remote
Promise.all([customersTable.fetch(), ordersTable.fetch(), employeesTable.fetch(), shippersTable.fetch()]).then(() => {
    const reportSheet = spread.addSheetTab(0, 'Customer Orders Report', GC.Spread.Sheets.SheetType.reportSheet);
    const templateSheet = reportSheet.getTemplate();

    // set style for the template
    const border = new GC.Spread.Sheets.LineBorder('#9E9E9E', 1);
    const style1 = new GC.Spread.Sheets.Style();
    style1.foreColor = '#424242';
    style1.hAlign = GC.Spread.Sheets.HorizontalAlign.center;
    style1.vAlign = GC.Spread.Sheets.VerticalAlign.center;
    style1.font = '12px Maine';
    const titleStyle = style1.clone();
    titleStyle.hAlign = GC.Spread.Sheets.HorizontalAlign.right;
    const dataStyle = style1.clone();
    dataStyle.hAlign = GC.Spread.Sheets.HorizontalAlign.left;
    dataStyle.textIndent = 1;
    dataStyle.borderBottom = border;
    templateSheet.addSpan(0, 0, 2, 15);
    templateSheet.addSpan(8, 0, 1, 15);
    templateSheet.setStyle(0, 0, style1);
    templateSheet.setValue(0, 0, 'Customer Orders');
    [2, 3, 4].forEach(r => {
        [0, 5, 10].forEach(c => {
            templateSheet.addSpan(r, c, 1, 2);
            templateSheet.addSpan(r, c + 2, 1, 3);
            templateSheet.setStyle(r, c, titleStyle);
            templateSheet.setStyle(r, c + 2, dataStyle);
            templateSheet.setStyle(r, c + 3, dataStyle);
            templateSheet.setStyle(r, c + 4, dataStyle);
        });
    });
    const masterInfos = [
        [2, 0, 'CustomerId'], [2, 5, 'CompanyName'], [2, 10, 'ContactName'],
        [3, 0, 'Country'], [3, 5, 'City'], [3, 10, 'Address'],
        [4, 0, 'PostalCode'], [4, 5, 'Phone'], [4, 10, 'Fax'],
    ];
    masterInfos.forEach(x => templateSheet.setValue(...x));
    const style2 = style1.clone();
    style2.hAlign = GC.Spread.Sheets.HorizontalAlign.right;
    style2.borderBottom = border;
    style2.borderTop = border;
    style2.borderLeft = border;
    const style3 = style2.clone();
    style3.backColor = '#BDBDBD';
    ['OrderId', 'Employee', 'OrderDate', 'RequiredDate', 'ShippedDate', 'Freight', 'ShipVia'].forEach((x, i) => {
        templateSheet.setValue(6, i * 2, x);
        templateSheet.addSpan(6, i * 2, 1, i === 6 ? 3 : 2);
        templateSheet.addSpan(7, i * 2, 1, i === 6 ? 3 : 2);
        templateSheet.setStyle(6, i * 2, style3.clone());
        templateSheet.setStyle(6, i * 2 + 1, style3.clone());
        templateSheet.setStyle(7, i * 2, style2.clone());
        templateSheet.setStyle(7, i * 2 + 1, style2.clone());
        if (i === 6) {
            templateSheet.setStyle(6, i * 2 + 2, style3.clone());
            templateSheet.setStyle(7, i * 2 + 2, style2.clone());
        }
    });
    [4, 6, 8].forEach(c => templateSheet.setFormatter(7, c, 'yyyy-MM-dd'));

    // set binding for the template
    masterInfos.forEach((x, i) => {
        templateSheet.setTemplateCell(x[0], x[1] + 2, {
            type: 'List',
            binding: `Customers[${x[2][0].toLowerCase()}${x[2].substring(1)}]`,
            context: {
                vertical: i !== 0 ? 'C3' : 'Default',
            }
        });
    });
    ['orderId', '', 'orderDate', 'requiredDate', 'shippedDate', 'freight'].forEach((x, i) => {
        if (x) {
            templateSheet.setTemplateCell(7, 2 * i, {
                type: 'List',
                binding: `Orders[${x}]`,
                context: {
                    vertical: i === 0 ? 'C3' : 'Default',
                },
                filter: i === 0 ? {
                    condition: {
                        column: 'customerId',
                        operator: 'Equal',
                        cell: 'C3',
                    }
                } : undefined,
            });
        }
    });
    templateSheet.setTemplateCell(7, 2, {
        type: 'List',
        binding: 'Employees[firstName]',
        filter: {
            condition: {
                column: 'employeeId',
                operator: 'Equal',
                dataColumn: {
                    columnName: 'employeeId',
                    tableName: 'Orders',
                },
            }
        },
    });
    templateSheet.setTemplateCell(7, 12, {
        type: 'List',
        binding: 'Shippers[companyName]',
        filter: {
            condition: {
                column: 'shipperId',
                operator: 'Equal',
                dataColumn: {
                    columnName: 'shipVia',
                    tableName: 'Orders',
                },
            }
        },
    });
    [[0, 0], [2, 10], [3, 5], [3, 10], [4, 5], [4, 10], [8, 0]].forEach(x => {
        templateSheet.setTemplateCell(x[0], x[1], {
            context: {
                vertical: 'C3',
            }
        });
    });

    reportSheet.refresh();
});