laravel框架excel扩展包maatwebsite-excel升级3.1版本兼容处理

maatwebsite/excel2.1版本与3.1版本写法差别有点大，不能同时使用，下面是旧方法升级新方法的调整

单Sheet导入

---

旧的写法使用新的扩展包导入会报错Call to undefined method Maatwebsite\Excel\Facades\Excel::load(),解决方法

旧版本：

	$file = $request->file('exfile');
    Excel::load($file, function($reader) use ($data){
        $reader = $reader->getSheet(0); //获取excel的第1张表
        $results = $reader->toArray();  //获取表中的数据
        ......
    });

新版本：

	$file = $request->file('exfile');
    $import = new Import();
    $path = storage_path('app').'/'.$file->store('temp');
    Excel::import($import, $path);
    $results = $import->data->toArray();
    ......

Import.php代码如下，根据实际命名空间调整：

<?php

namespace App;

use Illuminate\Support\Collection;
use Maatwebsite\Excel\Concerns\ToCollection;

/**
 * Class Import
 * @package App
 */
class Import implements ToCollection
{
    public $data;

    public function __construct()
    {

    }

    /**
     * @param Collection $rows
     */
    public function collection(Collection $rows)
    {
        $this->data = $rows;
    }

}

注意：上面的写法只能读取到最后的Sheet,如果有空的Sheet请删除，只保留一个工作Sheet，不然会影响读取结果。

多Sheet导入

---

创建一个继承多sheet类MultipleImport.php，如果已存在则不需要创建，同样用到上面的Import.php，MultipleImport.php代码内容如下

<?php

namespace App;


use Maatwebsite\Excel\Concerns\WithMultipleSheets;

class MultipleImport implements WithMultipleSheets
{
    public $sheet;
    public $sheetCount;

    /**
     * UserImport constructor.
     * @param int $sheetCount sheet数量
     */
    public function __construct($sheetCount)
    {
        $this->sheetCount = $sheetCount;
    }

    public function sheets(): array
    {
        for ($i = 0; $i < $this->sheetCount; $i++) {
            $this->sheet[$i] = new Import();
        }
        return $this->sheet;
    }
}

在控制器中使用：

$file = $request->file('exfile');
$import = new MultipleImport(3);
Excel::import($import, $file->getRealPath());
$results = $import->sheet[0]->data->toArray();
$results2 = $import->sheet[1]->data->toArray();
$results3 = $import->sheet[2]->data->toArray();
dd($results,$results2,$results3);

导出excel

---

创建文件Export.php

<?php
namespace App;
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();
    }

    /**
     * @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);
    }
}

在控制器中调用导出下载：

use App\Export;
use Maatwebsite\Excel\Facades\Excel as LaravelExcel;

$data = [];//导出数据
$head = [];//第一行的列标题
$filename = 'excel';//导出文件名，中文好像有乱码
$excel = new Export($data, $head , 'Sheet1');
return LaravelExcel::download($excel, $filename . date('Y-m-d') . '.xls');

注意：download方法只能在控制器中使用