Vendor/phpspreadsheet/phpoffice/phpspreadsheet/src/PhpSpreadsheet/ReferenceHelper.php
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 = [];
                //    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 = Coordinate::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) = Coordinate::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 = Coordinate::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) = Coordinate::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 = Coordinate::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);
    }
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 = [];
                //    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 = Coordinate::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) = Coordinate::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 = Coordinate::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) = Coordinate::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 = Coordinate::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);
    }
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 = [ ] ; //    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 = Coordinate :: 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 ) = Coordinate :: 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 = Coordinate :: 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 ) = Coordinate :: 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 = Coordinate :: 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 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

@throws Exception

@return string Updated formula
Update references within formulas .