Howto change condition

Topics: Developer Forum, User Forum
Nov 5, 2010 at 9:26 AM
Edited Nov 5, 2010 at 2:01 PM

Hello!

I'm generating a XLS2007-File by reading a template, filling it with data and write it back. This is working well. Now I'm trying to add the conditional-formats of the template to other cells.

Basically I want to have the conditional-format of the template-row (e.g. row 4) on row 5..x. 

 

$conditionalStyle = $objPHPExcel->getActiveSheet()->getStyle('K4')->getConditionalStyles();
$objPHPExcel->getActiveSheet()->getStyle('K5')->setConditionalStyles($conditionalStyle);

 

The condition is set correctly. So I have the same condition on row 5 as on the (template-) row nr. 4.

Unfortunately my condition looks like:

[0] =>PHPExcel_Style_Conditional(
_conditionType ='expression'
_operatorType =
_text = NULL
_condition =array(
[0] => 'AND(EXACT(K4,Y4),VALUE(LEN(Y4))>0)'
)
As you can see I need to manipulate the _condition to match the row-number. In fact every occurance of "4" needs to be changed to the actual row-number (e.g. 5).
I have somthing like the following in mind:
Read content of _condition -> manipulate it -> write it back to the object
Could this be done? How?
PHPExcel 1.7.4 - PHP 5.2.6-1 on debian
Thanks
Tim
Coordinator
Nov 5, 2010 at 10:14 PM

Methods to read and set conditions are described in the API documentation and include:

  • getCondition()
  • setCondition()
  • getConditions()
  • setConditions()
  • addCondition()

To change the references within the condition, the updateFormulaReferences() method in the PHPExcel_ReferenceHelper class should allow you to adjust the references within the formula itself.

Nov 8, 2010 at 2:09 PM

Hi Mark,

thank you for your answer. Is there an example available how to use updateFormulaReferences() ?

 

Thanks

Tim

Coordinator
Nov 10, 2010 at 12:45 PM
Edited Nov 10, 2010 at 12:47 PM
$helper = PHPExcel_ReferenceHelper::getInstance();

$conditionFormula = 'AND(EXACT(K4,Y4),VALUE(LEN(Y4))>0)';
for ($i = 1; $i <=10; $i++) {
   $adjustedConditionFormula = $helper->updateFormulaReferences($conditionFormula,'A1',0,$i);
   echo $adjustedConditionFormula,'<br />';
}

OR PERHAPS

$helper = PHPExcel_ReferenceHelper::getInstance();
 
$conditionFormula = 'AND(EXACT(K4,Y4),VALUE(LEN(Y4))>0)';
 
for ($i = 1; $i <=10; $i++) { 
   $conditionFormula = $helper->updateFormulaReferences($conditionFormula,'A1',0,1); 
   echo $conditionFormula,'<br />'; 
}