Conditionals: (1) Testing for NULL (or '') and (2) triggering conditional notes

Mar 7, 2012 at 5:03 PM

Two simple requests I can not seem to make work or find an answer too.  Basically, as I audit spreadsheet cells I want to discover all cells that are blank (null or ""), highlight the cell color (red), and insert a note for that cell IFF the cell is blank or null.  THis is the conditional code I wrote

$objConditional5 = new PHPExcel_Style_Conditional();
$objConditional5->setConditionType(PHPExcel_Style_Conditional::CONDITION_CONTAINSTEXT);
$objConditional5->setOperatorType(PHPExcel_Style_Conditional::OPERATOR_CONTAINSTEXT);
$objConditional5->setText('');
$objConditional5->getStyle()->getFont()->getColor()->setRGB('FF0000');
$objConditional5->getStyle()->getFont()->setBold(true);
$objConditional5->getStyle()->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getEndColor()->setRGB('CFCFCF');

and here is how I test for it:

for ($row = 2; $row <= $highestRow; ++$row) {
    $getThis = 'AJ'.$row;
    $conditionalStyles = $objPHPExcel->getActiveSheet()->getStyle($getThis)->getConditionalStyles();
    array_push($conditionalStyles, $objConditional5);
    $objPHPExcel->getActiveSheet()->getStyle($getThis)->setConditionalStyles($conditionalStyles);
    /* Pseudo code starts here
      if $objConditional5 resolved to be true
        set via not logic that the 'AJ'.$row should have the note applied "cannot be blank";
   end of PseudoCode */
}

Help.

My second question is really on figuring out when and which conditionals have proven to be true so I can insert a field note.  For example, the above conditional is evaluated to be true (field is blank or null) and I insert a note into cell 'AJ".$row that says something like "this field can never be blank".   But I do not see from the example code, and what I have specified above, which conditional has resolved to be true.

Help please.

Coordinator
Mar 7, 2012 at 5:44 PM

PHPExcel doesn't actually execute the conditions of conditional styles, it simply creates their definition so that they can be written to the Excel format files that you generate, and MS Excel can then execute them. There is no way of testing the conditions and applying additional logic, otherwise we'd use it in (for example) the HTML Writer.

Mar 8, 2012 at 11:35 AM
Edited Mar 8, 2012 at 12:17 PM

Alas, makes sense.  Blehhh.  Hmmm.  Now what to do...

Is it possible to add the writing of a cell note to the conditional so that if the resultant Excel conditional is true not only is the cell color changed, etc., but a note is inserted?  Generally something like this--

$objConditional6 = new PHPExcel_Style_Conditional();
$objConditional6->setConditionType(PHPExcel_Style_Conditional::CONDITION_CONTAINSTEXT);
$objConditional6->setOperatorType(PHPExcel_Style_Conditional::OPERATOR_NOTCONTAINS);
$objConditional6->setText('');
$objConditional6->getStyle()->getFont()->getColor()->setRGB('FF0000');
$objConditional6->getStyle()->getFont()->setBold(true);
$objConditional6->getStyle()->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getEndColor()->setRGB('F4A460');
// OK,  Here we go: write a NOTE..
$objConditional6->getComment($getThis)->setAuthor('auditSpreadsheet');
$objConditional6->$objCommentRichText = $objPHPExcel->getActiveSheet()->getComment($getThis)->getText()->createTextRun('IN4C Audit:');

$objConditional6->$objCommentRichText->getFont()->setBold(true);

$objConditional6->$objPHPExcel->getActiveSheet()->getComment($getThis)->getText()->createTextRun("\r\n");

$objConditional6->$objPHPExcel->getActiveSheet()->getComment($getThis)->getText()->createTextRun('Username cannot be blank.');

AND THEN LATER...

for ($row = 2; $row <= $highestRow; ++$row) {
    $getThis = 'AJ'.$row;
    $conditionalStyles = $objPHPExcel->getActiveSheet()->getStyle($getThis)->getConditionalStyles();
    array_push($conditionalStyles, $objConditional6);
    $objPHPExcel->getActiveSheet()->getStyle($getThis)->setConditionalStyles($conditionalStyles);

}

 

Since PHPEXEL is simply creating a definition, then is this possible?