Laravel Excel package Version 3.0 has recently been released with new features that can help simplify advanced requirements and are highly usable. Let's explore some hidden functions that may not be known, which make Laravel Excel the best choice for Excel expansion.

1. Importing data from HTML or Blade

Suppose you already have 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 re-import this form to 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 file:

Note: Only HTML tables can be exported here, without any tags, such as html, body, div, etc.

• • • • *

2. Export to PDF, HTML, or other formats

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

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

In this way, for example, HTML is exported, as shown in the following figure:

There aren't too many styles. Here's the source code:

Not only that, it can also be exported to PDF, and even you can choose three libraries from them. The way to use them is the same. You just need to specify the format for the last parameter. Here are some examples.   Example document:

Note: You must install the specified PDF package through composer, such as:

composer require dompdf/dompdf

The derived PDF is as follows:

• • • • *

3. Format cells on demand

Laravel Excel has a powerful "father"-- PhpSpreadsheet . Therefore, it has its various underlying functions, including various forms of cell formatting.

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

Step 1. Introduce the appropriate class in the head.

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. Create the registerEvents() method with the AfterSheet event.

/**
 * @return array
 */
public function registerEvents(): array
{
    return [
        AfterSheet::class    => function(AfterSheet $event) {
            // ... Here you can format it as you like.
        },
    ];
}

Here is an example:

/**
 * @return array
 */
public function registerEvents(): array
{
    return [
        AfterSheet::class    => function(AfterSheet $event) {
            // All headers - Set font to 14
            $cellRange = 'A1:W1';
            $event->sheet->getDelegate()->getStyle($cellRange)->getFont()->setSize(14);

            // Applying Style Arrays 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 height of the first line to 20
            $event->sheet->getDelegate()->getRowDimension(1)->setRowHeight(20);

            // Set up automatic line breaking for text in A1:D4 range
            $event->sheet->getDelegate()->getStyle('A1:D4')
                ->getAlignment()->setWrapText(true);
        },
    ];
}

The results of these "random" examples are as follows:

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

• • • • *

4. Hidden Model Attributes

Suppose we have created the Laravel 5.7 default users table:

Now let's try to export user table data with a simple FromCollection:

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 the properties of hidden fields are defined in the User model:

class User extends Authenticatable
{
    // ...

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

So, by default, these fields are hidden. If you want to export data without some fields being exported, you can directly define the hidden property $hidden in the model.

• • • • *

5. formula

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

Fortunately, we can write formulas directly into the classes that export the data. We need to set the cell value, like this: = A2+1 or SUM(A1:A10).

One way 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,
        ];
    }
}

These are five little-known features of Laravel Excel.

The article is transferred from: https://learnku.com/laravel/t...

More articles: https://learnku.com/laravel/c...