<?php

/**
 * PHPExcel_ReferenceHelper (Singleton)
 *
 * Copyright (c) 2006 - 2015 PHPExcel
 *
 * This library is free software; you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public
 * License as published by the Free Software Foundation; either
 * version 2.1 of the License, or (at your option) any later version.
 *
 * This library is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 * Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public
 * License along with this library; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
 *
 * @category   PHPExcel
 * @package    PHPExcel
 * @copyright  Copyright (c) 2006 - 2015 PHPExcel (http://www.codeplex.com/PHPExcel)
 * @license    http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt    LGPL
 * @version    ##VERSION##, ##DATE##
 */
class PHPExcel_ReferenceHelper
{
  /**
   * Constants
   */
  /**
   * Regular Expressions
   */
  const REFHELPER_REGEXP_CELLREF = '((\w*|\'[^!]*\')!)?(?<![:a-z\$])(\$?[a-z]{1,3}\$?\d+)(?=[^:!\d\'])';
  const REFHELPER_REGEXP_CELLRANGE = '((\w*|\'[^!]*\')!)?(\$?[a-z]{1,3}\$?\d+):(\$?[a-z]{1,3}\$?\d+)';
  const REFHELPER_REGEXP_ROWRANGE = '((\w*|\'[^!]*\')!)?(\$?\d+):(\$?\d+)';
  const REFHELPER_REGEXP_COLRANGE = '((\w*|\'[^!]*\')!)?(\$?[a-z]{1,3}):(\$?[a-z]{1,3})';
  
  /**
   * Instance of this class
   *
   * @var PHPExcel_ReferenceHelper
   */
  private static $instance;
  
  /**
   * Get an instance of this class
   *
   * @return PHPExcel_ReferenceHelper
   */
  public static function getInstance()
  {
    if (! isset ( self::$instance ) || (self::$instance === null))
    {
      self::$instance = new PHPExcel_ReferenceHelper ();
    }
    
    return self::$instance;
  }
  
  /**
   * Create a new PHPExcel_ReferenceHelper
   */
  protected function __construct()
  {
  }
  
  /**
   * Compare two column addresses
   * Intended for use as a Callback function for sorting column addresses by column
   *
   * @param string $a
   *          First column to test (e.g. 'AA')
   * @param string $b
   *          Second column to test (e.g. 'Z')
   * @return integer
   */
  public static function columnSort($a, $b)
  {
    return strcasecmp ( strlen ( $a ) . $a, strlen ( $b ) . $b );
  }
  
  /**
   * Compare two column addresses
   * Intended for use as a Callback function for reverse sorting column addresses by column
   *
   * @param string $a
   *          First column to test (e.g. 'AA')
   * @param string $b
   *          Second column to test (e.g. 'Z')
   * @return integer
   */
  public static function columnReverseSort($a, $b)
  {
    return 1 - strcasecmp ( strlen ( $a ) . $a, strlen ( $b ) . $b );
  }
  
  /**
   * Compare two cell addresses
   * Intended for use as a Callback function for sorting cell addresses by column and row
   *
   * @param string $a
   *          First cell to test (e.g. 'AA1')
   * @param string $b
   *          Second cell to test (e.g. 'Z1')
   * @return integer
   */
  public static function cellSort($a, $b)
  {
    sscanf ( $a, '%[A-Z]%d', $ac, $ar );
    sscanf ( $b, '%[A-Z]%d', $bc, $br );
    
    if ($ar == $br)
    {
      return strcasecmp ( strlen ( $ac ) . $ac, strlen ( $bc ) . $bc );
    }
    return ($ar < $br) ? - 1 : 1;
  }
  
  /**
   * Compare two cell addresses
   * Intended for use as a Callback function for sorting cell addresses by column and row
   *
   * @param string $a
   *          First cell to test (e.g. 'AA1')
   * @param string $b
   *          Second cell to test (e.g. 'Z1')
   * @return integer
   */
  public static function cellReverseSort($a, $b)
  {
    sscanf ( $a, '%[A-Z]%d', $ac, $ar );
    sscanf ( $b, '%[A-Z]%d', $bc, $br );
    
    if ($ar == $br)
    {
      return 1 - strcasecmp ( strlen ( $ac ) . $ac, strlen ( $bc ) . $bc );
    }
    return ($ar < $br) ? 1 : - 1;
  }
  
  /**
   * Test whether a cell address falls within a defined range of cells
   *
   * @param string $cellAddress
   *          Address of the cell we're testing
   * @param integer $beforeRow
   *          Number of the row we're inserting/deleting before
   * @param integer $pNumRows
   *          Number of rows to insert/delete (negative values indicate deletion)
   * @param integer $beforeColumnIndex
   *          Index number of the column we're inserting/deleting before
   * @param integer $pNumCols
   *          Number of columns to insert/delete (negative values indicate deletion)
   * @return boolean
   */
  private static function cellAddressInDeleteRange($cellAddress, $beforeRow, $pNumRows, $beforeColumnIndex, $pNumCols)
  {
    list ( $cellColumn, $cellRow ) = PHPExcel_Cell::coordinateFromString ( $cellAddress );
    $cellColumnIndex = PHPExcel_Cell::columnIndexFromString ( $cellColumn );
    // Is cell within the range of rows/columns if we're deleting
    if ($pNumRows < 0 && ($cellRow >= ($beforeRow + $pNumRows)) && ($cellRow < $beforeRow))
    {
      return true;
    }
    elseif ($pNumCols < 0 && ($cellColumnIndex >= ($beforeColumnIndex + $pNumCols)) && ($cellColumnIndex < $beforeColumnIndex))
    {
      return true;
    }
    return false;
  }
  
