使用ElementUI配合API数据快速渲染合并表格
一、需求描述 数据来源于后端API
要求，对item字段进行合并，并统计item的小计值
体验地址：
https://test.hamm.cn/table二、数据库设计(MySQL)
CREATE TABLE `item` (
`item_id` int(11) NOT NULL,
`item_row` int(11) NOT NULL,
`item_body` varchar(255) NOT NULL,
`item_cropnum` int(11) NOT NULL,
`item_premoney` int(11) NOT NULL,
`item_pretax` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `item` (`item_id`, `item_row`, `item_body`, `item_cropnum`, `item_premoney`, `item_pretax`) VALUES
(1, 1, '北京', 1, 100, 100),
(2, 1, '上海', 2, 200, 200),
(3, 1, '上海', 3, 300, 300),
(4, 2, '上海', 3, 300, 300),
(5, 2, '上海', 2, 200, 200),
(6, 2, '北京', 1, 100, 100);
CREATE TABLE `row` (
`row_id` int(11) NOT NULL,
`row_date` varchar(255) NOT NULL,
`row_name` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `row` (`row_id`, `row_date`, `row_name`) VALUES
(1, '2020-05', '张三'),
(2, '2020-05', '李四'),
(3, '2020-05', '王五');
ALTER TABLE `item`
ADD PRIMARY KEY (`item_id`);
ALTER TABLE `row`
ADD PRIMARY KEY (`row_id`);
ALTER TABLE `item`
MODIFY `item_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;
ALTER TABLE `row`
MODIFY `row_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;三、PHP实现API(demo)
<?php
require_once("mysql.php");
$link = mysqli_connect($host , $user , $pass);
if (!$link){
exit('数据库连接失败');
}
mysqli_select_db($link , 'test');
$sql = "select * from row";
$res = mysqli_query($link,$sql);
$data = [];
while($row = mysqli_fetch_assoc($res)){
$res_item = mysqli_query($link,'select * from item where item_row = '.$row['row_id']);
$itemArray=[];
while($row_item = mysqli_fetch_assoc($res_item)){
array_push($itemArray,$row_item);
}
$row['item'] = $itemArray;
$res_total_pretax = mysqli_query($link,'select sum(item_pretax) as taxTotal from item where item_row = '.$row['row_id']);
$row_item_pretax = mysqli_fetch_assoc($res_total_pretax);
$row['row_pretaxtotal'] = $row_item_pretax['taxTotal'] ?? 0;
$res_total_premoney = mysqli_query($link,'select sum(item_premoney) as moneyTotal from item where item_row = '.$row['row_id']);
$row_item_premoney = mysqli_fetch_assoc($res_total_premoney);
$row['row_premoneytotal'] = $row_item_premoney['moneyTotal'] ?? 0;
$row['item'] = $itemArray;
array_push($data,$row);
}
echo json_encode($data);四、前端实现
<!DOCTYPE html>
<html>
<head>
<title>test</title>
<meta charset="UTF-8">
<!-- import CSS -->
<link rel="stylesheet" href="https://sa.hamm.cn/static/css/element.css">
</head>
<body>
<div id="app" v-cloak>
<el-table :data="tableData" :span-method="objectSpanMethod" border>
<el-table-column prop="row_id" label="ID"></el-table-column>
<el-table-column prop="row_date" label="日期"></el-table-column>
<el-table-column prop="row_name" label="商务"></el-table-column>
<el-table-column prop="item_body" label="合作主体"></el-table-column>
<el-table-column prop="item_premoney" label="预估金额"></el-table-column>
<el-table-column prop="row_premoneytotal" label="小计"></el-table-column>
<el-table-column prop="item_pretax" label="预估发票"></el-table-column>
<el-table-column prop="row_pretaxtotal" label="小计"></el-table-column>
<el-table-column prop="item_cropnum" label="企业数量"></el-table-column>
</div>
</body>
<script src="https://sa.hamm.cn/static/js/vue-2.6.10.min.js"></script>
<script src="https://sa.hamm.cn/static/js/axios.min.js"></script>
<script src="https://sa.hamm.cn/static/js/element.js"></script>
<script>
new Vue({
el: '#app',
data() {
this.getData();
return {
tableData: []
}
},
methods: {
getData() {
var that = this;
axios.post('/api.php')
.then(function (response) {
console.log(response);
var data = [];
for (index in response.data) {
response.data[index].itemCount = response.data[index].item.length;
if (response.data[index].item.length == 0) {
response.data[index].item_pretax = 0;
response.data[index].item_premoney = 0;
response.data[index].item_cropnum = 0;
response.data[index].item_body = "暂无";
data.push(response.data[index]);
} else {
for (index_item in response.data[index].item) {
if (index_item == 0) {
data.push({...response.data[index], ...response.data[index].item[index_item]
});
} else {
data.push(response.data[index].item[index_item]);
}
}
}
}
that.tableData = data;
})
.
catch (function (error) {});
},
objectSpanMethod({
row, column, rowIndex, columnIndex
}) {
if (row.itemCount == 0) {
return {
rowspan: 1,
colspan: 1
};
} else {
switch (columnIndex) {
case 0:
case 1:
case 2:
case 5:
case 7:
if (rowIndex % row.itemCount == 0) {
return {
rowspan: row.itemCount,
colspan: 1
};
} else {
return {
rowspan: 0,
colspan: 0
};
}
break;
default:
}
}
}
}
})
</script>
</html>