Laravel扩展包Maatwebsite/excel(3.1) 导出案例

官网：https://docs.laravel-excel.com/3.1/getting-started/

安装

composer require maatwebsite/excel

导出

一、导出单个Sheet

1、service封装

<?php

namespace App\Utils\Excel;

use Maatwebsite\Excel\Concerns\FromArray;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithTitle;
use Maatwebsite\Excel\Concerns\WithColumnFormatting;

class ExcelExport implements FromArray, WithTitle, WithHeadings, WithColumnFormatting {

    // 数据
    private $data;

    // 表头
    private $headings;

    // sheet名称
    private $title;

    // 字段自定义格式
    private $columnFormats;

    /**
     * @param array $data
     * @param array $headings
     * @param string $title
     * @param string $filename
     * @param array $columnFormats
     * @return \Symfony\Component\HttpFoundation\BinaryFileResponse
     */
    public function download(array $data = [], array $headings = [], string $title = 'WorkSheet', string $filename = '未命名.xlsx', array $columnFormats = []) {
        $this->data          = $data;
        $this->headings      = $headings;
        $this->title         = $title;
        $this->columnFormats = $columnFormats;
        return \Maatwebsite\Excel\Facades\Excel::download($this, $filename);
    }

    /**
     * 数据
     * @var array
     */
    public function array() : array {
        return $this->data ? : [];
    }

    /**
     * 表头
     * @var array
     */
    public function title() : string {
        return $this->title ? : '';
    }

    /**
     * sheet名称
     * @var string
     */
    public function headings() : array {
        return $this->headings ? : [];
    }

    /**
     * 字段自定义格式
     * @return array
     */
    public function columnFormats() : array {
        return $this->columnFormats ? : [];
    }
}

2、使用案例

<?php

namespace App\OpenApi\Controllers\API\V1;

use App\OpenApi\Controllers\BaseController;
use App\Utils\Excel\ExcelExport;
use Illuminate\Http\Request;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;

class TestController extends BaseController {

    /**
     * @param Request $request
     * @return \Symfony\Component\HttpFoundation\BinaryFileResponse
     */
    public function index(Request $request) {
        // 数据
        $result = [
            [
                'field1' => '你这瓜保熟吗？',
                'field2' => '柚子味西瓜',
                'field3' => '柚子味香水',
                'field4' => '18000000000',
                'field5' => '12'
            ],
            [
                'field1' => '你这瓜保熟吗？',
                'field2' => '柚子味西瓜',
                'field3' => '柚子味香水',
                'field4' => '18000000000',
                'field5' => '12'
            ]
        ];
        // 标题
        $headings = ['字段1', '字段2', '字段3', '字段4', '字段5'];
        // 字段自定义格式
        $columnFormats = [
            'D' => NumberFormat::FORMAT_TEXT
        ];
        return (new ExcelExport())->download($result, $headings, 'WorkSheet', 'Demo.xlsx', $columnFormats);
    }
}

二、导出多个Sheet

1、services封装

<?php

namespace App\Utils\Excel;

use Maatwebsite\Excel\Concerns\FromArray;

use Maatwebsite\Excel\Concerns\WithTitle;
use Maatwebsite\Excel\Concerns\WithHeadings;

class Multiple implements FromArray, WithTitle, WithHeadings {

    private $data;
    private $headings;
    private $title;

    /**
     * @param array $data 数据
     * @param array $headings 头部
     * @param string $title sheet名称
     * @param array $columnFormats 格式化列
     */
    public function __construct(array $data, array $headings, string $title, array $columnFormats = []) {
        $this->data          = $data;
        $this->headings      = $headings;
        $this->title         = $title;
        $this->columnFormats = $columnFormats;
    }

    /**
     * 数据
     * @return array
     */
    public function array() : array {
        return $this->data ? : [];
    }

    /**
     * 表头
     * @return array
     */
    public function headings() : array {
        return $this->headings ? : [];
    }

    /**
     * sheet名称
     * @return string
     */
    public function title() : string {
        return $this->title ? : '';
    }

    /**
     * 列格式
     * @return array
     */
    public function columnFormats() : array {
        return $this->columnFormats ? : [];
    }
}
<?php

namespace App\Utils\Excel;

use Maatwebsite\Excel\Concerns\WithMultipleSheets;

class MultipleSheetsExport implements WithMultipleSheets {

    private $sheets;

    /**
     * @param array $data
     * @param array $registerEvents
     * @param string $filename
     * @return \Symfony\Component\HttpFoundation\BinaryFileResponse
     */
    public function download(array $sheets, string $filename = '未命名.xlsx') {
        $this->sheets = $sheets;
        return \Maatwebsite\Excel\Facades\Excel::download($this, $filename);
    }

    /**
     * @return array
     */
    public function sheets() : array {
        return $this->sheets ? : [];
    }
}

2、使用案例

<?php

namespace App\OpenApi\Controllers\API\V1;

use App\OpenApi\Controllers\BaseController;
use App\Utils\Excel\Multiple;
use App\Utils\Excel\MultipleSheetsExport;
use Illuminate\Http\Request;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;

class TestController extends BaseController {

    /**
     * @param Request $request
     * @return \Symfony\Component\HttpFoundation\BinaryFileResponse
     */
    public function index(Request $request) {
        // 数据
        $sheetData = [
            [
                'sheet'         => 'sheet1',
                'data'          => [
                    [
                        'field1' => '你这瓜保熟吗？',
                        'field2' => '柚子味西瓜',
                        'field3' => '柚子味香水',
                        'field4' => '18000000000',
                        'field5' => '12.00'
                    ],
                    [
                        'field1' => '你这瓜保熟吗？',
                        'field2' => '柚子味西瓜',
                        'field3' => '柚子味香水',
                        'field4' => '18000000000',
                        'field5' => '12.00'
                    ]
                ],
                'columnFormats' => [
                    'D' => NumberFormat::FORMAT_TEXT,
                    'E' => NumberFormat::FORMAT_TEXT,
                ]
            ],
            [
                'sheet'         => 'sheet2',
                'data'          => [[
                    'field1' => '你这瓜保熟吗？',
                    'field2' => '柚子味西瓜',
                    'field3' => '柚子味香水',
                    'field4' => '18000000000',
                    'field5' => '12.00'
                ],
                    [
                        'field1' => '你这瓜保熟吗？',
                        'field2' => '柚子味西瓜',
                        'field3' => '柚子味香水',
                        'field4' => '18000000000',
                        'field5' => '12.00'
                    ]
                ],
                'columnFormats' => [
                    'D' => NumberFormat::FORMAT_TEXT,
                    'E' => NumberFormat::FORMAT_TEXT,
                ]
            ]
        ];
        // 标题
        $headings = ['字段1', '字段2', '字段3', '字段4', '字段5'];
        $sheet    = [];
        foreach ($sheetData as &$item) {
            $sheet[] = new Multiple($item['data'], $headings, $item['sheet'], $item['columnFormats']);
        }
        return (new MultipleSheetsExport())->download($sheet);
    }
}