ILIAS  release_5-3 Revision v5.3.23-19-g915713cf615
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.

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

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

Referenced by insertNewBefore().

+ 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.

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 }
$key
$key
Definition: croninfo.php:18

References $key, and updateCellReference().

Referenced by insertNewBefore().

+ 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.

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 }

References $key, and updateCellReference().

Referenced by insertNewBefore().

+ 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.

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 }

References $key, and updateCellReference().

Referenced by insertNewBefore().

+ 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.

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 }

References $key, and updateCellReference().

Referenced by insertNewBefore().

+ 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.

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_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
PHPExcel_Worksheet\BREAK_NONE
const BREAK_NONE
Definition: Worksheet.php:39

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

Referenced by insertNewBefore().

+ 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.

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 }

References $key, and updateCellReference().

Referenced by insertNewBefore().

+ 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.

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 }
$i
$i
Definition: disco.tpl.php:19

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

Referenced by insertNewBefore().

+ 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.

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 }

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

Referenced by updateCellReference().

+ 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.

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\stringFromColumnIndex
static stringFromColumnIndex($pColumnIndex=0)
String from columnindex.
Definition: Cell.php:825
PHPExcel_Cell\columnIndexFromString
static columnIndexFromString($pString='A')
Column index from string.
Definition: Cell.php:782

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

Referenced by _updateCellRange(), and updateCellReference().

+ 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.

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 }

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

+ 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.

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:37
PHPExcel_ReferenceHelper\$_instance
static $_instance
Definition: ReferenceHelper.php:50

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

