Laravel-Excel Documentation

1. Project Overview & Quickstart (SpartnerNL/Laravel-Excel) Copy Chapter

1. Project Overview & Quickstart (SpartnerNL/Laravel-Excel) Copy Chapter

Technical Documentation: SpartnerNL/Laravel-Excel

ℹ️ Provenance: Multi-Tier Ingestion · CodeWiki Reference

Overview (`README.md`) Copy Section

Supercharged Excel exports and imports

[Quickstart](https://docs.laravel-excel.com/3.1/exports/) · [Documentation](https://docs.laravel-excel.com/3.1/getting-started/) · [Video Course](https://course.laravel-excel.com) · [Nova](https://github.com/SpartnerNL/Laravel-Nova-Excel) · [Blog](https://medium.com/maatwebsite/laravel-excel/home) · [Contributing](https://docs.laravel-excel.com/3.1/getting-started/contributing.html) · [Support](https://docs.laravel-excel.com/3.1/getting-started/support.html)

✨ Features Copy Chapter

• Easily export collections to Excel. Supercharge your Laravel collections and export them directly to an Excel or CSV document. Exporting has never been so easy.

• Supercharged exports. Export queries with automatic chunking for better performance. You provide us the query, we handle the performance. Exporting even larger datasets? No worries, Laravel Excel has your back. You can queue your exports so all of this happens in the background.

• Supercharged imports. Import workbooks and worksheets to Eloquent models with chunk reading and batch inserts! Have large files? You can queue every chunk of a file! Your entire import will happen in the background.

• Export Blade views. Want to have a custom layout in your spreadsheet? Use a HTML table in a Blade view and export that to Excel.

🎓 Learning Laravel Excel Copy Chapter

You can find the full documentation of Laravel Excel on the website.

We welcome suggestions for improving our docs. The documentation repository can be found at https://github.com/SpartnerNL/laravel-excel-docs.

Some articles and tutorials can be found on our blog: https://medium.com/maatwebsite/laravel-excel/home

:mailbox_with_mail: License & Postcardware Copy Chapter

Laravel Excel is created with love and care by Spartner (formerly known as Maatwebsite) to give back to the Laravel community. It is completely free (MIT license) to use, however the package is licensed as Postcardware. This means that if it makes it to your production environment, we would very much appreciate receiving a postcard from your hometown.

Spartner
Markt 2
6231 LS Meerssen
The Netherlands.

More about the license can be found at: https://docs.laravel-excel.com/3.1/getting-started/license.html

Created by Spartner (formerly Maatwebsite) Copy Chapter

We are a strategic development partner, creating web-based custom built software from Laravel. In need of a digital solution for your challenge? Give us a call.

https://spartner.software
[email protected]
+31 (0) 10 - 7449312

:wrench: Supported Versions Copy Chapter

Versions will be supported for a limited amount of time.

--- METRICS ---

• Files Extracted: 1
• Estimated Token Budget: ~1160 tokens
• Canonical Reference: https://codewiki.google/github.com/SpartnerNL/Laravel-Excel

2. Official Technical Reference & Guides (SpartnerNL/laravel-excel-docs) Copy Chapter

File: 2.1/import/README.md Copy Chapter

Importing a file

To start importing a file, you can use ->load($filename). The callback is optional.

Excel::load('file.xls', function($reader) {

    // reader methods

});

File: 2.1/getting-started/README.md Copy Chapter

Installation

Require this package in your composer.json and update composer. This will download the package and PHPExcel of PHPOffice.

Laravel 4

"maatwebsite/excel": "~1.3"

Laravel 5

"maatwebsite/excel": "~2.1.0"

After updating composer, add the ServiceProvider to the providers array in app/config/app.php

Maatwebsite\Excel\ExcelServiceProvider::class,

You can use the facade for shorter code. Add this to your aliasses:

'Excel' => Maatwebsite\Excel\Facades\Excel::class,

The class is binded to the ioC as excel

$excel = App::make('excel');

File: 2.1/export/README.md Copy Chapter

Simple Excel Export

Basics Copy Section

A new file can be created using the create method with the filename as first parameter.

Excel::create('Filename');

To manipulate the creation of the file you can use the callback with LaravelExcelWriter parameter

Excel::create('Filename', function($excel) {

    // Call writer methods here

});

Changing properties Copy Section

There are a couple of properties we can change inside the closure. Most of them are set to the config values by default. See app/config/packages/maatwebsite/excel/config.php.

Excel::create('Filename', function($excel) {

    // Set the title
    $excel->setTitle('Our new awesome title');

    // Chain the setters
    $excel->setCreator('Maatwebsite')
          ->setCompany('Maatwebsite');

    // Call them separately
    $excel->setDescription('A demonstration to change the file properties');

});

Go to the reference guide to see a list of available properties.

File: 3.0/getting-started/README.md Copy Chapter

Introduction

:rocket: Laravel Excel is intended at being Laravel-flavoured PhpSpreadsheet: a simple, but elegant wrapper around PhpSpreadsheet with the goal of simplifying
exports.

:fire: PhpSpreadsheet is a library written in pure PHP and providing a set of classes that allow you to read from and to write to different spreadsheet file formats, like Excel and LibreOffice Calc.

Laravel Excel features:

• Easily export collections to Excel
• Export queries with automatic chunking for better performance
• Queue exports for better performance
• Easily export Blade views to Excel

File: 3.0/exports/README.md Copy Chapter

:rocket: 5 minute quick start Copy Chapter

:muscle: Create an export class in app/Exports

You may do this by using the make:export command.

php artisan make:export UsersExport --model=User

If you prefer to create the export manually, you can create the following in app/Exports:

<?php

namespace App\Exports;

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

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

:fire: In your controller you can call this export now:

use App\Exports\UsersExport;
use Maatwebsite\Excel\Facades\Excel;
use App\Http\Controllers\Controller;

class UsersController extends Controller 
{
    public function export() 
    {
        return Excel::download(new UsersExport, 'users.xlsx');
    }
}

:page_facing_up: Find your users.xlsx in your downloads folder!

File: 3.1/imports/README.md Copy Chapter

pageClass: no-toc Copy Chapter

:rocket: 5 minute quick start Copy Chapter

:muscle: Create an import class in app/Imports

You may do this by using the make:import command.

php artisan make:import UsersImport --model=User

The file can be found in app/Imports:

::: vue
.
├── app
│   ├── Imports
│   │   ├── UsersImport.php
│ 
└── composer.json
:::

If you prefer to create the import manually, you can create the following in app/Imports:

<?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]),
        ]);
    }
}

:fire: In your controller you can call this import now:

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!');
    }
}

:page_facing_up: Find the imported users in your database!

File: 3.1/getting-started/README.md Copy Chapter

pageClass: no-toc Copy Chapter

Introduction

:rocket: Laravel Excel is intended at being Laravel-flavoured PhpSpreadsheet: a simple, but elegant wrapper around PhpSpreadsheet with the goal of simplifying
exports and imports.

:fire: PhpSpreadsheet is a library written in pure PHP and providing a set of classes that allow you to read from and to write to different spreadsheet file formats, like Excel and LibreOffice Calc.

Laravel Excel Features Copy Chapter

• Easily export collections to Excel.
• Export queries with automatic chunking for better performance.
• Queue exports for better performance.
• Easily export Blade views to Excel.
• Easily import to collections.
• Read the Excel file in chunks.
• Handle the import inserts in batches.

File: 3.1/exports/README.md Copy Chapter

pageClass: no-toc Copy Chapter

:rocket: 5 minute quick start

[[toc]]

:muscle: Create an export class in app/Exports

You may do this by using the make:export command.

php artisan make:export UsersExport --model=User

The file can be found in app/Exports:

::: vue
.
├── app
│   ├── Exports
│   │   ├── UsersExport.php
│ 
└── composer.json
:::

If you prefer to create the export manually, you can create the following in app/Exports:

<?php

namespace App\Exports;

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

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

:fire: In your controller you can call this export now:

<?php

namespace App\Http\Controllers;

use App\Exports\UsersExport;
use Maatwebsite\Excel\Facades\Excel;

class UsersController extends Controller 
{
    public function export() 
    {
        return Excel::download(new UsersExport, 'users.xlsx');
    }
}

And finally add a route to be able to access the export:

Route::get('users/export/', [UsersController::class, 'export']);

:page_facing_up: Find your users.xlsx in your downloads folder!

File: 3.1/architecture/README.md Copy Chapter

Lifecycle

[[toc]]

Introduction Copy Chapter

When using a package in your application, it's good to understand how the package functions behind the scenes.
Understanding the behind-the-scenes will make you feel more comfortable and confident using the maximum potential of the tool.

The goal of this page is to give you a high-level overview of how Laravel Excel works.

Exports Lifecycle Overview Copy Chapter

This section will try to give you an overview of how the export works behind the scenes.

Export Object Copy Section

Everything starts with the Export object. This object encapsulates your entire export logic.
It both configures and handles the export logic.

A simple example of an export object is:

<?php

namespace App\Exports;

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

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

If you want to read more about export objects, go to the architecture page of export objects.

Passing on the Export object Copy Section

Next the Export object will be passed on to the Laravel Excel package. The main entry point for this is the Maatwebsite/Excel/Excel class. This class can be called in multiple ways.

Facade

The easiest way to work with the Excel class is to use the Maatwebsite\Excel\Facades\Excel facade.
If you use auto-discovery, you can use the alias \Excel directly instead of using the fully qualified namespace.

Dependency injection

You can inject the Maatwebsite/Excel/Excel manager class into your class, either via constructor injection
or method injection in case of a controller.

<?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');
    }
}

Contract

You can also use the Maatwebsite\Excel\Exporter interface to decouple more from the concrete Excel manager implementation. The contract offers the same methods as the Excel class.
It will make it easier to e.g. stub out the Exporter in your unit tests. The Exporter contract can be either injected via the constructor or a method in a controller.

use App\Exports\UsersExport;
use Maatwebsite\Excel\Exporter;

class ExportsController
{
    private $exporter;

    public function __construct(Exporter $exporter)
    {
        $this->exporter = $exporter;
    }
    
    public function export()
    {
        return $this->exporter->download(new UsersExport, 'users.xlsx');
    }
}

Container binding

If you want to bind the Maatwebsite\Excel\Excel manager to your own class via a container binding, you can use the excel container binding.

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

Exportable trait

If you prefer a sprinkle of magic, you can use the Maatwebsite\Excel\Concerns\Exportable trait in your Export object. This trait will expose a couple of methods that will make it possible to directly export an Export object.

namespace App\Exports;

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

class UsersExport implements FromCollection
{
    use Exportable;

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

You can now download the export without the need for the facade or Excel manager.

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

Read more about the exportable trait in the exportables docs.

Handling the Export object Copy Section

Writer type detection

After using one of the above methods to pass on the Export object to the Excel manager, it will try to figure out what export it needs to be generated to.
This will be either based on the extension of the file, the explicitly passed writer type. You can find the extension to writer type mapping in the excel.php config, in the extension_detector section.
In case no writer type can be detected, a Maatwebsite\Excel\Exceptions\NoTypeDetectedException exception is thrown and the export process will be stopped.

Starting the Writing process

The Excel manager will then delegate the handling to the Maatwebsite\Excel\Writer. The first action of the Writer is to register the event listeners that are registered.
Next it will create a new PhpOffice\PhpSpreadsheet\Spreadsheet instance that we will use to convert our Export object to.

The first event raised is the BeforeExport event. This is raised just after the Spreadsheet instance is created and allows early access to it.

Multiple sheets

Next the Writer will determine if multiple sheets are configured, by checking for the Maatwebsite\Excel\Concerns\WithMultipleSheets concern.

Then it will delegate the further handling of each sheet to the Maatwebsite\Excel\Sheet class.

Processing the sheets

In the Sheet class, the most heavy lifting happens. It first will create a PhpOffice\PhpSpreadsheet\Worksheet\Worksheet instance. Then it will raise the BeforeSheet event which allows you to hook into the moment just before the sheet handling starts.

Then it will determine what kind of export we are dealing with: FromQuery, FromArray, FromCollection or FromView. Based on that it will start the connected export process.

• FromView will pass on the rendered Blade view to PhpSpreadsheet's Html Reader. That Reader will turn the table html into Excel cells. It also handles some inline styles (color and background color) and col/rowspans.
• The Query passed with the FromQuery will automatically be chunked and each chunk will be appended to the Sheet. The chunking is done to limit the amount of Eloquent objects it needs to keep in memory. It greatly reduces memory usage.
• The entire array of Collection will directly be appended to the Sheet.

When the Sheet starts appending records, it will first call the map() method if the WithMapping concern is used. This allows the Export object to format the data before it is inserted.

Then it will handle column formatting (WithColumnFormatting concern) and cell autosizing (ShouldAutoSize).

To close off the Sheet processing, it will raise a AfterSheet event.

Passing on to PhpSpreadsheet Copy Section

After the sheets are processed, the writing process will start. The writing process is started by raising the BeforeWriting event; this allows you to hook into the process of writing.
Next we will create a new PhpSpreadsheet Writer based on the writer type that was determined. Then it will save it to a temporary file and return that filepath to the Excel manager.

Creating a Response Copy Section

The Excel manager basically has 2 types of responses, it either starts the download process or it will store the file to disk.

Download the file

We will take the temporary file that was returned by the Writer and use Laravel's ResponseFactory to create a Symfony\Component\HttpFoundation\BinaryFileResponse. When returning this response in your controller, it will start a download.

Storing the file

The storing of the file will be handled by Laravel's Filesystem. By default the file will be stored on your default disk, but you can also pass a custom disk via the Excel::store() method.

Imports Lifecycle Overview Copy Chapter

This section will try to give you an overview of how the import works behind the scenes.

Import Object Copy Section

Everything starts with the Import object. This object encapsulates your entire import logic.
It both configures and handles the import logic.

A simple example of an import object is:

namespace App\Imports;

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

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

If you want to read more about imports objects, go to the architecture page of imports objects.

Passing on the Import object Copy Section

Next the Import object will be passed on to the Laravel Excel package. The main entry point for this is the Maatwebsite/Excel/Excel class. This class can be called in the same way as outlined in the Export lifecycle.

Contract

You can also use the Maatwebsite\Excel\Importer interface to decouple more from the concrete Excel manager implementation. The contract offers the same methods as the Excel class.
It will make it easier to e.g. stub out the Importer in your unit tests. The Importer contract can be either injected via the constructor or the method of a controller.

use App\Imports\UsersImport;
use Maatwebsite\Excel\Importer;

class ImportsController
{
    private $importer;

    public function __construct(Importer $importer)
    {
        $this->importer = $importer;
    }
    
    public function import()
    {
        return $this->importer->import(new UsersImport, 'users.xlsx');
    }
}

Importable trait

If you prefer a sprinkle of magic, you can use the Maatwebsite\Excel\Concerns\Importable trait in your Import object. This trait will expose a couple of methods that will make it possible to directly import an Import object.

namespace App\Imports;

use App\User;
use Maatwebsite\Excel\Concerns\ToCollection;
use Maatwebsite\Excel\Concerns\Importable;

class UsersImport implements ToCollection
{
    use Importable;

