CODEWITHSUNDEEP
phpexcelphpexcel
Nick Nikolic
Nick Nikolic

Reputation: 13

PHPExcel conditionally formatting a range with a formula

I have the following code:

$vOffset = 2;
$offset = 6;
$formatRows = 100;
$formatColumns = 100;

//set conditional formatting in place
for($row=$vOffset;$i<$formatRows;$row++){
  for($col=$offset+1;$col<$formatColumns;$col+3){
    //prepare conditional styles
    //if verbal is bigger
    $conditionalStyle_RED = new PHPExcel\Style_Conditional();
    $conditionalStyle_RED->setConditionType(PHPExcel\Style_Conditional::CONDITION_CELLIS);
    $conditionalStyle_RED->addCondition(
        "=" . $this->coordinates($col, $row) . " > " . $this->coordinates($col+1,$row)
    );
    $conditionalStyle_RED->getStyle()->getFont()->getColor()->setARGB(PHPExcel\Style_Color::COLOR_RED);
    array_push($conditionalStyles, $conditionalStyle_RED);

    $this->getActiveSheet()
        ->getStyle($this->coordinates($col, $row) . ':' . $this->coordinates($col+1, $row))
        ->setConditionalStyles($conditionalStyles);
  }
}

Where I attempt to set a conditional style with the formula akin to:

=A1 > A3

It isn't yet working. Anyone know how to accomplish this?

Upvotes: 1

Views: 5255

Answers (1)

ploopploop
ploopploop

Reputation: 138

Quite a late response, but I have been looking through the internet for a similar issue. Eventually I solved it. Incase people in the future might come across this problem I will note down a few things.

I have been struggling with the same kind of issue and I managed to solve it. What you want is to insert a formula into the conditional formatting. This sounds trickier than it actually is.

For your specific piece of code this should most likely work.

$vOffset = 2;
$offset = 6;
$formatRows = 100;
$formatColumns = 100;

//set conditional formatting in place
for($row=$vOffset;$i<$formatRows;$row++){
    for($col=$offset+1;$col<$formatColumns;$col+3){
        //prepare conditional styles
        //if verbal is bigger
        $conditionalStyle_RED = new PHPExcel\Style_Conditional();
        $conditionalStyle_RED->setConditionType(PHPExcel\Style_Conditional::CONDITION_EXPRESSION);
        $conditionalStyle_RED->setConditions(array("(" . $this->coordinates($col, $row) . " > " . $this->coordinates($col+1,$row) . ")"));
        $conditionalStyle_RED->getStyle()->getFont()->getColor()->setARGB(PHPExcel\Style_Color::COLOR_RED);
        array_push($conditionalStyles, $conditionalStyle_RED);

        $this->getActiveSheet()
            ->getStyle($this->coordinates($col, $row) . ':' . $this->coordinates($col+1, $row))
            ->setConditionalStyles($conditionalStyles);
    }
}

The "condition_expression" is used in order to place a formula inside a conditional format. As far as I was able to figure out the only way to implement a formula is by using the "setConditions". With this you can give an array of strings, with each string being a formula. In your code this would result in the color of red when the first coordinate is bigger than the second one.

Now back to your code, I haven't tested it so you might want to tweak a few values, but this should at least push you towards the right direction. Good luck mate!

Upvotes: 4

Related Questions