public function PHPExcel_ReferenceHelper::updateFormulaReferences in Loft Data Grids 7.2

Same name and namespace in other branches

1. 6.2 vendor/phpoffice/phpexcel/Classes/PHPExcel/ReferenceHelper.php \PHPExcel_ReferenceHelper::updateFormulaReferences()

* Update references within formulas * *

Parameters

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

1 call to PHPExcel_ReferenceHelper::updateFormulaReferences()

PHPExcel_ReferenceHelper::insertNewBefore in vendor/phpoffice/phpexcel/Classes/PHPExcel/ReferenceHelper.php

* Insert a new column or row, updating all possible related data * *

File

vendor/phpoffice/phpexcel/Classes/PHPExcel/ReferenceHelper.php, line 653

Class

PHPExcel_ReferenceHelper

PHPExcel_ReferenceHelper (Singleton)

Code

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