    ...
}

You can now import without the need for the facade or Excel manager.

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

Read more about the importable trait in the importables docs.

Handling the Import object Copy Section

Reader type detection

After using one of the above methods to pass on the Import object to the Excel manager, it will try to figure out what reader type it is .
This will be either based on the extension of the file or the explicitly passed reader type. You can find the extension to reader type mapping in the excel.php config, in the extension_detector section.
In case no reader type can be detected, a Maatwebsite\Excel\Exceptions\NoTypeDetectedException exception is thrown and the import process will be stopped.

Starting the Reading process

The Excel manager will then delegate the handling to the Maatwebsite\Excel\Reader. The first action of the Reader is to register the event listeners that are registered.
It will copy the file from Laravel's Filesystem to the local filesystem, so PhpSpreadsheet can read it.
Next it will create a PhpSpreadsheet Reader based on the reader type that was given and load the file into a PhpOffice\PhpSpreadsheet\Spreadsheet instance.

Next it will create a new PhpOffice\PhpSpreadsheet\Spreadsheet instance that we will use to read our Import object from.

The first event that is raised, is the BeforeImport event. This is raised just after the Spreadsheet instance is loaded and allows early access to it.

Multiple sheets

Next we will determine if we are dealing with multiple sheets. This is done based on the WithMultipleSheets concern.

Processing the sheets

Then each Sheet gets processed. This process gets started off by raising the BeforeSheet event.
Then it will either import it to a Collection, an array or handle each row as an Eloquent model.

• When using ToModel, each returned model will be persisted via Eloquent. When using this in combination with WithBatchInserts, it will defer the persistence till the batch is complete and then insert them as one batch in the database.
• When using ToCollection or ToArray, the entire dataset will be passed to the Import method and the user can determine itself how to use it.

The sheet handling is ended by raising the AfterSheet event.

2. Official Technical Reference & Guides (SpartnerNL/laravel-excel-docs) Copy Chapter

File: README.md Copy Chapter

File: index.md Copy Chapter

head:

• • meta
  • http-equiv: refresh
    content: 0;url=/4.x/getting-started/

Redirecting to the Laravel Excel 4.x documentation…

File: package.json Copy Chapter

{
"private": true,
"type": "module",
"engines": {
"node": ">=20.19.0"
},
"scripts": {
"docs:dev": "vitepress dev",
"docs:build": "vitepress build",
"docs:preview": "vitepress preview"
},
"devDependencies": {
"@fontsource-variable/ibm-plex-sans": "^5.3.0",
"@fontsource/ibm-plex-mono": "^5.3.0",
"markdown-it-container": "^4.0.0",
"vitepress": "^1.6.4",
"vitepress-plugin-llms": "^1.14.0"
}
}

File: 2.1/index.md Copy Chapter

File: 2.1/blade/load-view.md Copy Chapter

@Blade to Excel

We can utilise the magic of Laravel's Blade engine to power our Excel export. Sharing a view, loading a view per sheet, creating a html table inside a view, basic CSS styling, ...

Loading a view for a single sheet

We can load a view for every sheet we create with ->loadView().

Excel::create('New file', function($excel) {

    $excel->sheet('New sheet', function($sheet) {

        $sheet->loadView('folder.view');

    });

});

Using different views for different sheets

Excel::create('New file', function($excel) {

    $excel->sheet('First sheet', function($sheet) {

        $sheet->loadView('view_first');
    });

    $excel->sheet('Second sheet', function($sheet) {

        $sheet->loadView('view_second');
    });

});

Sharing a view for all sheets

We can share a view for all sheets with shareView().

Excel::shareView('folder.view')->create();

Unsetting a view for a sheet

When we are using a shared view, but we don't want to use a view for the current sheet, we can use ->unsetView().

$sheet->unsetView();

File: 2.1/blade/styling.md Copy Chapter

Styling sheets

General styling Copy Section

If you want to change the general styling of your sheet (not cell or range specific), you can use the ->setStyle() method or any of the other setters which can be found inside the export documentation.

// Font family
$sheet->setFontFamily('Comic Sans MS');

// Set font with ->setStyle()`
$sheet->setStyle(array(
    'font' => array(
        'name'      =>  'Calibri',
        'size'      =>  12,
        'bold'      =>  true
    )
));

Styling with PHPExcel methods Copy Section

It's possible to style the sheets and specific cells with help of PHPExcel methods. This package includes a lot of shortcuts (see export documentation), but also always the use of the native methods.

// Set background color for a specific cell
$sheet->getStyle('A1')->applyFromArray(array(
    'fill' => array(
        'type'  => PHPExcel_Style_Fill::FILL_SOLID,
        'color' => array('rgb' => 'FF0000')
    )
));

Using HTML tags Copy Section

Most of the HTML tags are supported.

<html>

    
    <h1>Big title</h1>

    
    **Bold cell**
    **Bold cell**

    
    *Italic cell*

    
    

</html>

Inside the view.php config you can change how these tags will be interpreted by Excel by default.

Using HTML attributes Copy Section

Some of the basic styling can be done with HTML attributes.

<html>

    
    Big title

    
    Bold cell

    
    Bold cell

    
    Italic cell

    
    Cell with width of 100

    
    Cell with height of 100

</html>

Styling through inline-styles Copy Section

It's possible to use inline styles inside your view files. Most of the general styles are supported.

<html>

    
    Cell

</html>

Inside the reference guide you can find a list of supported styles.

Styling through external CSS file Copy Section

Styling can be done through an external CSS file.

External css file:

#cell {
    background-color: #000000;
    color: #ffffff;
}

.cell {
    background-color: #000000;
    color: #ffffff;
}

tr td {
    background-color: #ffffff;
}

tr > td {
    border-bottom: 1px solid #000000;
}

Table:

<html>
    {{ HTML::style('css/table.css') }}

    <!-- Cell styled with class -->
    <td class="cell">Cell</td>

    <!-- Cell styled with ID -->
    <td id="cell">Cell</td>
</html>

Inside the reference guide you can find a list of supported styles.

It's advised to include <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> into the view to fix problems with UTF-8 encoding.

File: 2.1/blade/vars.md Copy Chapter

Passing variables to the view

As parameter Copy Section

We can pass variables to the view by using the second parameter inside the loadView() method.

$sheet->loadView('view', array('key' => 'value'));

With with() Copy Section

Alternatively you can use the with() method which works the same as with Laravel views.

// Using normal with()
$sheet->loadView('view')
	  ->with('key', 'value');

// using dynamic with()
$sheet->loadView('view')
	  ->withKey('value');

File: 2.1/changelog/version-1.md Copy Chapter

Version 1

1.3.5 Copy Section

• PHPExcel 1.8.1 compatibility
• Clean-up ServiceProvider
• Fix short array syntax for PHP5.3

1.3.4 Copy Section

• Fix usage of sheet callback when modifying an existing file
• Modifying existing files improvements (support style overriding)
• Add text-indent support to HtmlReader
• Add simple sheet password protection
• Add support for exporting multiple pdf pages
• Add inline cell formatting to Blade

1.3.3 Copy Section

• Fix issue with different start row in chunk filter

1.3.2 Copy Section

• Custom value binders
• Html reader update

1.3.1 Copy Section

• Fix short array syntax

1.3.0 Copy Section

• Additional headers with export()
• Float, integer, string, timestamps problems
• Cell content ending by zeros
• Images in html
• Font family bug
• Setting row number of row with headings

1.2.3 Copy Section

• PDF writer
• Include charts config
• Chunk filter with selected sheet
• Compatibility fix with new PHPExcel release
• setDateColumns fix
• Optional dependencies to require-dev
• Several bugfixes

1.2.2 Copy Section

• Chunk filter fixes
• Isset() CellCollection fixes
• PHP 5.3 support
• Missing border styles
• Add CSV settings (delimiter, enclosure, lineEnding) to ExcelFile objects

1.2.1 Copy Section

• Fix with() method parameters

1.2.0 Copy Section

• Filters
• Chunk filter (with chunked importer)
• ExcelFile (method) injections
• NewExcelFile (method) injections
• Edit existing worksheets
• Converting existing worksheet
• Laravel 4.* + 5.0 support

1.1.9 Copy Section

• PHP 5.3 fixes

1.1.8 Copy Section

• PHP 5.3 support
• fromArray bugfix

1.1.7 Copy Section

• Fix heading generation for export with ->fromArray()
• Bugfix for non-Unix kernels
• Enhanced CSS parser (thanks to tijsverkoyen/CssToInlineStyles)
• Support for nested CSS styles
• Support for multiple css attributes per class
• Support for internal and external CSS files
• Support for inline style blocks (<style>)

1.1.6 Copy Section

• Provides.json fix
• DocBlock fixes
• Define Illuminate dependencies inside composer.json
• Better HTML rowspan handling views
• use new CellCollection() instead of ::make, to support upcoming Laravel version
• Workaround for long integers
• Add support to wrap-text in views
• Fix empty dates parsing
• Support local stylesheets in view parsing
• Push tr classes to td-children in views
• Support for dynamically appending rows to an empty (new) sheet
• Fix separator typo in config

1.1.5 Copy Section

• Select sheets by index with Excel::selectSheetsByIndex(0,1)->load(...)
• Separator typo fix
• Added ->setFileName() method
• Use ->setTitle() only for workbook title not for setting the filename anymore
• Made setAutoSize() chainable for other sheet methods
• Export config setting to disable pre calculation of formulas during export
• Export config setting to set the autosizing method (approx|exact)
• Auto sizing export from view fix

1.1.4 Copy Section

• Fix for importing 0 as null
• New unit tests

1.1.3 Copy Section

• Cell writer ->setBorder() fix

1.1.2 Copy Section

• Fix for multiple imports on one pageload
• Multiple new import heading conversions (Config: excel::import.heading: true|false|slugged|ascii|numeric|hashed|trans|original)

1.1.1 Copy Section

• Retrieve workbook and sheet title during import (->getTitle())

1.1.0 Copy Section

• Limit(), skip() and take() support for fetching results
• Set default page margins
• Export Eloquent models directly (fromModel())
• Auto generate the first row (table heading) from the array keys
• Manipulate cells and cell ranges inside a closure
• Set cell backgrounds/fonts/values, ...
• Create/append/prepend new row/rows
• Manipulate row cells (background, fonts, ...)
• Config value default alignment on merge cells
• DocBlock updates to support better use of IDE autocomplete features
• Parse width and height inside views
• Parse images in views
• Optional to ASCII conversion of imported header columns (array indices)
• Config values for default null comparision and start cells for exports
• Changed default CSV enclosure to "
• Support for Laravel package installer

1.0.9 Copy Section

• Blade to Excel export fix for PHP5.3

1.0.8 Copy Section

• File format identifier enhancements

1.0.7 Copy Section

• Set workbook properties fix
• Extra units tests

1.0.6 Copy Section

• BatchReader fix

1.0.5 Copy Section

• Date parsing fix

1.0.4 Copy Section

• Fix calling $this in anonymous function to set locale and cache

1.0.3 Copy Section

• Table headings to attribute names undefined offset fix
• Composer.json enhancements
• Documentation fixes

1.0.2 Copy Section

• Cell Collection fixes
• Default autosizing bugfixes
• ->load() accepts input encoding parameter
• Documentation fixes

1.0.1 Copy Section

• Column width and row height bugfix
• Typo fixes

1.0.0 Copy Section

