wpdatatables / lib / phpoffice / phpspreadsheet / src / PhpSpreadsheet / Calculation / Engine / Operands / StructuredReference.php

StructuredReference.php in wpDataTables – WordPress Data Table, Dynamic Tables & Table Charts Plugin 6.5.1.7, at lib/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Engine/Operands/StructuredReference.php

345 lines 11.9 KB

Raw Download Zip

1	<?php
2
3	namespace PhpOffice\PhpSpreadsheet\Calculation\Engine\Operands;
4
5	use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
6	use PhpOffice\PhpSpreadsheet\Calculation\Exception;
7	use PhpOffice\PhpSpreadsheet\Cell\Cell;
8	use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
9	use PhpOffice\PhpSpreadsheet\Worksheet\Table;
10
11	final class StructuredReference implements Operand
12	{
13	public const NAME = 'Structured Reference';
14
15	private const OPEN_BRACE = '[';
16	private const CLOSE_BRACE = ']';
17
18	private const ITEM_SPECIFIER_ALL = '#All';
19	private const ITEM_SPECIFIER_HEADERS = '#Headers';
20	private const ITEM_SPECIFIER_DATA = '#Data';
21	private const ITEM_SPECIFIER_TOTALS = '#Totals';
22	private const ITEM_SPECIFIER_THIS_ROW = '#This Row';
23
24	private const ITEM_SPECIFIER_ROWS_SET = [
25	self::ITEM_SPECIFIER_ALL,
26	self::ITEM_SPECIFIER_HEADERS,
27	self::ITEM_SPECIFIER_DATA,
28	self::ITEM_SPECIFIER_TOTALS,
29	];
30
31	private const TABLE_REFERENCE = '/([\p{L}_\\\][\p{L}\p{N}\._]+)?(\[(?:[^\]\[]+\|(?R))*+\])/miu';
32
33	private string $value;
34
35	private string $tableName;
36
37	private Table $table;
38
39	private string $reference;
40
41	private ?int $headersRow;
42
43	private int $firstDataRow;
44
45	private int $lastDataRow;
46
47	private ?int $totalsRow;
48
49	private array $columns;
50
51	public function __construct(string $structuredReference)
52	{
53	$this->value = $structuredReference;
54	}
55
56	public static function fromParser(string $formula, int $index, array $matches): self
57	{
58	$val = $matches[0];
59
60	$srCount = substr_count($val, self::OPEN_BRACE)
61	- substr_count($val, self::CLOSE_BRACE);
62	while ($srCount > 0) {
63	$srIndex = strlen($val);
64	$srStringRemainder = substr($formula, $index + $srIndex);
65	$closingPos = strpos($srStringRemainder, self::CLOSE_BRACE);
66	if ($closingPos === false) {
67	throw new Exception("Formula Error: No closing ']' to match opening '['");
68	}
69	$srStringRemainder = substr($srStringRemainder, 0, $closingPos + 1);
70	--$srCount;
71	if (strpos($srStringRemainder, self::OPEN_BRACE) !== false) {
72	++$srCount;
73	}
74	$val .= $srStringRemainder;
75	}
76
77	return new self($val);
78	}
79
80	/**
81	* @throws Exception
82	* @throws \PhpOffice\PhpSpreadsheet\Exception
83	*/
84	public function parse(Cell $cell): string
85	{
86	$this->getTableStructure($cell);
87	$cellRange = ($this->isRowReference()) ? $this->getRowReference($cell) : $this->getColumnReference();
88
89	return $cellRange;
90	}
91
92	private function isRowReference(): bool
93	{
94	return strpos($this->value, '[@') !== false
95	\|\| strpos($this->value, '[' . self::ITEM_SPECIFIER_THIS_ROW . ']') !== false;
96	}
97
98	/**
99	* @throws Exception
100	* @throws \PhpOffice\PhpSpreadsheet\Exception
101	*/
102	private function getTableStructure(Cell $cell): void
103	{
104	preg_match(self::TABLE_REFERENCE, $this->value, $matches);
105
106	$this->tableName = $matches[1];
107	$this->table = ($this->tableName === '')
108	? $this->getTableForCell($cell)
109	: $this->getTableByName($cell);
110	$this->reference = $matches[2];
111	$tableRange = Coordinate::getRangeBoundaries($this->table->getRange());
112
113	$this->headersRow = ($this->table->getShowHeaderRow()) ? (int) $tableRange[0][1] : null;
114	$this->firstDataRow = ($this->table->getShowHeaderRow()) ? (int) $tableRange[0][1] + 1 : $tableRange[0][1];
115	$this->totalsRow = ($this->table->getShowTotalsRow()) ? (int) $tableRange[1][1] : null;
116	$this->lastDataRow = ($this->table->getShowTotalsRow()) ? (int) $tableRange[1][1] - 1 : $tableRange[1][1];
117
118	$this->columns = $this->getColumns($cell, $tableRange);
119	}
120
121	/**
122	* @throws Exception
123	* @throws \PhpOffice\PhpSpreadsheet\Exception
124	*/
125	private function getTableForCell(Cell $cell): Table
126	{
127	$tables = $cell->getWorksheet()->getTableCollection();
128	foreach ($tables as $table) {
129	/** @var Table $table */
130	$range = $table->getRange();
131	if ($cell->isInRange($range) === true) {
132	$this->tableName = $table->getName();
133
134	return $table;
135	}
136	}
137
138	throw new Exception('Table for Structured Reference cannot be identified');
139	}
140
141	/**
142	* @throws Exception
143	* @throws \PhpOffice\PhpSpreadsheet\Exception
144	*/
145	private function getTableByName(Cell $cell): Table
146	{
147	$table = $cell->getWorksheet()->getTableByName($this->tableName);
148
149	if ($table === null) {
150	throw new Exception("Table {$this->tableName} for Structured Reference cannot be located");
151	}
152
153	return $table;
154	}
155
156	private function getColumns(Cell $cell, array $tableRange): array
157	{
158	$worksheet = $cell->getWorksheet();
159	$cellReference = $cell->getCoordinate();
160
161	$columns = [];
162	$lastColumn = ++$tableRange[1][0];
163	for ($column = $tableRange[0][0]; $column !== $lastColumn; ++$column) {
164	$columns[$column] = $worksheet
165	->getCell($column . ($this->headersRow ?? ($this->firstDataRow - 1)))
166	->getCalculatedValue();
167	}
168
169	$worksheet->getCell($cellReference);
170
171	return $columns;
172	}
173
174	private function getRowReference(Cell $cell): string
175	{
176	$reference = str_replace("\u{a0}", ' ', $this->reference);
177	/** @var string $reference */
178	$reference = str_replace('[' . self::ITEM_SPECIFIER_THIS_ROW . '],', '', $reference);
179
180	foreach ($this->columns as $columnId => $columnName) {
181	$columnName = str_replace("\u{a0}", ' ', $columnName);
182	$reference = $this->adjustRowReference($columnName, $reference, $cell, $columnId);
183	}
184
185	/** @var string $reference */
186	return $this->validateParsedReference(trim($reference, '[]@, '));
187	}
188
189	private function adjustRowReference(string $columnName, string $reference, Cell $cell, string $columnId): string
190	{
191	if ($columnName !== '') {
192	$cellReference = $columnId . $cell->getRow();
193	$pattern1 = '/\[' . preg_quote($columnName, '/') . '\]/miu';
194	$pattern2 = '/@' . preg_quote($columnName, '/') . '/miu';
195	if (preg_match($pattern1, $reference) === 1) {
196	$reference = preg_replace($pattern1, $cellReference, $reference);
197	} elseif (preg_match($pattern2, $reference) === 1) {
198	$reference = preg_replace($pattern2, $cellReference, $reference);
199	}
200	/** @var string $reference */
201	}
202
203	return $reference;
204	}
205
206	/**
207	* @throws Exception
208	* @throws \PhpOffice\PhpSpreadsheet\Exception
209	*/
210	private function getColumnReference(): string
211	{
212	$reference = str_replace("\u{a0}", ' ', $this->reference);
213	$startRow = ($this->totalsRow === null) ? $this->lastDataRow : $this->totalsRow;
214	$endRow = ($this->headersRow === null) ? $this->firstDataRow : $this->headersRow;
215
216	[$startRow, $endRow] = $this->getRowsForColumnReference($reference, $startRow, $endRow);
217	$reference = $this->getColumnsForColumnReference($reference, $startRow, $endRow);
218
219	$reference = trim($reference, '[]@, ');
220	if (substr_count($reference, ':') > 1) {
221	$cells = explode(':', $reference);
222	$firstCell = array_shift($cells);
223	$lastCell = array_pop($cells);
224	$reference = "{$firstCell}:{$lastCell}";
225	}
226
227	return $this->validateParsedReference($reference);
228	}
229
230	/**
231	* @throws Exception
232	* @throws \PhpOffice\PhpSpreadsheet\Exception
233	*/
234	private function validateParsedReference(string $reference): string
235	{
236	if (preg_match('/^' . Calculation::CALCULATION_REGEXP_CELLREF . ':' . Calculation::CALCULATION_REGEXP_CELLREF . '$/miu', $reference) !== 1) {
237	if (preg_match('/^' . Calculation::CALCULATION_REGEXP_CELLREF . '$/miu', $reference) !== 1) {
238	throw new Exception(
239	"Invalid Structured Reference {$this->reference} {$reference}",
240	Exception::CALCULATION_ENGINE_PUSH_TO_STACK
241	);
242	}
243	}
244
245	return $reference;
246	}
247
248	private function fullData(int $startRow, int $endRow): string
249	{
250	$columns = array_keys($this->columns);
251	$firstColumn = array_shift($columns);
252	$lastColumn = (empty($columns)) ? $firstColumn : array_pop($columns);
253
254	return "{$firstColumn}{$startRow}:{$lastColumn}{$endRow}";
255	}
256
257	private function getMinimumRow(string $reference): int
258	{
259	switch ($reference) {
260	case self::ITEM_SPECIFIER_ALL:
261	case self::ITEM_SPECIFIER_HEADERS:
262	return $this->headersRow ?? $this->firstDataRow;
263	case self::ITEM_SPECIFIER_DATA:
264	return $this->firstDataRow;
265	case self::ITEM_SPECIFIER_TOTALS:
266	return $this->totalsRow ?? $this->lastDataRow;
267	}
268
269	return $this->headersRow ?? $this->firstDataRow;
270	}
271
272	private function getMaximumRow(string $reference): int
273	{
274	switch ($reference) {
275	case self::ITEM_SPECIFIER_HEADERS:
276	return $this->headersRow ?? $this->firstDataRow;
277	case self::ITEM_SPECIFIER_DATA:
278	return $this->lastDataRow;
279	case self::ITEM_SPECIFIER_ALL:
280	case self::ITEM_SPECIFIER_TOTALS:
281	return $this->totalsRow ?? $this->lastDataRow;
282	}
283
284	return $this->totalsRow ?? $this->lastDataRow;
285	}
286
287	public function value(): string
288	{
289	return $this->value;
290	}
291
292	/**
293	* @return array<int, int>
294	*/
295	private function getRowsForColumnReference(string &$reference, int $startRow, int $endRow): array
296	{
297	$rowsSelected = false;
298	foreach (self::ITEM_SPECIFIER_ROWS_SET as $rowReference) {
299	$pattern = '/\[' . $rowReference . '\]/mui';
300	/** @var string $reference */
301	if (preg_match($pattern, $reference) === 1) {
302	if (($rowReference === self::ITEM_SPECIFIER_HEADERS) && ($this->table->getShowHeaderRow() === false)) {
303	throw new Exception(
304	'Table Headers are Hidden, and should not be Referenced',
305	Exception::CALCULATION_ENGINE_PUSH_TO_STACK
306	);
307	}
308	$rowsSelected = true;
309	$startRow = min($startRow, $this->getMinimumRow($rowReference));
310	$endRow = max($endRow, $this->getMaximumRow($rowReference));
311	$reference = preg_replace($pattern, '', $reference);
312	}
313	}
314	if ($rowsSelected === false) {
315	// If there isn't any Special Item Identifier specified, then the selection defaults to data rows only.
316	$startRow = $this->firstDataRow;
317	$endRow = $this->lastDataRow;
318	}
319
320	return [$startRow, $endRow];
321	}
322
323	private function getColumnsForColumnReference(string $reference, int $startRow, int $endRow): string
324	{
325	$columnsSelected = false;
326	foreach ($this->columns as $columnId => $columnName) {
327	$columnName = str_replace("\u{a0}", ' ', $columnName);
328	$cellFrom = "{$columnId}{$startRow}";
329	$cellTo = "{$columnId}{$endRow}";
330	$cellReference = ($cellFrom === $cellTo) ? $cellFrom : "{$cellFrom}:{$cellTo}";
331	$pattern = '/\[' . preg_quote($columnName, '/') . '\]/mui';
332	if (preg_match($pattern, $reference) === 1) {
333	$columnsSelected = true;
334	$reference = preg_replace($pattern, $cellReference, $reference);
335	}
336	/** @var string $reference */
337	}
338	if ($columnsSelected === false) {
339	return $this->fullData($startRow, $endRow);
340	}
341
342	return $reference;
343	}
344	}
345