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