DEV Community

Cover image for How to easily export in Excel or CSV with Laravel
Tony Joe Dev
Tony Joe Dev

Posted on • Originally published at tonyjoe.dev

How to easily export in Excel or CSV with Laravel

"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).

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

Steps

  1. Before we go, what is the Model?

  2. Install the Laravel Excel package

  3. Create the export class

  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:

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

    public function customer(): BelongsTo
    {
        return $this->belongsTo(Customer::class);
    }
}
Enter fullscreen mode Exit fullscreen mode

The Customer Model:

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

    public function orders(): HasMany
    {
        return $this->hasMany(Order::class);
    }
}
Enter fullscreen mode Exit fullscreen mode

2. Install the Laravel Excel package

Let's start!

Install the package:

composer require maatwebsite/excel:^3.1
Enter fullscreen mode Exit fullscreen mode

Publish the config fileΒ config/excel.php:

php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider" --tag=config
Enter fullscreen mode Exit fullscreen mode

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:

php artisan make:export OrdersExport --model=Order
Enter fullscreen mode Exit fullscreen mode

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

// 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();
    }
}
Enter fullscreen mode Exit fullscreen mode

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:

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
    {
        // ...
    }
}
Enter fullscreen mode Exit fullscreen mode

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:

// 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',
        ];
    }
}
Enter fullscreen mode Exit fullscreen mode

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:

// 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";
    }
}
Enter fullscreen mode Exit fullscreen mode

And finally, the route:

// routes/web.php

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

✸ Enjoy your coding!

Β 

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

If you want to preview my content, Subscrive to my Newsletter!

Top comments (0)