前端報表資料處理【去重、行轉列、交叉、聚合】

NO IMAGE

原始資料

let data = {
fields: ['name', 'subject', 'score'],
data: [
['張三', '語文', 86],
['李四', '語文', 92],
['王五', '語文', 56],
['張三', '數學', 100],
['李四', '數學', 88],
['王五', '數學', 98],
['張三', '英語', 62],
['李四', '英語', 83]
]
}

1. 簡單去重羅列

Report.format(data, [{ converge: 'v' }, 2, 1])

輸出

‘score’‘subject’
86‘語文’
92‘語文’
56‘語文’
100‘數學’
88‘數學’
98‘數學’
62‘英語’
83‘英語’

2. 聚合(公式)

Report.format(data, [{
converge: 'v',
formula: 'sum'
}, 0, 1, 2], {
name: '姓名',
subject: '科目',
score: '成績'
})

輸出:

‘姓名’‘科目’‘成績’
‘張三’‘語文’86
‘李四’‘語文’92
‘王五’‘語文’56
‘張三’‘數學’100
‘李四’‘數學’88
‘王五’‘數學’98
‘張三’‘英語’62
‘李四’‘英語’83
665

3. 交叉聚合(公式)

Report.format(data, [{
field: 0,
converge: 'v',
formula: {
formula: 'sum',
label: '總分'
}
}, {
field: 1,
converge: 'v',
formula: {
formula: 'sum',
label: '總成績'
}
}, 2])

輸出:

‘name’‘subject’‘score’
‘張三’‘語文’86
‘數學’100
‘英語’62
‘總成績’248
‘李四’‘語文’92
‘數學’88
‘英語’83
‘總成績’263
‘王五’‘語文’56
‘數學’98
‘總成績’154
‘總分’665

4. 交叉聚合

Report.format(data, [{
field: 0,
converge: 'v',
formula: 'avg'
}, {
field: 1,
converge: 'h',
formula: 'sum'
}, 2])

輸出:

‘name’‘語文’‘數學’‘英語’‘sum’
‘張三’8610062248
‘李四’928883263
‘王五’56980154
‘avg’78.0095.3348.33221.67

5. 交叉聚合(忽略詳細)

Report.format(data, [{
field: 0,
converge: 'v',
formula: 'avg'
}, {
field: 1,
converge: 'h',
formula: {
detail: false,
formula: 'sum'
}
}, 2])

輸出:

‘name’‘sum’
‘張三’248
‘李四’263
‘王五’154
‘avg’221.67

6. 平級聚合

Report.format(data, [{
converge: 'v'
}, {
field: 1,
converge: 'v',
formula: {
formula: 'sum',
field: 2
}
}, 2])

輸出:

‘subject’‘sum’‘score’
‘語文’23486
92
56
‘數學’286100
88
98
‘英語’14562
83

7. 多級交叉聚合

Report.format({
fields: ['province', 'city', 'count', 'industry', 'type'],
data: [
["湖北", "武漢", 18000, "房地產", "收入"],
["湖北", "武漢", 10000, "房地產", "收入"],
["湖北", "武漢", 2000, "衛生", "支出"],
["湖北", "武漢", 1200, "衛生", "支出"],
["湖北", "武漢", 8000, "衛生", "收入"],
["湖北", "武漢", 12000, "房地產", "支出"],
["湖北", "黃石", 1500, "衛生", "支出"],
["湖北", "黃石", 3500, "衛生", "收入"],
["湖北", "襄陽", 15800, "房地產", "收入"],
["湖北", "襄陽", 5800, "衛生", "收入"],
["湖北", "襄陽", 11800, "房地產", "支出"],
["湖北", "襄陽", 1800, "衛生", "支出"],
["湖南", "長沙", 16000, "房地產", "收入"],
["湖南", "長沙", 6000, "衛生", "收入"],
["湖南", "長沙", 11500, "房地產", "支出"],
["湖南", "長沙", 1500, "衛生", "支出"],
["湖南", "岳陽", 13600, "房地產", "收入"],
["湖南", "岳陽", 3600, "衛生", "收入"],
["湖南", "岳陽", 11000, "房地產", "支出"],
["湖南", "岳陽", 1000, "衛生", "支出"],
["江西", "南昌", 15400, "房地產", "收入"],
["江西", "南昌", 5400, "衛生", "收入"],
["江西", "南昌", 11800, "房地產", "支出"],
["江西", "南昌", 1800, "衛生", "支出"],
["江西", "九江", 14800, "房地產", "收入"],
["江西", "九江", 4800, "衛生", "收入"],
["江西", "九江", 11600, "房地產", "支出"],
["江西", "九江", 1600, "衛生", "支出"]
]
}, [{
field: 0,
formula: [{ formula: 'sum', label: '全國合計' }],
converge: 'v'
}, {
field: 1,
formula: [{ formula: 'sum', label: '省合計' }],
converge: 'v'
}, {
field: 3,
formula: [{
formula: 'fmt',
label: '總利潤',
format: function(data) {
let fmt = 0;
for (let i = 0, len = data.length; i < len; i  = 2) {
fmt  = (data[i] - data[i   1]);
}
return fmt;
}
}],
converge: 'h'
}, {
field: 4,
formula: [{
formula: 'fmt',
label: '利潤',
format: function(data) {
return data[0] - data[1];
}
}],
converge: 'h'
}, {
field: 2
}], { province: '省', city: '市' })

輸出:

房地產衛生總利潤
收入支出利潤收入支出利潤
湖北武漢28000120001600080003200480020800
黃石0003500150020002000
襄陽158001180040005800180040008000
省合計4380023800200001730065001080030800
湖南岳陽136001100026003600100026005200
長沙160001150045006000150045009000
省合計2960022500710096002500710014200
江西九江148001160032004800160032006400
南昌154001180036005400180036007200
省合計30200234006800102003400680013600
全國合計103600697003390037100124002470058600

具體試驗,請移步 https://github.com/shixia226/…

歡迎提出更多整改意見