ILIAS  eassessment Revision 61809
 All Data Structures Namespaces Files Functions Variables Groups Pages
Public Member Functions | Static Public Member Functions | Data Fields | Protected Member Functions | Private Member Functions | Static Private Attributes
PHPExcel_ReferenceHelper Class Reference
+ Collaboration diagram for PHPExcel_ReferenceHelper:

Public Member Functions

 insertNewBefore ($pBefore= 'A1', $pNumCols=0, $pNumRows=0, PHPExcel_Worksheet $pSheet=null)
 Insert a new column, updating all possible related data.
 updateFormulaReferences ($pFormula= '', $pBefore= 'A1', $pNumCols=0, $pNumRows=0, $sheetName= '')
 Update references within formulas.
 updateCellReference ($pCellRange= 'A1', $pBefore= 'A1', $pNumCols=0, $pNumRows=0)
 Update cell reference.
 updateNamedFormulas (PHPExcel $pPhpExcel, $oldName= '', $newName= '')
 Update named formulas (i.e.
 __clone ()
 __clone implementation.

Static Public Member Functions

static getInstance ()
 Get an instance of this class.

Data Fields

const REFHELPER_REGEXP_CELLREF = '((\w*|\'[^!]*\')!)?(?<![:a-z\$])(\$?[a-z]{1,3}\$?\d+)(?=[^:!\d\'])'
 Constants.
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})'

Protected Member Functions

 __construct ()
 Create a new PHPExcel_ReferenceHelper.

Private Member Functions

 _updateCellRange ($pCellRange= 'A1:A1', $pBefore= 'A1', $pNumCols=0, $pNumRows=0)
 Update cell range.
 _updateSingleCellReference ($pCellReference= 'A1', $pBefore= 'A1', $pNumCols=0, $pNumRows=0)
 Update single cell reference.

Static Private Attributes

static $_instance

Detailed Description

Definition at line 36 of file ReferenceHelper.php.

Constructor & Destructor Documentation

PHPExcel_ReferenceHelper::__construct ( )
protected

Create a new PHPExcel_ReferenceHelper.

Definition at line 68 of file ReferenceHelper.php.

{
}

Member Function Documentation

PHPExcel_ReferenceHelper::__clone ( )
final

__clone implementation.

Cloning should not be allowed in a Singleton!

Exceptions
Exception

Definition at line 629 of file ReferenceHelper.php.

{
throw new Exception("Cloning a Singleton is not allowed!");
}
PHPExcel_ReferenceHelper::_updateCellRange (   $pCellRange = 'A1:A1',
  $pBefore = 'A1',
  $pNumCols = 0,
  $pNumRows = 0 
)
private

Update cell range.

Parameters
string$pCellRangeCell range (e.g. 'B2:D4', 'B:C' or '2:3')
int$pBeforeInsert before this one
int$pNumColsNumber of columns to increment
int$pNumRowsNumber of rows to increment
Returns
string Updated cell range
Exceptions
Exception

Definition at line 555 of file ReferenceHelper.php.

References _updateSingleCellReference(), PHPExcel_Cell\buildRange(), PHPExcel_Cell\coordinateFromString(), and PHPExcel_Cell\splitRange().

Referenced by updateCellReference().

: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.");
}
}

+ Here is the call graph for this function:

+ Here is the caller graph for this function:

PHPExcel_ReferenceHelper::_updateSingleCellReference (   $pCellReference = 'A1',
  $pBefore = 'A1',
  $pNumCols = 0,
  $pNumRows = 0 
)
private

Update single cell reference.

Parameters
string$pCellReferenceSingle cell reference
int$pBeforeInsert before this one
int$pNumColsNumber of columns to increment
int$pNumRowsNumber of rows to increment
Returns
string Updated cell reference
Exceptions
Exception

Definition at line 592 of file ReferenceHelper.php.

References PHPExcel_Cell\columnIndexFromString(), PHPExcel_Cell\coordinateFromString(), and PHPExcel_Cell\stringFromColumnIndex().

Referenced by _updateCellRange(), and updateCellReference().

{
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.");
}
}

+ Here is the call graph for this function:

+ Here is the caller graph for this function:

static PHPExcel_ReferenceHelper::getInstance ( )
static

Get an instance of this class.

Returns
PHPExcel_ReferenceHelper

Definition at line 57 of file ReferenceHelper.php.

References $_instance.

Referenced by PHPExcel_Reader_Excel2007\__construct(), PHPExcel_Reader_Gnumeric\__construct(), PHPExcel_Worksheet\insertNewColumnBefore(), PHPExcel_Worksheet\insertNewRowBefore(), PHPExcel_Worksheet\removeColumn(), PHPExcel_Worksheet\removeRow(), PHPExcel_NamedRange\setName(), and PHPExcel_Worksheet\setTitle().

