[Summary view]

Differences Between: [Versions 310 and 311] [Versions 311 and 400] [Versions 311 and 401] [Versions 311 and 402] [Versions 311 and 403] [Versions 39 and 311]

   1  <?php
   2  
   3  namespace PhpOffice\PhpSpreadsheet\Worksheet;
   4  
   5  use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
   6  use PhpOffice\PhpSpreadsheet\Calculation\DateTime;
   7  use PhpOffice\PhpSpreadsheet\Calculation\Functions;
   8  use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
   9  use PhpOffice\PhpSpreadsheet\Exception as PhpSpreadsheetException;
  10  use PhpOffice\PhpSpreadsheet\Shared\Date;
  11  
  12  class 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 $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 = DateTime::DATENOW();
 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          return array_pop(array_slice($dataValues, 0, $ruleValue));
 599      }
 600  
 601      /**
 602       * Apply the AutoFilter rules to the AutoFilter Range.
 603       *
 604       * @return $this
 605       */
 606      public function showHideRows()
 607      {
 608          [$rangeStart, $rangeEnd] = Coordinate::rangeBoundaries($this->range);
 609  
 610          //    The heading row should always be visible
 611          $this->workSheet->getRowDimension($rangeStart[1])->setVisible(true);
 612  
 613          $columnFilterTests = [];
 614          foreach ($this->columns as $columnID => $filterColumn) {
 615              $rules = $filterColumn->getRules();
 616              switch ($filterColumn->getFilterType()) {
 617                  case AutoFilter\Column::AUTOFILTER_FILTERTYPE_FILTER:
 618                      $ruleType = null;
 619                      $ruleValues = [];
 620                      //    Build a list of the filter value selections
 621                      foreach ($rules as $rule) {
 622                          $ruleType = $rule->getRuleType();
 623                          $ruleValues[] = $rule->getValue();
 624                      }
 625                      //    Test if we want to include blanks in our filter criteria
 626                      $blanks = false;
 627                      $ruleDataSet = array_filter($ruleValues);
 628                      if (count($ruleValues) != count($ruleDataSet)) {
 629                          $blanks = true;
 630                      }
 631                      if ($ruleType == AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_FILTER) {
 632                          //    Filter on absolute values
 633                          $columnFilterTests[$columnID] = [
 634                              'method' => 'filterTestInSimpleDataSet',
 635                              'arguments' => ['filterValues' => $ruleDataSet, 'blanks' => $blanks],
 636                          ];
 637                      } else {
 638                          //    Filter on date group values
 639                          $arguments = [
 640                              'date' => [],
 641                              'time' => [],
 642                              'dateTime' => [],
 643                          ];
 644                          foreach ($ruleDataSet as $ruleValue) {
 645                              $date = $time = '';
 646                              if (
 647                                  (isset($ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_YEAR])) &&
 648                                  ($ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_YEAR] !== '')
 649                              ) {
 650                                  $date .= sprintf('%04d', $ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_YEAR]);
 651                              }
 652                              if (
 653                                  (isset($ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_MONTH])) &&
 654                                  ($ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_MONTH] != '')
 655                              ) {
 656                                  $date .= sprintf('%02d', $ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_MONTH]);
 657                              }
 658                              if (
 659                                  (isset($ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_DAY])) &&
 660                                  ($ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_DAY] !== '')
 661                              ) {
 662                                  $date .= sprintf('%02d', $ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_DAY]);
 663                              }
 664                              if (
 665                                  (isset($ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_HOUR])) &&
 666                                  ($ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_HOUR] !== '')
 667                              ) {
 668                                  $time .= sprintf('%02d', $ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_HOUR]);
 669                              }
 670                              if (
 671                                  (isset($ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_MINUTE])) &&
 672                                  ($ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_MINUTE] !== '')
 673                              ) {
 674                                  $time .= sprintf('%02d', $ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_MINUTE]);
 675                              }
 676                              if (
 677                                  (isset($ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_SECOND])) &&
 678                                  ($ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_SECOND] !== '')
 679                              ) {
 680                                  $time .= sprintf('%02d', $ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_SECOND]);
 681                              }
 682                              $dateTime = $date . $time;
 683                              $arguments['date'][] = $date;
 684                              $arguments['time'][] = $time;
 685                              $arguments['dateTime'][] = $dateTime;
 686                          }
 687                          //    Remove empty elements
 688                          $arguments['date'] = array_filter($arguments['date']);
 689                          $arguments['time'] = array_filter($arguments['time']);
 690                          $arguments['dateTime'] = array_filter($arguments['dateTime']);
 691                          $columnFilterTests[$columnID] = [
 692                              'method' => 'filterTestInDateGroupSet',
 693                              'arguments' => ['filterValues' => $arguments, 'blanks' => $blanks],
 694                          ];
 695                      }
 696  
 697                      break;
 698                  case AutoFilter\Column::AUTOFILTER_FILTERTYPE_CUSTOMFILTER:
 699                      $customRuleForBlanks = false;
 700                      $ruleValues = [];
 701                      //    Build a list of the filter value selections
 702                      foreach ($rules as $rule) {
 703                          $ruleValue = $rule->getValue();
 704                          if (!is_numeric($ruleValue)) {
 705                              //    Convert to a regexp allowing for regexp reserved characters, wildcards and escaped wildcards
 706                              $ruleValue = preg_quote($ruleValue);
 707                              $ruleValue = str_replace(self::$fromReplace, self::$toReplace, $ruleValue);
 708                              if (trim($ruleValue) == '') {
 709                                  $customRuleForBlanks = true;
 710                                  $ruleValue = trim($ruleValue);
 711                              }
 712                          }
 713                          $ruleValues[] = ['operator' => $rule->getOperator(), 'value' => $ruleValue];
 714                      }
 715                      $join = $filterColumn->getJoin();
 716                      $columnFilterTests[$columnID] = [
 717                          'method' => 'filterTestInCustomDataSet',
 718                          'arguments' => ['filterRules' => $ruleValues, 'join' => $join, 'customRuleForBlanks' => $customRuleForBlanks],
 719                      ];
 720  
 721                      break;
 722                  case AutoFilter\Column::AUTOFILTER_FILTERTYPE_DYNAMICFILTER:
 723                      $ruleValues = [];
 724                      foreach ($rules as $rule) {
 725                          //    We should only ever have one Dynamic Filter Rule anyway
 726                          $dynamicRuleType = $rule->getGrouping();
 727                          if (
 728                              ($dynamicRuleType == AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_ABOVEAVERAGE) ||
 729                              ($dynamicRuleType == AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_BELOWAVERAGE)
 730                          ) {
 731                              //    Number (Average) based
 732                              //    Calculate the average
 733                              $averageFormula = '=AVERAGE(' . $columnID . ($rangeStart[1] + 1) . ':' . $columnID . $rangeEnd[1] . ')';
 734                              $average = Calculation::getInstance()->calculateFormula($averageFormula, null, $this->workSheet->getCell('A1'));
 735                              //    Set above/below rule based on greaterThan or LessTan
 736                              $operator = ($dynamicRuleType === AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_ABOVEAVERAGE)
 737                                  ? AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_GREATERTHAN
 738                                  : AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_LESSTHAN;
 739                              $ruleValues[] = [
 740                                  'operator' => $operator,
 741                                  'value' => $average,
 742                              ];
 743                              $columnFilterTests[$columnID] = [
 744                                  'method' => 'filterTestInCustomDataSet',
 745                                  'arguments' => ['filterRules' => $ruleValues, 'join' => AutoFilter\Column::AUTOFILTER_COLUMN_JOIN_OR],
 746                              ];
 747                          } else {
 748                              //    Date based
 749                              if ($dynamicRuleType[0] == 'M' || $dynamicRuleType[0] == 'Q') {
 750                                  $periodType = '';
 751                                  $period = 0;
 752                                  //    Month or Quarter
 753                                  sscanf($dynamicRuleType, '%[A-Z]%d', $periodType, $period);
 754                                  if ($periodType == 'M') {
 755                                      $ruleValues = [$period];
 756                                  } else {
 757                                      --$period;
 758                                      $periodEnd = (1 + $period) * 3;
 759                                      $periodStart = 1 + $period * 3;
 760                                      $ruleValues = range($periodStart, $periodEnd);
 761                                  }
 762                                  $columnFilterTests[$columnID] = [
 763                                      'method' => 'filterTestInPeriodDateSet',
 764                                      'arguments' => $ruleValues,
 765                                  ];
 766                                  $filterColumn->setAttributes([]);
 767                              } else {
 768                                  //    Date Range
 769                                  $columnFilterTests[$columnID] = $this->dynamicFilterDateRange($dynamicRuleType, $filterColumn);
 770  
 771                                  break;
 772                              }
 773                          }
 774                      }
 775  
 776                      break;
 777                  case AutoFilter\Column::AUTOFILTER_FILTERTYPE_TOPTENFILTER:
 778                      $ruleValues = [];
 779                      $dataRowCount = $rangeEnd[1] - $rangeStart[1];
 780                      foreach ($rules as $rule) {
 781                          //    We should only ever have one Dynamic Filter Rule anyway
 782                          $toptenRuleType = $rule->getGrouping();
 783                          $ruleValue = $rule->getValue();
 784                          $ruleOperator = $rule->getOperator();
 785                      }
 786                      if ($ruleOperator === AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_PERCENT) {
 787                          $ruleValue = floor($ruleValue * ($dataRowCount / 100));
 788                      }
 789                      if ($ruleValue < 1) {
 790                          $ruleValue = 1;
 791                      }
 792                      if ($ruleValue > 500) {
 793                          $ruleValue = 500;
 794                      }
 795  
 796                      $maxVal = $this->calculateTopTenValue($columnID, $rangeStart[1] + 1, $rangeEnd[1], $toptenRuleType, $ruleValue);
 797  
 798                      $operator = ($toptenRuleType == AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_TOP)
 799                          ? AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_GREATERTHANOREQUAL
 800                          : AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_LESSTHANOREQUAL;
 801                      $ruleValues[] = ['operator' => $operator, 'value' => $maxVal];
 802                      $columnFilterTests[$columnID] = [
 803                          'method' => 'filterTestInCustomDataSet',
 804                          'arguments' => ['filterRules' => $ruleValues, 'join' => AutoFilter\Column::AUTOFILTER_COLUMN_JOIN_OR],
 805                      ];
 806                      $filterColumn->setAttributes(['maxVal' => $maxVal]);
 807  
 808                      break;
 809              }
 810          }
 811  
 812          //    Execute the column tests for each row in the autoFilter range to determine show/hide,
 813          for ($row = $rangeStart[1] + 1; $row <= $rangeEnd[1]; ++$row) {
 814              $result = true;
 815              foreach ($columnFilterTests as $columnID => $columnFilterTest) {
 816                  $cellValue = $this->workSheet->getCell($columnID . $row)->getCalculatedValue();
 817                  //    Execute the filter test
 818                  $result = $result &&
 819                      call_user_func_array(
 820                          [self::class, $columnFilterTest['method']],
 821                          [$cellValue, $columnFilterTest['arguments']]
 822                      );
 823                  //    If filter test has resulted in FALSE, exit the loop straightaway rather than running any more tests
 824                  if (!$result) {
 825                      break;
 826                  }
 827              }
 828              //    Set show/hide for the row based on the result of the autoFilter result
 829              $this->workSheet->getRowDimension($row)->setVisible($result);
 830          }
 831  
 832          return $this;
 833      }
 834  
 835      /**
 836       * Implement PHP __clone to create a deep clone, not just a shallow copy.
 837       */
 838      public function __clone()
 839      {
 840          $vars = get_object_vars($this);
 841          foreach ($vars as $key => $value) {
 842              if (is_object($value)) {
 843                  if ($key === 'workSheet') {
 844                      //    Detach from worksheet
 845                      $this->{$key} = null;
 846                  } else {
 847                      $this->{$key} = clone $value;
 848                  }
 849              } elseif ((is_array($value)) && ($key == 'columns')) {
 850                  //    The columns array of \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet\AutoFilter objects
 851                  $this->{$key} = [];
 852                  foreach ($value as $k => $v) {
 853                      $this->{$key}[$k] = clone $v;
 854                      // attach the new cloned Column to this new cloned Autofilter object
 855                      $this->{$key}[$k]->setParent($this);
 856                  }
 857              } else {
 858                  $this->{$key} = $value;
 859              }
 860          }
 861      }
 862  
 863      /**
 864       * toString method replicates previous behavior by returning the range if object is
 865       * referenced as a property of its parent.
 866       */
 867      public function __toString()
 868      {
 869          return (string) $this->range;
 870      }
 871  }