laravel maatwebsite/excel 导出Excel

1、首先查看vendor 文件夹中有没有 maatwebsite/excel 依赖,若没有在项目根目录下使用Composer安装依赖

composer require "maatwebsite/excel:~2.1.0"

2、config/app.php中注册服务提供者到providers数组

Maatwebsite\Excel\ExcelServiceProvider::class,

 在config/app.php中注册门面到aliases数组

'Excel' => Maatwebsite\Excel\Facades\Excel::class,

 如果想要对Laravel Excel进行更多的自定义配置,执行如下Artisan命令

php artisan vendor:publish

 执行成功后会在config目录下生成一个配置文件excel.php

3、导出 Excel 文件

创建ExcelController.php

<?php

namespace App\Http\Controllers\Admin;

use Illuminate\Foundation\Application;
use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use Illuminate\Support\Facades\Artisan;
use App\Models\Member;
use App\Models\SpecialSubject;
use Excel;
use PHPExcel_Worksheet_Drawing;

class ExcelController extends Controller
{
    public function export(Request $request){
        ini_set('memory_limit','500M');
        set_time_limit(0);//设置超时限制为0分钟
        
        $info = Member::select('avatar','nickname','gender','wx_country','wx_province','wx_city','realname','mobile','company_name','email','is_news','industry','position','province','city','region','is_access','interest','is_enroll','prev_path')->get()->toArray();
               
        foreach($info as $k=>$v){
            $kk = $k + 1;
            // $cellData[$kk]['avatar'] = $v['avatar'];
            $cellData[$kk]['nickname'] = $v['nickname'];
            if($v['gender'] == 2){
                $cellData[$kk]['gender'] = '女';
            }else{
                $cellData[$kk]['gender'] = '男';
            }
            $cellData[$kk]['diqu'] = $v['wx_country'].' '.$v['wx_province'].' '.$v['wx_city'];
            $cellData[$kk]['realname'] = $v['realname'];
            $cellData[$kk]['mobile'] = $v['mobile'];
            $cellData[$kk]['company_name'] = $v['company_name'];
            $cellData[$kk]['email'] = $v['email'];
            if($v['is_news'] == 1){
                $cellData[$kk]['is_news'] = '是';
            }else{
                $cellData[$kk]['is_news'] = '否';
            }
            $cellData[$kk]['industry'] = $v['industry'];
            $cellData[$kk]['position'] = $v['position'];
            $cellData[$kk]['company_address'] = $v['province'].$v['city'].$v['region'];
            if($v['is_access'] == 1){
                $cellData[$kk]['is_access'] = '是';
            }else{
                $cellData[$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'];
            }
            $cellData[$kk]['interest'] = implode(',',$name);
            if($v['is_enroll'] == 1){
                $cellData[$kk]['is_enroll'] = '是';
            }else{
                $cellData[$kk]['is_enroll'] = '否';
            }
            $cellData[$kk]['prev_path'] = $v['prev_path'];
        }
        // 设置表头
        $cellData[0] = array('用户名','性别','地区','姓名','手机号','公司名称','公司邮箱','是否接收资讯更新','所属行业','职位','公司所在地','是否已接入穿山甲','感兴趣的话题','是否报名','补全资料的前一个路径');
        // 把需要导出的数据遍历成一个数组
        for($i=0;$i<count($cellData);$i++){
            $cellData[$i] = array_values($cellData[$i]);
            $cellData[$i][0] = str_replace('=',' '.'=',$cellData[$i][0]);
        }
        // dd($cellData);exit;
        // 创建导出excel 表格
        Excel::create('用户信息',function($excel) use ($cellData){
            $excel->sheet('score', function($sheet) use ($cellData){
                // init列
                $title_array = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H','I', 'J', 'K', 'L','M', 'N', 'O', 'P'];
                // 遍历数据
                for($i=0;$i<sizeof($cellData);$i++){
                    foreach($cellData[$i] as $k=>$v){
                        // 设置图片列高度
                        /* $i>0 && $sheet->setHeight($i+1, 65); */
                        // 设置图片列宽度
                        /* $sheet->setWidth(array('F'=>12)); */
                        // 图片放在第三列,列是从0开始的
                        // 判断图片列,如果是则放图片
                        /* if($k == 0 && $i>0){
                            $objDrawing = new PHPExcel_Worksheet_Drawing;
                            // 图片的路径
                            // $objDrawing->setPath(storage_path('/app/public/uploads/').$v);
                            $objDrawing->setPath($v);
                            $objDrawing->setCoordinates($title_array[$k] . ($i+1));
                            $objDrawing->setHeight(80);
                            $objDrawing->setOffsetX(1);
                            $objDrawing->setRotation(1);
                            $objDrawing->setWorksheet($sheet);
                            continue;
                        } */
                        // 否则放置文字数据
                        $sheet->cell($title_array[$k] . ($i+1), function ($cell) use ($v) {
                            $cell->setValue($v);
                        });    
                    }                    
                }
                ob_end_clean();
            });
        })->export('xls');
    }
}

定义相关路由,在浏览器访问即可;

导出数据如图:

 

posted @ 2020-08-20 14:37  糖糖Y  阅读(625)  评论(0编辑  收藏  举报