PHPExcel How to set conditional formatting to change cell background color based on cells values



PHP Snippet 1:

 $conditional1 = new \PhpOffice\PhpSpreadsheet\Style\Conditional();
    $conditional1->setConditionType(\PhpOffice\PhpSpreadsheet\Style\Conditional::CONDITION_CONTAINSTEXT);
    $conditional1->setOperatorType(\PhpOffice\PhpSpreadsheet\Style\Conditional::OPERATOR_CONTAINSTEXT);
    $conditional1->setText('No');
    $conditional1->getStyle()->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);
    $conditional1->getStyle()->getFont()->setBold(true);
    $conditional1->getStyle()->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID);

    $conditional1->getStyle()->getFill()->getEndColor()->setRGB('FFC7CE');
    $conditionalStyles[] = $conditional1;

    $spreadsheet->getActiveSheet()->getStyle('E4')->setConditionalStyles($conditionalStyles);

PHP Snippet 2:

 $objConditional = new PHPExcel_Style_Conditional();
 $objConditional->setConditionType(PHPExcel_Style_Conditional::CONDITION_CONTAINSTEXT)
    ->setOperatorType(PHPExcel_Style_Conditional::OPERATOR_CONTAINSTEXT)
    ->setText('NO')
    ->getStyle()
    ->applyFromArray(array(
        'font' => array(
             'color' => array(
                  'argb' => 'FF000000'
             )
         ),
         'fill' => array(
             'type' => \PHPExcel_Style_Fill::FILL_SOLID,
             'startcolor' => array(
                 'argb' => 'FFFF0000'
             ),
             'endcolor' => array(
                'argb' => 'FFFF0000'
             )
         )
     )
);
$conditionalStyles = $objPHPExcel->getActiveSheet()
      ->getStyle('B2')
      ->getConditionalStyles();
array_push($conditionalStyles, $objConditional);
$objPHPExcel->getActiveSheet()
       ->getStyle('B2')
       ->setConditionalStyles($conditionalStyles);

PHP Snippet 3:

function cellColor($col, $row){
    global $objPHPExcel;

    $cell = $col.$row;
    $cellValue = $objWorksheet->getCellByColumnAndRow($col, $row)->getValue();

    $color = 'ffffff';
    if($cellValue == 'ok')
       $color = 'ff0000';
    if($cellValue == 'no')
       $color = '30a30a';

    $objPHPExcel->getActiveSheet()->getStyle($cell)->getFill()->applyFromArray(array(
        'type' => PHPExcel_Style_Fill::FILL_SOLID,
        'startcolor' => array(
             'rgb' => $color
        )
    ));
}

PHP Snippet 4:

cellColor('B', '5');

PHP Snippet 5:

$conditional1 = new \PhpOffice\PhpSpreadsheet\Style\Conditional();
                    $conditional1->setConditionType(\PhpOffice\PhpSpreadsheet\Style\Conditional::OPERATOR_BEGINSWITH);
                    $conditional1->setOperatorType(\PhpOffice\PhpSpreadsheet\Style\Conditional::OPERATOR_EQUAL);
                    $conditional1->addCondition('2');
                    $conditional1->getStyle()->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);
                    $conditional1->getStyle()->getFont()->setBold(true);
    
                    $conditionalStyles = $spreadsheet->getActiveSheet()->getStyle('E2')->getConditionalStyles();
                    $conditionalStyles[] = $conditional1;
    
    $spreadsheet->getActiveSheet()->getStyle('D2')->setConditionalStyles($conditionalStyles);