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

While the proper way to do it is already answered by Amr Aly, for those of you who are still (unfortunately) stuck to using the deprecated PHPExcel rather than PhpOffice, here's how I managed. I've written what that answer does which just handles your "if 'NO', make the cell red" case.

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

The source was this blog which has a proper more detailed general example which helped me in my work. Hope this helps anybody.

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

Answer 4

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