\PhpOffice\PhpSpreadsheetReferenceHelper

Summary

Methods

Properties

Constants

getInstance()
columnSort()
columnReverseSort()
cellSort()
cellReverseSort()
insertNewBefore()
updateFormulaReferences()
updateCellReference()
updateNamedFormulas()
__clone()

No public properties found

REFHELPER_REGEXP_CELLREF
REFHELPER_REGEXP_CELLRANGE
REFHELPER_REGEXP_ROWRANGE
REFHELPER_REGEXP_COLRANGE

__construct()
adjustPageBreaks()
adjustComments()
adjustHyperlinks()
adjustDataValidations()
adjustMergeCells()
adjustProtectedCells()
adjustColumnDimensions()
adjustRowDimensions()

No protected properties found

N/A

cellAddressInDeleteRange()
updateCellRange()
updateSingleCellReference()

$instance

N/A

File: vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/ReferenceHelper.php
Package: Default
Class hierarchy: \PhpOffice\PhpSpreadsheet\ReferenceHelper

Constants

REFHELPER_REGEXP_CELLREF

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

Regular Expressions

REFHELPER_REGEXP_CELLRANGE

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

REFHELPER_REGEXP_ROWRANGE

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

REFHELPER_REGEXP_COLRANGE

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

Properties

$instance

$instance : \PhpOffice\PhpSpreadsheet\ReferenceHelper

Instance of this class.

Type

\PhpOffice\PhpSpreadsheet\ReferenceHelper

Methods

getInstance()

getInstance() : \PhpOffice\PhpSpreadsheet\ReferenceHelper

Get an instance of this class.

Returns

\PhpOffice\PhpSpreadsheet\ReferenceHelper

columnSort()

columnSort(string  $a, string  $b) : integer

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

Parameters

string	$a	First column to test (e.g. 'AA')
string	$b	Second column to test (e.g. 'Z')

Returns

integer

columnReverseSort()

columnReverseSort(string  $a, string  $b) : integer

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

Parameters

string	$a	First column to test (e.g. 'AA')
string	$b	Second column to test (e.g. 'Z')

Returns

integer

cellSort()

cellSort(string  $a, string  $b) : integer

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

Parameters

string	$a	First cell to test (e.g. 'AA1')
string	$b	Second cell to test (e.g. 'Z1')

Returns

integer

cellReverseSort()

cellReverseSort(string  $a, string  $b) : integer

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

Parameters

string	$a	First cell to test (e.g. 'AA1')
string	$b	Second cell to test (e.g. 'Z1')

Returns

integer

insertNewBefore()

insertNewBefore(string  $pBefore, integer  $pNumCols, integer  $pNumRows, \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet  $pSheet)

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

Parameters

string	$pBefore	Insert before this cell address (e.g. 'A1')
integer	$pNumCols	Number of columns to insert/delete (negative values indicate deletion)
integer	$pNumRows	Number of rows to insert/delete (negative values indicate deletion)
\PhpOffice\PhpSpreadsheet\Worksheet\Worksheet	$pSheet	The worksheet that we're editing

Throws

\PhpOffice\PhpSpreadsheet\Exception

updateFormulaReferences()

updateFormulaReferences(string  $pFormula = '', integer  $pBefore = 'A1', integer  $pNumCols, integer  $pNumRows, string  $sheetName = '') : string

Update references within formulas.

Parameters

string	$pFormula	Formula to update
integer	$pBefore	Insert before this one
integer	$pNumCols	Number of columns to insert
integer	$pNumRows	Number of rows to insert
string	$sheetName	Worksheet name/title

Throws

\PhpOffice\PhpSpreadsheet\Exception

Returns

string —

Updated formula

updateCellReference()

updateCellReference(string  $pCellRange = 'A1', string  $pBefore = 'A1', integer  $pNumCols, integer  $pNumRows) : string

Update cell reference.

Parameters

string	$pCellRange	Cell range
string	$pBefore	Insert before this one
integer	$pNumCols	Number of columns to increment
integer	$pNumRows	Number of rows to increment

Throws

\PhpOffice\PhpSpreadsheet\Exception

Returns

string —

Updated cell range

updateNamedFormulas()

updateNamedFormulas(\PhpOffice\PhpSpreadsheet\Spreadsheet  $spreadsheet, string  $oldName = '', string  $newName = '')

Update named formulas (i.e. containing worksheet references / named ranges).

Parameters

