How to easily export in Excel or CSV with Laravel
Tags: laravel, tutorial, webdev, excel
Published: 2023-11-19 17:20:53

> "*Hey Tony* 🙏, *I need to export those results to Excel by tomorrow morning otherwise I get fired!* 🤯"

Has something like this ever happened to you?

*Ok, keep calm and don't reinvent the wheel.*

---

Fortunately, the Laravel ecosystem is wonderful and provides us with truly great tools. This is the case with the package we are going to rely on now:  
**👉 Laravel Excel** ([laravel-excel.com](https://laravel-excel.com/)).

This package can be used to manage many aspects of both data export and import.

**Here we will focus on data export**, in a very common situation, that is, when the data source is a **Model** and therefore, presumably, the corresponding **table in the DB**.

![Export Excel or CSV with Laravel](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gclihz60pypi68rp3cpw.jpg)

## Steps

1. [Before we go, what is the Model?](#1-before-we-go-what-is-the-model)
    
2. [Install the Laravel Excel package](#2-install-the-laravel-excel-package)
    
3. [Create the export class](#3-create-the-export-class)
    
4. [Create the controller and open a route](#4-create-the-controller-and-open-a-route)
    

## 1\. Before we go, what is the Model?

Let's assume we have *Orders*, each of which is connected to a *Customer*.

The `Order` Model:

```php
class Order extends Model
{
    protected $fillable = [
        'code',
        'status',
        'amount',
        'notes',
    ];

    public function customer(): BelongsTo
    {
        return $this->belongsTo(Customer::class);
    }
}
```

The `Customer` Model:

```php
class Customer extends Model
{
    protected $fillable = [
        'business_name',
        'vat',
        'email',
    ];

    public function orders(): HasMany
    {
        return $this->hasMany(Order::class);
    }
}
```

---

## 2\. Install the Laravel Excel package

*Let's start!*

Install the package:

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

Publish the config file `config/excel.php`:

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

*You can find many default parameters in the configuration file that you can customize if necessary. But right now you can just move on.*

---

## 3\. Create the export class

Once the package has been installed, we have the `make:export` generator available.

We use it now:

```bash
php artisan make:export OrdersExport --model=Order
```

Ok, now let's open the newly created class:

```php
// app/Exports/OrdersExport.php

namespace App\Exports;

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

class OrdersExport implements FromCollection
{
    /**
    * @return \Illuminate\Support\Collection
    */
    public function collection()
    {
        return Order::all();
    }
}
```

This is a really basic version and we will almost certainly need to modify it.

First of all, let's remove the implementation of the `FromCollection` interface and replace it with the `FromQuery` interface. In this way, the query will be executed in *chunks*.

Furthermore, we add:

```php
class OrdersExport implements FromQuery, WithHeadings, WithMapping, WithCustomCsvSettings
{
    use Exportable;

    // ...

    /**
     * Prepare the query for data export
     */
    public function query()
    {
        // ...
    }

    /**
     * Customize the csv header (first row)
     */
    public function headings(): array
    {
        // ...
    }

    /**
     * Get and (eventually) customize single row
     */
    public function map($order): array
    {
        // ...
    }

    /**
     * Customize CSV seettings
     */
    public function getCsvSettings(): array
    {
        // ...
    }
}
```

Finally, here is the **complete version** of the `OrdersExport` class, in which we also manage 2 very simple filters, on *Customer* and on the reference *year*:

```php
// app/Exports/OrdersExport.php

namespace App\Exports;

use App\Models\Order;
use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\FromQuery;
use Maatwebsite\Excel\Concerns\WithCustomCsvSettings;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithMapping;

class OrdersExport implements FromQuery, WithHeadings, WithMapping, WithCustomCsvSettings
{
    use Exportable;

    private ?Customer $customer;
    private ?int $year;

    /**
     * Filter orders by specific Customer
     */
    public function forCustomer(?Customer $customer): self
    {
        $this->customer = $customer;

        return $this;
    }

    /**
     * Filter orders by specific year
     */
    public function forYear(?int $year): self
    {
        $this->year = $year;

        return $this;
    }

    /**
     * Prepare the query for data export
     */
    public function query()
    {
        $q = Order::query()->with(['customer']);

        if ($this->customer != null) {
            $q->where('customer_id', $this->customer->id);
        }

        if (filled($this->year) && $this->year > 1970) {
            $q->whereYear('created_at', $this->year);
        }

        return $q->latest();
    }

    /**
     * Customize the csv header (first row)
     */
    public function headings(): array
    {
        return [
            'Order ID',
            'Order Code',
            'Order Status',
            'Order Amount',

            'Customer Business Name',
            'Customer VAT',
            'Customer Email',

            'Order Notes',
            'Created At',
            'Last Updated At',
        ];
    }

    /**
     * Get and (eventually) customize single row
     */
    public function map($order): array
    {
        return [
            $order->id,
            $order->code,
            $order->status,
            $order->amount,

            $order->customer?->business_name ?? '(Unknown)',
            $order->customer?->vat,
            $order->customer?->email,

            $order->notes ?? '(No notes)',
            $order->created_at,
            $order->updated_at,
        ];
    }

    /**
     * Customize CSV seettings
     */
    public function getCsvSettings(): array
    {
        return [
            'delimiter' => ',',
            'use_bom' => false,
            'output_encoding' => 'UTF-8',
        ];
    }
}
```

---

## 4\. Create the controller and open a route

Now that the `OrdersExport` class is ready, we are almost done. All we have to do is use it in a controller and then open a specific route.

Here is an example controller:

```php
// app/Http/Controllers/OrdersController.php

namespace App\Http\Controllers;

use App\Exports\OrdersExport;

class OrdersController extends Controller
{
    public function export(?Customer $customer = null, ?int $year = null)
    {
        $filename = $this->buildFilename('orders', $customer, $year);

        return (new OrdersExport)
            ->forCustomer($customer)
            ->forYear($year)
            ->download($filename);
    }

    protected function buildFilename($basename, ?Customer $customer = null, ?int $year = null)
    {
        $customerfmt = ($customer)
            ? \Str::slug($customer->business_name)
            : 'anycustomer';

        $yearfmt = filled($year) ? $year : 'anytime';
        $today = date('Ymd');

        return "{$basename}-{$customerfmt}-{$yearfmt}-{$today}.csv";
    }
}
```

And finally, the route:

```php
// routes/web.php

Route::get('/orders/export/{customer?}/{year?}', [OrdersController::class, 'export'])
    ->name('orders.export');
```

---

✸ Enjoy your coding!

&nbsp;

###### If you liked this post, don't forget to add your Follow to my profile!

{% user tonyjoe %}

---

###### *If you want to preview my content, [Subscrive to my Newsletter](https://tonyjoe.dev/newsletter)*!