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