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

ReferenceHelper

Methods

getInstance()

getInstance() : \PhpOffice\PhpSpreadsheet\ReferenceHelper

Get an instance of this class.

Returns

\PhpOffice\PhpSpreadsheet\ReferenceHelper —

columnSort()

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

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

int —

columnReverseSort()

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

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

int —

cellSort()

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

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

int —

cellReverseSort()

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

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

int —

insertNewBefore()

insertNewBefore(string  $pBefore, int  $pNumCols, int  $pNumRows, \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet  $pSheet) : void

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

Parameters

string $pBefore

Insert before this cell address (e.g. 'A1')

int $pNumCols

Number of columns to insert/delete (negative values indicate deletion)

int $pNumRows

Number of rows to insert/delete (negative values indicate deletion)

\PhpOffice\PhpSpreadsheet\Worksheet\Worksheet $pSheet

The worksheet that we're editing

updateFormulaReferences()

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

Update references within formulas.

Parameters

string $pFormula

Formula to update

string $pBefore

Insert before this one

int $pNumCols

Number of columns to insert

int $pNumRows

Number of rows to insert

string $sheetName

Worksheet name/title

Returns

string —

Updated formula

updateFormulaReferencesAnyWorksheet()

updateFormulaReferencesAnyWorksheet(string  $formula = '', int  $insertColumns, int  $insertRows) : string

Update all cell references within a formula, irrespective of worksheet.

Parameters

string $formula
int $insertColumns
int $insertRows

Returns

string —

updateCellReference()

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

Update cell reference.

Parameters

string $pCellRange

Cell range

string $pBefore

Insert before this one

int $pNumCols

Number of columns to increment

int $pNumRows

Number of rows to increment

Returns

string —

Updated cell range

updateNamedFormulas()

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

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() : mixed

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

Returns

mixed —

__construct()

__construct() : mixed

Create a new ReferenceHelper.

Returns

mixed —

adjustPageBreaks()

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

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')

int $beforeColumnIndex

Index number of the column we're inserting/deleting before

int $pNumCols

Number of columns to insert/delete (negative values indicate deletion)

int $beforeRow

Number of the row we're inserting/deleting before

int $pNumRows

Number of rows to insert/delete (negative values indicate deletion)

adjustComments()

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

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')

int $beforeColumnIndex

Index number of the column we're inserting/deleting before

int $pNumCols

Number of columns to insert/delete (negative values indicate deletion)

int $beforeRow

Number of the row we're inserting/deleting before

int $pNumRows

Number of rows to insert/delete (negative values indicate deletion)

adjustHyperlinks()

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

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')

int $beforeColumnIndex

Index number of the column we're inserting/deleting before

int $pNumCols

Number of columns to insert/delete (negative values indicate deletion)

int $beforeRow

Number of the row we're inserting/deleting before

int $pNumRows

Number of rows to insert/delete (negative values indicate deletion)

adjustDataValidations()

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

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')

int $beforeColumnIndex

Index number of the column we're inserting/deleting before

int $pNumCols

Number of columns to insert/delete (negative values indicate deletion)

int $beforeRow

Number of the row we're inserting/deleting before

int $pNumRows

Number of rows to insert/delete (negative values indicate deletion)

adjustMergeCells()

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

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')

int $beforeColumnIndex

Index number of the column we're inserting/deleting before

int $pNumCols

Number of columns to insert/delete (negative values indicate deletion)

int $beforeRow

Number of the row we're inserting/deleting before

int $pNumRows

Number of rows to insert/delete (negative values indicate deletion)

adjustProtectedCells()

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

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')

int $beforeColumnIndex

Index number of the column we're inserting/deleting before

int $pNumCols

Number of columns to insert/delete (negative values indicate deletion)

int $beforeRow

Number of the row we're inserting/deleting before

int $pNumRows

Number of rows to insert/delete (negative values indicate deletion)

adjustColumnDimensions()

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

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')

int $beforeColumnIndex

Index number of the column we're inserting/deleting before

int $pNumCols

Number of columns to insert/delete (negative values indicate deletion)

int $beforeRow

Number of the row we're inserting/deleting before

int $pNumRows

Number of rows to insert/delete (negative values indicate deletion)

adjustRowDimensions()

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

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')

int $beforeColumnIndex

Index number of the column we're inserting/deleting before

int $pNumCols

Number of columns to insert/delete (negative values indicate deletion)

int $beforeRow

Number of the row we're inserting/deleting before

int $pNumRows

Number of rows to insert/delete (negative values indicate deletion)

cellAddressInDeleteRange()

cellAddressInDeleteRange(string  $cellAddress, int  $beforeRow, int  $pNumRows, int  $beforeColumnIndex, int  $pNumCols) : bool

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

Parameters

string $cellAddress

Address of the cell we're testing

int $beforeRow

Number of the row we're inserting/deleting before

int $pNumRows

Number of rows to insert/delete (negative values indicate deletion)

int $beforeColumnIndex

Index number of the column we're inserting/deleting before

int $pNumCols

Number of columns to insert/delete (negative values indicate deletion)

Returns

bool —

updateCellReferencesAllWorksheets()

updateCellReferencesAllWorksheets(string  $formula, int  $insertColumns, int  $insertRows) : string

Parameters

string $formula
int $insertColumns
int $insertRows

Returns

string —

updateColumnRangesAllWorksheets()

updateColumnRangesAllWorksheets(string  $formula, int  $insertColumns) : string

Parameters

string $formula
int $insertColumns

Returns

string —

updateRowRangesAllWorksheets()

updateRowRangesAllWorksheets(string  $formula, int  $insertRows) : string

Parameters

string $formula
int $insertRows

Returns

string —

updateCellRange()

updateCellRange(string  $pCellRange = 'A1:A1', string  $pBefore = 'A1', int  $pNumCols, int  $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

int $pNumCols

Number of columns to increment

int $pNumRows

Number of rows to increment

Returns

string —

Updated cell range

updateSingleCellReference()

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

Update single cell reference.

Parameters

string $pCellReference

Single cell reference

string $pBefore

Insert before this one

int $pNumCols

Number of columns to increment

int $pNumRows

Number of rows to increment

Returns

string —

Updated cell reference