主要说一下前端插件和后台思路
前端框架layui
所用组件 formSelects-v3/formSelects-v4
组件下载地址 http://sun.faysunshine.com/layui/formSelects-v4/example/example_v4.html
显示效果

前端js代码就不贴了 看下载地址的文档
注意的option的 value的值 同数据库字段命名要一直,方便处理
<div class="layui-form-item">
<div class="layui-input-inline" style="width: 900px;">
<select xm-select="excel" xm-select-skin="primary" xm-select-type="1">
<option value=""></option>
<option value="users.username" disabled selected>姓名</option>
<option value="users.phone,users.email">联系方式</option>
<option value="id_num">证件号</option>
<option value="order_goods.goods_name">考试场次</option>
<option value="order_sn">订单号</option>
<option value="actual_price">实付款</option>
@if($status == 2)
<option value="refund_fee">退款金额</option>
@endif
<option value="pay_time">付款时间</option>
</select>
</div>
<input type="hidden" name="_token" value="{{csrf_token()}}">
<div class="layui-form-mid layui-word-aux">
<span class="layui-btn layui-btn-small layui-btn-normal" lay-filter="customExport" lay-submit="">
确认导出 <i class="layui-icon"></i>
</span>
</div>
</div>
这里主要说思路
1.把要导出的字段 选中 传给后台
- 后台把接受的字段 从数据库取出
- 动态创建表格 -》导出
代码如下
public function customExcel()
{
$get = request()->input();
$goods_id = $get['goods_id'];
$status = $get['status'];
$custom = [];
$arr = $get['options'];
//所有的字段
$all = "actual_price,order_sn,users.username,id_num,users.phone,users.email,order_goods.goods_name,order_goods.price,pay_time,order.updated_at,refund_fee";
//至少导出一个字段
if (empty($arr)) {
return '导出参数错误';
}
$all = explode(',', $all); //所有的字段转数组
$arr = explode(',', $arr); //管理员选中的字段
$result = array_intersect($arr,$all); //取交集 好像这一步没有什么意义 0.0 忽略
//这里的是表格的第一行 自己理解
$title = [
"姓名" => "users.username",
"证件号" => "id_num",
"手机\邮箱" => "users.email",
"手机/邮箱" => "users.phone",
"考试场次" => "order_goods.goods_name",
"订单号" => "order_sn",
"实付款" => "actual_price",
"退款金额" => "refund_fee",
"付款时间" => "pay_time",
];
$cur_title = array_intersect($title,$arr); //取交集
//嗯, 因为后天是连表查,所有有的字段是 order.goods_id ,去掉表前缀
foreach ($cur_title as $k => $v) {
if (strpos($v,'.')) {
$cur_title[$k] = substr($v,strripos($v,".")+1);
}
}
$cur_title = array_flip($cur_title); //反转
// $result = implode(',', $result);
$custom = array_values($result);
$order = new OrderModel;
$goods_name = GoodsModel::query()->where('id',$goods_id)->value('name');
$datas = $order->paidOrder($goods_id, $custom); //付款订单
$order_stutus = '已付款订单';
if (empty($datas)) {
return view('layout.exception',['error'=>'暂无数据可导出']);
}
array_unshift($datas,$cur_title); //数组的第一项 即标题
$new = $this->excelTitle($cur_title);
//以上是为了组成 下面格式的数组
[
[
'username'=>‘用户名’,
'id_num'=>'证件号', <_#表格的第一行
‘order_sn’=>'订单号'
],
[
'username'=>‘胡汉三’,
'id_num'=>'42112523323',
‘order_sn’=>'2018090364548'
],
[
'username'=>‘李白’,
'id_num'=>'656898998',
‘order_sn’=>'20184659898989'
],
......
];
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$filename = $goods_name.'_'.$order_stutus.date('YmdHis').'.xlsx';
ob_end_clean();//清除缓冲区,避免乱码
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="'.$filename.'"');
header('Cache-Control: max-age=0');
//设置表格样式
$sheet->getStyle('A1:H1')->getFont()->setBold(true)->setName('Arial')->setSize(10);
//因为不知道会导出那些字段,无分别的取一样的宽度
$sheet->getColumnDimension('A')->setWidth(30);
$sheet->getColumnDimension('B')->setWidth(30);
$sheet->getColumnDimension('C')->setWidth(30);
$sheet->getColumnDimension('D')->setWidth(30);
$sheet->getColumnDimension('E')->setWidth(30);
$sheet->getColumnDimension('F')->setWidth(30);
$sheet->getColumnDimension('G')->setWidth(30);
$sheet->getColumnDimension('H')->setWidth(30);
$i = 0;
foreach($datas as $data){
$i++;
foreach ($new as $k => $v) {
$sheet->setCellValueExplicit($k.$i, $data[$v],\PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
//不知道是什么字段,统一转字符串,防止出现科学计数
}
}
$writer = new Xlsx($spreadsheet);
$writer->save('php://output');
}
public function excelTitle($title)
{
$letter = ['A','B','C','D','E','F','G','H'];
$count = count($title);
$cur_let = array_slice($letter, 0, $count);
$title = array_flip($title);
$new = array_combine($cur_let,$title);
return $new;
}
主要是一个思路,可以优化的是记住选中字段的顺序,导出时按选中顺序导出
网友评论