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