{
if (!isset(self::$_instance) || is_null(self::$_instance)) {
self::$_instance = new PHPExcel_ReferenceHelper();
}
return self::$_instance;
}

+ Here is the caller graph for this function:

PHPExcel_ReferenceHelper::insertNewBefore (   $pBefore = 'A1',
  $pNumCols = 0,
  $pNumRows = 0,
PHPExcel_Worksheet  $pSheet = null 
)

Insert a new column, updating all possible related data.

Parameters
int$pBeforeInsert before this one
int$pNumColsNumber of columns to insert
int$pNumRowsNumber of rows to insert
Exceptions
Exception

Definition at line 79 of file ReferenceHelper.php.

References PHPExcel_Worksheet\BREAK_NONE, PHPExcel_Cell\columnIndexFromString(), PHPExcel_Cell\coordinateFromString(), PHPExcel_Cell\stringFromColumnIndex(), PHPExcel_Cell_DataType\TYPE_FORMULA, PHPExcel_Cell_DataType\TYPE_NULL, updateCellReference(), and updateFormulaReferences().

{
$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();
}

+ Here is the call graph for this function:

PHPExcel_ReferenceHelper::updateCellReference (   $pCellRange = 'A1',
  $pBefore = 'A1',
  $pNumCols = 0,
  $pNumRows = 0 
)

Update cell reference.

Parameters
string$pCellRangeCell range
int$pBeforeInsert before this one
int$pNumColsNumber of columns to increment
int$pNumRowsNumber of rows to increment
Returns
string Updated cell range
Exceptions
Exception

Definition at line 501 of file ReferenceHelper.php.

References _updateCellRange(), and _updateSingleCellReference().

Referenced by insertNewBefore(), and updateFormulaReferences().

{
// 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;
}
}

+ Here is the call graph for this function:

+ Here is the caller graph for this function:

PHPExcel_ReferenceHelper::updateFormulaReferences (   $pFormula = '',
  $pBefore = 'A1',
  $pNumCols = 0,
  $pNumRows = 0,
  $sheetName = '' 
)

Update references within formulas.

Parameters
string$pFormulaFormula to update
int$pBeforeInsert before this one
int$pNumColsNumber of columns to insert
int$pNumRowsNumber of rows to insert
Returns
string Updated formula
Exceptions
Exception

Definition at line 367 of file ReferenceHelper.php.

References $row, PHPExcel_Cell\columnIndexFromString(), PHPExcel_Cell\coordinateFromString(), and updateCellReference().

Referenced by insertNewBefore().

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

+ Here is the call graph for this function:

+ Here is the caller graph for this function:

PHPExcel_ReferenceHelper::updateNamedFormulas ( PHPExcel  $pPhpExcel,
  $oldName = '',
  $newName = '' 
)

Update named formulas (i.e.

containing worksheet references / named ranges)

Parameters
PHPExcel$pPhpExcelObject to update
string$oldNameOld name (name to replace)
string$newNameNew name

Definition at line 525 of file ReferenceHelper.php.

References PHPExcel\getWorksheetIterator(), and PHPExcel_Cell_DataType\TYPE_FORMULA.

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

+ Here is the call graph for this function:

Field Documentation

PHPExcel_ReferenceHelper::$_instance
staticprivate

Definition at line 50 of file ReferenceHelper.php.

Referenced by getInstance().

const PHPExcel_ReferenceHelper::REFHELPER_REGEXP_CELLRANGE = '((\w*|\'[^!]*\')!)?(\$?[a-z]{1,3}\$?\d+):(\$?[a-z]{1,3}\$?\d+)'

Definition at line 41 of file ReferenceHelper.php.

const PHPExcel_ReferenceHelper::REFHELPER_REGEXP_CELLREF = '((\w*|\'[^!]*\')!)?(?<![:a-z\$])(\$?[a-z]{1,3}\$?\d+)(?=[^:!\d\'])'

Constants.

Regular Expressions

Definition at line 40 of file ReferenceHelper.php.

const PHPExcel_ReferenceHelper::REFHELPER_REGEXP_COLRANGE = '((\w*|\'[^!]*\')!)?(\$?[a-z]{1,3}):(\$?[a-z]{1,3})'

Definition at line 43 of file ReferenceHelper.php.

const PHPExcel_ReferenceHelper::REFHELPER_REGEXP_ROWRANGE = '((\w*|\'[^!]*\')!)?(\$?\d+):(\$?\d+)'

Definition at line 42 of file ReferenceHelper.php.

The documentation for this class was generated from the following file: