Let’s talk about five little-known functions of Laravel Excel

Laravel Excel package Recently released version 3.0, it has new features that can help simplify advanced needs and is extremely usable. Let’s explore some of the hidden features you may not know that make Laravel Excel the best choice for Excel extensions.

1. Import data from HTML or Blade

Assume there is already an HTML table

Template code -- resources/views/customers/table.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>

You can use it to repeatedly import this table into Excel

Step 1. Generate an Export class

php artisan make:export CustomersFromView --model=Customer

Step 2. Use FromView to operate

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()
        ]);
    }
}

Here is the imported Excel Let’s talk about five little-known functions of Laravel Excel:

Note: Only HTML tables can be exported here and cannot have any Tags, such as html, body, div, etc.

---

2. Export to PDF, HTML, or other format Let’s talk about five little-known functions of Laravel Excels

Although the name of the package is Laravel Excel, it provides a variety of export formats and is very simple to use. , just add another parameter to the class:

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

For example, if you do this, it will be exported to HTML, as shown below:

Not too There are many styles, here is the source code:

Not only that, it can also be exported to PDF, and you can even choose three libraries from it. The usage method is the same, you Just specify the format in the last parameter. Here are some examples. Document Example:

Note: You must install the specified PDF package through composer, for example:

composer require dompdf/dompdf

The exported PDF is as follows Display:

---

3. Format cells as needed

Laravel Excel has a powerful "dad" -- PhpSpreadsheet . So it has its various underlying functions, including various ways of cell formatting.

Here is an example of how to use it in a Laravel Export class, such as app/Exports/CustomersExportStyling.php:

Step 1. Introduce the appropriate class in the header.

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

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

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

Step 3. Use the AfterSheet event to create the registerEvents() method.

/**
 * @return array
 */
public function registerEvents(): array
{
    return [
        AfterSheet::class    => function(AfterSheet $event) {
            // ... 此处你可以任意格式化
        },
    ];
}

Here is an example:

/**
 * @return array
 */
public function registerEvents(): array
{
    return [
        AfterSheet::class    => function(AfterSheet $event) {
            // 所有表头-设置字体为14
            $cellRange = 'A1:W1';
            $event->sheet->getDelegate()->getStyle($cellRange)->getFont()->setSize(14);

            // 将样式数组应用于B2：G8范围单元格
            $styleArray = [
                'borders' => [
                    'outline' => [
                        'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK,
                        'color' => ['argb' => 'FFFF0000'],
                    ]
                ]
            ];
            $event->sheet->getDelegate()->getStyle('B2:G8')->applyFromArray($styleArray);

            // 将第一行行高设置为20
            $event->sheet->getDelegate()->getRowDimension(1)->setRowHeight(20);

            // 设置 A1:D4 范围内文本自动换行
            $event->sheet->getDelegate()->getStyle('A1:D4')
                ->getAlignment()->setWrapText(true);
        },
    ];
}

The results displayed by these "random" samples are as follows:

You can Find all of the above and many more examples in the Recipes page of PhpSpreadsheet docs.

---

4. Hidden model attributes

Assume we have created the Laravel 5.7default users table:

Now we try to use a simple FromCollection to export user table data:

class UsersExport implements FromCollection
{
    public function collection()
    {
        return User::all();
    }
}

In the exported Excel, you can only see the following fields, but There is no password and remember_token:

This is because hidden fields are defined in the User model Attributes:

class User extends Authenticatable
{
    // ...

    /**
     * 这个数组用来定义需要隐藏的字段。
     *
     * @var array
     */
    protected $hidden = [
        'password', 'remember_token',
    ];
}

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

---

5. Formulas

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

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

One of the ways 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,
        ];
    }
}

The above are the five little-known functions of Laravel Excel.

Original address: https://laravel-news.com/five-hidden-features-of-the-laravel-excel-package

Translation address: https:// learnku.com/laravel/t/24161

[Related recommendations: laravel video tutorial]

The above is the detailed content of Let’s talk about five little-known functions of Laravel Excel. For more information, please follow other related articles on the PHP Chinese website!