回首页

Maatwebsite/Laravel-Excel虽然导出大数据性能不咋滴,但对于小数据来说还是很方便的,做个记录,方便下次使用的时候,不用翻文档找半天

在AppServiceProvider.php boot 方法添加

use Maatwebsite\Excel\Writer;
use Maatwebsite\Excel\Sheet;

 Writer::macro('setCreator', function (Writer $writer, string $creator) {
            $writer->getDelegate()->getProperties()->setCreator($creator);
        });
Sheet::macro('setOrientation', function (Sheet $sheet, $orientation) {
    $sheet->getDelegate()->getPageSetup()->setOrientation($orientation);
});
Sheet::macro('styleCells', function (Sheet $sheet, string $cellRange, array $style) {
    $sheet->getDelegate()->getStyle($cellRange)->applyFromArray($style);
});

新建ExporXXX文件

实现 FromCollection接口 数据来自

public function collection()
    {
        return collect(self::$pkSuccess);
    }

实现 FromQuery接口 数据来自

   public function query()
    {
        return Model::query();
    }
<?php namespace App\Export;

use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithMapping;
use Maatwebsite\Excel\Concerns\FromQuery;
use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\ShouldAutoSize;
//事件相关
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Concerns\RegistersEventListeners;
use Maatwebsite\Excel\Events\BeforeExport;
use Maatwebsite\Excel\Events\BeforeWriting;
use Maatwebsite\Excel\Events\BeforeSheet;
use Maatwebsite\Excel\Events\AfterSheet;

class ExporXXX implements FromCollection, WithHeadings, WithMapping, ShouldAutoSize, WithEvents
{
    use Exportable, RegistersEventListeners;

    protected static $pkSuccess;

    public function __construct($pkSuccess)
    {
        self::$pkSuccess = $pkSuccess;
    }
    public function query()
    {
        return Model::query();
    }

    public function collection()
    {
        return collect(self::$pkSuccess);
    }

    public function map($item): array
    {

        return [
            '',
            $item['number'],

        ];
    }

    public function headings(): array
    {
        return [
            '#',
            '编号',

        ];
    }

    public static function beforeExport(BeforeExport $event)
    {

        $event->writer->setCreator('Patrick');
    }

    public static function beforeWriting(BeforeWriting $event)
    {

    }
    //
    public static function beforeSheet(BeforeSheet $event)
    {
        //可合并单元格
//        $event->sheet->mergeCells('A1:C1');
//        $event->sheet->mergeCells('A2:C2');
//        $event->sheet->setCellValue('A1', '活动名称:');
//        $event->sheet->setCellValue('A2', '活动时间:');
    }
    //
    public static function afterSheet(AfterSheet $event)
    {
     //可合并单元格
        $length =  count(self::$pkSuccess)/2;
        for ($i=0;$i<$length;$i++){
            $cell1 = "A".(2*$i+2).":A".(2*$i+3);
            $cell2 = "L".(2*$i+2).":L".(2*$i+3);
            $event->sheet->mergeCells($cell1);
            $event->sheet->mergeCells($cell2);

            $event->sheet->setCellValue("A".(2*$i+2), "第".($i+1)."对");

        }

        //        $event->sheet->setOrientation(\PhpOffice\PhpSpreadsheet\Worksheet\PageSetup::ORIENTATION_LANDSCAPE);
        $styleArray = [
            'font' => [
                'bold' => true,
            ],
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT,
            ],
            'borders' => [
                'top' => [
                    'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                ],
            ],
            'fill' => [
                'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_GRADIENT_LINEAR,
                'rotation' => 90,
                'startColor' => [
                    'argb' => 'FFA0A0A0',
                ],
                'endColor' => [
                    'argb' => 'FFFFFFFF',
                ],
            ],
        ];
//        $event->sheet->styleCells(
//            'A1:C2',
//            [
//                'alignment' => array(
//                    'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
//                )
//            ]
//        );
//        $event->sheet->styleCells(
//            'A3:C10',
//            [
//                'alignment' => array(
//                    'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT,
//                )
//            ]
//        );
    }
    //    public function registerEvents(): array
//    {
//
//        return [
//            BeforeExport::class  => function(BeforeExport $event) {
//                $event->writer->setCreator('Patrick');
//            },
//            AfterSheet::class    => function(AfterSheet $event) {
//                $event->sheet->setOrientation(\PhpOffice\PhpSpreadsheet\Worksheet\PageSetup::ORIENTATION_LANDSCAPE);
//
//                $event->sheet->styleCells(
//                    'B2:G2',
//                    [
//                        'borders' => [
//                            'outline' => [
//                                'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK,
//                                'color' => ['argb' => 'FFFF0000'],
//                            ],
//                        ]
//                    ]
//                );
//            },
//        ];
//    }
}

导出

 return (new Elec($pkSuccess))->download('xxxx.xlsx');