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