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

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

3,067 lines 85.8 KB

Raw Download Zip

1	<?php
2
3	namespace PhpOffice\PhpSpreadsheet\Worksheet;
4
5	use ArrayObject;
6	use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
7	use PhpOffice\PhpSpreadsheet\Cell\Cell;
8	use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
9	use PhpOffice\PhpSpreadsheet\Cell\DataType;
10	use PhpOffice\PhpSpreadsheet\Cell\DataValidation;
11	use PhpOffice\PhpSpreadsheet\Cell\Hyperlink;
12	use PhpOffice\PhpSpreadsheet\Chart\Chart;
13	use PhpOffice\PhpSpreadsheet\Collection\Cells;
14	use PhpOffice\PhpSpreadsheet\Collection\CellsFactory;
15	use PhpOffice\PhpSpreadsheet\Comment;
16	use PhpOffice\PhpSpreadsheet\Exception;
17	use PhpOffice\PhpSpreadsheet\IComparable;
18	use PhpOffice\PhpSpreadsheet\NamedRange;
19	use PhpOffice\PhpSpreadsheet\ReferenceHelper;
20	use PhpOffice\PhpSpreadsheet\RichText\RichText;
21	use PhpOffice\PhpSpreadsheet\Shared;
22	use PhpOffice\PhpSpreadsheet\Spreadsheet;
23	use PhpOffice\PhpSpreadsheet\Style\Color;
24	use PhpOffice\PhpSpreadsheet\Style\Conditional;
25	use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
26	use PhpOffice\PhpSpreadsheet\Style\Style;
27
28	class Worksheet implements IComparable
29	{
30	// Break types
31	const BREAK_NONE = 0;
32	const BREAK_ROW = 1;
33	const BREAK_COLUMN = 2;
34
35	// Sheet state
36	const SHEETSTATE_VISIBLE = 'visible';
37	const SHEETSTATE_HIDDEN = 'hidden';
38	const SHEETSTATE_VERYHIDDEN = 'veryHidden';
39
40	/**
41	* Maximum 31 characters allowed for sheet title.
42	*
43	* @var int
44	*/
45	const SHEET_TITLE_MAXIMUM_LENGTH = 31;
46
47	/**
48	* Invalid characters in sheet title.
49	*
50	* @var array
51	*/
52	private static $invalidCharacters = ['*', ':', '/', '\\', '?', '[', ']'];
53
54	/**
55	* Parent spreadsheet.
56	*
57	* @var Spreadsheet
58	*/
59	private $parent;
60
61	/**
62	* Collection of cells.
63	*
64	* @var Cells
65	*/
66	private $cellCollection;
67
68	/**
69	* Collection of row dimensions.
70	*
71	* @var RowDimension[]
72	*/
73	private $rowDimensions = [];
74
75	/**
76	* Default row dimension.
77	*
78	* @var RowDimension
79	*/
80	private $defaultRowDimension;
81
82	/**
83	* Collection of column dimensions.
84	*
85	* @var ColumnDimension[]
86	*/
87	private $columnDimensions = [];
88
89	/**
90	* Default column dimension.
91	*
92	* @var ColumnDimension
93	*/
94	private $defaultColumnDimension;
95
96	/**
97	* Collection of drawings.
98	*
99	* @var BaseDrawing[]
100	*/
101	private $drawingCollection;
102
103	/**
104	* Collection of Chart objects.
105	*
106	* @var Chart[]
107	*/
108	private $chartCollection = [];
109
110	/**
111	* Worksheet title.
112	*
113	* @var string
114	*/
115	private $title;
116
117	/**
118	* Sheet state.
119	*
120	* @var string
121	*/
122	private $sheetState;
123
124	/**
125	* Page setup.
126	*
127	* @var PageSetup
128	*/
129	private $pageSetup;
130
131	/**
132	* Page margins.
133	*
134	* @var PageMargins
135	*/
136	private $pageMargins;
137
138	/**
139	* Page header/footer.
140	*
141	* @var HeaderFooter
142	*/
143	private $headerFooter;
144
145	/**
146	* Sheet view.
147	*
148	* @var SheetView
149	*/
150	private $sheetView;
151
152	/**
153	* Protection.
154	*
155	* @var Protection
156	*/
157	private $protection;
158
159	/**
160	* Collection of styles.
161	*
162	* @var Style[]
163	*/
164	private $styles = [];
165
166	/**
167	* Conditional styles. Indexed by cell coordinate, e.g. 'A1'.
168	*
169	* @var array
170	*/
171	private $conditionalStylesCollection = [];
172
173	/**
174	* Is the current cell collection sorted already?
175	*
176	* @var bool
177	*/
178	private $cellCollectionIsSorted = false;
179
180	/**
181	* Collection of breaks.
182	*
183	* @var array
184	*/
185	private $breaks = [];
186
187	/**
188	* Collection of merged cell ranges.
189	*
190	* @var array
191	*/
192	private $mergeCells = [];
193
194	/**
195	* Collection of protected cell ranges.
196	*
197	* @var array
198	*/
199	private $protectedCells = [];
200
201	/**
202	* Autofilter Range and selection.
203	*
204	* @var AutoFilter
205	*/
206	private $autoFilter;
207
208	/**
209	* Freeze pane.
210	*
211	* @var null\|string
212	*/
213	private $freezePane;
214
215	/**
216	* Default position of the right bottom pane.
217	*
218	* @var null\|string
219	*/
220	private $topLeftCell;
221
222	/**
223	* Show gridlines?
224	*
225	* @var bool
226	*/
227	private $showGridlines = true;
228
229	/**
230	* Print gridlines?
231	*
232	* @var bool
233	*/
234	private $printGridlines = false;
235
236	/**
237	* Show row and column headers?
238	*
239	* @var bool
240	*/
241	private $showRowColHeaders = true;
242
243	/**
244	* Show summary below? (Row/Column outline).
245	*
246	* @var bool
247	*/
248	private $showSummaryBelow = true;
249
250	/**
251	* Show summary right? (Row/Column outline).
252	*
253	* @var bool
254	*/
255	private $showSummaryRight = true;
256
257	/**
258	* Collection of comments.
259	*
260	* @var Comment[]
261	*/
262	private $comments = [];
263
264	/**
265	* Active cell. (Only one!).
266	*
267	* @var string
268	*/
269	private $activeCell = 'A1';
270
271	/**
272	* Selected cells.
273	*
274	* @var string
275	*/
276	private $selectedCells = 'A1';
277
278	/**
279	* Cached highest column.
280	*
281	* @var string
282	*/
283	private $cachedHighestColumn = 'A';
284
285	/**
286	* Cached highest row.
287	*
288	* @var int
289	*/
290	private $cachedHighestRow = 1;
291
292	/**
293	* Right-to-left?
294	*
295	* @var bool
296	*/
297	private $rightToLeft = false;
298
299	/**
300	* Hyperlinks. Indexed by cell coordinate, e.g. 'A1'.
301	*
302	* @var array
303	*/
304	private $hyperlinkCollection = [];
305
306	/**
307	* Data validation objects. Indexed by cell coordinate, e.g. 'A1'.
308	*
309	* @var array
310	*/
311	private $dataValidationCollection = [];
312
313	/**
314	* Tab color.
315	*
316	* @var Color
317	*/
318	private $tabColor;
319
320	/**
321	* Dirty flag.
322	*
323	* @var bool
324	*/
325	private $dirty = true;
326
327	/**
328	* Hash.
329	*
330	* @var string
331	*/
332	private $hash;
333
334	/**
335	* CodeName.
336	*
337	* @var string
338	*/
339	private $codeName;
340
341	/**
342	* Create a new worksheet.
343	*
344	* @param Spreadsheet $parent
345	* @param string $pTitle
346	*/
347	public function __construct(Spreadsheet $parent = null, $pTitle = 'Worksheet')
348	{
349	// Set parent and title
350	$this->parent = $parent;
351	$this->setTitle($pTitle, false);
352	// setTitle can change $pTitle
353	$this->setCodeName($this->getTitle());
354	$this->setSheetState(self::SHEETSTATE_VISIBLE);
355
356	$this->cellCollection = CellsFactory::getInstance($this);
357	// Set page setup
358	$this->pageSetup = new PageSetup();
359	// Set page margins
360	$this->pageMargins = new PageMargins();
361	// Set page header/footer
362	$this->headerFooter = new HeaderFooter();
363	// Set sheet view
364	$this->sheetView = new SheetView();
365	// Drawing collection
366	$this->drawingCollection = new \ArrayObject();
367	// Chart collection
368	$this->chartCollection = new \ArrayObject();
369	// Protection
370	$this->protection = new Protection();
371	// Default row dimension
372	$this->defaultRowDimension = new RowDimension(null);
373	// Default column dimension
374	$this->defaultColumnDimension = new ColumnDimension(null);
375	$this->autoFilter = new AutoFilter(null, $this);
376	}
377
378	/**
379	* Disconnect all cells from this Worksheet object,
380	* typically so that the worksheet object can be unset.
381	*/
382	public function disconnectCells()
383	{
384	if ($this->cellCollection !== null) {
385	$this->cellCollection->unsetWorksheetCells();
386	$this->cellCollection = null;
387	}
388	// detach ourself from the workbook, so that it can then delete this worksheet successfully
389	$this->parent = null;
390	}
391
392	/**
393	* Code to execute when this worksheet is unset().
394	*/
395	public function __destruct()
396	{
397	Calculation::getInstance($this->parent)->clearCalculationCacheForWorksheet($this->title);
398
399	$this->disconnectCells();
400	}
401
402	/**
403	* Return the cell collection.
404	*
405	* @return Cells
406	*/
407	public function getCellCollection()
408	{
409	return $this->cellCollection;
410	}
411
412	/**
413	* Get array of invalid characters for sheet title.
414	*
415	* @return array
416	*/
417	public static function getInvalidCharacters()
418	{
419	return self::$invalidCharacters;
420	}
421
422	/**
423	* Check sheet code name for valid Excel syntax.
424	*
425	* @param string $pValue The string to check
426	*
427	* @throws Exception
428	*
429	* @return string The valid string
430	*/
431	private static function checkSheetCodeName($pValue)
432	{
433	$CharCount = Shared\StringHelper::countCharacters($pValue);
434	if ($CharCount == 0) {
435	throw new Exception('Sheet code name cannot be empty.');
436	}
437	// Some of the printable ASCII characters are invalid: * : / \ ? [ ] and first and last characters cannot be a "'"
438	if ((str_replace(self::$invalidCharacters, '', $pValue) !== $pValue) \|\|
439	(Shared\StringHelper::substring($pValue, -1, 1) == '\'') \|\|
440	(Shared\StringHelper::substring($pValue, 0, 1) == '\'')) {
441	throw new Exception('Invalid character found in sheet code name');
442	}
443
444	// Enforce maximum characters allowed for sheet title
445	if ($CharCount > self::SHEET_TITLE_MAXIMUM_LENGTH) {
446	throw new Exception('Maximum ' . self::SHEET_TITLE_MAXIMUM_LENGTH . ' characters allowed in sheet code name.');
447	}
448
449	return $pValue;
450	}
451
452	/**
453	* Check sheet title for valid Excel syntax.
454	*
455	* @param string $pValue The string to check
456	*
457	* @throws Exception
458	*
459	* @return string The valid string
460	*/
461	private static function checkSheetTitle($pValue)
462	{
463	// Some of the printable ASCII characters are invalid: * : / \ ? [ ]
464	if (str_replace(self::$invalidCharacters, '', $pValue) !== $pValue) {
465	throw new Exception('Invalid character found in sheet title');
466	}
467
468	// Enforce maximum characters allowed for sheet title
469	if (Shared\StringHelper::countCharacters($pValue) > self::SHEET_TITLE_MAXIMUM_LENGTH) {
470	throw new Exception('Maximum ' . self::SHEET_TITLE_MAXIMUM_LENGTH . ' characters allowed in sheet title.');
471	}
472
473	return $pValue;
474	}
475
476	/**
477	* Get a sorted list of all cell coordinates currently held in the collection by row and column.
478	*
479	* @param bool $sorted Also sort the cell collection?
480	*
481	* @return string[]
482	*/
483	public function getCoordinates($sorted = true)
484	{
485	if ($this->cellCollection == null) {
486	return [];
487	}
488
489	if ($sorted) {
490	return $this->cellCollection->getSortedCoordinates();
491	}
492
493	return $this->cellCollection->getCoordinates();
494	}
495
496	/**
497	* Get collection of row dimensions.
498	*
499	* @return RowDimension[]
500	*/
501	public function getRowDimensions()
502	{
503	return $this->rowDimensions;
504	}
505
506	/**
507	* Get default row dimension.
508	*
509	* @return RowDimension
510	*/
511	public function getDefaultRowDimension()
512	{
513	return $this->defaultRowDimension;
514	}
515
516	/**
517	* Get collection of column dimensions.
518	*
519	* @return ColumnDimension[]
520	*/
521	public function getColumnDimensions()
522	{
523	return $this->columnDimensions;
524	}
525
526	/**
527	* Get default column dimension.
528	*
529	* @return ColumnDimension
530	*/
531	public function getDefaultColumnDimension()
532	{
533	return $this->defaultColumnDimension;
534	}
535
536	/**
537	* Get collection of drawings.
538	*
539	* @return BaseDrawing[]
540	*/
541	public function getDrawingCollection()
542	{
543	return $this->drawingCollection;
544	}
545
546	/**
547	* Get collection of charts.
548	*
549	* @return Chart[]
550	*/
551	public function getChartCollection()
552	{
553	return $this->chartCollection;
554	}
555
556	/**
557	* Add chart.
558	*
559	* @param Chart $pChart
560	* @param null\|int $iChartIndex Index where chart should go (0,1,..., or null for last)
561	*
562	* @return Chart
563	*/
564	public function addChart(Chart $pChart, $iChartIndex = null)
565	{
566	$pChart->setWorksheet($this);
567	if ($iChartIndex === null) {
568	$this->chartCollection[] = $pChart;
569	} else {
570	// Insert the chart at the requested index
571	array_splice($this->chartCollection, $iChartIndex, 0, [$pChart]);
572	}
573
574	return $pChart;
575	}
576
577	/**
578	* Return the count of charts on this worksheet.
579	*
580	* @return int The number of charts
581	*/
582	public function getChartCount()
583	{
584	return count($this->chartCollection);
585	}
586
587	/**
588	* Get a chart by its index position.
589	*
590	* @param string $index Chart index position
591	*
592	* @return Chart\|false
593	*/
594	public function getChartByIndex($index)
595	{
596	$chartCount = count($this->chartCollection);
597	if ($chartCount == 0) {
598	return false;
599	}
600	if ($index === null) {
601	$index = --$chartCount;
602	}
603	if (!isset($this->chartCollection[$index])) {
604	return false;
605	}
606
607	return $this->chartCollection[$index];
608	}
609
610	/**
611	* Return an array of the names of charts on this worksheet.
612	*
613	* @return string[] The names of charts
614	*/
615	public function getChartNames()
616	{
617	$chartNames = [];
618	foreach ($this->chartCollection as $chart) {
619	$chartNames[] = $chart->getName();
620	}
621
622	return $chartNames;
623	}
624
625	/**
626	* Get a chart by name.
627	*
628	* @param string $chartName Chart name
629	*
630	* @return Chart\|false
631	*/
632	public function getChartByName($chartName)
633	{
634	$chartCount = count($this->chartCollection);
635	if ($chartCount == 0) {
636	return false;
637	}
638	foreach ($this->chartCollection as $index => $chart) {
639	if ($chart->getName() == $chartName) {
640	return $this->chartCollection[$index];
641	}
642	}
643
644	return false;
645	}
646
647	/**
648	* Refresh column dimensions.
649	*
650	* @return Worksheet
651	*/
652	public function refreshColumnDimensions()
653	{
654	$currentColumnDimensions = $this->getColumnDimensions();
655	$newColumnDimensions = [];
656
657	foreach ($currentColumnDimensions as $objColumnDimension) {
658	$newColumnDimensions[$objColumnDimension->getColumnIndex()] = $objColumnDimension;
659	}
660
661	$this->columnDimensions = $newColumnDimensions;
662
663	return $this;
664	}
665
666	/**
667	* Refresh row dimensions.
668	*
669	* @return Worksheet
670	*/
671	public function refreshRowDimensions()
672	{
673	$currentRowDimensions = $this->getRowDimensions();
674	$newRowDimensions = [];
675
676	foreach ($currentRowDimensions as $objRowDimension) {
677	$newRowDimensions[$objRowDimension->getRowIndex()] = $objRowDimension;
678	}
679
680	$this->rowDimensions = $newRowDimensions;
681
682	return $this;
683	}
684
685	/**
686	* Calculate worksheet dimension.
687	*
688	* @return string String containing the dimension of this worksheet
689	*/
690	public function calculateWorksheetDimension()
691	{
692	// Return
693	return 'A1' . ':' . $this->getHighestColumn() . $this->getHighestRow();
694	}
695
696	/**
697	* Calculate worksheet data dimension.
698	*
699	* @return string String containing the dimension of this worksheet that actually contain data
700	*/
701	public function calculateWorksheetDataDimension()
702	{
703	// Return
704	return 'A1' . ':' . $this->getHighestDataColumn() . $this->getHighestDataRow();
705	}
706
707	/**
708	* Calculate widths for auto-size columns.
709	*
710	* @return Worksheet;
711	*/
712	public function calculateColumnWidths()
713	{
714	// initialize $autoSizes array
715	$autoSizes = [];
716	foreach ($this->getColumnDimensions() as $colDimension) {
717	if ($colDimension->getAutoSize()) {
718	$autoSizes[$colDimension->getColumnIndex()] = -1;
719	}
720	}
721
722	// There is only something to do if there are some auto-size columns
723	if (!empty($autoSizes)) {
724	// build list of cells references that participate in a merge
725	$isMergeCell = [];
726	foreach ($this->getMergeCells() as $cells) {
727	foreach (Coordinate::extractAllCellReferencesInRange($cells) as $cellReference) {
728	$isMergeCell[$cellReference] = true;
729	}
730	}
731
732	// loop through all cells in the worksheet
733	foreach ($this->getCoordinates(false) as $coordinate) {
734	$cell = $this->getCell($coordinate, false);
735	if ($cell !== null && isset($autoSizes[$this->cellCollection->getCurrentColumn()])) {
736	//Determine if cell is in merge range
737	$isMerged = isset($isMergeCell[$this->cellCollection->getCurrentCoordinate()]);
738
739	//By default merged cells should be ignored
740	$isMergedButProceed = false;
741
742	//The only exception is if it's a merge range value cell of a 'vertical' randge (1 column wide)
743	if ($isMerged && $cell->isMergeRangeValueCell()) {
744	$range = $cell->getMergeRange();
745	$rangeBoundaries = Coordinate::rangeDimension($range);
746	if ($rangeBoundaries[0] == 1) {
747	$isMergedButProceed = true;
748	}
749	}
750
751	// Determine width if cell does not participate in a merge or does and is a value cell of 1-column wide range
752	if (!$isMerged \|\| $isMergedButProceed) {
753	// Calculated value
754	// To formatted string
755	$cellValue = NumberFormat::toFormattedString(
756	$cell->getCalculatedValue(),
757	$this->getParent()->getCellXfByIndex($cell->getXfIndex())->getNumberFormat()->getFormatCode()
758	);
759
760	$autoSizes[$this->cellCollection->getCurrentColumn()] = max(
761	(float) $autoSizes[$this->cellCollection->getCurrentColumn()],
762	(float) Shared\Font::calculateColumnWidth(
763	$this->getParent()->getCellXfByIndex($cell->getXfIndex())->getFont(),
764	$cellValue,
765	$this->getParent()->getCellXfByIndex($cell->getXfIndex())->getAlignment()->getTextRotation(),
766	$this->getParent()->getDefaultStyle()->getFont()
767	)
768	);
769	}
770	}
771	}
772
773	// adjust column widths
774	foreach ($autoSizes as $columnIndex => $width) {
775	if ($width == -1) {
776	$width = $this->getDefaultColumnDimension()->getWidth();
777	}
778	$this->getColumnDimension($columnIndex)->setWidth($width);
779	}
780	}
781
782	return $this;
783	}
784
785	/**
786	* Get parent.
787	*
788	* @return Spreadsheet
789	*/
790	public function getParent()
791	{
792	return $this->parent;
793	}
794
795	/**
796	* Re-bind parent.
797	*
798	* @param Spreadsheet $parent
799	*
800	* @return Worksheet
801	*/
802	public function rebindParent(Spreadsheet $parent)
803	{
804	if ($this->parent !== null) {
805	$namedRanges = $this->parent->getNamedRanges();
806	foreach ($namedRanges as $namedRange) {
807	$parent->addNamedRange($namedRange);
808	}
809
810	$this->parent->removeSheetByIndex(
811	$this->parent->getIndex($this)
812	);
813	}
814	$this->parent = $parent;
815
816	return $this;
817	}
818
819	/**
820	* Get title.
821	*
822	* @return string
823	*/
824	public function getTitle()
825	{
826	return $this->title;
827	}
828
829	/**
830	* Set title.
831	*
832	* @param string $pValue String containing the dimension of this worksheet
833	* @param bool $updateFormulaCellReferences Flag indicating whether cell references in formulae should
834	* be updated to reflect the new sheet name.
835	* This should be left as the default true, unless you are
836	* certain that no formula cells on any worksheet contain
837	* references to this worksheet
838	* @param bool $validate False to skip validation of new title. WARNING: This should only be set
839	* at parse time (by Readers), where titles can be assumed to be valid.
840	*
841	* @return Worksheet
842	*/
843	public function setTitle($pValue, $updateFormulaCellReferences = true, $validate = true)
844	{
845	// Is this a 'rename' or not?
846	if ($this->getTitle() == $pValue) {
847	return $this;
848	}
849
850	// Old title
851	$oldTitle = $this->getTitle();
852
853	if ($validate) {
854	// Syntax check
855	self::checkSheetTitle($pValue);
856
857	if ($this->parent) {
858	// Is there already such sheet name?
859	if ($this->parent->sheetNameExists($pValue)) {
860	// Use name, but append with lowest possible integer
861
862	if (Shared\StringHelper::countCharacters($pValue) > 29) {
863	$pValue = Shared\StringHelper::substring($pValue, 0, 29);
864	}
865	$i = 1;
866	while ($this->parent->sheetNameExists($pValue . ' ' . $i)) {
867	++$i;
868	if ($i == 10) {
869	if (Shared\StringHelper::countCharacters($pValue) > 28) {
870	$pValue = Shared\StringHelper::substring($pValue, 0, 28);
871	}
872	} elseif ($i == 100) {
873	if (Shared\StringHelper::countCharacters($pValue) > 27) {
874	$pValue = Shared\StringHelper::substring($pValue, 0, 27);
875	}
876	}
877	}
878
879	$pValue .= " $i";
880	}
881	}
882	}
883
884	// Set title
885	$this->title = $pValue;
886	$this->dirty = true;
887
888	if ($this->parent && $this->parent->getCalculationEngine()) {
889	// New title
890	$newTitle = $this->getTitle();
891	$this->parent->getCalculationEngine()
892	->renameCalculationCacheForWorksheet($oldTitle, $newTitle);
893	if ($updateFormulaCellReferences) {
894	ReferenceHelper::getInstance()->updateNamedFormulas($this->parent, $oldTitle, $newTitle);
895	}
896	}
897
898	return $this;
899	}
900
901	/**
902	* Get sheet state.
903	*
904	* @return string Sheet state (visible, hidden, veryHidden)
905	*/
906	public function getSheetState()
907	{
908	return $this->sheetState;
909	}
910
911	/**
912	* Set sheet state.
913	*
914	* @param string $value Sheet state (visible, hidden, veryHidden)
915	*
916	* @return Worksheet
917	*/
918	public function setSheetState($value)
919	{
920	$this->sheetState = $value;
921
922	return $this;
923	}
924
925	/**
926	* Get page setup.
927	*
928	* @return PageSetup
929	*/
930	public function getPageSetup()
931	{
932	return $this->pageSetup;
933	}
934
935	/**
936	* Set page setup.
937	*
938	* @param PageSetup $pValue
939	*
940	* @return Worksheet
941	*/
942	public function setPageSetup(PageSetup $pValue)
943	{
944	$this->pageSetup = $pValue;
945
946	return $this;
947	}
948
949	/**
950	* Get page margins.
951	*
952	* @return PageMargins
953	*/
954	public function getPageMargins()
955	{
956	return $this->pageMargins;
957	}
958
959	/**
960	* Set page margins.
961	*
962	* @param PageMargins $pValue
963	*
964	* @return Worksheet
965	*/
966	public function setPageMargins(PageMargins $pValue)
967	{
968	$this->pageMargins = $pValue;
969
970	return $this;
971	}
972
973	/**
974	* Get page header/footer.
975	*
976	* @return HeaderFooter
977	*/
978	public function getHeaderFooter()
979	{
980	return $this->headerFooter;
981	}
982
983	/**
984	* Set page header/footer.
985	*
986	* @param HeaderFooter $pValue
987	*
988	* @return Worksheet
989	*/
990	public function setHeaderFooter(HeaderFooter $pValue)
991	{
992	$this->headerFooter = $pValue;
993
994	return $this;
995	}
996
997	/**
998	* Get sheet view.
999	*
1000	* @return SheetView
1001	*/
1002	public function getSheetView()
1003	{
1004	return $this->sheetView;
1005	}
1006
1007	/**
1008	* Set sheet view.
1009	*
1010	* @param SheetView $pValue
1011	*
1012	* @return Worksheet
1013	*/
1014	public function setSheetView(SheetView $pValue)
1015	{
1016	$this->sheetView = $pValue;
1017
1018	return $this;
1019	}
1020
1021	/**
1022	* Get Protection.
1023	*
1024	* @return Protection
1025	*/
1026	public function getProtection()
1027	{
1028	return $this->protection;
1029	}
1030
1031	/**
1032	* Set Protection.
1033	*
1034	* @param Protection $pValue
1035	*
1036	* @return Worksheet
1037	*/
1038	public function setProtection(Protection $pValue)
1039	{
1040	$this->protection = $pValue;
1041	$this->dirty = true;
1042
1043	return $this;
1044	}
1045
1046	/**
1047	* Get highest worksheet column.
1048	*
1049	* @param string $row Return the data highest column for the specified row,
1050	* or the highest column of any row if no row number is passed
1051	*
1052	* @return string Highest column name
1053	*/
1054	public function getHighestColumn($row = null)
1055	{
1056	if ($row == null) {
1057	return $this->cachedHighestColumn;
1058	}
1059
1060	return $this->getHighestDataColumn($row);
1061	}
1062
1063	/**
1064	* Get highest worksheet column that contains data.
1065	*
1066	* @param string $row Return the highest data column for the specified row,
1067	* or the highest data column of any row if no row number is passed
1068	*
1069	* @return string Highest column name that contains data
1070	*/
1071	public function getHighestDataColumn($row = null)
1072	{
1073	return $this->cellCollection->getHighestColumn($row);
1074	}
1075
1076	/**
1077	* Get highest worksheet row.
1078	*
1079	* @param string $column Return the highest data row for the specified column,
1080	* or the highest row of any column if no column letter is passed
1081	*
1082	* @return int Highest row number
1083	*/
1084	public function getHighestRow($column = null)
1085	{
1086	if ($column == null) {
1087	return $this->cachedHighestRow;
1088	}
1089
1090	return $this->getHighestDataRow($column);
1091	}
1092
1093	/**
1094	* Get highest worksheet row that contains data.
1095	*
1096	* @param string $column Return the highest data row for the specified column,
1097	* or the highest data row of any column if no column letter is passed
1098	*
1099	* @return int Highest row number that contains data
1100	*/
1101	public function getHighestDataRow($column = null)
1102	{
1103	return $this->cellCollection->getHighestRow($column);
1104	}
1105
1106	/**
1107	* Get highest worksheet column and highest row that have cell records.
1108	*
1109	* @return array Highest column name and highest row number
1110	*/
1111	public function getHighestRowAndColumn()
1112	{
1113	return $this->cellCollection->getHighestRowAndColumn();
1114	}
1115
1116	/**
1117	* Set a cell value.
1118	*
1119	* @param string $pCoordinate Coordinate of the cell, eg: 'A1'
1120	* @param mixed $pValue Value of the cell
1121	*
1122	* @return Worksheet
1123	*/
1124	public function setCellValue($pCoordinate, $pValue)
1125	{
1126	$this->getCell($pCoordinate)->setValue($pValue);
1127
1128	return $this;
1129	}
1130
1131	/**
1132	* Set a cell value by using numeric cell coordinates.
1133	*
1134	* @param int $columnIndex Numeric column coordinate of the cell
1135	* @param int $row Numeric row coordinate of the cell
1136	* @param mixed $value Value of the cell
1137	*
1138	* @return Worksheet
1139	*/
1140	public function setCellValueByColumnAndRow($columnIndex, $row, $value)
1141	{
1142	$this->getCellByColumnAndRow($columnIndex, $row)->setValue($value);
1143
1144	return $this;
1145	}
1146
1147	/**
1148	* Set a cell value.
1149	*
1150	* @param string $pCoordinate Coordinate of the cell, eg: 'A1'
1151	* @param mixed $pValue Value of the cell
1152	* @param string $pDataType Explicit data type, see DataType::TYPE_*
1153	*
1154	* @return Worksheet
1155	*/
1156	public function setCellValueExplicit($pCoordinate, $pValue, $pDataType)
1157	{
1158	// Set value
1159	$this->getCell($pCoordinate)->setValueExplicit($pValue, $pDataType);
1160
1161	return $this;
1162	}
1163
1164	/**
1165	* Set a cell value by using numeric cell coordinates.
1166	*
1167	* @param int $columnIndex Numeric column coordinate of the cell
1168	* @param int $row Numeric row coordinate of the cell
1169	* @param mixed $value Value of the cell
1170	* @param string $dataType Explicit data type, see DataType::TYPE_*
1171	*
1172	* @return Worksheet
1173	*/
1174	public function setCellValueExplicitByColumnAndRow($columnIndex, $row, $value, $dataType)
1175	{
1176	$this->getCellByColumnAndRow($columnIndex, $row)->setValueExplicit($value, $dataType);
1177
1178	return $this;
1179	}
1180
1181	/**
1182	* Get cell at a specific coordinate.
1183	*
1184	* @param string $pCoordinate Coordinate of the cell, eg: 'A1'
1185	* @param bool $createIfNotExists Flag indicating whether a new cell should be created if it doesn't
1186	* already exist, or a null should be returned instead
1187	*
1188	* @throws Exception
1189	*
1190	* @return null\|Cell Cell that was found/created or null
1191	*/
1192	public function getCell($pCoordinate, $createIfNotExists = true)
1193	{
1194	// Uppercase coordinate
1195	$pCoordinateUpper = strtoupper($pCoordinate);
1196
1197	// Check cell collection
1198	if ($this->cellCollection->has($pCoordinateUpper)) {
1199	return $this->cellCollection->get($pCoordinateUpper);
1200	}
1201
1202	// Worksheet reference?
1203	if (strpos($pCoordinate, '!') !== false) {
1204	$worksheetReference = self::extractSheetTitle($pCoordinate, true);
1205
1206	return $this->parent->getSheetByName($worksheetReference[0])->getCell(strtoupper($worksheetReference[1]), $createIfNotExists);
1207	}
1208
1209	// Named range?
1210	if ((!preg_match('/^' . Calculation::CALCULATION_REGEXP_CELLREF . '$/i', $pCoordinate, $matches)) &&
1211	(preg_match('/^' . Calculation::CALCULATION_REGEXP_NAMEDRANGE . '$/i', $pCoordinate, $matches))) {
1212	$namedRange = NamedRange::resolveRange($pCoordinate, $this);
1213	if ($namedRange !== null) {
1214	$pCoordinate = $namedRange->getRange();
1215
1216	return $namedRange->getWorksheet()->getCell($pCoordinate, $createIfNotExists);
1217	}
1218	}
1219
1220	if (Coordinate::coordinateIsRange($pCoordinate)) {
1221	throw new Exception('Cell coordinate can not be a range of cells.');
1222	} elseif (strpos($pCoordinate, '$') !== false) {
1223	throw new Exception('Cell coordinate must not be absolute.');
1224	}
1225
1226	// Create new cell object, if required
1227	return $createIfNotExists ? $this->createNewCell($pCoordinateUpper) : null;
1228	}
1229
1230	/**
1231	* Get cell at a specific coordinate by using numeric cell coordinates.
1232	*
1233	* @param int $columnIndex Numeric column coordinate of the cell
1234	* @param int $row Numeric row coordinate of the cell
1235	* @param bool $createIfNotExists Flag indicating whether a new cell should be created if it doesn't
1236	* already exist, or a null should be returned instead
1237	*
1238	* @return null\|Cell Cell that was found/created or null
1239	*/
1240	public function getCellByColumnAndRow($columnIndex, $row, $createIfNotExists = true)
1241	{
1242	$columnLetter = Coordinate::stringFromColumnIndex($columnIndex);
1243	$coordinate = $columnLetter . $row;
1244
1245	if ($this->cellCollection->has($coordinate)) {
1246	return $this->cellCollection->get($coordinate);
1247	}
1248
1249	// Create new cell object, if required
1250	return $createIfNotExists ? $this->createNewCell($coordinate) : null;
1251	}
1252
1253	/**
1254	* Create a new cell at the specified coordinate.
1255	*
1256	* @param string $pCoordinate Coordinate of the cell
1257	*
1258	* @return Cell Cell that was created
1259	*/
1260	private function createNewCell($pCoordinate)
1261	{
1262	$cell = new Cell(null, DataType::TYPE_NULL, $this);
1263	$this->cellCollection->add($pCoordinate, $cell);
1264	$this->cellCollectionIsSorted = false;
1265
1266	// Coordinates
1267	$aCoordinates = Coordinate::coordinateFromString($pCoordinate);
1268	if (Coordinate::columnIndexFromString($this->cachedHighestColumn) < Coordinate::columnIndexFromString($aCoordinates[0])) {
1269	$this->cachedHighestColumn = $aCoordinates[0];
1270	}
1271	if ($aCoordinates[1] > $this->cachedHighestRow) {
1272	$this->cachedHighestRow = $aCoordinates[1];
1273	}
1274
1275	// Cell needs appropriate xfIndex from dimensions records
1276	// but don't create dimension records if they don't already exist
1277	$rowDimension = $this->getRowDimension($aCoordinates[1], false);
1278	$columnDimension = $this->getColumnDimension($aCoordinates[0], false);
1279
1280	if ($rowDimension !== null && $rowDimension->getXfIndex() > 0) {
1281	// then there is a row dimension with explicit style, assign it to the cell
1282	$cell->setXfIndex($rowDimension->getXfIndex());
1283	} elseif ($columnDimension !== null && $columnDimension->getXfIndex() > 0) {
1284	// then there is a column dimension, assign it to the cell
1285	$cell->setXfIndex($columnDimension->getXfIndex());
1286	}
1287
1288	return $cell;
1289	}
1290
1291	/**
1292	* Does the cell at a specific coordinate exist?
1293	*
1294	* @param string $pCoordinate Coordinate of the cell eg: 'A1'
1295	*
1296	* @throws Exception
1297	*
1298	* @return bool
1299	*/
1300	public function cellExists($pCoordinate)
1301	{
1302	// Worksheet reference?
1303	if (strpos($pCoordinate, '!') !== false) {
1304	$worksheetReference = self::extractSheetTitle($pCoordinate, true);
1305
1306	return $this->parent->getSheetByName($worksheetReference[0])->cellExists(strtoupper($worksheetReference[1]));
1307	}
1308
1309	// Named range?
1310	if ((!preg_match('/^' . Calculation::CALCULATION_REGEXP_CELLREF . '$/i', $pCoordinate, $matches)) &&
1311	(preg_match('/^' . Calculation::CALCULATION_REGEXP_NAMEDRANGE . '$/i', $pCoordinate, $matches))) {
1312	$namedRange = NamedRange::resolveRange($pCoordinate, $this);
1313	if ($namedRange !== null) {
1314	$pCoordinate = $namedRange->getRange();
1315	if ($this->getHashCode() != $namedRange->getWorksheet()->getHashCode()) {
1316	if (!$namedRange->getLocalOnly()) {
1317	return $namedRange->getWorksheet()->cellExists($pCoordinate);
1318	}
1319
1320	throw new Exception('Named range ' . $namedRange->getName() . ' is not accessible from within sheet ' . $this->getTitle());
1321	}
1322	} else {
1323	return false;
1324	}
1325	}
1326
1327	// Uppercase coordinate
1328	$pCoordinate = strtoupper($pCoordinate);
1329
1330	if (Coordinate::coordinateIsRange($pCoordinate)) {
1331	throw new Exception('Cell coordinate can not be a range of cells.');
1332	} elseif (strpos($pCoordinate, '$') !== false) {
1333	throw new Exception('Cell coordinate must not be absolute.');
1334	}
1335
1336	// Cell exists?
1337	return $this->cellCollection->has($pCoordinate);
1338	}
1339
1340	/**
1341	* Cell at a specific coordinate by using numeric cell coordinates exists?
1342	*
1343	* @param int $columnIndex Numeric column coordinate of the cell
1344	* @param int $row Numeric row coordinate of the cell
1345	*
1346	* @return bool
1347	*/
1348	public function cellExistsByColumnAndRow($columnIndex, $row)
1349	{
1350	return $this->cellExists(Coordinate::stringFromColumnIndex($columnIndex) . $row);
1351	}
1352
1353	/**
1354	* Get row dimension at a specific row.
1355	*
1356	* @param int $pRow Numeric index of the row
1357	* @param bool $create
1358	*
1359	* @return RowDimension
1360	*/
1361	public function getRowDimension($pRow, $create = true)
1362	{
1363	// Found
1364	$found = null;
1365
1366	// Get row dimension
1367	if (!isset($this->rowDimensions[$pRow])) {
1368	if (!$create) {
1369	return null;
1370	}
1371	$this->rowDimensions[$pRow] = new RowDimension($pRow);
1372
1373	$this->cachedHighestRow = max($this->cachedHighestRow, $pRow);
1374	}
1375
1376	return $this->rowDimensions[$pRow];
1377	}
1378
1379	/**
1380	* Get column dimension at a specific column.
1381	*
1382	* @param string $pColumn String index of the column eg: 'A'
1383	* @param bool $create
1384	*
1385	* @return ColumnDimension
1386	*/
1387	public function getColumnDimension($pColumn, $create = true)
1388	{
1389	// Uppercase coordinate
1390	$pColumn = strtoupper($pColumn);
1391
1392	// Fetch dimensions
1393	if (!isset($this->columnDimensions[$pColumn])) {
1394	if (!$create) {
1395	return null;
1396	}
1397	$this->columnDimensions[$pColumn] = new ColumnDimension($pColumn);
1398
1399	if (Coordinate::columnIndexFromString($this->cachedHighestColumn) < Coordinate::columnIndexFromString($pColumn)) {
1400	$this->cachedHighestColumn = $pColumn;
1401	}
1402	}
1403
1404	return $this->columnDimensions[$pColumn];
1405	}
1406
1407	/**
1408	* Get column dimension at a specific column by using numeric cell coordinates.
1409	*
1410	* @param int $columnIndex Numeric column coordinate of the cell
1411	*
1412	* @return ColumnDimension
1413	*/
1414	public function getColumnDimensionByColumn($columnIndex)
1415	{
1416	return $this->getColumnDimension(Coordinate::stringFromColumnIndex($columnIndex));
1417	}
1418
1419	/**
1420	* Get styles.
1421	*
1422	* @return Style[]
1423	*/
1424	public function getStyles()
1425	{
1426	return $this->styles;
1427	}
1428
1429	/**
1430	* Get style for cell.
1431	*
1432	* @param string $pCellCoordinate Cell coordinate (or range) to get style for, eg: 'A1'
1433	*
1434	* @throws Exception
1435	*
1436	* @return Style
1437	*/
1438	public function getStyle($pCellCoordinate)
1439	{
1440	// set this sheet as active
1441	$this->parent->setActiveSheetIndex($this->parent->getIndex($this));
1442
1443	// set cell coordinate as active
1444	$this->setSelectedCells(strtoupper($pCellCoordinate));
1445
1446	return $this->parent->getCellXfSupervisor();
1447	}
1448
1449	/**
1450	* Get conditional styles for a cell.
1451	*
1452	* @param string $pCoordinate eg: 'A1'
1453	*
1454	* @return Conditional[]
1455	*/
1456	public function getConditionalStyles($pCoordinate)
1457	{
1458	$pCoordinate = strtoupper($pCoordinate);
1459	if (!isset($this->conditionalStylesCollection[$pCoordinate])) {
1460	$this->conditionalStylesCollection[$pCoordinate] = [];
1461	}
1462
1463	return $this->conditionalStylesCollection[$pCoordinate];
1464	}
1465
1466	/**
1467	* Do conditional styles exist for this cell?
1468	*
1469	* @param string $pCoordinate eg: 'A1'
1470	*
1471	* @return bool
1472	*/
1473	public function conditionalStylesExists($pCoordinate)
1474	{
1475	return isset($this->conditionalStylesCollection[strtoupper($pCoordinate)]);
1476	}
1477
1478	/**
1479	* Removes conditional styles for a cell.
1480	*
1481	* @param string $pCoordinate eg: 'A1'
1482	*
1483	* @return Worksheet
1484	*/
1485	public function removeConditionalStyles($pCoordinate)
1486	{
1487	unset($this->conditionalStylesCollection[strtoupper($pCoordinate)]);
1488
1489	return $this;
1490	}
1491
1492	/**
1493	* Get collection of conditional styles.
1494	*
1495	* @return array
1496	*/
1497	public function getConditionalStylesCollection()
1498	{
1499	return $this->conditionalStylesCollection;
1500	}
1501
1502	/**
1503	* Set conditional styles.
1504	*
1505	* @param string $pCoordinate eg: 'A1'
1506	* @param $pValue Conditional[]
1507	*
1508	* @return Worksheet
1509	*/
1510	public function setConditionalStyles($pCoordinate, $pValue)
1511	{
1512	$this->conditionalStylesCollection[strtoupper($pCoordinate)] = $pValue;
1513
1514	return $this;
1515	}
1516
1517	/**
1518	* Get style for cell by using numeric cell coordinates.
1519	*
1520	* @param int $columnIndex1 Numeric column coordinate of the cell
1521	* @param int $row1 Numeric row coordinate of the cell
1522	* @param null\|int $columnIndex2 Numeric column coordinate of the range cell
1523	* @param null\|int $row2 Numeric row coordinate of the range cell
1524	*
1525	* @return Style
1526	*/
1527	public function getStyleByColumnAndRow($columnIndex1, $row1, $columnIndex2 = null, $row2 = null)
1528	{
1529	if ($columnIndex2 !== null && $row2 !== null) {
1530	$cellRange = Coordinate::stringFromColumnIndex($columnIndex1) . $row1 . ':' . Coordinate::stringFromColumnIndex($columnIndex2) . $row2;
1531
1532	return $this->getStyle($cellRange);
1533	}
1534
1535	return $this->getStyle(Coordinate::stringFromColumnIndex($columnIndex1) . $row1);
1536	}
1537
1538	/**
1539	* Duplicate cell style to a range of cells.
1540	*
1541	* Please note that this will overwrite existing cell styles for cells in range!
1542	*
1543	* @param Style $pCellStyle Cell style to duplicate
1544	* @param string $pRange Range of cells (i.e. "A1:B10"), or just one cell (i.e. "A1")
1545	*
1546	* @throws Exception
1547	*
1548	* @return Worksheet
1549	*/
1550	public function duplicateStyle(Style $pCellStyle, $pRange)
1551	{
1552	// Add the style to the workbook if necessary
1553	$workbook = $this->parent;
1554	if ($existingStyle = $this->parent->getCellXfByHashCode($pCellStyle->getHashCode())) {
1555	// there is already such cell Xf in our collection
1556	$xfIndex = $existingStyle->getIndex();
1557	} else {
1558	// we don't have such a cell Xf, need to add
1559	$workbook->addCellXf($pCellStyle);
1560	$xfIndex = $pCellStyle->getIndex();
1561	}
1562
1563	// Calculate range outer borders
1564	list($rangeStart, $rangeEnd) = Coordinate::rangeBoundaries($pRange . ':' . $pRange);
1565
1566	// Make sure we can loop upwards on rows and columns
1567	if ($rangeStart[0] > $rangeEnd[0] && $rangeStart[1] > $rangeEnd[1]) {
1568	$tmp = $rangeStart;
1569	$rangeStart = $rangeEnd;
1570	$rangeEnd = $tmp;
1571	}
1572
1573	// Loop through cells and apply styles
1574	for ($col = $rangeStart[0]; $col <= $rangeEnd[0]; ++$col) {
1575	for ($row = $rangeStart[1]; $row <= $rangeEnd[1]; ++$row) {
1576	$this->getCell(Coordinate::stringFromColumnIndex($col) . $row)->setXfIndex($xfIndex);
1577	}
1578	}
1579
1580	return $this;
1581	}
1582
1583	/**
1584	* Duplicate conditional style to a range of cells.
1585	*
1586	* Please note that this will overwrite existing cell styles for cells in range!
1587	*
1588	* @param Conditional[] $pCellStyle Cell style to duplicate
1589	* @param string $pRange Range of cells (i.e. "A1:B10"), or just one cell (i.e. "A1")
1590	*
1591	* @throws Exception
1592	*
1593	* @return Worksheet
1594	*/
1595	public function duplicateConditionalStyle(array $pCellStyle, $pRange = '')
1596	{
1597	foreach ($pCellStyle as $cellStyle) {
1598	if (!($cellStyle instanceof Conditional)) {
1599	throw new Exception('Style is not a conditional style');
1600	}
1601	}
1602
1603	// Calculate range outer borders
1604	list($rangeStart, $rangeEnd) = Coordinate::rangeBoundaries($pRange . ':' . $pRange);
1605
1606	// Make sure we can loop upwards on rows and columns
1607	if ($rangeStart[0] > $rangeEnd[0] && $rangeStart[1] > $rangeEnd[1]) {
1608	$tmp = $rangeStart;
1609	$rangeStart = $rangeEnd;
1610	$rangeEnd = $tmp;
1611	}
1612
1613	// Loop through cells and apply styles
1614	for ($col = $rangeStart[0]; $col <= $rangeEnd[0]; ++$col) {
1615	for ($row = $rangeStart[1]; $row <= $rangeEnd[1]; ++$row) {
1616	$this->setConditionalStyles(Coordinate::stringFromColumnIndex($col) . $row, $pCellStyle);
1617	}
1618	}
1619
1620	return $this;
1621	}
1622
1623	/**
1624	* Set break on a cell.
1625	*
1626	* @param string $pCoordinate Cell coordinate (e.g. A1)
1627	* @param int $pBreak Break type (type of Worksheet::BREAK_*)
1628	*
1629	* @throws Exception
1630	*
1631	* @return Worksheet
1632	*/
1633	public function setBreak($pCoordinate, $pBreak)
1634	{
1635	// Uppercase coordinate
1636	$pCoordinate = strtoupper($pCoordinate);
1637
1638	if ($pCoordinate != '') {
1639	if ($pBreak == self::BREAK_NONE) {
1640	if (isset($this->breaks[$pCoordinate])) {
1641	unset($this->breaks[$pCoordinate]);
1642	}
1643	} else {
1644	$this->breaks[$pCoordinate] = $pBreak;
1645	}
1646	} else {
1647	throw new Exception('No cell coordinate specified.');
1648	}
1649
1650	return $this;
1651	}
1652
1653	/**
1654	* Set break on a cell by using numeric cell coordinates.
1655	*
1656	* @param int $columnIndex Numeric column coordinate of the cell
1657	* @param int $row Numeric row coordinate of the cell
1658	* @param int $break Break type (type of Worksheet::BREAK_*)
1659	*
1660	* @return Worksheet
1661	*/
1662	public function setBreakByColumnAndRow($columnIndex, $row, $break)
1663	{
1664	return $this->setBreak(Coordinate::stringFromColumnIndex($columnIndex) . $row, $break);
1665	}
1666
1667	/**
1668	* Get breaks.
1669	*
1670	* @return array[]
1671	*/
1672	public function getBreaks()
1673	{
1674	return $this->breaks;
1675	}
1676
1677	/**
1678	* Set merge on a cell range.
1679	*
1680	* @param string $pRange Cell range (e.g. A1:E1)
1681	*
1682	* @throws Exception
1683	*
1684	* @return Worksheet
1685	*/
1686	public function mergeCells($pRange)
1687	{
1688	// Uppercase coordinate
1689	$pRange = strtoupper($pRange);
1690
1691	if (strpos($pRange, ':') !== false) {
1692	$this->mergeCells[$pRange] = $pRange;
1693
1694	// make sure cells are created
1695
1696	// get the cells in the range
1697	$aReferences = Coordinate::extractAllCellReferencesInRange($pRange);
1698
1699	// create upper left cell if it does not already exist
1700	$upperLeft = $aReferences[0];
1701	if (!$this->cellExists($upperLeft)) {
1702	$this->getCell($upperLeft)->setValueExplicit(null, DataType::TYPE_NULL);
1703	}
1704
1705	// Blank out the rest of the cells in the range (if they exist)
1706	$count = count($aReferences);
1707	for ($i = 1; $i < $count; ++$i) {
1708	if ($this->cellExists($aReferences[$i])) {
1709	$this->getCell($aReferences[$i])->setValueExplicit(null, DataType::TYPE_NULL);
1710	}
1711	}
1712	} else {
1713	throw new Exception('Merge must be set on a range of cells.');
1714	}
1715
1716	return $this;
1717	}
1718
1719	/**
1720	* Set merge on a cell range by using numeric cell coordinates.
1721	*
1722	* @param int $columnIndex1 Numeric column coordinate of the first cell
1723	* @param int $row1 Numeric row coordinate of the first cell
1724	* @param int $columnIndex2 Numeric column coordinate of the last cell
1725	* @param int $row2 Numeric row coordinate of the last cell
1726	*
1727	* @throws Exception
1728	*
1729	* @return Worksheet
1730	*/
1731	public function mergeCellsByColumnAndRow($columnIndex1, $row1, $columnIndex2, $row2)
1732	{
1733	$cellRange = Coordinate::stringFromColumnIndex($columnIndex1) . $row1 . ':' . Coordinate::stringFromColumnIndex($columnIndex2) . $row2;
1734
1735	return $this->mergeCells($cellRange);
1736	}
1737
1738	/**
1739	* Remove merge on a cell range.
1740	*
1741	* @param string $pRange Cell range (e.g. A1:E1)
1742	*
1743	* @throws Exception
1744	*
1745	* @return Worksheet
1746	*/
1747	public function unmergeCells($pRange)
1748	{
1749	// Uppercase coordinate
1750	$pRange = strtoupper($pRange);
1751
1752	if (strpos($pRange, ':') !== false) {
1753	if (isset($this->mergeCells[$pRange])) {
1754	unset($this->mergeCells[$pRange]);
1755	} else {
1756	throw new Exception('Cell range ' . $pRange . ' not known as merged.');
1757	}
1758	} else {
1759	throw new Exception('Merge can only be removed from a range of cells.');
1760	}
1761
1762	return $this;
1763	}
1764
1765	/**
1766	* Remove merge on a cell range by using numeric cell coordinates.
1767	*
1768	* @param int $columnIndex1 Numeric column coordinate of the first cell
1769	* @param int $row1 Numeric row coordinate of the first cell
1770	* @param int $columnIndex2 Numeric column coordinate of the last cell
1771	* @param int $row2 Numeric row coordinate of the last cell
1772	*
1773	* @throws Exception
1774	*
1775	* @return Worksheet
1776	*/
1777	public function unmergeCellsByColumnAndRow($columnIndex1, $row1, $columnIndex2, $row2)
1778	{
1779	$cellRange = Coordinate::stringFromColumnIndex($columnIndex1) . $row1 . ':' . Coordinate::stringFromColumnIndex($columnIndex2) . $row2;
1780
1781	return $this->unmergeCells($cellRange);
1782	}
1783
1784	/**
1785	* Get merge cells array.
1786	*
1787	* @return array[]
1788	*/
1789	public function getMergeCells()
1790	{
1791	return $this->mergeCells;
1792	}
1793
1794	/**
1795	* Set merge cells array for the entire sheet. Use instead mergeCells() to merge
1796	* a single cell range.
1797	*
1798	* @param array $pValue
1799	*
1800	* @return Worksheet
1801	*/
1802	public function setMergeCells(array $pValue)
1803	{
1804	$this->mergeCells = $pValue;
1805
1806	return $this;
1807	}
1808
1809	/**
1810	* Set protection on a cell range.
1811	*
1812	* @param string $pRange Cell (e.g. A1) or cell range (e.g. A1:E1)
1813	* @param string $pPassword Password to unlock the protection
1814	* @param bool $pAlreadyHashed If the password has already been hashed, set this to true
1815	*
1816	* @return Worksheet
1817	*/
1818	public function protectCells($pRange, $pPassword, $pAlreadyHashed = false)
1819	{
1820	// Uppercase coordinate
1821	$pRange = strtoupper($pRange);
1822
1823	if (!$pAlreadyHashed) {
1824	$pPassword = Shared\PasswordHasher::hashPassword($pPassword);
1825	}
1826	$this->protectedCells[$pRange] = $pPassword;
1827
1828	return $this;
1829	}
1830
1831	/**
1832	* Set protection on a cell range by using numeric cell coordinates.
1833	*
1834	* @param int $columnIndex1 Numeric column coordinate of the first cell
1835	* @param int $row1 Numeric row coordinate of the first cell
1836	* @param int $columnIndex2 Numeric column coordinate of the last cell
1837	* @param int $row2 Numeric row coordinate of the last cell
1838	* @param string $password Password to unlock the protection
1839	* @param bool $alreadyHashed If the password has already been hashed, set this to true
1840	*
1841	* @return Worksheet
1842	*/
1843	public function protectCellsByColumnAndRow($columnIndex1, $row1, $columnIndex2, $row2, $password, $alreadyHashed = false)
1844	{
1845	$cellRange = Coordinate::stringFromColumnIndex($columnIndex1) . $row1 . ':' . Coordinate::stringFromColumnIndex($columnIndex2) . $row2;
1846
1847	return $this->protectCells($cellRange, $password, $alreadyHashed);
1848	}
1849
1850	/**
1851	* Remove protection on a cell range.
1852	*
1853	* @param string $pRange Cell (e.g. A1) or cell range (e.g. A1:E1)
1854	*
1855	* @throws Exception
1856	*
1857	* @return Worksheet
1858	*/
1859	public function unprotectCells($pRange)
1860	{
1861	// Uppercase coordinate
1862	$pRange = strtoupper($pRange);
1863
1864	if (isset($this->protectedCells[$pRange])) {
1865	unset($this->protectedCells[$pRange]);
1866	} else {
1867	throw new Exception('Cell range ' . $pRange . ' not known as protected.');
1868	}
1869
1870	return $this;
1871	}
1872
1873	/**
1874	* Remove protection on a cell range by using numeric cell coordinates.
1875	*
1876	* @param int $columnIndex1 Numeric column coordinate of the first cell
1877	* @param int $row1 Numeric row coordinate of the first cell
1878	* @param int $columnIndex2 Numeric column coordinate of the last cell
1879	* @param int $row2 Numeric row coordinate of the last cell
1880	*
1881	* @throws Exception
1882	*
1883	* @return Worksheet
1884	*/
1885	public function unprotectCellsByColumnAndRow($columnIndex1, $row1, $columnIndex2, $row2)
1886	{
1887	$cellRange = Coordinate::stringFromColumnIndex($columnIndex1) . $row1 . ':' . Coordinate::stringFromColumnIndex($columnIndex2) . $row2;
1888
1889	return $this->unprotectCells($cellRange);
1890	}
1891
1892	/**
1893	* Get protected cells.
1894	*
1895	* @return array[]
1896	*/
1897	public function getProtectedCells()
1898	{
1899	return $this->protectedCells;
1900	}
1901
1902	/**
1903	* Get Autofilter.
1904	*
1905	* @return AutoFilter
1906	*/
1907	public function getAutoFilter()
1908	{
1909	return $this->autoFilter;
1910	}
1911
1912	/**
1913	* Set AutoFilter.
1914	*
1915	* @param AutoFilter\|string $pValue
1916	* A simple string containing a Cell range like 'A1:E10' is permitted for backward compatibility
1917	*
1918	* @throws Exception
1919	*
1920	* @return Worksheet
1921	*/
1922	public function setAutoFilter($pValue)
1923	{
1924	if (is_string($pValue)) {
1925	$this->autoFilter->setRange($pValue);
1926	} elseif (is_object($pValue) && ($pValue instanceof AutoFilter)) {
1927	$this->autoFilter = $pValue;
1928	}
1929
1930	return $this;
1931	}
1932
1933	/**
1934	* Set Autofilter Range by using numeric cell coordinates.
1935	*
1936	* @param int $columnIndex1 Numeric column coordinate of the first cell
1937	* @param int $row1 Numeric row coordinate of the first cell
1938	* @param int $columnIndex2 Numeric column coordinate of the second cell
1939	* @param int $row2 Numeric row coordinate of the second cell
1940	*
1941	* @throws Exception
1942	*
1943	* @return Worksheet
1944	*/
1945	public function setAutoFilterByColumnAndRow($columnIndex1, $row1, $columnIndex2, $row2)
1946	{
1947	return $this->setAutoFilter(
1948	Coordinate::stringFromColumnIndex($columnIndex1) . $row1
1949	. ':' .
1950	Coordinate::stringFromColumnIndex($columnIndex2) . $row2
1951	);
1952	}
1953
1954	/**
1955	* Remove autofilter.
1956	*
1957	* @return Worksheet
1958	*/
1959	public function removeAutoFilter()
1960	{
1961	$this->autoFilter->setRange(null);
1962
1963	return $this;
1964	}
1965
1966	/**
1967	* Get Freeze Pane.
1968	*
1969	* @return string
1970	*/
1971	public function getFreezePane()
1972	{
1973	return $this->freezePane;
1974	}
1975
1976	/**
1977	* Freeze Pane.
1978	*
1979	* Examples:
1980	*
1981	* - A2 will freeze the rows above cell A2 (i.e row 1)
1982	* - B1 will freeze the columns to the left of cell B1 (i.e column A)
1983	* - B2 will freeze the rows above and to the left of cell B2 (i.e row 1 and column A)
1984	*
1985	* @param null\|string $cell Position of the split
1986	* @param null\|string $topLeftCell default position of the right bottom pane
1987	*
1988	* @throws Exception
1989	*
1990	* @return Worksheet
1991	*/
1992	public function freezePane($cell, $topLeftCell = null)
1993	{
1994	if (is_string($cell) && Coordinate::coordinateIsRange($cell)) {
1995	throw new Exception('Freeze pane can not be set on a range of cells.');
1996	}
1997
1998	if ($cell !== null && $topLeftCell === null) {
1999	$coordinate = Coordinate::coordinateFromString($cell);
2000	$topLeftCell = $coordinate[0] . $coordinate[1];
2001	}
2002
2003	$this->freezePane = $cell;
2004	$this->topLeftCell = $topLeftCell;
2005
2006	return $this;
2007	}
2008
2009	/**
2010	* Freeze Pane by using numeric cell coordinates.
2011	*
2012	* @param int $columnIndex Numeric column coordinate of the cell
2013	* @param int $row Numeric row coordinate of the cell
2014	*
2015	* @return Worksheet
2016	*/
2017	public function freezePaneByColumnAndRow($columnIndex, $row)
2018	{
2019	return $this->freezePane(Coordinate::stringFromColumnIndex($columnIndex) . $row);
2020	}
2021
2022	/**
2023	* Unfreeze Pane.
2024	*
2025	* @return Worksheet
2026	*/
2027	public function unfreezePane()
2028	{
2029	return $this->freezePane(null);
2030	}
2031
2032	/**
2033	* Get the default position of the right bottom pane.
2034	*
2035	* @return int
2036	*/
2037	public function getTopLeftCell()
2038	{
2039	return $this->topLeftCell;
2040	}
2041
2042	/**
2043	* Insert a new row, updating all possible related data.
2044	*
2045	* @param int $pBefore Insert before this one
2046	* @param int $pNumRows Number of rows to insert
2047	*
2048	* @throws Exception
2049	*
2050	* @return Worksheet
2051	*/
2052	public function insertNewRowBefore($pBefore, $pNumRows = 1)
2053	{
2054	if ($pBefore >= 1) {
2055	$objReferenceHelper = ReferenceHelper::getInstance();
2056	$objReferenceHelper->insertNewBefore('A' . $pBefore, 0, $pNumRows, $this);
2057	} else {
2058	throw new Exception('Rows can only be inserted before at least row 1.');
2059	}
2060
2061	return $this;
2062	}
2063
2064	/**
2065	* Insert a new column, updating all possible related data.
2066	*
2067	* @param string $pBefore Insert before this one, eg: 'A'
2068	* @param int $pNumCols Number of columns to insert
2069	*
2070	* @throws Exception
2071	*
2072	* @return Worksheet
2073	*/
2074	public function insertNewColumnBefore($pBefore, $pNumCols = 1)
2075	{
2076	if (!is_numeric($pBefore)) {
2077	$objReferenceHelper = ReferenceHelper::getInstance();
2078	$objReferenceHelper->insertNewBefore($pBefore . '1', $pNumCols, 0, $this);
2079	} else {
2080	throw new Exception('Column references should not be numeric.');
2081	}
2082
2083	return $this;
2084	}
2085
2086	/**
2087	* Insert a new column, updating all possible related data.
2088	*
2089	* @param int $beforeColumnIndex Insert before this one (numeric column coordinate of the cell)
2090	* @param int $pNumCols Number of columns to insert
2091	*
2092	* @throws Exception
2093	*
2094	* @return Worksheet
2095	*/
2096	public function insertNewColumnBeforeByIndex($beforeColumnIndex, $pNumCols = 1)
2097	{
2098	if ($beforeColumnIndex >= 1) {
2099	return $this->insertNewColumnBefore(Coordinate::stringFromColumnIndex($beforeColumnIndex), $pNumCols);
2100	}
2101
2102	throw new Exception('Columns can only be inserted before at least column A (1).');
2103	}
2104
2105	/**
2106	* Delete a row, updating all possible related data.
2107	*
2108	* @param int $pRow Remove starting with this one
2109	* @param int $pNumRows Number of rows to remove
2110	*
2111	* @throws Exception
2112	*
2113	* @return Worksheet
2114	*/
2115	public function removeRow($pRow, $pNumRows = 1)
2116	{
2117	if ($pRow >= 1) {
2118	$highestRow = $this->getHighestDataRow();
2119	$objReferenceHelper = ReferenceHelper::getInstance();
2120	$objReferenceHelper->insertNewBefore('A' . ($pRow + $pNumRows), 0, -$pNumRows, $this);
2121	for ($r = 0; $r < $pNumRows; ++$r) {
2122	$this->getCellCollection()->removeRow($highestRow);
2123	--$highestRow;
2124	}
2125	} else {
2126	throw new Exception('Rows to be deleted should at least start from row 1.');
2127	}
2128
2129	return $this;
2130	}
2131
2132	/**
2133	* Remove a column, updating all possible related data.
2134	*
2135	* @param string $pColumn Remove starting with this one, eg: 'A'
2136	* @param int $pNumCols Number of columns to remove
2137	*
2138	* @throws Exception
2139	*
2140	* @return Worksheet
2141	*/
2142	public function removeColumn($pColumn, $pNumCols = 1)
2143	{
2144	if (!is_numeric($pColumn)) {
2145	$highestColumn = $this->getHighestDataColumn();
2146	$pColumn = Coordinate::stringFromColumnIndex(Coordinate::columnIndexFromString($pColumn) + $pNumCols);
2147	$objReferenceHelper = ReferenceHelper::getInstance();
2148	$objReferenceHelper->insertNewBefore($pColumn . '1', -$pNumCols, 0, $this);
2149	for ($c = 0; $c < $pNumCols; ++$c) {
2150	$this->getCellCollection()->removeColumn($highestColumn);
2151	$highestColumn = Coordinate::stringFromColumnIndex(Coordinate::columnIndexFromString($highestColumn) - 1);
2152	}
2153	} else {
2154	throw new Exception('Column references should not be numeric.');
2155	}
2156
2157	return $this;
2158	}
2159
2160	/**
2161	* Remove a column, updating all possible related data.
2162	*
2163	* @param int $columnIndex Remove starting with this one (numeric column coordinate of the cell)
2164	* @param int $numColumns Number of columns to remove
2165	*
2166	* @throws Exception
2167	*
2168	* @return Worksheet
2169	*/
2170	public function removeColumnByIndex($columnIndex, $numColumns = 1)
2171	{
2172	if ($columnIndex >= 1) {
2173	return $this->removeColumn(Coordinate::stringFromColumnIndex($columnIndex), $numColumns);
2174	}
2175
2176	throw new Exception('Columns to be deleted should at least start from column A (1)');
2177	}
2178
2179	/**
2180	* Show gridlines?
2181	*
2182	* @return bool
2183	*/
2184	public function getShowGridlines()
2185	{
2186	return $this->showGridlines;
2187	}
2188
2189	/**
2190	* Set show gridlines.
2191	*
2192	* @param bool $pValue Show gridlines (true/false)
2193	*
2194	* @return Worksheet
2195	*/
2196	public function setShowGridlines($pValue)
2197	{
2198	$this->showGridlines = $pValue;
2199
2200	return $this;
2201	}
2202
2203	/**
2204	* Print gridlines?
2205	*
2206	* @return bool
2207	*/
2208	public function getPrintGridlines()
2209	{
2210	return $this->printGridlines;
2211	}
2212
2213	/**
2214	* Set print gridlines.
2215	*
2216	* @param bool $pValue Print gridlines (true/false)
2217	*
2218	* @return Worksheet
2219	*/
2220	public function setPrintGridlines($pValue)
2221	{
2222	$this->printGridlines = $pValue;
2223
2224	return $this;
2225	}
2226
2227	/**
2228	* Show row and column headers?
2229	*
2230	* @return bool
2231	*/
2232	public function getShowRowColHeaders()
2233	{
2234	return $this->showRowColHeaders;
2235	}
2236
2237	/**
2238	* Set show row and column headers.
2239	*
2240	* @param bool $pValue Show row and column headers (true/false)
2241	*
2242	* @return Worksheet
2243	*/
2244	public function setShowRowColHeaders($pValue)
2245	{
2246	$this->showRowColHeaders = $pValue;
2247
2248	return $this;
2249	}
2250
2251	/**
2252	* Show summary below? (Row/Column outlining).
2253	*
2254	* @return bool
2255	*/
2256	public function getShowSummaryBelow()
2257	{
2258	return $this->showSummaryBelow;
2259	}
2260
2261	/**
2262	* Set show summary below.
2263	*
2264	* @param bool $pValue Show summary below (true/false)
2265	*
2266	* @return Worksheet
2267	*/
2268	public function setShowSummaryBelow($pValue)
2269	{
2270	$this->showSummaryBelow = $pValue;
2271
2272	return $this;
2273	}
2274
2275	/**
2276	* Show summary right? (Row/Column outlining).
2277	*
2278	* @return bool
2279	*/
2280	public function getShowSummaryRight()
2281	{
2282	return $this->showSummaryRight;
2283	}
2284
2285	/**
2286	* Set show summary right.
2287	*
2288	* @param bool $pValue Show summary right (true/false)
2289	*
2290	* @return Worksheet
2291	*/
2292	public function setShowSummaryRight($pValue)
2293	{
2294	$this->showSummaryRight = $pValue;
2295
2296	return $this;
2297	}
2298
2299	/**
2300	* Get comments.
2301	*
2302	* @return Comment[]
2303	*/
2304	public function getComments()
2305	{
2306	return $this->comments;
2307	}
2308
2309	/**
2310	* Set comments array for the entire sheet.
2311	*
2312	* @param Comment[] $pValue
2313	*
2314	* @return Worksheet
2315	*/
2316	public function setComments(array $pValue)
2317	{
2318	$this->comments = $pValue;
2319
2320	return $this;
2321	}
2322
2323	/**
2324	* Get comment for cell.
2325	*
2326	* @param string $pCellCoordinate Cell coordinate to get comment for, eg: 'A1'
2327	*
2328	* @throws Exception
2329	*
2330	* @return Comment
2331	*/
2332	public function getComment($pCellCoordinate)
2333	{
2334	// Uppercase coordinate
2335	$pCellCoordinate = strtoupper($pCellCoordinate);
2336
2337	if (Coordinate::coordinateIsRange($pCellCoordinate)) {
2338	throw new Exception('Cell coordinate string can not be a range of cells.');
2339	} elseif (strpos($pCellCoordinate, '$') !== false) {
2340	throw new Exception('Cell coordinate string must not be absolute.');
2341	} elseif ($pCellCoordinate == '') {
2342	throw new Exception('Cell coordinate can not be zero-length string.');
2343	}
2344
2345	// Check if we already have a comment for this cell.
2346	if (isset($this->comments[$pCellCoordinate])) {
2347	return $this->comments[$pCellCoordinate];
2348	}
2349
2350	// If not, create a new comment.
2351	$newComment = new Comment();
2352	$this->comments[$pCellCoordinate] = $newComment;
2353
2354	return $newComment;
2355	}
2356
2357	/**
2358	* Get comment for cell by using numeric cell coordinates.
2359	*
2360	* @param int $columnIndex Numeric column coordinate of the cell
2361	* @param int $row Numeric row coordinate of the cell
2362	*
2363	* @return Comment
2364	*/
2365	public function getCommentByColumnAndRow($columnIndex, $row)
2366	{
2367	return $this->getComment(Coordinate::stringFromColumnIndex($columnIndex) . $row);
2368	}
2369
2370	/**
2371	* Get active cell.
2372	*
2373	* @return string Example: 'A1'
2374	*/
2375	public function getActiveCell()
2376	{
2377	return $this->activeCell;
2378	}
2379
2380	/**
2381	* Get selected cells.
2382	*
2383	* @return string
2384	*/
2385	public function getSelectedCells()
2386	{
2387	return $this->selectedCells;
2388	}
2389
2390	/**
2391	* Selected cell.
2392	*
2393	* @param string $pCoordinate Cell (i.e. A1)
2394	*
2395	* @return Worksheet
2396	*/
2397	public function setSelectedCell($pCoordinate)
2398	{
2399	return $this->setSelectedCells($pCoordinate);
2400	}
2401
2402	/**
2403	* Select a range of cells.
2404	*
2405	* @param string $pCoordinate Cell range, examples: 'A1', 'B2:G5', 'A:C', '3:6'
2406	*
2407	* @return Worksheet
2408	*/
2409	public function setSelectedCells($pCoordinate)
2410	{
2411	// Uppercase coordinate
2412	$pCoordinate = strtoupper($pCoordinate);
2413
2414	// Convert 'A' to 'A:A'
2415	$pCoordinate = preg_replace('/^([A-Z]+)$/', '${1}:${1}', $pCoordinate);
2416
2417	// Convert '1' to '1:1'
2418	$pCoordinate = preg_replace('/^(\d+)$/', '${1}:${1}', $pCoordinate);
2419
2420	// Convert 'A:C' to 'A1:C1048576'
2421	$pCoordinate = preg_replace('/^([A-Z]+):([A-Z]+)$/', '${1}1:${2}1048576', $pCoordinate);
2422
2423	// Convert '1:3' to 'A1:XFD3'
2424	$pCoordinate = preg_replace('/^(\d+):(\d+)$/', 'A${1}:XFD${2}', $pCoordinate);
2425
2426	if (Coordinate::coordinateIsRange($pCoordinate)) {
2427	list($first) = Coordinate::splitRange($pCoordinate);
2428	$this->activeCell = $first[0];
2429	} else {
2430	$this->activeCell = $pCoordinate;
2431	}
2432	$this->selectedCells = $pCoordinate;
2433
2434	return $this;
2435	}
2436
2437	/**
2438	* Selected cell by using numeric cell coordinates.
2439	*
2440	* @param int $columnIndex Numeric column coordinate of the cell
2441	* @param int $row Numeric row coordinate of the cell
2442	*
2443	* @throws Exception
2444	*
2445	* @return Worksheet
2446	*/
2447	public function setSelectedCellByColumnAndRow($columnIndex, $row)
2448	{
2449	return $this->setSelectedCells(Coordinate::stringFromColumnIndex($columnIndex) . $row);
2450	}
2451
2452	/**
2453	* Get right-to-left.
2454	*
2455	* @return bool
2456	*/
2457	public function getRightToLeft()
2458	{
2459	return $this->rightToLeft;
2460	}
2461
2462	/**
2463	* Set right-to-left.
2464	*
2465	* @param bool $value Right-to-left true/false
2466	*
2467	* @return Worksheet
2468	*/
2469	public function setRightToLeft($value)
2470	{
2471	$this->rightToLeft = $value;
2472
2473	return $this;
2474	}
2475
2476	/**
2477	* Fill worksheet from values in array.
2478	*
2479	* @param array $source Source array
2480	* @param mixed $nullValue Value in source array that stands for blank cell
2481	* @param string $startCell Insert array starting from this cell address as the top left coordinate
2482	* @param bool $strictNullComparison Apply strict comparison when testing for null values in the array
2483	*
2484	* @throws Exception
2485	*
2486	* @return Worksheet
2487	*/
2488	public function fromArray(array $source, $nullValue = null, $startCell = 'A1', $strictNullComparison = false)
2489	{
2490	// Convert a 1-D array to 2-D (for ease of looping)
2491	if (!is_array(end($source))) {
2492	$source = [$source];
2493	}
2494
2495	// start coordinate
2496	list($startColumn, $startRow) = Coordinate::coordinateFromString($startCell);
2497
2498	// Loop through $source
2499	foreach ($source as $rowData) {
2500	$currentColumn = $startColumn;
2501	foreach ($rowData as $cellValue) {
2502	if ($strictNullComparison) {
2503	if ($cellValue !== $nullValue) {
2504	// Set cell value
2505	$this->getCell($currentColumn . $startRow)->setValue($cellValue);
2506	}
2507	} else {
2508	if ($cellValue != $nullValue) {
2509	// Set cell value
2510	$this->getCell($currentColumn . $startRow)->setValue($cellValue);
2511	}
2512	}
2513	++$currentColumn;
2514	}
2515	++$startRow;
2516	}
2517
2518	return $this;
2519	}
2520
2521	/**
2522	* Create array from a range of cells.
2523	*
2524	* @param string $pRange Range of cells (i.e. "A1:B10"), or just one cell (i.e. "A1")
2525	* @param mixed $nullValue Value returned in the array entry if a cell doesn't exist
2526	* @param bool $calculateFormulas Should formulas be calculated?
2527	* @param bool $formatData Should formatting be applied to cell values?
2528	* @param bool $returnCellRef False - Return a simple array of rows and columns indexed by number counting from zero
2529	* True - Return rows and columns indexed by their actual row and column IDs
2530	*
2531	* @return array
2532	*/
2533	public function rangeToArray($pRange, $nullValue = null, $calculateFormulas = true, $formatData = true, $returnCellRef = false)
2534	{
2535	// Returnvalue
2536	$returnValue = [];
2537	// Identify the range that we need to extract from the worksheet
2538	list($rangeStart, $rangeEnd) = Coordinate::rangeBoundaries($pRange);
2539	$minCol = Coordinate::stringFromColumnIndex($rangeStart[0]);
2540	$minRow = $rangeStart[1];
2541	$maxCol = Coordinate::stringFromColumnIndex($rangeEnd[0]);
2542	$maxRow = $rangeEnd[1];
2543
2544	++$maxCol;
2545	// Loop through rows
2546	$r = -1;
2547	for ($row = $minRow; $row <= $maxRow; ++$row) {
2548	$rRef = ($returnCellRef) ? $row : ++$r;
2549	$c = -1;
2550	// Loop through columns in the current row
2551	for ($col = $minCol; $col != $maxCol; ++$col) {
2552	$cRef = ($returnCellRef) ? $col : ++$c;
2553	// Using getCell() will create a new cell if it doesn't already exist. We don't want that to happen
2554	// so we test and retrieve directly against cellCollection
2555	if ($this->cellCollection->has($col . $row)) {
2556	// Cell exists
2557	$cell = $this->cellCollection->get($col . $row);
2558	if ($cell->getValue() !== null) {
2559	if ($cell->getValue() instanceof RichText) {
2560	$returnValue[$rRef][$cRef] = $cell->getValue()->getPlainText();
2561	} else {
2562	if ($calculateFormulas) {
2563	$returnValue[$rRef][$cRef] = $cell->getCalculatedValue();
2564	} else {
2565	$returnValue[$rRef][$cRef] = $cell->getValue();
2566	}
2567	}
2568
2569	if ($formatData) {
2570	$style = $this->parent->getCellXfByIndex($cell->getXfIndex());
2571	$returnValue[$rRef][$cRef] = NumberFormat::toFormattedString(
2572	$returnValue[$rRef][$cRef],
2573	($style && $style->getNumberFormat()) ? $style->getNumberFormat()->getFormatCode() : NumberFormat::FORMAT_GENERAL
2574	);
2575	}
2576	} else {
2577	// Cell holds a NULL
2578	$returnValue[$rRef][$cRef] = $nullValue;
2579	}
2580	} else {
2581	// Cell doesn't exist
2582	$returnValue[$rRef][$cRef] = $nullValue;
2583	}
2584	}
2585	}
2586
2587	// Return
2588	return $returnValue;
2589	}
2590
2591	/**
2592	* Create array from a range of cells.
2593	*
2594	* @param string $pNamedRange Name of the Named Range
2595	* @param mixed $nullValue Value returned in the array entry if a cell doesn't exist
2596	* @param bool $calculateFormulas Should formulas be calculated?
2597	* @param bool $formatData Should formatting be applied to cell values?
2598	* @param bool $returnCellRef False - Return a simple array of rows and columns indexed by number counting from zero
2599	* True - Return rows and columns indexed by their actual row and column IDs
2600	*
2601	* @throws Exception
2602	*
2603	* @return array
2604	*/
2605	public function namedRangeToArray($pNamedRange, $nullValue = null, $calculateFormulas = true, $formatData = true, $returnCellRef = false)
2606	{
2607	$namedRange = NamedRange::resolveRange($pNamedRange, $this);
2608	if ($namedRange !== null) {
2609	$pWorkSheet = $namedRange->getWorksheet();
2610	$pCellRange = $namedRange->getRange();
2611
2612	return $pWorkSheet->rangeToArray($pCellRange, $nullValue, $calculateFormulas, $formatData, $returnCellRef);
2613	}
2614
2615	throw new Exception('Named Range ' . $pNamedRange . ' does not exist.');
2616	}
2617
2618	/**
2619	* Create array from worksheet.
2620	*
2621	* @param mixed $nullValue Value returned in the array entry if a cell doesn't exist
2622	* @param bool $calculateFormulas Should formulas be calculated?
2623	* @param bool $formatData Should formatting be applied to cell values?
2624	* @param bool $returnCellRef False - Return a simple array of rows and columns indexed by number counting from zero
2625	* True - Return rows and columns indexed by their actual row and column IDs
2626	*
2627	* @return array
2628	*/
2629	public function toArray($nullValue = null, $calculateFormulas = true, $formatData = true, $returnCellRef = false)
2630	{
2631	// Garbage collect...
2632	$this->garbageCollect();
2633
2634	// Identify the range that we need to extract from the worksheet
2635	$maxCol = $this->getHighestColumn();
2636	$maxRow = $this->getHighestRow();
2637
2638	// Return
2639	return $this->rangeToArray('A1:' . $maxCol . $maxRow, $nullValue, $calculateFormulas, $formatData, $returnCellRef);
2640	}
2641
2642	/**
2643	* Get row iterator.
2644	*
2645	* @param int $startRow The row number at which to start iterating
2646	* @param int $endRow The row number at which to stop iterating
2647	*
2648	* @return RowIterator
2649	*/
2650	public function getRowIterator($startRow = 1, $endRow = null)
2651	{
2652	return new RowIterator($this, $startRow, $endRow);
2653	}
2654
2655	/**
2656	* Get column iterator.
2657	*
2658	* @param string $startColumn The column address at which to start iterating
2659	* @param string $endColumn The column address at which to stop iterating
2660	*
2661	* @return ColumnIterator
2662	*/
2663	public function getColumnIterator($startColumn = 'A', $endColumn = null)
2664	{
2665	return new ColumnIterator($this, $startColumn, $endColumn);
2666	}
2667
2668	/**
2669	* Run PhpSpreadsheet garbage collector.
2670	*
2671	* @return Worksheet
2672	*/
2673	public function garbageCollect()
2674	{
2675	// Flush cache
2676	$this->cellCollection->get('A1');
2677
2678	// Lookup highest column and highest row if cells are cleaned
2679	$colRow = $this->cellCollection->getHighestRowAndColumn();
2680	$highestRow = $colRow['row'];
2681	$highestColumn = Coordinate::columnIndexFromString($colRow['column']);
2682
2683	// Loop through column dimensions
2684	foreach ($this->columnDimensions as $dimension) {
2685	$highestColumn = max($highestColumn, Coordinate::columnIndexFromString($dimension->getColumnIndex()));
2686	}
2687
2688	// Loop through row dimensions
2689	foreach ($this->rowDimensions as $dimension) {
2690	$highestRow = max($highestRow, $dimension->getRowIndex());
2691	}
2692
2693	// Cache values
2694	if ($highestColumn < 1) {
2695	$this->cachedHighestColumn = 'A';
2696	} else {
2697	$this->cachedHighestColumn = Coordinate::stringFromColumnIndex($highestColumn);
2698	}
2699	$this->cachedHighestRow = $highestRow;
2700
2701	// Return
2702	return $this;
2703	}
2704
2705	/**
2706	* Get hash code.
2707	*
2708	* @return string Hash code
2709	*/
2710	public function getHashCode()
2711	{
2712	if ($this->dirty) {
2713	$this->hash = md5($this->title . $this->autoFilter . ($this->protection->isProtectionEnabled() ? 't' : 'f') . __CLASS__);
2714	$this->dirty = false;
2715	}
2716
2717	return $this->hash;
2718	}
2719
2720	/**
2721	* Extract worksheet title from range.
2722	*
2723	* Example: extractSheetTitle("testSheet!A1") ==> 'A1'
2724	* Example: extractSheetTitle("'testSheet 1'!A1", true) ==> ['testSheet 1', 'A1'];
2725	*
2726	* @param string $pRange Range to extract title from
2727	* @param bool $returnRange Return range? (see example)
2728	*
2729	* @return mixed
2730	*/
2731	public static function extractSheetTitle($pRange, $returnRange = false)
2732	{
2733	// Sheet title included?
2734	if (($sep = strrpos($pRange, '!')) === false) {
2735	return $returnRange ? ['', $pRange] : '';
2736	}
2737
2738	if ($returnRange) {
2739	return [substr($pRange, 0, $sep), substr($pRange, $sep + 1)];
2740	}
2741
2742	return substr($pRange, $sep + 1);
2743	}
2744
2745	/**
2746	* Get hyperlink.
2747	*
2748	* @param string $pCellCoordinate Cell coordinate to get hyperlink for, eg: 'A1'
2749	*
2750	* @return Hyperlink
2751	*/
2752	public function getHyperlink($pCellCoordinate)
2753	{
2754	// return hyperlink if we already have one
2755	if (isset($this->hyperlinkCollection[$pCellCoordinate])) {
2756	return $this->hyperlinkCollection[$pCellCoordinate];
2757	}
2758
2759	// else create hyperlink
2760	$this->hyperlinkCollection[$pCellCoordinate] = new Hyperlink();
2761
2762	return $this->hyperlinkCollection[$pCellCoordinate];
2763	}
2764
2765	/**
2766	* Set hyperlink.
2767	*
2768	* @param string $pCellCoordinate Cell coordinate to insert hyperlink, eg: 'A1'
2769	* @param null\|Hyperlink $pHyperlink
2770	*
2771	* @return Worksheet
2772	*/
2773	public function setHyperlink($pCellCoordinate, Hyperlink $pHyperlink = null)
2774	{
2775	if ($pHyperlink === null) {
2776	unset($this->hyperlinkCollection[$pCellCoordinate]);
2777	} else {
2778	$this->hyperlinkCollection[$pCellCoordinate] = $pHyperlink;
2779	}
2780
2781	return $this;
2782	}
2783
2784	/**
2785	* Hyperlink at a specific coordinate exists?
2786	*
2787	* @param string $pCoordinate eg: 'A1'
2788	*
2789	* @return bool
2790	*/
2791	public function hyperlinkExists($pCoordinate)
2792	{
2793	return isset($this->hyperlinkCollection[$pCoordinate]);
2794	}
2795
2796	/**
2797	* Get collection of hyperlinks.
2798	*
2799	* @return Hyperlink[]
2800	*/
2801	public function getHyperlinkCollection()
2802	{
2803	return $this->hyperlinkCollection;
2804	}
2805
2806	/**
2807	* Get data validation.
2808	*
2809	* @param string $pCellCoordinate Cell coordinate to get data validation for, eg: 'A1'
2810	*
2811	* @return DataValidation
2812	*/
2813	public function getDataValidation($pCellCoordinate)
2814	{
2815	// return data validation if we already have one
2816	if (isset($this->dataValidationCollection[$pCellCoordinate])) {
2817	return $this->dataValidationCollection[$pCellCoordinate];
2818	}
2819
2820	// else create data validation
2821	$this->dataValidationCollection[$pCellCoordinate] = new DataValidation();
2822
2823	return $this->dataValidationCollection[$pCellCoordinate];
2824	}
2825
2826	/**
2827	* Set data validation.
2828	*
2829	* @param string $pCellCoordinate Cell coordinate to insert data validation, eg: 'A1'
2830	* @param null\|DataValidation $pDataValidation
2831	*
2832	* @return Worksheet
2833	*/
2834	public function setDataValidation($pCellCoordinate, DataValidation $pDataValidation = null)
2835	{
2836	if ($pDataValidation === null) {
2837	unset($this->dataValidationCollection[$pCellCoordinate]);
2838	} else {
2839	$this->dataValidationCollection[$pCellCoordinate] = $pDataValidation;
2840	}
2841
2842	return $this;
2843	}
2844
2845	/**
2846	* Data validation at a specific coordinate exists?
2847	*
2848	* @param string $pCoordinate eg: 'A1'
2849	*
2850	* @return bool
2851	*/
2852	public function dataValidationExists($pCoordinate)
2853	{
2854	return isset($this->dataValidationCollection[$pCoordinate]);
2855	}
2856
2857	/**
2858	* Get collection of data validations.
2859	*
2860	* @return DataValidation[]
2861	*/
2862	public function getDataValidationCollection()
2863	{
2864	return $this->dataValidationCollection;
2865	}
2866
2867	/**
2868	* Accepts a range, returning it as a range that falls within the current highest row and column of the worksheet.
2869	*
2870	* @param string $range
2871	*
2872	* @return string Adjusted range value
2873	*/
2874	public function shrinkRangeToFit($range)
2875	{
2876	$maxCol = $this->getHighestColumn();
2877	$maxRow = $this->getHighestRow();
2878	$maxCol = Coordinate::columnIndexFromString($maxCol);
2879
2880	$rangeBlocks = explode(' ', $range);
2881	foreach ($rangeBlocks as &$rangeSet) {
2882	$rangeBoundaries = Coordinate::getRangeBoundaries($rangeSet);
2883
2884	if (Coordinate::columnIndexFromString($rangeBoundaries[0][0]) > $maxCol) {
2885	$rangeBoundaries[0][0] = Coordinate::stringFromColumnIndex($maxCol);
2886	}
2887	if ($rangeBoundaries[0][1] > $maxRow) {
2888	$rangeBoundaries[0][1] = $maxRow;
2889	}
2890	if (Coordinate::columnIndexFromString($rangeBoundaries[1][0]) > $maxCol) {
2891	$rangeBoundaries[1][0] = Coordinate::stringFromColumnIndex($maxCol);
2892	}
2893	if ($rangeBoundaries[1][1] > $maxRow) {
2894	$rangeBoundaries[1][1] = $maxRow;
2895	}
2896	$rangeSet = $rangeBoundaries[0][0] . $rangeBoundaries[0][1] . ':' . $rangeBoundaries[1][0] . $rangeBoundaries[1][1];
2897	}
2898	unset($rangeSet);
2899	$stRange = implode(' ', $rangeBlocks);
2900
2901	return $stRange;
2902	}
2903
2904	/**
2905	* Get tab color.
2906	*
2907	* @return Color
2908	*/
2909	public function getTabColor()
2910	{
2911	if ($this->tabColor === null) {
2912	$this->tabColor = new Color();
2913	}
2914
2915	return $this->tabColor;
2916	}
2917
2918	/**
2919	* Reset tab color.
2920	*
2921	* @return Worksheet
2922	*/
2923	public function resetTabColor()
2924	{
2925	$this->tabColor = null;
2926	unset($this->tabColor);
2927
2928	return $this;
2929	}
2930
2931	/**
2932	* Tab color set?
2933	*
2934	* @return bool
2935	*/
2936	public function isTabColorSet()
2937	{
2938	return $this->tabColor !== null;
2939	}
2940
2941	/**
2942	* Copy worksheet (!= clone!).
2943	*
2944	* @return Worksheet
2945	*/
2946	public function copy()
2947	{
2948	$copied = clone $this;
2949
2950	return $copied;
2951	}
2952
2953	/**
2954	* Implement PHP __clone to create a deep clone, not just a shallow copy.
2955	*/
2956	public function __clone()
2957	{
2958	foreach ($this as $key => $val) {
2959	if ($key == 'parent') {
2960	continue;
2961	}
2962
2963	if (is_object($val) \|\| (is_array($val))) {
2964	if ($key == 'cellCollection') {
2965	$newCollection = $this->cellCollection->cloneCellCollection($this);
2966	$this->cellCollection = $newCollection;
2967	} elseif ($key == 'drawingCollection') {
2968	$currentCollection = $this->drawingCollection;
2969	$this->drawingCollection = new ArrayObject();
2970	foreach ($currentCollection as $item) {
2971	if (is_object($item)) {
2972	$newDrawing = clone $item;
2973	$newDrawing->setWorksheet($this);
2974	}
2975	}
2976	} elseif (($key == 'autoFilter') && ($this->autoFilter instanceof AutoFilter)) {
2977	$newAutoFilter = clone $this->autoFilter;
2978	$this->autoFilter = $newAutoFilter;
2979	$this->autoFilter->setParent($this);
2980	} else {
2981	$this->{$key} = unserialize(serialize($val));
2982	}
2983	}
2984	}
2985	}
2986
2987	/**
2988	* Define the code name of the sheet.
2989	*
2990	* @param string $pValue Same rule as Title minus space not allowed (but, like Excel, change
2991	* silently space to underscore)
2992	* @param bool $validate False to skip validation of new title. WARNING: This should only be set
2993	* at parse time (by Readers), where titles can be assumed to be valid.
2994	*
2995	* @throws Exception
2996	*
2997	* @return Worksheet
2998	*/
2999	public function setCodeName($pValue, $validate = true)
3000	{
3001	// Is this a 'rename' or not?
3002	if ($this->getCodeName() == $pValue) {
3003	return $this;
3004	}
3005
3006	if ($validate) {
3007	$pValue = str_replace(' ', '_', $pValue); //Excel does this automatically without flinching, we are doing the same
3008
3009	// Syntax check
3010	// throw an exception if not valid
3011	self::checkSheetCodeName($pValue);
3012
3013	// We use the same code that setTitle to find a valid codeName else not using a space (Excel don't like) but a '_'
3014
3015	if ($this->getParent()) {
3016	// Is there already such sheet name?
3017	if ($this->getParent()->sheetCodeNameExists($pValue)) {
3018	// Use name, but append with lowest possible integer
3019
3020	if (Shared\StringHelper::countCharacters($pValue) > 29) {
3021	$pValue = Shared\StringHelper::substring($pValue, 0, 29);
3022	}
3023	$i = 1;
3024	while ($this->getParent()->sheetCodeNameExists($pValue . '_' . $i)) {
3025	++$i;
3026	if ($i == 10) {
3027	if (Shared\StringHelper::countCharacters($pValue) > 28) {
3028	$pValue = Shared\StringHelper::substring($pValue, 0, 28);
3029	}
3030	} elseif ($i == 100) {
3031	if (Shared\StringHelper::countCharacters($pValue) > 27) {
3032	$pValue = Shared\StringHelper::substring($pValue, 0, 27);
3033	}
3034	}
3035	}
3036
3037	$pValue = $pValue . '_' . $i; // ok, we have a valid name
3038	}
3039	}
3040	}
3041
3042	$this->codeName = $pValue;
3043
3044	return $this;
3045	}
3046
3047	/**
3048	* Return the code name of the sheet.
3049	*
3050	* @return null\|string
3051	*/
3052	public function getCodeName()
3053	{
3054	return $this->codeName;
3055	}
3056
3057	/**
3058	* Sheet has a code name ?
3059	*
3060	* @return bool
3061	*/
3062	public function hasCodeName()
3063	{
3064	return $this->codeName !== null;
3065	}
3066	}
3067