lib/phpexcel/PHPExcel/ReferenceHelper.php

   1 <?php
   2
   3 /**
   4  * PHPExcel_ReferenceHelper (Singleton)
   5  *
   6  * Copyright (c) 2006 - 2015 PHPExcel
   7  *
   8  * This library is free software; you can redistribute it and/or
   9  * modify it under the terms of the GNU Lesser General Public
  10  * License as published by the Free Software Foundation; either
  11  * version 2.1 of the License, or (at your option) any later version.
  12  *
  13  * This library is distributed in the hope that it will be useful,
  14  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  15  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
  16  * Lesser General Public License for more details.
  17  *
  18  * You should have received a copy of the GNU Lesser General Public
  19  * License along with this library; if not, write to the Free Software
  20  * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
  21  *
  22  * @category   PHPExcel
  23  * @package    PHPExcel
  24  * @copyright  Copyright (c) 2006 - 2015 PHPExcel (http://www.codeplex.com/PHPExcel)
  25  * @license    http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt    LGPL
  26  * @version    ##VERSION##, ##DATE##
  27  */
  28 class PHPExcel_ReferenceHelper
  29 {
  30     /**    Constants                */
  31     /**    Regular Expressions      */
  32     const REFHELPER_REGEXP_CELLREF      = '((\w*|\'[^!]*\')!)?(?<![:a-z\$])(\$?[a-z]{1,3}\$?\d+)(?=[^:!\d\'])';
  33     const REFHELPER_REGEXP_CELLRANGE    = '((\w*|\'[^!]*\')!)?(\$?[a-z]{1,3}\$?\d+):(\$?[a-z]{1,3}\$?\d+)';
  34     const REFHELPER_REGEXP_ROWRANGE     = '((\w*|\'[^!]*\')!)?(\$?\d+):(\$?\d+)';
  35     const REFHELPER_REGEXP_COLRANGE     = '((\w*|\'[^!]*\')!)?(\$?[a-z]{1,3}):(\$?[a-z]{1,3})';
  36
  37     /**
  38      * Instance of this class
  39      *
  40      * @var PHPExcel_ReferenceHelper
  41      */
  42     private static $instance;
  43
  44     /**
  45      * Get an instance of this class
  46      *
  47      * @return PHPExcel_ReferenceHelper
  48      */
  49     public static function getInstance()
  50     {
  51         if (!isset(self::$instance) || (self::$instance === null)) {
  52             self::$instance = new PHPExcel_ReferenceHelper();
  53         }
  54
  55         return self::$instance;
  56     }
  57
  58     /**
  59      * Create a new PHPExcel_ReferenceHelper
  60      */
  61     protected function __construct()
  62     {
  63     }
  64
  65     /**
  66      * Compare two column addresses
  67      * Intended for use as a Callback function for sorting column addresses by column
  68      *
  69      * @param   string   $a  First column to test (e.g. 'AA')
  70      * @param   string   $b  Second column to test (e.g. 'Z')
  71      * @return  integer
  72      */
  73     public static function columnSort($a, $b)
  74     {
  75         return strcasecmp(strlen($a) . $a, strlen($b) . $b);
  76     }
  77
  78     /**
  79      * Compare two column addresses
  80      * Intended for use as a Callback function for reverse sorting column addresses by column
  81      *
  82      * @param   string   $a  First column to test (e.g. 'AA')
  83      * @param   string   $b  Second column to test (e.g. 'Z')
  84      * @return  integer
  85      */
  86     public static function columnReverseSort($a, $b)
  87     {
  88         return 1 - strcasecmp(strlen($a) . $a, strlen($b) . $b);
  89     }
  90
  91     /**
  92      * Compare two cell addresses
  93      * Intended for use as a Callback function for sorting cell addresses by column and row
  94      *
  95      * @param   string   $a  First cell to test (e.g. 'AA1')
  96      * @param   string   $b  Second cell to test (e.g. 'Z1')
  97      * @return  integer
  98      */
  99     public static function cellSort($a, $b)
 100     {
 101         sscanf($a, '%[A-Z]%d', $ac, $ar);
 102         sscanf($b, '%[A-Z]%d', $bc, $br);
 103
 104         if ($ar == $br) {
 105             return strcasecmp(strlen($ac) . $ac, strlen($bc) . $bc);
 106         }
 107         return ($ar < $br) ? -1 : 1;
 108     }
 109
 110     /**
 111      * Compare two cell addresses
 112      * Intended for use as a Callback function for sorting cell addresses by column and row
 113      *
 114      * @param   string   $a  First cell to test (e.g. 'AA1')
 115      * @param   string   $b  Second cell to test (e.g. 'Z1')
 116      * @return  integer
 117      */
 118     public static function cellReverseSort($a, $b)
 119     {
 120         sscanf($a, '%[A-Z]%d', $ac, $ar);
 121         sscanf($b, '%[A-Z]%d', $bc, $br);
 122
 123         if ($ar == $br) {
 124             return 1 - strcasecmp(strlen($ac) . $ac, strlen($bc) . $bc);
 125         }
 126         return ($ar < $br) ? 1 : -1;
 127     }
 128
 129     /**
 130      * Test whether a cell address falls within a defined range of cells
 131      *
 132      * @param   string     $cellAddress        Address of the cell we're testing
 133      * @param   integer    $beforeRow          Number of the row we're inserting/deleting before
 134      * @param   integer    $pNumRows           Number of rows to insert/delete (negative values indicate deletion)
 135      * @param   integer    $beforeColumnIndex  Index number of the column we're inserting/deleting before
 136      * @param   integer    $pNumCols           Number of columns to insert/delete (negative values indicate deletion)
 137      * @return  boolean
 138      */
 139     private static function cellAddressInDeleteRange($cellAddress, $beforeRow, $pNumRows, $beforeColumnIndex, $pNumCols)
 140     {
 141         list($cellColumn, $cellRow) = PHPExcel_Cell::coordinateFromString($cellAddress);
 142         $cellColumnIndex = PHPExcel_Cell::columnIndexFromString($cellColumn);
 143         //    Is cell within the range of rows/columns if we're deleting
 144         if ($pNumRows < 0 &&
 145             ($cellRow >= ($beforeRow + $pNumRows)) &&
 146             ($cellRow < $beforeRow)) {
 147             return true;
 148         } elseif ($pNumCols < 0 &&
 149             ($cellColumnIndex >= ($beforeColumnIndex + $pNumCols)) &&
 150             ($cellColumnIndex < $beforeColumnIndex)) {
 151             return true;
 152         }
 153         return false;
 154     }
 155
 156     /**
 157      * Update page breaks when inserting/deleting rows/columns
 158      *
 159      * @param   PHPExcel_Worksheet  $pSheet             The worksheet that we're editing
 160      * @param   string              $pBefore            Insert/Delete before this cell address (e.g. 'A1')
 161      * @param   integer             $beforeColumnIndex  Index number of the column we're inserting/deleting before
 162      * @param   integer             $pNumCols           Number of columns to insert/delete (negative values indicate deletion)
 163      * @param   integer             $beforeRow          Number of the row we're inserting/deleting before
 164      * @param   integer             $pNumRows           Number of rows to insert/delete (negative values indicate deletion)
 165      */
 166     protected function adjustPageBreaks(PHPExcel_Worksheet $pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
 167     {
 168         $aBreaks = $pSheet->getBreaks();
 169         ($pNumCols > 0 || $pNumRows > 0) ?
 170             uksort($aBreaks, array('PHPExcel_ReferenceHelper','cellReverseSort')) :
 171             uksort($aBreaks, array('PHPExcel_ReferenceHelper','cellSort'));
 172
 173         foreach ($aBreaks as $key => $value) {
 174             if (self::cellAddressInDeleteRange($key, $beforeRow, $pNumRows, $beforeColumnIndex, $pNumCols)) {
 175                 //    If we're deleting, then clear any defined breaks that are within the range
 176                 //        of rows/columns that we're deleting
 177                 $pSheet->setBreak($key, PHPExcel_Worksheet::BREAK_NONE);
 178             } else {
 179                 //    Otherwise update any affected breaks by inserting a new break at the appropriate point
 180                 //        and removing the old affected break
 181                 $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
 182                 if ($key != $newReference) {
 183                     $pSheet->setBreak($newReference, $value)
 184                         ->setBreak($key, PHPExcel_Worksheet::BREAK_NONE);
 185                 }
 186             }
 187         }
 188     }
 189
 190     /**
 191      * Update cell comments when inserting/deleting rows/columns
 192      *
 193      * @param   PHPExcel_Worksheet  $pSheet             The worksheet that we're editing
 194      * @param   string              $pBefore            Insert/Delete before this cell address (e.g. 'A1')
 195      * @param   integer             $beforeColumnIndex  Index number of the column we're inserting/deleting before
 196      * @param   integer             $pNumCols           Number of columns to insert/delete (negative values indicate deletion)
 197      * @param   integer             $beforeRow          Number of the row we're inserting/deleting before
 198      * @param   integer             $pNumRows           Number of rows to insert/delete (negative values indicate deletion)
 199      */
 200     protected function adjustComments($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
 201     {
 202         $aComments = $pSheet->getComments();
 203         $aNewComments = array(); // the new array of all comments
 204
 205         foreach ($aComments as $key => &$value) {
 206             // Any comments inside a deleted range will be ignored
 207             if (!self::cellAddressInDeleteRange($key, $beforeRow, $pNumRows, $beforeColumnIndex, $pNumCols)) {
 208                 // Otherwise build a new array of comments indexed by the adjusted cell reference
 209                 $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
 210                 $aNewComments[$newReference] = $value;
 211             }
 212         }
 213         //    Replace the comments array with the new set of comments
 214         $pSheet->setComments($aNewComments);
 215     }
 216
 217     /**
 218      * Update hyperlinks when inserting/deleting rows/columns
 219      *
 220      * @param   PHPExcel_Worksheet  $pSheet             The worksheet that we're editing
 221      * @param   string              $pBefore            Insert/Delete before this cell address (e.g. 'A1')
 222      * @param   integer             $beforeColumnIndex  Index number of the column we're inserting/deleting before
 223      * @param   integer             $pNumCols           Number of columns to insert/delete (negative values indicate deletion)
 224      * @param   integer             $beforeRow          Number of the row we're inserting/deleting before
 225      * @param   integer             $pNumRows           Number of rows to insert/delete (negative values indicate deletion)
 226      */
 227     protected function adjustHyperlinks($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
 228     {
 229         $aHyperlinkCollection = $pSheet->getHyperlinkCollection();
 230         ($pNumCols > 0 || $pNumRows > 0) ? uksort($aHyperlinkCollection, array('PHPExcel_ReferenceHelper','cellReverseSort')) : uksort($aHyperlinkCollection, array('PHPExcel_ReferenceHelper','cellSort'));
 231
 232         foreach ($aHyperlinkCollection as $key => $value) {
 233             $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
 234             if ($key != $newReference) {
 235                 $pSheet->setHyperlink($newReference, $value);
 236                 $pSheet->setHyperlink($key, null);
 237             }
 238         }
 239     }
 240
 241     /**
 242      * Update data validations when inserting/deleting rows/columns
 243      *
 244      * @param   PHPExcel_Worksheet  $pSheet             The worksheet that we're editing
 245      * @param   string              $pBefore            Insert/Delete before this cell address (e.g. 'A1')
 246      * @param   integer             $beforeColumnIndex  Index number of the column we're inserting/deleting before
 247      * @param   integer             $pNumCols           Number of columns to insert/delete (negative values indicate deletion)
 248      * @param   integer             $beforeRow          Number of the row we're inserting/deleting before
 249      * @param   integer             $pNumRows           Number of rows to insert/delete (negative values indicate deletion)
 250      */
 251     protected function adjustDataValidations($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
 252     {
 253         $aDataValidationCollection = $pSheet->getDataValidationCollection();
 254         ($pNumCols > 0 || $pNumRows > 0) ? uksort($aDataValidationCollection, array('PHPExcel_ReferenceHelper','cellReverseSort')) : uksort($aDataValidationCollection, array('PHPExcel_ReferenceHelper','cellSort'));
 255
 256         foreach ($aDataValidationCollection as $key => $value) {
 257             $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
 258             if ($key != $newReference) {
 259                 $pSheet->setDataValidation($newReference, $value);
 260                 $pSheet->setDataValidation($key, null);
 261             }
 262         }
 263     }
 264
 265     /**
 266      * Update merged cells when inserting/deleting rows/columns
 267      *
 268      * @param   PHPExcel_Worksheet  $pSheet             The worksheet that we're editing
 269      * @param   string              $pBefore            Insert/Delete before this cell address (e.g. 'A1')
 270      * @param   integer             $beforeColumnIndex  Index number of the column we're inserting/deleting before
 271      * @param   integer             $pNumCols           Number of columns to insert/delete (negative values indicate deletion)
 272      * @param   integer             $beforeRow          Number of the row we're inserting/deleting before
 273      * @param   integer             $pNumRows           Number of rows to insert/delete (negative values indicate deletion)
 274      */
 275     protected function adjustMergeCells($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
 276     {
 277         $aMergeCells = $pSheet->getMergeCells();
 278         $aNewMergeCells = array(); // the new array of all merge cells
 279         foreach ($aMergeCells as $key => &$value) {
 280             $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
 281             $aNewMergeCells[$newReference] = $newReference;
 282         }
 283         $pSheet->setMergeCells($aNewMergeCells); // replace the merge cells array
 284     }
 285
 286     /**
 287      * Update protected cells when inserting/deleting rows/columns
 288      *
 289      * @param   PHPExcel_Worksheet  $pSheet             The worksheet that we're editing
 290      * @param   string              $pBefore            Insert/Delete before this cell address (e.g. 'A1')
 291      * @param   integer             $beforeColumnIndex  Index number of the column we're inserting/deleting before
 292      * @param   integer             $pNumCols           Number of columns to insert/delete (negative values indicate deletion)
 293      * @param   integer             $beforeRow          Number of the row we're inserting/deleting before
 294      * @param   integer             $pNumRows           Number of rows to insert/delete (negative values indicate deletion)
 295      */
 296     protected function adjustProtectedCells($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
 297     {
 298         $aProtectedCells = $pSheet->getProtectedCells();
 299         ($pNumCols > 0 || $pNumRows > 0) ?
 300             uksort($aProtectedCells, array('PHPExcel_ReferenceHelper','cellReverseSort')) :
 301             uksort($aProtectedCells, array('PHPExcel_ReferenceHelper','cellSort'));
 302         foreach ($aProtectedCells as $key => $value) {
 303             $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
 304             if ($key != $newReference) {
 305                 $pSheet->protectCells($newReference, $value, true);
 306                 $pSheet->unprotectCells($key);
 307             }
 308         }
 309     }
 310
 311     /**
 312      * Update column dimensions when inserting/deleting rows/columns
 313      *
 314      * @param   PHPExcel_Worksheet  $pSheet             The worksheet that we're editing
 315      * @param   string              $pBefore            Insert/Delete before this cell address (e.g. 'A1')
 316      * @param   integer             $beforeColumnIndex  Index number of the column we're inserting/deleting before
 317      * @param   integer             $pNumCols           Number of columns to insert/delete (negative values indicate deletion)
 318      * @param   integer             $beforeRow          Number of the row we're inserting/deleting before
 319      * @param   integer             $pNumRows           Number of rows to insert/delete (negative values indicate deletion)
 320      */
 321     protected function adjustColumnDimensions($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
 322     {
 323         $aColumnDimensions = array_reverse($pSheet->getColumnDimensions(), true);
 324         if (!empty($aColumnDimensions)) {
 325             foreach ($aColumnDimensions as $objColumnDimension) {
 326                 $newReference = $this->updateCellReference($objColumnDimension->getColumnIndex() . '1', $pBefore, $pNumCols, $pNumRows);
 327                 list($newReference) = PHPExcel_Cell::coordinateFromString($newReference);
 328                 if ($objColumnDimension->getColumnIndex() != $newReference) {
 329                     $objColumnDimension->setColumnIndex($newReference);
 330                 }
 331             }
 332             $pSheet->refreshColumnDimensions();
 333         }
 334     }
 335
 336     /**
 337      * Update row dimensions when inserting/deleting rows/columns
 338      *
 339      * @param   PHPExcel_Worksheet  $pSheet             The worksheet that we're editing
 340      * @param   string              $pBefore            Insert/Delete before this cell address (e.g. 'A1')
 341      * @param   integer             $beforeColumnIndex  Index number of the column we're inserting/deleting before
 342      * @param   integer             $pNumCols           Number of columns to insert/delete (negative values indicate deletion)
 343      * @param   integer             $beforeRow          Number of the row we're inserting/deleting before
 344      * @param   integer             $pNumRows           Number of rows to insert/delete (negative values indicate deletion)
 345      */
 346     protected function adjustRowDimensions($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
 347     {
 348         $aRowDimensions = array_reverse($pSheet->getRowDimensions(), true);
 349         if (!empty($aRowDimensions)) {
 350             foreach ($aRowDimensions as $objRowDimension) {
 351                 $newReference = $this->updateCellReference('A' . $objRowDimension->getRowIndex(), $pBefore, $pNumCols, $pNumRows);
 352                 list(, $newReference) = PHPExcel_Cell::coordinateFromString($newReference);
 353                 if ($objRowDimension->getRowIndex() != $newReference) {
 354                     $objRowDimension->setRowIndex($newReference);
 355                 }
 356             }
 357             $pSheet->refreshRowDimensions();
 358
 359             $copyDimension = $pSheet->getRowDimension($beforeRow - 1);
 360             for ($i = $beforeRow; $i <= $beforeRow - 1 + $pNumRows; ++$i) {
 361                 $newDimension = $pSheet->getRowDimension($i);
 362                 $newDimension->setRowHeight($copyDimension->getRowHeight());
 363                 $newDimension->setVisible($copyDimension->getVisible());
 364                 $newDimension->setOutlineLevel($copyDimension->getOutlineLevel());
 365                 $newDimension->setCollapsed($copyDimension->getCollapsed());
 366             }
 367         }
 368     }
 369
 370     /**
 371      * Insert a new column or row, updating all possible related data
 372      *
 373      * @param   string              $pBefore    Insert before this cell address (e.g. 'A1')
 374      * @param   integer             $pNumCols   Number of columns to insert/delete (negative values indicate deletion)
 375      * @param   integer             $pNumRows   Number of rows to insert/delete (negative values indicate deletion)
 376      * @param   PHPExcel_Worksheet  $pSheet     The worksheet that we're editing
 377      * @throws  PHPExcel_Exception
 378      */
 379     public function insertNewBefore($pBefore = 'A1', $pNumCols = 0, $pNumRows = 0, PHPExcel_Worksheet $pSheet = null)
 380     {
 381         $remove = ($pNumCols < 0 || $pNumRows < 0);
 382         $aCellCollection = $pSheet->getCellCollection();
 383
 384         // Get coordinates of $pBefore
 385         $beforeColumn    = 'A';
 386         $beforeRow        = 1;
 387         list($beforeColumn, $beforeRow) = PHPExcel_Cell::coordinateFromString($pBefore);
 388         $beforeColumnIndex = PHPExcel_Cell::columnIndexFromString($beforeColumn);
 389
 390         // Clear cells if we are removing columns or rows
 391         $highestColumn    = $pSheet->getHighestColumn();
 392         $highestRow    = $pSheet->getHighestRow();
 393
 394         // 1. Clear column strips if we are removing columns
 395         if ($pNumCols < 0 && $beforeColumnIndex - 2 + $pNumCols > 0) {
 396             for ($i = 1; $i <= $highestRow - 1; ++$i) {
 397                 for ($j = $beforeColumnIndex - 1 + $pNumCols; $j <= $beforeColumnIndex - 2; ++$j) {
 398                     $coordinate = PHPExcel_Cell::stringFromColumnIndex($j) . $i;
 399                     $pSheet->removeConditionalStyles($coordinate);
 400                     if ($pSheet->cellExists($coordinate)) {
 401                         $pSheet->getCell($coordinate)->setValueExplicit('', PHPExcel_Cell_DataType::TYPE_NULL);
 402                         $pSheet->getCell($coordinate)->setXfIndex(0);
 403                     }
 404                 }
 405             }
 406         }
 407
 408         // 2. Clear row strips if we are removing rows
 409         if ($pNumRows < 0 && $beforeRow - 1 + $pNumRows > 0) {
 410             for ($i = $beforeColumnIndex - 1; $i <= PHPExcel_Cell::columnIndexFromString($highestColumn) - 1; ++$i) {
 411                 for ($j = $beforeRow + $pNumRows; $j <= $beforeRow - 1; ++$j) {
 412                     $coordinate = PHPExcel_Cell::stringFromColumnIndex($i) . $j;
 413                     $pSheet->removeConditionalStyles($coordinate);
 414                     if ($pSheet->cellExists($coordinate)) {
 415                         $pSheet->getCell($coordinate)->setValueExplicit('', PHPExcel_Cell_DataType::TYPE_NULL);
 416                         $pSheet->getCell($coordinate)->setXfIndex(0);
 417                     }
 418                 }
 419             }
 420         }
 421
 422         // Loop through cells, bottom-up, and change cell coordinates
 423         if ($remove) {
 424             // It's faster to reverse and pop than to use unshift, especially with large cell collections
 425             $aCellCollection = array_reverse($aCellCollection);
 426         }
 427         while ($cellID = array_pop($aCellCollection)) {
 428             $cell = $pSheet->getCell($cellID);
 429             $cellIndex = PHPExcel_Cell::columnIndexFromString($cell->getColumn());
 430
 431             if ($cellIndex-1 + $pNumCols < 0) {
 432                 continue;
 433             }
 434
 435             // New coordinates
 436             $newCoordinates = PHPExcel_Cell::stringFromColumnIndex($cellIndex-1 + $pNumCols) . ($cell->getRow() + $pNumRows);
 437
 438             // Should the cell be updated? Move value and cellXf index from one cell to another.
 439             if (($cellIndex >= $beforeColumnIndex) && ($cell->getRow() >= $beforeRow)) {
 440                 // Update cell styles
 441                 $pSheet->getCell($newCoordinates)->setXfIndex($cell->getXfIndex());
 442
 443                 // Insert this cell at its new location
 444                 if ($cell->getDataType() == PHPExcel_Cell_DataType::TYPE_FORMULA) {
 445                     // Formula should be adjusted
 446                     $pSheet->getCell($newCoordinates)
 447                            ->setValue($this->updateFormulaReferences($cell->getValue(), $pBefore, $pNumCols, $pNumRows, $pSheet->getTitle()));
 448                 } else {
 449                     // Formula should not be adjusted
 450                     $pSheet->getCell($newCoordinates)->setValue($cell->getValue());
 451                 }
 452
 453                 // Clear the original cell
 454                 $pSheet->getCellCacheController()->deleteCacheData($cellID);
 455             } else {
 456                 /*    We don't need to update styles for rows/columns before our insertion position,
 457                         but we do still need to adjust any formulae    in those cells                    */
 458                 if ($cell->getDataType() == PHPExcel_Cell_DataType::TYPE_FORMULA) {
 459                     // Formula should be adjusted
 460                     $cell->setValue($this->updateFormulaReferences($cell->getValue(), $pBefore, $pNumCols, $pNumRows, $pSheet->getTitle()));
 461                 }
 462
 463             }
 464         }
 465
 466         // Duplicate styles for the newly inserted cells
 467         $highestColumn    = $pSheet->getHighestColumn();
 468         $highestRow    = $pSheet->getHighestRow();
 469
 470         if ($pNumCols > 0 && $beforeColumnIndex - 2 > 0) {
 471             for ($i = $beforeRow; $i <= $highestRow - 1; ++$i) {
 472                 // Style
 473                 $coordinate = PHPExcel_Cell::stringFromColumnIndex($beforeColumnIndex - 2) . $i;
 474                 if ($pSheet->cellExists($coordinate)) {
 475                     $xfIndex = $pSheet->getCell($coordinate)->getXfIndex();
 476                     $conditionalStyles = $pSheet->conditionalStylesExists($coordinate) ?
 477                         $pSheet->getConditionalStyles($coordinate) : false;
 478                     for ($j = $beforeColumnIndex - 1; $j <= $beforeColumnIndex - 2 + $pNumCols; ++$j) {
 479                         $pSheet->getCellByColumnAndRow($j, $i)->setXfIndex($xfIndex);
 480                         if ($conditionalStyles) {
 481                             $cloned = array();
 482                             foreach ($conditionalStyles as $conditionalStyle) {
 483                                 $cloned[] = clone $conditionalStyle;
 484                             }
 485                             $pSheet->setConditionalStyles(PHPExcel_Cell::stringFromColumnIndex($j) . $i, $cloned);
 486                         }
 487                     }
 488                 }
 489
 490             }
 491         }
 492
 493         if ($pNumRows > 0 && $beforeRow - 1 > 0) {
 494             for ($i = $beforeColumnIndex - 1; $i <= PHPExcel_Cell::columnIndexFromString($highestColumn) - 1; ++$i) {
 495                 // Style
 496                 $coordinate = PHPExcel_Cell::stringFromColumnIndex($i) . ($beforeRow - 1);
 497                 if ($pSheet->cellExists($coordinate)) {
 498                     $xfIndex = $pSheet->getCell($coordinate)->getXfIndex();
 499                     $conditionalStyles = $pSheet->conditionalStylesExists($coordinate) ?
 500                         $pSheet->getConditionalStyles($coordinate) : false;
 501                     for ($j = $beforeRow; $j <= $beforeRow - 1 + $pNumRows; ++$j) {
 502                         $pSheet->getCell(PHPExcel_Cell::stringFromColumnIndex($i) . $j)->setXfIndex($xfIndex);
 503                         if ($conditionalStyles) {
 504                             $cloned = array();
 505                             foreach ($conditionalStyles as $conditionalStyle) {
 506                                 $cloned[] = clone $conditionalStyle;
 507                             }
 508                             $pSheet->setConditionalStyles(PHPExcel_Cell::stringFromColumnIndex($i) . $j, $cloned);
 509                         }
 510                     }
 511                 }
 512             }
 513         }
 514
 515         // Update worksheet: column dimensions
 516         $this->adjustColumnDimensions($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
 517
 518         // Update worksheet: row dimensions
 519         $this->adjustRowDimensions($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
 520
 521         //    Update worksheet: page breaks
 522         $this->adjustPageBreaks($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
 523
 524         //    Update worksheet: comments
 525         $this->adjustComments($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
 526
 527         // Update worksheet: hyperlinks
 528         $this->adjustHyperlinks($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
 529
 530         // Update worksheet: data validations
 531         $this->adjustDataValidations($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
 532
 533         // Update worksheet: merge cells
 534         $this->adjustMergeCells($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
 535
 536         // Update worksheet: protected cells
 537         $this->adjustProtectedCells($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
 538
 539         // Update worksheet: autofilter
 540         $autoFilter = $pSheet->getAutoFilter();
 541         $autoFilterRange = $autoFilter->getRange();
 542         if (!empty($autoFilterRange)) {
 543             if ($pNumCols != 0) {
 544                 $autoFilterColumns = array_keys($autoFilter->getColumns());
 545                 if (count($autoFilterColumns) > 0) {
 546                     sscanf($pBefore, '%[A-Z]%d', $column, $row);
 547                     $columnIndex = PHPExcel_Cell::columnIndexFromString($column);
 548                     list($rangeStart, $rangeEnd) = PHPExcel_Cell::rangeBoundaries($autoFilterRange);
 549                     if ($columnIndex <= $rangeEnd[0]) {
 550                         if ($pNumCols < 0) {
 551                             //    If we're actually deleting any columns that fall within the autofilter range,
 552                             //        then we delete any rules for those columns
 553                             $deleteColumn = $columnIndex + $pNumCols - 1;
 554                             $deleteCount = abs($pNumCols);
 555                             for ($i = 1; $i <= $deleteCount; ++$i) {
 556                                 if (in_array(PHPExcel_Cell::stringFromColumnIndex($deleteColumn), $autoFilterColumns)) {
 557                                     $autoFilter->clearColumn(PHPExcel_Cell::stringFromColumnIndex($deleteColumn));
 558                                 }
 559                                 ++$deleteColumn;
 560                             }
 561                         }
 562                         $startCol = ($columnIndex > $rangeStart[0]) ? $columnIndex : $rangeStart[0];
 563
 564                         //    Shuffle columns in autofilter range
 565                         if ($pNumCols > 0) {
 566                             //    For insert, we shuffle from end to beginning to avoid overwriting
 567                             $startColID = PHPExcel_Cell::stringFromColumnIndex($startCol-1);
 568                             $toColID = PHPExcel_Cell::stringFromColumnIndex($startCol+$pNumCols-1);
 569                             $endColID = PHPExcel_Cell::stringFromColumnIndex($rangeEnd[0]);
 570
 571                             $startColRef = $startCol;
 572                             $endColRef = $rangeEnd[0];
 573                             $toColRef = $rangeEnd[0]+$pNumCols;
 574
 575                             do {
 576                                 $autoFilter->shiftColumn(PHPExcel_Cell::stringFromColumnIndex($endColRef-1), PHPExcel_Cell::stringFromColumnIndex($toColRef-1));
 577                                 --$endColRef;
 578                                 --$toColRef;
 579                             } while ($startColRef <= $endColRef);
 580                         } else {
 581                             //    For delete, we shuffle from beginning to end to avoid overwriting
 582                             $startColID = PHPExcel_Cell::stringFromColumnIndex($startCol-1);
 583                             $toColID = PHPExcel_Cell::stringFromColumnIndex($startCol+$pNumCols-1);
 584                             $endColID = PHPExcel_Cell::stringFromColumnIndex($rangeEnd[0]);
 585                             do {
 586                                 $autoFilter->shiftColumn($startColID, $toColID);
 587                                 ++$startColID;
 588                                 ++$toColID;
 589                             } while ($startColID != $endColID);
 590                         }
 591                     }
 592                 }
 593             }
 594             $pSheet->setAutoFilter($this->updateCellReference($autoFilterRange, $pBefore, $pNumCols, $pNumRows));
 595         }
 596
 597         // Update worksheet: freeze pane
 598         if ($pSheet->getFreezePane() != '') {
 599             $pSheet->freezePane($this->updateCellReference($pSheet->getFreezePane(), $pBefore, $pNumCols, $pNumRows));
 600         }
 601
 602         // Page setup
 603         if ($pSheet->getPageSetup()->isPrintAreaSet()) {
 604             $pSheet->getPageSetup()->setPrintArea($this->updateCellReference($pSheet->getPageSetup()->getPrintArea(), $pBefore, $pNumCols, $pNumRows));
 605         }
 606
 607         // Update worksheet: drawings
 608         $aDrawings = $pSheet->getDrawingCollection();
 609         foreach ($aDrawings as $objDrawing) {
 610             $newReference = $this->updateCellReference($objDrawing->getCoordinates(), $pBefore, $pNumCols, $pNumRows);
 611             if ($objDrawing->getCoordinates() != $newReference) {
 612                 $objDrawing->setCoordinates($newReference);
 613             }
 614         }
 615
 616         // Update workbook: named ranges
 617         if (count($pSheet->getParent()->getNamedRanges()) > 0) {
 618             foreach ($pSheet->getParent()->getNamedRanges() as $namedRange) {
 619                 if ($namedRange->getWorksheet()->getHashCode() == $pSheet->getHashCode()) {
 620                     $namedRange->setRange($this->updateCellReference($namedRange->getRange(), $pBefore, $pNumCols, $pNumRows));
 621                 }
 622             }
 623         }
 624
 625         // Garbage collect
 626         $pSheet->garbageCollect();
 627     }
 628
 629     /**
 630      * Update references within formulas
 631      *
 632      * @param    string    $pFormula    Formula to update
 633      * @param    int        $pBefore    Insert before this one
 634      * @param    int        $pNumCols    Number of columns to insert
 635      * @param    int        $pNumRows    Number of rows to insert
 636      * @param   string  $sheetName  Worksheet name/title
 637      * @return    string    Updated formula
 638      * @throws    PHPExcel_Exception
 639      */
 640     public function updateFormulaReferences($pFormula = '', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0, $sheetName = '')
 641     {
 642         //    Update cell references in the formula
 643         $formulaBlocks = explode('"', $pFormula);
 644         $i = false;
 645         foreach ($formulaBlocks as &$formulaBlock) {
 646             //    Ignore blocks that were enclosed in quotes (alternating entries in the $formulaBlocks array after the explode)
 647             if ($i = !$i) {
 648                 $adjustCount = 0;
 649                 $newCellTokens = $cellTokens = array();
 650                 //    Search for row ranges (e.g. 'Sheet1'!3:5 or 3:5) with or without $ absolutes (e.g. $3:5)
 651                 $matchCount = preg_match_all('/'.self::REFHELPER_REGEXP_ROWRANGE.'/i', ' '.$formulaBlock.' ', $matches, PREG_SET_ORDER);
 652                 if ($matchCount > 0) {
 653                     foreach ($matches as $match) {
 654                         $fromString = ($match[2] > '') ? $match[2].'!' : '';
 655                         $fromString .= $match[3].':'.$match[4];
 656                         $modified3 = substr($this->updateCellReference('$A'.$match[3], $pBefore, $pNumCols, $pNumRows), 2);
 657                         $modified4 = substr($this->updateCellReference('$A'.$match[4], $pBefore, $pNumCols, $pNumRows), 2);
 658
 659                         if ($match[3].':'.$match[4] !== $modified3.':'.$modified4) {
 660                             if (($match[2] == '') || (trim($match[2], "'") == $sheetName)) {
 661                                 $toString = ($match[2] > '') ? $match[2].'!' : '';
 662                                 $toString .= $modified3.':'.$modified4;
 663                                 //    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
 664                                 $column = 100000;
 665                                 $row = 10000000 + trim($match[3], '$');
 666                                 $cellIndex = $column.$row;
 667
 668                                 $newCellTokens[$cellIndex] = preg_quote($toString);
 669                                 $cellTokens[$cellIndex] = '/(?<!\d\$\!)'.preg_quote($fromString).'(?!\d)/i';
 670                                 ++$adjustCount;
 671                             }
 672                         }
 673                     }
 674                 }
 675                 //    Search for column ranges (e.g. 'Sheet1'!C:E or C:E) with or without $ absolutes (e.g. $C:E)
 676                 $matchCount = preg_match_all('/'.self::REFHELPER_REGEXP_COLRANGE.'/i', ' '.$formulaBlock.' ', $matches, PREG_SET_ORDER);
 677                 if ($matchCount > 0) {
 678                     foreach ($matches as $match) {
 679                         $fromString = ($match[2] > '') ? $match[2].'!' : '';
 680                         $fromString .= $match[3].':'.$match[4];
 681                         $modified3 = substr($this->updateCellReference($match[3].'$1', $pBefore, $pNumCols, $pNumRows), 0, -2);
 682                         $modified4 = substr($this->updateCellReference($match[4].'$1', $pBefore, $pNumCols, $pNumRows), 0, -2);
 683
 684                         if ($match[3].':'.$match[4] !== $modified3.':'.$modified4) {
 685                             if (($match[2] == '') || (trim($match[2], "'") == $sheetName)) {
 686                                 $toString = ($match[2] > '') ? $match[2].'!' : '';
 687                                 $toString .= $modified3.':'.$modified4;
 688                                 //    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
 689                                 $column = PHPExcel_Cell::columnIndexFromString(trim($match[3], '$')) + 100000;
 690                                 $row = 10000000;
 691                                 $cellIndex = $column.$row;
 692
 693                                 $newCellTokens[$cellIndex] = preg_quote($toString);
 694                                 $cellTokens[$cellIndex] = '/(?<![A-Z\$\!])'.preg_quote($fromString).'(?![A-Z])/i';
 695                                 ++$adjustCount;
 696                             }
 697                         }
 698                     }
 699                 }
 700                 //    Search for cell ranges (e.g. 'Sheet1'!A3:C5 or A3:C5) with or without $ absolutes (e.g. $A1:C$5)
 701                 $matchCount = preg_match_all('/'.self::REFHELPER_REGEXP_CELLRANGE.'/i', ' '.$formulaBlock.' ', $matches, PREG_SET_ORDER);
 702                 if ($matchCount > 0) {
 703                     foreach ($matches as $match) {
 704                         $fromString = ($match[2] > '') ? $match[2].'!' : '';
 705                         $fromString .= $match[3].':'.$match[4];
 706                         $modified3 = $this->updateCellReference($match[3], $pBefore, $pNumCols, $pNumRows);
 707                         $modified4 = $this->updateCellReference($match[4], $pBefore, $pNumCols, $pNumRows);
 708
 709                         if ($match[3].$match[4] !== $modified3.$modified4) {
 710                             if (($match[2] == '') || (trim($match[2], "'") == $sheetName)) {
 711                                 $toString = ($match[2] > '') ? $match[2].'!' : '';
 712                                 $toString .= $modified3.':'.$modified4;
 713                                 list($column, $row) = PHPExcel_Cell::coordinateFromString($match[3]);
 714                                 //    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
 715                                 $column = PHPExcel_Cell::columnIndexFromString(trim($column, '$')) + 100000;
 716                                 $row = trim($row, '$') + 10000000;
 717                                 $cellIndex = $column.$row;
 718
 719                                 $newCellTokens[$cellIndex] = preg_quote($toString);
 720                                 $cellTokens[$cellIndex] = '/(?<![A-Z]\$\!)'.preg_quote($fromString).'(?!\d)/i';
 721                                 ++$adjustCount;
 722                             }
 723                         }
 724                     }
 725                 }
 726                 //    Search for cell references (e.g. 'Sheet1'!A3 or C5) with or without $ absolutes (e.g. $A1 or C$5)
 727                 $matchCount = preg_match_all('/'.self::REFHELPER_REGEXP_CELLREF.'/i', ' '.$formulaBlock.' ', $matches, PREG_SET_ORDER);
 728
 729                 if ($matchCount > 0) {
 730                     foreach ($matches as $match) {
 731                         $fromString = ($match[2] > '') ? $match[2].'!' : '';
 732                         $fromString .= $match[3];
 733
 734                         $modified3 = $this->updateCellReference($match[3], $pBefore, $pNumCols, $pNumRows);
 735                         if ($match[3] !== $modified3) {
 736                             if (($match[2] == '') || (trim($match[2], "'") == $sheetName)) {
 737                                 $toString = ($match[2] > '') ? $match[2].'!' : '';
 738                                 $toString .= $modified3;
 739                                 list($column, $row) = PHPExcel_Cell::coordinateFromString($match[3]);
 740                                 //    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
 741                                 $column = PHPExcel_Cell::columnIndexFromString(trim($column, '$')) + 100000;
 742                                 $row = trim($row, '$') + 10000000;
 743                                 $cellIndex = $row . $column;
 744
 745                                 $newCellTokens[$cellIndex] = preg_quote($toString);
 746                                 $cellTokens[$cellIndex] = '/(?<![A-Z\$\!])'.preg_quote($fromString).'(?!\d)/i';
 747                                 ++$adjustCount;
 748                             }
 749                         }
 750                     }
 751                 }
 752                 if ($adjustCount > 0) {
 753                     if ($pNumCols > 0 || $pNumRows > 0) {
 754                         krsort($cellTokens);
 755                         krsort($newCellTokens);
 756                     } else {
 757                         ksort($cellTokens);
 758                         ksort($newCellTokens);
 759                     }   //  Update cell references in the formula
 760                     $formulaBlock = str_replace('\\', '', preg_replace($cellTokens, $newCellTokens, $formulaBlock));
 761                 }
 762             }
 763         }
 764         unset($formulaBlock);
 765
 766         //    Then rebuild the formula string
 767         return implode('"', $formulaBlocks);
 768     }
 769
 770     /**
 771      * Update cell reference
 772      *
 773      * @param    string    $pCellRange            Cell range
 774      * @param    int        $pBefore            Insert before this one
 775      * @param    int        $pNumCols            Number of columns to increment
 776      * @param    int        $pNumRows            Number of rows to increment
 777      * @return    string    Updated cell range
 778      * @throws    PHPExcel_Exception
 779      */
 780     public function updateCellReference($pCellRange = 'A1', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0)
 781     {
 782         // Is it in another worksheet? Will not have to update anything.
 783         if (strpos($pCellRange, "!") !== false) {
 784             return $pCellRange;
 785         // Is it a range or a single cell?
 786         } elseif (strpos($pCellRange, ':') === false && strpos($pCellRange, ',') === false) {
 787             // Single cell
 788             return $this->updateSingleCellReference($pCellRange, $pBefore, $pNumCols, $pNumRows);
 789         } elseif (strpos($pCellRange, ':') !== false || strpos($pCellRange, ',') !== false) {
 790             // Range
 791             return $this->updateCellRange($pCellRange, $pBefore, $pNumCols, $pNumRows);
 792         } else {
 793             // Return original
 794             return $pCellRange;
 795         }
 796     }
 797
 798     /**
 799      * Update named formulas (i.e. containing worksheet references / named ranges)
 800      *
 801      * @param PHPExcel $pPhpExcel    Object to update
 802      * @param string $oldName        Old name (name to replace)
 803      * @param string $newName        New name
 804      */
 805     public function updateNamedFormulas(PHPExcel $pPhpExcel, $oldName = '', $newName = '')
 806     {
 807         if ($oldName == '') {
 808             return;
 809         }
 810
 811         foreach ($pPhpExcel->getWorksheetIterator() as $sheet) {
 812             foreach ($sheet->getCellCollection(false) as $cellID) {
 813                 $cell = $sheet->getCell($cellID);
 814                 if (($cell !== null) && ($cell->getDataType() == PHPExcel_Cell_DataType::TYPE_FORMULA)) {
 815                     $formula = $cell->getValue();
 816                     if (strpos($formula, $oldName) !== false) {
 817                         $formula = str_replace("'" . $oldName . "'!", "'" . $newName . "'!", $formula);
 818                         $formula = str_replace($oldName . "!", $newName . "!", $formula);
 819                         $cell->setValueExplicit($formula, PHPExcel_Cell_DataType::TYPE_FORMULA);
 820                     }
 821                 }
 822             }
 823         }
 824     }
 825
 826     /**
 827      * Update cell range
 828      *
 829      * @param    string    $pCellRange            Cell range    (e.g. 'B2:D4', 'B:C' or '2:3')
 830      * @param    int        $pBefore            Insert before this one
 831      * @param    int        $pNumCols            Number of columns to increment
 832      * @param    int        $pNumRows            Number of rows to increment
 833      * @return    string    Updated cell range
 834      * @throws    PHPExcel_Exception
 835      */
 836     private function updateCellRange($pCellRange = 'A1:A1', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0)
 837     {
 838         if (strpos($pCellRange, ':') !== false || strpos($pCellRange, ',') !== false) {
 839             // Update range
 840             $range = PHPExcel_Cell::splitRange($pCellRange);
 841             $ic = count($range);
 842             for ($i = 0; $i < $ic; ++$i) {
 843                 $jc = count($range[$i]);
 844                 for ($j = 0; $j < $jc; ++$j) {
 845                     if (ctype_alpha($range[$i][$j])) {
 846                         $r = PHPExcel_Cell::coordinateFromString($this->updateSingleCellReference($range[$i][$j].'1', $pBefore, $pNumCols, $pNumRows));
 847                         $range[$i][$j] = $r[0];
 848                     } elseif (ctype_digit($range[$i][$j])) {
 849                         $r = PHPExcel_Cell::coordinateFromString($this->updateSingleCellReference('A'.$range[$i][$j], $pBefore, $pNumCols, $pNumRows));
 850                         $range[$i][$j] = $r[1];
 851                     } else {
 852                         $range[$i][$j] = $this->updateSingleCellReference($range[$i][$j], $pBefore, $pNumCols, $pNumRows);
 853                     }
 854                 }
 855             }
 856
 857             // Recreate range string
 858             return PHPExcel_Cell::buildRange($range);
 859         } else {
 860             throw new PHPExcel_Exception("Only cell ranges may be passed to this method.");
 861         }
 862     }
 863
 864     /**
 865      * Update single cell reference
 866      *
 867      * @param    string    $pCellReference        Single cell reference
 868      * @param    int        $pBefore            Insert before this one
 869      * @param    int        $pNumCols            Number of columns to increment
 870      * @param    int        $pNumRows            Number of rows to increment
 871      * @return    string    Updated cell reference
 872      * @throws    PHPExcel_Exception
 873      */
 874     private function updateSingleCellReference($pCellReference = 'A1', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0)
 875     {
 876         if (strpos($pCellReference, ':') === false && strpos($pCellReference, ',') === false) {
 877             // Get coordinates of $pBefore
 878             list($beforeColumn, $beforeRow) = PHPExcel_Cell::coordinateFromString($pBefore);
 879
 880             // Get coordinates of $pCellReference
 881             list($newColumn, $newRow) = PHPExcel_Cell::coordinateFromString($pCellReference);
 882
 883             // Verify which parts should be updated
 884             $updateColumn = (($newColumn{0} != '$') && ($beforeColumn{0} != '$') && (PHPExcel_Cell::columnIndexFromString($newColumn) >= PHPExcel_Cell::columnIndexFromString($beforeColumn)));
 885             $updateRow = (($newRow{0} != '$') && ($beforeRow{0} != '$') && $newRow >= $beforeRow);
 886
 887             // Create new column reference
 888             if ($updateColumn) {
 889                 $newColumn    = PHPExcel_Cell::stringFromColumnIndex(PHPExcel_Cell::columnIndexFromString($newColumn) - 1 + $pNumCols);
 890             }
 891
 892             // Create new row reference
 893             if ($updateRow) {
 894                 $newRow    = $newRow + $pNumRows;
 895             }
 896
 897             // Return new reference
 898             return $newColumn . $newRow;
 899         } else {
 900             throw new PHPExcel_Exception("Only single cell references may be passed to this method.");
 901         }
 902     }
 903
 904     /**
 905      * __clone implementation. Cloning should not be allowed in a Singleton!
 906      *
 907      * @throws    PHPExcel_Exception
 908      */
 909     final public function __clone()
 910     {
 911         throw new PHPExcel_Exception("Cloning a Singleton is not allowed!");
 912     }
 913 }