PHP 记录一下 AJAX导出EXECL

ajax异步导出execl表格

一、JS部分

$('.download').click(function(){
    var url = "http://xxxx.com/group/bi/export";
    var params = {
      from_date: '2017-09-01',
      to_date: '2017-09-08',
      group_id: 1
    };
    $.ajax({
      type:'POST',
      url: url,
      data: params,
      beforeSend: function(request) {
        request.setRequestHeader("Authorization", "token信息，验证身份");
      },
      success: function(redata) {
        // 创建a标签，设置属性，并触发点击下载
        var $a = $("<a>");
        $a.attr("href", redata.data.file);
        $a.attr("download", redata.data.filename);
        $("body").append($a);
        $a[0].click();
        $a.remove();
      }
    });
});

二、PHP部分

<?php
/**
 * 控制器
 */
namespace app\admin\controller\mingde;
//使用了PhpSpreadsheet导出
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
class User extends Base
{
      //会员导出
    public function execl_user(){

        if($this->request->isAjax())
        {
            $query = $this->paseWhere();
            $model = $this->model;
            $list = $model->where($query['where'])->order($query['order'])->select();
            $count =  $model->where($query['where'])->count();
            $spreadsheet = new Spreadsheet();
            $sheet = $spreadsheet->getActiveSheet();
            // 设置A1单元格水平居中对齐
            $styleArray = [
                'alignment' => [
                    'horizontal' => Alignment::HORIZONTAL_CENTER,
                ],
            ];
            $sheet->getStyle('A1:Q1')->applyFromArray($styleArray);
            $spreadsheet->getActiveSheet()->setCellValue('A1', '会员明细表');
            $spreadsheet->getActiveSheet()->getDefaultColumnDimension('A2')->setWidth(20);
            $spreadsheet->getActiveSheet()->getDefaultColumnDimension('B2')->setWidth(20);
            $sheet->setCellValue('A2', '编号');
            $sheet->setCellValue('B2', '昵称');
            $sheet->setCellValue('C2', '姓名');
            $sheet->setCellValue('D2', '会员等级');
            $sheet->setCellValue('E2', '电话');
            $sheet->setCellValue('F2', '性别');
            $sheet->setCellValue('G2', '电子邮箱');
            $sheet->setCellValue('H2', '佣金明细');
            $sheet->setCellValue('I2', '消费金额');
            $sheet->setCellValue('J2', '签到天数');
            $sheet->setCellValue('K2', '粉丝数量');
            $sheet->setCellValue('L2', '成长值');
            $sheet->setCellValue('M2', '读书卡领取数量');
            $sheet->setCellValue('N2', '地址');
            $sheet->setCellValue('O2', '提现明细');
            $sheet->setCellValue('P2', '是否付费会员');
            $sheet->setCellValue('Q2', '是否为老师');

            for ($i =3; $i <= $count+2; $i++) {
                foreach ($list as $k=>$v){
                    $sheet->setCellValue('A' . $i, $i-2);
                    $sheet->setCellValue('B' . $i, $list[$i-3]['nick_name']);
                    $sheet->setCellValue('C' . $i, $list[$i-3]['xingming']);
                    $sheet->setCellValue('D' . $i, UserLevel::where('id',$list[$i-3]['level'])->value('name'));
                    $sheet->setCellValue('E' . $i, $list[$i-3]['phone']);
                    switch ($list[$i-3]['sex']){
                        case 1:
                            $sex='男';
                            break;
                        case 2:
                            $sex='女';
                            break;
                        case 0:
                            $sex='未知';
                            break;
                    }
                    $sheet->setCellValue('F' . $i, $sex);
                    $sheet->setCellValue('G' . $i, $list[$i-3]['email']);
                    $yongjin='历史佣金：'.$list[$i-3]['history_commission'].' 当前佣金：'.$list[$i-3]['commission'].' 提现佣金：'.$list[$i-3]['withdrawal_commission'];
                    $sheet->setCellValue('H' . $i, $yongjin);
                    $sheet->setCellValue('I' . $i, $list[$i-3]['consume']);
                    $sheet->setCellValue('J' . $i, $list[$i-3]['sign_num']);
                    $sheet->setCellValue('K' . $i, $list[$i-3]['fans_num']);
                    $sheet->setCellValue('L' . $i, $list[$i-3]['growth_value']);
                    $sheet->setCellValue('M' . $i, $list[$i-3]['read_card_num']);
                    $sheet->setCellValue('N' . $i, $list[$i-3]['address']);
                    $tixian='提现姓名：'.$list[$i-3]['withdrawal_name'].' 提现电话：'.$list[$i-3]['withdrawal_telphone'].' 提现账户：'.$list[$i-3]['withdrawal_account'];
                    $sheet->setCellValue('O' . $i, $tixian);
                    $sheet->setCellValue('P' . $i, $list[$i-3]['due_date']==0?'否':'是');
                    $sheet->setCellValue('Q' . $i, $list[$i-3]['is_teacher']==0?'否':'是');
                }
            }
            header('Content-Type: application/vnd.ms-excel');
            header('Content-Disposition: attachment;filename="'.'会员明细表'.'.xlsx"');
            header('Cache-Control: max-age=0');
//            exit;
            $filename = '会员明细表.xlsx';
            $writer = new Xlsx($spreadsheet);
            ob_start();
            $writer->save('php://output');
            $xlsData = ob_get_contents();
            ob_end_clean();
            return  json(['filename' => $filename, 'file' => "data:application/vnd.ms-excel;base64," . base64_encode($xlsData)]);
        }
    }

}