CellMatcher::setReferenceCellForExpressions() - Code Metrics - Inspection of "Merge pull request #4453 from oleibman/issue56" - PHPOffice/PhpSpreadsheet - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — master ( f9b1b7...3922d9 )

unknown

created 2025-05-07 03:06 UTC

CellMatcher::setReferenceCellForExpressions() A

↳ Parent: CellMatcher

Complexity

Conditions	2
Paths	2

Size

Total Lines	17
Code Lines	10

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	12
CRAP Score	2

Importance

Changes

Metric	Value
eloc	10
dl	0
loc	17
ccs	12
cts	12
cp	1
rs	9.9332
c	0
b	0
f	0
cc	2
nc	2
nop	1
crap	2

<?php

namespace PhpOffice\PhpSpreadsheet\Style\ConditionalFormatting;

use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
use PhpOffice\PhpSpreadsheet\Calculation\Exception;
use PhpOffice\PhpSpreadsheet\Cell\Cell;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Shared\StringHelper;
use PhpOffice\PhpSpreadsheet\Style\Conditional;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;

class CellMatcher
{
    public const COMPARISON_OPERATORS = [
        Conditional::OPERATOR_EQUAL => '=',
        Conditional::OPERATOR_GREATERTHAN => '>',
        Conditional::OPERATOR_GREATERTHANOREQUAL => '>=',
        Conditional::OPERATOR_LESSTHAN => '<',
        Conditional::OPERATOR_LESSTHANOREQUAL => '<=',
        Conditional::OPERATOR_NOTEQUAL => '<>',
    ];

    public const COMPARISON_RANGE_OPERATORS = [
        Conditional::OPERATOR_BETWEEN => 'IF(AND(A1>=%s,A1<=%s),TRUE,FALSE)',
        Conditional::OPERATOR_NOTBETWEEN => 'IF(AND(A1>=%s,A1<=%s),FALSE,TRUE)',
    ];

    public const COMPARISON_DUPLICATES_OPERATORS = [
        Conditional::CONDITION_DUPLICATES => "COUNTIF('%s'!%s,%s)>1",
        Conditional::CONDITION_UNIQUE => "COUNTIF('%s'!%s,%s)=1",
    ];

    protected Cell $cell;

    protected int $cellRow;

    protected Worksheet $worksheet;

    protected int $cellColumn;

    protected string $conditionalRange;

    protected string $referenceCell;

    protected int $referenceRow;

    protected int $referenceColumn;

    protected Calculation $engine;

    public function __construct(Cell $cell, string $conditionalRange)
    {
        $this->cell = $cell;
        $this->worksheet = $cell->getWorksheet();
        [$this->cellColumn, $this->cellRow] = Coordinate::indexesFromString($this->cell->getCoordinate());
        $this->setReferenceCellForExpressions($conditionalRange);

        $this->engine = Calculation::getInstance($this->worksheet->getParent());
    }

    protected function setReferenceCellForExpressions(string $conditionalRange): void
    {
        $conditionalRange = Coordinate::splitRange(str_replace('$', '', strtoupper($conditionalRange)));
        [$this->referenceCell] = $conditionalRange[0];

        [$this->referenceColumn, $this->referenceRow] = Coordinate::indexesFromString($this->referenceCell);

        // Convert our conditional range to an absolute conditional range, so it can be used  "pinned" in formulae
        $rangeSets = [];
        foreach ($conditionalRange as $rangeSet) {
            $absoluteRangeSet = array_map(
                [Coordinate::class, 'absoluteCoordinate'],
                $rangeSet
            );
            $rangeSets[] = implode(':', $absoluteRangeSet);
        }
        $this->conditionalRange = implode(',', $rangeSets);
    }

