[PHP知识库]php Spreadsheet导出文件 ajax进行下载

前端界面

//   window.location.href="{:url('/admin/order/outerExcel')}?start="+this.excelStart+'&end='+this.excelEnd
                            if(isEmpty(this.excelStart) || isEmpty(this.excelEnd)){
                                return toastr.warning('请补齐以上信息')
                            }
                            var index2 = layer.load(0,{time:0,shade:0.3});
                           // $('#excelModal').modal('hide')
                            axios.post("{:url('/admin/order/outerExcel')}",{
                                start: this.excelStart,
                                end  : this.excelEnd
                            }).then(function(res){
                                if(res.data.code==200){
                                    var $a = $("<a>");
                                    $a.attr("href",res.data.file);
                                    $("body").append($a);
                                    $a.attr("download", res.data.filename);
                                    $a[0].click();
                                    $a.remove();
                                    layer.msg('请稍等!', {icon: 1})
                                }else{
                                    layer.msg('网络错误!', {icon: 2})
                                }
                              layer.close(index2);
                            })

后端界面 生成临时文件

 $check = Session::get('uid');
        $admin = Admin::get($check);
        $spreadsheet = new Spreadsheet();
        $sheet       = $spreadsheet->getActiveSheet();
        // $sheet->setCellValueByColumnAndRow(1 , 1, '发布日期');
        // $sheet->setCellValueByColumnAndRow(2 , 1, '接单人');
        // $sheet->setCellValueByColumnAndRow(3 , 1, '区域');
        // $sheet->setCellValueByColumnAndRow(4 , 1, '办理业务');
        // $sheet->setCellValueByColumnAndRow(5 , 1, '工商外勤');
        // $sheet->setCellValueByColumnAndRow(6 , 1, '订单状态');
        // $sheet->setCellValueByColumnAndRow(7 , 1, '状态时间');
        // $sheet->setCellValueByColumnAndRow(8 , 1, '公司名称');
        $sheet->setCellValueByColumnAndRow(1, 1, '业务')->getColumnDimension('A')->setWidth(15);
        $sheet->setCellValueByColumnAndRow(2, 1, '外勤')->getColumnDimension('B')->setWidth(15);
        $sheet->setCellValueByColumnAndRow(3, 1, '接单时间')->getColumnDimension('C')->setWidth(15);
        $sheet->setCellValueByColumnAndRow(4, 1, '开始办理时间')->getColumnDimension('D')->setWidth(15);
        $sheet->setCellValueByColumnAndRow(5, 1, '状态修改时间')->getColumnDimension('E')->setWidth(15);
        $start  = Request::post('start');
        $end    = Request::post('end');
        $orders = OrderList::where('create_time', 'between', [$start, $end])->where([['finish', 'in', '1'], ['status', 'in', '1,2,6']])->where(function ($query) use ($admin, $check) {
            $roleId = $admin->getdata('role');
            if (in_array($roleId, ['1', '2', '6', '10', '12'])) {
                if ($admin['role'] !== 1) {
                    $query->where('citys', $admin['city']);
                }
            } else {
                $query->where('outsider', $check);
            }
        })->select();
        foreach ($orders as $key => $value) {
            $column = $key + 2;
            // $sheet->setCellValueByColumnAndRow(1 , $column, $value['create_time']);
            // $sheet->setCellValueByColumnAndRow(2 , $column, $value['projecter']);
            // $sheet->setCellValueByColumnAndRow(3 , $column, $value['zone']);
            // $sheet->setCellValueByColumnAndRow(4 , $column, $value['work'].$value['work_remark']);
            // $sheet->setCellValueByColumnAndRow(5 , $column, $value['outsider']);
            // $sheet->setCellValueByColumnAndRow(6 , $column, $value['status']);
            // $statusTime = Db::name('order_log')->where('order_id',$value['id'])->order('id desc')->value('create_time');
            // $sheet->setCellValueByColumnAndRow(7 , $column, $statusTime);
            // $sheet->setCellValueByColumnAndRow(8 , $column, $value['company']);
            $sheet->setCellValueByColumnAndRow(1, $column, $value['projecter']);
            $sheet->setCellValueByColumnAndRow(2, $column, $value['outsider']);
            $sheet->setCellValueByColumnAndRow(3, $column, $value['create_time']);

            $q       = OrderLog::where(['order_id' => $value['id'], 'type' => 1])->limit(1)->value('create_time');//$process
            $w       = OrderLog::where(['order_id' => $value['id'], 'type' => 1])->order('id desc')->limit(1)->value('create_time');//$change
            $process = $q ? date('Y-m-d', strtotime($q)) : '暂无时间';
            $change  = $w ? date('Y-m-d', strtotime($w)) : '暂无时间';

            $sheet->setCellValueByColumnAndRow(4, $column, $process);
            $sheet->setCellValueByColumnAndRow(5, $column, $change);
        }

      $file = '工商外勤' . date('Ymdhis',time()) . '.xlsx';
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');//告诉浏览器输出07Excel文件
        header('Content-Disposition: attachment;filename="' . $file . '"');//告诉浏览器输出浏览器名称
        header('Cache-Control: max-age=0');//禁止缓存
        $writer = new Xlsx($spreadsheet);
        ob_start(); //打开缓冲区
        $writer->save('php://output');
        $xlsdata =ob_get_contents();
        ob_end_clean();
        return  json(['code'=>200,'filename'=>$file,'file'=>"data:application/vnd.ms-excel;base64,".base64_encode($xlsdata)]);

生成文件 存放在指定文件夹 返回路径和文件名

 $url =$this->excelPath('工商外勤',$spreadsheet);
return json(['code'=>200,'filename'=>$url['filename'],'file'=>$url['url']]);


//删除多于的xlsx文件
    private function deldir($path){
        //如果是目录则继续
        if(is_dir($path)){
            //扫描一个文件夹内的所有文件夹和文件并返回数组
            $p = scandir($path);
            foreach($p as $val){
                //排除目录中的.和..
                if($val !="." && $val !=".."){
                    //如果是目录则递归子目录，继续操作
                    if(is_dir($path.$val)){
                        //子目录中操作删除文件夹和文件
                        deldir($path.$val.'/');
                        //目录清空后删除空文件夹
                        @rmdir($path.$val.'/');
                    }else{
                        //如果是.xlsx文件直接删除
                        $result = glob($path.'/*.xlsx');
                        foreach($result as $file){
                            unlink($file);
                        }
                    }
                }
            }
        }
    }
   //返回路径
    private  function excelPath($name,$spreadsheet){

                    $dir =iconv("gb2312","utf-8","./uploads/".date("Ymd"));
                    if(!file_exists($dir)){
                        mkdir($dir,0777,true);
                    }
                    //删除昨日文件夹下的.xlsx文件
                    $folderpath ='./uploads/'.date("Ymd",strtotime("-1 day")); //要操作的目录
                   // $this->deldir($folderpath);
                    $pathUrl =$dir;
                    $title= $name . date('Ymdhis',time()) . '.xlsx';
                    $file =$pathUrl.'/'.$title;
                    $writer = new Xlsx($spreadsheet);
                   $result = $writer->save($file);
                   $url ='/uploads/'.date("Ymd").'/'.$title;
                    return  array('url'=>$url,'filename'=>$title);
    }