[Summary view]

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

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