Hidden Features of the Laravel Excel Package

The Laravel PHP framework is the most widely adopted in the community. It’s built on several rock-solid libraries available these days. Laravel app development services help save time and lets you focus on the core functionality of your business. The framework provides everything, from filing systems, console commands, database migrations, and so forth.

THE LARAVEL EXCEL PACKAGE

A Laravel app development company could offer Laravel Excel solutions. However, what’s the Laravel Excel package all about? Laravel Excel is meant to be Laravel-flavored PhpSpreadsheet, a simple yet elegant wrapper around the PhpSpreadsheet. Its goal is to simplify both imports and exports.

Purely written in PHP, the PhpSpreadsheet is a library that provides classes sets, which enable reading from as well as writing to different spreadsheet formats, including Excel and LibreOffice.

FEATURES OF LARAVEL EXCEL

• Seamless exporting of collections to Excel. Supercharge Laravel collections and directly export to a CSV or Excel document.
• Exports that are supercharged. With automatic chunking of export inquiries. Laravel Excel could handle even bigger sets of data. Exports could be queued so all of this occurs in the background.
• Imports are supercharged. Import worksheets and workbooks to Eloquent models with batch inserts and chunk reading. Big files could be queued so the whole import happens in the background.
• Blade views export. For custom layout in a spreadsheet, consider using an HTML table in Blade view and expert it to Excel.

LARAVEL EXCEL PACKAGE HIDDEN FEATURES

The Laravel Excel package recently has released the 3.0 version with some critical breaking changes. The Laravel Excel 3.0 objective is prioritizing one’s own requirements and then adding convenience methods that are needed and used only, instead of re-inventing the wheel of the PhpSpreadsheet. Less coding for resolving issues, the easier it is to maintain.

Version 3’s milestone, the new features are a great to help in simplifying and streamlining advanced use instances and they are simple to use as well. Consider exploring the hidden feature that you may not know of. These hidden features make Laravel as the go-to solution with Excel.

1. HTML/Blade Export. Imagine you have a list page with HTML table already. The Blade code is this—resources/views/customers/table.blade.php:

   @foreach ($customers as $customer)
   
   @endforeach
   

   

      

         

         
First name
         
Last name
         
Email
         
Created at
         
Updated at
     
   
   

     
{{ $customer->id }}
     
{{ $customer->first_name }}
     
{{ $customer->last_name }}
 
     
{{ $customer->email }}
     
{{ $customer->updated_at }}
   

It could be used again to export to Excel the same table.

Step 1: Export class generation.

php artisan make:export CustomersFromView –model=Customer

Step 2: FromView usage to do the operation.

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

Remember that you could export the HTML table only without any layout tags, like div, html, body and so forth;

2. HTML, PDF export and others. Although it is called the Laravel Excel package, it also provides export to other formats too. The use is pretty straightforward. It only takes adding more parameter to the class.

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

Furthermore, it enables PDF exporting and you could also make a selection from three libraries. All it takes is specifying the format as the last parameter. Moreover, you should also install a PDF package of choice via the composer, like this:

composer require dompdf/dompdf

3. Cell formatting in whatever way you want it to look. Laravel Excel has a robust ‘parent’, which is called the PhpSpreadSheet. Therefore it adopts all of the underneath functionalities, which include several ways of formatting. Check out how to do it in the Laravel Export class, app/Exports/CustomersExportStyling.php:

Step 1: Using the right classes in the header.

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

Step 2: WithEvents use in the implements section.

class CustomersExportStyling implements FromCollection, WithEvents
{
    // …

Step 3. Make a registerEvents() method using AfterSheet event.

/**
* @return array
*/
public function registerEvents(): array
{
     return [
        AfterSheet::class    => function(AfterSheet $event) {
        // ...you can do any formatting here
       },
    ],
}

This is an example:

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

4. Hidden Fields. Imagine you have seeded a Laravel 5.7 users table default. Take into account exporting this with FromCollection class:

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

In the Excel result, you would see some missing fields: password and remember…token. This is due to the app/User.php property:

class User extends Authenticatable
{
   // …
   /**
    * The attributes that should be hidden for arrays.
    *
    * @var array
    */
   protected $hidden = [
        'password', 'remember_token',
   ];
}

By default, the fields are hidden. However, it showcases the behavior of the Laravel Excel package. If you must or want to protect export fields, you could directly do it in the model. 5. The formulas. The official Laravel Excel package documentation, for some reason do not mention anything regarding formulas. This is however the whole point of why use Excel. Fortunately, it’s pretty straightforward writing formulas to an exported file. You have to set cell values the same as in Excel, such as for instance, =A2+1 or SUM(A1:A10).