• New documentation
• More logical file structure (dividing into files, separating the different functionality (import / export)
• More optional config settings
• CSV Delimiter fixes
• CSV Encoding
• Import into collections (to support utilisation of ->first(), etc.)
• Better column selecting and result limiting
• Batch upload
• Import dates as Carbon objects by default
• Advanced file import through config coordinates
• Select sheets to import
• Create closure (Excel::create('file', function($excel) { } ))
• More logical syntax for creating new files, syntaxes of creating by array and creating with view should be as identical as possible
• Rewrite of sheet building for views
• Using closures to build sheets for normal sheet creation
• Better support for calling native PHPExcel methods
• Better use of setters
• Config setting to set default store behavior
• Column/row width
• Share views over all sheets + easy views switching per sheet
• External stylesheet with classes/ids parsing for views
• Colspan fix
• Th default styling
• Caching / Cell caching

File: 2.1/changelog/version-2.md Copy Chapter

Version 2

2.0.4 Copy Section

• PHPExcel 1.8.1 compatibility
• Clean-up ServiceProvider

2.0.3 Copy Section

• Fix usage of sheet callback when modifying an existing file
• Modifying existing files improvements (support style overriding)
• Add text-indent support to HtmlReader
• Add simple sheet password protection
• Add support for exporting multiple pdf pages
• Add inline cell formatting to Blade

2.0.2 Copy Section

• Fix issue with different start row in chunk filter

2.0.1 Copy Section

• Custom value binders
• Html reader update

2.0.0 Copy Section

• Laravel 5 release

File: 2.1/export/array.md Copy Chapter

Creating a sheet from an array

Array Copy Chapter

To create a new file from an array use ->fromArray($source, $nullValue, $startCell, $strictNullComparison, $headingGeneration) inside the sheet closure.

Excel::create('Filename', function($excel) {

    $excel->sheet('Sheetname', function($sheet) {

        $sheet->fromArray(array(
            array('data1', 'data2'),
            array('data3', 'data4')
        ));

    });

})->export('xls');

Alternatively you can use ->with().

$sheet->with(array(
    array('data1', 'data2'),
    array('data3', 'data4')
));

If you want to pass variables inside the closure, use use($data)

$data = array(
    array('data1', 'data2'),
    array('data3', 'data4')
);

Excel::create('Filename', function($excel) use($data) {

    $excel->sheet('Sheetname', function($sheet) use($data) {

        $sheet->fromArray($data);

    });

})->export('xls');

Null comparision Copy Section

By default 0 is shown as an empty cell. If you want to change this behaviour, you can pass true as 4th parameter:

// Will show 0 as 0
$sheet->fromArray($data, null, 'A1', true);

To change the default behaviour, you can use excel::export.sheets.strictNullComparison config setting.

Eloquent model Copy Chapter

It's also possible to pass an Eloquent model and export it by using ->fromModel($model). The method accepts the same parameters as fromArray

Auto heading generation Copy Chapter

By default the export will use the keys of your array (or model attribute names) as first row (header column). To change this behaviour you can edit the default config setting (excel::export.generate_heading_by_indices) or pass false as 5th parameter:

// Won't auto generate heading columns
$sheet->fromArray($data, null, 'A1', false, false);

File: 2.1/export/autofilter.md Copy Chapter

Auto filter

To enable the auto filter use ->setAutoFilter($range = false).

// Auto filter for entire sheet
$sheet->setAutoFilter();

// Set auto filter for a range
$sheet->setAutoFilter('A1:E10');

File: 2.1/export/autosize.md Copy Chapter

Auto size

By default the exported file be automatically auto sized. To change this behaviour you can either change the config or use the setters:

// Set auto size for sheet
$sheet->setAutoSize(true);

// Disable auto size for sheet
$sheet->setAutoSize(false);

// Disable auto size for columns
$sheet->setAutoSize(array(
    'A', 'C'
));

The default config setting can be found in: export.php.

File: 2.1/export/call.md Copy Chapter

Calling PHPExcel's native methods

It's possible to call all native PHPExcel methods on the $excel and $sheet objects.

Calling Workbook methods Copy Section

Example:

// Get default style for this workbook
$excel->getDefaultStyle();

Calling worksheet methods Copy Section

Example:

// Protect cells
$sheet->protectCells('A1', $password);

Head over to PHPOffice to learn more about the native methods.

File: 2.1/export/cells.md Copy Chapter

Cell manipulation

$sheet->cell('A1', function($cell) {

    // manipulate the cell
    $cell->setValue('data1');

});

$sheet->cells('A1:A5', function($cells) {

    // manipulate the range of cells

});

Set background Copy Section

To change the background of a range of cells we can use ->setBackground($color, $type, $colorType)

// Set black background
$cells->setBackground('#000000');

Change fonts Copy Section

// Set with font color
$cells->setFontColor('#ffffff');

// Set font family
$cells->setFontFamily('Calibri');

// Set font size
$cells->setFontSize(16);

// Set font weight to bold
$cells->setFontWeight('bold');

// Set font
$cells->setFont(array(
    'family'     => 'Calibri',
    'size'       => '16',
    'bold'       =>  true
));

Set borders Copy Section

// Set all borders (top, right, bottom, left)
$cells->setBorder('solid', 'none', 'none', 'solid');

// Set borders with array
$cells->setBorder(array(
    'top'   => array(
        'style' => 'solid'
    ),
));

Set horizontal alignment Copy Section

// Set alignment to center
$cells->setAlignment('center');

Set vertical alignment Copy Section

// Set vertical alignment to middle
 $cells->setValignment('center');

File: 2.1/export/export.md Copy Chapter

Exporting

To download the created file, use ->export($ext) or ->download($ext).

Export to Excel5 (xls)

Excel::create('Filename', function($excel) {

})->export('xls');

// or
->download('xls');

Export to Excel2007 (xlsx)

->export('xlsx');

// or
->download('xlsx');

Export to CSV

->export('csv');

// or
->download('csv');

You can set the default enclosure and delimiter inside the config

Export to PDF

To export files to pdf, you will have to include "dompdf/dompdf": "~0.6.1", "mpdf/mpdf": "~6.1" or "tecnick.com/tcpdf": "~6.0.0" in your composer.json and change the export.pdf.driver config setting accordingly.

->export('pdf');

File: 2.1/export/format.md Copy Chapter

Column formatting

To tell Excel how it should interpret certain columns, you can use ->setColumnFormat($array).

// Format column as percentage
$sheet->setColumnFormat(array(
    'C' => '0%'
));

// Format a range with e.g. leading zeros
$sheet->setColumnFormat(array(
    'A2:K2' => '0000'
));

// Set multiple column formats
$sheet->setColumnFormat(array(
    'B' => '0',
    'D' => '0.00',
    'F' => '@',
    'F' => 'yyyy-mm-dd',
));

Go to the reference guide to see a list of available formats.

File: 2.1/export/freeze.md Copy Chapter

Freeze rows

If you want to freeze a cell, row or column, use:

// Freeze first row
$sheet->freezeFirstRow();

// Freeze the first column
$sheet->freezeFirstColumn();

// Freeze the first row and column
$sheet->freezeFirstRowAndColumn();

// Set freeze
$sheet->setFreeze('A2');

File: 2.1/export/index.md Copy Chapter

Simple Excel Export

Basics Copy Section

A new file can be created using the create method with the filename as first parameter.

Excel::create('Filename');

To manipulate the creation of the file you can use the callback with LaravelExcelWriter parameter

Excel::create('Filename', function($excel) {

    // Call writer methods here

});

Changing properties Copy Section

There are a couple of properties we can change inside the closure. Most of them are set to the config values by default. See app/config/packages/maatwebsite/excel/config.php.

Excel::create('Filename', function($excel) {

    // Set the title
    $excel->setTitle('Our new awesome title');

    // Chain the setters
    $excel->setCreator('Spartner')
          ->setCompany('Spartner');

    // Call them separately
    $excel->setDescription('A demonstration to change the file properties');

});

Go to the reference guide to see a list of available properties.

File: 2.1/export/injection.md Copy Chapter

NewExcelFile injections

Following the Laravel 5.0 philosophy with its new awesome FormRequest injections, we introduce you NewExcelFile injections.

NewExcelFile class Copy Chapter

This NewExcelFile is a wrapper for a new Excel file. Inside the getFilename() you can declare the wanted filename.

class UserListExport extends \Maatwebsite\Excel\Files\NewExcelFile {

    public function getFilename()
    {
        return 'filename';
    }
}

Usage Copy Chapter

You can inject these NewExcelFiles inside the __constructor or inside the method (when using Laravel 5.0), in e.g. the controller.

class ExampleController extends Controller {

    public function exportUserList(UserListExport $export)
    {
        // work on the export
        return $export->sheet('sheetName', function($sheet)
        {

        })->export('xls');
    }

}

Export Handlers Copy Chapter

To decouple your Excel-export code completely from the controller, you can use the export handlers.

class ExampleController extends Controller {

    public function exportUserList(UserListExport $export)
    {
        // Handle the export
        $export->handleExport();
    }

}

The handleExport() method will dynamically call a handler class which is your class name appended with Handler

class UserListExportHandler implements \Maatwebsite\Excel\Files\ExportHandler {

    public function handle(UserListExport $export)
    {
        // work on the export
        return $export->sheet('sheetName', function($sheet)
        {

        })->export('xls');
    }

}

File: 2.1/export/merge.md Copy Chapter

Column merging

Merging cells Copy Section

To merge a range of cells, use ->mergeCells($range).

$sheet->mergeCells('A1:E1');

Merging columns and rows Copy Section

To merge columns and rows, use ->setMergeColumn($array).

$sheet->setMergeColumn(array(
    'columns' => array('A','B','C','D'),
    'rows' => array(
        array(2,3),
        array(5,11),
    )
));

File: 2.1/export/rows.md Copy Chapter

Row manipulation

Manipulate certain row Copy Section

Change cell values

// Manipulate first row
$sheet->row(1, array(
     'test1', 'test2'
));

// Manipulate 2nd row
$sheet->row(2, array(
    'test3', 'test4'
));

Manipulate row cells

// Set black background
$sheet->row(1, function($row) {

    // call cell manipulation methods
    $row->setBackground('#000000');

});

Append row Copy Section

// Append row after row 2
$sheet->appendRow(2, array(
    'appended', 'appended'
));

// Append row as very last
$sheet->appendRow(array(
    'appended', 'appended'
));

Prepend row Copy Section

// Add before first row
$sheet->prependRow(1, array(
    'prepended', 'prepended'
));

// Add as very first
$sheet->prependRow(array(
    'prepended', 'prepended'
));

Append multiple rows Copy Section

// Append multiple rows
$sheet->rows(array(
    array('test1', 'test2'),
    array('test3', 'test4')
));

// Append multiple rows
$sheet->rows(array(
    array('test5', 'test6'),
    array('test7', 'test8')
));

File: 2.1/export/sheet-styling.md Copy Chapter

Sheet styling

General styling Copy Section

If you want to change the general styling of your sheet (not cell or range specific), you can use the ->setStyle() method.

// Set font with ->setStyle()`
$sheet->setStyle(array(
    'font' => array(
        'name'      =>  'Calibri',
        'size'      =>  15,
        'bold'      =>  true
    )
));

Fonts Copy Section

To change the font for the current sheet use ->setFont($array):

$sheet->setFont(array(
    'family'     => 'Calibri',
    'size'       => '15',
    'bold'       => true
));

Separate setters

// Font family
$sheet->setFontFamily('Comic Sans MS');

// Font size
$sheet->setFontSize(15);

// Font bold
$sheet->setFontBold(true);

Borders Copy Section

You can set borders for the sheet, by using:

// Sets all borders
$sheet->setAllBorders('thin');

// Set border for cells
$sheet->setBorder('A1', 'thin');

// Set border for range
$sheet->setBorder('A1:F10', 'thin');

Go to the reference guide to see a list of available border styles

File: 2.1/export/sheets.md Copy Chapter

Sheets

Creating a sheet Copy Section

To create a new sheet inside our newly created file, use ->sheet('Sheetname') with callback of LaravelExcelWorksheet type parameter.

Excel::create('Filename', function($excel) {

    $excel->sheet('Sheetname', function($sheet) {

        // Sheet manipulation

    });

})->export('xls');

Creating multiple sheets Copy Section

You can set as many sheets as you like inside the file:

Excel::create('Filename', function($excel) {

    // Our first sheet
    $excel->sheet('First sheet', function($sheet) {

    });

    // Our second sheet
    $excel->sheet('Second sheet', function($sheet) {

    });

})->export('xls');

Changing properties Copy Section

There are a couple of properties we can change inside the closure. Most of them are set to the config values by default. See app/config/packages/maatwebsite/excel/config.php.

Excel::create('Filename', function($excel) {

    $excel->sheet('Sheetname', function($sheet) {

        $sheet->setOrientation('landscape');

    });

})->export('xls');

Go to the reference guide to see a list of available properties.

Default page margin Copy Section

It's possible to set the default page margin inside the config file excel::export.sheets.
It accepts boolean, single value or array.

To manually set the page margin you can use: ->setPageMargin()

// Set top, right, bottom, left
$sheet->setPageMargin(array(
    0.25, 0.30, 0.25, 0.30
));

// Set all margins
$sheet->setPageMargin(0.25);

Password protecting a sheet Copy Section

A sheet can be password protected with $sheet->protect():

// Default protect
$sheet->protect('password');

// Advanced protect
$sheet->protect('password', function(\PHPExcel_Worksheet_Protection $protection) {
    $protection->setSort(true);
});

File: 2.1/export/sizing.md Copy Chapter

Cell size

Set column width Copy Section

To set the column width use ->setWidth($cell, $width).

// Set width for a single column
$sheet->setWidth('A', 5);

// Set width for multiple cells
$sheet->setWidth(array(
    'A'     =>  5,
    'B'     =>  10
));

Set row height Copy Section

To set the row height use ->setHeight($row, $height).

// Set height for a single row
$sheet->setHeight(1, 50);

// Set height for multiple rows
$sheet->setHeight(array(
    1     =>  50,
    2     =>  25
));

Set cell size Copy Section

To set the cell size use ->setSize($cell, $width, $height).

// Set size for a single cell
$sheet->setSize('A1', 500, 50);

$sheet->setSize(array(
    'A1' => array(
        'width'     => 50,
        'height'    => 500
    )
));

File: 2.1/export/store.md Copy Chapter

Store on server

To store the created file on the server, use ->store($ext, $path = false, $returnInfo = false) or ->save().

Normal export to default storage path Copy Section

By default the file will be stored inside the storage/exports folder, which has been defined in the export.php config file.

Excel::create('Filename', function($excel) {

    // Set sheets

})->store('xls');

Normal export to custom storage path Copy Section

If you want to use a custom storage path (e.g. to separate the files per client), you can set the folder as the second parameter.

->store('xls', storage_path('excel/exports'));

Store and export Copy Section

->store('xls')->export('xls');

Store and return storage info Copy Section

If you want to return storage information, set the third paramter to true or change the config setting inside export.php.

->store('xls', false, true);

Make sure your storage folder is writable!

File: 2.1/getting-started/config.md Copy Chapter

Config

Laravel 4 Copy Section

Laravel Excel includes several config settings for import-, export-, view- and CSV-specific settings.
Use the artisan publish command to publish the config file to your project.

php artisan config:publish maatwebsite/excel

The config files can now be found at app/config/packages/maatwebsite/excel

Laravel 5 Copy Section

To publish the config settings in Laravel 5 use:

php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider"

This will add an excel.php config file to your config folder.

File: 2.1/getting-started/contributing.md Copy Chapter

Contribution Guide

Bug fixes Copy Section

ALL bug fixes should be made to appropriate branch (e.g. 1.1 for 1.1.* bug fixes). Bug fixes should never be sent to the master branch.

Pull Requests Copy Section

Every pull request should pass the unit tests. If you include new functionality, make sure you include a test. Pull requests will be evaluated and possibly added to the next stable release.

Feature Requests Copy Section

If you have an idea for a new feature you would like to see added to Laravel Excel, you may create an issue on GitHub with [Request] in the title. The feature request will then be reviewed by @SpartnerNL.

Coding Guidelines Copy Section

Laravel, and therefore Spartner's Laravel Excel follows the PSR-0 and PSR-1 coding standards. In addition to these standards, below is a list of other coding standards that should be followed:

• Namespace declarations should be on the same line as <?php.
• Class opening { should be on the same line as the class name.
• Function and control structure opening { should be on a separate line.
• Interface and Trait names are suffixed with Interface (FooInterface) and Trait (FooTrait) respectively.

File: 2.1/getting-started/index.md Copy Chapter

Installation

Require this package in your composer.json and update composer. This will download the package and PHPExcel of PHPOffice.

Laravel 4

"maatwebsite/excel": "~1.3"

Laravel 5

"maatwebsite/excel": "~2.1.0"

After updating composer, add the ServiceProvider to the providers array in app/config/app.php

Maatwebsite\Excel\ExcelServiceProvider::class,

You can use the facade for shorter code. Add this to your aliasses:

'Excel' => Maatwebsite\Excel\Facades\Excel::class,

The class is binded to the ioC as excel

$excel = App::make('excel');

File: 2.1/getting-started/license.md Copy Chapter

License

This package is licensed under LGPL. You are free to use it in personal and commercial projects. The code can be forked and modified, but the original copyright author should always be included!

File: 2.1/getting-started/requirements.md Copy Chapter

Requirements

• PHP version >= 5.3.7
• Laravel >= 4.1
• PHPOffice PHPExcel >= 1.8.0 (included by composer.json)
• PHP extension php_zip enabled (required if you need PHPExcel to handle .xlsx .ods or .gnumeric files)
• PHP extension php_xml enabled
• PHP extension php_gd2 enabled (optional, but required for exact column width autocalculation)

File: 2.1/import/batch.md Copy Chapter

Batch import

Import a folder Copy Section

To import an entire folder (only XLS, XLSX and CSV files will be imported), set the folder as the first parameter.

Excel::batch('app/storage/uploads', function($rows, $file) {

    // Explain the reader how it should interpret each row,
    // for every file inside the batch
    $rows->each(function($row) {

        // Example: dump the firstname
        dd($row->firstname);

    });

});

Import multiple files Copy Section

It's also possible to provide an array of files to import.

$files = array(
    'file1.xls',
    'file2.xls'
);

Excel::batch($files, function($rows, $file) {

});

Import a folder and multiple sheets Copy Section

When your files contain multiple sheets, you should also loop the sheets

Excel::batch('app/storage/uploads', function($sheets, $file) {

    $sheets->each(function($sheet) {

    });

});

File: 2.1/import/cache.md Copy Chapter

Caching and Cell caching

Cell caching Copy Section

You can enable cell caching inside the config excel.php. You can choose between a couple of drivers and change a couple of settings. By default the caching is enabled and will use in memory caching.

Remembering results Copy Section

If you want to remember the results you can use ->remember($minutes). Next time you will load the same file (if it's still inside the cache), it will return the cached results.

// Remember for 10 minutes
$results = $reader->remember(10)->get();

File: 2.1/import/calculation.md Copy Chapter

Calculate formulas

By default formulas inside the file are being calculated and it's result will be returned. Inside import.php config you can change the default behaviour by setting calculate to the desired preference.

If you want to enable/disable it for a single import, you can use ->calculate($boolean)

// Enable calculation
$reader->calculate();

// Disable calculation
$reader->calculate(false);

File: 2.1/import/chunk.md Copy Chapter

Chunk importer

When dealing with big files, it's better to import the data in big chunks. You can enable this with filter('chunk');
To import it into chunks you can use chunk($size, $callback) instead of the normal get(). The first parameter is the size of the chunk. The second parameter is a closure which will return the results.

Excel::filter('chunk')->load('file.csv')->chunk(250, function($results)
{
        foreach($results as $row)
        {
            // do stuff
        }
});

ExcelFile class example: Copy Chapter

When working with ExcelFile injections (in the constructor or as method injection), you can enable the chunk filter inside the ExcelFile class

class UserListImport extends \Maatwebsite\Excel\Files\ExcelFile {

    public function getFile()
    {
        return 'file.csv';
    }

    public function getFilters()
    {
        return [
            'chunk'
        ];
    }

}

Injected ExcelFile example:

public function importUserList(UserListImport $import)
{
    $import->chunk(250, function($results)
    {
        // do stuff
        // or return true if you want to stop importing.
    });
}

Queued chunks Copy Chapter

We automatically queue every chunk for you, if you have enabled the queue driver in your config.

If you want to by-pass the behaviour, you can pass false as the third parameter of chunk($size, $callback, $shouldQueue).

A non-default job queue may be specified by passing its name as the third parameter of chunk($size, $callback, $shouldQueue).

File: 2.1/import/config.md Copy Chapter

Import by Config

When using advanced Excel files (e.g. without any heading columns), it can be complicated to import these.
->byConfig() will help you handle this problem.

Inside excel::import.sheets config you can find an example.

Excel::load('file.xls')->byConfig('excel::import.sheets', function($sheet) {

    // The firstname getter will correspond with a cell coordinate set inside the config
    $firstname = $sheet->firstname;

});

Note: if you are using multiple sheets. ->byConfig will loop through all sheets. If these getters are only exist on one sheet, you can always use ->selectSheets().

File: 2.1/import/convert.md Copy Chapter

Converting

You can convert from one filetype to another by using ->convert()

Excel::load('file.csv', function($file) {

    // modify stuff

})->convert('xls');

File: 2.1/import/dates.md Copy Chapter

Dates

By default the dates will be parsed as a Carbon object. You can disable date formatting completly inside import.php by setting dates.enabled to false.

To enable/disable date formatting for a single import, use ->formatDates($boolean, $format)

// Format the dates
$reader->formatDates(true);

// Disable date formatting
$reader->formatDates(false);

// Format dates + set date format
$reader->formatDates(true, 'Y-m-d');

Format dates Copy Section

By default the dates are not formatted, but returned as a Carbon object. There are a couple of options to format them.

Formatting results after ->get()

Inside your loop you can utilise the Carbon method ->format($dateFormat)

$rows->each(function($row) {

    $created_at = $row->created_at->format('Y-m-d');

});

Setting a default date format

Inside the config you can set a default date format. A Carbon object will no longer be returned.

Or you can use ->setDateFormat()

$reader->setDateFormat('Y-m-d');

Setting custom date columns Copy Section

Cells which are not Excel formatted dates will not be parsed as a date. To force this behaviour (or to use this with CSV imports), you can set these date columns manually: ->setDateColumns()

$reader->setDateColumns(array(
    'created_at',
    'deleted_at'
))->get();

File: 2.1/import/edit.md Copy Chapter

Editing existing files

You can edit existing Excel files, by loading them and after modification exporting them.

Excel::load('file.csv', function($file) {

    // modify stuff

})->export('csv');

File: 2.1/import/extra.md Copy Chapter

Extra

Disable using first row as collection attributes Copy Section

By default we will use the first row of a file as table heading (so as attribute names for the collection).
You can change the default behaviour inside import.php with import.heading.

To disable this for a single import, use ->noHeading().

$reader->noHeading();

Setting the cell name separator Copy Section

By default collection attribute names will be set by looking at the first row columns. Spaces will be translated to _.

E.g. Created at -> created_at

The default behaviour can be changed inside the import.php config by changing 'separator'. Or you can use ->setSeparator($separator).

$reader->setSeparator('-');

Ignoring empty cells Copy Section

By default empty cells will not be ignored and presented as null inside the cell collection.

To change the default behaviour, you can change 'ignoreEmpty' inside import.php or use ->ignoreEmpty().

$reader->ignoreEmpty();

Input encoding Copy Section

Inside the import.php config you can change the input encoding. In most cases UTF-8 will be the best solution. Hower if you dump your results make sure your HTML page has this exact same meta charset!

Optionally you can pass the input encoding inside the ->load() method.

// When utilising a closure, you can pass the input encoding as third parameter.
Excel::load('filename.csv', function($reader) {

}, 'UTF-8');

// or without a closure, you can use it as second parameter.
Excel::load('filename.csv', 'UTF-8');

CSV Settings Copy Section

Inside the csv.php config you can change the default settings, like the delimiter, the enclosure and the line_ending.

File: 2.1/import/formatting.md Copy Chapter

Custom formatting values

By default Laravel Excel uses PHPExcel's default value binder to intelligently format a cells value when reading it. You may override this behavior by passing in your own value binder to suit your specific needs. Value binders must implement PHPExcel_Cell_IValueBinder and have a bindValue method. They may also extend PHPExcel_Cell_DefaultValueBinder to return the default behavior.

use PHPExcel_Cell;
use PHPExcel_Cell_DataType;
use PHPExcel_Cell_IValueBinder;
use PHPExcel_Cell_DefaultValueBinder;

class MyValueBinder extends PHPExcel_Cell_DefaultValueBinder implements PHPExcel_Cell_IValueBinder
{
    public function bindValue(PHPExcel_Cell $cell, $value = null)
    {
        if (is_numeric($value))
        {
            $cell->setValueExplicit($value, PHPExcel_Cell_DataType::TYPE_NUMERIC);

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

$myValueBinder = new MyValueBinder;

$reader = Excel::setValueBinder($myValueBinder)->load('file.xls');
// Reader methods

Available PHPExcel_Cell_DataType's are TYPE_STRING, TYPE_FORMULA, TYPE_NUMERIC, TYPE_BOOL, TYPE_NULL, TYPE_INLINE and TYPE_ERROR

To reset the value binder back to default and/or before calling Laravel Excel after setting a custom value binder you need to call the resetValueBinder method.

Excel::resetValueBinder();

File: 2.1/import/index.md Copy Chapter

Importing a file

To start importing a file, you can use ->load($filename). The callback is optional.

Excel::load('file.xls', function($reader) {

    // reader methods

});

File: 2.1/import/injection.md Copy Chapter

ExcelFile injections

Following the Laravel 5.0 philosophy with its new awesome FormRequest injections, we introduce you ExcelFile injections.

ExcelFile class Copy Chapter

This class is a wrapper for a file on your server. Inside the getFile() method you return the filename and it's location. Inside the getFilters() method you can enable filters, like the chunk filter.

class UserListImport extends \Maatwebsite\Excel\Files\ExcelFile {

    public function getFile()
    {
        return storage_path('exports') . '/file.csv';
    }

    public function getFilters()
    {
        return [
            'chunk'
        ];
    }

}

If you want to have the getFile() dynamic based on user's input, you can easily do:

public function getFile()
{
    // Import a user provided file
    $file = Input::file('report');
    $filename = $this->doSomethingLikeUpload($file);

    // Return it's location
    return $filename;
}

Usage Copy Chapter

You can inject these ExcelFiles inside the __constructor or inside the method (when using Laravel 5.0), in e.g. the controller.

class ExampleController extends Controller {

    public function importUserList(UserListImport $import)
    {
        // get the results
        $results = $import->get();
    }

}

CSV Settings Copy Chapter

You can pass through optional CSV settings, like $delimiter, $enclosure and $lineEnding as protected properties of the class.

class UserListImport extends \Maatwebsite\Excel\Files\ExcelFile {

    protected $delimiter  = ',';
    protected $enclosure  = '"';
    protected $lineEnding = '\r\n';

}

Import Handlers Copy Chapter

To decouple your Excel-import code completely from the controller, you can use the import handlers.

class ExampleController extends Controller {

    public function importUserList(UserListImport $import)
    {
        // Handle the import
        $import->handleImport();
    }

}

The handleImport() method will dynamically call a handler class which is your class name appended with Handler

class UserListImportHandler implements \Maatwebsite\Excel\Files\ImportHandler {

    public function handle(UserListImport $import)
    {
        // get the results
        $results = $import->get();
    }

}

File: 2.1/import/results.md Copy Chapter

Handling imported results

Getting all sheets and rows Copy Section

After you have loaded a file, you can ->get() the results like so:

Excel::load('file.xls', function($reader) {

})->get();

or

Excel::load('file.xls', function($reader) {

    // Getting all results
    $results = $reader->get();

    // ->all() is a wrapper for ->get() and will work the same
    $results = $reader->all();

});

The ->get() and ->all() methods will return a sheet or row collection, depending on the amount of sheets the file has. You can disable this feature inside the import.php config by setting 'force_sheets_collection' to true. When set to true it will always return a sheet collection.

Table heading as attributes Copy Section

By default the first row of the Excel file will be used as attributes.

// Get the firstname
$row->firstname;

Note: by default these attributes will be converted to a slug. You can change the default inside the config excel::import.heading. Available options are: true|false|slugged|ascii|numeric|hashed|trans|original

True and slugged will be converted to ASCII as well when excel::import.to_ascii is set to true. You can change the default separator as well inside the config.

Collections Copy Section

Sheets, rows and cells are collections, this means after doing a ->get() you can use all default collection methods.

// E.g. group the results
$reader->get()->groupBy('firstname');

Getting the first sheet or row Copy Section

To get the first sheet or row, you can utilise ->first().

$reader->first();

Note: depending on the config 'force_sheets_collection' it will return the first row or sheet.

Workbook and sheet title Copy Section

It's possible to retrieve the workbook and sheet title with ->getTitle().

// Get workbook title
$workbookTitle = $reader->getTitle();

foreach($reader as $sheet)
{
    // get sheet title
    $sheetTitle = $sheet->getTitle();
}

Limiting the file reading Copy Section

Taking rows

When you only want to return the first x rows of a sheet, you can use ->takeRows() or ->limitRows().

// You can either use ->takeRows()
$reader->takeRows(10);

// Or ->limitRows()
$reader->limitRows(10);

Skipping rows

When you want to skip a certain amount of rows you can use ->skipRows() or ->limitRows(false, 10)

// Skip 10 results
$reader->skipRows(10);

// Skip 10 results with limit, but return all other rows
$reader->limitRows(false, 10);

// Skip and take
$reader->skipRows(10)->takeRows(10);

Taking columns

When you only want to return the first x columns of a sheet, you can use ->takeColumns() or ->limitColumns().

// You can either use ->takeColumns()
$reader->takeColumns(10);

// Or ->limitColumns()
$reader->limitColumns(10);

Skipping columns

When you want to skip a certain amount of columns you can use ->skipColumns() or ->limitColumns(false, 10)

// Skip 10 results
$reader->skipColumns(10);

// Skip 10 results with limit, but return all other columns
$reader->limitColumns(false, 10);

// Skip and take
$reader->skipColumns(10)->takeColumns(10);

Result mutators Copy Section

When you want to get an array instead of an object, you can use ->toArray().

$reader->toArray();

When you want an object, you can alternativly (instead of get() or all()) use ->toObject().

$reader->toObject();

Displaying results Copy Section

You can dump the results to a readable output by using ->dump() or ->dd().

// Dump the results
$reader->dump();

// Dump results and die
$reader->dd();

Iterating the results Copy Section

You can iterate the results by using ->each().

// Loop through all sheets
$reader->each(function($sheet) {

    // Loop through all rows
    $sheet->each(function($row) {

    });

});

Alternatively you can also foreach the results.

File: 2.1/import/select.md Copy Chapter

Selecting sheets and columns

Selecting one specific sheet Copy Section

If you want to select a single sheet, you can use ->selectSheets($name). Only that sheet will be loaded.

Excel::selectSheets('sheet1')->load();

Selecting multiple sheets Copy Section

If you want to select multiple sheets inside your file, you can pass an array as the parameter;

Excel::selectSheets('sheet1', 'sheet2')->load();

Selecting sheets by index Copy Section

// First sheet
Excel::selectSheetsByIndex(0)->load();

// First and second sheet
Excel::selectSheetsByIndex(0, 1)->load();

Selecting columns Copy Section

If you want to select only a couple of columns, you can use ->select($columns) or pass an array as the first parameter of ->get($columns).

// Select
$reader->select(array('firstname', 'lastname'))->get();

// Or
$reader->get(array('firstname', 'lastname'));

All get methods (like all(), first(), dump(), toArray(), ...) accept an array of columns.

File: 2.1/reference-guide/borders.md Copy Chapter

Available border styles

File: 2.1/reference-guide/closures.md Copy Chapter

Closures

File: 2.1/reference-guide/css-styles.md Copy Chapter

Available CSS styles

Styles that can be used inside an external CSS file or as inline CSS.

File: 2.1/reference-guide/file-properties.md Copy Chapter

Available file properties

Properties that can be set with $excel->set{$property}()

File: 2.1/reference-guide/formatting.md Copy Chapter

Available column formatting

File: 2.1/reference-guide/sheet-properties.md Copy Chapter

Available sheet properties

Properties that can be set with $sheet->set{$property}()

• See PHPExcel's Page Setup: Scaling options for more information

File: 3.0/index.md Copy Chapter

File: 3.0/exports/collection.md Copy Chapter

Exporting collections

The easiest way to start an export is to create a custom export class. We'll use an invoices export as example.

Create a new class called InvoicesExport in app/Exports:

namespace App\Exports;

use Maatwebsite\Excel\Concerns\FromCollection;

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

In your controller we can now download this export:

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

Or store it on a (e.g. s3) disk:

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

:bulb: More about storing exports can be found in the storing exports on disk page.

Dependency injection Copy Section

In case your export needs dependencies, you can inject the export class:

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 comparisons Copy Section

If you want your 0 values to be actual 0 values in your excel sheet instead of null (empty cells), you can use WithStrictNullComparison.

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 macros Copy Section

The package provides some macro to Laravel's collection class to easily download or store a collection.

Downloading a collection as Excel

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

Storing a collection on disk

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

File: 3.0/exports/column-formatting.md Copy Chapter

Formatting columns

You can easily format an entire column, by using WithColumnFormatting.
In case you want something more custom, it's suggested to use the AfterSheet event to directly interact with the underlying Worksheet class.

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_INTEGER,
        ];
    }
}

Dates Copy Section

When working with dates, it's recommended to use \PhpOffice\PhpSpreadsheet\Shared\Date::dateTimeToExcel() in your mapping
to ensure correct parsing of dates.

Auto size Copy Section

If you want Laravel Excel to perform an automatic width calculation, use the following code.

namespace App\Exports;

use Maatwebsite\Excel\Concerns\ShouldAutoSize;

class InvoicesExport implements ShouldAutoSize
{
    ...
}

File: 3.0/exports/concerns.md Copy Chapter

Export concerns

Traits Copy Section

File: 3.0/exports/export-formats.md Copy Chapter

Export formats

By default the export format is determined by the extension of the file, however if you want
to explicitly configured this, you can pass through the export format as 2nd parameter.

XLSX Copy Chapter

(new InvoicesExport)->download('invoices.xlsx', \Maatwebsite\Excel\Excel::XLSX);

CSV Copy Chapter

(new InvoicesExport)->download('invoices.csv', \Maatwebsite\Excel\Excel::CSV);

TSV Copy Chapter

(new InvoicesExport)->download('invoices.tsv', \Maatwebsite\Excel\Excel::TSV);

ODS Copy Chapter

(new InvoicesExport)->download('invoices.ods', \Maatwebsite\Excel\Excel::ODS);

XLS Copy Chapter

(new InvoicesExport)->download('invoices.xls', \Maatwebsite\Excel\Excel::XLS);

SLK Copy Chapter

(new InvoicesExport)->download('invoices.slk', \Maatwebsite\Excel\Excel::SLK);

XML Copy Chapter

(new InvoicesExport)->download('invoices.xml', \Maatwebsite\Excel\Excel::XML);

GNUMERIC Copy Chapter

(new InvoicesExport)->download('invoices.gnumeric', \Maatwebsite\Excel\Excel::GNUMERIC);

HTML Copy Chapter

(new InvoicesExport)->download('invoices.html', \Maatwebsite\Excel\Excel::HTML);

MPDF Copy Chapter

(new InvoicesExport)->download('invoices.pdf', \Maatwebsite\Excel\Excel::MPDF);

DOMPDF Copy Chapter

(new InvoicesExport)->download('invoices.pdf', \Maatwebsite\Excel\Excel::DOMPDF);

TCPDF Copy Chapter

(new InvoicesExport)->download('invoices.pdf', \Maatwebsite\Excel\Excel::TCPDF);

File: 3.0/exports/exportables.md Copy Chapter

Exportables

In the previous example, we used the Excel::download facade to start an export.

Laravel Excel also provides a Maatwebsite\Excel\Concerns\Exportable trait, to make export classes exportable.

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 now download the export without the need for the facade:

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

Or store it on a disk:

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

Responsable Copy Section

The previous example can be made even shorter when adding Laravel's Responsable interface to the export 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 now easily return the export class, without the need of calling ->download().

return new InvoicesExport();

File: 3.0/exports/extending.md Copy Chapter

Extending

[[toc]]

Events Copy Chapter

The export process has a few events you can leverage to interact with the underlying
classes to add custom behaviour to the export.

You are able to hook into the parent package by using events.
No need to use convenience methods like "query" or "view", if you need full control over the export.

The events will be activated by adding the WithEvents concern. Inside the registerEvents method, you
will have to return an array of events. The key is the Fully Qualified Name (FQN) of the event and the value is a callable event listener.
This can either be a closure, array-callable or 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) 
    {
        //
    }
}

Do note that using a Closure will not be possible in combination with queued exports, as PHP cannot serialize the closure.
In those cases it might be better to use the RegistersEventListeners trait.

Auto register event listeners Copy Section

By using the RegistersEventListeners trait you can auto-register the event listeners,
without the need of using the registerEvents. The listener will only be registered if the method is 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)
    {
        //
    }
}

