Laravel Excel Coloring

@Sinnbeck but it coloring full cells, i want coloring based on the export values

@sathishspec You are doing a foreach on a variable that does not exist?

You can bind it to $this

//collection method
$this->certificates = $certificates;

//After hook
foreach ($this->certificates as $value) {

@Sinnbeck but it makes an error, please can u edit my code, because i am confused where we pind these lines......plz

@sathishspec sadly I cannot. I am not an admin or anything like that. Buy can you tell us the error? Then maybe we can help you further. Or show the updated code that is causing the error

@Sinnbeck ErrorException Undefined property: App\Exports\CertificateExport::$certificates

namespace App\Exports;

use Maatwebsite\Excel\Concerns\FromCollection; use Maatwebsite\Excel\Concerns\Exportable; use Maatwebsite\Excel\Concerns\WithMapping; use Maatwebsite\Excel\Concerns\WithHeadings; use Maatwebsite\Excel\Concerns\ShouldAutoSize; use Maatwebsite\Excel\Concerns\WithEvents; use Maatwebsite\Excel\Events\BeforeExport; use Maatwebsite\Excel\Events\AfterSheet; use Maatwebsite\Excel\Concerns\WithColumnFormatting; use Maatwebsite\Excel\Concerns\WithTitle; use DB; use App\User; use App\Certificate; use App\Training;

class CertificateExport implements FromCollection, WithMapping, WithHeadings, ShouldAutoSize, WithColumnFormatting, WithEvents, WithTitle { /** * @return \Illuminate\Support\Collection */

private $data;

use Exportable;
public function __construct(array $data)
{
    $this->data = $data;
}

public function collection()
{
    $search_type = $this->data['search_type'];
    $search_data = $this->data['search'];
    $search_course = $this->data['search_course'];

    $certificates = Certificate::query();

    if ($search_type == 'individual') {
        $certificates = $certificates->where('certificates.type', 'Individual');
    }
    if ($search_type == 'company') {
        $certificates = $certificates->where('certificates.type', 'Company');
        
    }
    if ($search_data) {
    $search = $search_data;
    foreach ($search as $srch) {
        $certificates->where(function ($x) use ($srch) {
            $x->where('users.first_name', 'LIKE', '%' . $srch . '%')->orwhere('users.last_name', 'LIKE', '%' . $srch . '%');
        });
    }
    }
        
    if ($search_course != ''){
        $certificates = $certificates->where('certificates.training_id',$search_course);
    }
    $certificates = $certificates->join('users','users.id','=','certificates.user_id')->get();
    $i = 1;
    foreach ($certificates as $certificate) {
        $certificate->sno = $i++;
        $certificate->name = $certificate->first_name.' '.$certificate->last_name;
        if(@$certificate->training_id)
        {
            $certificate->training_id = $certificate->training->training_name;
        }
        if (@$certificate->approve_status == '1') {
            $certificate->approve_status = 'Approved';
        }elseif(@$certificate->approve_status == '2'){
            $certificate->approve_status = 'Rejected';
        }else{
            $certificate->approve_status = 'Pending';
        }
        if($certificate->training->validity_type == 'Year'){
            $validity_period = (int)$certificate->training->validity_period * 365 ;
        } else{ 
            $validity_period = (int)$certificate->training->validity_period;
        }
        
        $exp_date = date('d-m-Y', strtotime(@$certificate->validity_from.'+'.@$validity_period.' days'));
        $certificate->validity_to = $exp_date;
        $cur_date = date('d-m-Y');
        $d1 = date_create($cur_date);
        $d2 = date_create($exp_date);
        $diff = date_diff($d1,$d2);
        $exp_count = $diff->format("%R%a");
        if($certificate->training->validity_type == 'Days' )
        {
            $certificate->period = $certificate->training->validity_period;
        }else
        {
            $certificate->period = $certificate->training->validity_period * 365;
        }
        if (@$exp_count <= 0) {
            $certificate->certificate_status = 'Expired';
        }elseif(@$exp_count > 0 && @$exp_count < 90){
            $certificate->certificate_status = 'Expiring Soon';
        }elseif(@$exp_count > 90 && @$exp_count < 180){
            $certificate->certificate_status = 'Expiring';
        }else{
            $certificate->certificate_status = 'Valid';
        }
    }
    return $certificates;
    $this->certificates = $certificates;
}
 public function map($report): array
{
    return [
        $report->sno,
        $report->name,
        $report->training_id,
        $report->validity_from,
        $report->validity_to,
        $report->period,
        $report->approve_status,
        $report->type,
        $report->certificate_status,
    ];
}

public function headings(): array
{
    return [ 
        'S.No',
        'Candidate Name',
        'Course Name',
        'Validity From',
        'Validity To',
        'Period (Days)',
        'Approve Status',
        'Candidate Type',
        'Certificate Status',
    ];
}

public function columnFormats(): array
{
    return [
        
        
];
}

public function registerEvents(): array
{
    return [
        AfterSheet::class => function(AfterSheet $event) {
            $event->sheet->getStyle('A1:I1')->applyFromArray([
                'font'=>[
                    'bold'=>true,
                ],                
            ]);
            foreach ($this->certificates as $value) {
            
    if($value->certificate_status == 'Valid'){
    $sheet->cell('J', function($color){
     $color->setBackground('#008000');
   });
  }
  }              
        },
    ];
}

public function title(): string
{
    return 'Certificate Report';
}

}

@sathishspec Swap these lines. You are returning first which means that the method is done

$this->certificates = $certificates;
return $certificates; //this terminates the method and nothing after it is run
    

Thank you @sinnbeck it is almost done but, I am not able to set background color in the excel rows

@sathishspec Great. What happens now? Error? Or just no color? Did you check column J?

@Sinnbeck no color. not able to apply getfill() function and set background color in events

@sathishspec Try this

public function registerEvents(): array
    {
        return [
            AfterSheet::class => function(AfterSheet $event) {
                $event->sheet->getStyle('A1:I1')->applyFromArray([
                    'font'=>[
                        'bold'=>true,
                    ],
                ]);
                foreach ($this->certificates as $index => $value) {

                    if($value->certificate_status == 'Valid'){
                        $sheet->row($index + 1, function($row) { //we set the row here
                            $row->setBackground('#008000');
                        });
                    }
                }
            },
        ];
    }

@sathishspec Why do you increase the last column? That seems wrong? The last column would alway be the same

$lastColumn++; //remove this
$lastColumn = $event->sheet->getHighestColumn(); //and move this to before the first foreach()

@Sinnbeck okay, i will do it, The main thing is to set background color plz help us

@sathishspec Did you test my example above? Did that color in anything?

Or just try coloring everything

public function registerEvents(): array
    {
        return [
            AfterSheet::class => function(AfterSheet $event) {
                $event->sheet->getStyle('A1:I1')->applyFromArray([
                    'font'=>[
                        'bold'=>true,
                    ],
                ]);
                foreach ($this->certificates as $index => $value) {

                    if(true){
                        $sheet->row($index + 1, function($row) { //we set the row here
                            $row->setBackground('#008000');
                        });
                    }
                }
            },
        ];
    }

@Sinnbeck error: Method Maatwebsite\Excel\Sheet::row does not exist.

@sathishspec Hmm Maybe try getting the worksheet

public function registerEvents(): array
    {
        return [
            AfterSheet::class => function(AfterSheet $event) {
                $event->sheet->getStyle('A1:I1')->applyFromArray([
                    'font'=>[
                        'bold'=>true,
                    ],
                ]);
              $sheet = $sheet->getSheet()->getDelegate(); //this line
                foreach ($this->certificates as $index => $value) {

                    if(true){
                        $sheet->row($index + 1, function($row) { //we set the row here
                            $row->setBackground('#008000');
                        });
                    }
                }
            },
        ];
    }

@Sinnbeck Call to undefined method PhpOffice\PhpSpreadsheet\Worksheet\Worksheet::row()

@sathishspec Hmm ok. Did a bit of reading in the docs. Seems you can manually color a range

https://phpspreadsheet.readthedocs.io/en/latest/topics/recipes/#valid-array-keys-for-style-applyfromarray

$highest = $worksheet->getHighestColumn();
$sheet = $sheet->getSheet()->getDelegate(); //this line
                foreach ($this->certificates as $index => $value) {

                    if(true){
                        $sheet->getStyle('A' . $index +1 . ':' . $highest . $index +1)->getFill()
    ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
    ->getStartColor()->setARGB('FFFF0000');
                    }
                }