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