  /**
   * Update page breaks when inserting/deleting rows/columns
   *
   * @param PHPExcel_Worksheet $pSheet
   *          The worksheet that we're editing
   * @param string $pBefore
   *          Insert/Delete before this cell address (e.g. 'A1')
   * @param integer $beforeColumnIndex
   *          Index number of the column we're inserting/deleting before
   * @param integer $pNumCols
   *          Number of columns to insert/delete (negative values indicate deletion)
   * @param integer $beforeRow
   *          Number of the row we're inserting/deleting before
   * @param integer $pNumRows
   *          Number of rows to insert/delete (negative values indicate deletion)
   */
  protected function adjustPageBreaks(PHPExcel_Worksheet $pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
  {
    $aBreaks = $pSheet->getBreaks ();
    ($pNumCols > 0 || $pNumRows > 0) ? uksort ( $aBreaks, array (
        'PHPExcel_ReferenceHelper',
        'cellReverseSort' 
    ) ) : uksort ( $aBreaks, array (
        'PHPExcel_ReferenceHelper',
        'cellSort' 
    ) );
    
    foreach ( $aBreaks as $key => $value )
    {
      if (self::cellAddressInDeleteRange ( $key, $beforeRow, $pNumRows, $beforeColumnIndex, $pNumCols ))
      {
        // If we're deleting, then clear any defined breaks that are within the range
        // of rows/columns that we're deleting
        $pSheet->setBreak ( $key, PHPExcel_Worksheet::BREAK_NONE );
      }
      else
      {
        // Otherwise update any affected breaks by inserting a new break at the appropriate point
        // and removing the old affected break
        $newReference = $this->updateCellReference ( $key, $pBefore, $pNumCols, $pNumRows );
        if ($key != $newReference)
        {
          $pSheet->setBreak ( $newReference, $value )
            ->setBreak ( $key, PHPExcel_Worksheet::BREAK_NONE );
        }
      }
    }
  }
  
  /**
   * Update cell comments when inserting/deleting rows/columns
   *
   * @param PHPExcel_Worksheet $pSheet
   *          The worksheet that we're editing
   * @param string $pBefore
   *          Insert/Delete before this cell address (e.g. 'A1')
   * @param integer $beforeColumnIndex
   *          Index number of the column we're inserting/deleting before
   * @param integer $pNumCols
   *          Number of columns to insert/delete (negative values indicate deletion)
   * @param integer $beforeRow
   *          Number of the row we're inserting/deleting before
   * @param integer $pNumRows
   *          Number of rows to insert/delete (negative values indicate deletion)
   */
  protected function adjustComments($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
  {
    $aComments = $pSheet->getComments ();
    $aNewComments = array (); // the new array of all comments
    
    foreach ( $aComments as $key => &$value )
    {
      // Any comments inside a deleted range will be ignored
      if (! self::cellAddressInDeleteRange ( $key, $beforeRow, $pNumRows, $beforeColumnIndex, $pNumCols ))
      {
        // Otherwise build a new array of comments indexed by the adjusted cell reference
        $newReference = $this->updateCellReference ( $key, $pBefore, $pNumCols, $pNumRows );
        $aNewComments [$newReference] = $value;
      }
    }
    // Replace the comments array with the new set of comments
    $pSheet->setComments ( $aNewComments );
  }
  
  /**
   * Update hyperlinks when inserting/deleting rows/columns
   *
   * @param PHPExcel_Worksheet $pSheet
   *          The worksheet that we're editing
   * @param string $pBefore
   *          Insert/Delete before this cell address (e.g. 'A1')
   * @param integer $beforeColumnIndex
   *          Index number of the column we're inserting/deleting before
   * @param integer $pNumCols
   *          Number of columns to insert/delete (negative values indicate deletion)
   * @param integer $beforeRow
   *          Number of the row we're inserting/deleting before
   * @param integer $pNumRows
   *          Number of rows to insert/delete (negative values indicate deletion)
   */
  protected function adjustHyperlinks($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
  {
    $aHyperlinkCollection = $pSheet->getHyperlinkCollection ();
    ($pNumCols > 0 || $pNumRows > 0) ? uksort ( $aHyperlinkCollection, array (
        'PHPExcel_ReferenceHelper',
        'cellReverseSort' 
    ) ) : uksort ( $aHyperlinkCollection, array (
        'PHPExcel_ReferenceHelper',
        'cellSort' 
    ) );
    
    foreach ( $aHyperlinkCollection as $key => $value )
    {
      $newReference = $this->updateCellReference ( $key, $pBefore, $pNumCols, $pNumRows );
      if ($key != $newReference)
      {
        $pSheet->setHyperlink ( $newReference, $value );
        $pSheet->setHyperlink ( $key, null );
      }
    }
  }
  
  /**
   * Update data validations when inserting/deleting rows/columns
   *
   * @param PHPExcel_Worksheet $pSheet
   *          The worksheet that we're editing
   * @param string $pBefore
   *          Insert/Delete before this cell address (e.g. 'A1')
   * @param integer $beforeColumnIndex
   *          Index number of the column we're inserting/deleting before
   * @param integer $pNumCols
   *          Number of columns to insert/delete (negative values indicate deletion)
   * @param integer $beforeRow
   *          Number of the row we're inserting/deleting before
   * @param integer $pNumRows
   *          Number of rows to insert/delete (negative values indicate deletion)
   */
  protected function adjustDataValidations($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
  {
    $aDataValidationCollection = $pSheet->getDataValidationCollection ();
    ($pNumCols > 0 || $pNumRows > 0) ? uksort ( $aDataValidationCollection, array (
        'PHPExcel_ReferenceHelper',
        'cellReverseSort' 
    ) ) : uksort ( $aDataValidationCollection, array (
        'PHPExcel_ReferenceHelper',
        'cellSort' 
    ) );
    
    foreach ( $aDataValidationCollection as $key => $value )
    {
      $newReference = $this->updateCellReference ( $key, $pBefore, $pNumCols, $pNumRows );
      if ($key != $newReference)
      {
        $pSheet->setDataValidation ( $newReference, $value );
        $pSheet->setDataValidation ( $key, null );
      }
    }
  }
  
  /**
   * Update merged cells when inserting/deleting rows/columns
   *
   * @param PHPExcel_Worksheet $pSheet
   *          The worksheet that we're editing
   * @param string $pBefore
   *          Insert/Delete before this cell address (e.g. 'A1')
   * @param integer $beforeColumnIndex
   *          Index number of the column we're inserting/deleting before
   * @param integer $pNumCols
   *          Number of columns to insert/delete (negative values indicate deletion)
   * @param integer $beforeRow
   *          Number of the row we're inserting/deleting before
   * @param integer $pNumRows
   *          Number of rows to insert/delete (negative values indicate deletion)
   */
  protected function adjustMergeCells($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
  {
    $aMergeCells = $pSheet->getMergeCells ();
    $aNewMergeCells = array (); // the new array of all merge cells
    foreach ( $aMergeCells as $key => &$value )
    {
      $newReference = $this->updateCellReference ( $key, $pBefore, $pNumCols, $pNumRows );
      $aNewMergeCells [$newReference] = $newReference;
    }
    $pSheet->setMergeCells ( $aNewMergeCells ); // replace the merge cells array
  }
  
  /**
   * Update protected cells when inserting/deleting rows/columns
   *
   * @param PHPExcel_Worksheet $pSheet
   *          The worksheet that we're editing
   * @param string $pBefore
   *          Insert/Delete before this cell address (e.g. 'A1')
   * @param integer $beforeColumnIndex
   *          Index number of the column we're inserting/deleting before
   * @param integer $pNumCols
   *          Number of columns to insert/delete (negative values indicate deletion)
   * @param integer $beforeRow
   *          Number of the row we're inserting/deleting before
   * @param integer $pNumRows
   *          Number of rows to insert/delete (negative values indicate deletion)
   */
  protected function adjustProtectedCells($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
  {
    $aProtectedCells = $pSheet->getProtectedCells ();
    ($pNumCols > 0 || $pNumRows > 0) ? uksort ( $aProtectedCells, array (
        'PHPExcel_ReferenceHelper',
        'cellReverseSort' 
    ) ) : uksort ( $aProtectedCells, array (
        'PHPExcel_ReferenceHelper',
        'cellSort' 
    ) );
    foreach ( $aProtectedCells as $key => $value )
    {
      $newReference = $this->updateCellReference ( $key, $pBefore, $pNumCols, $pNumRows );
      if ($key != $newReference)
      {
        $pSheet->protectCells ( $newReference, $value, true );
        $pSheet->unprotectCells ( $key );
      }
    }
  }
  
  /**
   * Update column dimensions when inserting/deleting rows/columns
   *
   * @param PHPExcel_Worksheet $pSheet
   *          The worksheet that we're editing
   * @param string $pBefore
   *          Insert/Delete before this cell address (e.g. 'A1')
   * @param integer $beforeColumnIndex
   *          Index number of the column we're inserting/deleting before
   * @param integer $pNumCols
   *          Number of columns to insert/delete (negative values indicate deletion)
   * @param integer $beforeRow
   *          Number of the row we're inserting/deleting before
   * @param integer $pNumRows
   *          Number of rows to insert/delete (negative values indicate deletion)
   */
  protected function adjustColumnDimensions($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
  {
    $aColumnDimensions = array_reverse ( $pSheet->getColumnDimensions (), true );
    if (! empty ( $aColumnDimensions ))
    {
      foreach ( $aColumnDimensions as $objColumnDimension )
      {
        $newReference = $this->updateCellReference ( $objColumnDimension->getColumnIndex () . '1', $pBefore, $pNumCols, $pNumRows );
        list ( $newReference ) = PHPExcel_Cell::coordinateFromString ( $newReference );
        if ($objColumnDimension->getColumnIndex () != $newReference)
        {
          $objColumnDimension->setColumnIndex ( $newReference );
        }
      }
      $pSheet->refreshColumnDimensions ();
    }
  }
  
  /**
   * Update row dimensions when inserting/deleting rows/columns
   *
   * @param PHPExcel_Worksheet $pSheet
   *          The worksheet that we're editing
   * @param string $pBefore
   *          Insert/Delete before this cell address (e.g. 'A1')
   * @param integer $beforeColumnIndex
   *          Index number of the column we're inserting/deleting before
   * @param integer $pNumCols
   *          Number of columns to insert/delete (negative values indicate deletion)
   * @param integer $beforeRow
   *          Number of the row we're inserting/deleting before
   * @param integer $pNumRows
   *          Number of rows to insert/delete (negative values indicate deletion)
   */
  protected function adjustRowDimensions($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
  {
    $aRowDimensions = array_reverse ( $pSheet->getRowDimensions (), true );
    if (! empty ( $aRowDimensions ))
    {
      foreach ( $aRowDimensions as $objRowDimension )
      {
        $newReference = $this->updateCellReference ( 'A' . $objRowDimension->getRowIndex (), $pBefore, $pNumCols, $pNumRows );
        list ( , $newReference ) = PHPExcel_Cell::coordinateFromString ( $newReference );
        if ($objRowDimension->getRowIndex () != $newReference)
        {
          $objRowDimension->setRowIndex ( $newReference );
        }
      }
      $pSheet->refreshRowDimensions ();
      
      $copyDimension = $pSheet->getRowDimension ( $beforeRow - 1 );
      for($i = $beforeRow; $i <= $beforeRow - 1 + $pNumRows; ++ $i)
      {
        $newDimension = $pSheet->getRowDimension ( $i );
        $newDimension->setRowHeight ( $copyDimension->getRowHeight () );
        $newDimension->setVisible ( $copyDimension->getVisible () );
        $newDimension->setOutlineLevel ( $copyDimension->getOutlineLevel () );
        $newDimension->setCollapsed ( $copyDimension->getCollapsed () );
      }
    }
  }
  
  /**
   * Insert a new column or row, updating all possible related data
   *
   * @param string $pBefore
   *          Insert before this cell address (e.g. 'A1')
   * @param integer $pNumCols
   *          Number of columns to insert/delete (negative values indicate deletion)
   * @param integer $pNumRows
   *          Number of rows to insert/delete (negative values indicate deletion)
   * @param PHPExcel_Worksheet $pSheet
   *          The worksheet that we're editing
   * @throws PHPExcel_Exception
   */
  public function insertNewBefore($pBefore = 'A1', $pNumCols = 0, $pNumRows = 0, PHPExcel_Worksheet $pSheet = null)
  {
    $remove = ($pNumCols < 0 || $pNumRows < 0);
    $aCellCollection = $pSheet->getCellCollection ();
    
    // Get coordinates of $pBefore
    $beforeColumn = 'A';
    $beforeRow = 1;
    list ( $beforeColumn, $beforeRow ) = PHPExcel_Cell::coordinateFromString ( $pBefore );
    $beforeColumnIndex = PHPExcel_Cell::columnIndexFromString ( $beforeColumn );
    
    // Clear cells if we are removing columns or rows
    $highestColumn = $pSheet->getHighestColumn ();
    $highestRow = $pSheet->getHighestRow ();
    
    // 1. Clear column strips if we are removing columns
    if ($pNumCols < 0 && $beforeColumnIndex - 2 + $pNumCols > 0)
    {
      for($i = 1; $i <= $highestRow - 1; ++ $i)
      {
        for($j = $beforeColumnIndex - 1 + $pNumCols; $j <= $beforeColumnIndex - 2; ++ $j)
        {
          $coordinate = PHPExcel_Cell::stringFromColumnIndex ( $j ) . $i;
          $pSheet->removeConditionalStyles ( $coordinate );
          if ($pSheet->cellExists ( $coordinate ))
          {
            $pSheet->getCell ( $coordinate )
              ->setValueExplicit ( '', PHPExcel_Cell_DataType::TYPE_NULL );
            $pSheet->getCell ( $coordinate )
              ->setXfIndex ( 0 );
          }
        }
      }
    }
    
    // 2. Clear row strips if we are removing rows
    if ($pNumRows < 0 && $beforeRow - 1 + $pNumRows > 0)
    {
      for($i = $beforeColumnIndex - 1; $i <= PHPExcel_Cell::columnIndexFromString ( $highestColumn ) - 1; ++ $i)
      {
        for($j = $beforeRow + $pNumRows; $j <= $beforeRow - 1; ++ $j)
        {
          $coordinate = PHPExcel_Cell::stringFromColumnIndex ( $i ) . $j;
          $pSheet->removeConditionalStyles ( $coordinate );
          if ($pSheet->cellExists ( $coordinate ))
          {
            $pSheet->getCell ( $coordinate )
              ->setValueExplicit ( '', PHPExcel_Cell_DataType::TYPE_NULL );
            $pSheet->getCell ( $coordinate )
              ->setXfIndex ( 0 );
          }
        }
      }
    }
    
    // Loop through cells, bottom-up, and change cell coordinates
    if ($remove)
    {
      // It's faster to reverse and pop than to use unshift, especially with large cell collections
      $aCellCollection = array_reverse ( $aCellCollection );
    }
    while ( $cellID = array_pop ( $aCellCollection ) )
    {
      $cell = $pSheet->getCell ( $cellID );
      $cellIndex = PHPExcel_Cell::columnIndexFromString ( $cell->getColumn () );
      
      if ($cellIndex - 1 + $pNumCols < 0)
      {
        continue;
      }
      
      // New coordinates
      $newCoordinates = PHPExcel_Cell::stringFromColumnIndex ( $cellIndex - 1 + $pNumCols ) . ($cell->getRow () + $pNumRows);
      
      // Should the cell be updated? Move value and cellXf index from one cell to another.
      if (($cellIndex >= $beforeColumnIndex) && ($cell->getRow () >= $beforeRow))
      {
        // Update cell styles
        $pSheet->getCell ( $newCoordinates )
          ->setXfIndex ( $cell->getXfIndex () );
        
        // Insert this cell at its new location
        if ($cell->getDataType () == PHPExcel_Cell_DataType::TYPE_FORMULA)
        {
          // Formula should be adjusted
          $pSheet->getCell ( $newCoordinates )
            ->setValue ( $this->updateFormulaReferences ( $cell->getValue (), $pBefore, $pNumCols, $pNumRows, $pSheet->getTitle () ) );
        }
        else
        {
          // Formula should not be adjusted
          $pSheet->getCell ( $newCoordinates )
            ->setValue ( $cell->getValue () );
        }
        
        // Clear the original cell
        $pSheet->getCellCacheController ()
          ->deleteCacheData ( $cellID );
      }
      else
      {
        /*
         * We don't need to update styles for rows/columns before our insertion position,
         * but we do still need to adjust any formulae in those cells
         */
        if ($cell->getDataType () == PHPExcel_Cell_DataType::TYPE_FORMULA)
        {
          // Formula should be adjusted
          $cell->setValue ( $this->updateFormulaReferences ( $cell->getValue (), $pBefore, $pNumCols, $pNumRows, $pSheet->getTitle () ) );
        }
      }
    }
    
    // Duplicate styles for the newly inserted cells
    $highestColumn = $pSheet->getHighestColumn ();
    $highestRow = $pSheet->getHighestRow ();
    
    if ($pNumCols > 0 && $beforeColumnIndex - 2 > 0)
    {
      for($i = $beforeRow; $i <= $highestRow - 1; ++ $i)
      {
        // Style
        $coordinate = PHPExcel_Cell::stringFromColumnIndex ( $beforeColumnIndex - 2 ) . $i;
        if ($pSheet->cellExists ( $coordinate ))
        {
          $xfIndex = $pSheet->getCell ( $coordinate )
            ->getXfIndex ();
          $conditionalStyles = $pSheet->conditionalStylesExists ( $coordinate ) ? $pSheet->getConditionalStyles ( $coordinate ) : false;
          for($j = $beforeColumnIndex - 1; $j <= $beforeColumnIndex - 2 + $pNumCols; ++ $j)
          {
            $pSheet->getCellByColumnAndRow ( $j, $i )
              ->setXfIndex ( $xfIndex );
            if ($conditionalStyles)
            {
              $cloned = array ();
              foreach ( $conditionalStyles as $conditionalStyle )
              {
                $cloned [] = clone $conditionalStyle;
              }
              $pSheet->setConditionalStyles ( PHPExcel_Cell::stringFromColumnIndex ( $j ) . $i, $cloned );
            }
          }
        }
      }
    }
    
    if ($pNumRows > 0 && $beforeRow - 1 > 0)
    {
      for($i = $beforeColumnIndex - 1; $i <= PHPExcel_Cell::columnIndexFromString ( $highestColumn ) - 1; ++ $i)
      {
        // Style
        $coordinate = PHPExcel_Cell::stringFromColumnIndex ( $i ) . ($beforeRow - 1);
        if ($pSheet->cellExists ( $coordinate ))
        {
          $xfIndex = $pSheet->getCell ( $coordinate )
            ->getXfIndex ();
          $conditionalStyles = $pSheet->conditionalStylesExists ( $coordinate ) ? $pSheet->getConditionalStyles ( $coordinate ) : false;
          for($j = $beforeRow; $j <= $beforeRow - 1 + $pNumRows; ++ $j)
          {
            $pSheet->getCell ( PHPExcel_Cell::stringFromColumnIndex ( $i ) . $j )
              ->setXfIndex ( $xfIndex );
            if ($conditionalStyles)
            {
              $cloned = array ();
              foreach ( $conditionalStyles as $conditionalStyle )
              {
                $cloned [] = clone $conditionalStyle;
              }
              $pSheet->setConditionalStyles ( PHPExcel_Cell::stringFromColumnIndex ( $i ) . $j, $cloned );
            }
          }
        }
      }
    }
    
    // Update worksheet: column dimensions
    $this->adjustColumnDimensions ( $pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows );
    
    // Update worksheet: row dimensions
    $this->adjustRowDimensions ( $pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows );
    
    // Update worksheet: page breaks
    $this->adjustPageBreaks ( $pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows );
    
    // Update worksheet: comments
    $this->adjustComments ( $pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows );
    
    // Update worksheet: hyperlinks
    $this->adjustHyperlinks ( $pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows );
    
    // Update worksheet: data validations
    $this->adjustDataValidations ( $pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows );
    
    // Update worksheet: merge cells
    $this->adjustMergeCells ( $pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows );
    
    // Update worksheet: protected cells
    $this->adjustProtectedCells ( $pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows );
    
    // Update worksheet: autofilter
    $autoFilter = $pSheet->getAutoFilter ();
    $autoFilterRange = $autoFilter->getRange ();
    if (! empty ( $autoFilterRange ))
    {
      if ($pNumCols != 0)
      {
        $autoFilterColumns = array_keys ( $autoFilter->getColumns () );
        if (count ( $autoFilterColumns ) > 0)
        {
          sscanf ( $pBefore, '%[A-Z]%d', $column, $row );
          $columnIndex = PHPExcel_Cell::columnIndexFromString ( $column );
          list ( $rangeStart, $rangeEnd ) = PHPExcel_Cell::rangeBoundaries ( $autoFilterRange );
          if ($columnIndex <= $rangeEnd [0])
          {
            if ($pNumCols < 0)
            {
              // If we're actually deleting any columns that fall within the autofilter range,
              // then we delete any rules for those columns
              $deleteColumn = $columnIndex + $pNumCols - 1;
              $deleteCount = abs ( $pNumCols );
              for($i = 1; $i <= $deleteCount; ++ $i)
              {
                if (in_array ( PHPExcel_Cell::stringFromColumnIndex ( $deleteColumn ), $autoFilterColumns ))
                {
                  $autoFilter->clearColumn ( PHPExcel_Cell::stringFromColumnIndex ( $deleteColumn ) );
                }
                ++ $deleteColumn;
              }
            }
            $startCol = ($columnIndex > $rangeStart [0]) ? $columnIndex : $rangeStart [0];
            
            // Shuffle columns in autofilter range
            if ($pNumCols > 0)
            {
              // For insert, we shuffle from end to beginning to avoid overwriting
              $startColID = PHPExcel_Cell::stringFromColumnIndex ( $startCol - 1 );
              $toColID = PHPExcel_Cell::stringFromColumnIndex ( $startCol + $pNumCols - 1 );
              $endColID = PHPExcel_Cell::stringFromColumnIndex ( $rangeEnd [0] );
              
              $startColRef = $startCol;
              $endColRef = $rangeEnd [0];
              $toColRef = $rangeEnd [0] + $pNumCols;
              
              do
              {
                $autoFilter->shiftColumn ( PHPExcel_Cell::stringFromColumnIndex ( $endColRef - 1 ), PHPExcel_Cell::stringFromColumnIndex ( $toColRef - 1 ) );
                -- $endColRef;
                -- $toColRef;
              }
              while ( $startColRef <= $endColRef );
            }
            else
            {
              // For delete, we shuffle from beginning to end to avoid overwriting
              $startColID = PHPExcel_Cell::stringFromColumnIndex ( $startCol - 1 );
              $toColID = PHPExcel_Cell::stringFromColumnIndex ( $startCol + $pNumCols - 1 );
              $endColID = PHPExcel_Cell::stringFromColumnIndex ( $rangeEnd [0] );
              do
              {
                $autoFilter->shiftColumn ( $startColID, $toColID );
                ++ $startColID;
                ++ $toColID;
              }
              while ( $startColID != $endColID );
            }
          }
        }
      }
      $pSheet->setAutoFilter ( $this->updateCellReference ( $autoFilterRange, $pBefore, $pNumCols, $pNumRows ) );
    }
    
    // Update worksheet: freeze pane
    if ($pSheet->getFreezePane () != '')
    {
      $pSheet->freezePane ( $this->updateCellReference ( $pSheet->getFreezePane (), $pBefore, $pNumCols, $pNumRows ) );
    }
    
    // Page setup
    if ($pSheet->getPageSetup ()
      ->isPrintAreaSet ())
    {
      $pSheet->getPageSetup ()
        ->setPrintArea ( $this->updateCellReference ( $pSheet->getPageSetup ()
        ->getPrintArea (), $pBefore, $pNumCols, $pNumRows ) );
    }
    
    // Update worksheet: drawings
    $aDrawings = $pSheet->getDrawingCollection ();
    foreach ( $aDrawings as $objDrawing )
    {
      $newReference = $this->updateCellReference ( $objDrawing->getCoordinates (), $pBefore, $pNumCols, $pNumRows );
      if ($objDrawing->getCoordinates () != $newReference)
      {
        $objDrawing->setCoordinates ( $newReference );
      }
    }
    
    // Update workbook: named ranges
    if (count ( $pSheet->getParent ()
      ->getNamedRanges () ) > 0)
    {
      foreach ( $pSheet->getParent ()
        ->getNamedRanges () as $namedRange )
      {
        if ($namedRange->getWorksheet ()
          ->getHashCode () == $pSheet->getHashCode ())
        {
          $namedRange->setRange ( $this->updateCellReference ( $namedRange->getRange (), $pBefore, $pNumCols, $pNumRows ) );
        }
      }
    }
    
    // Garbage collect
    $pSheet->garbageCollect ();
  }
  
  /**
   * Update references within formulas
   *
   * @param string $pFormula
   *          Formula to update
   * @param int $pBefore
   *          Insert before this one
   * @param int $pNumCols
   *          Number of columns to insert
   * @param int $pNumRows
   *          Number of rows to insert
   * @param string $sheetName
   *          Worksheet name/title
   * @return string Updated formula
   * @throws PHPExcel_Exception
   */
  public function updateFormulaReferences($pFormula = '', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0, $sheetName = '')
  {
    // Update cell references in the formula
    $formulaBlocks = explode ( '"', $pFormula );
    $i = false;
    foreach ( $formulaBlocks as &$formulaBlock )
    {
      // Ignore blocks that were enclosed in quotes (alternating entries in the $formulaBlocks array after the explode)
      if ($i = ! $i)
      {
        $adjustCount = 0;
        $newCellTokens = $cellTokens = array ();
        // Search for row ranges (e.g. 'Sheet1'!3:5 or 3:5) with or without $ absolutes (e.g. $3:5)
        $matchCount = preg_match_all ( '/' . self::REFHELPER_REGEXP_ROWRANGE . '/i', ' ' . $formulaBlock . ' ', $matches, PREG_SET_ORDER );
        if ($matchCount > 0)
        {
          foreach ( $matches as $match )
          {
            $fromString = ($match [2] > '') ? $match [2] . '!' : '';
            $fromString .= $match [3] . ':' . $match [4];
            $modified3 = substr ( $this->updateCellReference ( '$A' . $match [3], $pBefore, $pNumCols, $pNumRows ), 2 );
            $modified4 = substr ( $this->updateCellReference ( '$A' . $match [4], $pBefore, $pNumCols, $pNumRows ), 2 );
            
            if ($match [3] . ':' . $match [4] !== $modified3 . ':' . $modified4)
            {
              if (($match [2] == '') || (trim ( $match [2], "'" ) == $sheetName))
              {
                $toString = ($match [2] > '') ? $match [2] . '!' : '';
                $toString .= $modified3 . ':' . $modified4;
                // 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
                $column = 100000;
                $row = 10000000 + trim ( $match [3], '$' );
                $cellIndex = $column . $row;
                
                $newCellTokens [$cellIndex] = preg_quote ( $toString );
                $cellTokens [$cellIndex] = '/(?<!\d\$\!)' . preg_quote ( $fromString ) . '(?!\d)/i';
                ++ $adjustCount;
              }
            }
          }
        }
        // Search for column ranges (e.g. 'Sheet1'!C:E or C:E) with or without $ absolutes (e.g. $C:E)
        $matchCount = preg_match_all ( '/' . self::REFHELPER_REGEXP_COLRANGE . '/i', ' ' . $formulaBlock . ' ', $matches, PREG_SET_ORDER );
        if ($matchCount > 0)
        {
          foreach ( $matches as $match )
          {
            $fromString = ($match [2] > '') ? $match [2] . '!' : '';
            $fromString .= $match [3] . ':' . $match [4];
            $modified3 = substr ( $this->updateCellReference ( $match [3] . '$1', $pBefore, $pNumCols, $pNumRows ), 0, - 2 );
            $modified4 = substr ( $this->updateCellReference ( $match [4] . '$1', $pBefore, $pNumCols, $pNumRows ), 0, - 2 );
            
            if ($match [3] . ':' . $match [4] !== $modified3 . ':' . $modified4)
            {
              if (($match [2] == '') || (trim ( $match [2], "'" ) == $sheetName))
              {
                $toString = ($match [2] > '') ? $match [2] . '!' : '';
                $toString .= $modified3 . ':' . $modified4;
                // 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
                $column = PHPExcel_Cell::columnIndexFromString ( trim ( $match [3], '$' ) ) + 100000;
                $row = 10000000;
                $cellIndex = $column . $row;
                
                $newCellTokens [$cellIndex] = preg_quote ( $toString );
                $cellTokens [$cellIndex] = '/(?<![A-Z\$\!])' . preg_quote ( $fromString ) . '(?![A-Z])/i';
                ++ $adjustCount;
              }
            }
          }
        }
        // Search for cell ranges (e.g. 'Sheet1'!A3:C5 or A3:C5) with or without $ absolutes (e.g. $A1:C$5)
        $matchCount = preg_match_all ( '/' . self::REFHELPER_REGEXP_CELLRANGE . '/i', ' ' . $formulaBlock . ' ', $matches, PREG_SET_ORDER );
        if ($matchCount > 0)
        {
          foreach ( $matches as $match )
          {
            $fromString = ($match [2] > '') ? $match [2] . '!' : '';
            $fromString .= $match [3] . ':' . $match [4];
            $modified3 = $this->updateCellReference ( $match [3], $pBefore, $pNumCols, $pNumRows );
            $modified4 = $this->updateCellReference ( $match [4], $pBefore, $pNumCols, $pNumRows );
            
            if ($match [3] . $match [4] !== $modified3 . $modified4)
            {
              if (($match [2] == '') || (trim ( $match [2], "'" ) == $sheetName))
              {
                $toString = ($match [2] > '') ? $match [2] . '!' : '';
                $toString .= $modified3 . ':' . $modified4;
                list ( $column, $row ) = PHPExcel_Cell::coordinateFromString ( $match [3] );
                // 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
                $column = PHPExcel_Cell::columnIndexFromString ( trim ( $column, '$' ) ) + 100000;
                $row = trim ( $row, '$' ) + 10000000;
                $cellIndex = $column . $row;
                
                $newCellTokens [$cellIndex] = preg_quote ( $toString );
                $cellTokens [$cellIndex] = '/(?<![A-Z]\$\!)' . preg_quote ( $fromString ) . '(?!\d)/i';
                ++ $adjustCount;
              }
            }
          }
        }
        // Search for cell references (e.g. 'Sheet1'!A3 or C5) with or without $ absolutes (e.g. $A1 or C$5)
        $matchCount = preg_match_all ( '/' . self::REFHELPER_REGEXP_CELLREF . '/i', ' ' . $formulaBlock . ' ', $matches, PREG_SET_ORDER );
        
        if ($matchCount > 0)
        {
          foreach ( $matches as $match )
          {
            $fromString = ($match [2] > '') ? $match [2] . '!' : '';
            $fromString .= $match [3];
            
            $modified3 = $this->updateCellReference ( $match [3], $pBefore, $pNumCols, $pNumRows );
            if ($match [3] !== $modified3)
            {
              if (($match [2] == '') || (trim ( $match [2], "'" ) == $sheetName))
              {
                $toString = ($match [2] > '') ? $match [2] . '!' : '';
                $toString .= $modified3;
                list ( $column, $row ) = PHPExcel_Cell::coordinateFromString ( $match [3] );
                // 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
                $column = PHPExcel_Cell::columnIndexFromString ( trim ( $column, '$' ) ) + 100000;
                $row = trim ( $row, '$' ) + 10000000;
                $cellIndex = $row . $column;
                
                $newCellTokens [$cellIndex] = preg_quote ( $toString );
                $cellTokens [$cellIndex] = '/(?<![A-Z\$\!])' . preg_quote ( $fromString ) . '(?!\d)/i';
                ++ $adjustCount;
              }
            }
          }
        }
        if ($adjustCount > 0)
        {
          if ($pNumCols > 0 || $pNumRows > 0)
          {
            krsort ( $cellTokens );
            krsort ( $newCellTokens );
          }
          else
          {
            ksort ( $cellTokens );
            ksort ( $newCellTokens );
          } // Update cell references in the formula
          $formulaBlock = str_replace ( '\\', '', preg_replace ( $cellTokens, $newCellTokens, $formulaBlock ) );
        }
      }
    }
    unset ( $formulaBlock );
    
    // Then rebuild the formula string
    return implode ( '"', $formulaBlocks );
  }
  
  /**
   * Update cell reference
   *
   * @param string $pCellRange
   *          Cell range
   * @param int $pBefore
   *          Insert before this one
   * @param int $pNumCols
   *          Number of columns to increment
   * @param int $pNumRows
   *          Number of rows to increment
   * @return string Updated cell range
   * @throws PHPExcel_Exception
   */
  public function updateCellReference($pCellRange = 'A1', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0)
  {
    // Is it in another worksheet? Will not have to update anything.
    if (strpos ( $pCellRange, "!" ) !== false)
    {
      return $pCellRange;
      // Is it a range or a single cell?
    }
    elseif (strpos ( $pCellRange, ':' ) === false && strpos ( $pCellRange, ',' ) === false)
    {
      // Single cell
      return $this->updateSingleCellReference ( $pCellRange, $pBefore, $pNumCols, $pNumRows );
    }
    elseif (strpos ( $pCellRange, ':' ) !== false || strpos ( $pCellRange, ',' ) !== false)
    {
      // Range
      return $this->updateCellRange ( $pCellRange, $pBefore, $pNumCols, $pNumRows );
    }
    else
    {
      // Return original
      return $pCellRange;
    }
  }
  
  /**
   * Update named formulas (i.e.
   * containing worksheet references / named ranges)
   *
   * @param PHPExcel $pPhpExcel
   *          Object to update
   * @param string $oldName
   *          Old name (name to replace)
   * @param string $newName
   *          New name
   */
  public function updateNamedFormulas(PHPExcel $pPhpExcel, $oldName = '', $newName = '')
  {
    if ($oldName == '')
    {
      return;
    }
    
    foreach ( $pPhpExcel->getWorksheetIterator () as $sheet )
    {
      foreach ( $sheet->getCellCollection ( false ) as $cellID )
      {
        $cell = $sheet->getCell ( $cellID );
        if (($cell !== null) && ($cell->getDataType () == PHPExcel_Cell_DataType::TYPE_FORMULA))
        {
          $formula = $cell->getValue ();
          if (strpos ( $formula, $oldName ) !== false)
          {
            $formula = str_replace ( "'" . $oldName . "'!", "'" . $newName . "'!", $formula );
            $formula = str_replace ( $oldName . "!", $newName . "!", $formula );
            $cell->setValueExplicit ( $formula, PHPExcel_Cell_DataType::TYPE_FORMULA );
          }
        }
      }
    }
  }
  
  /**
   * Update cell range
   *
   * @param string $pCellRange
   *          Cell range (e.g. 'B2:D4', 'B:C' or '2:3')
   * @param int $pBefore
   *          Insert before this one
   * @param int $pNumCols
   *          Number of columns to increment
   * @param int $pNumRows
   *          Number of rows to increment
   * @return string Updated cell range
   * @throws PHPExcel_Exception
   */
  private function updateCellRange($pCellRange = 'A1:A1', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0)
  {
    if (strpos ( $pCellRange, ':' ) !== false || strpos ( $pCellRange, ',' ) !== false)
    {
      // Update range
      $range = PHPExcel_Cell::splitRange ( $pCellRange );
      $ic = count ( $range );
      for($i = 0; $i < $ic; ++ $i)
      {
        $jc = count ( $range [$i] );
        for($j = 0; $j < $jc; ++ $j)
        {
          if (ctype_alpha ( $range [$i] [$j] ))
          {
            $r = PHPExcel_Cell::coordinateFromString ( $this->updateSingleCellReference ( $range [$i] [$j] . '1', $pBefore, $pNumCols, $pNumRows ) );
            $range [$i] [$j] = $r [0];
          }
          elseif (ctype_digit ( $range [$i] [$j] ))
          {
            $r = PHPExcel_Cell::coordinateFromString ( $this->updateSingleCellReference ( 'A' . $range [$i] [$j], $pBefore, $pNumCols, $pNumRows ) );
            $range [$i] [$j] = $r [1];
          }
          else
          {
            $range [$i] [$j] = $this->updateSingleCellReference ( $range [$i] [$j], $pBefore, $pNumCols, $pNumRows );
          }
        }
      }
      
      // Recreate range string
      return PHPExcel_Cell::buildRange ( $range );
    }
    else
    {
      throw new PHPExcel_Exception ( "Only cell ranges may be passed to this method." );
    }
  }
  
  /**
   * Update single cell reference
   *
   * @param string $pCellReference
   *          Single cell reference
   * @param int $pBefore
   *          Insert before this one
   * @param int $pNumCols
   *          Number of columns to increment
   * @param int $pNumRows
   *          Number of rows to increment
   * @return string Updated cell reference
   * @throws PHPExcel_Exception
   */
  private function updateSingleCellReference($pCellReference = 'A1', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0)
  {
    if (strpos ( $pCellReference, ':' ) === false && strpos ( $pCellReference, ',' ) === false)
    {
      // Get coordinates of $pBefore
      list ( $beforeColumn, $beforeRow ) = PHPExcel_Cell::coordinateFromString ( $pBefore );
      
      // Get coordinates of $pCellReference
      list ( $newColumn, $newRow ) = PHPExcel_Cell::coordinateFromString ( $pCellReference );
      
      // Verify which parts should be updated
      $updateColumn = (($newColumn {0} != '$') && ($beforeColumn {0} != '$') && (PHPExcel_Cell::columnIndexFromString ( $newColumn ) >= PHPExcel_Cell::columnIndexFromString ( $beforeColumn )));
      $updateRow = (($newRow {0} != '$') && ($beforeRow {0} != '$') && $newRow >= $beforeRow);
      
      // Create new column reference
      if ($updateColumn)
      {
        $newColumn = PHPExcel_Cell::stringFromColumnIndex ( PHPExcel_Cell::columnIndexFromString ( $newColumn ) - 1 + $pNumCols );
      }
      
      // Create new row reference
      if ($updateRow)
      {
        $newRow = $newRow + $pNumRows;
      }
      
      // Return new reference
      return $newColumn . $newRow;
    }
    else
    {
      throw new PHPExcel_Exception ( "Only single cell references may be passed to this method." );
    }
  }
  
  /**
   * __clone implementation.
   * Cloning should not be allowed in a Singleton!
   *
   * @throws PHPExcel_Exception
   */
  final public function __clone()
  {
    throw new PHPExcel_Exception ( "Cloning a Singleton is not allowed!" );
  }
}
