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

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

1,683 lines 45.3 KB

Raw Download Zip

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