[Summary view]

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

   1  <?php
   2  
   3  namespace PhpOffice\PhpSpreadsheet\Reader\Xlsx;
   4  
   5  use PhpOffice\PhpSpreadsheet\Worksheet\AutoFilter\Column;
   6  use PhpOffice\PhpSpreadsheet\Worksheet\AutoFilter\Column\Rule;
   7  use PhpOffice\PhpSpreadsheet\Worksheet\Table;
   8  use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
   9  use SimpleXMLElement;
  10  
  11  class AutoFilter
  12  {
  13      /**
  14       * @var Table|Worksheet
  15       */
  16      private $parent;
  17  
  18      /**
  19       * @var SimpleXMLElement
  20       */
  21      private $worksheetXml;
  22  
  23      /**
  24       * @param Table|Worksheet $parent
  25       */
  26      public function __construct($parent, SimpleXMLElement $worksheetXml)
  27      {
  28          $this->parent = $parent;
  29          $this->worksheetXml = $worksheetXml;
  30      }
  31  
  32      public function load(): void
  33      {
  34          // Remove all "$" in the auto filter range
  35          $autoFilterRange = (string) preg_replace('/\$/', '', $this->worksheetXml->autoFilter['ref'] ?? '');
  36          if (strpos($autoFilterRange, ':') !== false) {
  37              $this->readAutoFilter($autoFilterRange, $this->worksheetXml);
  38          }
  39      }
  40  
  41      private function readAutoFilter(string $autoFilterRange, SimpleXMLElement $xmlSheet): void
  42      {
  43          $autoFilter = $this->parent->getAutoFilter();
  44          $autoFilter->setRange($autoFilterRange);
  45  
  46          foreach ($xmlSheet->autoFilter->filterColumn as $filterColumn) {
  47              $column = $autoFilter->getColumnByOffset((int) $filterColumn['colId']);
  48              //    Check for standard filters
  49              if ($filterColumn->filters) {
  50                  $column->setFilterType(Column::AUTOFILTER_FILTERTYPE_FILTER);
  51                  $filters = $filterColumn->filters;
  52                  if ((isset($filters['blank'])) && ((int) $filters['blank'] == 1)) {
  53                      //    Operator is undefined, but always treated as EQUAL
  54                      $column->createRule()->setRule('', '')->setRuleType(Rule::AUTOFILTER_RULETYPE_FILTER);
  55                  }
  56                  //    Standard filters are always an OR join, so no join rule needs to be set
  57                  //    Entries can be either filter elements
  58                  foreach ($filters->filter as $filterRule) {
  59                      //    Operator is undefined, but always treated as EQUAL
  60                      $column->createRule()->setRule('', (string) $filterRule['val'])->setRuleType(Rule::AUTOFILTER_RULETYPE_FILTER);
  61                  }
  62  
  63                  //    Or Date Group elements
  64                  $this->readDateRangeAutoFilter($filters, $column);
  65              }
  66  
  67              //    Check for custom filters
  68              $this->readCustomAutoFilter($filterColumn, $column);
  69              //    Check for dynamic filters
  70              $this->readDynamicAutoFilter($filterColumn, $column);
  71              //    Check for dynamic filters
  72              $this->readTopTenAutoFilter($filterColumn, $column);
  73          }
  74          $autoFilter->setEvaluated(true);
  75      }
  76  
  77      private function readDateRangeAutoFilter(SimpleXMLElement $filters, Column $column): void
  78      {
  79          foreach ($filters->dateGroupItem as $dateGroupItem) {
  80              //    Operator is undefined, but always treated as EQUAL
  81              $column->createRule()->setRule(
  82                  '',
  83                  [
  84                      'year' => (string) $dateGroupItem['year'],
  85                      'month' => (string) $dateGroupItem['month'],
  86                      'day' => (string) $dateGroupItem['day'],
  87                      'hour' => (string) $dateGroupItem['hour'],
  88                      'minute' => (string) $dateGroupItem['minute'],
  89                      'second' => (string) $dateGroupItem['second'],
  90                  ],
  91                  (string) $dateGroupItem['dateTimeGrouping']
  92              )->setRuleType(Rule::AUTOFILTER_RULETYPE_DATEGROUP);
  93          }
  94      }
  95  
  96      private function readCustomAutoFilter(?SimpleXMLElement $filterColumn, Column $column): void
  97      {
  98          if (isset($filterColumn, $filterColumn->customFilters)) {
  99              $column->setFilterType(Column::AUTOFILTER_FILTERTYPE_CUSTOMFILTER);
 100              $customFilters = $filterColumn->customFilters;
 101              //    Custom filters can an AND or an OR join;
 102              //        and there should only ever be one or two entries
 103              if ((isset($customFilters['and'])) && ((string) $customFilters['and'] === '1')) {
 104                  $column->setJoin(Column::AUTOFILTER_COLUMN_JOIN_AND);
 105              }
 106              foreach ($customFilters->customFilter as $filterRule) {
 107                  $column->createRule()->setRule(
 108                      (string) $filterRule['operator'],
 109                      (string) $filterRule['val']
 110                  )->setRuleType(Rule::AUTOFILTER_RULETYPE_CUSTOMFILTER);
 111              }
 112          }
 113      }
 114  
 115      private function readDynamicAutoFilter(?SimpleXMLElement $filterColumn, Column $column): void
 116      {
 117          if (isset($filterColumn, $filterColumn->dynamicFilter)) {
 118              $column->setFilterType(Column::AUTOFILTER_FILTERTYPE_DYNAMICFILTER);
 119              //    We should only ever have one dynamic filter
 120              foreach ($filterColumn->dynamicFilter as $filterRule) {
 121                  //    Operator is undefined, but always treated as EQUAL
 122                  $column->createRule()->setRule(
 123                      '',
 124                      (string) $filterRule['val'],
 125                      (string) $filterRule['type']
 126                  )->setRuleType(Rule::AUTOFILTER_RULETYPE_DYNAMICFILTER);
 127                  if (isset($filterRule['val'])) {
 128                      $column->setAttribute('val', (string) $filterRule['val']);
 129                  }
 130                  if (isset($filterRule['maxVal'])) {
 131                      $column->setAttribute('maxVal', (string) $filterRule['maxVal']);
 132                  }
 133              }
 134          }
 135      }
 136  
 137      private function readTopTenAutoFilter(?SimpleXMLElement $filterColumn, Column $column): void
 138      {
 139          if (isset($filterColumn, $filterColumn->top10)) {
 140              $column->setFilterType(Column::AUTOFILTER_FILTERTYPE_TOPTENFILTER);
 141              //    We should only ever have one top10 filter
 142              foreach ($filterColumn->top10 as $filterRule) {
 143                  $column->createRule()->setRule(
 144                      (
 145                          ((isset($filterRule['percent'])) && ((string) $filterRule['percent'] === '1'))
 146                          ? Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_PERCENT
 147                          : Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_BY_VALUE
 148                      ),
 149                      (string) $filterRule['val'],
 150                      (
 151                          ((isset($filterRule['top'])) && ((string) $filterRule['top'] === '1'))
 152                          ? Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_TOP
 153                          : Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_BOTTOM
 154                      )
 155                  )->setRuleType(Rule::AUTOFILTER_RULETYPE_TOPTENFILTER);
 156              }
 157          }
 158      }
 159  }