Laravel excel 3.0 document

Time:2021-12-22

Basics

The simplest export method is to create a custom export class. Here we use invoice export as an example

stayApp/ExportsCreate nextInvoicesExportclass

namespace App\Exports;

use Maatwebsite\Excel\Concerns\FromCollection;

class InvoicesExport implements FromCollection
{
    public function collection()
    {
        return Invoice::all();
    }
}

In the controller, you can use the following methods to download

public function export() 
{
    return Excel::download(new InvoicesExport, 'invoices.xlsx');
}

Or stored ins3In disk

public function storeExcel() 
{
    return Excel::store(new InvoicesExport, 'invoices.xlsx', 's3');
}

Dependency injection

If your export needs dependencies, you can inject export classes

namespace App\Exports;

use Maatwebsite\Excel\Concerns\FromCollection;

class InvoicesExport implements FromCollection
{
    public function __construct(InvoicesRepository $invoices)
    {
        $this->invoices = $invoices;
    }

    public function collection()
    {
        return $this->invoices->all();
    }
}
public function export(Excel $excel, InvoicesExport $export) 
{
    return $excel->download($export, 'invoices.xlsx');
}

Strict null comparison

If you want0In Excel cells, 0 is displayed instead ofnull(empty cell), you can useWithStrictNullComparison

namespace App\Exports;

use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithStrictNullComparison;

class InvoicesExport implements FromCollection, WithStrictNullComparison
{
    public function __construct(InvoicesRepository $invoices)
    {
        $this->invoices = $invoices;
    }

    public function collection()
    {
        return $this->invoices->all();
    }
}

Collection global definition / macro

This package provides some additional methods (macros) of laravel collection to simply download or store in Excel

Download collection as Excel

(new Collection([[1, 2, 3], [1, 2, 3]]))->downloadExcel(
    $filePath,
    $writerType = null,
    $headings = false
)

Store collection on disk

(new Collection([[1, 2, 3], [1, 2, 3]]))->storeExcel(
    $filePath,
    $disk = null,
    $writerType = null,
    $headings = false
)

Store export on disk

The export can be stored in any laravel supportedfile systemMedium

public function storeExcel() 
{
    // Store on default disk
    Excel::store(new InvoicesExport(2018), 'invoices.xlsx');

    // Store on a different disk (e.g. s3)
    Excel::store(new InvoicesExport(2018), 'invoices.xlsx', 's3');

    // Store on a different disk with a defined writer type. 
    Excel::store(new InvoicesExport(2018), 'invoices.xlsx', 's3', Excel::XLSX);
}

Exportables / exportable

In the previous example, we usedExcel::downloadUse this facade to start an export

Laravel excel also supportsMaatwebsite\Excel\Concerns\ExportableTrait, so that a class can be exported directly. Of course, this class needs a collection method

namespace App\Exports;

use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\Exportable;

class InvoicesExport implements FromCollection
{
    use Exportable;

    public function collection()
    {
        return Invoice::all();
    }
}

We can download classes directly without using the facade

return (new InvoicesExport)->download('invoices.xlsx');

Or stored on disk

return (new InvoicesExport)->store('invoices.xlsx', 's3');

Responsable

The previous example can be simpler. For example, we add laravelResponsableExport to class

namespace App\Exports;

use Illuminate\Contracts\Support\Responsable;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\Exportable;

class InvoicesExport implements FromCollection, Responsable
{
    use Exportable;

    /**
    * It's required to define the fileName within
    * the export class when making use of Responsable.
    */
    private $fileName = 'invoices.xlsx';

    public function collection()
    {
        return Invoice::all();
    }
}

You can simply return the exported class, but you don’t need to call->download()method.

return new InvoicesExport();

From query / output from query

In the previous example, we query in the export class. Of course, this solution can be used in small export classes For export classes with larger data, it may cause large performance overhead

through the use ofFromQueryRelationship, we can pre query an export. The principle of this scenario is that the query can be executed in blocks

stayInvoicesExportClass, addFromQueryRelationship, and add a query, and make sure not to use->get()To get the data

namespace App\Exports;

use Maatwebsite\Excel\Concerns\FromQuery;
use Maatwebsite\Excel\Concerns\Exportable;

class InvoicesExport implements FromQuery
{
    use Exportable;

    public function query()
    {
        return Invoice::query();
    }
}

We can download it in the same way

return (new InvoicesExport)->download('invoices.xlsx');

Custom query

This method can be queried through user – defined parameters Simply import and export classes as dependencies

As constructor parameter

namespace App\Exports;

use Maatwebsite\Excel\Concerns\FromQuery;
use Maatwebsite\Excel\Concerns\Exportable;

