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