[PHP知识库]laravel maatwebsite/excel3.1 导入导出详解

官方文档地址
https://docs.laravel-excel.com/3.1/imports/batch-inserts.html

安装

1. 安装Composer包

composer require maatwebsite/excel

3. 在 config/app.php 中注册服务提供者到 providers 数组：

Maatwebsite\Excel\ExcelServiceProvider::class,

3.在 config/app.php 中注册到 aliases 数组：

‘Excel’ => Maatwebsite\Excel\Facades\Excel::class,

4.生成 Laravel Excel 的配置文件:（发布）

php artisan vendor:publish --provider=“Maatwebsite\Excel\ExcelServiceProvider”

至此，完成了composer包的安装步骤

导入使用说明

创建一个导入类

php artisan make:import 文件名称 --model=Model地址

或

php artisan make:import 文件名称

注：导入文件名命名为导入表模型名+Import，例如模型名WasteCode，导入类名WasteCodeImport

此操作会在app/下创建一个Import目录并创建一个文件

.
├── app
│   ├── Imports
│   │   ├── WasteCodeImport
│ 
└── composer.json

我这里需要导入的数据需要自己处理一下，所以使用的ToCollection的方法

app/Imports/WasteCodeImport代码如下：

<?php

namespace App\Imports;

use App\Models\admin\WasteCode;
use Maatwebsite\Excel\Concerns\ToCollection;
use Illuminate\Support\Collection;

class WasteCodeImport implements ToCollection
{
    /**
     * 使用 ToCollection
     * @param array $row
     *
     * @return User|null
     */
    public function collection(Collection $rows)
    {
        foreach ($rows as $row) 
        {
            // dump($row);
            // 处理数据
        }
        WasteCode::insert($data);
    }

    public function createData($rows)
    {
        //todo
    }
}

控制器调用

<?php

namespace App\Http\Controllers\WasteCode;


use Illuminate\Http\Request;
use App\Imports\WasteCodeImport;
use App\Http\Controllers\Controller;
use Maatwebsite\Excel\Facades\Excel;

class WasteCode extends Controller
{

    /**
     * excel批量导入成员
     *
     * @param Request $request
     * @return void
     */
    public function importMember(Request $request)
    {
		$path = '文件路径'；
        Excel::import(new WasteCodeImport, $path);
    }
}

导出使用说明

1.发布配置，请运行 vendor publish 命令：

php artisan vendor:publish

将会自动创建一个新配置文件config/excel.php

2、用法

先创建导出类，以导出用户为例

php artisan make:export Export

将会自动创建一个新文件app/Exports/Export.php

以下为导出类代码(Export.php)

<?php
namespace App\Exports;
use Illuminate\Support\Collection;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Events\AfterSheet;
use PhpOffice\PhpSpreadsheet\Style\Border;
class Export implements FromCollection,WithHeadings, WithEvents
{
    protected $data;
    protected $headings;
    protected $columnWidth = [];//设置列宽       key：列  value:宽
    protected $rowHeight = [];  //设置行高       key：行  value:高
    protected $mergeCells = []; //合并单元格    value:A1:K8
    protected $font = [];       //设置字体       key：A1:K8  value:Arial
    protected $fontSize = [];       //设置字体大小       key：A1:K8  value:11
    protected $bold = [];       //设置粗体       key：A1:K8  value:true
    protected $background = []; //设置背景颜色    key：A1:K8  value:#F0F0F0F
    protected $vertical = [];   //设置定位       key：A1:K8  value:center
    protected $sheetName; //sheet title
    protected $borders = []; //设置边框颜色  key：A1:K8  value:#000000
    //设置页面属性时如果无效   更改excel格式尝试即可
    //构造函数传值
    public function __construct($data, $headings,$sheetName)
    {
        $this->data = $data;
        $this->headings = $headings;
        $this->sheetName = $sheetName;
        $this->createData();
    }
 
    public function headings(): array
    {
        return $this->headings;
    }
 
    //数组转集合
    public function collection()
    {
        return new Collection($this->data);
    }
    //业务代码
    public function createData()
    {
        $this->data = collect($this->data)->toArray();
    }
 
