[Summary view] [Print] [Text view]

   1  <?php
   2  /**
   3   *    PHPExcel
   4   *
   5   *    Copyright (c) 2006 - 2014 PHPExcel
   6   *
   7   *    This library is free software; you can redistribute it and/or
   8   *    modify it under the terms of the GNU Lesser General Public
   9   *    License as published by the Free Software Foundation; either
  10   *    version 2.1 of the License, or (at your option) any later version.
  11   *
  12   *    This library is distributed in the hope that it will be useful,
  13   *    but WITHOUT ANY WARRANTY; without even the implied warranty of
  14   *    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
  15   *    Lesser General Public License for more details.
  16   *
  17   *    You should have received a copy of the GNU Lesser General Public
  18   *    License along with this library; if not, write to the Free Software
  19   *    Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
  20   *
  21   *    @category    PHPExcel
  22   *    @package    PHPExcel_Cell
  23   *    @copyright    Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)
  24   *    @license    http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt    LGPL
  25   *    @version    ##VERSION##, ##DATE##
  26   */
  27  
  28  
  29  /**
  30   *    PHPExcel_Cell
  31   *
  32   *    @category   PHPExcel
  33   *    @package    PHPExcel_Cell
  34   *    @copyright  Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)
  35   */
  36  class PHPExcel_Cell
  37  {
  38  
  39      /**
  40       *  Default range variable constant
  41       *
  42       *  @var  string
  43       */
  44      const DEFAULT_RANGE = 'A1:A1';
  45  
  46      /**
  47       *    Value binder to use
  48       *
  49       *    @var    PHPExcel_Cell_IValueBinder
  50       */
  51      private static $_valueBinder = NULL;
  52  
  53      /**
  54       *    Value of the cell
  55       *
  56       *    @var    mixed
  57       */
  58      private $_value;
  59  
  60      /**
  61       *    Calculated value of the cell (used for caching)
  62       *    This returns the value last calculated by MS Excel or whichever spreadsheet program was used to
  63       *        create the original spreadsheet file.
  64       *    Note that this value is not guaranteed to reflect the actual calculated value because it is
  65       *        possible that auto-calculation was disabled in the original spreadsheet, and underlying data
  66       *        values used by the formula have changed since it was last calculated.
  67       *
  68       *    @var mixed
  69       */
  70      private $_calculatedValue = NULL;
  71  
  72      /**
  73       *    Type of the cell data
  74       *
  75       *    @var    string
  76       */
  77      private $_dataType;
  78  
  79      /**
  80       *    Parent worksheet
  81       *
  82       *    @var    PHPExcel_CachedObjectStorage_CacheBase
  83       */
  84      private $_parent;
  85  
  86      /**
  87       *    Index to cellXf
  88       *
  89       *    @var    int
  90       */
  91      private $_xfIndex;
  92  
  93      /**
  94       *    Attributes of the formula
  95       *
  96       */
  97      private $_formulaAttributes;
  98  
  99  
 100      /**
 101       *    Send notification to the cache controller
 102       *
 103       *    @return void
 104       **/
 105  	public function notifyCacheController() {
 106          $this->_parent->updateCacheData($this);
 107  
 108          return $this;
 109      }
 110  
 111  	public function detach() {
 112          $this->_parent = NULL;
 113      }
 114  
 115  	public function attach(PHPExcel_CachedObjectStorage_CacheBase $parent) {
 116  
 117  
 118          $this->_parent = $parent;
 119      }
 120  
 121  
 122      /**
 123       *    Create a new Cell
 124       *
 125       *    @param    mixed                $pValue
 126       *    @param    string                $pDataType
 127       *    @param    PHPExcel_Worksheet    $pSheet
 128       *    @throws    PHPExcel_Exception
 129       */
 130  	public function __construct($pValue = NULL, $pDataType = NULL, PHPExcel_Worksheet $pSheet = NULL)
 131      {
 132          // Initialise cell value
 133          $this->_value = $pValue;
 134  
 135          // Set worksheet cache
 136          $this->_parent = $pSheet->getCellCacheController();
 137  
 138          // Set datatype?
 139          if ($pDataType !== NULL) {
 140              if ($pDataType == PHPExcel_Cell_DataType::TYPE_STRING2)
 141                  $pDataType = PHPExcel_Cell_DataType::TYPE_STRING;
 142              $this->_dataType = $pDataType;
 143          } else {
 144              if (!self::getValueBinder()->bindValue($this, $pValue)) {
 145                  throw new PHPExcel_Exception("Value could not be bound to cell.");
 146              }
 147          }
 148  
 149          // set default index to cellXf
 150          $this->_xfIndex = 0;
 151      }
 152  
 153      /**
 154       *    Get cell coordinate column
 155       *
 156       *    @return    string
 157       */
 158  	public function getColumn()
 159      {
 160          return $this->_parent->getCurrentColumn();
 161      }
 162  
 163      /**
 164       *    Get cell coordinate row
 165       *
 166       *    @return    int
 167       */
 168  	public function getRow()
 169      {
 170          return $this->_parent->getCurrentRow();
 171      }
 172  
 173      /**
 174       *    Get cell coordinate
 175       *
 176       *    @return    string
 177       */
 178  	public function getCoordinate()
 179      {
 180          return $this->_parent->getCurrentAddress();
 181      }
 182  
 183      /**
 184       *    Get cell value
 185       *
 186       *    @return    mixed
 187       */
 188  	public function getValue()
 189      {
 190          return $this->_value;
 191      }
 192  
 193      /**
 194       *    Get cell value with formatting
 195       *
 196       *    @return    string
 197       */
 198  	public function getFormattedValue()
 199      {
 200          return (string) PHPExcel_Style_NumberFormat::toFormattedString(
 201                  $this->getCalculatedValue(),
 202                  $this->getWorksheet()->getParent()->getCellXfByIndex($this->getXfIndex())
 203                      ->getNumberFormat()->getFormatCode()
 204              );
 205      }
 206  
 207      /**
 208       *    Set cell value
 209       *
 210       *    Sets the value for a cell, automatically determining the datatype using the value binder
 211       *
 212       *    @param    mixed    $pValue                    Value
 213       *    @return    PHPExcel_Cell
 214       *    @throws    PHPExcel_Exception
 215       */
 216  	public function setValue($pValue = NULL)
 217      {
 218          if (!self::getValueBinder()->bindValue($this, $pValue)) {
 219              throw new PHPExcel_Exception("Value could not be bound to cell.");
 220          }
 221          return $this;
 222      }
 223  
 224      /**
 225       *    Set the value for a cell, with the explicit data type passed to the method (bypassing any use of the value binder)
 226       *
 227       *    @param    mixed    $pValue            Value
 228       *    @param    string    $pDataType        Explicit data type
 229       *    @return    PHPExcel_Cell
 230       *    @throws    PHPExcel_Exception
 231       */
 232  	public function setValueExplicit($pValue = NULL, $pDataType = PHPExcel_Cell_DataType::TYPE_STRING)
 233      {
 234          // set the value according to data type
 235          switch ($pDataType) {
 236              case PHPExcel_Cell_DataType::TYPE_NULL:
 237                  $this->_value = $pValue;
 238                  break;
 239              case PHPExcel_Cell_DataType::TYPE_STRING2:
 240                  $pDataType = PHPExcel_Cell_DataType::TYPE_STRING;
 241              case PHPExcel_Cell_DataType::TYPE_STRING:
 242              case PHPExcel_Cell_DataType::TYPE_INLINE:
 243                  $this->_value = PHPExcel_Cell_DataType::checkString($pValue);
 244                  break;
 245              case PHPExcel_Cell_DataType::TYPE_NUMERIC:
 246                  $this->_value = (float)$pValue;
 247                  break;
 248              case PHPExcel_Cell_DataType::TYPE_FORMULA:
 249                  $this->_value = (string)$pValue;
 250                  break;
 251              case PHPExcel_Cell_DataType::TYPE_BOOL:
 252                  $this->_value = (bool)$pValue;
 253                  break;
 254              case PHPExcel_Cell_DataType::TYPE_ERROR:
 255                  $this->_value = PHPExcel_Cell_DataType::checkErrorCode($pValue);
 256                  break;
 257              default:
 258                  throw new PHPExcel_Exception('Invalid datatype: ' . $pDataType);
 259                  break;
 260          }
 261  
 262          // set the datatype
 263          $this->_dataType = $pDataType;
 264  
 265          return $this->notifyCacheController();
 266      }
 267  
 268      /**
 269       *    Get calculated cell value
 270       *
 271       *    @deprecated        Since version 1.7.8 for planned changes to cell for array formula handling
 272       *
 273       *    @param    boolean $resetLog  Whether the calculation engine logger should be reset or not
 274       *    @return    mixed
 275       *    @throws    PHPExcel_Exception
 276       */
 277  	public function getCalculatedValue($resetLog = TRUE)
 278      {
 279  //echo 'Cell '.$this->getCoordinate().' value is a '.$this->_dataType.' with a value of '.$this->getValue().PHP_EOL;
 280          if ($this->_dataType == PHPExcel_Cell_DataType::TYPE_FORMULA) {
 281              try {
 282  //echo 'Cell value for '.$this->getCoordinate().' is a formula: Calculating value'.PHP_EOL;
 283                  $result = PHPExcel_Calculation::getInstance(
 284                      $this->getWorksheet()->getParent()
 285                  )->calculateCellValue($this,$resetLog);
 286  //echo $this->getCoordinate().' calculation result is '.$result.PHP_EOL;
 287                  //    We don't yet handle array returns
 288                  if (is_array($result)) {
 289                      while (is_array($result)) {
 290                          $result = array_pop($result);
 291                      }
 292                  }
 293              } catch ( PHPExcel_Exception $ex ) {
 294                  if (($ex->getMessage() === 'Unable to access External Workbook') && ($this->_calculatedValue !== NULL)) {
 295  //echo 'Returning fallback value of '.$this->_calculatedValue.' for cell '.$this->getCoordinate().PHP_EOL;
 296                      return $this->_calculatedValue; // Fallback for calculations referencing external files.
 297                  }
 298  //echo 'Calculation Exception: '.$ex->getMessage().PHP_EOL;
 299                  $result = '#N/A';
 300                  throw new PHPExcel_Calculation_Exception(
 301                      $this->getWorksheet()->getTitle().'!'.$this->getCoordinate().' -> '.$ex->getMessage()
 302                  );
 303              }
 304  
 305              if ($result === '#Not Yet Implemented') {
 306  //echo 'Returning fallback value of '.$this->_calculatedValue.' for cell '.$this->getCoordinate().PHP_EOL;
 307                  return $this->_calculatedValue; // Fallback if calculation engine does not support the formula.
 308              }
 309  //echo 'Returning calculated value of '.$result.' for cell '.$this->getCoordinate().PHP_EOL;
 310              return $result;
 311          } elseif($this->_value instanceof PHPExcel_RichText) {
 312  //        echo 'Cell value for '.$this->getCoordinate().' is rich text: Returning data value of '.$this->_value.'<br />';
 313              return $this->_value->getPlainText();
 314          }
 315  //        echo 'Cell value for '.$this->getCoordinate().' is not a formula: Returning data value of '.$this->_value.'<br />';
 316          return $this->_value;
 317      }
 318  
 319      /**
 320       *    Set old calculated value (cached)
 321       *
 322       *    @param    mixed $pValue    Value
 323       *    @return    PHPExcel_Cell
 324       */
 325  	public function setCalculatedValue($pValue = NULL)
 326      {
 327          if ($pValue !== NULL) {
 328              $this->_calculatedValue = (is_numeric($pValue)) ? (float) $pValue : $pValue;
 329          }
 330  
 331          return $this->notifyCacheController();
 332      }
 333  
 334      /**
 335       *    Get old calculated value (cached)
 336       *    This returns the value last calculated by MS Excel or whichever spreadsheet program was used to
 337       *        create the original spreadsheet file.
 338       *    Note that this value is not guaranteed to refelect the actual calculated value because it is
 339       *        possible that auto-calculation was disabled in the original spreadsheet, and underlying data
 340       *        values used by the formula have changed since it was last calculated.
 341       *
 342       *    @return    mixed
 343       */
 344  	public function getOldCalculatedValue()
 345      {
 346          return $this->_calculatedValue;
 347      }
 348  
 349      /**
 350       *    Get cell data type
 351       *
 352       *    @return string
 353       */
 354  	public function getDataType()
 355      {
 356          return $this->_dataType;
 357      }
 358  
 359      /**
 360       *    Set cell data type
 361       *
 362       *    @param    string $pDataType
 363       *    @return    PHPExcel_Cell
 364       */
 365  	public function setDataType($pDataType = PHPExcel_Cell_DataType::TYPE_STRING)
 366      {
 367          if ($pDataType == PHPExcel_Cell_DataType::TYPE_STRING2)
 368              $pDataType = PHPExcel_Cell_DataType::TYPE_STRING;
 369  
 370          $this->_dataType = $pDataType;
 371  
 372          return $this->notifyCacheController();
 373      }
 374  
 375      /**
 376       *  Identify if the cell contains a formula
 377       *
 378       *  @return boolean
 379       */
 380      public function isFormula()
 381      {
 382          return $this->_dataType == PHPExcel_Cell_DataType::TYPE_FORMULA;
 383      }
 384  
 385      /**
 386       *    Does this cell contain Data validation rules?
 387       *
 388       *    @return    boolean
 389       *    @throws    PHPExcel_Exception
 390       */
 391  	public function hasDataValidation()
 392      {
 393          if (!isset($this->_parent)) {
 394              throw new PHPExcel_Exception('Cannot check for data validation when cell is not bound to a worksheet');
 395          }
 396  
 397          return $this->getWorksheet()->dataValidationExists($this->getCoordinate());
 398      }
 399  
 400      /**
 401       *    Get Data validation rules
 402       *
 403       *    @return    PHPExcel_Cell_DataValidation
 404       *    @throws    PHPExcel_Exception
 405       */
 406  	public function getDataValidation()
 407      {
 408          if (!isset($this->_parent)) {
 409              throw new PHPExcel_Exception('Cannot get data validation for cell that is not bound to a worksheet');
 410          }
 411  
 412          return $this->getWorksheet()->getDataValidation($this->getCoordinate());
 413      }
 414  
 415      /**
 416       *    Set Data validation rules
 417       *
 418       *    @param    PHPExcel_Cell_DataValidation    $pDataValidation
 419       *    @return    PHPExcel_Cell
 420       *    @throws    PHPExcel_Exception
 421       */
 422  	public function setDataValidation(PHPExcel_Cell_DataValidation $pDataValidation = NULL)
 423      {
 424          if (!isset($this->_parent)) {
 425              throw new PHPExcel_Exception('Cannot set data validation for cell that is not bound to a worksheet');
 426          }
 427  
 428          $this->getWorksheet()->setDataValidation($this->getCoordinate(), $pDataValidation);
 429  
 430          return $this->notifyCacheController();
 431      }
 432  
 433      /**
 434       *    Does this cell contain a Hyperlink?
 435       *
 436       *    @return boolean
 437       *    @throws    PHPExcel_Exception
 438       */
 439  	public function hasHyperlink()
 440      {
 441          if (!isset($this->_parent)) {
 442              throw new PHPExcel_Exception('Cannot check for hyperlink when cell is not bound to a worksheet');
 443          }
 444  
 445          return $this->getWorksheet()->hyperlinkExists($this->getCoordinate());
 446      }
 447  
 448      /**
 449       *    Get Hyperlink
 450       *
 451       *    @return    PHPExcel_Cell_Hyperlink
 452       *    @throws    PHPExcel_Exception
 453       */
 454  	public function getHyperlink()
 455      {
 456          if (!isset($this->_parent)) {
 457              throw new PHPExcel_Exception('Cannot get hyperlink for cell that is not bound to a worksheet');
 458          }
 459  
 460          return $this->getWorksheet()->getHyperlink($this->getCoordinate());
 461      }
 462  
 463      /**
 464       *    Set Hyperlink
 465       *
 466       *    @param    PHPExcel_Cell_Hyperlink    $pHyperlink
 467       *    @return    PHPExcel_Cell
 468       *    @throws    PHPExcel_Exception
 469       */
 470  	public function setHyperlink(PHPExcel_Cell_Hyperlink $pHyperlink = NULL)
 471      {
 472          if (!isset($this->_parent)) {
 473              throw new PHPExcel_Exception('Cannot set hyperlink for cell that is not bound to a worksheet');
 474          }
 475  
 476          $this->getWorksheet()->setHyperlink($this->getCoordinate(), $pHyperlink);
 477  
 478          return $this->notifyCacheController();
 479      }
 480  
 481      /**
 482       *    Get parent worksheet
 483       *
 484       *    @return PHPExcel_CachedObjectStorage_CacheBase
 485       */
 486  	public function getParent() {
 487          return $this->_parent;
 488      }
 489  
 490      /**
 491       *    Get parent worksheet
 492       *
 493       *    @return PHPExcel_Worksheet
 494       */
 495  	public function getWorksheet() {
 496          return $this->_parent->getParent();
 497      }
 498  
 499      /**
 500       *    Get cell style
 501       *
 502       *    @return    PHPExcel_Style
 503       */
 504  	public function getStyle()
 505      {
 506          return $this->getWorksheet()->getParent()->getCellXfByIndex($this->getXfIndex());
 507      }
 508  
 509      /**
 510       *    Re-bind parent
 511       *
 512       *    @param    PHPExcel_Worksheet $parent
 513       *    @return    PHPExcel_Cell
 514       */
 515  	public function rebindParent(PHPExcel_Worksheet $parent) {
 516          $this->_parent = $parent->getCellCacheController();
 517  
 518          return $this->notifyCacheController();
 519      }
 520  
 521      /**
 522       *    Is cell in a specific range?
 523       *
 524       *    @param    string    $pRange        Cell range (e.g. A1:A1)
 525       *    @return    boolean
 526       */
 527  	public function isInRange($pRange = 'A1:A1')
 528      {
 529          list($rangeStart,$rangeEnd) = self::rangeBoundaries($pRange);
 530  
 531          // Translate properties
 532          $myColumn    = self::columnIndexFromString($this->getColumn());
 533          $myRow        = $this->getRow();
 534  
 535          // Verify if cell is in range
 536          return (($rangeStart[0] <= $myColumn) && ($rangeEnd[0] >= $myColumn) &&
 537                  ($rangeStart[1] <= $myRow) && ($rangeEnd[1] >= $myRow)
 538                 );
 539      }
 540  
 541      /**
 542       *    Coordinate from string
 543       *
 544       *    @param    string    $pCoordinateString
 545       *    @return    array    Array containing column and row (indexes 0 and 1)
 546       *    @throws    PHPExcel_Exception
 547       */
 548  	public static function coordinateFromString($pCoordinateString = 'A1')
 549      {
 550          if (preg_match("/^([$]?[A-Z]{1,3})([$]?\d{1,7})$/", $pCoordinateString, $matches)) {
 551              return array($matches[1],$matches[2]);
 552          } elseif ((strpos($pCoordinateString,':') !== FALSE) || (strpos($pCoordinateString,',') !== FALSE)) {
 553              throw new PHPExcel_Exception('Cell coordinate string can not be a range of cells');
 554          } elseif ($pCoordinateString == '') {
 555              throw new PHPExcel_Exception('Cell coordinate can not be zero-length string');
 556          }
 557  
 558          throw new PHPExcel_Exception('Invalid cell coordinate '.$pCoordinateString);
 559      }
 560  
 561      /**
 562       *    Make string row, column or cell coordinate absolute
 563       *
 564       *    @param    string    $pCoordinateString        e.g. 'A' or '1' or 'A1'
 565       *                    Note that this value can be a row or column reference as well as a cell reference
 566       *    @return    string    Absolute coordinate        e.g. '$A' or '$1' or '$A$1'
 567       *    @throws    PHPExcel_Exception
 568       */
 569  	public static function absoluteReference($pCoordinateString = 'A1')
 570      {
 571          if (strpos($pCoordinateString,':') === FALSE && strpos($pCoordinateString,',') === FALSE) {
 572              // Split out any worksheet name from the reference
 573              $worksheet = '';
 574              $cellAddress = explode('!',$pCoordinateString);
 575              if (count($cellAddress) > 1) {
 576                  list($worksheet,$pCoordinateString) = $cellAddress;
 577              }
 578              if ($worksheet > '')    $worksheet .= '!';
 579  
 580              // Create absolute coordinate
 581              if (ctype_digit($pCoordinateString)) {
 582                  return $worksheet . '$' . $pCoordinateString;
 583              } elseif (ctype_alpha($pCoordinateString)) {
 584                  return $worksheet . '$' . strtoupper($pCoordinateString);
 585              }
 586              return $worksheet . self::absoluteCoordinate($pCoordinateString);
 587          }
 588  
 589          throw new PHPExcel_Exception('Cell coordinate string can not be a range of cells');
 590      }
 591  
 592      /**
 593       *    Make string coordinate absolute
 594       *
 595       *    @param    string    $pCoordinateString        e.g. 'A1'
 596       *    @return    string    Absolute coordinate        e.g. '$A$1'
 597       *    @throws    PHPExcel_Exception
 598       */
 599  	public static function absoluteCoordinate($pCoordinateString = 'A1')
 600      {
 601          if (strpos($pCoordinateString,':') === FALSE && strpos($pCoordinateString,',') === FALSE) {
 602              // Split out any worksheet name from the coordinate
 603              $worksheet = '';
 604              $cellAddress = explode('!',$pCoordinateString);
 605              if (count($cellAddress) > 1) {
 606                  list($worksheet,$pCoordinateString) = $cellAddress;
 607              }
 608              if ($worksheet > '')    $worksheet .= '!';
 609  
 610              // Create absolute coordinate
 611              list($column, $row) = self::coordinateFromString($pCoordinateString);
 612              $column = ltrim($column,'$');
 613              $row = ltrim($row,'$');
 614              return $worksheet . '$' . $column . '$' . $row;
 615          }
 616  
 617          throw new PHPExcel_Exception('Cell coordinate string can not be a range of cells');
 618      }
 619  
 620      /**
 621       *    Split range into coordinate strings
 622       *
 623       *    @param    string    $pRange        e.g. 'B4:D9' or 'B4:D9,H2:O11' or 'B4'
 624       *    @return    array    Array containg one or more arrays containing one or two coordinate strings
 625       *                                e.g. array('B4','D9') or array(array('B4','D9'),array('H2','O11'))
 626       *                                        or array('B4')
 627       */
 628  	public static function splitRange($pRange = 'A1:A1')
 629      {
 630          // Ensure $pRange is a valid range
 631          if(empty($pRange)) {
 632              $pRange = self::DEFAULT_RANGE;
 633          }
 634  
 635          $exploded = explode(',', $pRange);
 636          $counter = count($exploded);
 637          for ($i = 0; $i < $counter; ++$i) {
 638              $exploded[$i] = explode(':', $exploded[$i]);
 639          }
 640          return $exploded;
 641      }
 642  
 643      /**
 644       *    Build range from coordinate strings
 645       *
 646       *    @param    array    $pRange    Array containg one or more arrays containing one or two coordinate strings
 647       *    @return    string    String representation of $pRange
 648       *    @throws    PHPExcel_Exception
 649       */
 650  	public static function buildRange($pRange)
 651      {
 652          // Verify range
 653          if (!is_array($pRange) || empty($pRange) || !is_array($pRange[0])) {
 654              throw new PHPExcel_Exception('Range does not contain any information');
 655          }
 656  
 657          // Build range
 658          $imploded = array();
 659          $counter = count($pRange);
 660          for ($i = 0; $i < $counter; ++$i) {
 661              $pRange[$i] = implode(':', $pRange[$i]);
 662          }
 663          $imploded = implode(',', $pRange);
 664  
 665          return $imploded;
 666      }
 667  
 668      /**
 669       *    Calculate range boundaries
 670       *
 671       *    @param    string    $pRange        Cell range (e.g. A1:A1)
 672       *    @return    array    Range coordinates array(Start Cell, End Cell)
 673       *                    where Start Cell and End Cell are arrays (Column Number, Row Number)
 674       */
 675  	public static function rangeBoundaries($pRange = 'A1:A1')
 676      {
 677          // Ensure $pRange is a valid range
 678          if(empty($pRange)) {
 679              $pRange = self::DEFAULT_RANGE;
 680          }
 681  
 682          // Uppercase coordinate
 683          $pRange = strtoupper($pRange);
 684  
 685          // Extract range
 686          if (strpos($pRange, ':') === FALSE) {
 687              $rangeA = $rangeB = $pRange;
 688          } else {
 689              list($rangeA, $rangeB) = explode(':', $pRange);
 690          }
 691  
 692          // Calculate range outer borders
 693          $rangeStart = self::coordinateFromString($rangeA);
 694          $rangeEnd    = self::coordinateFromString($rangeB);
 695  
 696          // Translate column into index
 697          $rangeStart[0]    = self::columnIndexFromString($rangeStart[0]);
 698          $rangeEnd[0]    = self::columnIndexFromString($rangeEnd[0]);
 699  
 700          return array($rangeStart, $rangeEnd);
 701      }
 702  
 703      /**
 704       *    Calculate range dimension
 705       *
 706       *    @param    string    $pRange        Cell range (e.g. A1:A1)
 707       *    @return    array    Range dimension (width, height)
 708       */
 709  	public static function rangeDimension($pRange = 'A1:A1')
 710      {
 711          // Calculate range outer borders
 712          list($rangeStart,$rangeEnd) = self::rangeBoundaries($pRange);
 713  
 714          return array( ($rangeEnd[0] - $rangeStart[0] + 1), ($rangeEnd[1] - $rangeStart[1] + 1) );
 715      }
 716  
 717      /**
 718       *    Calculate range boundaries
 719       *
 720       *    @param    string    $pRange        Cell range (e.g. A1:A1)
 721       *    @return    array    Range coordinates array(Start Cell, End Cell)
 722       *                    where Start Cell and End Cell are arrays (Column ID, Row Number)
 723       */
 724  	public static function getRangeBoundaries($pRange = 'A1:A1')
 725      {
 726          // Ensure $pRange is a valid range
 727          if(empty($pRange)) {
 728              $pRange = self::DEFAULT_RANGE;
 729          }
 730  
 731          // Uppercase coordinate
 732          $pRange = strtoupper($pRange);
 733  
 734          // Extract range
 735          if (strpos($pRange, ':') === FALSE) {
 736              $rangeA = $rangeB = $pRange;
 737          } else {
 738              list($rangeA, $rangeB) = explode(':', $pRange);
 739          }
 740  
 741          return array( self::coordinateFromString($rangeA), self::coordinateFromString($rangeB));
 742      }
 743  
 744      /**
 745       *    Column index from string
 746       *
 747       *    @param    string $pString
 748       *    @return    int Column index (base 1 !!!)
 749       */
 750  	public static function columnIndexFromString($pString = 'A')
 751      {
 752          //    Using a lookup cache adds a slight memory overhead, but boosts speed
 753          //    caching using a static within the method is faster than a class static,
 754          //        though it's additional memory overhead
 755          static $_indexCache = array();
 756  
 757          if (isset($_indexCache[$pString]))
 758              return $_indexCache[$pString];
 759  
 760          //    It's surprising how costly the strtoupper() and ord() calls actually are, so we use a lookup array rather than use ord()
 761          //        and make it case insensitive to get rid of the strtoupper() as well. Because it's a static, there's no significant
 762          //        memory overhead either
 763          static $_columnLookup = array(
 764              'A' => 1, 'B' => 2, 'C' => 3, 'D' => 4, 'E' => 5, 'F' => 6, 'G' => 7, 'H' => 8, 'I' => 9, 'J' => 10, 'K' => 11, 'L' => 12, 'M' => 13,
 765              'N' => 14, 'O' => 15, 'P' => 16, 'Q' => 17, 'R' => 18, 'S' => 19, 'T' => 20, 'U' => 21, 'V' => 22, 'W' => 23, 'X' => 24, 'Y' => 25, 'Z' => 26,
 766              'a' => 1, 'b' => 2, 'c' => 3, 'd' => 4, 'e' => 5, 'f' => 6, 'g' => 7, 'h' => 8, 'i' => 9, 'j' => 10, 'k' => 11, 'l' => 12, 'm' => 13,
 767              'n' => 14, 'o' => 15, 'p' => 16, 'q' => 17, 'r' => 18, 's' => 19, 't' => 20, 'u' => 21, 'v' => 22, 'w' => 23, 'x' => 24, 'y' => 25, 'z' => 26
 768          );
 769  
 770          //    We also use the language construct isset() rather than the more costly strlen() function to match the length of $pString
 771          //        for improved performance
 772          if (isset($pString{0})) {
 773              if (!isset($pString{1})) {
 774                  $_indexCache[$pString] = $_columnLookup[$pString];
 775                  return $_indexCache[$pString];
 776              } elseif(!isset($pString{2})) {
 777                  $_indexCache[$pString] = $_columnLookup[$pString{0}] * 26 + $_columnLookup[$pString{1}];
 778                  return $_indexCache[$pString];
 779              } elseif(!isset($pString{3})) {
 780                  $_indexCache[$pString] = $_columnLookup[$pString{0}] * 676 + $_columnLookup[$pString{1}] * 26 + $_columnLookup[$pString{2}];
 781                  return $_indexCache[$pString];
 782              }
 783          }
 784          throw new PHPExcel_Exception("Column string index can not be " . ((isset($pString{0})) ? "longer than 3 characters" : "empty"));
 785      }
 786  
 787      /**
 788       *    String from columnindex
 789       *
 790       *    @param    int $pColumnIndex Column index (base 0 !!!)
 791       *    @return    string
 792       */
 793  	public static function stringFromColumnIndex($pColumnIndex = 0)
 794      {
 795          //    Using a lookup cache adds a slight memory overhead, but boosts speed
 796          //    caching using a static within the method is faster than a class static,
 797          //        though it's additional memory overhead
 798          static $_indexCache = array();
 799  
 800          if (!isset($_indexCache[$pColumnIndex])) {
 801              // Determine column string
 802              if ($pColumnIndex < 26) {
 803                  $_indexCache[$pColumnIndex] = chr(65 + $pColumnIndex);
 804              } elseif ($pColumnIndex < 702) {
 805                  $_indexCache[$pColumnIndex] = chr(64 + ($pColumnIndex / 26)) .
 806                                                chr(65 + $pColumnIndex % 26);
 807              } else {
 808                  $_indexCache[$pColumnIndex] = chr(64 + (($pColumnIndex - 26) / 676)) .
 809                                                chr(65 + ((($pColumnIndex - 26) % 676) / 26)) .
 810                                                chr(65 + $pColumnIndex % 26);
 811              }
 812          }
 813          return $_indexCache[$pColumnIndex];
 814      }
 815  
 816      /**
 817       *    Extract all cell references in range
 818       *
 819       *    @param    string    $pRange        Range (e.g. A1 or A1:C10 or A1:E10 A20:E25)
 820       *    @return    array    Array containing single cell references
 821       */
 822  	public static function extractAllCellReferencesInRange($pRange = 'A1') {
 823          // Returnvalue
 824          $returnValue = array();
 825  
 826          // Explode spaces
 827          $cellBlocks = explode(' ', str_replace('$', '', strtoupper($pRange)));
 828          foreach ($cellBlocks as $cellBlock) {
 829              // Single cell?
 830              if (strpos($cellBlock,':') === FALSE && strpos($cellBlock,',') === FALSE) {
 831                  $returnValue[] = $cellBlock;
 832                  continue;
 833              }
 834  
 835              // Range...
 836              $ranges = self::splitRange($cellBlock);
 837              foreach($ranges as $range) {
 838                  // Single cell?
 839                  if (!isset($range[1])) {
 840                      $returnValue[] = $range[0];
 841                      continue;
 842                  }
 843  
 844                  // Range...
 845                  list($rangeStart, $rangeEnd)    = $range;
 846                  sscanf($rangeStart,'%[A-Z]%d', $startCol, $startRow);
 847                  sscanf($rangeEnd,'%[A-Z]%d', $endCol, $endRow);
 848                  $endCol++;
 849  
 850                  // Current data
 851                  $currentCol    = $startCol;
 852                  $currentRow    = $startRow;
 853  
 854                  // Loop cells
 855                  while ($currentCol != $endCol) {
 856                      while ($currentRow <= $endRow) {
 857                          $returnValue[] = $currentCol.$currentRow;
 858                          ++$currentRow;
 859                      }
 860                      ++$currentCol;
 861                      $currentRow = $startRow;
 862                  }
 863              }
 864          }
 865  
 866          //    Sort the result by column and row
 867          $sortKeys = array();
 868          foreach (array_unique($returnValue) as $coord) {
 869              sscanf($coord,'%[A-Z]%d', $column, $row);
 870              $sortKeys[sprintf('%3s%09d',$column,$row)] = $coord;
 871          }
 872          ksort($sortKeys);
 873  
 874          // Return value
 875          return array_values($sortKeys);
 876      }
 877  
 878      /**
 879       * Compare 2 cells
 880       *
 881       * @param    PHPExcel_Cell    $a    Cell a
 882       * @param    PHPExcel_Cell    $b    Cell b
 883       * @return    int        Result of comparison (always -1 or 1, never zero!)
 884       */
 885  	public static function compareCells(PHPExcel_Cell $a, PHPExcel_Cell $b)
 886      {
 887          if ($a->getRow() < $b->getRow()) {
 888              return -1;
 889          } elseif ($a->getRow() > $b->getRow()) {
 890              return 1;
 891          } elseif (self::columnIndexFromString($a->getColumn()) < self::columnIndexFromString($b->getColumn())) {
 892              return -1;
 893          } else {
 894              return 1;
 895          }
 896      }
 897  
 898      /**
 899       * Get value binder to use
 900       *
 901       * @return PHPExcel_Cell_IValueBinder
 902       */
 903  	public static function getValueBinder() {
 904          if (self::$_valueBinder === NULL) {
 905              self::$_valueBinder = new PHPExcel_Cell_DefaultValueBinder();
 906          }
 907  
 908          return self::$_valueBinder;
 909      }
 910  
 911      /**
 912       * Set value binder to use
 913       *
 914       * @param PHPExcel_Cell_IValueBinder $binder
 915       * @throws PHPExcel_Exception
 916       */
 917  	public static function setValueBinder(PHPExcel_Cell_IValueBinder $binder = NULL) {
 918          if ($binder === NULL) {
 919              throw new PHPExcel_Exception("A PHPExcel_Cell_IValueBinder is required for PHPExcel to function correctly.");
 920          }
 921  
 922          self::$_valueBinder = $binder;
 923      }
 924  
 925      /**
 926       * Implement PHP __clone to create a deep clone, not just a shallow copy.
 927       */
 928  	public function __clone() {
 929          $vars = get_object_vars($this);
 930          foreach ($vars as $key => $value) {
 931              if ((is_object($value)) && ($key != '_parent')) {
 932                  $this->$key = clone $value;
 933              } else {
 934                  $this->$key = $value;
 935              }
 936          }
 937      }
 938  
 939      /**
 940       * Get index to cellXf
 941       *
 942       * @return int
 943       */
 944  	public function getXfIndex()
 945      {
 946          return $this->_xfIndex;
 947      }
 948  
 949      /**
 950       * Set index to cellXf
 951       *
 952       * @param int $pValue
 953       * @return PHPExcel_Cell
 954       */
 955  	public function setXfIndex($pValue = 0)
 956      {
 957          $this->_xfIndex = $pValue;
 958  
 959          return $this->notifyCacheController();
 960      }
 961  
 962      /**
 963       *    @deprecated        Since version 1.7.8 for planned changes to cell for array formula handling
 964       */
 965  	public function setFormulaAttributes($pAttributes)
 966      {
 967          $this->_formulaAttributes = $pAttributes;
 968          return $this;
 969      }
 970  
 971      /**
 972       *    @deprecated        Since version 1.7.8 for planned changes to cell for array formula handling
 973       */
 974  	public function getFormulaAttributes()
 975      {
 976          return $this->_formulaAttributes;
 977      }
 978  
 979      /**
 980       * Convert to string
 981       *
 982       * @return string
 983       */
 984  	public function __toString()
 985      {
 986          return (string) $this->getValue();
 987      }
 988  
 989  }
 990