Global event listeners Copy Section

Event listeners can also be configured globally, if you want to perform the same actions on all exports in your app.
You can add them to e.g. your AppServiceProvider in the register() method.

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

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

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

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

Available events Copy Section

Custom Concerns Copy Chapter

You can add custom concerns to your app. This can be useful if you want to share some concerns over a few projects or want to open source your custom concerns.

Let's add a WithCustomProperties concern to your app. You could add these concerns to app/Exports/Concerns, but any location will do as long as it can be auto-loaded by composer.

namespace App\Exports\Concerns;

interface WithCustomProperties
{
    /**
     *
     * @return string
     */
    public function description(): string;
}

Next to this concern we will create a concern handler. WithCustomerPropertiesHandler. This class can also be added to app/Exports/Concerns, but is again completely free of choice.
A concern handler is basically just an invokable class. It receives your exportable object and either a Writer or Sheet object, depending on the choosen event.

namespace App\Exports\Concerns;

use App\Exports\Concerns\WithCustomProperties;
use Maatwebsite\Excel\Writer;

class WithCustomPropertiesHandler
{
    /**
     * @param WithCustomProperties $exportable
     * @param Writer               $writer
     */
    public function __invoke(WithCustomProperties $exportable, Writer $writer)
    {
        $writer
            ->getDelegate()
            ->getProperties()
            ->setDescription(
                $exportable->description()
            );
    }
}

We then will register this concern in a service provider. You could use app/Providers/AppServiceProvider for this.

public function register()
{
    Excel::extend(WithCustomProperties::class, new WithCustomPropertiesHandler);
}

:bulb: ::extend accepts a callable as second parameter. It's also possible to pass a closure.

public function register()
{
    Excel::extend(WithCustomProperties::class, function(WithCustomProperties $exportable, Writer $writer) {
        $writer->getDelegate()->getProperties()->setDescription($exportable->description());
    });
}

You can also bind concern handlers to different hooks. By default a concern handler is always bound to the BeforeWriting event. You can easily customize this, by supplying a 3rd parameter.

public function register()
{
    Excel::extend(WithCustomProperties::class, new WithCustomPropertiesHandler, BeforeExport::class);
}

Macroable Copy Chapter

Both Writer and Sheet are "macroable" which means they can easily be extended to fit your needs.
Both Writer and Sheet have a ->getDelegate() method which returns the underlying PhpSpreadsheet class.
This will allow you to add custom macros as shortcuts to PhpSpreadsheet methods that are not available in this package.

Writer Copy Section

use \Maatwebsite\Excel\Writer;

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

Sheet Copy Section

use \Maatwebsite\Excel\Sheet;

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

Customize Copy Section

You can create your own macro to add custom methods to a spreadsheet instance.

For example, to add styling to a cell, we first create a macro, let's call it styleCells:

use \Maatwebsite\Excel\Sheet;

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

Once the macro is created, it can be used inside the registerEvents() method as such:

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

Feel free to use the above macro, or be creative and invent your own!

For PhpSpreadsheet methods, please refer to their documentation: https://phpspreadsheet.readthedocs.io/

File: 3.0/exports/from-query.md Copy Chapter

From Query

In the previous example, we did the query inside the export class.
While this is a good solution for small exports,
for bigger exports this will come at a hefty performance price.

By using the FromQuery concern, we can prepare a query for an export. Behind the scenes this query is executed in chunks.

In the InvoicesExport class, add the FromQuery concern, and return a query. Be sure to not ->get() the results!

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 still download the export in the same way:

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

Customizing the query Copy Section

It's easy to pass custom parameters to the query,
by simply passing them as dependencies to the export class.

As constructor param

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);
    }
}

The year can now be passed as dependency to the export class:

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

As setter

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 adjust the year by using the forYear method:

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

File: 3.0/exports/from-view.md Copy Chapter

From View

Exports can be created from a Blade view, by using the FromView concern.

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

It will convert an HTML table into an Excel spreadsheet. For example; users.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>

File: 3.0/exports/index.md Copy Chapter

:rocket: 5 minute quick start Copy Chapter

:muscle: Create an export class in app/Exports

You may do this by using the make:export command.

php artisan make:export UsersExport --model=User

If you prefer to create the export manually, you can create the following in app/Exports:

<?php

namespace App\Exports;

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

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

:fire: In your controller you can call this export now:

use App\Exports\UsersExport;
use Maatwebsite\Excel\Facades\Excel;
use App\Http\Controllers\Controller;

class UsersController extends Controller 
{
    public function export() 
    {
        return Excel::download(new UsersExport, 'users.xlsx');
    }
}

:page_facing_up: Find your users.xlsx in your downloads folder!

File: 3.0/exports/mapping.md Copy Chapter

Mapping data

Mapping rows Copy Section

By adding WithMapping you map the data that needs to be added as row. This way you have control over the actual source for each column.
In case of using the Eloquent 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),
        ];
    }
}

Adding a heading row Copy Section

A heading row can easily be added by adding the WithHeadings concern. The heading row will be added
as very first row of the sheet.

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

class InvoicesExport implements FromQuery, WithHeadings
    
    public function headings(): array
    {
        return [
            '#',
            'Date',
        ];
    }
}

File: 3.0/exports/multiple-sheets.md Copy Chapter

Multiple Sheets

To allow the export to have multiple sheets, the WithMultipleSheets concern should be used.
The sheets() method expects an array of sheet export objects to be returned.

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

The InvoicesPerMonthSheet can implement concerns like FromQuery, FromCollection, ...

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

