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

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

3,782 lines 118.8 KB

Raw Download Zip

1	<?php
2
3	namespace PhpOffice\PhpSpreadsheet\Worksheet;
4
5	use ArrayObject;
6	use Composer\Pcre\Preg;
7	use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
8	use PhpOffice\PhpSpreadsheet\Calculation\Functions;
9	use PhpOffice\PhpSpreadsheet\Cell\AddressRange;
10	use PhpOffice\PhpSpreadsheet\Cell\Cell;
11	use PhpOffice\PhpSpreadsheet\Cell\CellAddress;
12	use PhpOffice\PhpSpreadsheet\Cell\CellRange;
13	use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
14	use PhpOffice\PhpSpreadsheet\Cell\DataType;
15	use PhpOffice\PhpSpreadsheet\Cell\DataValidation;
16	use PhpOffice\PhpSpreadsheet\Cell\Hyperlink;
17	use PhpOffice\PhpSpreadsheet\Cell\IValueBinder;
18	use PhpOffice\PhpSpreadsheet\Chart\Chart;
19	use PhpOffice\PhpSpreadsheet\Collection\Cells;
20	use PhpOffice\PhpSpreadsheet\Collection\CellsFactory;
21	use PhpOffice\PhpSpreadsheet\Comment;
22	use PhpOffice\PhpSpreadsheet\DefinedName;
23	use PhpOffice\PhpSpreadsheet\Exception;
24	use PhpOffice\PhpSpreadsheet\ReferenceHelper;
25	use PhpOffice\PhpSpreadsheet\RichText\RichText;
26	use PhpOffice\PhpSpreadsheet\Shared;
27	use PhpOffice\PhpSpreadsheet\Spreadsheet;
28	use PhpOffice\PhpSpreadsheet\Style\Alignment;
29	use PhpOffice\PhpSpreadsheet\Style\Color;
30	use PhpOffice\PhpSpreadsheet\Style\Conditional;
31	use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
32	use PhpOffice\PhpSpreadsheet\Style\Style;
33
34	class Worksheet
35	{
36	// Break types
37	public const BREAK_NONE = 0;
38	public const BREAK_ROW = 1;
39	public const BREAK_COLUMN = 2;
40	// Maximum column for row break
41	public const BREAK_ROW_MAX_COLUMN = 16383;
42
43	// Sheet state
44	public const SHEETSTATE_VISIBLE = 'visible';
45	public const SHEETSTATE_HIDDEN = 'hidden';
46	public const SHEETSTATE_VERYHIDDEN = 'veryHidden';
47
48	public const MERGE_CELL_CONTENT_EMPTY = 'empty';
49	public const MERGE_CELL_CONTENT_HIDE = 'hide';
50	public const MERGE_CELL_CONTENT_MERGE = 'merge';
51
52	protected const SHEET_NAME_REQUIRES_NO_QUOTES = '/^[_\p{L}][_\p{L}\p{N}]*$/mui';
53
54	/**
55	* Maximum 31 characters allowed for sheet title.
56	*
57	* @var int
58	*/
59	const SHEET_TITLE_MAXIMUM_LENGTH = 31;
60
61	/**
62	* Invalid characters in sheet title.
63	*
64	* @var array
65	*/
66	private static $invalidCharacters = ['*', ':', '/', '\\', '?', '[', ']'];
67
68	/**
69	* Parent spreadsheet.
70	*
71	* @var ?Spreadsheet
72	*/
73	private $parent;
74
75	/**
76	* Collection of cells.
77	*
78	* @var Cells
79	*/
80	private $cellCollection;
81
82	/**
83	* Collection of row dimensions.
84	*
85	* @var RowDimension[]
86	*/
87	private $rowDimensions = [];
88
89	/**
90	* Default row dimension.
91	*
92	* @var RowDimension
93	*/
94	private $defaultRowDimension;
95
96	/**
97	* Collection of column dimensions.
98	*
99	* @var ColumnDimension[]
100	*/
101	private $columnDimensions = [];
102
103	/**
104	* Default column dimension.
105	*
106	* @var ColumnDimension
107	*/
108	private $defaultColumnDimension;
109
110	/**
111	* Collection of drawings.
112	*
113	* @var ArrayObject<int, BaseDrawing>
114	*/
115	private $drawingCollection;
116
117	/**
118	* Collection of Chart objects.
119	*
120	* @var ArrayObject<int, Chart>
121	*/
122	private $chartCollection;
123
124	/**
125	* Collection of Table objects.
126	*
127	* @var ArrayObject<int, Table>
128	*/
129	private $tableCollection;
130
131	/**
132	* Worksheet title.
133	*
134	* @var string
135	*/
136	private $title;
137
138	/**
139	* Sheet state.
140	*
141	* @var string
142	*/
143	private $sheetState;
144
145	/**
146	* Page setup.
147	*
148	* @var PageSetup
149	*/
150	private $pageSetup;
151
152	/**
153	* Page margins.
154	*
155	* @var PageMargins
156	*/
157	private $pageMargins;
158
159	/**
160	* Page header/footer.
161	*
162	* @var HeaderFooter
163	*/
164	private $headerFooter;
165
166	/**
167	* Sheet view.
168	*
169	* @var SheetView
170	*/
171	private $sheetView;
172
173	/**
174	* Protection.
175	*
176	* @var Protection
177	*/
178	private $protection;
179
180	/**
181	* Collection of styles.
182	*
183	* @var Style[]
184	*/
185	private $styles = [];
186
187	/**
188	* Conditional styles. Indexed by cell coordinate, e.g. 'A1'.
189	*
190	* @var array
191	*/
192	private $conditionalStylesCollection = [];
193
194	/**
195	* Collection of row breaks.
196	*
197	* @var PageBreak[]
198	*/
199	private $rowBreaks = [];
200
201	/**
202	* Collection of column breaks.
203	*
204	* @var PageBreak[]
205	*/
206	private $columnBreaks = [];
207
208	/**
209	* Collection of merged cell ranges.
210	*
211	* @var string[]
212	*/
213	private $mergeCells = [];
214
215	/**
216	* Collection of protected cell ranges.
217	*
218	* @var string[]
219	*/
220	private $protectedCells = [];
221
222	/**
223	* Autofilter Range and selection.
224	*
225	* @var AutoFilter
226	*/
227	private $autoFilter;
228
229	/**
230	* Freeze pane.
231	*
232	* @var null\|string
233	*/
234	private $freezePane;
235
236	/**
237	* Default position of the right bottom pane.
238	*
239	* @var null\|string
240	*/
241	private $topLeftCell;
242
243	/**
244	* Show gridlines?
245	*
246	* @var bool
247	*/
248	private $showGridlines = true;
249
250	/**
251	* Print gridlines?
252	*
253	* @var bool
254	*/
255	private $printGridlines = false;
256
257	/**
258	* Show row and column headers?
259	*
260	* @var bool
261	*/
262	private $showRowColHeaders = true;
263
264	/**
265	* Show summary below? (Row/Column outline).
266	*
267	* @var bool
268	*/
269	private $showSummaryBelow = true;
270
271	/**
272	* Show summary right? (Row/Column outline).
273	*
274	* @var bool
275	*/
276	private $showSummaryRight = true;
277
278	/**
279	* Collection of comments.
280	*
281	* @var Comment[]
282	*/
283	private $comments = [];
284
285	/**
286	* Active cell. (Only one!).
287	*
288	* @var string
289	*/
290	private $activeCell = 'A1';
291
292	/**
293	* Selected cells.
294	*
295	* @var string
296	*/
297	private $selectedCells = 'A1';
298
299	/**
300	* Cached highest column.
301	*
302	* @var int
303	*/
304	private $cachedHighestColumn = 1;
305
306	/**
307	* Cached highest row.
308	*
309	* @var int
310	*/
311	private $cachedHighestRow = 1;
312
313	/**
314	* Right-to-left?
315	*
316	* @var bool
317	*/
318	private $rightToLeft = false;
319
320	/**
321	* Hyperlinks. Indexed by cell coordinate, e.g. 'A1'.
322	*
323	* @var array
324	*/
325	private $hyperlinkCollection = [];
326
327	/**
328	* Data validation objects. Indexed by cell coordinate, e.g. 'A1'.
329	*
330	* @var array
331	*/
332	private $dataValidationCollection = [];
333
334	/**
335	* Tab color.
336	*
337	* @var null\|Color
338	*/
339	private $tabColor;
340
341	/**
342	* Hash.
343	*
344	* @var int
345	*/
346	private $hash;
347
348	/**
349	* CodeName.
350	*
351	* @var string
352	*/
353	private $codeName;
354
355	/**
356	* Create a new worksheet.
357	*
358	* @param string $title
359	*/
360	public function __construct(?Spreadsheet $parent = null, $title = 'Worksheet')
361	{
362	// Set parent and title
363	$this->parent = $parent;
364	$this->hash = spl_object_id($this);
365	$this->setTitle($title, false);
366	// setTitle can change $pTitle
367	$this->setCodeName($this->getTitle());
368	$this->setSheetState(self::SHEETSTATE_VISIBLE);
369
370	$this->cellCollection = CellsFactory::getInstance($this);
371	// Set page setup
372	$this->pageSetup = new PageSetup();
373	// Set page margins
374	$this->pageMargins = new PageMargins();
375	// Set page header/footer
376	$this->headerFooter = new HeaderFooter();
377	// Set sheet view
378	$this->sheetView = new SheetView();
379	// Drawing collection
380	$this->drawingCollection = new ArrayObject();
381	// Chart collection
382	$this->chartCollection = new ArrayObject();
383	// Protection
384	$this->protection = new Protection();
385	// Default row dimension
386	$this->defaultRowDimension = new RowDimension(null);
387	// Default column dimension
388	$this->defaultColumnDimension = new ColumnDimension(null);
389	// AutoFilter
390	$this->autoFilter = new AutoFilter('', $this);
391	// Table collection
392	$this->tableCollection = new ArrayObject();
393	}
394
395	/**
396	* Disconnect all cells from this Worksheet object,
397	* typically so that the worksheet object can be unset.
398	*/
399	public function disconnectCells(): void
400	{
401	if ($this->cellCollection !== null) {
402	$this->cellCollection->unsetWorksheetCells();
403	// @phpstan-ignore-next-line
404	$this->cellCollection = null;
405	}
406	// detach ourself from the workbook, so that it can then delete this worksheet successfully
407	$this->parent = null;
408	}
409
410	/**
411	* Code to execute when this worksheet is unset().
412	*/
413	public function __destruct()
414	{
415	Calculation::getInstance($this->parent)->clearCalculationCacheForWorksheet($this->title);
416
417	$this->disconnectCells();
418	$this->rowDimensions = [];
419	}
420
421	public function __wakeup(): void
422	{
423	$this->hash = spl_object_id($this);
424	}
425
426	/**
427	* Return the cell collection.
428	*
429	* @return Cells
430	*/
431	public function getCellCollection()
432	{
433	return $this->cellCollection;
434	}
435
436	/**
437	* Get array of invalid characters for sheet title.
438	*
439	* @return array
440	*/
441	public static function getInvalidCharacters()
442	{
443	return self::$invalidCharacters;
444	}
445
446	/**
447	* Check sheet code name for valid Excel syntax.
448	*
449	* @param string $sheetCodeName The string to check
450	*
451	* @return string The valid string
452	*/
453	private static function checkSheetCodeName($sheetCodeName)
454	{
455	$charCount = Shared\StringHelper::countCharacters($sheetCodeName);
456	if ($charCount == 0) {
457	throw new Exception('Sheet code name cannot be empty.');
458	}
459	// Some of the printable ASCII characters are invalid: * : / \ ? [ ] and first and last characters cannot be a "'"
460	if (
461	(str_replace(self::$invalidCharacters, '', $sheetCodeName) !== $sheetCodeName) \|\|
462	(Shared\StringHelper::substring($sheetCodeName, -1, 1) == '\'') \|\|
463	(Shared\StringHelper::substring($sheetCodeName, 0, 1) == '\'')
464	) {
465	throw new Exception('Invalid character found in sheet code name');
466	}
467
468	// Enforce maximum characters allowed for sheet title
469	if ($charCount > self::SHEET_TITLE_MAXIMUM_LENGTH) {
470	throw new Exception('Maximum ' . self::SHEET_TITLE_MAXIMUM_LENGTH . ' characters allowed in sheet code name.');
471	}
472
473	return $sheetCodeName;
474	}
475
476	/**
477	* Check sheet title for valid Excel syntax.
478	*
479	* @param string $sheetTitle The string to check
480	*
481	* @return string The valid string
482	*/
483	private static function checkSheetTitle($sheetTitle)
484	{
485	// Some of the printable ASCII characters are invalid: * : / \ ? [ ]
486	if (str_replace(self::$invalidCharacters, '', $sheetTitle) !== $sheetTitle) {
487	throw new Exception('Invalid character found in sheet title');
488	}
489
490	// Enforce maximum characters allowed for sheet title
491	if (Shared\StringHelper::countCharacters($sheetTitle) > self::SHEET_TITLE_MAXIMUM_LENGTH) {
492	throw new Exception('Maximum ' . self::SHEET_TITLE_MAXIMUM_LENGTH . ' characters allowed in sheet title.');
493	}
494
495	return $sheetTitle;
496	}
497
498	/**
499	* Get a sorted list of all cell coordinates currently held in the collection by row and column.
500	*
501	* @param bool $sorted Also sort the cell collection?
502	*
503	* @return string[]
504	*/
505	public function getCoordinates($sorted = true)
506	{
507	if ($this->cellCollection == null) {
508	return [];
509	}
510
511	if ($sorted) {
512	return $this->cellCollection->getSortedCoordinates();
513	}
514
515	return $this->cellCollection->getCoordinates();
516	}
517
518	/**
519	* Get collection of row dimensions.
520	*
521	* @return RowDimension[]
522	*/
523	public function getRowDimensions()
524	{
525	return $this->rowDimensions;
526	}
527
528	/**
529	* Get default row dimension.
530	*
531	* @return RowDimension
532	*/
533	public function getDefaultRowDimension()
534	{
535	return $this->defaultRowDimension;
536	}
537
538	/**
539	* Get collection of column dimensions.
540	*
541	* @return ColumnDimension[]
542	*/
543	public function getColumnDimensions()
544	{
545	/** @var callable */
546	$callable = [self::class, 'columnDimensionCompare'];
547	uasort($this->columnDimensions, $callable);
548
549	return $this->columnDimensions;
550	}
551
552	private static function columnDimensionCompare(ColumnDimension $a, ColumnDimension $b): int
553	{
554	return $a->getColumnNumeric() - $b->getColumnNumeric();
555	}
556
557	/**
558	* Get default column dimension.
559	*
560	* @return ColumnDimension
561	*/
562	public function getDefaultColumnDimension()
563	{
564	return $this->defaultColumnDimension;
565	}
566
567	/**
568	* Get collection of drawings.
569	*
570	* @return ArrayObject<int, BaseDrawing>
571	*/
572	public function getDrawingCollection()
573	{
574	return $this->drawingCollection;
575	}
576
577	/**
578	* Get collection of charts.
579	*
580	* @return ArrayObject<int, Chart>
581	*/
582	public function getChartCollection()
583	{
584	return $this->chartCollection;
585	}
586
587	/**
588	* Add chart.
589	*
590	* @param null\|int $chartIndex Index where chart should go (0,1,..., or null for last)
591	*
592	* @return Chart
593	*/
594	public function addChart(Chart $chart, $chartIndex = null)
595	{
596	$chart->setWorksheet($this);
597	if ($chartIndex === null) {
598	$this->chartCollection[] = $chart;
599	} else {
600	// Insert the chart at the requested index
601	// @phpstan-ignore-next-line
602	array_splice(/** @scrutinizer ignore-type */ $this->chartCollection, $chartIndex, 0, [$chart]);
603	}
604
605	return $chart;
606	}
607
608	/**
609	* Return the count of charts on this worksheet.
610	*
611	* @return int The number of charts
612	*/
613	public function getChartCount()
614	{
615	return count($this->chartCollection);
616	}
617
618	/**
619	* Get a chart by its index position.
620	*
621	* @param ?string $index Chart index position
622	*
623	* @return Chart\|false
624	*/
625	public function getChartByIndex($index)
626	{
627	$chartCount = count($this->chartCollection);
628	if ($chartCount == 0) {
629	return false;
630	}
631	if ($index === null) {
632	$index = --$chartCount;
633	}
634	if (!isset($this->chartCollection[$index])) {
635	return false;
636	}
637
638	return $this->chartCollection[$index];
639	}
640
641	/**
642	* Return an array of the names of charts on this worksheet.
643	*
644	* @return string[] The names of charts
645	*/
646	public function getChartNames()
647	{
648	$chartNames = [];
649	foreach ($this->chartCollection as $chart) {
650	$chartNames[] = $chart->getName();
651	}
652
653	return $chartNames;
654	}
655
656	/**
657	* Get a chart by name.
658	*
659	* @param string $chartName Chart name
660	*
661	* @return Chart\|false
662	*/
663	public function getChartByName($chartName)
664	{
665	foreach ($this->chartCollection as $index => $chart) {
666	if ($chart->getName() == $chartName) {
667	return $chart;
668	}
669	}
670
671	return false;
672	}
673
674	/**
675	* Refresh column dimensions.
676	*
677	* @return $this
678	*/
679	public function refreshColumnDimensions()
680	{
681	$newColumnDimensions = [];
682	foreach ($this->getColumnDimensions() as $objColumnDimension) {
683	$newColumnDimensions[$objColumnDimension->getColumnIndex()] = $objColumnDimension;
684	}
685
686	$this->columnDimensions = $newColumnDimensions;
687
688	return $this;
689	}
690
691	/**
692	* Refresh row dimensions.
693	*
694	* @return $this
695	*/
696	public function refreshRowDimensions()
697	{
698	$newRowDimensions = [];
699	foreach ($this->getRowDimensions() as $objRowDimension) {
700	$newRowDimensions[$objRowDimension->getRowIndex()] = $objRowDimension;
701	}
702
703	$this->rowDimensions = $newRowDimensions;
704
705	return $this;
706	}
707
708	/**
709	* Calculate worksheet dimension.
710	*
711	* @return string String containing the dimension of this worksheet
712	*/
713	public function calculateWorksheetDimension()
714	{
715	// Return
716	return 'A1:' . $this->getHighestColumn() . $this->getHighestRow();
717	}
718
719	/**
720	* Calculate worksheet data dimension.
721	*
722	* @return string String containing the dimension of this worksheet that actually contain data
723	*/
724	public function calculateWorksheetDataDimension()
725	{
726	// Return
727	return 'A1:' . $this->getHighestDataColumn() . $this->getHighestDataRow();
728	}
729
730	/**
731	* Calculate widths for auto-size columns.
732	*
733	* @return $this
734	*/
735	public function calculateColumnWidths()
736	{
737	// initialize $autoSizes array
738	$autoSizes = [];
739	foreach ($this->getColumnDimensions() as $colDimension) {
740	if ($colDimension->getAutoSize()) {
741	$autoSizes[$colDimension->getColumnIndex()] = -1;
742	}
743	}
744
745	// There is only something to do if there are some auto-size columns
746	if (!empty($autoSizes)) {
747	// build list of cells references that participate in a merge
748	$isMergeCell = [];
749	foreach ($this->getMergeCells() as $cells) {
750	foreach (Coordinate::extractAllCellReferencesInRange($cells) as $cellReference) {
751	$isMergeCell[$cellReference] = true;
752	}
753	}
754
755	$autoFilterIndentRanges = (new AutoFit($this))->getAutoFilterIndentRanges();
756
757	// loop through all cells in the worksheet
758	foreach ($this->getCoordinates(false) as $coordinate) {
759	$cell = $this->getCellOrNull($coordinate);
760
761	if ($cell !== null && isset($autoSizes[$this->cellCollection->getCurrentColumn()])) {
762	//Determine if cell is in merge range
763	$isMerged = isset($isMergeCell[$this->cellCollection->getCurrentCoordinate()]);
764
765	//By default merged cells should be ignored
766	$isMergedButProceed = false;
767
768	//The only exception is if it's a merge range value cell of a 'vertical' range (1 column wide)
769	if ($isMerged && $cell->isMergeRangeValueCell()) {
770	$range = (string) $cell->getMergeRange();
771	$rangeBoundaries = Coordinate::rangeDimension($range);
772	if ($rangeBoundaries[0] === 1) {
773	$isMergedButProceed = true;
774	}
775	}
776
777	// Determine width if cell is not part of a merge or does and is a value cell of 1-column wide range
778	if (!$isMerged \|\| $isMergedButProceed) {
779	// Determine if we need to make an adjustment for the first row in an AutoFilter range that
780	// has a column filter dropdown
781	$filterAdjustment = false;
782	if (!empty($autoFilterIndentRanges)) {
783	foreach ($autoFilterIndentRanges as $autoFilterFirstRowRange) {
784	if ($cell->isInRange($autoFilterFirstRowRange)) {
785	$filterAdjustment = true;
786
787	break;
788	}
789	}
790	}
791
792	$indentAdjustment = $cell->getStyle()->getAlignment()->getIndent();
793	$indentAdjustment += (int) ($cell->getStyle()->getAlignment()->getHorizontal() === Alignment::HORIZONTAL_CENTER);
794
795	// Calculated value
796	// To formatted string
797	$cellValue = NumberFormat::toFormattedString(
798	$cell->getCalculatedValue(),
799	(string) $this->getParentOrThrow()->getCellXfByIndex($cell->getXfIndex())
800	->getNumberFormat()->getFormatCode()
801	);
802
803	if ($cellValue !== null && $cellValue !== '') {
804	$autoSizes[$this->cellCollection->getCurrentColumn()] = max(
805	$autoSizes[$this->cellCollection->getCurrentColumn()],
806	round(
807	Shared\Font::calculateColumnWidth(
808	$this->getParentOrThrow()->getCellXfByIndex($cell->getXfIndex())->getFont(),
809	$cellValue,
810	(int) $this->getParentOrThrow()->getCellXfByIndex($cell->getXfIndex())
811	->getAlignment()->getTextRotation(),
812	$this->getParentOrThrow()->getDefaultStyle()->getFont(),
813	$filterAdjustment,
814	$indentAdjustment
815	),
816	3
817	)
818	);
819	}
820	}
821	}
822	}
823
824	// adjust column widths
825	foreach ($autoSizes as $columnIndex => $width) {
826	if ($width == -1) {
827	$width = $this->getDefaultColumnDimension()->getWidth();
828	}
829	$this->getColumnDimension($columnIndex)->setWidth($width);
830	}
831	}
832
833	return $this;
834	}
835
836	/**
837	* Get parent or null.
838	*/
839	public function getParent(): ?Spreadsheet
840	{
841	return $this->parent;
842	}
843
844	/**
845	* Get parent, throw exception if null.
846	*/
847	public function getParentOrThrow(): Spreadsheet
848	{
849	if ($this->parent !== null) {
850	return $this->parent;
851	}
852
853	throw new Exception('Sheet does not have a parent.');
854	}
855
856	/**
857	* Re-bind parent.
858	*
859	* @return $this
860	*/
861	public function rebindParent(Spreadsheet $parent)
862	{
863	if ($this->parent !== null) {
864	$definedNames = $this->parent->getDefinedNames();
865	foreach ($definedNames as $definedName) {
866	$parent->addDefinedName($definedName);
867	}
868
869	$this->parent->removeSheetByIndex(
870	$this->parent->getIndex($this)
871	);
872	}
873	$this->parent = $parent;
874
875	return $this;
876	}
877
878	/**
879	* Get title.
880	*
881	* @return string
882	*/
883	public function getTitle()
884	{
885	return $this->title;
886	}
887
888	/**
889	* Set title.
890	*
891	* @param string $title String containing the dimension of this worksheet
892	* @param bool $updateFormulaCellReferences Flag indicating whether cell references in formulae should
893	* be updated to reflect the new sheet name.
894	* This should be left as the default true, unless you are
895	* certain that no formula cells on any worksheet contain
896	* references to this worksheet
897	* @param bool $validate False to skip validation of new title. WARNING: This should only be set
898	* at parse time (by Readers), where titles can be assumed to be valid.
899	*
900	* @return $this
901	*/
902	public function setTitle($title, $updateFormulaCellReferences = true, $validate = true)
903	{
904	// Is this a 'rename' or not?
905	if ($this->getTitle() == $title) {
906	return $this;
907	}
908
909	// Old title
910	$oldTitle = $this->getTitle();
911
912	if ($validate) {
913	// Syntax check
914	self::checkSheetTitle($title);
915
916	if ($this->parent && $this->parent->getIndex($this, true) >= 0) {
917	// Is there already such sheet name?
918	if ($this->parent->sheetNameExists($title)) {
919	// Use name, but append with lowest possible integer
920
921	if (Shared\StringHelper::countCharacters($title) > 29) {
922	$title = Shared\StringHelper::substring($title, 0, 29);
923	}
924	$i = 1;
925	while ($this->parent->sheetNameExists($title . ' ' . $i)) {
926	++$i;
927	if ($i == 10) {
928	if (Shared\StringHelper::countCharacters($title) > 28) {
929	$title = Shared\StringHelper::substring($title, 0, 28);
930	}
931	} elseif ($i == 100) {
932	if (Shared\StringHelper::countCharacters($title) > 27) {
933	$title = Shared\StringHelper::substring($title, 0, 27);
934	}
935	}
936	}
937
938	$title .= " $i";
939	}
940	}
941	}
942
943	// Set title
944	$this->title = $title;
945
946	if ($this->parent && $this->parent->getIndex($this, true) >= 0 && $this->parent->getCalculationEngine()) {
947	// New title
948	$newTitle = $this->getTitle();
949	$this->parent->getCalculationEngine()
950	->renameCalculationCacheForWorksheet($oldTitle, $newTitle);
951	if ($updateFormulaCellReferences) {
952	ReferenceHelper::getInstance()->updateNamedFormulae($this->parent, $oldTitle, $newTitle);
953	}
954	}
955
956	return $this;
957	}
958
959	/**
960	* Get sheet state.
961	*
962	* @return string Sheet state (visible, hidden, veryHidden)
963	*/
964	public function getSheetState()
965	{
966	return $this->sheetState;
967	}
968
969	/**
970	* Set sheet state.
971	*
972	* @param string $value Sheet state (visible, hidden, veryHidden)
973	*
974	* @return $this
975	*/
976	public function setSheetState($value)
977	{
978	$this->sheetState = $value;
979
980	return $this;
981	}
982
983	/**
984	* Get page setup.
985	*
986	* @return PageSetup
987	*/
988	public function getPageSetup()
989	{
990	return $this->pageSetup;
991	}
992
993	/**
994	* Set page setup.
995	*
996	* @return $this
997	*/
998	public function setPageSetup(PageSetup $pageSetup)
999	{
1000	$this->pageSetup = $pageSetup;
1001
1002	return $this;
1003	}
1004
1005	/**
1006	* Get page margins.
1007	*
1008	* @return PageMargins
1009	*/
1010	public function getPageMargins()
1011	{
1012	return $this->pageMargins;
1013	}
1014
1015	/**
1016	* Set page margins.
1017	*
1018	* @return $this
1019	*/
1020	public function setPageMargins(PageMargins $pageMargins)
1021	{
1022	$this->pageMargins = $pageMargins;
1023
1024	return $this;
1025	}
1026
1027	/**
1028	* Get page header/footer.
1029	*
1030	* @return HeaderFooter
1031	*/
1032	public function getHeaderFooter()
1033	{
1034	return $this->headerFooter;
1035	}
1036
1037	/**
1038	* Set page header/footer.
1039	*
1040	* @return $this
1041	*/
1042	public function setHeaderFooter(HeaderFooter $headerFooter)
1043	{
1044	$this->headerFooter = $headerFooter;
1045
1046	return $this;
1047	}
1048
1049	/**
1050	* Get sheet view.
1051	*
1052	* @return SheetView
1053	*/
1054	public function getSheetView()
1055	{
1056	return $this->sheetView;
1057	}
1058
1059	/**
1060	* Set sheet view.
1061	*
1062	* @return $this
1063	*/
1064	public function setSheetView(SheetView $sheetView)
1065	{
1066	$this->sheetView = $sheetView;
1067
1068	return $this;
1069	}
1070
1071	/**
1072	* Get Protection.
1073	*
1074	* @return Protection
1075	*/
1076	public function getProtection()
1077	{
1078	return $this->protection;
1079	}
1080
1081	/**
1082	* Set Protection.
1083	*
1084	* @return $this
1085	*/
1086	public function setProtection(Protection $protection)
1087	{
1088	$this->protection = $protection;
1089
1090	return $this;
1091	}
1092
1093	/**
1094	* Get highest worksheet column.
1095	*
1096	* @param null\|int\|string $row Return the data highest column for the specified row,
1097	* or the highest column of any row if no row number is passed
1098	*
1099	* @return string Highest column name
1100	*/
1101	public function getHighestColumn($row = null)
1102	{
1103	if ($row === null) {
1104	return Coordinate::stringFromColumnIndex($this->cachedHighestColumn);
1105	}
1106
1107	return $this->getHighestDataColumn($row);
1108	}
1109
1110	/**
1111	* Get highest worksheet column that contains data.
1112	*
1113	* @param null\|int\|string $row Return the highest data column for the specified row,
1114	* or the highest data column of any row if no row number is passed
1115	*
1116	* @return string Highest column name that contains data
1117	*/
1118	public function getHighestDataColumn($row = null)
1119	{
1120	return $this->cellCollection->getHighestColumn($row);
1121	}
1122
1123	/**
1124	* Get highest worksheet row.
1125	*
1126	* @param null\|string $column Return the highest data row for the specified column,
1127	* or the highest row of any column if no column letter is passed
1128	*
1129	* @return int Highest row number
1130	*/
1131	public function getHighestRow($column = null)
1132	{
1133	if ($column === null) {
1134	return $this->cachedHighestRow;
1135	}
1136
1137	return $this->getHighestDataRow($column);
1138	}
1139
1140	/**
1141	* Get highest worksheet row that contains data.
1142	*
1143	* @param null\|string $column Return the highest data row for the specified column,
1144	* or the highest data row of any column if no column letter is passed
1145	*
1146	* @return int Highest row number that contains data
1147	*/
1148	public function getHighestDataRow($column = null)
1149	{
1150	return $this->cellCollection->getHighestRow($column);
1151	}
1152
1153	/**
1154	* Get highest worksheet column and highest row that have cell records.
1155	*
1156	* @return array Highest column name and highest row number
1157	*/
1158	public function getHighestRowAndColumn()
1159	{
1160	return $this->cellCollection->getHighestRowAndColumn();
1161	}
1162
1163	/**
1164	* Set a cell value.
1165	*
1166	* @param array<int>\|CellAddress\|string $coordinate Coordinate of the cell as a string, eg: 'C5';
1167	* or as an array of [$columnIndex, $row] (e.g. [3, 5]), or a CellAddress object.
1168	* @param mixed $value Value for the cell
1169	* @param null\|IValueBinder $binder Value Binder to override the currently set Value Binder
1170	*
1171	* @return $this
1172	*/
1173	public function setCellValue($coordinate, $value, ?IValueBinder $binder = null)
1174	{
1175	$cellAddress = Functions::trimSheetFromCellReference(Validations::validateCellAddress($coordinate));
1176	$this->getCell($cellAddress)->setValue($value, $binder);
1177
1178	return $this;
1179	}
1180
1181	/**
1182	* Set a cell value by using numeric cell coordinates.
1183	*
1184	* @deprecated 1.23.0
1185	* Use the setCellValue() method with a cell address such as 'C5' instead;,
1186	* or passing in an array of [$columnIndex, $row] (e.g. [3, 5]), or a CellAddress object.
1187	* @see Worksheet::setCellValue()
1188	*
1189	* @param int $columnIndex Numeric column coordinate of the cell
1190	* @param int $row Numeric row coordinate of the cell
1191	* @param mixed $value Value of the cell
1192	* @param null\|IValueBinder $binder Value Binder to override the currently set Value Binder
1193	*
1194	* @return $this
1195	*/
1196	public function setCellValueByColumnAndRow($columnIndex, $row, $value, ?IValueBinder $binder = null)
1197	{
1198	$this->getCell(Coordinate::stringFromColumnIndex($columnIndex) . $row)->setValue($value, $binder);
1199
1200	return $this;
1201	}
1202
1203	/**
1204	* Set a cell value.
1205	*
1206	* @param array<int>\|CellAddress\|string $coordinate Coordinate of the cell as a string, eg: 'C5';
1207	* or as an array of [$columnIndex, $row] (e.g. [3, 5]), or a CellAddress object.
1208	* @param mixed $value Value of the cell
1209	* @param string $dataType Explicit data type, see DataType::TYPE_*
1210	* Note that PhpSpreadsheet does not validate that the value and datatype are consistent, in using this
1211	* method, then it is your responsibility as an end-user developer to validate that the value and
1212	* the datatype match.
1213	* If you do mismatch value and datatpe, then the value you enter may be changed to match the datatype
1214	* that you specify.
1215	*
1216	* @see DataType
1217	*
1218	* @return $this
1219	*/
1220	public function setCellValueExplicit($coordinate, $value, $dataType)
1221	{
1222	$cellAddress = Functions::trimSheetFromCellReference(Validations::validateCellAddress($coordinate));
1223	$this->getCell($cellAddress)->setValueExplicit($value, $dataType);
1224
1225	return $this;
1226	}
1227
1228	/**
1229	* Set a cell value by using numeric cell coordinates.
1230	*
1231	* @deprecated 1.23.0
1232	* Use the setCellValueExplicit() method with a cell address such as 'C5' instead;,
1233	* or passing in an array of [$columnIndex, $row] (e.g. [3, 5]), or a CellAddress object.
1234	* @see Worksheet::setCellValueExplicit()
1235	*
1236	* @param int $columnIndex Numeric column coordinate of the cell
1237	* @param int $row Numeric row coordinate of the cell
1238	* @param mixed $value Value of the cell
1239	* @param string $dataType Explicit data type, see DataType::TYPE_*
1240	* Note that PhpSpreadsheet does not validate that the value and datatype are consistent, in using this
1241	* method, then it is your responsibility as an end-user developer to validate that the value and
1242	* the datatype match.
1243	* If you do mismatch value and datatpe, then the value you enter may be changed to match the datatype
1244	* that you specify.
1245	*
1246	* @see DataType
1247	*
1248	* @return $this
1249	*/
1250	public function setCellValueExplicitByColumnAndRow($columnIndex, $row, $value, $dataType)
1251	{
1252	$this->getCell(Coordinate::stringFromColumnIndex($columnIndex) . $row)->setValueExplicit($value, $dataType);
1253
1254	return $this;
1255	}
1256
1257	/**
1258	* Get cell at a specific coordinate.
1259	*
1260	* @param array<int>\|CellAddress\|string $coordinate Coordinate of the cell as a string, eg: 'C5';
1261	* or as an array of [$columnIndex, $row] (e.g. [3, 5]), or a CellAddress object.
1262	*
1263	* @return Cell Cell that was found or created
1264	* WARNING: Because the cell collection can be cached to reduce memory, it only allows one
1265	* "active" cell at a time in memory. If you assign that cell to a variable, then select
1266	* another cell using getCell() or any of its variants, the newly selected cell becomes
1267	* the "active" cell, and any previous assignment becomes a disconnected reference because
1268	* the active cell has changed.
1269	*/
1270	public function getCell($coordinate): Cell
1271	{
1272	$cellAddress = Functions::trimSheetFromCellReference(Validations::validateCellAddress($coordinate));
1273
1274	// Shortcut for increased performance for the vast majority of simple cases
1275	if ($this->cellCollection->has($cellAddress)) {
1276	/** @var Cell $cell */
1277	$cell = $this->cellCollection->get($cellAddress);
1278
1279	return $cell;
1280	}
1281
1282	/** @var Worksheet $sheet */
1283	[$sheet, $finalCoordinate] = $this->getWorksheetAndCoordinate($cellAddress);
1284	$cell = $sheet->cellCollection->get($finalCoordinate);
1285
1286	return $cell ?? $sheet->createNewCell($finalCoordinate);
1287	}
1288
1289	/**
1290	* Get the correct Worksheet and coordinate from a coordinate that may
1291	* contains reference to another sheet or a named range.
1292	*
1293	* @return array{0: Worksheet, 1: string}
1294	*/
1295	private function getWorksheetAndCoordinate(string $coordinate): array
1296	{
1297	$sheet = null;
1298	$finalCoordinate = null;
1299
1300	// Worksheet reference?
1301	if (strpos($coordinate, '!') !== false) {
1302	$worksheetReference = self::extractSheetTitle($coordinate, true);
1303
1304	$sheet = $this->getParentOrThrow()->getSheetByName($worksheetReference[0]);
1305	$finalCoordinate = strtoupper($worksheetReference[1]);
1306
1307	if ($sheet === null) {
1308	throw new Exception('Sheet not found for name: ' . $worksheetReference[0]);
1309	}
1310	} elseif (
1311	!Preg::isMatch('/^' . Calculation::CALCULATION_REGEXP_CELLREF . '$/i', $coordinate) &&
1312	Preg::isMatch('/^' . Calculation::CALCULATION_REGEXP_DEFINEDNAME . '$/iu', $coordinate)
1313	) {
1314	// Named range?
1315	$namedRange = $this->validateNamedRange($coordinate, true);
1316	if ($namedRange !== null) {
1317	$sheet = $namedRange->getWorksheet();
1318	if ($sheet === null) {
1319	throw new Exception('Sheet not found for named range: ' . $namedRange->getName());
1320	}
1321
1322	/** @phpstan-ignore-next-line */
1323	$cellCoordinate = ltrim(substr($namedRange->getValue(), strrpos($namedRange->getValue(), '!')), '!');
1324	$finalCoordinate = str_replace('$', '', $cellCoordinate);
1325	}
1326	}
1327
1328	if ($sheet === null \|\| $finalCoordinate === null) {
1329	$sheet = $this;
1330	$finalCoordinate = strtoupper($coordinate);
1331	}
1332
1333	if (Coordinate::coordinateIsRange($finalCoordinate)) {
1334	throw new Exception('Cell coordinate string can not be a range of cells.');
1335	} elseif (strpos($finalCoordinate, '$') !== false) {
1336	throw new Exception('Cell coordinate must not be absolute.');
1337	}
1338
1339	return [$sheet, $finalCoordinate];
1340	}
1341
1342	/**
1343	* Get an existing cell at a specific coordinate, or null.
1344	*
1345	* @param string $coordinate Coordinate of the cell, eg: 'A1'
1346	*
1347	* @return null\|Cell Cell that was found or null
1348	*/
1349	private function getCellOrNull($coordinate): ?Cell
1350	{
1351	// Check cell collection
1352	if ($this->cellCollection->has($coordinate)) {
1353	return $this->cellCollection->get($coordinate);
1354	}
1355
1356	return null;
1357	}
1358
1359	/**
1360	* Get cell at a specific coordinate by using numeric cell coordinates.
1361	*
1362	* @deprecated 1.23.0
1363	* Use the getCell() method with a cell address such as 'C5' instead;,
1364	* or passing in an array of [$columnIndex, $row] (e.g. [3, 5]), or a CellAddress object.
1365	* @see Worksheet::getCell()
1366	*
1367	* @param int $columnIndex Numeric column coordinate of the cell
1368	* @param int $row Numeric row coordinate of the cell
1369	*
1370	* @return Cell Cell that was found/created or null
1371	* WARNING: Because the cell collection can be cached to reduce memory, it only allows one
1372	* "active" cell at a time in memory. If you assign that cell to a variable, then select
1373	* another cell using getCell() or any of its variants, the newly selected cell becomes
1374	* the "active" cell, and any previous assignment becomes a disconnected reference because
1375	* the active cell has changed.
1376	*/
1377	public function getCellByColumnAndRow($columnIndex, $row): Cell
1378	{
1379	return $this->getCell(Coordinate::stringFromColumnIndex($columnIndex) . $row);
1380	}
1381
1382	/**
1383	* Create a new cell at the specified coordinate.
1384	*
1385	* @param string $coordinate Coordinate of the cell
1386	*
1387	* @return Cell Cell that was created
1388	* WARNING: Because the cell collection can be cached to reduce memory, it only allows one
1389	* "active" cell at a time in memory. If you assign that cell to a variable, then select
1390	* another cell using getCell() or any of its variants, the newly selected cell becomes
1391	* the "active" cell, and any previous assignment becomes a disconnected reference because
1392	* the active cell has changed.
1393	*/
1394	public function createNewCell($coordinate): Cell
1395	{
1396	[$column, $row, $columnString] = Coordinate::indexesFromString($coordinate);
1397	$cell = new Cell(null, DataType::TYPE_NULL, $this);
1398	$this->cellCollection->add($coordinate, $cell);
1399
1400	// Coordinates
1401	if ($column > $this->cachedHighestColumn) {
1402	$this->cachedHighestColumn = $column;
1403	}
1404	if ($row > $this->cachedHighestRow) {
1405	$this->cachedHighestRow = $row;
1406	}
1407
1408	// Cell needs appropriate xfIndex from dimensions records
1409	// but don't create dimension records if they don't already exist
1410	$rowDimension = $this->rowDimensions[$row] ?? null;
1411	$columnDimension = $this->columnDimensions[$columnString] ?? null;
1412
1413	if ($rowDimension !== null) {
1414	$rowXf = (int) $rowDimension->getXfIndex();
1415	if ($rowXf > 0) {
1416	// then there is a row dimension with explicit style, assign it to the cell
1417	$cell->setXfIndex($rowXf);
1418	}
1419	} elseif ($columnDimension !== null) {
1420	$colXf = (int) $columnDimension->getXfIndex();
1421	if ($colXf > 0) {
1422	// then there is a column dimension, assign it to the cell
1423	$cell->setXfIndex($colXf);
1424	}
1425	}
1426
1427	return $cell;
1428	}
1429
1430	/**
1431	* Does the cell at a specific coordinate exist?
1432	*
1433	* @param array<int>\|CellAddress\|string $coordinate Coordinate of the cell as a string, eg: 'C5';
1434	* or as an array of [$columnIndex, $row] (e.g. [3, 5]), or a CellAddress object.
1435	*/
1436	public function cellExists($coordinate): bool
1437	{
1438	$cellAddress = Validations::validateCellAddress($coordinate);
1439	/** @var Worksheet $sheet */
1440	[$sheet, $finalCoordinate] = $this->getWorksheetAndCoordinate($cellAddress);
1441
1442	return $sheet->cellCollection->has($finalCoordinate);
1443	}
1444
1445	/**
1446	* Cell at a specific coordinate by using numeric cell coordinates exists?
1447	*
1448	* @deprecated 1.23.0
1449	* Use the cellExists() method with a cell address such as 'C5' instead;,
1450	* or passing in an array of [$columnIndex, $row] (e.g. [3, 5]), or a CellAddress object.
1451	* @see Worksheet::cellExists()
1452	*
1453	* @param int $columnIndex Numeric column coordinate of the cell
1454	* @param int $row Numeric row coordinate of the cell
1455	*/
1456	public function cellExistsByColumnAndRow($columnIndex, $row): bool
1457	{
1458	return $this->cellExists(Coordinate::stringFromColumnIndex($columnIndex) . $row);
1459	}
1460
1461	/**
1462	* Get row dimension at a specific row.
1463	*
1464	* @param int $row Numeric index of the row
1465	*/
1466	public function getRowDimension(int $row): RowDimension
1467	{
1468	// Get row dimension
1469	if (!isset($this->rowDimensions[$row])) {
1470	$this->rowDimensions[$row] = new RowDimension($row);
1471
1472	$this->cachedHighestRow = max($this->cachedHighestRow, $row);
1473	}
1474
1475	return $this->rowDimensions[$row];
1476	}
1477
1478	public function rowDimensionExists(int $row): bool
1479	{
1480	return isset($this->rowDimensions[$row]);
1481	}
1482
1483	/**
1484	* Get column dimension at a specific column.
1485	*
1486	* @param string $column String index of the column eg: 'A'
1487	*/
1488	public function getColumnDimension(string $column): ColumnDimension
1489	{
1490	// Uppercase coordinate
1491	$column = strtoupper($column);
1492
1493	// Fetch dimensions
1494	if (!isset($this->columnDimensions[$column])) {
1495	$this->columnDimensions[$column] = new ColumnDimension($column);
1496
1497	$columnIndex = Coordinate::columnIndexFromString($column);
1498	if ($this->cachedHighestColumn < $columnIndex) {
1499	$this->cachedHighestColumn = $columnIndex;
1500	}
1501	}
1502
1503	return $this->columnDimensions[$column];
1504	}
1505
1506	/**
1507	* Get column dimension at a specific column by using numeric cell coordinates.
1508	*
1509	* @param int $columnIndex Numeric column coordinate of the cell
1510	*/
1511	public function getColumnDimensionByColumn(int $columnIndex): ColumnDimension
1512	{
1513	return $this->getColumnDimension(Coordinate::stringFromColumnIndex($columnIndex));
1514	}
1515
1516	/**
1517	* Get styles.
1518	*
1519	* @return Style[]
1520	*/
1521	public function getStyles()
1522	{
1523	return $this->styles;
1524	}
1525
1526	/**
1527	* Get style for cell.
1528	*
1529	* @param AddressRange\|array<int>\|CellAddress\|int\|string $cellCoordinate
1530	* A simple string containing a cell address like 'A1' or a cell range like 'A1:E10'
1531	* or passing in an array of [$fromColumnIndex, $fromRow, $toColumnIndex, $toRow] (e.g. [3, 5, 6, 8]),
1532	* or a CellAddress or AddressRange object.
1533	*/
1534	public function getStyle($cellCoordinate): Style
1535	{
1536	$cellCoordinate = Validations::validateCellOrCellRange($cellCoordinate);
1537
1538	// set this sheet as active
1539	$this->getParentOrThrow()->setActiveSheetIndex($this->getParentOrThrow()->getIndex($this));
1540
1541	// set cell coordinate as active
1542	$this->setSelectedCells($cellCoordinate);
1543
1544	return $this->getParentOrThrow()->getCellXfSupervisor();
1545	}
1546
1547	/**
1548	* Get style for cell by using numeric cell coordinates.
1549	*
1550	* @deprecated 1.23.0
1551	* Use the getStyle() method with a cell address range such as 'C5:F8' instead;,
1552	* or passing in an array of [$fromColumnIndex, $fromRow, $toColumnIndex, $toRow] (e.g. [3, 5, 6, 8]),
1553	* or an AddressRange object.
1554	* @see Worksheet::getStyle()
1555	*
1556	* @param int $columnIndex1 Numeric column coordinate of the cell
1557	* @param int $row1 Numeric row coordinate of the cell
1558	* @param null\|int $columnIndex2 Numeric column coordinate of the range cell
1559	* @param null\|int $row2 Numeric row coordinate of the range cell
1560	*
1561	* @return Style
1562	*/
1563	public function getStyleByColumnAndRow($columnIndex1, $row1, $columnIndex2 = null, $row2 = null)
1564	{
1565	if ($columnIndex2 !== null && $row2 !== null) {
1566	$cellRange = new CellRange(
1567	CellAddress::fromColumnAndRow($columnIndex1, $row1),
1568	CellAddress::fromColumnAndRow($columnIndex2, $row2)
1569	);
1570
1571	return $this->getStyle($cellRange);
1572	}
1573
1574	return $this->getStyle(CellAddress::fromColumnAndRow($columnIndex1, $row1));
1575	}
1576
1577	/**
1578	* Get conditional styles for a cell.
1579	*
1580	* @param string $coordinate eg: 'A1' or 'A1:A3'.
1581	* If a single cell is referenced, then the array of conditional styles will be returned if the cell is
1582	* included in a conditional style range.
1583	* If a range of cells is specified, then the styles will only be returned if the range matches the entire
1584	* range of the conditional.
1585	*
1586	* @return Conditional[]
1587	*/
1588	public function getConditionalStyles(string $coordinate): array
1589	{
1590	$coordinate = strtoupper($coordinate);
1591	if (strpos($coordinate, ':') !== false) {
1592	return $this->conditionalStylesCollection[$coordinate] ?? [];
1593	}
1594
1595	$cell = $this->getCell($coordinate);
1596	foreach (array_keys($this->conditionalStylesCollection) as $conditionalRange) {
1597	if ($cell->isInRange($conditionalRange)) {
1598	return $this->conditionalStylesCollection[$conditionalRange];
1599	}
1600	}
1601
1602	return [];
1603	}
1604
1605	public function getConditionalRange(string $coordinate): ?string
1606	{
1607	$coordinate = strtoupper($coordinate);
1608	$cell = $this->getCell($coordinate);
1609	foreach (array_keys($this->conditionalStylesCollection) as $conditionalRange) {
1610	if ($cell->isInRange($conditionalRange)) {
1611	return $conditionalRange;
1612	}
1613	}
1614
1615	return null;
1616	}
1617
1618	/**
1619	* Do conditional styles exist for this cell?
1620	*
1621	* @param string $coordinate eg: 'A1' or 'A1:A3'.
1622	* If a single cell is specified, then this method will return true if that cell is included in a
1623	* conditional style range.
1624	* If a range of cells is specified, then true will only be returned if the range matches the entire
1625	* range of the conditional.
1626	*/
1627	public function conditionalStylesExists($coordinate): bool
1628	{
1629	$coordinate = strtoupper($coordinate);
1630	if (strpos($coordinate, ':') !== false) {
1631	return isset($this->conditionalStylesCollection[$coordinate]);
1632	}
1633
1634	$cell = $this->getCell($coordinate);
1635	foreach (array_keys($this->conditionalStylesCollection) as $conditionalRange) {
1636	if ($cell->isInRange($conditionalRange)) {
1637	return true;
1638	}
1639	}
1640
1641	return false;
1642	}
1643
1644	/**
1645	* Removes conditional styles for a cell.
1646	*
1647	* @param string $coordinate eg: 'A1'
1648	*
1649	* @return $this
1650	*/
1651	public function removeConditionalStyles($coordinate)
1652	{
1653	unset($this->conditionalStylesCollection[strtoupper($coordinate)]);
1654
1655	return $this;
1656	}
1657
1658	/**
1659	* Get collection of conditional styles.
1660	*
1661	* @return array
1662	*/
1663	public function getConditionalStylesCollection()
1664	{
1665	return $this->conditionalStylesCollection;
1666	}
1667
1668	/**
1669	* Set conditional styles.
1670	*
1671	* @param string $coordinate eg: 'A1'
1672	* @param Conditional[] $styles
1673	*
1674	* @return $this
1675	*/
1676	public function setConditionalStyles($coordinate, $styles)
1677	{
1678	$this->conditionalStylesCollection[strtoupper($coordinate)] = $styles;
1679
1680	return $this;
1681	}
1682
1683	/**
1684	* Duplicate cell style to a range of cells.
1685	*
1686	* Please note that this will overwrite existing cell styles for cells in range!
1687	*
1688	* @param Style $style Cell style to duplicate
1689	* @param string $range Range of cells (i.e. "A1:B10"), or just one cell (i.e. "A1")
1690	*
1691	* @return $this
1692	*/
1693	public function duplicateStyle(Style $style, $range)
1694	{
1695	// Add the style to the workbook if necessary
1696	$workbook = $this->getParentOrThrow();
1697	if ($existingStyle = $workbook->getCellXfByHashCode($style->getHashCode())) {
1698	// there is already such cell Xf in our collection
1699	$xfIndex = $existingStyle->getIndex();
1700	} else {
1701	// we don't have such a cell Xf, need to add
1702	$workbook->addCellXf($style);
1703	$xfIndex = $style->getIndex();
1704	}
1705
1706	// Calculate range outer borders
1707	[$rangeStart, $rangeEnd] = Coordinate::rangeBoundaries($range . ':' . $range);
1708
1709	// Make sure we can loop upwards on rows and columns
1710	if ($rangeStart[0] > $rangeEnd[0] && $rangeStart[1] > $rangeEnd[1]) {
1711	$tmp = $rangeStart;
1712	$rangeStart = $rangeEnd;
1713	$rangeEnd = $tmp;
1714	}
1715
1716	// Loop through cells and apply styles
1717	for ($col = $rangeStart[0]; $col <= $rangeEnd[0]; ++$col) {
1718	for ($row = $rangeStart[1]; $row <= $rangeEnd[1]; ++$row) {
1719	$this->getCell(Coordinate::stringFromColumnIndex($col) . $row)->setXfIndex($xfIndex);
1720	}
1721	}
1722
1723	return $this;
1724	}
1725
1726	/**
1727	* Duplicate conditional style to a range of cells.
1728	*
1729	* Please note that this will overwrite existing cell styles for cells in range!
1730	*
1731	* @param Conditional[] $styles Cell style to duplicate
1732	* @param string $range Range of cells (i.e. "A1:B10"), or just one cell (i.e. "A1")
1733	*
1734	* @return $this
1735	*/
1736	public function duplicateConditionalStyle(array $styles, $range = '')
1737	{
1738	foreach ($styles as $cellStyle) {
1739	if (!($cellStyle instanceof Conditional)) {
1740	throw new Exception('Style is not a conditional style');
1741	}
1742	}
1743
1744	// Calculate range outer borders
1745	[$rangeStart, $rangeEnd] = Coordinate::rangeBoundaries($range . ':' . $range);
1746
1747	// Make sure we can loop upwards on rows and columns
1748	if ($rangeStart[0] > $rangeEnd[0] && $rangeStart[1] > $rangeEnd[1]) {
1749	$tmp = $rangeStart;
1750	$rangeStart = $rangeEnd;
1751	$rangeEnd = $tmp;
1752	}
1753
1754	// Loop through cells and apply styles
1755	for ($col = $rangeStart[0]; $col <= $rangeEnd[0]; ++$col) {
1756	for ($row = $rangeStart[1]; $row <= $rangeEnd[1]; ++$row) {
1757	$this->setConditionalStyles(Coordinate::stringFromColumnIndex($col) . $row, $styles);
1758	}
1759	}
1760
1761	return $this;
1762	}
1763
1764	/**
1765	* Set break on a cell.
1766	*
1767	* @param array<int>\|CellAddress\|string $coordinate Coordinate of the cell as a string, eg: 'C5';
1768	* or as an array of [$columnIndex, $row] (e.g. [3, 5]), or a CellAddress object.
1769	* @param int $break Break type (type of Worksheet::BREAK_*)
1770	*
1771	* @return $this
1772	*/
1773	public function setBreak($coordinate, $break, int $max = -1)
1774	{
1775	$cellAddress = Functions::trimSheetFromCellReference(Validations::validateCellAddress($coordinate));
1776
1777	if ($break === self::BREAK_NONE) {
1778	unset($this->rowBreaks[$cellAddress], $this->columnBreaks[$cellAddress]);
1779	} elseif ($break === self::BREAK_ROW) {
1780	$this->rowBreaks[$cellAddress] = new PageBreak($break, $cellAddress, $max);
1781	} elseif ($break === self::BREAK_COLUMN) {
1782	$this->columnBreaks[$cellAddress] = new PageBreak($break, $cellAddress, $max);
1783	}
1784
1785	return $this;
1786	}
1787
1788	/**
1789	* Set break on a cell by using numeric cell coordinates.
1790	*
1791	* @deprecated 1.23.0
1792	* Use the setBreak() method with a cell address such as 'C5' instead;,
1793	* or passing in an array of [$columnIndex, $row] (e.g. [3, 5]), or a CellAddress object.
1794	* @see Worksheet::setBreak()
1795	*
1796	* @param int $columnIndex Numeric column coordinate of the cell
1797	* @param int $row Numeric row coordinate of the cell
1798	* @param int $break Break type (type of Worksheet::BREAK_*)
1799	*
1800	* @return $this
1801	*/
1802	public function setBreakByColumnAndRow($columnIndex, $row, $break)
1803	{
1804	return $this->setBreak(Coordinate::stringFromColumnIndex($columnIndex) . $row, $break);
1805	}
1806
1807	/**
1808	* Get breaks.
1809	*
1810	* @return int[]
1811	*/
1812	public function getBreaks()
1813	{
1814	$breaks = [];
1815	/** @var callable */
1816	$compareFunction = [self::class, 'compareRowBreaks'];
1817	uksort($this->rowBreaks, $compareFunction);
1818	foreach ($this->rowBreaks as $break) {
1819	$breaks[$break->getCoordinate()] = self::BREAK_ROW;
1820	}
1821	/** @var callable */
1822	$compareFunction = [self::class, 'compareColumnBreaks'];
1823	uksort($this->columnBreaks, $compareFunction);
1824	foreach ($this->columnBreaks as $break) {
1825	$breaks[$break->getCoordinate()] = self::BREAK_COLUMN;
1826	}
1827
1828	return $breaks;
1829	}
1830
1831	/**
1832	* Get row breaks.
1833	*
1834	* @return PageBreak[]
1835	*/
1836	public function getRowBreaks()
1837	{
1838	/** @var callable */
1839	$compareFunction = [self::class, 'compareRowBreaks'];
1840	uksort($this->rowBreaks, $compareFunction);
1841
1842	return $this->rowBreaks;
1843	}
1844
1845	protected static function compareRowBreaks(string $coordinate1, string $coordinate2): int
1846	{
1847	$row1 = Coordinate::indexesFromString($coordinate1)[1];
1848	$row2 = Coordinate::indexesFromString($coordinate2)[1];
1849
1850	return $row1 - $row2;
1851	}
1852
1853	protected static function compareColumnBreaks(string $coordinate1, string $coordinate2): int
1854	{
1855	$column1 = Coordinate::indexesFromString($coordinate1)[0];
1856	$column2 = Coordinate::indexesFromString($coordinate2)[0];
1857
1858	return $column1 - $column2;
1859	}
1860
1861	/**
1862	* Get column breaks.
1863	*
1864	* @return PageBreak[]
1865	*/
1866	public function getColumnBreaks()
1867	{
1868	/** @var callable */
1869	$compareFunction = [self::class, 'compareColumnBreaks'];
1870	uksort($this->columnBreaks, $compareFunction);
1871
1872	return $this->columnBreaks;
1873	}
1874
1875	/**
1876	* Set merge on a cell range.
1877	*
1878	* @param AddressRange\|array<int>\|string $range A simple string containing a Cell range like 'A1:E10'
1879	* or passing in an array of [$fromColumnIndex, $fromRow, $toColumnIndex, $toRow] (e.g. [3, 5, 6, 8]),
1880	* or an AddressRange.
1881	* @param string $behaviour How the merged cells should behave.
1882	* Possible values are:
1883	* MERGE_CELL_CONTENT_EMPTY - Empty the content of the hidden cells
1884	* MERGE_CELL_CONTENT_HIDE - Keep the content of the hidden cells
1885	* MERGE_CELL_CONTENT_MERGE - Move the content of the hidden cells into the first cell
1886	*
1887	* @return $this
1888	*/
1889	public function mergeCells($range, $behaviour = self::MERGE_CELL_CONTENT_EMPTY)
1890	{
1891	$range = Functions::trimSheetFromCellReference(Validations::validateCellRange($range));
1892
1893	if (strpos($range, ':') === false) {
1894	$range .= ":{$range}";
1895	}
1896
1897	if (!Preg::isMatch('/^([A-Z]+)(\d+):([A-Z]+)(\d+)$/', $range, $matches)) {
1898	throw new Exception('Merge must be on a valid range of cells.');
1899	}
1900
1901	$this->mergeCells[$range] = $range;
1902	$firstRow = (int) $matches[2];
1903	$lastRow = (int) $matches[4];
1904	$firstColumn = $matches[1];
1905	$lastColumn = $matches[3];
1906	$firstColumnIndex = Coordinate::columnIndexFromString($firstColumn);
1907	$lastColumnIndex = Coordinate::columnIndexFromString($lastColumn);
1908	$numberRows = $lastRow - $firstRow;
1909	$numberColumns = $lastColumnIndex - $firstColumnIndex;
1910
1911	if ($numberRows === 1 && $numberColumns === 1) {
1912	return $this;
1913	}
1914
1915	// create upper left cell if it does not already exist
1916	$upperLeft = "{$firstColumn}{$firstRow}";
1917	if (!$this->cellExists($upperLeft)) {
1918	$this->getCell($upperLeft)->setValueExplicit(null, DataType::TYPE_NULL);
1919	}
1920
1921	if ($behaviour !== self::MERGE_CELL_CONTENT_HIDE) {
1922	// Blank out the rest of the cells in the range (if they exist)
1923	if ($numberRows > $numberColumns) {
1924	$this->clearMergeCellsByColumn($firstColumn, $lastColumn, $firstRow, $lastRow, $upperLeft, $behaviour);
1925	} else {
1926	$this->clearMergeCellsByRow($firstColumn, $lastColumnIndex, $firstRow, $lastRow, $upperLeft, $behaviour);
1927	}
1928	}
1929
1930	return $this;
1931	}
1932
1933	private function clearMergeCellsByColumn(string $firstColumn, string $lastColumn, int $firstRow, int $lastRow, string $upperLeft, string $behaviour): void
1934	{
1935	$leftCellValue = ($behaviour === self::MERGE_CELL_CONTENT_MERGE)
1936	? [$this->getCell($upperLeft)->getFormattedValue()]
1937	: [];
1938
1939	foreach ($this->getColumnIterator($firstColumn, $lastColumn) as $column) {
1940	$iterator = $column->getCellIterator($firstRow);
1941	$iterator->setIterateOnlyExistingCells(true);
1942	foreach ($iterator as $cell) {
1943	if ($cell !== null) {
1944	$row = $cell->getRow();
1945	if ($row > $lastRow) {
1946	break;
1947	}
1948	$leftCellValue = $this->mergeCellBehaviour($cell, $upperLeft, $behaviour, $leftCellValue);
1949	}
1950	}
1951	}
1952
1953	if ($behaviour === self::MERGE_CELL_CONTENT_MERGE) {
1954	$this->getCell($upperLeft)->setValueExplicit(implode(' ', $leftCellValue), DataType::TYPE_STRING);
1955	}
1956	}
1957
1958	private function clearMergeCellsByRow(string $firstColumn, int $lastColumnIndex, int $firstRow, int $lastRow, string $upperLeft, string $behaviour): void
1959	{
1960	$leftCellValue = ($behaviour === self::MERGE_CELL_CONTENT_MERGE)
1961	? [$this->getCell($upperLeft)->getFormattedValue()]
1962	: [];
1963
1964	foreach ($this->getRowIterator($firstRow, $lastRow) as $row) {
1965	$iterator = $row->getCellIterator($firstColumn);
1966	$iterator->setIterateOnlyExistingCells(true);
1967	foreach ($iterator as $cell) {
1968	if ($cell !== null) {
1969	$column = $cell->getColumn();
1970	$columnIndex = Coordinate::columnIndexFromString($column);
1971	if ($columnIndex > $lastColumnIndex) {
1972	break;
1973	}
1974	$leftCellValue = $this->mergeCellBehaviour($cell, $upperLeft, $behaviour, $leftCellValue);
1975	}
1976	}
1977	}
1978
1979	if ($behaviour === self::MERGE_CELL_CONTENT_MERGE) {
1980	$this->getCell($upperLeft)->setValueExplicit(implode(' ', $leftCellValue), DataType::TYPE_STRING);
1981	}
1982	}
1983
1984	public function mergeCellBehaviour(Cell $cell, string $upperLeft, string $behaviour, array $leftCellValue): array
1985	{
1986	if ($cell->getCoordinate() !== $upperLeft) {
1987	Calculation::getInstance($cell->getWorksheet()->getParentOrThrow())->flushInstance();
1988	if ($behaviour === self::MERGE_CELL_CONTENT_MERGE) {
1989	$cellValue = $cell->getFormattedValue();
1990	if ($cellValue !== '') {
1991	$leftCellValue[] = $cellValue;
1992	}
1993	}
1994	$cell->setValueExplicit(null, DataType::TYPE_NULL);
1995	}
1996
1997	return $leftCellValue;
1998	}
1999
2000	/**
2001	* Set merge on a cell range by using numeric cell coordinates.
2002	*
2003	* @deprecated 1.23.0
2004	* Use the mergeCells() method with a cell address range such as 'C5:F8' instead;,
2005	* or passing in an array of [$fromColumnIndex, $fromRow, $toColumnIndex, $toRow] (e.g. [3, 5, 6, 8]),
2006	* or an AddressRange object.
2007	* @see Worksheet::mergeCells()
2008	*
2009	* @param int $columnIndex1 Numeric column coordinate of the first cell
2010	* @param int $row1 Numeric row coordinate of the first cell
2011	* @param int $columnIndex2 Numeric column coordinate of the last cell
2012	* @param int $row2 Numeric row coordinate of the last cell
2013	* @param string $behaviour How the merged cells should behave.
2014	* Possible values are:
2015	* MERGE_CELL_CONTENT_EMPTY - Empty the content of the hidden cells
2016	* MERGE_CELL_CONTENT_HIDE - Keep the content of the hidden cells
2017	* MERGE_CELL_CONTENT_MERGE - Move the content of the hidden cells into the first cell
2018	*
2019	* @return $this
2020	*/
2021	public function mergeCellsByColumnAndRow($columnIndex1, $row1, $columnIndex2, $row2, $behaviour = self::MERGE_CELL_CONTENT_EMPTY)
2022	{
2023	$cellRange = new CellRange(
2024	CellAddress::fromColumnAndRow($columnIndex1, $row1),
2025	CellAddress::fromColumnAndRow($columnIndex2, $row2)
2026	);
2027
2028	return $this->mergeCells($cellRange, $behaviour);
2029	}
2030
2031	/**
2032	* Remove merge on a cell range.
2033	*
2034	* @param AddressRange\|array<int>\|string $range A simple string containing a Cell range like 'A1:E10'
2035	* or passing in an array of [$fromColumnIndex, $fromRow, $toColumnIndex, $toRow] (e.g. [3, 5, 6, 8]),
2036	* or an AddressRange.
2037	*
2038	* @return $this
2039	*/
2040	public function unmergeCells($range)
2041	{
2042	$range = Functions::trimSheetFromCellReference(Validations::validateCellRange($range));
2043
2044	if (strpos($range, ':') !== false) {
2045	if (isset($this->mergeCells[$range])) {
2046	unset($this->mergeCells[$range]);
2047	} else {
2048	throw new Exception('Cell range ' . $range . ' not known as merged.');
2049	}
2050	} else {
2051	throw new Exception('Merge can only be removed from a range of cells.');
2052	}
2053
2054	return $this;
2055	}
2056
2057	/**
2058	* Remove merge on a cell range by using numeric cell coordinates.
2059	*
2060	* @deprecated 1.23.0
2061	* Use the unmergeCells() method with a cell address range such as 'C5:F8' instead;,
2062	* or passing in an array of [$fromColumnIndex, $fromRow, $toColumnIndex, $toRow] (e.g. [3, 5, 6, 8]),
2063	* or an AddressRange object.
2064	* @see Worksheet::unmergeCells()
2065	*
2066	* @param int $columnIndex1 Numeric column coordinate of the first cell
2067	* @param int $row1 Numeric row coordinate of the first cell
2068	* @param int $columnIndex2 Numeric column coordinate of the last cell
2069	* @param int $row2 Numeric row coordinate of the last cell
2070	*
2071	* @return $this
2072	*/
2073	public function unmergeCellsByColumnAndRow($columnIndex1, $row1, $columnIndex2, $row2)
2074	{
2075	$cellRange = new CellRange(
2076	CellAddress::fromColumnAndRow($columnIndex1, $row1),
2077	CellAddress::fromColumnAndRow($columnIndex2, $row2)
2078	);
2079
2080	return $this->unmergeCells($cellRange);
2081	}
2082
2083	/**
2084	* Get merge cells array.
2085	*
2086	* @return string[]
2087	*/
2088	public function getMergeCells()
2089	{
2090	return $this->mergeCells;
2091	}
2092
2093	/**
2094	* Set merge cells array for the entire sheet. Use instead mergeCells() to merge
2095	* a single cell range.
2096	*
2097	* @param string[] $mergeCells
2098	*
2099	* @return $this
2100	*/
2101	public function setMergeCells(array $mergeCells)
2102	{
2103	$this->mergeCells = $mergeCells;
2104
2105	return $this;
2106	}
2107
2108	/**
2109	* Set protection on a cell or cell range.
2110	*
2111	* @param AddressRange\|array<int>\|CellAddress\|int\|string $range A simple string containing a Cell range like 'A1:E10'
2112	* or passing in an array of [$fromColumnIndex, $fromRow, $toColumnIndex, $toRow] (e.g. [3, 5, 6, 8]),
2113	* or a CellAddress or AddressRange object.
2114	* @param string $password Password to unlock the protection
2115	* @param bool $alreadyHashed If the password has already been hashed, set this to true
2116	*
2117	* @return $this
2118	*/
2119	public function protectCells($range, $password, $alreadyHashed = false)
2120	{
2121	$range = Functions::trimSheetFromCellReference(Validations::validateCellOrCellRange($range));
2122
2123	if (!$alreadyHashed) {
2124	$password = Shared\PasswordHasher::hashPassword($password);
2125	}
2126	$this->protectedCells[$range] = $password;
2127
2128	return $this;
2129	}
2130
2131	/**
2132	* Set protection on a cell range by using numeric cell coordinates.
2133	*
2134	* @deprecated 1.23.0
2135	* Use the protectCells() method with a cell address range such as 'C5:F8' instead;,
2136	* or passing in an array of [$fromColumnIndex, $fromRow, $toColumnIndex, $toRow] (e.g. [3, 5, 6, 8]),
2137	* or an AddressRange object.
2138	* @see Worksheet::protectCells()
2139	*
2140	* @param int $columnIndex1 Numeric column coordinate of the first cell
2141	* @param int $row1 Numeric row coordinate of the first cell
2142	* @param int $columnIndex2 Numeric column coordinate of the last cell
2143	* @param int $row2 Numeric row coordinate of the last cell
2144	* @param string $password Password to unlock the protection
2145	* @param bool $alreadyHashed If the password has already been hashed, set this to true
2146	*
2147	* @return $this
2148	*/
2149	public function protectCellsByColumnAndRow($columnIndex1, $row1, $columnIndex2, $row2, $password, $alreadyHashed = false)
2150	{
2151	$cellRange = new CellRange(
2152	CellAddress::fromColumnAndRow($columnIndex1, $row1),
2153	CellAddress::fromColumnAndRow($columnIndex2, $row2)
2154	);
2155
2156	return $this->protectCells($cellRange, $password, $alreadyHashed);
2157	}
2158
2159	/**
2160	* Remove protection on a cell or cell range.
2161	*
2162	* @param AddressRange\|array<int>\|CellAddress\|int\|string $range A simple string containing a Cell range like 'A1:E10'
2163	* or passing in an array of [$fromColumnIndex, $fromRow, $toColumnIndex, $toRow] (e.g. [3, 5, 6, 8]),
2164	* or a CellAddress or AddressRange object.
2165	*
2166	* @return $this
2167	*/
2168	public function unprotectCells($range)
2169	{
2170	$range = Functions::trimSheetFromCellReference(Validations::validateCellOrCellRange($range));
2171
2172	if (isset($this->protectedCells[$range])) {
2173	unset($this->protectedCells[$range]);
2174	} else {
2175	throw new Exception('Cell range ' . $range . ' not known as protected.');
2176	}
2177
2178	return $this;
2179	}
2180
2181	/**
2182	* Remove protection on a cell range by using numeric cell coordinates.
2183	*
2184	* @deprecated 1.23.0
2185	* Use the unprotectCells() method with a cell address range such as 'C5:F8' instead;,
2186	* or passing in an array of [$fromColumnIndex, $fromRow, $toColumnIndex, $toRow] (e.g. [3, 5, 6, 8]),
2187	* or an AddressRange object.
2188	* @see Worksheet::unprotectCells()
2189	*
2190	* @param int $columnIndex1 Numeric column coordinate of the first cell
2191	* @param int $row1 Numeric row coordinate of the first cell
2192	* @param int $columnIndex2 Numeric column coordinate of the last cell
2193	* @param int $row2 Numeric row coordinate of the last cell
2194	*
2195	* @return $this
2196	*/
2197	public function unprotectCellsByColumnAndRow($columnIndex1, $row1, $columnIndex2, $row2)
2198	{
2199	$cellRange = new CellRange(
2200	CellAddress::fromColumnAndRow($columnIndex1, $row1),
2201	CellAddress::fromColumnAndRow($columnIndex2, $row2)
2202	);
2203
2204	return $this->unprotectCells($cellRange);
2205	}
2206
2207	/**
2208	* Get protected cells.
2209	*
2210	* @return string[]
2211	*/
2212	public function getProtectedCells()
2213	{
2214	return $this->protectedCells;
2215	}
2216
2217	/**
2218	* Get Autofilter.
2219	*
2220	* @return AutoFilter
2221	*/
2222	public function getAutoFilter()
2223	{
2224	return $this->autoFilter;
2225	}
2226
2227	/**
2228	* Set AutoFilter.
2229	*
2230	* @param AddressRange\|array<int>\|AutoFilter\|string $autoFilterOrRange
2231	* A simple string containing a Cell range like 'A1:E10' is permitted for backward compatibility
2232	* or passing in an array of [$fromColumnIndex, $fromRow, $toColumnIndex, $toRow] (e.g. [3, 5, 6, 8]),
2233	* or an AddressRange.
2234	*
2235	* @return $this
2236	*/
2237	public function setAutoFilter($autoFilterOrRange)
2238	{
2239	if (is_object($autoFilterOrRange) && ($autoFilterOrRange instanceof AutoFilter)) {
2240	$this->autoFilter = $autoFilterOrRange;
2241	} else {
2242	$cellRange = Functions::trimSheetFromCellReference(Validations::validateCellRange($autoFilterOrRange));
2243
2244	$this->autoFilter->setRange($cellRange);
2245	}
2246
2247	return $this;
2248	}
2249
2250	/**
2251	* Set Autofilter Range by using numeric cell coordinates.
2252	*
2253	* @deprecated 1.23.0
2254	* Use the setAutoFilter() method with a cell address range such as 'C5:F8' instead;,
2255	* or passing in an array of [$fromColumnIndex, $fromRow, $toColumnIndex, $toRow] (e.g. [3, 5, 6, 8]),
2256	* or an AddressRange object or AutoFilter object.
2257	* @see Worksheet::setAutoFilter()
2258	*
2259	* @param int $columnIndex1 Numeric column coordinate of the first cell
2260	* @param int $row1 Numeric row coordinate of the first cell
2261	* @param int $columnIndex2 Numeric column coordinate of the second cell
2262	* @param int $row2 Numeric row coordinate of the second cell
2263	*
2264	* @return $this
2265	*/
2266	public function setAutoFilterByColumnAndRow($columnIndex1, $row1, $columnIndex2, $row2)
2267	{
2268	$cellRange = new CellRange(
2269	CellAddress::fromColumnAndRow($columnIndex1, $row1),
2270	CellAddress::fromColumnAndRow($columnIndex2, $row2)
2271	);
2272
2273	return $this->setAutoFilter($cellRange);
2274	}
2275
2276	/**
2277	* Remove autofilter.
2278	*/
2279	public function removeAutoFilter(): self
2280	{
2281	$this->autoFilter->setRange('');
2282
2283	return $this;
2284	}
2285
2286	/**
2287	* Get collection of Tables.
2288	*
2289	* @return ArrayObject<int, Table>
2290	*/
2291	public function getTableCollection()
2292	{
2293	return $this->tableCollection;
2294	}
2295
2296	/**
2297	* Add Table.
2298	*
2299	* @return $this
2300	*/
2301	public function addTable(Table $table): self
2302	{
2303	$table->setWorksheet($this);
2304	$this->tableCollection[] = $table;
2305
2306	return $this;
2307	}
2308
2309	/**
2310	* @return string[] array of Table names
2311	*/
2312	public function getTableNames(): array
2313	{
2314	$tableNames = [];
2315
2316	foreach ($this->tableCollection as $table) {
2317	/** @var Table $table */
2318	$tableNames[] = $table->getName();
2319	}
2320
2321	return $tableNames;
2322	}
2323
2324	/** @var null\|Table */
2325	private static $scrutinizerNullTable;
2326
2327	/** @var null\|int */
2328	private static $scrutinizerNullInt;
2329
2330	/**
2331	* @param string $name the table name to search
2332	*
2333	* @return null\|Table The table from the tables collection, or null if not found
2334	*/
2335	public function getTableByName(string $name): ?Table
2336	{
2337	$tableIndex = $this->getTableIndexByName($name);
2338
2339	return ($tableIndex === null) ? self::$scrutinizerNullTable : $this->tableCollection[$tableIndex];
2340	}
2341
2342	/**
2343	* @param string $name the table name to search
2344	*
2345	* @return null\|int The index of the located table in the tables collection, or null if not found
2346	*/
2347	protected function getTableIndexByName(string $name): ?int
2348	{
2349	$name = Shared\StringHelper::strToUpper($name);
2350	foreach ($this->tableCollection as $index => $table) {
2351	/** @var Table $table */
2352	if (Shared\StringHelper::strToUpper($table->getName()) === $name) {
2353	return $index;
2354	}
2355	}
2356
2357	return self::$scrutinizerNullInt;
2358	}
2359
2360	/**
2361	* Remove Table by name.
2362	*
2363	* @param string $name Table name
2364	*
2365	* @return $this
2366	*/
2367	public function removeTableByName(string $name): self
2368	{
2369	$tableIndex = $this->getTableIndexByName($name);
2370
2371	if ($tableIndex !== null) {
2372	unset($this->tableCollection[$tableIndex]);
2373	}
2374
2375	return $this;
2376	}
2377
2378	/**
2379	* Remove collection of Tables.
2380	*/
2381	public function removeTableCollection(): self
2382	{
2383	$this->tableCollection = new ArrayObject();
2384
2385	return $this;
2386	}
2387
2388	/**
2389	* Get Freeze Pane.
2390	*
2391	* @return null\|string
2392	*/
2393	public function getFreezePane()
2394	{
2395	return $this->freezePane;
2396	}
2397
2398	/**
2399	* Freeze Pane.
2400	*
2401	* Examples:
2402	*
2403	* - A2 will freeze the rows above cell A2 (i.e row 1)
2404	* - B1 will freeze the columns to the left of cell B1 (i.e column A)
2405	* - B2 will freeze the rows above and to the left of cell B2 (i.e row 1 and column A)
2406	*
2407	* @param null\|array<int>\|CellAddress\|string $coordinate Coordinate of the cell as a string, eg: 'C5';
2408	* or as an array of [$columnIndex, $row] (e.g. [3, 5]), or a CellAddress object.
2409	* Passing a null value for this argument will clear any existing freeze pane for this worksheet.
2410	* @param null\|array<int>\|CellAddress\|string $topLeftCell default position of the right bottom pane
2411	* Coordinate of the cell as a string, eg: 'C5'; or as an array of [$columnIndex, $row] (e.g. [3, 5]),
2412	* or a CellAddress object.
2413	*
2414	* @return $this
2415	*/
2416	public function freezePane($coordinate, $topLeftCell = null)
2417	{
2418	$cellAddress = ($coordinate !== null)
2419	? Functions::trimSheetFromCellReference(Validations::validateCellAddress($coordinate))
2420	: null;
2421	if ($cellAddress !== null && Coordinate::coordinateIsRange($cellAddress)) {
2422	throw new Exception('Freeze pane can not be set on a range of cells.');
2423	}
2424	$topLeftCell = ($topLeftCell !== null)
2425	? Functions::trimSheetFromCellReference(Validations::validateCellAddress($topLeftCell))
2426	: null;
2427
2428	if ($cellAddress !== null && $topLeftCell === null) {
2429	$coordinate = Coordinate::coordinateFromString($cellAddress);
2430	$topLeftCell = $coordinate[0] . $coordinate[1];
2431	}
2432
2433	$this->freezePane = $cellAddress;
2434	$this->topLeftCell = $topLeftCell;
2435
2436	return $this;
2437	}
2438
2439	public function setTopLeftCell(string $topLeftCell): self
2440	{
2441	$this->topLeftCell = $topLeftCell;
2442
2443	return $this;
2444	}
2445
2446	/**
2447	* Freeze Pane by using numeric cell coordinates.
2448	*
2449	* @deprecated 1.23.0
2450	* Use the freezePane() method with a cell address such as 'C5' instead;,
2451	* or passing in an array of [$columnIndex, $row] (e.g. [3, 5]), or a CellAddress object.
2452	* @see Worksheet::freezePane()
2453	*
2454	* @param int $columnIndex Numeric column coordinate of the cell
2455	* @param int $row Numeric row coordinate of the cell
2456	*
2457	* @return $this
2458	*/
2459	public function freezePaneByColumnAndRow($columnIndex, $row)
2460	{
2461	return $this->freezePane(Coordinate::stringFromColumnIndex($columnIndex) . $row);
2462	}
2463
2464	/**
2465	* Unfreeze Pane.
2466	*
2467	* @return $this
2468	*/
2469	public function unfreezePane()
2470	{
2471	return $this->freezePane(null);
2472	}
2473
2474	/**
2475	* Get the default position of the right bottom pane.
2476	*
2477	* @return null\|string
2478	*/
2479	public function getTopLeftCell()
2480	{
2481	return $this->topLeftCell;
2482	}
2483
2484	/**
2485	* Insert a new row, updating all possible related data.
2486	*
2487	* @param int $before Insert before this row number
2488	* @param int $numberOfRows Number of new rows to insert
2489	*
2490	* @return $this
2491	*/
2492	public function insertNewRowBefore(int $before, int $numberOfRows = 1)
2493	{
2494	if ($before >= 1) {
2495	$objReferenceHelper = ReferenceHelper::getInstance();
2496	$objReferenceHelper->insertNewBefore('A' . $before, 0, $numberOfRows, $this);
2497	} else {
2498	throw new Exception('Rows can only be inserted before at least row 1.');
2499	}
2500
2501	return $this;
2502	}
2503
2504	/**
2505	* Insert a new column, updating all possible related data.
2506	*
2507	* @param string $before Insert before this column Name, eg: 'A'
2508	* @param int $numberOfColumns Number of new columns to insert
2509	*
2510	* @return $this
2511	*/
2512	public function insertNewColumnBefore(string $before, int $numberOfColumns = 1)
2513	{
2514	if (!is_numeric($before)) {
2515	$objReferenceHelper = ReferenceHelper::getInstance();
2516	$objReferenceHelper->insertNewBefore($before . '1', $numberOfColumns, 0, $this);
2517	} else {
2518	throw new Exception('Column references should not be numeric.');
2519	}
2520
2521	return $this;
2522	}
2523
2524	/**
2525	* Insert a new column, updating all possible related data.
2526	*
2527	* @param int $beforeColumnIndex Insert before this column ID (numeric column coordinate of the cell)
2528	* @param int $numberOfColumns Number of new columns to insert
2529	*
2530	* @return $this
2531	*/
2532	public function insertNewColumnBeforeByIndex(int $beforeColumnIndex, int $numberOfColumns = 1)
2533	{
2534	if ($beforeColumnIndex >= 1) {
2535	return $this->insertNewColumnBefore(Coordinate::stringFromColumnIndex($beforeColumnIndex), $numberOfColumns);
2536	}
2537
2538	throw new Exception('Columns can only be inserted before at least column A (1).');
2539	}
2540
2541	/**
2542	* Delete a row, updating all possible related data.
2543	*
2544	* @param int $row Remove rows, starting with this row number
2545	* @param int $numberOfRows Number of rows to remove
2546	*
2547	* @return $this
2548	*/
2549	public function removeRow(int $row, int $numberOfRows = 1)
2550	{
2551	if ($row < 1) {
2552	throw new Exception('Rows to be deleted should at least start from row 1.');
2553	}
2554	$startRow = $row;
2555	$endRow = $startRow + $numberOfRows - 1;
2556	$removeKeys = [];
2557	$addKeys = [];
2558	foreach ($this->mergeCells as $key => $value) {
2559	if (
2560	Preg::isMatch(
2561	'/^([a-z]{1,3})(\d+):([a-z]{1,3})(\d+)/i',
2562	$key,
2563	$matches
2564	)
2565	) {
2566	$startMergeInt = (int) $matches[2];
2567	$endMergeInt = (int) $matches[4];
2568	if ($startMergeInt >= $startRow) {
2569	if ($startMergeInt <= $endRow) {
2570	$removeKeys[] = $key;
2571	}
2572	} elseif ($endMergeInt >= $startRow) {
2573	if ($endMergeInt <= $endRow) {
2574	$temp = $endMergeInt - 1;
2575	$removeKeys[] = $key;
2576	if ($temp !== $startMergeInt) {
2577	$temp3 = $matches[1] . $matches[2] . ':' . $matches[3] . $temp;
2578	$addKeys[] = $temp3;
2579	}
2580	}
2581	}
2582	}
2583	}
2584	foreach ($removeKeys as $key) {
2585	unset($this->mergeCells[$key]);
2586	}
2587	foreach ($addKeys as $key) {
2588	$this->mergeCells[$key] = $key;
2589	}
2590
2591	$holdRowDimensions = $this->removeRowDimensions($row, $numberOfRows);
2592	$highestRow = $this->getHighestDataRow();
2593	$removedRowsCounter = 0;
2594
2595	for ($r = 0; $r < $numberOfRows; ++$r) {
2596	if ($row + $r <= $highestRow) {
2597	$this->getCellCollection()->removeRow($row + $r);
2598	++$removedRowsCounter;
2599	}
2600	}
2601
2602	$objReferenceHelper = ReferenceHelper::getInstance();
2603	$objReferenceHelper->insertNewBefore('A' . ($row + $numberOfRows), 0, -$numberOfRows, $this);
2604	for ($r = 0; $r < $removedRowsCounter; ++$r) {
2605	$this->getCellCollection()->removeRow($highestRow);
2606	--$highestRow;
2607	}
2608
2609	$this->rowDimensions = $holdRowDimensions;
2610
2611	return $this;
2612	}
2613
2614	private function removeRowDimensions(int $row, int $numberOfRows): array
2615	{
2616	$highRow = $row + $numberOfRows - 1;
2617	$holdRowDimensions = [];
2618	foreach ($this->rowDimensions as $rowDimension) {
2619	$num = $rowDimension->getRowIndex();
2620	if ($num < $row) {
2621	$holdRowDimensions[$num] = $rowDimension;
2622	} elseif ($num > $highRow) {
2623	$num -= $numberOfRows;
2624	$cloneDimension = clone $rowDimension;
2625	$cloneDimension->setRowIndex(/** @scrutinizer ignore-type */ $num);
2626	$holdRowDimensions[$num] = $cloneDimension;
2627	}
2628	}
2629
2630	return $holdRowDimensions;
2631	}
2632
2633	/**
2634	* Remove a column, updating all possible related data.
2635	*
2636	* @param string $column Remove columns starting with this column name, eg: 'A'
2637	* @param int $numberOfColumns Number of columns to remove
2638	*
2639	* @return $this
2640	*/
2641	public function removeColumn(string $column, int $numberOfColumns = 1)
2642	{
2643	if (is_numeric($column)) {
2644	throw new Exception('Column references should not be numeric.');
2645	}
2646	$startColumnInt = Coordinate::columnIndexFromString($column);
2647	$endColumnInt = $startColumnInt + $numberOfColumns - 1;
2648	$removeKeys = [];
2649	$addKeys = [];
2650	foreach ($this->mergeCells as $key => $value) {
2651	if (
2652	Preg::isMatch(
2653	'/^([a-z]{1,3})(\d+):([a-z]{1,3})(\d+)/i',
2654	$key,
2655	$matches
2656	)
2657	) {
2658	$startMergeInt = Coordinate::columnIndexFromString($matches[1]);
2659	$endMergeInt = Coordinate::columnIndexFromString($matches[3]);
2660	if ($startMergeInt >= $startColumnInt) {
2661	if ($startMergeInt <= $endColumnInt) {
2662	$removeKeys[] = $key;
2663	}
2664	} elseif ($endMergeInt >= $startColumnInt) {
2665	if ($endMergeInt <= $endColumnInt) {
2666	$temp = Coordinate::columnIndexFromString($matches[3]) - 1;
2667	$temp2 = Coordinate::stringFromColumnIndex($temp);
2668	$removeKeys[] = $key;
2669	if ($temp2 !== $matches[1]) {
2670	$temp3 = $matches[1] . $matches[2] . ':' . $temp2 . $matches[4];
2671	$addKeys[] = $temp3;
2672	}
2673	}
2674	}
2675	}
2676	}
2677	foreach ($removeKeys as $key) {
2678	unset($this->mergeCells[$key]);
2679	}
2680	foreach ($addKeys as $key) {
2681	$this->mergeCells[$key] = $key;
2682	}
2683
2684	$highestColumn = $this->getHighestDataColumn();
2685	$highestColumnIndex = Coordinate::columnIndexFromString($highestColumn);
2686	$pColumnIndex = Coordinate::columnIndexFromString($column);
2687
2688	$holdColumnDimensions = $this->removeColumnDimensions($pColumnIndex, $numberOfColumns);
2689
2690	$column = Coordinate::stringFromColumnIndex($pColumnIndex + $numberOfColumns);
2691	$objReferenceHelper = ReferenceHelper::getInstance();
2692	$objReferenceHelper->insertNewBefore($column . '1', -$numberOfColumns, 0, $this);
2693
2694	$this->columnDimensions = $holdColumnDimensions;
2695
2696	if ($pColumnIndex > $highestColumnIndex) {
2697	return $this;
2698	}
2699
2700	$maxPossibleColumnsToBeRemoved = $highestColumnIndex - $pColumnIndex + 1;
2701
2702	for ($c = 0, $n = min($maxPossibleColumnsToBeRemoved, $numberOfColumns); $c < $n; ++$c) {
2703	$this->getCellCollection()->removeColumn($highestColumn);
2704	$highestColumn = Coordinate::stringFromColumnIndex(Coordinate::columnIndexFromString($highestColumn) - 1);
2705	}
2706
2707	$this->garbageCollect();
2708
2709	return $this;
2710	}
2711
2712	private function removeColumnDimensions(int $pColumnIndex, int $numberOfColumns): array
2713	{
2714	$highCol = $pColumnIndex + $numberOfColumns - 1;
2715	$holdColumnDimensions = [];
2716	foreach ($this->columnDimensions as $columnDimension) {
2717	$num = $columnDimension->getColumnNumeric();
2718	if ($num < $pColumnIndex) {
2719	$str = $columnDimension->getColumnIndex();
2720	$holdColumnDimensions[$str] = $columnDimension;
2721	} elseif ($num > $highCol) {
2722	$cloneDimension = clone $columnDimension;
2723	$cloneDimension->setColumnNumeric($num - $numberOfColumns);
2724	$str = $cloneDimension->getColumnIndex();
2725	$holdColumnDimensions[$str] = $cloneDimension;
2726	}
2727	}
2728
2729	return $holdColumnDimensions;
2730	}
2731
2732	/**
2733	* Remove a column, updating all possible related data.
2734	*
2735	* @param int $columnIndex Remove starting with this column Index (numeric column coordinate)
2736	* @param int $numColumns Number of columns to remove
2737	*
2738	* @return $this
2739	*/
2740	public function removeColumnByIndex(int $columnIndex, int $numColumns = 1)
2741	{
2742	if ($columnIndex >= 1) {
2743	return $this->removeColumn(Coordinate::stringFromColumnIndex($columnIndex), $numColumns);
2744	}
2745
2746	throw new Exception('Columns to be deleted should at least start from column A (1)');
2747	}
2748
2749	/**
2750	* Show gridlines?
2751	*/
2752	public function getShowGridlines(): bool
2753	{
2754	return $this->showGridlines;
2755	}
2756
2757	/**
2758	* Set show gridlines.
2759	*
2760	* @param bool $showGridLines Show gridlines (true/false)
2761	*
2762	* @return $this
2763	*/
2764	public function setShowGridlines(bool $showGridLines): self
2765	{
2766	$this->showGridlines = $showGridLines;
2767
2768	return $this;
2769	}
2770
2771	/**
2772	* Print gridlines?
2773	*/
2774	public function getPrintGridlines(): bool
2775	{
2776	return $this->printGridlines;
2777	}
2778
2779	/**
2780	* Set print gridlines.
2781	*
2782	* @param bool $printGridLines Print gridlines (true/false)
2783	*
2784	* @return $this
2785	*/
2786	public function setPrintGridlines(bool $printGridLines): self
2787	{
2788	$this->printGridlines = $printGridLines;
2789
2790	return $this;
2791	}
2792
2793	/**
2794	* Show row and column headers?
2795	*/
2796	public function getShowRowColHeaders(): bool
2797	{
2798	return $this->showRowColHeaders;
2799	}
2800
2801	/**
2802	* Set show row and column headers.
2803	*
2804	* @param bool $showRowColHeaders Show row and column headers (true/false)
2805	*
2806	* @return $this
2807	*/
2808	public function setShowRowColHeaders(bool $showRowColHeaders): self
2809	{
2810	$this->showRowColHeaders = $showRowColHeaders;
2811
2812	return $this;
2813	}
2814
2815	/**
2816	* Show summary below? (Row/Column outlining).
2817	*/
2818	public function getShowSummaryBelow(): bool
2819	{
2820	return $this->showSummaryBelow;
2821	}
2822
2823	/**
2824	* Set show summary below.
2825	*
2826	* @param bool $showSummaryBelow Show summary below (true/false)
2827	*
2828	* @return $this
2829	*/
2830	public function setShowSummaryBelow(bool $showSummaryBelow): self
2831	{
2832	$this->showSummaryBelow = $showSummaryBelow;
2833
2834	return $this;
2835	}
2836
2837	/**
2838	* Show summary right? (Row/Column outlining).
2839	*/
2840	public function getShowSummaryRight(): bool
2841	{
2842	return $this->showSummaryRight;
2843	}
2844
2845	/**
2846	* Set show summary right.
2847	*
2848	* @param bool $showSummaryRight Show summary right (true/false)
2849	*
2850	* @return $this
2851	*/
2852	public function setShowSummaryRight(bool $showSummaryRight): self
2853	{
2854	$this->showSummaryRight = $showSummaryRight;
2855
2856	return $this;
2857	}
2858
2859	/**
2860	* Get comments.
2861	*
2862	* @return Comment[]
2863	*/
2864	public function getComments()
2865	{
2866	return $this->comments;
2867	}
2868
2869	/**
2870	* Set comments array for the entire sheet.
2871	*
2872	* @param Comment[] $comments
2873	*
2874	* @return $this
2875	*/
2876	public function setComments(array $comments): self
2877	{
2878	$this->comments = $comments;
2879
2880	return $this;
2881	}
2882
2883	/**
2884	* Remove comment from cell.
2885	*
2886	* @param array<int>\|CellAddress\|string $cellCoordinate Coordinate of the cell as a string, eg: 'C5';
2887	* or as an array of [$columnIndex, $row] (e.g. [3, 5]), or a CellAddress object.
2888	*
2889	* @return $this
2890	*/
2891	public function removeComment($cellCoordinate): self
2892	{
2893	$cellAddress = Functions::trimSheetFromCellReference(Validations::validateCellAddress($cellCoordinate));
2894
2895	if (Coordinate::coordinateIsRange($cellAddress)) {
2896	throw new Exception('Cell coordinate string can not be a range of cells.');
2897	} elseif (strpos($cellAddress, '$') !== false) {
2898	throw new Exception('Cell coordinate string must not be absolute.');
2899	} elseif ($cellAddress == '') {
2900	throw new Exception('Cell coordinate can not be zero-length string.');
2901	}
2902	// Check if we have a comment for this cell and delete it
2903	if (isset($this->comments[$cellAddress])) {
2904	unset($this->comments[$cellAddress]);
2905	}
2906
2907	return $this;
2908	}
2909
2910	/**
2911	* Get comment for cell.
2912	*
2913	* @param array<int>\|CellAddress\|string $cellCoordinate Coordinate of the cell as a string, eg: 'C5';
2914	* or as an array of [$columnIndex, $row] (e.g. [3, 5]), or a CellAddress object.
2915	*/
2916	public function getComment($cellCoordinate): Comment
2917	{
2918	$cellAddress = Functions::trimSheetFromCellReference(Validations::validateCellAddress($cellCoordinate));
2919
2920	if (Coordinate::coordinateIsRange($cellAddress)) {
2921	throw new Exception('Cell coordinate string can not be a range of cells.');
2922	} elseif (strpos($cellAddress, '$') !== false) {
2923	throw new Exception('Cell coordinate string must not be absolute.');
2924	} elseif ($cellAddress == '') {
2925	throw new Exception('Cell coordinate can not be zero-length string.');
2926	}
2927
2928	// Check if we already have a comment for this cell.
2929	if (isset($this->comments[$cellAddress])) {
2930	return $this->comments[$cellAddress];
2931	}
2932
2933	// If not, create a new comment.
2934	$newComment = new Comment();
2935	$this->comments[$cellAddress] = $newComment;
2936
2937	return $newComment;
2938	}
2939
2940	/**
2941	* Get comment for cell by using numeric cell coordinates.
2942	*
2943	* @deprecated 1.23.0
2944	* Use the getComment() method with a cell address such as 'C5' instead;,
2945	* or passing in an array of [$columnIndex, $row] (e.g. [3, 5]), or a CellAddress object.
2946	* @see Worksheet::getComment()
2947	*
2948	* @param int $columnIndex Numeric column coordinate of the cell
2949	* @param int $row Numeric row coordinate of the cell
2950	*/
2951	public function getCommentByColumnAndRow($columnIndex, $row): Comment
2952	{
2953	return $this->getComment(Coordinate::stringFromColumnIndex($columnIndex) . $row);
2954	}
2955
2956	/**
2957	* Get active cell.
2958	*
2959	* @return string Example: 'A1'
2960	*/
2961	public function getActiveCell()
2962	{
2963	return $this->activeCell;
2964	}
2965
2966	/**
2967	* Get selected cells.
2968	*
2969	* @return string
2970	*/
2971	public function getSelectedCells()
2972	{
2973	return $this->selectedCells;
2974	}
2975
2976	/**
2977	* Selected cell.
2978	*
2979	* @param string $coordinate Cell (i.e. A1)
2980	*
2981	* @return $this
2982	*/
2983	public function setSelectedCell($coordinate)
2984	{
2985	return $this->setSelectedCells($coordinate);
2986	}
2987
2988	/**
2989	* Select a range of cells.
2990	*
2991	* @param AddressRange\|array<int>\|CellAddress\|int\|string $coordinate A simple string containing a Cell range like 'A1:E10'
2992	* or passing in an array of [$fromColumnIndex, $fromRow, $toColumnIndex, $toRow] (e.g. [3, 5, 6, 8]),
2993	* or a CellAddress or AddressRange object.
2994	*
2995	* @return $this
2996	*/
2997	public function setSelectedCells($coordinate)
2998	{
2999	if (is_string($coordinate)) {
3000	$coordinate = Validations::definedNameToCoordinate($coordinate, $this);
3001	}
3002	$coordinate = Validations::validateCellOrCellRange($coordinate);
3003
3004	if (Coordinate::coordinateIsRange($coordinate)) {
3005	[$first] = Coordinate::splitRange($coordinate);
3006	$this->activeCell = $first[0];
3007	} else {
3008	$this->activeCell = $coordinate;
3009	}
3010	$this->selectedCells = $coordinate;
3011
3012	return $this;
3013	}
3014
3015	/**
3016	* Selected cell by using numeric cell coordinates.
3017	*
3018	* @deprecated 1.23.0
3019	* Use the setSelectedCells() method with a cell address such as 'C5' instead;,
3020	* or passing in an array of [$columnIndex, $row] (e.g. [3, 5]), or a CellAddress object.
3021	* @see Worksheet::setSelectedCells()
3022	*
3023	* @param int $columnIndex Numeric column coordinate of the cell
3024	* @param int $row Numeric row coordinate of the cell
3025	*
3026	* @return $this
3027	*/
3028	public function setSelectedCellByColumnAndRow($columnIndex, $row)
3029	{
3030	return $this->setSelectedCells(Coordinate::stringFromColumnIndex($columnIndex) . $row);
3031	}
3032
3033	/**
3034	* Get right-to-left.
3035	*
3036	* @return bool
3037	*/
3038	public function getRightToLeft()
3039	{
3040	return $this->rightToLeft;
3041	}
3042
3043	/**
3044	* Set right-to-left.
3045	*
3046	* @param bool $value Right-to-left true/false
3047	*
3048	* @return $this
3049	*/
3050	public function setRightToLeft($value)
3051	{
3052	$this->rightToLeft = $value;
3053
3054	return $this;
3055	}
3056
3057	/**
3058	* Fill worksheet from values in array.
3059	*
3060	* @param array $source Source array
3061	* @param mixed $nullValue Value in source array that stands for blank cell
3062	* @param string $startCell Insert array starting from this cell address as the top left coordinate
3063	* @param bool $strictNullComparison Apply strict comparison when testing for null values in the array
3064	*
3065	* @return $this
3066	*/
3067	public function fromArray(array $source, $nullValue = null, $startCell = 'A1', $strictNullComparison = false)
3068	{
3069	// Convert a 1-D array to 2-D (for ease of looping)
3070	if (!is_array(end($source))) {
3071	$source = [$source];
3072	}
3073
3074	// start coordinate
3075	[$startColumn, $startRow] = Coordinate::coordinateFromString($startCell);
3076
3077	// Loop through $source
3078	foreach ($source as $rowData) {
3079	$currentColumn = $startColumn;
3080	foreach ($rowData as $cellValue) {
3081	if ($strictNullComparison) {
3082	if ($cellValue !== $nullValue) {
3083	// Set cell value
3084	$this->getCell($currentColumn . $startRow)->setValue($cellValue);
3085	}
3086	} else {
3087	if ($cellValue != $nullValue) {
3088	// Set cell value
3089	$this->getCell($currentColumn . $startRow)->setValue($cellValue);
3090	}
3091	}
3092	++$currentColumn;
3093	}
3094	++$startRow;
3095	}
3096
3097	return $this;
3098	}
3099
3100	/**
3101	* @param mixed $nullValue
3102	*
3103	* @throws Exception
3104	* @throws \PhpOffice\PhpSpreadsheet\Calculation\Exception
3105	*
3106	* @return mixed
3107	*/
3108	protected function cellToArray(Cell $cell, bool $calculateFormulas, bool $formatData, $nullValue)
3109	{
3110	$returnValue = $nullValue;
3111
3112	if ($cell->getValue() !== null) {
3113	if ($cell->getValue() instanceof RichText) {
3114	$returnValue = $cell->getValue()->getPlainText();
3115	} else {
3116	$returnValue = ($calculateFormulas) ? $cell->getCalculatedValue() : $cell->getValue();
3117	}
3118
3119	if ($formatData) {
3120	$style = $this->getParentOrThrow()->getCellXfByIndex($cell->getXfIndex());
3121	$returnValue = NumberFormat::toFormattedString(
3122	$returnValue,
3123	$style->getNumberFormat()->getFormatCode() ?? NumberFormat::FORMAT_GENERAL
3124	);
3125	}
3126	}
3127
3128	return $returnValue;
3129	}
3130
3131	/**
3132	* Create array from a range of cells.
3133	*
3134	* @param mixed $nullValue Value returned in the array entry if a cell doesn't exist
3135	* @param bool $calculateFormulas Should formulas be calculated?
3136	* @param bool $formatData Should formatting be applied to cell values?
3137	* @param bool $returnCellRef False - Return a simple array of rows and columns indexed by number counting from zero
3138	* True - Return rows and columns indexed by their actual row and column IDs
3139	* @param bool $ignoreHidden False - Return values for rows/columns even if they are defined as hidden.
3140	* True - Don't return values for rows/columns that are defined as hidden.
3141	*/
3142	public function rangeToArray(
3143	string $range,
3144	$nullValue = null,
3145	bool $calculateFormulas = true,
3146	bool $formatData = true,
3147	bool $returnCellRef = false,
3148	bool $ignoreHidden = false
3149	): array {
3150	$range = Validations::validateCellOrCellRange($range);
3151
3152	$returnValue = [];
3153	// Identify the range that we need to extract from the worksheet
3154	[$rangeStart, $rangeEnd] = Coordinate::rangeBoundaries($range);
3155	$minCol = Coordinate::stringFromColumnIndex($rangeStart[0]);
3156	$minRow = $rangeStart[1];
3157	$maxCol = Coordinate::stringFromColumnIndex($rangeEnd[0]);
3158	$maxRow = $rangeEnd[1];
3159
3160	++$maxCol;
3161	// Loop through rows
3162	$r = -1;
3163	for ($row = $minRow; $row <= $maxRow; ++$row) {
3164	if (($ignoreHidden === true) && ($this->getRowDimension($row)->getVisible() === false)) {
3165	continue;
3166	}
3167	$rowRef = $returnCellRef ? $row : ++$r;
3168	$c = -1;
3169	// Loop through columns in the current row
3170	for ($col = $minCol; $col !== $maxCol; ++$col) {
3171	if (($ignoreHidden === true) && ($this->getColumnDimension($col)->getVisible() === false)) {
3172	continue;
3173	}
3174	$columnRef = $returnCellRef ? $col : ++$c;
3175	// Using getCell() will create a new cell if it doesn't already exist. We don't want that to happen
3176	// so we test and retrieve directly against cellCollection
3177	$cell = $this->cellCollection->get("{$col}{$row}");
3178	$returnValue[$rowRef][$columnRef] = $nullValue;
3179	if ($cell !== null) {
3180	$returnValue[$rowRef][$columnRef] = $this->cellToArray($cell, $calculateFormulas, $formatData, $nullValue);
3181	}
3182	}
3183	}
3184
3185	// Return
3186	return $returnValue;
3187	}
3188
3189	private function validateNamedRange(string $definedName, bool $returnNullIfInvalid = false): ?DefinedName
3190	{
3191	$namedRange = DefinedName::resolveName($definedName, $this);
3192	if ($namedRange === null) {
3193	if ($returnNullIfInvalid) {
3194	return null;
3195	}
3196
3197	throw new Exception('Named Range ' . $definedName . ' does not exist.');
3198	}
3199
3200	if ($namedRange->isFormula()) {
3201	if ($returnNullIfInvalid) {
3202	return null;
3203	}
3204
3205	throw new Exception('Defined Named ' . $definedName . ' is a formula, not a range or cell.');
3206	}
3207
3208	if ($namedRange->getLocalOnly()) {
3209	$worksheet = $namedRange->getWorksheet();
3210	if ($worksheet === null \|\| $this->getHashInt() !== $worksheet->getHashInt()) {
3211	if ($returnNullIfInvalid) {
3212	return null;
3213	}
3214
3215	throw new Exception(
3216	'Named range ' . $definedName . ' is not accessible from within sheet ' . $this->getTitle()
3217	);
3218	}
3219	}
3220
3221	return $namedRange;
3222	}
3223
3224	/**
3225	* Create array from a range of cells.
3226	*
3227	* @param string $definedName The Named Range that should be returned
3228	* @param mixed $nullValue Value returned in the array entry if a cell doesn't exist
3229	* @param bool $calculateFormulas Should formulas be calculated?
3230	* @param bool $formatData Should formatting be applied to cell values?
3231	* @param bool $returnCellRef False - Return a simple array of rows and columns indexed by number counting from zero
3232	* True - Return rows and columns indexed by their actual row and column IDs
3233	* @param bool $ignoreHidden False - Return values for rows/columns even if they are defined as hidden.
3234	* True - Don't return values for rows/columns that are defined as hidden.
3235	*/
3236	public function namedRangeToArray(
3237	string $definedName,
3238	$nullValue = null,
3239	bool $calculateFormulas = true,
3240	bool $formatData = true,
3241	bool $returnCellRef = false,
3242	bool $ignoreHidden = false
3243	): array {
3244	$retVal = [];
3245	$namedRange = $this->validateNamedRange($definedName);
3246	if ($namedRange !== null) {
3247	$cellRange = ltrim(substr($namedRange->getValue(), (int) strrpos($namedRange->getValue(), '!')), '!');
3248	$cellRange = str_replace('$', '', $cellRange);
3249	$workSheet = $namedRange->getWorksheet();
3250	if ($workSheet !== null) {
3251	$retVal = $workSheet->rangeToArray($cellRange, $nullValue, $calculateFormulas, $formatData, $returnCellRef, $ignoreHidden);
3252	}
3253	}
3254
3255	return $retVal;
3256	}
3257
3258	/**
3259	* Create array from worksheet.
3260	*
3261	* @param mixed $nullValue Value returned in the array entry if a cell doesn't exist
3262	* @param bool $calculateFormulas Should formulas be calculated?
3263	* @param bool $formatData Should formatting be applied to cell values?
3264	* @param bool $returnCellRef False - Return a simple array of rows and columns indexed by number counting from zero
3265	* True - Return rows and columns indexed by their actual row and column IDs
3266	* @param bool $ignoreHidden False - Return values for rows/columns even if they are defined as hidden.
3267	* True - Don't return values for rows/columns that are defined as hidden.
3268	*/
3269	public function toArray(
3270	$nullValue = null,
3271	bool $calculateFormulas = true,
3272	bool $formatData = true,
3273	bool $returnCellRef = false,
3274	bool $ignoreHidden = false
3275	): array {
3276	// Garbage collect...
3277	$this->garbageCollect();
3278
3279	// Identify the range that we need to extract from the worksheet
3280	$maxCol = $this->getHighestColumn();
3281	$maxRow = $this->getHighestRow();
3282
3283	// Return
3284	return $this->rangeToArray("A1:{$maxCol}{$maxRow}", $nullValue, $calculateFormulas, $formatData, $returnCellRef, $ignoreHidden);
3285	}
3286
3287	/**
3288	* Get row iterator.
3289	*
3290	* @param int $startRow The row number at which to start iterating
3291	* @param int $endRow The row number at which to stop iterating
3292	*
3293	* @return RowIterator
3294	*/
3295	public function getRowIterator($startRow = 1, $endRow = null)
3296	{
3297	return new RowIterator($this, $startRow, $endRow);
3298	}
3299
3300	/**
3301	* Get column iterator.
3302	*
3303	* @param string $startColumn The column address at which to start iterating
3304	* @param string $endColumn The column address at which to stop iterating
3305	*
3306	* @return ColumnIterator
3307	*/
3308	public function getColumnIterator($startColumn = 'A', $endColumn = null)
3309	{
3310	return new ColumnIterator($this, $startColumn, $endColumn);
3311	}
3312
3313	/**
3314	* Run PhpSpreadsheet garbage collector.
3315	*
3316	* @return $this
3317	*/
3318	public function garbageCollect()
3319	{
3320	// Flush cache
3321	$this->cellCollection->get('A1');
3322
3323	// Lookup highest column and highest row if cells are cleaned
3324	$colRow = $this->cellCollection->getHighestRowAndColumn();
3325	$highestRow = $colRow['row'];
3326	$highestColumn = Coordinate::columnIndexFromString($colRow['column']);
3327
3328	// Loop through column dimensions
3329	foreach ($this->columnDimensions as $dimension) {
3330	$highestColumn = max($highestColumn, Coordinate::columnIndexFromString($dimension->getColumnIndex()));
3331	}
3332
3333	// Loop through row dimensions
3334	foreach ($this->rowDimensions as $dimension) {
3335	$highestRow = max($highestRow, $dimension->getRowIndex());
3336	}
3337
3338	// Cache values
3339	if ($highestColumn < 1) {
3340	$this->cachedHighestColumn = 1;
3341	} else {
3342	$this->cachedHighestColumn = $highestColumn;
3343	}
3344	$this->cachedHighestRow = $highestRow;
3345
3346	// Return
3347	return $this;
3348	}
3349
3350	/**
3351	* @deprecated 3.5.0 use getHashInt instead.
3352	*
3353	* @return string Hash code
3354	*/
3355	public function getHashCode()
3356	{
3357	return (string) $this->hash;
3358	}
3359
3360	/**
3361	* @return int Hash code
3362	*/
3363	public function getHashInt()
3364	{
3365	return $this->hash;
3366	}
3367
3368	/**
3369	* Extract worksheet title from range.
3370	*
3371	* Example: extractSheetTitle("testSheet!A1") ==> 'A1'
3372	* Example: extractSheetTitle("testSheet!A1:C3") ==> 'A1:C3'
3373	* Example: extractSheetTitle("'testSheet 1'!A1", true) ==> ['testSheet 1', 'A1'];
3374	* Example: extractSheetTitle("'testSheet 1'!A1:C3", true) ==> ['testSheet 1', 'A1:C3'];
3375	* Example: extractSheetTitle("A1", true) ==> ['', 'A1'];
3376	* Example: extractSheetTitle("A1:C3", true) ==> ['', 'A1:C3']
3377	*
3378	* @param string $range Range to extract title from
3379	* @param bool $returnRange Return range? (see example)
3380	*
3381	* @return mixed
3382	*/
3383	public static function extractSheetTitle($range, $returnRange = false)
3384	{
3385	if (empty($range)) {
3386	return $returnRange ? [null, null] : null;
3387	}
3388
3389	// Sheet title included?
3390	if (($sep = strrpos($range, '!')) === false) {
3391	return $returnRange ? ['', $range] : '';
3392	}
3393
3394	if ($returnRange) {
3395	return [substr($range, 0, $sep), substr($range, $sep + 1)];
3396	}
3397
3398	return substr($range, $sep + 1);
3399	}
3400
3401	/**
3402	* Get hyperlink.
3403	*
3404	* @param string $cellCoordinate Cell coordinate to get hyperlink for, eg: 'A1'
3405	*
3406	* @return Hyperlink
3407	*/
3408	public function getHyperlink($cellCoordinate)
3409	{
3410	// return hyperlink if we already have one
3411	if (isset($this->hyperlinkCollection[$cellCoordinate])) {
3412	return $this->hyperlinkCollection[$cellCoordinate];
3413	}
3414
3415	// else create hyperlink
3416	$this->hyperlinkCollection[$cellCoordinate] = new Hyperlink();
3417
3418	return $this->hyperlinkCollection[$cellCoordinate];
3419	}
3420
3421	/**
3422	* Set hyperlink.
3423	*
3424	* @param string $cellCoordinate Cell coordinate to insert hyperlink, eg: 'A1'
3425	*
3426	* @return $this
3427	*/
3428	public function setHyperlink($cellCoordinate, ?Hyperlink $hyperlink = null)
3429	{
3430	if ($hyperlink === null) {
3431	unset($this->hyperlinkCollection[$cellCoordinate]);
3432	} else {
3433	$this->hyperlinkCollection[$cellCoordinate] = $hyperlink;
3434	}
3435
3436	return $this;
3437	}
3438
3439	/**
3440	* Hyperlink at a specific coordinate exists?
3441	*
3442	* @param string $coordinate eg: 'A1'
3443	*
3444	* @return bool
3445	*/
3446	public function hyperlinkExists($coordinate)
3447	{
3448	return isset($this->hyperlinkCollection[$coordinate]);
3449	}
3450
3451	/**
3452	* Get collection of hyperlinks.
3453	*
3454	* @return Hyperlink[]
3455	*/
3456	public function getHyperlinkCollection()
3457	{
3458	return $this->hyperlinkCollection;
3459	}
3460
3461	/**
3462	* Get data validation.
3463	*
3464	* @param string $cellCoordinate Cell coordinate to get data validation for, eg: 'A1'
3465	*
3466	* @return DataValidation
3467	*/
3468	public function getDataValidation($cellCoordinate)
3469	{
3470	// return data validation if we already have one
3471	if (isset($this->dataValidationCollection[$cellCoordinate])) {
3472	return $this->dataValidationCollection[$cellCoordinate];
3473	}
3474
3475	// else create data validation
3476	$this->dataValidationCollection[$cellCoordinate] = new DataValidation();
3477
3478	return $this->dataValidationCollection[$cellCoordinate];
3479	}
3480
3481	/**
3482	* Set data validation.
3483	*
3484	* @param string $cellCoordinate Cell coordinate to insert data validation, eg: 'A1'
3485	*
3486	* @return $this
3487	*/
3488	public function setDataValidation($cellCoordinate, ?DataValidation $dataValidation = null)
3489	{
3490	if ($dataValidation === null) {
3491	unset($this->dataValidationCollection[$cellCoordinate]);
3492	} else {
3493	$this->dataValidationCollection[$cellCoordinate] = $dataValidation;
3494	}
3495
3496	return $this;
3497	}
3498
3499	/**
3500	* Data validation at a specific coordinate exists?
3501	*
3502	* @param string $coordinate eg: 'A1'
3503	*
3504	* @return bool
3505	*/
3506	public function dataValidationExists($coordinate)
3507	{
3508	return isset($this->dataValidationCollection[$coordinate]);
3509	}
3510
3511	/**
3512	* Get collection of data validations.
3513	*
3514	* @return DataValidation[]
3515	*/
3516	public function getDataValidationCollection()
3517	{
3518	return $this->dataValidationCollection;
3519	}
3520
3521	/**
3522	* Accepts a range, returning it as a range that falls within the current highest row and column of the worksheet.
3523	*
3524	* @param string $range
3525	*
3526	* @return string Adjusted range value
3527	*/
3528	public function shrinkRangeToFit($range)
3529	{
3530	$maxCol = $this->getHighestColumn();
3531	$maxRow = $this->getHighestRow();
3532	$maxCol = Coordinate::columnIndexFromString($maxCol);
3533
3534	$rangeBlocks = explode(' ', $range);
3535	foreach ($rangeBlocks as &$rangeSet) {
3536	$rangeBoundaries = Coordinate::getRangeBoundaries($rangeSet);
3537
3538	if (Coordinate::columnIndexFromString($rangeBoundaries[0][0]) > $maxCol) {
3539	$rangeBoundaries[0][0] = Coordinate::stringFromColumnIndex($maxCol);
3540	}
3541	if ($rangeBoundaries[0][1] > $maxRow) {
3542	$rangeBoundaries[0][1] = $maxRow;
3543	}
3544	if (Coordinate::columnIndexFromString($rangeBoundaries[1][0]) > $maxCol) {
3545	$rangeBoundaries[1][0] = Coordinate::stringFromColumnIndex($maxCol);
3546	}
3547	if ($rangeBoundaries[1][1] > $maxRow) {
3548	$rangeBoundaries[1][1] = $maxRow;
3549	}
3550	$rangeSet = $rangeBoundaries[0][0] . $rangeBoundaries[0][1] . ':' . $rangeBoundaries[1][0] . $rangeBoundaries[1][1];
3551	}
3552	unset($rangeSet);
3553
3554	return implode(' ', $rangeBlocks);
3555	}
3556
3557	/**
3558	* Get tab color.
3559	*
3560	* @return Color
3561	*/
3562	public function getTabColor()
3563	{
3564	if ($this->tabColor === null) {
3565	$this->tabColor = new Color();
3566	}
3567
3568	return $this->tabColor;
3569	}
3570
3571	/**
3572	* Reset tab color.
3573	*
3574	* @return $this
3575	*/
3576	public function resetTabColor()
3577	{
3578	$this->tabColor = null;
3579
3580	return $this;
3581	}
3582
3583	/**
3584	* Tab color set?
3585	*
3586	* @return bool
3587	*/
3588	public function isTabColorSet()
3589	{
3590	return $this->tabColor !== null;
3591	}
3592
3593	/**
3594	* Copy worksheet (!= clone!).
3595	*
3596	* @return static
3597	*/
3598	public function copy()
3599	{
3600	return clone $this;
3601	}
3602
3603	/**
3604	* Returns a boolean true if the specified row contains no cells. By default, this means that no cell records
3605	* exist in the collection for this row. false will be returned otherwise.
3606	* This rule can be modified by passing a $definitionOfEmptyFlags value:
3607	* 1 - CellIterator::TREAT_NULL_VALUE_AS_EMPTY_CELL If the only cells in the collection are null value
3608	* cells, then the row will be considered empty.
3609	* 2 - CellIterator::TREAT_EMPTY_STRING_AS_EMPTY_CELL If the only cells in the collection are empty
3610	* string value cells, then the row will be considered empty.
3611	* 3 - CellIterator::TREAT_NULL_VALUE_AS_EMPTY_CELL \| CellIterator::TREAT_EMPTY_STRING_AS_EMPTY_CELL
3612	* If the only cells in the collection are null value or empty string value cells, then the row
3613	* will be considered empty.
3614	*
3615	* @param int $definitionOfEmptyFlags
3616	* Possible Flag Values are:
3617	* CellIterator::TREAT_NULL_VALUE_AS_EMPTY_CELL
3618	* CellIterator::TREAT_EMPTY_STRING_AS_EMPTY_CELL
3619	*/
3620	public function isEmptyRow(int $rowId, int $definitionOfEmptyFlags = 0): bool
3621	{
3622	try {
3623	$iterator = new RowIterator($this, $rowId, $rowId);
3624	$iterator->seek($rowId);
3625	$row = $iterator->current();
3626	} catch (Exception $e) {
3627	return true;
3628	}
3629
3630	return $row->isEmpty($definitionOfEmptyFlags);
3631	}
3632
3633	/**
3634	* Returns a boolean true if the specified column contains no cells. By default, this means that no cell records
3635	* exist in the collection for this column. false will be returned otherwise.
3636	* This rule can be modified by passing a $definitionOfEmptyFlags value:
3637	* 1 - CellIterator::TREAT_NULL_VALUE_AS_EMPTY_CELL If the only cells in the collection are null value
3638	* cells, then the column will be considered empty.
3639	* 2 - CellIterator::TREAT_EMPTY_STRING_AS_EMPTY_CELL If the only cells in the collection are empty
3640	* string value cells, then the column will be considered empty.
3641	* 3 - CellIterator::TREAT_NULL_VALUE_AS_EMPTY_CELL \| CellIterator::TREAT_EMPTY_STRING_AS_EMPTY_CELL
3642	* If the only cells in the collection are null value or empty string value cells, then the column
3643	* will be considered empty.
3644	*
3645	* @param int $definitionOfEmptyFlags
3646	* Possible Flag Values are:
3647	* CellIterator::TREAT_NULL_VALUE_AS_EMPTY_CELL
3648	* CellIterator::TREAT_EMPTY_STRING_AS_EMPTY_CELL
3649	*/
3650	public function isEmptyColumn(string $columnId, int $definitionOfEmptyFlags = 0): bool
3651	{
3652	try {
3653	$iterator = new ColumnIterator($this, $columnId, $columnId);
3654	$iterator->seek($columnId);
3655	$column = $iterator->current();
3656	} catch (Exception $e) {
3657	return true;
3658	}
3659
3660	return $column->isEmpty($definitionOfEmptyFlags);
3661	}
3662
3663	/**
3664	* Implement PHP __clone to create a deep clone, not just a shallow copy.
3665	*/
3666	public function __clone()
3667	{
3668	// @phpstan-ignore-next-line
3669	foreach ($this as $key => $val) {
3670	if ($key == 'parent') {
3671	continue;
3672	}
3673
3674	if (is_object($val) \|\| (is_array($val))) {
3675	if ($key == 'cellCollection') {
3676	$newCollection = $this->cellCollection->cloneCellCollection($this);
3677	$this->cellCollection = $newCollection;
3678	} elseif ($key == 'drawingCollection') {
3679	$currentCollection = $this->drawingCollection;
3680	$this->drawingCollection = new ArrayObject();
3681	foreach ($currentCollection as $item) {
3682	if (is_object($item)) {
3683	$newDrawing = clone $item;
3684	$newDrawing->setWorksheet($this);
3685	}
3686	}
3687	} elseif (($key == 'autoFilter') && ($this->autoFilter instanceof AutoFilter)) {
3688	$newAutoFilter = clone $this->autoFilter;
3689	$this->autoFilter = $newAutoFilter;
3690	$this->autoFilter->setParent($this);
3691	} else {
3692	$this->{$key} = unserialize(serialize($val));
3693	}
3694	}
3695	}
3696	$this->hash = spl_object_id($this);
3697	}
3698
3699	/**
3700	* Define the code name of the sheet.
3701	*
3702	* @param string $codeName Same rule as Title minus space not allowed (but, like Excel, change
3703	* silently space to underscore)
3704	* @param bool $validate False to skip validation of new title. WARNING: This should only be set
3705	* at parse time (by Readers), where titles can be assumed to be valid.
3706	*
3707	* @return $this
3708	*/
3709	public function setCodeName($codeName, $validate = true)
3710	{
3711	// Is this a 'rename' or not?
3712	if ($this->getCodeName() == $codeName) {
3713	return $this;
3714	}
3715
3716	if ($validate) {
3717	$codeName = str_replace(' ', '_', $codeName); //Excel does this automatically without flinching, we are doing the same
3718
3719	// Syntax check
3720	// throw an exception if not valid
3721	self::checkSheetCodeName($codeName);
3722
3723	// We use the same code that setTitle to find a valid codeName else not using a space (Excel don't like) but a '_'
3724
3725	if ($this->parent !== null) {
3726	// Is there already such sheet name?
3727	if ($this->parent->sheetCodeNameExists($codeName)) {
3728	// Use name, but append with lowest possible integer
3729
3730	if (Shared\StringHelper::countCharacters($codeName) > 29) {
3731	$codeName = Shared\StringHelper::substring($codeName, 0, 29);
3732	}
3733	$i = 1;
3734	while ($this->getParentOrThrow()->sheetCodeNameExists($codeName . '_' . $i)) {
3735	++$i;
3736	if ($i == 10) {
3737	if (Shared\StringHelper::countCharacters($codeName) > 28) {
3738	$codeName = Shared\StringHelper::substring($codeName, 0, 28);
3739	}
3740	} elseif ($i == 100) {
3741	if (Shared\StringHelper::countCharacters($codeName) > 27) {
3742	$codeName = Shared\StringHelper::substring($codeName, 0, 27);
3743	}
3744	}
3745	}
3746
3747	$codeName .= '_' . $i; // ok, we have a valid name
3748	}
3749	}
3750	}
3751
3752	$this->codeName = $codeName;
3753
3754	return $this;
3755	}
3756
3757	/**
3758	* Return the code name of the sheet.
3759	*
3760	* @return null\|string
3761	*/
3762	public function getCodeName()
3763	{
3764	return $this->codeName;
3765	}
3766
3767	/**
3768	* Sheet has a code name ?
3769	*
3770	* @return bool
3771	*/
3772	public function hasCodeName()
3773	{
3774	return $this->codeName !== null;
3775	}
3776
3777	public static function nameRequiresQuotes(string $sheetName): bool
3778	{
3779	return !Preg::isMatch(self::SHEET_NAME_REQUIRES_NO_QUOTES, $sheetName);
3780	}
3781	}
3782