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