판매 V 분할 교차 보고서
판매 V 분할 교차 보고서는 개별 영업사원 또는 제품군의 실적을 추적하고, 소비자 행동의 추세를 파악하고, 전반적인 수익 증가 또는 감소를 확인하는 데 유용합니다.
설명
app.js
index.html
styles.css
새 창에서 데모를 열어 디자이너 열기
다음 단계에 따라 '그룹' 및 '요약' 셀 유형을 사용하여 판매 V 분할 보고서를 생성할 수 있습니다.
- 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],
['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'];
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;
templateSheet.defaults.rowHeight = 30;
templateSheet.setColumnWidth(0, 200);
const headerStyle = new GC.Spread.Sheets.Style();
headerStyle.backColor = '#90CAF9';
headerStyle.foreColor = '#424242';
headerStyle.hAlign = GC.Spread.Sheets.HorizontalAlign.center;
headerStyle.vAlign = GC.Spread.Sheets.VerticalAlign.center;
headerStyle.font = '12px Maine';
templateSheet.getRange('A1:D8').setStyle(headerStyle);
[2, 4, 6].forEach(r => {
const newStyle = templateSheet.getStyle(r, 0).clone();
newStyle.hAlign = GC.Spread.Sheets.HorizontalAlign.left;
newStyle.textIndent = 1;
templateSheet.setStyle(r, 0, newStyle);
});
[1, 2].forEach(c => {
const newStyle = templateSheet.getStyle(1, c).clone();
newStyle.backColor = '#B3E5FC';
templateSheet.setStyle(1, c, newStyle);
});
const newStyle = templateSheet.getStyle(0, 1).clone();
newStyle.backColor = '#B3E5FC';
templateSheet.setStyle(0, 1, newStyle);
const border = new GC.Spread.Sheets.LineBorder('#E0E0E0', 1);
['#C8E6C9', '#A5D6A7', '#81C784'].forEach((bg, r) => {
r = (r * 2) + 3;
const newStyle = templateSheet.getStyle(r, 0).clone();
newStyle.backColor = bg;
templateSheet.setStyle(r, 0, newStyle);
['#FFCC80', undefined, '#FFA726'].forEach((x, c) => {
const newStyle1 = templateSheet.getStyle(r, c + 1).clone();
newStyle1.backColor = x;
if (!x) {
newStyle1.borderBottom = border;
newStyle1.borderTop = border;
newStyle1.borderLeft = border;
newStyle1.borderRight = border;
}
templateSheet.setStyle(r, c + 1, newStyle1);
})
});
[[0, 0, 2, 1], [0, 1, 2, 1], [0, 3, 2, 1], [2, 0, 1, 4], [4, 0, 1, 4], [6, 0, 1, 4]].forEach(x => templateSheet.addSpan(...x));
templateSheet.setValue(0, 0, 'Category');
templateSheet.setValue(0, 2, 'Include');
templateSheet.setValue(0, 3, 'Total');
templateSheet.setValue(2, 0, 'Product Sales');
templateSheet.setValue(4, 0, 'Product Type Sales');
templateSheet.setValue(6, 0, 'Total Sales');
templateSheet.setValue(7, 0, 'Total');
// binding for the template
templateSheet.setTemplateCell(0, 1, {
type: 'Group',
binding: 'Sales[Region]',
spillDirection: 'Horizontal',
showCollapseButton: true,
});
templateSheet.setTemplateCell(1, 2, {
type: 'Group',
binding: 'Sales[Salesman]',
spillDirection: 'Horizontal',
context: {
horizontal: 'B1',
},
});
['Product', 'ProductType', ''].forEach((x, r) => {
r = (r * 2) + 3;
if (x) {
templateSheet.setTemplateCell(r, 0, {
type: 'Group',
binding: `Sales[${x}]`,
});
}
[1, 2, 3].forEach(c => {
templateSheet.setTemplateCell(r, c, {
type: 'Summary',
aggregate: 'Sum',
binding: 'Sales[Sales]',
context: {
horizontal: c === 1
? 'B1'
: c === 2 ? 'C2' : 'Default',
},
});
});
});
[2, 4, 6].forEach(r => {
templateSheet.setTemplateCell(r, 0, {
type: 'Static',
context: {
horizontal: 'None',
},
});
});
reportSheet.refresh();
});