Five hidden features of Laravel Excel

Laravel Excel Package was recently released in version 3.0 with new features that help simplify advanced requirements and are highly available. Let’s take a look at some of the hidden features you may not know that make Laravel Excel the perfect choice for Excel extensions.

1. Import data from HTML or Blade

Suppose you already have an HTML table

Template code – resources/views/customers/table. The blade. PHP:

<table class="table">
    <thead>
    <tr>
        <th></th>
        <th>First name</th>
        <th>Last name</th>
        <th>Email</th>
        <th>Created at</th>
        <th>Updated at</th>
    </tr>
    </thead>
    <tbody>
    @foreach ($customers as $customer)
    <tr>
        <td>{{ $customer->id }}</td>
        <td>{{ $customer->first_name }}</td>
        <td>{{ $customer->last_name }}</td>
        <td>{{ $customer->email }}</td>
        <td>{{ $customer->created_at }}</td>
        <td>{{ $customer->updated_at }}</td>
    </tr>
    @endforeach
    </tbody>
</table>

Copy the code

You can use it to repeatedly import the table into Excel

Step 1. Generate an Export class

php artisan make:export CustomersFromView --model=Customer

Copy the code

Step 2. Perform operations FromView

namespace App\Exports;

use App\Customer;
use Illuminate\Contracts\View\View;
use Maatwebsite\Excel\Concerns\FromView;

class CustomersExportView implements FromView
{
    public function view(): View
    {
        return view('customers.table'['customers' => Customer::orderBy('id'.'desc')->take(100)->get() ]); }}Copy the code

Here is the imported Excel file:

Note: Only HTML tables can be exported, not tags such as HTML, body, div, etc.

---

2. Export to PDF, HTML, or other file formats

Although the package name is Laravel Excel, there are several export formats available and it is as simple as adding one more parameter to the class:

return Excel::download(new CustomersExport(), 'customers.xlsx'.'Html');

Copy the code

For example, if you do this, you export to HTML, as shown below:

Not too many styles, here’s the source code:

Not only that, it can be exported to PDF, and you can even choose from three libraries, using the same method, you only need to specify the format in the last parameter, here are some examples. Document examples:

Note: You must install the specified PDF package using Composer, for example:

composer require dompdf/dompdf

Copy the code

The exported PDF is as follows:

---

3. Format cells as needed

Laravel Excel has a powerful “dad” – PhpSpreadsheet. So it has its various low-level functions, including various forms of cell formatting.

Here is a how in Laravel Export class examples of its use, for example app/Exports/CustomersExportStyling PHP:

Step 1. Introduce the appropriate classes in the header.

use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Events\AfterSheet;

Copy the code

Step 2. Use the WithEvents interface in the implements section.

class CustomersExportStyling implements FromCollection, WithEvents
{
    // ...

Copy the code

Step 3. Create the registerEvents() method with the AfterSheet event.

/ * * * @return array
 */
public function registerEvents(): array
{
    return [
        AfterSheet::class    => function(AfterSheet $event{/ /... You can format it any way you like},]; }Copy the code

Here’s an example:

/ * * * @return array
 */
public function registerEvents(): array
{
    return [
        AfterSheet::class    => function(AfterSheet $event) {// All table headers - set font to 14$cellRange = 'A1:W1';
            $event->sheet->getDelegate()->getStyle($cellRange)->getFont()->setSize(14); // Apply the style array to B2: G8 range cells$styleArray = [
                'borders'= > ['outline'= > ['borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK,
                        'color'= > ['argb'= >'FFFF0000']]]];$event->sheet->getDelegate()->getStyle('B2:G8')->applyFromArray($styleArray); // Set the first row height to 20$event->sheet->getDelegate()->getRowDimension(1)->setRowHeight(20); // Set text wrapping in range A1:D4$event->sheet->getDelegate()->getStyle('A1:D4')
                ->getAlignment()->setWrapText(true); },]; }Copy the code

These “random” samples show the following results:

You can find all of these and more examples in Recipes Page of SPREADSHEET Docs.

---

4. Hide model attributes

Suppose we have created the default Users table for Laravel 5.7:

Now let’s try exporting user table data using a simple FromCollection:

class UsersExport implements FromCollection
{
    public function collection()
    {
        returnUser::all(); }}Copy the code

In the exported Excel, you can only see the following fields without password and remember_token:

This is because the User model defines attributes for hidden fields:

class User extends Authenticatable { // ... /** * This array is used to define fields that need to be hidden. * * @var array */ protected$hidden = [
        'password'.'remember_token',]; }Copy the code

Therefore, these fields are hidden by default. If you want some fields not to be exported when exporting data, you can directly define the hidden property $hidden in the model.

---

Formula for 5.

For some reason, formulas are not mentioned in the official documentation of the Laravel Excel package, but they are an important feature of Excel!

Luckily, we can write the formula directly in the class that exports the data. We need to set the value of the cell like this: =A2+1 or SUM(A1:A10).

One way to do this is to implement the WithMapping interface:

use App\Customer;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithMapping;

class CustomersExportFormulas implements FromCollection, WithMapping
{
    public function collection()
    {
        return Customer::all();
    }

    /**
     * @var Customer $customer
     * @return array
     */
    public function map($customer): array
    {
        return [
            $customer->id,
            '=A2+1'.$customer->first_name,
            $customer->last_name,
            $customer->email, ]; }}Copy the code

Those are the five little-known features of Laravel Excel.

The article from: https://learnku.com/laravel/t/24161 more article: https://learnku.com/laravel/c/translations