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