Setting up Laravel-excel and PhpSpreadsheet macro

Question

I am using Maatwebsite Laravel-excel version 3.1. And I want to set the default styling of the sheet. I have read the documentation about including a macro in your laravel app by setting this in my AppServiceProvider boot() method. :

Sheet::macro('getDefaultStyle',function(Sheet $sheet){
        $sheet->getDefaultStyle();
    });

But when everytime i reload the page it crashes the page and the laravel server in my cmd stops and re run. Here is my Export.php looks like:

 public function registerEvents():array 
{
    return[
        AfterSheet::class=>function(AfterSheet $event){

    $header_style_array = [
        'font'=>['bold'=>true]
    ];
    $style_array = [
        'font'=>['bold'=>true]
    ];
            $event->sheet->getStyle('A1:B1')->getAlignment()->setHorizontal('center');
            $event->sheet->getStyle('A1:B1')->applyFromArray($header_style_array);
            $event->sheet->getDefaultStyle()->getFont()->setSize(5);
    }];
}

I already included use Maatwebsite\Excel\Concerns\WithEvents; use Maatwebsite\Excel\Events\AfterSheet; above my Export.php file.

Is there something that I missed? I find this so hard to set up. And there's little article about setting this up.

Any help would be much appreciated Refs: https://phpspreadsheet.readthedocs.io/en/latest/topics/recipes/#styles https://docs.laravel-excel.com/3.1/exports/extending.html

Answer 1

If you examine the documentation for PhpSpreadsheet, I think you will find that the getDefaultStyle() method is not accessible from the active sheet.

To Laravel Excel, $event->sheet is equivalent to $spreadsheet->getActiveSheet(). This is why your current configuration will not work.

// this doesn't work
// $spreadsheet->getActiveSheet()->getDefaultStyle()->getFont()->setSize(5);

// this does
$spreadsheet->getDefaultStyle()->getFont()->setSize(5);

You should set default styles through the writer in BeforeWriting.

public function registerEvents():array
{
    return [
        BeforeWriting::class=>function(BeforeWriting $event){
            $event->writer->getDefaultStyle()->getFont()->setSize(5);
        },
    ];
}

If you want to turn this into a macro, you should use a Writer macro rather than a Sheet macro.
https://docs.laravel-excel.com/3.1/exports/extending.html#writer

public function registerEvents():array
{
    Writer::macro('setDefaultStyle', function (Writer $writer) {
        $writer->getDefaultStyle()->getFont()->setSize(5);
    });

    return [
        BeforeWriting::class=>function(BeforeWriting $event){
            $event->writer->setDefaultStyle();
        },
    ];
}