CODEWITHSUNDEEP
phpformattingphpexcel
sms
sms

Reputation: 393

PHPExcel Conditional Formatting inside loop

I'm trying to apply PHPExcel conditional formatting to all the cells in columm B with a value under 50. I took the example from the developer documentation but so far I can't make it work. Any ideas?

Here is the conditional:

$objConditional1 = new PHPExcel_Style_Conditional(); 
$objConditional1->setConditionType(PHPExcel_Style_Conditional::CONDITION_CELLIS);
$objConditional1->setOperatorType(PHPExcel_Style_Conditional::OPERATOR_LESSTHAN);

$objConditional1->addCondition('50'); 
$objConditional1->getStyle()->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED); 
$objConditional1->getStyle()->getFont()->setBold(true);

Here is the loop:

$rowCount = 3;
foreach ($biocount as $value) {

$objPHPExcel->getActiveSheet()->SetCellValue('B'.$rowCount, $value);
$objPHPExcel->getActiveSheet()->getStyle('B'. $rowCount)->getFont()->setSize(14);
$objPHPExcel->getActiveSheet()->getStyle('B'. $rowCount)->setConditionalStyles($objConditional1);

$rowCount++;
      }

Upvotes: 3

Views: 4542

Answers (1)

Mark Baker
Mark Baker

Reputation: 212402

The setConditionalStyles() expects an array of conditional styles, not a single conditional style object

/**
 * Set Conditional Styles. Only used on supervisor.
 *
 * @param PHPExcel_Style_Conditional[] $pValue Array of condtional styles
 * @return PHPExcel_Style
 */

e.g.

$rowCount = 3;
foreach ($data as $value) {
    $objPHPExcel->getActiveSheet()->SetCellValue('B'.$rowCount, $value);
    $objPHPExcel->getActiveSheet()->getStyle('B'. $rowCount)
        ->getFont()->setSize(14);
    $objPHPExcel->getActiveSheet()->getStyle('B'. $rowCount)
        ->setConditionalStyles(array($objConditional1));
    $rowCount++;
}

EDIT

Note that, rather than having the overhead of setting the conditional styles for each individual cell within the loop, you could use the duplicateConditionalStyle() method to apply the styling to a range of cells in a single step once you've finished looping

$rowCount = 3;
foreach ($data as $value) {
    $objPHPExcel->getActiveSheet()->SetCellValue('B'.$rowCount, $value);
    $objPHPExcel->getActiveSheet()->getStyle('B'. $rowCount)
        ->getFont()->setSize(14);
    $rowCount++;
}

$objPHPExcel->getActiveSheet()
    ->duplicateConditionalStyle(
        array($objConditional1), 
        'B3:B' .($rowCount - 1)
    );

Upvotes: 4

Related Questions