Conditional Formating

Topics: Developer Forum
Apr 3, 2013 at 3:52 PM
Hi

I hope someone can help me with a few issues.

I am trying to create a few conditional formats for a range of values on different rows.

To be clear I want to color code the lowest, second and third lowest values on each row.

I have created the following code:

$objConditional1=new PHPExcel_Style_Conditional();
$objConditional1->setConditionType(PHPExcel_Style_Conditional::CONDITION_EXPRESSION);
$objConditional1->setOperatorType(PHPExcel_Style_Conditional::OPERATOR_EQUAL);
$objConditional1->addCondition('C7=MIN($C7:$AH7)');
$objConditional1->addCondition(TRUE);
$objConditional1->getStyle()->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED);

$objConditional2=new PHPExcel_Style_Conditional();
$objConditional2->setConditionType(PHPExcel_Style_Conditional::CONDITION_EXPRESSION);
$objConditional2->setOperatorType(PHPExcel_Style_Conditional::OPERATOR_EQUAL);
$objConditional2->addCondition('C7=SMALL($C7:$AH7,2)');
$objConditional2->addCondition(TRUE);
$objConditional2->getStyle()->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_GREEN);

$objConditional3=new PHPExcel_Style_Conditional();
$objConditional3->setConditionType(PHPExcel_Style_Conditional::CONDITION_EXPRESSION);
$objConditional3->setOperatorType(PHPExcel_Style_Conditional::OPERATOR_EQUAL);
$objConditional3->addCondition('C7=SMALL($C7:$AH7,3)');
$objConditional3->addCondition(TRUE);
$objConditional3->getStyle()->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_YELLOW);

$conditionalStyles=$objPHPExcel->getActiveSheet()->getStyle('C7')->getConditionalStyles();

array_push($conditionalStyles,$objConditional1);
array_push($conditionalStyles,$objConditional2);
array_push($conditionalStyles,$objConditional3);

$objPHPExcel->getActiveSheet()->getStyle('C7')->setConditionalStyles($conditionalStyles);

The first condition works but the other 2 do not.

Another question is how can I duplicate the formatting to all other rows in the sheet.

I am running the latest phpExcel on Windows 8 64 bit.

Thank you for your help

Dov
Apr 3, 2013 at 5:22 PM
I have now sorted the issue. In case anyone else finds this useful in the future I changed the line that applies the format to:

$objPHPExcel->getActiveSheet()->getStyle('C7:AH77')->setConditionalStyles($conditionalStyles);

This now applies the format to all the cells in the range.