Documentation

ReferenceHelper
in package

Table of Contents

Constants

REFHELPER_REGEXP_CELLRANGE  = self::SHEETNAME_PART . '?(\$?[a-z]{1,3}\$?\d+):(\$?[a-z]{1,3}\$?\d+)'
REFHELPER_REGEXP_CELLREF  = self::SHEETNAME_PART . '?(?<![:a-z1-9_\.\$])(\$?[a-z]{1,3}\$?\d+)(?=[^:!\d\'])'
REFHELPER_REGEXP_COLRANGE  = self::SHEETNAME_PART . '?(\$?[a-z]{1,3}):(\$?[a-z]{1,3})'
REFHELPER_REGEXP_ROWRANGE  = self::SHEETNAME_PART . '?(\$?\d+):(\$?\d+)'
SHEETNAME_PART  = '((\w*|\'[^!]*\')!)'
Regular Expressions
SHEETNAME_PART_WITH_SLASHES  = '/' . self::SHEETNAME_PART . '/'

Properties

$cellReferenceHelper  : CellReferenceHelper|null
$instance  : ReferenceHelper|null
Instance of this class.

Methods

__clone()  : mixed
__clone implementation. Cloning should not be allowed in a Singleton!
cellReverseSort()  : int
Compare two cell addresses Intended for use as a Callback function for sorting cell addresses by column and row.
cellSort()  : int
Compare two cell addresses Intended for use as a Callback function for sorting cell addresses by column and row.
columnReverseSort()  : int
Compare two column addresses Intended for use as a Callback function for reverse sorting column addresses by column.
columnSort()  : int
Compare two column addresses Intended for use as a Callback function for sorting column addresses by column.
getInstance()  : self
Get an instance of this class.
insertNewBefore()  : void
Insert a new column or row, updating all possible related data.
updateFormulaReferences()  : string
Update references within formulas.
updateFormulaReferencesAnyWorksheet()  : string
Update all cell references within a formula, irrespective of worksheet.
updateNamedFormulae()  : void
Update named formulae (i.e. containing worksheet references / named ranges).
__construct()  : mixed
Create a new ReferenceHelper.
adjustColumnDimensions()  : void
Update column dimensions when inserting/deleting rows/columns.
adjustComments()  : void
Update cell comments when inserting/deleting rows/columns.
adjustConditionalFormatting()  : void
Update conditional formatting styles when inserting/deleting rows/columns.
adjustDataValidations()  : void
Update data validations when inserting/deleting rows/columns.
adjustHyperlinks()  : void
Update hyperlinks when inserting/deleting rows/columns.
adjustMergeCells()  : void
Update merged cells when inserting/deleting rows/columns.
adjustPageBreaks()  : void
Update page breaks when inserting/deleting rows/columns.
adjustProtectedCells()  : void
Update protected cells when inserting/deleting rows/columns.
adjustRowDimensions()  : void
Update row dimensions when inserting/deleting rows/columns.
adjustAutoFilter()  : void
adjustAutoFilterDelete()  : void
adjustAutoFilterDeleteRules()  : void
adjustAutoFilterInsert()  : void
adjustTable()  : void
adjustTableDelete()  : void
adjustTableDeleteRules()  : void
adjustTableInsert()  : void
clearColumnStrips()  : void
clearRowStrips()  : void
clearStripCell()  : void
duplicateStylesByColumn()  : void
duplicateStylesByRow()  : void
matchSheetName()  : bool
sheetnameBeforeCells()  : string
updateCellRange()  : string
Update cell range.
updateCellReference()  : string
Update cell reference.
updateCellReferencesAllWorksheets()  : string
updateColumnRangesAllWorksheets()  : string
updateDefinedNames()  : void
updateNamedFormula()  : void
updateNamedRange()  : void
updateRowRangesAllWorksheets()  : string

Constants

REFHELPER_REGEXP_CELLRANGE

public mixed REFHELPER_REGEXP_CELLRANGE = self::SHEETNAME_PART . '?(\$?[a-z]{1,3}\$?\d+):(\$?[a-z]{1,3}\$?\d+)'

REFHELPER_REGEXP_CELLREF

public mixed REFHELPER_REGEXP_CELLREF = self::SHEETNAME_PART . '?(?<![:a-z1-9_\.\$])(\$?[a-z]{1,3}\$?\d+)(?=[^:!\d\'])'

