laravel 使用 PHPExcel 导出

1、下载 PHPExcel

直接去github下载
https://github.com/PHPOffice/PHPExcel

2、在 app 目录下新建 libs 目录，将下载的 PHPExcel 放入 libs 目录下，如下图

3、在项目根目录的 composer.json 文件中添加 "app/libs/PHPExcel/classes" 如下图所示

laravel 使用 PHPExcel 导出

4、在项目根目录下运行cmd，执行语句 composer dumpautoload

5、具体实现代码如下：

<?php

namespace AppHttpControllersAdmin;

use IlluminateFoundationApplication;
use IlluminateHttpRequest;
use AppHttpControllersController;
use IlluminateSupportFacadesArtisan;
use AppModelsMember;
use AppModelsSpecialSubject;

class ExcelController extends Controller
{
    
    public function export(Request $request){
        ini_set('memory_limit','500M');
        set_time_limit(0);//设置超时限制为0分钟
        
        $fileName = '用户信息'.'.xls';  // 文件名称
        
        $select['search'] = $request->input("ex_search");
        $select['is_wanshan'] = (int)$request->input("ex_wanshan");
        
        $info = Member::orderBy("id", "desc");
        
        if (!empty($select['search'])){
            $info = $info->where("nickname",'like', '%'.$select['search'].'%')->orWhere("realname",'like', '%'.$select['search'].'%')->orWhere("mobile",'like', '%'.$select['search'].'%');
        } 
        if ($select['is_wanshan'] == 1){
            $info = $info->whereNotNull("realname")->whereNotNull("mobile")->whereNotNull("email")->whereNotNull("company_name")->whereNotNull("industry");
        }
        $info = $info->get()->toArray();
        if(empty($info)){
            return false;
        }
        
        foreach($info as $k=>$v){
            $kk = $k + 1;
            // $data[$kk]['avatar'] = $v['avatar'];
            $data[$kk]['nickname'] = $v['nickname'];
            if($v['gender'] == 2){
                $data[$kk]['gender'] = '女';
            }else{
                $data[$kk]['gender'] = '男';
            }
            $data[$kk]['diqu'] = $v['wx_country'].' '.$v['wx_province'].' '.$v['wx_city'];
            $data[$kk]['realname'] = $v['realname'];
            $data[$kk]['mobile'] = "	" .$v['mobile']; //数据前面加上 "	" 则可以避免自动转化为科学计数法
            $data[$kk]['company_name'] = $v['company_name'];
            $data[$kk]['email'] = $v['email'];
            if($v['is_news'] == 1){
                $data[$kk]['is_news'] = '是';
            }else{
                $data[$kk]['is_news'] = '否';
            }
            $data[$kk]['industry'] = $v['industry'];
            $data[$kk]['position'] = $v['position'];
            $data[$kk]['company_address'] = $v['province'].$v['city'].$v['region'];
            if($v['is_access'] == 1){
                $data[$kk]['is_access'] = '是';
            }else{
                $data[$kk]['is_access'] = '否';
            }
            
            $ids = explode(',',$v['interest']);
            $list = SpecialSubject::whereIn('id',$ids)->get(['id','title','short'])->toArray();
            
            $name = [];
            foreach($list as $vv){
                $name[] = $vv['title'].$vv['short'];
            }
            $data[$kk]['interest'] = implode(',',$name);
            if($v['is_enroll'] == 1){
                $data[$kk]['is_enroll'] = '是';
            }else{
                $data[$kk]['is_enroll'] = '否';
            }
            $data[$kk]['prev_path'] = $v['prev_path'];
        }
        
        // Excel 表头内容
        $columnName = array('用户名','性别','地区','姓名','手机号','公司名称','公司邮箱','是否接收资讯更新','所属行业','职位','公司所在地','是否已接入穿山甲','感兴趣的话题','是否报名穿山甲扶持计划','补全资料的前一个路径');
        
        // Excel 表格数据对应的查询出来的数据字段值
        $column = array('nickname','gender','diqu','realname','mobile','company_name','email','is_news','industry','position','company_address','is_access','interest','is_enroll','prev_path');
        
        // 表格列数
        $cell = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z');

        $count = count($column);
        $objPHPExcel = new PHPExcel();
        $objPHPExcel->setActiveSheetIndex(0);
        foreach($columnName as $k => $v){
            $objPHPExcel->getActiveSheet()->setCellValue($cell[$k].'1', $v);
        }
        foreach ($data as $key => $value) {
            $i = $key;
            for($j=0;$j<$count;$j++){
                // $objPHPExcel->getActiveSheet()->setCellValue($cell[$j].($i+1),  iconv('gbk','UTF-8',$value[$column[$j]]));
                $objPHPExcel->getActiveSheet()->setCellValue($cell[$j].($i+1), $value[$column[$j]]);
            }
        }
        ob_end_clean();//清除缓冲区,避免乱码
        // Redirect output to a client’s web browser (Excel5)
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="'.$fileName.'"');
        header('Cache-Control: max-age=0');
        // If you're serving to IE 9, then the following may be needed
        header('Cache-Control: max-age=1');
 
        // If you're serving to IE over SSL, then the following may be needed
        header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
        header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header ('Pragma: public'); // HTTP/1.0
 
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;

    }
}

laravel 使用 PHPExcel 导出

相关推荐