1<?php 2 3namespace PhpOffice\PhpSpreadsheet\Worksheet; 4 5use PhpOffice\PhpSpreadsheet\Calculation\Calculation; 6use PhpOffice\PhpSpreadsheet\Calculation\DateTimeExcel; 7use PhpOffice\PhpSpreadsheet\Calculation\Functions; 8use PhpOffice\PhpSpreadsheet\Cell\Coordinate; 9use PhpOffice\PhpSpreadsheet\Exception as PhpSpreadsheetException; 10use PhpOffice\PhpSpreadsheet\Shared\Date; 11 12class AutoFilter 13{ 14 /** 15 * Autofilter Worksheet. 16 * 17 * @var null|Worksheet 18 */ 19 private $workSheet; 20 21 /** 22 * Autofilter Range. 23 * 24 * @var string 25 */ 26 private $range = ''; 27 28 /** 29 * Autofilter Column Ruleset. 30 * 31 * @var AutoFilter\Column[] 32 */ 33 private $columns = []; 34 35 /** 36 * Create a new AutoFilter. 37 * 38 * @param string $pRange Cell range (i.e. A1:E10) 39 * @param Worksheet $pSheet 40 */ 41 public function __construct($pRange = '', ?Worksheet $pSheet = null) 42 { 43 $this->range = $pRange; 44 $this->workSheet = $pSheet; 45 } 46 47 /** 48 * Get AutoFilter Parent Worksheet. 49 * 50 * @return null|Worksheet 51 */ 52 public function getParent() 53 { 54 return $this->workSheet; 55 } 56 57 /** 58 * Set AutoFilter Parent Worksheet. 59 * 60 * @param Worksheet $pSheet 61 * 62 * @return $this 63 */ 64 public function setParent(?Worksheet $pSheet = null) 65 { 66 $this->workSheet = $pSheet; 67 68 return $this; 69 } 70 71 /** 72 * Get AutoFilter Range. 73 * 74 * @return string 75 */ 76 public function getRange() 77 { 78 return $this->range; 79 } 80 81 /** 82 * Set AutoFilter Range. 83 * 84 * @param string $pRange Cell range (i.e. A1:E10) 85 * 86 * @return $this 87 */ 88 public function setRange($pRange) 89 { 90 // extract coordinate 91 [$worksheet, $pRange] = Worksheet::extractSheetTitle($pRange, true); 92 93 if (strpos($pRange, ':') !== false) { 94 $this->range = $pRange; 95 } elseif (empty($pRange)) { 96 $this->range = ''; 97 } else { 98 throw new PhpSpreadsheetException('Autofilter must be set on a range of cells.'); 99 } 100 101 if (empty($pRange)) { 102 // Discard all column rules 103 $this->columns = []; 104 } else { 105 // Discard any column rules that are no longer valid within this range 106 [$rangeStart, $rangeEnd] = Coordinate::rangeBoundaries($this->range); 107 foreach ($this->columns as $key => $value) { 108 $colIndex = Coordinate::columnIndexFromString($key); 109 if (($rangeStart[0] > $colIndex) || ($rangeEnd[0] < $colIndex)) { 110 unset($this->columns[$key]); 111 } 112 } 113 } 114 115 return $this; 116 } 117 118 /** 119 * Get all AutoFilter Columns. 120 * 121 * @return AutoFilter\Column[] 122 */ 123 public function getColumns() 124 { 125 return $this->columns; 126 } 127 128 /** 129 * Validate that the specified column is in the AutoFilter range. 130 * 131 * @param string $column Column name (e.g. A) 132 * 133 * @return int The column offset within the autofilter range 134 */ 135 public function testColumnInRange($column) 136 { 137 if (empty($this->range)) { 138 throw new PhpSpreadsheetException('No autofilter range is defined.'); 139 } 140 141 $columnIndex = Coordinate::columnIndexFromString($column); 142 [$rangeStart, $rangeEnd] = Coordinate::rangeBoundaries($this->range); 143 if (($rangeStart[0] > $columnIndex) || ($rangeEnd[0] < $columnIndex)) { 144 throw new PhpSpreadsheetException('Column is outside of current autofilter range.'); 145 } 146 147 return $columnIndex - $rangeStart[0]; 148 } 149 150 /** 151 * Get a specified AutoFilter Column Offset within the defined AutoFilter range. 152 * 153 * @param string $pColumn Column name (e.g. A) 154 * 155 * @return int The offset of the specified column within the autofilter range 156 */ 157 public function getColumnOffset($pColumn) 158 { 159 return $this->testColumnInRange($pColumn); 160 } 161 162 /** 163 * Get a specified AutoFilter Column. 164 * 165 * @param string $pColumn Column name (e.g. A) 166 * 167 * @return AutoFilter\Column 168 */ 169 public function getColumn($pColumn) 170 { 171 $this->testColumnInRange($pColumn); 172 173 if (!isset($this->columns[$pColumn])) { 174 $this->columns[$pColumn] = new AutoFilter\Column($pColumn, $this); 175 } 176 177 return $this->columns[$pColumn]; 178 } 179 180 /** 181 * Get a specified AutoFilter Column by it's offset. 182 * 183 * @param int $pColumnOffset Column offset within range (starting from 0) 184 * 185 * @return AutoFilter\Column 186 */ 187 public function getColumnByOffset($pColumnOffset) 188 { 189 [$rangeStart, $rangeEnd] = Coordinate::rangeBoundaries($this->range); 190 $pColumn = Coordinate::stringFromColumnIndex($rangeStart[0] + $pColumnOffset); 191 192 return $this->getColumn($pColumn); 193 } 194 195 /** 196 * Set AutoFilter. 197 * 198 * @param AutoFilter\Column|string $pColumn 199 * A simple string containing a Column ID like 'A' is permitted 200 * 201 * @return $this 202 */ 203 public function setColumn($pColumn) 204 { 205 if ((is_string($pColumn)) && (!empty($pColumn))) { 206 $column = $pColumn; 207 } elseif (is_object($pColumn) && ($pColumn instanceof AutoFilter\Column)) { 208 $column = $pColumn->getColumnIndex(); 209 } else { 210 throw new PhpSpreadsheetException('Column is not within the autofilter range.'); 211 } 212 $this->testColumnInRange($column); 213 214 if (is_string($pColumn)) { 215 $this->columns[$pColumn] = new AutoFilter\Column($pColumn, $this); 216 } elseif (is_object($pColumn) && ($pColumn instanceof AutoFilter\Column)) { 217 $pColumn->setParent($this); 218 $this->columns[$column] = $pColumn; 219 } 220 ksort($this->columns); 221 222 return $this; 223 } 224 225 /** 226 * Clear a specified AutoFilter Column. 227 * 228 * @param string $pColumn Column name (e.g. A) 229 * 230 * @return $this 231 */ 232 public function clearColumn($pColumn) 233 { 234 $this->testColumnInRange($pColumn); 235 236 if (isset($this->columns[$pColumn])) { 237 unset($this->columns[$pColumn]); 238 } 239 240 return $this; 241 } 242 243 /** 244 * Shift an AutoFilter Column Rule to a different column. 245 * 246 * Note: This method bypasses validation of the destination column to ensure it is within this AutoFilter range. 247 * Nor does it verify whether any column rule already exists at $toColumn, but will simply override any existing value. 248 * Use with caution. 249 * 250 * @param string $fromColumn Column name (e.g. A) 251 * @param string $toColumn Column name (e.g. B) 252 * 253 * @return $this 254 */ 255 public function shiftColumn($fromColumn, $toColumn) 256 { 257 $fromColumn = strtoupper($fromColumn); 258 $toColumn = strtoupper($toColumn); 259 260 if (($fromColumn !== null) && (isset($this->columns[$fromColumn])) && ($toColumn !== null)) { 261 $this->columns[$fromColumn]->setParent(); 262 $this->columns[$fromColumn]->setColumnIndex($toColumn); 263 $this->columns[$toColumn] = $this->columns[$fromColumn]; 264 $this->columns[$toColumn]->setParent($this); 265 unset($this->columns[$fromColumn]); 266 267 ksort($this->columns); 268 } 269 270 return $this; 271 } 272 273 /** 274 * Test if cell value is in the defined set of values. 275 * 276 * @param mixed $cellValue 277 * @param mixed[] $dataSet 278 * 279 * @return bool 280 */ 281 private static function filterTestInSimpleDataSet($cellValue, $dataSet) 282 { 283 $dataSetValues = $dataSet['filterValues']; 284 $blanks = $dataSet['blanks']; 285 if (($cellValue == '') || ($cellValue === null)) { 286 return $blanks; 287 } 288 289 return in_array($cellValue, $dataSetValues); 290 } 291 292 /** 293 * Test if cell value is in the defined set of Excel date values. 294 * 295 * @param mixed $cellValue 296 * @param mixed[] $dataSet 297 * 298 * @return bool 299 */ 300 private static function filterTestInDateGroupSet($cellValue, $dataSet) 301 { 302 $dateSet = $dataSet['filterValues']; 303 $blanks = $dataSet['blanks']; 304 if (($cellValue == '') || ($cellValue === null)) { 305 return $blanks; 306 } 307 308 if (is_numeric($cellValue)) { 309 $dateValue = Date::excelToTimestamp($cellValue); 310 if ($cellValue < 1) { 311 // Just the time part 312 $dtVal = date('His', $dateValue); 313 $dateSet = $dateSet['time']; 314 } elseif ($cellValue == floor($cellValue)) { 315 // Just the date part 316 $dtVal = date('Ymd', $dateValue); 317 $dateSet = $dateSet['date']; 318 } else { 319 // date and time parts 320 $dtVal = date('YmdHis', $dateValue); 321 $dateSet = $dateSet['dateTime']; 322 } 323 foreach ($dateSet as $dateValue) { 324 // Use of substr to extract value at the appropriate group level 325 if (substr($dtVal, 0, strlen($dateValue)) == $dateValue) { 326 return true; 327 } 328 } 329 } 330 331 return false; 332 } 333 334 /** 335 * Test if cell value is within a set of values defined by a ruleset. 336 * 337 * @param mixed $cellValue 338 * @param mixed[] $ruleSet 339 * 340 * @return bool 341 */ 342 private static function filterTestInCustomDataSet($cellValue, $ruleSet) 343 { 344 $dataSet = $ruleSet['filterRules']; 345 $join = $ruleSet['join']; 346 $customRuleForBlanks = $ruleSet['customRuleForBlanks'] ?? false; 347 348 if (!$customRuleForBlanks) { 349 // Blank cells are always ignored, so return a FALSE 350 if (($cellValue == '') || ($cellValue === null)) { 351 return false; 352 } 353 } 354 $returnVal = ($join == AutoFilter\Column::AUTOFILTER_COLUMN_JOIN_AND); 355 foreach ($dataSet as $rule) { 356 $retVal = false; 357 358 if (is_numeric($rule['value'])) { 359 // Numeric values are tested using the appropriate operator 360 switch ($rule['operator']) { 361 case AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_EQUAL: 362 $retVal = ($cellValue == $rule['value']); 363 364 break; 365 case AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_NOTEQUAL: 366 $retVal = ($cellValue != $rule['value']); 367 368 break; 369 case AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_GREATERTHAN: 370 $retVal = ($cellValue > $rule['value']); 371 372 break; 373 case AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_GREATERTHANOREQUAL: 374 $retVal = ($cellValue >= $rule['value']); 375 376 break; 377 case AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_LESSTHAN: 378 $retVal = ($cellValue < $rule['value']); 379 380 break; 381 case AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_LESSTHANOREQUAL: 382 $retVal = ($cellValue <= $rule['value']); 383 384 break; 385 } 386 } elseif ($rule['value'] == '') { 387 switch ($rule['operator']) { 388 case AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_EQUAL: 389 $retVal = (($cellValue == '') || ($cellValue === null)); 390 391 break; 392 case AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_NOTEQUAL: 393 $retVal = (($cellValue != '') && ($cellValue !== null)); 394 395 break; 396 default: 397 $retVal = true; 398 399 break; 400 } 401 } else { 402 // String values are always tested for equality, factoring in for wildcards (hence a regexp test) 403 $retVal = preg_match('/^' . $rule['value'] . '$/i', $cellValue); 404 } 405 // If there are multiple conditions, then we need to test both using the appropriate join operator 406 switch ($join) { 407 case AutoFilter\Column::AUTOFILTER_COLUMN_JOIN_OR: 408 $returnVal = $returnVal || $retVal; 409 // Break as soon as we have a TRUE match for OR joins, 410 // to avoid unnecessary additional code execution 411 if ($returnVal) { 412 return $returnVal; 413 } 414 415 break; 416 case AutoFilter\Column::AUTOFILTER_COLUMN_JOIN_AND: 417 $returnVal = $returnVal && $retVal; 418 419 break; 420 } 421 } 422 423 return $returnVal; 424 } 425 426 /** 427 * Test if cell date value is matches a set of values defined by a set of months. 428 * 429 * @param mixed $cellValue 430 * @param mixed[] $monthSet 431 * 432 * @return bool 433 */ 434 private static function filterTestInPeriodDateSet($cellValue, $monthSet) 435 { 436 // Blank cells are always ignored, so return a FALSE 437 if (($cellValue == '') || ($cellValue === null)) { 438 return false; 439 } 440 441 if (is_numeric($cellValue)) { 442 $dateValue = date('m', Date::excelToTimestamp($cellValue)); 443 if (in_array($dateValue, $monthSet)) { 444 return true; 445 } 446 } 447 448 return false; 449 } 450 451 /** 452 * Search/Replace arrays to convert Excel wildcard syntax to a regexp syntax for preg_matching. 453 * 454 * @var array 455 */ 456 private static $fromReplace = ['\*', '\?', '~~', '~.*', '~.?']; 457 458 private static $toReplace = ['.*', '.', '~', '\*', '\?']; 459 460 /** 461 * Convert a dynamic rule daterange to a custom filter range expression for ease of calculation. 462 * 463 * @param string $dynamicRuleType 464 * @param AutoFilter\Column $filterColumn 465 * 466 * @return mixed[] 467 */ 468 private function dynamicFilterDateRange($dynamicRuleType, &$filterColumn) 469 { 470 $rDateType = Functions::getReturnDateType(); 471 Functions::setReturnDateType(Functions::RETURNDATE_PHP_NUMERIC); 472 $val = $maxVal = null; 473 474 $ruleValues = []; 475 $baseDate = DateTimeExcel\Current::now(); 476 // Calculate start/end dates for the required date range based on current date 477 switch ($dynamicRuleType) { 478 case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTWEEK: 479 $baseDate = strtotime('-7 days', $baseDate); 480 481 break; 482 case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTWEEK: 483 $baseDate = strtotime('-7 days', $baseDate); 484 485 break; 486 case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTMONTH: 487 $baseDate = strtotime('-1 month', gmmktime(0, 0, 0, 1, date('m', $baseDate), date('Y', $baseDate))); 488 489 break; 490 case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTMONTH: 491 $baseDate = strtotime('+1 month', gmmktime(0, 0, 0, 1, date('m', $baseDate), date('Y', $baseDate))); 492 493 break; 494 case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTQUARTER: 495 $baseDate = strtotime('-3 month', gmmktime(0, 0, 0, 1, date('m', $baseDate), date('Y', $baseDate))); 496 497 break; 498 case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTQUARTER: 499 $baseDate = strtotime('+3 month', gmmktime(0, 0, 0, 1, date('m', $baseDate), date('Y', $baseDate))); 500 501 break; 502 case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTYEAR: 503 $baseDate = strtotime('-1 year', gmmktime(0, 0, 0, 1, date('m', $baseDate), date('Y', $baseDate))); 504 505 break; 506 case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTYEAR: 507 $baseDate = strtotime('+1 year', gmmktime(0, 0, 0, 1, date('m', $baseDate), date('Y', $baseDate))); 508 509 break; 510 } 511 512 switch ($dynamicRuleType) { 513 case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_TODAY: 514 case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_YESTERDAY: 515 case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_TOMORROW: 516 $maxVal = (int) Date::PHPtoExcel(strtotime('+1 day', $baseDate)); 517 $val = (int) Date::PHPToExcel($baseDate); 518 519 break; 520 case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_YEARTODATE: 521 $maxVal = (int) Date::PHPtoExcel(strtotime('+1 day', $baseDate)); 522 $val = (int) Date::PHPToExcel(gmmktime(0, 0, 0, 1, 1, date('Y', $baseDate))); 523 524 break; 525 case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_THISYEAR: 526 case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTYEAR: 527 case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTYEAR: 528 $maxVal = (int) Date::PHPToExcel(gmmktime(0, 0, 0, 31, 12, date('Y', $baseDate))); 529 ++$maxVal; 530 $val = (int) Date::PHPToExcel(gmmktime(0, 0, 0, 1, 1, date('Y', $baseDate))); 531 532 break; 533 case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_THISQUARTER: 534 case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTQUARTER: 535 case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTQUARTER: 536 $thisMonth = date('m', $baseDate); 537 $thisQuarter = floor(--$thisMonth / 3); 538 $maxVal = (int) Date::PHPtoExcel(gmmktime(0, 0, 0, date('t', $baseDate), (1 + $thisQuarter) * 3, date('Y', $baseDate))); 539 ++$maxVal; 540 $val = (int) Date::PHPToExcel(gmmktime(0, 0, 0, 1, 1 + $thisQuarter * 3, date('Y', $baseDate))); 541 542 break; 543 case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_THISMONTH: 544 case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTMONTH: 545 case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTMONTH: 546 $maxVal = (int) Date::PHPtoExcel(gmmktime(0, 0, 0, date('t', $baseDate), date('m', $baseDate), date('Y', $baseDate))); 547 ++$maxVal; 548 $val = (int) Date::PHPToExcel(gmmktime(0, 0, 0, 1, date('m', $baseDate), date('Y', $baseDate))); 549 550 break; 551 case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_THISWEEK: 552 case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTWEEK: 553 case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTWEEK: 554 $dayOfWeek = date('w', $baseDate); 555 $val = (int) Date::PHPToExcel($baseDate) - $dayOfWeek; 556 $maxVal = $val + 7; 557 558 break; 559 } 560 561 switch ($dynamicRuleType) { 562 // Adjust Today dates for Yesterday and Tomorrow 563 case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_YESTERDAY: 564 --$maxVal; 565 --$val; 566 567 break; 568 case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_TOMORROW: 569 ++$maxVal; 570 ++$val; 571 572 break; 573 } 574 575 // Set the filter column rule attributes ready for writing 576 $filterColumn->setAttributes(['val' => $val, 'maxVal' => $maxVal]); 577 578 // Set the rules for identifying rows for hide/show 579 $ruleValues[] = ['operator' => AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_GREATERTHANOREQUAL, 'value' => $val]; 580 $ruleValues[] = ['operator' => AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_LESSTHAN, 'value' => $maxVal]; 581 Functions::setReturnDateType($rDateType); 582 583 return ['method' => 'filterTestInCustomDataSet', 'arguments' => ['filterRules' => $ruleValues, 'join' => AutoFilter\Column::AUTOFILTER_COLUMN_JOIN_AND]]; 584 } 585 586 private function calculateTopTenValue($columnID, $startRow, $endRow, $ruleType, $ruleValue) 587 { 588 $range = $columnID . $startRow . ':' . $columnID . $endRow; 589 $dataValues = Functions::flattenArray($this->workSheet->rangeToArray($range, null, true, false)); 590 591 $dataValues = array_filter($dataValues); 592 if ($ruleType == AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_TOP) { 593 rsort($dataValues); 594 } else { 595 sort($dataValues); 596 } 597 598 $slice = array_slice($dataValues, 0, $ruleValue); 599 600 return array_pop($slice); 601 } 602 603 /** 604 * Apply the AutoFilter rules to the AutoFilter Range. 605 * 606 * @return $this 607 */ 608 public function showHideRows() 609 { 610 [$rangeStart, $rangeEnd] = Coordinate::rangeBoundaries($this->range); 611 612 // The heading row should always be visible 613 $this->workSheet->getRowDimension($rangeStart[1])->setVisible(true); 614 615 $columnFilterTests = []; 616 foreach ($this->columns as $columnID => $filterColumn) { 617 $rules = $filterColumn->getRules(); 618 switch ($filterColumn->getFilterType()) { 619 case AutoFilter\Column::AUTOFILTER_FILTERTYPE_FILTER: 620 $ruleType = null; 621 $ruleValues = []; 622 // Build a list of the filter value selections 623 foreach ($rules as $rule) { 624 $ruleType = $rule->getRuleType(); 625 $ruleValues[] = $rule->getValue(); 626 } 627 // Test if we want to include blanks in our filter criteria 628 $blanks = false; 629 $ruleDataSet = array_filter($ruleValues); 630 if (count($ruleValues) != count($ruleDataSet)) { 631 $blanks = true; 632 } 633 if ($ruleType == AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_FILTER) { 634 // Filter on absolute values 635 $columnFilterTests[$columnID] = [ 636 'method' => 'filterTestInSimpleDataSet', 637 'arguments' => ['filterValues' => $ruleDataSet, 'blanks' => $blanks], 638 ]; 639 } else { 640 // Filter on date group values 641 $arguments = [ 642 'date' => [], 643 'time' => [], 644 'dateTime' => [], 645 ]; 646 foreach ($ruleDataSet as $ruleValue) { 647 $date = $time = ''; 648 if ( 649 (isset($ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_YEAR])) && 650 ($ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_YEAR] !== '') 651 ) { 652 $date .= sprintf('%04d', $ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_YEAR]); 653 } 654 if ( 655 (isset($ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_MONTH])) && 656 ($ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_MONTH] != '') 657 ) { 658 $date .= sprintf('%02d', $ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_MONTH]); 659 } 660 if ( 661 (isset($ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_DAY])) && 662 ($ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_DAY] !== '') 663 ) { 664 $date .= sprintf('%02d', $ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_DAY]); 665 } 666 if ( 667 (isset($ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_HOUR])) && 668 ($ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_HOUR] !== '') 669 ) { 670 $time .= sprintf('%02d', $ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_HOUR]); 671 } 672 if ( 673 (isset($ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_MINUTE])) && 674 ($ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_MINUTE] !== '') 675 ) { 676 $time .= sprintf('%02d', $ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_MINUTE]); 677 } 678 if ( 679 (isset($ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_SECOND])) && 680 ($ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_SECOND] !== '') 681 ) { 682 $time .= sprintf('%02d', $ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_SECOND]); 683 } 684 $dateTime = $date . $time; 685 $arguments['date'][] = $date; 686 $arguments['time'][] = $time; 687 $arguments['dateTime'][] = $dateTime; 688 } 689 // Remove empty elements 690 $arguments['date'] = array_filter($arguments['date']); 691 $arguments['time'] = array_filter($arguments['time']); 692 $arguments['dateTime'] = array_filter($arguments['dateTime']); 693 $columnFilterTests[$columnID] = [ 694 'method' => 'filterTestInDateGroupSet', 695 'arguments' => ['filterValues' => $arguments, 'blanks' => $blanks], 696 ]; 697 } 698 699 break; 700 case AutoFilter\Column::AUTOFILTER_FILTERTYPE_CUSTOMFILTER: 701 $customRuleForBlanks = false; 702 $ruleValues = []; 703 // Build a list of the filter value selections 704 foreach ($rules as $rule) { 705 $ruleValue = $rule->getValue(); 706 if (!is_numeric($ruleValue)) { 707 // Convert to a regexp allowing for regexp reserved characters, wildcards and escaped wildcards 708 $ruleValue = preg_quote($ruleValue); 709 $ruleValue = str_replace(self::$fromReplace, self::$toReplace, $ruleValue); 710 if (trim($ruleValue) == '') { 711 $customRuleForBlanks = true; 712 $ruleValue = trim($ruleValue); 713 } 714 } 715 $ruleValues[] = ['operator' => $rule->getOperator(), 'value' => $ruleValue]; 716 } 717 $join = $filterColumn->getJoin(); 718 $columnFilterTests[$columnID] = [ 719 'method' => 'filterTestInCustomDataSet', 720 'arguments' => ['filterRules' => $ruleValues, 'join' => $join, 'customRuleForBlanks' => $customRuleForBlanks], 721 ]; 722 723 break; 724 case AutoFilter\Column::AUTOFILTER_FILTERTYPE_DYNAMICFILTER: 725 $ruleValues = []; 726 foreach ($rules as $rule) { 727 // We should only ever have one Dynamic Filter Rule anyway 728 $dynamicRuleType = $rule->getGrouping(); 729 if ( 730 ($dynamicRuleType == AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_ABOVEAVERAGE) || 731 ($dynamicRuleType == AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_BELOWAVERAGE) 732 ) { 733 // Number (Average) based 734 // Calculate the average 735 $averageFormula = '=AVERAGE(' . $columnID . ($rangeStart[1] + 1) . ':' . $columnID . $rangeEnd[1] . ')'; 736 $average = Calculation::getInstance()->calculateFormula($averageFormula, null, $this->workSheet->getCell('A1')); 737 // Set above/below rule based on greaterThan or LessTan 738 $operator = ($dynamicRuleType === AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_ABOVEAVERAGE) 739 ? AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_GREATERTHAN 740 : AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_LESSTHAN; 741 $ruleValues[] = [ 742 'operator' => $operator, 743 'value' => $average, 744 ]; 745 $columnFilterTests[$columnID] = [ 746 'method' => 'filterTestInCustomDataSet', 747 'arguments' => ['filterRules' => $ruleValues, 'join' => AutoFilter\Column::AUTOFILTER_COLUMN_JOIN_OR], 748 ]; 749 } else { 750 // Date based 751 if ($dynamicRuleType[0] == 'M' || $dynamicRuleType[0] == 'Q') { 752 $periodType = ''; 753 $period = 0; 754 // Month or Quarter 755 sscanf($dynamicRuleType, '%[A-Z]%d', $periodType, $period); 756 if ($periodType == 'M') { 757 $ruleValues = [$period]; 758 } else { 759 --$period; 760 $periodEnd = (1 + $period) * 3; 761 $periodStart = 1 + $period * 3; 762 $ruleValues = range($periodStart, $periodEnd); 763 } 764 $columnFilterTests[$columnID] = [ 765 'method' => 'filterTestInPeriodDateSet', 766 'arguments' => $ruleValues, 767 ]; 768 $filterColumn->setAttributes([]); 769 } else { 770 // Date Range 771 $columnFilterTests[$columnID] = $this->dynamicFilterDateRange($dynamicRuleType, $filterColumn); 772 773 break; 774 } 775 } 776 } 777 778 break; 779 case AutoFilter\Column::AUTOFILTER_FILTERTYPE_TOPTENFILTER: 780 $ruleValues = []; 781 $dataRowCount = $rangeEnd[1] - $rangeStart[1]; 782 $toptenRuleType = null; 783 $ruleValue = 0; 784 $ruleOperator = null; 785 foreach ($rules as $rule) { 786 // We should only ever have one Dynamic Filter Rule anyway 787 $toptenRuleType = $rule->getGrouping(); 788 $ruleValue = $rule->getValue(); 789 $ruleOperator = $rule->getOperator(); 790 } 791 if ($ruleOperator === AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_PERCENT) { 792 $ruleValue = floor($ruleValue * ($dataRowCount / 100)); 793 } 794 if (!is_array($ruleValue) && $ruleValue < 1) { 795 $ruleValue = 1; 796 } 797 if (!is_array($ruleValue) && $ruleValue > 500) { 798 $ruleValue = 500; 799 } 800 801 $maxVal = $this->calculateTopTenValue($columnID, $rangeStart[1] + 1, $rangeEnd[1], $toptenRuleType, $ruleValue); 802 803 $operator = ($toptenRuleType == AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_TOP) 804 ? AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_GREATERTHANOREQUAL 805 : AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_LESSTHANOREQUAL; 806 $ruleValues[] = ['operator' => $operator, 'value' => $maxVal]; 807 $columnFilterTests[$columnID] = [ 808 'method' => 'filterTestInCustomDataSet', 809 'arguments' => ['filterRules' => $ruleValues, 'join' => AutoFilter\Column::AUTOFILTER_COLUMN_JOIN_OR], 810 ]; 811 $filterColumn->setAttributes(['maxVal' => $maxVal]); 812 813 break; 814 } 815 } 816 817 // Execute the column tests for each row in the autoFilter range to determine show/hide, 818 for ($row = $rangeStart[1] + 1; $row <= $rangeEnd[1]; ++$row) { 819 $result = true; 820 foreach ($columnFilterTests as $columnID => $columnFilterTest) { 821 $cellValue = $this->workSheet->getCell($columnID . $row)->getCalculatedValue(); 822 // Execute the filter test 823 $result = $result && 824 call_user_func_array( 825 [self::class, $columnFilterTest['method']], 826 [$cellValue, $columnFilterTest['arguments']] 827 ); 828 // If filter test has resulted in FALSE, exit the loop straightaway rather than running any more tests 829 if (!$result) { 830 break; 831 } 832 } 833 // Set show/hide for the row based on the result of the autoFilter result 834 $this->workSheet->getRowDimension($row)->setVisible($result); 835 } 836 837 return $this; 838 } 839 840 /** 841 * Implement PHP __clone to create a deep clone, not just a shallow copy. 842 */ 843 public function __clone() 844 { 845 $vars = get_object_vars($this); 846 foreach ($vars as $key => $value) { 847 if (is_object($value)) { 848 if ($key === 'workSheet') { 849 // Detach from worksheet 850 $this->{$key} = null; 851 } else { 852 $this->{$key} = clone $value; 853 } 854 } elseif ((is_array($value)) && ($key == 'columns')) { 855 // The columns array of \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet\AutoFilter objects 856 $this->{$key} = []; 857 foreach ($value as $k => $v) { 858 $this->{$key}[$k] = clone $v; 859 // attach the new cloned Column to this new cloned Autofilter object 860 $this->{$key}[$k]->setParent($this); 861 } 862 } else { 863 $this->{$key} = $value; 864 } 865 } 866 } 867 868 /** 869 * toString method replicates previous behavior by returning the range if object is 870 * referenced as a property of its parent. 871 */ 872 public function __toString() 873 { 874 return (string) $this->range; 875 } 876} 877