Backend Knowledge Sharing #11
Hidden features of Laravel Excel Package, Dynamic query filters using Criteria Class
Table of Content
Hidden features of Laravel Excel package
Laravel Excel package is an elegant wrapper around PhpSpreadsheet with the goal of simplifying exports and imports. It recently released version 3, with new features to simplify its use and more flexible to use. This week, Akita Nakarmi shared some of the hidden features which you might not know about.
Exporting from HTML/Blade
Assume you already have a listing page in HTML.
Here is a re-usable code to export a table into Excel.
Step 1. Generate Export class
php artisan make:export UsersFromView --model=UserStep 2. Use FromView to perform the operation.
namespace App\Exports;use App\User;use Illuminate\Contracts\View\View;
use Maatwebsite\Excel\Concerns\FromView;class UsersExportView implements FromView
{
public function view(): View
{
$users = User::orderBy('id', 'desc')->take(100)->get(); return view('users.table', [
'users' => $users
]);
}
}
Here’s the generated Excel file:
Notice: you can export only HTML table, without any layout tags like <html>, <body>, <div> etc.
Format Cells
Laravel Excel package uses PhpSheet library as a parent. So we get all the underneath functionality, along with various cell formattings.
We need to use WithEvents in implement section. Create registerEvents() method with AfterSheet() event. In the AfterSheet event, we can modify the cell according to our requirements. Here’s an example:
The result of this styling is below:
For other cell formatting and styling options, refer Recipes page of PhpSpreadsheet docs.
Dynamic query filters using Criteria Class
With the Laravel framework, it is easy to implement the Repository pattern to our Eloquent Models. We are quite fond of the Laravel 5 Repositories (l5-repository) package. It is used to abstract the data layer, making our application more flexible and easy to maintain. The packages provide many features out of the box with detailed documentation. One of the most used is the Criteria classes which provide a clean approach to apply filters in our database query.
$posts = $this->postRepository
->pushCriteria(PostCriteria::class)
// ->pushCriteria(new PostCriteria(['name' => 'summer']))
// ->first()
// ->paginate(20)
->get();Our PostCriteria class
use Prettus\Repository\Contracts\RepositoryInterface;
use Prettus\Repository\Contracts\CriteriaInterface;class PostCriteria implements CriteriaInterface {public function apply($model, RepositoryInterface $repository)
{
if($searchName = request()->get('name')) {
$model = $model->where('name', $searchName );
}
return $model;
}public function apply($model, RepositoryInterface $repository)
{
if($searchName = request()->get('name')) {
$model = $model->where('name', $searchName );
}
return $model;
}
Kushal has shared how to tweak the standard definition of Criteria class to apply filters dynamically based on key-value pair availability in the parameters array (generally Request).
Here is the gist to his solution.
If you are interested you can read our other blogs,
