Tony Joe Dev
Posted on November 19, 2023
"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.
Steps
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);
}
}
The Customer Model:
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:
composer require maatwebsite/excel:^3.1
Publish the config file config/excel.php:
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:
php artisan make:export OrdersExport --model=Order
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();
}
}
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
{
// ...
}
}
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',
];
}
}
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";
}
}
And finally, the route:
// routes/web.php
Route::get('/orders/export/{customer?}/{year?}', [OrdersController::class, 'export'])
->name('orders.export');
✸ 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!
Posted on November 19, 2023
Join Our Newsletter. No Spam, Only the good stuff.
Sign up to receive the latest update from our blog.
Related