V 트리 판매 보고서는 개별 영업사원 또는 제품군의 실적을 추적하고, 소비자 행동의 추세를 파악하고, 전반적인 수익 증가 또는 감소를 확인하는 데 유용합니다.
다음 단계에 따라 '그룹' 및 '요약' 셀 유형, 그리고 사용자 정의한 왼쪽 부모 항목을 사용하여 V 트리 판매 보고서를 생성할 수 있습니다.
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, 'V Tree Sales Report', GC.Spread.Sheets.SheetType.reportSheet);
const templateSheet = reportSheet.getTemplate();
// set style for the template
templateSheet.defaults.colWidth = 120;
templateSheet.defaults.rowHeight = 30;
templateSheet.setColumnWidth(0, 250);
const style = new GC.Spread.Sheets.Style();
style.backColor = '#81D4FA';
style.foreColor = '#424242';
style.hAlign = GC.Spread.Sheets.HorizontalAlign.center;
style.vAlign = GC.Spread.Sheets.VerticalAlign.center;
style.font = '12px Maine';
templateSheet.getRange('A1:C6').setStyle(style);
for (let r = 2; r <= 5; r++) {
for (let c = 0; c <= 2; c++) {
const newStyle = templateSheet.getStyle(r, c).clone();
if (c === 0) {
newStyle.hAlign = GC.Spread.Sheets.HorizontalAlign.left;
if (r === 2) {
newStyle.backColor = '#00BCD4';
}
if (r === 3) {
newStyle.backColor = '#26C6DA';
newStyle.textIndent = 2;
}
if (r === 4) {
newStyle.backColor = '#80DEEA';
newStyle.textIndent = 4;
}
if (r === 5) {
newStyle.backColor = '#B2EBF2';
newStyle.textIndent = 8;
}
}
else {
if (r === 2) {
newStyle.backColor = '#FFCC80';
}
if (r === 3) {
newStyle.backColor = '#FFE0B2';
}
if (r === 4) {
newStyle.backColor = '#FFF3E0';
}
if (r === 5) {
newStyle.backColor = undefined;
}
}
templateSheet.setStyle(r, c, newStyle);
}
}
[[0, 0, 2, 1], [0, 1, 1, 2]].forEach(x => templateSheet.addSpan(...x));
templateSheet.setValue(0, 0, 'Category');
templateSheet.setValue(0, 1, 'Summary');
templateSheet.setValue(1, 1, 'Sales');
templateSheet.setValue(1, 2, 'Return');
// set binding for the template
['Region', 'Salesman', 'ProductType', 'Product'].forEach((x, r) => {
templateSheet.setTemplateCell(r + 2, 0, {
type: 'Group',
binding: `Sales[${x}]`,
spillDirection: 'Vertical',
context: {
vertical: r > 0 ? `A${r + 2}` : 'Default',
},
showCollapseButton: r !== 3,
});
templateSheet.setTemplateCell(r + 2, 1, {
type: 'Summary',
aggregate: 'Sum',
binding: 'Sales[Sales]',
});
templateSheet.setTemplateCell(r + 2, 2, {
type: 'Summary',
aggregate: 'Sum',
binding: 'Sales[Return]',
});
});
reportSheet.refresh();
});