tablepress / libraries / vendor / PhpSpreadsheet / Style / ConditionalFormatting / CellMatcher.php

CellMatcher.php in TablePress – Tables in WordPress made easy 3.4, at libraries/vendor/PhpSpreadsheet/Style/ConditionalFormatting/CellMatcher.php

299 lines 8.9 KB

Raw Download Zip

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