visualizer / vendor / phpoffice / phpspreadsheet / src / PhpSpreadsheet / Spreadsheet.php

Spreadsheet.php in Visualizer – Tables & Charts Manager with Built-in AI Generator 3.11.0, at vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Spreadsheet.php

1,491 lines 37.4 KB

Raw Download Zip

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