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