[Summary view]

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\Cell;
   4  
   5  use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
   6  use PhpOffice\PhpSpreadsheet\Calculation\Information\ExcelError;
   7  use PhpOffice\PhpSpreadsheet\Collection\Cells;
   8  use PhpOffice\PhpSpreadsheet\Exception;
   9  use PhpOffice\PhpSpreadsheet\RichText\RichText;
  10  use PhpOffice\PhpSpreadsheet\Shared\Date as SharedDate;
  11  use PhpOffice\PhpSpreadsheet\Shared\StringHelper;
  12  use PhpOffice\PhpSpreadsheet\Style\ConditionalFormatting\CellStyleAssessor;
  13  use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
  14  use PhpOffice\PhpSpreadsheet\Style\Style;
  15  use PhpOffice\PhpSpreadsheet\Worksheet\Table;
  16  use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
  17  
  18  class Cell
  19  {
  20      /**
  21       * Value binder to use.
  22       *
  23       * @var IValueBinder
  24       */
  25      private static $valueBinder;
  26  
  27      /**
  28       * Value of the cell.
  29       *
  30       * @var mixed
  31       */
  32      private $value;
  33  
  34      /**
  35       *    Calculated value of the cell (used for caching)
  36       *    This returns the value last calculated by MS Excel or whichever spreadsheet program was used to
  37       *        create the original spreadsheet file.
  38       *    Note that this value is not guaranteed to reflect the actual calculated value because it is
  39       *        possible that auto-calculation was disabled in the original spreadsheet, and underlying data
  40       *        values used by the formula have changed since it was last calculated.
  41       *
  42       * @var mixed
  43       */
  44      private $calculatedValue;
  45  
  46      /**
  47       * Type of the cell data.
  48       *
  49       * @var string
  50       */
  51      private $dataType;
  52  
  53      /**
  54       * The collection of cells that this cell belongs to (i.e. The Cell Collection for the parent Worksheet).
  55       *
  56       * @var ?Cells
  57       */
  58      private $parent;
  59  
  60      /**
  61       * Index to the cellXf reference for the styling of this cell.
  62       *
  63       * @var int
  64       */
  65      private $xfIndex = 0;
  66  
  67      /**
  68       * Attributes of the formula.
  69       *
  70       * @var mixed
  71       */
  72      private $formulaAttributes;
  73  
  74      /**
  75       * Update the cell into the cell collection.
  76       *
  77       * @return $this
  78       */
  79      public function updateInCollection(): self
  80      {
  81          $parent = $this->parent;
  82          if ($parent === null) {
  83              throw new Exception('Cannot update when cell is not bound to a worksheet');
  84          }
  85          $parent->update($this);
  86  
  87          return $this;
  88      }
  89  
  90      public function detach(): void
  91      {
  92          $this->parent = null;
  93      }
  94  
  95      public function attach(Cells $parent): void
  96      {
  97          $this->parent = $parent;
  98      }
  99  
 100      /**
 101       * Create a new Cell.
 102       *
 103       * @param mixed $value
 104       */
 105      public function __construct($value, ?string $dataType, Worksheet $worksheet)
 106      {
 107          // Initialise cell value
 108          $this->value = $value;
 109  
 110          // Set worksheet cache
 111          $this->parent = $worksheet->getCellCollection();
 112  
 113          // Set datatype?
 114          if ($dataType !== null) {
 115              if ($dataType == DataType::TYPE_STRING2) {
 116                  $dataType = DataType::TYPE_STRING;
 117              }
 118              $this->dataType = $dataType;
 119          } elseif (self::getValueBinder()->bindValue($this, $value) === false) {
 120              throw new Exception('Value could not be bound to cell.');
 121          }
 122      }
 123  
 124      /**
 125       * Get cell coordinate column.
 126       *
 127       * @return string
 128       */
 129      public function getColumn()
 130      {
 131          $parent = $this->parent;
 132          if ($parent === null) {
 133              throw new Exception('Cannot get column when cell is not bound to a worksheet');
 134          }
 135  
 136          return $parent->getCurrentColumn();
 137      }
 138  
 139      /**
 140       * Get cell coordinate row.
 141       *
 142       * @return int
 143       */
 144      public function getRow()
 145      {
 146          $parent = $this->parent;
 147          if ($parent === null) {
 148              throw new Exception('Cannot get row when cell is not bound to a worksheet');
 149          }
 150  
 151          return $parent->getCurrentRow();
 152      }
 153  
 154      /**
 155       * Get cell coordinate.
 156       *
 157       * @return string
 158       */
 159      public function getCoordinate()
 160      {
 161          $parent = $this->parent;
 162          if ($parent !== null) {
 163              $coordinate = $parent->getCurrentCoordinate();
 164          } else {
 165              $coordinate = null;
 166          }
 167          if ($coordinate === null) {
 168              throw new Exception('Coordinate no longer exists');
 169          }
 170  
 171          return $coordinate;
 172      }
 173  
 174      /**
 175       * Get cell value.
 176       *
 177       * @return mixed
 178       */
 179      public function getValue()
 180      {
 181          return $this->value;
 182      }
 183  
 184      /**
 185       * Get cell value with formatting.
 186       */
 187      public function getFormattedValue(): string
 188      {
 189          return (string) NumberFormat::toFormattedString(
 190              $this->getCalculatedValue(),
 191              (string) $this->getStyle()->getNumberFormat()->getFormatCode()
 192          );
 193      }
 194  
 195      /**
 196       * @param mixed $oldValue
 197       * @param mixed $newValue
 198       */
 199      protected static function updateIfCellIsTableHeader(?Worksheet $workSheet, self $cell, $oldValue, $newValue): void
 200      {
 201          if (StringHelper::strToLower($oldValue ?? '') === StringHelper::strToLower($newValue ?? '') || $workSheet === null) {
 202              return;
 203          }
 204  
 205          foreach ($workSheet->getTableCollection() as $table) {
 206              /** @var Table $table */
 207              if ($cell->isInRange($table->getRange())) {
 208                  $rangeRowsColumns = Coordinate::getRangeBoundaries($table->getRange());
 209                  if ($cell->getRow() === (int) $rangeRowsColumns[0][1]) {
 210                      Table\Column::updateStructuredReferences($workSheet, $oldValue, $newValue);
 211                  }
 212  
 213                  return;
 214              }
 215          }
 216      }
 217  
 218      /**
 219       * Set cell value.
 220       *
 221       *    Sets the value for a cell, automatically determining the datatype using the value binder
 222       *
 223       * @param mixed $value Value
 224       * @param null|IValueBinder $binder Value Binder to override the currently set Value Binder
 225       *
 226       * @throws Exception
 227       *
 228       * @return $this
 229       */
 230      public function setValue($value, ?IValueBinder $binder = null): self
 231      {
 232          $binder ??= self::getValueBinder();
 233          if (!$binder->bindValue($this, $value)) {
 234              throw new Exception('Value could not be bound to cell.');
 235          }
 236  
 237          return $this;
 238      }
 239  
 240      /**
 241       * Set the value for a cell, with the explicit data type passed to the method (bypassing any use of the value binder).
 242       *
 243       * @param mixed $value Value
 244       * @param string $dataType Explicit data type, see DataType::TYPE_*
 245       *        Note that PhpSpreadsheet does not validate that the value and datatype are consistent, in using this
 246       *             method, then it is your responsibility as an end-user developer to validate that the value and
 247       *             the datatype match.
 248       *       If you do mismatch value and datatype, then the value you enter may be changed to match the datatype
 249       *          that you specify.
 250       *
 251       * @return Cell
 252       */
 253      public function setValueExplicit($value, string $dataType = DataType::TYPE_STRING)
 254      {
 255          $oldValue = $this->value;
 256  
 257          // set the value according to data type
 258          switch ($dataType) {
 259              case DataType::TYPE_NULL:
 260                  $this->value = null;
 261  
 262                  break;
 263              case DataType::TYPE_STRING2:
 264                  $dataType = DataType::TYPE_STRING;
 265                  // no break
 266              case DataType::TYPE_STRING:
 267                  // Synonym for string
 268              case DataType::TYPE_INLINE:
 269                  // Rich text
 270                  $this->value = DataType::checkString($value);
 271  
 272                  break;
 273              case DataType::TYPE_NUMERIC:
 274                  if (is_string($value) && !is_numeric($value)) {
 275                      throw new Exception('Invalid numeric value for datatype Numeric');
 276                  }
 277                  $this->value = 0 + $value;
 278  
 279                  break;
 280              case DataType::TYPE_FORMULA:
 281                  $this->value = (string) $value;
 282  
 283                  break;
 284              case DataType::TYPE_BOOL:
 285                  $this->value = (bool) $value;
 286  
 287                  break;
 288              case DataType::TYPE_ISO_DATE:
 289                  $this->value = SharedDate::convertIsoDate($value);
 290                  $dataType = DataType::TYPE_NUMERIC;
 291  
 292                  break;
 293              case DataType::TYPE_ERROR:
 294                  $this->value = DataType::checkErrorCode($value);
 295  
 296                  break;
 297              default:
 298                  throw new Exception('Invalid datatype: ' . $dataType);
 299          }
 300  
 301          // set the datatype
 302          $this->dataType = $dataType;
 303  
 304          $this->updateInCollection();
 305          $cellCoordinate = $this->getCoordinate();
 306          self::updateIfCellIsTableHeader($this->getParent()->getParent(), $this, $oldValue, $value); // @phpstan-ignore-line
 307  
 308          return $this->getParent()->get($cellCoordinate); // @phpstan-ignore-line
 309      }
 310  
 311      public const CALCULATE_DATE_TIME_ASIS = 0;
 312      public const CALCULATE_DATE_TIME_FLOAT = 1;
 313      public const CALCULATE_TIME_FLOAT = 2;
 314  
 315      /** @var int */
 316      private static $calculateDateTimeType = self::CALCULATE_DATE_TIME_ASIS;
 317  
 318      public static function getCalculateDateTimeType(): int
 319      {
 320          return self::$calculateDateTimeType;
 321      }
 322  
 323      public static function setCalculateDateTimeType(int $calculateDateTimeType): void
 324      {
 325          switch ($calculateDateTimeType) {
 326              case self::CALCULATE_DATE_TIME_ASIS:
 327              case self::CALCULATE_DATE_TIME_FLOAT:
 328              case self::CALCULATE_TIME_FLOAT:
 329                  self::$calculateDateTimeType = $calculateDateTimeType;
 330  
 331                  break;
 332              default:
 333                  throw new \PhpOffice\PhpSpreadsheet\Calculation\Exception("Invalid value $calculateDateTimeType for calculated date time type");
 334          }
 335      }
 336  
 337      /**
 338       * Convert date, time, or datetime from int to float if desired.
 339       *
 340       * @param mixed $result
 341       *
 342       * @return mixed
 343       */
 344      private function convertDateTimeInt($result)
 345      {
 346          if (is_int($result)) {
 347              if (self::$calculateDateTimeType === self::CALCULATE_TIME_FLOAT) {
 348                  if (SharedDate::isDateTime($this, $result, false)) {
 349                      $result = (float) $result;
 350                  }
 351              } elseif (self::$calculateDateTimeType === self::CALCULATE_DATE_TIME_FLOAT) {
 352                  if (SharedDate::isDateTime($this, $result, true)) {
 353                      $result = (float) $result;
 354                  }
 355              }
 356          }
 357  
 358          return $result;
 359      }
 360  
 361      /**
 362       * Get calculated cell value.
 363       *
 364       * @param bool $resetLog Whether the calculation engine logger should be reset or not
 365       *
 366       * @return mixed
 367       */
 368      public function getCalculatedValue(bool $resetLog = true)
 369      {
 370          if ($this->dataType === DataType::TYPE_FORMULA) {
 371              try {
 372                  $index = $this->getWorksheet()->getParentOrThrow()->getActiveSheetIndex();
 373                  $selected = $this->getWorksheet()->getSelectedCells();
 374                  $result = Calculation::getInstance(
 375                      $this->getWorksheet()->getParent()
 376                  )->calculateCellValue($this, $resetLog);
 377                  $result = $this->convertDateTimeInt($result);
 378                  $this->getWorksheet()->setSelectedCells($selected);
 379                  $this->getWorksheet()->getParentOrThrow()->setActiveSheetIndex($index);
 380                  //    We don't yet handle array returns
 381                  if (is_array($result)) {
 382                      while (is_array($result)) {
 383                          $result = array_shift($result);
 384                      }
 385                  }
 386              } catch (Exception $ex) {
 387                  if (($ex->getMessage() === 'Unable to access External Workbook') && ($this->calculatedValue !== null)) {
 388                      return $this->calculatedValue; // Fallback for calculations referencing external files.
 389                  } elseif (preg_match('/[Uu]ndefined (name|offset: 2|array key 2)/', $ex->getMessage()) === 1) {
 390                      return ExcelError::NAME();
 391                  }
 392  
 393                  throw new \PhpOffice\PhpSpreadsheet\Calculation\Exception(
 394                      $this->getWorksheet()->getTitle() . '!' . $this->getCoordinate() . ' -> ' . $ex->getMessage()
 395                  );
 396              }
 397  
 398              if ($result === '#Not Yet Implemented') {
 399                  return $this->calculatedValue; // Fallback if calculation engine does not support the formula.
 400              }
 401  
 402              return $result;
 403          } elseif ($this->value instanceof RichText) {
 404              return $this->value->getPlainText();
 405          }
 406  
 407          return $this->convertDateTimeInt($this->value);
 408      }
 409  
 410      /**
 411       * Set old calculated value (cached).
 412       *
 413       * @param mixed $originalValue Value
 414       */
 415      public function setCalculatedValue($originalValue): self
 416      {
 417          if ($originalValue !== null) {
 418              $this->calculatedValue = (is_numeric($originalValue)) ? (float) $originalValue : $originalValue;
 419          }
 420  
 421          return $this->updateInCollection();
 422      }
 423  
 424      /**
 425       *    Get old calculated value (cached)
 426       *    This returns the value last calculated by MS Excel or whichever spreadsheet program was used to
 427       *        create the original spreadsheet file.
 428       *    Note that this value is not guaranteed to reflect the actual calculated value because it is
 429       *        possible that auto-calculation was disabled in the original spreadsheet, and underlying data
 430       *        values used by the formula have changed since it was last calculated.
 431       *
 432       * @return mixed
 433       */
 434      public function getOldCalculatedValue()
 435      {
 436          return $this->calculatedValue;
 437      }
 438  
 439      /**
 440       * Get cell data type.
 441       */
 442      public function getDataType(): string
 443      {
 444          return $this->dataType;
 445      }
 446  
 447      /**
 448       * Set cell data type.
 449       *
 450       * @param string $dataType see DataType::TYPE_*
 451       */
 452      public function setDataType($dataType): self
 453      {
 454          if ($dataType == DataType::TYPE_STRING2) {
 455              $dataType = DataType::TYPE_STRING;
 456          }
 457          $this->dataType = $dataType;
 458  
 459          return $this->updateInCollection();
 460      }
 461  
 462      /**
 463       * Identify if the cell contains a formula.
 464       */
 465      public function isFormula(): bool
 466      {
 467          return $this->dataType === DataType::TYPE_FORMULA && $this->getStyle()->getQuotePrefix() === false;
 468      }
 469  
 470      /**
 471       *    Does this cell contain Data validation rules?
 472       */
 473      public function hasDataValidation(): bool
 474      {
 475          if (!isset($this->parent)) {
 476              throw new Exception('Cannot check for data validation when cell is not bound to a worksheet');
 477          }
 478  
 479          return $this->getWorksheet()->dataValidationExists($this->getCoordinate());
 480      }
 481  
 482      /**
 483       * Get Data validation rules.
 484       */
 485      public function getDataValidation(): DataValidation
 486      {
 487          if (!isset($this->parent)) {
 488              throw new Exception('Cannot get data validation for cell that is not bound to a worksheet');
 489          }
 490  
 491          return $this->getWorksheet()->getDataValidation($this->getCoordinate());
 492      }
 493  
 494      /**
 495       * Set Data validation rules.
 496       */
 497      public function setDataValidation(?DataValidation $dataValidation = null): self
 498      {
 499          if (!isset($this->parent)) {
 500              throw new Exception('Cannot set data validation for cell that is not bound to a worksheet');
 501          }
 502  
 503          $this->getWorksheet()->setDataValidation($this->getCoordinate(), $dataValidation);
 504  
 505          return $this->updateInCollection();
 506      }
 507  
 508      /**
 509       * Does this cell contain valid value?
 510       */
 511      public function hasValidValue(): bool
 512      {
 513          $validator = new DataValidator();
 514  
 515          return $validator->isValid($this);
 516      }
 517  
 518      /**
 519       * Does this cell contain a Hyperlink?
 520       */
 521      public function hasHyperlink(): bool
 522      {
 523          if (!isset($this->parent)) {
 524              throw new Exception('Cannot check for hyperlink when cell is not bound to a worksheet');
 525          }
 526  
 527          return $this->getWorksheet()->hyperlinkExists($this->getCoordinate());
 528      }
 529  
 530      /**
 531       * Get Hyperlink.
 532       */
 533      public function getHyperlink(): Hyperlink
 534      {
 535          if (!isset($this->parent)) {
 536              throw new Exception('Cannot get hyperlink for cell that is not bound to a worksheet');
 537          }
 538  
 539          return $this->getWorksheet()->getHyperlink($this->getCoordinate());
 540      }
 541  
 542      /**
 543       * Set Hyperlink.
 544       */
 545      public function setHyperlink(?Hyperlink $hyperlink = null): self
 546      {
 547          if (!isset($this->parent)) {
 548              throw new Exception('Cannot set hyperlink for cell that is not bound to a worksheet');
 549          }
 550  
 551          $this->getWorksheet()->setHyperlink($this->getCoordinate(), $hyperlink);
 552  
 553          return $this->updateInCollection();
 554      }
 555  
 556      /**
 557       * Get cell collection.
 558       *
 559       * @return ?Cells
 560       */
 561      public function getParent()
 562      {
 563          return $this->parent;
 564      }
 565  
 566      /**
 567       * Get parent worksheet.
 568       */
 569      public function getWorksheet(): Worksheet
 570      {
 571          $parent = $this->parent;
 572          if ($parent !== null) {
 573              $worksheet = $parent->getParent();
 574          } else {
 575              $worksheet = null;
 576          }
 577  
 578          if ($worksheet === null) {
 579              throw new Exception('Worksheet no longer exists');
 580          }
 581  
 582          return $worksheet;
 583      }
 584  
 585      public function getWorksheetOrNull(): ?Worksheet
 586      {
 587          $parent = $this->parent;
 588          if ($parent !== null) {
 589              $worksheet = $parent->getParent();
 590          } else {
 591              $worksheet = null;
 592          }
 593  
 594          return $worksheet;
 595      }
 596  
 597      /**
 598       * Is this cell in a merge range.
 599       */
 600      public function isInMergeRange(): bool
 601      {
 602          return (bool) $this->getMergeRange();
 603      }
 604  
 605      /**
 606       * Is this cell the master (top left cell) in a merge range (that holds the actual data value).
 607       */
 608      public function isMergeRangeValueCell(): bool
 609      {
 610          if ($mergeRange = $this->getMergeRange()) {
 611              $mergeRange = Coordinate::splitRange($mergeRange);
 612              [$startCell] = $mergeRange[0];
 613  
 614              return $this->getCoordinate() === $startCell;
 615          }
 616  
 617          return false;
 618      }
 619  
 620      /**
 621       * If this cell is in a merge range, then return the range.
 622       *
 623       * @return false|string
 624       */
 625      public function getMergeRange()
 626      {
 627          foreach ($this->getWorksheet()->getMergeCells() as $mergeRange) {
 628              if ($this->isInRange($mergeRange)) {
 629                  return $mergeRange;
 630              }
 631          }
 632  
 633          return false;
 634      }
 635  
 636      /**
 637       * Get cell style.
 638       */
 639      public function getStyle(): Style
 640      {
 641          return $this->getWorksheet()->getStyle($this->getCoordinate());
 642      }
 643  
 644      /**
 645       * Get cell style.
 646       */
 647      public function getAppliedStyle(): Style
 648      {
 649          if ($this->getWorksheet()->conditionalStylesExists($this->getCoordinate()) === false) {
 650              return $this->getStyle();
 651          }
 652          $range = $this->getWorksheet()->getConditionalRange($this->getCoordinate());
 653          if ($range === null) {
 654              return $this->getStyle();
 655          }
 656  
 657          $matcher = new CellStyleAssessor($this, $range);
 658  
 659          return $matcher->matchConditions($this->getWorksheet()->getConditionalStyles($this->getCoordinate()));
 660      }
 661  
 662      /**
 663       * Re-bind parent.
 664       */
 665      public function rebindParent(Worksheet $parent): self
 666      {
 667          $this->parent = $parent->getCellCollection();
 668  
 669          return $this->updateInCollection();
 670      }
 671  
 672      /**
 673       *    Is cell in a specific range?
 674       *
 675       * @param string $range Cell range (e.g. A1:A1)
 676       */
 677      public function isInRange(string $range): bool
 678      {
 679          [$rangeStart, $rangeEnd] = Coordinate::rangeBoundaries($range);
 680  
 681          // Translate properties
 682          $myColumn = Coordinate::columnIndexFromString($this->getColumn());
 683          $myRow = $this->getRow();
 684  
 685          // Verify if cell is in range
 686          return ($rangeStart[0] <= $myColumn) && ($rangeEnd[0] >= $myColumn) &&
 687                  ($rangeStart[1] <= $myRow) && ($rangeEnd[1] >= $myRow);
 688      }
 689  
 690      /**
 691       * Compare 2 cells.
 692       *
 693       * @param Cell $a Cell a
 694       * @param Cell $b Cell b
 695       *
 696       * @return int Result of comparison (always -1 or 1, never zero!)
 697       */
 698      public static function compareCells(self $a, self $b): int
 699      {
 700          if ($a->getRow() < $b->getRow()) {
 701              return -1;
 702          } elseif ($a->getRow() > $b->getRow()) {
 703              return 1;
 704          } elseif (Coordinate::columnIndexFromString($a->getColumn()) < Coordinate::columnIndexFromString($b->getColumn())) {
 705              return -1;
 706          }
 707  
 708          return 1;
 709      }
 710  
 711      /**
 712       * Get value binder to use.
 713       */
 714      public static function getValueBinder(): IValueBinder
 715      {
 716          if (self::$valueBinder === null) {
 717              self::$valueBinder = new DefaultValueBinder();
 718          }
 719  
 720          return self::$valueBinder;
 721      }
 722  
 723      /**
 724       * Set value binder to use.
 725       */
 726      public static function setValueBinder(IValueBinder $binder): void
 727      {
 728          self::$valueBinder = $binder;
 729      }
 730  
 731      /**
 732       * Implement PHP __clone to create a deep clone, not just a shallow copy.
 733       */
 734      public function __clone()
 735      {
 736          $vars = get_object_vars($this);
 737          foreach ($vars as $propertyName => $propertyValue) {
 738              if ((is_object($propertyValue)) && ($propertyName !== 'parent')) {
 739                  $this->$propertyName = clone $propertyValue;
 740              } else {
 741                  $this->$propertyName = $propertyValue;
 742              }
 743          }
 744      }
 745  
 746      /**
 747       * Get index to cellXf.
 748       */
 749      public function getXfIndex(): int
 750      {
 751          return $this->xfIndex;
 752      }
 753  
 754      /**
 755       * Set index to cellXf.
 756       */
 757      public function setXfIndex(int $indexValue): self
 758      {
 759          $this->xfIndex = $indexValue;
 760  
 761          return $this->updateInCollection();
 762      }
 763  
 764      /**
 765       * Set the formula attributes.
 766       *
 767       * @param mixed $attributes
 768       *
 769       * @return $this
 770       */
 771      public function setFormulaAttributes($attributes): self
 772      {
 773          $this->formulaAttributes = $attributes;
 774  
 775          return $this;
 776      }
 777  
 778      /**
 779       * Get the formula attributes.
 780       *
 781       * @return mixed
 782       */
 783      public function getFormulaAttributes()
 784      {
 785          return $this->formulaAttributes;
 786      }
 787  
 788      /**
 789       * Convert to string.
 790       *
 791       * @return string
 792       */
 793      public function __toString()
 794      {
 795          return (string) $this->getValue();
 796      }
 797  }