1<?php
2
3namespace PhpOffice\PhpSpreadsheet\Reader\Xlsx;
4
5use PhpOffice\PhpSpreadsheet\Worksheet\AutoFilter\Column;
6use PhpOffice\PhpSpreadsheet\Worksheet\AutoFilter\Column\Rule;
7use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
8use SimpleXMLElement;
9
10class 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}
147