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