Issue with condtional formatting with PHPspreadsheet

[pbabugn]

This is:

- [ ] a bug report
- [ ] a feature request
- [ ] **not** a usage question (ask them on https://stackoverflow.com/questions/tagged/phpspreadsheet or https://gitter.im/PHPOffice/PhpSpreadsheet)

What is the expected behavior?

I have a template excel, where I have some headers, formulas and conditional styles. I am writing data to the cells and styles are applying.

I have condition on row 8 in column A as greater than $M$8 then apply some format.
So my condition should change for 9th row column A like $M$9 , for 10 th row $M10 and so on.
If I insert new row before 8th row 8th row will become 9th row and the condition will get change to $M$9.

What is the current behavior?

But my condition is still same (like $M$8 for 9,10,11.... rows) through phpspreadsheet.

What are the steps to reproduce?

1. create a template excel and put one conditional format for any column based the row (like cell val > $M$8)
2. insert new row before 8th row (using insertNewRowBefore founction)
3. save template to another excel and see the 9th row condtional format (still $M$8), but if I insert manual then the condition is changing to ($M$rowno)

Please provide a Minimal, Complete, and Verifiable example of code that exhibits the issue without relying on an external Excel file or a web server:

<?php

require __DIR__ . '/vendor/autoload.php';

// Create new Spreadsheet object
$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();

// add code that show the issue here...

Which versions of PhpSpreadsheet and PHP are affected?

[MarkBaker]

I've been doing some work with CF over the last few days; and I can see the logic in the ReferenceHelper to move the cell range for the style; but I can't see any logic to adjust cell references in the conditions.

I'll take a look at it as part of that work.

[MarkBaker]

Taking a deeper look at the existing code while I was having my lunch break: the existing code only seems to be updating the cell styling if the insert falls within the CF range; but doesn't update the range itself, nor the conditions :-(

[pbabugn]

Hi Mark,
I updated my code with latest changes.
If I have formula in my cell then I am getting( formula on U6 : IFERROR(VLOOKUP($N6,INDIRECT(CONCATENATE($G6,"!B:W")),#REF!,FALSE),"") ) the issue and not able to open the excel with below error .

THISPAGEHASACODINGPROBLEM

Fatal error: Uncaught PhpOffice\PhpSpreadsheet\Calculation\Exception: test!U6 -> Formula Error: An unexpected error occurred in utils/phpspreadsheet/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Cell/Cell.php:275
Stack trace:
#0 utils/phpspreadsheet/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Writer/Xlsx/Worksheet.php(1250): PhpOffice\PhpSpreadsheet\Cell\Cell->getCalculatedValue()
#1 utils/phpspreadsheet/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Writer/Xlsx/Worksheet.php(1322): PhpOffice\PhpSpreadsheet\Writer\Xlsx\Worksheet->writeCellFormula(Object(PhpOffice\PhpSpreadsheet\Shared\XMLWriter), '=IFERROR(VLOOKU...', Object(PhpOffice\PhpSpreadsheet\Cell\Cell))
#2 utils/phpspreadsheet/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Writer/Xlsx/Worksheet.php(1174): PhpOffice\PhpSpreadsheet\Writer\Xlsx\Worksheet->writeCell(Object(PhpOffice\PhpSpreadsheet\Shared\XMLWriter), Object(PhpOffice\PhpSpreadshee in utils/phpspreadsheet/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Cell/Cell.php on line 275

[MarkBaker]

Is this related to Condiional Formatting? Or is it just a formula error?

[pbabugn]

In my template I have formulas earlier(same formula) also, But only change updated the phpspreadhseet code with latest changes(my phpspreadsheet code one year old).

[MarkBaker]

If it's not related to this Conditional Formatting Issue, then please raise it as a separate issue, otherwise tracking the investigation and the work involved in resolving it becomes a lot harder.

To help debug a formula issue, the following code may be useful:

function evaluate(Spreadsheet $spreadSheet, Worksheet $workSheet, string $cell)
{
    // Initialise the calculation engine for debug logging
    $calculationEngine = Calculation::getInstance($spreadSheet);
    $debugLog = $calculationEngine->getDebugLog();

    $calculationEngine->flushInstance();
    $debugLog->setWriteDebugLog(true);
    $debugLog->setEchoDebugLog(true);

    $formulaValue = $workSheet->getCell($cell)->getValue();

    echo PHP_EOL, 'Formula value for evaluation is ', $formulaValue, PHP_EOL;

    $canExecuteCalculation = false;

    try {
        $tokens = $calculationEngine->parseFormula($formulaValue);
        $canExecuteCalculation = true;
    } catch (Exception $e) {
        echo 'PARSER ERROR: ', $e->getMessage(), PHP_EOL;
    } finally {
        echo 'Parser Stack :-';
        print_r($tokens ?? PHP_EOL . 'NULL');
    }

    $callStartTime = microtime(true);

    if ($canExecuteCalculation) {
        //  calculate
        try {
            $cellValue = $workSheet->getCell($cell)
                ->getCalculatedValue();

            echo 'Result is ', $cellValue, PHP_EOL;

            echo 'Evaluation Log:', PHP_EOL;
            print_r($debugLog->getLog());
        } catch (Exception $e) {
            echo 'CALCULATION ENGINE ERROR: ', $e->getMessage(), PHP_EOL;

            echo 'Evaluation Log:', PHP_EOL;
            print_r($debugLog->getLog());
        }
    }

    $callEndTime = microtime(true);
    $callTime = $callEndTime - $callStartTime;

    echo PHP_EOL;
    echo 'Call time to evaluate formula was ', sprintf('%.4f', $callTime), ' seconds', PHP_EOL;

    return $cellValue ?? null;
}

[MarkBaker]

Although looking more closely, it seems that INDIRECT rejects row/column ranges with a #REF! error, and only recognises cell ranges

[pbabugn]

Hi ,

I am using the below code to update the conditional style, but the all rows having the same condition ( I mean the condition is overriding with the last condition)
expecting : condtion should be $C$1,$c$2,$C$3
current behaviour : $C$3,$C$3,$C$3

$aStyles = $sheet->getConditionalStylesCollection();
$ros = 2;
foreach ($aStyles as $cellAddress => $cfRules) {
    foreach ($cfRules as &$cfRule) {
        $conditions = $cfRule->getConditions();
        foreach ($conditions as &$condition) {
            if (is_string($condition)) {
                $cfRule->setConditions('$C$'.$ros);
            }
        }
    }
   $ros ++;
}

What needs to be done to get as above, it seems the condition:PhpOffice\PhpSpreadsheet\Style\Conditional:private is global condition?

[MarkBaker]

Without delving too closely into this, I'd suggest that your use of "by reference" might cause some problems

What are you actually trying to achieve here? Because $C$1,$c$2,$C$3 is meaningless as a condition for Conditional Formatting.
If you're trying to set conditional style if the cell value matches an entry in a list, then you'll need to use an expression, like MATCH(A1, {"Shorts";"Shirts";"Skirts"}, 0) or COUNTIF($C$1:$C$3, A1)

And no, conditions are not "global"

P.S. When you have a completely new question to ask, please raise a new issue.... don't simply piggy-back on this issue for every single question that you have.... it makes any kind of issue tracking that we are trying to do nearly impossible!!!

[pbabugn]

My condition formatting as attached.

What I am trying to do is reading the Conditional styles with the getConditionalStylesCollection(say condition is in row now 6 and copying the same condition to the other newly inserted cell (at row no 7)) and I am changing that condition with below statement.
$cfRule->setConditions('$C$'.$ros);

When I changed, the condition in the screen shot(un the row no 6) also changing to Cell value > $AL$7,Cell value < $AL$7, Cell value = $AL$7.

This is not a new question , this is related to the same question which I posted earlier.

[MarkBaker]

There is actually code in the master branch of PhpSpreadsheet that will do this work when you insert rows: https://github.com/PHPOffice/PhpSpreadsheet/blob/master/src/PhpSpreadsheet/ReferenceHelper.php#L209 by this PR on 1th March (if you want to duplicate that).... or run the script against the master branch if you don't want to rewrite changes that have already been implemented.

Or use the CF Wizards to change the conditional range