|
1
|
|
|
<?php |
|
2
|
|
|
|
|
3
|
|
|
namespace PhpOffice\PhpSpreadsheet\Reader\Xml; |
|
4
|
|
|
|
|
5
|
|
|
use PhpOffice\PhpSpreadsheet\Cell\AddressHelper; |
|
6
|
|
|
use PhpOffice\PhpSpreadsheet\Cell\Coordinate; |
|
7
|
|
|
use PhpOffice\PhpSpreadsheet\Cell\DataValidation; |
|
8
|
|
|
use PhpOffice\PhpSpreadsheet\Reader\Xlsx\Namespaces; |
|
9
|
|
|
use PhpOffice\PhpSpreadsheet\Spreadsheet; |
|
10
|
|
|
use SimpleXMLElement; |
|
11
|
|
|
|
|
12
|
|
|
class DataValidations |
|
13
|
|
|
{ |
|
14
|
|
|
private const OPERATOR_MAPPINGS = [ |
|
15
|
|
|
'between' => DataValidation::OPERATOR_BETWEEN, |
|
16
|
|
|
'equal' => DataValidation::OPERATOR_EQUAL, |
|
17
|
|
|
'greater' => DataValidation::OPERATOR_GREATERTHAN, |
|
18
|
|
|
'greaterorequal' => DataValidation::OPERATOR_GREATERTHANOREQUAL, |
|
19
|
|
|
'less' => DataValidation::OPERATOR_LESSTHAN, |
|
20
|
|
|
'lessorequal' => DataValidation::OPERATOR_LESSTHANOREQUAL, |
|
21
|
|
|
'notbetween' => DataValidation::OPERATOR_NOTBETWEEN, |
|
22
|
|
|
'notequal' => DataValidation::OPERATOR_NOTEQUAL, |
|
23
|
|
|
]; |
|
24
|
|
|
|
|
25
|
|
|
private const TYPE_MAPPINGS = [ |
|
26
|
|
|
'textlength' => DataValidation::TYPE_TEXTLENGTH, |
|
27
|
|
|
]; |
|
28
|
|
|
|
|
29
|
|
|
private int $thisRow = 0; |
|
30
|
|
|
|
|
31
|
|
|
private int $thisColumn = 0; |
|
32
|
|
|
|
|
33
|
3 |
|
private function replaceR1C1(array $matches): string |
|
34
|
|
|
{ |
|
35
|
3 |
|
return AddressHelper::convertToA1($matches[0], $this->thisRow, $this->thisColumn, false); |
|
36
|
|
|
} |
|
37
|
|
|
|
|
38
|
40 |
|
public function loadDataValidations(SimpleXMLElement $worksheet, Spreadsheet $spreadsheet): void |
|
39
|
|
|
{ |
|
40
|
40 |
|
$xmlX = $worksheet->children(Namespaces::URN_EXCEL); |
|
41
|
40 |
|
$sheet = $spreadsheet->getActiveSheet(); |
|
42
|
|
|
/** @var callable $pregCallback */ |
|
43
|
40 |
|
$pregCallback = [$this, 'replaceR1C1']; |
|
44
|
40 |
|
foreach ($xmlX->DataValidation as $dataValidation) { |
|
45
|
4 |
|
$combinedCells = ''; |
|
46
|
4 |
|
$separator = ''; |
|
47
|
4 |
|
$validation = new DataValidation(); |
|
48
|
|
|
|
|
49
|
|
|
// set defaults |
|
50
|
4 |
|
$validation->setShowDropDown(true); |
|
51
|
4 |
|
$validation->setShowInputMessage(true); |
|
52
|
4 |
|
$validation->setShowErrorMessage(true); |
|
53
|
4 |
|
$validation->setShowDropDown(true); |
|
54
|
4 |
|
$this->thisRow = 1; |
|
55
|
4 |
|
$this->thisColumn = 1; |
|
56
|
|
|
|
|
57
|
4 |
|
foreach ($dataValidation as $tagName => $tagValue) { |
|
58
|
4 |
|
$tagValue = (string) $tagValue; |
|
59
|
4 |
|
$tagValueLower = strtolower($tagValue); |
|
60
|
|
|
switch ($tagName) { |
|
61
|
4 |
|
case 'Range': |
|
62
|
4 |
|
foreach (explode(',', $tagValue) as $range) { |
|
63
|
4 |
|
$cell = ''; |
|
64
|
4 |
|
if (preg_match('/^R(\d+)C(\d+):R(\d+)C(\d+)$/', (string) $range, $selectionMatches) === 1) { |
|
65
|
|
|
// range |
|
66
|
3 |
|
$firstCell = Coordinate::stringFromColumnIndex((int) $selectionMatches[2]) |
|
67
|
3 |
|
. $selectionMatches[1]; |
|
68
|
3 |
|
$cell = $firstCell |
|
69
|
3 |
|
. ':' |
|
70
|
3 |
|
. Coordinate::stringFromColumnIndex((int) $selectionMatches[4]) |
|
71
|
3 |
|
. $selectionMatches[3]; |
|
72
|
3 |
|
$this->thisRow = (int) $selectionMatches[1]; |
|
73
|
3 |
|
$this->thisColumn = (int) $selectionMatches[2]; |
|
74
|
3 |
|
$sheet->getCell($firstCell); |
|
75
|
3 |
|
$combinedCells .= "$separator$cell"; |
|
76
|
3 |
|
$separator = ' '; |
|
77
|
4 |
|
} elseif (preg_match('/^R(\d+)C(\d+)$/', (string) $range, $selectionMatches) === 1) { |
|
78
|
|
|
// cell |
|
79
|
4 |
|
$cell = Coordinate::stringFromColumnIndex((int) $selectionMatches[2]) |
|
80
|
4 |
|
. $selectionMatches[1]; |
|
81
|
4 |
|
$sheet->getCell($cell); |
|
82
|
4 |
|
$this->thisRow = (int) $selectionMatches[1]; |
|
83
|
4 |
|
$this->thisColumn = (int) $selectionMatches[2]; |
|
84
|
4 |
|
$combinedCells .= "$separator$cell"; |
|
85
|
4 |
|
$separator = ' '; |
|
86
|
4 |
|
} elseif (preg_match('/^C(\d+)(:C(]\\d+))?$/', (string) $range, $selectionMatches) === 1) { |
|
87
|
|
|
// column |
|
88
|
3 |
|
$firstCol = $selectionMatches[1]; |
|
89
|
3 |
|
$firstColString = Coordinate::stringFromColumnIndex((int) $firstCol); |
|
90
|
3 |
|
$lastCol = $selectionMatches[3] ?? $firstCol; |
|
91
|
3 |
|
$lastColString = Coordinate::stringFromColumnIndex((int) $lastCol); |
|
92
|
3 |
|
$firstCell = "{$firstColString}1"; |
|
93
|
3 |
|
$cell = "$firstColString:$lastColString"; |
|
94
|
3 |
|
$this->thisColumn = (int) $firstCol; |
|
95
|
3 |
|
$sheet->getCell($firstCell); |
|
96
|
3 |
|
$combinedCells .= "$separator$cell"; |
|
97
|
3 |
|
$separator = ' '; |
|
98
|
1 |
|
} elseif (preg_match('/^R(\\d+)(:R(]\\d+))?$/', (string) $range, $selectionMatches)) { |
|
99
|
|
|
// row |
|
100
|
1 |
|
$firstRow = $selectionMatches[1]; |
|
101
|
1 |
|
$lastRow = $selectionMatches[3] ?? $firstRow; |
|
102
|
1 |
|
$firstCell = "A$firstRow"; |
|
103
|
1 |
|
$cell = "$firstRow:$lastRow"; |
|
104
|
1 |
|
$this->thisRow = (int) $firstRow; |
|
105
|
1 |
|
$sheet->getCell($firstCell); |
|
106
|
1 |
|
$combinedCells .= "$separator$cell"; |
|
107
|
1 |
|
$separator = ' '; |
|
108
|
|
|
} |
|
109
|
|
|
} |
|
110
|
|
|
|
|
111
|
4 |
|
break; |
|
112
|
4 |
|
case 'Type': |
|
113
|
4 |
|
$validation->setType(self::TYPE_MAPPINGS[$tagValueLower] ?? $tagValueLower); |
|
114
|
|
|
|
|
115
|
4 |
|
break; |
|
116
|
4 |
|
case 'Qualifier': |
|
117
|
3 |
|
$validation->setOperator(self::OPERATOR_MAPPINGS[$tagValueLower] ?? $tagValueLower); |
|
118
|
|
|
|
|
119
|
3 |
|
break; |
|
120
|
4 |
|
case 'InputTitle': |
|
121
|
3 |
|
$validation->setPromptTitle($tagValue); |
|
122
|
|
|
|
|
123
|
3 |
|
break; |
|
124
|
4 |
|
case 'InputMessage': |
|
125
|
3 |
|
$validation->setPrompt($tagValue); |
|
126
|
|
|
|
|
127
|
3 |
|
break; |
|
128
|
4 |
|
case 'InputHide': |
|
129
|
4 |
|
$validation->setShowInputMessage(false); |
|
130
|
|
|
|
|
131
|
4 |
|
break; |
|
132
|
4 |
|
case 'ErrorStyle': |
|
133
|
1 |
|
$validation->setErrorStyle($tagValueLower); |
|
134
|
|
|
|
|
135
|
1 |
|
break; |
|
136
|
4 |
|
case 'ErrorTitle': |
|
137
|
4 |
|
$validation->setErrorTitle($tagValue); |
|
138
|
|
|
|
|
139
|
4 |
|
break; |
|
140
|
4 |
|
case 'ErrorMessage': |
|
141
|
4 |
|
$validation->setError($tagValue); |
|
142
|
|
|
|
|
143
|
4 |
|
break; |
|
144
|
4 |
|
case 'ErrorHide': |
|
145
|
1 |
|
$validation->setShowErrorMessage(false); |
|
146
|
|
|
|
|
147
|
1 |
|
break; |
|
148
|
4 |
|
case 'ComboHide': |
|
149
|
1 |
|
$validation->setShowDropDown(false); |
|
150
|
|
|
|
|
151
|
1 |
|
break; |
|
152
|
4 |
|
case 'UseBlank': |
|
153
|
4 |
|
$validation->setAllowBlank(true); |
|
154
|
|
|
|
|
155
|
4 |
|
break; |
|
156
|
4 |
|
case 'CellRangeList': |
|
157
|
|
|
// FIXME missing FIXME |
|
158
|
|
|
|
|
159
|
4 |
|
break; |
|
160
|
4 |
|
case 'Min': |
|
161
|
4 |
|
case 'Value': |
|
162
|
4 |
|
$tagValue = (string) preg_replace_callback(AddressHelper::R1C1_COORDINATE_REGEX, $pregCallback, $tagValue); |
|
163
|
4 |
|
$validation->setFormula1($tagValue); |
|
164
|
|
|
|
|
165
|
4 |
|
break; |
|
166
|
3 |
|
case 'Max': |
|
167
|
3 |
|
$tagValue = (string) preg_replace_callback(AddressHelper::R1C1_COORDINATE_REGEX, $pregCallback, $tagValue); |
|
168
|
3 |
|
$validation->setFormula2($tagValue); |
|
169
|
|
|
|
|
170
|
3 |
|
break; |
|
171
|
|
|
} |
|
172
|
|
|
} |
|
173
|
|
|
|
|
174
|
4 |
|
$sheet->setDataValidation($combinedCells, $validation); |
|
175
|
|
|
} |
|
176
|
|
|
} |
|
177
|
|
|
} |
|
178
|
|
|
|
PHPOffice /
PhpSpreadsheet