    public function evaluateConditional(Conditional $conditional): bool
    {
        // Some calculations may modify the stored cell; so reset it before every evaluation.
        $cellColumn = Coordinate::stringFromColumnIndex($this->cellColumn);
        $cellAddress = "{$cellColumn}{$this->cellRow}";
        $this->cell = $this->worksheet->getCell($cellAddress);

        return match ($conditional->getConditionType()) {
            Conditional::CONDITION_CELLIS => $this->processOperatorComparison($conditional),
            Conditional::CONDITION_DUPLICATES, Conditional::CONDITION_UNIQUE => $this->processDuplicatesComparison($conditional),
            // Expression is NOT(ISERROR(SEARCH("<TEXT>",<Cell Reference>)))
            Conditional::CONDITION_CONTAINSTEXT,
            // Expression is ISERROR(SEARCH("<TEXT>",<Cell Reference>))
            Conditional::CONDITION_NOTCONTAINSTEXT,
            // Expression is LEFT(<Cell Reference>,LEN("<TEXT>"))="<TEXT>"
            Conditional::CONDITION_BEGINSWITH,
            // Expression is RIGHT(<Cell Reference>,LEN("<TEXT>"))="<TEXT>"
            Conditional::CONDITION_ENDSWITH,
            // Expression is LEN(TRIM(<Cell Reference>))=0
            Conditional::CONDITION_CONTAINSBLANKS,
            // Expression is LEN(TRIM(<Cell Reference>))>0
            Conditional::CONDITION_NOTCONTAINSBLANKS,
            // Expression is ISERROR(<Cell Reference>)
            Conditional::CONDITION_CONTAINSERRORS,
            // Expression is NOT(ISERROR(<Cell Reference>))
            Conditional::CONDITION_NOTCONTAINSERRORS,
            // Expression varies, depending on specified timePeriod value, e.g.
            // Yesterday FLOOR(<Cell Reference>,1)=TODAY()-1
            // Today FLOOR(<Cell Reference>,1)=TODAY()
            // Tomorrow FLOOR(<Cell Reference>,1)=TODAY()+1
            // Last 7 Days AND(TODAY()-FLOOR(<Cell Reference>,1)<=6,FLOOR(<Cell Reference>,1)<=TODAY())
            Conditional::CONDITION_TIMEPERIOD,
            Conditional::CONDITION_EXPRESSION => $this->processExpression($conditional),
            Conditional::CONDITION_COLORSCALE => $this->processColorScale($conditional),
            default => false,
        };
    }

    protected function wrapValue(mixed $value): float|int|string
    {
        if (!is_numeric($value)) {
            if (is_bool($value)) {
                return $value ? 'TRUE' : 'FALSE';
            } elseif ($value === null) {
                return 'NULL';
            }

            return '"' . StringHelper::convertToString($value) . '"';
        }

        return $value;
    }

    protected function wrapCellValue(): float|int|string
    {
        $this->cell = $this->worksheet->getCell([$this->cellColumn, $this->cellRow]);

        return $this->wrapValue($this->cell->getCalculatedValue());
    }

    /** @param string[] $matches */
    protected function conditionCellAdjustment(array $matches): float|int|string
    {
        $column = $matches[6];
        $row = $matches[7];
        if (!str_contains($column, '$')) {
            //            $column = Coordinate::stringFromColumnIndex($this->cellColumn);
            $column = Coordinate::columnIndexFromString($column);
            $column += $this->cellColumn - $this->referenceColumn;
            $column = Coordinate::stringFromColumnIndex($column);
        }

        if (!str_contains($row, '$')) {
            $row = (int) $row + $this->cellRow - $this->referenceRow;
        }

        if (!empty($matches[4])) {
            $worksheet = $this->worksheet->getParentOrThrow()->getSheetByName(trim($matches[4], "'"));
            if ($worksheet === null) {
                return $this->wrapValue(null);
            }

            return $this->wrapValue(
                $worksheet
                    ->getCell(str_replace('$', '', "{$column}{$row}"))
                    ->getCalculatedValue()
            );
        }

        return $this->wrapValue(
            $this->worksheet
                ->getCell(str_replace('$', '', "{$column}{$row}"))
                ->getCalculatedValue()
        );
    }

