Use formula to determine which cells to format - issue with conditionCellAdjustment()

14 views Asked by At

Trying to use a conditional formating to compare 2 cells in each row to determine if they are equal. The issue I'm having issue with is the below formula ($A3 <> $B3). The method conditionCellAdjustment() in WizardAbstract.php changes my formula to ($A5 <> $B5) because there is no $ after the letter. Tried multiple ways with no luck, any help is much appreciated. Here is my code:

    $redStyle = new Style(false, true);
    $redStyle->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB('FBABAD');
    $redStyle->getFill()->getEndColor()->setARGB('FBABAD');

    $cellRange = 'A3:A5';
    $conditionalStyles = [];
    $wizardFactory = new Wizard($cellRange);

    $formulaWizard = $wizardFactory->newRule(Wizard::FORMULA);
    // $formulaWizard->expression('$A3 <> $B3')
    $formulaWizard->formula('$A3 <> $B3')
        ->setStyle($redStyle);

    $conditionalStyles[] = $formulaWizard->getConditional();

    $doc->getActiveSheet()
        ->getStyle($formulaWizard->getCellRange())
        ->setConditionalStyles($conditionalStyles);

Here is the function causing the formula change, conditionalCellAdjustment() in vendor\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Style\ConditionalFormatting\Wizard\WizardAbstract.php

    protected function conditionCellAdjustment(array $matches): string {

    $worksheet = $matches[1];
    $column = $matches[6];
    $row = $matches[7];

    if (strpos($column, '$') === false) {
        $column = Coordinate::columnIndexFromString($column);
        $column += $this->referenceColumn - 1;
        $column = Coordinate::stringFromColumnIndex($column);
    }
    
    // this is causing the formula row number to increase
    if (strpos($row, '$') === false) {
        $row += $this->referenceRow - 1;
    }

    return "{$worksheet}{$column}{$row}";
}

Here is some documentation on using conditional formatting: https://phpspreadsheet.readthedocs.io/en/latest/topics/conditional-formatting/#wizards

0

There are 0 answers