판매 보고서는 개별 영업사원 또는 제품군의 실적을 추적하고, 소비자 행동의 추세를 파악하고, 전반적인 수익 증가 또는 감소를 확인하는 데 유용합니다.
다음 단계에 따라 '그룹' 및 '요약' 셀 유형을 사용하여 다중 섹션 판매 보고서를 생성할 수 있습니다.
window.onload = async () => {
const spread = new GC.Spread.Sheets.Workbook('demo-host', { sheetCount: 1 });
await addSalesTable(spread).fetch();
addMultiSectionsReport(spread);
}
function addSalesTable(spread) {
const records = [
['East China', 'John', 'Fruits', 'Banana', 180],
['East China', 'Mike', 'Fruits', 'Banana', 200],
['East China', 'Emma', 'Fruits', 'Banana', 210],
['East China', 'Sophia', 'Fruits', 'Banana', 316],
['East China', 'Jennifer', 'Fruits', 'Banana', 130],
['North China', 'Kristen', 'Fruits', 'Banana', 410.0],
['North China', 'Mia', 'Fruits', 'Banana', 290.0],
['North China', 'Bella', 'Fruits', 'Banana', 342.0],
['North China', 'Eva', 'Fruits', 'Banana', 214.0],
['East China', 'John', 'Fruits', 'Strawberry', 230.0],
['East China', 'Mike', 'Fruits', 'Strawberry', 641.0],
['East China', 'Emma', 'Fruits', 'Strawberry', 234.0],
['East China', 'Sophia', 'Fruits', 'Strawberry', 625.0],
['East China', 'Jennifer', 'Fruits', 'Strawberry', 241.0],
['North China', 'Kristen', 'Fruits', 'Strawberry', 195.0],
['North China', 'Mia', 'Fruits', 'Strawberry', 569.0],
['North China', 'Bella', 'Fruits', 'Strawberry', 698.0],
['North China', 'Eva', 'Fruits', 'Strawberry', 214.0],
['East China', 'John', 'Fruits', 'Watermelon', 147.0],
['East China', 'Mike', 'Fruits', 'Watermelon', 489.0],
['East China', 'Emma', 'Fruits', 'Watermelon', 347.0],
['East China', 'Sophia', 'Fruits', 'Watermelon', 652.0],
['East China', 'Jennifer', 'Fruits', 'Watermelon', 471.0],
['North China', 'Kristen', 'Fruits', 'Watermelon', 287.0],
['North China', 'Mia', 'Fruits', 'Watermelon', 349.0],
['North China', 'Bella', 'Fruits', 'Watermelon', 163.0],
['North China', 'Eva', 'Fruits', 'Watermelon', 841.0],
['East China', 'John', 'Snack', 'Chips', 292.0],
['East China', 'Mike', 'Snack', 'Chips', 514.0],
['East China', 'Emma', 'Snack', 'Chips', 256.0],
['East China', 'Sophia', 'Snack', 'Chips', 148.0],
['East China', 'Jennifer', 'Snack', 'Chips', 486.0],
['North China', 'Kristen', 'Snack', 'Chips', 285.0],
['North China', 'Mia', 'Snack', 'Chips', 741.0],
['North China', 'Bella', 'Snack', 'Chips', 249.0],
['North China', 'Eva', 'Snack', 'Chips', 105.0],
['East China', 'John', 'Snack', 'Cookie', 554.0],
['East China', 'Mike', 'Snack', 'Cookie', 311.0],
['East China', 'Emma', 'Snack', 'Cookie', 186.0],
['East China', 'Sophia', 'Snack', 'Cookie', 654.0],
['East China', 'Jennifer', 'Snack', 'Cookie', 247.0],
['North China', 'Kristen', 'Snack', 'Cookie', 143.0],
['North China', 'Mia', 'Snack', 'Cookie', 617.0],
['North China', 'Bella', 'Snack', 'Cookie', 214.0],
['North China', 'Eva', 'Snack', 'Cookie', 324.0],
];
const columns = ['Region', 'Salesman', 'ProductType', 'Product', 'Sales'];
return spread.dataManager().addTable('Sales', {
data: records.map((x) => {
const record = {};
columns.forEach((c, i) => record[c] = x[i]);
return record;
})
});
}
function addMultiSectionsReport(spread) {
const reportSheet = spread.addSheetTab(0, 'Multi Sections Sales Report', GC.Spread.Sheets.SheetType.reportSheet);
const templateSheet = reportSheet.getTemplate();
const { Style, HorizontalAlign, VerticalAlign, LineBorder } = GC.Spread.Sheets;
templateSheet.options.gridline.showHorizontalGridline = false;
templateSheet.options.gridline.showVerticalGridline = false;
templateSheet.defaults.colWidth = 100;
templateSheet.defaults.rowHeight = 30;
templateSheet.setColumnWidth(0, 10);
templateSheet.setRowHeight(0, 10);
const headerStyle = new Style();
headerStyle.backColor = '#BBDEFB';
headerStyle.hAlign = HorizontalAlign.left;
headerStyle.vAlign = VerticalAlign.center;
headerStyle.textIndent = 1;
headerStyle.foreColor = '#424242';
headerStyle.font = '12px "Open Sans"';
const dataStyle = new Style();
dataStyle.hAlign = HorizontalAlign.left;
dataStyle.vAlign = VerticalAlign.center;
dataStyle.textIndent = 1;
dataStyle.foreColor = '#424242';
dataStyle.font = '12px "Open Sans"';
const border = new LineBorder('#e0e0e0', 1);
dataStyle.borderBottom = border;
// section 1
templateSheet.setValue(1, 1, 'Product Sales');
templateSheet.setValue(2, 1, 'Salesman');
templateSheet.getRange(2, 1, 1, 2).setStyle(headerStyle);
templateSheet.getRange(3, 1, 1, 2).setStyle(dataStyle);
templateSheet.getStyle(2, 2).hAlign = HorizontalAlign.right;
templateSheet.getStyle(3, 2).hAlign = HorizontalAlign.right;
templateSheet.setTemplateCell(3, 1, {
type: 'Group',
binding: 'Sales[Salesman]',
});
templateSheet.setTemplateCell(2, 2, {
type: 'Group',
binding: 'Sales[Product]',
spillDirection: 'Horizontal',
});
templateSheet.setTemplateCell(3, 2, {
type: 'Summary',
binding: 'Sales[Sales]',
aggregate: 'Sum',
});
// section 2
templateSheet.setValue(5, 1, 'ProductType Sales');
templateSheet.setValue(6, 1, 'Salesman');
templateSheet.getRange(6, 1, 1, 2).setStyle(headerStyle);
templateSheet.getRange(7, 1, 1, 2).setStyle(dataStyle);
templateSheet.getStyle(6, 2).hAlign = HorizontalAlign.right;
templateSheet.getStyle(7, 2).hAlign = HorizontalAlign.right;
templateSheet.setTemplateCell(7, 1, {
type: 'Group',
binding: 'Sales[Salesman]',
});
templateSheet.setTemplateCell(6, 2, {
type: 'Group',
binding: 'Sales[ProductType]',
spillDirection: 'Horizontal',
context: {
horizontal: 'None',
}
});
templateSheet.setTemplateCell(7, 2, {
type: 'Summary',
binding: 'Sales[Sales]',
aggregate: 'Sum',
});
// section 3
templateSheet.setValue(9, 1, 'Salesman Sales');
templateSheet.setValue(10, 1, 'Salesman');
templateSheet.setValue(10, 2, 'Sales');
templateSheet.getRange(10, 1, 1, 2).setStyle(headerStyle);
templateSheet.getRange(11, 1, 1, 2).setStyle(dataStyle);
templateSheet.getStyle(10, 2).hAlign = HorizontalAlign.right;
templateSheet.getStyle(11, 2).hAlign = HorizontalAlign.right;
templateSheet.setTemplateCell(11, 1, {
type: 'Group',
binding: 'Sales[Salesman]',
});
templateSheet.setTemplateCell(10, 2, {
type: 'Static',
pin: 'Both',
context: {
horizontal: 'None',
}
});
templateSheet.setTemplateCell(11, 2, {
type: 'Summary',
binding: 'Sales[Sales]',
aggregate: 'Sum',
});
reportSheet.refresh();
}