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