tablepress / libraries / vendor / PhpSpreadsheet / Reader / Xml / DataValidations.php

DataValidations.php in TablePress – Tables in WordPress made easy 3.4, at libraries/vendor/PhpSpreadsheet/Reader/Xml/DataValidations.php

179 lines 5.6 KB

Raw Download Zip

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