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.