\PhpOffice\PhpSpreadsheet\Spreadsheet	$spreadsheet	Object to update
string	$oldName	Old name (name to replace)
string	$newName	New name

__clone()

__clone()

__clone implementation. Cloning should not be allowed in a Singleton!

Throws

\PhpOffice\PhpSpreadsheet\Exception

__construct()

__construct()

Create a new ReferenceHelper.

adjustPageBreaks()

adjustPageBreaks(\PhpOffice\PhpSpreadsheet\Worksheet\Worksheet  $pSheet, string  $pBefore, integer  $beforeColumnIndex, integer  $pNumCols, integer  $beforeRow, integer  $pNumRows)

Update page breaks when inserting/deleting rows/columns.

Parameters

\PhpOffice\PhpSpreadsheet\Worksheet\Worksheet	$pSheet	The worksheet that we're editing
string	$pBefore	Insert/Delete before this cell address (e.g. 'A1')
integer	$beforeColumnIndex	Index number of the column we're inserting/deleting before
integer	$pNumCols	Number of columns to insert/delete (negative values indicate deletion)
integer	$beforeRow	Number of the row we're inserting/deleting before
integer	$pNumRows	Number of rows to insert/delete (negative values indicate deletion)

adjustComments()

adjustComments(\PhpOffice\PhpSpreadsheet\Worksheet\Worksheet  $pSheet, string  $pBefore, integer  $beforeColumnIndex, integer  $pNumCols, integer  $beforeRow, integer  $pNumRows)

Update cell comments when inserting/deleting rows/columns.

Parameters

\PhpOffice\PhpSpreadsheet\Worksheet\Worksheet	$pSheet	The worksheet that we're editing
string	$pBefore	Insert/Delete before this cell address (e.g. 'A1')
integer	$beforeColumnIndex	Index number of the column we're inserting/deleting before
integer	$pNumCols	Number of columns to insert/delete (negative values indicate deletion)
integer	$beforeRow	Number of the row we're inserting/deleting before
integer	$pNumRows	Number of rows to insert/delete (negative values indicate deletion)

adjustHyperlinks()

adjustHyperlinks(\PhpOffice\PhpSpreadsheet\Worksheet\Worksheet  $pSheet, string  $pBefore, integer  $beforeColumnIndex, integer  $pNumCols, integer  $beforeRow, integer  $pNumRows)

Update hyperlinks when inserting/deleting rows/columns.

Parameters

\PhpOffice\PhpSpreadsheet\Worksheet\Worksheet	$pSheet	The worksheet that we're editing
string	$pBefore	Insert/Delete before this cell address (e.g. 'A1')
integer	$beforeColumnIndex	Index number of the column we're inserting/deleting before
integer	$pNumCols	Number of columns to insert/delete (negative values indicate deletion)
integer	$beforeRow	Number of the row we're inserting/deleting before
integer	$pNumRows	Number of rows to insert/delete (negative values indicate deletion)

adjustDataValidations()

adjustDataValidations(\PhpOffice\PhpSpreadsheet\Worksheet\Worksheet  $pSheet, string  $pBefore, integer  $beforeColumnIndex, integer  $pNumCols, integer  $beforeRow, integer  $pNumRows)

Update data validations when inserting/deleting rows/columns.

Parameters

\PhpOffice\PhpSpreadsheet\Worksheet\Worksheet	$pSheet	The worksheet that we're editing
string	$pBefore	Insert/Delete before this cell address (e.g. 'A1')
integer	$beforeColumnIndex	Index number of the column we're inserting/deleting before
integer	$pNumCols	Number of columns to insert/delete (negative values indicate deletion)
integer	$beforeRow	Number of the row we're inserting/deleting before
integer	$pNumRows	Number of rows to insert/delete (negative values indicate deletion)

adjustMergeCells()

adjustMergeCells(\PhpOffice\PhpSpreadsheet\Worksheet\Worksheet  $pSheet, string  $pBefore, integer  $beforeColumnIndex, integer  $pNumCols, integer  $beforeRow, integer  $pNumRows)

Update merged cells when inserting/deleting rows/columns.

Parameters

\PhpOffice\PhpSpreadsheet\Worksheet\Worksheet	$pSheet	The worksheet that we're editing
string	$pBefore	Insert/Delete before this cell address (e.g. 'A1')
integer	$beforeColumnIndex	Index number of the column we're inserting/deleting before
integer	$pNumCols	Number of columns to insert/delete (negative values indicate deletion)
integer	$beforeRow	Number of the row we're inserting/deleting before
integer	$pNumRows	Number of rows to insert/delete (negative values indicate deletion)

