ReferenceHelper.php | searchcode

/common/libraries/plugin/phpexcel/PHPExcel/ReferenceHelper.php

https://bitbucket.org/renaatdemuynck/chamilo
PHP | 719 lines | 569 code | 46 blank | 104 comment | 71 complexity | 6ca686cc138dcdff6f1535666d646798 MD5 | raw file
Possible License(s): BSD-3-Clause, LGPL-2.1, LGPL-3.0, GPL-3.0, MIT, GPL-2.0

<?php

/**
 * PHPExcel
 *
 * Copyright (c) 2006 - 2011 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 - 2011 PHPExcel (http://www.codeplex.com/PHPExcel)
 * @license	http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt	LGPL
 * @version	1.7.6, 2011-02-27
 */



/**
 * PHPExcel_ReferenceHelper (Singleton)
 *
 * @category   PHPExcel
 * @package	PHPExcel
 * @copyright  Copyright (c) 2006 - 2011 PHPExcel (http://www.codeplex.com/PHPExcel)
 */

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) || is_null(self :: $_instance))

        {

            self :: $_instance = new PHPExcel_ReferenceHelper();

        }

        

        return self :: $_instance;

    }



    /**
     * Create a new PHPExcel_ReferenceHelper
     */

    protected function __construct()

    {

    }



    /**
     * Insert a new column, updating all possible related data
     *
     * @param	int	$pBefore	Insert before this one
     * @param	int	$pNumCols	Number of columns to insert
     * @param	int	$pNumRows	Number of rows to insert
     * @throws	Exception
     */

    public function insertNewBefore($pBefore = 'A1', $pNumCols = 0, $pNumRows = 0, PHPExcel_Worksheet $pSheet = null)

    {

        $aCellCollection = $pSheet->getCellCollection();

        

        // Get coordinates of $pBefore
        $beforeColumn = 'A';

        $beforeRow = 1;

        list($beforeColumn, $beforeRow) = PHPExcel_Cell :: coordinateFromString($pBefore);

        

        // 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 && PHPExcel_Cell :: columnIndexFromString($beforeColumn) - 2 + $pNumCols > 0)

        {

            for($i = 1; $i <= $highestRow - 1; ++ $i)

            {

                for($j = PHPExcel_Cell :: columnIndexFromString($beforeColumn) - 1 + $pNumCols; $j <= PHPExcel_Cell :: columnIndexFromString($beforeColumn) - 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 = PHPExcel_Cell :: columnIndexFromString($beforeColumn) - 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
        while (($cellID = ($pNumCols < 0 || $pNumRows < 0) ? array_shift($aCellCollection) : array_pop($aCellCollection)))

        {

            $cell = $pSheet->getCell($cellID);

            

            // New coordinates
            $newCoordinates = PHPExcel_Cell :: stringFromColumnIndex(PHPExcel_Cell :: columnIndexFromString($cell->getColumn()) - 1 + $pNumCols) . ($cell->getRow() + $pNumRows);

            

            // Should the cell be updated? Move value and cellXf index from one cell to another.
            if ((PHPExcel_Cell :: columnIndexFromString($cell->getColumn()) >= PHPExcel_Cell :: columnIndexFromString($beforeColumn)) && ($cell->getRow() >= $beforeRow))

            {

                

                // Update cell styles
                $pSheet->getCell($newCoordinates)->setXfIndex($cell->getXfIndex());

                $cell->setXfIndex(0);

                

                // 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->getCell($cell->getCoordinate())->setValue('');

            

            }

            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 && PHPExcel_Cell :: columnIndexFromString($beforeColumn) - 2 > 0)

        {

            for($i = $beforeRow; $i <= $highestRow - 1; ++ $i)

            {

                

                // Style
                $coordinate = PHPExcel_Cell :: stringFromColumnIndex(PHPExcel_Cell :: columnIndexFromString($beforeColumn) - 2) . $i;

                if ($pSheet->cellExists($coordinate))

                {

                    $xfIndex = $pSheet->getCell($coordinate)->getXfIndex();

                    $conditionalStyles = $pSheet->conditionalStylesExists($coordinate) ? $pSheet->getConditionalStyles($coordinate) : false;

                    for($j = PHPExcel_Cell :: columnIndexFromString($beforeColumn) - 1; $j <= PHPExcel_Cell :: columnIndexFromString($beforeColumn) - 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 = PHPExcel_Cell :: columnIndexFromString($beforeColumn) - 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
        $aColumnDimensions = array_reverse($pSheet->getColumnDimensions(), true);

        if (count($aColumnDimensions) > 0)

        {

            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 worksheet: row dimensions
        $aRowDimensions = array_reverse($pSheet->getRowDimensions(), true);

        if (count($aRowDimensions) > 0)

        {

            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());

            }

        }

        

        // Update worksheet: breaks
        $aBreaks = array_reverse($pSheet->getBreaks(), true);

        foreach ($aBreaks as $key => $value)

        {

            $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);

            if ($key != $newReference)

            {

                $pSheet->setBreak($newReference, $value);

                $pSheet->setBreak($key, PHPExcel_Worksheet :: BREAK_NONE);

            }

        }

        

        // Update worksheet: comments
        $aComments = $pSheet->getComments();

        $aNewComments = array(); // the new array of all comments
        foreach ($aComments as $key => &$value)

        {

            $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);

            $aNewComments[$newReference] = $value;

        }

        $pSheet->setComments($aNewComments); // replace the comments array
        



        // Update worksheet: hyperlinks
        $aHyperlinkCollection = array_reverse($pSheet->getHyperlinkCollection(), true);

        foreach ($aHyperlinkCollection as $key => $value)

        {

            $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);

            if ($key != $newReference)

            {

                $pSheet->setHyperlink($newReference, $value);

                $pSheet->setHyperlink($key, null);

            }

        }

        

        // Update worksheet: data validations
        $aDataValidationCollection = array_reverse($pSheet->getDataValidationCollection(), true);

        foreach ($aDataValidationCollection as $key => $value)

        {

            $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);

            if ($key != $newReference)

            {

                $pSheet->setDataValidation($newReference, $value);

                $pSheet->setDataValidation($key, null);

            }

        }

        

        // Update worksheet: merge cells
        $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 worksheet: protected cells
        $aProtectedCells = array_reverse($pSheet->getProtectedCells(), true);

        foreach ($aProtectedCells as $key => $value)

        {

            $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);

            if ($key != $newReference)

            {

                $pSheet->protectCells($newReference, $value, true);

                $pSheet->unprotectCells($key);

            }

        }

        

        // Update worksheet: autofilter
        if ($pSheet->getAutoFilter() != '')

        {

            $pSheet->setAutoFilter($this->updateCellReference($pSheet->getAutoFilter(), $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
     * @return	string	Updated formula
     * @throws	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 = $column . $row;

                                

                                $newCellTokens[$cellIndex] = preg_quote($toString);

                                $cellTokens[$cellIndex] = '/(?<![A-Z])' . preg_quote($fromString) . '(?!\d)/i';

                                ++ $adjustCount;

                            }

                        }

                    }

                }

                if ($adjustCount > 0)

                {

                    krsort($cellTokens);

                    krsort($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	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 (! is_null($cell) && $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	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 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	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 Exception("Only single cell references may be passed to this method.");

        }

    }



    /**
     * __clone implementation. Cloning should not be allowed in a Singleton!
     *
     * @throws	Exception
     */

    public final function __clone()

    {

        throw new Exception("Cloning a Singleton is not allowed!");

    }

}