+ 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.

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 }
$objDrawing
$objDrawing
Definition: 04printing.php:70
$column
$column
Definition: 39dropdown.php:62
PHPExcel_Cell_DataType\TYPE_FORMULA
const TYPE_FORMULA
Definition: DataType.php:41
PHPExcel_Cell_DataType\TYPE_NULL
const TYPE_NULL
Definition: DataType.php:44
PHPExcel_Cell\rangeBoundaries
static rangeBoundaries($pRange='A1:A1')
Calculate range boundaries.
Definition: Cell.php:707
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_ReferenceHelper\_adjustRowDimensions
_adjustRowDimensions($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
Update row dimensions when inserting/deleting rows/columns.
Definition: ReferenceHelper.php:350
PHPExcel_ReferenceHelper\_adjustHyperlinks
_adjustHyperlinks($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
Update hyperlinks when inserting/deleting rows/columns.
Definition: ReferenceHelper.php:228
PHPExcel_ReferenceHelper\_adjustMergeCells
_adjustMergeCells($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
Update merged cells when inserting/deleting rows/columns.
Definition: ReferenceHelper.php:279
PHPExcel_ReferenceHelper\updateFormulaReferences
updateFormulaReferences($pFormula='', $pBefore='A1', $pNumCols=0, $pNumRows=0, $sheetName='')
Update references within formulas.
Definition: ReferenceHelper.php:653
PHPExcel_ReferenceHelper\_adjustDataValidations
_adjustDataValidations($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
Update data validations when inserting/deleting rows/columns.
Definition: ReferenceHelper.php:254
PHPExcel_ReferenceHelper\_adjustComments
_adjustComments($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
Update cell comments when inserting/deleting rows/columns.
Definition: ReferenceHelper.php:201
PHPExcel_ReferenceHelper\_adjustColumnDimensions
_adjustColumnDimensions($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
Update column dimensions when inserting/deleting rows/columns.
Definition: ReferenceHelper.php:325
PHPExcel_ReferenceHelper\_adjustProtectedCells
_adjustProtectedCells($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
Update protected cells when inserting/deleting rows/columns.
Definition: ReferenceHelper.php:300
PHPExcel_Worksheet\getHighestColumn
getHighestColumn($row=null)
Get highest worksheet column.
Definition: Worksheet.php:1028
PHPExcel_Worksheet\garbageCollect
garbageCollect()
Run PHPExcel garabage collector.
Definition: Worksheet.php:2588
PHPExcel_Worksheet\conditionalStylesExists
conditionalStylesExists($pCoordinate='A1')
Do conditional styles exist for this cell?
Definition: Worksheet.php:1445
PHPExcel_Worksheet\getCell
getCell($pCoordinate='A1')
Get cell at a specific coordinate.
Definition: Worksheet.php:1153
PHPExcel_Worksheet\getAutoFilter
getAutoFilter()
Get Autofilter.
Definition: Worksheet.php:1892
PHPExcel_Worksheet\setConditionalStyles
setConditionalStyles($pCoordinate='A1', $pValue)
Set conditional styles.
Definition: Worksheet.php:1482
PHPExcel_Worksheet\setAutoFilter
setAutoFilter($pValue)
Set AutoFilter.
Definition: Worksheet.php:1905
PHPExcel_Worksheet\getHashCode
getHashCode()
Get hash code.
Definition: Worksheet.php:2632
PHPExcel_Worksheet\freezePane
freezePane($pCell='')
Freeze Pane.
Definition: Worksheet.php:1969
PHPExcel_Worksheet\getHighestRow
getHighestRow($column=null)
Get highest worksheet row.
Definition: Worksheet.php:1055
PHPExcel_Worksheet\getFreezePane
getFreezePane()
Get Freeze Pane.
Definition: Worksheet.php:1952
PHPExcel_Worksheet\getTitle
getTitle()
Get title.
Definition: Worksheet.php:817
PHPExcel_Worksheet\getPageSetup
getPageSetup()
Get page setup.
Definition: Worksheet.php:914
PHPExcel_Worksheet\getConditionalStyles
getConditionalStyles($pCoordinate='A1')
Get conditional styles for a cell.
Definition: Worksheet.php:1430
PHPExcel_Worksheet\getDrawingCollection
getDrawingCollection()
Get collection of drawings.
Definition: Worksheet.php:554
PHPExcel_Worksheet\removeConditionalStyles
removeConditionalStyles($pCoordinate='A1')
Removes conditional styles for a cell.
Definition: Worksheet.php:1459
PHPExcel_Worksheet\cellExists
cellExists($pCoordinate='A1')
Does the cell at a specific coordinate exist?
Definition: Worksheet.php:1256
PHPExcel_Worksheet\getCellCacheController
getCellCacheController()
Return the cache controller for the cell collection.
Definition: Worksheet.php:413
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\getCellCollection
getCellCollection($pSorted=true)
Get collection of cells.
Definition: Worksheet.php:484
PHPExcel_Worksheet\getParent
getParent()
Get parent.
Definition: Worksheet.php:786

References $autoFilter, $column, $conditionalStyles, $i, $objDrawing, $row, _adjustColumnDimensions(), _adjustComments(), _adjustDataValidations(), _adjustHyperlinks(), _adjustMergeCells(), _adjustPageBreaks(), _adjustProtectedCells(), _adjustRowDimensions(), 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().

+ 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.

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\_updateCellRange
_updateCellRange($pCellRange='A1:A1', $pBefore='A1', $pNumCols=0, $pNumRows=0)
Update cell range.
Definition: ReferenceHelper.php:846
PHPExcel_ReferenceHelper\_updateSingleCellReference
_updateSingleCellReference($pCellReference='A1', $pBefore='A1', $pNumCols=0, $pNumRows=0)
Update single cell reference.
Definition: ReferenceHelper.php:883

References _updateCellRange(), and _updateSingleCellReference().

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

+ 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.

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 }

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

Referenced by insertNewBefore().

+ 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.

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

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

+ Here is the call graph for this function:

Field Documentation

◆ $_instance

PHPExcel_ReferenceHelper::$_instance
staticprivate

Definition at line 50 of file ReferenceHelper.php.

Referenced by getInstance().

◆ 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: