[Summary view]

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

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