美文网首页
php自定义字段导出表格

php自定义字段导出表格

作者: riyihu | 来源:发表于2018-11-19 14:17 被阅读0次

主要说一下前端插件和后台思路

前端框架layui
所用组件 formSelects-v3/formSelects-v4
组件下载地址 http://sun.faysunshine.com/layui/formSelects-v4/example/example_v4.html
显示效果

custom.png

前端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">&#xe66b;</i>
          </span>
        </div>
    </div>

这里主要说思路
1.把要导出的字段 选中 传给后台

  1. 后台把接受的字段 从数据库取出
  2. 动态创建表格 -》导出
    代码如下
 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;
    }

主要是一个思路,可以优化的是记住选中字段的顺序,导出时按选中顺序导出

相关文章

网友评论

      本文标题:php自定义字段导出表格

      本文链接:https://www.haomeiwen.com/subject/gwnofqtx.html