Laravel Excel

https://docs.laravel-excel.com/3.1/
Admin
Laravel Excel is a Laravel-flavoured wrapper around PhpSpreadsheet that simplifies exporting and...
Tokens:37,157
Snippets:478
Trust Score:8.9
Update:1 month ago
Show doc for...
Tokens:
Raw
### Define Custom Start Cell

Source: https://docs.laravel-excel.com/3.1/exports/collection.html

Implement WithCustomStartCell to specify a starting cell other than A1. This concern is only supported for FromCollection exports.

```php
namespace App\Exports;

use App\Invoice;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithCustomStartCell;

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

    public function startCell(): string
    {
        return 'B2';
    }
}
```

--------------------------------

### Manual UsersImport Class Structure

Source: https://docs.laravel-excel.com/3.1/imports

An example of the file structure for the `app/Imports` directory when creating an import class manually. This shows the location of the generated `UsersImport.php` file.

```text
.\n├── app\n│   ├── Imports\n│   │   ├── UsersImport.php\n│\n└── composer.json
```

--------------------------------

### Install Laravel Excel Package

Source: https://docs.laravel-excel.com/3.1/getting-started/installation.html

Use Composer to require the specific version of the Laravel Excel package.

```bash
composer require "maatwebsite/excel:^3.1"
```

--------------------------------

### Basic Export from Query

Source: https://docs.laravel-excel.com/3.1/exports/from-query.html

Implement the FromQuery concern and return a query builder instance. Do not call ->get() on the query.

```php
namespace App\Exports;

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

class InvoicesExport implements FromQuery
{
    use Exportable;

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

--------------------------------

### Attaching Job Middleware to an Import Class

Source: https://docs.laravel-excel.com/3.1/imports/queued.html

Use the `middleware` method within your import class to specify which job middleware should be applied. This example shows how to attach a `RateLimited` middleware.

```php
namespace App\Imports;

use App\Jobs\Middleware\RateLimited;
use Maatwebsite\Excel\Concerns\Importable;
use Maatwebsite\Excel\Concerns\FromQuery;

class ImportClass implements FromQuery
{
    use Importable;

    public function middleware()
    {
        return [new RateLimited];
    }

    public function retryUntil()
    {
        return now()->addSeconds(5);
    }

    public function query()
    {
        // ...
    }
}
```

--------------------------------

### Sheet Import Class Example

Source: https://docs.laravel-excel.com/3.1/imports/multiple-sheets.html

A basic sheet import class implementing ToCollection to process rows.

```php
namespace App\Imports;

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

class FirstSheetImport implements ToCollection
{
    public function collection(Collection $rows)
    {
        //
    }
}

```

--------------------------------

### Download Exported Data

Source: https://docs.laravel-excel.com/3.1/architecture/objects.html

Initiate a file download using an export object. The example demonstrates downloading user data for a specific year.

```php
Excel::download(new UsersExport(2019), 'users.xlsx');
```

--------------------------------

### Install Laravel Excel Without Caret

Source: https://docs.laravel-excel.com/3.1/getting-started/installation.html

If facing version conflicts, try installing the Laravel Excel package without specifying a caret version.

```bash
composer require maatwebsite/excel
```

--------------------------------

### Configuring Remote Temporary Files

Source: https://docs.laravel-excel.com/3.1/exports/queued.html

In a multi-server setup, ensure temporary files are consistent across jobs by configuring a remote disk in `config/excel.php`. This is crucial for load-balanced environments.

```php
'temporary_files' => [
    'remote_disk' => 's3',
],

```

--------------------------------

### Implement WithStartRow Concern for Imports

Source: https://docs.laravel-excel.com/3.1/imports/start-row.html

Use this concern to define the starting row for your import. Ensure the `startRow()` method returns an integer representing the row number to begin importing from.

```php
namespace App\Imports;

use App\User;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithStartRow;

class UsersImport implements ToModel, WithStartRow
{
    public function model(array $row)
    {
        return new User([
            'name' => $row[0],
        ]);
    }

    public function startRow(): int
    {
        return 2;
    }
}
```

--------------------------------

### Import Users in Controller

Source: https://docs.laravel-excel.com/3.1/imports

Call the `UsersImport` class from your controller to process an Excel file named 'users.xlsx'. This example shows how to initiate the import and redirect with a success message.

```php
use App\Imports\UsersImport;
use Maatwebsite\Excel\Facades\Excel;
use App\Http\Controllers\Controller;

class UsersController extends Controller 
{
    public function import() 
    {
        Excel::import(new UsersImport, 'users.xlsx');
        
        return redirect('/')->with('success', 'All good!');
    }
}
```

--------------------------------

### Set Specific Export Properties

Source: https://docs.laravel-excel.com/3.1/exports/settings.html

When using the WithProperties concern, you can omit any properties you do not wish to override. This example only sets the creator.

```php
namespace App\Exports;

use Maatwebsite\Excel\Concerns\WithProperties;

class InvoicesExport implements WithProperties
{
    public function properties(): array
    {
        return [
            'creator'        => 'Patrick Brouwers',
        ];
    }
}
```

--------------------------------

### Globally Registering BeforeExport Event Listener

Source: https://docs.laravel-excel.com/3.1/architecture/objects.html

Configure event listeners globally using Writer::listen to execute actions before any export process starts across the application. This is useful for common pre-export tasks.

```php
Writer::listen(BeforeExport::class, function () {
    // Do something before any export process starts.
});
```

--------------------------------

### Custom Value Binder for Specific Formatting

Source: https://docs.laravel-excel.com/3.1/exports/column-formatting.html

Implement WithCustomValueBinder and extend DefaultValueBinder to control how cell values are bound. This example ensures numeric values are explicitly set as numeric.

```PHP
namespace App\Exports;