This will now download an xlsx of all invoices in 2018, with 12 worksheets representing each month of the year.

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

File: 3.0/exports/queued.md Copy Chapter

Queued

In case you are working with a lot of data, it might be wise to queue the entire process.

Given we have 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();
    }
}

It's as easy as calling ->queue() now.

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

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

Behind the scenes the query will be chunked and multiple jobs will be chained. These jobs will be executed in the correct order,
and will only execute if none of the previous have failed.

Implicit Export queueing Copy Section

You can also mark an export implicitly as a queued export. You can do this by using Laravel's ShouldQueue contract.

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 your controller you can now call the normal ->store() method.
Based on the presence of the ShouldQueue contract, the export will be queued.

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

Appending jobs Copy Section

The queue() method returns an instance of Laravel's PendingDispatch. This means you can chain extra jobs that will be added to the end of the queue and only executed if all export jobs are correctly executed.

(new InvoicesExport)->queue('invoices.xlsx')->chain([
    new NotifyUserOfCompletedExport(request()->user()),
]);

namespace App\Jobs;

use App\User;
use Illuminate\Bus\Queueable;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Queue\SerializesModels;

class NotifyUserOfCompletedExport implements ShouldQueue
{
    use Queueable, SerializesModels;
    
    public $user;
    
    public function __construct(User $user)
    {
        $this->user = $user;
    }

    public function handle()
    {
        $this->user->notify(new ExportReady());
    }
}

Custom queues Copy Section

Because PendingDispatch is returned, we can also change the queue that should be used.

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

File: 3.0/exports/store.md Copy Chapter

Storing exports on disk

Exports can easily be stored on any filesystem that Laravel supports.

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);
}

File: 3.0/exports/testing.md Copy Chapter

Testing

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

Testing downloads Copy Section

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

Testing storing exports Copy Section

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

Testing queuing exports Copy Section

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

File: 3.0/getting-started/contributing.md Copy Chapter

Contributing

[[toc]]

Please read and understand the contribution guide before creating an issue or pull request.

Important Links Copy Chapter

• Docs
• Issue tracker
• Support

Etiquette Copy Chapter

This project is an open source project, and as such, the maintainers use their free time to build and maintain it.
The code is freely available and can be used, forked and modified.

Please be considerate towards maintainers when raising issues or presenting pull requests.

It's the duty of the maintainer to ensure that all submissions to the project are of sufficient
quality to benefit the project. Many developers have different skill sets, strengths, and weaknesses. Respect the maintainer's decision, and do not be upset or abusive if your submission is not used.

Viability Copy Chapter

When requesting or submitting new features, first consider whether it might be useful to others. Open
source projects are used by many developers, who may have entirely different needs to your own. Think about
whether or not your feature is likely to be used by other users of the project.

How to submit changes? Copy Chapter

• Check the codebase to ensure that your feature doesn't already exist.
• Check the pull requests to ensure that another person hasn't already submitted the feature or fix.
• Use the pull request template.

How to report a bug? Copy Chapter

• Attempt to replicate the problem, to ensure that it wasn't a coincidental incident.
• Check to make sure your bug report isn't already present within the project.
• Check the pull requests tab to ensure that the bug doesn't have a fix in progress.
• Check the pull requests tab to ensure that the feature isn't already in progress.
• Use the issue template.

Requirements Copy Chapter

• PSR-2 Coding Standard. - Style will get automatically fixed by StyleCI.
• Add tests backing up your change! - You can run the test by running vendor/bin/phpunit
• Document any change in behaviour - Documentation is located in the https://github.com/SpartnerNL/laravel-excel-docs
• One feature per Pull Request
• Add meaningful commit messages

File: 3.0/getting-started/index.md Copy Chapter

Introduction

:rocket: Laravel Excel is intended at being Laravel-flavoured PhpSpreadsheet: a simple, but elegant wrapper around PhpSpreadsheet with the goal of simplifying
exports.

:fire: PhpSpreadsheet is a library written in pure PHP and providing a set of classes that allow you to read from and to write to different spreadsheet file formats, like Excel and LibreOffice Calc.

Laravel Excel features:

• Easily export collections to Excel
• Export queries with automatic chunking for better performance
• Queue exports for better performance
• Easily export Blade views to Excel

File: 3.0/getting-started/installation.md Copy Chapter

Installation

Requirements Copy Chapter

• PHP: ^7.0
• Laravel: ^5.5
• PhpSpreadsheet: ^1.2
• PHP extension php_zip enabled
• PHP extension php_xml enabled
• PHP extension php_gd2 enabled

Installation Copy Chapter

Require this package in the composer.json of your Laravel project. This will download the package and PhpSpreadsheet.

composer require maatwebsite/excel

The Maatwebsite\Excel\ExcelServiceProvider is auto-discovered and registered by default, but if you want to register it yourself:

Add the ServiceProvider in config/app.php:

'providers' => [
    /*
     * Package Service Providers...
     */
    Maatwebsite\Excel\ExcelServiceProvider::class,
]

The Excel facade is also auto-discovered, but if you want to add it manually:

Add the Facade in config/app.php:

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

To publish the config, run the vendor publish command:

php artisan vendor:publish

This will create a new config file named config/excel.php.

Usage Copy Chapter

Laravel Excel can be used in a various of ways. I'm sure you will find your prefered of using it. This can be either via dependency injection or if you prefer you can even use a facade.

Via dependency injection Copy Section

You can inject the Excel manager class into your class, either via constructor injection or method injection in case of a controller.

use App\YourExport;
use Maatwebsite\Excel\Excel;

class YourController
{
    private $excel;

    public function __construct(Excel $excel)
    {
        $this->excel = $excel;
    }
    
    public function export()
    {
        return $this->excel->download(new YourExport);
    }
}

Via Exporter interface Copy Section

You can also use the Maatwebsite\Excel\Exporter to decouple more from the concrete Excel manager implementation.

use App\YourExport;
use Maatwebsite\Excel\Exporter;

class YourController
{
    private $exporter;

    public function __construct(Exporter $exporter)
    {
        $this->exporter = $exporter;
    }
    
    public function export()
    {
        return $this->exporter->download(new YourExport);
    }
}

Via the Facade Copy Section

If you prefer facades, you can you use the Maatwebsite\Excel\Facades\Excel facade or if you use the alias the Excel facade alias.

use App\YourExport;
use Maatwebsite\Excel\Facades\Excel;

class YourController
{
    public function export()
    {
        return Excel::download(new YourExport);
    }
}

Via container binding Copy Section

If you want to bind the Excel manager to your own class via a container binding, you can use the excel container binding.

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

File: 3.0/getting-started/license.md Copy Chapter

License

Our software is open source and licensed under the MIT license. According to the postcardware concept, if you use the software for your project(s) we would appreciate to receive a postcard of your hometown. Please send it to:

Spartner
Florijnruwe 111-2
6218 CA Maastricht
The Netherlands

You are free to use the software as you like. The code can be forked and modified, but the original copyright author should always be included!

::: warning Support
We hold no liability and will provide support on a best effort basis. For more information about support please see support.
:::

:rocket: If you use the software commercially and need support urgently, we can offer this on a commercial basis. Please contact or phone +31 (0)10 744 9312.

File: 3.0/getting-started/support.md Copy Chapter

Support

[[toc]]

Our software is free and open source, meaning that the use of our software is optional. We hold no liability and there is no obligation to support. We will provide support on a best effort basis.

If you use the software commercially and need elaborate support or need it urgently, we can offer this on a commercial basis. Please contact or via phone +31 (0)10 744 9312.

Supported Versions Copy Chapter

Versions will be supported for a limited amount of time.

::: warning PHP version support
Support for PHP versions will only be maintained for a period of six months beyond the end-of-life of that PHP version.
:::

Requesting support Copy Chapter

Before you request support, please check the following:

• Attempt to replicate the problem, to ensure that it wasn't a coincidental incident.
• Check to make sure your support request isn't already present within the project.
• Check the pull requests tab to ensure that the bug doesn't have a fix in progress.
• Use the issue template.

Filling out the template is required. Issues that do not include enough information might not be picked up. Please write in English (or Dutch).

Please prefix your issue with one of the following: [BUG] [PROPOSAL] [QUESTION].

And please be considerate towards maintainers when raising issues.

File: 3.0/getting-started/upgrade.md Copy Chapter

Upgrade Guide

[[toc]]

Upgrading to 3.0 from 2.1 Copy Chapter

Version 3.0 is not backwards compatible with 2.*. It's not possible to provide a step-by-step migration guide as it's a complete paradigm shift.

New dependencies

3.0 introduces some new dependencies.

• Requires PHP 7.0 or higher.
• Requires Laravel 5.5 (or higher).
• Requires PhpSpreadsheet instead of PHPExcel.

Deprecations

ALL Laravel Excel 2.* methods are deprecated and will not be able to use in 3.0 .

• Excel::load() is removed and will not be re-added until 3.1
• Excel::create() is removed and replaced by Excel::download/Excel::store($yourExport)
• 3.0 provides no convenience methods for styling, you are encouraged to use PhpSpreadsheets native methods.

File: 3.1/index.md Copy Chapter

File: 3.1/architecture/concerns.md Copy Chapter

Concerns

[[toc]]

Most of the export/import configuration is done by using Concerns.

Contracts Copy Chapter

Concerns are basically just simple interfaces. Implementing them will make the object adhere to a
certain contract. This contract can request specific methods that e.g. data can be passed through.

For instance, the FromCollection requests the Export object to implement a collection method, that needs to return a Collection instance.

namespace App\Exports;

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

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

Pointer interface Copy Chapter

In other cases it might not ask for any methods to be implemented, but merely functions as a pointer interface.

For instance, the ShouldAutoSize concern doesn't pass on any specific information, but does tell the Export process that the columns need to be automatically sized.

namespace App\Exports;

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

class UsersExport implements ShouldAutoSize
{

}

File: 3.1/architecture/index.md Copy Chapter

Lifecycle

[[toc]]

Introduction Copy Chapter

When using a package in your application, it's good to understand how the package functions behind the scenes.
Understanding the behind-the-scenes will make you feel more comfortable and confident using the maximum potential of the tool.

The goal of this page is to give you a high-level overview of how Laravel Excel works.

Exports Lifecycle Overview Copy Chapter

This section will try to give you an overview of how the export works behind the scenes.

Export Object Copy Section

Everything starts with the Export object. This object encapsulates your entire export logic.
It both configures and handles the export logic.

A simple example of an export object is:

<?php

namespace App\Exports;

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

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

If you want to read more about export objects, go to the architecture page of export objects.

Passing on the Export object Copy Section

Next the Export object will be passed on to the Laravel Excel package. The main entry point for this is the Maatwebsite/Excel/Excel class. This class can be called in multiple ways.

Facade

The easiest way to work with the Excel class is to use the Maatwebsite\Excel\Facades\Excel facade.
If you use auto-discovery, you can use the alias \Excel directly instead of using the fully qualified namespace.

Dependency injection

You can inject the Maatwebsite/Excel/Excel manager class into your class, either via constructor injection
or method injection in case of a controller.

<?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');
    }
}

Contract

You can also use the Maatwebsite\Excel\Exporter interface to decouple more from the concrete Excel manager implementation. The contract offers the same methods as the Excel class.
It will make it easier to e.g. stub out the Exporter in your unit tests. The Exporter contract can be either injected via the constructor or a method in a controller.

use App\Exports\UsersExport;
use Maatwebsite\Excel\Exporter;

class ExportsController
{
    private $exporter;

    public function __construct(Exporter $exporter)
    {
        $this->exporter = $exporter;
    }
    
    public function export()
    {
        return $this->exporter->download(new UsersExport, 'users.xlsx');
    }
}

Container binding

If you want to bind the Maatwebsite\Excel\Excel manager to your own class via a container binding, you can use the excel container binding.

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

Exportable trait

If you prefer a sprinkle of magic, you can use the Maatwebsite\Excel\Concerns\Exportable trait in your Export object. This trait will expose a couple of methods that will make it possible to directly export an Export object.

namespace App\Exports;

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

class UsersExport implements FromCollection
{
    use Exportable;

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

You can now download the export without the need for the facade or Excel manager.

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

Read more about the exportable trait in the exportables docs.

Handling the Export object Copy Section

Writer type detection

After using one of the above methods to pass on the Export object to the Excel manager, it will try to figure out what export it needs to be generated to.
This will be either based on the extension of the file, the explicitly passed writer type. You can find the extension to writer type mapping in the excel.php config, in the extension_detector section.
In case no writer type can be detected, a Maatwebsite\Excel\Exceptions\NoTypeDetectedException exception is thrown and the export process will be stopped.

Starting the Writing process

The Excel manager will then delegate the handling to the Maatwebsite\Excel\Writer. The first action of the Writer is to register the event listeners that are registered.
Next it will create a new PhpOffice\PhpSpreadsheet\Spreadsheet instance that we will use to convert our Export object to.

The first event raised is the BeforeExport event. This is raised just after the Spreadsheet instance is created and allows early access to it.

Multiple sheets

Next the Writer will determine if multiple sheets are configured, by checking for the Maatwebsite\Excel\Concerns\WithMultipleSheets concern.

Then it will delegate the further handling of each sheet to the Maatwebsite\Excel\Sheet class.

Processing the sheets

In the Sheet class, the most heavy lifting happens. It first will create a PhpOffice\PhpSpreadsheet\Worksheet\Worksheet instance. Then it will raise the BeforeSheet event which allows you to hook into the moment just before the sheet handling starts.

Then it will determine what kind of export we are dealing with: FromQuery, FromArray, FromCollection or FromView. Based on that it will start the connected export process.

• FromView will pass on the rendered Blade view to PhpSpreadsheet's Html Reader. That Reader will turn the table html into Excel cells. It also handles some inline styles (color and background color) and col/rowspans.
• The Query passed with the FromQuery will automatically be chunked and each chunk will be appended to the Sheet. The chunking is done to limit the amount of Eloquent objects it needs to keep in memory. It greatly reduces memory usage.
• The entire array of Collection will directly be appended to the Sheet.

When the Sheet starts appending records, it will first call the map() method if the WithMapping concern is used. This allows the Export object to format the data before it is inserted.

Then it will handle column formatting (WithColumnFormatting concern) and cell autosizing (ShouldAutoSize).

To close off the Sheet processing, it will raise a AfterSheet event.

Passing on to PhpSpreadsheet Copy Section

After the sheets are processed, the writing process will start. The writing process is started by raising the BeforeWriting event; this allows you to hook into the process of writing.
Next we will create a new PhpSpreadsheet Writer based on the writer type that was determined. Then it will save it to a temporary file and return that filepath to the Excel manager.

Creating a Response Copy Section

The Excel manager basically has 2 types of responses, it either starts the download process or it will store the file to disk.

Download the file

We will take the temporary file that was returned by the Writer and use Laravel's ResponseFactory to create a Symfony\Component\HttpFoundation\BinaryFileResponse. When returning this response in your controller, it will start a download.

Storing the file

The storing of the file will be handled by Laravel's Filesystem. By default the file will be stored on your default disk, but you can also pass a custom disk via the Excel::store() method.

Imports Lifecycle Overview Copy Chapter

This section will try to give you an overview of how the import works behind the scenes.

Import Object Copy Section

Everything starts with the Import object. This object encapsulates your entire import logic.
It both configures and handles the import logic.

A simple example of an import object is:

namespace App\Imports;

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

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

If you want to read more about imports objects, go to the architecture page of imports objects.

Passing on the Import object Copy Section

Next the Import object will be passed on to the Laravel Excel package. The main entry point for this is the Maatwebsite/Excel/Excel class. This class can be called in the same way as outlined in the Export lifecycle.

Contract

You can also use the Maatwebsite\Excel\Importer interface to decouple more from the concrete Excel manager implementation. The contract offers the same methods as the Excel class.
It will make it easier to e.g. stub out the Importer in your unit tests. The Importer contract can be either injected via the constructor or the method of a controller.

use App\Imports\UsersImport;
use Maatwebsite\Excel\Importer;

class ImportsController
{
    private $importer;

    public function __construct(Importer $importer)
    {
        $this->importer = $importer;
    }
    
    public function import()
    {
        return $this->importer->import(new UsersImport, 'users.xlsx');
    }
}

Importable trait

If you prefer a sprinkle of magic, you can use the Maatwebsite\Excel\Concerns\Importable trait in your Import object. This trait will expose a couple of methods that will make it possible to directly import an Import object.

namespace App\Imports;

use App\User;
use Maatwebsite\Excel\Concerns\ToCollection;
use Maatwebsite\Excel\Concerns\Importable;

class UsersImport implements ToCollection
{
    use Importable;

    ...
}

You can now import without the need for the facade or Excel manager.

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

Read more about the importable trait in the importables docs.

Handling the Import object Copy Section

Reader type detection

After using one of the above methods to pass on the Import object to the Excel manager, it will try to figure out what reader type it is .
This will be either based on the extension of the file or the explicitly passed reader type. You can find the extension to reader type mapping in the excel.php config, in the extension_detector section.
In case no reader type can be detected, a Maatwebsite\Excel\Exceptions\NoTypeDetectedException exception is thrown and the import process will be stopped.

Starting the Reading process

The Excel manager will then delegate the handling to the Maatwebsite\Excel\Reader. The first action of the Reader is to register the event listeners that are registered.
It will copy the file from Laravel's Filesystem to the local filesystem, so PhpSpreadsheet can read it.
Next it will create a PhpSpreadsheet Reader based on the reader type that was given and load the file into a PhpOffice\PhpSpreadsheet\Spreadsheet instance.

Next it will create a new PhpOffice\PhpSpreadsheet\Spreadsheet instance that we will use to read our Import object from.

The first event that is raised, is the BeforeImport event. This is raised just after the Spreadsheet instance is loaded and allows early access to it.

Multiple sheets

Next we will determine if we are dealing with multiple sheets. This is done based on the WithMultipleSheets concern.

Processing the sheets

Then each Sheet gets processed. This process gets started off by raising the BeforeSheet event.
Then it will either import it to a Collection, an array or handle each row as an Eloquent model.

• When using ToModel, each returned model will be persisted via Eloquent. When using this in combination with WithBatchInserts, it will defer the persistence till the batch is complete and then insert them as one batch in the database.
• When using ToCollection or ToArray, the entire dataset will be passed to the Import method and the user can determine itself how to use it.

The sheet handling is ended by raising the AfterSheet event.

File: 3.1/architecture/objects.md Copy Chapter

Import & Export Objects

[[toc]]

The entire Laravel Excel philosophy evolves around having Export and/or Import objects.

Directory structure Copy Chapter

The location of these Import and Exports classes could be as follows:

::: vue
.
├── app
│   ├── Exports (Groups all exports in your app)
│   │   ├── UsersExport.php
│   │   ├── ProductsExport.php
│   │   └── Sheets (You can group sheets together)
│   │   ├── InactiveUsersSheet.php
│   │   └── ActiveUsersSheet.php
| |
│   ├── Imports (Groups all imports in your app)
│   │   ├── UsersImport.php
│   │   ├── ProductsImport.php
│   │   └── Sheets (You can group sheets together)
│   │   ├── OutOfStockProductsSheet.php
│   │   └── ProductsOnSaleSheet.php
│ 
└── composer.json
:::

Encapsulation Copy Chapter

These objects encapsulate the entire export or import process.
The idea behind it is that you can neatly use these objects in controllers, services, jobs, event listeners or commands.
In all of theses cases, the entire logic of how the export/import needs to happen is kept within this object.

Data transfer object Copy Chapter

The import/export objects are in essence simple data transfer objects (DTO).
This means they will transfer the information you want to export/import to the Excel writer/reader.
This information is not only the actual data you want to export, but also additional information like
the styling of the file, the name of the worksheet, the number format of the cell etc.

In most cases, this means that your code doesn't need to have direct exposure to the actual read/write process.

Plain Old PHP Object Copy Chapter

Besides being a DTO, the import/export objects are also just Plain Old PHP Objects (POPO). This means that you can do anything with them that you can do with normal PHP objects.

Constructor Copy Section

For instance, you can simply inject data through the constructor of the export object.

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

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

Setters Copy Section

Another option is to add setters for data that you want to pass.

class UsersExport implements FromCollection {
    private $year;

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

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

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

Getters Copy Section

In similar fashion to setters, you can also add getters. This can potentially be useful if you want to keep a state of your export/import.

For instance, you can keep a row count in your users import.

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

After doing the import, we can request the state with the getter.

$import = new UsersImport;
Excel::import($import, 'users.xlsx');

dd('Row count: ' . $import->getRowCount());

Concerns Copy Chapter

Most of the export/import configuration is done by using Concerns. Concerns are basically just simple interfaces.
Implementing them will make the object adhere to a certain contract. This contract can request specific methods that e.g. data can be passed through.
In other cases it might not ask for any methods to be implemented, but merely functions as a pointer interface.

Read more about Concerns in the concerns documentation.

Hooks Copy Chapter

In more complex cases you might want to hook into certain moments of the read/write process. This can be done by using Events.
To register these events in your import/export object, you need to implement the Maatwebsite\Excel\Concerns\WithEvents concern.

namespace App\Exports;

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

class UsersExport implements WithEvents
{
    /**
     * @return array
     */
    public function registerEvents(): array
    {
        return [
            // Handle by a closure.
            BeforeExport::class => function(BeforeExport $event) {
                // Do something before the export process starts.
            },
        ];
    }
}

Alternatively, you can also configure these listeners globally if you want it to happen to any export.

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

File: 3.1/exports/charts.md Copy Chapter

Charts

[[toc]]

By using the WithCharts concern, you can add one or multiple charts to your worksheet.

Instantiating a chart Copy Chapter

You first have to instantiate a new PhpOffice\PhpSpreadsheet\Worksheet\Chart, passing the name, title, legend and plot in the constructor.

$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');

You can view all available properties for Charts on the PhpSpreadsheet docs.

Adding a single chart Copy Chapter

When you've instantiated the chart, you can add the WithCharts concern to your export class and return the Chart instance in the charts method.

<?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;
    }
}

Adding multiple charts Copy Chapter

You can add multiple charts to the worksheet by returning an array in the charts method.

<?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];
    }
}

File: 3.1/exports/collection.md Copy Chapter

Exporting collections

[[toc]]

The easiest way to start an export is to create a custom export class. We'll use an invoices export as example.

Create a new class called InvoicesExport in app/Exports:

namespace App\Exports;

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

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

In your controller we can now download this export:

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

Optionally you can pass in whether or not to output headers and custom response headers:

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

Or store it on a disk, (e.g. s3):

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

:bulb: More about storing exports can be found in the storing exports on disk page.

:::tip
If you want to use relationships in Collection, combine with Mapping Data
:::

Using custom structures Copy Chapter

If you are not using Eloquent or having another datasource (e.g. an API, MongoDB, Cache, ...) you can also return a custom collection:

namespace App\Exports;

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

class InvoicesExport implements FromCollection
{
    public function collection()
    {
        return new Collection([
            [1, 2, 3],
            [4, 5, 6]
        ]);
    }
}

Using arrays Copy Chapter

If you prefer to use plain arrays over Collections, you can use the FromArray concern:

namespace App\Exports;

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

class InvoicesExport implements FromArray
{
    public function array(): array
    {
        return [
            [1, 2, 3],
            [4, 5, 6]
        ];
    }
}

If you need to pass data from the controller to your export, you can use the constructor to do so:

namespace App\Exports;

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

class InvoicesExport implements FromArray
{
    protected $invoices;

    public function __construct(array $invoices)
    {
        $this->invoices = $invoices;
    }

    public function array(): array
    {
        return $this->invoices;
    }
}

In your controller you can now use the constructor of the export class:

public function export() 
{
    $export = new InvoicesExport([
        [1, 2, 3],
        [4, 5, 6]
    ]);

    return Excel::download($export, 'invoices.xlsx');
}

Dependency injection Copy Chapter

In case your export needs dependencies, you can inject the export class:

namespace App\Exports;

use Maatwebsite\Excel\Concerns\FromCollection;

class InvoicesExport implements FromCollection
{
    protected $invoices;

    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 comparisons Copy Chapter

If you want your 0 values to be actual 0 values in your Excel sheet instead of null (empty cells), you can use WithStrictNullComparison.

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

Custom start cell Copy Chapter

The default start cell is A1. Implementing the WithCustomStartCell concern in your export class allows you to specify a custom start cell.

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';
    }
}

:::warning
WithCustomStartCell is only supported for FromCollection exports.
:::

Storing raw contents Copy Chapter

If you want to receive the raw contents of the exported file, you can use the raw() method:

$contents = Excel::raw(new InvoicesExport, \Maatwebsite\Excel\Excel::XLSX);

Collection macros Copy Chapter

The package provides some macro to Laravel's collection class to easily download or store a collection.

Downloading a collection as Excel Copy Section

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

It doesn't have to be an Eloquent collection to work:

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

Storing a collection on disk Copy Section

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

File: 3.1/exports/column-formatting.md Copy Chapter

Customizing columns

[[toc]]

Formatting columns Copy Chapter

You can easily format an entire column, by using WithColumnFormatting.
In case you want something more custom, it's suggested to use the AfterSheet event to directly interact with the underlying Worksheet class.

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
        ];
    }
    
    public function columnFormats(): array
    {
        return [
            'B' => NumberFormat::FORMAT_DATE_DDMMYYYY,
            'C' => NumberFormat::FORMAT_CURRENCY_EUR_INTEGER,
        ];
    }
}

Dates Copy Section

When working with dates, it's recommended to use \PhpOffice\PhpSpreadsheet\Shared\Date::dateTimeToExcel() in your mapping
to ensure correct parsing of dates.

Value binders Copy Section

By default Laravel Excel uses PhpSpreadsheet's default value binder to intelligently format a cell's value when reading it. You may override this behavior by implementing the WithCustomValueBinder concern and the bindValue method. Your export class may also extend DefaultValueBinder to return the default behavior.

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);
    }
}

Available DataTypes

• PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING
• PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_FORMULA
• PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_NUMERIC
• PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_BOOL
• PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_NULL
• PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_INLINE
• PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_ERROR

Disable intelligent formatting Copy Section

If you want to disable the intelligent formatting of values, you can extend your export class with \PhpOffice\PhpSpreadsheet\Cell\StringValueBinder. In this case all values are passed on as strings.

namespace App\Exports;

use Maatwebsite\Excel\Concerns\WithCustomValueBinder;

class UsersExport extends \PhpOffice\PhpSpreadsheet\Cell\StringValueBinder implements WithCustomValueBinder
{

}

Default Value Binder Copy Section

If you want to use one value binder for all your exports, you can configure the default value binder in the config.

In config/excel.php:

'value_binder' => [
    'default' => Maatwebsite\Excel\DefaultValueBinder::class,
],

Auto size Copy Chapter

If you want Laravel Excel to perform an automatic width calculation, you need to implement the ShouldAutoSize interface, like the following code.

namespace App\Exports;

use Maatwebsite\Excel\Concerns\ShouldAutoSize;

class InvoicesExport implements ShouldAutoSize
{
    ...
}

Column widths Copy Chapter

In some cases you might want more control over the actual column width instead of relying on autosizing. You can do so with the WithColumnWidths concerns. It accepts an array of columns (alphabetic representation: A, B, C) and a numeric width.

namespace App\Exports;

use Maatwebsite\Excel\Concerns\WithColumnWidths;

class InvoicesExport implements WithColumnWidths
{
    public function columnWidths(): array
    {
        return [
            'A' => 55,
            'B' => 45,            
        ];
    }
}

Can be used together with ShouldAutoSize. Only the columns with explicit widths won't be autosized.

Styling Copy Chapter

The WithStyles (available after v3.1.21) concerns allows styling columns, cells and rows. This might be useful when you want to make the heading row bold.

namespace App\Exports;

use Maatwebsite\Excel\Concerns\WithStyles;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;

class InvoicesExport implements WithStyles
{
    public function styles(Worksheet $sheet)
    {
        return [
            // Style the first row as bold text.
            1    => ['font' => ['bold' => true]],

            // Styling a specific cell by coordinate.
            'B2' => ['font' => ['italic' => true]],

            // Styling an entire column.
            'C'  => ['font' => ['size' => 16]],
        ];
    }
}

For the contents of the styles array, please refer to the PhpSpreadsheet docs

If you prefer the fluent syntax for styling cells, you can do it as follows:

namespace App\Exports;

use Maatwebsite\Excel\Concerns\WithStyles;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;

class InvoicesExport implements WithStyles
{
    public function styles(Worksheet $sheet)
    {
        $sheet->getStyle('B2')->getFont()->setBold(true);
    }
}

Default styles Copy Chapter

The WithDefaultStyles (available after v3.1.40) concerns allows styling the entire workbook.

namespace App\Exports;

use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Style;
use PhpOffice\PhpSpreadsheet\Style\Color;
use Maatwebsite\Excel\Concerns\WithDefaultStyles;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;

class InvoicesExport implements WithDefaultStyles
{
    public function defaultStyles(Style $defaultStyle)
    {
        // Configure the default styles
        return $defaultStyle->getFill()->setFillType(Fill::FILL_SOLID);
    
        // Or return the styles array
        return [
            'fill' => [
                'fillType'   => Fill::FILL_SOLID,
                'startColor' => ['argb' => Color::RED],
            ],
        ];
    }
}

Workbook background color Copy Chapter

The WithBackgroundColor (available after v3.1.40) concerns adds support to configure the background color of the entire workbook

namespace App\Exports;

use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Style;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use Maatwebsite\Excel\Concerns\WithBackgroundColor;

class InvoicesExport implements WithBackgroundColor
{
    public function backgroundColor()
    {
        // Return RGB color code.
        return '000000';
    
        // Return a Color instance. The fill type will automatically be set to "solid"
        return new Color(Color::COLOR_BLUE);
    
        // Or return the styles array
        return [
             'fillType'   => Fill::FILL_GRADIENT_LINEAR,
             'startColor' => ['argb' => Color::COLOR_RED],
        ];
    }
}

Full Styling Map Copy Chapter

The full styling map can be taken from the PhpSpreadsheet Styles class as follows:

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

File: 3.1/exports/concerns.md Copy Chapter

pageClass: no-toc Copy Chapter

Export concerns

Traits Copy Section

File: 3.1/exports/drawings.md Copy Chapter

Drawings

[[toc]]

By using the WithDrawings concern, you can add one or multiple drawings to your worksheet.

Instantiating a drawing Copy Chapter

You first have to instantiate a new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing, and assign its properties a meaningful value.

$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);

You can view all available properties for Drawing on the PhpSpreadsheet docs.

Adding a single drawing Copy Chapter

When you've instantiated the drawing, you can add the WithDrawings concern to your export class. Return the Drawing instance in the drawings method.

<?php

namespace App\Exports;

use Maatwebsite\Excel\Concerns\WithDrawings;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;

class InvoicesExport implements WithDrawings
{
    public function drawings()
    {
        $drawing = new Drawing();
        $drawing->setName('Logo');
        $drawing->setDescription('This is my logo');
        $drawing->setPath(public_path('/img/logo.jpg'));
        $drawing->setHeight(90);
        $drawing->setCoordinates('B3');

        return $drawing;
    }
}

Adding multiple drawings Copy Chapter

You can add multiple drawings to the worksheet by returning an array in the drawings method.

<?php

namespace App\Exports;

use Maatwebsite\Excel\Concerns\WithDrawings;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;

class InvoicesExport implements WithDrawings
{
    public function drawings()
    {
        $drawing = new Drawing();
        $drawing->setName('Logo');
        $drawing->setDescription('This is my logo');
        $drawing->setPath(public_path('/img/logo.jpg'));
        $drawing->setHeight(50);
        $drawing->setCoordinates('B3');

        $drawing2 = new Drawing();
        $drawing2->setName('Other image');
        $drawing2->setDescription('This is a second image');
        $drawing2->setPath(public_path('/img/other.jpg'));
        $drawing2->setHeight(120);
        $drawing2->setCoordinates('G2');

        return [$drawing, $drawing2];
    }
}

Adding a drawing of remote image Copy Chapter

You can instantiate a new drawing from \PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing, and create a string containing the binary image data, or from an external url by imagecreatefromstring(file_get_contents($url)), then assign it to setImageResource. Return the Drawing instance in the drawings method.

<?php

namespace App\Exports;

use Maatwebsite\Excel\Concerns\WithDrawings;
use PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing;

class InvoicesExport implements WithDrawings
{
    public function drawings()
    {
        if (!$imageResource = @imagecreatefromstring(file_get_contents('http://example.jpg'))) {
            throw new \Exception('The image URL cannot be converted into an image resource.');
        }

        $drawing = new MemoryDrawing();
        $drawing->setName('Logo');
        $drawing->setDescription('This is my logo');
        $drawing->setImageResource($imageResource);
        $drawing->setHeight(90);
        $drawing->setCoordinates('B3');

        return $drawing;
    }
}

File: 3.1/exports/export-formats.md Copy Chapter

Export formats

[[toc]]

By default, the export format is determined by the extension of the file. If you want
to explicitly configure the export format, you can pass it through as 2nd parameter.

XLSX Copy Chapter

return Excel::download(new InvoicesExport, 'invoices.xlsx', \Maatwebsite\Excel\Excel::XLSX);

CSV Copy Chapter

return Excel::download(new InvoicesExport, 'invoices.csv', \Maatwebsite\Excel\Excel::CSV);

By default the CSV is download with Content Type text/plain, if you want to customize the Content-Type header, you can do so by passing it as 3rd parameter.

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

:::tip Laravel CSV
You may have a look at our Laravel CSV package as well.
:::

TSV Copy Chapter

return Excel::download(new InvoicesExport, 'invoices.tsv', \Maatwebsite\Excel\Excel::TSV);

ODS Copy Chapter

return Excel::download(new InvoicesExport, 'invoices.ods', \Maatwebsite\Excel\Excel::ODS);

XLS Copy Chapter

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

HTML Copy Chapter

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

::: warning Exporting to PDF
If you'd like to export to PDF, you must now install a PDF rendering library yourself. Please refer to the PhpSpreadsheet Documentation for more information.
:::

MPDF Copy Chapter

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

DOMPDF Copy Chapter

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

TCPDF Copy Chapter

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

File: 3.1/exports/exportables.md Copy Chapter

Exportables

[[toc]]

In the previous example, we used the Excel::download facade to start an export.

Laravel Excel also provides a Maatwebsite\Excel\Concerns\Exportable trait, to make export classes exportable.

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

We can now download the export without the need for the facade:

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