REFHELPER_REGEXP_COLRANGE

public mixed REFHELPER_REGEXP_COLRANGE = self::SHEETNAME_PART . '?(\$?[a-z]{1,3}):(\$?[a-z]{1,3})'

REFHELPER_REGEXP_ROWRANGE

public mixed REFHELPER_REGEXP_ROWRANGE = self::SHEETNAME_PART . '?(\$?\d+):(\$?\d+)'

SHEETNAME_PART

Regular Expressions

private mixed SHEETNAME_PART = '((\w*|\'[^!]*\')!)'

SHEETNAME_PART_WITH_SLASHES

private mixed SHEETNAME_PART_WITH_SLASHES = '/' . self::SHEETNAME_PART . '/'

Properties

Methods

__clone()

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

public final __clone() : mixed

cellReverseSort()

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

public static cellReverseSort(string $a, string $b) : int
Parameters
$a : string

First cell to test (e.g. 'AA1')

$b : string

Second cell to test (e.g. 'Z1')

Return values
int

cellSort()

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

public static cellSort(string $a, string $b) : int
Parameters
$a : string

First cell to test (e.g. 'AA1')

$b : string

Second cell to test (e.g. 'Z1')

Return values
int

columnReverseSort()

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

public static columnReverseSort(string $a, string $b) : int
Parameters
$a : string

First column to test (e.g. 'AA')

$b : string

Second column to test (e.g. 'Z')

Return values
int

columnSort()

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

public static columnSort(string $a, string $b) : int
Parameters
$a : string

First column to test (e.g. 'AA')

$b : string

Second column to test (e.g. 'Z')

Return values
int

getInstance()

Get an instance of this class.

public static getInstance() : self
Return values
self

insertNewBefore()

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

public insertNewBefore(string $beforeCellAddress, int $numberOfColumns, int $numberOfRows, Worksheet $worksheet) : void
Parameters
$beforeCellAddress : string

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

$numberOfColumns : int

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

$numberOfRows : int

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

$worksheet : Worksheet

The worksheet that we're editing

updateFormulaReferences()

Update references within formulas.

public updateFormulaReferences([string $formula = '' ][, string $beforeCellAddress = 'A1' ][, int $numberOfColumns = 0 ][, int $numberOfRows = 0 ][, string $worksheetName = '' ][, bool $includeAbsoluteReferences = false ][, bool $onlyAbsoluteReferences = false ]) : string
Parameters
$formula : string = ''

Formula to update

$beforeCellAddress : string = 'A1'

Insert before this one

$numberOfColumns : int = 0

Number of columns to insert

$numberOfRows : int = 0

Number of rows to insert

$worksheetName : string = ''

Worksheet name/title

$includeAbsoluteReferences : bool = false
$onlyAbsoluteReferences : bool = false
Return values
string —

Updated formula

updateFormulaReferencesAnyWorksheet()

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

public updateFormulaReferencesAnyWorksheet([string $formula = '' ][, int $numberOfColumns = 0 ][, int $numberOfRows = 0 ]) : string
Parameters
$formula : string = ''
$numberOfColumns : int = 0
$numberOfRows : int = 0
Return values
string

updateNamedFormulae()

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

public updateNamedFormulae(Spreadsheet $spreadsheet[, string $oldName = '' ][, string $newName = '' ]) : void
Parameters
$spreadsheet : Spreadsheet

Object to update

$oldName : string = ''

Old name (name to replace)

$newName : string = ''

New name

__construct()

Create a new ReferenceHelper.

protected __construct() : mixed

adjustColumnDimensions()

Update column dimensions when inserting/deleting rows/columns.

protected adjustColumnDimensions(Worksheet $worksheet) : void
Parameters
$worksheet : Worksheet

The worksheet that we're editing

adjustComments()

Update cell comments when inserting/deleting rows/columns.

protected adjustComments(Worksheet $worksheet) : void
Parameters
$worksheet : Worksheet

The worksheet that we're editing

adjustConditionalFormatting()

Update conditional formatting styles when inserting/deleting rows/columns.

protected adjustConditionalFormatting(Worksheet $worksheet, int $numberOfColumns, int $numberOfRows) : void
Parameters
$worksheet : Worksheet