class InvoicesExport implements FromQuery
{
    use Exportable;

    public function __construct(int $year)
    {
        $this->year = $year;
    }

    public function query()
    {
        return Invoice::query()->whereYear('created_at', $this->year);
    }
}

$yearParameters can be passed to the exported class

return (new InvoicesExport(2018))->download('invoices.xlsx');

As setting item

namespace App\Exports;

use Maatwebsite\Excel\Concerns\FromQuery;
use Maatwebsite\Excel\Concerns\Exportable;

class InvoicesExport implements FromQuery
{
    use Exportable;

    public function forYear(int $year)
    {
        $this->year = $year;

        return $this;
    }

    public function query()
    {
        return Invoice::query()->whereYear('created_at', $this->year);
    }
}

We can passforYearMethod to adjust the year

return (new InvoicesExport)->forYear(2018)->download('invoices.xlsx');

Through view

We can create an export from the blade view through the use ofFromViewRelationship

namespace App\Exports;

use Illuminate\Contracts\View\View;
use Maatwebsite\Excel\Concerns\FromView;

class InvoicesExport implements FromView
{
    public function view(): View
    {
        return view('exports.invoices', [
            'invoices' => Invoice::all()
        ]);
    }
}

This method will export an HTML table to an excel cell table, such asusers.blade.php:

<table>
    <thead>
    <tr>
        <th>Name</th>
        <th>Email</th>
    </tr>
    </thead>
    <tbody>
    @foreach($users as $user)
        <tr>
            <td>{{ $user->name }}</td>
            <td>{{ $user->email }}</td>
        </tr>
    @endforeach
    </tbody>
</table>

queue

If you are dealing with a larger amount of data, it is wise to use queues to run

For example, the following export class:

namespace App\Exports;

use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\FromQuery;

class InvoicesExport implements FromQuery
{
    use Exportable;

    public function query()
    {
        return Invoice::query();
    }
}

We just need to call one->queue()The method is OK

return (new InvoicesExport)->queue('invoices.xlsx');

The way to process these queries in the background is through multi task / multi cutting These tasks are performed in the correct order And ensure that the previous queries are correct

Another way of queue implementation

You can use the export as an implementation that can be thrown into the queue (using laravel)ShouldQueueConstraints

namespace App\Exports;

use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\FromQuery;
use Illuminate\Contracts\Queue\ShouldQueue;

class InvoicesExport implements FromQuery, ShouldQueue
{
    use Exportable;

    public function query()
    {
        return Invoice::query();
    }
}

In the controller, you can call the normal->store()method. be based onShouldQueue, queue processing is realized through the queue mode of laravel [PS: you need to configure the queue yourself first]

return (new InvoicesExport)->store('invoices.xlsx');

Append tasks / jobs

 queue()Method returns a laravelPendingDispatchInstance, which means you can connect other tasks in series. Subsequent tasks can be executed only when the current task is successfully executed

return (new InvoicesExport)->queue('invoices.xlsx')->chain([
    new NotifyUserOfCompletedExport(request()->user()),
]);
namespace App\Jobs;

use Illuminate\Bus\Queueable;
use Illuminate\Contracts\Queue\ShouldQueue;

class InvoiceExportCompletedJob implements ShouldQueue
{
    use Queueable;

    public function handle()
    {
        // Do something.
    }
}

Custom queue

WhenPendingDispatchWhen we return, we can change the queue we use

return (new InvoicesExport)->queue('invoices.xlsx')->allOnQueue('exports');

Multi cell table

In order for the export to support multi cell tables, you need to useWithMultipleSheetsRelationship thissheets()Method needs to return an array of cell tables

namespace App\Exports;

use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\WithMultipleSheets;

class InvoicesExport implements WithMultipleSheets
{
    use Exportable;

    protected $year;

    public function __construct(int $year)
    {
        $this->year = $year;
    }

    /**
     * @return array
     */
    public function sheets(): array
    {
        $sheets = [];

        for ($month = 1; $month <= 12; $month++) {
            $sheets[] = new InvoicesPerMonthSheet($this->year, $month);
        }

        return $sheets;
    }
}

thisInvoicesPerMonthSheetMultiple relationships can be implemented for exampleFromQueryFromCollection, …

namespace App\Exports;

use Maatwebsite\Excel\Concerns\FromQuery;
use Maatwebsite\Excel\Concerns\WithTitle;

class InvoicesPerMonthSheet implements FromQuery, WithTitle
{
    private $month;
    private $year;

    public function __construct(int $year, int $month)
    {
        $this->month = $month;
        $this->year  = $year;
    }