use PhpOffice\PhpSpreadsheet\Cell\Cell;
use Maatwebsite\Excel\Concerns\ToModel;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use Maatwebsite\Excel\Concerns\WithCustomValueBinder;
use PhpOffice\PhpSpreadsheet\Cell\DefaultValueBinder;

class UsersExport extends DefaultValueBinder implements WithCustomValueBinder
{
    public function bindValue(Cell $cell, $value)
    {
        if (is_numeric($value)) {
            $cell->setValueExplicit($value, DataType::TYPE_NUMERIC);

            return true;
        }

        // else return default behavior
        return parent::bindValue($cell, $value);
    }
}
```

--------------------------------

### Import Object with Getter for Row Count

Source: https://docs.laravel-excel.com/3.1/architecture/objects.html

Implement a getter method in an import object to retrieve the total number of rows processed. This example tracks the row count during user import.

```php
namespace App\Imports;

use App\User;
use Maatwebsite\Excel\Concerns\ToModel;

class UsersImport implements ToModel
{
    private $rows = 0;

    public function model(array $row)
    {
        ++$this->rows;
    
        return new User([
            'name' => $row[0],
        ]);
    }
    
    public function getRowCount(): int
    {
        return $this->rows;
    }
}
```

--------------------------------

### Export Object with Constructor Injection

Source: https://docs.laravel-excel.com/3.1/architecture/objects.html

Inject data into an export object using its constructor. This example shows how to pass a year to filter user data for export.

```php
class UsersExport implements FromCollection {
    private $year;

    public function __construct(int $year) 
    {
        $this->year = $year;
    }
    
    public function collection()
    {
        return Users::whereYear('created_at', $this->year)->get();
    }
}
```

--------------------------------

### Export with Multiple Sheets

Source: https://docs.laravel-excel.com/3.1/exports/multiple-sheets.html

Implement `WithMultipleSheets` to create an Excel file with multiple sheets. The `sheets()` method should return an array of sheet export objects. This example iterates through months to create a sheet for each.

```php
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;
    }
}
```

--------------------------------

### Direct Download using Exportable Trait

Source: https://docs.laravel-excel.com/3.1/architecture

Shows how to initiate a download directly from an export object that uses the `Exportable` trait.

```php
return (new UsersExport)->download('users.xlsx');

```

--------------------------------

### Instantiate a Drawing object

Source: https://docs.laravel-excel.com/3.1/exports/drawings.html

Create and configure a new Drawing instance with properties like name, description, path, and height.

```php
$drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
$drawing->setName('Logo');
$drawing->setDescription('This is my logo');
$drawing->setPath(public_path('/img/logo.jpg'));
$drawing->setHeight(90);
```

--------------------------------

### Importing Users with Chunk Reading and Queuing

Source: https://docs.laravel-excel.com/3.1/imports/queued.html

Implement `ToModel`, `WithChunkReading`, and `ShouldQueue` to process imports in chunks via queue jobs. Each chunk of 1000 rows will be processed as a separate queue job.

```php
namespace App\Imports;

use App\User;
use Maatwebsite\Excel\Concerns\ToModel;
use Illuminate\Contracts\Queue\ShouldQueue;
use Maatwebsite\Excel\Concerns\WithChunkReading;

class UsersImport implements ToModel, WithChunkReading, ShouldQueue
{
    public function model(array $row)
    {
        return new User([
            'name' => $row[0],
        ]);
    }

    public function chunkSize(): int
    {
        return 1000;
    }
}

```

--------------------------------

### Download Exported Query

Source: https://docs.laravel-excel.com/3.1/exports/from-query.html

Initiate the download of the export generated from a query.

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

--------------------------------

### Run Unit Tests

Source: https://docs.laravel-excel.com/3.1/getting-started/contributing.html

Run the existing tests to ensure no functionality is broken by your changes. This command should be executed from the project's root directory.

```bash
vendor/bin/phpunit
```

--------------------------------

### Import from a Full Local Path

Source: https://docs.laravel-excel.com/3.1/imports/basics.html

Import a file directly from its full path without needing to move it to a configured disk. Use the `storage_path()` helper for convenience.

```php
Excel::import(new UsersImport, storage_path('users.xlsx'));
```

--------------------------------

### Exportable Trait for Invoices

Source: https://docs.laravel-excel.com/3.1/exports/exportables.html

Use the Exportable trait to make export classes reusable. This example shows how to export all invoices.

```php
namespace App\Exports;

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

