tablepress / libraries / vendor / PhpSpreadsheet / Spreadsheet.php

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

1,971 lines 48.4 KB

Raw Download Zip

1	<?php
2
3	namespace TablePress\PhpOffice\PhpSpreadsheet;
4
5	use TablePress\Composer\Pcre\Preg;
6	use JsonSerializable;
7	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Calculation;
8	use TablePress\PhpOffice\PhpSpreadsheet\Cell\IValueBinder;
9	use TablePress\PhpOffice\PhpSpreadsheet\Document\Properties;
10	use TablePress\PhpOffice\PhpSpreadsheet\Document\Security;
11	use TablePress\PhpOffice\PhpSpreadsheet\Shared\Date;
12	use TablePress\PhpOffice\PhpSpreadsheet\Shared\Font as SharedFont;
13	use TablePress\PhpOffice\PhpSpreadsheet\Shared\StringHelper;
14	use TablePress\PhpOffice\PhpSpreadsheet\Style\Style;
15	use TablePress\PhpOffice\PhpSpreadsheet\Worksheet\Iterator;
16	use TablePress\PhpOffice\PhpSpreadsheet\Worksheet\Table;
17	use TablePress\PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
18
19	class Spreadsheet implements JsonSerializable
20	{
21	// Allowable values for workbook window visibility
22	const VISIBILITY_VISIBLE = 'visible';
23	const VISIBILITY_HIDDEN = 'hidden';
24	const VISIBILITY_VERY_HIDDEN = 'veryHidden';
25
26	private const DEFINED_NAME_IS_RANGE = false;
27	private const DEFINED_NAME_IS_FORMULA = true;
28
29	private const WORKBOOK_VIEW_VISIBILITY_VALUES = [
30	self::VISIBILITY_VISIBLE,
31	self::VISIBILITY_HIDDEN,
32	self::VISIBILITY_VERY_HIDDEN,
33	];
34
35	protected int $excelCalendar = Date::CALENDAR_WINDOWS_1900;
36
37	/**
38	* Unique ID.
39	*/
40	private string $uniqueID;
41
42	/**
43	* Document properties.
44	*/
45	private Properties $properties;
46
47	/**
48	* Document security.
49	*/
50	private Security $security;
51
52	/**
53	* Collection of Worksheet objects.
54	*
55	* @var Worksheet[]
56	*/
57	private array $workSheetCollection;
58
59	/**
60	* Calculation Engine.
61	*/
62	private Calculation $calculationEngine;
63
64	/**
65	* Active sheet index.
66	*/
67	private int $activeSheetIndex;
68
69	/**
70	* Named ranges.
71	*
72	* @var DefinedName[]
73	*/
74	private array $definedNames;
75
76	/**
77	* CellXf supervisor.
78	*/
79	private Style $cellXfSupervisor;
80
81	/**
82	* CellXf collection.
83	*
84	* @var Style[]
85	*/
86	private array $cellXfCollection = [];
87
88	/**
89	* CellStyleXf collection.
90	*
91	* @var Style[]
92	*/
93	private array $cellStyleXfCollection = [];
94
95	/**
96	* hasMacros : this workbook have macros ?
97	*/
98	private bool $hasMacros = false;
99
100	/**
101	* macrosCode : all macros code as binary data (the vbaProject.bin file, this include form, code, etc.), null if no macro.
102	*/
103	private ?string $macrosCode = null;
104
105	/**
106	* macrosCertificate : if macros are signed, contains binary data vbaProjectSignature.bin file, null if not signed.
107	*/
108	private ?string $macrosCertificate = null;
109
110	/**
111	* ribbonXMLData : null if workbook isn't Excel 2007 or not contain a customized UI.
112	*
113	* @var null\|array{target: string, data: string}
114	*/
115	private ?array $ribbonXMLData = null;
116
117	/**
118	* ribbonBinObjects : null if workbook isn't Excel 2007 or not contain embedded objects (picture(s)) for Ribbon Elements
119	* ignored if $ribbonXMLData is null.
120	*
121	* @var null\|mixed[]
122	*/
123	private ?array $ribbonBinObjects = null;
124
125	/**
126	* List of unparsed loaded data for export to same format with better compatibility.
127	* It has to be minimized when the library start to support currently unparsed data.
128	*
129	* @var array<array<array<array<string>\|string>>>
130	*/
131	private array $unparsedLoadedData = [];
132
133	/**
134	* Controls visibility of the horizonal scroll bar in the application.
135	*/
136	private bool $showHorizontalScroll = true;
137
138	/**
139	* Controls visibility of the horizonal scroll bar in the application.
140	*/
141	private bool $showVerticalScroll = true;
142
143	/**
144	* Controls visibility of the sheet tabs in the application.
145	*/
146	private bool $showSheetTabs = true;
147
148	/**
149	* Specifies a boolean value that indicates whether the workbook window
150	* is minimized.
151	*/
152	private bool $minimized = false;
153
154	/**
155	* Specifies a boolean value that indicates whether to group dates
156	* when presenting the user with filtering options in the user
157	* interface.
158	*/
159	private bool $autoFilterDateGrouping = true;
160
161	/**
162	* Specifies the index to the first sheet in the book view.
163	*/
164	private int $firstSheetIndex = 0;
165
166	/**
167	* Specifies the visible status of the workbook.
168	*/
169	private string $visibility = self::VISIBILITY_VISIBLE;
170
171	/**
172	* Specifies the ratio between the workbook tabs bar and the horizontal
173	* scroll bar. TabRatio is assumed to be out of 1000 of the horizontal
174	* window width.
175	*/
176	private int $tabRatio = 600;
177
178	private Theme $theme;
179
180	private ?IValueBinder $valueBinder = null;
181
182	/** @var array<string, int> */
183	private array $fontCharsets = [
184	'B Nazanin' => SharedFont::CHARSET_ANSI_ARABIC,
185	];
186
187	/**
188	* @param int $charset uses any value from Shared\Font,
189	* but defaults to ARABIC because that is the only known
190	* charset for which this declaration might be needed
191	*/
192	public function addFontCharset(string $fontName, int $charset = SharedFont::CHARSET_ANSI_ARABIC): void
193	{
194	$this->fontCharsets[$fontName] = $charset;
195	}
196
197	public function getFontCharset(string $fontName): int
198	{
199	return $this->fontCharsets[$fontName] ?? -1;
200	}
201
202	/**
203	* Return all fontCharsets.
204	*
205	* @return array<string, int>
206	*/
207	public function getFontCharsets(): array
208	{
209	return $this->fontCharsets;
210	}
211
212	public function getTheme(): Theme
213	{
214	return $this->theme;
215	}
216
217	/**
218	* The workbook has macros ?
219	*/
220	public function hasMacros(): bool
221	{
222	return $this->hasMacros;
223	}
224
225	/**
226	* Define if a workbook has macros.
227	*
228	* @param bool $hasMacros true\|false
229	*/
230	public function setHasMacros(bool $hasMacros): void
231	{
232	$this->hasMacros = (bool) $hasMacros;
233	}
234
235	/**
236	* Set the macros code.
237	*/
238	public function setMacrosCode(?string $macroCode): void
239	{
240	$this->macrosCode = $macroCode;
241	$this->setHasMacros($macroCode !== null);
242	}
243
244	/**
245	* Return the macros code.
246	*/
247	public function getMacrosCode(): ?string
248	{
249	return $this->macrosCode;
250	}
251
252	/**
253	* Set the macros certificate.
254	*/
255	public function setMacrosCertificate(?string $certificate): void
256	{
257	$this->macrosCertificate = $certificate;
258	}
259
260	/**
261	* Is the project signed ?
262	*
263	* @return bool true\|false
264	*/
265	public function hasMacrosCertificate(): bool
266	{
267	return $this->macrosCertificate !== null;
268	}
269
270	/**
271	* Return the macros certificate.
272	*/
273	public function getMacrosCertificate(): ?string
274	{
275	return $this->macrosCertificate;
276	}
277
278	/**
279	* Remove all macros, certificate from spreadsheet.
280	*/
281	public function discardMacros(): void
282	{
283	$this->hasMacros = false;
284	$this->macrosCode = null;
285	$this->macrosCertificate = null;
286	}
287
288	/**
289	* set ribbon XML data.
290	* @param mixed $target
291	* @param mixed $xmlData
292	*/
293	public function setRibbonXMLData($target, $xmlData): void
294	{
295	if (is_string($target) && is_string($xmlData)) {
296	$this->ribbonXMLData = ['target' => $target, 'data' => $xmlData];
297	} else {
298	$this->ribbonXMLData = null;
299	}
300	}
301
302	/**
303	* retrieve ribbon XML Data.
304	*
305	* @return mixed[]
306	*/
307	public function getRibbonXMLData(string $what = 'all') //we need some constants here...
308	{
309	$returnData = null;
310	$what = strtolower($what);
311	switch ($what) {
312	case 'all':
313	$returnData = $this->ribbonXMLData;
314
315	break;
316	case 'target':
317	case 'data':
318	if (is_array($this->ribbonXMLData)) {
319	$returnData = $this->ribbonXMLData[$what];
320	}
321
322	break;
323	}
324
325	return $returnData;
326	}
327
328	/**
329	* store binaries ribbon objects (pictures).
330	* @param mixed $binObjectsNames
331	* @param mixed $binObjectsData
332	*/
333	public function setRibbonBinObjects($binObjectsNames, $binObjectsData): void
334	{
335	if ($binObjectsNames !== null && $binObjectsData !== null) {
336	$this->ribbonBinObjects = ['names' => $binObjectsNames, 'data' => $binObjectsData];
337	} else {
338	$this->ribbonBinObjects = null;
339	}
340	}
341
342	/**
343	* List of unparsed loaded data for export to same format with better compatibility.
344	* It has to be minimized when the library start to support currently unparsed data.
345	*
346	* @internal
347	*
348	* @return mixed[]
349	*/
350	public function getUnparsedLoadedData(): array
351	{
352	return $this->unparsedLoadedData;
353	}
354
355	/**
356	* List of unparsed loaded data for export to same format with better compatibility.
357	* It has to be minimized when the library start to support currently unparsed data.
358	*
359	* @internal
360	*
361	* @param array<array<array<array<string>\|string>>> $unparsedLoadedData
362	*/
363	public function setUnparsedLoadedData(array $unparsedLoadedData): void
364	{
365	$this->unparsedLoadedData = $unparsedLoadedData;
366	}
367
368	/**
369	* retrieve Binaries Ribbon Objects.
370	*
371	* @return mixed[]
372	*/
373	public function getRibbonBinObjects(string $what = 'all'): ?array
374	{
375	$ReturnData = null;
376	$what = strtolower($what);
377	switch ($what) {
378	case 'all':
379	return $this->ribbonBinObjects;
380	case 'names':
381	case 'data':
382	if (is_array($this->ribbonBinObjects) && is_array($this->ribbonBinObjects[$what] ?? null)) {
383	$ReturnData = $this->ribbonBinObjects[$what];
384	}
385
386	break;
387	case 'types':
388	if (
389	is_array($this->ribbonBinObjects)
390	&& isset($this->ribbonBinObjects['data']) && is_array($this->ribbonBinObjects['data'])
391	) {
392	$tmpTypes = array_keys($this->ribbonBinObjects['data']);
393	$ReturnData = array_unique(array_map(fn (string $path): string => pathinfo($path, PATHINFO_EXTENSION), $tmpTypes));
394	} else {
395	$ReturnData = []; // the caller want an array... not null if empty
396	}
397
398	break;
399	}
400
401	return $ReturnData;
402	}
403
404	/**
405	* This workbook have a custom UI ?
406	*/
407	public function hasRibbon(): bool
408	{
409	return $this->ribbonXMLData !== null;
410	}
411
412	/**
413	* This workbook have additional object for the ribbon ?
414	*/
415	public function hasRibbonBinObjects(): bool
416	{
417	return $this->ribbonBinObjects !== null;
418	}
419
420	/**
421	* This workbook has in cell images.
422	*/
423	public function hasInCellDrawings(): bool
424	{
425	$sheetCount = $this->getSheetCount();
426	for ($i = 0; $i < $sheetCount; ++$i) {
427	if ($this->getSheet($i)->getInCellDrawingCollection()->count() > 0) {
428	return true;
429	}
430	}
431
432	return false;
433	}
434
435	/**
436	* Check if a sheet with a specified code name already exists.
437	*
438	* @param string $codeName Name of the worksheet to check
439	*/
440	public function sheetCodeNameExists(string $codeName): bool
441	{
442	return $this->getSheetByCodeName($codeName) !== null;
443	}
444
445	/**
446	* Get sheet by code name. Warning : sheet don't have always a code name !
447	*
448	* @param string $codeName Sheet name
449	*/
450	public function getSheetByCodeName(string $codeName): ?Worksheet
451	{
452	$worksheetCount = count($this->workSheetCollection);
453	for ($i = 0; $i < $worksheetCount; ++$i) {
454	if ($this->workSheetCollection[$i]->getCodeName() == $codeName) {
455	return $this->workSheetCollection[$i];
456	}
457	}
458
459	return null;
460	}
461
462	/**
463	* Create a new PhpSpreadsheet with one Worksheet.
464	*/
465	public function __construct()
466	{
467	$this->uniqueID = uniqid('', true);
468	$this->calculationEngine = new Calculation($this);
469	$this->theme = new Theme();
470
471	// Initialise worksheet collection and add one worksheet
472	$this->workSheetCollection = [];
473	$this->workSheetCollection[] = new Worksheet($this);
474	$this->activeSheetIndex = 0;
475
476	// Create document properties
477	$this->properties = new Properties();
478
479	// Create document security
480	$this->security = new Security();
481
482	// Set defined names
483	$this->definedNames = [];
484
485	// Create the cellXf supervisor
486	$this->cellXfSupervisor = new Style(true);
487	$this->cellXfSupervisor->bindParent($this);
488
489	// Create the default style
490	$this->addCellXf(new Style());
491	$this->addCellStyleXf(new Style());
492	}
493
494	/**
495	* Code to execute when this worksheet is unset().
496	*/
497	public function __destruct()
498	{
499	$this->disconnectWorksheets();
500	unset($this->calculationEngine);
501	$this->cellXfCollection = [];
502	$this->cellStyleXfCollection = [];
503	$this->definedNames = [];
504	}
505
506	/**
507	* Disconnect all worksheets from this PhpSpreadsheet workbook object,
508	* typically so that the PhpSpreadsheet object can be unset.
509	*/
510	public function disconnectWorksheets(): void
511	{
512	foreach ($this->workSheetCollection as $worksheet) {
513	$worksheet->disconnectCells();
514	unset($worksheet);
515	}
516	$this->workSheetCollection = [];
517	$this->activeSheetIndex = -1;
518	}
519
520	/**
521	* Return the calculation engine for this worksheet.
522	*/
523	public function getCalculationEngine(): Calculation
524	{
525	return $this->calculationEngine;
526	}
527
528	/**
529	* Intended for use only via a destructor.
530	*
531	* @internal
532	*/
533	public function getCalculationEngineOrNull(): ?Calculation
534	{
535	if (!isset($this->calculationEngine)) { //* @phpstan-ignore isset.initializedProperty (may be null at destruct time)
536	return null;
537	}
538
539	return $this->calculationEngine;
540	}
541
542	/**
543	* Get properties.
544	*/
545	public function getProperties(): Properties
546	{
547	return $this->properties;
548	}
549
550	/**
551	* Set properties.
552	*/
553	public function setProperties(Properties $documentProperties): void
554	{
555	$this->properties = $documentProperties;
556	}
557
558	/**
559	* Get security.
560	*/
561	public function getSecurity(): Security
562	{
563	return $this->security;
564	}
565
566	/**
567	* Set security.
568	*/
569	public function setSecurity(Security $documentSecurity): void
570	{
571	$this->security = $documentSecurity;
572	}
573
574	/**
575	* Get active sheet.
576	*/
577	public function getActiveSheet(): Worksheet
578	{
579	return $this->getSheet($this->activeSheetIndex);
580	}
581
582	/**
583	* Create sheet and add it to this workbook.
584	*
585	* @param null\|int $sheetIndex Index where sheet should go (0,1,..., or null for last)
586	*/
587	public function createSheet(?int $sheetIndex = null): Worksheet
588	{
589	$newSheet = new Worksheet($this);
590	$this->addSheet($newSheet, $sheetIndex, true);
591
592	return $newSheet;
593	}
594
595	/**
596	* Check if a sheet with a specified name already exists.
597	*
598	* @param string $worksheetName Name of the worksheet to check
599	*/
600	public function sheetNameExists(string $worksheetName): bool
601	{
602	return $this->getSheetByName($worksheetName) !== null;
603	}
604
605	public function duplicateWorksheetByTitle(string $title): Worksheet
606	{
607	$original = $this->getSheetByNameOrThrow($title);
608	$index = $this->getIndex($original) + 1;
609	$clone = clone $original;
610
611	return $this->addSheet($clone, $index, true);
612	}
613
614	/**
615	* Add sheet.
616	*
617	* @param Worksheet $worksheet The worksheet to add
618	* @param null\|int $sheetIndex Index where sheet should go (0,1,..., or null for last)
619	*/
620	public function addSheet(Worksheet $worksheet, ?int $sheetIndex = null, bool $retitleIfNeeded = false): Worksheet
621	{
622	if ($retitleIfNeeded) {
623	$title = $worksheet->getTitle();
624	if ($this->sheetNameExists($title)) {
625	$i = 1;
626	$newTitle = "$title $i";
627	while ($this->sheetNameExists($newTitle)) {
628	++$i;
629	$newTitle = "$title $i";
630	}
631	$worksheet->setTitle($newTitle);
632	}
633	}
634	if ($this->sheetNameExists($worksheet->getTitle())) {
635	throw new Exception(
636	"Workbook already contains a worksheet named '{$worksheet->getTitle()}'. Rename this worksheet first."
637	);
638	}
639
640	if ($sheetIndex === null) {
641	if ($this->activeSheetIndex < 0) {
642	$this->activeSheetIndex = 0;
643	}
644	$this->workSheetCollection[] = $worksheet;
645	} else {
646	// Insert the sheet at the requested index
647	array_splice(
648	$this->workSheetCollection,
649	$sheetIndex,
650	0,
651	[$worksheet]
652	);
653
654	// Adjust active sheet index if necessary
655	if ($this->activeSheetIndex >= $sheetIndex) {
656	++$this->activeSheetIndex;
657	}
658	if ($this->activeSheetIndex < 0) {
659	$this->activeSheetIndex = 0;
660	}
661	}
662
663	if ($worksheet->getParent() === null) {
664	$worksheet->rebindParent($this);
665	}
666
667	return $worksheet;
668	}
669
670	/**
671	* Remove sheet by index.
672	*
673	* @param int $sheetIndex Index position of the worksheet to remove
674	*/
675	public function removeSheetByIndex(int $sheetIndex): void
676	{
677	$numSheets = count($this->workSheetCollection);
678	if ($sheetIndex > $numSheets - 1) {
679	throw new Exception(
680	"You tried to remove a sheet by the out of bounds index: {$sheetIndex}. The actual number of sheets is {$numSheets}."
681	);
682	}
683	array_splice($this->workSheetCollection, $sheetIndex, 1);
684
685	// Adjust active sheet index if necessary
686	if (
687	($this->activeSheetIndex >= $sheetIndex)
688	&& ($this->activeSheetIndex > 0 \|\| $numSheets <= 1)
689	) {
690	--$this->activeSheetIndex;
691	}
692	}
693
694	/**
695	* Get sheet by index.
696	*
697	* @param int $sheetIndex Sheet index
698	*/
699	public function getSheet(int $sheetIndex): Worksheet
700	{
701	if (!isset($this->workSheetCollection[$sheetIndex])) {
702	$numSheets = $this->getSheetCount();
703
704	throw new Exception(
705	"Your requested sheet index: {$sheetIndex} is out of bounds. The actual number of sheets is {$numSheets}."
706	);
707	}
708
709	return $this->workSheetCollection[$sheetIndex];
710	}
711
712	/**
713	* Get all sheets.
714	*
715	* @return Worksheet[]
716	*/
717	public function getAllSheets(): array
718	{
719	return $this->workSheetCollection;
720	}
721
722	/**
723	* Get sheet by name.
724	*
725	* @param string $worksheetName Sheet name
726	*/
727	public function getSheetByName(string $worksheetName): ?Worksheet
728	{
729	$trimWorksheetName = StringHelper::strToUpper(trim($worksheetName, "'"));
730	foreach ($this->workSheetCollection as $worksheet) {
731	if (StringHelper::strToUpper($worksheet->getTitle()) === $trimWorksheetName) {
732	return $worksheet;
733	}
734	}
735
736	return null;
737	}
738
739	/**
740	* Get sheet by name, throwing exception if not found.
741	*/
742	public function getSheetByNameOrThrow(string $worksheetName): Worksheet
743	{
744	$worksheet = $this->getSheetByName($worksheetName);
745	if ($worksheet === null) {
746	throw new Exception("Sheet $worksheetName does not exist.");
747	}
748
749	return $worksheet;
750	}
751
752	/**
753	* Get index for sheet.
754	*
755	* @return int index
756	*/
757	public function getIndex(Worksheet $worksheet, bool $noThrow = false): int
758	{
759	foreach ($this->workSheetCollection as $key => $value) {
760	if ($value === $worksheet) {
761	return $key;
762	}
763	}
764	if ($noThrow) {
765	return -1;
766	}
767
768	throw new Exception('Sheet does not exist.');
769	}
770
771	/**
772	* Set index for sheet by sheet name.
773	*
774	* @param string $worksheetName Sheet name to modify index for
775	* @param int $newIndexPosition New index for the sheet
776	*
777	* @return int New sheet index
778	*/
779	public function setIndexByName(string $worksheetName, int $newIndexPosition): int
780	{
781	$oldIndex = $this->getIndex($this->getSheetByNameOrThrow($worksheetName));
782	$worksheet = array_splice(
783	$this->workSheetCollection,
784	$oldIndex,
785	1
786	);
787	array_splice(
788	$this->workSheetCollection,
789	$newIndexPosition,
790	0,
791	$worksheet
792	);
793
794	return $newIndexPosition;
795	}
796
797	/**
798	* Get sheet count.
799	*/
800	public function getSheetCount(): int
801	{
802	return count($this->workSheetCollection);
803	}
804
805	/**
806	* Get active sheet index.
807	*
808	* @return int Active sheet index
809	*/
810	public function getActiveSheetIndex(): int
811	{
812	return $this->activeSheetIndex;
813	}
814
815	/**
816	* Set active sheet index.
817	*
818	* @param int $worksheetIndex Active sheet index
819	*/
820	public function setActiveSheetIndex(int $worksheetIndex): Worksheet
821	{
822	$numSheets = count($this->workSheetCollection);
823
824	if ($worksheetIndex > $numSheets - 1) {
825	throw new Exception(
826	"You tried to set a sheet active by the out of bounds index: {$worksheetIndex}. The actual number of sheets is {$numSheets}."
827	);
828	}
829	$this->activeSheetIndex = $worksheetIndex;
830
831	return $this->getActiveSheet();
832	}
833
834	/**
835	* Set active sheet index by name.
836	*
837	* @param string $worksheetName Sheet title
838	*/
839	public function setActiveSheetIndexByName(string $worksheetName): Worksheet
840	{
841	if (($worksheet = $this->getSheetByName($worksheetName)) instanceof Worksheet) {
842	$this->setActiveSheetIndex($this->getIndex($worksheet));
843
844	return $worksheet;
845	}
846
847	throw new Exception('Workbook does not contain sheet:' . $worksheetName);
848	}
849
850	/**
851	* Get sheet names.
852	*
853	* @return string[]
854	*/
855	public function getSheetNames(): array
856	{
857	$returnValue = [];
858	$worksheetCount = $this->getSheetCount();
859	for ($i = 0; $i < $worksheetCount; ++$i) {
860	$returnValue[] = $this->getSheet($i)->getTitle();
861	}
862
863	return $returnValue;
864	}
865
866	/**
867	* Add external sheet.
868	*
869	* @param Worksheet $worksheet External sheet to add
870	* @param null\|int $sheetIndex Index where sheet should go (0,1,..., or null for last)
871	*/
872	public function addExternalSheet(Worksheet $worksheet, ?int $sheetIndex = null): Worksheet
873	{
874	if ($this->sheetNameExists($worksheet->getTitle())) {
875	throw new Exception("Workbook already contains a worksheet named '{$worksheet->getTitle()}'. Rename the external sheet first.");
876	}
877
878	// count how many cellXfs there are in this workbook currently, we will need this below
879	$countCellXfs = count($this->cellXfCollection);
880
881	// copy all the shared cellXfs from the external workbook and append them to the current
882	foreach ($worksheet->getParentOrThrow()->getCellXfCollection() as $cellXf) {
883	$this->addCellXf(clone $cellXf);
884	}
885
886	// move sheet to this workbook
887	$worksheet->rebindParent($this);
888
889	// update the cellXfs
890	foreach ($worksheet->getCoordinates(false) as $coordinate) {
891	$cell = $worksheet->getCell($coordinate);
892	$cell->setXfIndex($cell->getXfIndex() + $countCellXfs);
893	}
894
895	// update the column dimensions Xfs
896	foreach ($worksheet->getColumnDimensions() as $columnDimension) {
897	$columnDimension->setXfIndex($columnDimension->getXfIndex() + $countCellXfs);
898	}
899
900	// update the row dimensions Xfs
901	foreach ($worksheet->getRowDimensions() as $rowDimension) {
902	$xfIndex = $rowDimension->getXfIndex();
903	if ($xfIndex !== null) {
904	$rowDimension->setXfIndex($xfIndex + $countCellXfs);
905	}
906	}
907
908	return $this->addSheet($worksheet, $sheetIndex);
909	}
910
911	/**
912	* Get an array of all Named Ranges.
913	*
914	* @return DefinedName[]
915	*/
916	public function getNamedRanges(): array
917	{
918	return array_filter(
919	$this->definedNames,
920	fn (DefinedName $definedName): bool => $definedName->isFormula() === self::DEFINED_NAME_IS_RANGE
921	);
922	}
923
924	/**
925	* Get an array of all Named Formulae.
926	*
927	* @return DefinedName[]
928	*/
929	public function getNamedFormulae(): array
930	{
931	return array_filter(
932	$this->definedNames,
933	fn (DefinedName $definedName): bool => $definedName->isFormula() === self::DEFINED_NAME_IS_FORMULA
934	);
935	}
936
937	/**
938	* Get an array of all Defined Names (both named ranges and named formulae).
939	*
940	* @return DefinedName[]
941	*/
942	public function getDefinedNames(): array
943	{
944	return $this->definedNames;
945	}
946
947	/**
948	* Add a named range.
949	* If a named range with this name already exists, then this will replace the existing value.
950	*/
951	public function addNamedRange(NamedRange $namedRange): void
952	{
953	$this->addDefinedName($namedRange);
954	}
955
956	/**
957	* Add a named formula.
958	* If a named formula with this name already exists, then this will replace the existing value.
959	*/
960	public function addNamedFormula(NamedFormula $namedFormula): void
961	{
962	$this->addDefinedName($namedFormula);
963	}
964
965	/**
966	* Add a defined name (either a named range or a named formula).
967	* If a defined named with this name already exists, then this will replace the existing value.
968	*/
969	public function addDefinedName(DefinedName $definedName): void
970	{
971	$upperCaseName = StringHelper::strToUpper($definedName->getName());
972	if ($definedName->getScope() == null) {
973	// global scope
974	$this->definedNames[$upperCaseName] = $definedName;
975	} else {
976	// local scope
977	$this->definedNames[$definedName->getScope()->getTitle() . '!' . $upperCaseName] = $definedName;
978	}
979	}
980
981	/**
982	* Get named range.
983	*
984	* @param null\|Worksheet $worksheet Scope. Use null for global scope
985	*/
986	public function getNamedRange(string $namedRange, ?Worksheet $worksheet = null): ?NamedRange
987	{
988	$returnValue = null;
989
990	if ($namedRange !== '') {
991	$namedRange = StringHelper::strToUpper($namedRange);
992	// first look for global named range
993	$returnValue = $this->getGlobalDefinedNameByType($namedRange, self::DEFINED_NAME_IS_RANGE);
994	// then look for local named range (has priority over global named range if both names exist)
995	$returnValue = $this->getLocalDefinedNameByType($namedRange, self::DEFINED_NAME_IS_RANGE, $worksheet) ?: $returnValue;
996	}
997
998	return $returnValue instanceof NamedRange ? $returnValue : null;
999	}
1000
1001	/**
1002	* Get named formula.
1003	*
1004	* @param null\|Worksheet $worksheet Scope. Use null for global scope
1005	*/
1006	public function getNamedFormula(string $namedFormula, ?Worksheet $worksheet = null): ?NamedFormula
1007	{
1008	$returnValue = null;
1009
1010	if ($namedFormula !== '') {
1011	$namedFormula = StringHelper::strToUpper($namedFormula);
1012	// first look for global named formula
1013	$returnValue = $this->getGlobalDefinedNameByType($namedFormula, self::DEFINED_NAME_IS_FORMULA);
1014	// then look for local named formula (has priority over global named formula if both names exist)
1015	$returnValue = $this->getLocalDefinedNameByType($namedFormula, self::DEFINED_NAME_IS_FORMULA, $worksheet) ?: $returnValue;
1016	}
1017
1018	return $returnValue instanceof NamedFormula ? $returnValue : null;
1019	}
1020
1021	private function getGlobalDefinedNameByType(string $name, bool $type): ?DefinedName
1022	{
1023	if (isset($this->definedNames[$name]) && $this->definedNames[$name]->isFormula() === $type) {
1024	return $this->definedNames[$name];
1025	}
1026
1027	return null;
1028	}
1029
1030	private function getLocalDefinedNameByType(string $name, bool $type, ?Worksheet $worksheet = null): ?DefinedName
1031	{
1032	if (
1033	($worksheet !== null) && isset($this->definedNames[$worksheet->getTitle() . '!' . $name])
1034	&& $this->definedNames[$worksheet->getTitle() . '!' . $name]->isFormula() === $type
1035	) {
1036	return $this->definedNames[$worksheet->getTitle() . '!' . $name];
1037	}
1038
1039	return null;
1040	}
1041
1042	/**
1043	* Get named range.
1044	*
1045	* @param null\|Worksheet $worksheet Scope. Use null for global scope
1046	*/
1047	public function getDefinedName(string $definedName, ?Worksheet $worksheet = null): ?DefinedName
1048	{
1049	$returnValue = null;
1050
1051	if ($definedName !== '') {
1052	$definedName = StringHelper::strToUpper($definedName);
1053	// first look for global defined name
1054	foreach ($this->definedNames as $dn) {
1055	$upper = StringHelper::strToUpper($dn->getName());
1056	if (
1057	!$dn->getLocalOnly()
1058	&& $definedName === $upper
1059	) {
1060	$returnValue = $dn;
1061
1062	break;
1063	}
1064	}
1065
1066	// then look for local defined name (has priority over global defined name if both names exist)
1067	if ($worksheet !== null) {
1068	$wsTitle = StringHelper::strToUpper($worksheet->getTitle());
1069	$definedName = Preg::replace('/^.*!/', '', $definedName);
1070	foreach ($this->definedNames as $dn) {
1071	$sheet = $dn->getScope() ?? $dn->getWorksheet();
1072	$upper = StringHelper::strToUpper($dn->getName());
1073	$upperTitle = StringHelper::strToUpper((string) (($nullsafeVariable1 = $sheet) ? $nullsafeVariable1->getTitle() : null));
1074	if (
1075	$dn->getLocalOnly()
1076	&& $upper === $definedName
1077	&& $upperTitle === $wsTitle
1078	) {
1079	return $dn;
1080	}
1081	}
1082	}
1083	}
1084
1085	return $returnValue;
1086	}
1087
1088	/**
1089	* Remove named range.
1090	*
1091	* @param null\|Worksheet $worksheet scope: use null for global scope
1092	*
1093	* @return $this
1094	*/
1095	public function removeNamedRange(string $namedRange, ?Worksheet $worksheet = null): self
1096	{
1097	if ($this->getNamedRange($namedRange, $worksheet) === null) {
1098	return $this;
1099	}
1100
1101	return $this->removeDefinedName($namedRange, $worksheet);
1102	}
1103
1104	/**
1105	* Remove named formula.
1106	*
1107	* @param null\|Worksheet $worksheet scope: use null for global scope
1108	*
1109	* @return $this
1110	*/
1111	public function removeNamedFormula(string $namedFormula, ?Worksheet $worksheet = null): self
1112	{
1113	if ($this->getNamedFormula($namedFormula, $worksheet) === null) {
1114	return $this;
1115	}
1116
1117	return $this->removeDefinedName($namedFormula, $worksheet);
1118	}
1119
1120	/**
1121	* Remove defined name.
1122	*
1123	* @param null\|Worksheet $worksheet scope: use null for global scope
1124	*
1125	* @return $this
1126	*/
1127	public function removeDefinedName(string $definedName, ?Worksheet $worksheet = null): self
1128	{
1129	$definedName = StringHelper::strToUpper($definedName);
1130
1131	if ($worksheet === null) {
1132	if (isset($this->definedNames[$definedName])) {
1133	unset($this->definedNames[$definedName]);
1134	}
1135	} else {
1136	if (isset($this->definedNames[$worksheet->getTitle() . '!' . $definedName])) {
1137	unset($this->definedNames[$worksheet->getTitle() . '!' . $definedName]);
1138	} elseif (isset($this->definedNames[$definedName])) {
1139	unset($this->definedNames[$definedName]);
1140	}
1141	}
1142
1143	return $this;
1144	}
1145
1146	/**
1147	* Get worksheet iterator.
1148	*/
1149	public function getWorksheetIterator(): Iterator
1150	{
1151	return new Iterator($this);
1152	}
1153
1154	/**
1155	* Copy workbook (!= clone!).
1156	*
1157	* Uses serialize/unserialize which is broadly faster than clone across
1158	* PHP versions and platforms, though clone uses less memory.
1159	*
1160	* @see \PhpOffice\PhpSpreadsheetBenchmarks\SpreadsheetCopyBenchmarkTest
1161	*/
1162	public function copy(): self
1163	{
1164	return unserialize(serialize($this)); //* @phpstan-ignore return.type (phpstan is wrong)
1165	}
1166
1167	/**
1168	* Implement PHP __clone to create a deep clone, not just a shallow copy.
1169	*
1170	* Clone uses less memory than serialize/unserialize but speed varies
1171	* across PHP versions and platforms.
1172	*
1173	* @see \PhpOffice\PhpSpreadsheetBenchmarks\SpreadsheetCopyBenchmarkTest
1174	*/
1175	public function __clone()
1176	{
1177	$this->uniqueID = uniqid('', true);
1178
1179	$usedKeys = [];
1180	// I don't know why new Style rather than clone.
1181	$this->cellXfSupervisor = new Style(true);
1182	//$this->cellXfSupervisor = clone $this->cellXfSupervisor;
1183	$this->cellXfSupervisor->bindParent($this);
1184	$usedKeys['cellXfSupervisor'] = true;
1185
1186	$oldCalc = $this->calculationEngine;
1187	$this->calculationEngine = new Calculation($this);
1188	$this->calculationEngine
1189	->setSuppressFormulaErrors(
1190	$oldCalc->getSuppressFormulaErrors()
1191	)
1192	->setCalculationCacheEnabled(
1193	$oldCalc->getCalculationCacheEnabled()
1194	)
1195	->setBranchPruningEnabled(
1196	$oldCalc->getBranchPruningEnabled()
1197	)
1198	->setInstanceArrayReturnType(
1199	$oldCalc->getInstanceArrayReturnType()
1200	);
1201	$usedKeys['calculationEngine'] = true;
1202
1203	$currentCollection = $this->cellStyleXfCollection;
1204	$this->cellStyleXfCollection = [];
1205	foreach ($currentCollection as $item) {
1206	$clone = $item->exportArray();
1207	$style = (new Style())->applyFromArray($clone);
1208	$this->addCellStyleXf($style);
1209	}
1210	$usedKeys['cellStyleXfCollection'] = true;
1211
1212	$currentCollection = $this->cellXfCollection;
1213	$this->cellXfCollection = [];
1214	foreach ($currentCollection as $item) {
1215	$clone = $item->exportArray();
1216	$style = (new Style())->applyFromArray($clone);
1217	$this->addCellXf($style);
1218	}
1219	$usedKeys['cellXfCollection'] = true;
1220
1221	$currentCollection = $this->workSheetCollection;
1222	$this->workSheetCollection = [];
1223	foreach ($currentCollection as $item) {
1224	$clone = clone $item;
1225	$clone->setParent($this);
1226	$this->workSheetCollection[] = $clone;
1227	}
1228	$usedKeys['workSheetCollection'] = true;
1229
1230	foreach (get_object_vars($this) as $key => $val) {
1231	if (isset($usedKeys[$key])) {
1232	continue;
1233	}
1234	switch ($key) {
1235	// arrays of objects not covered above
1236	case 'definedNames':
1237	/** @var DefinedName[] */
1238	$currentCollection = $val;
1239	$this->$key = [];
1240	foreach ($currentCollection as $item) {
1241	$clone = clone $item;
1242	$title = ($nullsafeVariable2 = $clone->getWorksheet()) ? $nullsafeVariable2->getTitle() : null;
1243	if ($title !== null) {
1244	$ws = $this->getSheetByName($title);
1245	$clone->setWorksheet($ws);
1246	}
1247	$title = ($nullsafeVariable3 = $clone->getScope()) ? $nullsafeVariable3->getTitle() : null;
1248	if ($title !== null) {
1249	$ws = $this->getSheetByName($title);
1250	$clone->setScope($ws);
1251	}
1252	$this->{$key}[] = $clone;
1253	}
1254
1255	break;
1256	default:
1257	if (is_object($val)) {
1258	$this->$key = clone $val;
1259	}
1260	}
1261	}
1262	}
1263
1264	/**
1265	* Get the workbook collection of cellXfs.
1266	*
1267	* @return Style[]
1268	*/
1269	public function getCellXfCollection(): array
1270	{
1271	return $this->cellXfCollection;
1272	}
1273
1274	/**
1275	* Get cellXf by index.
1276	*/
1277	public function getCellXfByIndex(int $cellStyleIndex): Style
1278	{
1279	return $this->cellXfCollection[$cellStyleIndex];
1280	}
1281
1282	public function getCellXfByIndexOrNull(?int $cellStyleIndex): ?Style
1283	{
1284	return ($cellStyleIndex === null) ? null : ($this->cellXfCollection[$cellStyleIndex] ?? null);
1285	}
1286
1287	/**
1288	* Get cellXf by hash code.
1289	*
1290	* @return false\|Style
1291	*/
1292	public function getCellXfByHashCode(string $hashcode)
1293	{
1294	foreach ($this->cellXfCollection as $cellXf) {
1295	if ($cellXf->getHashCode() === $hashcode) {
1296	return $cellXf;
1297	}
1298	}
1299
1300	return false;
1301	}
1302
1303	/**
1304	* Check if style exists in style collection.
1305	*/
1306	public function cellXfExists(Style $cellStyleIndex): bool
1307	{
1308	return in_array($cellStyleIndex, $this->cellXfCollection, true);
1309	}
1310
1311	/**
1312	* Get default style.
1313	*/
1314	public function getDefaultStyle(): Style
1315	{
1316	if (isset($this->cellXfCollection[0])) {
1317	return $this->cellXfCollection[0];
1318	}
1319
1320	throw new Exception('No default style found for this workbook');
1321	}
1322
1323	/**
1324	* Add a cellXf to the workbook.
1325	*/
1326	public function addCellXf(Style $style): void
1327	{
1328	$this->cellXfCollection[] = $style;
1329	$style->setIndex(count($this->cellXfCollection) - 1);
1330	}
1331
1332	/**
1333	* Remove cellXf by index. It is ensured that all cells get their xf index updated.
1334	*
1335	* @param int $cellStyleIndex Index to cellXf
1336	*/
1337	public function removeCellXfByIndex(int $cellStyleIndex): void
1338	{
1339	if ($cellStyleIndex > count($this->cellXfCollection) - 1) {
1340	throw new Exception('CellXf index is out of bounds.');
1341	}
1342
1343	// first remove the cellXf
1344	array_splice($this->cellXfCollection, $cellStyleIndex, 1);
1345
1346	// then update cellXf indexes for cells
1347	foreach ($this->workSheetCollection as $worksheet) {
1348	foreach ($worksheet->getCoordinates(false) as $coordinate) {
1349	$cell = $worksheet->getCell($coordinate);
1350	$xfIndex = $cell->getXfIndex();
1351	if ($xfIndex > $cellStyleIndex) {
1352	// decrease xf index by 1
1353	$cell->setXfIndex($xfIndex - 1);
1354	} elseif ($xfIndex == $cellStyleIndex) {
1355	// set to default xf index 0
1356	$cell->setXfIndex(0);
1357	}
1358	}
1359	}
1360	}
1361
1362	/**
1363	* Get the cellXf supervisor.
1364	*/
1365	public function getCellXfSupervisor(): Style
1366	{
1367	return $this->cellXfSupervisor;
1368	}
1369
1370	/**
1371	* Get the workbook collection of cellStyleXfs.
1372	*
1373	* @return Style[]
1374	*/
1375	public function getCellStyleXfCollection(): array
1376	{
1377	return $this->cellStyleXfCollection;
1378	}
1379
1380	/**
1381	* Get cellStyleXf by index.
1382	*
1383	* @param int $cellStyleIndex Index to cellXf
1384	*/
1385	public function getCellStyleXfByIndex(int $cellStyleIndex): Style
1386	{
1387	return $this->cellStyleXfCollection[$cellStyleIndex];
1388	}
1389
1390	/**
1391	* Get cellStyleXf by hash code.
1392	*
1393	* @return false\|Style
1394	*/
1395	public function getCellStyleXfByHashCode(string $hashcode)
1396	{
1397	foreach ($this->cellStyleXfCollection as $cellStyleXf) {
1398	if ($cellStyleXf->getHashCode() === $hashcode) {
1399	return $cellStyleXf;
1400	}
1401	}
1402
1403	return false;
1404	}
1405
1406	/**
1407	* Add a cellStyleXf to the workbook.
1408	*/
1409	public function addCellStyleXf(Style $style): void
1410	{
1411	$this->cellStyleXfCollection[] = $style;
1412	$style->setIndex(count($this->cellStyleXfCollection) - 1);
1413	}
1414
1415	/**
1416	* Remove cellStyleXf by index.
1417	*
1418	* @param int $cellStyleIndex Index to cellXf
1419	*/
1420	public function removeCellStyleXfByIndex(int $cellStyleIndex): void
1421	{
1422	if ($cellStyleIndex > count($this->cellStyleXfCollection) - 1) {
1423	throw new Exception('CellStyleXf index is out of bounds.');
1424	}
1425	array_splice($this->cellStyleXfCollection, $cellStyleIndex, 1);
1426	}
1427
1428	/**
1429	* Eliminate all unneeded cellXf and afterwards update the xfIndex for all cells
1430	* and columns in the workbook.
1431	*/
1432	public function garbageCollect(): void
1433	{
1434	// how many references are there to each cellXf ?
1435	$countReferencesCellXf = [];
1436	foreach ($this->cellXfCollection as $index => $cellXf) {
1437	$countReferencesCellXf[$index] = 0;
1438	}
1439
1440	foreach ($this->getWorksheetIterator() as $sheet) {
1441	// from cells
1442	foreach ($sheet->getCoordinates(false) as $coordinate) {
1443	$cell = $sheet->getCell($coordinate);
1444	++$countReferencesCellXf[$cell->getXfIndex()];
1445	}
1446
1447	// from row dimensions
1448	foreach ($sheet->getRowDimensions() as $rowDimension) {
1449	if ($rowDimension->getXfIndex() !== null) {
1450	++$countReferencesCellXf[$rowDimension->getXfIndex()];
1451	}
1452	}
1453
1454	// from column dimensions
1455	foreach ($sheet->getColumnDimensions() as $columnDimension) {
1456	++$countReferencesCellXf[$columnDimension->getXfIndex()];
1457	}
1458	}
1459
1460	// remove cellXfs without references and create mapping so we can update xfIndex
1461	// for all cells and columns
1462	$countNeededCellXfs = 0;
1463	$map = [];
1464	foreach ($this->cellXfCollection as $index => $cellXf) {
1465	if ($countReferencesCellXf[$index] > 0 \|\| $index == 0) { // we must never remove the first cellXf
1466	++$countNeededCellXfs;
1467	} else {
1468	unset($this->cellXfCollection[$index]);
1469	}
1470	$map[$index] = $countNeededCellXfs - 1;
1471	}
1472	$this->cellXfCollection = array_values($this->cellXfCollection);
1473
1474	// update the index for all cellXfs
1475	foreach ($this->cellXfCollection as $i => $cellXf) {
1476	$cellXf->setIndex($i);
1477	}
1478
1479	// make sure there is always at least one cellXf (there should be)
1480	if (empty($this->cellXfCollection)) {
1481	$this->cellXfCollection[] = new Style();
1482	}
1483
1484	// update the xfIndex for all cells, row dimensions, column dimensions
1485	foreach ($this->getWorksheetIterator() as $sheet) {
1486	// for all cells
1487	foreach ($sheet->getCoordinates(false) as $coordinate) {
1488	$cell = $sheet->getCell($coordinate);
1489	$cell->setXfIndex($map[$cell->getXfIndex()]);
1490	}
1491
1492	// for all row dimensions
1493	foreach ($sheet->getRowDimensions() as $rowDimension) {
1494	if ($rowDimension->getXfIndex() !== null) {
1495	$rowDimension->setXfIndex($map[$rowDimension->getXfIndex()]);
1496	}
1497	}
1498
1499	// for all column dimensions
1500	foreach ($sheet->getColumnDimensions() as $columnDimension) {
1501	$columnDimension->setXfIndex($map[$columnDimension->getXfIndex()]);
1502	}
1503
1504	// also do garbage collection for all the sheets
1505	$sheet->garbageCollect();
1506	}
1507	}
1508
1509	/**
1510	* Return the unique ID value assigned to this spreadsheet workbook.
1511	*
1512	* @deprecated 5.2.0 Serves no useful purpose. No replacement.
1513	*
1514	* @codeCoverageIgnore
1515	*/
1516	public function getID(): string
1517	{
1518	return $this->uniqueID;
1519	}
1520
1521	/**
1522	* Get the visibility of the horizonal scroll bar in the application.
1523	*
1524	* @return bool True if horizonal scroll bar is visible
1525	*/
1526	public function getShowHorizontalScroll(): bool
1527	{
1528	return $this->showHorizontalScroll;
1529	}
1530
1531	/**
1532	* Set the visibility of the horizonal scroll bar in the application.
1533	*
1534	* @param bool $showHorizontalScroll True if horizonal scroll bar is visible
1535	*/
1536	public function setShowHorizontalScroll(bool $showHorizontalScroll): void
1537	{
1538	$this->showHorizontalScroll = (bool) $showHorizontalScroll;
1539	}
1540
1541	/**
1542	* Get the visibility of the vertical scroll bar in the application.
1543	*
1544	* @return bool True if vertical scroll bar is visible
1545	*/
1546	public function getShowVerticalScroll(): bool
1547	{
1548	return $this->showVerticalScroll;
1549	}
1550
1551	/**
1552	* Set the visibility of the vertical scroll bar in the application.
1553	*
1554	* @param bool $showVerticalScroll True if vertical scroll bar is visible
1555	*/
1556	public function setShowVerticalScroll(bool $showVerticalScroll): void
1557	{
1558	$this->showVerticalScroll = (bool) $showVerticalScroll;
1559	}
1560
1561	/**
1562	* Get the visibility of the sheet tabs in the application.
1563	*
1564	* @return bool True if the sheet tabs are visible
1565	*/
1566	public function getShowSheetTabs(): bool
1567	{
1568	return $this->showSheetTabs;
1569	}
1570
1571	/**
1572	* Set the visibility of the sheet tabs in the application.
1573	*
1574	* @param bool $showSheetTabs True if sheet tabs are visible
1575	*/
1576	public function setShowSheetTabs(bool $showSheetTabs): void
1577	{
1578	$this->showSheetTabs = (bool) $showSheetTabs;
1579	}
1580
1581	/**
1582	* Return whether the workbook window is minimized.
1583	*
1584	* @return bool true if workbook window is minimized
1585	*/
1586	public function getMinimized(): bool
1587	{
1588	return $this->minimized;
1589	}
1590
1591	/**
1592	* Set whether the workbook window is minimized.
1593	*
1594	* @param bool $minimized true if workbook window is minimized
1595	*/
1596	public function setMinimized(bool $minimized): void
1597	{
1598	$this->minimized = (bool) $minimized;
1599	}
1600
1601	/**
1602	* Return whether to group dates when presenting the user with
1603	* filtering options in the user interface.
1604	*
1605	* @return bool true if workbook window is minimized
1606	*/
1607	public function getAutoFilterDateGrouping(): bool
1608	{
1609	return $this->autoFilterDateGrouping;
1610	}
1611
1612	/**
1613	* Set whether to group dates when presenting the user with
1614	* filtering options in the user interface.
1615	*
1616	* @param bool $autoFilterDateGrouping true if workbook window is minimized
1617	*/
1618	public function setAutoFilterDateGrouping(bool $autoFilterDateGrouping): void
1619	{
1620	$this->autoFilterDateGrouping = (bool) $autoFilterDateGrouping;
1621	}
1622
1623	/**
1624	* Return the first sheet in the book view.
1625	*
1626	* @return int First sheet in book view
1627	*/
1628	public function getFirstSheetIndex(): int
1629	{
1630	return $this->firstSheetIndex;
1631	}
1632
1633	/**
1634	* Set the first sheet in the book view.
1635	*
1636	* @param int $firstSheetIndex First sheet in book view
1637	*/
1638	public function setFirstSheetIndex(int $firstSheetIndex): void
1639	{
1640	if ($firstSheetIndex >= 0) {
1641	$this->firstSheetIndex = (int) $firstSheetIndex;
1642	} else {
1643	throw new Exception('First sheet index must be a positive integer.');
1644	}
1645	}
1646
1647	/**
1648	* Return the visibility status of the workbook.
1649	*
1650	* This may be one of the following three values:
1651	* - visibile
1652	*
1653	* @return string Visible status
1654	*/
1655	public function getVisibility(): string
1656	{
1657	return $this->visibility;
1658	}
1659
1660	/**
1661	* Set the visibility status of the workbook.
1662	*
1663	* Valid values are:
1664	* - 'visible' (self::VISIBILITY_VISIBLE):
1665	* Workbook window is visible
1666	* - 'hidden' (self::VISIBILITY_HIDDEN):
1667	* Workbook window is hidden, but can be shown by the user
1668	* via the user interface
1669	* - 'veryHidden' (self::VISIBILITY_VERY_HIDDEN):
1670	* Workbook window is hidden and cannot be shown in the
1671	* user interface.
1672	*
1673	* @param null\|string $visibility visibility status of the workbook
1674	*/
1675	public function setVisibility(?string $visibility): void
1676	{
1677	if ($visibility === null) {
1678	$visibility = self::VISIBILITY_VISIBLE;
1679	}
1680
1681	if (in_array($visibility, self::WORKBOOK_VIEW_VISIBILITY_VALUES)) {
1682	$this->visibility = $visibility;
1683	} else {
1684	throw new Exception('Invalid visibility value.');
1685	}
1686	}
1687
1688	/**
1689	* Get the ratio between the workbook tabs bar and the horizontal scroll bar.
1690	* TabRatio is assumed to be out of 1000 of the horizontal window width.
1691	*
1692	* @return int Ratio between the workbook tabs bar and the horizontal scroll bar
1693	*/
1694	public function getTabRatio(): int
1695	{
1696	return $this->tabRatio;
1697	}
1698
1699	/**
1700	* Set the ratio between the workbook tabs bar and the horizontal scroll bar
1701	* TabRatio is assumed to be out of 1000 of the horizontal window width.
1702	*
1703	* @param int $tabRatio Ratio between the tabs bar and the horizontal scroll bar
1704	*/
1705	public function setTabRatio(int $tabRatio): void
1706	{
1707	if ($tabRatio >= 0 && $tabRatio <= 1000) {
1708	$this->tabRatio = (int) $tabRatio;
1709	} else {
1710	throw new Exception('Tab ratio must be between 0 and 1000.');
1711	}
1712	}
1713
1714	public function reevaluateAutoFilters(bool $resetToMax): void
1715	{
1716	foreach ($this->workSheetCollection as $sheet) {
1717	$filter = $sheet->getAutoFilter();
1718	if (!empty($filter->getRange())) {
1719	if ($resetToMax) {
1720	$filter->setRangeToMaxRow();
1721	}
1722	$filter->showHideRows();
1723	}
1724	}
1725	}
1726
1727	/**
1728	* @throws Exception
1729	* @return mixed
1730	*/
1731	#[\ReturnTypeWillChange]
1732	public function jsonSerialize()
1733	{
1734	throw new Exception('Spreadsheet objects cannot be json encoded');
1735	}
1736
1737	public function resetThemeFonts(): void
1738	{
1739	$majorFontLatin = $this->theme->getMajorFontLatin();
1740	$minorFontLatin = $this->theme->getMinorFontLatin();
1741	foreach ($this->cellXfCollection as $cellStyleXf) {
1742	$scheme = $cellStyleXf->getFont()->getScheme();
1743	if ($scheme === 'major') {
1744	$cellStyleXf->getFont()->setName($majorFontLatin)->setScheme($scheme);
1745	} elseif ($scheme === 'minor') {
1746	$cellStyleXf->getFont()->setName($minorFontLatin)->setScheme($scheme);
1747	}
1748	}
1749	foreach ($this->cellStyleXfCollection as $cellStyleXf) {
1750	$scheme = $cellStyleXf->getFont()->getScheme();
1751	if ($scheme === 'major') {
1752	$cellStyleXf->getFont()->setName($majorFontLatin)->setScheme($scheme);
1753	} elseif ($scheme === 'minor') {
1754	$cellStyleXf->getFont()->setName($minorFontLatin)->setScheme($scheme);
1755	}
1756	}
1757	}
1758
1759	public function getTableByName(string $tableName): ?Table
1760	{
1761	$table = null;
1762	foreach ($this->workSheetCollection as $sheet) {
1763	$table = $sheet->getTableByName($tableName);
1764	if ($table !== null) {
1765	break;
1766	}
1767	}
1768
1769	return $table;
1770	}
1771
1772	/**
1773	* @return bool Success or failure
1774	*/
1775	public function setExcelCalendar(int $baseYear): bool
1776	{
1777	if (($baseYear === Date::CALENDAR_WINDOWS_1900) \|\| ($baseYear === Date::CALENDAR_MAC_1904)) {
1778	$this->excelCalendar = $baseYear;
1779
1780	return true;
1781	}
1782
1783	return false;
1784	}
1785
1786	/**
1787	* @return int Excel base date (1900 or 1904)
1788	*/
1789	public function getExcelCalendar(): int
1790	{
1791	return $this->excelCalendar;
1792	}
1793
1794	public function deleteLegacyDrawing(Worksheet $worksheet): void
1795	{
1796	unset($this->unparsedLoadedData['sheets'][$worksheet->getCodeName()]['legacyDrawing']);
1797	}
1798
1799	public function getLegacyDrawing(Worksheet $worksheet): ?string
1800	{
1801	/** @var ?string */
1802	$temp = $this->unparsedLoadedData['sheets'][$worksheet->getCodeName()]['legacyDrawing'] ?? null;
1803
1804	return $temp;
1805	}
1806
1807	public function getValueBinder(): ?IValueBinder
1808	{
1809	return $this->valueBinder;
1810	}
1811
1812	public function setValueBinder(?IValueBinder $valueBinder): self
1813	{
1814	$this->valueBinder = $valueBinder;
1815
1816	return $this;
1817	}
1818
1819	/**
1820	* All the PDF writers treat charts as if they occupy a single cell.
1821	* This will be better most of the time.
1822	* It is not needed for any other output type.
1823	* It changes the contents of the spreadsheet, so you might
1824	* be better off cloning the spreadsheet and then using
1825	* this method on, and then writing, the clone.
1826	*/
1827	public function mergeChartCellsForPdf(): void
1828	{
1829	foreach ($this->workSheetCollection as $worksheet) {
1830	foreach ($worksheet->getChartCollection() as $chart) {
1831	$br = $chart->getBottomRightCell();
1832	$tl = $chart->getTopLeftCell();
1833	if ($br !== '' && $br !== $tl) {
1834	if (!$worksheet->cellExists($br)) {
1835	$worksheet->getCell($br)->setValue(' ');
1836	}
1837	$worksheet->mergeCells("$tl:$br");
1838	}
1839	}
1840	}
1841	}
1842
1843	/**
1844	* All the PDF writers do better with drawings than charts.
1845	* This will be better some of the time.
1846	* It is not needed for any other output type.
1847	* It changes the contents of the spreadsheet, so you might
1848	* be better off cloning the spreadsheet and then using
1849	* this method on, and then writing, the clone.
1850	*/
1851	public function mergeDrawingCellsForPdf(): void
1852	{
1853	foreach ($this->workSheetCollection as $worksheet) {
1854	foreach ($worksheet->getDrawingCollection() as $drawing) {
1855	$br = $drawing->getCoordinates2();
1856	$tl = $drawing->getCoordinates();
1857	if ($br !== '' && $br !== $tl) {
1858	if (!$worksheet->cellExists($br)) {
1859	$worksheet->getCell($br)->setValue(' ');
1860	}
1861	$worksheet->mergeCells("$tl:$br");
1862	}
1863	}
1864	}
1865	}
1866
1867	/**
1868	* Excel will sometimes replace user's formatting choice
1869	* with a built-in choice that it thinks is equivalent.
1870	* Its choice is often not equivalent after all.
1871	* Such treatment is astonishingly user-hostile.
1872	* This function will undo such changes.
1873	*/
1874	public function replaceBuiltinNumberFormat(int $builtinFormatIndex, string $formatCode): void
1875	{
1876	foreach ($this->cellXfCollection as $style) {
1877	$numberFormat = $style->getNumberFormat();
1878	if ($numberFormat->getBuiltInFormatCode() === $builtinFormatIndex) {
1879	$numberFormat->setFormatCode($formatCode);
1880	}
1881	}
1882	}
1883
1884	/**
1885	* Change all 2-digit-year date styles to use 4-digit year;
1886	* change all dd-mm-yyyy and mm-dd-yyyy styles to yyyy-mm-dd;
1887	* dd-mmm-yyyy is unambiguous and left unchanged.
1888	*/
1889	public function disambiguateDateStyles(): void
1890	{
1891	foreach ($this->cellXfCollection as $style) {
1892	$numberFormat = $style->getNumberFormat();
1893	$oldFormat = (string) $numberFormat->getFormatCode();
1894	$newFormat = Preg::replace('/\byy\b/i', 'yyyy', $oldFormat);
1895	$newFormat = Preg::replace(
1896	'~\bdd?(-\|/\|"-"\|"/")'
1897	. 'mm?(-\|/\|"-"\|"/")'
1898	. 'yyyy~',
1899	'yyyy-mm-dd',
1900	$newFormat
1901	);
1902	$newFormat = Preg::replace(
1903	'~\bmm?(-\|/\|"-"\|"/")'
1904	. 'dd?(-\|/\|"-"\|"/")'
1905	. 'yyyy~',
1906	'yyyy-mm-dd',
1907	$newFormat
1908	);
1909	if ($newFormat !== $oldFormat) {
1910	$numberFormat->setFormatCode($newFormat);
1911	}
1912	}
1913	}
1914
1915	public function returnArrayAsArray(): void
1916	{
1917	$this->calculationEngine->setInstanceArrayReturnType(
1918	Calculation::RETURN_ARRAY_AS_ARRAY
1919	);
1920	}
1921
1922	public function returnArrayAsValue(): void
1923	{
1924	$this->calculationEngine->setInstanceArrayReturnType(
1925	Calculation::RETURN_ARRAY_AS_VALUE
1926	);
1927	}
1928
1929	/** @var string[] */
1930	private $domainWhiteList = [];
1931
1932	/**
1933	* Currently used only by WEBSERVICE function.
1934	*
1935	* @param string[] $domainWhiteList
1936	*/
1937	public function setDomainWhiteList(array $domainWhiteList): self
1938	{
1939	$this->domainWhiteList = $domainWhiteList;
1940
1941	return $this;
1942	}
1943
1944	/** @return string[] */
1945	public function getDomainWhiteList(): array
1946	{
1947	return $this->domainWhiteList;
1948	}
1949
1950	private bool $usesCheckBoxStyle = false;
1951
1952	public function getUsesCheckBoxStyle(): bool
1953	{
1954	return $this->usesCheckBoxStyle;
1955	}
1956
1957	public function setUsesCheckBoxStyle(): bool
1958	{
1959	$this->usesCheckBoxStyle = false;
1960	foreach ($this->getCellXfCollection() as $cellXf) {
1961	if ($cellXf->getCheckBox()) {
1962	$this->usesCheckBoxStyle = true;
1963
1964	break;
1965	}
1966	}
1967
1968	return $this->usesCheckBoxStyle;
1969	}
1970	}
1971