Vue+PHP后台实现导出Excel表格，要有自定义（选择字段）和筛选功能
要求：订单管理导出excel表格要有自定义（选择字段）和筛选功能。
PHP代码
/**
* @title 订单Excel内容生成
* @description 订单Excel内容生成
* @author
* @url /order/order/get_Excel
* @method *
* @param name:token require:1 default: desc:token
* @list id:id out_trade_no:订单编号 product_id:产品id price:价格 is_coupon:是否使用优惠券 coupon_id:优惠券id pay_price:实际支付价格 is_pay:是否支付 pay_time:支付时间 pay_type:支付类型 pay_status:订单状态 order_status:订单状态 create_time:创建时间 user_id:用户id title:订单标题 order_type:订单类型 contacts:联系人 phone:手机号 email:邮箱 remark:备注 travel_start:出行开始日期 travel_end:出行结束日期 success_time:完成时间 product_type:产品类型
* @return error_id:1-10-01-0006 校验失败
*/
public function get_Excel()
{
$message = input();
$data = array();
/*dump($message[0]['header']);
exit();*/
foreach ($message[0]['header'] as $k => $v) {
$data['key'][] = $k;
$data['value'][] = $v;
}
$field = implode(',',$data['key']);
/*dump($field);
exit();*/
$where = array();
foreach ($message[1]['data'] as $key => $value) {
if ($key == 'limit' || $key == 'page') {
unset($value);
continue;
}
if ($value === 0) {
continue;
}
/*if (empty($value)) {
unset($value);
}*/
if ($key == 'create_time' || $key == 'pay_time' || $key == 'travel_start' || $key == 'travel_end') {
if (!empty($value[0]) && !empty($value[1])) {
$start = strtotime($value[0]);
$end = strtotime($value[1]);
unset($value);
$where[] = [$key, '>=', $start];
$where[] = [$key, '<=', $end];
}
}
if ($key =='id'||$key =='out_trade_no'||$key =='phone'||$key =='coupon_id'||$key =='is_pay'||$key =='pay_type'||$key =='pay_status'||$key =='order_status'||$key=='is_coupon'||$key=='product_type'){
$where[] = [$key, '=', $value];
}
}
$data['list'] = Db::name('order')
->where($where)
->withAttr('pay_time', function ($value, $data) {
if (!empty($data['pay_time'])){
return date('Y-m-d H:i:s', $data['pay_time']);
}
})
->withAttr('create_time', function ($value, $data) {
if (!empty($data['create_time'])){
return date('Y-m-d H:i:s', $data['create_time']);
}
})
->withAttr('create_time', function ($value, $data) {
if (!empty($data['create_time'])){
return date('Y-m-d H:i:s', $data['create_time']);
}
})
->withAttr('travel_start', function ($value, $data) {
if (!empty($data['travel_start'])){
return date('Y-m-d H:i:s', $data['travel_start']);
}
})
->withAttr('travel_end', function ($value, $data) {
if (!empty($data['travel_end'])){
return date('Y-m-d H:i:s', $data['travel_end']);
}
})
->withAttr('success_time', function ($value, $data) {
if (!empty($data['success_time'])){
return date('Y-m-d H:i:s', $data['success_time']);
}
})
->field($field)
->order('id desc')->select();
foreach ($data['list'] as $k => $v) {
foreach ($v as $key => $value) {
if ($key === 'user_id') {
if (empty($v[$key])) {
$data['list'][$k][$key] = '用户不存在';
} elseif (!empty(get_curl('user', '/user/info', ['token' => $message['token'], 'id' => $v[$key]])['data']['nickname'])) {
$data['list'][$k][$key] = get_curl('user', '/user/info', ['token' => $message['token'], 'id' => $v[$key]])['data']['nickname'];
} else {
$data['list'][$k][$key] = '用户不存在';
}
}
if ($key === 'product_id') {
if (empty($v[$key])) {
$data['list'][$k][$key] = '产品名称不健全';
} elseif (!empty(get_curl('product', '/product/info', ['token' => $message['token'], 'id' => $v[$key]])['data']['title'])) {
$data['list'][$k][$key] = get_curl('product', '/product/info', ['token' => $message['token'], 'id' => $v[$key]])['data']['title'];
} else {
$data['list'][$k][$key] = '产品名称不健全';
}
}
if ($key === 'is_coupon') {
if (empty($v[$key])) {
$data['list'][$k][$key] = '未知';
} elseif ($v[$key] === 1) {
$data['list'][$k][$key] = '是';
} else {
$data['list'][$k][$key] = '否';
}
}
if ($key === 'is_pay') {
if (empty($v[$key])) {
$data['list'][$k][$key] = '未知';
} elseif ($v[$key] === 1) {
$data['list'][$k][$key] = '支付';
} else {
$data['list'][$k][$key] = '未支付';
}
}
if ($key === 'pay_type') {
if (empty($v[$key])) {
$data['list'][$k][$key] = '未知';
} elseif ($v[$key] === 'wx_pay') {
$data['list'][$k][$key] = '微信支付';
} else {
$data['list'][$k][$key] = '其他';
}
}
if ($key === 'pay_status') {
if (empty($v[$key])) {
$data['list'][$k][$key] = '未知';
} elseif ($v[$key] === 'paid') {
$data['list'][$k][$key] = '支付';
} else {
$data['list'][$k][$key] = '未支付';
}
}
//订单状态'refunded: 已退款','refunding: 退款中','cancel'已取消,'success'已完成,'wait_evaluate'待评价,'wait_travel'待出行,'obligation'待付款
if ($key === 'order_status') {
switch ($v[$key]){
case 'refunded':$data['list'][$k][$key] = '已退款'; break;
case 'refunding':$data['list'][$k][$key] = '退款中'; break;
case 'cancel':$data['list'][$k][$key] = '已取消'; break;
case 'success':$data['list'][$k][$key] = '已完成'; break;
case 'wait_evaluate':$data['list'][$k][$key] = '待评价'; break;
case 'wait_travel':$data['list'][$k][$key] = '待出行'; break;
case 'obligation':$data['list'][$k][$key] = '待付款'; break;
default :$data['list'][$k][$key] = '未知';
}
}
if ($key === 'product_type') {
if (empty($v[$key])) {
$data['list'][$k][$key] = '未知';
} elseif ($v[$key] === 'social') {
$data['list'][$k][$key] = '约玩';
} elseif ($v[$key] === 'product') {
$data['list'][$k][$key] = '产品';
} else {
$data['list'][$k][$key] = '其他';
}
}
}
}
/*dump($data['list']);
exit();*/
$msg['list'] = $data;
return $this->successJson($msg);
}
Vue代码
DownloadExcel() {
this.downloadLoading = true
var formThead = this.formThead;
for (var i in formThead) {
if (formThead[i] == false){
delete(formThead[i]);
}
}
for (var e in formThead) {
switch (e) {
case 'id': formThead[e]='id'; break;
case 'out_trade_no':formThead[e]= '订单编号';break;
case 'product_id':formThead[e]= '产品id';break;
case 'price':formThead[e]='价格';break;
case 'is_coupon':formThead[e]= '是否使用优惠券';break;
case 'coupon_id':formThead[e]= '优惠券id';break;
case 'pay_price':formThead[e]= '实际支付价格';break;
case 'is_pay':formThead[e]= '是否支付';break;
case 'pay_time':formThead[e]= '支付时间';break;
case 'pay_type':formThead[e]= '支付类型';break;
case 'pay_status':formThead[e]= '支付状态';break;
case 'order_status':formThead[e]= '订单状态';break;
case 'create_time':formThead[e]= '创建时间';break;
case 'user_id':formThead[e]= '用户姓名';break;
case 'title':formThead[e]= '订单标题';break;
case 'contacts':formThead[e]= '联系人';break;
case 'phone':formThead[e]= '手机号码';break;
case 'email':formThead[e]= '邮箱';break;
case 'remark':formThead[e]= '备注';break;
case 'travel_start':formThead[e]= '出行时间';break;
case 'travel_end': formThead[e]='出行结束时间';break;
case 'success_time':formThead[e]= '完成时间';break;
case 'product_type':formThead[e]= '产品类型';break;
case 'nickname':delete(formThead[e]);break;
case 'product':delete (formThead[e]);break;
}
}
console.log(formThead)
getExcel(store.getters.token, [{ header: formThead }, { data: this.listQuery }]).then(response => {
import('@/vendor/Export2Excel').then(excel => {
// console.log(response.data.list.value)
const tHeader = response.data.list.value
const filterVal = response.data.list.key
const list = response.data.list.list
const data = this.formatJson(filterVal, list)
excel.export_json_to_excel({
header: tHeader,
data,
filename: 'order_list',
autoWidth: true,
bookType: 'xlsx'
})
this.downloadLoading = false
})
}).catch(() => {
this.getLists()
this.$message({
type: 'info',
message: '失败!'
})
})
},
formatJson(filterVal, jsonData) {
return jsonData.map(v => filterVal.map(j => {
if (j === 'timestamp') {
return parseTime(v[j])
} else {
return v[j]
}
}))
}
订单数据
listQuery: {
case: undefined,
id: undefined,
out_trade_no: undefined,
product_id: undefined,
product: undefined,
price: undefined,
is_coupon: undefined,
coupon_id: undefined,
pay_price: undefined,
is_pay: undefined,
pay_time: undefined,
pay_type: undefined,
pay_status: undefined,
order_status: undefined,
create_time: undefined,
user_id: undefined,
nickname: undefined,
title: undefined,
contacts: undefined,
phone: undefined,
email: undefined,
remark: undefined,
travel_start: undefined,
travel_end: undefined,
success_time: undefined,
product_type: undefined,
is_delete: undefined,
is_tickets: undefined,
tickets_buy_status: undefined,
is_cate: undefined,
cate_issue_status: undefined,
page: 1,
limit: 10
},