    public function registerEvents(): array
    {
        return [
            AfterSheet::class  => function(AfterSheet $event) {
                //设置区域单元格垂直居中
                $event->sheet->getDelegate()->getStyle('A1:Z1265')->getAlignment()->setVertical('center');
                //设置区域单元格水平居中
                $event->sheet->getDelegate()->getStyle('A1:Z1265')->getAlignment()->setHorizontal('center');
                //设置列宽
                foreach ($this->columnWidth as $column => $width) {
                    $event->sheet->getDelegate()
                        ->getColumnDimension($column)
                        ->setWidth($width);
                }
                //设置行高，$i为数据行数
                foreach ($this->rowHeight as $row => $height) {
                    $event->sheet->getDelegate()
                        ->getRowDimension($row)
                        ->setRowHeight($height);
                }
 
                //设置区域单元格垂直居中
                foreach ($this->vertical as $region => $position) {
                    $event->sheet->getDelegate()
                        ->getStyle($region)
                        ->getAlignment()
                        ->setVertical($position);
                }
 
                //设置区域单元格字体
                foreach ($this->font as $region => $value) {
                    $event->sheet->getDelegate()
                        ->getStyle($region)
                        ->getFont()->setName($value);
                }
                //设置区域单元格字体大小
                foreach ($this->fontSize as $region => $value) {
                    $event->sheet->getDelegate()
                        ->getStyle($region)
                        ->getFont()
                        ->setSize($value);
                }
 
                //设置区域单元格字体粗体
                foreach ($this->bold as $region => $bool) {
                    $event->sheet->getDelegate()
                        ->getStyle($region)
                        ->getFont()
                        ->setBold($bool);
                }
 
 
                //设置区域单元格背景颜色
                foreach ($this->background as $region => $item) {
                    $event->sheet->getDelegate()->getStyle($region)->applyFromArray([
                        'fill' => [
                            'fillType' => 'linear', //线性填充，类似渐变
                            'startColor' => [
                                'rgb' => $item //初始颜色
                            ],
                            //结束颜色，如果需要单一背景色，请和初始颜色保持一致
                            'endColor' => [
                                'argb' => $item
                            ]
                        ]
                    ]);
                }
                //设置边框颜色
                foreach ($this->borders as $region => $item) {
                    $event->sheet->getDelegate()->getStyle($region)->applyFromArray([
                        'borders' => [
                            'allBorders' => [
                                'borderStyle' =>Border::BORDER_THIN,
                                'color' => ['argb' => $item],
                            ],
                        ],
                    ]);
                }
                //合并单元格
                $event->sheet->getDelegate()->setMergeCells($this->mergeCells);
                if(!empty($this->sheetName)){
                    $event->sheet->getDelegate()->setTitle($this->sheetName);
                }
            }
        ];
    }
 
    /**
     * @return array
     * [
     *    'B' => 40,
     *    'C' => 60
     * ]
     */
    public function setColumnWidth (array $columnwidth)
    {
        $this->columnWidth = array_change_key_case($columnwidth, CASE_UPPER);
    }
 
    /**
     * @return array
     * [
     *    1 => 40,
     *    2 => 60
     * ]
     */
    public function setRowHeight (array $rowHeight)
    {
        $this->rowHeight = $rowHeight;
    }
 
    /**
     * @return array
     * [
     *    A1:K7 => '宋体'
     * ]
     */
    public function setFont (array $font)
    {
        $this->font = array_change_key_case($font, CASE_UPPER);
    }
 
    /**
     * @return array
     * @2020/3/22 10:33
     * [
     *    A1:K7 => true
     * ]
     */
    public function setBold (array $bold)
    {
        $this->bold = array_change_key_case($bold, CASE_UPPER);
    }
 
    /**
     * @return array
     * @2020/3/22 10:33
     * [
     *    A1:K7 => F0FF0F
     * ]
     */
    public function setBackground (array $background)
    {
        $this->background = array_change_key_case($background, CASE_UPPER);
    }
    /**
     * @return array
     * [
     *    A1:K7
     * ]
     */
    public function setMergeCells (array $mergeCells)
    {
        $this->mergeCells = array_change_key_case($mergeCells, CASE_UPPER);
    }
    /**
     * @return array
     * [
     *    A1:K7 => 14
     * ]
     */
    public function setFontSize (array $fontSize)
    {
        $this->fontSize = array_change_key_case($fontSize, CASE_UPPER);
    }
    /**
     * @return array
     * [
     *    A1:K7 => #000000
     * ]
     */
    public function setBorders (array $borders)
    {
        $this->borders = array_change_key_case($borders, CASE_UPPER);
    }
}

3、调用方法 导出文件（UserController .php)

<?php
namespace App\Http\Controllers\Admin;
use Maatwebsite\Excel\Facades\Excel;
use App\Exports\UserExport;
use App\Models\User as userModel;
class UserController extends Controller
{
    /**
     * 用户列表导出
     * @param Request $request
     */
    public function user_export(Request $request){
　　　　//设置表头
        $row = [[
            "id"=>'ID',
            "nickname"=>'用户昵称',
            "gender_text"=>'性别',
            "mobile"=>'手机号',
            "addtime"=>'创建时间  '
        ]];
 
       //数据$list=[
            0=>[
                "id"=>'1',
                "nickname"=>'张三',
                "gender_text"=>'男',
                "mobile"=>'18812345678',
                "addtime"=>'2019-11-21  '
            ],
            2=>[
                "id"=>'2',
                "nickname"=>'李四',
                "gender_text"=>'女',
                "mobile"=>'18812349999',
                "addtime"=>'2019-11-21  '
            ]
        ];
　　
　　　　//执行导出
?
        $data = $list;//要导入的数据
        $header = $row;//导出表头
        $excel = new export($data, $header,'导出sheetName');
        $excel->setColumnWidth(['B' => 40, 'C' => 40]);
        $excel->setRowHeight([1 => 40, 2 => 50]);
        $excel->setFont(['A1:Z1265' => '宋体']);
        $excel->setFontSize(['A1:I1' => 14,'A2:Z1265' => 10]);
        $excel->setBold(['A1:Z2' => true]);
        $excel->setBackground(['A1:A1' => '808080','C1:C1' => '708080']);
        $excel->setMergeCells(['A1:I1']);
        $excel->setBorders(['A2:D5' => '#000000']);
        return Excel::download($excel, '导出文件.xlsx');
 
?

参考了网上搜罗来的源码加上实际开发中用的sheetName设置、合并单元格、设置边框、设置字体等，网上留存，以备后用