| 1441 |
ariadna |
1 |
<?php
|
|
|
2 |
|
|
|
3 |
namespace PhpOffice\PhpSpreadsheet\Worksheet;
|
|
|
4 |
|
|
|
5 |
use DateTime;
|
|
|
6 |
use DateTimeZone;
|
|
|
7 |
use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
|
|
|
8 |
use PhpOffice\PhpSpreadsheet\Calculation\Functions;
|
|
|
9 |
use PhpOffice\PhpSpreadsheet\Calculation\Internal\WildcardMatch;
|
|
|
10 |
use PhpOffice\PhpSpreadsheet\Cell\AddressRange;
|
|
|
11 |
use PhpOffice\PhpSpreadsheet\Cell\CellAddress;
|
|
|
12 |
use PhpOffice\PhpSpreadsheet\Cell\CellRange;
|
|
|
13 |
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
|
|
|
14 |
use PhpOffice\PhpSpreadsheet\Exception;
|
|
|
15 |
use PhpOffice\PhpSpreadsheet\Shared\Date;
|
|
|
16 |
use PhpOffice\PhpSpreadsheet\Worksheet\AutoFilter\Column\Rule;
|
|
|
17 |
use Stringable;
|
|
|
18 |
|
|
|
19 |
class AutoFilter implements Stringable
|
|
|
20 |
{
|
|
|
21 |
/**
|
|
|
22 |
* Autofilter Worksheet.
|
|
|
23 |
*/
|
|
|
24 |
private ?Worksheet $workSheet;
|
|
|
25 |
|
|
|
26 |
/**
|
|
|
27 |
* Autofilter Range.
|
|
|
28 |
*/
|
|
|
29 |
private string $range;
|
|
|
30 |
|
|
|
31 |
/**
|
|
|
32 |
* Autofilter Column Ruleset.
|
|
|
33 |
*
|
|
|
34 |
* @var AutoFilter\Column[]
|
|
|
35 |
*/
|
|
|
36 |
private array $columns = [];
|
|
|
37 |
|
|
|
38 |
private bool $evaluated = false;
|
|
|
39 |
|
|
|
40 |
public function getEvaluated(): bool
|
|
|
41 |
{
|
|
|
42 |
return $this->evaluated;
|
|
|
43 |
}
|
|
|
44 |
|
|
|
45 |
public function setEvaluated(bool $value): void
|
|
|
46 |
{
|
|
|
47 |
$this->evaluated = $value;
|
|
|
48 |
}
|
|
|
49 |
|
|
|
50 |
/**
|
|
|
51 |
* Create a new AutoFilter.
|
|
|
52 |
*
|
|
|
53 |
* @param AddressRange<CellAddress>|AddressRange<int>|AddressRange<string>|array{0: int, 1: int, 2: int, 3: int}|array{0: int, 1: int}|string $range
|
|
|
54 |
* A simple string containing a Cell range like 'A1:E10' is permitted
|
|
|
55 |
* or passing in an array of [$fromColumnIndex, $fromRow, $toColumnIndex, $toRow] (e.g. [3, 5, 6, 8]),
|
|
|
56 |
* or an AddressRange object.
|
|
|
57 |
*/
|
|
|
58 |
public function __construct(AddressRange|string|array $range = '', ?Worksheet $worksheet = null)
|
|
|
59 |
{
|
|
|
60 |
if ($range !== '') {
|
|
|
61 |
[, $range] = Worksheet::extractSheetTitle(Validations::validateCellRange($range), true);
|
|
|
62 |
}
|
|
|
63 |
|
|
|
64 |
$this->range = $range ?? '';
|
|
|
65 |
$this->workSheet = $worksheet;
|
|
|
66 |
}
|
|
|
67 |
|
|
|
68 |
public function __destruct()
|
|
|
69 |
{
|
|
|
70 |
$this->workSheet = null;
|
|
|
71 |
}
|
|
|
72 |
|
|
|
73 |
/**
|
|
|
74 |
* Get AutoFilter Parent Worksheet.
|
|
|
75 |
*/
|
|
|
76 |
public function getParent(): null|Worksheet
|
|
|
77 |
{
|
|
|
78 |
return $this->workSheet;
|
|
|
79 |
}
|
|
|
80 |
|
|
|
81 |
/**
|
|
|
82 |
* Set AutoFilter Parent Worksheet.
|
|
|
83 |
*
|
|
|
84 |
* @return $this
|
|
|
85 |
*/
|
|
|
86 |
public function setParent(?Worksheet $worksheet = null): static
|
|
|
87 |
{
|
|
|
88 |
$this->evaluated = false;
|
|
|
89 |
$this->workSheet = $worksheet;
|
|
|
90 |
|
|
|
91 |
return $this;
|
|
|
92 |
}
|
|
|
93 |
|
|
|
94 |
/**
|
|
|
95 |
* Get AutoFilter Range.
|
|
|
96 |
*/
|
|
|
97 |
public function getRange(): string
|
|
|
98 |
{
|
|
|
99 |
return $this->range;
|
|
|
100 |
}
|
|
|
101 |
|
|
|
102 |
/**
|
|
|
103 |
* Set AutoFilter Cell Range.
|
|
|
104 |
*
|
|
|
105 |
* @param AddressRange<CellRange>|array{0: int, 1: int, 2: int, 3: int}|array{0: int, 1: int}|string $range
|
|
|
106 |
* A simple string containing a Cell range like 'A1:E10' or a Cell address like 'A1' is permitted
|
|
|
107 |
* or passing in an array of [$fromColumnIndex, $fromRow, $toColumnIndex, $toRow] (e.g. [3, 5, 6, 8]),
|
|
|
108 |
* or an AddressRange object.
|
|
|
109 |
*/
|
|
|
110 |
public function setRange(AddressRange|string|array $range = ''): self
|
|
|
111 |
{
|
|
|
112 |
$this->evaluated = false;
|
|
|
113 |
// extract coordinate
|
|
|
114 |
if ($range !== '') {
|
|
|
115 |
[, $range] = Worksheet::extractSheetTitle(Validations::validateCellRange($range), true);
|
|
|
116 |
}
|
|
|
117 |
|
|
|
118 |
if (empty($range)) {
|
|
|
119 |
// Discard all column rules
|
|
|
120 |
$this->columns = [];
|
|
|
121 |
$this->range = '';
|
|
|
122 |
|
|
|
123 |
return $this;
|
|
|
124 |
}
|
|
|
125 |
|
|
|
126 |
if (ctype_digit($range) || ctype_alpha($range)) {
|
|
|
127 |
throw new Exception("{$range} is an invalid range for AutoFilter");
|
|
|
128 |
}
|
|
|
129 |
|
|
|
130 |
$this->range = $range;
|
|
|
131 |
// Discard any column rules that are no longer valid within this range
|
|
|
132 |
[$rangeStart, $rangeEnd] = Coordinate::rangeBoundaries($this->range);
|
|
|
133 |
foreach ($this->columns as $key => $value) {
|
|
|
134 |
$colIndex = Coordinate::columnIndexFromString($key);
|
|
|
135 |
if (($rangeStart[0] > $colIndex) || ($rangeEnd[0] < $colIndex)) {
|
|
|
136 |
unset($this->columns[$key]);
|
|
|
137 |
}
|
|
|
138 |
}
|
|
|
139 |
|
|
|
140 |
return $this;
|
|
|
141 |
}
|
|
|
142 |
|
|
|
143 |
public function setRangeToMaxRow(): self
|
|
|
144 |
{
|
|
|
145 |
$this->evaluated = false;
|
|
|
146 |
if ($this->workSheet !== null) {
|
|
|
147 |
$thisrange = $this->range;
|
|
|
148 |
$range = (string) preg_replace('/\d+$/', (string) $this->workSheet->getHighestRow(), $thisrange);
|
|
|
149 |
if ($range !== $thisrange) {
|
|
|
150 |
$this->setRange($range);
|
|
|
151 |
}
|
|
|
152 |
}
|
|
|
153 |
|
|
|
154 |
return $this;
|
|
|
155 |
}
|
|
|
156 |
|
|
|
157 |
/**
|
|
|
158 |
* Get all AutoFilter Columns.
|
|
|
159 |
*
|
|
|
160 |
* @return AutoFilter\Column[]
|
|
|
161 |
*/
|
|
|
162 |
public function getColumns(): array
|
|
|
163 |
{
|
|
|
164 |
return $this->columns;
|
|
|
165 |
}
|
|
|
166 |
|
|
|
167 |
/**
|
|
|
168 |
* Validate that the specified column is in the AutoFilter range.
|
|
|
169 |
*
|
|
|
170 |
* @param string $column Column name (e.g. A)
|
|
|
171 |
*
|
|
|
172 |
* @return int The column offset within the autofilter range
|
|
|
173 |
*/
|
|
|
174 |
public function testColumnInRange(string $column): int
|
|
|
175 |
{
|
|
|
176 |
if (empty($this->range)) {
|
|
|
177 |
throw new Exception('No autofilter range is defined.');
|
|
|
178 |
}
|
|
|
179 |
|
|
|
180 |
$columnIndex = Coordinate::columnIndexFromString($column);
|
|
|
181 |
[$rangeStart, $rangeEnd] = Coordinate::rangeBoundaries($this->range);
|
|
|
182 |
if (($rangeStart[0] > $columnIndex) || ($rangeEnd[0] < $columnIndex)) {
|
|
|
183 |
throw new Exception('Column is outside of current autofilter range.');
|
|
|
184 |
}
|
|
|
185 |
|
|
|
186 |
return $columnIndex - $rangeStart[0];
|
|
|
187 |
}
|
|
|
188 |
|
|
|
189 |
/**
|
|
|
190 |
* Get a specified AutoFilter Column Offset within the defined AutoFilter range.
|
|
|
191 |
*
|
|
|
192 |
* @param string $column Column name (e.g. A)
|
|
|
193 |
*
|
|
|
194 |
* @return int The offset of the specified column within the autofilter range
|
|
|
195 |
*/
|
|
|
196 |
public function getColumnOffset(string $column): int
|
|
|
197 |
{
|
|
|
198 |
return $this->testColumnInRange($column);
|
|
|
199 |
}
|
|
|
200 |
|
|
|
201 |
/**
|
|
|
202 |
* Get a specified AutoFilter Column.
|
|
|
203 |
*
|
|
|
204 |
* @param string $column Column name (e.g. A)
|
|
|
205 |
*/
|
|
|
206 |
public function getColumn(string $column): AutoFilter\Column
|
|
|
207 |
{
|
|
|
208 |
$this->testColumnInRange($column);
|
|
|
209 |
|
|
|
210 |
if (!isset($this->columns[$column])) {
|
|
|
211 |
$this->columns[$column] = new AutoFilter\Column($column, $this);
|
|
|
212 |
}
|
|
|
213 |
|
|
|
214 |
return $this->columns[$column];
|
|
|
215 |
}
|
|
|
216 |
|
|
|
217 |
/**
|
|
|
218 |
* Get a specified AutoFilter Column by it's offset.
|
|
|
219 |
*
|
|
|
220 |
* @param int $columnOffset Column offset within range (starting from 0)
|
|
|
221 |
*/
|
|
|
222 |
public function getColumnByOffset(int $columnOffset): AutoFilter\Column
|
|
|
223 |
{
|
|
|
224 |
[$rangeStart, $rangeEnd] = Coordinate::rangeBoundaries($this->range);
|
|
|
225 |
$pColumn = Coordinate::stringFromColumnIndex($rangeStart[0] + $columnOffset);
|
|
|
226 |
|
|
|
227 |
return $this->getColumn($pColumn);
|
|
|
228 |
}
|
|
|
229 |
|
|
|
230 |
/**
|
|
|
231 |
* Set AutoFilter.
|
|
|
232 |
*
|
|
|
233 |
* @param AutoFilter\Column|string $columnObjectOrString
|
|
|
234 |
* A simple string containing a Column ID like 'A' is permitted
|
|
|
235 |
*
|
|
|
236 |
* @return $this
|
|
|
237 |
*/
|
|
|
238 |
public function setColumn(AutoFilter\Column|string $columnObjectOrString): static
|
|
|
239 |
{
|
|
|
240 |
$this->evaluated = false;
|
|
|
241 |
if ((is_string($columnObjectOrString)) && (!empty($columnObjectOrString))) {
|
|
|
242 |
$column = $columnObjectOrString;
|
|
|
243 |
} elseif ($columnObjectOrString instanceof AutoFilter\Column) {
|
|
|
244 |
$column = $columnObjectOrString->getColumnIndex();
|
|
|
245 |
} else {
|
|
|
246 |
throw new Exception('Column is not within the autofilter range.');
|
|
|
247 |
}
|
|
|
248 |
$this->testColumnInRange($column);
|
|
|
249 |
|
|
|
250 |
if (is_string($columnObjectOrString)) {
|
|
|
251 |
$this->columns[$columnObjectOrString] = new AutoFilter\Column($columnObjectOrString, $this);
|
|
|
252 |
} else {
|
|
|
253 |
$columnObjectOrString->setParent($this);
|
|
|
254 |
$this->columns[$column] = $columnObjectOrString;
|
|
|
255 |
}
|
|
|
256 |
ksort($this->columns);
|
|
|
257 |
|
|
|
258 |
return $this;
|
|
|
259 |
}
|
|
|
260 |
|
|
|
261 |
/**
|
|
|
262 |
* Clear a specified AutoFilter Column.
|
|
|
263 |
*
|
|
|
264 |
* @param string $column Column name (e.g. A)
|
|
|
265 |
*
|
|
|
266 |
* @return $this
|
|
|
267 |
*/
|
|
|
268 |
public function clearColumn(string $column): static
|
|
|
269 |
{
|
|
|
270 |
$this->evaluated = false;
|
|
|
271 |
$this->testColumnInRange($column);
|
|
|
272 |
|
|
|
273 |
if (isset($this->columns[$column])) {
|
|
|
274 |
unset($this->columns[$column]);
|
|
|
275 |
}
|
|
|
276 |
|
|
|
277 |
return $this;
|
|
|
278 |
}
|
|
|
279 |
|
|
|
280 |
/**
|
|
|
281 |
* Shift an AutoFilter Column Rule to a different column.
|
|
|
282 |
*
|
|
|
283 |
* Note: This method bypasses validation of the destination column to ensure it is within this AutoFilter range.
|
|
|
284 |
* Nor does it verify whether any column rule already exists at $toColumn, but will simply override any existing value.
|
|
|
285 |
* Use with caution.
|
|
|
286 |
*
|
|
|
287 |
* @param string $fromColumn Column name (e.g. A)
|
|
|
288 |
* @param string $toColumn Column name (e.g. B)
|
|
|
289 |
*
|
|
|
290 |
* @return $this
|
|
|
291 |
*/
|
|
|
292 |
public function shiftColumn(string $fromColumn, string $toColumn): static
|
|
|
293 |
{
|
|
|
294 |
$this->evaluated = false;
|
|
|
295 |
$fromColumn = strtoupper($fromColumn);
|
|
|
296 |
$toColumn = strtoupper($toColumn);
|
|
|
297 |
|
|
|
298 |
if (isset($this->columns[$fromColumn])) {
|
|
|
299 |
$this->columns[$fromColumn]->setParent();
|
|
|
300 |
$this->columns[$fromColumn]->setColumnIndex($toColumn);
|
|
|
301 |
$this->columns[$toColumn] = $this->columns[$fromColumn];
|
|
|
302 |
$this->columns[$toColumn]->setParent($this);
|
|
|
303 |
unset($this->columns[$fromColumn]);
|
|
|
304 |
|
|
|
305 |
ksort($this->columns);
|
|
|
306 |
}
|
|
|
307 |
|
|
|
308 |
return $this;
|
|
|
309 |
}
|
|
|
310 |
|
|
|
311 |
/**
|
|
|
312 |
* Test if cell value is in the defined set of values.
|
|
|
313 |
*
|
|
|
314 |
* @param array{blanks: bool, filterValues: array<string,array<string,string>>} $dataSet
|
|
|
315 |
*/
|
|
|
316 |
protected static function filterTestInSimpleDataSet(mixed $cellValue, array $dataSet): bool
|
|
|
317 |
{
|
|
|
318 |
$dataSetValues = $dataSet['filterValues'];
|
|
|
319 |
$blanks = $dataSet['blanks'];
|
|
|
320 |
if (($cellValue === '') || ($cellValue === null)) {
|
|
|
321 |
return $blanks;
|
|
|
322 |
}
|
|
|
323 |
|
|
|
324 |
return in_array($cellValue, $dataSetValues);
|
|
|
325 |
}
|
|
|
326 |
|
|
|
327 |
/**
|
|
|
328 |
* Test if cell value is in the defined set of Excel date values.
|
|
|
329 |
*
|
|
|
330 |
* @param array{blanks: bool, filterValues: array<string,array<string,string>>} $dataSet
|
|
|
331 |
*/
|
|
|
332 |
protected static function filterTestInDateGroupSet(mixed $cellValue, array $dataSet): bool
|
|
|
333 |
{
|
|
|
334 |
$dateSet = $dataSet['filterValues'];
|
|
|
335 |
$blanks = $dataSet['blanks'];
|
|
|
336 |
if (($cellValue === '') || ($cellValue === null)) {
|
|
|
337 |
return $blanks;
|
|
|
338 |
}
|
|
|
339 |
$timeZone = new DateTimeZone('UTC');
|
|
|
340 |
|
|
|
341 |
if (is_numeric($cellValue)) {
|
|
|
342 |
$dateTime = Date::excelToDateTimeObject((float) $cellValue, $timeZone);
|
|
|
343 |
$cellValue = (float) $cellValue;
|
|
|
344 |
if ($cellValue < 1) {
|
|
|
345 |
// Just the time part
|
|
|
346 |
$dtVal = $dateTime->format('His');
|
|
|
347 |
$dateSet = $dateSet['time'];
|
|
|
348 |
} elseif ($cellValue == floor($cellValue)) {
|
|
|
349 |
// Just the date part
|
|
|
350 |
$dtVal = $dateTime->format('Ymd');
|
|
|
351 |
$dateSet = $dateSet['date'];
|
|
|
352 |
} else {
|
|
|
353 |
// date and time parts
|
|
|
354 |
$dtVal = $dateTime->format('YmdHis');
|
|
|
355 |
$dateSet = $dateSet['dateTime'];
|
|
|
356 |
}
|
|
|
357 |
foreach ($dateSet as $dateValue) {
|
|
|
358 |
// Use of substr to extract value at the appropriate group level
|
|
|
359 |
if (str_starts_with($dtVal, $dateValue)) {
|
|
|
360 |
return true;
|
|
|
361 |
}
|
|
|
362 |
}
|
|
|
363 |
}
|
|
|
364 |
|
|
|
365 |
return false;
|
|
|
366 |
}
|
|
|
367 |
|
|
|
368 |
/**
|
|
|
369 |
* Test if cell value is within a set of values defined by a ruleset.
|
|
|
370 |
*
|
|
|
371 |
* @param mixed[] $ruleSet
|
|
|
372 |
*/
|
|
|
373 |
protected static function filterTestInCustomDataSet(mixed $cellValue, array $ruleSet): bool
|
|
|
374 |
{
|
|
|
375 |
/** @var array[] $dataSet */
|
|
|
376 |
$dataSet = $ruleSet['filterRules'];
|
|
|
377 |
$join = $ruleSet['join'];
|
|
|
378 |
$customRuleForBlanks = $ruleSet['customRuleForBlanks'] ?? false;
|
|
|
379 |
|
|
|
380 |
if (!$customRuleForBlanks) {
|
|
|
381 |
// Blank cells are always ignored, so return a FALSE
|
|
|
382 |
if (($cellValue === '') || ($cellValue === null)) {
|
|
|
383 |
return false;
|
|
|
384 |
}
|
|
|
385 |
}
|
|
|
386 |
$returnVal = ($join == AutoFilter\Column::AUTOFILTER_COLUMN_JOIN_AND);
|
|
|
387 |
foreach ($dataSet as $rule) {
|
|
|
388 |
/** @var string $ruleValue */
|
|
|
389 |
$ruleValue = $rule['value'];
|
|
|
390 |
/** @var string $ruleOperator */
|
|
|
391 |
$ruleOperator = $rule['operator'];
|
|
|
392 |
/** @var string $cellValueString */
|
|
|
393 |
$cellValueString = $cellValue ?? '';
|
|
|
394 |
$retVal = false;
|
|
|
395 |
|
|
|
396 |
if (is_numeric($ruleValue)) {
|
|
|
397 |
// Numeric values are tested using the appropriate operator
|
|
|
398 |
$numericTest = is_numeric($cellValue);
|
|
|
399 |
switch ($ruleOperator) {
|
|
|
400 |
case Rule::AUTOFILTER_COLUMN_RULE_EQUAL:
|
|
|
401 |
$retVal = $numericTest && ($cellValue == $ruleValue);
|
|
|
402 |
|
|
|
403 |
break;
|
|
|
404 |
case Rule::AUTOFILTER_COLUMN_RULE_NOTEQUAL:
|
|
|
405 |
$retVal = !$numericTest || ($cellValue != $ruleValue);
|
|
|
406 |
|
|
|
407 |
break;
|
|
|
408 |
case Rule::AUTOFILTER_COLUMN_RULE_GREATERTHAN:
|
|
|
409 |
$retVal = $numericTest && ($cellValue > $ruleValue);
|
|
|
410 |
|
|
|
411 |
break;
|
|
|
412 |
case Rule::AUTOFILTER_COLUMN_RULE_GREATERTHANOREQUAL:
|
|
|
413 |
$retVal = $numericTest && ($cellValue >= $ruleValue);
|
|
|
414 |
|
|
|
415 |
break;
|
|
|
416 |
case Rule::AUTOFILTER_COLUMN_RULE_LESSTHAN:
|
|
|
417 |
$retVal = $numericTest && ($cellValue < $ruleValue);
|
|
|
418 |
|
|
|
419 |
break;
|
|
|
420 |
case Rule::AUTOFILTER_COLUMN_RULE_LESSTHANOREQUAL:
|
|
|
421 |
$retVal = $numericTest && ($cellValue <= $ruleValue);
|
|
|
422 |
|
|
|
423 |
break;
|
|
|
424 |
}
|
|
|
425 |
} elseif ($ruleValue == '') {
|
|
|
426 |
$retVal = match ($ruleOperator) {
|
|
|
427 |
Rule::AUTOFILTER_COLUMN_RULE_EQUAL => ($cellValue === '') || ($cellValue === null),
|
|
|
428 |
Rule::AUTOFILTER_COLUMN_RULE_NOTEQUAL => ($cellValue != ''),
|
|
|
429 |
default => true,
|
|
|
430 |
};
|
|
|
431 |
} else {
|
|
|
432 |
// String values are always tested for equality, factoring in for wildcards (hence a regexp test)
|
|
|
433 |
switch ($ruleOperator) {
|
|
|
434 |
case Rule::AUTOFILTER_COLUMN_RULE_EQUAL:
|
|
|
435 |
$retVal = (bool) preg_match('/^' . $ruleValue . '$/i', $cellValueString);
|
|
|
436 |
|
|
|
437 |
break;
|
|
|
438 |
case Rule::AUTOFILTER_COLUMN_RULE_NOTEQUAL:
|
|
|
439 |
$retVal = !((bool) preg_match('/^' . $ruleValue . '$/i', $cellValueString));
|
|
|
440 |
|
|
|
441 |
break;
|
|
|
442 |
case Rule::AUTOFILTER_COLUMN_RULE_GREATERTHAN:
|
|
|
443 |
$retVal = strcasecmp($cellValueString, $ruleValue) > 0;
|
|
|
444 |
|
|
|
445 |
break;
|
|
|
446 |
case Rule::AUTOFILTER_COLUMN_RULE_GREATERTHANOREQUAL:
|
|
|
447 |
$retVal = strcasecmp($cellValueString, $ruleValue) >= 0;
|
|
|
448 |
|
|
|
449 |
break;
|
|
|
450 |
case Rule::AUTOFILTER_COLUMN_RULE_LESSTHAN:
|
|
|
451 |
$retVal = strcasecmp($cellValueString, $ruleValue) < 0;
|
|
|
452 |
|
|
|
453 |
break;
|
|
|
454 |
case Rule::AUTOFILTER_COLUMN_RULE_LESSTHANOREQUAL:
|
|
|
455 |
$retVal = strcasecmp($cellValueString, $ruleValue) <= 0;
|
|
|
456 |
|
|
|
457 |
break;
|
|
|
458 |
}
|
|
|
459 |
}
|
|
|
460 |
// If there are multiple conditions, then we need to test both using the appropriate join operator
|
|
|
461 |
switch ($join) {
|
|
|
462 |
case AutoFilter\Column::AUTOFILTER_COLUMN_JOIN_OR:
|
|
|
463 |
$returnVal = $returnVal || $retVal;
|
|
|
464 |
// Break as soon as we have a TRUE match for OR joins,
|
|
|
465 |
// to avoid unnecessary additional code execution
|
|
|
466 |
if ($returnVal) {
|
|
|
467 |
return $returnVal;
|
|
|
468 |
}
|
|
|
469 |
|
|
|
470 |
break;
|
|
|
471 |
case AutoFilter\Column::AUTOFILTER_COLUMN_JOIN_AND:
|
|
|
472 |
$returnVal = $returnVal && $retVal;
|
|
|
473 |
|
|
|
474 |
break;
|
|
|
475 |
}
|
|
|
476 |
}
|
|
|
477 |
|
|
|
478 |
return $returnVal;
|
|
|
479 |
}
|
|
|
480 |
|
|
|
481 |
/**
|
|
|
482 |
* Test if cell date value is matches a set of values defined by a set of months.
|
|
|
483 |
*
|
|
|
484 |
* @param mixed[] $monthSet
|
|
|
485 |
*/
|
|
|
486 |
protected static function filterTestInPeriodDateSet(mixed $cellValue, array $monthSet): bool
|
|
|
487 |
{
|
|
|
488 |
// Blank cells are always ignored, so return a FALSE
|
|
|
489 |
if (($cellValue === '') || ($cellValue === null)) {
|
|
|
490 |
return false;
|
|
|
491 |
}
|
|
|
492 |
|
|
|
493 |
if (is_numeric($cellValue)) {
|
|
|
494 |
$dateObject = Date::excelToDateTimeObject((float) $cellValue, new DateTimeZone('UTC'));
|
|
|
495 |
$dateValue = (int) $dateObject->format('m');
|
|
|
496 |
if (in_array($dateValue, $monthSet)) {
|
|
|
497 |
return true;
|
|
|
498 |
}
|
|
|
499 |
}
|
|
|
500 |
|
|
|
501 |
return false;
|
|
|
502 |
}
|
|
|
503 |
|
|
|
504 |
private static function makeDateObject(int $year, int $month, int $day, int $hour = 0, int $minute = 0, int $second = 0): DateTime
|
|
|
505 |
{
|
|
|
506 |
$baseDate = new DateTime();
|
|
|
507 |
$baseDate->setDate($year, $month, $day);
|
|
|
508 |
$baseDate->setTime($hour, $minute, $second);
|
|
|
509 |
|
|
|
510 |
return $baseDate;
|
|
|
511 |
}
|
|
|
512 |
|
|
|
513 |
private const DATE_FUNCTIONS = [
|
|
|
514 |
Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTMONTH => 'dynamicLastMonth',
|
|
|
515 |
Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTQUARTER => 'dynamicLastQuarter',
|
|
|
516 |
Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTWEEK => 'dynamicLastWeek',
|
|
|
517 |
Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTYEAR => 'dynamicLastYear',
|
|
|
518 |
Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTMONTH => 'dynamicNextMonth',
|
|
|
519 |
Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTQUARTER => 'dynamicNextQuarter',
|
|
|
520 |
Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTWEEK => 'dynamicNextWeek',
|
|
|
521 |
Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTYEAR => 'dynamicNextYear',
|
|
|
522 |
Rule::AUTOFILTER_RULETYPE_DYNAMIC_THISMONTH => 'dynamicThisMonth',
|
|
|
523 |
Rule::AUTOFILTER_RULETYPE_DYNAMIC_THISQUARTER => 'dynamicThisQuarter',
|
|
|
524 |
Rule::AUTOFILTER_RULETYPE_DYNAMIC_THISWEEK => 'dynamicThisWeek',
|
|
|
525 |
Rule::AUTOFILTER_RULETYPE_DYNAMIC_THISYEAR => 'dynamicThisYear',
|
|
|
526 |
Rule::AUTOFILTER_RULETYPE_DYNAMIC_TODAY => 'dynamicToday',
|
|
|
527 |
Rule::AUTOFILTER_RULETYPE_DYNAMIC_TOMORROW => 'dynamicTomorrow',
|
|
|
528 |
Rule::AUTOFILTER_RULETYPE_DYNAMIC_YEARTODATE => 'dynamicYearToDate',
|
|
|
529 |
Rule::AUTOFILTER_RULETYPE_DYNAMIC_YESTERDAY => 'dynamicYesterday',
|
|
|
530 |
];
|
|
|
531 |
|
|
|
532 |
private static function dynamicLastMonth(): array
|
|
|
533 |
{
|
|
|
534 |
$maxval = new DateTime();
|
|
|
535 |
$year = (int) $maxval->format('Y');
|
|
|
536 |
$month = (int) $maxval->format('m');
|
|
|
537 |
$maxval->setDate($year, $month, 1);
|
|
|
538 |
$maxval->setTime(0, 0, 0);
|
|
|
539 |
$val = clone $maxval;
|
|
|
540 |
$val->modify('-1 month');
|
|
|
541 |
|
|
|
542 |
return [$val, $maxval];
|
|
|
543 |
}
|
|
|
544 |
|
|
|
545 |
private static function firstDayOfQuarter(): DateTime
|
|
|
546 |
{
|
|
|
547 |
$val = new DateTime();
|
|
|
548 |
$year = (int) $val->format('Y');
|
|
|
549 |
$month = (int) $val->format('m');
|
|
|
550 |
$month = 3 * intdiv($month - 1, 3) + 1;
|
|
|
551 |
$val->setDate($year, $month, 1);
|
|
|
552 |
$val->setTime(0, 0, 0);
|
|
|
553 |
|
|
|
554 |
return $val;
|
|
|
555 |
}
|
|
|
556 |
|
|
|
557 |
private static function dynamicLastQuarter(): array
|
|
|
558 |
{
|
|
|
559 |
$maxval = self::firstDayOfQuarter();
|
|
|
560 |
$val = clone $maxval;
|
|
|
561 |
$val->modify('-3 months');
|
|
|
562 |
|
|
|
563 |
return [$val, $maxval];
|
|
|
564 |
}
|
|
|
565 |
|
|
|
566 |
private static function dynamicLastWeek(): array
|
|
|
567 |
{
|
|
|
568 |
$val = new DateTime();
|
|
|
569 |
$val->setTime(0, 0, 0);
|
|
|
570 |
$dayOfWeek = (int) $val->format('w'); // Sunday is 0
|
|
|
571 |
$subtract = $dayOfWeek + 7; // revert to prior Sunday
|
|
|
572 |
$val->modify("-$subtract days");
|
|
|
573 |
$maxval = clone $val;
|
|
|
574 |
$maxval->modify('+7 days');
|
|
|
575 |
|
|
|
576 |
return [$val, $maxval];
|
|
|
577 |
}
|
|
|
578 |
|
|
|
579 |
private static function dynamicLastYear(): array
|
|
|
580 |
{
|
|
|
581 |
$val = new DateTime();
|
|
|
582 |
$year = (int) $val->format('Y');
|
|
|
583 |
$val = self::makeDateObject($year - 1, 1, 1);
|
|
|
584 |
$maxval = self::makeDateObject($year, 1, 1);
|
|
|
585 |
|
|
|
586 |
return [$val, $maxval];
|
|
|
587 |
}
|
|
|
588 |
|
|
|
589 |
private static function dynamicNextMonth(): array
|
|
|
590 |
{
|
|
|
591 |
$val = new DateTime();
|
|
|
592 |
$year = (int) $val->format('Y');
|
|
|
593 |
$month = (int) $val->format('m');
|
|
|
594 |
$val->setDate($year, $month, 1);
|
|
|
595 |
$val->setTime(0, 0, 0);
|
|
|
596 |
$val->modify('+1 month');
|
|
|
597 |
$maxval = clone $val;
|
|
|
598 |
$maxval->modify('+1 month');
|
|
|
599 |
|
|
|
600 |
return [$val, $maxval];
|
|
|
601 |
}
|
|
|
602 |
|
|
|
603 |
private static function dynamicNextQuarter(): array
|
|
|
604 |
{
|
|
|
605 |
$val = self::firstDayOfQuarter();
|
|
|
606 |
$val->modify('+3 months');
|
|
|
607 |
$maxval = clone $val;
|
|
|
608 |
$maxval->modify('+3 months');
|
|
|
609 |
|
|
|
610 |
return [$val, $maxval];
|
|
|
611 |
}
|
|
|
612 |
|
|
|
613 |
private static function dynamicNextWeek(): array
|
|
|
614 |
{
|
|
|
615 |
$val = new DateTime();
|
|
|
616 |
$val->setTime(0, 0, 0);
|
|
|
617 |
$dayOfWeek = (int) $val->format('w'); // Sunday is 0
|
|
|
618 |
$add = 7 - $dayOfWeek; // move to next Sunday
|
|
|
619 |
$val->modify("+$add days");
|
|
|
620 |
$maxval = clone $val;
|
|
|
621 |
$maxval->modify('+7 days');
|
|
|
622 |
|
|
|
623 |
return [$val, $maxval];
|
|
|
624 |
}
|
|
|
625 |
|
|
|
626 |
private static function dynamicNextYear(): array
|
|
|
627 |
{
|
|
|
628 |
$val = new DateTime();
|
|
|
629 |
$year = (int) $val->format('Y');
|
|
|
630 |
$val = self::makeDateObject($year + 1, 1, 1);
|
|
|
631 |
$maxval = self::makeDateObject($year + 2, 1, 1);
|
|
|
632 |
|
|
|
633 |
return [$val, $maxval];
|
|
|
634 |
}
|
|
|
635 |
|
|
|
636 |
private static function dynamicThisMonth(): array
|
|
|
637 |
{
|
|
|
638 |
$baseDate = new DateTime();
|
|
|
639 |
$baseDate->setTime(0, 0, 0);
|
|
|
640 |
$year = (int) $baseDate->format('Y');
|
|
|
641 |
$month = (int) $baseDate->format('m');
|
|
|
642 |
$val = self::makeDateObject($year, $month, 1);
|
|
|
643 |
$maxval = clone $val;
|
|
|
644 |
$maxval->modify('+1 month');
|
|
|
645 |
|
|
|
646 |
return [$val, $maxval];
|
|
|
647 |
}
|
|
|
648 |
|
|
|
649 |
private static function dynamicThisQuarter(): array
|
|
|
650 |
{
|
|
|
651 |
$val = self::firstDayOfQuarter();
|
|
|
652 |
$maxval = clone $val;
|
|
|
653 |
$maxval->modify('+3 months');
|
|
|
654 |
|
|
|
655 |
return [$val, $maxval];
|
|
|
656 |
}
|
|
|
657 |
|
|
|
658 |
private static function dynamicThisWeek(): array
|
|
|
659 |
{
|
|
|
660 |
$val = new DateTime();
|
|
|
661 |
$val->setTime(0, 0, 0);
|
|
|
662 |
$dayOfWeek = (int) $val->format('w'); // Sunday is 0
|
|
|
663 |
$subtract = $dayOfWeek; // revert to Sunday
|
|
|
664 |
$val->modify("-$subtract days");
|
|
|
665 |
$maxval = clone $val;
|
|
|
666 |
$maxval->modify('+7 days');
|
|
|
667 |
|
|
|
668 |
return [$val, $maxval];
|
|
|
669 |
}
|
|
|
670 |
|
|
|
671 |
private static function dynamicThisYear(): array
|
|
|
672 |
{
|
|
|
673 |
$val = new DateTime();
|
|
|
674 |
$year = (int) $val->format('Y');
|
|
|
675 |
$val = self::makeDateObject($year, 1, 1);
|
|
|
676 |
$maxval = self::makeDateObject($year + 1, 1, 1);
|
|
|
677 |
|
|
|
678 |
return [$val, $maxval];
|
|
|
679 |
}
|
|
|
680 |
|
|
|
681 |
private static function dynamicToday(): array
|
|
|
682 |
{
|
|
|
683 |
$val = new DateTime();
|
|
|
684 |
$val->setTime(0, 0, 0);
|
|
|
685 |
$maxval = clone $val;
|
|
|
686 |
$maxval->modify('+1 day');
|
|
|
687 |
|
|
|
688 |
return [$val, $maxval];
|
|
|
689 |
}
|
|
|
690 |
|
|
|
691 |
private static function dynamicTomorrow(): array
|
|
|
692 |
{
|
|
|
693 |
$val = new DateTime();
|
|
|
694 |
$val->setTime(0, 0, 0);
|
|
|
695 |
$val->modify('+1 day');
|
|
|
696 |
$maxval = clone $val;
|
|
|
697 |
$maxval->modify('+1 day');
|
|
|
698 |
|
|
|
699 |
return [$val, $maxval];
|
|
|
700 |
}
|
|
|
701 |
|
|
|
702 |
private static function dynamicYearToDate(): array
|
|
|
703 |
{
|
|
|
704 |
$maxval = new DateTime();
|
|
|
705 |
$maxval->setTime(0, 0, 0);
|
|
|
706 |
$val = self::makeDateObject((int) $maxval->format('Y'), 1, 1);
|
|
|
707 |
$maxval->modify('+1 day');
|
|
|
708 |
|
|
|
709 |
return [$val, $maxval];
|
|
|
710 |
}
|
|
|
711 |
|
|
|
712 |
private static function dynamicYesterday(): array
|
|
|
713 |
{
|
|
|
714 |
$maxval = new DateTime();
|
|
|
715 |
$maxval->setTime(0, 0, 0);
|
|
|
716 |
$val = clone $maxval;
|
|
|
717 |
$val->modify('-1 day');
|
|
|
718 |
|
|
|
719 |
return [$val, $maxval];
|
|
|
720 |
}
|
|
|
721 |
|
|
|
722 |
/**
|
|
|
723 |
* Convert a dynamic rule daterange to a custom filter range expression for ease of calculation.
|
|
|
724 |
*
|
|
|
725 |
* @return mixed[]
|
|
|
726 |
*/
|
|
|
727 |
private function dynamicFilterDateRange(string $dynamicRuleType, AutoFilter\Column &$filterColumn): array
|
|
|
728 |
{
|
|
|
729 |
$ruleValues = [];
|
|
|
730 |
$callBack = [__CLASS__, self::DATE_FUNCTIONS[$dynamicRuleType]]; // What if not found?
|
|
|
731 |
// Calculate start/end dates for the required date range based on current date
|
|
|
732 |
// Val is lowest permitted value.
|
|
|
733 |
// Maxval is greater than highest permitted value
|
|
|
734 |
$val = $maxval = 0;
|
|
|
735 |
if (is_callable($callBack)) { //* @phpstan-ignore-line
|
|
|
736 |
[$val, $maxval] = $callBack();
|
|
|
737 |
}
|
|
|
738 |
$val = Date::dateTimeToExcel($val);
|
|
|
739 |
$maxval = Date::dateTimeToExcel($maxval);
|
|
|
740 |
|
|
|
741 |
// Set the filter column rule attributes ready for writing
|
|
|
742 |
$filterColumn->setAttributes(['val' => $val, 'maxVal' => $maxval]);
|
|
|
743 |
|
|
|
744 |
// Set the rules for identifying rows for hide/show
|
|
|
745 |
$ruleValues[] = ['operator' => Rule::AUTOFILTER_COLUMN_RULE_GREATERTHANOREQUAL, 'value' => $val];
|
|
|
746 |
$ruleValues[] = ['operator' => Rule::AUTOFILTER_COLUMN_RULE_LESSTHAN, 'value' => $maxval];
|
|
|
747 |
|
|
|
748 |
return ['method' => 'filterTestInCustomDataSet', 'arguments' => ['filterRules' => $ruleValues, 'join' => AutoFilter\Column::AUTOFILTER_COLUMN_JOIN_AND]];
|
|
|
749 |
}
|
|
|
750 |
|
|
|
751 |
/**
|
|
|
752 |
* Apply the AutoFilter rules to the AutoFilter Range.
|
|
|
753 |
*/
|
|
|
754 |
private function calculateTopTenValue(string $columnID, int $startRow, int $endRow, ?string $ruleType, mixed $ruleValue): mixed
|
|
|
755 |
{
|
|
|
756 |
$range = $columnID . $startRow . ':' . $columnID . $endRow;
|
|
|
757 |
$retVal = null;
|
|
|
758 |
if ($this->workSheet !== null) {
|
|
|
759 |
$dataValues = Functions::flattenArray($this->workSheet->rangeToArray($range, null, true, false));
|
|
|
760 |
$dataValues = array_filter($dataValues);
|
|
|
761 |
|
|
|
762 |
if ($ruleType == Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_TOP) {
|
|
|
763 |
rsort($dataValues);
|
|
|
764 |
} else {
|
|
|
765 |
sort($dataValues);
|
|
|
766 |
}
|
|
|
767 |
|
|
|
768 |
if (is_numeric($ruleValue)) {
|
|
|
769 |
$ruleValue = (int) $ruleValue;
|
|
|
770 |
}
|
|
|
771 |
if ($ruleValue === null || is_int($ruleValue)) {
|
|
|
772 |
$slice = array_slice($dataValues, 0, $ruleValue);
|
|
|
773 |
$retVal = array_pop($slice);
|
|
|
774 |
}
|
|
|
775 |
}
|
|
|
776 |
|
|
|
777 |
return $retVal;
|
|
|
778 |
}
|
|
|
779 |
|
|
|
780 |
/**
|
|
|
781 |
* Apply the AutoFilter rules to the AutoFilter Range.
|
|
|
782 |
*
|
|
|
783 |
* @return $this
|
|
|
784 |
*/
|
|
|
785 |
public function showHideRows(): static
|
|
|
786 |
{
|
|
|
787 |
if ($this->workSheet === null) {
|
|
|
788 |
return $this;
|
|
|
789 |
}
|
|
|
790 |
[$rangeStart, $rangeEnd] = Coordinate::rangeBoundaries($this->range);
|
|
|
791 |
|
|
|
792 |
// The heading row should always be visible
|
|
|
793 |
$this->workSheet->getRowDimension($rangeStart[1])->setVisible(true);
|
|
|
794 |
|
|
|
795 |
$columnFilterTests = [];
|
|
|
796 |
foreach ($this->columns as $columnID => $filterColumn) {
|
|
|
797 |
$rules = $filterColumn->getRules();
|
|
|
798 |
switch ($filterColumn->getFilterType()) {
|
|
|
799 |
case AutoFilter\Column::AUTOFILTER_FILTERTYPE_FILTER:
|
|
|
800 |
$ruleType = null;
|
|
|
801 |
$ruleValues = [];
|
|
|
802 |
// Build a list of the filter value selections
|
|
|
803 |
foreach ($rules as $rule) {
|
|
|
804 |
$ruleType = $rule->getRuleType();
|
|
|
805 |
$ruleValues[] = $rule->getValue();
|
|
|
806 |
}
|
|
|
807 |
// Test if we want to include blanks in our filter criteria
|
|
|
808 |
$blanks = false;
|
|
|
809 |
$ruleDataSet = array_filter($ruleValues);
|
|
|
810 |
if (count($ruleValues) != count($ruleDataSet)) {
|
|
|
811 |
$blanks = true;
|
|
|
812 |
}
|
|
|
813 |
if ($ruleType == Rule::AUTOFILTER_RULETYPE_FILTER) {
|
|
|
814 |
// Filter on absolute values
|
|
|
815 |
$columnFilterTests[$columnID] = [
|
|
|
816 |
'method' => 'filterTestInSimpleDataSet',
|
|
|
817 |
'arguments' => ['filterValues' => $ruleDataSet, 'blanks' => $blanks],
|
|
|
818 |
];
|
|
|
819 |
} elseif ($ruleType !== null) {
|
|
|
820 |
// Filter on date group values
|
|
|
821 |
$arguments = [
|
|
|
822 |
'date' => [],
|
|
|
823 |
'time' => [],
|
|
|
824 |
'dateTime' => [],
|
|
|
825 |
];
|
|
|
826 |
foreach ($ruleDataSet as $ruleValue) {
|
|
|
827 |
if (!is_array($ruleValue)) {
|
|
|
828 |
continue;
|
|
|
829 |
}
|
|
|
830 |
$date = $time = '';
|
|
|
831 |
if (
|
|
|
832 |
(isset($ruleValue[Rule::AUTOFILTER_RULETYPE_DATEGROUP_YEAR]))
|
|
|
833 |
&& ($ruleValue[Rule::AUTOFILTER_RULETYPE_DATEGROUP_YEAR] !== '')
|
|
|
834 |
) {
|
|
|
835 |
$date .= sprintf('%04d', $ruleValue[Rule::AUTOFILTER_RULETYPE_DATEGROUP_YEAR]);
|
|
|
836 |
}
|
|
|
837 |
if (
|
|
|
838 |
(isset($ruleValue[Rule::AUTOFILTER_RULETYPE_DATEGROUP_MONTH]))
|
|
|
839 |
&& ($ruleValue[Rule::AUTOFILTER_RULETYPE_DATEGROUP_MONTH] != '')
|
|
|
840 |
) {
|
|
|
841 |
$date .= sprintf('%02d', $ruleValue[Rule::AUTOFILTER_RULETYPE_DATEGROUP_MONTH]);
|
|
|
842 |
}
|
|
|
843 |
if (
|
|
|
844 |
(isset($ruleValue[Rule::AUTOFILTER_RULETYPE_DATEGROUP_DAY]))
|
|
|
845 |
&& ($ruleValue[Rule::AUTOFILTER_RULETYPE_DATEGROUP_DAY] !== '')
|
|
|
846 |
) {
|
|
|
847 |
$date .= sprintf('%02d', $ruleValue[Rule::AUTOFILTER_RULETYPE_DATEGROUP_DAY]);
|
|
|
848 |
}
|
|
|
849 |
if (
|
|
|
850 |
(isset($ruleValue[Rule::AUTOFILTER_RULETYPE_DATEGROUP_HOUR]))
|
|
|
851 |
&& ($ruleValue[Rule::AUTOFILTER_RULETYPE_DATEGROUP_HOUR] !== '')
|
|
|
852 |
) {
|
|
|
853 |
$time .= sprintf('%02d', $ruleValue[Rule::AUTOFILTER_RULETYPE_DATEGROUP_HOUR]);
|
|
|
854 |
}
|
|
|
855 |
if (
|
|
|
856 |
(isset($ruleValue[Rule::AUTOFILTER_RULETYPE_DATEGROUP_MINUTE]))
|
|
|
857 |
&& ($ruleValue[Rule::AUTOFILTER_RULETYPE_DATEGROUP_MINUTE] !== '')
|
|
|
858 |
) {
|
|
|
859 |
$time .= sprintf('%02d', $ruleValue[Rule::AUTOFILTER_RULETYPE_DATEGROUP_MINUTE]);
|
|
|
860 |
}
|
|
|
861 |
if (
|
|
|
862 |
(isset($ruleValue[Rule::AUTOFILTER_RULETYPE_DATEGROUP_SECOND]))
|
|
|
863 |
&& ($ruleValue[Rule::AUTOFILTER_RULETYPE_DATEGROUP_SECOND] !== '')
|
|
|
864 |
) {
|
|
|
865 |
$time .= sprintf('%02d', $ruleValue[Rule::AUTOFILTER_RULETYPE_DATEGROUP_SECOND]);
|
|
|
866 |
}
|
|
|
867 |
$dateTime = $date . $time;
|
|
|
868 |
$arguments['date'][] = $date;
|
|
|
869 |
$arguments['time'][] = $time;
|
|
|
870 |
$arguments['dateTime'][] = $dateTime;
|
|
|
871 |
}
|
|
|
872 |
// Remove empty elements
|
|
|
873 |
$arguments['date'] = array_filter($arguments['date']);
|
|
|
874 |
$arguments['time'] = array_filter($arguments['time']);
|
|
|
875 |
$arguments['dateTime'] = array_filter($arguments['dateTime']);
|
|
|
876 |
$columnFilterTests[$columnID] = [
|
|
|
877 |
'method' => 'filterTestInDateGroupSet',
|
|
|
878 |
'arguments' => ['filterValues' => $arguments, 'blanks' => $blanks],
|
|
|
879 |
];
|
|
|
880 |
}
|
|
|
881 |
|
|
|
882 |
break;
|
|
|
883 |
case AutoFilter\Column::AUTOFILTER_FILTERTYPE_CUSTOMFILTER:
|
|
|
884 |
$customRuleForBlanks = true;
|
|
|
885 |
$ruleValues = [];
|
|
|
886 |
// Build a list of the filter value selections
|
|
|
887 |
foreach ($rules as $rule) {
|
|
|
888 |
$ruleValue = $rule->getValue();
|
|
|
889 |
if (!is_array($ruleValue) && !is_numeric($ruleValue)) {
|
|
|
890 |
// Convert to a regexp allowing for regexp reserved characters, wildcards and escaped wildcards
|
|
|
891 |
$ruleValue = WildcardMatch::wildcard($ruleValue);
|
|
|
892 |
if (trim($ruleValue) == '') {
|
|
|
893 |
$customRuleForBlanks = true;
|
|
|
894 |
$ruleValue = trim($ruleValue);
|
|
|
895 |
}
|
|
|
896 |
}
|
|
|
897 |
$ruleValues[] = ['operator' => $rule->getOperator(), 'value' => $ruleValue];
|
|
|
898 |
}
|
|
|
899 |
$join = $filterColumn->getJoin();
|
|
|
900 |
$columnFilterTests[$columnID] = [
|
|
|
901 |
'method' => 'filterTestInCustomDataSet',
|
|
|
902 |
'arguments' => ['filterRules' => $ruleValues, 'join' => $join, 'customRuleForBlanks' => $customRuleForBlanks],
|
|
|
903 |
];
|
|
|
904 |
|
|
|
905 |
break;
|
|
|
906 |
case AutoFilter\Column::AUTOFILTER_FILTERTYPE_DYNAMICFILTER:
|
|
|
907 |
$ruleValues = [];
|
|
|
908 |
foreach ($rules as $rule) {
|
|
|
909 |
// We should only ever have one Dynamic Filter Rule anyway
|
|
|
910 |
$dynamicRuleType = $rule->getGrouping();
|
|
|
911 |
if (
|
|
|
912 |
($dynamicRuleType == Rule::AUTOFILTER_RULETYPE_DYNAMIC_ABOVEAVERAGE)
|
|
|
913 |
|| ($dynamicRuleType == Rule::AUTOFILTER_RULETYPE_DYNAMIC_BELOWAVERAGE)
|
|
|
914 |
) {
|
|
|
915 |
// Number (Average) based
|
|
|
916 |
// Calculate the average
|
|
|
917 |
$averageFormula = '=AVERAGE(' . $columnID . ($rangeStart[1] + 1) . ':' . $columnID . $rangeEnd[1] . ')';
|
|
|
918 |
$average = Calculation::getInstance($this->workSheet->getParent())->calculateFormula($averageFormula, null, $this->workSheet->getCell('A1'));
|
|
|
919 |
while (is_array($average)) {
|
|
|
920 |
$average = array_pop($average);
|
|
|
921 |
}
|
|
|
922 |
// Set above/below rule based on greaterThan or LessTan
|
|
|
923 |
$operator = ($dynamicRuleType === Rule::AUTOFILTER_RULETYPE_DYNAMIC_ABOVEAVERAGE)
|
|
|
924 |
? Rule::AUTOFILTER_COLUMN_RULE_GREATERTHAN
|
|
|
925 |
: Rule::AUTOFILTER_COLUMN_RULE_LESSTHAN;
|
|
|
926 |
$ruleValues[] = [
|
|
|
927 |
'operator' => $operator,
|
|
|
928 |
'value' => $average,
|
|
|
929 |
];
|
|
|
930 |
$columnFilterTests[$columnID] = [
|
|
|
931 |
'method' => 'filterTestInCustomDataSet',
|
|
|
932 |
'arguments' => ['filterRules' => $ruleValues, 'join' => AutoFilter\Column::AUTOFILTER_COLUMN_JOIN_OR],
|
|
|
933 |
];
|
|
|
934 |
} else {
|
|
|
935 |
// Date based
|
|
|
936 |
if ($dynamicRuleType[0] == 'M' || $dynamicRuleType[0] == 'Q') {
|
|
|
937 |
$periodType = '';
|
|
|
938 |
$period = 0;
|
|
|
939 |
// Month or Quarter
|
|
|
940 |
sscanf($dynamicRuleType, '%[A-Z]%d', $periodType, $period);
|
|
|
941 |
if ($periodType == 'M') {
|
|
|
942 |
$ruleValues = [$period];
|
|
|
943 |
} else {
|
|
|
944 |
--$period;
|
|
|
945 |
$periodEnd = (1 + $period) * 3;
|
|
|
946 |
$periodStart = 1 + $period * 3;
|
|
|
947 |
$ruleValues = range($periodStart, $periodEnd);
|
|
|
948 |
}
|
|
|
949 |
$columnFilterTests[$columnID] = [
|
|
|
950 |
'method' => 'filterTestInPeriodDateSet',
|
|
|
951 |
'arguments' => $ruleValues,
|
|
|
952 |
];
|
|
|
953 |
$filterColumn->setAttributes([]);
|
|
|
954 |
} else {
|
|
|
955 |
// Date Range
|
|
|
956 |
$columnFilterTests[$columnID] = $this->dynamicFilterDateRange($dynamicRuleType, $filterColumn);
|
|
|
957 |
|
|
|
958 |
break;
|
|
|
959 |
}
|
|
|
960 |
}
|
|
|
961 |
}
|
|
|
962 |
|
|
|
963 |
break;
|
|
|
964 |
case AutoFilter\Column::AUTOFILTER_FILTERTYPE_TOPTENFILTER:
|
|
|
965 |
$ruleValues = [];
|
|
|
966 |
$dataRowCount = $rangeEnd[1] - $rangeStart[1];
|
|
|
967 |
$toptenRuleType = null;
|
|
|
968 |
$ruleValue = 0;
|
|
|
969 |
$ruleOperator = null;
|
|
|
970 |
foreach ($rules as $rule) {
|
|
|
971 |
// We should only ever have one Dynamic Filter Rule anyway
|
|
|
972 |
$toptenRuleType = $rule->getGrouping();
|
|
|
973 |
$ruleValue = $rule->getValue();
|
|
|
974 |
$ruleOperator = $rule->getOperator();
|
|
|
975 |
}
|
|
|
976 |
if (is_numeric($ruleValue) && $ruleOperator === Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_PERCENT) {
|
|
|
977 |
$ruleValue = (int) floor((float) $ruleValue * ($dataRowCount / 100));
|
|
|
978 |
}
|
|
|
979 |
if (!is_array($ruleValue) && $ruleValue < 1) {
|
|
|
980 |
$ruleValue = 1;
|
|
|
981 |
}
|
|
|
982 |
if (!is_array($ruleValue) && $ruleValue > 500) {
|
|
|
983 |
$ruleValue = 500;
|
|
|
984 |
}
|
|
|
985 |
|
|
|
986 |
/** @var float|int|string */
|
|
|
987 |
$maxVal = $this->calculateTopTenValue($columnID, $rangeStart[1] + 1, (int) $rangeEnd[1], $toptenRuleType, $ruleValue);
|
|
|
988 |
|
|
|
989 |
$operator = ($toptenRuleType == Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_TOP)
|
|
|
990 |
? Rule::AUTOFILTER_COLUMN_RULE_GREATERTHANOREQUAL
|
|
|
991 |
: Rule::AUTOFILTER_COLUMN_RULE_LESSTHANOREQUAL;
|
|
|
992 |
$ruleValues[] = ['operator' => $operator, 'value' => $maxVal];
|
|
|
993 |
$columnFilterTests[$columnID] = [
|
|
|
994 |
'method' => 'filterTestInCustomDataSet',
|
|
|
995 |
'arguments' => ['filterRules' => $ruleValues, 'join' => AutoFilter\Column::AUTOFILTER_COLUMN_JOIN_OR],
|
|
|
996 |
];
|
|
|
997 |
$filterColumn->setAttributes(['maxVal' => $maxVal]);
|
|
|
998 |
|
|
|
999 |
break;
|
|
|
1000 |
}
|
|
|
1001 |
}
|
|
|
1002 |
|
|
|
1003 |
$rangeEnd[1] = $this->autoExtendRange($rangeStart[1], $rangeEnd[1]);
|
|
|
1004 |
|
|
|
1005 |
// Execute the column tests for each row in the autoFilter range to determine show/hide,
|
|
|
1006 |
for ($row = $rangeStart[1] + 1; $row <= $rangeEnd[1]; ++$row) {
|
|
|
1007 |
$result = true;
|
|
|
1008 |
foreach ($columnFilterTests as $columnID => $columnFilterTest) {
|
|
|
1009 |
$cellValue = $this->workSheet->getCell($columnID . $row)->getCalculatedValue();
|
|
|
1010 |
// Execute the filter test
|
|
|
1011 |
/** @var callable */
|
|
|
1012 |
$temp = [self::class, $columnFilterTest['method']];
|
|
|
1013 |
/** @var bool */
|
|
|
1014 |
$result // $result && // phpstan says $result is always true here
|
|
|
1015 |
= call_user_func_array($temp, [$cellValue, $columnFilterTest['arguments']]);
|
|
|
1016 |
// If filter test has resulted in FALSE, exit the loop straightaway rather than running any more tests
|
|
|
1017 |
if (!$result) {
|
|
|
1018 |
break;
|
|
|
1019 |
}
|
|
|
1020 |
}
|
|
|
1021 |
// Set show/hide for the row based on the result of the autoFilter result
|
|
|
1022 |
// If the RowDimension object has not been allocated yet and the row should be visible,
|
|
|
1023 |
// then we can avoid any operation since the rows are visible by default (saves a lot of memory)
|
|
|
1024 |
if ($result === false || $this->workSheet->rowDimensionExists((int) $row)) {
|
|
|
1025 |
$this->workSheet->getRowDimension((int) $row)->setVisible($result);
|
|
|
1026 |
}
|
|
|
1027 |
}
|
|
|
1028 |
$this->evaluated = true;
|
|
|
1029 |
|
|
|
1030 |
return $this;
|
|
|
1031 |
}
|
|
|
1032 |
|
|
|
1033 |
/**
|
|
|
1034 |
* Magic Range Auto-sizing.
|
|
|
1035 |
* For a single row rangeSet, we follow MS Excel rules, and search for the first empty row to determine our range.
|
|
|
1036 |
*/
|
|
|
1037 |
public function autoExtendRange(int $startRow, int $endRow): int
|
|
|
1038 |
{
|
|
|
1039 |
if ($startRow === $endRow && $this->workSheet !== null) {
|
|
|
1040 |
try {
|
|
|
1041 |
$rowIterator = $this->workSheet->getRowIterator($startRow + 1);
|
|
|
1042 |
} catch (Exception) {
|
|
|
1043 |
// If there are no rows below $startRow
|
|
|
1044 |
return $startRow;
|
|
|
1045 |
}
|
|
|
1046 |
foreach ($rowIterator as $row) {
|
|
|
1047 |
if ($row->isEmpty(CellIterator::TREAT_NULL_VALUE_AS_EMPTY_CELL | CellIterator::TREAT_EMPTY_STRING_AS_EMPTY_CELL) === true) {
|
|
|
1048 |
return $row->getRowIndex() - 1;
|
|
|
1049 |
}
|
|
|
1050 |
}
|
|
|
1051 |
}
|
|
|
1052 |
|
|
|
1053 |
return $endRow;
|
|
|
1054 |
}
|
|
|
1055 |
|
|
|
1056 |
/**
|
|
|
1057 |
* Implement PHP __clone to create a deep clone, not just a shallow copy.
|
|
|
1058 |
*/
|
|
|
1059 |
public function __clone()
|
|
|
1060 |
{
|
|
|
1061 |
$vars = get_object_vars($this);
|
|
|
1062 |
foreach ($vars as $key => $value) {
|
|
|
1063 |
if (is_object($value)) {
|
|
|
1064 |
if ($key === 'workSheet') {
|
|
|
1065 |
// Detach from worksheet
|
|
|
1066 |
$this->{$key} = null;
|
|
|
1067 |
} else {
|
|
|
1068 |
$this->{$key} = clone $value;
|
|
|
1069 |
}
|
|
|
1070 |
} elseif ((is_array($value)) && ($key == 'columns')) {
|
|
|
1071 |
// The columns array of \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet\AutoFilter objects
|
|
|
1072 |
$this->{$key} = [];
|
|
|
1073 |
foreach ($value as $k => $v) {
|
|
|
1074 |
$this->{$key}[$k] = clone $v;
|
|
|
1075 |
// attach the new cloned Column to this new cloned Autofilter object
|
|
|
1076 |
$this->{$key}[$k]->setParent($this);
|
|
|
1077 |
}
|
|
|
1078 |
} else {
|
|
|
1079 |
$this->{$key} = $value;
|
|
|
1080 |
}
|
|
|
1081 |
}
|
|
|
1082 |
}
|
|
|
1083 |
|
|
|
1084 |
/**
|
|
|
1085 |
* toString method replicates previous behavior by returning the range if object is
|
|
|
1086 |
* referenced as a property of its parent.
|
|
|
1087 |
*/
|
|
|
1088 |
public function __toString(): string
|
|
|
1089 |
{
|
|
|
1090 |
return (string) $this->range;
|
|
|
1091 |
}
|
|
|
1092 |
}
|