class InvoicesExport implements FromCollection
{
    use Exportable;

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

--------------------------------

### Add Macro to Sheet

Source: https://docs.laravel-excel.com/3.1/exports/extending.html

Extend the Sheet class with a custom macro. This example adds a 'setOrientation' macro to control page orientation.

```php
use \Maatwebsite\Excel\Sheet;

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

--------------------------------

### Download Exported Data with Setter

Source: https://docs.laravel-excel.com/3.1/architecture/objects.html

Instantiate an export object, set its properties using setters, and then initiate a file download.

```php
$export = new UsersExport();
$export->setYear(2019);

Excel::download($export, 'users.xlsx');
```

--------------------------------

### Publish Excel Configuration

Source: https://docs.laravel-excel.com/3.1/getting-started/installation.html

Run the vendor publish command to create the configuration file for Laravel Excel.

```bash
php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider" --tag=config
```

--------------------------------

### Add Macro to Writer

Source: https://docs.laravel-excel.com/3.1/exports/extending.html

Extend the Writer class with a custom macro to add shortcuts to PhpSpreadsheet methods. This example adds a 'setCreator' macro.

```php
use \Maatwebsite\Excel\Writer;

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

--------------------------------

### Download Export with Constructor Parameter

Source: https://docs.laravel-excel.com/3.1/exports/from-query.html

Download an export, passing a specific year to the export class constructor.

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

--------------------------------

### Convert Import to Array or Collection

Source: https://docs.laravel-excel.com/3.1/imports/basics.html

Bypass `ToArray` or `ToCollection` concerns to get imported data as an array or collection. Be mindful of performance implications for large files.

```php
$array = Excel::toArray(new UsersImport, 'users.xlsx');
```

```php
$collection = Excel::toCollection(new UsersImport, 'users.xlsx');
```

--------------------------------

### Import from a Specific Filesystem Disk (e.g., S3)

Source: https://docs.laravel-excel.com/3.1/imports/basics.html

Specify an alternative filesystem disk, such as 's3', for importing by providing it as the third parameter to the `Excel::import()` method.

```php
Excel::import(new UsersImport, 'users.xlsx', 's3');
```

--------------------------------

### Store Export with Disk Options

Source: https://docs.laravel-excel.com/3.1/exports/exportables.html

Pass additional options to the storage disk when storing an export.

```php
return (new InvoicesExport)->store('invoices.xlsx', 's3', null, 'private');
```

--------------------------------

### Implement Batch Inserts

Source: https://docs.laravel-excel.com/3.1/imports/batch-inserts.html

Use the WithBatchInserts concern to specify how many models should be inserted into the database at once. This significantly speeds up imports.

```php
namespace App\Imports;

use App\User;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithBatchInserts;

class UsersImport implements ToModel, WithBatchInserts
{
    public function model(array $row)
    {
        return new User([
            'name' => $row[0],
        ]);
    }
    
    public function batchSize(): int
    {
        return 1000;
    }
}
```

--------------------------------

### Import from Default Filesystem Disk

Source: https://docs.laravel-excel.com/3.1/imports/basics.html

Use the `Excel::import()` method to import a file located in your default filesystem disk. Pass an instance of your import class and the filename.

```php
Excel::import(new UsersImport, 'users.xlsx');
```

--------------------------------

### Define User Import Logic with ToModel

Source: https://docs.laravel-excel.com/3.1/imports/basics.html

Implement the `ToModel` concern to map each row of the Excel file to a Eloquent model. Ensure necessary facades like `Hash` are imported.

```php
<?php

namespace App\Imports;

use App\Models\User;
use Illuminate\Support\Facades\Hash;
use Maatwebsite\Excel\Concerns\ToModel;

class UsersImport implements ToModel
{
    /**
     * @param array $row
     *
     * @return User|null
     */
    public function model(array $row)
    {
        return new User([
           'name'     => $row[0],
           'email'    => $row[1],
           'password' => Hash::make($row[2]),
        ]);
    }
}
```

--------------------------------

### Handling Import Failures with Events

Source: https://docs.laravel-excel.com/3.1/imports/queued.html

Implement `WithEvents` and listen for the `ImportFailed` event to handle failures in queued imports. This allows you to notify users or perform other actions when an import fails.

```php
namespace App\Imports;

use App\User;
use App\Notifications\ImportHasFailedNotification;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Events\ImportFailed;
use Illuminate\Contracts\Queue\ShouldQueue;
use Maatwebsite\Excel\Concerns\WithChunkReading;

class UsersImport implements ToModel, WithChunkReading, ShouldQueue, WithEvents
{
    public function __construct(User $importedBy)
    {
        $this->importedBy = $importedBy;
    }

    public function registerEvents(): array
    {
        return [
            ImportFailed::class => function(ImportFailed $event) {
                $this->importedBy->notify(new ImportHasFailedNotification);
            },
        ];
    }
}

```

--------------------------------

### Download Export with Setter Method

Source: https://docs.laravel-excel.com/3.1/exports/from-query.html

Download an export, setting the year using the forYear method.

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

--------------------------------

### Combine Chunk Reading with Batch Inserts

Source: https://docs.laravel-excel.com/3.1/imports/chunk-reading.html

For optimal performance, combine `WithChunkReading` with `WithBatchInserts`. This approach minimizes both time and memory consumption by processing data in batches and chunks. Configure both `batchSize` and `chunkSize` methods.

```php
namespace App\Imports;

use App\User;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithBatchInserts;
use Maatwebsite\Excel\Concerns\WithChunkReading;

class UsersImport implements ToModel, WithBatchInserts, WithChunkReading
{
    public function model(array $row)
    {
        return new User([
            'name' => $row[0],
        ]);
    }
    
    public function batchSize(): int
    {
        return 1000;
    }
    
    public function chunkSize(): int
    {
        return 1000;
    }
}
```

--------------------------------

### Use Import with Progress Bar in Console Command

Source: https://docs.laravel-excel.com/3.1/imports/progress-bar.html

In your console command, instantiate your import class and use the `withOutput` and `import` methods to process the Excel file and display the progress bar.

```php
<?php

namespace App\Console\Commands;

use App\Imports\UsersImport;
use Illuminate\Console\Command;

class ImportExcel extends Command
{
    protected $signature = 'import:excel';

    protected $description = 'Laravel Excel importer';

    public function handle()
    {
        $this->output->title('Starting import');
        (new UsersImport)->withOutput($this->output)->import('users.xlsx');
        $this->output->success('Import successful');
    }
}

```

--------------------------------

### Forcing Remote Temporary File Resync in Multi-Server Setups

Source: https://docs.laravel-excel.com/3.1/imports/queued.html

Enable `force_resync_remote` to true in the configuration to ensure local temporary files are deleted on each server after its processed chunk. This prevents storage limits from being exceeded in multi-server environments.

```php
'temporary_files' => [
    'force_resync_remote' => true,
],

```

--------------------------------

### Store Export with Disk Options

Source: https://docs.laravel-excel.com/3.1/exports/store.html

Passes specific options to the filesystem disk when storing an export, like setting file visibility to 'private'.

```php
public function storeExcel()
{
    Excel::store(new InvoicesExport(2018), 'invoices.xlsx', 's3', null, [
        'visibility' => 'private',
    ]);
}
```

```php
public function storeExcel()
{
    Excel::store(new InvoicesExport(2018), 'invoices.xlsx', 's3', null, 'private');
}
```

--------------------------------

### Basic Queued Export

Source: https://docs.laravel-excel.com/3.1/exports/queued.html

Queue an export process by calling the `queue()` method on an export instance. This is suitable for large datasets that may take a long time to process.

```php
namespace App\Exports;

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

class InvoicesExport implements FromQuery
{
    use Exportable;

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

```

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

return back()->withSuccess('Export started!');

```

--------------------------------

### Apply Custom Macro and Set Page Orientation in AfterSheet Event

Source: https://docs.laravel-excel.com/3.1/exports/extending.html

Use the `registerEvents` method to hook into the `AfterSheet` event. This example demonstrates setting page orientation to landscape and applying custom cell styles using the `styleCells` macro.

```php
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'],
                            ],
                        ]
                    ]
                );
            },
        ];
    }
}

