How to set background color for row in Laravel Excel?

Question

I use Laravel Excel library and I have tried this code:

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

            $styleArray =  array('fill' => array(
                'color' => array('rgb' => '000000')
            ));

            $cellRange = 'A1:W1'; // All headers
            $event->sheet->getDelegate()->getStyle($cellRange)->applyFromArray($styleArray);
        },
    ];
}

As result I get headers without black background color.

Also I tried this array settings:

$styleArray = [
                    'font' => [
                        'bold' => true,
                    ],
                    'background' => [
                        'color'=> '#000000'
                    ]
                ];

I use events, not creating. Please don't recommend not relevant answers

Shum

Possible duplicate of How can I change color a character laravel excel maatwebsite? – Photochronograph

That is different question, about background – Shum

IMHO they are both about styling in Laravel Excel the code is nearly the same. – Photochronograph

Answer 1

Try this

$sheet->row(1, ['Col 1', 'Col 2', 'Col 3']); // etc etc
$sheet->row(1, function($row) { $row->setBackground('#CCCCCC'); });

You can also change $sheet->row() to $sheet->cell() and keep passing a row number as first argument.

$sheet->cell(1, function($row) { 
    $row->setBackground('#CCCCCC'); 
});

Then You can also use a more Excel-ish notation :

$sheet->cells('A1:D1', function ($cells) {
    $cells->setBackground('#008686');
    $cells->setAlignment('center');
});

Watersoak

I dont have $sheet object – Shum

Can you tell us how to get the $sheet? is that the $event->sheet? – Ethnocentrism

i have the same code the background-color does not apply on the last cell – Prodigious

Answer 2

As per New Laravel-excel 3.1 one can use macro for styling https://docs.laravel-excel.com/3.1/imports/extending.html#macroable

You can use the macro for styling like i have done.

I added Following code before start of class

Sheet::macro('styleCells', function (Sheet $sheet, string $cellRange, array $style) {
    $sheet->getDelegate()->getStyle($cellRange)->applyFromArray($style);
});

Then under event of AfterSheet i used it.

You can register event like following

public function registerEvents(): array
{
        return [
            AfterSheet::class => [self::class, 'afterSheet']
        ];
}

Then define that static function.

public static function afterSheet(AfterSheet $event){
//Single Column
$event->sheet->styleCells(
            'A1',
            [
                'alignment' => [
                    'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                ],
                'fill' => [
                    'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
                    'color' => ['argb' => $singleHeaderColumnColorCode]
                ]
            ]
        );

//Range Columns
$event->sheet->styleCells(
            'B2:E2',
            [
                'alignment' => [
                    'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                ],
                'fill' => [
                    'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
                    'color' => ['argb' => $mergedAndCenterHeaderSubColumnColorCode]
                ]
            ]
        );
}

Make sure you have added the interface WithEvents

As shown in image below, This is how i am doing for my export.

Purvis

Hi, what is $singleHeaderColumnColorCode. What is the format if requires? – Fascicule

@Čamo you can write rgb code there. 'color' => ['argb' => "c0c0c0"] – Embay

Answer 3

This code works for me. Add this code in your export file within the styles function

$sheet->getStyle('A1:P1')->getFill()->applyFromArray(['fillType' => 'solid','rotation' => 0, 'color' => ['rgb' => 'D9D9D9'],]);

Triatomic

Nice answer but the rotation attribute seems totally unnecessary. For me it works without it. – Clarify