Newbie questions regarding CF using formula

Topics: User Forum
Apr 2, 2012 at 1:19 AM

A few days ago, I created a spreadsheet for a Zend Framework project I am writing for a customer.  We had massive changes to the menus, and I wanted something to help me see what was changing, and to see which menu items had been deactivated.  So I ended up with a file which was structured like this:

Sheet1 ("Prod) - Loaded from the database table for the menus.

Sheet2 ("Updates") - What started as a copy of the data from Sheet1, with the following conditional formatting applied:

CF Formula #1 (†): =INDIRECT(ADDRESS(ROW(),COLUMN(),,,"Prod"))<>INDIRECT(ADDRESS(ROW(),COLUMN()))
CF Formula #2: =INDIRECT("J"&ROW())="f"

To try to speed things up, I thought to just load things into the project, and let it parse things out, using the formula already there for the conditional formatting.  And so, I "discovered" the pear channel, did a pear install (which in reality, put it one level too deep for the autoloader to find properly), and once I got the test action loading the spreadsheet, I started to tackle going after the data and checking the conditional formatting for the cell, which is where I really hit a wall.The problem I ran into is that the only way I could determine to find out what the conditional formatting left the background at was to do something such as:

 

$sheet = $excel->getSheetByName('Updates');
$calc = new PHPExcel_Calculation::getInstance();
// deletia
foreach(range('1', $highest_row) as $row) {
    echo '    <tr>' . PHP_EOL;
    foreach(range('A', $highest_col) as $col) {
        $addr = $col . $row;
        $cstyles = $sheet->getConditionalStyles($addr);
        foreach ($cstyles as $cstyle) {
            switch ($cstyle->getConditionType()) {
// deletia
            case PHPExcel_Style_Conditional::CONDITION_EXPRESSION:
                $cond = $cstyle->getCondition();
                try {
                    $res = $calc->calculateFormula('='.$cond, $col . $row, $refcell);
                } catch (Exception $e) {
                    echo 'error!!';
                }
                break;
// deletia
            }
        }
    }
}

I first found that the code for INDIRECT did not work unless I passed in $refcell.  Leaving it unspecified resulted in attempting to execute a method of a non-object (e.g. null).  But when I do include it, I get an error stating that there is a circular reference.  Now, if we were updating the cell value, I could see this, but for a conditional formatting formula... this really is not the case.  And so, I find myself going about it in what I view is slightly less optimal method, by hard-coding the comparison into my PHP code, rather than letting what the spreadsheet did handle things for me.

Anyone have any suggestions on how to properly let what is in the spreadsheet conditions drive this??

Thanks!

- Doug


†  This is the final formula I tried.  It started out as

 

=INDIRECT("Prod!R"&ROW()&"C"&COLUMN()))<>INDIRECT("R"&ROW()&"C"&COLUMN())

and then changed to

=INDIRECT("Prod"&ADDRESS(ROW(),COLUMN()))<>INDIRECT(ADDRESS(ROW(),COLUMN()))

before reaching the form above due to limitations found in PHPExcel.  But all of these worked in Excel.

 

BTW: clicking on the upper right corner to select the sheet resulted in a range of "=$1:$1048576", which crashed PHPExcel due to memory exhaustion.