```

--------------------------------

### Manual UsersImport Class Implementation

Source: https://docs.laravel-excel.com/3.1/imports

Manually create this `UsersImport` class in `app/Imports`. It implements the `ToModel` concern to map rows from the Excel file to `User` model instances, hashing the password.

```php
<?php

namespace App\Imports;

use App\Models\User;
use Illuminate\Support\Facades\Hash;
use Maatwebsite\Excel\Concerns\ToModel;

class UsersImport implements ToModel
{
    /**
     * @param array $row
     *
     * @return User|null
     */
    public function model(array $row)
    {
        return new User([
           'name'     => $row[0],
           'email'    => $row[1], 
           'password' => Hash::make($row[2]),
        ]);
    }
}
```

--------------------------------

### Implement WithProgressBar in Import Class

Source: https://docs.laravel-excel.com/3.1/imports/progress-bar.html

Implement the `WithProgressBar` concern in your import class to enable progress bar display. This class handles the model mapping for each row.

```php
<?php

namespace App\Imports;

use App\User;
use Illuminate\Support\Facades\Hash;
use Maatwebsite\Excel\Concerns\Importable;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithProgressBar;

class UsersImport implements ToModel, WithProgressBar
{
    use Importable;

    public function model(array $row)
    {
        return new User([
            'name'     => $row[0],
            'email'    => $row[1],
            'password' => Hash::make($row[2]),
        ]);
    }
}

```

--------------------------------

### Import with SLK format

Source: https://docs.laravel-excel.com/3.1/imports/import-formats.html

Explicitly set the import format to SLK (Symbolic Link). Use this for data interchange files in this format.

```php
(new UsersImport)->import('users.slk', null, \Maatwebsite\Excel\Excel::SLK);
```

--------------------------------

### Export to HTML

Source: https://docs.laravel-excel.com/3.1/exports/export-formats.html

Explicitly set the export format to HTML.

```php
return Excel::download(new InvoicesExport, 'invoices.html', 
Maatwebsite\Excel\Excel::HTML);
```

--------------------------------

### Manual Users Export Class

Source: https://docs.laravel-excel.com/3.1/exports

Manually create an export class that implements FromCollection to export all users.

```php
<?php

namespace App\Exports;

use App\Models\User;
use Maatwebsite\Excel\Concerns\FromCollection;

class UsersExport implements FromCollection
{
    public function collection()
    {
        return User::all();
    }
}

