Conditional Formatting Text

Topics: User Forum
Feb 17, 2011 at 6:51 PM

I've searched everywhere but can't seem to find any documentation or examples on how to use conditional formatting to test if a cell contains text.

I'm assuming it's something like this:

    $objConditional = new PHPExcel_Style_Conditional();
    $objConditional->setConditionType(PHPExcel_Style_Conditional::CONDITION_CONTAINSTEXT);
    $objConditional->setOperatorType(PHPExcel_Style_Conditional::OPERATOR_CONTAINSTEXT);
    $objConditional->addCondition('?');

Its the addCondition part I'm unsure of, which is why there is a question mark there. If tried 1, 0, TRUE, a specific text value, and it just doesn't seem to work.

 

Does anybody have an example of how to use conditional formatting with text?

Feb 18, 2011 at 8:08 AM

Hi,

 

 use the following code:

                $conditional = new PHPExcel_Style_Conditional();                                                                                
                $conditional->setConditionType(PHPExcel_Style_Conditional::CONDITION_CONTAINSTEXT);                                             
                $conditional->setOperatorType(PHPExcel_Style_Conditional::OPERATOR_CONTAINSTEXT);                                               
                $conditional->setText('OK');                                                                                                    
                $conditional->getStyle()->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_DARKGREEN);                                
                $conditional->getStyle()->getFont()->setBold(true);  
This works if the string in the cell contains the substring "OK".

Feb 18, 2011 at 3:47 PM

Thanks for the reply, but that isn't working for me, here is exactly whats going on... Column H contains a formula that under one condition displays the word Neutral.

$sheet->setCellValue("H$r", "=IF(AND(G$r>=8%,G$r<15%),\"Neutral\",IF(G$r>=15%,((C$r/100)*50)*(15-(G$r*100)),IF(G$r<=8%,((C$r/100)*50)*(8-(G$r*100)),0)))");

Then I'm setting conditional formatting to do the following.

  1. If the result is negative the text is red with a gray background.
  2. If the result of the formula is a positive value, then the text is green with a gray background.
  3. If the result is the word Neutral, display black text with a white background.

It seems that the greater than '0' is trumping the contains text.

    $objConditional = new PHPExcel_Style_Conditional();
    $objConditional->setConditionType(PHPExcel_Style_Conditional::CONDITION_CELLIS);
    $objConditional->setOperatorType(PHPExcel_Style_Conditional::OPERATOR_LESSTHAN);
    $objConditional->addCondition('0');
    $objConditional->getStyle()->getFont()->getColor()->setRGB('FF0000');
    $objConditional->getStyle()->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD_SIMPLE);
    $objConditional->getStyle()->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getEndColor()->setRGB('CFCFCF');

    $objConditional2 = new PHPExcel_Style_Conditional();
    $objConditional2->setConditionType(PHPExcel_Style_Conditional::CONDITION_CELLIS);
    $objConditional2->setOperatorType(PHPExcel_Style_Conditional::OPERATOR_GREATERTHAN);
    $objConditional2->addCondition('0');
    $objConditional2->getStyle()->getFont()->getColor()->setRGB('00B050');
    $objConditional2->getStyle()->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD_SIMPLE);
    $objConditional2->getStyle()->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getEndColor()->setRGB('CFCFCF');

    $objConditional3 = new PHPExcel_Style_Conditional();
    $objConditional3->setConditionType(PHPExcel_Style_Conditional::CONDITION_CONTAINSTEXT);
    $objConditional3->setOperatorType(PHPExcel_Style_Conditional::OPERATOR_CONTAINSTEXT);
    $objConditional3->setText('Neutral');
    $objConditional3->getStyle()->getFont()->getColor()->setRGB('000000');
    $objConditional3->getStyle()->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getEndColor()->setRGB('FFFFFF');

    $conditionalStyles = $sheet->getStyle('H'.$reportStart.':H'.$r)->getConditionalStyles();
    array_push($conditionalStyles, $objConditional);
    array_push($conditionalStyles, $objConditional2);
    array_push($conditionalStyles, $objConditional3);
    $sheet->getStyle('H'.$reportStart.':H'.$r)->setConditionalStyles($conditionalStyles);
Any suggestions? If I remove the other conditions, the Text condition works.

That just gave me an Idea.....

OK so I changed the order of the conditions and it works! I moved the Text condition first. Confessional debugging at it's best.

    $conditionalStyles = $sheet->getStyle('H'.$reportStart.':H'.$r)->getConditionalStyles();
    array_push($conditionalStyles, $objConditional3);
    array_push($conditionalStyles, $objConditional);
    array_push($conditionalStyles, $objConditional2);
    $sheet->getStyle('H'.$reportStart.':H'.$r)->setConditionalStyles($conditionalStyles);
So problem solved but I'm going to post anyways, someone else may be frustrated with the same problem in the future.