tablepress / libraries / vendor / PhpSpreadsheet / Reader / Xls / DataValidationHelper.php

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

219 lines 6.9 KB

Raw Download Zip

1	<?php
2
3	namespace TablePress\PhpOffice\PhpSpreadsheet\Reader\Xls;
4
5	use TablePress\PhpOffice\PhpSpreadsheet\Cell\AddressRange;
6	use TablePress\PhpOffice\PhpSpreadsheet\Cell\DataValidation;
7	use TablePress\PhpOffice\PhpSpreadsheet\Exception as PhpSpreadsheetException;
8	use TablePress\PhpOffice\PhpSpreadsheet\Reader\Xls;
9
10	class DataValidationHelper extends Xls
11	{
12	/**
13	* @var array<int, string>
14	*/
15	private static array $types = [
16	0x00 => DataValidation::TYPE_NONE,
17	0x01 => DataValidation::TYPE_WHOLE,
18	0x02 => DataValidation::TYPE_DECIMAL,
19	0x03 => DataValidation::TYPE_LIST,
20	0x04 => DataValidation::TYPE_DATE,
21	0x05 => DataValidation::TYPE_TIME,
22	0x06 => DataValidation::TYPE_TEXTLENGTH,
23	0x07 => DataValidation::TYPE_CUSTOM,
24	];
25
26	/**
27	* @var array<int, string>
28	*/
29	private static array $errorStyles = [
30	0x00 => DataValidation::STYLE_STOP,
31	0x01 => DataValidation::STYLE_WARNING,
32	0x02 => DataValidation::STYLE_INFORMATION,
33	];
34
35	/**
36	* @var array<int, string>
37	*/
38	private static array $operators = [
39	0x00 => DataValidation::OPERATOR_BETWEEN,
40	0x01 => DataValidation::OPERATOR_NOTBETWEEN,
41	0x02 => DataValidation::OPERATOR_EQUAL,
42	0x03 => DataValidation::OPERATOR_NOTEQUAL,
43	0x04 => DataValidation::OPERATOR_GREATERTHAN,
44	0x05 => DataValidation::OPERATOR_LESSTHAN,
45	0x06 => DataValidation::OPERATOR_GREATERTHANOREQUAL,
46	0x07 => DataValidation::OPERATOR_LESSTHANOREQUAL,
47	];
48
49	public static function type(int $type): ?string
50	{
51	return self::$types[$type] ?? null;
52	}
53
54	public static function errorStyle(int $errorStyle): ?string
55	{
56	return self::$errorStyles[$errorStyle] ?? null;
57	}
58
59	public static function operator(int $operator): ?string
60	{
61	return self::$operators[$operator] ?? null;
62	}
63
64	/**
65	* Read DATAVALIDATION record.
66	*/
67	protected function readDataValidation2(Xls $xls): void
68	{
69	$length = self::getUInt2d($xls->data, $xls->pos + 2);
70	$recordData = $xls->readRecordData($xls->data, $xls->pos + 4, $length);
71
72	// move stream pointer forward to next record
73	$xls->pos += 4 + $length;
74
75	if ($xls->readDataOnly) {
76	return;
77	}
78
79	// offset: 0; size: 4; Options
80	$options = self::getInt4d($recordData, 0);
81
82	// bit: 0-3; mask: 0x0000000F; type
83	$type = (0x0000000F & $options) >> 0;
84	$type = self::type($type);
85
86	// bit: 4-6; mask: 0x00000070; error type
87	$errorStyle = (0x00000070 & $options) >> 4;
88	$errorStyle = self::errorStyle($errorStyle);
89
90	// bit: 7; mask: 0x00000080; 1= formula is explicit (only applies to list)
91	// I have only seen cases where this is 1
92	//$explicitFormula = (0x00000080 & $options) >> 7;
93
94	// bit: 8; mask: 0x00000100; 1= empty cells allowed
95	$allowBlank = (0x00000100 & $options) >> 8;
96
97	// bit: 9; mask: 0x00000200; 1= suppress drop down arrow in list type validity
98	$suppressDropDown = (0x00000200 & $options) >> 9;
99
100	// bit: 18; mask: 0x00040000; 1= show prompt box if cell selected
101	$showInputMessage = (0x00040000 & $options) >> 18;
102
103	// bit: 19; mask: 0x00080000; 1= show error box if invalid values entered
104	$showErrorMessage = (0x00080000 & $options) >> 19;
105
106	// bit: 20-23; mask: 0x00F00000; condition operator
107	$operator = (0x00F00000 & $options) >> 20;
108	$operator = self::operator($operator);
109
110	if ($type === null \|\| $errorStyle === null \|\| $operator === null) {
111	return;
112	}
113
114	// offset: 4; size: var; title of the prompt box
115	$offset = 4;
116	$string = self::readUnicodeStringLong((string) substr($recordData, $offset));
117	$promptTitle = $string['value'] !== chr(0) ? $string['value'] : '';
118	$offset += $string['size'];
119
120	// offset: var; size: var; title of the error box
121	$string = self::readUnicodeStringLong((string) substr($recordData, $offset));
122	$errorTitle = $string['value'] !== chr(0) ? $string['value'] : '';
123	$offset += $string['size'];
124
125	// offset: var; size: var; text of the prompt box
126	$string = self::readUnicodeStringLong((string) substr($recordData, $offset));
127	$prompt = $string['value'] !== chr(0) ? $string['value'] : '';
128	$offset += $string['size'];
129
130	// offset: var; size: var; text of the error box
131	$string = self::readUnicodeStringLong((string) substr($recordData, $offset));
132	$error = $string['value'] !== chr(0) ? $string['value'] : '';
133	$offset += $string['size'];
134
135	// offset: var; size: 2; size of the formula data for the first condition
136	$sz1 = self::getUInt2d($recordData, $offset);
137	$offset += 2;
138
139	// offset: var; size: 2; not used
140	$offset += 2;
141
142	// offset: var; size: $sz1; formula data for first condition (without size field)
143	$formula1 = (string) substr($recordData, $offset, $sz1);
144	$formula1 = pack('v', $sz1) . $formula1; // prepend the length
145
146	try {
147	$formula1 = $xls->getFormulaFromStructure($formula1);
148
149	// in list type validity, null characters are used as item separators
150	if ($type == DataValidation::TYPE_LIST) {
151	$formula1 = str_replace(chr(0), ',', $formula1);
152	}
153	} catch (PhpSpreadsheetException $e) {
154	return;
155	}
156	$offset += $sz1;
157
158	// offset: var; size: 2; size of the formula data for the first condition
159	$sz2 = self::getUInt2d($recordData, $offset);
160	$offset += 2;
161
162	// offset: var; size: 2; not used
163	$offset += 2;
164
165	// offset: var; size: $sz2; formula data for second condition (without size field)
166	$formula2 = (string) substr($recordData, $offset, $sz2);
167	$formula2 = pack('v', $sz2) . $formula2; // prepend the length
168
169	try {
170	$formula2 = $xls->getFormulaFromStructure($formula2);
171	} catch (PhpSpreadsheetException $exception) {
172	return;
173	}
174	$offset += $sz2;
175
176	// offset: var; size: var; cell range address list with
177	$cellRangeAddressList = Biff8::readBIFF8CellRangeAddressList((string) substr($recordData, $offset));
178	/** @var string[] */
179	$cellRangeAddresses = $cellRangeAddressList['cellRangeAddresses'];
180	$maxRow = (string) AddressRange::MAX_ROW;
181	$maxCol = AddressRange::MAX_COLUMN;
182	$maxXlsRow = (string) AddressRange::MAX_ROW_XLS;
183	$maxXlsColumnString = AddressRange::MAX_COLUMN_XLS;
184
185	foreach ($cellRangeAddresses as $cellRange) {
186	$cellRange = preg_replace(
187	[
188	"/([a-z]+)1:([a-z]+)$maxXlsRow/i",
189	"/([a-z]+\\d+):([a-z]+)$maxXlsRow/i",
190	"/A(\\d+):$maxXlsColumnString(\\d+)/i",
191	"/([a-z]+\\d+):$maxXlsColumnString(\\d+)/i",
192	],
193	[
194	'$1:$2',
195	'$1:${2}' . $maxRow,
196	'$1:$2',
197	'$1:' . $maxCol . '$2',
198	],
199	$cellRange
200	) ?? $cellRange;
201	$objValidation = new DataValidation();
202	$objValidation->setType($type);
203	$objValidation->setErrorStyle($errorStyle);
204	$objValidation->setAllowBlank((bool) $allowBlank);
205	$objValidation->setShowInputMessage((bool) $showInputMessage);
206	$objValidation->setShowErrorMessage((bool) $showErrorMessage);
207	$objValidation->setShowDropDown(!$suppressDropDown);
208	$objValidation->setOperator($operator);
209	$objValidation->setErrorTitle($errorTitle);
210	$objValidation->setError($error);
211	$objValidation->setPromptTitle($promptTitle);
212	$objValidation->setPrompt($prompt);
213	$objValidation->setFormula1($formula1);
214	$objValidation->setFormula2($formula2);
215	$xls->phpSheet->setDataValidation($cellRange, $objValidation);
216	}
217	}
218	}
219