```

--------------------------------

### Import with HTML format

Source: https://docs.laravel-excel.com/3.1/imports/import-formats.html

Explicitly set the import format to HTML. Use this to import data from HTML tables.

```php
(new UsersImport)->import('users.html', null, \Maatwebsite\Excel\Excel::HTML);
```

--------------------------------

### Import with XLSX format

Source: https://docs.laravel-excel.com/3.1/imports/import-formats.html

Explicitly set the import format to XLSX. Use this when the file extension might not be sufficient or for clarity.

```php
(new UsersImport)->import('users.xlsx', null, \Maatwebsite\Excel\Excel::XLSX);
```

--------------------------------

### Queue an Excel Import

Source: https://docs.laravel-excel.com/3.1/imports/importables.html

Queue an Excel import for background processing. This is useful for large files to avoid blocking the main request.

```php
(new UsersImport)->queue('users.xlsx');
```

--------------------------------

### Return Responsable Export Class

Source: https://docs.laravel-excel.com/3.1/exports/exportables.html

Return an instance of the export class implementing Responsable directly to handle the download response.

```php
return new InvoicesExport();
```

--------------------------------

### Configure Custom CSV Settings

Source: https://docs.laravel-excel.com/3.1/exports/settings.html

Implement the WithCustomCsvSettings interface to override default CSV export settings like delimiter, BOM usage, and output encoding.

```php
namespace App\Exports;

use Maatwebsite\Excel\Concerns\WithCustomCsvSettings;

class InvoicesExport implements WithCustomCsvSettings
{
    public function getCsvSettings(): array
    {
        return [
            'delimiter' => ';',
            'use_bom' => false,
            'output_encoding' => 'ISO-8859-1',
        ];
    }
}
```

--------------------------------

### Download Export with Exportable Trait

Source: https://docs.laravel-excel.com/3.1/exports/exportables.html

Download an export directly using an instance of the export class that utilizes the Exportable trait.

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

--------------------------------

### Create an InvoicesExport class

Source: https://docs.laravel-excel.com/3.1/exports/collection.html

Define an export class implementing FromCollection to return an Eloquent collection.

```php
namespace App\Exports;

use App\Invoice;
use Maatwebsite\Excel\Concerns\FromCollection;

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

--------------------------------

### Importing Multiple Sheets by Order

Source: https://docs.laravel-excel.com/3.1/imports/multiple-sheets.html

Implement WithMultipleSheets and return an array of sheet import objects. The order determines the sheet mapping.

```php
namespace App\Imports;

use Maatwebsite\Excel\Concerns\WithMultipleSheets;

class UsersImport implements WithMultipleSheets 
{
   
    public function sheets(): array
    {
        return [
            new FirstSheetImport()
        ];
    }
}

```

--------------------------------

### Download an export with custom headers

Source: https://docs.laravel-excel.com/3.1/exports/collection.html

Pass additional parameters to Excel::download to include headers and custom response headers.

```php
public function export() 
{
    return Excel::download(new InvoicesExport, 'invoices.xlsx', true, ['X-Vapor-Base64-Encode' => 'True']);
}
```

--------------------------------

### Basic Export Object Implementation

Source: https://docs.laravel-excel.com/3.1/architecture

Defines an export object that retrieves all users from the database. Requires the `FromCollection` concern.

```php
<?php

namespace App\Exports;

use App\User;
use Maatwebsite\Excel\Concerns\FromCollection;

class UsersExport implements FromCollection
{
    public function collection()
    {
        return User::all();
    }
}

```

--------------------------------

### Download Query Directly to Excel

Source: https://docs.laravel-excel.com/3.1/exports/from-query.html

Use the downloadExcel macro on an Eloquent query to directly export results to an Excel file.

```php
User::query()->where('name', 'Patrick')->downloadExcel('query-download.xlsx');
```

--------------------------------

### Registering events with WithEvents

Source: https://docs.laravel-excel.com/3.1/imports/extending.html

Implement the WithEvents concern and define the registerEvents method to return an array of event listeners. Closures cannot be used with queued imports.

```php
namespace App\Imports;

use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Events\BeforeImport;
use Maatwebsite\Excel\Events\AfterImport;
use Maatwebsite\Excel\Events\AfterSheet;
use Maatwebsite\Excel\Events\BeforeSheet;


class UsersImport implements WithEvents
{
    /**
     * @return array
     */
    public function registerEvents(): array
    {
        return [
            // Handle by a closure.
            BeforeImport::class => function(BeforeImport $event) {
                $creator = $event->reader->getProperties()->getCreator();
            },
			
		   
            // Using a class with an __invoke method.
            BeforeSheet::class => new BeforeSheetHandler(),
            
            // Array callable, refering to a static method.
            AfterSheet::class => [self::class, 'afterSheet'],
                        
        ];
    }
    
    public static function afterSheet(AfterSheet $event) 
    {
        //
    }
	
}
```

--------------------------------

### Container Binding for Custom Exporter

Source: https://docs.laravel-excel.com/3.1/architecture

Demonstrates how to bind a custom exporter class to the application's container, utilizing the 'excel' container binding.

```php
$this->app->bind(YourCustomExporter::class, function() {
    return new YourCustomExporter($this->app['excel']);
});

```

--------------------------------

### Export with Constructor Parameter for Year

Source: https://docs.laravel-excel.com/3.1/exports/from-query.html

Customize the query by passing a year as a constructor parameter to filter the results.

```php
namespace App\Exports;

use App\Invoice;
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);
    }
}
```

--------------------------------

### Apply Full Styling Map from PhpSpreadsheet

Source: https://docs.laravel-excel.com/3.1/exports/column-formatting.html

Demonstrates how to apply a comprehensive set of styles to a cell using the `applyFromArray` method, referencing PhpSpreadsheet's styling capabilities.