    protected function cellConditionCheck(string $condition): string
    {
        $splitCondition = explode(Calculation::FORMULA_STRING_QUOTE, $condition);
        $i = false;
        foreach ($splitCondition as &$value) {
            //    Only count/replace in alternating array entries (ie. not in quoted strings)
            $i = $i === false;
            if ($i) {
                $value = (string) preg_replace_callback(
                    '/' . Calculation::CALCULATION_REGEXP_CELLREF_RELATIVE . '/i',
                    [$this, 'conditionCellAdjustment'],
                    $value
                );
            }
        }
        unset($value);

        //    Then rebuild the condition string to return it
        return implode(Calculation::FORMULA_STRING_QUOTE, $splitCondition);
    }

    /**
     * @param mixed[] $conditions
     *
     * @return mixed[]
     */
    protected function adjustConditionsForCellReferences(array $conditions): array
    {
        return array_map(
            [$this, 'cellConditionCheck'],
            $conditions
        );
    }

    protected function processOperatorComparison(Conditional $conditional): bool
    {
        if (array_key_exists($conditional->getOperatorType(), self::COMPARISON_RANGE_OPERATORS)) {
            return $this->processRangeOperator($conditional);
        }

        $operator = self::COMPARISON_OPERATORS[$conditional->getOperatorType()];
        $conditions = $this->adjustConditionsForCellReferences($conditional->getConditions());
        /** @var float|int|string */
        $temp1 = $this->wrapCellValue();
        /** @var scalar */
        $temp2 = array_pop($conditions);
        $expression = sprintf('%s%s%s', (string) $temp1, $operator, (string) $temp2);

        return $this->evaluateExpression($expression);
    }

    protected function processColorScale(Conditional $conditional): bool
    {
        if (is_numeric($this->wrapCellValue()) && $conditional->getColorScale()?->colorScaleReadyForUse()) {
            return true;
        }

        return false;
    }

    protected function processRangeOperator(Conditional $conditional): bool
    {
        $conditions = $this->adjustConditionsForCellReferences($conditional->getConditions());
        sort($conditions);
        $expression = sprintf(
            (string) preg_replace(
                '/\bA1\b/i',
                (string) $this->wrapCellValue(),
                self::COMPARISON_RANGE_OPERATORS[$conditional->getOperatorType()]
            ),
            ...$conditions //* @phpstan-ignore-line
        );

        return $this->evaluateExpression($expression);
    }

    protected function processDuplicatesComparison(Conditional $conditional): bool
    {
        $worksheetName = $this->cell->getWorksheet()->getTitle();

        $expression = sprintf(
            self::COMPARISON_DUPLICATES_OPERATORS[$conditional->getConditionType()],
            $worksheetName,
            $this->conditionalRange,
            $this->cellConditionCheck($this->cell->getCalculatedValueString())
        );

        return $this->evaluateExpression($expression);
    }

    protected function processExpression(Conditional $conditional): bool
    {
        $conditions = $this->adjustConditionsForCellReferences($conditional->getConditions());
        /** @var string */
        $expression = array_pop($conditions);
        /** @var float|int|string */
        $temp = $this->wrapCellValue();

        $expression = (string) preg_replace(
            '/\b' . $this->referenceCell . '\b/i',
            (string) $temp,
            $expression
        );

        return $this->evaluateExpression($expression);
    }

    protected function evaluateExpression(string $expression): bool
    {
        $expression = "={$expression}";

        try {
            $this->engine->flushInstance();
            $result = (bool) $this->engine->calculateFormula($expression);
        } catch (Exception) {
            return false;
        }

        return $result;
    }
}


