wpdatatables / lib / phpoffice / phpspreadsheet / src / PhpSpreadsheet / Worksheet / Table.php

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

586 lines 16.9 KB

Raw Download Zip

1	<?php
2
3	namespace PhpOffice\PhpSpreadsheet\Worksheet;
4
5	use PhpOffice\PhpSpreadsheet\Cell\AddressRange;
6	use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
7	use PhpOffice\PhpSpreadsheet\Cell\DataType;
8	use PhpOffice\PhpSpreadsheet\Exception as PhpSpreadsheetException;
9	use PhpOffice\PhpSpreadsheet\Shared\StringHelper;
10	use PhpOffice\PhpSpreadsheet\Spreadsheet;
11	use PhpOffice\PhpSpreadsheet\Worksheet\Table\TableStyle;
12
13	class Table
14	{
15	/**
16	* Table Name.
17	*
18	* @var string
19	*/
20	private $name;
21
22	/**
23	* Show Header Row.
24	*
25	* @var bool
26	*/
27	private $showHeaderRow = true;
28
29	/**
30	* Show Totals Row.
31	*
32	* @var bool
33	*/
34	private $showTotalsRow = false;
35
36	/**
37	* Table Range.
38	*
39	* @var string
40	*/
41	private $range = '';
42
43	/**
44	* Table Worksheet.
45	*
46	* @var null\|Worksheet
47	*/
48	private $workSheet;
49
50	/**
51	* Table allow filter.
52	*
53	* @var bool
54	*/
55	private $allowFilter = true;
56
57	/**
58	* Table Column.
59	*
60	* @var Table\Column[]
61	*/
62	private $columns = [];
63
64	/**
65	* Table Style.
66	*
67	* @var TableStyle
68	*/
69	private $style;
70
71	/**
72	* Table AutoFilter.
73	*
74	* @var AutoFilter
75	*/
76	private $autoFilter;
77
78	/**
79	* Create a new Table.
80	*
81	* @param AddressRange\|array<int>\|string $range
82	* A simple string containing a Cell range like 'A1:E10' is permitted
83	* or passing in an array of [$fromColumnIndex, $fromRow, $toColumnIndex, $toRow] (e.g. [3, 5, 6, 8]),
84	* or an AddressRange object.
85	* @param string $name (e.g. Table1)
86	*/
87	public function __construct($range = '', string $name = '')
88	{
89	$this->style = new TableStyle();
90	$this->autoFilter = new AutoFilter($range);
91	$this->setRange($range);
92	$this->setName($name);
93	}
94
95	/**
96	* Get Table name.
97	*/
98	public function getName(): string
99	{
100	return $this->name;
101	}
102
103	/**
104	* Set Table name.
105	*
106	* @throws PhpSpreadsheetException
107	*/
108	public function setName(string $name): self
109	{
110	$name = trim($name);
111
112	if (!empty($name)) {
113	if (strlen($name) === 1 && in_array($name, ['C', 'c', 'R', 'r'])) {
114	throw new PhpSpreadsheetException('The table name is invalid');
115	}
116	if (StringHelper::countCharacters($name) > 255) {
117	throw new PhpSpreadsheetException('The table name cannot be longer than 255 characters');
118	}
119	// Check for A1 or R1C1 cell reference notation
120	if (
121	preg_match(Coordinate::A1_COORDINATE_REGEX, $name) \|\|
122	preg_match('/^R\[?\-?[0-9]\]?C\[?\-?[0-9]\]?$/i', $name)
123	) {
124	throw new PhpSpreadsheetException('The table name can\'t be the same as a cell reference');
125	}
126	if (!preg_match('/^[\p{L}_\\\]/iu', $name)) {
127	throw new PhpSpreadsheetException('The table name must begin a name with a letter, an underscore character (_), or a backslash (\)');
128	}
129	if (!preg_match('/^[\p{L}_\\\][\p{L}\p{M}0-9\._]+$/iu', $name)) {
130	throw new PhpSpreadsheetException('The table name contains invalid characters');
131	}
132
133	$this->checkForDuplicateTableNames($name, $this->workSheet);
134	$this->updateStructuredReferences($name);
135	}
136
137	$this->name = $name;
138
139	return $this;
140	}
141
142	/**
143	* @throws PhpSpreadsheetException
144	*/
145	private function checkForDuplicateTableNames(string $name, ?Worksheet $worksheet): void
146	{
147	// Remember that table names are case-insensitive
148	$tableName = StringHelper::strToLower($name);
149
150	if ($worksheet !== null && StringHelper::strToLower($this->name) !== $name) {
151	$spreadsheet = $worksheet->getParentOrThrow();
152
153	foreach ($spreadsheet->getWorksheetIterator() as $sheet) {
154	foreach ($sheet->getTableCollection() as $table) {
155	if (StringHelper::strToLower($table->getName()) === $tableName && $table != $this) {
156	throw new PhpSpreadsheetException("Spreadsheet already contains a table named '{$this->name}'");
157	}
158	}
159	}
160	}
161	}
162
163	private function updateStructuredReferences(string $name): void
164	{
165	if ($this->workSheet === null \|\| $this->name === null \|\| $this->name === '') {
166	return;
167	}
168
169	// Remember that table names are case-insensitive
170	if (StringHelper::strToLower($this->name) !== StringHelper::strToLower($name)) {
171	// We need to check all formula cells that might contain fully-qualified Structured References
172	// that refer to this table, and update those formulae to reference the new table name
173	$spreadsheet = $this->workSheet->getParentOrThrow();
174	foreach ($spreadsheet->getWorksheetIterator() as $sheet) {
175	$this->updateStructuredReferencesInCells($sheet, $name);
176	}
177	$this->updateStructuredReferencesInNamedFormulae($spreadsheet, $name);
178	}
179	}
180
181	private function updateStructuredReferencesInCells(Worksheet $worksheet, string $newName): void
182	{
183	$pattern = '/' . preg_quote($this->name, '/') . '\[/mui';
184
185	foreach ($worksheet->getCoordinates(false) as $coordinate) {
186	$cell = $worksheet->getCell($coordinate);
187	if ($cell->getDataType() === DataType::TYPE_FORMULA) {
188	$formula = $cell->getValue();
189	if (preg_match($pattern, $formula) === 1) {
190	$formula = preg_replace($pattern, "{$newName}[", $formula);
191	$cell->setValueExplicit($formula, DataType::TYPE_FORMULA);
192	}
193	}
194	}
195	}
196
197	private function updateStructuredReferencesInNamedFormulae(Spreadsheet $spreadsheet, string $newName): void
198	{
199	$pattern = '/' . preg_quote($this->name, '/') . '\[/mui';
200
201	foreach ($spreadsheet->getNamedFormulae() as $namedFormula) {
202	$formula = $namedFormula->getValue();
203	if (preg_match($pattern, $formula) === 1) {
204	$formula = preg_replace($pattern, "{$newName}[", $formula);
205	$namedFormula->setValue($formula); // @phpstan-ignore-line
206	}
207	}
208	}
209
210	/**
211	* Get show Header Row.
212	*/
213	public function getShowHeaderRow(): bool
214	{
215	return $this->showHeaderRow;
216	}
217
218	/**
219	* Set show Header Row.
220	*/
221	public function setShowHeaderRow(bool $showHeaderRow): self
222	{
223	$this->showHeaderRow = $showHeaderRow;
224
225	return $this;
226	}
227
228	/**
229	* Get show Totals Row.
230	*/
231	public function getShowTotalsRow(): bool
232	{
233	return $this->showTotalsRow;
234	}
235
236	/**
237	* Set show Totals Row.
238	*/
239	public function setShowTotalsRow(bool $showTotalsRow): self
240	{
241	$this->showTotalsRow = $showTotalsRow;
242
243	return $this;
244	}
245
246	/**
247	* Get allow filter.
248	* If false, autofiltering is disabled for the table, if true it is enabled.
249	*/
250	public function getAllowFilter(): bool
251	{
252	return $this->allowFilter;
253	}
254
255	/**
256	* Set show Autofiltering.
257	* Disabling autofiltering has the same effect as hiding the filter button on all the columns in the table.
258	*/
259	public function setAllowFilter(bool $allowFilter): self
260	{
261	$this->allowFilter = $allowFilter;
262
263	return $this;
264	}
265
266	/**
267	* Get Table Range.
268	*/
269	public function getRange(): string
270	{
271	return $this->range;
272	}
273
274	/**
275	* Set Table Cell Range.
276	*
277	* @param AddressRange\|array<int>\|string $range
278	* A simple string containing a Cell range like 'A1:E10' is permitted
279	* or passing in an array of [$fromColumnIndex, $fromRow, $toColumnIndex, $toRow] (e.g. [3, 5, 6, 8]),
280	* or an AddressRange object.
281	*/
282	public function setRange($range = ''): self
283	{
284	// extract coordinate
285	if ($range !== '') {
286	[, $range] = Worksheet::extractSheetTitle(Validations::validateCellRange($range), true);
287	}
288	if (empty($range)) {
289	// Discard all column rules
290	$this->columns = [];
291	$this->range = '';
292
293	return $this;
294	}
295
296	if (strpos($range, ':') === false) {
297	throw new PhpSpreadsheetException('Table must be set on a range of cells.');
298	}
299
300	[$width, $height] = Coordinate::rangeDimension($range);
301	if ($width < 1 \|\| $height < 1) {
302	throw new PhpSpreadsheetException('The table range must be at least 1 column and row');
303	}
304
305	$this->range = $range;
306	$this->autoFilter->setRange($range);
307
308	// Discard any column rules that are no longer valid within this range
309	[$rangeStart, $rangeEnd] = Coordinate::rangeBoundaries($this->range);
310	foreach ($this->columns as $key => $value) {
311	$colIndex = Coordinate::columnIndexFromString($key);
312	if (($rangeStart[0] > $colIndex) \|\| ($rangeEnd[0] < $colIndex)) {
313	unset($this->columns[$key]);
314	}
315	}
316
317	return $this;
318	}
319
320	/**
321	* Set Table Cell Range to max row.
322	*/
323	public function setRangeToMaxRow(): self
324	{
325	if ($this->workSheet !== null) {
326	$thisrange = $this->range;
327	$range = (string) preg_replace('/\d+$/', (string) $this->workSheet->getHighestRow(), $thisrange);
328	if ($range !== $thisrange) {
329	$this->setRange($range);
330	}
331	}
332
333	return $this;
334	}
335
336	/**
337	* Get Table's Worksheet.
338	*/
339	public function getWorksheet(): ?Worksheet
340	{
341	return $this->workSheet;
342	}
343
344	/**
345	* Set Table's Worksheet.
346	*/
347	public function setWorksheet(?Worksheet $worksheet = null): self
348	{
349	if ($this->name !== '' && $worksheet !== null) {
350	$spreadsheet = $worksheet->getParentOrThrow();
351	$tableName = StringHelper::strToUpper($this->name);
352
353	foreach ($spreadsheet->getWorksheetIterator() as $sheet) {
354	foreach ($sheet->getTableCollection() as $table) {
355	if (StringHelper::strToUpper($table->getName()) === $tableName) {
356	throw new PhpSpreadsheetException("Workbook already contains a table named '{$this->name}'");
357	}
358	}
359	}
360	}
361
362	$this->workSheet = $worksheet;
363	$this->autoFilter->setParent($worksheet);
364
365	return $this;
366	}
367
368	/**
369	* Get all Table Columns.
370	*
371	* @return Table\Column[]
372	*/
373	public function getColumns(): array
374	{
375	return $this->columns;
376	}
377
378	/**
379	* Validate that the specified column is in the Table range.
380	*
381	* @param string $column Column name (e.g. A)
382	*
383	* @return int The column offset within the table range
384	*/
385	public function isColumnInRange(string $column): int
386	{
387	if (empty($this->range)) {
388	throw new PhpSpreadsheetException('No table range is defined.');
389	}
390
391	$columnIndex = Coordinate::columnIndexFromString($column);
392	[$rangeStart, $rangeEnd] = Coordinate::rangeBoundaries($this->range);
393	if (($rangeStart[0] > $columnIndex) \|\| ($rangeEnd[0] < $columnIndex)) {
394	throw new PhpSpreadsheetException('Column is outside of current table range.');
395	}
396
397	return $columnIndex - $rangeStart[0];
398	}
399
400	/**
401	* Get a specified Table Column Offset within the defined Table range.
402	*
403	* @param string $column Column name (e.g. A)
404	*
405	* @return int The offset of the specified column within the table range
406	*/
407	public function getColumnOffset($column): int
408	{
409	return $this->isColumnInRange($column);
410	}
411
412	/**
413	* Get a specified Table Column.
414	*
415	* @param string $column Column name (e.g. A)
416	*/
417	public function getColumn($column): Table\Column
418	{
419	$this->isColumnInRange($column);
420
421	if (!isset($this->columns[$column])) {
422	$this->columns[$column] = new Table\Column($column, $this);
423	}
424
425	return $this->columns[$column];
426	}
427
428	/**
429	* Get a specified Table Column by it's offset.
430	*
431	* @param int $columnOffset Column offset within range (starting from 0)
432	*/
433	public function getColumnByOffset($columnOffset): Table\Column
434	{
435	[$rangeStart, $rangeEnd] = Coordinate::rangeBoundaries($this->range);
436	$pColumn = Coordinate::stringFromColumnIndex($rangeStart[0] + $columnOffset);
437
438	return $this->getColumn($pColumn);
439	}
440
441	/**
442	* Set Table.
443	*
444	* @param string\|Table\Column $columnObjectOrString
445	* A simple string containing a Column ID like 'A' is permitted
446	*/
447	public function setColumn($columnObjectOrString): self
448	{
449	if ((is_string($columnObjectOrString)) && (!empty($columnObjectOrString))) {
450	$column = $columnObjectOrString;
451	} elseif (is_object($columnObjectOrString) && ($columnObjectOrString instanceof Table\Column)) {
452	$column = $columnObjectOrString->getColumnIndex();
453	} else {
454	throw new PhpSpreadsheetException('Column is not within the table range.');
455	}
456	$this->isColumnInRange($column);
457
458	if (is_string($columnObjectOrString)) {
459	$this->columns[$columnObjectOrString] = new Table\Column($columnObjectOrString, $this);
460	} else {
461	$columnObjectOrString->setTable($this);
462	$this->columns[$column] = $columnObjectOrString;
463	}
464	ksort($this->columns);
465
466	return $this;
467	}
468
469	/**
470	* Clear a specified Table Column.
471	*
472	* @param string $column Column name (e.g. A)
473	*/
474	public function clearColumn($column): self
475	{
476	$this->isColumnInRange($column);
477
478	if (isset($this->columns[$column])) {
479	unset($this->columns[$column]);
480	}
481
482	return $this;
483	}
484
485	/**
486	* Shift an Table Column Rule to a different column.
487	*
488	* Note: This method bypasses validation of the destination column to ensure it is within this Table range.
489	* Nor does it verify whether any column rule already exists at $toColumn, but will simply override any existing value.
490	* Use with caution.
491	*
492	* @param string $fromColumn Column name (e.g. A)
493	* @param string $toColumn Column name (e.g. B)
494	*/
495	public function shiftColumn($fromColumn, $toColumn): self
496	{
497	$fromColumn = strtoupper($fromColumn);
498	$toColumn = strtoupper($toColumn);
499
500	if (($fromColumn !== null) && (isset($this->columns[$fromColumn])) && ($toColumn !== null)) {
501	$this->columns[$fromColumn]->setTable();
502	$this->columns[$fromColumn]->setColumnIndex($toColumn);
503	$this->columns[$toColumn] = $this->columns[$fromColumn];
504	$this->columns[$toColumn]->setTable($this);
505	unset($this->columns[$fromColumn]);
506
507	ksort($this->columns);
508	}
509
510	return $this;
511	}
512
513	/**
514	* Get table Style.
515	*/
516	public function getStyle(): Table\TableStyle
517	{
518	return $this->style;
519	}
520
521	/**
522	* Set table Style.
523	*/
524	public function setStyle(TableStyle $style): self
525	{
526	$this->style = $style;
527
528	return $this;
529	}
530
531	/**
532	* Get AutoFilter.
533	*/
534	public function getAutoFilter(): AutoFilter
535	{
536	return $this->autoFilter;
537	}
538
539	/**
540	* Set AutoFilter.
541	*/
542	public function setAutoFilter(AutoFilter $autoFilter): self
543	{
544	$this->autoFilter = $autoFilter;
545
546	return $this;
547	}
548
549	/**
550	* Implement PHP __clone to create a deep clone, not just a shallow copy.
551	*/
552	public function __clone()
553	{
554	$vars = get_object_vars($this);
555	foreach ($vars as $key => $value) {
556	if (is_object($value)) {
557	if ($key === 'workSheet') {
558	// Detach from worksheet
559	$this->{$key} = null;
560	} else {
561	$this->{$key} = clone $value;
562	}
563	} elseif ((is_array($value)) && ($key === 'columns')) {
564	// The columns array of \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet\Table objects
565	$this->{$key} = [];
566	foreach ($value as $k => $v) {
567	$this->{$key}[$k] = clone $v;
568	// attach the new cloned Column to this new cloned Table object
569	$this->{$key}[$k]->setTable($this);
570	}
571	} else {
572	$this->{$key} = $value;
573	}
574	}
575	}
576
577	/**
578	* toString method replicates previous behavior by returning the range if object is
579	* referenced as a property of its worksheet.
580	*/
581	public function __toString()
582	{
583	return (string) $this->range;
584	}
585	}
586