```php
/**
 * Apply styles from array.
 */
$spreadsheet->getActiveSheet()->getStyle('B2')->applyFromArray(
    [
        'font' => [
            'name' => 'Arial',
            'bold' => true,
            'italic' => false,
            'underline' => Font::UNDERLINE_DOUBLE,
            'strikethrough' => false,
            'color' => [
                'rgb' => '808080'
            ]
        ],
        'borders' => [
            'bottom' => [
                'borderStyle' => Border::BORDER_DASHDOT,
                'color' => [
                    'rgb' => '808080'
                ]
            ],
            'top' => [
                'borderStyle' => Border::BORDER_DASHDOT,
                'color' => [
                    'rgb' => '808080'
                ]
            ]
        ],
        'alignment' => [
            'horizontal' => Alignment::HORIZONTAL_CENTER,
            'vertical' => Alignment::VERTICAL_CENTER,
            'wrapText' => true,
        ],
        'quotePrefix'    => true
    ]
);


```

--------------------------------

### Test Dynamic File Names with Regex

Source: https://docs.laravel-excel.com/3.1/imports/testing.html

Enable regular expression matching for file names using Excel::matchByRegex(). This allows testing imports with dynamic or pattern-based file names.

```php
/**
* @test
*/
public function user_can_import_users()
{
    Excel::fake();
    
    $this->actingAs($this->givenUser())
         ->get('/users/import/xlsx');
    
    // Tells the mock to use regular expressions
    Excel::matchByRegex(); 
    // For a given dynamic named file 'dynamic_1234_filename.xlsx'
    Excel::assertImported('/\w{7}_\d{4}\_\w{8}\.xlsx/', 'diskName');
}
```

--------------------------------

### Directly Import with Importable Trait

Source: https://docs.laravel-excel.com/3.1/architecture

Instantiate your Import class and call the import method directly. This leverages the Importable trait for a streamlined import process.

```php
(new UsersImport)->import('users.xlsx');
```

--------------------------------

### Import with XML format

Source: https://docs.laravel-excel.com/3.1/imports/import-formats.html

Explicitly set the import format to XML. Use this when importing data structured in XML format.

```php
(new UsersImport)->import('users.xml', null, \Maatwebsite\Excel\Excel::XML);
```

--------------------------------

### Implement Strict Null Comparison

Source: https://docs.laravel-excel.com/3.1/exports/collection.html

Use the WithStrictNullComparison concern to ensure zero values are exported as 0 instead of empty cells.

```php
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();
    }
}
```

--------------------------------

### Download Collection as Excel

Source: https://docs.laravel-excel.com/3.1/exports/collection.html

Use these methods to trigger a browser download of a collection as an Excel file. Works with both Eloquent collections and standard Laravel collections.

```php
User::all()->downloadExcel(
    $filePath,
    $writerType = null,
    $headings = false
)
```

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

--------------------------------

### Store Collection on Disk

Source: https://docs.laravel-excel.com/3.1/exports/collection.html

Saves a collection as an Excel file to a specified disk.

```php
User::all()->storeExcel(
    $filePath,
    $disk = null,
    $writerType = null,
    $headings = false
)
```

--------------------------------

### Download Query with Header Row

Source: https://docs.laravel-excel.com/3.1/exports/from-query.html

Export query results to Excel, including a header row by passing true as the third parameter.

```php
User::query()->downloadExcel('query-download.xlsx', Excel::XLSX, true);
```

--------------------------------

### Configuring Global Event Listeners

Source: https://docs.laravel-excel.com/3.1/exports/extending.html

Set up global event listeners in your AppServiceProvider to apply consistent actions across all exports in your application. This is useful for cross-cutting concerns.

```php
Writer::listen(BeforeExport::class, function () {
    //
});

Writer::listen(BeforeWriting::class, function () {
    //
});

Sheet::listen(BeforeSheet::class, function () {
    //
});

Sheet::listen(AfterSheet::class, function () {
    //
});
```

--------------------------------

### Download Export with Writer Type and Headers

Source: https://docs.laravel-excel.com/3.1/exports/exportables.html

Specify the writer type (e.g., CSV) and custom headers when downloading an export.

```php
return (new InvoicesExport)->download('invoices.csv', Excel::CSV, ['Content-Type' => 'text/csv']);
```

--------------------------------

### Download Export in Controller

Source: https://docs.laravel-excel.com/3.1/exports/from-generator.html

Trigger the file download by calling the download method on the export class instance within a controller.

```php
public function export() 
{
    return (new DataExport)->download('data.xlsx');
}
```

--------------------------------

### Direct Eloquent Import with Headings

Source: https://docs.laravel-excel.com/3.1/imports/model.html

Import an Excel file directly into Eloquent rows using the `import` macro. Ensure your Excel file has a heading row that matches your database table column names.

```php
User::query()->import('import-users-with-headings.xlsx');

```

--------------------------------

### Generate UsersImport Class

Source: https://docs.laravel-excel.com/3.1/imports

Use the `make:import` Artisan command to generate a new import class for the User model. This command creates the basic file structure for your import.

```bash
php artisan make:import UsersImport --model=User
```

--------------------------------

### Implement FromView Concern

Source: https://docs.laravel-excel.com/3.1/exports/from-view.html

Create an export class that implements the FromView concern to return a Blade view.

```php
namespace App\Exports;

use App\Invoice;
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()
        ]);
    }
}
```

--------------------------------

### Handle import errors with SkipsOnError

Source: https://docs.laravel-excel.com/3.1/imports/validation.html

Implement SkipsOnError to prevent the entire import from rolling back when a database exception occurs.