The worksheet that we're editing

$numberOfColumns : int

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

$numberOfRows : int

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

adjustDataValidations()

Update data validations when inserting/deleting rows/columns.

protected adjustDataValidations(Worksheet $worksheet, int $numberOfColumns, int $numberOfRows, string $beforeCellAddress) : void
Parameters
$worksheet : Worksheet

The worksheet that we're editing

$numberOfColumns : int

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

$numberOfRows : int

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

$beforeCellAddress : string

adjustHyperlinks()

Update hyperlinks when inserting/deleting rows/columns.

protected adjustHyperlinks(Worksheet $worksheet, int $numberOfColumns, int $numberOfRows) : void
Parameters
$worksheet : Worksheet

The worksheet that we're editing

$numberOfColumns : int

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

$numberOfRows : int

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

adjustMergeCells()

Update merged cells when inserting/deleting rows/columns.

protected adjustMergeCells(Worksheet $worksheet) : void
Parameters
$worksheet : Worksheet

The worksheet that we're editing

adjustPageBreaks()

Update page breaks when inserting/deleting rows/columns.

protected adjustPageBreaks(Worksheet $worksheet, int $numberOfColumns, int $numberOfRows) : void
Parameters
$worksheet : Worksheet

The worksheet that we're editing

$numberOfColumns : int

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

$numberOfRows : int

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

adjustProtectedCells()

Update protected cells when inserting/deleting rows/columns.

protected adjustProtectedCells(Worksheet $worksheet, int $numberOfColumns, int $numberOfRows) : void
Parameters
$worksheet : Worksheet

The worksheet that we're editing

$numberOfColumns : int

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

$numberOfRows : int

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

adjustRowDimensions()

Update row dimensions when inserting/deleting rows/columns.

protected adjustRowDimensions(Worksheet $worksheet, int $beforeRow, int $numberOfRows) : void
Parameters
$worksheet : Worksheet

The worksheet that we're editing

$beforeRow : int

Number of the row we're inserting/deleting before

$numberOfRows : int

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

adjustAutoFilter()

private adjustAutoFilter(Worksheet $worksheet, string $beforeCellAddress, int $numberOfColumns) : void
Parameters
$worksheet : Worksheet
$beforeCellAddress : string
$numberOfColumns : int

adjustAutoFilterDelete()

private adjustAutoFilterDelete(int $startCol, int $numberOfColumns, int $rangeEnd, AutoFilter $autoFilter) : void
Parameters
$startCol : int
$numberOfColumns : int
$rangeEnd : int
$autoFilter : AutoFilter

adjustAutoFilterDeleteRules()

private adjustAutoFilterDeleteRules(int $columnIndex, int $numberOfColumns, array<string|int, mixed> $autoFilterColumns, AutoFilter $autoFilter) : void
Parameters
$columnIndex : int
$numberOfColumns : int
$autoFilterColumns : array<string|int, mixed>
$autoFilter : AutoFilter

adjustAutoFilterInsert()

private adjustAutoFilterInsert(int $startCol, int $numberOfColumns, int $rangeEnd, AutoFilter $autoFilter) : void
Parameters
$startCol : int
$numberOfColumns : int
$rangeEnd : int
$autoFilter : AutoFilter

adjustTable()

private adjustTable(Worksheet $worksheet, string $beforeCellAddress, int $numberOfColumns) : void
Parameters
$worksheet : Worksheet
$beforeCellAddress : string
$numberOfColumns : int

adjustTableDelete()

private adjustTableDelete(int $startCol, int $numberOfColumns, int $rangeEnd, Table $table) : void
Parameters
$startCol : int
$numberOfColumns : int
$rangeEnd : int
$table : Table

adjustTableDeleteRules()

private adjustTableDeleteRules(int $columnIndex, int $numberOfColumns, array<string|int, mixed> $tableColumns, Table $table) : void
Parameters
$columnIndex : int
$numberOfColumns : int
$tableColumns : array<string|int, mixed>
$table : Table

adjustTableInsert()

private adjustTableInsert(int $startCol, int $numberOfColumns, int $rangeEnd, Table $table) : void
Parameters
$startCol : int
$numberOfColumns : int
$rangeEnd : int
$table : Table

