Search moodle.org's
Developer Documentation
Developer Documentation
- Bug fixes for general core bugs in 3.10.x will end 8 November 2021 (12 months).
- Bug fixes for security issues in 3.10.x will end 9 May 2022 (18 months).
- PHP version: minimum PHP 7.2.0 Note: minimum PHP version has increased since Moodle 3.8. PHP 7.3.x and 7.4.x are supported too.
Differences Between: [Versions 310 and 311] [Versions 310 and 400] [Versions 310 and 401] [Versions 310 and 402] [Versions 310 and 403]
1 <?php 2 3 namespace PhpOffice\PhpSpreadsheet; 4 5 use PhpOffice\PhpSpreadsheet\Calculation\Calculation; 6 use PhpOffice\PhpSpreadsheet\Style\Style; 7 use PhpOffice\PhpSpreadsheet\Worksheet\Iterator; 8 use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet; 9 10 class Spreadsheet 11 { 12 // Allowable values for workbook window visilbity 13 const VISIBILITY_VISIBLE = 'visible'; 14 const VISIBILITY_HIDDEN = 'hidden'; 15 const VISIBILITY_VERY_HIDDEN = 'veryHidden'; 16 17 private static $workbookViewVisibilityValues = [ 18 self::VISIBILITY_VISIBLE, 19 self::VISIBILITY_HIDDEN, 20 self::VISIBILITY_VERY_HIDDEN, 21 ]; 22 23 /** 24 * Unique ID. 25 * 26 * @var string 27 */ 28 private $uniqueID; 29 30 /** 31 * Document properties. 32 * 33 * @var Document\Properties 34 */ 35 private $properties; 36 37 /** 38 * Document security. 39 * 40 * @var Document\Security 41 */ 42 private $security; 43 44 /** 45 * Collection of Worksheet objects. 46 * 47 * @var Worksheet[] 48 */ 49 private $workSheetCollection = []; 50 51 /** 52 * Calculation Engine. 53 * 54 * @var Calculation 55 */ 56 private $calculationEngine; 57 58 /** 59 * Active sheet index. 60 * 61 * @var int 62 */ 63 private $activeSheetIndex = 0; 64 65 /** 66 * Named ranges. 67 * 68 * @var NamedRange[] 69 */ 70 private $namedRanges = []; 71 72 /** 73 * CellXf supervisor. 74 * 75 * @var Style 76 */ 77 private $cellXfSupervisor; 78 79 /** 80 * CellXf collection. 81 * 82 * @var Style[] 83 */ 84 private $cellXfCollection = []; 85 86 /** 87 * CellStyleXf collection. 88 * 89 * @var Style[] 90 */ 91 private $cellStyleXfCollection = []; 92 93 /** 94 * hasMacros : this workbook have macros ? 95 * 96 * @var bool 97 */ 98 private $hasMacros = false; 99 100 /** 101 * macrosCode : all macros code as binary data (the vbaProject.bin file, this include form, code, etc.), null if no macro. 102 * 103 * @var string 104 */ 105 private $macrosCode; 106 107 /** 108 * macrosCertificate : if macros are signed, contains binary data vbaProjectSignature.bin file, null if not signed. 109 * 110 * @var string 111 */ 112 private $macrosCertificate; 113 114 /** 115 * ribbonXMLData : null if workbook is'nt Excel 2007 or not contain a customized UI. 116 * 117 * @var null|string 118 */ 119 private $ribbonXMLData; 120 121 /** 122 * ribbonBinObjects : null if workbook is'nt Excel 2007 or not contain embedded objects (picture(s)) for Ribbon Elements 123 * ignored if $ribbonXMLData is null. 124 * 125 * @var null|array 126 */ 127 private $ribbonBinObjects; 128 129 /** 130 * List of unparsed loaded data for export to same format with better compatibility. 131 * It has to be minimized when the library start to support currently unparsed data. 132 * 133 * @var array 134 */ 135 private $unparsedLoadedData = []; 136 137 /** 138 * Controls visibility of the horizonal scroll bar in the application. 139 * 140 * @var bool 141 */ 142 private $showHorizontalScroll = true; 143 144 /** 145 * Controls visibility of the horizonal scroll bar in the application. 146 * 147 * @var bool 148 */ 149 private $showVerticalScroll = true; 150 151 /** 152 * Controls visibility of the sheet tabs in the application. 153 * 154 * @var bool 155 */ 156 private $showSheetTabs = true; 157 158 /** 159 * Specifies a boolean value that indicates whether the workbook window 160 * is minimized. 161 * 162 * @var bool 163 */ 164 private $minimized = false; 165 166 /** 167 * Specifies a boolean value that indicates whether to group dates 168 * when presenting the user with filtering optiomd in the user 169 * interface. 170 * 171 * @var bool 172 */ 173 private $autoFilterDateGrouping = true; 174 175 /** 176 * Specifies the index to the first sheet in the book view. 177 * 178 * @var int 179 */ 180 private $firstSheetIndex = 0; 181 182 /** 183 * Specifies the visible status of the workbook. 184 * 185 * @var string 186 */ 187 private $visibility = self::VISIBILITY_VISIBLE; 188 189 /** 190 * Specifies the ratio between the workbook tabs bar and the horizontal 191 * scroll bar. TabRatio is assumed to be out of 1000 of the horizontal 192 * window width. 193 * 194 * @var int 195 */ 196 private $tabRatio = 600; 197 198 /** 199 * The workbook has macros ? 200 * 201 * @return bool 202 */ 203 public function hasMacros() 204 { 205 return $this->hasMacros; 206 } 207 208 /** 209 * Define if a workbook has macros. 210 * 211 * @param bool $hasMacros true|false 212 */ 213 public function setHasMacros($hasMacros) 214 { 215 $this->hasMacros = (bool) $hasMacros; 216 } 217 218 /** 219 * Set the macros code. 220 * 221 * @param string $macroCode string|null 222 */ 223 public function setMacrosCode($macroCode) 224 { 225 $this->macrosCode = $macroCode; 226 $this->setHasMacros($macroCode !== null); 227 } 228 229 /** 230 * Return the macros code. 231 * 232 * @return null|string 233 */ 234 public function getMacrosCode() 235 { 236 return $this->macrosCode; 237 } 238 239 /** 240 * Set the macros certificate. 241 * 242 * @param null|string $certificate 243 */ 244 public function setMacrosCertificate($certificate) 245 { 246 $this->macrosCertificate = $certificate; 247 } 248 249 /** 250 * Is the project signed ? 251 * 252 * @return bool true|false 253 */ 254 public function hasMacrosCertificate() 255 { 256 return $this->macrosCertificate !== null; 257 } 258 259 /** 260 * Return the macros certificate. 261 * 262 * @return null|string 263 */ 264 public function getMacrosCertificate() 265 { 266 return $this->macrosCertificate; 267 } 268 269 /** 270 * Remove all macros, certificate from spreadsheet. 271 */ 272 public function discardMacros() 273 { 274 $this->hasMacros = false; 275 $this->macrosCode = null; 276 $this->macrosCertificate = null; 277 } 278 279 /** 280 * set ribbon XML data. 281 * 282 * @param null|mixed $target 283 * @param null|mixed $xmlData 284 */ 285 public function setRibbonXMLData($target, $xmlData) 286 { 287 if ($target !== null && $xmlData !== null) { 288 $this->ribbonXMLData = ['target' => $target, 'data' => $xmlData]; 289 } else { 290 $this->ribbonXMLData = null; 291 } 292 } 293 294 /** 295 * retrieve ribbon XML Data. 296 * 297 * return string|null|array 298 * 299 * @param string $what 300 * 301 * @return string 302 */ 303 public function getRibbonXMLData($what = 'all') //we need some constants here... 304 { 305 $returnData = null; 306 $what = strtolower($what); 307 switch ($what) { 308 case 'all': 309 $returnData = $this->ribbonXMLData; 310 311 break; 312 case 'target': 313 case 'data': 314 if (is_array($this->ribbonXMLData) && isset($this->ribbonXMLData[$what])) { 315 $returnData = $this->ribbonXMLData[$what]; 316 } 317 318 break; 319 } 320 321 return $returnData; 322 } 323 324 /** 325 * store binaries ribbon objects (pictures). 326 * 327 * @param null|mixed $BinObjectsNames 328 * @param null|mixed $BinObjectsData 329 */ 330 public function setRibbonBinObjects($BinObjectsNames, $BinObjectsData) 331 { 332 if ($BinObjectsNames !== null && $BinObjectsData !== null) { 333 $this->ribbonBinObjects = ['names' => $BinObjectsNames, 'data' => $BinObjectsData]; 334 } else { 335 $this->ribbonBinObjects = null; 336 } 337 } 338 339 /** 340 * List of unparsed loaded data for export to same format with better compatibility. 341 * It has to be minimized when the library start to support currently unparsed data. 342 * 343 * @internal 344 * 345 * @return array 346 */ 347 public function getUnparsedLoadedData() 348 { 349 return $this->unparsedLoadedData; 350 } 351 352 /** 353 * List of unparsed loaded data for export to same format with better compatibility. 354 * It has to be minimized when the library start to support currently unparsed data. 355 * 356 * @internal 357 * 358 * @param array $unparsedLoadedData 359 */ 360 public function setUnparsedLoadedData(array $unparsedLoadedData) 361 { 362 $this->unparsedLoadedData = $unparsedLoadedData; 363 } 364 365 /** 366 * return the extension of a filename. Internal use for a array_map callback (php<5.3 don't like lambda function). 367 * 368 * @param mixed $path 369 * 370 * @return string 371 */ 372 private function getExtensionOnly($path) 373 { 374 return pathinfo($path, PATHINFO_EXTENSION); 375 } 376 377 /** 378 * retrieve Binaries Ribbon Objects. 379 * 380 * @param string $what 381 * 382 * @return null|array 383 */ 384 public function getRibbonBinObjects($what = 'all') 385 { 386 $ReturnData = null; 387 $what = strtolower($what); 388 switch ($what) { 389 case 'all': 390 return $this->ribbonBinObjects; 391 392 break; 393 case 'names': 394 case 'data': 395 if (is_array($this->ribbonBinObjects) && isset($this->ribbonBinObjects[$what])) { 396 $ReturnData = $this->ribbonBinObjects[$what]; 397 } 398 399 break; 400 case 'types': 401 if (is_array($this->ribbonBinObjects) && 402 isset($this->ribbonBinObjects['data']) && is_array($this->ribbonBinObjects['data'])) { 403 $tmpTypes = array_keys($this->ribbonBinObjects['data']); 404 $ReturnData = array_unique(array_map([$this, 'getExtensionOnly'], $tmpTypes)); 405 } else { 406 $ReturnData = []; // the caller want an array... not null if empty 407 } 408 409 break; 410 } 411 412 return $ReturnData; 413 } 414 415 /** 416 * This workbook have a custom UI ? 417 * 418 * @return bool 419 */ 420 public function hasRibbon() 421 { 422 return $this->ribbonXMLData !== null; 423 } 424 425 /** 426 * This workbook have additionnal object for the ribbon ? 427 * 428 * @return bool 429 */ 430 public function hasRibbonBinObjects() 431 { 432 return $this->ribbonBinObjects !== null; 433 } 434 435 /** 436 * Check if a sheet with a specified code name already exists. 437 * 438 * @param string $pSheetCodeName Name of the worksheet to check 439 * 440 * @return bool 441 */ 442 public function sheetCodeNameExists($pSheetCodeName) 443 { 444 return $this->getSheetByCodeName($pSheetCodeName) !== null; 445 } 446 447 /** 448 * Get sheet by code name. Warning : sheet don't have always a code name ! 449 * 450 * @param string $pName Sheet name 451 * 452 * @return Worksheet 453 */ 454 public function getSheetByCodeName($pName) 455 { 456 $worksheetCount = count($this->workSheetCollection); 457 for ($i = 0; $i < $worksheetCount; ++$i) { 458 if ($this->workSheetCollection[$i]->getCodeName() == $pName) { 459 return $this->workSheetCollection[$i]; 460 } 461 } 462 463 return null; 464 } 465 466 /** 467 * Create a new PhpSpreadsheet with one Worksheet. 468 */ 469 public function __construct() 470 { 471 $this->uniqueID = uniqid('', true); 472 $this->calculationEngine = new Calculation($this); 473 474 // Initialise worksheet collection and add one worksheet 475 $this->workSheetCollection = []; 476 $this->workSheetCollection[] = new Worksheet($this); 477 $this->activeSheetIndex = 0; 478 479 // Create document properties 480 $this->properties = new Document\Properties(); 481 482 // Create document security 483 $this->security = new Document\Security(); 484 485 // Set named ranges 486 $this->namedRanges = []; 487 488 // Create the cellXf supervisor 489 $this->cellXfSupervisor = new Style(true); 490 $this->cellXfSupervisor->bindParent($this); 491 492 // Create the default style 493 $this->addCellXf(new Style()); 494 $this->addCellStyleXf(new Style()); 495 } 496 497 /** 498 * Code to execute when this worksheet is unset(). 499 */ 500 public function __destruct() 501 { 502 $this->calculationEngine = null; 503 $this->disconnectWorksheets(); 504 } 505 506 /** 507 * Disconnect all worksheets from this PhpSpreadsheet workbook object, 508 * typically so that the PhpSpreadsheet object can be unset. 509 */ 510 public function disconnectWorksheets() 511 { 512 $worksheet = null; 513 foreach ($this->workSheetCollection as $k => &$worksheet) { 514 $worksheet->disconnectCells(); 515 $this->workSheetCollection[$k] = null; 516 } 517 unset($worksheet); 518 $this->workSheetCollection = []; 519 } 520 521 /** 522 * Return the calculation engine for this worksheet. 523 * 524 * @return Calculation 525 */ 526 public function getCalculationEngine() 527 { 528 return $this->calculationEngine; 529 } 530 531 /** 532 * Get properties. 533 * 534 * @return Document\Properties 535 */ 536 public function getProperties() 537 { 538 return $this->properties; 539 } 540 541 /** 542 * Set properties. 543 * 544 * @param Document\Properties $pValue 545 */ 546 public function setProperties(Document\Properties $pValue) 547 { 548 $this->properties = $pValue; 549 } 550 551 /** 552 * Get security. 553 * 554 * @return Document\Security 555 */ 556 public function getSecurity() 557 { 558 return $this->security; 559 } 560 561 /** 562 * Set security. 563 * 564 * @param Document\Security $pValue 565 */ 566 public function setSecurity(Document\Security $pValue) 567 { 568 $this->security = $pValue; 569 } 570 571 /** 572 * Get active sheet. 573 * 574 * @throws Exception 575 * 576 * @return Worksheet 577 */ 578 public function getActiveSheet() 579 { 580 return $this->getSheet($this->activeSheetIndex); 581 } 582 583 /** 584 * Create sheet and add it to this workbook. 585 * 586 * @param null|int $sheetIndex Index where sheet should go (0,1,..., or null for last) 587 * 588 * @throws Exception 589 * 590 * @return Worksheet 591 */ 592 public function createSheet($sheetIndex = null) 593 { 594 $newSheet = new Worksheet($this); 595 $this->addSheet($newSheet, $sheetIndex); 596 597 return $newSheet; 598 } 599 600 /** 601 * Check if a sheet with a specified name already exists. 602 * 603 * @param string $pSheetName Name of the worksheet to check 604 * 605 * @return bool 606 */ 607 public function sheetNameExists($pSheetName) 608 { 609 return $this->getSheetByName($pSheetName) !== null; 610 } 611 612 /** 613 * Add sheet. 614 * 615 * @param Worksheet $pSheet 616 * @param null|int $iSheetIndex Index where sheet should go (0,1,..., or null for last) 617 * 618 * @throws Exception 619 * 620 * @return Worksheet 621 */ 622 public function addSheet(Worksheet $pSheet, $iSheetIndex = null) 623 { 624 if ($this->sheetNameExists($pSheet->getTitle())) { 625 throw new Exception( 626 "Workbook already contains a worksheet named '{$pSheet->getTitle()}'. Rename this worksheet first." 627 ); 628 } 629 630 if ($iSheetIndex === null) { 631 if ($this->activeSheetIndex < 0) { 632 $this->activeSheetIndex = 0; 633 } 634 $this->workSheetCollection[] = $pSheet; 635 } else { 636 // Insert the sheet at the requested index 637 array_splice( 638 $this->workSheetCollection, 639 $iSheetIndex, 640 0, 641 [$pSheet] 642 ); 643 644 // Adjust active sheet index if necessary 645 if ($this->activeSheetIndex >= $iSheetIndex) { 646 ++$this->activeSheetIndex; 647 } 648 } 649 650 if ($pSheet->getParent() === null) { 651 $pSheet->rebindParent($this); 652 } 653 654 return $pSheet; 655 } 656 657 /** 658 * Remove sheet by index. 659 * 660 * @param int $pIndex Active sheet index 661 * 662 * @throws Exception 663 */ 664 public function removeSheetByIndex($pIndex) 665 { 666 $numSheets = count($this->workSheetCollection); 667 if ($pIndex > $numSheets - 1) { 668 throw new Exception( 669 "You tried to remove a sheet by the out of bounds index: {$pIndex}. The actual number of sheets is {$numSheets}." 670 ); 671 } 672 array_splice($this->workSheetCollection, $pIndex, 1); 673 674 // Adjust active sheet index if necessary 675 if (($this->activeSheetIndex >= $pIndex) && 676 ($pIndex > count($this->workSheetCollection) - 1)) { 677 --$this->activeSheetIndex; 678 } 679 } 680 681 /** 682 * Get sheet by index. 683 * 684 * @param int $pIndex Sheet index 685 * 686 * @throws Exception 687 * 688 * @return Worksheet 689 */ 690 public function getSheet($pIndex) 691 { 692 if (!isset($this->workSheetCollection[$pIndex])) { 693 $numSheets = $this->getSheetCount(); 694 695 throw new Exception( 696 "Your requested sheet index: {$pIndex} is out of bounds. The actual number of sheets is {$numSheets}." 697 ); 698 } 699 700 return $this->workSheetCollection[$pIndex]; 701 } 702 703 /** 704 * Get all sheets. 705 * 706 * @return Worksheet[] 707 */ 708 public function getAllSheets() 709 { 710 return $this->workSheetCollection; 711 } 712 713 /** 714 * Get sheet by name. 715 * 716 * @param string $pName Sheet name 717 * 718 * @return null|Worksheet 719 */ 720 public function getSheetByName($pName) 721 { 722 $worksheetCount = count($this->workSheetCollection); 723 for ($i = 0; $i < $worksheetCount; ++$i) { 724 if ($this->workSheetCollection[$i]->getTitle() === trim($pName, "'")) { 725 return $this->workSheetCollection[$i]; 726 } 727 } 728 729 return null; 730 } 731 732 /** 733 * Get index for sheet. 734 * 735 * @param Worksheet $pSheet 736 * 737 * @throws Exception 738 * 739 * @return int index 740 */ 741 public function getIndex(Worksheet $pSheet) 742 { 743 foreach ($this->workSheetCollection as $key => $value) { 744 if ($value->getHashCode() == $pSheet->getHashCode()) { 745 return $key; 746 } 747 } 748 749 throw new Exception('Sheet does not exist.'); 750 } 751 752 /** 753 * Set index for sheet by sheet name. 754 * 755 * @param string $sheetName Sheet name to modify index for 756 * @param int $newIndex New index for the sheet 757 * 758 * @throws Exception 759 * 760 * @return int New sheet index 761 */ 762 public function setIndexByName($sheetName, $newIndex) 763 { 764 $oldIndex = $this->getIndex($this->getSheetByName($sheetName)); 765 $pSheet = array_splice( 766 $this->workSheetCollection, 767 $oldIndex, 768 1 769 ); 770 array_splice( 771 $this->workSheetCollection, 772 $newIndex, 773 0, 774 $pSheet 775 ); 776 777 return $newIndex; 778 } 779 780 /** 781 * Get sheet count. 782 * 783 * @return int 784 */ 785 public function getSheetCount() 786 { 787 return count($this->workSheetCollection); 788 } 789 790 /** 791 * Get active sheet index. 792 * 793 * @return int Active sheet index 794 */ 795 public function getActiveSheetIndex() 796 { 797 return $this->activeSheetIndex; 798 } 799 800 /** 801 * Set active sheet index. 802 * 803 * @param int $pIndex Active sheet index 804 * 805 * @throws Exception 806 * 807 * @return Worksheet 808 */ 809 public function setActiveSheetIndex($pIndex) 810 { 811 $numSheets = count($this->workSheetCollection); 812 813 if ($pIndex > $numSheets - 1) { 814 throw new Exception( 815 "You tried to set a sheet active by the out of bounds index: {$pIndex}. The actual number of sheets is {$numSheets}." 816 ); 817 } 818 $this->activeSheetIndex = $pIndex; 819 820 return $this->getActiveSheet(); 821 } 822 823 /** 824 * Set active sheet index by name. 825 * 826 * @param string $pValue Sheet title 827 * 828 * @throws Exception 829 * 830 * @return Worksheet 831 */ 832 public function setActiveSheetIndexByName($pValue) 833 { 834 if (($worksheet = $this->getSheetByName($pValue)) instanceof Worksheet) { 835 $this->setActiveSheetIndex($this->getIndex($worksheet)); 836 837 return $worksheet; 838 } 839 840 throw new Exception('Workbook does not contain sheet:' . $pValue); 841 } 842 843 /** 844 * Get sheet names. 845 * 846 * @return string[] 847 */ 848 public function getSheetNames() 849 { 850 $returnValue = []; 851 $worksheetCount = $this->getSheetCount(); 852 for ($i = 0; $i < $worksheetCount; ++$i) { 853 $returnValue[] = $this->getSheet($i)->getTitle(); 854 } 855 856 return $returnValue; 857 } 858 859 /** 860 * Add external sheet. 861 * 862 * @param Worksheet $pSheet External sheet to add 863 * @param null|int $iSheetIndex Index where sheet should go (0,1,..., or null for last) 864 * 865 * @throws Exception 866 * 867 * @return Worksheet 868 */ 869 public function addExternalSheet(Worksheet $pSheet, $iSheetIndex = null) 870 { 871 if ($this->sheetNameExists($pSheet->getTitle())) { 872 throw new Exception("Workbook already contains a worksheet named '{$pSheet->getTitle()}'. Rename the external sheet first."); 873 } 874 875 // count how many cellXfs there are in this workbook currently, we will need this below 876 $countCellXfs = count($this->cellXfCollection); 877 878 // copy all the shared cellXfs from the external workbook and append them to the current 879 foreach ($pSheet->getParent()->getCellXfCollection() as $cellXf) { 880 $this->addCellXf(clone $cellXf); 881 } 882 883 // move sheet to this workbook 884 $pSheet->rebindParent($this); 885 886 // update the cellXfs 887 foreach ($pSheet->getCoordinates(false) as $coordinate) { 888 $cell = $pSheet->getCell($coordinate); 889 $cell->setXfIndex($cell->getXfIndex() + $countCellXfs); 890 } 891 892 return $this->addSheet($pSheet, $iSheetIndex); 893 } 894 895 /** 896 * Get named ranges. 897 * 898 * @return NamedRange[] 899 */ 900 public function getNamedRanges() 901 { 902 return $this->namedRanges; 903 } 904 905 /** 906 * Add named range. 907 * 908 * @param NamedRange $namedRange 909 * 910 * @return bool 911 */ 912 public function addNamedRange(NamedRange $namedRange) 913 { 914 if ($namedRange->getScope() == null) { 915 // global scope 916 $this->namedRanges[$namedRange->getName()] = $namedRange; 917 } else { 918 // local scope 919 $this->namedRanges[$namedRange->getScope()->getTitle() . '!' . $namedRange->getName()] = $namedRange; 920 } 921 922 return true; 923 } 924 925 /** 926 * Get named range. 927 * 928 * @param string $namedRange 929 * @param null|Worksheet $pSheet Scope. Use null for global scope 930 * 931 * @return null|NamedRange 932 */ 933 public function getNamedRange($namedRange, Worksheet $pSheet = null) 934 { 935 $returnValue = null; 936 937 if ($namedRange != '' && ($namedRange !== null)) { 938 // first look for global defined name 939 if (isset($this->namedRanges[$namedRange])) { 940 $returnValue = $this->namedRanges[$namedRange]; 941 } 942 943 // then look for local defined name (has priority over global defined name if both names exist) 944 if (($pSheet !== null) && isset($this->namedRanges[$pSheet->getTitle() . '!' . $namedRange])) { 945 $returnValue = $this->namedRanges[$pSheet->getTitle() . '!' . $namedRange]; 946 } 947 } 948 949 return $returnValue; 950 } 951 952 /** 953 * Remove named range. 954 * 955 * @param string $namedRange 956 * @param null|Worksheet $pSheet scope: use null for global scope 957 * 958 * @return Spreadsheet 959 */ 960 public function removeNamedRange($namedRange, Worksheet $pSheet = null) 961 { 962 if ($pSheet === null) { 963 if (isset($this->namedRanges[$namedRange])) { 964 unset($this->namedRanges[$namedRange]); 965 } 966 } else { 967 if (isset($this->namedRanges[$pSheet->getTitle() . '!' . $namedRange])) { 968 unset($this->namedRanges[$pSheet->getTitle() . '!' . $namedRange]); 969 } 970 } 971 972 return $this; 973 } 974 975 /** 976 * Get worksheet iterator. 977 * 978 * @return Iterator 979 */ 980 public function getWorksheetIterator() 981 { 982 return new Iterator($this); 983 } 984 985 /** 986 * Copy workbook (!= clone!). 987 * 988 * @return Spreadsheet 989 */ 990 public function copy() 991 { 992 $copied = clone $this; 993 994 $worksheetCount = count($this->workSheetCollection); 995 for ($i = 0; $i < $worksheetCount; ++$i) { 996 $this->workSheetCollection[$i] = $this->workSheetCollection[$i]->copy(); 997 $this->workSheetCollection[$i]->rebindParent($this); 998 } 999 1000 return $copied; 1001 } 1002 1003 /** 1004 * Implement PHP __clone to create a deep clone, not just a shallow copy. 1005 */ 1006 public function __clone() 1007 { 1008 foreach ($this as $key => $val) { 1009 if (is_object($val) || (is_array($val))) { 1010 $this->{$key} = unserialize(serialize($val)); 1011 } 1012 } 1013 } 1014 1015 /** 1016 * Get the workbook collection of cellXfs. 1017 * 1018 * @return Style[] 1019 */ 1020 public function getCellXfCollection() 1021 { 1022 return $this->cellXfCollection; 1023 } 1024 1025 /** 1026 * Get cellXf by index. 1027 * 1028 * @param int $pIndex 1029 * 1030 * @return Style 1031 */ 1032 public function getCellXfByIndex($pIndex) 1033 { 1034 return $this->cellXfCollection[$pIndex]; 1035 } 1036 1037 /** 1038 * Get cellXf by hash code. 1039 * 1040 * @param string $pValue 1041 * 1042 * @return false|Style 1043 */ 1044 public function getCellXfByHashCode($pValue) 1045 { 1046 foreach ($this->cellXfCollection as $cellXf) { 1047 if ($cellXf->getHashCode() == $pValue) { 1048 return $cellXf; 1049 } 1050 } 1051 1052 return false; 1053 } 1054 1055 /** 1056 * Check if style exists in style collection. 1057 * 1058 * @param Style $pCellStyle 1059 * 1060 * @return bool 1061 */ 1062 public function cellXfExists($pCellStyle) 1063 { 1064 return in_array($pCellStyle, $this->cellXfCollection, true); 1065 } 1066 1067 /** 1068 * Get default style. 1069 * 1070 * @throws Exception 1071 * 1072 * @return Style 1073 */ 1074 public function getDefaultStyle() 1075 { 1076 if (isset($this->cellXfCollection[0])) { 1077 return $this->cellXfCollection[0]; 1078 } 1079 1080 throw new Exception('No default style found for this workbook'); 1081 } 1082 1083 /** 1084 * Add a cellXf to the workbook. 1085 * 1086 * @param Style $style 1087 */ 1088 public function addCellXf(Style $style) 1089 { 1090 $this->cellXfCollection[] = $style; 1091 $style->setIndex(count($this->cellXfCollection) - 1); 1092 } 1093 1094 /** 1095 * Remove cellXf by index. It is ensured that all cells get their xf index updated. 1096 * 1097 * @param int $pIndex Index to cellXf 1098 * 1099 * @throws Exception 1100 */ 1101 public function removeCellXfByIndex($pIndex) 1102 { 1103 if ($pIndex > count($this->cellXfCollection) - 1) { 1104 throw new Exception('CellXf index is out of bounds.'); 1105 } 1106 1107 // first remove the cellXf 1108 array_splice($this->cellXfCollection, $pIndex, 1); 1109 1110 // then update cellXf indexes for cells 1111 foreach ($this->workSheetCollection as $worksheet) { 1112 foreach ($worksheet->getCoordinates(false) as $coordinate) { 1113 $cell = $worksheet->getCell($coordinate); 1114 $xfIndex = $cell->getXfIndex(); 1115 if ($xfIndex > $pIndex) { 1116 // decrease xf index by 1 1117 $cell->setXfIndex($xfIndex - 1); 1118 } elseif ($xfIndex == $pIndex) { 1119 // set to default xf index 0 1120 $cell->setXfIndex(0); 1121 } 1122 } 1123 } 1124 } 1125 1126 /** 1127 * Get the cellXf supervisor. 1128 * 1129 * @return Style 1130 */ 1131 public function getCellXfSupervisor() 1132 { 1133 return $this->cellXfSupervisor; 1134 } 1135 1136 /** 1137 * Get the workbook collection of cellStyleXfs. 1138 * 1139 * @return Style[] 1140 */ 1141 public function getCellStyleXfCollection() 1142 { 1143 return $this->cellStyleXfCollection; 1144 } 1145 1146 /** 1147 * Get cellStyleXf by index. 1148 * 1149 * @param int $pIndex Index to cellXf 1150 * 1151 * @return Style 1152 */ 1153 public function getCellStyleXfByIndex($pIndex) 1154 { 1155 return $this->cellStyleXfCollection[$pIndex]; 1156 } 1157 1158 /** 1159 * Get cellStyleXf by hash code. 1160 * 1161 * @param string $pValue 1162 * 1163 * @return false|Style 1164 */ 1165 public function getCellStyleXfByHashCode($pValue) 1166 { 1167 foreach ($this->cellStyleXfCollection as $cellStyleXf) { 1168 if ($cellStyleXf->getHashCode() == $pValue) { 1169 return $cellStyleXf; 1170 } 1171 } 1172 1173 return false; 1174 } 1175 1176 /** 1177 * Add a cellStyleXf to the workbook. 1178 * 1179 * @param Style $pStyle 1180 */ 1181 public function addCellStyleXf(Style $pStyle) 1182 { 1183 $this->cellStyleXfCollection[] = $pStyle; 1184 $pStyle->setIndex(count($this->cellStyleXfCollection) - 1); 1185 } 1186 1187 /** 1188 * Remove cellStyleXf by index. 1189 * 1190 * @param int $pIndex Index to cellXf 1191 * 1192 * @throws Exception 1193 */ 1194 public function removeCellStyleXfByIndex($pIndex) 1195 { 1196 if ($pIndex > count($this->cellStyleXfCollection) - 1) { 1197 throw new Exception('CellStyleXf index is out of bounds.'); 1198 } 1199 array_splice($this->cellStyleXfCollection, $pIndex, 1); 1200 } 1201 1202 /** 1203 * Eliminate all unneeded cellXf and afterwards update the xfIndex for all cells 1204 * and columns in the workbook. 1205 */ 1206 public function garbageCollect() 1207 { 1208 // how many references are there to each cellXf ? 1209 $countReferencesCellXf = []; 1210 foreach ($this->cellXfCollection as $index => $cellXf) { 1211 $countReferencesCellXf[$index] = 0; 1212 } 1213 1214 foreach ($this->getWorksheetIterator() as $sheet) { 1215 // from cells 1216 foreach ($sheet->getCoordinates(false) as $coordinate) { 1217 $cell = $sheet->getCell($coordinate); 1218 ++$countReferencesCellXf[$cell->getXfIndex()]; 1219 } 1220 1221 // from row dimensions 1222 foreach ($sheet->getRowDimensions() as $rowDimension) { 1223 if ($rowDimension->getXfIndex() !== null) { 1224 ++$countReferencesCellXf[$rowDimension->getXfIndex()]; 1225 } 1226 } 1227 1228 // from column dimensions 1229 foreach ($sheet->getColumnDimensions() as $columnDimension) { 1230 ++$countReferencesCellXf[$columnDimension->getXfIndex()]; 1231 } 1232 } 1233 1234 // remove cellXfs without references and create mapping so we can update xfIndex 1235 // for all cells and columns 1236 $countNeededCellXfs = 0; 1237 foreach ($this->cellXfCollection as $index => $cellXf) { 1238 if ($countReferencesCellXf[$index] > 0 || $index == 0) { // we must never remove the first cellXf 1239 ++$countNeededCellXfs; 1240 } else { 1241 unset($this->cellXfCollection[$index]); 1242 } 1243 $map[$index] = $countNeededCellXfs - 1; 1244 } 1245 $this->cellXfCollection = array_values($this->cellXfCollection); 1246 1247 // update the index for all cellXfs 1248 foreach ($this->cellXfCollection as $i => $cellXf) { 1249 $cellXf->setIndex($i); 1250 } 1251 1252 // make sure there is always at least one cellXf (there should be) 1253 if (empty($this->cellXfCollection)) { 1254 $this->cellXfCollection[] = new Style(); 1255 } 1256 1257 // update the xfIndex for all cells, row dimensions, column dimensions 1258 foreach ($this->getWorksheetIterator() as $sheet) { 1259 // for all cells 1260 foreach ($sheet->getCoordinates(false) as $coordinate) { 1261 $cell = $sheet->getCell($coordinate); 1262 $cell->setXfIndex($map[$cell->getXfIndex()]); 1263 } 1264 1265 // for all row dimensions 1266 foreach ($sheet->getRowDimensions() as $rowDimension) { 1267 if ($rowDimension->getXfIndex() !== null) { 1268 $rowDimension->setXfIndex($map[$rowDimension->getXfIndex()]); 1269 } 1270 } 1271 1272 // for all column dimensions 1273 foreach ($sheet->getColumnDimensions() as $columnDimension) { 1274 $columnDimension->setXfIndex($map[$columnDimension->getXfIndex()]); 1275 } 1276 1277 // also do garbage collection for all the sheets 1278 $sheet->garbageCollect(); 1279 } 1280 } 1281 1282 /** 1283 * Return the unique ID value assigned to this spreadsheet workbook. 1284 * 1285 * @return string 1286 */ 1287 public function getID() 1288 { 1289 return $this->uniqueID; 1290 } 1291 1292 /** 1293 * Get the visibility of the horizonal scroll bar in the application. 1294 * 1295 * @return bool True if horizonal scroll bar is visible 1296 */ 1297 public function getShowHorizontalScroll() 1298 { 1299 return $this->showHorizontalScroll; 1300 } 1301 1302 /** 1303 * Set the visibility of the horizonal scroll bar in the application. 1304 * 1305 * @param bool $showHorizontalScroll True if horizonal scroll bar is visible 1306 */ 1307 public function setShowHorizontalScroll($showHorizontalScroll) 1308 { 1309 $this->showHorizontalScroll = (bool) $showHorizontalScroll; 1310 } 1311 1312 /** 1313 * Get the visibility of the vertical scroll bar in the application. 1314 * 1315 * @return bool True if vertical scroll bar is visible 1316 */ 1317 public function getShowVerticalScroll() 1318 { 1319 return $this->showVerticalScroll; 1320 } 1321 1322 /** 1323 * Set the visibility of the vertical scroll bar in the application. 1324 * 1325 * @param bool $showVerticalScroll True if vertical scroll bar is visible 1326 */ 1327 public function setShowVerticalScroll($showVerticalScroll) 1328 { 1329 $this->showVerticalScroll = (bool) $showVerticalScroll; 1330 } 1331 1332 /** 1333 * Get the visibility of the sheet tabs in the application. 1334 * 1335 * @return bool True if the sheet tabs are visible 1336 */ 1337 public function getShowSheetTabs() 1338 { 1339 return $this->showSheetTabs; 1340 } 1341 1342 /** 1343 * Set the visibility of the sheet tabs in the application. 1344 * 1345 * @param bool $showSheetTabs True if sheet tabs are visible 1346 */ 1347 public function setShowSheetTabs($showSheetTabs) 1348 { 1349 $this->showSheetTabs = (bool) $showSheetTabs; 1350 } 1351 1352 /** 1353 * Return whether the workbook window is minimized. 1354 * 1355 * @return bool true if workbook window is minimized 1356 */ 1357 public function getMinimized() 1358 { 1359 return $this->minimized; 1360 } 1361 1362 /** 1363 * Set whether the workbook window is minimized. 1364 * 1365 * @param bool $minimized true if workbook window is minimized 1366 */ 1367 public function setMinimized($minimized) 1368 { 1369 $this->minimized = (bool) $minimized; 1370 } 1371 1372 /** 1373 * Return whether to group dates when presenting the user with 1374 * filtering optiomd in the user interface. 1375 * 1376 * @return bool true if workbook window is minimized 1377 */ 1378 public function getAutoFilterDateGrouping() 1379 { 1380 return $this->autoFilterDateGrouping; 1381 } 1382 1383 /** 1384 * Set whether to group dates when presenting the user with 1385 * filtering optiomd in the user interface. 1386 * 1387 * @param bool $autoFilterDateGrouping true if workbook window is minimized 1388 */ 1389 public function setAutoFilterDateGrouping($autoFilterDateGrouping) 1390 { 1391 $this->autoFilterDateGrouping = (bool) $autoFilterDateGrouping; 1392 } 1393 1394 /** 1395 * Return the first sheet in the book view. 1396 * 1397 * @return int First sheet in book view 1398 */ 1399 public function getFirstSheetIndex() 1400 { 1401 return $this->firstSheetIndex; 1402 } 1403 1404 /** 1405 * Set the first sheet in the book view. 1406 * 1407 * @param int $firstSheetIndex First sheet in book view 1408 * 1409 * @throws Exception if the given value is invalid 1410 */ 1411 public function setFirstSheetIndex($firstSheetIndex) 1412 { 1413 if ($firstSheetIndex >= 0) { 1414 $this->firstSheetIndex = (int) $firstSheetIndex; 1415 } else { 1416 throw new Exception('First sheet index must be a positive integer.'); 1417 } 1418 } 1419 1420 /** 1421 * Return the visibility status of the workbook. 1422 * 1423 * This may be one of the following three values: 1424 * - visibile 1425 * 1426 * @return string Visible status 1427 */ 1428 public function getVisibility() 1429 { 1430 return $this->visibility; 1431 } 1432 1433 /** 1434 * Set the visibility status of the workbook. 1435 * 1436 * Valid values are: 1437 * - 'visible' (self::VISIBILITY_VISIBLE): 1438 * Workbook window is visible 1439 * - 'hidden' (self::VISIBILITY_HIDDEN): 1440 * Workbook window is hidden, but can be shown by the user 1441 * via the user interface 1442 * - 'veryHidden' (self::VISIBILITY_VERY_HIDDEN): 1443 * Workbook window is hidden and cannot be shown in the 1444 * user interface. 1445 * 1446 * @param string $visibility visibility status of the workbook 1447 * 1448 * @throws Exception if the given value is invalid 1449 */ 1450 public function setVisibility($visibility) 1451 { 1452 if ($visibility === null) { 1453 $visibility = self::VISIBILITY_VISIBLE; 1454 } 1455 1456 if (in_array($visibility, self::$workbookViewVisibilityValues)) { 1457 $this->visibility = $visibility; 1458 } else { 1459 throw new Exception('Invalid visibility value.'); 1460 } 1461 } 1462 1463 /** 1464 * Get the ratio between the workbook tabs bar and the horizontal scroll bar. 1465 * TabRatio is assumed to be out of 1000 of the horizontal window width. 1466 * 1467 * @return int Ratio between the workbook tabs bar and the horizontal scroll bar 1468 */ 1469 public function getTabRatio() 1470 { 1471 return $this->tabRatio; 1472 } 1473 1474 /** 1475 * Set the ratio between the workbook tabs bar and the horizontal scroll bar 1476 * TabRatio is assumed to be out of 1000 of the horizontal window width. 1477 * 1478 * @param int $tabRatio Ratio between the tabs bar and the horizontal scroll bar 1479 * 1480 * @throws Exception if the given value is invalid 1481 */ 1482 public function setTabRatio($tabRatio) 1483 { 1484 if ($tabRatio >= 0 || $tabRatio <= 1000) { 1485 $this->tabRatio = (int) $tabRatio; 1486 } else { 1487 throw new Exception('Tab ratio must be between 0 and 1000.'); 1488 } 1489 } 1490 }
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
