Asudahlah.com

Programming, tutorials, mechatronics, operating systems, and other tech stuff

Conditional Formatting in PHPExcel

, , 2 comments
Doing conditional formatting in PHPExcel is pretty easy. First you have to define the formatting. Then apply it to a cell.

I hope this snippet could be useful. 
<?php
...
$objConditional = new PHPExcel_Style_Conditional();
$objConditional->setConditionType(PHPExcel_Style_Conditional::CONDITION_CELLIS)
                ->setOperatorType(PHPExcel_Style_Conditional::OPERATOR_LESSTHAN)
                ->addCondition('B2')
                ->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('C2')->getConditionalStyles();
array_push($conditionalStyles,$objConditional);
$objPHPExcel->getActiveSheet()->getStyle('C2')->setConditionalStyles($conditionalStyles);

B2 is the reference cell, and C2 is the cell for conditional formating to be applied to.

By the way, not everything went smooth when i did this, because the "fill" style doesn't works while the "font" style works well. I spend hour to scour the documentation, and search every possible keywords in google before i finally found what the problem is.
If you want to use SOLID fill, you need to use 'startcolor' instead of 'color'.
That's it. Simple matter but cost me my worthy minutes.
P.S: If you want to use textual conditional formatting insetad of numeric data. Check out Krazal's comment below.

2 comments :

  1. KrazalJune 19, 2018 at 3:43 AM

    If you want to use textual condition (instead of a numeric one, based on a cell) use this:

    $objConditional->setConditionType(PHPExcel_Style_Conditional::CONDITION_CONTAINSTEXT)
    ->setOperatorType(PHPExcel_Style_Conditional::OPERATOR_CONTAINSTEXT)
    ->setText('YES') # or 'abc123' etc.
    ...

    I hope this helps someone.

    ReplyDelete
    Replies
    1. gemaJune 25, 2018 at 12:51 AM

      Wonderful! Thank you for sharing. I really appreciate it.
      I'm pretty sure it will help someone.

      Delete

Subscribe to: Post Comments ( Atom )