```php
<?php

namespace App\Imports;

use App\User;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\Importable;
use Maatwebsite\Excel\Concerns\SkipsOnError;
use Maatwebsite\Excel\Concerns\WithValidation;

class UsersImport implements ToModel, WithValidation, SkipsOnError
{
    use Importable;

    /**
     * @param \Throwable $e
     */
    public function onError(\Throwable $e)
    {
        // Handle the exception how you'd like.
    }
}
```

--------------------------------

### Configuring global event listeners

Source: https://docs.laravel-excel.com/3.1/imports/extending.html

Register event listeners globally in a service provider to apply logic to all imports across the application.

```php
Reader::listen(BeforeImport::class, function () {
    //
});

Sheet::listen(AfterImport::class, function () {
    //
});

Sheet::listen(BeforeSheet::class, function () {
    //
});

Sheet::listen(AfterSheet::class, function () {
    //
});
```

--------------------------------

### Responsable Interface for Exports

Source: https://docs.laravel-excel.com/3.1/exports/exportables.html

Implement the Responsable interface to handle exports as HTTP responses. Requires defining fileName, writerType, and headers within the export class.

```php
namespace App\Exports;

use App\Invoice;
use Maatwebsite\Excel\Excel;
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';
    
    /**
    * Optional Writer Type
    */
    private $writerType = Excel::XLSX;
    
    /**
    * Optional headers
    */
    private $headers = [
        'Content-Type' => 'text/csv',
    ];

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

--------------------------------

### Register Excel Facade

Source: https://docs.laravel-excel.com/3.1/getting-started/installation.html

Manually add the Excel Facade to your Laravel application's config/app.php file if auto-discovery is not enabled.

```php
'aliases' => [
    ...
    'Excel' => Maatwebsite\Excel\Facades\Excel::class,
]
```

--------------------------------

### Directly Import an Excel File

Source: https://docs.laravel-excel.com/3.1/imports/importables.html

Import an Excel file directly using an instance of your importable class. Specify the file path, disk, and file type.

```php
(new UsersImport)->import('users.xlsx', 'local', \Maatwebsite\Excel\Excel::XLSX);
```

--------------------------------

### Add Multiple Charts to Export

Source: https://docs.laravel-excel.com/3.1/exports/charts.html

Return an array of Chart instances from the charts method to include multiple charts in the worksheet.

```php
<?php

namespace App\Exports;

use Maatwebsite\Excel\Concerns\WithCharts;
use PhpOffice\PhpSpreadsheet\Worksheet\Chart;

class InvoicesExport implements WithCharts
{
    public function charts()
    {
        $label      = [new DataSeriesValues('String', 'Worksheet!$B$1', null, 1)];
        $categories = [new DataSeriesValues('String', 'Worksheet!$B$2:$B$5', null, 4)];
        $values     = [new DataSeriesValues('Number', 'Worksheet!$A$2:$A$5', null, 4)];

        $series = new DataSeries(DataSeries::TYPE_PIECHART, DataSeries::GROUPING_STANDARD,
            range(0, \count($values) - 1), $label, $categories, $values);
        $plot   = new PlotArea(null, [$series]);

        $legend = new Legend();

        $chart  = new Chart('chart name', new Title('chart title'), $legend, $plot);
        $chart->setTopLeftPosition('D1');
        $chart->setBottomRightPosition('K12');

        $chart2 = new Chart('chart 2 name', new Title('chart 2 title'), $legend, $plot);
        $chart2->setTopLeftPosition('D13');
        $chart2->setBottomRightPosition('K24');

        return [$chart, $chart2];
    }
}
```

--------------------------------

### storeExcel

Source: https://docs.laravel-excel.com/3.1/exports/collection.html

Stores a collection as an Excel file on a specified disk.

```APIDOC
## storeExcel

### Description
Stores the collection as an Excel file on the specified disk.

### Signature
`storeExcel($filePath, $disk = null, $writerType = null, $headings = false)`

### Parameters
- **$filePath** (string) - Required - The path where the file should be stored.
- **$disk** (string) - Optional - The disk name to store the file on.
- **$writerType** (string) - Optional - The type of Excel writer to use.
- **$headings** (boolean) - Optional - Whether to include headings in the exported file.
```

--------------------------------

### Export via Constructor Injection

Source: https://docs.laravel-excel.com/3.1/architecture

Injects the `Excel` manager into the controller's constructor to perform a download. Ensure the `UsersExport` class is available.

```php
<?php
use App\Exports\UsersExport;
use Maatwebsite\Excel\Excel;

class ExportController
{
    private $excel;

    public function __construct(Excel $excel)
    {
        $this->excel = $excel;
    }
    
    public function exportViaConstructorInjection()
    {
        return $this->excel->download(new UsersExport, 'users.xlsx');
    }
    
    public function exportViaMethodInjection(Excel $excel)
    {
        return $excel->download(new UsersExport, 'users.xlsx');
    }
}

```

--------------------------------

### Store an export on a disk

Source: https://docs.laravel-excel.com/3.1/exports/collection.html

Save the generated file to a specific storage disk like s3 using Excel::store.

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

--------------------------------

### Download Multi-Sheet Excel

Source: https://docs.laravel-excel.com/3.1/exports/multiple-sheets.html

This method initiates the download of an Excel file containing all invoices from the current year, organized into 12 separate worksheets for each month.

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

--------------------------------

### Create a UsersImport class

Source: https://docs.laravel-excel.com/3.1/imports/collection.html

Implement the ToCollection interface to process rows from an Excel file. The collection method receives a collection of rows where each row is an array of cell values.

```php
namespace App\Imports;

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

