[Summary view]

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