ILIAS  release_5-2 Revision v5.2.25-18-g3f80b828510
Public Member Functions | Static Public Member Functions | Data Fields | Protected Member Functions | Private Member Functions | Static 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 or row, updating all possible related data. More...
 
 updateFormulaReferences ($pFormula='', $pBefore='A1', $pNumCols=0, $pNumRows=0, $sheetName='')
 Update references within formulas. More...
 
 updateCellReference ($pCellRange='A1', $pBefore='A1', $pNumCols=0, $pNumRows=0)
 Update cell reference. More...
 
 updateNamedFormulas (PHPExcel $pPhpExcel, $oldName='', $newName='')
 Update named formulas (i.e. More...
 
 __clone ()
 __clone implementation. More...
 

Static Public Member Functions

static getInstance ()
 Get an instance of this class. More...
 
static columnSort ($a, $b)
 Compare two column addresses Intended for use as a Callback function for sorting column addresses by column. More...
 
static columnReverseSort ($a, $b)
 Compare two column addresses Intended for use as a Callback function for reverse sorting column addresses by column. More...
 
static cellSort ($a, $b)
 Compare two cell addresses Intended for use as a Callback function for sorting cell addresses by column and row. More...
 
static cellReverseSort ($a, $b)
 Compare two cell addresses Intended for use as a Callback function for sorting cell addresses by column and row. More...
 

Data Fields

const REFHELPER_REGEXP_CELLREF = '((\w*|\'[^!]*\')!)?(?<![:a-z\$])(\$?[a-z]{1,3}\$?\d+)(?=[^:!\d\'])'
 Constants. More...
 
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. More...
 
 _adjustPageBreaks (PHPExcel_Worksheet $pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
 Update page breaks when inserting/deleting rows/columns. More...
 
 _adjustComments ($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
 Update cell comments when inserting/deleting rows/columns. More...
 
 _adjustHyperlinks ($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
 Update hyperlinks when inserting/deleting rows/columns. More...
 
 _adjustDataValidations ($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
 Update data validations when inserting/deleting rows/columns. More...
 
 _adjustMergeCells ($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
 Update merged cells when inserting/deleting rows/columns. More...
 
 _adjustProtectedCells ($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
 Update protected cells when inserting/deleting rows/columns. More...
 
 _adjustColumnDimensions ($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
 Update column dimensions when inserting/deleting rows/columns. More...
 
 _adjustRowDimensions ($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
 Update row dimensions when inserting/deleting rows/columns. More...
 

Private Member Functions

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

Static Private Member Functions

static cellAddressInDeleteRange ($cellAddress, $beforeRow, $pNumRows, $beforeColumnIndex, $pNumCols)
 Test whether a cell address falls within a defined range of cells. More...
 

Static Private Attributes

static $_instance
 

Detailed Description

Definition at line 36 of file ReferenceHelper.php.

Constructor & Destructor Documentation

◆ __construct()

PHPExcel_ReferenceHelper::__construct ( )
protected

Create a new PHPExcel_ReferenceHelper.

Definition at line 68 of file ReferenceHelper.php.

68  {
69  }

Member Function Documentation

◆ __clone()

PHPExcel_ReferenceHelper::__clone ( )
final

__clone implementation.

Cloning should not be allowed in a Singleton!

Exceptions
PHPExcel_Exception

Definition at line 919 of file ReferenceHelper.php.

919  {
920  throw new PHPExcel_Exception("Cloning a Singleton is not allowed!");
921  }
PHPExcel_Exception
Definition: Exception.php:36

◆ _adjustColumnDimensions()

PHPExcel_ReferenceHelper::_adjustColumnDimensions (   $pSheet,
  $pBefore,
  $beforeColumnIndex,
  $pNumCols,
  $beforeRow,
  $pNumRows 
)
protected

Update column dimensions when inserting/deleting rows/columns.

Parameters
PHPExcel_Worksheet$pSheetThe worksheet that we're editing
string$pBeforeInsert/Delete before this cell address (e.g. 'A1')
integer$beforeColumnIndexIndex number of the column we're inserting/deleting before
integer$pNumColsNumber of columns to insert/delete (negative values indicate deletion)
integer$beforeRowNumber of the row we're inserting/deleting before
integer$pNumRowsNumber of rows to insert/delete (negative values indicate deletion)

Definition at line 325 of file ReferenceHelper.php.

References PHPExcel_Cell\coordinateFromString(), and updateCellReference().

Referenced by insertNewBefore().

326  {
327  $aColumnDimensions = array_reverse($pSheet->getColumnDimensions(), true);
328  if (!empty($aColumnDimensions)) {
329  foreach ($aColumnDimensions as $objColumnDimension) {
330  $newReference = $this->updateCellReference($objColumnDimension->getColumnIndex() . '1', $pBefore, $pNumCols, $pNumRows);
331  list($newReference) = PHPExcel_Cell::coordinateFromString($newReference);
332  if ($objColumnDimension->getColumnIndex() != $newReference) {
333  $objColumnDimension->setColumnIndex($newReference);
334  }
335  }
336  $pSheet->refreshColumnDimensions();
337  }
338  }
PHPExcel_Cell\coordinateFromString
static coordinateFromString($pCoordinateString='A1')
Coordinate from string.
Definition: Cell.php:580
PHPExcel_ReferenceHelper\updateCellReference
updateCellReference($pCellRange='A1', $pBefore='A1', $pNumCols=0, $pNumRows=0)
Update cell reference.
Definition: ReferenceHelper.php:792
+ Here is the call graph for this function:
+ Here is the caller graph for this function:

◆ _adjustComments()

PHPExcel_ReferenceHelper::_adjustComments (   $pSheet,
  $pBefore,
  $beforeColumnIndex,
  $pNumCols,
  $beforeRow,
  $pNumRows 
)
protected

Update cell comments when inserting/deleting rows/columns.

Parameters
PHPExcel_Worksheet$pSheetThe worksheet that we're editing
string$pBeforeInsert/Delete before this cell address (e.g. 'A1')
integer$beforeColumnIndexIndex number of the column we're inserting/deleting before
integer$pNumColsNumber of columns to insert/delete (negative values indicate deletion)
integer$beforeRowNumber of the row we're inserting/deleting before
integer$pNumRowsNumber of rows to insert/delete (negative values indicate deletion)

Definition at line 201 of file ReferenceHelper.php.

References array, and updateCellReference().

Referenced by insertNewBefore().

202  {
203  $aComments = $pSheet->getComments();
204  $aNewComments = array(); // the new array of all comments
205 
206  foreach ($aComments as $key => &$value) {
207  // Any comments inside a deleted range will be ignored
208  if (!self::cellAddressInDeleteRange($key, $beforeRow, $pNumRows, $beforeColumnIndex, $pNumCols)) {
209  // Otherwise build a new array of comments indexed by the adjusted cell reference
210  $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
211  $aNewComments[$newReference] = $value;
212  }
213  }
214  // Replace the comments array with the new set of comments
215  $pSheet->setComments($aNewComments);
216  }
PHPExcel_ReferenceHelper\updateCellReference
updateCellReference($pCellRange='A1', $pBefore='A1', $pNumCols=0, $pNumRows=0)
Update cell reference.
Definition: ReferenceHelper.php:792
array
Create styles array
The data for the language used.
Definition: 40duplicateStyle.php:19
+ Here is the call graph for this function:
+ Here is the caller graph for this function:

◆ _adjustDataValidations()

PHPExcel_ReferenceHelper::_adjustDataValidations (   $pSheet,
  $pBefore,
  $beforeColumnIndex,
  $pNumCols,
  $beforeRow,
  $pNumRows 
)
protected

Update data validations when inserting/deleting rows/columns.

Parameters
PHPExcel_Worksheet$pSheetThe worksheet that we're editing
string$pBeforeInsert/Delete before this cell address (e.g. 'A1')
integer$beforeColumnIndexIndex number of the column we're inserting/deleting before
integer$pNumColsNumber of columns to insert/delete (negative values indicate deletion)
integer$beforeRowNumber of the row we're inserting/deleting before
integer$pNumRowsNumber of rows to insert/delete (negative values indicate deletion)

Definition at line 254 of file ReferenceHelper.php.

References array, and updateCellReference().

Referenced by insertNewBefore().

255  {
256  $aDataValidationCollection = $pSheet->getDataValidationCollection();
257  ($pNumCols > 0 || $pNumRows > 0) ?
258  uksort($aDataValidationCollection, array('PHPExcel_ReferenceHelper','cellReverseSort')) :
259  uksort($aDataValidationCollection, array('PHPExcel_ReferenceHelper','cellSort'));
260  foreach ($aDataValidationCollection as $key => $value) {
261  $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
262  if ($key != $newReference) {
263  $pSheet->setDataValidation( $newReference, $value );
264  $pSheet->setDataValidation( $key, null );
265  }
266  }
267  }
PHPExcel_ReferenceHelper\updateCellReference
updateCellReference($pCellRange='A1', $pBefore='A1', $pNumCols=0, $pNumRows=0)
Update cell reference.
Definition: ReferenceHelper.php:792
array
Create styles array
The data for the language used.
Definition: 40duplicateStyle.php:19
+ Here is the call graph for this function:
+ Here is the caller graph for this function:

◆ _adjustHyperlinks()

PHPExcel_ReferenceHelper::_adjustHyperlinks (   $pSheet,
  $pBefore,
  $beforeColumnIndex,
  $pNumCols,
  $beforeRow,
  $pNumRows 
)
protected

Update hyperlinks when inserting/deleting rows/columns.

Parameters
PHPExcel_Worksheet$pSheetThe worksheet that we're editing
string$pBeforeInsert/Delete before this cell address (e.g. 'A1')
integer$beforeColumnIndexIndex number of the column we're inserting/deleting before
integer$pNumColsNumber of columns to insert/delete (negative values indicate deletion)
integer$beforeRowNumber of the row we're inserting/deleting before
integer$pNumRowsNumber of rows to insert/delete (negative values indicate deletion)

Definition at line 228 of file ReferenceHelper.php.

References array, and updateCellReference().

Referenced by insertNewBefore().

229  {
230  $aHyperlinkCollection = $pSheet->getHyperlinkCollection();
231  ($pNumCols > 0 || $pNumRows > 0) ?
232  uksort($aHyperlinkCollection, array('PHPExcel_ReferenceHelper','cellReverseSort')) :
233  uksort($aHyperlinkCollection, array('PHPExcel_ReferenceHelper','cellSort'));
234 
235  foreach ($aHyperlinkCollection as $key => $value) {
236  $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
237  if ($key != $newReference) {
238  $pSheet->setHyperlink( $newReference, $value );
239  $pSheet->setHyperlink( $key, null );
240  }
241  }
242  }
PHPExcel_ReferenceHelper\updateCellReference
updateCellReference($pCellRange='A1', $pBefore='A1', $pNumCols=0, $pNumRows=0)
Update cell reference.
Definition: ReferenceHelper.php:792
array
Create styles array
The data for the language used.
Definition: 40duplicateStyle.php:19
+ Here is the call graph for this function:
+ Here is the caller graph for this function:

◆ _adjustMergeCells()

PHPExcel_ReferenceHelper::_adjustMergeCells (   $pSheet,
  $pBefore,
  $beforeColumnIndex,
  $pNumCols,
  $beforeRow,
  $pNumRows 
)
protected

Update merged cells when inserting/deleting rows/columns.

Parameters
PHPExcel_Worksheet$pSheetThe worksheet that we're editing
string$pBeforeInsert/Delete before this cell address (e.g. 'A1')
integer$beforeColumnIndexIndex number of the column we're inserting/deleting before
integer$pNumColsNumber of columns to insert/delete (negative values indicate deletion)
integer$beforeRowNumber of the row we're inserting/deleting before
integer$pNumRowsNumber of rows to insert/delete (negative values indicate deletion)

Definition at line 279 of file ReferenceHelper.php.

References array, and updateCellReference().

Referenced by insertNewBefore().

280  {
281  $aMergeCells = $pSheet->getMergeCells();
282  $aNewMergeCells = array(); // the new array of all merge cells
283  foreach ($aMergeCells as $key => &$value) {
284  $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
285  $aNewMergeCells[$newReference] = $newReference;
286  }
287  $pSheet->setMergeCells($aNewMergeCells); // replace the merge cells array
288  }
PHPExcel_ReferenceHelper\updateCellReference
updateCellReference($pCellRange='A1', $pBefore='A1', $pNumCols=0, $pNumRows=0)
Update cell reference.
Definition: ReferenceHelper.php:792
array
Create styles array
The data for the language used.
Definition: 40duplicateStyle.php:19
+ Here is the call graph for this function:
+ Here is the caller graph for this function:

◆ _adjustPageBreaks()

PHPExcel_ReferenceHelper::_adjustPageBreaks ( PHPExcel_Worksheet  $pSheet,
  $pBefore,
  $beforeColumnIndex,
  $pNumCols,
  $beforeRow,
  $pNumRows 
)
protected

Update page breaks when inserting/deleting rows/columns.

Parameters
PHPExcel_Worksheet$pSheetThe worksheet that we're editing
string$pBeforeInsert/Delete before this cell address (e.g. 'A1')
integer$beforeColumnIndexIndex number of the column we're inserting/deleting before
integer$pNumColsNumber of columns to insert/delete (negative values indicate deletion)
integer$beforeRowNumber of the row we're inserting/deleting before
integer$pNumRowsNumber of rows to insert/delete (negative values indicate deletion)

Definition at line 167 of file ReferenceHelper.php.

References array, PHPExcel_Worksheet\BREAK_NONE, PHPExcel_Worksheet\getBreaks(), PHPExcel_Worksheet\setBreak(), and updateCellReference().

Referenced by insertNewBefore().

168  {
169  $aBreaks = $pSheet->getBreaks();
170  ($pNumCols > 0 || $pNumRows > 0) ?
171  uksort($aBreaks, array('PHPExcel_ReferenceHelper','cellReverseSort')) :
172  uksort($aBreaks, array('PHPExcel_ReferenceHelper','cellSort'));
173 
174  foreach ($aBreaks as $key => $value) {
175  if (self::cellAddressInDeleteRange($key, $beforeRow, $pNumRows, $beforeColumnIndex, $pNumCols)) {
176  // If we're deleting, then clear any defined breaks that are within the range
177  // of rows/columns that we're deleting
178  $pSheet->setBreak($key, PHPExcel_Worksheet::BREAK_NONE);
179  } else {
180  // Otherwise update any affected breaks by inserting a new break at the appropriate point
181  // and removing the old affected break
182  $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
183  if ($key != $newReference) {
184  $pSheet->setBreak($newReference, $value)
185  ->setBreak($key, PHPExcel_Worksheet::BREAK_NONE);
186  }
187  }
188  }
189  }
PHPExcel_ReferenceHelper\updateCellReference
updateCellReference($pCellRange='A1', $pBefore='A1', $pNumCols=0, $pNumRows=0)
Update cell reference.
Definition: ReferenceHelper.php:792
PHPExcel_Worksheet\BREAK_NONE
const BREAK_NONE
Definition: Worksheet.php:39
array
Create styles array
The data for the language used.
Definition: 40duplicateStyle.php:19
PHPExcel_Worksheet\getBreaks
getBreaks()
Get breaks.
Definition: Worksheet.php:1675
PHPExcel_Worksheet\setBreak
setBreak($pCell='A1', $pBreak=PHPExcel_Worksheet::BREAK_NONE)
Set break on a cell.
Definition: Worksheet.php:1637
+ Here is the call graph for this function:
+ Here is the caller graph for this function:

◆ _adjustProtectedCells()

PHPExcel_ReferenceHelper::_adjustProtectedCells (   $pSheet,
  $pBefore,
  $beforeColumnIndex,
  $pNumCols,
  $beforeRow,
  $pNumRows 
)
protected

Update protected cells when inserting/deleting rows/columns.

Parameters
PHPExcel_Worksheet$pSheetThe worksheet that we're editing
string$pBeforeInsert/Delete before this cell address (e.g. 'A1')
integer$beforeColumnIndexIndex number of the column we're inserting/deleting before
integer$pNumColsNumber of columns to insert/delete (negative values indicate deletion)
integer$beforeRowNumber of the row we're inserting/deleting before
integer$pNumRowsNumber of rows to insert/delete (negative values indicate deletion)

Definition at line 300 of file ReferenceHelper.php.

References array, and updateCellReference().

Referenced by insertNewBefore().

301  {
302  $aProtectedCells = $pSheet->getProtectedCells();
303  ($pNumCols > 0 || $pNumRows > 0) ?
304  uksort($aProtectedCells, array('PHPExcel_ReferenceHelper','cellReverseSort')) :
305  uksort($aProtectedCells, array('PHPExcel_ReferenceHelper','cellSort'));
306  foreach ($aProtectedCells as $key => $value) {
307  $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
308  if ($key != $newReference) {
309  $pSheet->protectCells( $newReference, $value, true );
310  $pSheet->unprotectCells( $key );
311  }
312  }
313  }
PHPExcel_ReferenceHelper\updateCellReference
updateCellReference($pCellRange='A1', $pBefore='A1', $pNumCols=0, $pNumRows=0)
Update cell reference.
Definition: ReferenceHelper.php:792
array
Create styles array
The data for the language used.
Definition: 40duplicateStyle.php:19
+ Here is the call graph for this function:
+ Here is the caller graph for this function:

◆ _adjustRowDimensions()

PHPExcel_ReferenceHelper::_adjustRowDimensions (   $pSheet,
  $pBefore,
  $beforeColumnIndex,
  $pNumCols,
  $beforeRow,
  $pNumRows 
)
protected

Update row dimensions when inserting/deleting rows/columns.

Parameters
PHPExcel_Worksheet$pSheetThe worksheet that we're editing
string$pBeforeInsert/Delete before this cell address (e.g. 'A1')
integer$beforeColumnIndexIndex number of the column we're inserting/deleting before
integer$pNumColsNumber of columns to insert/delete (negative values indicate deletion)
integer$beforeRowNumber of the row we're inserting/deleting before
integer$pNumRowsNumber of rows to insert/delete (negative values indicate deletion)

Definition at line 350 of file ReferenceHelper.php.

References PHPExcel_Cell\coordinateFromString(), and updateCellReference().

Referenced by insertNewBefore().

351  {
352  $aRowDimensions = array_reverse($pSheet->getRowDimensions(), true);
353  if (!empty($aRowDimensions)) {
354  foreach ($aRowDimensions as $objRowDimension) {
355  $newReference = $this->updateCellReference('A' . $objRowDimension->getRowIndex(), $pBefore, $pNumCols, $pNumRows);
356  list(, $newReference) = PHPExcel_Cell::coordinateFromString($newReference);
357  if ($objRowDimension->getRowIndex() != $newReference) {
358  $objRowDimension->setRowIndex($newReference);
359  }
360  }
361  $pSheet->refreshRowDimensions();
362 
363  $copyDimension = $pSheet->getRowDimension($beforeRow - 1);
364  for ($i = $beforeRow; $i <= $beforeRow - 1 + $pNumRows; ++$i) {
365  $newDimension = $pSheet->getRowDimension($i);
366  $newDimension->setRowHeight($copyDimension->getRowHeight());
367  $newDimension->setVisible($copyDimension->getVisible());
368  $newDimension->setOutlineLevel($copyDimension->getOutlineLevel());
369  $newDimension->setCollapsed($copyDimension->getCollapsed());
370  }
371  }
372  }
PHPExcel_Cell\coordinateFromString
static coordinateFromString($pCoordinateString='A1')
Coordinate from string.
Definition: Cell.php:580
PHPExcel_ReferenceHelper\updateCellReference
updateCellReference($pCellRange='A1', $pBefore='A1', $pNumCols=0, $pNumRows=0)
Update cell reference.
Definition: ReferenceHelper.php:792
+ Here is the call graph for this function:
+ Here is the caller graph for this function:

◆ _updateCellRange()

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
PHPExcel_Exception

Definition at line 846 of file ReferenceHelper.php.

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

Referenced by updateCellReference().

846  :A1', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0) {
847  if (strpos($pCellRange,':') !== false || strpos($pCellRange, ',') !== false) {
848  // Update range
849  $range = PHPExcel_Cell::splitRange($pCellRange);
850  $ic = count($range);
851  for ($i = 0; $i < $ic; ++$i) {
852  $jc = count($range[$i]);
853  for ($j = 0; $j < $jc; ++$j) {
854  if (ctype_alpha($range[$i][$j])) {
855  $r = PHPExcel_Cell::coordinateFromString($this->_updateSingleCellReference($range[$i][$j].'1', $pBefore, $pNumCols, $pNumRows));
856  $range[$i][$j] = $r[0];
857  } elseif(ctype_digit($range[$i][$j])) {
858  $r = PHPExcel_Cell::coordinateFromString($this->_updateSingleCellReference('A'.$range[$i][$j], $pBefore, $pNumCols, $pNumRows));
859  $range[$i][$j] = $r[1];
860  } else {
861  $range[$i][$j] = $this->_updateSingleCellReference($range[$i][$j], $pBefore, $pNumCols, $pNumRows);
862  }
863  }
864  }
865 
866  // Recreate range string
867  return PHPExcel_Cell::buildRange($range);
868  } else {
869  throw new PHPExcel_Exception("Only cell ranges may be passed to this method.");
870  }
871  }
+ Here is the call graph for this function:
+ Here is the caller graph for this function:

◆ _updateSingleCellReference()

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
PHPExcel_Exception

Definition at line 883 of file ReferenceHelper.php.

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

Referenced by _updateCellRange(), and updateCellReference().

883  {
884  if (strpos($pCellReference, ':') === false && strpos($pCellReference, ',') === false) {
885  // Get coordinates of $pBefore
886  list($beforeColumn, $beforeRow) = PHPExcel_Cell::coordinateFromString( $pBefore );
887 
888  // Get coordinates of $pCellReference
889  list($newColumn, $newRow) = PHPExcel_Cell::coordinateFromString( $pCellReference );
890 
891  // Verify which parts should be updated
892  $updateColumn = (($newColumn{0} != '$') && ($beforeColumn{0} != '$') &&
893  PHPExcel_Cell::columnIndexFromString($newColumn) >= PHPExcel_Cell::columnIndexFromString($beforeColumn));
894  $updateRow = (($newRow{0} != '$') && ($beforeRow{0} != '$') &&
895  $newRow >= $beforeRow);
896 
897  // Create new column reference
898  if ($updateColumn) {
899  $newColumn = PHPExcel_Cell::stringFromColumnIndex( PHPExcel_Cell::columnIndexFromString($newColumn) - 1 + $pNumCols );
900  }
901 
902  // Create new row reference
903  if ($updateRow) {
904  $newRow = $newRow + $pNumRows;
905  }
906 
907  // Return new reference
908  return $newColumn . $newRow;
909  } else {
910  throw new PHPExcel_Exception("Only single cell references may be passed to this method.");
911  }
912  }
PHPExcel_Cell\coordinateFromString
static coordinateFromString($pCoordinateString='A1')
Coordinate from string.
Definition: Cell.php:580
PHPExcel_Exception
Definition: Exception.php:36
PHPExcel_Cell\columnIndexFromString
static columnIndexFromString($pString='A')
Column index from string.
Definition: Cell.php:782
PHPExcel_Cell\stringFromColumnIndex
static stringFromColumnIndex($pColumnIndex=0)
String from columnindex.
Definition: Cell.php:825
+ Here is the call graph for this function:
+ Here is the caller graph for this function:

◆ cellAddressInDeleteRange()

static PHPExcel_ReferenceHelper::cellAddressInDeleteRange (   $cellAddress,
  $beforeRow,
  $pNumRows,
  $beforeColumnIndex,
  $pNumCols 
)
staticprivate

Test whether a cell address falls within a defined range of cells.

Parameters
string$cellAddressAddress of the cell we're testing
integer$beforeRowNumber of the row we're inserting/deleting before
integer$pNumRowsNumber of rows to insert/delete (negative values indicate deletion)
integer$beforeColumnIndexIndex number of the column we're inserting/deleting before
integer$pNumColsNumber of columns to insert/delete (negative values indicate deletion)
Returns
boolean

Definition at line 141 of file ReferenceHelper.php.

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

141  {
142  list($cellColumn, $cellRow) = PHPExcel_Cell::coordinateFromString($cellAddress);
143  $cellColumnIndex = PHPExcel_Cell::columnIndexFromString($cellColumn);
144  // Is cell within the range of rows/columns if we're deleting
145  if ($pNumRows < 0 &&
146  ($cellRow >= ($beforeRow + $pNumRows)) &&
147  ($cellRow < $beforeRow)) {
148  return TRUE;
149  } elseif ($pNumCols < 0 &&
150  ($cellColumnIndex >= ($beforeColumnIndex + $pNumCols)) &&
151  ($cellColumnIndex < $beforeColumnIndex)) {
152  return TRUE;
153  }
154  return FALSE;
155  }
PHPExcel_Cell\coordinateFromString
static coordinateFromString($pCoordinateString='A1')
Coordinate from string.
Definition: Cell.php:580
PHPExcel_Cell\columnIndexFromString
static columnIndexFromString($pString='A')
Column index from string.
Definition: Cell.php:782
+ Here is the call graph for this function:

◆ cellReverseSort()

static PHPExcel_ReferenceHelper::cellReverseSort (   $a,
  $b 
)
static

Compare two cell addresses Intended for use as a Callback function for sorting cell addresses by column and row.

Parameters
string$aFirst cell to test (e.g. 'AA1')
string$bSecond cell to test (e.g. 'Z1')
Returns
integer

Definition at line 121 of file ReferenceHelper.php.

121  {
122  sscanf($a,'%[A-Z]%d', $ac, $ar);
123  sscanf($b,'%[A-Z]%d', $bc, $br);
124 
125  if ($ar == $br) {
126  return 1 - strcasecmp(strlen($ac) . $ac, strlen($bc) . $bc);
127  }
128  return ($ar < $br) ? 1 : -1;
129  }

◆ cellSort()

static PHPExcel_ReferenceHelper::cellSort (   $a,
  $b 
)
static

Compare two cell addresses Intended for use as a Callback function for sorting cell addresses by column and row.

Parameters
string$aFirst cell to test (e.g. 'AA1')
string$bSecond cell to test (e.g. 'Z1')
Returns
integer

Definition at line 103 of file ReferenceHelper.php.

103  {
104  sscanf($a,'%[A-Z]%d', $ac, $ar);
105  sscanf($b,'%[A-Z]%d', $bc, $br);
106 
107  if ($ar == $br) {
108  return strcasecmp(strlen($ac) . $ac, strlen($bc) . $bc);
109  }
110  return ($ar < $br) ? -1 : 1;
111  }

◆ columnReverseSort()

static PHPExcel_ReferenceHelper::columnReverseSort (   $a,
  $b 
)
static

Compare two column addresses Intended for use as a Callback function for reverse sorting column addresses by column.

Parameters
string$aFirst column to test (e.g. 'AA')
string$bSecond column to test (e.g. 'Z')
Returns
integer

Definition at line 91 of file ReferenceHelper.php.

91  {
92  return 1 - strcasecmp(strlen($a) . $a, strlen($b) . $b);
93  }

◆ columnSort()

static PHPExcel_ReferenceHelper::columnSort (   $a,
  $b 
)
static

Compare two column addresses Intended for use as a Callback function for sorting column addresses by column.

Parameters
string$aFirst column to test (e.g. 'AA')
string$bSecond column to test (e.g. 'Z')
Returns
integer

Definition at line 79 of file ReferenceHelper.php.

79  {
80  return strcasecmp(strlen($a) . $a, strlen($b) . $b);
81  }

◆ getInstance()

static PHPExcel_ReferenceHelper::getInstance ( )
static

Get an instance of this class.

Returns
PHPExcel_ReferenceHelper

Definition at line 57 of file ReferenceHelper.php.

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

57  {
58  if (!isset(self::$_instance) || (self::$_instance === NULL)) {
59  self::$_instance = new PHPExcel_ReferenceHelper();
60  }
61 
62  return self::$_instance;
63  }
PHPExcel_ReferenceHelper
Definition: ReferenceHelper.php:36
+ Here is the caller graph for this function:

◆ insertNewBefore()

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

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

Parameters
string$pBeforeInsert before this cell address (e.g. 'A1')
integer$pNumColsNumber of columns to insert/delete (negative values indicate deletion)
integer$pNumRowsNumber of rows to insert/delete (negative values indicate deletion)
PHPExcel_Worksheet$pSheetThe worksheet that we're editing
Exceptions
PHPExcel_Exception

Definition at line 383 of file ReferenceHelper.php.

References $autoFilter, $column, $conditionalStyles, $objDrawing, $row, _adjustColumnDimensions(), _adjustComments(), _adjustDataValidations(), _adjustHyperlinks(), _adjustMergeCells(), _adjustPageBreaks(), _adjustProtectedCells(), _adjustRowDimensions(), array, PHPExcel_Cell\columnIndexFromString(), PHPExcel_Cell\coordinateFromString(), PHPExcel_Cell\rangeBoundaries(), PHPExcel_Cell\stringFromColumnIndex(), PHPExcel_Cell_DataType\TYPE_FORMULA, PHPExcel_Cell_DataType\TYPE_NULL, updateCellReference(), and updateFormulaReferences().

384  {
385  $remove = ($pNumCols < 0 || $pNumRows < 0);
386  $aCellCollection = $pSheet->getCellCollection();
387 
388  // Get coordinates of $pBefore
389  $beforeColumn = 'A';
390  $beforeRow = 1;
391  list($beforeColumn, $beforeRow) = PHPExcel_Cell::coordinateFromString($pBefore);
392  $beforeColumnIndex = PHPExcel_Cell::columnIndexFromString($beforeColumn);
393 
394  // Clear cells if we are removing columns or rows
395  $highestColumn = $pSheet->getHighestColumn();
396  $highestRow = $pSheet->getHighestRow();
397 
398  // 1. Clear column strips if we are removing columns
399  if ($pNumCols < 0 && $beforeColumnIndex - 2 + $pNumCols > 0) {
400  for ($i = 1; $i <= $highestRow - 1; ++$i) {
401  for ($j = $beforeColumnIndex - 1 + $pNumCols; $j <= $beforeColumnIndex - 2; ++$j) {
402  $coordinate = PHPExcel_Cell::stringFromColumnIndex($j) . $i;
403  $pSheet->removeConditionalStyles($coordinate);
404  if ($pSheet->cellExists($coordinate)) {
405  $pSheet->getCell($coordinate)->setValueExplicit('', PHPExcel_Cell_DataType::TYPE_NULL);
406  $pSheet->getCell($coordinate)->setXfIndex(0);
407  }
408  }
409  }
410  }
411 
412  // 2. Clear row strips if we are removing rows
413  if ($pNumRows < 0 && $beforeRow - 1 + $pNumRows > 0) {
414  for ($i = $beforeColumnIndex - 1; $i <= PHPExcel_Cell::columnIndexFromString($highestColumn) - 1; ++$i) {
415  for ($j = $beforeRow + $pNumRows; $j <= $beforeRow - 1; ++$j) {
416  $coordinate = PHPExcel_Cell::stringFromColumnIndex($i) . $j;
417  $pSheet->removeConditionalStyles($coordinate);
418  if ($pSheet->cellExists($coordinate)) {
419  $pSheet->getCell($coordinate)->setValueExplicit('', PHPExcel_Cell_DataType::TYPE_NULL);
420  $pSheet->getCell($coordinate)->setXfIndex(0);
421  }
422  }
423  }
424  }
425 
426  // Loop through cells, bottom-up, and change cell coordinates
427  if($remove) {
428  // It's faster to reverse and pop than to use unshift, especially with large cell collections
429  $aCellCollection = array_reverse($aCellCollection);
430  }
431  while ($cellID = array_pop($aCellCollection)) {
432  $cell = $pSheet->getCell($cellID);
433  $cellIndex = PHPExcel_Cell::columnIndexFromString($cell->getColumn());
434 
435  if ($cellIndex-1 + $pNumCols < 0) {
436  continue;
437  }
438 
439  // New coordinates
440  $newCoordinates = PHPExcel_Cell::stringFromColumnIndex($cellIndex-1 + $pNumCols) . ($cell->getRow() + $pNumRows);
441 
442  // Should the cell be updated? Move value and cellXf index from one cell to another.
443  if (($cellIndex >= $beforeColumnIndex) &&
444  ($cell->getRow() >= $beforeRow)) {
445 
446  // Update cell styles
447  $pSheet->getCell($newCoordinates)->setXfIndex($cell->getXfIndex());
448 
449  // Insert this cell at its new location
450  if ($cell->getDataType() == PHPExcel_Cell_DataType::TYPE_FORMULA) {
451  // Formula should be adjusted
452  $pSheet->getCell($newCoordinates)
453  ->setValue($this->updateFormulaReferences($cell->getValue(),
454  $pBefore, $pNumCols, $pNumRows, $pSheet->getTitle()));
455  } else {
456  // Formula should not be adjusted
457  $pSheet->getCell($newCoordinates)->setValue($cell->getValue());
458  }
459 
460  // Clear the original cell
461  $pSheet->getCellCacheController()->deleteCacheData($cellID);
462 
463  } else {
464  /* We don't need to update styles for rows/columns before our insertion position,
465  but we do still need to adjust any formulae in those cells */
466  if ($cell->getDataType() == PHPExcel_Cell_DataType::TYPE_FORMULA) {
467  // Formula should be adjusted
468  $cell->setValue($this->updateFormulaReferences($cell->getValue(),
469  $pBefore, $pNumCols, $pNumRows, $pSheet->getTitle()));
470  }
471 
472  }
473  }
474 
475  // Duplicate styles for the newly inserted cells
476  $highestColumn = $pSheet->getHighestColumn();
477  $highestRow = $pSheet->getHighestRow();
478 
479  if ($pNumCols > 0 && $beforeColumnIndex - 2 > 0) {
480  for ($i = $beforeRow; $i <= $highestRow - 1; ++$i) {
481 
482  // Style
483  $coordinate = PHPExcel_Cell::stringFromColumnIndex( $beforeColumnIndex - 2 ) . $i;
484  if ($pSheet->cellExists($coordinate)) {
485  $xfIndex = $pSheet->getCell($coordinate)->getXfIndex();
486  $conditionalStyles = $pSheet->conditionalStylesExists($coordinate) ?
487  $pSheet->getConditionalStyles($coordinate) : false;
488  for ($j = $beforeColumnIndex - 1; $j <= $beforeColumnIndex - 2 + $pNumCols; ++$j) {
489  $pSheet->getCellByColumnAndRow($j, $i)->setXfIndex($xfIndex);
490  if ($conditionalStyles) {
491  $cloned = array();
492  foreach ($conditionalStyles as $conditionalStyle) {
493  $cloned[] = clone $conditionalStyle;
494  }
495  $pSheet->setConditionalStyles(PHPExcel_Cell::stringFromColumnIndex($j) . $i, $cloned);
496  }
497  }
498  }
499 
500  }
501  }
502 
503  if ($pNumRows > 0 && $beforeRow - 1 > 0) {
504  for ($i = $beforeColumnIndex - 1; $i <= PHPExcel_Cell::columnIndexFromString($highestColumn) - 1; ++$i) {
505 
506  // Style
507  $coordinate = PHPExcel_Cell::stringFromColumnIndex($i) . ($beforeRow - 1);
508  if ($pSheet->cellExists($coordinate)) {
509  $xfIndex = $pSheet->getCell($coordinate)->getXfIndex();
510  $conditionalStyles = $pSheet->conditionalStylesExists($coordinate) ?
511  $pSheet->getConditionalStyles($coordinate) : false;
512  for ($j = $beforeRow; $j <= $beforeRow - 1 + $pNumRows; ++$j) {
513  $pSheet->getCell(PHPExcel_Cell::stringFromColumnIndex($i) . $j)->setXfIndex($xfIndex);
514  if ($conditionalStyles) {
515  $cloned = array();
516  foreach ($conditionalStyles as $conditionalStyle) {
517  $cloned[] = clone $conditionalStyle;
518  }
519  $pSheet->setConditionalStyles(PHPExcel_Cell::stringFromColumnIndex($i) . $j, $cloned);
520  }
521  }
522  }
523  }
524  }
525 
526  // Update worksheet: column dimensions
527  $this->_adjustColumnDimensions($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
528 
529  // Update worksheet: row dimensions
530  $this->_adjustRowDimensions($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
531 
532  // Update worksheet: page breaks
533  $this->_adjustPageBreaks($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
534 
535  // Update worksheet: comments
536  $this->_adjustComments($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
537 
538  // Update worksheet: hyperlinks
539  $this->_adjustHyperlinks($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
540 
541  // Update worksheet: data validations
542  $this->_adjustDataValidations($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
543 
544  // Update worksheet: merge cells
545  $this->_adjustMergeCells($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
546 
547  // Update worksheet: protected cells
548  $this->_adjustProtectedCells($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
549 
550  // Update worksheet: autofilter
551  $autoFilter = $pSheet->getAutoFilter();
552  $autoFilterRange = $autoFilter->getRange();
553  if (!empty($autoFilterRange)) {
554  if ($pNumCols != 0) {
555  $autoFilterColumns = array_keys($autoFilter->getColumns());
556  if (count($autoFilterColumns) > 0) {
557  sscanf($pBefore,'%[A-Z]%d', $column, $row);
558  $columnIndex = PHPExcel_Cell::columnIndexFromString($column);
559  list($rangeStart,$rangeEnd) = PHPExcel_Cell::rangeBoundaries($autoFilterRange);
560  if ($columnIndex <= $rangeEnd[0]) {
561  if ($pNumCols < 0) {
562  // If we're actually deleting any columns that fall within the autofilter range,
563  // then we delete any rules for those columns
564  $deleteColumn = $columnIndex + $pNumCols - 1;
565  $deleteCount = abs($pNumCols);
566  for ($i = 1; $i <= $deleteCount; ++$i) {
567  if (in_array(PHPExcel_Cell::stringFromColumnIndex($deleteColumn),$autoFilterColumns)) {
568  $autoFilter->clearColumn(PHPExcel_Cell::stringFromColumnIndex($deleteColumn));
569  }
570  ++$deleteColumn;
571  }
572  }
573  $startCol = ($columnIndex > $rangeStart[0]) ? $columnIndex : $rangeStart[0];
574 
575  // Shuffle columns in autofilter range
576  if ($pNumCols > 0) {
577  // For insert, we shuffle from end to beginning to avoid overwriting
578  $startColID = PHPExcel_Cell::stringFromColumnIndex($startCol-1);
579  $toColID = PHPExcel_Cell::stringFromColumnIndex($startCol+$pNumCols-1);
580  $endColID = PHPExcel_Cell::stringFromColumnIndex($rangeEnd[0]);
581 
582  $startColRef = $startCol;
583  $endColRef = $rangeEnd[0];
584  $toColRef = $rangeEnd[0]+$pNumCols;
585 
586  do {
587  $autoFilter->shiftColumn(PHPExcel_Cell::stringFromColumnIndex($endColRef-1),PHPExcel_Cell::stringFromColumnIndex($toColRef-1));
588  --$endColRef;
589  --$toColRef;
590  } while ($startColRef <= $endColRef);
591  } else {
592  // For delete, we shuffle from beginning to end to avoid overwriting
593  $startColID = PHPExcel_Cell::stringFromColumnIndex($startCol-1);
594  $toColID = PHPExcel_Cell::stringFromColumnIndex($startCol+$pNumCols-1);
595  $endColID = PHPExcel_Cell::stringFromColumnIndex($rangeEnd[0]);
596  do {
597  $autoFilter->shiftColumn($startColID,$toColID);
598  ++$startColID;
599  ++$toColID;
600  } while ($startColID != $endColID);
601  }
602  }
603  }
604  }
605  $pSheet->setAutoFilter( $this->updateCellReference($autoFilterRange, $pBefore, $pNumCols, $pNumRows) );
606  }
607 
608  // Update worksheet: freeze pane
609  if ($pSheet->getFreezePane() != '') {
610  $pSheet->freezePane( $this->updateCellReference($pSheet->getFreezePane(), $pBefore, $pNumCols, $pNumRows) );
611  }
612 
613  // Page setup
614  if ($pSheet->getPageSetup()->isPrintAreaSet()) {
615  $pSheet->getPageSetup()->setPrintArea( $this->updateCellReference($pSheet->getPageSetup()->getPrintArea(), $pBefore, $pNumCols, $pNumRows) );
616  }
617 
618  // Update worksheet: drawings
619  $aDrawings = $pSheet->getDrawingCollection();
620  foreach ($aDrawings as $objDrawing) {
621  $newReference = $this->updateCellReference($objDrawing->getCoordinates(), $pBefore, $pNumCols, $pNumRows);
622  if ($objDrawing->getCoordinates() != $newReference) {
623  $objDrawing->setCoordinates($newReference);
624  }
625  }
626 
627  // Update workbook: named ranges
628  if (count($pSheet->getParent()->getNamedRanges()) > 0) {
629  foreach ($pSheet->getParent()->getNamedRanges() as $namedRange) {
630  if ($namedRange->getWorksheet()->getHashCode() == $pSheet->getHashCode()) {
631  $namedRange->setRange(
632  $this->updateCellReference($namedRange->getRange(), $pBefore, $pNumCols, $pNumRows)
633  );
634  }
635  }
636  }
637 
638  // Garbage collect
639  $pSheet->garbageCollect();
640  }
PHPExcel_Worksheet\getDrawingCollection
getDrawingCollection()
Get collection of drawings.
Definition: Worksheet.php:554
PHPExcel_Cell\coordinateFromString
static coordinateFromString($pCoordinateString='A1')
Coordinate from string.
Definition: Cell.php:580
PHPExcel_Worksheet\getHashCode
getHashCode()
Get hash code.
Definition: Worksheet.php:2632
PHPExcel_ReferenceHelper\_adjustRowDimensions
_adjustRowDimensions($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
Update row dimensions when inserting/deleting rows/columns.
Definition: ReferenceHelper.php:350
PHPExcel_ReferenceHelper\_adjustPageBreaks
_adjustPageBreaks(PHPExcel_Worksheet $pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
Update page breaks when inserting/deleting rows/columns.
Definition: ReferenceHelper.php:167
PHPExcel_Cell\rangeBoundaries
static rangeBoundaries($pRange='A1:A1')
Calculate range boundaries.
Definition: Cell.php:707
PHPExcel_ReferenceHelper\updateCellReference
updateCellReference($pCellRange='A1', $pBefore='A1', $pNumCols=0, $pNumRows=0)
Update cell reference.
Definition: ReferenceHelper.php:792
PHPExcel_ReferenceHelper\_adjustDataValidations
_adjustDataValidations($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
Update data validations when inserting/deleting rows/columns.
Definition: ReferenceHelper.php:254
PHPExcel_Worksheet\getCellByColumnAndRow
getCellByColumnAndRow($pColumn=0, $pRow=1)
Get cell at a specific coordinate by using numeric cell coordinates.
Definition: Worksheet.php:1197
PHPExcel_Worksheet\getFreezePane
getFreezePane()
Get Freeze Pane.
Definition: Worksheet.php:1952
PHPExcel_ReferenceHelper\_adjustHyperlinks
_adjustHyperlinks($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
Update hyperlinks when inserting/deleting rows/columns.
Definition: ReferenceHelper.php:228
PHPExcel_Worksheet\getHighestRow
getHighestRow($column=null)
Get highest worksheet row.
Definition: Worksheet.php:1055
PHPExcel_Worksheet\getCell
getCell($pCoordinate='A1')
Get cell at a specific coordinate.
Definition: Worksheet.php:1153
PHPExcel_ReferenceHelper\updateFormulaReferences
updateFormulaReferences($pFormula='', $pBefore='A1', $pNumCols=0, $pNumRows=0, $sheetName='')
Update references within formulas.
Definition: ReferenceHelper.php:653
$column
$column
Definition: 39dropdown.php:62
PHPExcel_Cell_DataType\TYPE_NULL
const TYPE_NULL
Definition: DataType.php:44
PHPExcel_Worksheet\getParent
getParent()
Get parent.
Definition: Worksheet.php:786
PHPExcel_Worksheet\getCellCollection
getCellCollection($pSorted=true)
Get collection of cells.
Definition: Worksheet.php:484
PHPExcel_ReferenceHelper\_adjustProtectedCells
_adjustProtectedCells($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
Update protected cells when inserting/deleting rows/columns.
Definition: ReferenceHelper.php:300
array
Create styles array
The data for the language used.
Definition: 40duplicateStyle.php:19
PHPExcel_Worksheet\getHighestColumn
getHighestColumn($row=null)
Get highest worksheet column.
Definition: Worksheet.php:1028
PHPExcel_Worksheet\setConditionalStyles
setConditionalStyles($pCoordinate='A1', $pValue)
Set conditional styles.
Definition: Worksheet.php:1482
PHPExcel_Worksheet\freezePane
freezePane($pCell='')
Freeze Pane.
Definition: Worksheet.php:1969
PHPExcel_Worksheet\getPageSetup
getPageSetup()
Get page setup.
Definition: Worksheet.php:914
PHPExcel_Worksheet\removeConditionalStyles
removeConditionalStyles($pCoordinate='A1')
Removes conditional styles for a cell.
Definition: Worksheet.php:1459
PHPExcel_ReferenceHelper\_adjustComments
_adjustComments($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
Update cell comments when inserting/deleting rows/columns.
Definition: ReferenceHelper.php:201
PHPExcel_Cell\columnIndexFromString
static columnIndexFromString($pString='A')
Column index from string.
Definition: Cell.php:782
PHPExcel_Worksheet\conditionalStylesExists
conditionalStylesExists($pCoordinate='A1')
Do conditional styles exist for this cell?
Definition: Worksheet.php:1445
PHPExcel_Cell\stringFromColumnIndex
static stringFromColumnIndex($pColumnIndex=0)
String from columnindex.
Definition: Cell.php:825
$objDrawing
$objDrawing
Definition: 04printing.php:70
PHPExcel_Worksheet\garbageCollect
garbageCollect()
Run PHPExcel garabage collector.
Definition: Worksheet.php:2588
PHPExcel_Worksheet\getCellCacheController
getCellCacheController()
Return the cache controller for the cell collection.
Definition: Worksheet.php:413
PHPExcel_Worksheet\getTitle
getTitle()
Get title.
Definition: Worksheet.php:817
PHPExcel_Worksheet\setAutoFilter
setAutoFilter($pValue)
Set AutoFilter.
Definition: Worksheet.php:1905
PHPExcel_Worksheet\getAutoFilter
getAutoFilter()
Get Autofilter.
Definition: Worksheet.php:1892
PHPExcel_Worksheet\getConditionalStyles
getConditionalStyles($pCoordinate='A1')
Get conditional styles for a cell.
Definition: Worksheet.php:1430
PHPExcel_Cell_DataType\TYPE_FORMULA
const TYPE_FORMULA
Definition: DataType.php:41
PHPExcel_Worksheet\cellExists
cellExists($pCoordinate='A1')
Does the cell at a specific coordinate exist?
Definition: Worksheet.php:1256
PHPExcel_ReferenceHelper\_adjustColumnDimensions
_adjustColumnDimensions($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
Update column dimensions when inserting/deleting rows/columns.
Definition: ReferenceHelper.php:325
PHPExcel_ReferenceHelper\_adjustMergeCells
_adjustMergeCells($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
Update merged cells when inserting/deleting rows/columns.
Definition: ReferenceHelper.php:279
+ Here is the call graph for this function:

◆ updateCellReference()

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
PHPExcel_Exception

Definition at line 792 of file ReferenceHelper.php.

References _updateCellRange(), and _updateSingleCellReference().

Referenced by _adjustColumnDimensions(), _adjustComments(), _adjustDataValidations(), _adjustHyperlinks(), _adjustMergeCells(), _adjustPageBreaks(), _adjustProtectedCells(), _adjustRowDimensions(), insertNewBefore(), and updateFormulaReferences().

792  {
793  // Is it in another worksheet? Will not have to update anything.
794  if (strpos($pCellRange, "!") !== false) {
795  return $pCellRange;
796  // Is it a range or a single cell?
797  } elseif (strpos($pCellRange, ':') === false && strpos($pCellRange, ',') === false) {
798  // Single cell
799  return $this->_updateSingleCellReference($pCellRange, $pBefore, $pNumCols, $pNumRows);
800  } elseif (strpos($pCellRange, ':') !== false || strpos($pCellRange, ',') !== false) {
801  // Range
802  return $this->_updateCellRange($pCellRange, $pBefore, $pNumCols, $pNumRows);
803  } else {
804  // Return original
805  return $pCellRange;
806  }
807  }
PHPExcel_ReferenceHelper\_updateSingleCellReference
_updateSingleCellReference($pCellReference='A1', $pBefore='A1', $pNumCols=0, $pNumRows=0)
Update single cell reference.
Definition: ReferenceHelper.php:883
PHPExcel_ReferenceHelper\_updateCellRange
_updateCellRange($pCellRange='A1:A1', $pBefore='A1', $pNumCols=0, $pNumRows=0)
Update cell range.
Definition: ReferenceHelper.php:846
+ Here is the call graph for this function:
+ Here is the caller graph for this function:

◆ updateFormulaReferences()

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
string$sheetNameWorksheet name/title
Returns
string Updated formula
Exceptions
PHPExcel_Exception

Definition at line 653 of file ReferenceHelper.php.

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

Referenced by insertNewBefore().

653  {
654  // Update cell references in the formula
655  $formulaBlocks = explode('"',$pFormula);
656  $i = false;
657  foreach($formulaBlocks as &$formulaBlock) {
658  // Ignore blocks that were enclosed in quotes (alternating entries in the $formulaBlocks array after the explode)
659  if ($i = !$i) {
660  $adjustCount = 0;
661  $newCellTokens = $cellTokens = array();
662  // Search for row ranges (e.g. 'Sheet1'!3:5 or 3:5) with or without $ absolutes (e.g. $3:5)
663  $matchCount = preg_match_all('/'.self::REFHELPER_REGEXP_ROWRANGE.'/i', ' '.$formulaBlock.' ', $matches, PREG_SET_ORDER);
664  if ($matchCount > 0) {
665  foreach($matches as $match) {
666  $fromString = ($match[2] > '') ? $match[2].'!' : '';
667  $fromString .= $match[3].':'.$match[4];
668  $modified3 = substr($this->updateCellReference('$A'.$match[3],$pBefore,$pNumCols,$pNumRows),2);
669  $modified4 = substr($this->updateCellReference('$A'.$match[4],$pBefore,$pNumCols,$pNumRows),2);
670 
671  if ($match[3].':'.$match[4] !== $modified3.':'.$modified4) {
672  if (($match[2] == '') || (trim($match[2],"'") == $sheetName)) {
673  $toString = ($match[2] > '') ? $match[2].'!' : '';
674  $toString .= $modified3.':'.$modified4;
675  // 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
676  $column = 100000;
677  $row = 10000000+trim($match[3],'$');
678  $cellIndex = $column.$row;
679 
680  $newCellTokens[$cellIndex] = preg_quote($toString);
681  $cellTokens[$cellIndex] = '/(?<!\d\$\!)'.preg_quote($fromString).'(?!\d)/i';
682  ++$adjustCount;
683  }
684  }
685  }
686  }
687  // Search for column ranges (e.g. 'Sheet1'!C:E or C:E) with or without $ absolutes (e.g. $C:E)
688  $matchCount = preg_match_all('/'.self::REFHELPER_REGEXP_COLRANGE.'/i', ' '.$formulaBlock.' ', $matches, PREG_SET_ORDER);
689  if ($matchCount > 0) {
690  foreach($matches as $match) {
691  $fromString = ($match[2] > '') ? $match[2].'!' : '';
692  $fromString .= $match[3].':'.$match[4];
693  $modified3 = substr($this->updateCellReference($match[3].'$1',$pBefore,$pNumCols,$pNumRows),0,-2);
694  $modified4 = substr($this->updateCellReference($match[4].'$1',$pBefore,$pNumCols,$pNumRows),0,-2);
695 
696  if ($match[3].':'.$match[4] !== $modified3.':'.$modified4) {
697  if (($match[2] == '') || (trim($match[2],"'") == $sheetName)) {
698  $toString = ($match[2] > '') ? $match[2].'!' : '';
699  $toString .= $modified3.':'.$modified4;
700  // 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
701  $column = PHPExcel_Cell::columnIndexFromString(trim($match[3],'$')) + 100000;
702  $row = 10000000;
703  $cellIndex = $column.$row;
704 
705  $newCellTokens[$cellIndex] = preg_quote($toString);
706  $cellTokens[$cellIndex] = '/(?<![A-Z\$\!])'.preg_quote($fromString).'(?![A-Z])/i';
707  ++$adjustCount;
708  }
709  }
710  }
711  }
712  // Search for cell ranges (e.g. 'Sheet1'!A3:C5 or A3:C5) with or without $ absolutes (e.g. $A1:C$5)
713  $matchCount = preg_match_all('/'.self::REFHELPER_REGEXP_CELLRANGE.'/i', ' '.$formulaBlock.' ', $matches, PREG_SET_ORDER);
714  if ($matchCount > 0) {
715  foreach($matches as $match) {
716  $fromString = ($match[2] > '') ? $match[2].'!' : '';
717  $fromString .= $match[3].':'.$match[4];
718  $modified3 = $this->updateCellReference($match[3],$pBefore,$pNumCols,$pNumRows);
719  $modified4 = $this->updateCellReference($match[4],$pBefore,$pNumCols,$pNumRows);
720 
721  if ($match[3].$match[4] !== $modified3.$modified4) {
722  if (($match[2] == '') || (trim($match[2],"'") == $sheetName)) {
723  $toString = ($match[2] > '') ? $match[2].'!' : '';
724  $toString .= $modified3.':'.$modified4;
725  list($column,$row) = PHPExcel_Cell::coordinateFromString($match[3]);
726  // 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
727  $column = PHPExcel_Cell::columnIndexFromString(trim($column,'$')) + 100000;
728  $row = trim($row,'$') + 10000000;
729  $cellIndex = $column.$row;
730 
731  $newCellTokens[$cellIndex] = preg_quote($toString);
732  $cellTokens[$cellIndex] = '/(?<![A-Z]\$\!)'.preg_quote($fromString).'(?!\d)/i';
733  ++$adjustCount;
734  }
735  }
736  }
737  }
738  // Search for cell references (e.g. 'Sheet1'!A3 or C5) with or without $ absolutes (e.g. $A1 or C$5)
739  $matchCount = preg_match_all('/'.self::REFHELPER_REGEXP_CELLREF.'/i', ' '.$formulaBlock.' ', $matches, PREG_SET_ORDER);
740 
741  if ($matchCount > 0) {
742  foreach($matches as $match) {
743  $fromString = ($match[2] > '') ? $match[2].'!' : '';
744  $fromString .= $match[3];
745 
746  $modified3 = $this->updateCellReference($match[3],$pBefore,$pNumCols,$pNumRows);
747  if ($match[3] !== $modified3) {
748  if (($match[2] == '') || (trim($match[2],"'") == $sheetName)) {
749  $toString = ($match[2] > '') ? $match[2].'!' : '';
750  $toString .= $modified3;
751  list($column,$row) = PHPExcel_Cell::coordinateFromString($match[3]);
752  // 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
753  $column = PHPExcel_Cell::columnIndexFromString(trim($column,'$')) + 100000;
754  $row = trim($row,'$') + 10000000;
755  $cellIndex = $row . $column;
756 
757  $newCellTokens[$cellIndex] = preg_quote($toString);
758  $cellTokens[$cellIndex] = '/(?<![A-Z\$\!])'.preg_quote($fromString).'(?!\d)/i';
759  ++$adjustCount;
760  }
761  }
762  }
763  }
764  if ($adjustCount > 0) {
765  if ($pNumCols > 0 || $pNumRows > 0) {
766  krsort($cellTokens);
767  krsort($newCellTokens);
768  } else {
769  ksort($cellTokens);
770  ksort($newCellTokens);
771  } // Update cell references in the formula
772  $formulaBlock = str_replace('\\','',preg_replace($cellTokens,$newCellTokens,$formulaBlock));
773  }
774  }
775  }
776  unset($formulaBlock);
777 
778  // Then rebuild the formula string
779  return implode('"',$formulaBlocks);
780  }
PHPExcel_Cell\coordinateFromString
static coordinateFromString($pCoordinateString='A1')
Coordinate from string.
Definition: Cell.php:580
PHPExcel_ReferenceHelper\updateCellReference
updateCellReference($pCellRange='A1', $pBefore='A1', $pNumCols=0, $pNumRows=0)
Update cell reference.
Definition: ReferenceHelper.php:792
$column
$column
Definition: 39dropdown.php:62
array
Create styles array
The data for the language used.
Definition: 40duplicateStyle.php:19
PHPExcel_Cell\columnIndexFromString
static columnIndexFromString($pString='A')
Column index from string.
Definition: Cell.php:782
+ Here is the call graph for this function:
+ Here is the caller graph for this function:

◆ updateNamedFormulas()

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

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

816  {
817  if ($oldName == '') {
818  return;
819  }
820 
821  foreach ($pPhpExcel->getWorksheetIterator() as $sheet) {
822  foreach ($sheet->getCellCollection(false) as $cellID) {
823  $cell = $sheet->getCell($cellID);
824  if (($cell !== NULL) && ($cell->getDataType() == PHPExcel_Cell_DataType::TYPE_FORMULA)) {
825  $formula = $cell->getValue();
826  if (strpos($formula, $oldName) !== false) {
827  $formula = str_replace("'" . $oldName . "'!", "'" . $newName . "'!", $formula);
828  $formula = str_replace($oldName . "!", $newName . "!", $formula);
829  $cell->setValueExplicit($formula, PHPExcel_Cell_DataType::TYPE_FORMULA);
830  }
831  }
832  }
833  }
834  }
PHPExcel\getWorksheetIterator
getWorksheetIterator()
Get worksheet iterator.
Definition: PHPExcel.php:839
PHPExcel_Cell_DataType\TYPE_FORMULA
const TYPE_FORMULA
Definition: DataType.php:41
+ Here is the call graph for this function:

Field Documentation

◆ $_instance

PHPExcel_ReferenceHelper::$_instance
staticprivate

Definition at line 50 of file ReferenceHelper.php.

◆ REFHELPER_REGEXP_CELLRANGE

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.

◆ REFHELPER_REGEXP_CELLREF

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.

◆ REFHELPER_REGEXP_COLRANGE

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

Definition at line 43 of file ReferenceHelper.php.

◆ REFHELPER_REGEXP_ROWRANGE

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: