### Install Laravel Nova Excel Package via Composer Source: https://docs.laravel-excel.com/nova/1.x/exports This command installs the Maatwebsite Laravel Nova Excel package using Composer, which enables Excel export functionality in Laravel Nova. It requires a Laravel project with Composer installed. The output is the package added to composer.json and vendor directory, with no specific file outputs. ```bash composer require maatwebsite/laravel-nova-excel ``` -------------------------------- ### Install Laravel Excel Package via Composer (Shell) Source: https://docs.laravel-excel.com/3.1/getting-started/installation Installs the Maatwebsite Excel package and its dependencies, including PhpSpreadsheet, via Composer. Requires PHP ^7.2 or ^8.0 and Laravel ^5.8. Outputs package installation or error messages if version conflicts occur. Use without version if standard command fails due to PHP version mismatches. ```shell composer require "maatwebsite/excel:^3.1" ``` ```shell composer require maatwebsite/excel ``` -------------------------------- ### Publish Excel Configuration via Artisan in Laravel (Shell) Source: https://docs.laravel-excel.com/3.1/getting-started/installation Publishes the Excel package configuration file to config/excel.php using Laravel's vendor publish command. Requires the package and service provider to be installed. Outputs a new configuration file. No specific limitations, but ensures the service provider is registered beforehand. ```shell php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider" --tag=config ``` -------------------------------- ### Use Excel via Facade Source: https://docs.laravel-excel.com/3.0/getting-started/installation Example of using Laravel Excel through the facade. The Excel facade provides a static interface to the Excel manager. ```php use App\YourExport; use Maatwebsite\Excel\Facades\Excel; class YourController { public function export() { return Excel::download(new YourExport); } } ``` -------------------------------- ### Add DownloadExcel Action to Nova User Resource Source: https://docs.laravel-excel.com/nova/1.x/exports This PHP code extends a Nova resource to include the DownloadExcel action, allowing export of selected users to an Excel file. It depends on the installed Laravel Nova Excel package and a corresponding User model. In the Nova panel, select users and trigger the action to generate users.xlsx in the downloads folder; limitations include default export without custom formatting. ```php [ /* * Package Service Providers... */ Maatwebsite\Excel\ExcelServiceProvider::class, ] ``` -------------------------------- ### Use Excel via Exporter Interface Source: https://docs.laravel-excel.com/3.0/getting-started/installation Example of using Laravel Excel through the Exporter interface for better decoupling. The Exporter is injected into the constructor. ```php 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); } } ``` -------------------------------- ### Use Excel via Dependency Injection Source: https://docs.laravel-excel.com/3.0/getting-started/installation Example of using Laravel Excel through dependency injection in a controller. The Excel manager is injected into the constructor. ```php 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); } } ``` -------------------------------- ### Implement export method in UsersController (PHP) Source: https://docs.laravel-excel.com/3.0/exports Adds an export action to UsersController that returns an XLSX download using the previously defined UsersExport class. The method leverages the Excel facade to stream the file to the browser. ```php 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'); } } ``` -------------------------------- ### Define Route for Export (PHP) Source: https://docs.laravel-excel.com/3.1/exports Adds a new GET route to the Laravel application that maps a URL to the `export` method of the `UsersController`. This allows users to access the export functionality via a web browser. ```php Route::get('users/export/', [UsersController::class, 'export']); ``` -------------------------------- ### Define UsersExport class for Laravel Excel (PHP) Source: https://docs.laravel-excel.com/3.0/exports Manually creates a UsersExport class implementing FromCollection to export all User records. No additional dependencies beyond Laravel Excel are required. ```php Maatwebsite\Excel\Facades\Excel::class. Applies to scenarios where auto-discovery does not register the facade. ```php 'aliases' => [ ... 'Excel' => Maatwebsite\Excel\Facades\Excel::class, ] ``` -------------------------------- ### Trigger Excel Export in Controller (PHP) Source: https://docs.laravel-excel.com/3.1/exports Demonstrates how to trigger an Excel export within a Laravel controller. It instantiates the `UsersExport` class and uses the `Excel::download` facade to initiate the download with a specified filename. ```php invoices = $invoices; } public function collection() { return $this->invoices->all(); } } ``` ```PHP public function export(Excel $excel, InvoicesExport $export) { return $excel->download($export, 'invoices.xlsx'); } ``` -------------------------------- ### Define UsersExport Class (PHP) Source: https://docs.laravel-excel.com/3.1/exports Manually creates an export class that implements the `FromCollection` concern. This class is responsible for retrieving all users from the database and returning them for export. ```php downloadExcel( $filePath, $writerType = null, $headings = false ) ``` -------------------------------- ### Download Excel Export in Controller Source: https://docs.laravel-excel.com/3.0/exports/collection Shows how to download an Excel export in a Laravel controller using the Excel facade. The export is triggered with a specified filename. ```PHP public function export() { return Excel::download(new InvoicesExport, 'invoices.xlsx'); } ``` -------------------------------- ### Publish Laravel Excel Config Source: https://docs.laravel-excel.com/3.0/getting-started/installation Artisan command to publish the Laravel Excel configuration file. This creates config/excel.php. ```bash php artisan vendor:publish ``` -------------------------------- ### Install Laravel Excel - Composer Source: https://docs.laravel-excel.com/2 This snippet demonstrates how to add the maatwebsite/excel package to your Laravel project using Composer. It requires Composer and a Laravel project to be set up. It will download the package and dependencies. ```composer "maatwebsite/excel": "~1.3" ``` ```composer "maatwebsite/excel": "~2.1.0" ``` -------------------------------- ### Bind Excel to Custom Class via Container Source: https://docs.laravel-excel.com/3.0/getting-started/installation Example of binding the Excel manager to a custom class through Laravel's service container. This allows for custom implementations. ```php $this->app->bind(YourCustomExporter::class, function() { return new YourCustomExporter($this->app['excel']); }); ``` -------------------------------- ### Batch import all files from a folder with Laravel Excel Source: https://docs.laravel-excel.com/2.1/import/batch This example shows how to batch import all XLS, XLSX, and CSV files from a folder using Laravel Excel. The callback receives rows and file information, allowing you to process each row, e.g., dump the firstname. Works only for supported formats. ```PHP 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); }); }); ``` -------------------------------- ### Download Excel with Custom Headers in Laravel Source: https://docs.laravel-excel.com/3.1/exports/collection Illustrates downloading an Excel export with custom response headers. Uses the Excel facade and allows specifying headers like 'X-Vapor-Base64-Encode'. ```PHP public function export() { return Excel::download(new InvoicesExport, 'invoices.xlsx', true, ['X-Vapor-Base64-Encode' => 'True']); } ``` -------------------------------- ### Instantiate Chart with PhpSpreadsheet Source: https://docs.laravel-excel.com/3.1/exports/charts Demonstrates how to create a Chart instance by instantiating its components including DataSeriesValues, DataSeries, PlotArea, Legend, and Chart objects. This example creates a pie chart with labels, categories, and values from worksheet cells. ```php $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); ``` -------------------------------- ### Remote Temporary Files in Multi-Server Setup Source: https://docs.laravel-excel.com/3.1/exports/queued Configures remote temporary files for queued exports in a multi-server setup, using a remote disk like S3. ```PHP 'temporary_files' => [ 'remote_disk' => 's3', ], ``` -------------------------------- ### Basic Excel Export in Laravel Source: https://docs.laravel-excel.com/3.0/exports/collection Demonstrates creating a basic export class in Laravel to export invoices to Excel. The class implements FromCollection and returns all invoices. ```PHP namespace App\Exports; use Maatwebsite\Excel\Concerns\FromCollection; class InvoicesExport implements FromCollection { public function collection() { return Invoice::all(); } } ``` -------------------------------- ### Test Excel download in Laravel Source: https://docs.laravel-excel.com/3.0/exports/testing This snippet shows how to test downloading an Excel file in Laravel. It uses the Excel facade to fake the exporter and assert that the correct file is downloaded with the expected content. ```PHP /** * @test */ public function user_can_download_invoices_export() { Excel::fake(); $this->actingAs($this->givenUser()) ->get('/invoices/download/xlsx'); Excel::assertDownloaded('filename.xlsx', function(InvoicesExport $export) { // Assert that the correct export is downloaded. return $export->collection()->contains('#2018-01'); }); } ``` -------------------------------- ### Register Service Providers in Laravel Application Source: https://docs.laravel-excel.com/nova/1.x/getting-started/installation Manually registers the Excel and Laravel Nova Excel service providers in the `config/app.php` file. This is necessary if auto-discovery is not enabled, ensuring that the package's functionality is available throughout the application. ```php 'providers' => [ /* * Package Service Providers... */ Maatwebsite\Excel\ExcelServiceProvider::class, Maatwebsite\LaravelNovaExcel\LaravelNovaExcelServiceProvider::class, ] ``` -------------------------------- ### Store Excel Export on Disk Source: https://docs.laravel-excel.com/3.0/exports/collection Illustrates storing an Excel export on a disk (e.g., S3) in Laravel. The export is saved with a specified filename and disk. ```PHP public function storeExcel() { return Excel::store(new InvoicesExport, 'invoices.xlsx', 's3'); } ``` -------------------------------- ### Pass Data to Excel Export in Laravel Source: https://docs.laravel-excel.com/3.1/exports/collection Shows how to pass data from a controller to an Excel export class using the constructor. Useful for dynamic data exports. ```PHP 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; } } ``` ```PHP public function export() { $export = new InvoicesExport([ [1, 2, 3], [4, 5, 6] ]); return Excel::download($export, 'invoices.xlsx'); } ``` -------------------------------- ### Generate UsersImport class with Artisan command Source: https://docs.laravel-excel.com/3.1/imports Creates a new import class named UsersImport linked to the User model using the Laravel Artisan CLI. This command sets up the necessary file structure in the app/Imports directory. No additional dependencies are required beyond the Laravel Excel package. ```bash php artisan make:import UsersImport --model=User ``` -------------------------------- ### Test Excel queue in Laravel Source: https://docs.laravel-excel.com/3.0/exports/testing This snippet illustrates testing the queuing of an Excel file export in Laravel. It uses the Excel facade to fake the exporter and verify that the file is queued for export with optional disk and content checks. ```PHP /** * @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; }); } ``` -------------------------------- ### Download Collection as Excel File Source: https://docs.laravel-excel.com/3.1/exports/collection Download a collection of data as an Excel file using Laravel's collection macros. Works with both Eloquent collections and regular collections. Supports optional file path, writer type, and headings parameters. ```php User::all()->downloadExcel( $filePath, $writerType = null, $headings = false ) ``` ```php (new Collection([[1, 2, 3], [1, 2, 3]]))->downloadExcel( $filePath, $writerType = null, $headings = false ) ``` -------------------------------- ### Test Excel store in Laravel Source: https://docs.laravel-excel.com/3.0/exports/testing This snippet demonstrates testing the storage of an Excel file in Laravel. It fakes the exporter and asserts that the file is stored on the specified disk with optional content validation. ```PHP /** * @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; }); } ``` -------------------------------- ### Basic Queued Export in Laravel Source: https://docs.laravel-excel.com/3.1/exports/queued Demonstrates how to queue an export in Laravel Excel. The export class implements FromQuery and uses the Exportable trait. The queue method is called to start the export process. ```PHP namespace App\Exports; use App\Invoice; use Maatwebsite\Excel\Concerns\Exportable; use Maatwebsite\Excel\Concerns\FromQuery; class InvoicesExport implements FromQuery { use Exportable; public function query() { return Invoice::query(); } } ``` ```PHP (new InvoicesExport)->queue('invoices.xlsx'); return back()->withSuccess('Export started!'); ``` -------------------------------- ### Store Collection as Excel on Disk Source: https://docs.laravel-excel.com/3.0/exports/collection Illustrates using a Laravel collection macro to store data as an Excel file on a disk. The method allows specifying file path, disk, writer type, and headings. ```PHP (new Collection([[1, 2, 3], [1, 2, 3]]))->storeExcel( $filePath, $disk = null, $writerType = null, $headings = false ) ``` -------------------------------- ### Export Array to Excel in Laravel Source: https://docs.laravel-excel.com/3.1/exports/collection Demonstrates exporting an array to Excel using the FromArray concern. Useful for non-Eloquent data sources like APIs or MongoDB. ```PHP 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] ]; } } ``` -------------------------------- ### Download as Gnumeric in Laravel Excel Source: https://docs.laravel-excel.com/3.0/exports/export-formats Provides an example for explicitly downloading an export as a Gnumeric file using Laravel Excel. Gnumeric is a free and open-source spreadsheet program. ```php (new InvoicesExport)->download('invoices.gnumeric', \Maatwebsite\Excel\Excel::GNUMERIC); ``` -------------------------------- ### Group Excel Import Results Using Collection Methods Source: https://docs.laravel-excel.com/2.1/import/results Demonstrates how to use Laravel's collection methods on imported Excel data. After calling `->get()`, the resulting collection can be manipulated, for example, by grouping rows based on a specific column value. ```php // E.g. group the results $reader->get()->groupBy('firstname'); ``` -------------------------------- ### Strict Null Comparisons in Excel Export Source: https://docs.laravel-excel.com/3.0/exports/collection Shows how to ensure zero values are exported as actual zeros instead of null in Excel. The export class implements WithStrictNullComparison. ```PHP namespace App\Exports; use Maatwebsite\Excel\Concerns\FromCollection; use Maatwebsite\Excel\Concerns\WithStrictNullComparison; class InvoicesExport implements FromCollection, WithStrictNullComparison { public function __construct(InvoicesRepository $invoices) { $this->invoices = $invoices; } public function collection() { return $this->invoices->all(); } } ``` -------------------------------- ### Get All Sheets and Rows from Excel File Source: https://docs.laravel-excel.com/2.1/import/results Loads an Excel file and retrieves all its contents. The `->get()` and `->all()` methods return a sheet or row collection based on the file's structure. This behavior can be modified in the `import.php` config file. ```php Excel::load('file.xls', function($reader) { })->get(); ``` ```php 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(); }); ``` -------------------------------- ### Define UsersImport class manually Source: https://docs.laravel-excel.com/3.1/imports Provides the PHP implementation of the UsersImport class implementing the ToModel concern. It maps each row of the spreadsheet to a new User instance, hashing the password before saving. Requires the Laravel Excel package and Laravel's Hash facade. ```php $row[0], 'email' => $row[1], 'password' => Hash::make($row[2]), ]); } } ``` -------------------------------- ### Import a specific set of files using Laravel Excel batch Source: https://docs.laravel-excel.com/2.1/import/batch Demonstrates importing a specific set of files by passing an array of file paths to Excel::batch. The callback receives rows and file metadata. Useful when you need to import selected files rather than an entire directory. ```PHP $files array( 'file1.xls', 'file2.xls' ); Excel::batch($files, function($rows, $file) { }); ``` -------------------------------- ### Import users in controller using Laravel Excel Source: https://docs.laravel-excel.com/3.1/imports Shows how to trigger the UsersImport import within a controller action. The method imports the 'users.xlsx' file and redirects back with a success flash message. Ensure the Excel facade is properly aliased and the UsersImport class is imported. ```php with('success', 'All good!'); } } ``` -------------------------------- ### Import from Default Disk in PHP Source: https://docs.laravel-excel.com/3.1/imports/basics This code imports an Excel file using the default filesystem disk configured in Laravel, passing a UsersImport instance and the file name. It relies on the Maatwebsite\Excel package's Excel facade and assumes the file exists on the default disk with a valid extension. Output is the imported data processed via the import class, with no explicit return; limitations include no custom disk support or reader type specification. ```PHP Excel::import(new UsersImport, 'users.xlsx'); ``` -------------------------------- ### Export Custom Collection to Excel in Laravel Source: https://docs.laravel-excel.com/3.1/exports/collection Shows how to export a custom collection (not Eloquent) to Excel. Uses the FromCollection concern to return a manually created collection. ```PHP 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] ]); } } ``` -------------------------------- ### Create Custom Export Actions Source: https://docs.laravel-excel.com/nova/1.x/exports/customizations Provides an example of creating a custom Nova action for exporting data by extending either `ExportToExcel` or `DownloadExcel`. This allows for full control over the export process and custom logic. ```php storeExcel( $filePath, $disk = null, $writerType = null, $headings = false ) ``` -------------------------------- ### Export Collection to Excel in Laravel Source: https://docs.laravel-excel.com/3.1/exports/collection Demonstrates how to export a collection of Eloquent models to Excel. Uses the FromCollection concern and requires the Laravel Excel package. Outputs an Excel file with all records from the Invoice model. ```PHP namespace App\Exports; use App\Invoice; use Maatwebsite\Excel\Concerns\FromCollection; class InvoicesExport implements FromCollection { public function collection() { return Invoice::all(); } } ``` -------------------------------- ### Create Sheet from Array Using with (PHP) Source: https://docs.laravel-excel.com/2.1/export/array This demonstrates an alternative using `with()`, to create the sheet using an array as data. It accepts parameters for null values, start cell, strict null comparison, and heading generation. ```PHP $sheet->with(array( array('data1', 'data2'), array('data3', 'data4') )); ``` -------------------------------- ### Download as ODS in Laravel Excel Source: https://docs.laravel-excel.com/3.0/exports/export-formats Provides an example of how to explicitly download an export as an ODS (OpenDocument Spreadsheet) file using Laravel Excel. This format is compatible with applications like LibreOffice Calc. ```php (new InvoicesExport)->download('invoices.ods', \Maatwebsite\Excel\Excel::ODS); ``` -------------------------------- ### Implement Custom Value Binder in PHP Source: https://docs.laravel-excel.com/2.1/import/formatting Demonstrates creating a custom value binder class that implements PHPExcel_Cell_IValueBinder. This example specifically handles numeric values differently from the default binder. ```php 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'); ``` -------------------------------- ### Create ToModel Import Class in PHP Source: https://docs.laravel-excel.com/3.1/imports/basics This PHP class implements the ToModel concern from Maatwebsite\Excel to map Excel rows to Eloquent models for database insertion. It requires Laravel's Hash facade for password hashing and expects input rows with name, email, and password in sequence. Output is new User model instances, limited to basic user creation without validation or error handling. ```PHP $row[0], 'email' => $row[1], 'password' => Hash::make($row[2]), ]); } } ``` -------------------------------- ### Import from Custom Disk in PHP Source: https://docs.laravel-excel.com/3.1/imports/basics This PHP snippet imports an Excel file from a specified filesystem disk like S3, using the Maatwebsite\Excel facade. Input includes the import class, file path, and disk name from config/filesystems.php. It outputs processed data without a return value, dependent on Laravel's filesystem and the file's presence on the specified disk. ```PHP Excel::import(new UsersImport, 'users.xlsx', 's3'); ``` -------------------------------- ### Downloading Multi-Sheet Excel Export in Laravel Controller Source: https://docs.laravel-excel.com/3.1/exports/multiple-sheets This method triggers the download of an XLSX file containing multiple sheets from the InvoicesExport class. It depends on the Laravel Excel package and the defined export class. Inputs: Implicit year (hardcoded as 2018 in example); outputs a downloadable Excel file named 'invoices.xlsx'. Limitations: Year is fixed in the example; adjust constructor for dynamic input. ```php public function downloadInvoices() { return (new InvoicesExport(2018))->download('invoices.xlsx'); } ``` -------------------------------- ### Laravel Excel - Selecting Sheets by Index Source: https://docs.laravel-excel.com/3.1/imports/multiple-sheets Shows how to select sheets by their index using the `WithMultipleSheets` concern. Sheet indices start at 0. This provides more control over sheet mapping. ```php namespace App\Imports;\n\nuse Maatwebsite\Excel\Concerns\WithMultipleSheets;\n\nclass UsersImport implements WithMultipleSheets\n{ \n public function sheets(): array\n { \n return [\n 0 => new FirstSheetImport(),\n 1 => new SecondSheetImport(),\n ];\n }\n} ``` -------------------------------- ### Select specific columns with Laravel Excel Source: https://docs.laravel-excel.com/2.1/import/select Limits retrieved data to chosen columns using select or get with an array of column names. Works with the reader instance and returns only the specified fields. All get methods accept an array of columns. ```php // Select $reader->select(array('firstname', 'lastname'))->get(); // Or $reader->get(array('firstname', 'lastname')); ``` -------------------------------- ### Basic FromQuery Export Class in Laravel Excel Source: https://docs.laravel-excel.com/3.1/exports/from-query Implements the FromQuery concern to return an Eloquent query for chunked export processing, avoiding full result loading for better performance on large datasets. Requires Maatwebsite\Excel package and an Invoice model. Outputs an Excel file via the Exportable trait's download method. Limitation: Do not call get() on the query. ```php namespace App\Exports; use App\Invoice; use Maatwebsite\Excel\Concerns\FromQuery; use Maatwebsite\Excel\Concerns\Exportable; class InvoicesExport implements FromQuery { use Exportable; public function query() { return Invoice::query(); } } ``` ```php return (new InvoicesExport)->download('invoices.xlsx'); ``` -------------------------------- ### Batch import folders with multiple sheets using Laravel Excel Source: https://docs.laravel-excel.com/2.1/import/batch Shows handling files that contain multiple sheets during a batch import. The callback receives a collection of sheets for each file, enabling iteration over each sheet. Suitable for Excel workbooks with several worksheets. ```PHP Excel::batch('app/storage/uploads', function($sheets, $file) { $sheets->each(function($sheet) { }); }); ``` -------------------------------- ### Resolve Excel Class - PHP Source: https://docs.laravel-excel.com/2 This example shows how to resolve the `excel` class from the Laravel IoC container using `App::make()`. The class provides the core Excel functionality and allows for more control than using the facade. Requires Laravel framework. ```php $excel = App::make('excel'); ``` -------------------------------- ### Import with Full Path in PHP Source: https://docs.laravel-excel.com/3.1/imports/basics This snippet imports an Excel file by specifying its full filesystem path, bypassing disk abstractions. It requires the Maatwebsite\Excel facade and a valid file path like storage_path. Returns no value but processes the import; assumes file exists and is readable, with potential security risks if paths are untrusted. ```PHP Excel::import(new UsersImport, storage_path('users.xlsx')); ``` -------------------------------- ### Implement WithStartRow for Custom Start Row in Laravel Excel Import Source: https://docs.laravel-excel.com/3.1/imports/start-row This code demonstrates how to implement the `WithStartRow` concern within a Laravel Excel import class. This allows you to define a specific row number from which the import process should begin, effectively skipping rows before it. It requires the `MaatwebsiteExcelConcernsWithStartRow` and `MaatwebsiteExcelConcernsToModel` traits. ```php namespace App\Imports; use App\User; use Maatwebsite\Excel\Concerns\ToModel; use Maatwebsite\Excel\Concerns\WithStartRow; class UsersImport implements ToModel, WithStartRow { public function model(array $row) { return new User([ 'name' => $row[0], ]); } public function startRow(): int { return 2; } } ``` -------------------------------- ### Append multiple rows in Laravel Excel Source: https://docs.laravel-excel.com/2.1/export/rows Shows how to add multiple rows at once in Laravel Excel. The examples demonstrate adding two sets of rows. ```PHP // Append multiple rows $sheet->rows(array( array('test1', 'test2'), array('test3', 'test4') )); // Append multiple rows $sheet->rows(array( array('test5', 'test6'), array('test7', 'test8') )); ``` -------------------------------- ### Manipulate row cells in Laravel Excel Source: https://docs.laravel-excel.com/2.1/export/rows Shows how to apply styling to specific rows in Laravel Excel. The example sets a black background for the first row. ```PHP // Set black background $sheet->row(1, function($row) { // call cell manipulation methods $row->setBackground('#000000'); }); ``` -------------------------------- ### Create Basic Excel File Source: https://docs.laravel-excel.com/2.1/export Demonstrates the basic usage of the `create` method to initiate a new Excel file. It shows how to provide a filename and optionally use a callback for further manipulation. ```php Excel::create('Filename'); ``` ```php Excel::create('Filename', function($excel) { // Call writer methods here }); ``` -------------------------------- ### Test Dynamic File Name Import with Laravel Excel Source: https://docs.laravel-excel.com/3.1/imports/testing Tests importing users with dynamically named files using regular expressions. It fakes the Excel facade, performs a GET request, enables regular expression matching with `Excel::matchByRegex()`, and asserts the import using a regex pattern for the filename. ```php /** * @test */ public function user_can_import_users() { Excel::fake(); $this->actingAs($this->givenUser()) ->get('/users/import/xlsx'); // Tells the mock to use regular expressions Excel::matchByRegex(); // For a given dynamic named file 'dynamic_1234_filename.xlsx' Excel::assertImported('/\w{7}_\d{4}\_\w{8}\.xlsx/', 'diskName'); } ``` -------------------------------- ### Export Raw File Contents Source: https://docs.laravel-excel.com/3.1/exports/collection Retrieve raw contents of an exported Excel file without writing to disk. Returns the file contents as a string, useful for streaming or custom file handling. Requires the Maatwebsite\Excel facade and Excel::XLSX writer type. ```php $contents = Excel::raw(new InvoicesExport, \Maatwebsite\Excel\Excel::XLSX); ``` -------------------------------- ### Testing Excel Storage in Laravel PHP Source: https://docs.laravel-excel.com/3.1/exports/testing This PHP snippet tests storing an Excel export by faking the export and asserting it's stored on disk. Dependencies include the Excel facade and InvoicesExport class. It simulates a GET request to store the export and checks storage assertions with optional callbacks. Note that disk defaults apply when callbacks are used as the second parameter. ```php /** * @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; }); } ``` -------------------------------- ### Download PDF using TCPDF in Laravel Excel Source: https://docs.laravel-excel.com/3.0/exports/export-formats Provides an example of how to explicitly download an export as a PDF file using the TCPDF library through Laravel Excel. TCPDF is another robust option for PDF generation. ```php (new InvoicesExport)->download('invoices.pdf', \Maatwebsite\Excel\Excel::TCPDF); ``` -------------------------------- ### Change cell fonts in Laravel Excel Source: https://docs.laravel-excel.com/2.1/export/cells Provides examples of changing various font properties in Laravel Excel including color, family, size, and weight. ```PHP $cells->setFontColor('#ffffff'); $cells->setFontFamily('Calibri'); $cells->setFontSize(16); $cells->setFontWeight('bold'); $cells->setFont(array( 'family' => 'Calibri', 'size' => '16', 'bold' => true )); ``` -------------------------------- ### Register Excel Events in Laravel (PHP) Source: https://docs.laravel-excel.com/3.1/exports/extending This code demonstrates how to register events within a Laravel Excel export class, using the `WithEvents` concern. The example sets the creator and landscape orientation. It leverages PhpSpreadsheet's methods for styling. ```PHP namespace App\Exports; use Maatwebsite\Excel\Concerns\WithEvents; use Maatwebsite\Excel\Events\BeforeExport; use Maatwebsite\Excel\Events\AfterSheet; class InvoicesExport implements WithEvents { /** * @return array */ public function registerEvents(): array { return [ BeforeExport::class => function(BeforeExport $event) { $event->writer->setCreator('Patrick'); }, AfterSheet::class => function(AfterSheet $event) { $event->sheet->setOrientation(\PhpOffice\PhpSpreadsheet\Worksheet\PageSetup::ORIENTATION_LANDSCAPE); $event->sheet->styleCells( 'B2:G8', [ 'borders' => [ 'outline' => [ 'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK, 'color' => ['argb' => 'FFFF0000'], ], ] ] ); }, ]; } } ``` -------------------------------- ### Create Sheet from Array (PHP) Source: https://docs.laravel-excel.com/2.1/export/array This snippet demonstrates creating an Excel sheet from a PHP array using `->fromArray()`. It accepts parameters for null values, start cell, strict null comparison, and heading generation. The resulting array is written to the Excel file. ```PHP Excel::create('Filename', function($excel) { $excel->sheet('Sheetname', function($sheet) { $sheet->fromArray(array( array('data1', 'data2'), array('data3', 'data4') )); }); })->export('xls'); ``` -------------------------------- ### Download Basic Excel Export in Laravel PHP Source: https://docs.laravel-excel.com/3.0/exports/from-query This code shows how to instantiate the InvoicesExport class and trigger an Excel file download. It calls the download method with a filename. No additional inputs; outputs a downloadable .xlsx file based on the query. ```php return (new InvoicesExport)->download('invoices.xlsx'); ``` -------------------------------- ### Import with Reader Type in PHP Source: https://docs.laravel-excel.com/3.1/imports/basics This imports an Excel file by explicitly specifying the reader type (e.g., XLSX) via Maatwebsite\Excel constants. Useful when file extensions are misleading. Inputs include file, disk, and reader constant; outputs processed data through the import class, with dependencies on correct reader type matching the file format. ```PHP Excel::import(new UsersImport, 'users.xlsx', 's3', \Maatwebsite\Excel\Excel::XLSX); ``` -------------------------------- ### Testing Excel Queuing in Laravel PHP Source: https://docs.laravel-excel.com/3.1/exports/testing This PHP code tests queuing an Excel export using fakes and assertions for queued exports. It relies on the Excel facade, InvoicesExport, and potentially job classes like NotifyUsers. The test triggers a GET request to queue the export and verifies queuing with disk and chain options. Limitations include ensuring the expression matches only one file to avoid test failures. ```php /** * @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(), ]) } ``` -------------------------------- ### Attach Job Middleware to Laravel Excel Import Class Source: https://docs.laravel-excel.com/3.1/imports/queued Demonstrates how to attach job middleware to a Laravel Excel import class using the middleware method. The example imports a RateLimited middleware and defines a retryUntil method to limit retry attempts. No additional dependencies beyond Laravel and maatwebsite/excel are required. ```php namespace App\Imports; use App\Jobs\Middleware\RateLimited; use Maatwebsite\Excel\Concerns\Importable; use Maatwebsite\Excel\Concerns\FromQuery; class ImportClass implements FromQuery { use Importable; public function middleware() { return [new RateLimited]; } public function retryUntil() { return now()->addSeconds(5); } public function query() { // ... } } ``` -------------------------------- ### Change cell values in Laravel Excel Source: https://docs.laravel-excel.com/2.1/export/rows Demonstrates how to modify specific rows and their cell values in Laravel Excel. The examples show changing values in the first and second rows. ```PHP // Manipulate first row $sheet->row(1, array( 'test1', 'test2' )); // Manipulate 2nd row $sheet->row(2, array( 'test3', 'test4' )); ``` -------------------------------- ### Chain Jobs to Queued Exports Source: https://docs.laravel-excel.com/nova/1.x/exports/customizations Explains how to chain additional jobs to a `QueuedExport` using the `onSuccess` callback. This example shows chaining a `NotifyUserOfCompletedExport` job to be executed after the export is successfully queued, allowing for asynchronous tasks. ```php use Illuminate\Foundation\Bus\PendingDispatch; public function actions(Request $request) { return [ (new QueuedExport) ->onSuccess(function (ActionRequest $request, PendingDispatch $queue) { $queue ->allOnQueue('exports') ->chain([ new NotifyUserOfCompletedExport($request->user()), ]); return Action::message('Your export is queued!'); }), ]; } ``` ```php user = $user; } public function handle() { $this->user->notify(...); } } ``` -------------------------------- ### Upsert with Specific Column Updates Source: https://docs.laravel-excel.com/3.1/imports/model Extends upsert functionality by implementing WithUpsertColumns concern to control which columns get updated during upsert operations. Only specified columns are modified when duplicates are found. ```php class UsersImport implements ToModel, WithUpserts, WithUpsertColumns { /** * @return array */ public function upsertColumns() { return ['name', 'role']; } } ``` -------------------------------- ### Create WithCustomPropertiesHandler class Source: https://docs.laravel-excel.com/3.0/exports/extending Implements a concern handler for the WithCustomProperties interface. This handler sets the document description property using the exportable's description method. Depends on Maatwebsite Excel package and receives Writer object during execution. ```php 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() ); } } ``` -------------------------------- ### Testing Excel Downloads in Laravel PHP Source: https://docs.laravel-excel.com/3.1/exports/testing This PHP code tests if a user can download an Excel export by faking the export and asserting the download. It depends on Laravel's Excel facade and an InvoicesExport class. The input is a GET request to the download endpoint, and it outputs an assertion on the downloaded file's content. Limitations include requiring a specific export class and matching exact file names. ```php /** * @test */ public function user_can_download_invoices_export() { Excel::fake(); $this->actingAs($this->givenUser()) ->get('/invoices/download/xlsx'); Excel::assertDownloaded('filename.xlsx', function(InvoicesExport $export) { // Assert that the correct export is downloaded. return $export->collection()->contains('#2018-01'); }); } ``` -------------------------------- ### Bind Excel Facade - PHP Source: https://docs.laravel-excel.com/2 This section demonstrates how to use the Excel facade by adding it as an alias to your application's `app/config/app.php` file. Using facades provides a more concise syntax when working with the Excel package. It simplifies common Excel operations. ```php 'Excel' => Maatwebsite\Excel\Facades\Excel::class, ``` -------------------------------- ### Test Queued User Import with Laravel Excel Source: https://docs.laravel-excel.com/3.1/imports/testing Tests queuing user imports via an XLSX file. It fakes the Excel facade, performs an authenticated GET request to the queue import endpoint, and asserts that the import was queued for a specific filename and disk, with optional callback validation. ```php /** * @test */ public function user_can_queue_the_users_import() { Excel::fake(); $this->actingAs($this->givenUser()) ->get('/users/queue/xlsx'); Excel::assertQueued('filename.xlsx', 'diskName'); Excel::assertQueued('filename.xlsx', 'diskName', function(UsersImport $import) { return true; }); // When passing the callback as 2nd param, the disk will be the default disk. Excel::assertQueued('filename.xlsx', function(UsersImport $import) { return true; }); } ``` -------------------------------- ### Format date fields after retrieval with Carbon in Laravel Excel Source: https://docs.laravel-excel.com/2.1/import/dates Shows how to format date fields after fetching rows by utilizing Carbon's format method inside a loop. Each row's date attribute is converted to the desired string format. ```php $rows->each(function($row) { $created_at = $row->created_at->format('Y-m-d'); }); ``` -------------------------------- ### Auto-Registering Events with Trait in PHP Source: https://docs.laravel-excel.com/3.1/exports/extending This PHP example uses the RegistersEventListeners trait in an export class to automatically register event listeners without the registerEvents method. Static methods are defined for BeforeExport, BeforeWriting, BeforeSheet, and AfterSheet events. The trait ensures listeners are only registered if the methods exist. ```php 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) { // } } ``` -------------------------------- ### Strict Null Comparison in fromArray (PHP) Source: https://docs.laravel-excel.com/2.1/export/array This example highlights how to configure strict null comparison with `->fromArray()`. By default, 0 is treated as an empty cell, but setting the 4th parameter to `true` prevents this behavior. ```PHP // Will show 0 as 0 $sheet->fromArray($data, null, 'A1', true); ``` -------------------------------- ### Format dates using formatDates in Laravel Excel Source: https://docs.laravel-excel.com/2.1/import/dates Demonstrates how to enable or disable date formatting for a Laravel Excel import and optionally set a custom date format. Uses the $reader->formatDates method with a boolean flag and an optional format string. ```php $reader->formatDates(true); $reader->formatDates(false); $reader->formatDates(true, 'Y-m-d'); ``` -------------------------------- ### Set default date format with setDateFormat in Laravel Excel Source: https://docs.laravel-excel.com/2.1/import/dates Sets a global default date format for the import, causing dates to be returned as formatted strings instead of Carbon objects. This can be defined in configuration or via the setDateFormat method. ```php $reader->setDateFormat('Y-m-d'); ``` -------------------------------- ### Get the First Sheet or Row from Imported Excel Data Source: https://docs.laravel-excel.com/2.1/import/results Retrieves the first sheet or row from the imported Excel file using the `->first()` method. The specific return value (first row or first sheet) depends on the `'force_sheets_collection'` configuration setting. ```php $reader->first(); ``` -------------------------------- ### Import to Array or Collection in PHP Source: https://docs.laravel-excel.com/3.1/imports/basics This PHP code converts an Excel file to an array or collection using Maatwebsite\Excel's toArray or toCollection methods, suitable for one-off data access. It takes a file path or import class; outputs are PHP arrays or Laravel collections. Note potential performance issues with large files and bypasses standard import concerns like ToModel. ```PHP $array = Excel::toArray(new UsersImport, 'users.xlsx'); $collection = Excel::toCollection(new UsersImport, 'users.xlsx'); ``` -------------------------------- ### Test User Import with Laravel Excel Source: https://docs.laravel-excel.com/3.1/imports/testing Tests importing users via an XLSX file. It fakes the Excel facade, performs an authenticated GET request to the import endpoint, and asserts that the import occurred for a specific filename and disk, optionally with a callback to check the import details. ```php /** * @test */ public function user_can_import_users() { Excel::fake(); $this->actingAs($this->givenUser()) ->get('/users/import/xlsx'); Excel::assertImported('filename.xlsx', 'diskName'); Excel::assertImported('filename.xlsx', 'diskName', function(UsersImport $import) { return true; }); // When passing the callback as 2nd param, the disk will be the default disk. Excel::assertImported('filename.xlsx', function(UsersImport $import) { return true; }); } ``` -------------------------------- ### Implement row validation with WithValidation in Laravel Source: https://docs.laravel-excel.com/3.1/imports/validation This snippet shows how to use the WithValidation concern to define validation rules for each row in an Excel import. It includes examples of using Rule::in and callback validation rules. ```PHP $row[0], 'email' => $row[1], 'password' => 'secret', ]); } public function rules(): array { return [ '1' => Rule::in(['patrick@maatwebsite.nl']), // Above is alias for as it always validates in batches '*.1' => Rule::in(['patrick@maatwebsite.nl']), // Can also use callback validation rules '0' => function($attribute, $value, $onFailure) { if ($value !== 'Patrick Brouwers') { $onFailure('Name is not Patrick Brouwers'); } } ]; } } ``` -------------------------------- ### Register concern handler with custom event hook Source: https://docs.laravel-excel.com/3.0/exports/extending Registers a concern handler with a specific event hook instead of the default BeforeWriting event. Uses BeforeExport event as an example. Third parameter specifies the event class to bind to. ```php public function register() { Excel::extend(WithCustomProperties::class, new WithCustomPropertiesHandler, BeforeExport::class); } ``` -------------------------------- ### Injected ExcelFile Chunk Processing in Laravel Excel Source: https://docs.laravel-excel.com/2.1/import/chunk Injects the custom ExcelFile class into a method (e.g., controller) to perform chunked processing via the chunk method. The callback handles results per chunk and can return true to stop importing. Depends on Laravel dependency injection and Excel package; inputs are the injected import instance, outputs processed data or halted import. ```PHP public function importUserList(UserListImport $import) { $import->chunk(250, function($results) { // do stuff // or return true if you want to stop importing. }); } ``` -------------------------------- ### Set Page Margins for a Sheet in Laravel Excel Source: https://docs.laravel-excel.com/2.1/export/sheets Provides examples of setting page margins for a sheet in Laravel Excel. Margins can be set individually for top, right, bottom, and left, or all margins can be set to a single value. This functionality can also be configured globally in the config file. ```php // Set top, right, bottom, left $sheet->setPageMargin(array( 0.25, 0.30, 0.25, 0.30 )); // Set all margins $sheet->setPageMargin(0.25); ``` -------------------------------- ### Map multiple rows with WithMapping Source: https://docs.laravel-excel.com/3.1/exports/mapping Returns multiple rows from the map function where each row can have different column counts. Useful for exporting complex data structures like invoice headers and line items. The example returns 3 rows with varying column counts. ```php 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, ] ]; } } ``` -------------------------------- ### Adding a Remote Image Drawing Using MemoryDrawing in PHP Source: https://docs.laravel-excel.com/3.1/exports/drawings This example uses MemoryDrawing to embed a remote image by fetching and converting it to a resource via file_get_contents and imagecreatefromstring. Depends on PhpSpreadsheet and GD extension for image handling. The drawings() method returns a MemoryDrawing instance; inputs are remote URLs, outputs embedded image in Excel. Limitations: Requires network access and valid image URL; throws exception on failure. ```php setName('Logo'); $drawing->setDescription('This is my logo'); $drawing->setImageResource($imageResource); $drawing->setHeight(90); $drawing->setCoordinates('B3'); return $drawing; } } ``` -------------------------------- ### Chunk Import Using Excel Filter in Laravel Excel Source: https://docs.laravel-excel.com/2.1/import/chunk Enables chunked importing for large files by applying the 'chunk' filter and using the chunk method. The first parameter sets chunk size (e.g., 250 rows), and the callback processes each chunk's results. This approach prevents memory overload; no additional dependencies beyond Laravel Excel are needed, inputs are file paths, outputs are processed rows in the callback. ```PHP Excel::filter('chunk')->load('file.csv')->chunk(250, function($results) { foreach($results as $row) { // do stuff } }); ``` -------------------------------- ### ExcelFile Class with Chunk Filter in Laravel Excel Source: https://docs.laravel-excel.com/2.1/import/chunk Defines a custom ExcelFile class extending Maatwebsite\Excel\Files\ExcelFile to specify the file and enable chunk filtering. The getFilters method returns the 'chunk' filter array. This is useful for structured imports; requires Laravel Excel package, inputs include file path in getFile, outputs filtered import instance for further processing like chunking. ```PHP class UserListImport extends \Maatwebsite\Excel\Files\ExcelFile { public function getFile() { return 'file.csv'; } public function getFilters() { return [ 'chunk' ]; } } ``` -------------------------------- ### Container Binding for Custom Exporter in PHP Source: https://docs.laravel-excel.com/3.1/architecture This PHP snippet demonstrates binding a custom exporter class in the service container. It depends on Laravel's container and Maatwebsite Excel. Inputs are the app instance and Excel manager; outputs a bound custom exporter. Useful for custom logic but requires container setup. ```php $this->app->bind(YourCustomExporter::class, function() { return new YourCustomExporter($this->app['excel']); }); ``` -------------------------------- ### Set Cell Name Separator Source: https://docs.laravel-excel.com/2.1/import/extra Configures the separator used to convert column names from the first row into collection attribute names. By default, spaces are converted to underscores. This example shows setting a hyphen as separator, which would transform 'Created At' into 'Created-At' instead of 'Created_at'. ```php $reader->setSeparator('-'); ``` -------------------------------- ### Change Sheet Properties in Laravel Excel Source: https://docs.laravel-excel.com/2.1/export/sheets Shows how to modify sheet properties, such as orientation, within Laravel Excel. Default properties are based on configuration values. This example sets the sheet orientation to 'landscape' before exporting as an XLS file. ```php Excel::create('Filename', function($excel) { $excel->sheet('Sheetname', function($sheet) { $sheet->setOrientation('landscape'); }); })->export('xls'); ``` -------------------------------- ### Store Excel with disk options - PHP Source: https://docs.laravel-excel.com/3.1/exports/store Demonstrates passing disk options to Excel::store() method, including visibility settings and private file shortcut. ```php public function storeExcel() { Excel::store(new InvoicesExport(2018), 'invoices.xlsx', 's3', null, [ 'visibility' => 'private', ]); } ``` ```php public function storeExcel() { Excel::store(new InvoicesExport(2018), 'invoices.xlsx', 's3', null, 'private'); } ``` -------------------------------- ### Eloquent Macro for Direct Query Download in Laravel Excel Source: https://docs.laravel-excel.com/3.1/exports/from-query Uses built-in macros on Eloquent models or builders to directly download query results as Excel without a separate export class, ideal for simple cases. Requires Maatwebsite\Excel and Excel facade for formats. Inputs: query, filename, optional format and header flag; outputs Excel file. Limitation: Best for smaller queries; use FromQuery for large ones. ```php User::query()->where('name', 'Patrick')->downloadExcel('query-download.xlsx'); ``` ```php User::query()->downloadExcel('query-download.xlsx', Excel::XLSX, true); ``` -------------------------------- ### Implement FromView Export Class in Laravel PHP Source: https://docs.laravel-excel.com/3.1/exports/from-view This code implements an export class using the FromView concern to generate Excel files from Blade views. It depends on Laravel framework, Maatwebsite Excel package, and a model like Invoice. Input is none; output is a View object. Limitations include requiring proper view file and data setup. ```PHP namespace App\Exports; use App\Invoice; use Illuminate\Contracts\View\View; use Maatwebsite\Excel\Concerns\FromView; class InvoicesExport implements FromView { public function view(): View { return view('exports.invoices', [ 'invoices' => Invoice::all() ]); } } ``` -------------------------------- ### Export Users by Year using Setter - PHP Source: https://docs.laravel-excel.com/3.1/architecture/objects This PHP code example shows an Export object that uses a setter method (`setYear`) to receive the year for filtering users. This approach allows for more flexibility in setting the export parameters after the object has been instantiated. The export process is initiated using the `Excel::download` facade. ```php namespace App\Exports; use App\Models\User; // Assuming you have a User model use Maatwebsite\Excel\Concerns\FromCollection; class UsersExport implements FromCollection { private $year; public function setYear(int $year) { $this->year = $year; } public function collection() { // Fetch users created in the specified year return User::whereYear('created_at', $this->year)->get(); } } ``` ```php // To download the export: use App\Exports\UsersExport; use Maatwebsite\Excel\Facades\Excel; $export = new UsersExport(); $export->setYear(2019); return Excel::download($export, 'users.xlsx'); ``` -------------------------------- ### Import Uploaded File in PHP Source: https://docs.laravel-excel.com/3.1/imports/basics This code directly imports an uploaded Excel file via Laravel's request object, avoiding intermediate storage. It uses the Maatwebsite\Excel import method with the UsersImport class. Input is the uploaded file from a form; output is processed import data, with limitations in handling multiple files or validation errors. ```PHP Excel::import(new UsersImport, request()->file('your_file')); ``` -------------------------------- ### Specify custom date columns using setDateColumns in Laravel Excel Source: https://docs.laravel-excel.com/2.1/import/dates Defines specific columns that should be treated as dates, forcing Laravel Excel to parse them accordingly even if the Excel file does not mark them as date types. Useful for CSV imports or non‑standard Excel date columns. ```php $reader->setDateColumns(array( 'created_at', 'deleted_at' ))->get(); ``` -------------------------------- ### Adding Download Excel Action in Laravel Nova Source: https://docs.laravel-excel.com/nova/1.x/exports/download This snippet demonstrates adding the DownloadExcel action to a Laravel Nova resource's actions method, enabling users to download an Excel file of the resource data. It requires the Maatwebsite\LaravelNovaExcel package and a Request instance. The output is an xlsx file named after the resource, with no specific inputs beyond the standard Nova request handling. Limitations include default naming and basic export without custom configurations. ```php use Maatwebsite\LaravelNovaExcel\Actions\DownloadExcel; public function actions(Request $request) { return [ new DownloadExcel(), ]; } ``` -------------------------------- ### Set Borders in PHP Source: https://docs.laravel-excel.com/2.1/export/sheet-styling Apply borders to sheets, cells, or ranges using setAllBorders, setBorder methods in Laravel Excel. Depends on PHPSpreadsheet for sheet manipulation. Inputs are border style and optional cell/range references. Outputs styled borders; refer to reference guide for available styles. ```PHP // Sets all borders $sheet->setAllBorders('thin'); // Set border for cells $sheet->setBorder('A1', 'thin'); // Set border for range $sheet->setBorder('A1:F10', 'thin'); ``` -------------------------------- ### Download Multiple Sheets Excel File in PHP Laravel Source: https://docs.laravel-excel.com/3.0/exports/multiple-sheets This method downloads an Excel file with multiple sheets for a given year. It instantiates the InvoicesExport class and calls download. Input is year 2018, output is a downloadable xlsx file, assumes proper setup. ```PHP public function download() { return (new InvoicesExport(2018))->download('invoices.xlsx'); } ``` -------------------------------- ### Eloquent Macro for Direct Query Store in Laravel Excel Source: https://docs.laravel-excel.com/3.1/exports/from-query Stores query results directly to a disk using the storeExcel macro on Eloquent builders, bypassing download for server-side storage. Depends on Maatwebsite\Excel and configured storage disk. Inputs: query, filename, disk name; outputs file on specified disk. Useful for background jobs but requires disk configuration. ```php User::query()->storeExcel('query-store.xlsx', 'your-disk'); ``` -------------------------------- ### Download and Store Export Files - Laravel Excel Source: https://docs.laravel-excel.com/3.0/exports/exportables Demonstrates downloading export files directly from export class instances or storing them on external disks like S3. Uses the exportable class without facade dependency. ```php return (new InvoicesExport)->download('invoices.xlsx'); ``` ```php return (new InvoicesExport)->store('invoices.xlsx', 's3'); ``` -------------------------------- ### Apply Inline Styles in HTML for Laravel Excel Sheets Source: https://docs.laravel-excel.com/2.1/blade/styling Uses inline CSS styles within HTML elements for styling like background color. Supported by Laravel Excel with a reference guide for allowed styles. Direct application in view files for cell-specific customizations. ```html Cell ``` -------------------------------- ### Store with Return Info in Laravel Excel with PHP Source: https://docs.laravel-excel.com/2.1/export/store Stores the Excel file and returns storage information if the third parameter is set to true. Information includes full path, path, filename, title, and extension. Requires config setting or parameter toggle; outputs an array of info. ```php ->store('xls', false, true); ``` -------------------------------- ### Export to PDF Format in Laravel Excel Source: https://docs.laravel-excel.com/2.1/export/export Exports the file to PDF using ->export('pdf'), requiring one of the supported PDF libraries like dompdf, mpdf, or tcpdf installed via Composer. Update the 'export.pdf.driver' config to match the chosen library. Outputs are generated server-side and can be downloaded directly. ```php ->export('pdf'); ``` -------------------------------- ### Handling Failures in Queued Export Source: https://docs.laravel-excel.com/3.1/exports/queued Demonstrates how to handle failures in queued exports by implementing a failed method in the export class. ```PHP 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 } } ``` -------------------------------- ### Configure CSV Delimiter Setting Source: https://docs.laravel-excel.com/3.1/imports/custom-csv-settings Example showing how to configure the CSV delimiter setting to use tab character. The delimiter setting accepts any single character including special characters like tab '\t'. This is useful when importing CSV files that don't use comma as separator. ```php public function getCsvSettings(): array { return [ 'delimiter' => "\t" ]; } ``` -------------------------------- ### Download Export (PHP) Source: https://docs.laravel-excel.com/3.1/exports/exportables Shows how to download the generated Excel file using the `download` method. It accepts the filename, writer type, and optional headers as arguments. ```php return (new InvoicesExport)->download('invoices.xlsx'); ``` ```php return (new InvoicesExport)->download('invoices.csv', Excel::CSV, ['Content-Type' => 'text/csv']); ``` -------------------------------- ### Explicit Import Format Specification in Laravel Excel (PHP) Source: https://docs.laravel-excel.com/3.1/imports/import-formats This functionality allows developers to explicitly define the import format when using the import method on an import class, such as UsersImport. It depends on the Maatwebsite\Excel package being installed in a Laravel application. Inputs include the file path, optional disk (null for default), and the format constant. Outputs process the file according to the specified format for data import. Limitations include requiring package configuration and supported format handlers. ```php (new UsersImport)->import('users.xlsx', null, \Maatwebsite\Excel\Excel::XLSX); ``` ```php (new UsersImport)->import('users.csv', null, \Maatwebsite\Excel\Excel::CSV); ``` ```php (new UsersImport)->import('users.tsv', null, \Maatwebsite\Excel\Excel::TSV); ``` ```php (new UsersImport)->import('users.ods', null, \Maatwebsite\Excel\Excel::ODS); ``` ```php (new UsersImport)->import('users.xls', null, \Maatwebsite\Excel\Excel::XLS); ``` ```php (new UsersImport)->import('users.slk', null, \Maatwebsite\Excel\Excel::SLK); ``` ```php (new UsersImport)->import('users.xml', null, \Maatwebsite\Excel\Excel::XML); ``` ```php (new UsersImport)->import('users.gnumeric', null, \Maatwebsite\Excel\Excel::GNUMERIC); ``` ```php (new UsersImport)->import('users.html', null, \Maatwebsite\Excel\Excel::HTML); ``` -------------------------------- ### Import Users and Get Row Count - PHP Source: https://docs.laravel-excel.com/3.1/architecture/objects This PHP snippet defines an Import object that implements the `ToModel` concern for processing rows into User models. It includes a private property `$rows` to track the number of processed rows and a public getter method `getRowCount` to retrieve this count. After importing a file, the row count can be accessed. ```php namespace App\Imports; use App\Models\User; // Assuming you have a User model use Maatwebsite\Excel\Concerns\ToModel; class UsersImport implements ToModel { private $rows = 0; public function model(array $row) { // Increment row count for each processed row ++$this->rows; // Create a new User model from the row data return new User([ 'name' => $row[0], // Assuming name is in the first column // Add other fields as needed ]); } public function getRowCount(): int { // Return the total number of rows processed return $this->rows; } } ``` ```php // To perform the import and get the row count: use App\Imports\UsersImport; use Maatwebsite\Excel\Facades\Excel; $import = new UsersImport; Excel::import($import, 'users.xlsx'); // Access the row count after import dd('Row count: ' . $import->getRowCount()); ``` -------------------------------- ### Using Progress Bar in Laravel Console Command - PHP Source: https://docs.laravel-excel.com/3.1/imports/progress-bar This PHP console command class demonstrates how to execute the Excel import with progress bar output in a Laravel Artisan command. It sets up the command signature, displays a title, imports using the withOutput method, and shows a success message. Requires the UsersImport class and an existing 'users.xlsx' file in the application. ```php output->title('Starting import'); (new UsersImport)->withOutput($this->output)->import('users.xlsx'); $this->output->success('Import successful'); } } ``` -------------------------------- ### Direct Import Using Importable Trait - PHP Source: https://docs.laravel-excel.com/3.1/architecture Demonstrates direct import instantiation using the Importable trait without needing the facade or Excel manager. This is the simplest approach for importing Excel files when you have a properly configured Import class. The import method accepts a file path as parameter. ```php (new UsersImport)->import('users.xlsx'); ``` -------------------------------- ### Adding a Single Drawing to Laravel Excel Export Source: https://docs.laravel-excel.com/3.1/exports/drawings This example shows implementing the WithDrawings concern in a Laravel export class to add one drawing to the worksheet. It depends on Maatwebsite\Excel and PhpSpreadsheet packages. The drawings() method returns a single Drawing instance with coordinates; inputs include image path and properties, outputting an Excel file with the embedded image at specified position. Limitation: Image must be accessible via public_path. ```php setName('Logo'); $drawing->setDescription('This is my logo'); $drawing->setPath(public_path('/img/logo.jpg')); $drawing->setHeight(90); $drawing->setCoordinates('B3'); return $drawing; } } ``` -------------------------------- ### Download Export with Constructor Parameter in Laravel PHP Source: https://docs.laravel-excel.com/3.0/exports/from-query Demonstrates passing a year parameter to the InvoicesExport constructor and downloading the filtered export. Inputs: Year integer (e.g., 2018). Outputs: Excel file with invoices from that year. ```php return (new InvoicesExport(2018))->download('invoices.xlsx'); ``` -------------------------------- ### Create WithCustomProperties interface Source: https://docs.laravel-excel.com/3.0/exports/extending Defines a custom concern interface for adding description properties to Excel exports. This interface should be implemented by export classes that need custom property functionality. No external dependencies required. ```php namespace App\Exports\Concerns; interface WithCustomProperties { /** * * @return string */ public function description(): string; } ``` -------------------------------- ### Store Export on Disk (PHP) Source: https://docs.laravel-excel.com/3.1/exports/exportables Illustrates storing the Excel file on a specified disk (e.g., 's3'). You can also pass additional storage options like visibility. ```php return (new InvoicesExport)->store('invoices.xlsx', 's3'); ``` ```php return (new InvoicesExport)->store('invoices.xlsx', 's3', null, 'private'); ``` -------------------------------- ### Download Export with Setter in Laravel PHP Source: https://docs.laravel-excel.com/3.0/exports/from-query Shows using the forYear setter to chain the year parameter before downloading the export. Provides a fluent interface for customization. Inputs: Year integer. Outputs: Filtered Excel file. ```php return (new InvoicesExport)->forYear(2018)->download('invoices.xlsx'); ``` -------------------------------- ### Register Excel ServiceProvider - PHP Source: https://docs.laravel-excel.com/2 This sample demonstrates how to add the Excel service provider to your Laravel application's `app/config/app.php` file. It's a prerequisite for using the Excel facade and class. The provider enables Excel functionality. ```php Maatwebsite\Excel\ExcelServiceProvider::class, ``` -------------------------------- ### Basic queued export implementation Source: https://docs.laravel-excel.com/3.0/exports/queued Shows how to queue an export using the queue() method. This approach automatically chunks the query and chains multiple jobs that execute in order. Requires an export class that implements FromQuery concern. ```php 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(); } } ``` ```php (new InvoicesExport)->queue('invoices.xlsx'); return back()->withSuccess('Export started!'); ``` -------------------------------- ### FromQuery with Setter Method in Laravel Excel Source: https://docs.laravel-excel.com/3.1/exports/from-query Allows query customization by chaining a setter method like forYear before downloading, providing a fluent interface for parameter injection. Relies on Maatwebsite\Excel and Invoice model. Input via setter call; outputs customized Excel export. Offers flexibility for method chaining but requires the setter to store the parameter. ```php namespace App\Exports; use App\Invoice; use Maatwebsite\Excel\Concerns\FromQuery; use Maatwebsite\Excel\Concerns\Exportable; class InvoicesExport implements FromQuery { use Exportable; public function forYear(int $year) { $this->year = $year; return $this; } public function query() { return Invoice::query()->whereYear('created_at', $this->year); } } ``` ```php return (new InvoicesExport)->forYear(2018)->download('invoices.xlsx'); ``` -------------------------------- ### Configure Global Event Listeners in Laravel Excel Source: https://docs.laravel-excel.com/3.0/exports/extending Illustrates how to set up global event listeners for Laravel Excel exports, allowing the same actions to be performed across all exports in the application. ```PHP Writer::listen(BeforeExport::class, function () { // }); Writer::listen(BeforeWriting::class, function () { // }); Sheet::listen(BeforeSheet::class, function () { // }); Sheet::listen(AfterSheet::class, function () { // }); ``` -------------------------------- ### Download Export using Exportable Trait in PHP Source: https://docs.laravel-excel.com/3.1/architecture This PHP one-liner demonstrates downloading an export directly via the Exportable trait. Depends on a pre-configured export class. Input is a filename; outputs a downloadable file. Simple and magical but bypasses facade or manager. ```php return (new UsersExport)->download('users.xlsx'); ``` -------------------------------- ### Implement Basic FromQuery Export in Laravel PHP Source: https://docs.laravel-excel.com/3.0/exports/from-query This code defines an InvoicesExport class implementing the FromQuery concern from Laravel Excel. It uses a query method to return an Invoice query for chunked execution. Suitable for large exports to avoid memory issues. Dependencies: Maatwebsite\Excel package. Inputs: None. Outputs: Query object. ```php 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(); } } ``` -------------------------------- ### Display or Debug Imported Excel Results Source: https://docs.laravel-excel.com/2.1/import/results Offers methods for inspecting the imported Excel data. `->dump()` displays the results in a readable format, while `->dd()` displays the results and then terminates the script execution. ```php // Dump the results $reader->dump(); // Dump results and die $reader->dd(); ``` -------------------------------- ### Laravel Controller: Export in controller method and using handleExport() Source: https://docs.laravel-excel.com/2.1/export/injection The `ExampleController` shows two ways to use the `UserListExport` class. First, the export logic is defined directly in the `exportUserList` method using a `sheet` callback. Second, it uses the `handleExport()` method, which will dynamically find and use a `UserListExportHandler` to perform the export. ```php class ExampleController extends Controller { public function exportUserList(UserListExport $export) { // work on the export return $export->sheet('sheetName', function($sheet) { })->export('xls'); } } ``` -------------------------------- ### Prepare rows before mapping with prepareRows Source: https://docs.laravel-excel.com/3.1/exports/mapping Prepares rows before they are flattened and mapped by implementing the prepareRows method. Transforms user data by appending '(prepared)' to names before mapping. Called before the map() method during export processing. ```php 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; }); } } ``` -------------------------------- ### Implement Responsable Interface - Laravel Excel Source: https://docs.laravel-excel.com/3.0/exports/exportables Implements Laravel's Responsable interface in export classes for automatic HTTP responses. Requires defining fileName property and allows returning export class directly from controllers. ```php 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(); } } ``` ```php return new InvoicesExport(); ``` -------------------------------- ### Explicitly Queue Excel Import Using Excel Facade Source: https://docs.laravel-excel.com/3.1/imports/queued Demonstrates explicit queuing of Excel imports using the Excel facade's queueImport method. This approach requires the import class to implement ShouldQueue interface. Returns a PendingDispatch instance that allows chaining additional queue jobs. Best for direct import execution without using the Importable trait. ```php Excel::queueImport(new UsersImport, 'users.xlsx'); ``` -------------------------------- ### Basic File Storage in Laravel Excel with PHP Source: https://docs.laravel-excel.com/2.1/export/store Demonstrates storing an Excel file to the default storage path using the ->store() method. It creates an Excel file via Excel::create() and saves it to storage/exports by default as defined in config. Requires the storage folder to be writable; no custom inputs needed beyond filename. ```php Excel::create('Filename', function($excel) { // Set sheets })->store('xls'); ``` -------------------------------- ### Instantiating a PhpSpreadsheet Drawing in PHP Source: https://docs.laravel-excel.com/3.1/exports/drawings This snippet demonstrates how to create a new Drawing object from PhpSpreadsheet and set its basic properties like name, description, path, and height. It requires the PhpSpreadsheet library and a local image file. The output is a configured Drawing instance ready for worksheet addition; no coordinates are set here, which is done later. ```php $drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing(); $drawing->setName('Logo'); $drawing->setDescription('This is my logo'); $drawing->setPath(public_path('/img/logo.jpg')); $drawing->setHeight(90); ``` -------------------------------- ### Limit or Skip Columns During Excel Import Source: https://docs.laravel-excel.com/2.1/import/results Provides functionality to control the number of columns processed from an Excel sheet. `->takeColumns()` or `->limitColumns()` specify the number of columns to include, while `->skipColumns()` or `->limitColumns(false, x)` skip a specified number of columns from the beginning. ```php // You can either use ->takeColumns() $reader->takeColumns(10); // Or ->limitColumns() $reader->limitColumns(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); ``` -------------------------------- ### Localizing Queued Export Source: https://docs.laravel-excel.com/3.1/exports/queued Demonstrates how to localize a queued export by implementing the HasLocalePreference contract and specifying the preferred locale. ```PHP 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; } } ``` -------------------------------- ### Implement Exportable Trait - Laravel Excel Source: https://docs.laravel-excel.com/3.0/exports/exportables Creates an exportable class using Laravel Excel's Exportable trait and FromCollection interface. This allows direct downloading without facade usage. Requires Maatwebsite\Excel package and an Invoice model. ```php 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(); } } ``` -------------------------------- ### Set Excel File Properties Source: https://docs.laravel-excel.com/2.1/export Illustrates how to set various properties of an Excel file during its creation using chained or separate method calls within the callback. Properties include title, creator, company, and description. ```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'); }); ``` -------------------------------- ### Combine chunk reading with batch inserts in Laravel Excel Source: https://docs.laravel-excel.com/3.1/imports/chunk-reading Implementation combining WithChunkReading and WithBatchInserts concerns for optimal performance. Both chunkSize and batchSize methods return 1000, allowing for efficient database insertion while maintaining controlled memory usage. This approach provides the best balance between time and memory consumption. ```php namespace App\Imports; use App\User; use Maatwebsite\Excel\Concerns\ToModel; use Maatwebsite\Excel\Concerns\WithBatchInserts; use Maatwebsite\Excel\Concerns\WithChunkReading; class UsersImport implements ToModel, WithBatchInserts, WithChunkReading { public function model(array $row) { return new User([ 'name' => $row[0], ]); } public function batchSize(): int { return 1000; } public function chunkSize(): int { return 1000; } } ``` -------------------------------- ### Perform Direct Import with PHP Source: https://docs.laravel-excel.com/3.1/imports/importables Demonstrates how to initiate an Excel import directly using an instance of the `UsersImport` class. This method is suitable for smaller files or when immediate processing is required. ```php (new UsersImport)->import('users.xlsx', 'local', \Maatwebsite\Excel\Excel::XLSX); ``` -------------------------------- ### Implement FromQuery Export with Constructor Parameter in Laravel PHP Source: https://docs.laravel-excel.com/3.0/exports/from-query This version of InvoicesExport accepts a year in the constructor to filter invoices by creation year. Enables customizable exports via dependency injection. Dependencies: Maatwebsite\Excel. Inputs: Integer year. Outputs: Filtered query object. Limitations: Requires year value at instantiation. ```php 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); } } ``` -------------------------------- ### Export with Responsable Interface (PHP) Source: https://docs.laravel-excel.com/3.1/exports/exportables Demonstrates using the `Responsable` interface to simplify the export process. When implementing `Responsable`, you need to define the `fileName` property, and optionally `writerType` and `headers` within the export class. ```php namespace App\Exports;\n\nuse App\Invoice;\nuse Maatwebsite\Excel\Excel;\nuse Illuminate\Contracts\Support\Responsable;\nuse Maatwebsite\Excel\Concerns\FromCollection;\nuse Maatwebsite\Excel\Concerns\Exportable;\n\nclass InvoicesExport implements FromCollection, Responsable\n{ use Exportable;\n \n /**\n * It's required to define the fileName within\n * the export class when making use of Responsable.\n */\n private $fileName = 'invoices.xlsx';\n \n /**\n * Optional Writer Type\n */\n private $writerType = Excel::XLSX;\n \n /**\n * Optional headers\n */\n private $headers = [ 'Content-Type' => 'text/csv',\n ];\n\n public function collection()\n { return Invoice::all();\n }\n\n} ``` ```php return new InvoicesExport(); ``` -------------------------------- ### FromQuery with Constructor Parameter in Laravel Excel Source: https://docs.laravel-excel.com/3.1/exports/from-query Customizes the export query by passing parameters like year via the class constructor, filtering the query accordingly for targeted exports. Depends on Invoice model and Maatwebsite\Excel. Input is the year integer; outputs filtered Excel file. Suitable for dynamic exports but requires instantiation with parameters. ```php namespace App\Exports; use App\Invoice; use Maatwebsite\Excel\Concerns\FromQuery; use Maatwebsite\Excel\Concerns\Exportable; class InvoicesExport implements FromQuery { use Exportable; public function __construct(int $year) { $this->year = $year; } public function query() { return Invoice::query()->whereYear('created_at', $this->year); } } ``` ```php return (new InvoicesExport(2018))->download('invoices.xlsx'); ``` -------------------------------- ### Configuring Global Event Listeners in PHP Source: https://docs.laravel-excel.com/3.1/exports/extending This PHP code demonstrates setting global event listeners, typically in AppServiceProvider's register method, to apply custom behavior to all exports in the application. It listens to BeforeExport, BeforeWriting, BeforeSheet, and AfterSheet events on Writer or Sheet classes. Global listeners execute the same actions across multiple export processes. ```php Writer::listen(BeforeExport::class, function () { // }); Writer::listen(BeforeWriting::class, function () { // }); Sheet::listen(BeforeSheet::class, function () { // }); Sheet::listen(AfterSheet::class, function () { // }); ``` -------------------------------- ### Prompt user for export filename in Laravel Excel Source: https://docs.laravel-excel.com/nova/1.x/exports/interactions Uses the askForFilename() method to let users specify their own export filename. Defaults to .xlsx if no extension is provided. Can accept a custom prompt message as parameter. ```PHP public function actions(Request $request) { return [ (new DownloadExcel())->askForFilename() ]; } ``` ```PHP public function actions(Request $request) { return [ (new DownloadExcel())->askForFilename(__('Filename for your export')) ]; } ``` -------------------------------- ### Implementing WithProgressBar Concern in Laravel Excel Import Class - PHP Source: https://docs.laravel-excel.com/3.1/imports/progress-bar This PHP code defines an import class that implements the ToModel and WithProgressBar concerns to import user data from an Excel file with a progress bar. It uses the Importable trait for functionality and creates User models from rows containing name, email, and password. Requires the Maatwebsite\Excel package and assumes a User model exists. ```php $row[0], 'email' => $row[1], 'password' => Hash::make($row[2]), ]); } } ``` -------------------------------- ### Download as XML in Laravel Excel Source: https://docs.laravel-excel.com/3.0/exports/export-formats Illustrates exporting data to an XML file explicitly using Laravel Excel's `download` method. This is useful for structured data exchange. ```php (new InvoicesExport)->download('invoices.xml', \Maatwebsite\Excel\Excel::XML); ``` -------------------------------- ### Handle Import Failures with ImportFailed Event Source: https://docs.laravel-excel.com/3.1/imports/queued Implements failure handling for queued imports using the ImportFailed event. The class uses WithEvents concern to register event handlers and sends notifications when imports fail. Accepts a User instance in constructor to identify who should be notified. Useful for monitoring import job statuses and alerting users. ```php namespace App\Imports; use App\User; use App\Notifications\ImportHasFailedNotification; use Maatwebsite\Excel\Concerns\ToModel; use Maatwebsite\Excel\Concerns\WithEvents; use Maatwebsite\Excel\Events\ImportFailed; use Illuminate\Contracts\Queue\ShouldQueue; use Maatwebsite\Excel\Concerns\WithChunkReading; class UsersImport implements ToModel, WithChunkReading, ShouldQueue, WithEvents { public function __construct(User $importedBy) { $this->importedBy = $importedBy; } public function registerEvents(): array { return [ ImportFailed::class => function(ImportFailed $event) { $this->importedBy->notify(new ImportHasFailedNotification); }, ]; } } ``` -------------------------------- ### Queue Excel Import with PHP Source: https://docs.laravel-excel.com/3.1/imports/importables Illustrates how to queue an Excel import using the `UsersImport` class. Queuing is recommended for large files to avoid blocking the main application thread and improve user experience. ```php (new UsersImport)->queue('users.xlsx'); ``` -------------------------------- ### Job Middleware in Queued Export Source: https://docs.laravel-excel.com/3.1/exports/queued Illustrates how to attach job middleware to a queued export in Laravel 6+, using the middleware method in the export class. ```PHP namespace App\Exports; use App\Jobs\Middleware\RateLimited; use Maatwebsite\Excel\Concerns\Exportable; use Maatwebsite\Excel\Concerns\FromQuery; class ExportClass implements FromQuery { use Exportable; public function middleware() { return [new RateLimited]; } public function query() { // ... } } ``` -------------------------------- ### Iterate Through Imported Excel Data Source: https://docs.laravel-excel.com/2.1/import/results Provides methods for looping through the imported Excel data. `->each()` allows iteration over sheets and rows, with nested callbacks for processing each level. Direct `foreach` loops are also supported. ```php // Loop through all sheets $reader->each(function($sheet) { // Loop through all rows $sheet->each(function($row) { }); }); ``` -------------------------------- ### Configuring CSV Settings in ExcelFile PHP Source: https://docs.laravel-excel.com/2.1/import/injection This class sets CSV-specific settings like delimiter and enclosure for proper parsing. It requires the Maatwebsite Excel package. Inputs are none; outputs are configured import settings. Limitations apply to CSV-specific characters that may conflict. ```PHP class UserListImport extends \Maatwebsite\Excel\Files\ExcelFile { protected $delimiter = ','; protected $enclosure = '"'; protected $lineEnding = '\r\n'; } ``` -------------------------------- ### Import Interface Dependency Injection - PHP Source: https://docs.laravel-excel.com/3.1/architecture Demonstrates how to inject the Maatwebsite\Excel\Importer interface into a controller constructor for decoupling from concrete implementation. This approach enables easier unit testing and follows dependency inversion principle. The imported file is processed using UsersImport class. ```php 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'); } } ``` -------------------------------- ### Store Excel Exports on Disk in PHP Source: https://docs.laravel-excel.com/3.0/exports/store This PHP code snippet shows how to use the Laravel Excel package to store export files on disk. It depends on Laravel framework and the maatwebsite/excel package. The function demonstrates storing on default disk, specific disks like S3, and with defined writer types; outputs are stored files, no return values shown. ```php 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); } ``` -------------------------------- ### Format Excel columns with Laravel Excel Source: https://docs.laravel-excel.com/2.1/export/format Demonstrates how to format Excel columns using Laravel Excel's setColumnFormat method. Supports percentage formatting, leading zeros, and multiple column formats. Requires Laravel Excel package. ```PHP // 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' )); ``` -------------------------------- ### Download as XLS in Laravel Excel Source: https://docs.laravel-excel.com/3.0/exports/export-formats Demonstrates downloading an export as an XLS file, an older Excel format, using Laravel Excel. The `download` method allows explicit specification of the XLS format. ```php (new InvoicesExport)->download('invoices.xls', \Maatwebsite\Excel\Excel::XLS); ``` -------------------------------- ### Export using Contract Interface in PHP Source: https://docs.laravel-excel.com/3.1/architecture This PHP controller decouples the exporter using the Exporter interface for unit testing. It requires the Maatwebsite Excel package. Input is an export request; outputs a downloadable file. Facilitates mocking in tests but relies on interface implementation. ```php 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'); } } ``` -------------------------------- ### Implement Exportable Trait in PHP Source: https://docs.laravel-excel.com/3.1/architecture This PHP export class uses the Exportable trait for direct downloading. It requires the Maatwebsite Excel package and a model. Input is none; enables direct method calls on the object. Adds magic methods but may reduce explicitness. ```php 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(); } } ``` -------------------------------- ### Select multiple sheets with Laravel Excel Source: https://docs.laravel-excel.com/2.1/import/select Loads several sheets by providing their names as arguments to selectSheets. Accepts multiple string parameters for each sheet name. Returns data for each specified sheet. ```php Excel::selectSheets('sheet1', 'sheet2')->load(); ``` -------------------------------- ### Creating Monthly Invoice Sheet with FromQuery and WithTitle in Laravel Excel Source: https://docs.laravel-excel.com/3.1/exports/multiple-sheets This sheet class implements FromQuery to fetch invoices for a specific month and year using Eloquent queries, and WithTitle to name the sheet. It depends on Laravel's query builder and the Invoice model. Inputs are year and month integers via constructor; outputs a query builder instance and sheet title string. Limitations: Assumes 'created_at' timestamp column; no additional styling or mapping unless other concerns are added. ```php 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; } } ``` -------------------------------- ### Specifying Export Disk in Laravel PHP Source: https://docs.laravel-excel.com/nova/1.x/exports/customizations The withDisk() method sets the storage disk for the export, defaulting to 'default', and supports any disk configured in config/filesystems.php. It takes a string parameter for the disk name. This is useful for custom storage locations. ```php /** * Get the actions available for the resource. * * @param \Illuminate\Http\Request $request * * @return array */ public function actions(Request $request) { return [ (new ExportToExcel)->withDisk('my-custom-disk'), ]; } ``` -------------------------------- ### Registering Events in PHP Export Class Source: https://docs.laravel-excel.com/3.1/exports/extending This PHP code implements the WithEvents concern in an export class to register event listeners for various stages of the Excel export process. It uses closures, array-callable static methods, and invokable classes for handling BeforeExport, BeforeWriting, and BeforeSheet events. Be aware that closures are not suitable for queued exports as they cannot be serialized. ```php 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) { // } } ``` -------------------------------- ### Download as XLSX in Laravel Excel Source: https://docs.laravel-excel.com/3.0/exports/export-formats Demonstrates how to explicitly download an export as an XLSX file using the `download` method in Laravel Excel. This method takes the filename and the desired export format constant as arguments. ```php (new InvoicesExport)->download('invoices.xlsx', \Maatwebsite\Excel\Excel::XLSX); ``` -------------------------------- ### Laravel ExportHandler implementation Source: https://docs.laravel-excel.com/2.1/export/injection The `UserListExportHandler` class implements the `ExportHandler` interface. Its `handle` method contains the export logic, which is then called by the `handleExport()` method in the controller. This decouples the export logic from the controller, leading to cleaner and more manageable code. ```php class UserListExportHandler implements \Maatwebsite\Excel\Files\ExportHandler { public function handle(UserListExport $export) { // work on the export return $export->sheet('sheetName', function($sheet) { })->export('xls'); } } ``` -------------------------------- ### Download as SLK in Laravel Excel Source: https://docs.laravel-excel.com/3.0/exports/export-formats Shows how to explicitly download an export in SLK (Symbolic Link) format using Laravel Excel. This format is often used for data exchange between spreadsheet programs. ```php (new InvoicesExport)->download('invoices.slk', \Maatwebsite\Excel\Excel::SLK); ``` -------------------------------- ### Dynamic File Retrieval in ExcelFile PHP Source: https://docs.laravel-excel.com/2.1/import/injection This method overrides getFile() to dynamically handle user-uploaded files in Laravel. It requires Laravel's Input facade and an upload method. Inputs are user file from 'report'; outputs are the processed filename. Limitations may include file validation or storage directory constraints. ```PHP public function getFile() { // Import a user provided file $file = Input::file('report'); $filename = $this->doSomethingLikeUpload($file); // Return it's location return $filename; } ``` -------------------------------- ### Implement Queued Excel Import with Chunk Reading Source: https://docs.laravel-excel.com/3.1/imports/queued Defines a Laravel Excel import class that implements queued processing with chunk reading. The class uses ShouldQueue contract and WithChunkReading concern to process large files in 1000-row chunks. XLS files cannot be queued due to non-UTF8 character limitations. Requires implementation of model() and chunkSize() methods. ```php namespace App\Imports; use App\User; use Maatwebsite\Excel\Concerns\ToModel; use Illuminate\Contracts\Queue\ShouldQueue; use Maatwebsite\Excel\Concerns\WithChunkReading; class UsersImport implements ToModel, WithChunkReading, ShouldQueue { public function model(array $row) { return new User([ 'name' => $row[0], ]); } public function chunkSize(): int { return 1000; } } ``` -------------------------------- ### Download Excel Export in Laravel Controller Source: https://docs.laravel-excel.com/3.1/exports/from-generator This PHP method in a Laravel controller handles downloading the Excel export. It instantiates the DataExport class and calls the download method with a filename. Requires the DataExport class and Laravel framework. Outputs an Excel file response. No limitations specified. ```PHP download('data.xlsx'); } ?> ``` -------------------------------- ### Export Data with Exportable Trait (PHP) Source: https://docs.laravel-excel.com/3.1/exports/exportables This code snippet demonstrates using the Exportable trait from Laravel Excel to create an export class. The class implements the `FromCollection` interface to fetch data from a model (Invoice in this case) and then is used to generate an Excel file. This method utilizes the facade for downloading. ```php namespace App\Exports;\n\nuse App\Invoice;\nuse Maatwebsite\Excel\Concerns\FromCollection;\nuse Maatwebsite\Excel\Concerns\Exportable;\n\nclass InvoicesExport implements FromCollection\n{ use Exportable;\n\n public function collection()\n { return Invoice::all();\n }\n\n} ``` -------------------------------- ### Store and Export in Laravel Excel with PHP Source: https://docs.laravel-excel.com/2.1/export/store Combines storing the file on the server and exporting it directly using chained methods. This allows saving to storage and downloading simultaneously. Outputs the file to the user; dependencies include a writable storage folder. ```php ->store('xls')->export('xls'); ``` -------------------------------- ### Custom Queues in Laravel Excel Source: https://docs.laravel-excel.com/3.1/exports/queued Shows how to specify a custom queue for a queued export in Laravel Excel, using the onQueue method for Laravel 8+ and allOnQueue for older versions. ```PHP (new InvoicesExport)->queue('invoices.xlsx')->onQueue('exports'); ``` ```PHP (new InvoicesExport)->queue('invoices.xlsx')->allOnQueue('exports'); ``` -------------------------------- ### Configure Global Event Listeners in Laravel Excel PHP Source: https://docs.laravel-excel.com/3.1/architecture/objects This PHP code shows how to set up global event listeners in Laravel Excel using Writer::listen, applying to all exports. It requires the Maatwebsite\Excel package. The method takes an event class and a closure handler. Useful for consistent global behavior, but may impact performance or override specific configurations. ```PHP Writer::listen(BeforeExport::class, function () { // Do something before any export process starts. }); ``` -------------------------------- ### Create Custom Macro in Laravel Excel (PHP) Source: https://docs.laravel-excel.com/3.1/exports/extending This code snippet shows how to define a macro in Laravel Excel to add custom methods to a spreadsheet instance, specifically styling cells. It uses the `Sheet::macro()` method and requires the Maatwebsite Excel package. The macro accepts a cell range and style array as input and applies the style to the specified range. ```PHP use \Maatwebsite\Excel\Sheet; Sheet::macro('styleCells', function (Sheet $sheet, string $cellRange, array $style) { $sheet->getDelegate()->getStyle($cellRange)->applyFromArray($style); }); ``` -------------------------------- ### Download as HTML in Laravel Excel Source: https://docs.laravel-excel.com/3.0/exports/export-formats Shows how to explicitly download an export as an HTML file using Laravel Excel. This converts spreadsheet data into a simple HTML table. ```php (new InvoicesExport)->download('invoices.html', \Maatwebsite\Excel\Excel::HTML); ``` -------------------------------- ### Configure Custom Queue for Excel Imports Source: https://docs.laravel-excel.com/3.1/imports/queued Shows how to specify a custom queue for queued imports using allOnQueue() method. This allows organizing imports into separate queue channels for better resource management. The queue name must exist in your Laravel queue configuration. Useful for isolating import traffic from other queue operations. ```php (new UsersImport)->queue('users.xlsx')->allOnQueue('imports'); ``` -------------------------------- ### Implementing WithMapping for User Data Export in PHP Source: https://docs.laravel-excel.com/nova/1.x/exports/customizations This code defines an ExportUsers action that extends DownloadExcel and implements the WithMapping concern to transform user data into an array for Excel export. It maps the user's name, email, and converts the created_at timestamp to an Excel date format using PhpSpreadsheet. Dependencies include Laravel Nova Excel, PhpSpreadsheet, and the App\User model; it processes a User instance as input and returns an array per row, with limitations on direct date handling without additional formatting. ```php name, $user->email, Date::dateTimeToExcel($user->created_at), ]; } } ``` -------------------------------- ### Register Custom Concern in Service Provider in PHP Source: https://docs.laravel-excel.com/3.1/exports/extending Registers the WithCustomProperties concern with its handler in a Laravel service provider. ```php public function register() { Excel::extend(WithCustomProperties::class, new WithCustomPropertiesHandler); } ``` ```php public function register() { Excel::extend(WithCustomProperties::class, function(WithCustomProperties $exportable, Writer $writer) { $writer->getDelegate()->getProperties()->setDescription($exportable->description()); }); } ``` ```php public function register() { Excel::extend(WithCustomProperties::class, new WithCustomPropertiesHandler, BeforeExport::class); } ``` -------------------------------- ### Create a Sheet in Laravel Excel Source: https://docs.laravel-excel.com/2.1/export/sheets Demonstrates how to create a single sheet within a new Excel file using Laravel Excel. It utilizes a callback function to define the sheet's name and allows for subsequent manipulation within the closure. The output is exported as an XLS file. ```php Excel::create('Filename', function($excel) { $excel->sheet('Sheetname', function($sheet) { // Sheet manipulation }); })->export('xls'); ``` -------------------------------- ### Register concern handler with closure Source: https://docs.laravel-excel.com/3.0/exports/extending Alternative registration method using a closure instead of a separate handler class. Provides the same functionality as the class-based approach but defined inline. Useful for simple concern handlers. ```php public function register() { Excel::extend(WithCustomProperties::class, function(WithCustomProperties $exportable, Writer $writer) { $writer->getDelegate()->getProperties()->setDescription($exportable->description()); }); } ``` -------------------------------- ### Applying Default Styles to Workbook in PHP Source: https://docs.laravel-excel.com/3.1/exports/column-formatting The WithDefaultStyles concern (v3.1.40+) sets styles for the entire workbook via defaultStyles method, using fluent or array returns. Depends on Maatwebsite\Excel, PhpOffice\PhpSpreadsheet\Style\Style, Fill, and Color. Inputs are style configurations; outputs workbook-wide styles. Limitation: Applies uniformly, overriding specific styles if not careful. ```PHP 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], ], ]; } } ``` -------------------------------- ### Testing Dynamic Excel Filename in Laravel PHP Source: https://docs.laravel-excel.com/3.1/exports/testing This PHP test covers asserting stored Excel exports with dynamic file names using regular expressions. It uses the Excel facade and fakes for flexible matching. The test enables regex matching to assert dynamic names like 'invoices_2019.xlsx'. Restrictions require the regex to match exactly one file, or the test will fail. ```php /** * @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. ``` -------------------------------- ### Prompt user for writer type in Laravel Excel Source: https://docs.laravel-excel.com/nova/1.x/exports/interactions Uses the askForWriterType() method to let users choose the export file format. Can accept custom options array and prompt message as parameters. ```PHP public function actions(Request $request) { return [ (new DownloadExcel())->askForWriterType() ]; } ``` ```PHP public function actions(Request $request) { return [ (new DownloadExcel)->askForWriterType([ \Maatwebsite\Excel\Excel::CSV => __('Csv document'), ]) ]; } ``` ```PHP public function actions(Request $request) { return [ (new DownloadExcel())->askForWriterType(null, __('Filename for your export')) ]; } ``` -------------------------------- ### Retrieve Workbook and Sheet Titles Source: https://docs.laravel-excel.com/2.1/import/results Fetches the title of the workbook using `->getTitle()` and also retrieves the title of each sheet within the workbook during iteration. ```php // Get workbook title $workbookTitle = $reader->getTitle(); foreach($reader as $sheet) { // get sheet title $sheetTitle = $sheet->getTitle(); } ``` -------------------------------- ### Implement FromQuery Export with Setter in Laravel PHP Source: https://docs.laravel-excel.com/3.0/exports/from-query This implementation adds a forYear setter method for fluent year parameter setting. Allows chaining to customize the export query. Dependencies: Maatwebsite\Excel. Inputs: Integer year. Outputs: Filtered query with method chaining. ```php 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); } } ``` -------------------------------- ### Import Excel by Config in PHP (Laravel) Source: https://docs.laravel-excel.com/2.1/import/config This snippet demonstrates how to import an Excel file using `byConfig()` in Laravel. It reads an Excel file and applies configuration settings defined in 'excel::import.sheets' to map cell data to specific getters like 'firstname'. This is useful for files without standard headers. The `byConfig` method iterates through all sheets by default. ```php 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; }); ``` -------------------------------- ### Register Events in Laravel Excel Source: https://docs.laravel-excel.com/3.0/exports/extending Demonstrates how to register event listeners in a Laravel Excel export class using the WithEvents concern. Events can be handled by closures, static methods, or invokable classes. ```PHP 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) { // } } ``` -------------------------------- ### Export to Excel5 (XLS) Format in Laravel Excel Source: https://docs.laravel-excel.com/2.1/export/export Creates an Excel file and exports it to Excel5 (XLS) format using the ->export('xls') method to save or ->download('xls') for direct download. The Excel::create() method initializes the sheet with a filename and callback function. No additional dependencies are required for this format. ```php Excel::create('Filename', function($excel) { })->export('xls'); // or ->download('xls'); ``` -------------------------------- ### Defining ExcelFile Class in PHP Laravel Source: https://docs.laravel-excel.com/2.1/import/injection This code defines a custom ExcelFile class extending Maatwebsite's ExcelFile to handle CSV imports with file path and filters. It depends on the Laravel framework and Maatwebsite Excel package. Inputs are none in static case; outputs are the file location and filters array. Limitations include static file path placement. ```PHP class UserListImport extends \Maatwebsite\Excel\Files\ExcelFile { public function getFile() { return storage_path('exports') . '/file.csv'; } public function getFilters() { return [ 'chunk' ]; } } ``` -------------------------------- ### Download as CSV in Laravel Excel Source: https://docs.laravel-excel.com/3.0/exports/export-formats Shows how to explicitly download an export as a CSV file using Laravel Excel's `download` method. This is useful for ensuring the correct format regardless of the file extension. ```php (new InvoicesExport)->download('invoices.csv', \Maatwebsite\Excel\Excel::CSV); ``` -------------------------------- ### Export via Dependency Injection in PHP Source: https://docs.laravel-excel.com/3.1/architecture This PHP controller uses dependency injection to handle Excel exports, injecting the Excel manager via constructor and method. It depends on the Maatwebsite Excel package and an export object. Input is an export request; outputs a downloadable XLSX file. Suitable for controllers; ensures testability. ```php 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'); } } ``` -------------------------------- ### Implement Batch Upserts in Laravel Excel Imports Source: https://docs.laravel-excel.com/3.1/imports/batch-inserts Allows for efficient updating of existing records during import by using Laravel's `upsert` method. Requires implementing `WithBatchInserts` and `WithUpserts` concerns, and defining a `uniqueBy` method to specify the unique identifier for record matching. Database indexes may be required for certain databases. ```php 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'; } } ``` -------------------------------- ### Setting Workbook Background Color with WithBackgroundColor in PHP Source: https://docs.laravel-excel.com/3.1/exports/column-formatting WithBackgroundColor (v3.1.40+) configures workbook background via backgroundColor method, supporting RGB, Color instances, or arrays. Requires Maatwebsite\Excel, PhpOffice\PhpSpreadsheet\Style\Fill, Style, Color, and Worksheet. Inputs are color definitions; outputs colored workbook background. Limitation: Fill type auto-sets to solid for Color instances. ```PHP 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], ]; } } ``` -------------------------------- ### Controller method to execute Excel import Source: https://docs.laravel-excel.com/3.1/imports/collection This snippet demonstrates how to call the import from a controller method. The Excel facade's import method is used with the custom import class and file path. ```PHP public function import() { Excel::import(new UsersImport, 'users.xlsx'); } ``` -------------------------------- ### Add Multiple Charts to Export Class Source: https://docs.laravel-excel.com/3.1/exports/charts Demonstrates how to add multiple charts to a single worksheet by returning an array of Chart instances from the charts() method. This implementation creates two different charts and returns them as an array, allowing multiple visualizations in the same export. ```php queue('users.xlsx'); ``` -------------------------------- ### Basic Excel Import to Eloquent Model Source: https://docs.laravel-excel.com/3.1/imports/model Implements ToModel concern to import Excel rows into Eloquent models. The model() method processes each row and returns a new model instance. Each row results in at least one save operation and fires model events. ```php namespace App\Imports; use App\User; use Maatwebsite\Excel\Concerns\ToModel; class UsersImport implements ToModel { public function model(array $row) { return new User([ 'name' => $row[0], ]); } } ``` -------------------------------- ### Customize CSV Settings in Laravel Excel Source: https://docs.laravel-excel.com/3.1/exports/settings Shows how to customize CSV export settings such as delimiter, BOM usage, and output encoding by implementing the WithCustomCsvSettings interface. The settings are returned as an associative array. ```PHP namespace App\Exports; use Maatwebsite\Excel\Concerns\WithCustomCsvSettings; class InvoicesExport implements WithCustomCsvSettings { public function getCsvSettings(): array { return [ 'delimiter' => ';', 'use_bom' => false, 'output_encoding' => 'ISO-8859-1', ]; } } ``` -------------------------------- ### Chain Additional Jobs After Queued Import Completion Source: https://docs.laravel-excel.com/3.1/imports/queued Demonstrates chaining additional queue jobs after successful import completion using the chain() method on PendingDispatch. The chained jobs only execute if all import jobs complete successfully. This pattern allows post-import processing like sending notifications or triggering follow-up tasks. The import must return PendingDispatch for chaining to work. ```php (new UsersImport)->queue('users.xlsx')->chain([ new NotifyUserOfCompletedImport(request()->user()), ]); ``` -------------------------------- ### Implement Batch Inserts in Laravel Excel Imports Source: https://docs.laravel-excel.com/3.1/imports/batch-inserts Enables importing large files by inserting models in batches, significantly reducing the number of database queries. This concern works exclusively with the `ToModel` concern and requires specifying a batch size for optimal performance. ```php namespace App\Imports; use App\User; use Maatwebsite\Excel\Concerns\ToModel; use Maatwebsite\Excel\Concerns\WithBatchInserts; class UsersImport implements ToModel, WithBatchInserts { public function model(array $row) { return new User([ 'name' => $row[0], ]); } public function batchSize(): int { return 1000; } } ``` -------------------------------- ### Laravel Excel - Import Multiple Sheets Source: https://docs.laravel-excel.com/3.1/imports/multiple-sheets Demonstrates how to import multiple sheets from an Excel file using the `WithMultipleSheets` concern. Each sheet is associated with a corresponding import object, and the order matters. ```php namespace App\Imports;\n\nuse Maatwebsite\Excel\Concerns\WithMultipleSheets;\n\nclass UsersImport implements WithMultipleSheets\n{ \n public function sheets(): array\n {\n return [\n new FirstSheetImport()\n ];\n }\n} ``` -------------------------------- ### Implicit Queued Export in Laravel Source: https://docs.laravel-excel.com/3.1/exports/queued Shows how to mark an export as queued implicitly by implementing Laravel's ShouldQueue contract. The store method will automatically queue the export. ```PHP 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(); } } ``` ```PHP (new InvoicesExport)->store('invoices.xlsx'); ``` -------------------------------- ### Multiple Column Name Mapping Source: https://docs.laravel-excel.com/3.1/imports/model Uses null coalescing operator (??) to handle Excel files with variable column names. Checks multiple possible column names in priority order, falling back to alternatives if primary column doesn't exist. ```php public function model(array $row) { return new User([ 'name' => $row['client_name'] ?? $row['client'] ?? $row['name'] ?? null ]); } ``` -------------------------------- ### Handling Imports via Handler in Laravel PHP Source: https://docs.laravel-excel.com/2.1/import/injection This controller uses handleImport() to decouple logic via a handler class. It depends on the import class with handler capability. Inputs are the import object; outputs are handled via the handler. Limitations include reliance on naming conventions for handlers. ```PHP class ExampleController extends Controller { public function importUserList(UserListImport $import) { // Handle the import $import->handleImport(); } } ``` -------------------------------- ### Implementing ImportHandler Class in PHP Laravel Source: https://docs.laravel-excel.com/2.1/import/injection This handler class processes the import results, decoupling from controller. It implements Maatwebsite's ImportHandler interface. Inputs are the import object; outputs are processed results. Limitations include dependency on the main import class structure. ```PHP class UserListImportHandler implements \Maatwebsite\Excel\Files\ImportHandler { public function handle(UserListImport $import) { // get the results $results = $import->get(); } } ``` -------------------------------- ### Configure default value binder in Laravel Excel config (PHP) Source: https://docs.laravel-excel.com/3.1/imports/custom-formatting-values Provides the configuration array entry for setting a global default value binder in the config/excel.php file. By assigning Maatwebsite\Excel\DefaultValueBinder::class, all imports will use this binder unless overridden locally. Adjust the 'default' key to customize binder behavior across the application. ```PHP 'value_binder' => [ 'default' => Maatwebsite\Excel\DefaultValueBinder::class, ], ``` -------------------------------- ### Add setCreator macro to Writer Source: https://docs.laravel-excel.com/3.0/exports/extending Extends the Writer class with a custom macro to set document creator property. Uses the underlying PhpSpreadsheet getProperties() method. Macro provides a convenient shortcut for setting creator information. ```php use \Maatwebsite\Excel\Writer; Writer::macro('setCreator', function (Writer $writer, string $creator) { $writer->getDelegate()->getProperties()->setCreator($creator); }); ``` -------------------------------- ### Direct Excel Import with Eloquent Macro in Laravel PHP Source: https://docs.laravel-excel.com/3.1/imports/model This PHP code uses an Eloquent macro to directly import an Excel file into the User model, assuming headers match database columns. It requires the MaatwebsiteExcel package and a properly formatted Excel file with headings. This approach is simple but limited to direct column mappings without custom logic. ```php User::query()->import('import-users-with-headings.xlsx'); ``` -------------------------------- ### Custom Path File Storage in Laravel Excel with PHP Source: https://docs.laravel-excel.com/2.1/export/store Shows how to store the Excel file to a custom storage path by providing the path as the second parameter. Useful for organizing files per client or project. The directory must be writable, and it returns the file path; inputs include extension and path. ```php ->store('xls', storage_path('excel/exports')); ``` -------------------------------- ### Configure Input Encoding Source: https://docs.laravel-excel.com/2.1/import/extra Sets the character encoding for reading CSV files, most commonly used for UTF-8 files. The encoding can be specified either as the third parameter when using a closure, or as the second parameter when not using a closure. This ensures proper handling of special characters and international text. ```php // 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'); ``` -------------------------------- ### Select a specific sheet with Laravel Excel Source: https://docs.laravel-excel.com/2.1/import/select Loads only the specified sheet using selectSheets. Requires the Excel facade. Input is the sheet name as a string and the method returns the loaded sheet data. This approach is limited to selecting one sheet at a time. ```php Excel::selectSheets('sheet1')->load(); ``` -------------------------------- ### Limit or Skip Rows During Excel Import Source: https://docs.laravel-excel.com/2.1/import/results Allows control over the number of rows processed from an Excel sheet. `->takeRows()` or `->limitRows()` specify the number of rows to include, while `->skipRows()` or `->limitRows(false, x)` skip a specified number of rows from the beginning. ```php // You can either use ->takeRows() $reader->takeRows(10); // Or ->limitRows() $reader->limitRows(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); ``` -------------------------------- ### Prepend row in Laravel Excel Source: https://docs.laravel-excel.com/2.1/export/rows Demonstrates how to add new rows before specific positions or at the beginning of the sheet in Laravel Excel. ```PHP // Add before first row $sheet->prependRow(1, array( 'prepended', 'prepended' )); // Add as very first $sheet->prependRow(array( 'prepended', 'prepended' )); ``` -------------------------------- ### Define multiple sheets for import with WithMultipleSheets interface Source: https://docs.laravel-excel.com/3.1/imports/multiple-sheets Shows a basic UsersImport class implementing WithMultipleSheets to return a list of sheet import classes. This sets up the import to handle multiple worksheets without conditional logic. ```php namespace App\Imports; use Maatwebsite\Excel\Concerns\WithMultipleSheets; class UsersImport implements WithMultipleSheets { public function sheets(): array { return [ new FirstSheetImport(), new SecondSheetImport() ]; } } ``` -------------------------------- ### Download as TSV in Laravel Excel Source: https://docs.laravel-excel.com/3.0/exports/export-formats Illustrates how to explicitly download an export as a TSV (Tab-Separated Values) file using the `download` method in Laravel Excel. This ensures data is correctly delimited for TSV format. ```php (new InvoicesExport)->download('invoices.tsv', \Maatwebsite\Excel\Excel::TSV); ``` -------------------------------- ### Injecting ExcelFile in Laravel Controller Source: https://docs.laravel-excel.com/2.1/import/injection This controller method injects the ExcelFile for importing data in Laravel 5.0 style. It depends on the UserListImport class and Maatwebsite Excel. Inputs are the injected import object; outputs are the import results. Limitations depend on file availability and parsing. ```PHP class ExampleController extends Controller { public function importUserList(UserListImport $import) { // get the results $results = $import->get(); } } ``` -------------------------------- ### Implement Notification Job for Import Completion Source: https://docs.laravel-excel.com/3.1/imports/queued Defines a Laravel queue job that sends notifications after import completion. Uses Queueable and SerializesModels traits for proper queue handling and model serialization. The job can be chained after import jobs and will only execute if all previous jobs succeed. Accepts a User model in constructor to identify the notification recipient. ```php namespace App\Jobs; use App\User; use Illuminate\Bus\Queueable; use Illuminate\Contracts\Queue\ShouldQueue; use Illuminate\Queue\SerializesModels; class NotifyUserOfCompletedImport implements ShouldQueue { use Queueable, SerializesModels; public $user; public function __construct(User $user) { $this->user = $user; } public function handle() { $this->user->notify(new ImportReady()); } } ``` -------------------------------- ### Format Excel columns in Laravel with PHP Source: https://docs.laravel-excel.com/3.0/exports/column-formatting Demonstrates how to format columns in an Excel export using Laravel Excel's WithColumnFormatting concern. Formats date and currency columns. Requires PhpOffice\PhpSpreadsheet for date handling. ```php 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, ]; } } ``` -------------------------------- ### Implement Skipping Duplicates in Laravel Excel Imports Source: https://docs.laravel-excel.com/3.1/imports/batch-inserts Enables ignoring rows that would result in duplicate entries based on primary or unique keys during import. This is achieved by implementing the `WithSkipDuplicates` concern, which utilizes Laravel's `insertOrIgnore` method to prevent errors caused by duplicate data. ```php class UsersImport implements ToModel, WithBatchInserts, WithSkipDuplicates { public function model(array $row) { return new User([ 'name' => $row[0], ]); } public function batchSize(): int { return 1000; } } ``` -------------------------------- ### Download PDF using MPDF in Laravel Excel Source: https://docs.laravel-excel.com/3.0/exports/export-formats Demonstrates how to explicitly download an export as a PDF file using the MPDF library integration within Laravel Excel. This method is suitable for generating complex PDF documents. ```php (new InvoicesExport)->download('invoices.pdf', \Maatwebsite\Excel\Excel::MPDF); ``` -------------------------------- ### Laravel Excel - Selecting Sheets by Name Source: https://docs.laravel-excel.com/3.1/imports/multiple-sheets Illustrates how to select sheets by their name using `WithMultipleSheets`. The sheet name is used as the key to map to an import object. ```php namespace App\Imports;\n\nuse Maatwebsite\Excel\Concerns\WithMultipleSheets;\n\nclass UsersImport implements WithMultipleSheets\n{ \n public function sheets(): array\n { \n return [\n 'Worksheet 1' => new FirstSheetImport(),\n 'Worksheet 2' => new SecondSheetImport(),\n ];\n }\n} ``` -------------------------------- ### Auto Register Event Listeners in Laravel Excel Source: https://docs.laravel-excel.com/3.0/exports/extending Shows how to auto-register event listeners in Laravel Excel using the RegistersEventListeners trait, eliminating the need for manual event registration. ```PHP 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) { // } } ``` -------------------------------- ### Export to Excel2007 (XLSX) Format in Laravel Excel Source: https://docs.laravel-excel.com/2.1/export/export Exports the created Excel file to Excel2007 (XLSX) format via ->export('xlsx') or ->download('xlsx'). This follows the initialization with Excel::create() and supports modern Excel features. No extra packages needed beyond the core Laravel Excel. ```php ->export('xlsx'); // or ->download('xlsx'); ``` -------------------------------- ### Register Events in Laravel Excel PHP Export Class Source: https://docs.laravel-excel.com/3.1/architecture/objects This PHP code demonstrates implementing the WithEvents concern in a Laravel Excel export class to register event listeners like BeforeExport. It requires the Maatwebsite\Excel package and proper namespace. The method returns an array of event mappings with closures for custom logic during export. Limitations include dependency on the package and PHP environment. ```PHP 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. }, ]; } } ``` -------------------------------- ### Laravel Excel - Skipping Unknown Sheets Source: https://docs.laravel-excel.com/3.1/imports/multiple-sheets Demonstrates how to use the `SkipsUnknownSheets` concern to ignore sheets that are not explicitly defined in the `sheets()` method. This prevents `SheetNotFoundException` errors. ```php namespace App\Imports;\n\nuse Maatwebsite\Excel\Concerns\WithMultipleSheets;\nuse Maatwebsite\Excel\Concerns\SkipsUnknownSheets;\n\nclass UsersImport implements WithMultipleSheets, SkipsUnknownSheets\n{ \n public function sheets(): array\n { \n return [\n 'Worksheet 1' => new FirstSheetImport(),\n 'Worksheet 2' => new SecondSheetImport(),\n ];\n }\n \n public function onUnknownSheet($sheetName)\n { \n // E.g. you can log that a sheet was not found.\n info("Sheet {$sheetName} was skipped")\n }\n} ``` -------------------------------- ### Reset Value Binder in PHP Source: https://docs.laravel-excel.com/2.1/import/formatting Shows how to reset the value binder back to the default implementation after using a custom binder. ```php Excel::resetValueBinder(); ``` -------------------------------- ### Implement chunk reading for Laravel Excel imports Source: https://docs.laravel-excel.com/3.1/imports/chunk-reading Implementation of WithChunkReading concern to reduce memory usage when importing large Excel files. The chunkSize method returns 1000 rows per chunk. Memory usage is controlled by processing data in smaller segments rather than loading the entire file. ```php 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; } } ``` -------------------------------- ### Laravel: NewExcelFile class and filename Source: https://docs.laravel-excel.com/2.1/export/injection Defines a class, `UserListExport`, that extends `NewExcelFile` to set the filename for the Excel export. The `getFilename()` method returns the desired filename, which will be used when the file is downloaded. ```php class UserListExport extends \Maatwebsite\Excel\Files\NewExcelFile { public function getFilename() { return 'filename'; } } ``` -------------------------------- ### Importable Trait Implementation - PHP Source: https://docs.laravel-excel.com/3.1/architecture Shows how to use the Importable trait in an Import class to enable direct import functionality without requiring the Excel facade or manager. This trait exposes methods that make it possible to import an Import object directly. The class implements ToCollection concern for processing Excel data. ```php namespace App\Imports; use App\User; use Maatwebsite\Excel\Concerns\ToCollection; use Maatwebsite\Excel\Concerns\Importable; class UsersImport implements ToCollection { use Importable; ... } ``` -------------------------------- ### Track chunk offset in Laravel Excel imports Source: https://docs.laravel-excel.com/3.1/imports/chunk-reading Implementation using RemembersChunkOffset trait to track the offset of the current chunk during import. The getChunkOffset method provides information about the chunk's position in the overall file. This is useful when only the chunk offset information is needed, rather than individual row numbers. ```php 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; } } ``` -------------------------------- ### Prefetching Heading Row with HeadingRowImport in PHP Source: https://docs.laravel-excel.com/3.1/imports/heading-row HeadingRowImport allows extracting only headers for validation before full import. It depends on Maatwebsite\Excel and returns an array of headings per sheet. Inputs are Excel files; outputs are heading arrays; use in controllers for quick checks. ```PHP use Maatwebsite\Excel\HeadingRowImport; class UsersImportController extends Controller { public function import() { $headings = (new HeadingRowImport)->toArray('users.xlsx'); } } ``` -------------------------------- ### Custom-Mapped Excel Import with importAs Macro in Laravel PHP Source: https://docs.laravel-excel.com/3.1/imports/model This PHP code uses the importAs Eloquent macro to import an Excel file with custom row mapping, transforming data before insertion. It depends on the MaatwebsiteExcel package. Inputs include the file path and a mapping closure; outputs are database insertions based on the custom logic. ```php User::query()->importAs('import-users.xlsx', function (array $row) { return [ 'name' => $row[0], 'email' => $row[1], 'password' => 'secret', ]; }); ``` -------------------------------- ### Customize Export Success and Failure Messages Source: https://docs.laravel-excel.com/nova/1.x/exports/customizations Illustrates how to override the default success and failure notifications for `ExportToExcel` and `DownloadExcel` actions using `onSuccess` and `onFailure` callbacks. These callbacks allow for custom messages or actions upon export completion or failure. ```php public function actions(Request $request) { return [ (new ExportToExcel) ->onSuccess(function() { return Action::message('Your export is ready for you! :)'); })->onFailure(function() { return Action::danger('Oh dear! I could not create that export for you :(.'); }), ]; } ``` -------------------------------- ### Add setCreator macro to Laravel Excel Reader Source: https://docs.laravel-excel.com/3.1/imports/extending Defines a macro on the Reader class to set the document creator using the underlying PhpSpreadsheet properties. Requires the Reader class from Maatwebsite\Excel and accepts a string creator name. This macro simplifies repeated creator assignments across imports. ```PHP use \Maatwebsite\Excel\Reader; Reader::macro('setCreator', function (Reader $reader, string $creator) { $reader->getDelegate()->getProperties()->setCreator($creator); }); ``` -------------------------------- ### Create HTML Table Blade Template in Laravel Source: https://docs.laravel-excel.com/3.1/exports/from-view This Blade template generates an HTML table from invoice data, which Laravel Excel converts to an Excel spreadsheet. It depends on Blade templating and a collection of invoices. Input is invoice data; output is HTML table. Limitations include supported attributes and Laravel Blade syntax. ```Blade @foreach($invoices as $invoice) @endforeach
Name Email
{{ $invoice->name }} {{ $invoice->email }}
``` -------------------------------- ### Load Blade view for Excel sheet in Laravel Source: https://docs.laravel-excel.com/2.1/blade/load-view Demonstrates how to load a Blade view for a single sheet in an Excel file. Uses Laravel Excel's `loadView` method to render the view as a sheet. ```PHP Excel::create('New file', function($excel) { $excel->sheet('New sheet', function($sheet) { $sheet->loadView('folder.view'); }); }); ``` -------------------------------- ### Custom queue configuration for exports Source: https://docs.laravel-excel.com/3.0/exports/queued Demonstrates how to specify a custom queue name for queued exports. Uses Laravel's allOnQueue() method to direct all export jobs to a specific queue, which is useful for organizing and prioritizing different types of background jobs. ```php (new InvoicesExport)->queue('invoices.xlsx')->allOnQueue('exports'); ``` -------------------------------- ### Laravel Excel - Import Single Sheet Source: https://docs.laravel-excel.com/3.1/imports/multiple-sheets Demonstrates how to import a single sheet from an Excel file using the `ToCollection` concern. This is the standard approach for importing data into a collection. ```php namespace App\Imports;\n\nuse Illuminate\Support\Collection;\nuse Maatwebsite\Excel\Concerns\ToCollection;\n\nclass FirstSheetImport implements ToCollection\n{ \n public function collection(Collection $rows)\n { \n //\n }\n} ``` -------------------------------- ### Implement Queued Export Action in Laravel Nova Resource (PHP) Source: https://docs.laravel-excel.com/nova/1.x/exports/queued Adds a QueuedExport action to the resource's actions method for handling large exports asynchronously. Requires the Maatwebsite\LaravelNovaExcel\Actions\QueuedExport import. Exports are chunked by default (200 items) and stored in the default storage folder; use withChunkCount() to customize chunk size. Does not support queued downloads, only disk storage. ```php use Maatwebsite\LaravelNovaExcel\Actions\QueuedExport; public function actions(Request $request) { return [ new QueuedExport(), ]; } ``` ```php /** * Get the actions available for the resource. * * @param \Illuminate\Http\Request $request * * @return array */ public function actions(Request $request) { return [ (new QueuedExport)->withChunkCount(1000), ]; } ``` -------------------------------- ### Convert Excel Import Results to Array or Object Source: https://docs.laravel-excel.com/2.1/import/results Converts the imported Excel data into different formats. `->toArray()` returns the data as an array, while `->toObject()` returns it as an object, offering flexibility in data handling. ```php $reader->toArray(); ``` ```php $reader->toObject(); ``` -------------------------------- ### Skip Duplicate Model Imports Source: https://docs.laravel-excel.com/3.1/imports/model Implements WithSkipDuplicates concern to automatically skip rows that would create duplicate models based on primary or unique key constraints. Uses Laravel's insertOrIgnore method internally. ```php class UsersImport implements ToModel, WithSkipDuplicates { // No additional methods are required for this concern } ``` -------------------------------- ### Customize Chunk Count for Query Performance Source: https://docs.laravel-excel.com/nova/1.x/exports/customizations Shows how to adjust the default chunk size for querying resources during export using the `withChunkCount()` method. This can improve performance for large datasets by processing data in smaller batches. ```php public function actions(Request $request) { return [ (new DownloadExcel)->withChunkCount(300), ]; } ``` -------------------------------- ### Style Sheets with External CSS in Laravel Excel Source: https://docs.laravel-excel.com/2.1/blade/styling Links external CSS files to apply styles via classes and IDs, supporting background, color, and borders. Requires HTML inclusion via Laravel's HTML helper and proper UTF-8 meta. Enables reusable styling across multiple sheets in Excel exports. ```css #cell { background-color: #000000; color: #ffffff; } .cell { background-color: #000000; color: #ffffff; } tr td { background-color: #ffffff; } tr > td { border-bottom: 1px solid #000000; } ``` ```html {{ HTML::style('css/table.css') }} Cell Cell ``` -------------------------------- ### Download PDF using DOMPDF in Laravel Excel Source: https://docs.laravel-excel.com/3.0/exports/export-formats Illustrates downloading an export as a PDF using the DOMPDF library with Laravel Excel. This provides an alternative method for PDF generation. ```php (new InvoicesExport)->download('invoices.pdf', \Maatwebsite\Excel\Excel::DOMPDF); ``` -------------------------------- ### Override Worksheet Properties in Laravel Excel Source: https://docs.laravel-excel.com/3.1/exports/settings Demonstrates how to override default worksheet properties like title, creator, and description on a per-export basis using the WithProperties concern. The properties are returned as an associative array. ```PHP namespace App\Exports; use Maatwebsite\Excel\Concerns\WithProperties; class InvoicesExport implements WithProperties { public function properties(): array { return [ 'creator' => 'Patrick Brouwers', 'lastModifiedBy' => 'Patrick Brouwers', 'title' => 'Invoices Export', 'description' => 'Latest Invoices', 'subject' => 'Invoices', 'keywords' => 'invoices,export,spreadsheet', 'category' => 'Invoices', 'manager' => 'Patrick Brouwers', 'company' => 'Maatwebsite', ]; } } ``` ```PHP namespace App\Exports; use Maatwebsite\Excel\Concerns\WithProperties; class InvoicesExport implements WithProperties { public function properties(): array { return [ 'creator' => 'Patrick Brouwers', ]; } } ``` -------------------------------- ### Implement OnEachRow for Custom Row Persistence in Laravel PHP Source: https://docs.laravel-excel.com/3.1/imports/model This PHP code implements the OnEachRow concern to manually handle each row in an Excel import, allowing custom logic for creating groups and users. It depends on the MaatwebsiteExcel package and Eloquent models. Limitations include the inability to use batch inserts since persistence occurs in the onRow method. ```php namespace App\Imports; use App\Group; use App\User; use Maatwebsite\Excel\Row; use Maatwebsite\Excel\Concerns\OnEachRow; class UsersImport implements OnEachRow { public function onRow(Row $row) { $rowIndex = $row->getIndex(); $row = $row->toArray(); $group = Group::firstOrCreate([ 'name' => $row[1], ]); $group->users()->create([ 'name' => $row[0], ]); } } ``` -------------------------------- ### Use Custom Macro in Laravel Excel Export Events Source: https://docs.laravel-excel.com/3.0/exports/extending This PHP code demonstrates how to integrate a custom macro, `styleCells`, within the `registerEvents` method of a Laravel Excel export class. It shows setting document creator on `BeforeExport` and applying landscape orientation and custom cell styling on `AfterSheet` events. It utilizes `MaatwebsiteExcelConcernsWithEvents` and PhpSpreadsheet classes. ```php namespace App\Exports; use Maatwebsite\Excel\Concerns\WithEvents; use Maatwebsite\Excel\Events\BeforeExport; use Maatwebsite\Excel\Events\AfterSheet; class InvoicesExport implements WithEvents { /** * @return array */ public function registerEvents(): array { return [ BeforeExport::class => function(BeforeExport $event) { $event->writer->setCreator('Patrick'); }, AfterSheet::class => function(AfterSheet $event) { $event->sheet->setOrientation(\PhpOffice\PhpSpreadsheet\Worksheet\PageSetup::ORIENTATION_LANDSCAPE); $event->sheet->styleCells( 'B2:G8', [ 'borders' => [ 'outline' => [ 'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK, 'color' => ['argb' => 'FFFF0000'], ], ] ] ); }, ]; } } ``` -------------------------------- ### Chaining jobs after queued export Source: https://docs.laravel-excel.com/3.0/exports/queued Shows how to chain additional jobs that will execute after the export is completed. The chain() method accepts an array of jobs that will run only if all export jobs succeed. This is useful for notifications or post-processing tasks. ```php (new InvoicesExport)->queue('invoices.xlsx')->chain([ new NotifyUserOfCompletedExport(request()->user()), ]); ``` ```php 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()); } } ``` -------------------------------- ### Select sheets by index with Laravel Excel Source: https://docs.laravel-excel.com/2.1/import/select Loads sheets by zero‑based index using selectSheetsByIndex. Accepts one or more integer indices and returns the corresponding sheets. Useful when sheet names are unknown. ```php // First sheet Excel::selectSheetsByIndex(0)->load(); // First and second sheet Excel::selectSheetsByIndex(0, 1)->load(); ``` -------------------------------- ### Global Event Listeners in Laravel Excel Source: https://docs.laravel-excel.com/3.1/imports/extending Registers global listeners for Reader and Sheet events (e.g., in AppServiceProvider::register). Applies to all exports/imports. Closures are not serializable, so avoid using them with queued jobs. ```PHP Reader::listen(BeforeImport::class, function () { // }); Sheet::listen(AfterImport::class, function () { // }); Sheet::listen(BeforeSheet::class, function () { // }); Sheet::listen(AfterSheet::class, function () { // }); ``` -------------------------------- ### Applying Full Styles from Array in PhpSpreadsheet PHP Source: https://docs.laravel-excel.com/3.1/exports/column-formatting This PhpSpreadsheet method applies comprehensive styles to a cell using applyFromArray on getStyle, covering font, borders, alignment, etc. Depends on PhpOffice\PhpSpreadsheet components like Font, Border, Alignment. Inputs are detailed style arrays; outputs fully styled cell. Limitation: Requires precise PhpSpreadsheet class constants. ```PHP /** * Apply styles from array. */ $spreadsheet->getActiveSheet()->getStyle('B2')->applyFromArray( [ 'font' => [ 'name' => 'Arial', 'bold' => true, 'italic' => false, 'underline' => Font::UNDERLINE_DOUBLE, 'strikethrough' => false, 'color' => [ 'rgb' => '808080' ] ], 'borders' => [ 'bottom' => [ 'borderStyle' => Border::BORDER_DASHDOT, 'color' => [ 'rgb' => '808080' ] ], 'top' => [ 'borderStyle' => Border::BORDER_DASHDOT, 'color' => [ 'rgb' => '808080' ] ] ], 'alignment' => [ 'horizontal' => Alignment::HORIZONTAL_CENTER, 'vertical' => Alignment::VERTICAL_CENTER, 'wrapText' => true, ], 'quotePrefix' => true ] ); ``` -------------------------------- ### Import Users via ToCollection in PHP Source: https://docs.laravel-excel.com/3.1/architecture This PHP code defines an Import class that processes Excel data into a Collection and creates User model instances. It relies on the Maatwebsite\Excel package, specifically the ToCollection concern, and assumes an Excel file with rows where the first column contains user names. The import is limited to simple array-based data without error handling or validation. ```php 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], ]); } } } ``` -------------------------------- ### Import Excel to Array with PHP Source: https://docs.laravel-excel.com/3.1/imports/importables Shows how to load the contents of an Excel file directly into a PHP array using the `UsersImport` class. This is useful for quick data inspection or when the data structure is simple. ```php $array = (new UsersImport)->toArray('users.xlsx'); ``` -------------------------------- ### Auto-size Excel columns in Laravel with PHP Source: https://docs.laravel-excel.com/3.0/exports/column-formatting Shows how to implement automatic column width calculation in Laravel Excel exports using the ShouldAutoSize concern. Automatically adjusts column widths to fit content. ```php namespace App\Exports; use Maatwebsite\Excel\Concerns\ShouldAutoSize; class InvoicesExport implements ShouldAutoSize { ... } ``` -------------------------------- ### Create Sheet from Eloquent Model (PHP) Source: https://docs.laravel-excel.com/2.1/export/array Demonstrates exporting an Eloquent model to an Excel sheet using `->fromModel($model)`. This method uses the model's attributes as column headers. ```PHP // Export from Eloquent Model // $model is an instance of an Eloquent Model $sheet->fromModel($model); ``` -------------------------------- ### Add Single Chart to Export Class Source: https://docs.laravel-excel.com/3.1/exports/charts Shows how to implement the WithCharts concern in a Laravel Excel export class to add a single chart. The charts() method returns a Chart instance that will be added to the worksheet. The class must implement the charts() method and return a Chart object. ```php setValueExplicit($value, DataType::TYPE_NUMERIC); return true; } // else return default behavior return parent::bindValue($cell, $value); } } ``` -------------------------------- ### Exporting All Fields in Laravel PHP Source: https://docs.laravel-excel.com/nova/1.x/exports/customizations The allFields() method includes all resource attributes in the export, bypassing the default limitation to index fields only. It takes no parameters. This is useful for comprehensive data exports but may include unnecessary fields. ```php public function actions(Request $request) { return [ (new DownloadExcel)->allFields(), ]; } ``` -------------------------------- ### Auto-Register Event Listeners in Laravel Excel Source: https://docs.laravel-excel.com/3.1/imports/extending Uses the RegistersEventListeners trait to auto-register public static methods named after events (beforeImport, afterImport, beforeSheet, afterSheet). Methods can be static or, from 3.1.50 onwards, instance methods using $this. ```PHP 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) { // } } ``` -------------------------------- ### Export Users by Year using Constructor - PHP Source: https://docs.laravel-excel.com/3.1/architecture/objects This snippet demonstrates how to create an Export object in PHP that accepts a year through its constructor. It then uses this year to filter users from the database before exporting them. The `FromCollection` concern is used to specify that the data will be a collection. ```php namespace App\Exports; use App\Models\User; // Assuming you have a User model use Maatwebsite\Excel\Concerns\FromCollection; class UsersExport implements FromCollection { private $year; public function __construct(int $year) { $this->year = $year; } public function collection() { // Fetch users created in the specified year return User::whereYear('created_at', $this->year)->get(); } } ``` ```php // To download the export: use Maatwebsite\Excel\Facades\Excel; return Excel::download(new UsersExport(2019), 'users.xlsx'); ``` -------------------------------- ### Implement FromGenerator in PHP Export Class Source: https://docs.laravel-excel.com/3.1/exports/from-generator This PHP class implements the FromGenerator concern to create an Excel export using a generator function. It uses the Maatwebsite Excel package. The generator yields rows of data without storing them in memory. No external dependencies beyond Laravel and the package are required. Suitable for large datasets. ```PHP ``` -------------------------------- ### Add multiple heading rows with WithHeadings Source: https://docs.laravel-excel.com/3.1/exports/mapping Creates multiple heading rows by returning a nested array from the headings() method. Each sub-array represents a separate heading row in the export. Useful for complex reports requiring multi-level headers. ```php 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'], ]; } } ``` -------------------------------- ### Store Excel export on default disk - PHP Source: https://docs.laravel-excel.com/3.1/exports/store Stores an Excel export on Laravel's default filesystem disk. Uses the InvoicesExport class and saves as invoices.xlsx. ```php public function storeExcel() { // Store on default disk Excel::store(new InvoicesExport(2018), 'invoices.xlsx'); } ``` -------------------------------- ### AutoSize Columns with ShouldAutoSize (PHP) Source: https://docs.laravel-excel.com/3.1/architecture/concerns Shows how to utilize the `ShouldAutoSize` concern to automatically size columns in an Excel export. This concern doesn't require any methods to be implemented; it simply instructs the export process to auto-size the columns. This can be useful for ensuring readability. ```PHP namespace App\Exports;\n\nuse App\User;\nuse Maatwebsite\Excel\Concerns\ShouldAutoSize;\n\nclass UsersExport implements ShouldAutoSize\n{\n\n\n} ``` -------------------------------- ### Store Excel export on custom disk - PHP Source: https://docs.laravel-excel.com/3.1/exports/store Stores an Excel export on a custom disk (e.g. s3) with optional writer type specification. Shows two variations of disk storage. ```php 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); } ``` -------------------------------- ### Import only selected sheets using onlySheets method Source: https://docs.laravel-excel.com/3.1/imports/multiple-sheets Demonstrates how to instantiate a UsersImport and restrict the import to specific worksheets at runtime with the onlySheets method, then execute the import using Excel::import. Useful for dynamic sheet selection based on user input. ```php $import = new UsersImport(); $import->onlySheets('Worksheet 1', 'Worksheet 3'); Excel::import($import, 'users.xlsx'); ``` -------------------------------- ### Implementing WithHeadingRow for Excel Model Import in PHP Source: https://docs.laravel-excel.com/3.1/imports/heading-row The WithHeadingRow concern enables using header names as keys in the row array passed to the model method, avoiding numeric indices. It depends on the Maatwebsite\Excel package and requires implementing ToModel. Inputs are Excel rows; outputs are Eloquent models; assumes first row as headers by default. ```PHP namespace App\Imports; use App\User; use Maatwebsite\Excel\Concerns\ToModel; use Maatwebsite\Excel\Concerns\WithHeadingRow; class UsersImport implements ToModel, WithHeadingRow { public function model(array $row) { return new User([ 'name' => $row['name'], 'email' => $row['email'], 'at' => $row['at_field'], ]); } } ``` ```PHP namespace App\Imports; use App\User; use Maatwebsite\Excel\Concerns\ToModel; use Maatwebsite\Excel\Concerns\WithHeadingRow; class UsersImport implements ToModel, WithHeadingRow { public function model(array $row) { return new User([ 'name' => $row['name'], 'email' => $row['email'], ]); } public function headingRow(): int { return 2; } } ``` -------------------------------- ### Laravel Excel - Skipping Specific Sheets Source: https://docs.laravel-excel.com/3.1/imports/multiple-sheets Shows how to selectively skip sheets using the `SkipsUnknownSheets` concern implemented in a specific sheet import class. Only the implementing class's sheets will be skipped. ```php namespace App\Imports;\n\nuse Maatwebsite\Excel\Concerns\SkipsUnknownSheets;\n\nclass FirstSheetImport implements SkipsUnknownSheets\n{ \n public function onUnknownSheet($sheetName)\n { \n // E.g. you can log that a sheet was not found.\n info("Sheet {$sheetName} was skipped")\n }\n} ``` -------------------------------- ### Enable Auto-Size for Excel Columns in PHP Source: https://docs.laravel-excel.com/3.1/exports/column-formatting This PHP export class implements ShouldAutoSize to automatically calculate column widths in Excel sheets. It ensures columns adjust to content without manual sizing. No additional methods are needed beyond the interface implementation. ```php namespace App\Exports; use Maatwebsite\Excel\Concerns\ShouldAutoSize; class InvoicesExport implements ShouldAutoSize { ... } ``` -------------------------------- ### Implement custom value binder in Laravel Excel (PHP) Source: https://docs.laravel-excel.com/3.1/imports/custom-formatting-values Defines a UsersImport class that extends DefaultValueBinder and implements WithCustomValueBinder to enforce numeric values using setValueExplicit. This customization overrides the default intelligent formatting for numeric cells while preserving default behavior for other types. Requires Php and Maatwebsite\Excel packages. ```PHP 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); } } ``` -------------------------------- ### Register Event Listeners in Laravel Excel Source: https://docs.laravel-excel.com/3.1/imports/extending Implements WithEvents and returns an associative array mapping fully qualified event class names to listeners. Listeners can be closures, invokable classes, or array-callables. Closures cannot be serialized and thus are not compatible with queued imports. ```PHP namespace App\Imports; use Maatwebsite\Excel\Concerns\WithEvents; use Maatwebsite\Excel\Events\BeforeImport; use Maatwebsite\Excel\Events\AfterImport; use Maatwebsite\Excel\Events\AfterSheet; use Maatwebsite\Excel\Events\BeforeSheet; class UsersImport implements WithEvents { /** * @return array */ public function registerEvents(): array { return [ // Handle by a closure. BeforeImport::class => function(BeforeImport $event) { $creator = $event->reader->getProperties()->getCreator(); }, // Using a class with an __invoke method. BeforeSheet::class => new BeforeSheetHandler(), // Array callable, refering to a static method. AfterSheet::class => [self::class, 'afterSheet'], ]; } public static function afterSheet(AfterSheet $event) { // } } ``` -------------------------------- ### Configure Default Value Binder in PHP Laravel Excel Source: https://docs.laravel-excel.com/3.1/exports/column-formatting This PHP configuration snippet sets the default value binder for Laravel Excel in the config/excel.php file. It uses an array to specify the binder class, allowing global customization of value handling. This applies to all exports unless overridden locally. ```php 'value_binder' => [ 'default' => Maatwebsite\Excel\DefaultValueBinder::class, ], ``` -------------------------------- ### Register concern handler in service provider Source: https://docs.laravel-excel.com/3.0/exports/extending Registers the custom concern handler with the Excel facade using the extend method. Binds the WithCustomProperties interface to its handler. Can be placed in any service provider, commonly AppServiceProvider. ```php public function register() { Excel::extend(WithCustomProperties::class, new WithCustomPropertiesHandler); } ``` -------------------------------- ### Force Local File Cleanup After Each Import Chunk Source: https://docs.laravel-excel.com/3.1/imports/queued Enables forced local file synchronization cleanup in multi-server environments. Setting force_resync_remote to true ensures temporary files are deleted from local storage after each chunk is processed on each server. This prevents local storage exhaustion when using remote temporary files with multiple queue workers. ```php 'temporary_files' => [ 'force_resync_remote' => true, ], ``` -------------------------------- ### Implement Custom CSV Settings Interface Source: https://docs.laravel-excel.com/3.1/imports/custom-csv-settings Implementation of WithCustomCsvSettings interface to customize CSV parsing behavior. This allows overriding default CSV settings like encoding and delimiter. The getCsvSettings method returns an array of configuration options that will be used during CSV import processing. ```php 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' ]; } } ``` -------------------------------- ### Adding Multiple Drawings to Laravel Excel Export Source: https://docs.laravel-excel.com/3.1/exports/drawings This snippet extends the WithDrawings concern to include multiple drawings by returning an array from the drawings() method. Requires Maatwebsite\Excel and PhpSpreadsheet. Each drawing needs unique properties and coordinates; inputs are local image paths, outputs an Excel with multiple positioned images. Limitation: All images must be local and accessible. ```php 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]; } } ``` -------------------------------- ### Create Multiple Sheets in Laravel Excel Source: https://docs.laravel-excel.com/2.1/export/sheets Illustrates the creation of multiple distinct sheets within a single Excel file using Laravel Excel. Each sheet can be defined with its own name and associated callback for content manipulation. The final output is exported in XLS format. ```php 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'); ``` -------------------------------- ### Create UsersImport class for Excel import Source: https://docs.laravel-excel.com/3.1/imports/collection This snippet shows how to create a custom import class that implements ToCollection interface. It processes each row from the Excel file and creates User records. The collection method receives all rows as a Laravel collection. ```PHP 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], ]); } } } ``` -------------------------------- ### Import Excel to Collection with PHP Source: https://docs.laravel-excel.com/3.1/imports/importables Demonstrates loading an Excel file's data into a Laravel Collection using the `UsersImport` class. Collections provide convenient methods for data manipulation and iteration. ```php $collection = (new UsersImport)->toCollection('users.xlsx'); ``` -------------------------------- ### Create Month-Based Sheet Class in PHP Laravel Source: https://docs.laravel-excel.com/3.0/exports/multiple-sheets This snippet shows a sheet class implementing FromQuery and WithTitle for each month's invoices. It queries invoices by year and month from the database. Inputs are year and month integers, output is a query builder, with title based on month. ```PHP 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; } } ``` -------------------------------- ### Pass Variables into Closure (PHP) Source: https://docs.laravel-excel.com/2.1/export/array Shows how to pass external variables into the sheet creation closure using `use($data)`. This allows you to dynamically generate the Excel data based on variables. ```PHP $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'); ``` -------------------------------- ### Format Excel Columns with WithColumnFormatting in PHP Source: https://docs.laravel-excel.com/3.1/exports/column-formatting This PHP class implements WithColumnFormatting and WithMapping to apply custom formatting to Excel columns during export. It uses PhpOfficePhpSpreadsheet's Date and NumberFormat classes to format dates and currencies. The map method transforms invoice data into an array suitable for export, ensuring correct data types. ```php 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, ]; } } ``` -------------------------------- ### Styling Cells with Fluent Syntax in WithStyles PHP Source: https://docs.laravel-excel.com/3.1/exports/column-formatting WithStyles also supports fluent syntax for direct cell manipulation on the Worksheet. Requires Maatwebsite\Excel and PhpOffice\PhpSpreadsheet\Worksheet\Worksheet. Inputs are style methods like getStyle and getFont; outputs modified Worksheet styles. Limitation: More verbose than array method for complex styling. ```PHP 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); } } ``` -------------------------------- ### Apply General Styles in Laravel Excel using PHP Source: https://docs.laravel-excel.com/2.1/blade/styling Uses the setStyle() method or other setters to apply general font styles like family, name, size, and boldness to the entire sheet. This method is part of the Laravel Excel export API and requires a sheet object. Outputs styled Excel sheets with specified font attributes. ```php // Font family $sheet->setFontFamily('Comic Sans MS'); // Set font with ->setStyle()` $sheet->setStyle(array( 'font' => array( 'name' => 'Calibri', 'size' => 12, 'bold' => true ) )); ``` -------------------------------- ### Ignore Empty Cells Source: https://docs.laravel-excel.com/2.1/import/extra Configures the reader to skip empty cells instead of including them as null values in the collection. This helps clean up data processing when dealing with spreadsheets that have blank cells scattered throughout. Can be set globally in import.php config or applied per import using this method. ```php $reader->ignoreEmpty(); ``` -------------------------------- ### Add ExportToExcel Action to Laravel Nova Resource Source: https://docs.laravel-excel.com/nova/1.x/exports/store This code snippet shows how to add the ExportToExcel action to a Laravel Nova resource's actions method. The action enables exporting resources to Excel format and storing them in the default storage disk. ```php use Maatwebsite\LaravelNovaExcel\Actions\ExportToExcel; public function actions(Request $request) { return [ new ExportToExcel(), ]; } ``` -------------------------------- ### Share Blade view across all Excel sheets in Laravel Source: https://docs.laravel-excel.com/2.1/blade/load-view Illustrates how to share a single Blade view across all sheets in an Excel file using Laravel Excel's `shareView` method. ```PHP Excel::shareView('folder.view')->create(); ``` -------------------------------- ### Set cell size in Laravel Excel Source: https://docs.laravel-excel.com/2.1/export/sizing Configures both width and height for one or multiple cells in a Laravel Excel sheet. Accepts either individual parameters or an array with width/height specifications. ```PHP // Set size for a single cell $sheet->setSize('A1', 500, 50); $sheet->setSize(array( 'A1' => array( 'width' => 50, 'height' => 500 ) )); ``` -------------------------------- ### Password Protect a Sheet in Laravel Excel Source: https://docs.laravel-excel.com/2.1/export/sheets Demonstrates how to password protect a sheet in Laravel Excel using the `$sheet->protect()` method. It shows both a basic password protection and an advanced usage with a callback to configure specific protection settings, such as enabling sorting. ```php // Default protect $sheet->protect('password'); // Advanced protect $sheet->protect('password', function(PHPExcel_Worksheet_Protection $protection) { $protection->setSort(true); }); ``` -------------------------------- ### Adding Headings to Export in Laravel PHP Source: https://docs.laravel-excel.com/nova/1.x/exports/customizations The withHeadings() method adds a header row to the export with column names; without parameters, it uses resource index fields. Custom headings can be passed as an array of strings for more control. This improves readability of the exported data. ```php /** * Get the actions available for the resource. * * @param \Illuminate\Http\Request $request * * @return array */ public function actions(Request $request) { return [ (new DownloadExcel)->withHeadings(), ]; } ``` ```php public function actions(Request $request) { return [ (new DownloadExcel)->withHeadings('#', 'Name', 'E-mail'), ]; } ``` -------------------------------- ### Disable intelligent formatting using StringValueBinder (PHP) Source: https://docs.laravel-excel.com/3.1/imports/custom-formatting-values Shows how to extend StringValueBinder to force all imported cell values to be treated as strings, effectively disabling Laravel Excel's intelligent formatting. The UsersImport class implements WithCustomValueBinder and ToModel but contains no additional logic. Suitable when raw string data is required. ```PHP namespace App\Imports; use Maatwebsite\Excel\Concerns\ToModel; use Maatwebsite\Excel\Concerns\WithCustomValueBinder; class UsersImport extends \PhpOffice\PhpSpreadsheet\Cell\StringValueBinder implements WithCustomValueBinder, ToModel { } ``` -------------------------------- ### Handle Validation Failures in Laravel Excel Source: https://docs.laravel-excel.com/3.1/imports/validation Demonstrates catching ValidationException to gather all import failures, providing access to row, attribute, errors, and values for each failure. ```PHP try { $import->import('import-users.xlsx'); } catch (\Maatwebsite\Excel\Validators\ValidationException $e) { $failures = $e->failures(); foreach ($failures as $failure) { $failure->row(); // row that went wrong $failure->attribute(); // either heading key (if using heading row concern) or column index $failure->errors(); // Actual error messages from Laravel validator $failure->values(); // The values of the row that has failed. } } ``` -------------------------------- ### Implicit queued export with ShouldQueue Source: https://docs.laravel-excel.com/3.0/exports/queued Demonstrates how to mark an export as implicitly queued by implementing Laravel's ShouldQueue contract. When using ShouldQueue, calling store() will automatically queue the export. This approach simplifies the controller code by removing the need to explicitly call queue(). ```php 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(); } } ``` ```php (new InvoicesExport)->store('invoices.xlsx'); ``` -------------------------------- ### Export Collection with FromCollection (PHP) Source: https://docs.laravel-excel.com/3.1/architecture/concerns Demonstrates using the `FromCollection` concern to export a collection of data. This concern requests an implementation of the `collection` method which returns a `Collection` instance. This snippet shows exporting all users from the `User` model. ```PHP namespace App\Exports;\n\nuse App\User;\nuse Maatwebsite\Excel\Concerns\FromCollection;\n\nclass UsersExport implements FromCollection\n{\n public function collection()\n {\n return User::all();\n }\n\n} ``` -------------------------------- ### Mapping Invoice Rows in Laravel Excel PHP Source: https://docs.laravel-excel.com/3.0/exports/mapping This PHP code demonstrates implementing the WithMapping concern to transform Eloquent model data into an array for each row in the Excel export. It uses the FromQuery concern to query data and requires the Carbon Date class for date formatting. The map method processes each invoice object, output is an array of values per row, limited to predefined columns and assuming Invoice model exists. ```PHP 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), ]; } } ``` -------------------------------- ### Export Excel from Blade View in Laravel Source: https://docs.laravel-excel.com/3.0/exports/from-view The FromView concern enables exporting Excel files by rendering a Blade view containing an HTML table. Implement the view() method to return a View with data like model collections. The package converts the table to spreadsheet format; requires MaatwebsiteExcel and Laravel. Limitations: View must have a proper table structure for accurate conversion. ```php 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() ]); } } ``` ```blade @foreach($users as $user) @endforeach
Name Email
{{ $user->name }} {{ $user->email }}
``` -------------------------------- ### Export to CSV Format in Laravel Excel Source: https://docs.laravel-excel.com/2.1/export/export Generates a CSV export using ->export('csv') or ->download('csv') after creating the Excel object. Default enclosure and delimiter can be customized in the package's config file. Suitable for simple data exports without Excel dependencies. ```php ->export('csv'); // or ->download('csv'); ``` -------------------------------- ### Configuring Heading Row Formatter in Laravel Excel PHP Source: https://docs.laravel-excel.com/3.1/imports/heading-row HeadingRowFormatter customizes how header keys are processed, defaulting to slug format. Dependencies include the Excel package; extend or set in service providers/config. Inputs are raw headers; outputs formatted keys; limitations include per-import or global settings only. ```PHP use Maatwebsite\Excel\Imports\HeadingRowFormatter; HeadingRowFormatter::default('none'); public function model(array $row) { return new User([ 'name' => $row['Name'], 'email' => $row['Email'], ]); } ``` ```PHP HeadingRowFormatter::extend('custom', function($value, $key) { return 'do-something-custom' . $value; // And you can use heading column index. // return 'column-' . $key; }); ``` ```PHP 'imports' => [ 'heading_row' => [ 'formatter' => 'custom', ], ], ``` ```PHP HeadingRowFormatter::default('custom'); ``` -------------------------------- ### Use different Blade views for multiple Excel sheets in Laravel Source: https://docs.laravel-excel.com/2.1/blade/load-view Shows how to assign different Blade views to multiple sheets in an Excel file. Each sheet can have its own view for customized content. ```PHP 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'); }); }); ``` -------------------------------- ### Map single row data with WithMapping Source: https://docs.laravel-excel.com/3.1/exports/mapping Maps data for a single row export using the WithMapping concern. Transforms invoice data including number, user name, and formatted creation date. Requires Maatwebsite\Excel\Concerns\FromQuery and Maatwebsite\Excel\Concerns\WithMapping interfaces. ```php 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), ]; } } ``` -------------------------------- ### Access Row Data Using Table Headings as Attributes Source: https://docs.laravel-excel.com/2.1/import/results Retrieves data from an Excel row where the first row of the file is used as attribute names. These attributes are converted to slugs by default, but this behavior can be configured. ```php // Get the firstname $row->firstname; ``` -------------------------------- ### Define Importable Class with PHP Source: https://docs.laravel-excel.com/3.1/imports/importables This snippet shows how to define a custom import class using the `Importable` trait and `ToModel` concern. It specifies how each row from the Excel file should be mapped to a User model. ```php namespace App\Imports; use App\User; use Maatwebsite\Excel\Concerns\ToModel; use Maatwebsite\Excel\Concerns\Importable; class UsersImport implements ToModel { use Importable; public function model(array $row) { return new User([ 'name' => $row[0], ]); } } ``` -------------------------------- ### Adding Headings to Laravel Excel Export PHP Source: https://docs.laravel-excel.com/3.0/exports/mapping This PHP code shows how to add a heading row to an Excel export by implementing the WithHeadings concern alongside FromQuery. It defines column headers as an array in the headings method, which is added as the first row. Dependencies include Maatwebsite Excel concerns; input is handled by FromQuery, output is the header arrays, with no specific limitations mentioned but assumes query provides data. ```PHP use Maatwebsite\Excel\Concerns\FromQuery; use Maatwebsite\Excel\Concerns\WithHeadings; class InvoicesExport implements FromQuery, WithHeadings { public function headings(): array { return [ '#', 'Date', ]; } } ``` -------------------------------- ### Set Right-to-Left (RTL) Sheet Direction (PHP) Source: https://docs.laravel-excel.com/3.1/exports/extending This snippet shows how to set the Excel sheet direction to Right-to-Left (RTL) using the `setRightToLeft()` method within the `AfterSheet` event in Laravel Excel. It utilizes the Maatwebsite Excel package and requires the PhpSpreadsheet component. It is within a `WithEvents` class ```PHP namespace App\Exports; use Maatwebsite\Excel\Concerns\WithEvents; use Maatwebsite\Excel\Events\BeforeExport; use Maatwebsite\Excel\Events\AfterSheet; class InvoicesExport implements WithEvents { /** * @return array */ public function registerEvents(): array { return [ AfterSheet::class => function(AfterSheet $event) { $event->sheet->getDelegate()->setRightToLeft(true); }, ]; } } ``` -------------------------------- ### Add setOrientation macro to Sheet Source: https://docs.laravel-excel.com/3.0/exports/extending Extends the Sheet class with a custom macro to set page orientation. Accesses underlying PhpSpreadsheet getPageSetup() method. Macro provides convenient way to set page orientation for sheets. ```php use \Maatwebsite\Excel\Sheet; Sheet::macro('setOrientation', function (Sheet $sheet, $orientation) { $sheet->getDelegate()->getPageSetup()->setOrientation($orientation); }); ``` -------------------------------- ### Control Field Visibility on Export in Laravel Nova Source: https://docs.laravel-excel.com/nova/1.x/exports/customizations Demonstrates how to use helper macros like `hideOnExport` and `onlyOnExport` to manage which fields are included in export requests. This prevents issues with fields not intended for export, such as those displaying HTML content. ```php public function fields(Request $request): array { return [ Text::make(__('Email'), 'email') ->rules('required', 'email') ->displayUsing(fn(string $email): string => "{$email}") ->asHtml() ->hideOnExport(), Text::make(__('Email'), 'email') ->onlyOnExport(), ]; } ``` -------------------------------- ### Set General Sheet Style in PHP Source: https://docs.laravel-excel.com/2.1/export/sheet-styling This functionality allows setting general styling for an Excel sheet using the setStyle method in Laravel Excel. It requires a PHPSpreadsheet or similar dependency for $sheet object. Inputs include an associative array of font properties like name, size, and bold. Outputs modified sheet; no significant limitations. ```PHP $sheet->setStyle(array( 'font' => array( 'name' => 'Calibri', 'size' => 15, 'bold' => true ) )); ``` -------------------------------- ### Conditional Row Import with Validation Source: https://docs.laravel-excel.com/3.1/imports/model Implements conditional logic in model() method to skip specific rows by returning null. Useful for filtering out invalid or incomplete data before import. ```php public function model(array $row) { if (!isset($row[0])) { return null; } return new User([ 'name' => $row[0], ]); } ``` -------------------------------- ### Set Custom Column Widths in PHP Excel Export Source: https://docs.laravel-excel.com/3.1/exports/column-formatting This PHP class uses WithColumnWidths to define specific widths for Excel columns, overriding auto-sizing where needed. It returns an array mapping alphabetic column letters to numeric widths. Can be combined with ShouldAutoSize for mixed controls. ```php namespace App\Exports; use Maatwebsite\Excel\Concerns\WithColumnWidths; class InvoicesExport implements WithColumnWidths { public function columnWidths(): array { return [ 'A' => 55, 'B' => 45, ]; } } ``` -------------------------------- ### Set Font Styles in PHP Source: https://docs.laravel-excel.com/2.1/export/sheet-styling Change the font for the entire sheet using the setFont method in Laravel Excel. Depends on having a sheet object from PHPSpreadsheet. Takes an array of font properties as input. Applies changes to the sheet; ensure compatibility with Excel versions. ```PHP $sheet->setFont(array( 'family' => 'Calibri', 'size' => '15', 'bold' => true )); ``` -------------------------------- ### Set cell borders in Laravel Excel Source: https://docs.laravel-excel.com/2.1/export/cells Demonstrates how to set borders for cells in Laravel Excel, either individually or using an array for more complex styling. ```PHP $cells->setBorder('solid', 'none', 'none', 'solid'); $cells->setBorder(array( 'top' => array( 'style' => 'solid' ) )); ``` -------------------------------- ### Load specific sheets conditionally with WithConditionalSheets trait Source: https://docs.laravel-excel.com/3.1/imports/multiple-sheets Implements the WithConditionalSheets trait in a Laravel Excel import to define which worksheets should be imported based on a conditional mapping. Requires the WithMultipleSheets interface and returns an associative array of sheet names to import classes. ```php namespace App\Imports; use Maatwebsite\Excel\Concerns\WithMultipleSheets; use Maatwebsite\Excel\Concerns\WithConditionalSheets; class UsersImport implements WithMultipleSheets { use WithConditionalSheets; public function conditionalSheets(): array { return [ 'Worksheet 1' => new FirstSheetImport(), 'Worksheet 2' => new SecondSheetImport(), 'Worksheet 3' => new ThirdSheetImport(), ]; } } ``` -------------------------------- ### Define Custom Macro for Cell Styling in Laravel Excel Source: https://docs.laravel-excel.com/3.0/exports/extending This snippet shows how to define a custom macro named `styleCells` for the Sheet object in Laravel Excel. This macro allows applying array-based styles to a specified cell range, leveraging PhpSpreadsheet's underlying functionality. It requires the `MaatwebsiteExcelSheet` namespace. ```php use \Maatwebsite\Excel\Sheet; Sheet::macro('styleCells', function (Sheet $sheet, string $cellRange, array $style) { $sheet->getDelegate()->getStyle($cellRange)->applyFromArray($style); }); ``` -------------------------------- ### Prepare Data for Validation in PHP Source: https://docs.laravel-excel.com/3.1/imports/validation Tweak row data before it's passed to the validator by implementing the prepareForValidation method. This method receives the raw row data and index, returning the manipulated data suitable for validation. ```php class UsersImport implements WithValidation { public function prepareForValidation($data, $index) { $data['email'] = $data['email'] ?? $this->myOtherWayOfFindingTheEmail($data); return $data; } } ``` -------------------------------- ### Exporting Specific Columns in Laravel PHP Source: https://docs.laravel-excel.com/nova/1.x/exports/customizations The only() method restricts the export to selected columns by passing their names as arguments, defaulting to all index fields otherwise. It takes variable string parameters for column names. This allows focused exports on relevant data. ```php public function actions(Request $request) { return [ (new DownloadExcel)->only('name', 'email'), ]; } ``` -------------------------------- ### Map Single Cells to Named Keys in Laravel Excel Import Source: https://docs.laravel-excel.com/3.1/imports/mapped-cells Implements WithMappedCells with ToModel to map spreadsheet cell coordinates (e.g., B1, B2) to named keys (e.g., name, email). The mapping() method defines cell addresses, and model() receives an associative array of those keys. Dependencies include Laravel and maatwebsite/excel. Limitation: only cell references are allowed, not column mapping. ```php namespace App\Imports; use App\User; use Maatwebsite\Excel\Concerns\ToModel; use Maatwebsite\Excel\Concerns\WithMappedCells; class UsersImport implements WithMappedCells, ToModel { public function mapping(): array { return [ 'name' => 'B1', 'email' => 'B2', ]; } public function model(array $row) { return new User([ 'name' => $row['name'], 'email' => $row['email'], ]); } } ``` -------------------------------- ### Style Cells with PHPExcel Methods in Laravel Excel Source: https://docs.laravel-excel.com/2.1/blade/styling Utilizes native PHPExcel methods to apply specific styling like background color to individual cells via getStyle(). This is integrated into Laravel Excel and uses applyFromArray for style definitions. Effective for targeted cell styling in exported Excel files. ```php // Set background color for a specific cell $sheet->getStyle('A1')->applyFromArray(array( 'fill' => array( 'type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'FF0000') ) )); ``` -------------------------------- ### Add single heading row with WithHeadings Source: https://docs.laravel-excel.com/3.1/exports/mapping Adds a single heading row to the export by implementing the WithHeadings concern. The headings appear as the first row of the sheet. Requires Maatwebsite\Excel\Concerns\FromQuery and Maatwebsite\Excel\Concerns\WithHeadings interfaces. ```php use Maatwebsite\Excel\Concerns\FromQuery; use Maatwebsite\Excel\Concerns\WithHeadings; class InvoicesExport implements FromQuery, WithHeadings { public function headings(): array { return [ '#', 'User', 'Date', ]; } } ``` -------------------------------- ### Enable cross-sheet formula references with HasReferencesToOtherSheets concern Source: https://docs.laravel-excel.com/3.1/imports/multiple-sheets Implements the HasReferencesToOtherSheets concern in a sheet import class so that formulas referencing other sheets are recognized during import. The class also implements ToArray for row handling. ```php namespace App\Imports; use Maatwebsite\Excel\Concerns\ToArray; use Maatwebsite\Excel\Concerns\HasReferencesToOtherSheets; class FirstSheetImport implements ToArray, HasReferencesToOtherSheets { public function array(array: $row) { } } ``` -------------------------------- ### Customizing Export Filename in Laravel PHP Source: https://docs.laravel-excel.com/nova/1.x/exports/customizations The withFilename() method allows setting a custom name for the exported file, defaulting to the plural resource name with .xlsx extension. It takes a string parameter for the filename. This overrides the automatic generation and can include dynamic elements like timestamps. ```php /** * Get the actions available for the resource. * * @param \Illuminate\Http\Request $request * * @return array */ public function actions(Request $request) { return [ (new DownloadExcel)->withFilename('users-' . time() . '.xlsx'), ]; } ``` -------------------------------- ### Skip Failures in Laravel Excel Import Source: https://docs.laravel-excel.com/3.1/imports/validation Shows how to use the SkipsOnFailure concern to handle failures individually without rolling back the entire import. The onFailure method receives all failures. ```PHP setWidth('A', 5); // Set width for multiple cells $sheet->setWidth(array( 'A' => 5, 'B' => 10 )); ``` -------------------------------- ### Collect Failures After Import in Laravel Excel Source: https://docs.laravel-excel.com/3.1/imports/validation Shows how to collect and iterate over all failures after an import using the failures method provided by the SkipsFailures trait. ```PHP $import = new UsersImport(); $import->import('users.xlsx'); foreach ($import->failures() as $failure) { $failure->row(); // row that went wrong $failure->attribute(); // either heading key (if using heading row concern) or column index $failure->errors(); // Actual error messages from Laravel validator $failure->values(); // The values of the row that has failed. } ``` -------------------------------- ### Disable First Row as Headers Source: https://docs.laravel-excel.com/2.1/import/extra Prevents Laravel Excel from treating the first row as column headers and attribute names for the collection. This is useful when your data doesn't have headers or you want to process all rows as data. Can be configured globally in import.php or applied per import. ```php $reader->noHeading(); ``` -------------------------------- ### Set cell background in Laravel Excel Source: https://docs.laravel-excel.com/2.1/export/cells Illustrates setting the background color of cells in Laravel Excel using hexadecimal color codes. ```PHP $cells->setBackground('#000000'); ``` -------------------------------- ### Track row numbers in Laravel Excel chunk reading Source: https://docs.laravel-excel.com/3.1/imports/chunk-reading Implementation using RemembersRowNumber trait with chunk reading to track current row numbers during import. The getRowNumber method provides the current row number within the model method. This approach is specifically intended for ToModel imports and helps maintain row context during chunked processing. ```php 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; } } ``` -------------------------------- ### Separate Font Setters in PHP Source: https://docs.laravel-excel.com/2.1/export/sheet-styling Apply specific font attributes using individual setter methods in Laravel Excel. Requires a sheet object; methods like setFontFamily, setFontSize, setFontBold take specific values. Modifies sheet's font properties; individual setters may override previous settings. ```PHP // Font family $sheet->setFontFamily('Comic Sans MS'); // Font size $sheet->setFontSize(15); // Font bold $sheet->setFontBold(true); ``` -------------------------------- ### Custom Empty Row Logic in Laravel Excel Source: https://docs.laravel-excel.com/3.1/imports/validation Shows how to define custom logic for skipping rows by implementing the isEmptyWhen method in the importer, allowing for flexible row skipping conditions. ```PHP year = $year; } /** * @return array */ public function sheets(): array { $sheets = []; for ($month = 1; $month <= 12; $month++) { $sheets[] = new InvoicesPerMonthSheet($this->year, $month); } return $sheets; } } ``` -------------------------------- ### Style Sheets using HTML Attributes in Laravel Excel Source: https://docs.laravel-excel.com/2.1/blade/styling Applies HTML attributes like align, valign, rowspan, colspan, width, and height to table cells for layout and alignment. Interpreted by Laravel Excel into sheet formatting. Useful for table-like structures in Excel exports with predefined dimensions. ```html Big title Bold cell Bold cell Italic cell Cell with width of 100 Cell with height of 100 ``` -------------------------------- ### Disable Intelligent Formatting in PHP Excel Export Source: https://docs.laravel-excel.com/3.1/exports/column-formatting This simple PHP class extends StringValueBinder to disable intelligent value formatting, treating all input as strings. It implements WithCustomValueBinder for uniform string handling across the export. Suitable when precise string control is needed without automatic type conversions. ```php namespace App\Exports; use Maatwebsite\Excel\Concerns\WithCustomValueBinder; class UsersExport extends \PhpOffice\PhpSpreadsheet\Cell\StringValueBinder implements WithCustomValueBinder { } ``` -------------------------------- ### Styling Rows, Cells, Columns with WithStyles Array in PHP Source: https://docs.laravel-excel.com/3.1/exports/column-formatting The WithStyles concern (v3.1.21+) enables styling via an array in the styles method, targeting rows by number, cells by coordinates, or columns by letter. It depends on Maatwebsite\Excel and PhpOffice\PhpSpreadsheet\Worksheet\Worksheet. Inputs are style definitions; outputs a styled Worksheet. Limitations: Styles must match PhpSpreadsheet formats. ```PHP 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]], ]; } } ``` -------------------------------- ### Setting Export Writer Type in Laravel PHP Source: https://docs.laravel-excel.com/nova/1.x/exports/customizations The withWriterType() method customizes the export format based on the specified writer, such as CSV or XLSX, instead of defaulting to the filename extension. It requires the Excel class constant for the desired format. Available formats are listed in the export formats documentation. ```php /** * Get the actions available for the resource. * * @param \Illuminate\Http\Request $request * * @return array */ public function actions(Request $request) { return [ (new DownloadExcel)->withWriterType(\Maatwebsite\Excel\Excel::CSV), ]; } ``` -------------------------------- ### Upsert Models with Unique Identifier Source: https://docs.laravel-excel.com/3.1/imports/model Implements WithUpserts concern to perform upsert operations instead of basic inserts. The uniqueBy() method specifies which column(s) uniquely identify records. Uses Laravel's upsert method internally. ```php class UsersImport implements ToModel, WithUpserts { /** * @return string|array */ public function uniqueBy() { return 'email'; } } ``` -------------------------------- ### Excluding Columns from Export in Laravel PHP Source: https://docs.laravel-excel.com/nova/1.x/exports/customizations The except() method omits specified columns from the export, defaulting to index fields minus exclusions; combine with allFields() for all attributes except selected ones. It takes variable string parameters for columns to exclude. This helps remove sensitive or irrelevant data. ```php public function actions(Request $request) { return [ (new DownloadExcel)->except('email'), ]; } ``` ```php public function actions(Request $request) { return [ (new DownloadExcel)->allFields()->except('email'), ]; } ``` -------------------------------- ### Skip Empty Rows in Laravel Excel Import Source: https://docs.laravel-excel.com/3.1/imports/validation Demonstrates using the SkipsEmptyRows concern to skip empty rows during both validation and import, useful with required validation rules. ```PHP [ 'required', 'string', ], ]; } } ``` -------------------------------- ### Set row height in Laravel Excel Source: https://docs.laravel-excel.com/2.1/export/sizing Adjusts the height of one or multiple rows in a Laravel Excel sheet. Accepts either a single row number with height or an array of row-height pairs. ```PHP // Set height for a single row $sheet->setHeight(1, 50); // Set height for multiple rows $sheet->setHeight(array( 1 => 50, 2 => 25 )); ``` -------------------------------- ### Disable Auto Heading Generation (PHP) Source: https://docs.laravel-excel.com/2.1/export/array Shows how to disable automatic heading generation by setting the 5th parameter of `->fromArray()` to `false`. This prevents the keys of the array from being used as header columns. ```PHP // Won't auto generate heading columns $sheet->fromArray($data, null, 'A1', false, false); ``` -------------------------------- ### Append row in Laravel Excel Source: https://docs.laravel-excel.com/2.1/export/rows Illustrates how to add new rows after specific positions or at the end of the sheet in Laravel Excel. ```PHP // Append row after row 2 $sheet->appendRow(2, array( 'appended', 'appended' )); // Append row as very last $sheet->appendRow(array( 'appended', 'appended' )); ``` -------------------------------- ### Map Multi-dimensional Repeating Cells in Laravel Excel Source: https://docs.laravel-excel.com/3.1/imports/mapped-cells Defines nested cell coordinates to import repeating groups of data. The mapping() method returns arrays of cell addresses for each group (e.g., team1 and team2), producing nested arrays of row data keyed by the group names. Each element contains fields such as name and score. Dependencies include Laravel and maatwebsite/excel. Limitation: only cell references are allowed, not column mapping. ```php use Maatwebsite\Excel\Concerns\WithMappedCells; class ScoresImport implements WithMappedCells { public function mapping(): array { return [ 'team1' => [ [ 'name' => 'A2', 'score' => 'B2', ], [ 'name' => 'A3', 'score' => 'B3', ], ], 'team2' => [ [ 'name' => 'C2', 'score' => 'D2', ], [ 'name' => 'C3', 'score' => 'D3', ], ], ]; } public function model(array $row) { // Example: persist or process the row if needed // This example simply returns null as multi-dimensional mapping // often returns structured data for further processing rather than a model. return null; } } ``` -------------------------------- ### Reference other fields in Laravel Excel validation Source: https://docs.laravel-excel.com/3.1/imports/validation This snippet shows how to reference other field names in validation rules, such as using 'gte' to compare fields, with the field name prefixed with '*.'. ```PHP public function rules(): array { return [ 'maximum' => 'gte:*.minimum', ]; } ``` -------------------------------- ### Cascading Relation Persistence Source: https://docs.laravel-excel.com/3.1/imports/model Implements PersistRelations concern to enable saving of BelongsTo and BelongsToMany relations along with the main model. Sets relations using setRelation() method before returning the model. Not compatible with BatchInserts. ```php class UsersImport implements ToModel, PersistRelations { public function model(array $row) { $user = new User([ 'name' => $row[0], ]); // There should be a `public function team(): BelongsTo` relation in the User model. $user->setRelation('team', new Team(['name' => $row[1]])); return $user; } } ``` -------------------------------- ### Use SkipsFailures Trait in Laravel Excel Source: https://docs.laravel-excel.com/3.1/imports/validation Illustrates using the SkipsFailures trait to automatically skip failed rows and collect failures at the end of the import, simplifying failure handling. ```PHP setAlignment('center'); ``` -------------------------------- ### Style Sheets using HTML Tags in Laravel Excel Source: https://docs.laravel-excel.com/2.1/blade/styling Embeds HTML tags like headings, bold, italic, and images directly in table cells for styling. Supported by Laravel Excel's HTML interpretation, configurable in view.php. Outputs Excel sheets with rich text elements derived from HTML. ```html

Big title

Bold cell Bold cell Italic cell ``` -------------------------------- ### Set vertical alignment in Laravel Excel Source: https://docs.laravel-excel.com/2.1/export/cells Illustrates setting the vertical alignment of cells in Laravel Excel, such as middle alignment. ```PHP $cells->setValignment('center'); ``` -------------------------------- ### Custom validation messages in Laravel Excel Source: https://docs.laravel-excel.com/3.1/imports/validation This snippet demonstrates how to specify custom validation messages for each failure by implementing the customValidationMessages method in Laravel Excel imports. ```PHP /** * @return array */ public function rules(): array { return [ '1' => Rule::in(['patrick@maatwebsite.nl']), ]; } /** * @return array */ public function customValidationMessages() { return [ '1.in' => 'Custom message for :attribute.', ]; } ``` -------------------------------- ### Custom validation attributes in Laravel Excel Source: https://docs.laravel-excel.com/3.1/imports/validation This snippet shows how to specify custom attribute names for each column by implementing the customValidationAttributes method in Laravel Excel imports. ```PHP /** * @return array */ public function rules(): array { return [ '1' => Rule::in(['patrick@maatwebsite.nl']), ]; } /** * @return array */ public function customValidationAttributes() { return ['1' => 'email']; } ``` -------------------------------- ### Collect Import Errors in PHP Source: https://docs.laravel-excel.com/3.1/imports/validation After performing an import using SkipsErrors, you can retrieve all collected errors using the errors() method. This method returns an array of failures encountered during the import process. ```php $import = new UsersImport(); $import->import('users.xlsx'); dd($import->errors()); ``` -------------------------------- ### Unset Blade view for Excel sheet in Laravel Source: https://docs.laravel-excel.com/2.1/blade/load-view Explains how to remove a shared Blade view for a specific sheet in an Excel file using the `unsetView` method. ```PHP $sheet->unsetView(); ``` -------------------------------- ### Skip Row Errors with SkipsOnError Concern in PHP Source: https://docs.laravel-excel.com/3.1/imports/validation Implement the SkipsOnError concern to handle individual row import failures without rolling back the entire import. The onError method allows custom error handling logic when a database exception occurs. ```php cell('A1', function($cell) { $cell->setValue('data1'); }); ``` -------------------------------- ### Manipulate cell range in Laravel Excel Source: https://docs.laravel-excel.com/2.1/export/cells Shows how to manipulate a range of cells in Laravel Excel. The range is specified (e.g., 'A1:A5') and can be styled or modified collectively. ```PHP $sheet->cells('A1:A5', function($cells) { // manipulate the range of cells }); ``` -------------------------------- ### Configure Validator with Custom Logic in PHP Source: https://docs.laravel-excel.com/3.1/imports/validation Customize the validation process by implementing the withValidator method. This allows adding conditional validation rules, custom error messages using $validator->errors()->add(), or applying rules based on specific conditions using $validator->sometimes(). ```php class UsersImport implements WithValidation { public function withValidator($validator) { $validator->after(function ($validator) { if ($this->somethingElseIsInvalid()) { $validator->errors()->add('field', 'Something is wrong with this field!'); } }); // or... $validator->sometimes('*.email', 'required', $this->someConditionalRequirement()); } } ``` -------------------------------- ### Register Custom Transaction Handler in Laravel Excel Source: https://docs.laravel-excel.com/3.1/imports/validation This snippet shows how to register a custom transaction handler for Laravel Excel, useful for non-traditional databases like MongoDB. The handler must implement the TransactionHandler interface. ```PHP $this->app->make(\Maatwebsite\Excel\Transactions\TransactionManager::class)->extend('your_handler', function() { return new YourTransactionHandler(); }); ``` -------------------------------- ### Validate with heading row in Laravel Excel Source: https://docs.laravel-excel.com/3.1/imports/validation This snippet demonstrates how to use the WithHeadingRow concern to validate rows using heading names as rule attributes in Laravel Excel imports. ```PHP $row['name'], 'email' => $row['email'], 'password' => 'secret', ]); } public function rules(): array { return [ 'email' => Rule::in(['patrick@maatwebsite.nl']), // Above is alias for as it always validates in batches '*.email' => Rule::in(['patrick@maatwebsite.nl']), ]; } } ``` -------------------------------- ### Row Validation without ToModel Concern in PHP Source: https://docs.laravel-excel.com/3.1/imports/validation Perform row-level validation without using the ToModel concern by leveraging Laravel's Validator facade within the collection method. This allows for custom validation rules on each row of the imported data. ```php toArray(), [ '*.0' => 'required', ])->validate(); foreach ($rows as $row) { User::create([ 'name' => $row[0], ]); } } } ``` -------------------------------- ### Disable database transactions in Laravel Excel Source: https://docs.laravel-excel.com/3.1/imports/validation This snippet shows how to disable database transactions during Excel imports by configuring the transaction handler in the Excel config file. ```PHP 'transactions' => [ 'handler' => 'db', ], ``` === COMPLETE CONTENT === This response contains all available snippets from this library. No additional content exists. Do not make further requests.