class UsersImport implements ToCollection
{
    public function collection(Collection $collection)
    {
        foreach ($collection as $row) 
        {
            User::create([
                'name' => $row[0],
            ]);
        }
    }
}
```

--------------------------------

### Generate Users Export Class

Source: https://docs.laravel-excel.com/3.1/exports

Use the Artisan command to create a new export class for the User model.

```php
php artisan make:export UsersExport --model=User

```

--------------------------------

### Explicitly Queueing an Import

Source: https://docs.laravel-excel.com/3.1/imports/queued.html

Use the `Excel::queueImport` facade method to explicitly queue an import process. This is useful for direct control over when an import is queued.

```php
Excel::queueImport(new UsersImport, 'users.xlsx');

```

--------------------------------

### Attaching Job Middleware

Source: https://docs.laravel-excel.com/3.1/exports/queued.html

Use the `middleware()` method in your export class to attach job middleware, such as rate limiting. This is available for Laravel 6 and later.

```php
namespace App\Exports;

use App\Jobs\Middleware\RateLimited;
use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\FromQuery;

class ExportClass implements FromQuery
{
    use Exportable;
    
    public function middleware()
    {
        return [new RateLimited];
    }

    public function query()
    {
        // ...
    }
}

```

--------------------------------

### Enable Auto Size for Columns

Source: https://docs.laravel-excel.com/3.1/exports/column-formatting.html

Implement the ShouldAutoSize interface to allow Laravel Excel to automatically calculate and set column widths based on content.

```PHP
namespace App\Exports;

use Maatwebsite\Excel\Concerns\ShouldAutoSize;

class InvoicesExport implements ShouldAutoSize
{
    ...
}
```

--------------------------------

### Importing Data to Eloquent Models with ToModel

Source: https://docs.laravel-excel.com/3.1/imports/model.html

Use the `ToModel` concern to map each row of an import to an Eloquent model instance. The `model()` method should return a new model instance for each row. Avoid saving the model manually within this method to leverage batch insert functionality.

```php
namespace App\Imports;

use App\User;
use Maatwebsite\Excel\Concerns\ToModel;

class UsersImport implements ToModel
{
    public function model(array $row)
    {
        return new User([
            'name' => $row[0],
        ]);
    }
}

```

--------------------------------

### Export to XLS

Source: https://docs.laravel-excel.com/3.1/exports/export-formats.html

Explicitly set the export format to XLS.

```php
return Excel::download(new InvoicesExport, 'invoices.xls', 
Maatwebsite\Excel\Excel::XLS);
```

--------------------------------

### Test Downloading Excel Exports

Source: https://docs.laravel-excel.com/3.1/exports/testing.html

Assert that an Excel export was downloaded. You can optionally provide a callback to assert specific properties of the exported file.

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

```

--------------------------------

### Add a Single Chart to Export

Source: https://docs.laravel-excel.com/3.1/exports/charts.html

Implement the WithCharts concern in an export class and return a single Chart instance from the charts method.

```php
<?php

namespace App\Exports;

use Maatwebsite\Excel\Concerns\WithCharts;
use PhpOffice\PhpSpreadsheet\Worksheet\Chart;

class InvoicesExport implements WithCharts
{
    public function charts()
    {
        $label      = [new DataSeriesValues('String', 'Worksheet!$B$1', null, 1)];
        $categories = [new DataSeriesValues('String', 'Worksheet!$B$2:$B$5', null, 4)];
        $values     = [new DataSeriesValues('Number', 'Worksheet!$A$2:$A$5', null, 4)];

        $series = new DataSeries(DataSeries::TYPE_PIECHART, DataSeries::GROUPING_STANDARD,
            range(0, \count($values) - 1), $label, $categories, $values);
        $plot   = new PlotArea(null, [$series]);

        $legend = new Legend();
        $chart  = new Chart('chart name', new Title('chart title'), $legend, $plot);

        $chart->setTopLeftPosition('D1');
        $chart->setBottomRightPosition('K12');

        return $chart;
    }
}
```

--------------------------------

### Export to PDF using MPDF

Source: https://docs.laravel-excel.com/3.1/exports/export-formats.html

Export to PDF format using the MPDF library.

```php
return Excel::download(new InvoicesExport, 'invoices.pdf', 
Maatwebsite\Excel\Excel::MPDF);
```

--------------------------------

### Set Export Properties

Source: https://docs.laravel-excel.com/3.1/exports/settings.html

Use the WithProperties concern to set metadata for your export, such as title, author, and keywords. All properties are optional.

```php
namespace App\Exports;

use Maatwebsite\Excel\Concerns\WithProperties;

class InvoicesExport implements WithProperties
{
    public function properties(): array
    {
        return [
            'creator'        => 'Patrick Brouwers',
            'lastModifiedBy' => 'Patrick Brouwers',
            'title'          => 'Invoices Export',
            'description'    => 'Latest Invoices',
            'subject'        => 'Invoices',
            'keywords'       => 'invoices,export,spreadsheet',
            'category'       => 'Invoices',
            'manager'        => 'Patrick Brouwers',
            'company'        => 'Maatwebsite',
        ];
    }
}
```
Tokens:
Raw