1		<?php
2
3		namespace PhpOffice\PhpSpreadsheet\Style\ConditionalFormatting;
4
5		use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
6		use PhpOffice\PhpSpreadsheet\Calculation\Exception;
7		use PhpOffice\PhpSpreadsheet\Cell\Cell;
8		use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
9		use PhpOffice\PhpSpreadsheet\Shared\StringHelper;
10		use PhpOffice\PhpSpreadsheet\Style\Conditional;
11		use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
12
13		class CellMatcher
14		{
15		public const COMPARISON_OPERATORS = [
16		Conditional::OPERATOR_EQUAL => '=',
17		Conditional::OPERATOR_GREATERTHAN => '>',
18		Conditional::OPERATOR_GREATERTHANOREQUAL => '>=',
19		Conditional::OPERATOR_LESSTHAN => '<',
20		Conditional::OPERATOR_LESSTHANOREQUAL => '<=',
21		Conditional::OPERATOR_NOTEQUAL => '<>',
22		];
23
24		public const COMPARISON_RANGE_OPERATORS = [
25		Conditional::OPERATOR_BETWEEN => 'IF(AND(A1>=%s,A1<=%s),TRUE,FALSE)',
26		Conditional::OPERATOR_NOTBETWEEN => 'IF(AND(A1>=%s,A1<=%s),FALSE,TRUE)',
27		];
28
29		public const COMPARISON_DUPLICATES_OPERATORS = [
30		Conditional::CONDITION_DUPLICATES => "COUNTIF('%s'!%s,%s)>1",
31		Conditional::CONDITION_UNIQUE => "COUNTIF('%s'!%s,%s)=1",
32		];
33
34		protected Cell $cell;
35
36		protected int $cellRow;
37
38		protected Worksheet $worksheet;
39
40		protected int $cellColumn;
41
42		protected string $conditionalRange;
43
44		protected string $referenceCell;
45
46		protected int $referenceRow;
47
48		protected int $referenceColumn;
49
50		protected Calculation $engine;
51
52	175	public function __construct(Cell $cell, string $conditionalRange)
53		{
54	175	$this->cell = $cell;
55	175	$this->worksheet = $cell->getWorksheet();
56	175	[$this->cellColumn, $this->cellRow] = Coordinate::indexesFromString($this->cell->getCoordinate());
57	175	$this->setReferenceCellForExpressions($conditionalRange);
58
59	175	$this->engine = Calculation::getInstance($this->worksheet->getParent());
60		}
61
62	175	protected function setReferenceCellForExpressions(string $conditionalRange): void
63		{
64	175	$conditionalRange = Coordinate::splitRange(str_replace('$', '', strtoupper($conditionalRange)));
65	175	[$this->referenceCell] = $conditionalRange[0];
66
67	175	[$this->referenceColumn, $this->referenceRow] = Coordinate::indexesFromString($this->referenceCell);
68
69		// Convert our conditional range to an absolute conditional range, so it can be used "pinned" in formulae
70	175	$rangeSets = [];
71	175	foreach ($conditionalRange as $rangeSet) {
72	175	$absoluteRangeSet = array_map(
73	175	[Coordinate::class, 'absoluteCoordinate'],
74	175	$rangeSet
75	175	);
76	175	$rangeSets[] = implode(':', $absoluteRangeSet);
77		}
78	175	$this->conditionalRange = implode(',', $rangeSets);
79		}
80
81	175	public function evaluateConditional(Conditional $conditional): bool
82		{
83		// Some calculations may modify the stored cell; so reset it before every evaluation.
84	175	$cellColumn = Coordinate::stringFromColumnIndex($this->cellColumn);
85	175	$cellAddress = "{$cellColumn}{$this->cellRow}";
86	175	$this->cell = $this->worksheet->getCell($cellAddress);
87
88	175	return match ($conditional->getConditionType()) {
89	46	Conditional::CONDITION_CELLIS => $this->processOperatorComparison($conditional),
90	7	Conditional::CONDITION_DUPLICATES, Conditional::CONDITION_UNIQUE => $this->processDuplicatesComparison($conditional),
91		// Expression is NOT(ISERROR(SEARCH("<TEXT>",<Cell Reference>)))
92	175	Conditional::CONDITION_CONTAINSTEXT,
93		// Expression is ISERROR(SEARCH("<TEXT>",<Cell Reference>))
94	175	Conditional::CONDITION_NOTCONTAINSTEXT,
95		// Expression is LEFT(<Cell Reference>,LEN("<TEXT>"))="<TEXT>"
96	175	Conditional::CONDITION_BEGINSWITH,
97		// Expression is RIGHT(<Cell Reference>,LEN("<TEXT>"))="<TEXT>"
98	175	Conditional::CONDITION_ENDSWITH,
99		// Expression is LEN(TRIM(<Cell Reference>))=0
100	175	Conditional::CONDITION_CONTAINSBLANKS,
101		// Expression is LEN(TRIM(<Cell Reference>))>0
102	175	Conditional::CONDITION_NOTCONTAINSBLANKS,
103		// Expression is ISERROR(<Cell Reference>)
104	175	Conditional::CONDITION_CONTAINSERRORS,
105		// Expression is NOT(ISERROR(<Cell Reference>))
106	175	Conditional::CONDITION_NOTCONTAINSERRORS,
107		// Expression varies, depending on specified timePeriod value, e.g.
108		// Yesterday FLOOR(<Cell Reference>,1)=TODAY()-1
109		// Today FLOOR(<Cell Reference>,1)=TODAY()
110		// Tomorrow FLOOR(<Cell Reference>,1)=TODAY()+1
111		// Last 7 Days AND(TODAY()-FLOOR(<Cell Reference>,1)<=6,FLOOR(<Cell Reference>,1)<=TODAY())
112	175	Conditional::CONDITION_TIMEPERIOD,
113	122	Conditional::CONDITION_EXPRESSION => $this->processExpression($conditional),
114	2	Conditional::CONDITION_COLORSCALE => $this->processColorScale($conditional),
115	175	default => false,
116	175	};
117		}
118
119	168	protected function wrapValue(mixed $value): float\|int\|string
120		{
121	168	if (!is_numeric($value)) {
122	105	if (is_bool($value)) {
123		return $value ? 'TRUE' : 'FALSE';
124	105	} elseif ($value === null) {
125	12	return 'NULL';
126		}
127
128	101	return '"' . StringHelper::convertToString($value) . '"';
129		}
130
131	75	return $value;
132		}
133
134	168	protected function wrapCellValue(): float\|int\|string
135		{
136	168	$this->cell = $this->worksheet->getCell([$this->cellColumn, $this->cellRow]);
137
138	168	return $this->wrapValue($this->cell->getCalculatedValue());
139		}
140
141		/** @param string[] $matches */
142	147	protected function conditionCellAdjustment(array $matches): float\|int\|string
143		{
144	147	$column = $matches[6];
145	147	$row = $matches[7];
146	147	if (!str_contains($column, '$')) {
147		// $column = Coordinate::stringFromColumnIndex($this->cellColumn);
148	104	$column = Coordinate::columnIndexFromString($column);
149	104	$column += $this->cellColumn - $this->referenceColumn;
150	104	$column = Coordinate::stringFromColumnIndex($column);
151		}
152
153	147	if (!str_contains($row, '$')) {
154	144	$row = (int) $row + $this->cellRow - $this->referenceRow;
155		}
156
157	147	if (!empty($matches[4])) {
158	5	$worksheet = $this->worksheet->getParentOrThrow()->getSheetByName(trim($matches[4], "'"));
159	5	if ($worksheet === null) {
160		return $this->wrapValue(null);
161		}
162
163	5	return $this->wrapValue(
164	5	$worksheet
165	5	->getCell(str_replace('$', '', "{$column}{$row}"))
166	5	->getCalculatedValue()
167	5	);
168		}
169
170	142	return $this->wrapValue(
171	142	$this->worksheet
172	142	->getCell(str_replace('$', '', "{$column}{$row}"))
173	142	->getCalculatedValue()
174	142	);
175		}
176
177	173	protected function cellConditionCheck(string $condition): string
178		{
179	173	$splitCondition = explode(Calculation::FORMULA_STRING_QUOTE, $condition);
180	173	$i = false;
181	173	foreach ($splitCondition as &$value) {
182		// Only count/replace in alternating array entries (ie. not in quoted strings)
183	173	$i = $i === false;
184	173	if ($i) {
185	173	$value = (string) preg_replace_callback(
186	173	'/' . Calculation::CALCULATION_REGEXP_CELLREF_RELATIVE . '/i',
187	173	[$this, 'conditionCellAdjustment'],
188	173	$value
189	173	);
190		}
191		}
192	173	unset($value);
193
194		// Then rebuild the condition string to return it
195	173	return implode(Calculation::FORMULA_STRING_QUOTE, $splitCondition);
196		}
197
198		/**
199		* @param mixed[] $conditions
200		*
201		* @return mixed[]
202		*/
203	166	protected function adjustConditionsForCellReferences(array $conditions): array
204		{
205	166	return array_map(
206	166	[$this, 'cellConditionCheck'],
207	166	$conditions
208	166	);
209		}
210
211	46	protected function processOperatorComparison(Conditional $conditional): bool
212		{
213	46	if (array_key_exists($conditional->getOperatorType(), self::COMPARISON_RANGE_OPERATORS)) {
214	13	return $this->processRangeOperator($conditional);
215		}
216
217	33	$operator = self::COMPARISON_OPERATORS[$conditional->getOperatorType()];
218	33	$conditions = $this->adjustConditionsForCellReferences($conditional->getConditions());
219		/** @var float\|int\|string */
220	33	$temp1 = $this->wrapCellValue();
221		/** @var scalar */
222	33	$temp2 = array_pop($conditions);
223	33	$expression = sprintf('%s%s%s', (string) $temp1, $operator, (string) $temp2);
224
225	33	return $this->evaluateExpression($expression);
226		}
227
228	2	protected function processColorScale(Conditional $conditional): bool
229		{
230	2	if (is_numeric($this->wrapCellValue()) && $conditional->getColorScale()?->colorScaleReadyForUse()) {
231	2	return true;
232		}
233
234	2	return false;
235		}
236
237	13	protected function processRangeOperator(Conditional $conditional): bool
238		{
239	13	$conditions = $this->adjustConditionsForCellReferences($conditional->getConditions());
240	13	sort($conditions);
241	13	$expression = sprintf(
242	13	(string) preg_replace(
243	13	'/\bA1\b/i',
244	13	(string) $this->wrapCellValue(),
245	13	self::COMPARISON_RANGE_OPERATORS[$conditional->getOperatorType()]
246	13	),
247	13	...$conditions //* @phpstan-ignore-line
248	13	);
249
250	13	return $this->evaluateExpression($expression);
251		}
252
253	7	protected function processDuplicatesComparison(Conditional $conditional): bool
254		{
255	7	$worksheetName = $this->cell->getWorksheet()->getTitle();
256
257	7	$expression = sprintf(
258	7	self::COMPARISON_DUPLICATES_OPERATORS[$conditional->getConditionType()],
259	7	$worksheetName,
260	7	$this->conditionalRange,
261	7	$this->cellConditionCheck($this->cell->getCalculatedValueString())
262	7	);
263
264	7	return $this->evaluateExpression($expression);
265		}
266
267	122	protected function processExpression(Conditional $conditional): bool
268		{
269	122	$conditions = $this->adjustConditionsForCellReferences($conditional->getConditions());
270		/** @var string */
271	122	$expression = array_pop($conditions);
272		/** @var float\|int\|string */
273	122	$temp = $this->wrapCellValue();
274
275	122	$expression = (string) preg_replace(
276	122	'/\b' . $this->referenceCell . '\b/i',
277	122	(string) $temp,
278	122	$expression
279	122	);
280
281	122	return $this->evaluateExpression($expression);
282		}
283
284	173	protected function evaluateExpression(string $expression): bool
285		{
286	173	$expression = "={$expression}";
287
288		try {
289	173	$this->engine->flushInstance();
290	173	$result = (bool) $this->engine->calculateFormula($expression);
291		} catch (Exception) {
292		return false;
293		}
294
295	173	return $result;
296		}
297		}
298

PHPOffice / PhpSpreadsheet

Push — master ( f9b1b7...3922d9 )

CellMatcher::setReferenceCellForExpressions() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like