adjustProtectedCells()

adjustProtectedCells(\PhpOffice\PhpSpreadsheet\Worksheet\Worksheet  $pSheet, string  $pBefore, integer  $beforeColumnIndex, integer  $pNumCols, integer  $beforeRow, integer  $pNumRows)

Update protected cells when inserting/deleting rows/columns.

Parameters

\PhpOffice\PhpSpreadsheet\Worksheet\Worksheet	$pSheet	The worksheet that we're editing
string	$pBefore	Insert/Delete before this cell address (e.g. 'A1')
integer	$beforeColumnIndex	Index number of the column we're inserting/deleting before
integer	$pNumCols	Number of columns to insert/delete (negative values indicate deletion)
integer	$beforeRow	Number of the row we're inserting/deleting before
integer	$pNumRows	Number of rows to insert/delete (negative values indicate deletion)

adjustColumnDimensions()

adjustColumnDimensions(\PhpOffice\PhpSpreadsheet\Worksheet\Worksheet  $pSheet, string  $pBefore, integer  $beforeColumnIndex, integer  $pNumCols, integer  $beforeRow, integer  $pNumRows)

Update column dimensions when inserting/deleting rows/columns.

Parameters

\PhpOffice\PhpSpreadsheet\Worksheet\Worksheet	$pSheet	The worksheet that we're editing
string	$pBefore	Insert/Delete before this cell address (e.g. 'A1')
integer	$beforeColumnIndex	Index number of the column we're inserting/deleting before
integer	$pNumCols	Number of columns to insert/delete (negative values indicate deletion)
integer	$beforeRow	Number of the row we're inserting/deleting before
integer	$pNumRows	Number of rows to insert/delete (negative values indicate deletion)

adjustRowDimensions()

adjustRowDimensions(\PhpOffice\PhpSpreadsheet\Worksheet\Worksheet  $pSheet, string  $pBefore, integer  $beforeColumnIndex, integer  $pNumCols, integer  $beforeRow, integer  $pNumRows)

Update row dimensions when inserting/deleting rows/columns.

Parameters

\PhpOffice\PhpSpreadsheet\Worksheet\Worksheet	$pSheet	The worksheet that we're editing
string	$pBefore	Insert/Delete before this cell address (e.g. 'A1')
integer	$beforeColumnIndex	Index number of the column we're inserting/deleting before
integer	$pNumCols	Number of columns to insert/delete (negative values indicate deletion)
integer	$beforeRow	Number of the row we're inserting/deleting before
integer	$pNumRows	Number of rows to insert/delete (negative values indicate deletion)

cellAddressInDeleteRange()

cellAddressInDeleteRange(string  $cellAddress, integer  $beforeRow, integer  $pNumRows, integer  $beforeColumnIndex, integer  $pNumCols) : boolean

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

Parameters

string	$cellAddress	Address of the cell we're testing
integer	$beforeRow	Number of the row we're inserting/deleting before
integer	$pNumRows	Number of rows to insert/delete (negative values indicate deletion)
integer	$beforeColumnIndex	Index number of the column we're inserting/deleting before
integer	$pNumCols	Number of columns to insert/delete (negative values indicate deletion)

Returns

boolean

updateCellRange()

updateCellRange(string  $pCellRange = 'A1:A1', string  $pBefore = 'A1', integer  $pNumCols, integer  $pNumRows) : string

Update cell range.

Parameters

string	$pCellRange	Cell range (e.g. 'B2:D4', 'B:C' or '2:3')
string	$pBefore	Insert before this one
integer	$pNumCols	Number of columns to increment
integer	$pNumRows	Number of rows to increment

Throws

\PhpOffice\PhpSpreadsheet\Exception

Returns

string —

Updated cell range

updateSingleCellReference()

updateSingleCellReference(string  $pCellReference = 'A1', string  $pBefore = 'A1', integer  $pNumCols, integer  $pNumRows) : string

Update single cell reference.

Parameters

string	$pCellReference	Single cell reference
string	$pBefore	Insert before this one
integer	$pNumCols	Number of columns to increment
integer	$pNumRows	Number of rows to increment

Throws

\PhpOffice\PhpSpreadsheet\Exception

Returns

string —

Updated cell reference