Laravel Excel - conditional formatting of a cell

Hello,

Using Laravel 9 and maatwebsite excel version 3.1.

When exporting a file from an array, on each line I need to check: If column C is empty, make column A bold.

I read the documentation and saw some examples but I can't understand how to do that.

I have this function in my export class:

public function registerEvents(): array
    {        
        return [
            AfterSheet::class => function(AfterSheet $event) {
        
            //If cell C ('day') is empty, make cell A bold
            $cellRange = 'C';
            $conditional1 = new \PhpOffice\PhpSpreadsheet\Style\Conditional();
            $conditional1->setConditionType(\PhpOffice\PhpSpreadsheet\Style\Conditional::CONDITION_CELLIS);
            $conditional1->setOperatorType(\PhpOffice\PhpSpreadsheet\Style\Conditional:: CONDITION_CONTAINSBLANKS);
            $conditional1->addCondition('0');                
            $conditional1->getStyle()->getFont()->setBold(true);

            $conditionalStyles = $event->sheet->getDelegate()->getStyle('C')->getConditionalStyles();
            $conditionalStyles[] = $conditional1;
            
            $event->sheet->getDelegate()->getStyle('A')->setConditionalStyles($conditionalStyles);
            },
        ];
    }

Then when I try to open the exported file, I get this message:

We found a problem with some content. Do you want us to recover as much as we can? If you trust the source of this workbook, click Yes.

I open the file anyway, and the column that is supposed to be bold is not bold.

It is clear I don't fully understand the documentation of it all.

Can someone please help?

Undest1966

2 years ago

Best Answer

Level 1

It seems you are trying to apply conditional formatting to a cell in a Laravel 9 project using the maatwebsite/excel package (version 3.1) to export data to an Excel file. The goal is to make cell A bold if cell C is empty.

It looks like you are on the right track, but there are a couple of adjustments needed to make the conditional formatting work correctly. Let's modify the registerEvents() function in your export class as follows:

use Maatwebsite\Excel\Concerns{WithEvents, ShouldAutoSize, WithHeadings, WithMapping}; use PhpOffice\PhpSpreadsheet{Style\Font, Style\Conditional, Style\Conditional\Rule, Worksheet};

class YourExportClass implements WithEvents, ShouldAutoSize, WithHeadings, WithMapping { // ...

public function registerEvents(): array
{
    return [
        AfterSheet::class => function(AfterSheet $event) {
            // Get the total number of rows
            $totalRows = count($this->data);

            // Loop through each row and apply conditional formatting
            for ($row = 2; $row <= $totalRows + 1; $row++) {
                $cellValue = $event->sheet->getCell('C' . $row)->getValue();

                if (empty($cellValue)) {
                    // Apply bold font to cell A in the current row
                    $event->sheet->getStyle('A' . $row)->getFont()->setBold(true);
                }
            }
        },
    ];
}

// ...

}

Explanation:

We use the AfterSheet event to apply conditional formatting after the sheet is generated. We get the total number of rows in the data using count($this->data), assuming that $this->data contains the data you are exporting. We then loop through each row (starting from row 2 since row 1 is the header) and check if the value in column C is empty. If it is, we make cell A in that row bold using $event->sheet->getStyle('A' . $row)->getFont()->setBold(true). With this modification, the conditional formatting should be applied correctly, and the cells in column A will be bold if the corresponding cell in column C is empty.

Please make sure to replace YourExportClass with the actual name of your export class. Also, ensure that you have properly defined the properties and methods required for your export class (e.g., data, headings, map, etc.).

Remember to use the correct namespace for AfterSheet and adjust it according to your Laravel project structure. https://www.myjdfaccount.net/

If you are still facing issues, double-check that you have correctly included the necessary classes and used the correct version of the maatwebsite/excel package (version 3.1) in your Laravel project.

i Hope you like my answer thank you.

1 like