ILIAS  Release_4_0_x_branch Revision 61816
 All Data Structures Namespaces Files Functions Variables Groups Pages
Public Member Functions | Static Public Member Functions | 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)
 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.

Protected Member Functions

 __construct ()
 Create a new PHPExcel_Calculation.

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 66 of file ReferenceHelper.php.

Constructor & Destructor Documentation

PHPExcel_ReferenceHelper::__construct ( )
protected

Create a new PHPExcel_Calculation.

Definition at line 91 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 543 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
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 467 of file ReferenceHelper.php.

References _updateSingleCellReference(), PHPExcel_Cell\buildRange(), 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);
for ($i = 0; $i < count($range); $i++) {
for ($j = 0; $j < count($range[$i]); $j++) {
$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 494 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
$beforeColumn = 'A';
$beforeRow = 1;
list($beforeColumn, $beforeRow) = PHPExcel_Cell::coordinateFromString( $pBefore );
// Get coordinates
$newColumn = 'A';
$newRow = 1;
list($newColumn, $newRow) = PHPExcel_Cell::coordinateFromString( $pCellReference );
// Make sure the reference can be used
if ($newColumn == '' && $newRow == '')
{
return $pCellReference;
}
// Verify which parts should be updated
$updateColumn = (PHPExcel_Cell::columnIndexFromString($newColumn) >= PHPExcel_Cell::columnIndexFromString($beforeColumn))
&& (strpos($newColumn, '$') === false)
&& (strpos($beforeColumn, '$') === false);
$updateRow = ($newRow >= $beforeRow)
&& (strpos($newRow, '$') === false)
&& (strpos($beforeRow, '$') === false);
// 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 80 of file ReferenceHelper.php.

References $_instance.

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

{
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 102 of file ReferenceHelper.php.

References $key, 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().

{
// Get a copy of the cell collection
/*$aTemp = $pSheet->getCellCollection();
$aCellCollection = array();
foreach ($aTemp as $key => $value) {
$aCellCollection[$key] = clone $value;
}*/
$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 ( ($cell = ($pNumCols < 0 || $pNumRows < 0) ? array_shift($aCellCollection) : array_pop($aCellCollection)) ) {
// 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->setCellValue(
$newCoordinates
, $this->updateFormulaReferences($cell->getValue(), $pBefore, $pNumCols, $pNumRows)
);
} else {
// Formula should not be adjusted
$pSheet->setCellValue($newCoordinates, $cell->getValue());
}
// Clear the original cell
$pSheet->setCellValue($cell->getCoordinate(), '');
}
}
// 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: 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 = array_reverse($pSheet->getMergeCells(), true);
foreach ($aMergeCells as $key => $value) {
$newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
if ($key != $newReference) {
$pSheet->mergeCells( $newReference );
$pSheet->unmergeCells( $key );
}
}
// 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 418 of file ReferenceHelper.php.

References _updateCellRange(), _updateSingleCellReference(), and elseif().

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);
} else if (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 
)

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 380 of file ReferenceHelper.php.

References PHPExcel_Calculation\CALCULATION_REGEXP_CELLREF, getInstance(), and updateCellReference().

Referenced by insertNewBefore().

{
// Formula stack
$executableFormulaArray = array();
// Parse formula into a tree of tokens
$tokenisedFormula = PHPExcel_Calculation::getInstance()->parseFormula($pFormula);
$newCellTokens = $cellTokens = array();
// Build the translation table of cell tokens
foreach($tokenisedFormula as $token) {
if (preg_match('/^'.PHPExcel_Calculation::CALCULATION_REGEXP_CELLREF.'$/i', $token, $matches)) {
$newCellTokens[] = $this->updateCellReference($token, $pBefore, $pNumCols, $pNumRows);
$cellTokens[] = '/'.$token.'/';
}
}
// Update cell references in the formula
$formulaBlocks = explode('"',$pFormula);
$i = 0;
foreach($formulaBlocks as $formulaBlockKey => $formulaBlock) {
// Only count/replace in alternate array entries
if (($i++ % 2) == 0) {
$formulaBlocks[$formulaBlockKey] = preg_replace($cellTokens,$newCellTokens,$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 442 of file ReferenceHelper.php.

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

{
foreach ($pPhpExcel->getWorksheetIterator() as $sheet) {
foreach ($sheet->getCellCollection(false) as $cell) {
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 73 of file ReferenceHelper.php.

Referenced by getInstance().

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