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