[Summary view]

Differences Between: [Versions 310 and 400] [Versions 311 and 400] [Versions 39 and 400] [Versions 400 and 401] [Versions 400 and 402] [Versions 400 and 403]

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