Thinkphp5 Excel导出

1、首先，先下载PHPExcel 插件

ThinkPHP版本：5.0
PHPExcel版本：1.8 

PHPExcel 官方下载地址：https://github.com/PHPOffice/PHPExcel

2、要求：如下图所示，带高级搜索，带Excel 导出

3、HTML 前端代码  

注：Excel 导出按钮一定要用<button>标签，然后给其设置一个 name名 及 value 值，后台根据此判断是进行搜索还是Excel 导出操作；

<form class="well form-inline margin-top-20" method="post" id="singcms-form" action="{:url('ServiceOrder/index')}" >

        订单状态：
        <select class="form-control" name="status" style="width: 140px;">
            <option value='99' <if condition="$param['status'] eq 99">selected="selected"</if> >全部</option>
             <foreach name="Status" id="st" key="key">
                 <option value="{$key}" <if condition="$param['status'] eq $key">selected="selected"</if> >{$st}</option>
             </foreach>
        </select> &nbsp;&nbsp;
        时间:
        <input type="text" class="form-control js-bootstrap-datetime" name="start_time"
               value="{$param['start_time']|default=''}" style="width: 140px;" autocomplete="off"> --
        <input type="text" class="form-control js-bootstrap-datetime" name="end_time"
               value="{$param['end_time']|default=''}" style="width: 140px;" autocomplete="off"> &nbsp; &nbsp;
        关键字:
        <input type="text" class="form-control" name="keyword" style="width: 250px;"
               value="{$param['keyword']|default=''}" placeholder="请输入订单号..." />
        <input type="submit" class="btn btn-primary" value="搜索" />
        <button type="submit" class="btn btn-success" name="excel" value="1" >导出Excel</button>
    </form>

 

4、将下载好的 PHPExcel 包放入extend 文件夹中，新建一个文件夹名称如下（插件包中只保留Classes 文件夹，其余的全部删掉）：

5、后端控制器代码

public function index()
    {
       
        $ServerOrderModel = new ServerOrderModel();
        $param = $this->request->param();

        $condition = '';
        if(!empty($param)){

            if(isset($param['status']) && $param['status'] != 99){  // 订单状态
                $condition['status'] = $param['status'];
            }
            if(!empty($param['start_time']) && !empty($param['end_time'])){
                $condition['create_time'] = array('between',[$param['start_time'],$param['end_time']]);
            }
            if(!empty($param['start_time']) && empty($param['end_time'])){
                $condition['create_time'] = array('egt',$param['start_time']);
            }
            if(empty($param['start_time']) && !empty($param['end_time'])){
                $condition['create_time'] = array('elt',$param['end_time']);
            }
            if(!empty($param['keyword'])){
                $condition['order_num'] = array('like','%'.$param['keyword'].'%');
            }
        }

        $search = ['query'=>[]];
        if(isset($param['status'])){
            $search['query']['status'] = $param['status'] == 99 ? '' : $param['start_time'];
        }
        $search['query']['start_time'] = isset($param['start_time']) ? $param['start_time'] : '';
        $search['query']['end_time'] = isset($param['end_time']) ? $param['end_time'] : '';
        $search['query']['keyword'] = isset($param['keyword']) ? $param['keyword'] : '';
        $param['status'] = isset($param['status'])? $param['status']: 99;      // 给导出用
        if(isset($param['excel']) && $param['excel'] == 1){
            $list = $ServerOrderModel->getOrderList($condition);
            if(!$list->isEmpty()){             // 此处为个人处理业务用，可忽略
                $list = $list->toArray();
                $num = count($list);
                for($i = 0; $i < $num; $i++){
                    if($list[$i]['pay_type'] == 1){
                        $list[$i]['pay_type'] = '支付宝';
                    }
                    if($list[$i]['pay_type'] == 2){
                        $list[$i]['pay_type'] = '微信';
                    }
                    if($list[$i]['is_pay'] == 1){
                        $list[$i]['is_pay'] = '已支付';
                    }else{
                        $list[$i]['is_pay'] = '未支付';
                    }
                }
            }
            $header = [
                'A' => ['order_num', '订单号'],
                'B' => ['s_name', '服务名称'],
                'C' => ['server_num', '服务数量'],
                'D' => ['order_price', '订单金额'],
                'E' => ['pay_type', '支付方式'],
                'F' => ['is_pay', '是否支付'],
                'G' => ['create_time', '下单时间']
            ];
            $fileName = '订单'.date('Y-m-d');
            excel($header, $list, $fileName);       // Excel 导出，在公共方法中
        }else{
            $list = $ServerOrderModel -> getList($condition,$search);
        }
      
        $this->assign("list",$list);
        $this->assign('page',$list->render());
        $this->assign('param',$param);
        $this->assign('Status',$ServerOrderModel::$STATUS);
        return $this->fetch();

    }

6、公共方法 excel

// Excel 导出
function excel($header,$list,$fileName){

    // 引入 Excel 表格插件
    import('PHPExcel.Classes.PHPExcel');
    import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
    $PHPExcel = new \PHPExcel();
    $PHPSheet = $PHPExcel->getActiveSheet();

        // 设置表格宽度
        $PHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(30);
        $PHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(15);
        $PHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15);
        $PHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(10);
        $PHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(10);
        $PHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(15);
        $PHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(15);
  

    foreach ($list as $k => $v) {
        foreach ($header as $key => $value) {
            if ($k == 0) {
                $PHPSheet->setCellValue($key.'1', end($value));
            }
            $i = $k + 2;
            $PHPSheet->setCellValue($key.$i, $v[reset($value)]);
        }
    }
    $PHPWriter = \PHPExcel_IOFactory::createWriter($PHPExcel,"Excel2007");
    header("Pragma: public");
    header("Expires: 0");
    header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
    header("Content-Type:application/force-download");
    header("Content-Type:application/vnd.ms-execl");
    header("Content-Type:application/octet-stream");
    header("Content-Type:application/download");;
    header('Content-Disposition:attachment;filename='.$fileName.'.xlsx');
    header("Content-Transfer-Encoding:binary");
    $PHPWriter->save("php://output");

}

7、导出结果如下（开心）：