[Summary view]

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