visualizer / vendor / phpoffice / phpexcel / Classes / PHPExcel.php

PHPExcel.php in Visualizer – Tables & Charts Manager with Built-in AI Generator 3.0.3, at vendor/phpoffice/phpexcel/Classes/PHPExcel.php

1,140 lines 29.7 KB

Raw Download Zip

1	<?php
2	/**
3	* PHPExcel
4	*
5	* Copyright (c) 2006 - 2014 PHPExcel
6	*
7	* This library is free software; you can redistribute it and/or
8	* modify it under the terms of the GNU Lesser General Public
9	* License as published by the Free Software Foundation; either
10	* version 2.1 of the License, or (at your option) any later version.
11	*
12	* This library is distributed in the hope that it will be useful,
13	* but WITHOUT ANY WARRANTY; without even the implied warranty of
14	* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
15	* Lesser General Public License for more details.
16	*
17	* You should have received a copy of the GNU Lesser General Public
18	* License along with this library; if not, write to the Free Software
19	* Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
20	*
21	* @category PHPExcel
22	* @package PHPExcel
23	* @copyright Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)
24	* @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL
25	* @version ##VERSION##, ##DATE##
26	*/
27
28
29	/** PHPExcel root directory */
30	if (!defined('PHPEXCEL_ROOT')) {
31	define('PHPEXCEL_ROOT', dirname(__FILE__) . '/');
32	require(PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php');
33	}
34
35
36	/**
37	* PHPExcel
38	*
39	* @category PHPExcel
40	* @package PHPExcel
41	* @copyright Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)
42	*/
43	class PHPExcel
44	{
45	/**
46	* Unique ID
47	*
48	* @var string
49	*/
50	private $_uniqueID;
51
52	/**
53	* Document properties
54	*
55	* @var PHPExcel_DocumentProperties
56	*/
57	private $_properties;
58
59	/**
60	* Document security
61	*
62	* @var PHPExcel_DocumentSecurity
63	*/
64	private $_security;
65
66	/**
67	* Collection of Worksheet objects
68	*
69	* @var PHPExcel_Worksheet[]
70	*/
71	private $_workSheetCollection = array();
72
73	/**
74	* Calculation Engine
75	*
76	* @var PHPExcel_Calculation
77	*/
78	private $_calculationEngine = NULL;
79
80	/**
81	* Active sheet index
82	*
83	* @var int
84	*/
85	private $_activeSheetIndex = 0;
86
87	/**
88	* Named ranges
89	*
90	* @var PHPExcel_NamedRange[]
91	*/
92	private $_namedRanges = array();
93
94	/**
95	* CellXf supervisor
96	*
97	* @var PHPExcel_Style
98	*/
99	private $_cellXfSupervisor;
100
101	/**
102	* CellXf collection
103	*
104	* @var PHPExcel_Style[]
105	*/
106	private $_cellXfCollection = array();
107
108	/**
109	* CellStyleXf collection
110	*
111	* @var PHPExcel_Style[]
112	*/
113	private $_cellStyleXfCollection = array();
114
115	/**
116	* _hasMacros : this workbook have macros ?
117	*
118	* @var bool
119	*/
120	private $_hasMacros = FALSE;
121
122	/**
123	* _macrosCode : all macros code (the vbaProject.bin file, this include form, code, etc.), NULL if no macro
124	*
125	* @var binary
126	*/
127	private $_macrosCode=NULL;
128	/**
129	* _macrosCertificate : if macros are signed, contains vbaProjectSignature.bin file, NULL if not signed
130	*
131	* @var binary
132	*/
133	private $_macrosCertificate=NULL;
134
135	/**
136	* _ribbonXMLData : NULL if workbook is'nt Excel 2007 or not contain a customized UI
137	*
138	* @var NULL\|string
139	*/
140	private $_ribbonXMLData=NULL;
141
142	/**
143	* _ribbonBinObjects : NULL if workbook is'nt Excel 2007 or not contain embedded objects (picture(s)) for Ribbon Elements
144	* ignored if $_ribbonXMLData is null
145	*
146	* @var NULL\|array
147	*/
148	private $_ribbonBinObjects=NULL;
149
150	/**
151	* The workbook has macros ?
152	*
153	* @return true if workbook has macros, false if not
154	*/
155	public function hasMacros(){
156	return $this->_hasMacros;
157	}
158
159	/**
160	* Define if a workbook has macros
161	*
162	* @param true\|false
163	*/
164	public function setHasMacros($hasMacros=false){
165	$this->_hasMacros=(bool)$hasMacros;
166	}
167
168	/**
169	* Set the macros code
170	*
171	* @param binary string\|null
172	*/
173	public function setMacrosCode($MacrosCode){
174	$this->_macrosCode=$MacrosCode;
175	$this->setHasMacros(!is_null($MacrosCode));
176	}
177
178	/**
179	* Return the macros code
180	*
181	* @return binary\|null
182	*/
183	public function getMacrosCode(){
184	return $this->_macrosCode;
185	}
186
187	/**
188	* Set the macros certificate
189	*
190	* @param binary\|null
191	*/
192	public function setMacrosCertificate($Certificate=NULL){
193	$this->_macrosCertificate=$Certificate;
194	}
195
196	/**
197	* Is the project signed ?
198	*
199	* @return true\|false
200	*/
201	public function hasMacrosCertificate(){
202	return !is_null($this->_macrosCertificate);
203	}
204
205	/**
206	* Return the macros certificate
207	*
208	* @return binary\|null
209	*/
210	public function getMacrosCertificate(){
211	return $this->_macrosCertificate;
212	}
213
214	/**
215	* Remove all macros, certificate from spreadsheet
216	*
217	* @param none
218	* @return void
219	*/
220	public function discardMacros(){
221	$this->_hasMacros=false;
222	$this->_macrosCode=NULL;
223	$this->_macrosCertificate=NULL;
224	}
225
226	/**
227	* set ribbon XML data
228	*
229	*/
230	public function setRibbonXMLData($Target=NULL, $XMLData=NULL){
231	if(!is_null($Target) && !is_null($XMLData)){
232	$this->_ribbonXMLData=array('target'=>$Target, 'data'=>$XMLData);
233	}else{
234	$this->_ribbonXMLData=NULL;
235	}
236	}
237
238	/**
239	* retrieve ribbon XML Data
240	*
241	* return string\|null\|array
242	*/
243	public function getRibbonXMLData($What='all'){//we need some constants here...
244	$ReturnData=NULL;
245	$What=strtolower($What);
246	switch($What){
247	case 'all':
248	$ReturnData=$this->_ribbonXMLData;
249	break;
250	case 'target':
251	case 'data':
252	if(is_array($this->_ribbonXMLData) && array_key_exists($What,$this->_ribbonXMLData)){
253	$ReturnData=$this->_ribbonXMLData[$What];
254	}//else $ReturnData stay at null
255	break;
256	}//default: $ReturnData at null
257	return $ReturnData;
258	}
259
260	/**
261	* store binaries ribbon objects (pictures)
262	*
263	*/
264	public function setRibbonBinObjects($BinObjectsNames=NULL, $BinObjectsData=NULL){
265	if(!is_null($BinObjectsNames) && !is_null($BinObjectsData)){
266	$this->_ribbonBinObjects=array('names'=>$BinObjectsNames, 'data'=>$BinObjectsData);
267	}else{
268	$this->_ribbonBinObjects=NULL;
269	}
270	}
271	/**
272	* return the extension of a filename. Internal use for a array_map callback (php<5.3 don't like lambda function)
273	*
274	*/
275	private function _getExtensionOnly($ThePath){
276	return pathinfo($ThePath, PATHINFO_EXTENSION);
277	}
278
279	/**
280	* retrieve Binaries Ribbon Objects
281	*
282	*/
283	public function getRibbonBinObjects($What='all'){
284	$ReturnData=NULL;
285	$What=strtolower($What);
286	switch($What){
287	case 'all':
288	return $this->_ribbonBinObjects;
289	break;
290	case 'names':
291	case 'data':
292	if(is_array($this->_ribbonBinObjects) && array_key_exists($What, $this->_ribbonBinObjects)){
293	$ReturnData=$this->_ribbonBinObjects[$What];
294	}
295	break;
296	case 'types':
297	if(is_array($this->_ribbonBinObjects) && array_key_exists('data', $this->_ribbonBinObjects) && is_array($this->_ribbonBinObjects['data'])){
298	$tmpTypes=array_keys($this->_ribbonBinObjects['data']);
299	$ReturnData=array_unique(array_map(array($this,'_getExtensionOnly'), $tmpTypes));
300	}else
301	$ReturnData=array();//the caller want an array... not null if empty
302	break;
303	}
304	return $ReturnData;
305	}
306
307	/**
308	* This workbook have a custom UI ?
309	*
310	* @return true\|false
311	*/
312	public function hasRibbon(){
313	return !is_null($this->_ribbonXMLData);
314	}
315
316	/**
317	* This workbook have additionnal object for the ribbon ?
318	*
319	* @return true\|false
320	*/
321	public function hasRibbonBinObjects(){
322	return !is_null($this->_ribbonBinObjects);
323	}
324
325	/**
326	* Check if a sheet with a specified code name already exists
327	*
328	* @param string $pSheetCodeName Name of the worksheet to check
329	* @return boolean
330	*/
331	public function sheetCodeNameExists($pSheetCodeName)
332	{
333	return ($this->getSheetByCodeName($pSheetCodeName) !== NULL);
334	}
335
336	/**
337	* Get sheet by code name. Warning : sheet don't have always a code name !
338	*
339	* @param string $pName Sheet name
340	* @return PHPExcel_Worksheet
341	*/
342	public function getSheetByCodeName($pName = '')
343	{
344	$worksheetCount = count($this->_workSheetCollection);
345	for ($i = 0; $i < $worksheetCount; ++$i) {
346	if ($this->_workSheetCollection[$i]->getCodeName() == $pName) {
347	return $this->_workSheetCollection[$i];
348	}
349	}
350
351	return null;
352	}
353
354	/**
355	* Create a new PHPExcel with one Worksheet
356	*/
357	public function __construct()
358	{
359	$this->_uniqueID = uniqid();
360	$this->_calculationEngine = PHPExcel_Calculation::getInstance($this);
361
362	// Initialise worksheet collection and add one worksheet
363	$this->_workSheetCollection = array();
364	$this->_workSheetCollection[] = new PHPExcel_Worksheet($this);
365	$this->_activeSheetIndex = 0;
366
367	// Create document properties
368	$this->_properties = new PHPExcel_DocumentProperties();
369
370	// Create document security
371	$this->_security = new PHPExcel_DocumentSecurity();
372
373	// Set named ranges
374	$this->_namedRanges = array();
375
376	// Create the cellXf supervisor
377	$this->_cellXfSupervisor = new PHPExcel_Style(true);
378	$this->_cellXfSupervisor->bindParent($this);
379
380	// Create the default style
381	$this->addCellXf(new PHPExcel_Style);
382	$this->addCellStyleXf(new PHPExcel_Style);
383	}
384
385	/**
386	* Code to execute when this worksheet is unset()
387	*
388	*/
389	public function __destruct() {
390	PHPExcel_Calculation::unsetInstance($this);
391	$this->disconnectWorksheets();
392	} // function __destruct()
393
394	/**
395	* Disconnect all worksheets from this PHPExcel workbook object,
396	* typically so that the PHPExcel object can be unset
397	*
398	*/
399	public function disconnectWorksheets()
400	{
401	$worksheet = NULL;
402	foreach($this->_workSheetCollection as $k => &$worksheet) {
403	$worksheet->disconnectCells();
404	$this->_workSheetCollection[$k] = null;
405	}
406	unset($worksheet);
407	$this->_workSheetCollection = array();
408	}
409
410	/**
411	* Return the calculation engine for this worksheet
412	*
413	* @return PHPExcel_Calculation
414	*/
415	public function getCalculationEngine()
416	{
417	return $this->_calculationEngine;
418	} // function getCellCacheController()
419
420	/**
421	* Get properties
422	*
423	* @return PHPExcel_DocumentProperties
424	*/
425	public function getProperties()
426	{
427	return $this->_properties;
428	}
429
430	/**
431	* Set properties
432	*
433	* @param PHPExcel_DocumentProperties $pValue
434	*/
435	public function setProperties(PHPExcel_DocumentProperties $pValue)
436	{
437	$this->_properties = $pValue;
438	}
439
440	/**
441	* Get security
442	*
443	* @return PHPExcel_DocumentSecurity
444	*/
445	public function getSecurity()
446	{
447	return $this->_security;
448	}
449
450	/**
451	* Set security
452	*
453	* @param PHPExcel_DocumentSecurity $pValue
454	*/
455	public function setSecurity(PHPExcel_DocumentSecurity $pValue)
456	{
457	$this->_security = $pValue;
458	}
459
460	/**
461	* Get active sheet
462	*
463	* @return PHPExcel_Worksheet
464	*
465	* @throws PHPExcel_Exception
466	*/
467	public function getActiveSheet()
468	{
469	return $this->getSheet($this->_activeSheetIndex);
470	}
471
472	/**
473	* Create sheet and add it to this workbook
474	*
475	* @param int\|null $iSheetIndex Index where sheet should go (0,1,..., or null for last)
476	* @return PHPExcel_Worksheet
477	* @throws PHPExcel_Exception
478	*/
479	public function createSheet($iSheetIndex = NULL)
480	{
481	$newSheet = new PHPExcel_Worksheet($this);
482	$this->addSheet($newSheet, $iSheetIndex);
483	return $newSheet;
484	}
485
486	/**
487	* Check if a sheet with a specified name already exists
488	*
489	* @param string $pSheetName Name of the worksheet to check
490	* @return boolean
491	*/
492	public function sheetNameExists($pSheetName)
493	{
494	return ($this->getSheetByName($pSheetName) !== NULL);
495	}
496
497	/**
498	* Add sheet
499	*
500	* @param PHPExcel_Worksheet $pSheet
501	* @param int\|null $iSheetIndex Index where sheet should go (0,1,..., or null for last)
502	* @return PHPExcel_Worksheet
503	* @throws PHPExcel_Exception
504	*/
505	public function addSheet(PHPExcel_Worksheet $pSheet, $iSheetIndex = NULL)
506	{
507	if ($this->sheetNameExists($pSheet->getTitle())) {
508	throw new PHPExcel_Exception(
509	"Workbook already contains a worksheet named '{$pSheet->getTitle()}'. Rename this worksheet first."
510	);
511	}
512
513	if($iSheetIndex === NULL) {
514	if ($this->_activeSheetIndex < 0) {
515	$this->_activeSheetIndex = 0;
516	}
517	$this->_workSheetCollection[] = $pSheet;
518	} else {
519	// Insert the sheet at the requested index
520	array_splice(
521	$this->_workSheetCollection,
522	$iSheetIndex,
523	0,
524	array($pSheet)
525	);
526
527	// Adjust active sheet index if necessary
528	if ($this->_activeSheetIndex >= $iSheetIndex) {
529	++$this->_activeSheetIndex;
530	}
531	}
532
533	if ($pSheet->getParent() === null) {
534	$pSheet->rebindParent($this);
535	}
536
537	return $pSheet;
538	}
539
540	/**
541	* Remove sheet by index
542	*
543	* @param int $pIndex Active sheet index
544	* @throws PHPExcel_Exception
545	*/
546	public function removeSheetByIndex($pIndex = 0)
547	{
548
549	$numSheets = count($this->_workSheetCollection);
550
551	if ($pIndex > $numSheets - 1) {
552	throw new PHPExcel_Exception(
553	"You tried to remove a sheet by the out of bounds index: {$pIndex}. The actual number of sheets is {$numSheets}."
554	);
555	} else {
556	array_splice($this->_workSheetCollection, $pIndex, 1);
557	}
558	// Adjust active sheet index if necessary
559	if (($this->_activeSheetIndex >= $pIndex) &&
560	($pIndex > count($this->_workSheetCollection) - 1)) {
561	--$this->_activeSheetIndex;
562	}
563
564	}
565
566	/**
567	* Get sheet by index
568	*
569	* @param int $pIndex Sheet index
570	* @return PHPExcel_Worksheet
571	* @throws PHPExcel_Exception
572	*/
573	public function getSheet($pIndex = 0)
574	{
575	if (!isset($this->_workSheetCollection[$pIndex])) {
576	$numSheets = $this->getSheetCount();
577	throw new PHPExcel_Exception(
578	"Your requested sheet index: {$pIndex} is out of bounds. The actual number of sheets is {$numSheets}."
579	);
580	}
581
582	return $this->_workSheetCollection[$pIndex];
583	}
584
585	/**
586	* Get all sheets
587	*
588	* @return PHPExcel_Worksheet[]
589	*/
590	public function getAllSheets()
591	{
592	return $this->_workSheetCollection;
593	}
594
595	/**
596	* Get sheet by name
597	*
598	* @param string $pName Sheet name
599	* @return PHPExcel_Worksheet
600	*/
601	public function getSheetByName($pName = '')
602	{
603	$worksheetCount = count($this->_workSheetCollection);
604	for ($i = 0; $i < $worksheetCount; ++$i) {
605	if ($this->_workSheetCollection[$i]->getTitle() === $pName) {
606	return $this->_workSheetCollection[$i];
607	}
608	}
609
610	return NULL;
611	}
612
613	/**
614	* Get index for sheet
615	*
616	* @param PHPExcel_Worksheet $pSheet
617	* @return Sheet index
618	* @throws PHPExcel_Exception
619	*/
620	public function getIndex(PHPExcel_Worksheet $pSheet)
621	{
622	foreach ($this->_workSheetCollection as $key => $value) {
623	if ($value->getHashCode() == $pSheet->getHashCode()) {
624	return $key;
625	}
626	}
627
628	throw new PHPExcel_Exception("Sheet does not exist.");
629	}
630
631	/**
632	* Set index for sheet by sheet name.
633	*
634	* @param string $sheetName Sheet name to modify index for
635	* @param int $newIndex New index for the sheet
636	* @return New sheet index
637	* @throws PHPExcel_Exception
638	*/
639	public function setIndexByName($sheetName, $newIndex)
640	{
641	$oldIndex = $this->getIndex($this->getSheetByName($sheetName));
642	$pSheet = array_splice(
643	$this->_workSheetCollection,
644	$oldIndex,
645	1
646	);
647	array_splice(
648	$this->_workSheetCollection,
649	$newIndex,
650	0,
651	$pSheet
652	);
653	return $newIndex;
654	}
655
656	/**
657	* Get sheet count
658	*
659	* @return int
660	*/
661	public function getSheetCount()
662	{
663	return count($this->_workSheetCollection);
664	}
665
666	/**
667	* Get active sheet index
668	*
669	* @return int Active sheet index
670	*/
671	public function getActiveSheetIndex()
672	{
673	return $this->_activeSheetIndex;
674	}
675
676	/**
677	* Set active sheet index
678	*
679	* @param int $pIndex Active sheet index
680	* @throws PHPExcel_Exception
681	* @return PHPExcel_Worksheet
682	*/
683	public function setActiveSheetIndex($pIndex = 0)
684	{
685	$numSheets = count($this->_workSheetCollection);
686
687	if ($pIndex > $numSheets - 1) {
688	throw new PHPExcel_Exception(
689	"You tried to set a sheet active by the out of bounds index: {$pIndex}. The actual number of sheets is {$numSheets}."
690	);
691	} else {
692	$this->_activeSheetIndex = $pIndex;
693	}
694	return $this->getActiveSheet();
695	}
696
697	/**
698	* Set active sheet index by name
699	*
700	* @param string $pValue Sheet title
701	* @return PHPExcel_Worksheet
702	* @throws PHPExcel_Exception
703	*/
704	public function setActiveSheetIndexByName($pValue = '')
705	{
706	if (($worksheet = $this->getSheetByName($pValue)) instanceof PHPExcel_Worksheet) {
707	$this->setActiveSheetIndex($this->getIndex($worksheet));
708	return $worksheet;
709	}
710
711	throw new PHPExcel_Exception('Workbook does not contain sheet:' . $pValue);
712	}
713
714	/**
715	* Get sheet names
716	*
717	* @return string[]
718	*/
719	public function getSheetNames()
720	{
721	$returnValue = array();
722	$worksheetCount = $this->getSheetCount();
723	for ($i = 0; $i < $worksheetCount; ++$i) {
724	$returnValue[] = $this->getSheet($i)->getTitle();
725	}
726
727	return $returnValue;
728	}
729
730	/**
731	* Add external sheet
732	*
733	* @param PHPExcel_Worksheet $pSheet External sheet to add
734	* @param int\|null $iSheetIndex Index where sheet should go (0,1,..., or null for last)
735	* @throws PHPExcel_Exception
736	* @return PHPExcel_Worksheet
737	*/
738	public function addExternalSheet(PHPExcel_Worksheet $pSheet, $iSheetIndex = null) {
739	if ($this->sheetNameExists($pSheet->getTitle())) {
740	throw new PHPExcel_Exception("Workbook already contains a worksheet named '{$pSheet->getTitle()}'. Rename the external sheet first.");
741	}
742
743	// count how many cellXfs there are in this workbook currently, we will need this below
744	$countCellXfs = count($this->_cellXfCollection);
745
746	// copy all the shared cellXfs from the external workbook and append them to the current
747	foreach ($pSheet->getParent()->getCellXfCollection() as $cellXf) {
748	$this->addCellXf(clone $cellXf);
749	}
750
751	// move sheet to this workbook
752	$pSheet->rebindParent($this);
753
754	// update the cellXfs
755	foreach ($pSheet->getCellCollection(false) as $cellID) {
756	$cell = $pSheet->getCell($cellID);
757	$cell->setXfIndex( $cell->getXfIndex() + $countCellXfs );
758	}
759
760	return $this->addSheet($pSheet, $iSheetIndex);
761	}
762
763	/**
764	* Get named ranges
765	*
766	* @return PHPExcel_NamedRange[]
767	*/
768	public function getNamedRanges() {
769	return $this->_namedRanges;
770	}
771
772	/**
773	* Add named range
774	*
775	* @param PHPExcel_NamedRange $namedRange
776	* @return PHPExcel
777	*/
778	public function addNamedRange(PHPExcel_NamedRange $namedRange) {
779	if ($namedRange->getScope() == null) {
780	// global scope
781	$this->_namedRanges[$namedRange->getName()] = $namedRange;
782	} else {
783	// local scope
784	$this->_namedRanges[$namedRange->getScope()->getTitle().'!'.$namedRange->getName()] = $namedRange;
785	}
786	return true;
787	}
788
789	/**
790	* Get named range
791	*
792	* @param string $namedRange
793	* @param PHPExcel_Worksheet\|null $pSheet Scope. Use null for global scope
794	* @return PHPExcel_NamedRange\|null
795	*/
796	public function getNamedRange($namedRange, PHPExcel_Worksheet $pSheet = null) {
797	$returnValue = null;
798
799	if ($namedRange != '' && ($namedRange !== NULL)) {
800	// first look for global defined name
801	if (isset($this->_namedRanges[$namedRange])) {
802	$returnValue = $this->_namedRanges[$namedRange];
803	}
804
805	// then look for local defined name (has priority over global defined name if both names exist)
806	if (($pSheet !== NULL) && isset($this->_namedRanges[$pSheet->getTitle() . '!' . $namedRange])) {
807	$returnValue = $this->_namedRanges[$pSheet->getTitle() . '!' . $namedRange];
808	}
809	}
810
811	return $returnValue;
812	}
813
814	/**
815	* Remove named range
816	*
817	* @param string $namedRange
818	* @param PHPExcel_Worksheet\|null $pSheet Scope: use null for global scope.
819	* @return PHPExcel
820	*/
821	public function removeNamedRange($namedRange, PHPExcel_Worksheet $pSheet = null) {
822	if ($pSheet === NULL) {
823	if (isset($this->_namedRanges[$namedRange])) {
824	unset($this->_namedRanges[$namedRange]);
825	}
826	} else {
827	if (isset($this->_namedRanges[$pSheet->getTitle() . '!' . $namedRange])) {
828	unset($this->_namedRanges[$pSheet->getTitle() . '!' . $namedRange]);
829	}
830	}
831	return $this;
832	}
833
834	/**
835	* Get worksheet iterator
836	*
837	* @return PHPExcel_WorksheetIterator
838	*/
839	public function getWorksheetIterator() {
840	return new PHPExcel_WorksheetIterator($this);
841	}
842
843	/**
844	* Copy workbook (!= clone!)
845	*
846	* @return PHPExcel
847	*/
848	public function copy() {
849	$copied = clone $this;
850
851	$worksheetCount = count($this->_workSheetCollection);
852	for ($i = 0; $i < $worksheetCount; ++$i) {
853	$this->_workSheetCollection[$i] = $this->_workSheetCollection[$i]->copy();
854	$this->_workSheetCollection[$i]->rebindParent($this);
855	}
856
857	return $copied;
858	}
859
860	/**
861	* Implement PHP __clone to create a deep clone, not just a shallow copy.
862	*/
863	public function __clone() {
864	foreach($this as $key => $val) {
865	if (is_object($val) \|\| (is_array($val))) {
866	$this->{$key} = unserialize(serialize($val));
867	}
868	}
869	}
870
871	/**
872	* Get the workbook collection of cellXfs
873	*
874	* @return PHPExcel_Style[]
875	*/
876	public function getCellXfCollection()
877	{
878	return $this->_cellXfCollection;
879	}
880
881	/**
882	* Get cellXf by index
883	*
884	* @param int $pIndex
885	* @return PHPExcel_Style
886	*/
887	public function getCellXfByIndex($pIndex = 0)
888	{
889	return $this->_cellXfCollection[$pIndex];
890	}
891
892	/**
893	* Get cellXf by hash code
894	*
895	* @param string $pValue
896	* @return PHPExcel_Style\|false
897	*/
898	public function getCellXfByHashCode($pValue = '')
899	{
900	foreach ($this->_cellXfCollection as $cellXf) {
901	if ($cellXf->getHashCode() == $pValue) {
902	return $cellXf;
903	}
904	}
905	return false;
906	}
907
908	/**
909	* Check if style exists in style collection
910	*
911	* @param PHPExcel_Style $pCellStyle
912	* @return boolean
913	*/
914	public function cellXfExists($pCellStyle = null)
915	{
916	return in_array($pCellStyle, $this->_cellXfCollection, true);
917	}
918
919	/**
920	* Get default style
921	*
922	* @return PHPExcel_Style
923	* @throws PHPExcel_Exception
924	*/
925	public function getDefaultStyle()
926	{
927	if (isset($this->_cellXfCollection[0])) {
928	return $this->_cellXfCollection[0];
929	}
930	throw new PHPExcel_Exception('No default style found for this workbook');
931	}
932
933	/**
934	* Add a cellXf to the workbook
935	*
936	* @param PHPExcel_Style $style
937	*/
938	public function addCellXf(PHPExcel_Style $style)
939	{
940	$this->_cellXfCollection[] = $style;
941	$style->setIndex(count($this->_cellXfCollection) - 1);
942	}
943
944	/**
945	* Remove cellXf by index. It is ensured that all cells get their xf index updated.
946	*
947	* @param int $pIndex Index to cellXf
948	* @throws PHPExcel_Exception
949	*/
950	public function removeCellXfByIndex($pIndex = 0)
951	{
952	if ($pIndex > count($this->_cellXfCollection) - 1) {
953	throw new PHPExcel_Exception("CellXf index is out of bounds.");
954	} else {
955	// first remove the cellXf
956	array_splice($this->_cellXfCollection, $pIndex, 1);
957
958	// then update cellXf indexes for cells
959	foreach ($this->_workSheetCollection as $worksheet) {
960	foreach ($worksheet->getCellCollection(false) as $cellID) {
961	$cell = $worksheet->getCell($cellID);
962	$xfIndex = $cell->getXfIndex();
963	if ($xfIndex > $pIndex ) {
964	// decrease xf index by 1
965	$cell->setXfIndex($xfIndex - 1);
966	} else if ($xfIndex == $pIndex) {
967	// set to default xf index 0
968	$cell->setXfIndex(0);
969	}
970	}
971	}
972	}
973	}
974
975	/**
976	* Get the cellXf supervisor
977	*
978	* @return PHPExcel_Style
979	*/
980	public function getCellXfSupervisor()
981	{
982	return $this->_cellXfSupervisor;
983	}
984
985	/**
986	* Get the workbook collection of cellStyleXfs
987	*
988	* @return PHPExcel_Style[]
989	*/
990	public function getCellStyleXfCollection()
991	{
992	return $this->_cellStyleXfCollection;
993	}
994
995	/**
996	* Get cellStyleXf by index
997	*
998	* @param int $pIndex
999	* @return PHPExcel_Style
1000	*/
1001	public function getCellStyleXfByIndex($pIndex = 0)
1002	{
1003	return $this->_cellStyleXfCollection[$pIndex];
1004	}
1005
1006	/**
1007	* Get cellStyleXf by hash code
1008	*
1009	* @param string $pValue
1010	* @return PHPExcel_Style\|false
1011	*/
1012	public function getCellStyleXfByHashCode($pValue = '')
1013	{
1014	foreach ($this->_cellStyleXfCollection as $cellStyleXf) {
1015	if ($cellStyleXf->getHashCode() == $pValue) {
1016	return $cellStyleXf;
1017	}
1018	}
1019	return false;
1020	}
1021
1022	/**
1023	* Add a cellStyleXf to the workbook
1024	*
1025	* @param PHPExcel_Style $pStyle
1026	*/
1027	public function addCellStyleXf(PHPExcel_Style $pStyle)
1028	{
1029	$this->_cellStyleXfCollection[] = $pStyle;
1030	$pStyle->setIndex(count($this->_cellStyleXfCollection) - 1);
1031	}
1032
1033	/**
1034	* Remove cellStyleXf by index
1035	*
1036	* @param int $pIndex
1037	* @throws PHPExcel_Exception
1038	*/
1039	public function removeCellStyleXfByIndex($pIndex = 0)
1040	{
1041	if ($pIndex > count($this->_cellStyleXfCollection) - 1) {
1042	throw new PHPExcel_Exception("CellStyleXf index is out of bounds.");
1043	} else {
1044	array_splice($this->_cellStyleXfCollection, $pIndex, 1);
1045	}
1046	}
1047
1048	/**
1049	* Eliminate all unneeded cellXf and afterwards update the xfIndex for all cells
1050	* and columns in the workbook
1051	*/
1052	public function garbageCollect()
1053	{
1054	// how many references are there to each cellXf ?
1055	$countReferencesCellXf = array();
1056	foreach ($this->_cellXfCollection as $index => $cellXf) {
1057	$countReferencesCellXf[$index] = 0;
1058	}
1059
1060	foreach ($this->getWorksheetIterator() as $sheet) {
1061
1062	// from cells
1063	foreach ($sheet->getCellCollection(false) as $cellID) {
1064	$cell = $sheet->getCell($cellID);
1065	++$countReferencesCellXf[$cell->getXfIndex()];
1066	}
1067
1068	// from row dimensions
1069	foreach ($sheet->getRowDimensions() as $rowDimension) {
1070	if ($rowDimension->getXfIndex() !== null) {
1071	++$countReferencesCellXf[$rowDimension->getXfIndex()];
1072	}
1073	}
1074
1075	// from column dimensions
1076	foreach ($sheet->getColumnDimensions() as $columnDimension) {
1077	++$countReferencesCellXf[$columnDimension->getXfIndex()];
1078	}
1079	}
1080
1081	// remove cellXfs without references and create mapping so we can update xfIndex
1082	// for all cells and columns
1083	$countNeededCellXfs = 0;
1084	foreach ($this->_cellXfCollection as $index => $cellXf) {
1085	if ($countReferencesCellXf[$index] > 0 \|\| $index == 0) { // we must never remove the first cellXf
1086	++$countNeededCellXfs;
1087	} else {
1088	unset($this->_cellXfCollection[$index]);
1089	}
1090	$map[$index] = $countNeededCellXfs - 1;
1091	}
1092	$this->_cellXfCollection = array_values($this->_cellXfCollection);
1093
1094	// update the index for all cellXfs
1095	foreach ($this->_cellXfCollection as $i => $cellXf) {
1096	$cellXf->setIndex($i);
1097	}
1098
1099	// make sure there is always at least one cellXf (there should be)
1100	if (empty($this->_cellXfCollection)) {
1101	$this->_cellXfCollection[] = new PHPExcel_Style();
1102	}
1103
1104	// update the xfIndex for all cells, row dimensions, column dimensions
1105	foreach ($this->getWorksheetIterator() as $sheet) {
1106
1107	// for all cells
1108	foreach ($sheet->getCellCollection(false) as $cellID) {
1109	$cell = $sheet->getCell($cellID);
1110	$cell->setXfIndex( $map[$cell->getXfIndex()] );
1111	}
1112
1113	// for all row dimensions
1114	foreach ($sheet->getRowDimensions() as $rowDimension) {
1115	if ($rowDimension->getXfIndex() !== null) {
1116	$rowDimension->setXfIndex( $map[$rowDimension->getXfIndex()] );
1117	}
1118	}
1119
1120	// for all column dimensions
1121	foreach ($sheet->getColumnDimensions() as $columnDimension) {
1122	$columnDimension->setXfIndex( $map[$columnDimension->getXfIndex()] );
1123	}
1124
1125	// also do garbage collection for all the sheets
1126	$sheet->garbageCollect();
1127	}
1128	}
1129
1130	/**
1131	* Return the unique ID value assigned to this spreadsheet workbook
1132	*
1133	* @return string
1134	*/
1135	public function getID() {
1136	return $this->_uniqueID;
1137	}
1138
1139	}
1140