Laravel Excel Export: Formatting and Styling Cells

By: Povilas Korop

Laravel-Excel package is great for exporting data. But not a lot of info there about formatting Excel cells – widths, word wraps, fonts etc. So when I encountered this in a client’s project, I decided to write this article with a few tips on this topic.

Default Laravel Export

Let’s create a dummy project with Users table and default Laravel Auth. Then we create 10 random users. Actually, Laravel comes with pre-built Factory class for User model, so all we need to do is to create a UsersTableSeeder.php


class UsersTableSeeder extends Seeder
{
    public function run()
    {
        factory(App\User::class, 10)->create();
    }
}

class UsersTableSeeder extends Seeder

{

public function run()

{

factory(App\User::class, 10)->create();

}

Then, we require Laravel Excel package:


composer require maatwebsite/excel

composer require maatwebsite/excel

Now, we export all users to Excel. To do that, we create a separate class app/Exports/UsersExport.php:


namespace App\Exports;

use App\User;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;

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

    public function headings(): array
    {
        return [
            '#',
            'Name',
            'Email',
            'Created at',
            'Updated at'
        ];
    }

}

namespace App\Exports;

use App\User;

use Maatwebsite\Excel\Concerns\FromCollection;

use Maatwebsite\Excel\Concerns\WithHeadings;

class UsersExport implements FromCollection, WithHeadings

{

public function collection()

{

return User::all();

}

public function headings(): array

{

return [

'#',

'Name',

'Email',

'Created at',

'Updated at'

];

}

Finally, let’s use it from Controller:


namespace App\Http\Controllers;

use App\Exports\UsersExport;
use Maatwebsite\Excel\Facades\Excel;

class ExportController extends Controller
{

    function export()
    {
        return Excel::download(new UsersExport, 'users.xlsx');
    }

}

namespace App\Http\Controllers;

use App\Exports\UsersExport;

use Maatwebsite\Excel\Facades\Excel;

class ExportController extends Controller

{

function export()

{

return Excel::download(new UsersExport, 'users.xlsx');

}

Here’s the visual result:

It does the job, exports the data. But does it look good and readable? Far from it. So let’s tweak it a little.

Step 1. Auto-formatting column widths

Somewhere deep in the official documentation I’ve found a really easy way to adjust the column sizes automatically. All you need is to use one Trait ShouldAutoSize:


// ...
use Maatwebsite\Excel\Concerns\ShouldAutoSize;

class UsersExport implements FromCollection, WithHeadings, ShouldAutoSize
{
// ...

// ...

use Maatwebsite\Excel\Concerns\ShouldAutoSize;

class UsersExport implements FromCollection, WithHeadings, ShouldAutoSize

{

// ...

And here’s the new Excel:

Better, huh? Let’s move on.

Step 2. Formatting the fonts and sizes

You can adjust any cell styles using method called registerEvents() and AfterSheet event:


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

class UsersExport implements FromCollection, WithHeadings, ShouldAutoSize, WithEvents
{

    // ...

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

}

// ...

use Maatwebsite\Excel\Concerns\WithEvents;

use Maatwebsite\Excel\Events\AfterSheet;

class UsersExport implements FromCollection, WithHeadings, ShouldAutoSize, WithEvents

{

// ...

/**

* @return array

public function registerEvents(): array

{

return [

AfterSheet::class => function(AfterSheet $event) {

$cellRange = 'A1:W1'; // All headers

$event->sheet->getDelegate()->getStyle($cellRange)->getFont()->setSize(14);

];

}

Here’s how the header row looks now:

The main logic here is this line:


$event->sheet->getDelegate()->getStyle($cellRange)->getFont()->setSize(14);

$event->sheet->getDelegate()->getStyle($cellRange)->getFont()->setSize(14);

It just takes whatever cell range we pass, and changes the styles.
Here’s where we need to dig deeper and look at the package which is the base of Laravel Excel. It’s actually based on PHPSpreadsheet package. So let’s take a look at its documentation, and specifically section Recipes.

Some examples from there:

Wrap text:


$spreadsheet->getActiveSheet()->getStyle('A1:D4')
    ->getAlignment()->setWrapText(true);

$spreadsheet->getActiveSheet()->getStyle('A1:D4')

->getAlignment()->setWrapText(true);

Default style of a worksheet:


$spreadsheet->getDefaultStyle()->getFont()->setName('Arial');
$spreadsheet->getDefaultStyle()->getFont()->setSize(8);

$spreadsheet->getDefaultStyle()->getFont()->setName('Arial');

$spreadsheet->getDefaultStyle()->getFont()->setSize(8);

Styling borders and applying styles from array:


$styleArray = [
    'borders' => [
        'outline' => [
            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK,
            'color' => ['argb' => 'FFFF0000'],
        ],
    ],
];

$worksheet->getStyle('B2:G8')->applyFromArray($styleArray);

$styleArray = [

'borders' => [

'outline' => [

'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK,

'color' => ['argb' => 'FFFF0000'],

];

$worksheet->getStyle('B2:G8')->applyFromArray($styleArray);

You can find more examples in that recipes section, just apply it to your individual needs.

That’s it, that’s how you format exported excel with Laravel and PHP.

Want to generate Laravel adminpanel online?
You don't need any packages to do that!

QuickAdminPanel.com

5 thoughts on “Laravel Excel Export: Formatting and Styling Cells”

Pidrow says:

June 27, 2018 at 4:21 pm

Hello.

I was wondering, what about the route? and if I want to click a button in a blade view to trigger the download, how should I do it? Thanks!

1. Povilas Korop says:
  
  June 28, 2018 at 4:47 am
  
  Hi, sorry, I didn’t mention this in the article, cause I thought it was pretty basic thing people would figure out.
  
  In this article I’m using controller ExportController and method export(), so you need to add this line to your routes:
  Route::get('export', 'ExportController@export')->name('export');
  
  And then somewhere in your blade files:
  <a href="{{ route('export') }}">Export</a>
  
Richard says:

July 1, 2018 at 1:09 pm

Nice article!
I expected to see the final result at the end though, but alas..

Jerome says:

July 19, 2018 at 12:21 pm

Hi,
I was wondering in which file do I put this line :
$spreadsheet->getActiveSheet()->getStyle(‘A1:D4’)
->getAlignment()->setWrapText(true);
Thanks
Jerome

Dapo says:

November 1, 2018 at 9:31 am

Beautiful article, but in the case of importing a spreadsheet file using this package how do we catch errors, let’s say an invalid file was uploaded, also in the case where we expect the uploaded file to have some named headings how do we check if the headings are there? Thanks

Laravel Daily