Conditional formatting

Topics: Developer Forum, Project Management Forum, User Forum
Feb 23, 2012 at 3:08 AM

When i compare two cell with same format and value (h:mm, 08:00) then conditional formatting assume they are not equal. One is writen with PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME3 and second is inserted manually. Solution is use function Text, but in Conditional formatting i can't write it without error.

$objConditional = new PHPExcel_Style_Conditional();
            $objConditional->setConditionType(PHPExcel_Style_Conditional::CONDITION_EXPRESSION);
            $objConditional->setOperatorType(PHPExcel_Style_Conditional::OPERATOR_EQUAL);
            $objConditional->addCondition('TEXT($'.PHPExcel_Cell::stringFromColumnIndex($targetColumn).'$'.($targetRow-1).';"h:mm")<>TEXT($'.PHPExcel_Cell::stringFromColumnIndex($targetColumn).'$'.$targetRow.';"h:mm")');
            $objConditional->getStyle()->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getEndColor()->setRGB('FF0000');
           
            $conditionalStyles = $this->sheet->getStyle(PHPExcel_Cell::stringFromColumnIndex($sourceColumn).$sourceRow.':'.PHPExcel_Cell::stringFromColumnIndex($sourceColumn).$sourceRow)->getConditionalStyles();
            array_push($conditionalStyles, $objConditional);
            $this->sheet->getStyle(PHPExcel_Cell::stringFromColumnIndex($sourceColumn).$sourceRow.':'.PHPExcel_Cell::stringFromColumnIndex($sourceColumn).$sourceRow)->setConditionalStyles($conditionalStyles);
  

Coordinator
Feb 24, 2012 at 9:48 AM

It might be better to test against the underlying value rather than the formatted value, and use OPERATOR_BETWEEN with a margin of error to allow for seconds either side

Feb 26, 2012 at 10:39 PM

I changed script. Conditional formatting is now proper, but not working. I must manually try to change it in excel file, click, don't change anything and after this procedure is working. It seems that excel have some strange problem with accepting him.

$objConditional = new PHPExcel_Style_Conditional();
$objConditional->setConditionType(PHPExcel_Style_Conditional::CONDITION_CONTAINSTEXT);
$objConditional->setOperatorType(PHPExcel_Style_Conditional::OPERATOR_NOTCONTAINS);
$objConditional->setText('=$'.PHPExcel_Cell::stringFromColumnIndex($targetColumn).'$'.$targetRow);


$objConditional->getStyle()->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getEndColor()->setRGB('FF0000');
           
$conditionalStyles = $this->sheet->getStyle(PHPExcel_Cell::stringFromColumnIndex($sourceColumn).$sourceRow.':'.PHPExcel_Cell::stringFromColumnIndex($sourceColumn).$sourceRow)->getConditionalStyles();
array_push($conditionalStyles, $objConditional);
$this->sheet->getStyle(PHPExcel_Cell::stringFromColumnIndex($sourceColumn).$sourceRow.':'.PHPExcel_Cell::stringFromColumnIndex($sourceColumn).$sourceRow)->setConditionalStyles($conditionalStyles);