[Summary view]

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