[Summary view]

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

   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 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  }