You can also pass the Writer Type and optional headers to the download method:

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

Or store it on a disk:

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

You can also pass options to the disk if you like:

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

Responsable Copy Chapter

The previous (download) example can be made even shorter when adding Laravel's Responsable interface to the export class:

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

You can now easily return the export class, without the need of calling ->download().

return new InvoicesExport();

File: 3.1/exports/extending.md Copy Chapter

Extending

[[toc]]

Events Copy Chapter

The export process has a few events you can leverage to interact with the underlying
classes to add custom behaviour to the export.

You are able to hook into the parent package by using events.
No need to use convenience methods like "query" or "view", if you need full control over the export.

The events will be activated by adding the WithEvents concern. Inside the registerEvents method, you
will have to return an array of events. The key is the Fully Qualified Name (FQN) of the event and the value is a callable event listener.
This can either be a closure, array-callable or 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) 
    {
        //
    }
}

Do note that using a Closure will not be possible in combination with queued exports, as PHP cannot serialize the closure.
In those cases it might be better to use the RegistersEventListeners trait.

Auto register event listeners Copy Section

By using the RegistersEventListeners trait you can auto-register the event listeners,
without the need of using the registerEvents. The listener will only be registered if the method is 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)
    {
        //
    }
}

Global event listeners Copy Section

Event listeners can also be configured globally, if you want to perform the same actions on all exports in your app.
You can add them to e.g. your AppServiceProvider in the register() method.

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

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

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

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

Available events Copy Section

Custom Concerns Copy Chapter

You can add custom concerns to your app. This can be useful if you want to share some concerns over a few projects or want to open source your custom concerns.

Let's add a WithCustomProperties concern to your app. You could add these concerns to App/Exports/Concerns, but any location will do as long as it can be autoloaded by Composer.

namespace App\Exports\Concerns;

interface WithCustomProperties
{
    /**
     *
     * @return string
     */
    public function description(): string;
}

Next to this concern we will create a concern handler WithCustomPropertiesHandler. This class can also be added to App/Exports/Concerns, but is again completely free of choice.
A concern handler is basically just an invokable class. It receives your exportable object and either a Writer or Sheet object, depending on the chosen event.

namespace App\Exports\Concerns;

use App\Exports\Concerns\WithCustomProperties;
use Maatwebsite\Excel\Writer;

class WithCustomPropertiesHandler
{
    /**
     * @param WithCustomProperties $exportable
     * @param Writer               $writer
     */
    public function __invoke(WithCustomProperties $exportable, Writer $writer)
    {
        $writer
            ->getDelegate()
            ->getProperties()
            ->setDescription(
                $exportable->description()
            );
    }
}

We will then register this concern in a service provider. You could use App/Providers/AppServiceProvider for this.

public function register()
{
    Excel::extend(WithCustomProperties::class, new WithCustomPropertiesHandler);
}

:bulb: ::extend accepts a callable as second parameter. It's also possible to pass a closure.

public function register()
{
    Excel::extend(WithCustomProperties::class, function(WithCustomProperties $exportable, Writer $writer) {
        $writer->getDelegate()->getProperties()->setDescription($exportable->description());
    });
}

You can also bind concern handlers to different hooks. By default a concern handler is always bound to the BeforeWriting event. You can easily customize this, by supplying a 3rd parameter.

public function register()
{
    Excel::extend(WithCustomProperties::class, new WithCustomPropertiesHandler, BeforeExport::class);
}

Macroable Copy Chapter

Both Writer and Sheet are "macroable" which means they can easily be extended to fit your needs.
Both Writer and Sheet have a ->getDelegate() method which returns the underlying PhpSpreadsheet class.
This will allow you to add custom macros as shortcuts to PhpSpreadsheet methods that are not available in this package.

Writer Copy Section

use \Maatwebsite\Excel\Writer;

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

Sheet Copy Section

use \Maatwebsite\Excel\Sheet;

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

Customize Copy Section

You can create your own macro to add custom methods to a spreadsheet instance.

For example, to add styling to a cell, we first create a macro, let's call it styleCells:

use \Maatwebsite\Excel\Sheet;

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

Once the macro is created, it can be used inside the registerEvents() method as such:

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

RTL (Right to Left) Sheets Copy Section

To change the Excel sheet direction you can use setRightToLeft(true) as in the below example

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 [
            AfterSheet::class    => function(AfterSheet $event) {
                $event->sheet->getDelegate()->setRightToLeft(true);
            },
        ];
    }
}

Feel free to use the above macro, or be creative and invent your own!

For PhpSpreadsheet methods, please refer to their documentation.

File: 3.1/exports/from-generator.md Copy Chapter

pageClass: no-toc Copy Chapter

From Generator

Exports can be created from a PHP generator class, by using the FromGenerator concern.

A generator allows you to write code that uses foreach to iterate over a set of data without needing to build an array in memory.

namespace App\Exports;

use Generator;
use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\FromGenerator;

class DataExport implements FromGenerator
{
    use Exportable;

    public function generator(): Generator
    {
        for ($i = 1; $i <= 100; $i++) {
            yield [$i, $i+1, $i+2];
        }
    }
}

You can download the export in your controller:

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

:::tip Using Queue with FromGenerator is not supported. In case a queue is desired, we recommend wrapping the export file in a job, then using ->store :::

File: 3.1/exports/from-query.md Copy Chapter

From Query

[[toc]]

In the previous example, we did the query inside the export class.
While this is a good solution for small exports,
for bigger exports this will come at a hefty performance price.

By using the FromQuery concern, we can prepare a query for an export. Behind the scenes this query is executed in chunks.

In the InvoicesExport class, add the FromQuery concern and return a query. Be sure to not ->get() the results!

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

We can still download the export in the same way:

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

Customizing the query Copy Chapter

It's easy to pass custom parameters to the query,
by simply passing them as dependencies to the export class.

As constructor parameter Copy Section

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);
    }
}

The year can now be passed as dependency to the export class:

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

As setter Copy Section

namespace App\Exports;

use App\Invoice;
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 adjust the year by using the forYear method:

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

Macro's and Mixins Copy Section

The Eloquent Builder/Model has a macro to directly download an Eloquent query to Excel.

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

If you want to include header row, you can pass true as third parameter:

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

Similarly, you can store the results of a query:

User::query()->storeExcel('query-store.xlsx', 'your-disk');

File: 3.1/exports/from-view.md Copy Chapter

pageClass: no-toc Copy Chapter

From View

Exports can be created from a Blade view, by using the FromView concern.

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

It will convert an HTML table into an Excel spreadsheet. For example; invoices.blade.php:

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

Attribute in tag html support:

Style inline support

• background
• background-color
• color
• border|border-top|border-bottom|border-left|border-right
• font-size|font-weight|font-sytle|font-family
• text-decoration: underline|line-through
• text-align
• vertical-align
• width|height
• word-wrap
• text-indent

You can download the export in your controller:

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

File: 3.1/exports/headings.md Copy Chapter

Headings

[[toc]]

Adding a heading row Copy Chapter

A heading row can easily be added by adding the WithHeadings concern. The heading row will be added
as very first row of the sheet.

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

class InvoicesExport implements FromQuery, WithHeadings
{
    public function headings(): array
    {
        return [
            '#',
            'User',
            'Date',
        ];
    }
}

If you need to have multiple heading rows, you can return multiple rows from the headings() method:

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

class InvoicesExport implements FromQuery, WithHeadings
{
    public function headings(): array
    {
        return [
           ['First row', 'First row'],
           ['Second row', 'Second row'],
        ];
    }
}

File: 3.1/exports/index.md Copy Chapter

pageClass: no-toc Copy Chapter

:rocket: 5 minute quick start

[[toc]]

:muscle: Create an export class in app/Exports

You may do this by using the make:export command.

php artisan make:export UsersExport --model=User

The file can be found in app/Exports:

::: vue
.
├── app
│   ├── Exports
│   │   ├── UsersExport.php
│ 
└── composer.json
:::

If you prefer to create the export manually, you can create the following in app/Exports:

<?php

namespace App\Exports;

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

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

:fire: In your controller you can call this export now:

<?php

namespace App\Http\Controllers;

use App\Exports\UsersExport;
use Maatwebsite\Excel\Facades\Excel;

class UsersController extends Controller 
{
    public function export() 
    {
        return Excel::download(new UsersExport, 'users.xlsx');
    }
}

And finally add a route to be able to access the export:

Route::get('users/export/', [UsersController::class, 'export']);

:page_facing_up: Find your users.xlsx in your downloads folder!

File: 3.1/exports/mapping.md Copy Chapter

Mapping data

[[toc]]

Mapping rows Copy Chapter

By adding WithMapping you map the data that needs to be added as row. This way you have control over the actual source for each column.
In case of using the Eloquent query builder:

use Maatwebsite\Excel\Concerns\FromQuery;
use Maatwebsite\Excel\Concerns\WithMapping;
use PhpOffice\PhpSpreadsheet\Shared\Date;

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

Multiple rows Copy Section

You can also return multiple rows inside the map function:

use Maatwebsite\Excel\Concerns\FromQuery;
use Maatwebsite\Excel\Concerns\WithMapping;
use PhpOffice\PhpSpreadsheet\Shared\Date;

class InvoicesExport implements FromQuery, WithMapping
{    
    /**
    * @param Invoice $invoice
    */
    public function map($invoice): array
    {
        // This example will return 3 rows.
        // First row will have 2 column, the next 2 will have 1 column
        return [
            [
                $invoice->invoice_number,
                Date::dateTimeToExcel($invoice->created_at),
            ],
            [
                $invoice->lines->first()->description,
            ],
            [
                $invoice->lines->last()->description,
            ]
        ];
    }
}

Prepare rows Copy Chapter

If you need to prepare rows before appending these rows to sheet, you can add method prepareRows to your export class. This method will be called before flattening the query output and calling map().

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

class UsersExport implements FromQuery, WithHeadings
{   
    public function prepareRows($rows)
    {
        return $rows->transform(function ($user) {
            $user->name .= ' (prepared)';

            return $user;
        });
    }
}

File: 3.1/exports/multiple-sheets.md Copy Chapter

Multiple Sheets

[[toc]]

To allow the export to have multiple sheets, the WithMultipleSheets concern should be used.
The sheets() method expects an array of sheet export objects to be returned.

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

Sheet classes Copy Chapter

The InvoicesPerMonthSheet can implement concerns like FromQuery, FromCollection, FromView, ...

Note: The WithTitle concern is needed in order to name each sheet using the title() method

namespace App\Exports\Sheets;

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

The code below can be implemented in any class in order to download an xlsx of all invoices from the current year, with 12 worksheets representing each month of the year.

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

File: 3.1/exports/queued.md Copy Chapter

Queued

[[toc]]

In case you are working with a lot of data, it might be wise to queue the entire process.

Given we have the following export class:

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

It's as easy as calling ->queue() now.

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

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

Behind the scenes the query will be chunked and multiple jobs will be chained. These jobs will be executed in the correct order,
and will only execute if none of the previous have failed.

Implicit Export queueing Copy Chapter

You can also mark an export implicitly as a queued export. You can do this by using Laravel's ShouldQueue contract.

namespace App\Exports;

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

class InvoicesExport implements FromQuery, ShouldQueue
{
    use Exportable;

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

In your controller you can now call the normal ->store() method.
Based on the presence of the ShouldQueue contract, the export will be queued.

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

Appending jobs Copy Chapter

The queue() method returns an instance of Laravel's PendingDispatch. This means you can chain extra jobs that will be added to the end of the queue and only executed if all export jobs are correctly executed.

(new InvoicesExport)->queue('invoices.xlsx')->chain([
    new NotifyUserOfCompletedExport(request()->user()),
]);

namespace App\Jobs;

use App\User;
use Illuminate\Bus\Queueable;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Queue\SerializesModels;

class NotifyUserOfCompletedExport implements ShouldQueue
{
    use Queueable, SerializesModels;
    
    public $user;
    
    public function __construct(User $user)
    {
        $this->user = $user;
    }

    public function handle()
    {
        $this->user->notify(new ExportReady());
    }
}

Handling failures in queued exports Copy Chapter

When queuing exports you might want a way to handle failed exports. You can do this by adding failed method to your export class.

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

class UsersExport implements FromQuery, WithHeadings
{   
    public function failed(Throwable $exception): void
    {
        // handle failed export
    }
}

Custom queues Copy Chapter

Because PendingDispatch is returned, we can also change the queue that should be used.

For Laravel 8+:

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

For older versions of Laravel:

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

Multi-server setup Copy Chapter

If you are dealing with a multi-server setup (using e.g. a loadbalancer), you might want to make sure the temporary file that is used to store each chunk of data on, is the same for each job. You can achieve this by configuring a remote temporary file in the config.

In config/excel.php

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

Job Middleware Copy Chapter

If you are using Laravel 6, job middleware can be attached to the export class using the middleware method:

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()
    {
        // ...
    }
}

Localizing Queued Export Copy Chapter

If you want to localize your queued export you should implement HasLocalePreference contract on your export:

namespace App\Exports;

use Illuminate\Contracts\Translation\HasLocalePreference;
use Maatwebsite\Excel\Concerns\Exportable;

class ExportClass implements HasLocalePreference
{
    use Exportable;
    
    public function __construct(string $locale)
    {
        $this->locale = $locale;
    }
    
    public function preferredLocale()
    {
        return $this->locale;
    }
}

Custom Query Size Copy Chapter

Queued exportables are processed in chunks; each chunk being a job pushed to the queue by the QueuedWriter.
In case of exportables that implement the FromQuery concern, the number of jobs is calculated by dividing the $query->count() by the chunk size.

When to use Copy Section

Depending on the implementation of the query() method (e.g. when using a groupBy clause), the calculation mentioned before might not be correct.

If this is the case, you should use the WithCustomQuerySize concern to provide a custom calculation of the query size.

File: 3.1/exports/settings.md Copy Chapter

Settings

[[toc]]

Properties Copy Chapter

By default the Worksheet properties get configured in config/excel.php. You can configure a default title, description, creator, etc.

If you want to overrule on a per export basis, you can use the WithProperties concern.

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'        => 'Spartner',
        ];
    }
}

It's not required to return all properties, you can ommit the keys you don't want to overrule.

namespace App\Exports;

use Maatwebsite\Excel\Concerns\WithProperties;

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

Custom CSV Settings Copy Chapter

By default Laravel Excel uses the defaults from the config (config/excel.php). You can change this by adding the WithCustomCsvSettings interface.

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

Available csv settings Copy Section

• delimiter
• enclosure
• line_ending
• use_bom
• include_separator_line
• excel_compatibility
• output_encoding

Cell caching Copy Chapter

By default PhpSpreadsheet keeps all cell values in memory, however when dealing with large files, this might result into memory issues. If you want to mitigate that, you can configure a cell caching driver here.

When using the illuminate driver, it will store each value in the cache store. This can slow down the process, because it needs to store each value. However it will use less memory.
It will automatically use your default cache store. However if you prefer to have the cell cache on a separate store, you can configure the store name here. You can use any store defined in your cache config. When leaving at "null" it will use the default store.

You can use the "batch" store if you want to only persist to the store when the memory limit is reached. You can tweak the memory limit in the config.

Cells written to the cache store expire after cache.default_ttl seconds (set it to null to keep them indefinitely).

::: warning
default_ttl is only applied when the cache is called without a TTL argument. An explicit null TTL stores the cell forever. If you wrap or extend the batch driver (for example with CacheManager::extend()), don't forward a null TTL to it: leave the argument out, or pass $ttl ?? config('excel.cache.default_ttl'). Otherwise, the cells of any export that fails before PhpSpreadsheet releases them stay in the cache store permanently.
:::

File: 3.1/exports/store.md Copy Chapter

Storing exports on disk

[[toc]]

Exports can easily be stored on any filesystem that Laravel supports.

Default disk Copy Chapter

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

Custom disks Copy Chapter

public function storeExcel() 
{
    // 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);
}

Disk options Copy Chapter

If you want to pass some options to the disk, pass them to Excel::store() as the fifth parameter.

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

Laravel has a shortcut for private files:

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

::: warning File names cannot include certain characters:

• < (less than)
• > (greater than)
• : (colon)
• " (double quote)
• / (forward slash)
• \ (backslash)
• | (vertical bar or pipe)
• ? (question mark)
• * (asterisk)
  :::

Note about queuing Copy Chapter

If you are storing the export using Excel::queue() or using the ShouldQueue interface, make sure to have a look at the queuing docs

File: 3.1/exports/testing.md Copy Chapter

Testing

[[toc]]

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

Testing downloads Copy Chapter

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

Testing storing exports Copy Chapter

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

Testing queuing exports Copy Chapter

/**
* @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;
    });
    
    // Assert that the export was queued with a specific chain of other jobs.
    Excel::assertQueuedWithChain([
        new NotifyUsers(),
    ])
}

Testing exports with dynamic file name/path Copy Chapter

If you have dynamic naming for files or paths, you can use a regular expression to represent those while testing:

/**
* @test
*/
public function user_can_store_invoices_export() 
{
   Excel::fake();
   
   $this->actingAs($this->givenUser())
        ->get('/invoices/store/xlsx');
   
   // Tells the mock to use regular expressions
   Excel::matchByRegex(); 
   // For a given dynamic named file 'invoices_2019.xlsx'
   Excel::assertStored('/invoices_\d{4}\.xlsx/', 'diskName');
}