    /**
     * @return Builder
     */
    public function query()
    {
        return Invoice
            ::query()
            ->whereYear('created_at', $this->year)
            ->whereMonth('created_at', $this->month);
    }

    /**
     * @return string
     */
    public function title(): string
    {
        return 'Month ' . $this->month;
    }
}

Below you can download all invoices for 2018, which contains a 12 unit table to display the data of each month

public function download() 
{
    return (new InvoicesExport(2018))->download('invoices.xlsx');
}

Data traversal

Traversal row

By addingWithMapping, you can traverse every piece of data added to the cell row and return
In this way, you can control the data of each column. Suppose you use eloquent’s query builder


use Maatwebsite\Excel\Concerns\FromQuery;
use Maatwebsite\Excel\Concerns\WithMapping;

class InvoicesExport implements FromQuery, WithMapping
{
    /**
    * @var Invoice $invoice
    */
    public function map($invoice): array
    {
        return [
            $invoice->invoice_number,
            Date::dateTimeToExcel($invoice->created_at),
        ];
    }
}

Add header

You can add aWithHeadingsConstraints The header is added to the first row of all data


use Maatwebsite\Excel\Concerns\FromQuery;
use Maatwebsite\Excel\Concerns\WithHeadings;

class InvoicesExport implements FromQuery, WithHeadings

    public function headings(): array
    {
        return [
            '#',
            'Date',
        ];
    }
}

Format column

You can format the entire column by addingWithColumnFormatting, if you want more customization Recommended useAfterSheetEvents come directly to the dungeonWorksheetClass

namespace App\Exports;

use PhpOffice\PhpSpreadsheet\Shared\Date;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use Maatwebsite\Excel\Concerns\WithColumnFormatting;
use Maatwebsite\Excel\Concerns\WithMapping;

class InvoicesExport implements WithColumnFormatting, WithMapping
{
    public function map($invoice): array
    {
        return [
            $invoice->invoice_number,
            Date::dateTimeToExcel($invoice->created_at),
            $invoice->total
        ];
    }

    /**
     * @return array
     */
    public function columnFormats(): array
    {
        return [
            'B' => NumberFormat::FORMAT_DATE_DDMMYYYY,
            'C' => NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE,
        ];
    }
}

date

When the operation date Recommended use\PhpOffice\PhpSpreadsheet\Shared\Date::dateTimeToExcel()To correctly parse your date data

Export relationship

Interface Explanation
Maatwebsite\Excel\Concerns\FromCollection Use a Laravel Collection to populate the export.
Maatwebsite\Excel\Concerns\FromQuery Use an Eloquent query to populate the export.
Maatwebsite\Excel\Concerns\FromView Use a (Blade) view to to populate the export.
Maatwebsite\Excel\Concerns\WithTitle Set the Workbook or Worksheet title.
Maatwebsite\Excel\Concerns\WithHeadings Prepend a heading row.
Maatwebsite\Excel\Concerns\WithMapping Format the row before it’s written to the file.
Maatwebsite\Excel\Concerns\WithColumnFormatting Format certain columns.
Maatwebsite\Excel\Concerns\WithMultipleSheets Enable multi-sheet support. Each sheet can have its own concerns (except this one).
Maatwebsite\Excel\Concerns\ShouldAutoSize Auto-size the columns in the worksheet.
Maatwebsite\Excel\Concerns\WithStrictNullComparison Uses strict comparisions when testing cells for null value.
Maatwebsite\Excel\Concerns\WithEvents Register events to hook into the PhpSpreadsheet process.

Traits

Trait Explanation
Maatwebsite\Excel\Concerns\Exportable Add download/store abilities right on the export class itself.
Maatwebsite\Excel\Concerns\RegistersEventListeners Auto-register the available event listeners.

extend

event

The export process has events that you can use to interact with the underlying classes to add custom behavior to the export.

By using events, you can connect to the parent package. If you need complete control over the export, you don’t need to use convenient methods such as “query” or “view”.

Events will be added byWithEventsFocus to activate. stayregisterEventsMethod, you must return a series of events. Key is the fully qualified name (FQN) of the event, and value is the callable event listener. This can be a closure, a callable array, or an invokable class.

namespace App\Exports;

use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Events\BeforeExport;
use Maatwebsite\Excel\Events\BeforeWriting;
use Maatwebsite\Excel\Events\BeforeSheet;

class InvoicesExport implements WithEvents
{
    /**
     * @return array
     */
    public function registerEvents(): array
    {
        return [
            // Handle by a closure.
            BeforeExport::class => function(BeforeExport $event) {
                $event->writer->getProperties()->setCreator('Patrick');
            },

            // Array callable, refering to a static method.
            BeforeWriting::class => [self::class, 'beforeWriting'],

            // Using a class with an __invoke method.
            BeforeSheet::class => new BeforeSheetHandler()
        ];
    }

