[Summary view] [Print] [Text view]

   1  <?php
   2  /**
   3   * PHPExcel
   4   *
   5   * Copyright (c) 2006 - 2012 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
  23   * @copyright  Copyright (c) 2006 - 2012 PHPExcel (http://www.codeplex.com/PHPExcel)
  24   * @license    http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt    LGPL
  25   * @version    1.7.7, 2012-05-19
  26   */
  27  
  28  
  29  /**
  30   * PHPExcel_ReferenceHelper (Singleton)
  31   *
  32   * @category   PHPExcel
  33   * @package    PHPExcel
  34   * @copyright  Copyright (c) 2006 - 2012 PHPExcel (http://www.codeplex.com/PHPExcel)
  35   */
  36  class PHPExcel_ReferenceHelper
  37  {
  38      /**    Constants                */
  39      /**    Regular Expressions        */
  40      const REFHELPER_REGEXP_CELLREF        = '((\w*|\'[^!]*\')!)?(?<![:a-z\$])(\$?[a-z]{1,3}\$?\d+)(?=[^:!\d\'])';
  41      const REFHELPER_REGEXP_CELLRANGE    = '((\w*|\'[^!]*\')!)?(\$?[a-z]{1,3}\$?\d+):(\$?[a-z]{1,3}\$?\d+)';
  42      const REFHELPER_REGEXP_ROWRANGE        = '((\w*|\'[^!]*\')!)?(\$?\d+):(\$?\d+)';
  43      const REFHELPER_REGEXP_COLRANGE        = '((\w*|\'[^!]*\')!)?(\$?[a-z]{1,3}):(\$?[a-z]{1,3})';
  44  
  45      /**
  46       * Instance of this class
  47       *
  48       * @var PHPExcel_ReferenceHelper
  49       */
  50      private static $_instance;
  51  
  52      /**
  53       * Get an instance of this class
  54       *
  55       * @return PHPExcel_ReferenceHelper
  56       */
  57  	public static function getInstance() {
  58          if (!isset(self::$_instance) || (self::$_instance === NULL)) {
  59              self::$_instance = new PHPExcel_ReferenceHelper();
  60          }
  61  
  62          return self::$_instance;
  63      }
  64  
  65      /**
  66       * Create a new PHPExcel_ReferenceHelper
  67       */
  68  	protected function __construct() {
  69      }
  70  
  71      /**
  72       * Insert a new column, updating all possible related data
  73       *
  74       * @param    int    $pBefore    Insert before this one
  75       * @param    int    $pNumCols    Number of columns to insert
  76       * @param    int    $pNumRows    Number of rows to insert
  77       * @throws    Exception
  78       */
  79  	public function insertNewBefore($pBefore = 'A1', $pNumCols = 0, $pNumRows = 0, PHPExcel_Worksheet $pSheet = null) {
  80          $aCellCollection = $pSheet->getCellCollection();
  81  
  82          // Get coordinates of $pBefore
  83          $beforeColumn    = 'A';
  84          $beforeRow        = 1;
  85          list($beforeColumn, $beforeRow) = PHPExcel_Cell::coordinateFromString( $pBefore );
  86  
  87  
  88          // Clear cells if we are removing columns or rows
  89          $highestColumn    = $pSheet->getHighestColumn();
  90          $highestRow    = $pSheet->getHighestRow();
  91  
  92          // 1. Clear column strips if we are removing columns
  93          if ($pNumCols < 0 && PHPExcel_Cell::columnIndexFromString($beforeColumn) - 2 + $pNumCols > 0) {
  94              for ($i = 1; $i <= $highestRow - 1; ++$i) {
  95                  for ($j = PHPExcel_Cell::columnIndexFromString($beforeColumn) - 1 + $pNumCols; $j <= PHPExcel_Cell::columnIndexFromString($beforeColumn) - 2; ++$j) {
  96                      $coordinate = PHPExcel_Cell::stringFromColumnIndex($j) . $i;
  97                      $pSheet->removeConditionalStyles($coordinate);
  98                      if ($pSheet->cellExists($coordinate)) {
  99                          $pSheet->getCell($coordinate)->setValueExplicit('', PHPExcel_Cell_DataType::TYPE_NULL);
 100                          $pSheet->getCell($coordinate)->setXfIndex(0);
 101                      }
 102                  }
 103              }
 104          }
 105  
 106          // 2. Clear row strips if we are removing rows
 107          if ($pNumRows < 0 && $beforeRow - 1 + $pNumRows > 0) {
 108              for ($i = PHPExcel_Cell::columnIndexFromString($beforeColumn) - 1; $i <= PHPExcel_Cell::columnIndexFromString($highestColumn) - 1; ++$i) {
 109                  for ($j = $beforeRow + $pNumRows; $j <= $beforeRow - 1; ++$j) {
 110                      $coordinate = PHPExcel_Cell::stringFromColumnIndex($i) . $j;
 111                      $pSheet->removeConditionalStyles($coordinate);
 112                      if ($pSheet->cellExists($coordinate)) {
 113                          $pSheet->getCell($coordinate)->setValueExplicit('', PHPExcel_Cell_DataType::TYPE_NULL);
 114                          $pSheet->getCell($coordinate)->setXfIndex(0);
 115                      }
 116                  }
 117              }
 118          }
 119  
 120  
 121          // Loop through cells, bottom-up, and change cell coordinates
 122          while (($cellID = ($pNumCols < 0 || $pNumRows < 0) ? array_shift($aCellCollection) : array_pop($aCellCollection))) {
 123              $cell = $pSheet->getCell($cellID);
 124  
 125              // New coordinates
 126              $newCoordinates = PHPExcel_Cell::stringFromColumnIndex( PHPExcel_Cell::columnIndexFromString($cell->getColumn()) - 1 + $pNumCols ) . ($cell->getRow() + $pNumRows);
 127  
 128              // Should the cell be updated? Move value and cellXf index from one cell to another.
 129              if ((PHPExcel_Cell::columnIndexFromString( $cell->getColumn() ) >= PHPExcel_Cell::columnIndexFromString($beforeColumn)) &&
 130                  ($cell->getRow() >= $beforeRow)) {
 131  
 132                  // Update cell styles
 133                  $pSheet->getCell($newCoordinates)->setXfIndex($cell->getXfIndex());
 134                  $cell->setXfIndex(0);
 135  
 136                  // Insert this cell at its new location
 137                  if ($cell->getDataType() == PHPExcel_Cell_DataType::TYPE_FORMULA) {
 138                      // Formula should be adjusted
 139                      $pSheet->getCell($newCoordinates)
 140                             ->setValue($this->updateFormulaReferences($cell->getValue(),
 141                                                 $pBefore, $pNumCols, $pNumRows, $pSheet->getTitle()));
 142                  } else {
 143                      // Formula should not be adjusted
 144                      $pSheet->getCell($newCoordinates)->setValue($cell->getValue());
 145                  }
 146  
 147                  // Clear the original cell
 148                  $pSheet->getCell($cell->getCoordinate())->setValue('');
 149  
 150              } else {
 151                  /*    We don't need to update styles for rows/columns before our insertion position,
 152                          but we do still need to adjust any formulae    in those cells                    */
 153                  if ($cell->getDataType() == PHPExcel_Cell_DataType::TYPE_FORMULA) {
 154                      // Formula should be adjusted
 155                      $cell->setValue($this->updateFormulaReferences($cell->getValue(),
 156                                          $pBefore, $pNumCols, $pNumRows, $pSheet->getTitle()));
 157                  }
 158  
 159              }
 160          }
 161  
 162  
 163          // Duplicate styles for the newly inserted cells
 164          $highestColumn    = $pSheet->getHighestColumn();
 165          $highestRow    = $pSheet->getHighestRow();
 166  
 167          if ($pNumCols > 0 && PHPExcel_Cell::columnIndexFromString($beforeColumn) - 2 > 0) {
 168              for ($i = $beforeRow; $i <= $highestRow - 1; ++$i) {
 169  
 170                  // Style
 171                  $coordinate = PHPExcel_Cell::stringFromColumnIndex( PHPExcel_Cell::columnIndexFromString($beforeColumn) - 2 ) . $i;
 172                  if ($pSheet->cellExists($coordinate)) {
 173                      $xfIndex = $pSheet->getCell($coordinate)->getXfIndex();
 174                      $conditionalStyles = $pSheet->conditionalStylesExists($coordinate) ?
 175                          $pSheet->getConditionalStyles($coordinate) : false;
 176                      for ($j = PHPExcel_Cell::columnIndexFromString($beforeColumn) - 1; $j <= PHPExcel_Cell::columnIndexFromString($beforeColumn) - 2 + $pNumCols; ++$j) {
 177                          $pSheet->getCellByColumnAndRow($j, $i)->setXfIndex($xfIndex);
 178                          if ($conditionalStyles) {
 179                              $cloned = array();
 180                              foreach ($conditionalStyles as $conditionalStyle) {
 181                                  $cloned[] = clone $conditionalStyle;
 182                              }
 183                              $pSheet->setConditionalStyles(PHPExcel_Cell::stringFromColumnIndex($j) . $i, $cloned);
 184                          }
 185                      }
 186                  }
 187  
 188              }
 189          }
 190  
 191          if ($pNumRows > 0 && $beforeRow - 1 > 0) {
 192              for ($i = PHPExcel_Cell::columnIndexFromString($beforeColumn) - 1; $i <= PHPExcel_Cell::columnIndexFromString($highestColumn) - 1; ++$i) {
 193  
 194                  // Style
 195                  $coordinate = PHPExcel_Cell::stringFromColumnIndex($i) . ($beforeRow - 1);
 196                  if ($pSheet->cellExists($coordinate)) {
 197                      $xfIndex = $pSheet->getCell($coordinate)->getXfIndex();
 198                      $conditionalStyles = $pSheet->conditionalStylesExists($coordinate) ?
 199                          $pSheet->getConditionalStyles($coordinate) : false;
 200                      for ($j = $beforeRow; $j <= $beforeRow - 1 + $pNumRows; ++$j) {
 201                          $pSheet->getCell(PHPExcel_Cell::stringFromColumnIndex($i) . $j)->setXfIndex($xfIndex);
 202                          if ($conditionalStyles) {
 203                              $cloned = array();
 204                              foreach ($conditionalStyles as $conditionalStyle) {
 205                                  $cloned[] = clone $conditionalStyle;
 206                              }
 207                              $pSheet->setConditionalStyles(PHPExcel_Cell::stringFromColumnIndex($i) . $j, $cloned);
 208                          }
 209                      }
 210                  }
 211              }
 212          }
 213  
 214  
 215          // Update worksheet: column dimensions
 216          $aColumnDimensions = array_reverse($pSheet->getColumnDimensions(), true);
 217          if (!empty($aColumnDimensions)) {
 218              foreach ($aColumnDimensions as $objColumnDimension) {
 219                  $newReference = $this->updateCellReference($objColumnDimension->getColumnIndex() . '1', $pBefore, $pNumCols, $pNumRows);
 220                  list($newReference) = PHPExcel_Cell::coordinateFromString($newReference);
 221                  if ($objColumnDimension->getColumnIndex() != $newReference) {
 222                      $objColumnDimension->setColumnIndex($newReference);
 223                  }
 224              }
 225              $pSheet->refreshColumnDimensions();
 226          }
 227  
 228  
 229          // Update worksheet: row dimensions
 230          $aRowDimensions = array_reverse($pSheet->getRowDimensions(), true);
 231          if (!empty($aRowDimensions)) {
 232              foreach ($aRowDimensions as $objRowDimension) {
 233                  $newReference = $this->updateCellReference('A' . $objRowDimension->getRowIndex(), $pBefore, $pNumCols, $pNumRows);
 234                  list(, $newReference) = PHPExcel_Cell::coordinateFromString($newReference);
 235                  if ($objRowDimension->getRowIndex() != $newReference) {
 236                      $objRowDimension->setRowIndex($newReference);
 237                  }
 238              }
 239              $pSheet->refreshRowDimensions();
 240  
 241              $copyDimension = $pSheet->getRowDimension($beforeRow - 1);
 242              for ($i = $beforeRow; $i <= $beforeRow - 1 + $pNumRows; ++$i) {
 243                  $newDimension = $pSheet->getRowDimension($i);
 244                  $newDimension->setRowHeight($copyDimension->getRowHeight());
 245                  $newDimension->setVisible($copyDimension->getVisible());
 246                  $newDimension->setOutlineLevel($copyDimension->getOutlineLevel());
 247                  $newDimension->setCollapsed($copyDimension->getCollapsed());
 248              }
 249          }
 250  
 251  
 252          // Update worksheet: breaks
 253          $aBreaks = array_reverse($pSheet->getBreaks(), true);
 254          foreach ($aBreaks as $key => $value) {
 255              $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
 256              if ($key != $newReference) {
 257                  $pSheet->setBreak( $newReference, $value );
 258                  $pSheet->setBreak( $key, PHPExcel_Worksheet::BREAK_NONE );
 259              }
 260          }
 261  
 262          // Update worksheet: comments
 263          $aComments = $pSheet->getComments();
 264          $aNewComments = array(); // the new array of all comments
 265          foreach ($aComments as $key => &$value) {
 266              $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
 267              $aNewComments[$newReference] = $value;
 268          }
 269          $pSheet->setComments($aNewComments); // replace the comments array
 270  
 271          // Update worksheet: hyperlinks
 272          $aHyperlinkCollection = array_reverse($pSheet->getHyperlinkCollection(), true);
 273          foreach ($aHyperlinkCollection as $key => $value) {
 274              $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
 275              if ($key != $newReference) {
 276                  $pSheet->setHyperlink( $newReference, $value );
 277                  $pSheet->setHyperlink( $key, null );
 278              }
 279          }
 280  
 281  
 282          // Update worksheet: data validations
 283          $aDataValidationCollection = array_reverse($pSheet->getDataValidationCollection(), true);
 284          foreach ($aDataValidationCollection as $key => $value) {
 285              $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
 286              if ($key != $newReference) {
 287                  $pSheet->setDataValidation( $newReference, $value );
 288                  $pSheet->setDataValidation( $key, null );
 289              }
 290          }
 291  
 292  
 293          // Update worksheet: merge cells
 294          $aMergeCells = $pSheet->getMergeCells();
 295          $aNewMergeCells = array(); // the new array of all merge cells
 296          foreach ($aMergeCells as $key => &$value) {
 297              $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
 298              $aNewMergeCells[$newReference] = $newReference;
 299          }
 300          $pSheet->setMergeCells($aNewMergeCells); // replace the merge cells array
 301  
 302  
 303          // Update worksheet: protected cells
 304          $aProtectedCells = array_reverse($pSheet->getProtectedCells(), true);
 305          foreach ($aProtectedCells as $key => $value) {
 306              $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
 307              if ($key != $newReference) {
 308                  $pSheet->protectCells( $newReference, $value, true );
 309                  $pSheet->unprotectCells( $key );
 310              }
 311          }
 312  
 313  
 314          // Update worksheet: autofilter
 315          if ($pSheet->getAutoFilter() != '') {
 316              $pSheet->setAutoFilter( $this->updateCellReference($pSheet->getAutoFilter(), $pBefore, $pNumCols, $pNumRows) );
 317          }
 318  
 319  
 320          // Update worksheet: freeze pane
 321          if ($pSheet->getFreezePane() != '') {
 322              $pSheet->freezePane( $this->updateCellReference($pSheet->getFreezePane(), $pBefore, $pNumCols, $pNumRows) );
 323          }
 324  
 325  
 326          // Page setup
 327          if ($pSheet->getPageSetup()->isPrintAreaSet()) {
 328              $pSheet->getPageSetup()->setPrintArea( $this->updateCellReference($pSheet->getPageSetup()->getPrintArea(), $pBefore, $pNumCols, $pNumRows) );
 329          }
 330  
 331  
 332          // Update worksheet: drawings
 333          $aDrawings = $pSheet->getDrawingCollection();
 334          foreach ($aDrawings as $objDrawing) {
 335              $newReference = $this->updateCellReference($objDrawing->getCoordinates(), $pBefore, $pNumCols, $pNumRows);
 336              if ($objDrawing->getCoordinates() != $newReference) {
 337                  $objDrawing->setCoordinates($newReference);
 338              }
 339          }
 340  
 341  
 342          // Update workbook: named ranges
 343          if (count($pSheet->getParent()->getNamedRanges()) > 0) {
 344              foreach ($pSheet->getParent()->getNamedRanges() as $namedRange) {
 345                  if ($namedRange->getWorksheet()->getHashCode() == $pSheet->getHashCode()) {
 346                      $namedRange->setRange(
 347                          $this->updateCellReference($namedRange->getRange(), $pBefore, $pNumCols, $pNumRows)
 348                      );
 349                  }
 350              }
 351          }
 352  
 353          // Garbage collect
 354          $pSheet->garbageCollect();
 355      }
 356  
 357      /**
 358       * Update references within formulas
 359       *
 360       * @param    string    $pFormula    Formula to update
 361       * @param    int        $pBefore    Insert before this one
 362       * @param    int        $pNumCols    Number of columns to insert
 363       * @param    int        $pNumRows    Number of rows to insert
 364       * @return    string    Updated formula
 365       * @throws    Exception
 366       */
 367  	public function updateFormulaReferences($pFormula = '', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0, $sheetName = '') {
 368          //    Update cell references in the formula
 369          $formulaBlocks = explode('"',$pFormula);
 370          $i = false;
 371          foreach($formulaBlocks as &$formulaBlock) {
 372              //    Ignore blocks that were enclosed in quotes (alternating entries in the $formulaBlocks array after the explode)
 373              if ($i = !$i) {
 374                  $adjustCount = 0;
 375                  $newCellTokens = $cellTokens = array();
 376                  //    Search for row ranges (e.g. 'Sheet1'!3:5 or 3:5) with or without $ absolutes (e.g. $3:5)
 377                  $matchCount = preg_match_all('/'.self::REFHELPER_REGEXP_ROWRANGE.'/i', ' '.$formulaBlock.' ', $matches, PREG_SET_ORDER);
 378                  if ($matchCount > 0) {
 379                      foreach($matches as $match) {
 380                          $fromString = ($match[2] > '') ? $match[2].'!' : '';
 381                          $fromString .= $match[3].':'.$match[4];
 382                          $modified3 = substr($this->updateCellReference('$A'.$match[3],$pBefore,$pNumCols,$pNumRows),2);
 383                          $modified4 = substr($this->updateCellReference('$A'.$match[4],$pBefore,$pNumCols,$pNumRows),2);
 384  
 385                          if ($match[3].':'.$match[4] !== $modified3.':'.$modified4) {
 386                              if (($match[2] == '') || (trim($match[2],"'") == $sheetName)) {
 387                                  $toString = ($match[2] > '') ? $match[2].'!' : '';
 388                                  $toString .= $modified3.':'.$modified4;
 389                                  //    Max worksheet size is 1,048,576 rows by 16,384 columns in Excel 2007, so our adjustments need to be at least one digit more
 390                                  $column = 100000;
 391                                  $row = 10000000+trim($match[3],'$');
 392                                  $cellIndex = $column.$row;
 393  
 394                                  $newCellTokens[$cellIndex] = preg_quote($toString);
 395                                  $cellTokens[$cellIndex] = '/(?<!\d)'.preg_quote($fromString).'(?!\d)/i';
 396                                  ++$adjustCount;
 397                              }
 398                          }
 399                      }
 400                  }
 401                  //    Search for column ranges (e.g. 'Sheet1'!C:E or C:E) with or without $ absolutes (e.g. $C:E)
 402                  $matchCount = preg_match_all('/'.self::REFHELPER_REGEXP_COLRANGE.'/i', ' '.$formulaBlock.' ', $matches, PREG_SET_ORDER);
 403                  if ($matchCount > 0) {
 404                      foreach($matches as $match) {
 405                          $fromString = ($match[2] > '') ? $match[2].'!' : '';
 406                          $fromString .= $match[3].':'.$match[4];
 407                          $modified3 = substr($this->updateCellReference($match[3].'$1',$pBefore,$pNumCols,$pNumRows),0,-2);
 408                          $modified4 = substr($this->updateCellReference($match[4].'$1',$pBefore,$pNumCols,$pNumRows),0,-2);
 409  
 410                          if ($match[3].':'.$match[4] !== $modified3.':'.$modified4) {
 411                              if (($match[2] == '') || (trim($match[2],"'") == $sheetName)) {
 412                                  $toString = ($match[2] > '') ? $match[2].'!' : '';
 413                                  $toString .= $modified3.':'.$modified4;
 414                                  //    Max worksheet size is 1,048,576 rows by 16,384 columns in Excel 2007, so our adjustments need to be at least one digit more
 415                                  $column = PHPExcel_Cell::columnIndexFromString(trim($match[3],'$')) + 100000;
 416                                  $row = 10000000;
 417                                  $cellIndex = $column.$row;
 418  
 419                                  $newCellTokens[$cellIndex] = preg_quote($toString);
 420                                  $cellTokens[$cellIndex] = '/(?<![A-Z])'.preg_quote($fromString).'(?![A-Z])/i';
 421                                  ++$adjustCount;
 422                              }
 423                          }
 424                      }
 425                  }
 426                  //    Search for cell ranges (e.g. 'Sheet1'!A3:C5 or A3:C5) with or without $ absolutes (e.g. $A1:C$5)
 427                  $matchCount = preg_match_all('/'.self::REFHELPER_REGEXP_CELLRANGE.'/i', ' '.$formulaBlock.' ', $matches, PREG_SET_ORDER);
 428                  if ($matchCount > 0) {
 429                      foreach($matches as $match) {
 430                          $fromString = ($match[2] > '') ? $match[2].'!' : '';
 431                          $fromString .= $match[3].':'.$match[4];
 432                          $modified3 = $this->updateCellReference($match[3],$pBefore,$pNumCols,$pNumRows);
 433                          $modified4 = $this->updateCellReference($match[4],$pBefore,$pNumCols,$pNumRows);
 434  
 435                          if ($match[3].$match[4] !== $modified3.$modified4) {
 436                              if (($match[2] == '') || (trim($match[2],"'") == $sheetName)) {
 437                                  $toString = ($match[2] > '') ? $match[2].'!' : '';
 438                                  $toString .= $modified3.':'.$modified4;
 439                                  list($column,$row) = PHPExcel_Cell::coordinateFromString($match[3]);
 440                                  //    Max worksheet size is 1,048,576 rows by 16,384 columns in Excel 2007, so our adjustments need to be at least one digit more
 441                                  $column = PHPExcel_Cell::columnIndexFromString(trim($column,'$')) + 100000;
 442                                  $row = trim($row,'$') + 10000000;
 443                                  $cellIndex = $column.$row;
 444  
 445                                  $newCellTokens[$cellIndex] = preg_quote($toString);
 446                                  $cellTokens[$cellIndex] = '/(?<![A-Z])'.preg_quote($fromString).'(?!\d)/i';
 447                                  ++$adjustCount;
 448                              }
 449                          }
 450                      }
 451                  }
 452                  //    Search for cell references (e.g. 'Sheet1'!A3 or C5) with or without $ absolutes (e.g. $A1 or C$5)
 453                  $matchCount = preg_match_all('/'.self::REFHELPER_REGEXP_CELLREF.'/i', ' '.$formulaBlock.' ', $matches, PREG_SET_ORDER);
 454                  if ($matchCount > 0) {
 455                      foreach($matches as $match) {
 456                          $fromString = ($match[2] > '') ? $match[2].'!' : '';
 457                          $fromString .= $match[3];
 458                          $modified3 = $this->updateCellReference($match[3],$pBefore,$pNumCols,$pNumRows);
 459  
 460                          if ($match[3] !== $modified3) {
 461                              if (($match[2] == '') || (trim($match[2],"'") == $sheetName)) {
 462                                  $toString = ($match[2] > '') ? $match[2].'!' : '';
 463                                  $toString .= $modified3;
 464                                  list($column,$row) = PHPExcel_Cell::coordinateFromString($match[3]);
 465                                  //    Max worksheet size is 1,048,576 rows by 16,384 columns in Excel 2007, so our adjustments need to be at least one digit more
 466                                  $column = PHPExcel_Cell::columnIndexFromString(trim($column,'$')) + 100000;
 467                                  $row = trim($row,'$') + 10000000;
 468                                  $cellIndex = $column.$row;
 469  
 470                                  $newCellTokens[$cellIndex] = preg_quote($toString);
 471                                  $cellTokens[$cellIndex] = '/(?<![A-Z])'.preg_quote($fromString).'(?!\d)/i';
 472                                  ++$adjustCount;
 473                              }
 474                          }
 475                      }
 476                  }
 477                  if ($adjustCount > 0) {
 478                      krsort($cellTokens);
 479                      krsort($newCellTokens);
 480                      //    Update cell references in the formula
 481                      $formulaBlock = str_replace('\\','',preg_replace($cellTokens,$newCellTokens,$formulaBlock));
 482                  }
 483              }
 484          }
 485          unset($formulaBlock);
 486  
 487          //    Then rebuild the formula string
 488          return implode('"',$formulaBlocks);
 489      }
 490  
 491      /**
 492       * Update cell reference
 493       *
 494       * @param    string    $pCellRange            Cell range
 495       * @param    int        $pBefore            Insert before this one
 496       * @param    int        $pNumCols            Number of columns to increment
 497       * @param    int        $pNumRows            Number of rows to increment
 498       * @return    string    Updated cell range
 499       * @throws    Exception
 500       */
 501  	public function updateCellReference($pCellRange = 'A1', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0) {
 502          // Is it in another worksheet? Will not have to update anything.
 503          if (strpos($pCellRange, "!") !== false) {
 504              return $pCellRange;
 505          // Is it a range or a single cell?
 506          } elseif (strpos($pCellRange, ':') === false && strpos($pCellRange, ',') === false) {
 507              // Single cell
 508              return $this->_updateSingleCellReference($pCellRange, $pBefore, $pNumCols, $pNumRows);
 509          } elseif (strpos($pCellRange, ':') !== false || strpos($pCellRange, ',') !== false) {
 510              // Range
 511              return $this->_updateCellRange($pCellRange, $pBefore, $pNumCols, $pNumRows);
 512          } else {
 513              // Return original
 514              return $pCellRange;
 515          }
 516      }
 517  
 518      /**
 519       * Update named formulas (i.e. containing worksheet references / named ranges)
 520       *
 521       * @param PHPExcel $pPhpExcel    Object to update
 522       * @param string $oldName        Old name (name to replace)
 523       * @param string $newName        New name
 524       */
 525  	public function updateNamedFormulas(PHPExcel $pPhpExcel, $oldName = '', $newName = '') {
 526          if ($oldName == '') {
 527              return;
 528          }
 529  
 530          foreach ($pPhpExcel->getWorksheetIterator() as $sheet) {
 531              foreach ($sheet->getCellCollection(false) as $cellID) {
 532                  $cell = $sheet->getCell($cellID);
 533                  if (($cell !== NULL) && ($cell->getDataType() == PHPExcel_Cell_DataType::TYPE_FORMULA)) {
 534                      $formula = $cell->getValue();
 535                      if (strpos($formula, $oldName) !== false) {
 536                          $formula = str_replace("'" . $oldName . "'!", "'" . $newName . "'!", $formula);
 537                          $formula = str_replace($oldName . "!", $newName . "!", $formula);
 538                          $cell->setValueExplicit($formula, PHPExcel_Cell_DataType::TYPE_FORMULA);
 539                      }
 540                  }
 541              }
 542          }
 543      }
 544  
 545      /**
 546       * Update cell range
 547       *
 548       * @param    string    $pCellRange            Cell range    (e.g. 'B2:D4', 'B:C' or '2:3')
 549       * @param    int        $pBefore            Insert before this one
 550       * @param    int        $pNumCols            Number of columns to increment
 551       * @param    int        $pNumRows            Number of rows to increment
 552       * @return    string    Updated cell range
 553       * @throws    Exception
 554       */
 555  	private function _updateCellRange($pCellRange = 'A1:A1', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0) {
 556          if (strpos($pCellRange,':') !== false || strpos($pCellRange, ',') !== false) {
 557              // Update range
 558              $range = PHPExcel_Cell::splitRange($pCellRange);
 559              $ic = count($range);
 560              for ($i = 0; $i < $ic; ++$i) {
 561                  $jc = count($range[$i]);
 562                  for ($j = 0; $j < $jc; ++$j) {
 563                      if (ctype_alpha($range[$i][$j])) {
 564                          $r = PHPExcel_Cell::coordinateFromString($this->_updateSingleCellReference($range[$i][$j].'1', $pBefore, $pNumCols, $pNumRows));
 565                          $range[$i][$j] = $r[0];
 566                      } elseif(ctype_digit($range[$i][$j])) {
 567                          $r = PHPExcel_Cell::coordinateFromString($this->_updateSingleCellReference('A'.$range[$i][$j], $pBefore, $pNumCols, $pNumRows));
 568                          $range[$i][$j] = $r[1];
 569                      } else {
 570                          $range[$i][$j] = $this->_updateSingleCellReference($range[$i][$j], $pBefore, $pNumCols, $pNumRows);
 571                      }
 572                  }
 573              }
 574  
 575              // Recreate range string
 576              return PHPExcel_Cell::buildRange($range);
 577          } else {
 578              throw new Exception("Only cell ranges may be passed to this method.");
 579          }
 580      }
 581  
 582      /**
 583       * Update single cell reference
 584       *
 585       * @param    string    $pCellReference        Single cell reference
 586       * @param    int        $pBefore            Insert before this one
 587       * @param    int        $pNumCols            Number of columns to increment
 588       * @param    int        $pNumRows            Number of rows to increment
 589       * @return    string    Updated cell reference
 590       * @throws    Exception
 591       */
 592  	private function _updateSingleCellReference($pCellReference = 'A1', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0) {
 593          if (strpos($pCellReference, ':') === false && strpos($pCellReference, ',') === false) {
 594              // Get coordinates of $pBefore
 595              list($beforeColumn, $beforeRow) = PHPExcel_Cell::coordinateFromString( $pBefore );
 596  
 597              // Get coordinates of $pCellReference
 598              list($newColumn, $newRow) = PHPExcel_Cell::coordinateFromString( $pCellReference );
 599  
 600              // Verify which parts should be updated
 601              $updateColumn = (($newColumn{0} != '$') && ($beforeColumn{0} != '$') &&
 602                               PHPExcel_Cell::columnIndexFromString($newColumn) >= PHPExcel_Cell::columnIndexFromString($beforeColumn));
 603  
 604              $updateRow = (($newRow{0} != '$') && ($beforeRow{0} != '$') &&
 605                            $newRow >= $beforeRow);
 606  
 607              // Create new column reference
 608              if ($updateColumn) {
 609                  $newColumn    = PHPExcel_Cell::stringFromColumnIndex( PHPExcel_Cell::columnIndexFromString($newColumn) - 1 + $pNumCols );
 610              }
 611  
 612              // Create new row reference
 613              if ($updateRow) {
 614                  $newRow    = $newRow + $pNumRows;
 615              }
 616  
 617              // Return new reference
 618              return $newColumn . $newRow;
 619          } else {
 620              throw new Exception("Only single cell references may be passed to this method.");
 621          }
 622      }
 623  
 624      /**
 625       * __clone implementation. Cloning should not be allowed in a Singleton!
 626       *
 627       * @throws    Exception
 628       */
 629  	public final function __clone() {
 630          throw new Exception("Cloning a Singleton is not allowed!");
 631      }
 632  }