Laravel Excel Export: Formatting and Styling Cells

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

Then, we require Laravel Excel package:

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'
        ];
    }

}

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');
    }

}

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
{
// ...

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

}

Here’s how the header row looks now:

The main logic here is this line:

$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);

Default style of a worksheet:

$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);

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.

Related articles:

Like our articles?
Check out our Laravel online courses!

28 COMMENTS

  1. 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!

    • 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>

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

  4. 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

    • try the following:
      public function headings(): array
      {
      return [
      [
      ‘#’,
      ‘Name’,
      ‘Email’,
      ‘Created at’,
      ‘Updated at’
      ], [
      ‘#2’,
      ‘Name2’,
      ‘Email2’,
      ‘Created at2’,
      ‘Updated at2’
      ]
      ];
      }

  6. Hello thank you for your contribution, I have a question if I would like to add a new sheet to your example with a glossary of the results of the first one can be done? is that I have been investigating for some time and I do not get anything thanks in advance greetings

  8. A better and dynamic solution for the cellRange would be:

    $cellRange = ‘A1:’.$event->sheet->getDelegate()->getHighestColumn().’1′; // All headers

  9. Nice Article , I have a question how can I do this? does data are coming from my database. Thank you.

    Branch: XXX111
    PO Number: PO1111
    PO Creater: Username
    Date PO: 4/12/2019

    Inclusion Date: 04/05/2019 TO 04/05/2019 Delivery Date: 04/15/2019
    Supplier: SAmple Supplier

    Description PO Qty RackNO
    7777-Air Freshener 10 D

  10. Hello…Thank you for your useful post…I would like to ask when you use $worksheet or $spreadsheet for example in $spreadsheet->getDefaultStyle()->getFont()->setName(‘Arial’) ….what is exactly $spreadsheet come from…help please

    • Hi Lukcy, I think this might help you.

      Thanks for the post Povilas 🙂

      AfterSheet::class => function(AfterSheet $event) {
      $cellRange = ‘A1:W1’; // All headers

      $sheet = $event->sheet->getDelegate();

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

      $spreadsheet = $event->sheet->getParent()->getProperties()
      ->setCreator(“Maarten Balliauw”)
      ->setLastModifiedBy(“Maarten Balliauw”)
      ->setTitle(“Office 2007 XLSX Test Document”)
      ->setSubject(“Office 2007 XLSX Test Document”)
      ->setDescription(
      “Test document for Office 2007 XLSX, generated using PHP classes.”
      )
      ->setKeywords(“office 2007 openxml php”)
      ->setCategory(“Test result file”);;
      },

  11. Hi,if i to use styling according to query,
    for example :if a row has status 1,then ,that row should be red ,other wise no change
    How can I do this?

  13. i have question how passing parameter to beforeClass

    example :: param costructor
    ———————————————–
    protected $jobdescp;

    public function __construct($jobdescp)
    {
    $this->jobdescp = $jobdescp;
    }
    ————————————————————
    public static function beforeSheet(BeforeSheet $event)
    {
    return [
    $event->sheet->setCellValue(‘A1’,$this->jobdescp)->mergeCells(‘A1:E3’)
    ->getStyle(‘A1:E3’)
    ->getAlignment()
    ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER)
    ->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER)
    ,
    ]}
    if i using like that ” setCellValue(‘A1’,$this->jobdescp)” i get eror $this object non context
    can u help me confused to find solution , thanks for artikel

  15. is there a possibility of limitation when exporting rows and columns? Example: Bring only A-H columns? Because my export only goes to the letter H and it brings me from I to Z without any registration.

    Thanks.

  21. hello sir i am working on laravel excel but i am not able to format cells i am doing the exact as you have told in this article but there is no change on excel can you please help me with it ?? thanks in advance

  22. how to do this dynamically i mean i dont know the cell or row i just want to change background color on row where i can match some value then append my attribute on that particular row.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Or, you can subscribe without commenting.