clearColumnStrips()

private clearColumnStrips(int $highestRow, int $beforeColumn, int $numberOfColumns, Worksheet $worksheet) : void
Parameters
$highestRow : int
$beforeColumn : int
$numberOfColumns : int
$worksheet : Worksheet

clearRowStrips()

private clearRowStrips(string $highestColumn, int $beforeColumn, int $beforeRow, int $numberOfRows, Worksheet $worksheet) : void
Parameters
$highestColumn : string
$beforeColumn : int
$beforeRow : int
$numberOfRows : int
$worksheet : Worksheet

duplicateStylesByColumn()

private duplicateStylesByColumn(Worksheet $worksheet, int $beforeColumn, int $beforeRow, int $highestRow, int $numberOfColumns) : void
Parameters
$worksheet : Worksheet
$beforeColumn : int
$beforeRow : int
$highestRow : int
$numberOfColumns : int

duplicateStylesByRow()

private duplicateStylesByRow(Worksheet $worksheet, int $beforeColumn, int $beforeRow, string $highestColumn, int $numberOfRows) : void
Parameters
$worksheet : Worksheet
$beforeColumn : int
$beforeRow : int
$highestColumn : string
$numberOfRows : int

matchSheetName()

private static matchSheetName(string|null $match, string $worksheetName) : bool
Parameters
$match : string|null
$worksheetName : string
Return values
bool

sheetnameBeforeCells()

private static sheetnameBeforeCells(string $match, string $worksheetName, string $cells) : string
Parameters
$match : string
$worksheetName : string
$cells : string
Return values
string

updateCellRange()

Update cell range.

private updateCellRange([string $cellRange = 'A1:A1' ][, bool $includeAbsoluteReferences = false ][, bool $onlyAbsoluteReferences = false ]) : string
Parameters
$cellRange : string = 'A1:A1'

Cell range (e.g. 'B2:D4', 'B:C' or '2:3')

$includeAbsoluteReferences : bool = false
$onlyAbsoluteReferences : bool = false
Return values
string —

Updated cell range

updateCellReference()

Update cell reference.

private updateCellReference([string $cellReference = 'A1' ][, bool $includeAbsoluteReferences = false ][, bool $onlyAbsoluteReferences = false ][, bool|null $topLeft = null ]) : string
Parameters
$cellReference : string = 'A1'

Cell address or range of addresses

$includeAbsoluteReferences : bool = false
$onlyAbsoluteReferences : bool = false
$topLeft : bool|null = null
Return values
string —

Updated cell range

updateCellReferencesAllWorksheets()

private updateCellReferencesAllWorksheets(string $formula, int $numberOfColumns, int $numberOfRows) : string
Parameters
$formula : string
$numberOfColumns : int
$numberOfRows : int
Return values
string

updateColumnRangesAllWorksheets()

private updateColumnRangesAllWorksheets(string $formula, int $numberOfColumns) : string
Parameters
$formula : string
$numberOfColumns : int
Return values
string

updateDefinedNames()

private updateDefinedNames(Worksheet $worksheet, string $beforeCellAddress, int $numberOfColumns, int $numberOfRows) : void
Parameters
$worksheet : Worksheet
$beforeCellAddress : string
$numberOfColumns : int
$numberOfRows : int

updateNamedFormula()

private updateNamedFormula(DefinedName $definedName, Worksheet $worksheet, string $beforeCellAddress, int $numberOfColumns, int $numberOfRows) : void
Parameters
$definedName : DefinedName
$worksheet : Worksheet
$beforeCellAddress : string
$numberOfColumns : int
$numberOfRows : int

updateNamedRange()

private updateNamedRange(DefinedName $definedName, Worksheet $worksheet, string $beforeCellAddress, int $numberOfColumns, int $numberOfRows) : void
Parameters
$definedName : DefinedName
$worksheet : Worksheet
$beforeCellAddress : string
$numberOfColumns : int
$numberOfRows : int

updateRowRangesAllWorksheets()

private updateRowRangesAllWorksheets(string $formula, int $numberOfRows) : string
Parameters
$formula : string
$numberOfRows : int
Return values
string 

        

        
    




    

    

                                

                

                                
    

        On this page

        
    


                

                            

            

    

        

            
Search results