Please note that your expression must match only one file/path. If more than one match is found, the test will fail.

File: 3.1/getting-started/contributing.md Copy Chapter

Contributing

[[toc]]

Please read and understand the contribution guide before creating an issue or pull request.

Important Links Copy Chapter

• Docs
• Issue tracker
• Support

Etiquette Copy Chapter

This project is an open source project, and as such, the maintainers use their free time to build and maintain it.
The code is freely available and can be used, forked and modified.

Please be considerate towards maintainers when raising issues or presenting pull requests.

It's the duty of the maintainer to ensure that all submissions to the project are of sufficient
quality to benefit the project. Many developers have different skill sets, strengths, and weaknesses. Respect the maintainer's decision, and do not be upset or abusive if your submission is not used.

Viability Copy Chapter

When requesting or submitting new features, first consider whether it might be useful to others. Open
source projects are used by many developers, who may have entirely different needs to your own. Think about
whether or not your feature is likely to be used by other users of the project.

How to submit changes? Copy Chapter

• Check the codebase to ensure that your feature doesn't already exist.
• Check the pull requests to ensure that another person hasn't already submitted the feature or fix.
• Use the pull request template.

How to report a bug? Copy Chapter

• Attempt to replicate the problem, to ensure that it wasn't a coincidental incident.
• Check to make sure your bug report isn't already present within the project.
• Check the pull requests tab to ensure that the bug doesn't have a fix in progress.
• Check the pull requests tab to ensure that the feature isn't already in progress.
• Use the issue template.

PR Requirements Copy Chapter

• The PR must apply to PSR-2 Coding Standard. StyleCI help you detect code style issues.
• The PR must be as clean as possible. Try to remove all inline TODOs and unneeded comments. They are noise and distraction for the reviewer. Use Github's (inline) comment system to start a discussion about a change.
• The existing tests must not fail after your changes. This would indicate that existing functionality is broken and should be addressed before a review is started. You can run the test by running vendor/bin/phpunit
• The changes must be backed-up with tests.
• The PR must only have one feature. Split up multiple features in multiple PRs, so they can be individually reviewed and merged.
• The PR must be accompanied by a PR to the docs in case of new features. Documentation is located in the https://github.com/SpartnerNL/laravel-excel-docs repository.
• The PR should contain meaningful commit messages.

File: 3.1/getting-started/index.md Copy Chapter

pageClass: no-toc Copy Chapter

Introduction

:rocket: Laravel Excel is intended at being Laravel-flavoured PhpSpreadsheet: a simple, but elegant wrapper around PhpSpreadsheet with the goal of simplifying
exports and imports.

:fire: PhpSpreadsheet is a library written in pure PHP and providing a set of classes that allow you to read from and to write to different spreadsheet file formats, like Excel and LibreOffice Calc.

Laravel Excel Features Copy Chapter

• Easily export collections to Excel.
• Export queries with automatic chunking for better performance.
• Queue exports for better performance.
• Easily export Blade views to Excel.
• Easily import to collections.
• Read the Excel file in chunks.
• Handle the import inserts in batches.

File: 3.1/getting-started/installation.md Copy Chapter

Installation

[[toc]]

Requirements Copy Chapter

• PHP: ^7.2\|^8.0
• Laravel: ^5.8
• PhpSpreadsheet: ^1.21
• PHP extension php_zip enabled
• PHP extension php_xml enabled
• PHP extension php_gd2 enabled
• PHP extension php_iconv enabled
• PHP extension php_simplexml enabled
• PHP extension php_xmlreader enabled
• PHP extension php_zlib enabled

Installation Copy Chapter

Require this package in the composer.json of your Laravel project. This will download the package and PhpSpreadsheet.

composer require "maatwebsite/excel:^3.1"

If installing your receive the following error

Your requirements could not be resolved to an installable set of packages.

  Problem 1
    - Root composer.json requires maatwebsite/excel 3.1 -> satisfiable by maatwebsite/excel[3.1.0].
    - maatwebsite/excel 3.1.0 requires php ^7.0 -> your php version (8.2.8) does not satisfy that requirement.

You can try simply to install without the caret

composer require maatwebsite/excel

If you don't get the latest version or run into more composer errors, please make sure you have installed all required PHP extensions like zip, gd, etc.

The Maatwebsite\Excel\ExcelServiceProvider is auto-discovered and registered by default.

If you want to register it yourself, add the ServiceProvider in config/app.php:

'providers' => [
    /*
     * Package Service Providers...
     */
    Maatwebsite\Excel\ExcelServiceProvider::class,
]

The Excel facade is also auto-discovered.

If you want to add it manually, add the Facade in config/app.php:

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

To publish the config, run the vendor publish command:

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

This will create a new config file named config/excel.php.

File: 3.1/getting-started/license.md Copy Chapter

License

[[toc]]

MIT Copy Chapter

Our software is open source and licensed under the MIT license. You are free to use the software as you like. The code can be forked and modified, but the original copyright author should always be included!

Postcardware Copy Chapter

According to the postcardware concept, if you use the software for your project(s) we would appreciate to receive a postcard of your hometown. Please send it to:

Spartner
Markt 2
6231 LS Meerssen
The Netherlands

Created by Spartner (formerly Maatwebsite) Copy Chapter

We are a strategic development partner, creating web-based custom built software from Laravel. In need of a digital solution for your challenge? Give us a call.

https://spartner.software
[email protected]
+31 (0) 10 - 7449312

Support Copy Chapter

::: warning Support
We hold no liability and will provide support on a best effort basis. For more information about support please see support.
:::

:::tip Commercial Support
:rocket: If you use the software commercially and need support urgently, we can offer this on a commercial basis. Please contact or phone +31 (0)10 744 9312.
:::

File: 3.1/getting-started/support.md Copy Chapter

Support

[[toc]]

Our software is free and open source, meaning that the use of our software is optional. We hold no liability and there is no obligation to support. We will provide support on a best effort basis.

:::tip Commercial Support
If you use the software commercially and need elaborate support or need it urgently, we can offer this on a commercial basis. Please contact or via phone +31 (0)10 744 9312.
:::

Supported Versions Copy Chapter

Versions will be supported for a limited amount of time.

::: warning PHP version support
Support for PHP versions will only be maintained for a period of six months beyond the end-of-life of that PHP version.
:::

Requesting support Copy Chapter

Before you request support, please check the following:

• Attempt to replicate the problem, to ensure that it wasn't a coincidental incident.
• Check to make sure your support request isn't already present within the project.
• Check the pull requests tab to ensure that the bug doesn't have a fix in progress.
• Use one of the issue templates.

Filling out the template is required. Issues that do not include enough information might not be picked up. Please write in English (or Dutch).

Please prefix your issue with one of the following: [BUG] [PROPOSAL] [QUESTION].

And please be considerate towards maintainers when raising issues.

File: 3.1/getting-started/upgrade.md Copy Chapter

Upgrade Guide

[[toc]]

Upgrading to 3.1 from 3.0 Copy Chapter

Version 3.1 is backwards compatible with 3.0. Only features were added in this release.

Additions

• Imports feature.
• ChunkReading
• BatchInserts
• Queued imports
• ToArray concern for Exports.
• Custom value binders for Imports and Exports.

Removals

• Excel::filter('chunk') method is removed, chunk filter is automatically added when using chunk reading.

Upgrading to 3.* from 2.1 Copy Chapter

Version 3.* is not backwards compatible with 2.*. It's not possible to provide a step-by-step migration guide as it's a complete paradigm shift.

New dependencies

3.* introduces some new dependencies.

• Requires PHP 7.0 or higher.
• Requires Laravel 5.5 (or higher).
• Requires PhpSpreadsheet instead of PHPExcel.

Deprecations

ALL Laravel Excel 2.* methods are deprecated and will not be able to use in 3.0 .

• Excel::load() is removed and replaced by Excel::import($yourImport)
• Excel::create() is removed and replaced by Excel::download/Excel::store($yourExport)
• Excel::create()->string('xlsx') is removed an replaced by Excel::raw($yourExport, Excel::XLSX)
• 3.0 provides no convenience methods for styling, you are encouraged to use PhpSpreadsheets native methods.

You can find an example upgrade for an export here: https://github.com/SpartnerNL/Laravel-Excel/issues/1799

File: 3.1/imports/basics.md Copy Chapter

Importing basics Copy Chapter

[[toc]]

If you have followed the 5 minute quick start, you'll already have a UsersImport class.

<?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]),
        ]);
    }
}

Importing from default disk Copy Chapter

Passing the UsersImport object to the Excel::import() method will tell the package how to import the file that is passed as second parameter.
The file is expected to be located in your default filesystem disk (see config/filesystems.php).

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

Importing from another disk Copy Section

You can specify another disk with the third parameter like your Amazon s3 disk. (see config/filesystems.php)

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

Importing uploaded files Copy Chapter

If you let your user upload the document, you can also just pass the uploaded file directly.

Excel::import(new UsersImport, request()->file('your_file'));

Importing full path Copy Section

If you want to specifiy the path where your file is, without having to move it to a disk, you can directly pass that file path to the import method.

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

Importing to array or collection Copy Chapter

If you want to bypass the ToArray or ToCollection concerns and want to have an array of imported data in your controller (beware of performance!), you can use the ::toArray() or ::toCollection() method.

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

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

Specifying a reader type Copy Chapter

If the reader type is not detectable by the file extension, you can specify a reader type by passing it as fourth parameter.

Excel::import(new UsersImport, 'users.xlsx', 's3', \Maatwebsite\Excel\Excel::XLSX);

File: 3.1/imports/batch-inserts.md Copy Chapter

Batch inserts

[[toc]]

Importing a large file to Eloquent models, might quickly become a bottleneck as every row results into an insert query.

With the WithBatchInserts concern you can limit the amount of queries done by specifying a batch size. This batch size will determine how many models will be inserted into the database in one time. This will drastically reduce the import duration.

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

:::warning ToModel
This concern only works with the ToModel concern.
:::

:::tip Batch Size
A batch size of 1000 will not be the most optimal situation for your import. Play around with this number to find the sweet spot.
:::

Batch upserts Copy Chapter

For batch upserts, you can additionally implement the WithUpserts concern.

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

    public function uniqueBy()
    {
        return 'email';
    }
}

In the example above, if a user already exists with the same email, the row will be updated instead. Behind the scenes, this feature uses the Laravel upsert method and the uniqueBy method is used for the second argument of the upsert method, which lists the column(s) that uniquely identify records within the associated table.

:::warning
All databases except SQL Server require the uniqueBy columns to have a "primary" or "unique" index.
:::

Skipping duplicate rows Copy Chapter

For skipping duplicate models, you can additionally implement the WithSkipDuplicates concern.

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

In the example above, if a user already exists with the same primary or unique key, the row will be ignored. Behind the scenes, this feature uses the Laravel insertOrIgnore method to insert records while ignoring duplicates, preventing any errors that would normally occur due to duplicate entries.

File: 3.1/imports/chunk-reading.md Copy Chapter

Chunk reading

[[toc]]

Importing a large file can have a huge impact on the memory usage, as the library will try to load the entire sheet into memory.

To mitigate this increase in memory usage, you can use the WithChunkReading concern. This will read the spreadsheet in chunks and keep the memory usage under control.

namespace App\Imports;

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

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

::: warning
A chunk size of 1000 will not be the most optimal situation for your import. Play around with this number to find the sweet spot.
:::

Using it together with Batch Inserts Copy Chapter

The most ideal situation (regarding time and memory consumption) you will find when combining batch inserts and chunk reading.

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

Keep Track of the Row number Copy Chapter

If you need the row number you can use the RemembersRowNumber Trait.

namespace App\Imports;

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

class UsersImport implements ToModel, WithChunkReading
{
    use RemembersRowNumber;

    public function model(array $row)
    {
        $currentRowNumber = $this->getRowNumber();

        return new User([
            'name' => $row[0],
        ]);
    }
    
    public function chunkSize(): int
    {
        return 1000;
    }
}

::: warning
Remembering row numbers is only intended for ToModel imports.
:::

If you only need the information about the offset of the chunk you can use the RemembersChunkOffset Trait.

namespace App\Imports;

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

class UsersImport implements ToModel, WithChunkReading
{
    use RemembersChunkOffset;

    public function model(array $row)
    {
        $chunkOffset = $this->getChunkOffset();

        return new User([
            'name' => $row[0],
        ]);
    }
    
    public function chunkSize(): int
    {
        return 1000;
    }
}

File: 3.1/imports/collection.md Copy Chapter

pageClass: no-toc Copy Chapter

Importing to collections

[[toc]]

The easiest way to start an import is to create a custom import class. We'll use a user import as example.

Create a new class called UsersImport in app/Imports:

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

The collection method will receive a collection of rows. A row is an array filled with the cell values.

In case of the file having multiple sheets, the collection() method will be called multiple times.

In your controller we can now import this:

public function import() 
{
    Excel::import(new UsersImport, 'users.xlsx');
}

:::warning
Whatever you return in the collection() method will not be returned to the controller.
:::

File: 3.1/imports/concerns.md Copy Chapter

pageClass: no-toc Copy Chapter

Import concerns

Traits Copy Section

File: 3.1/imports/custom-csv-settings.md Copy Chapter

Custom CSV Settings

[[toc]]

By default Laravel Excel uses the defaults from the config (config/excel.php). You can change this by adding the WithCustomCsvSettings interface.

namespace App\Imports;

use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithCustomCsvSettings;

class UsersImport implements ToModel, WithCustomCsvSettings
{
    public function model(array $row)
    {
        return new User([
            'name' => $row['0'],
            'email' => $row['1']
        ]);
    }
    
    public function getCsvSettings(): array
    {
        return [
            'input_encoding' => 'ISO-8859-1'
        ];
    }
}

A CSV file stores data in rows and the values in each row is separated with a separator, also known as a delimiter. Although the file is defined as Comma Separated Values, the delimiter could be anything. Delimiter requires a single character. For Tab use "\t". The most common delimiters are: a comma ,, a semicolon ;, a tab \t, a space , or a pipe |.

public function getCsvSettings(): array
{
    return [
        'delimiter' => "\t"
    ];
}

Available settings Copy Chapter

• delimiter
• enclosure
• escape_character
• contiguous
• input_encoding

File: 3.1/imports/custom-formatting-values.md Copy Chapter

Custom Formatting Values

[[toc]]

Value Binder Copy Chapter

By default Laravel Excel uses PhpSpreadsheet's default value binder to intelligently format a cell's value when reading it. You may override this behavior by implementing the WithCustomValueBinder concern and the bindValue method. Your import class may also extend DefaultValueBinder to return the default behavior.

namespace App\Imports;

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 UsersImport extends DefaultValueBinder implements WithCustomValueBinder, ToModel
{
    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);
    }
}

Available DataTypes Copy Chapter

• PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING
• PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_FORMULA
• PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_NUMERIC
• PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_BOOL
• PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_NULL
• PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_INLINE
• PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_ERROR

Disable intelligent formatting Copy Chapter

If you want to disable the intelligent formatting of values, you can extend your import class with \PhpOffice\PhpSpreadsheet\Cell\StringValueBinder. In this case all values are passed on as strings.

namespace App\Imports;

use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithCustomValueBinder;

class UsersImport extends \PhpOffice\PhpSpreadsheet\Cell\StringValueBinder implements WithCustomValueBinder, ToModel
{

}

Default Value Binder Copy Chapter

If you want to use one value binder for all your imports, you can configure the default value binder in the config.

In config/excel.php:

'value_binder' => [
    'default' => Maatwebsite\Excel\DefaultValueBinder::class,
],

File: 3.1/imports/extending.md Copy Chapter

Extending

[[toc]]

Events Copy Chapter

The import process has a few events you can leverage to interact with the underlying
classes to add custom behaviour to the import.

You are able to hook into the parent package by using events.

The events will be activated by adding the WithEvents concern. Inside the registerEvents method, you
will have to return an array of events. The key is the Fully Qualified Name (FQN) of the event and the value is a callable event listener.
This can either be a closure, array-callable or invokable class.

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) 
    {
        //
    }
	
}

Do note that using a Closure will not be possible in combination with queued imports, as PHP cannot serialize the closure.
In those cases it might be better to use the RegistersEventListeners trait.

Auto register event listeners Copy Section

By using the RegistersEventListeners trait you can auto-register the event listeners,
without the need of using the registerEvents. The listener will only be registered if the method is created.

namespace App\Imports;

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

class UsersImport implements WithEvents
{
    use Importable, RegistersEventListeners;
    
    public static function beforeImport(BeforeImport $event)
    {
        //
    }
	
    public static function afterImport(AfterImport $event)
    {
        //
    }

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

    // From 3.1.50 onwards the methods do not need to be static, allowing use of $this
    public function afterSheet(AfterSheet $event)
    {
        //
    }
	
}

Global event listeners Copy Section

Event listeners can also be configured globally, if you want to perform the same actions on all exports in your app.
You can add them to e.g. your AppServiceProvider in the register() method.

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

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

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

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

Available events Copy Section

Macroable Copy Chapter

Both Reader and Sheet are "macroable" (which means they can easily be extended to fit your needs).
Both Reader and Sheet have a ->getDelegate() method which returns the underlying PhpSpreadsheet class.
This will allow you to add custom macros as shortcuts to PhpSpreadsheet methods that are not available in this package.

Reader Copy Section

use \Maatwebsite\Excel\Reader;

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