class ReferenceHelper (View source)

Constants

REFHELPER_REGEXP_CELLREF

Regular Expressions

REFHELPER_REGEXP_CELLRANGE

REFHELPER_REGEXP_ROWRANGE

REFHELPER_REGEXP_COLRANGE

Methods

static ReferenceHelper
getInstance()

Get an instance of this class.

__construct()

Create a new ReferenceHelper.

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

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

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

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

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

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

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

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

adjustPageBreaks(Worksheet $pSheet, string $pBefore, int $beforeColumnIndex, int $pNumCols, int $beforeRow, int $pNumRows)

Update page breaks when inserting/deleting rows/columns.

adjustComments(Worksheet $pSheet, string $pBefore, int $beforeColumnIndex, int $pNumCols, int $beforeRow, int $pNumRows)

Update cell comments when inserting/deleting rows/columns.

adjustHyperlinks(Worksheet $pSheet, string $pBefore, int $beforeColumnIndex, int $pNumCols, int $beforeRow, int $pNumRows)

Update hyperlinks when inserting/deleting rows/columns.

adjustDataValidations(Worksheet $pSheet, string $pBefore, int $beforeColumnIndex, int $pNumCols, int $beforeRow, int $pNumRows)

Update data validations when inserting/deleting rows/columns.

adjustMergeCells(Worksheet $pSheet, string $pBefore, int $beforeColumnIndex, int $pNumCols, int $beforeRow, int $pNumRows)

Update merged cells when inserting/deleting rows/columns.

adjustProtectedCells(Worksheet $pSheet, string $pBefore, int $beforeColumnIndex, int $pNumCols, int $beforeRow, int $pNumRows)

Update protected cells when inserting/deleting rows/columns.

adjustColumnDimensions(Worksheet $pSheet, string $pBefore, int $beforeColumnIndex, int $pNumCols, int $beforeRow, int $pNumRows)

Update column dimensions when inserting/deleting rows/columns.

adjustRowDimensions(Worksheet $pSheet, string $pBefore, int $beforeColumnIndex, int $pNumCols, int $beforeRow, int $pNumRows)

Update row dimensions when inserting/deleting rows/columns.

insertNewBefore(string $pBefore, int $pNumCols, int $pNumRows, Worksheet $pSheet)

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

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

Update references within formulas.

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

Update cell reference.

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

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

__clone()

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

Details

static ReferenceHelper getInstance()

Get an instance of this class.

Return Value

ReferenceHelper

protected __construct()

Create a new ReferenceHelper.

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

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

Return Value

int

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

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

Return Value

int

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

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

Return Value

int

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

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

Return Value

int

protected adjustPageBreaks(Worksheet $pSheet, string $pBefore, int $beforeColumnIndex, int $pNumCols, int $beforeRow, int $pNumRows)

Update page breaks when inserting/deleting rows/columns.

Parameters

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)

protected adjustComments(Worksheet $pSheet, string $pBefore, int $beforeColumnIndex, int $pNumCols, int $beforeRow, int $pNumRows)

Update cell comments when inserting/deleting rows/columns.

Parameters

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)

Update hyperlinks when inserting/deleting rows/columns.

Parameters

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)

protected adjustDataValidations(Worksheet $pSheet, string $pBefore, int $beforeColumnIndex, int $pNumCols, int $beforeRow, int $pNumRows)

Update data validations when inserting/deleting rows/columns.

Parameters

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)

protected adjustMergeCells(Worksheet $pSheet, string $pBefore, int $beforeColumnIndex, int $pNumCols, int $beforeRow, int $pNumRows)

Update merged cells when inserting/deleting rows/columns.

Parameters

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)

protected adjustProtectedCells(Worksheet $pSheet, string $pBefore, int $beforeColumnIndex, int $pNumCols, int $beforeRow, int $pNumRows)

Update protected cells when inserting/deleting rows/columns.

Parameters

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)

protected adjustColumnDimensions(Worksheet $pSheet, string $pBefore, int $beforeColumnIndex, int $pNumCols, int $beforeRow, int $pNumRows)

Update column dimensions when inserting/deleting rows/columns.

Parameters

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)

protected adjustRowDimensions(Worksheet $pSheet, string $pBefore, int $beforeColumnIndex, int $pNumCols, int $beforeRow, int $pNumRows)

Update row dimensions when inserting/deleting rows/columns.

Parameters

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)

insertNewBefore(string $pBefore, int $pNumCols, int $pNumRows, Worksheet $pSheet)

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)
Worksheet $pSheet The worksheet that we're editing

Exceptions

Exception

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

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

Return Value

string Updated formula

Exceptions

Exception

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

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

Return Value

string Updated cell range

Exceptions

Exception

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

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

Parameters

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

final __clone()

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

Exceptions

Exception