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