    public static function beforeWriting(BeforeWriting $event) 
    {
        //
    }
}

Note that usingClosureIt will not be possible to merge with the queue export because PHP cannot serialize closures. In these cases, it is best to useRegistersEventListenerscharacteristic.

Automatically register event listeners

By usingRegistersEventListenersTrail, you can automatically register event listeners without usingregisterEvents。 Listeners are registered only when methods are created.

namespace App\Exports;

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 InvoicesExport implements WithEvents
{
    use Exportable, RegistersEventListeners;

    public static function beforeExport(BeforeExport $event)
    {
        //
    }

    public static function beforeWriting(BeforeWriting $event)
    {
        //
    }

    public static function beforeSheet(BeforeSheet $event)
    {
        //
    }

    public static function afterSheet(AfterSheet $event)
    {
        //
    }
}

Available events

Event name Payload Explanation
Maatwebsite\Excel\Events\BeforeExport $event->writer : Writer Event gets raised at the start of the process.
Maatwebsite\Excel\Events\BeforeWriting $event->writer : Writer Event gets raised before the download/store starts.
Maatwebsite\Excel\Events\BeforeSheet $event->sheet : Sheet Event gets raised just after the sheet is created.
Maatwebsite\Excel\Events\AfterSheet $event->sheet : Sheet Event gets raised at the end of the sheet process.

macro

WriterandSheetCan be macro operated, which means that it can be easily extended to meet your needs. Both writer and sheet have one->getDelegate()Method, which returns the underlying PHP spreadsheet class. This will allow you to add shortcuts to the phpspreadsheets method, which is not available in this package.

Writer / write

use \Maatwebsite\Excel\Writer;

Writer::macro('setCreator', function (Writer $writer, string $creator) {
    $writer->getDelegate()->getProperties()->setCreator($creator);
});

Sheet / cell table

use \Maatwebsite\Excel\Sheet;

Sheet::macro('setOrientation', function (Sheet $sheet, $orientation) {
    $sheet->getDelegate()->getPageSetup()->setOrientation($orientation);
});

You can also add some shortcuts to style cells. You are free to use this macro or create your own syntax!

use \Maatwebsite\Excel\Sheet;

Sheet::macro('styleCells', function (Sheet $sheet, string $cellRange, array style) {
    $sheet->getDelegate()->getStyle($cellRange)->applyFromArray($style);
});

The above examples can be:

namespace App\Exports;

use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Events\BeforeExport;
use Maatwebsite\Excel\Events\AfterSheet;

class InvoicesExport implements WithEvents
{
    /**
     * @return array
     */
    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:G8',
                    [
                        'borders' => [
                            'outline' => [
                                'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK,
                                'color' => ['argb' => 'FFFF0000'],
                            ],
                        ]
                    ]
                );
            },
        ];
    }
}

For the phpspreadsheet method, you can view the documentation:https://phpspreadsheet.readthedocs.io/

Testing / testing

The Excel facade can be used to swap the exporter to a fake.

Test Download

/**
* @test
*/
public function user_can_download_invoices_export() 
{
    Excel::fake();

    $this->actingAs($this->givenUser())
         ->get('/invoices/download/xlsx');

    Excel::assertDownloaded('filename.xlsx', function(InvoicesExport $export) {
        // Assert that the correct export is downloaded.
        return $export->collection()->contains('#2018-01');
    });
}

Test store export

/**
* @test
*/
public function user_can_store_invoices_export() 
{
    Excel::fake();

    $this->actingAs($this->givenUser())
         ->get('/invoices/store/xlsx');

    Excel::assertStored('filename.xlsx', 'diskName');

    Excel::assertStored('filename.xlsx', 'diskName', function(InvoicesExport $export) {
        return true;
    });

    // When passing the callback as 2nd param, the disk will be the default disk.
    Excel::assertStored('filename.xlsx', function(InvoicesExport $export) {
        return true;
    });
}

Test queue export

/**
* @test
*/
public function user_can_queue_invoices_export() 
{
    Excel::fake();

    $this->actingAs($this->givenUser())
         ->get('/invoices/queue/xlsx');

    Excel::assertQueued('filename.xlsx', 'diskName');

    Excel::assertQueued('filename.xlsx', 'diskName', function(InvoicesExport $export) {
        return true;
    });

    // When passing the callback as 2nd param, the disk will be the default disk.
    Excel::assertQueued('filename.xlsx', function(InvoicesExport $export) {
        return true;
    });
}