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

Question

Good morning,

I need to set cells background colors using PHPExcel, but I don't know how to do it.

This is for my header and It's working:

$styleArray = array(
    'font' => array(
        'bold' => true,
    ),
    'alignment' => array(
        'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT,
    ),
    'borders' => array(
        'allborders' => array(
            'style' => PHPExcel_Style_Border::BORDER_THIN,
        )
    )
);

$objPHPExcel->getActiveSheet()->getStyle('A1:F1')->applyFromArray($styleArray);

Now I'd like to set cells bg colors in this way:

Columns J:Q, if cell's value is "OK", bg color is green; if it's "NO", bg color is red.

Thanks

Answer 1

I know this answer is pretty late, but I don't think the question is answered correctly here. This has to be done with conditional formatting because if the cell value changes after PHP generates the excel the color will not change. ie: Excel handles the logic for the formatting. I kept trying to get this to work and apparently getEndColor()->setRGB() has to be used and not getStartColor(). See example below:

 $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);

Answer 2

https://phpoffice.github.io/PhpSpreadsheet/classes/PhpOffice-PhpSpreadsheet-Style-Conditional.html

https://phpspreadsheet.readthedocs.io/en/latest/topics/recipes/#formatting-cells

$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);

Answer 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
        )
    ));
}

For example, you can use like this:

cellColor('B', '5');