Documentation

Worksheet
in package
implements IComparable

Table of Contents

Interfaces

IComparable

Constants

BREAK_COLUMN  = 2
BREAK_NONE  = 0
BREAK_ROW  = 1
BREAK_ROW_MAX_COLUMN  = 16383
MERGE_CELL_CONTENT_EMPTY  = 'empty'
MERGE_CELL_CONTENT_HIDE  = 'hide'
MERGE_CELL_CONTENT_MERGE  = 'merge'
PANE_FROZEN  = 'frozen'
PANE_FROZENSPLIT  = 'frozenSplit'
PANE_SPLIT  = 'split'
SHEET_TITLE_MAXIMUM_LENGTH  = 31
Maximum 31 characters allowed for sheet title.
SHEETSTATE_HIDDEN  = 'hidden'
SHEETSTATE_VERYHIDDEN  = 'veryHidden'
SHEETSTATE_VISIBLE  = 'visible'
SHEET_NAME_REQUIRES_NO_QUOTES  = '/^[_\p{L}][_\p{L}\p{N}]*$/mui'
VALIDFROZENSTATE  = [self::PANE_FROZEN, self::PANE_FROZENSPLIT]
VALIDPANESTATE  = [self::PANE_FROZEN, self::PANE_SPLIT, self::PANE_FROZENSPLIT]

Properties

$activeCell  : string
Active cell. (Only one!).
$activePane  : string
$autoFilter  : AutoFilter
Autofilter Range and selection.
$backgroundExtension  : string
$backgroundImage  : string
$backgroundMime  : string
$cachedHighestColumn  : int
Cached highest column.
$cachedHighestRow  : int
Cached highest row.
$cellCollection  : Cells
Collection of cells.
$cellCollectionInitialized  : bool
$chartCollection  : ArrayObject<int, Chart>
Collection of Chart objects.
$codeName  : string|null
CodeName.
$columnBreaks  : array<string|int, PageBreak>
Collection of column breaks.
$columnDimensions  : array<string|int, ColumnDimension>
Collection of column dimensions.
$comments  : array<string|int, Comment>
Collection of comments.
$conditionalStylesCollection  : array<string|int, mixed>
Conditional styles. Indexed by cell coordinate, e.g. 'A1'.
$dataValidationCollection  : array<string|int, mixed>
Data validation objects. Indexed by cell coordinate, e.g. 'A1'.
$defaultColumnDimension  : ColumnDimension
Default column dimension.
$defaultRowDimension  : RowDimension
Default row dimension.
$dirty  : bool
Dirty flag.
$drawingCollection  : ArrayObject<int, BaseDrawing>
Collection of drawings.
$freezePane  : string|null
Freeze pane.
$hash  : string
Hash.
$headerFooter  : HeaderFooter
Page header/footer.
$hyperlinkCollection  : array<string|int, mixed>
Hyperlinks. Indexed by cell coordinate, e.g. 'A1'.
$invalidCharacters  : array<string|int, mixed>
Invalid characters in sheet title.
$mergeCells  : array<string|int, string>
Collection of merged cell ranges.
$pageMargins  : PageMargins
Page margins.
$pageSetup  : PageSetup
Page setup.
$panes  : array<string|int, null|Pane>
Properties of the 4 panes.
$paneState  : string
$paneTopLeftCell  : string
$parent  : Spreadsheet|null
Parent spreadsheet.
$printGridlines  : bool
Print gridlines?
$protectedCells  : array<string|int, ProtectedRange>
Collection of protected cell ranges.
$protection  : Protection
Protection.
$rightToLeft  : bool
Right-to-left?
$rowBreaks  : array<string|int, PageBreak>
Collection of row breaks.
$rowDimensions  : array<string|int, RowDimension>
Collection of row dimensions.
$selectedCells  : string
Selected cells.
$sheetState  : string
Sheet state.
$sheetView  : SheetView
Sheet view.
$showGridlines  : bool
Show gridlines?
$showRowColHeaders  : bool
Show row and column headers?
$showSummaryBelow  : bool
Show summary below? (Row/Column outline).
$showSummaryRight  : bool
Show summary right? (Row/Column outline).
$styles  : array<string|int, Style>
Collection of styles.
$tabColor  : Color|null
Tab color.
$tableCollection  : ArrayObject<int, Table>
Collection of Table objects.
$title  : string
Worksheet title.
$topLeftCell  : string|null
Default position of the right bottom pane.
$xSplit  : int
$ySplit  : int

Methods

__clone()  : mixed
Implement PHP __clone to create a deep clone, not just a shallow copy.
__construct()  : mixed
Create a new worksheet.
__destruct()  : mixed
Code to execute when this worksheet is unset().
addChart()  : Chart
addTable()  : $this
Add Table.
applyStylesFromArray()  : bool
calculateColumnWidths()  : $this
Calculate widths for auto-size columns.
calculateWorksheetDataDimension()  : string
Calculate worksheet data dimension.
calculateWorksheetDimension()  : string
Calculate worksheet dimension.
cellExists()  : bool
Does the cell at a specific coordinate exist?
columnDimensionExists()  : bool
conditionalStylesExists()  : bool
Do conditional styles exist for this cell?
copy()  : static
Copy worksheet (!= clone!).
copyCells()  : void
Copy cells, adjusting relative cell references in formulas.
createNewCell()  : Cell
Create a new cell at the specified coordinate.
dataValidationExists()  : bool
Data validation at a specific coordinate exists?
disconnectCells()  : void
Disconnect all cells from this Worksheet object, typically so that the worksheet object can be unset.
duplicateConditionalStyle()  : $this
Duplicate conditional style to a range of cells.
duplicateStyle()  : $this
Duplicate cell style to a range of cells.
extractSheetTitle()  : mixed
Extract worksheet title from range.
freezePane()  : $this
Freeze Pane.
fromArray()  : $this
Fill worksheet from values in array.
garbageCollect()  : $this
Run PhpSpreadsheet garbage collector.
getActiveCell()  : string
Get active cell.
getActivePane()  : string
getAutoFilter()  : AutoFilter
Get Autofilter.
getBackgroundExtension()  : string
getBackgroundImage()  : string
getBackgroundMime()  : string
getBreaks()  : array<string|int, int>
Get breaks.
getCell()  : Cell
Get cell at a specific coordinate.
getCellCollection()  : Cells
Return the cell collection.
getChartByIndex()  : Chart|false
Get a chart by its index position.
getChartByName()  : Chart|false
Get a chart by name.
getChartByNameOrThrow()  : Chart
getChartCollection()  : ArrayObject<int, Chart>
Get collection of charts.
getChartCount()  : int
Return the count of charts on this worksheet.
getChartNames()  : array<string|int, string>
Return an array of the names of charts on this worksheet.
getCodeName()  : string|null
Return the code name of the sheet.
getColumnBreaks()  : array<string|int, PageBreak>
Get column breaks.
getColumnDimension()  : ColumnDimension
Get column dimension at a specific column.
getColumnDimensionByColumn()  : ColumnDimension
Get column dimension at a specific column by using numeric cell coordinates.
getColumnDimensions()  : array<string|int, ColumnDimension>
Get collection of column dimensions.
getColumnIterator()  : ColumnIterator
Get column iterator.
getComment()  : Comment
Get comment for cell.
getComments()  : array<string|int, Comment>
Get comments.
getConditionalRange()  : string|null
getConditionalStyles()  : array<string|int, Conditional>
Get conditional styles for a cell.
getConditionalStylesCollection()  : array<string|int, mixed>
Get collection of conditional styles.
getCoordinates()  : array<string|int, string>
Get a sorted list of all cell coordinates currently held in the collection by row and column.
getDataValidation()  : DataValidation
Get data validation.
getDataValidationCollection()  : array<string|int, DataValidation>
Get collection of data validations.
getDefaultColumnDimension()  : ColumnDimension
Get default column dimension.
getDefaultRowDimension()  : RowDimension
Get default row dimension.
getDrawingCollection()  : ArrayObject<int, BaseDrawing>
Get collection of drawings.
getFreezePane()  : string|null
Get Freeze Pane.
getHashCode()  : string
Get hash code.
getHeaderFooter()  : HeaderFooter
Get page header/footer.
getHighestColumn()  : string
Get highest worksheet column.
getHighestDataColumn()  : string
Get highest worksheet column that contains data.
getHighestDataRow()  : int
Get highest worksheet row that contains data.
getHighestRow()  : int
Get highest worksheet row.
getHighestRowAndColumn()  : array<string|int, mixed>
Get highest worksheet column and highest row that have cell records.
getHyperlink()  : Hyperlink
Get hyperlink.
getHyperlinkCollection()  : array<string|int, Hyperlink>
Get collection of hyperlinks.
getInvalidCharacters()  : array<string|int, mixed>
Get array of invalid characters for sheet title.
getMergeCells()  : array<string|int, string>
Get merge cells array.
getPageMargins()  : PageMargins
Get page margins.
getPageSetup()  : PageSetup
Get page setup.
getPane()  : Pane|null
getPanes()  : array<string|int, null|Pane>
getPaneState()  : string
getPaneTopLeftCell()  : string
getParent()  : Spreadsheet|null
Get parent or null.
getParentOrThrow()  : Spreadsheet
Get parent, throw exception if null.
getPrintGridlines()  : bool
Print gridlines?
getProtectedCellRanges()  : array<string|int, ProtectedRange>
Get protected cells.
getProtectedCells()  : array<string|int, string>
Get password for protected cells.
getProtection()  : Protection
Get Protection.
getRightToLeft()  : bool
Get right-to-left.
getRowBreaks()  : array<string|int, PageBreak>
Get row breaks.
getRowDimension()  : RowDimension
Get row dimension at a specific row.
getRowDimensions()  : array<string|int, RowDimension>
Get collection of row dimensions.
getRowIterator()  : RowIterator
Get row iterator.
getSelectedCells()  : string
Get selected cells.
getSheetState()  : string
Get sheet state.
getSheetView()  : SheetView
Get sheet view.
getShowGridlines()  : bool
Show gridlines?
getShowRowColHeaders()  : bool
Show row and column headers?
getShowSummaryBelow()  : bool
Show summary below? (Row/Column outlining).
getShowSummaryRight()  : bool
Show summary right? (Row/Column outlining).
getStyle()  : Style
Get style for cell.
getStyles()  : array<string|int, Style>
Get styles.
getTabColor()  : Color
Get tab color.
getTableByName()  : null|Table
getTableCollection()  : ArrayObject<int, Table>
Get collection of Tables.
getTableNames()  : array<string|int, string>
getTitle()  : string
Get title.
getTopLeftCell()  : string|null
Get the default position of the right bottom pane.
getXSplit()  : int
getYSplit()  : int
hasCodeName()  : bool
Sheet has a code name ?
hyperlinkExists()  : bool
Hyperlink at a specific coordinate exists?
insertNewColumnBefore()  : $this
Insert a new column, updating all possible related data.
insertNewColumnBeforeByIndex()  : $this
Insert a new column, updating all possible related data.
insertNewRowBefore()  : $this
Insert a new row, updating all possible related data.
isCellHiddenOnFormulaBar()  : bool
Same as Cell->isHiddenOnFormulaBar, but without creating cell if it doesn't exist.
isCellLocked()  : bool
Same as Cell->isLocked, but without creating cell if it doesn't exist.
isEmptyColumn()  : bool
Returns a boolean true if the specified column contains no cells. By default, this means that no cell records exist in the collection for this column. false will be returned otherwise.
isEmptyRow()  : bool
Returns a boolean true if the specified row contains no cells. By default, this means that no cell records exist in the collection for this row. false will be returned otherwise.
isRowVisible()  : bool
isTabColorSet()  : bool
Tab color set?
mergeCellBehaviour()  : array<string|int, mixed>
mergeCells()  : $this
Set merge on a cell range.
namedRangeToArray()  : array<string|int, mixed>
Create array from a range of cells.
nameRequiresQuotes()  : bool
protectCells()  : $this
Set protection on a cell or cell range.
rangeToArray()  : array<string|int, mixed>
Create array from a range of cells.
rangeToArrayYieldRows()  : Generator<string|int, array<string|int, mixed>>
Create array from a range of cells, yielding each row in turn.
rebindParent()  : $this
Re-bind parent.
refreshColumnDimensions()  : $this
Refresh column dimensions.
refreshRowDimensions()  : $this
Refresh row dimensions.
removeAutoFilter()  : self
Remove autofilter.
removeColumn()  : $this
Remove a column, updating all possible related data.
removeColumnByIndex()  : $this
Remove a column, updating all possible related data.
removeComment()  : $this
Remove comment from cell.
removeConditionalStyles()  : $this
Removes conditional styles for a cell.
removeRow()  : $this
Delete a row, updating all possible related data.
removeTableByName()  : $this
Remove Table by name.
removeTableCollection()  : self
Remove collection of Tables.
resetTabColor()  : $this
Reset tab color.
rowDimensionExists()  : bool
setActivePane()  : self
setAutoFilter()  : $this
Set AutoFilter.
setBackgroundImage()  : self
Set background image.
setBreak()  : $this
Set break on a cell.
setCellValue()  : $this
Set a cell value.
setCellValueExplicit()  : $this
Set a cell value.
setCodeName()  : $this
Define the code name of the sheet.
setComments()  : $this
Set comments array for the entire sheet.
setConditionalStyles()  : $this
Set conditional styles.
setDataValidation()  : $this
Set data validation.
setHeaderFooter()  : $this
Set page header/footer.
setHyperlink()  : $this
Set hyperlink.
setMergeCells()  : $this
Set merge cells array for the entire sheet. Use instead mergeCells() to merge a single cell range.
setPageMargins()  : $this
Set page margins.
setPageSetup()  : $this
Set page setup.
setPane()  : self
setPaneState()  : self
setPaneTopLeftCell()  : self
setPrintGridlines()  : $this
Set print gridlines.
setProtection()  : $this
Set Protection.
setRightToLeft()  : $this
Set right-to-left.
setSelectedCell()  : $this
Selected cell.
setSelectedCells()  : $this
Select a range of cells.
setSheetState()  : $this
Set sheet state.
setSheetView()  : $this
Set sheet view.
setShowGridlines()  : $this
Set show gridlines.
setShowRowColHeaders()  : $this
Set show row and column headers.
setShowSummaryBelow()  : $this
Set show summary below.
setShowSummaryRight()  : $this
Set show summary right.
setTitle()  : $this
Set title.
setTopLeftCell()  : self
setXSplit()  : self
setYSplit()  : self
shrinkRangeToFit()  : string
Accepts a range, returning it as a range that falls within the current highest row and column of the worksheet.
toArray()  : array<string|int, mixed>
Create array from worksheet.
unfreezePane()  : $this
Unfreeze Pane.
unmergeCells()  : $this
Remove merge on a cell range.
unprotectCells()  : $this
Remove protection on a cell or cell range.
usesPanes()  : bool
cellToArray()  : mixed
compareColumnBreaks()  : int
compareRowBreaks()  : int
getTableIndexByName()  : null|int
buildNullRow()  : array<string|int, mixed>
Prepare a row data filled with null values to deduplicate the memory areas for empty rows.
checkSheetCodeName()  : string
Check sheet code name for valid Excel syntax.
checkSheetTitle()  : string
Check sheet title for valid Excel syntax.
clearMergeCellsByColumn()  : void
clearMergeCellsByRow()  : void
columnDimensionCompare()  : int
getCellOrNull()  : null|Cell
Get an existing cell at a specific coordinate, or null.
getWorksheetAndCoordinate()  : Worksheet, 1: string}
Get the correct Worksheet and coordinate from a coordinate that may contains reference to another sheet or a named range.
getXfIndex()  : int|null
removeColumnDimensions()  : array<string|int, mixed>
removeRowDimensions()  : array<string|int, mixed>
setSelectedCellsActivePane()  : void
validateNamedRange()  : DefinedName|null

Constants

BREAK_ROW_MAX_COLUMN

public mixed BREAK_ROW_MAX_COLUMN = 16383

MERGE_CELL_CONTENT_EMPTY

public mixed MERGE_CELL_CONTENT_EMPTY = 'empty'

MERGE_CELL_CONTENT_HIDE

public mixed MERGE_CELL_CONTENT_HIDE = 'hide'

MERGE_CELL_CONTENT_MERGE

public mixed MERGE_CELL_CONTENT_MERGE = 'merge'

PANE_FROZEN

public mixed PANE_FROZEN = 'frozen'

PANE_FROZENSPLIT

public mixed PANE_FROZENSPLIT = 'frozenSplit'

PANE_SPLIT

public mixed PANE_SPLIT = 'split'

SHEET_TITLE_MAXIMUM_LENGTH

Maximum 31 characters allowed for sheet title.

public int SHEET_TITLE_MAXIMUM_LENGTH = 31

SHEETSTATE_HIDDEN

public mixed SHEETSTATE_HIDDEN = 'hidden'

SHEETSTATE_VERYHIDDEN

public mixed SHEETSTATE_VERYHIDDEN = 'veryHidden'

SHEETSTATE_VISIBLE

public mixed SHEETSTATE_VISIBLE = 'visible'

SHEET_NAME_REQUIRES_NO_QUOTES

protected mixed SHEET_NAME_REQUIRES_NO_QUOTES = '/^[_\p{L}][_\p{L}\p{N}]*$/mui'

VALIDFROZENSTATE

private mixed VALIDFROZENSTATE = [self::PANE_FROZEN, self::PANE_FROZENSPLIT]

VALIDPANESTATE

private mixed VALIDPANESTATE = [self::PANE_FROZEN, self::PANE_SPLIT, self::PANE_FROZENSPLIT]

Properties

$activeCell

Active cell. (Only one!).

private string $activeCell = 'A1'

$activePane

private string $activePane = ''

$backgroundExtension

private string $backgroundExtension = ''

$backgroundImage

private string $backgroundImage = ''

$backgroundMime

private string $backgroundMime = ''

$cachedHighestColumn

Cached highest column.

private int $cachedHighestColumn = 1

$cachedHighestRow

Cached highest row.

private int $cachedHighestRow = 1

$cellCollectionInitialized

private bool $cellCollectionInitialized = true

$chartCollection

Collection of Chart objects.

private ArrayObject<int, Chart> $chartCollection

$codeName

CodeName.

private string|null $codeName = null

$columnBreaks

Collection of column breaks.

private array<string|int, PageBreak> $columnBreaks = []

$comments

Collection of comments.

private array<string|int, Comment> $comments = []

$conditionalStylesCollection

Conditional styles. Indexed by cell coordinate, e.g. 'A1'.

private array<string|int, mixed> $conditionalStylesCollection = []

$dataValidationCollection

Data validation objects. Indexed by cell coordinate, e.g. 'A1'.

private array<string|int, mixed> $dataValidationCollection = []

$dirty

Dirty flag.

private bool $dirty = true

$drawingCollection

Collection of drawings.

private ArrayObject<int, BaseDrawing> $drawingCollection

$freezePane

Freeze pane.

private string|null $freezePane = null

$hyperlinkCollection

Hyperlinks. Indexed by cell coordinate, e.g. 'A1'.

private array<string|int, mixed> $hyperlinkCollection = []

$invalidCharacters

Invalid characters in sheet title.

private static array<string|int, mixed> $invalidCharacters = ['*', ':', '/', '\\', '?', '[', ']']

$mergeCells

Collection of merged cell ranges.

private array<string|int, string> $mergeCells = []

$panes

Properties of the 4 panes.

private array<string|int, null|Pane> $panes = ['bottomRight' => null, 'bottomLeft' => null, 'topRight' => null, 'topLeft' => null]

$paneTopLeftCell

private string $paneTopLeftCell = ''

$printGridlines

Print gridlines?

private bool $printGridlines = false

$protectedCells

Collection of protected cell ranges.

private array<string|int, ProtectedRange> $protectedCells = []

$rightToLeft

Right-to-left?

private bool $rightToLeft = false

$rowBreaks

Collection of row breaks.

private array<string|int, PageBreak> $rowBreaks = []

$rowDimensions

Collection of row dimensions.

private array<string|int, RowDimension> $rowDimensions = []

$selectedCells

Selected cells.

private string $selectedCells = 'A1'

$sheetState

Sheet state.

private string $sheetState

$showGridlines

Show gridlines?

private bool $showGridlines = true

$showRowColHeaders

Show row and column headers?

private bool $showRowColHeaders = true

$showSummaryBelow

Show summary below? (Row/Column outline).

private bool $showSummaryBelow = true

$showSummaryRight

Show summary right? (Row/Column outline).

private bool $showSummaryRight = true

$styles

Collection of styles.

private array<string|int, Style> $styles = []

$tableCollection

Collection of Table objects.

private ArrayObject<int, Table> $tableCollection

$title

Worksheet title.

private string $title = ''

$topLeftCell

Default position of the right bottom pane.

private string|null $topLeftCell = null

Methods

__clone()

Implement PHP __clone to create a deep clone, not just a shallow copy.

public __clone() : mixed

__construct()

Create a new worksheet.

public __construct([Spreadsheet|null $parent = null ][, string $title = 'Worksheet' ]) : mixed
Parameters
$parent : Spreadsheet|null = null
$title : string = 'Worksheet'

__destruct()

Code to execute when this worksheet is unset().

public __destruct() : mixed

addTable()

Add Table.

public addTable(Table $table) : $this
Parameters
$table : Table
Return values
$this

applyStylesFromArray()

public applyStylesFromArray(string $coordinate, array<string|int, mixed> $styleArray) : bool
Parameters
$coordinate : string
$styleArray : array<string|int, mixed>
Return values
bool

calculateColumnWidths()

Calculate widths for auto-size columns.

public calculateColumnWidths() : $this
Return values
$this

calculateWorksheetDataDimension()

Calculate worksheet data dimension.

public calculateWorksheetDataDimension() : string
Return values
string

String containing the dimension of this worksheet that actually contain data

calculateWorksheetDimension()

Calculate worksheet dimension.

public calculateWorksheetDimension() : string
Return values
string

String containing the dimension of this worksheet

cellExists()

Does the cell at a specific coordinate exist?

public cellExists(array{0: int, 1: int}|CellAddress|string $coordinate) : bool
Parameters
$coordinate : array{0: int, 1: int}|CellAddress|string

Coordinate of the cell as a string, eg: 'C5'; or as an array of [$columnIndex, $row] (e.g. [3, 5]), or a CellAddress object.

Return values
bool

columnDimensionExists()

public columnDimensionExists(string $column) : bool
Parameters
$column : string
Return values
bool

conditionalStylesExists()

Do conditional styles exist for this cell?

public conditionalStylesExists(string $coordinate) : bool
Parameters
$coordinate : string

eg: 'A1' or 'A1:A3'. If a single cell is specified, then this method will return true if that cell is included in a conditional style range. If a range of cells is specified, then true will only be returned if the range matches the entire range of the conditional.

Return values
bool

copy()

Copy worksheet (!= clone!).

public copy() : static
Return values
static

copyCells()

Copy cells, adjusting relative cell references in formulas.

public copyCells(string $fromCell, string $toCells[, bool $copyStyle = true ]) : void

Acts similarly to Excel "fill handle" feature.

Parameters
$fromCell : string

Single source cell, e.g. C3

$toCells : string

Single cell or cell range, e.g. C4 or C4:C10

$copyStyle : bool = true

Copy styles as well as values, defaults to true

createNewCell()

Create a new cell at the specified coordinate.

public createNewCell(string $coordinate) : Cell
Parameters
$coordinate : string

Coordinate of the cell

Return values
Cell

Cell that was created WARNING: Because the cell collection can be cached to reduce memory, it only allows one "active" cell at a time in memory. If you assign that cell to a variable, then select another cell using getCell() or any of its variants, the newly selected cell becomes the "active" cell, and any previous assignment becomes a disconnected reference because the active cell has changed.

dataValidationExists()

Data validation at a specific coordinate exists?

public dataValidationExists(string $coordinate) : bool
Parameters
$coordinate : string

eg: 'A1'

Return values
bool

disconnectCells()

Disconnect all cells from this Worksheet object, typically so that the worksheet object can be unset.

public disconnectCells() : void

duplicateConditionalStyle()

Duplicate conditional style to a range of cells.

public duplicateConditionalStyle(array<string|int, Conditional$styles[, string $range = '' ]) : $this

Please note that this will overwrite existing cell styles for cells in range!

Parameters
$styles : array<string|int, Conditional>

Cell style to duplicate

$range : string = ''

Range of cells (i.e. "A1:B10"), or just one cell (i.e. "A1")

Return values
$this

duplicateStyle()

Duplicate cell style to a range of cells.

public duplicateStyle(Style $style, string $range) : $this

Please note that this will overwrite existing cell styles for cells in range!

Parameters
$style : Style

Cell style to duplicate

$range : string

Range of cells (i.e. "A1:B10"), or just one cell (i.e. "A1")

Return values
$this

extractSheetTitle()

Extract worksheet title from range.

public static extractSheetTitle(string|null $range[, bool $returnRange = false ]) : mixed

Example: extractSheetTitle("testSheet!A1") ==> 'A1' Example: extractSheetTitle("testSheet!A1:C3") ==> 'A1:C3' Example: extractSheetTitle("'testSheet 1'!A1", true) ==> ['testSheet 1', 'A1']; Example: extractSheetTitle("'testSheet 1'!A1:C3", true) ==> ['testSheet 1', 'A1:C3']; Example: extractSheetTitle("A1", true) ==> ['', 'A1']; Example: extractSheetTitle("A1:C3", true) ==> ['', 'A1:C3']

Parameters
$range : string|null

Range to extract title from

$returnRange : bool = false

Return range? (see example)

freezePane()

Freeze Pane.

public freezePane(null|array{0: int, 1: int}|CellAddress|string $coordinate[, null|array{0: int, 1: int}|CellAddress|string $topLeftCell = null ][, bool $frozenSplit = false ]) : $this

Examples:

  • A2 will freeze the rows above cell A2 (i.e row 1)
  • B1 will freeze the columns to the left of cell B1 (i.e column A)
  • B2 will freeze the rows above and to the left of cell B2 (i.e row 1 and column A)
Parameters
$coordinate : null|array{0: int, 1: int}|CellAddress|string

Coordinate of the cell as a string, eg: 'C5'; or as an array of [$columnIndex, $row] (e.g. [3, 5]), or a CellAddress object. Passing a null value for this argument will clear any existing freeze pane for this worksheet.

$topLeftCell : null|array{0: int, 1: int}|CellAddress|string = null

default position of the right bottom pane Coordinate of the cell as a string, eg: 'C5'; or as an array of [$columnIndex, $row] (e.g. [3, 5]), or a CellAddress object.

$frozenSplit : bool = false
Return values
$this

fromArray()

Fill worksheet from values in array.

public fromArray(array<string|int, mixed> $source[, mixed $nullValue = null ][, string $startCell = 'A1' ][, bool $strictNullComparison = false ]) : $this
Parameters
$source : array<string|int, mixed>

Source array

$nullValue : mixed = null

Value in source array that stands for blank cell

$startCell : string = 'A1'

Insert array starting from this cell address as the top left coordinate

$strictNullComparison : bool = false

Apply strict comparison when testing for null values in the array

Return values
$this

garbageCollect()

Run PhpSpreadsheet garbage collector.

public garbageCollect() : $this
Return values
$this

getActiveCell()

Get active cell.

public getActiveCell() : string
Return values
string

Example: 'A1'

getActivePane()

public getActivePane() : string
Return values
string

getBackgroundExtension()

public getBackgroundExtension() : string
Return values
string

getBackgroundImage()

public getBackgroundImage() : string
Return values
string

getBackgroundMime()

public getBackgroundMime() : string
Return values
string

getBreaks()

Get breaks.

public getBreaks() : array<string|int, int>
Return values
array<string|int, int>

getCell()

Get cell at a specific coordinate.

public getCell(array{0: int, 1: int}|CellAddress|string $coordinate) : Cell
Parameters
$coordinate : array{0: int, 1: int}|CellAddress|string

Coordinate of the cell as a string, eg: 'C5'; or as an array of [$columnIndex, $row] (e.g. [3, 5]), or a CellAddress object.

Return values
Cell

Cell that was found or created WARNING: Because the cell collection can be cached to reduce memory, it only allows one "active" cell at a time in memory. If you assign that cell to a variable, then select another cell using getCell() or any of its variants, the newly selected cell becomes the "active" cell, and any previous assignment becomes a disconnected reference because the active cell has changed.

getCellCollection()

Return the cell collection.

public getCellCollection() : Cells
Return values
Cells

getChartByIndex()

Get a chart by its index position.

public getChartByIndex(string|null $index) : Chart|false
Parameters
$index : string|null

Chart index position

Return values
Chart|false

getChartByName()

Get a chart by name.

public getChartByName(string $chartName) : Chart|false
Parameters
$chartName : string

Chart name

Return values
Chart|false

getChartByNameOrThrow()

public getChartByNameOrThrow(string $chartName) : Chart
Parameters
$chartName : string
Return values
Chart

getChartCollection()

Get collection of charts.

public getChartCollection() : ArrayObject<int, Chart>
Return values
ArrayObject<int, Chart>

getChartCount()

Return the count of charts on this worksheet.

public getChartCount() : int
Return values
int

The number of charts

getChartNames()

Return an array of the names of charts on this worksheet.

public getChartNames() : array<string|int, string>
Return values
array<string|int, string>

The names of charts

getCodeName()

Return the code name of the sheet.

public getCodeName() : string|null
Return values
string|null

getColumnBreaks()

Get column breaks.

public getColumnBreaks() : array<string|int, PageBreak>
Return values
array<string|int, PageBreak>

getColumnDimension()

Get column dimension at a specific column.

public getColumnDimension(string $column) : ColumnDimension
Parameters
$column : string

String index of the column eg: 'A'

Return values
ColumnDimension

getColumnDimensionByColumn()

Get column dimension at a specific column by using numeric cell coordinates.

public getColumnDimensionByColumn(int $columnIndex) : ColumnDimension
Parameters
$columnIndex : int

Numeric column coordinate of the cell

Return values
ColumnDimension

getColumnIterator()

Get column iterator.

public getColumnIterator([string $startColumn = 'A' ][, string|null $endColumn = null ]) : ColumnIterator
Parameters
$startColumn : string = 'A'

The column address at which to start iterating

$endColumn : string|null = null

The column address at which to stop iterating

Return values
ColumnIterator

getComment()

Get comment for cell.

public getComment(array{0: int, 1: int}|CellAddress|string $cellCoordinate[, bool $attachNew = true ]) : Comment
Parameters
$cellCoordinate : array{0: int, 1: int}|CellAddress|string

Coordinate of the cell as a string, eg: 'C5'; or as an array of [$columnIndex, $row] (e.g. [3, 5]), or a CellAddress object.

$attachNew : bool = true
Return values
Comment

getComments()

Get comments.

public getComments() : array<string|int, Comment>
Return values
array<string|int, Comment>

getConditionalRange()

public getConditionalRange(string $coordinate) : string|null
Parameters
$coordinate : string
Return values
string|null

getConditionalStyles()

Get conditional styles for a cell.

public getConditionalStyles(string $coordinate) : array<string|int, Conditional>
Parameters
$coordinate : string

eg: 'A1' or 'A1:A3'. If a single cell is referenced, then the array of conditional styles will be returned if the cell is included in a conditional style range. If a range of cells is specified, then the styles will only be returned if the range matches the entire range of the conditional.

Return values
array<string|int, Conditional>

getConditionalStylesCollection()

Get collection of conditional styles.

public getConditionalStylesCollection() : array<string|int, mixed>
Return values
array<string|int, mixed>

getCoordinates()

Get a sorted list of all cell coordinates currently held in the collection by row and column.

public getCoordinates([bool $sorted = true ]) : array<string|int, string>
Parameters
$sorted : bool = true

Also sort the cell collection?

Return values
array<string|int, string>

getDataValidation()

Get data validation.

public getDataValidation(string $cellCoordinate) : DataValidation
Parameters
$cellCoordinate : string

Cell coordinate to get data validation for, eg: 'A1'

Return values
DataValidation

getDataValidationCollection()

Get collection of data validations.

public getDataValidationCollection() : array<string|int, DataValidation>
Return values
array<string|int, DataValidation>

getDrawingCollection()

Get collection of drawings.

public getDrawingCollection() : ArrayObject<int, BaseDrawing>
Return values
ArrayObject<int, BaseDrawing>

getFreezePane()

Get Freeze Pane.

public getFreezePane() : string|null
Return values
string|null

getHashCode()

Get hash code.

public getHashCode() : string
Return values
string

Hash code

getHighestColumn()

Get highest worksheet column.

public getHighestColumn([null|int|string $row = null ]) : string
Parameters
$row : null|int|string = null

Return the data highest column for the specified row, or the highest column of any row if no row number is passed

Return values
string

Highest column name

getHighestDataColumn()

Get highest worksheet column that contains data.

public getHighestDataColumn([null|int|string $row = null ]) : string
Parameters
$row : null|int|string = null

Return the highest data column for the specified row, or the highest data column of any row if no row number is passed

Return values
string

Highest column name that contains data

getHighestDataRow()

Get highest worksheet row that contains data.

public getHighestDataRow([null|string $column = null ]) : int
Parameters
$column : null|string = null

Return the highest data row for the specified column, or the highest data row of any column if no column letter is passed

Return values
int

Highest row number that contains data

getHighestRow()

Get highest worksheet row.

public getHighestRow([null|string $column = null ]) : int
Parameters
$column : null|string = null

Return the highest data row for the specified column, or the highest row of any column if no column letter is passed

Return values
int

Highest row number

getHighestRowAndColumn()

Get highest worksheet column and highest row that have cell records.

public getHighestRowAndColumn() : array<string|int, mixed>
Return values
array<string|int, mixed>

Highest column name and highest row number

Get hyperlink.

public getHyperlink(string $cellCoordinate) : Hyperlink
Parameters
$cellCoordinate : string

Cell coordinate to get hyperlink for, eg: 'A1'

Return values
Hyperlink

getHyperlinkCollection()

Get collection of hyperlinks.

public getHyperlinkCollection() : array<string|int, Hyperlink>
Return values
array<string|int, Hyperlink>

getInvalidCharacters()

Get array of invalid characters for sheet title.

public static getInvalidCharacters() : array<string|int, mixed>
Return values
array<string|int, mixed>

getMergeCells()

Get merge cells array.

public getMergeCells() : array<string|int, string>
Return values
array<string|int, string>

getPane()

public getPane(string $position) : Pane|null
Parameters
$position : string
Return values
Pane|null

getPanes()

public getPanes() : array<string|int, null|Pane>
Return values
array<string|int, null|Pane>

getPaneState()

public getPaneState() : string
Return values
string

getPaneTopLeftCell()

public getPaneTopLeftCell() : string
Return values
string

getPrintGridlines()

Print gridlines?

public getPrintGridlines() : bool
Return values
bool

getProtectedCells()

Get password for protected cells.

public getProtectedCells() : array<string|int, string>
Tags
deprecated
2.0.1

use getProtectedCellRanges instead

see
Worksheet::getProtectedCellRanges()
Return values
array<string|int, string>

getRightToLeft()

Get right-to-left.

public getRightToLeft() : bool
Return values
bool

getRowDimension()

Get row dimension at a specific row.

public getRowDimension(int $row) : RowDimension
Parameters
$row : int

Numeric index of the row

Return values
RowDimension

getRowIterator()

Get row iterator.

public getRowIterator([int $startRow = 1 ][, int|null $endRow = null ]) : RowIterator
Parameters
$startRow : int = 1

The row number at which to start iterating

$endRow : int|null = null

The row number at which to stop iterating

Return values
RowIterator

getSelectedCells()

Get selected cells.

public getSelectedCells() : string
Return values
string

getSheetState()

Get sheet state.

public getSheetState() : string
Return values
string

Sheet state (visible, hidden, veryHidden)

getShowGridlines()

Show gridlines?

public getShowGridlines() : bool
Return values
bool

getShowRowColHeaders()

Show row and column headers?

public getShowRowColHeaders() : bool
Return values
bool

getShowSummaryBelow()

Show summary below? (Row/Column outlining).

public getShowSummaryBelow() : bool
Return values
bool

getShowSummaryRight()

Show summary right? (Row/Column outlining).

public getShowSummaryRight() : bool
Return values
bool

getStyle()

Get style for cell.

public getStyle(AddressRange<string|int, CellAddress>|array{0: int, 1: int, 2: int, 3: int}|array{0: int, 1: int}|CellAddress|int|string $cellCoordinate) : Style
Parameters
$cellCoordinate : AddressRange<string|int, CellAddress>|array{0: int, 1: int, 2: int, 3: int}|array{0: int, 1: int}|CellAddress|int|string

A simple string containing a cell address like 'A1' or a cell range like 'A1:E10' or passing in an array of [$fromColumnIndex, $fromRow, $toColumnIndex, $toRow] (e.g. [3, 5, 6, 8]), or a CellAddress or AddressRange object.

Return values
Style

getStyles()

Get styles.

public getStyles() : array<string|int, Style>
Return values
array<string|int, Style>

getTableByName()

public getTableByName(string $name) : null|Table
Parameters
$name : string

the table name to search

Return values
null|Table

The table from the tables collection, or null if not found

getTableCollection()

Get collection of Tables.

public getTableCollection() : ArrayObject<int, Table>
Return values
ArrayObject<int, Table>

getTableNames()

public getTableNames() : array<string|int, string>
Return values
array<string|int, string>

array of Table names

getTitle()

Get title.

public getTitle() : string
Return values
string

getTopLeftCell()

Get the default position of the right bottom pane.

public getTopLeftCell() : string|null
Return values
string|null

getXSplit()

public getXSplit() : int
Return values
int

getYSplit()

public getYSplit() : int
Return values
int

hasCodeName()

Sheet has a code name ?

public hasCodeName() : bool
Return values
bool

hyperlinkExists()

Hyperlink at a specific coordinate exists?

public hyperlinkExists(string $coordinate) : bool
Parameters
$coordinate : string

eg: 'A1'

Return values
bool

insertNewColumnBefore()

Insert a new column, updating all possible related data.

public insertNewColumnBefore(string $before[, int $numberOfColumns = 1 ]) : $this
Parameters
$before : string

Insert before this column Name, eg: 'A'

$numberOfColumns : int = 1

Number of new columns to insert

Return values
$this

insertNewColumnBeforeByIndex()

Insert a new column, updating all possible related data.

public insertNewColumnBeforeByIndex(int $beforeColumnIndex[, int $numberOfColumns = 1 ]) : $this
Parameters
$beforeColumnIndex : int

Insert before this column ID (numeric column coordinate of the cell)

$numberOfColumns : int = 1

Number of new columns to insert

Return values
$this

insertNewRowBefore()

Insert a new row, updating all possible related data.

public insertNewRowBefore(int $before[, int $numberOfRows = 1 ]) : $this
Parameters
$before : int

Insert before this row number

$numberOfRows : int = 1

Number of new rows to insert

Return values
$this

isCellHiddenOnFormulaBar()

Same as Cell->isHiddenOnFormulaBar, but without creating cell if it doesn't exist.

public isCellHiddenOnFormulaBar(string $coordinate) : bool
Parameters
$coordinate : string
Return values
bool

isCellLocked()

Same as Cell->isLocked, but without creating cell if it doesn't exist.

public isCellLocked(string $coordinate) : bool
Parameters
$coordinate : string
Return values
bool

isEmptyColumn()

Returns a boolean true if the specified column contains no cells. By default, this means that no cell records exist in the collection for this column. false will be returned otherwise.

public isEmptyColumn(string $columnId[, int $definitionOfEmptyFlags = 0 ]) : bool

This rule can be modified by passing a $definitionOfEmptyFlags value: 1 - CellIterator::TREAT_NULL_VALUE_AS_EMPTY_CELL If the only cells in the collection are null value cells, then the column will be considered empty. 2 - CellIterator::TREAT_EMPTY_STRING_AS_EMPTY_CELL If the only cells in the collection are empty string value cells, then the column will be considered empty. 3 - CellIterator::TREAT_NULL_VALUE_AS_EMPTY_CELL | CellIterator::TREAT_EMPTY_STRING_AS_EMPTY_CELL If the only cells in the collection are null value or empty string value cells, then the column will be considered empty.

Parameters
$columnId : string
$definitionOfEmptyFlags : int = 0

Possible Flag Values are: CellIterator::TREAT_NULL_VALUE_AS_EMPTY_CELL CellIterator::TREAT_EMPTY_STRING_AS_EMPTY_CELL

Return values
bool

isEmptyRow()

Returns a boolean true if the specified row contains no cells. By default, this means that no cell records exist in the collection for this row. false will be returned otherwise.

public isEmptyRow(int $rowId[, int $definitionOfEmptyFlags = 0 ]) : bool

This rule can be modified by passing a $definitionOfEmptyFlags value: 1 - CellIterator::TREAT_NULL_VALUE_AS_EMPTY_CELL If the only cells in the collection are null value cells, then the row will be considered empty. 2 - CellIterator::TREAT_EMPTY_STRING_AS_EMPTY_CELL If the only cells in the collection are empty string value cells, then the row will be considered empty. 3 - CellIterator::TREAT_NULL_VALUE_AS_EMPTY_CELL | CellIterator::TREAT_EMPTY_STRING_AS_EMPTY_CELL If the only cells in the collection are null value or empty string value cells, then the row will be considered empty.

Parameters
$rowId : int
$definitionOfEmptyFlags : int = 0

Possible Flag Values are: CellIterator::TREAT_NULL_VALUE_AS_EMPTY_CELL CellIterator::TREAT_EMPTY_STRING_AS_EMPTY_CELL

Return values
bool

isRowVisible()

public isRowVisible(int $row) : bool
Parameters
$row : int
Return values
bool

isTabColorSet()

Tab color set?

public isTabColorSet() : bool
Return values
bool

mergeCellBehaviour()

public mergeCellBehaviour(Cell $cell, string $upperLeft, string $behaviour, array<string|int, mixed> $leftCellValue) : array<string|int, mixed>
Parameters
$cell : Cell
$upperLeft : string
$behaviour : string
$leftCellValue : array<string|int, mixed>
Return values
array<string|int, mixed>

mergeCells()

Set merge on a cell range.

public mergeCells(AddressRange<string|int, CellAddress>|array{0: int, 1: int, 2: int, 3: int}|array{0: int, 1: int}|string $range[, string $behaviour = self::MERGE_CELL_CONTENT_EMPTY ]) : $this
Parameters
$range : AddressRange<string|int, CellAddress>|array{0: int, 1: int, 2: int, 3: int}|array{0: int, 1: int}|string

A simple string containing a Cell range like 'A1:E10' or passing in an array of [$fromColumnIndex, $fromRow, $toColumnIndex, $toRow] (e.g. [3, 5, 6, 8]), or an AddressRange.

$behaviour : string = self::MERGE_CELL_CONTENT_EMPTY

How the merged cells should behave. Possible values are: MERGE_CELL_CONTENT_EMPTY - Empty the content of the hidden cells MERGE_CELL_CONTENT_HIDE - Keep the content of the hidden cells MERGE_CELL_CONTENT_MERGE - Move the content of the hidden cells into the first cell

Return values
$this

namedRangeToArray()

Create array from a range of cells.

public namedRangeToArray(string $definedName[, null|bool|float|int|RichText|string $nullValue = null ][, bool $calculateFormulas = true ][, bool $formatData = true ][, bool $returnCellRef = false ][, bool $ignoreHidden = false ]) : array<string|int, mixed>
Parameters
$definedName : string

The Named Range that should be returned

$nullValue : null|bool|float|int|RichText|string = null

Value returned in the array entry if a cell doesn't exist

$calculateFormulas : bool = true

Should formulas be calculated?

$formatData : bool = true

Should formatting be applied to cell values?

$returnCellRef : bool = false

False - Return a simple array of rows and columns indexed by number counting from zero True - Return rows and columns indexed by their actual row and column IDs

$ignoreHidden : bool = false

False - Return values for rows/columns even if they are defined as hidden. True - Don't return values for rows/columns that are defined as hidden.

Return values
array<string|int, mixed>

nameRequiresQuotes()

public static nameRequiresQuotes(string $sheetName) : bool
Parameters
$sheetName : string
Return values
bool

protectCells()

Set protection on a cell or cell range.

public protectCells(AddressRange<string|int, CellAddress>|array{0: int, 1: int, 2: int, 3: int}|array{0: int, 1: int}|CellAddress|int|string $range[, string $password = '' ][, bool $alreadyHashed = false ][, string $name = '' ][, string $securityDescriptor = '' ]) : $this
Parameters
$range : AddressRange<string|int, CellAddress>|array{0: int, 1: int, 2: int, 3: int}|array{0: int, 1: int}|CellAddress|int|string

A simple string containing a Cell range like 'A1:E10' or passing in an array of [$fromColumnIndex, $fromRow, $toColumnIndex, $toRow] (e.g. [3, 5, 6, 8]), or a CellAddress or AddressRange object.

$password : string = ''

Password to unlock the protection

$alreadyHashed : bool = false

If the password has already been hashed, set this to true

$name : string = ''
$securityDescriptor : string = ''
Return values
$this

rangeToArray()

Create array from a range of cells.

public rangeToArray(string $range[, null|bool|float|int|RichText|string $nullValue = null ][, bool $calculateFormulas = true ][, bool $formatData = true ][, bool $returnCellRef = false ][, bool $ignoreHidden = false ]) : array<string|int, mixed>
Parameters
$range : string
$nullValue : null|bool|float|int|RichText|string = null

Value returned in the array entry if a cell doesn't exist

$calculateFormulas : bool = true

Should formulas be calculated?

$formatData : bool = true

Should formatting be applied to cell values?

$returnCellRef : bool = false

False - Return a simple array of rows and columns indexed by number counting from zero True - Return rows and columns indexed by their actual row and column IDs

$ignoreHidden : bool = false

False - Return values for rows/columns even if they are defined as hidden. True - Don't return values for rows/columns that are defined as hidden.

Return values
array<string|int, mixed>

rangeToArrayYieldRows()

Create array from a range of cells, yielding each row in turn.

public rangeToArrayYieldRows(string $range[, null|bool|float|int|RichText|string $nullValue = null ][, bool $calculateFormulas = true ][, bool $formatData = true ][, bool $returnCellRef = false ][, bool $ignoreHidden = false ]) : Generator<string|int, array<string|int, mixed>>
Parameters
$range : string
$nullValue : null|bool|float|int|RichText|string = null

Value returned in the array entry if a cell doesn't exist

$calculateFormulas : bool = true

Should formulas be calculated?

$formatData : bool = true

Should formatting be applied to cell values?

$returnCellRef : bool = false

False - Return a simple array of rows and columns indexed by number counting from zero True - Return rows and columns indexed by their actual row and column IDs

$ignoreHidden : bool = false

False - Return values for rows/columns even if they are defined as hidden. True - Don't return values for rows/columns that are defined as hidden.

Return values
Generator<string|int, array<string|int, mixed>>

refreshColumnDimensions()

Refresh column dimensions.

public refreshColumnDimensions() : $this
Return values
$this

refreshRowDimensions()

Refresh row dimensions.

public refreshRowDimensions() : $this
Return values
$this

removeAutoFilter()

Remove autofilter.

public removeAutoFilter() : self
Return values
self

removeColumn()

Remove a column, updating all possible related data.

public removeColumn(string $column[, int $numberOfColumns = 1 ]) : $this
Parameters
$column : string

Remove columns starting with this column name, eg: 'A'

$numberOfColumns : int = 1

Number of columns to remove

Return values
$this

removeColumnByIndex()

Remove a column, updating all possible related data.

public removeColumnByIndex(int $columnIndex[, int $numColumns = 1 ]) : $this
Parameters
$columnIndex : int

Remove starting with this column Index (numeric column coordinate)

$numColumns : int = 1

Number of columns to remove

Return values
$this

removeComment()

Remove comment from cell.

public removeComment(array{0: int, 1: int}|CellAddress|string $cellCoordinate) : $this
Parameters
$cellCoordinate : array{0: int, 1: int}|CellAddress|string

Coordinate of the cell as a string, eg: 'C5'; or as an array of [$columnIndex, $row] (e.g. [3, 5]), or a CellAddress object.

Return values
$this

removeConditionalStyles()

Removes conditional styles for a cell.

public removeConditionalStyles(string $coordinate) : $this
Parameters
$coordinate : string

eg: 'A1'

Return values
$this

removeRow()

Delete a row, updating all possible related data.

public removeRow(int $row[, int $numberOfRows = 1 ]) : $this
Parameters
$row : int

Remove rows, starting with this row number

$numberOfRows : int = 1

Number of rows to remove

Return values
$this

removeTableByName()

Remove Table by name.

public removeTableByName(string $name) : $this
Parameters
$name : string

Table name

Return values
$this

removeTableCollection()

Remove collection of Tables.

public removeTableCollection() : self
Return values
self

resetTabColor()

Reset tab color.

public resetTabColor() : $this
Return values
$this

rowDimensionExists()

public rowDimensionExists(int $row) : bool
Parameters
$row : int
Return values
bool

setActivePane()

public setActivePane(string $activePane) : self
Parameters
$activePane : string
Return values
self

setAutoFilter()

Set AutoFilter.

public setAutoFilter(AddressRange<string|int, CellAddress>|array{0: int, 1: int, 2: int, 3: int}|array{0: int, 1: int}|AutoFilter|string $autoFilterOrRange) : $this
Parameters
$autoFilterOrRange : AddressRange<string|int, CellAddress>|array{0: int, 1: int, 2: int, 3: int}|array{0: int, 1: int}|AutoFilter|string

A simple string containing a Cell range like 'A1:E10' is permitted for backward compatibility or passing in an array of [$fromColumnIndex, $fromRow, $toColumnIndex, $toRow] (e.g. [3, 5, 6, 8]), or an AddressRange.

Return values
$this

setBackgroundImage()

Set background image.

public setBackgroundImage(string $backgroundImage) : self

Used on read/write for Xlsx. Used on write for Html.

Parameters
$backgroundImage : string

Image represented as a string, e.g. results of file_get_contents

Return values
self

setBreak()

Set break on a cell.

public setBreak(array{0: int, 1: int}|CellAddress|string $coordinate, int $break[, int $max = -1 ]) : $this
Parameters
$coordinate : array{0: int, 1: int}|CellAddress|string

Coordinate of the cell as a string, eg: 'C5'; or as an array of [$columnIndex, $row] (e.g. [3, 5]), or a CellAddress object.

$break : int

Break type (type of Worksheet::BREAK_*)

$max : int = -1
Return values
$this

setCellValue()

Set a cell value.

public setCellValue(array{0: int, 1: int}|CellAddress|string $coordinate, mixed $value[, null|IValueBinder $binder = null ]) : $this
Parameters
$coordinate : array{0: int, 1: int}|CellAddress|string

Coordinate of the cell as a string, eg: 'C5'; or as an array of [$columnIndex, $row] (e.g. [3, 5]), or a CellAddress object.

$value : mixed

Value for the cell

$binder : null|IValueBinder = null

Value Binder to override the currently set Value Binder

Return values
$this

setCellValueExplicit()

Set a cell value.

public setCellValueExplicit(array{0: int, 1: int}|CellAddress|string $coordinate, mixed $value, string $dataType) : $this
Parameters
$coordinate : array{0: int, 1: int}|CellAddress|string

Coordinate of the cell as a string, eg: 'C5'; or as an array of [$columnIndex, $row] (e.g. [3, 5]), or a CellAddress object.

$value : mixed

Value of the cell

$dataType : string

Explicit data type, see DataType::TYPE_* Note that PhpSpreadsheet does not validate that the value and datatype are consistent, in using this method, then it is your responsibility as an end-user developer to validate that the value and the datatype match. If you do mismatch value and datatpe, then the value you enter may be changed to match the datatype that you specify.

Tags
see
DataType
Return values
$this

setCodeName()

Define the code name of the sheet.

public setCodeName(string $codeName[, bool $validate = true ]) : $this
Parameters
$codeName : string

Same rule as Title minus space not allowed (but, like Excel, change silently space to underscore)

$validate : bool = true

False to skip validation of new title. WARNING: This should only be set at parse time (by Readers), where titles can be assumed to be valid.

Return values
$this

setComments()

Set comments array for the entire sheet.

public setComments(array<string|int, Comment$comments) : $this
Parameters
$comments : array<string|int, Comment>
Return values
$this

setConditionalStyles()

Set conditional styles.

public setConditionalStyles(string $coordinate, array<string|int, Conditional$styles) : $this
Parameters
$coordinate : string

eg: 'A1'

$styles : array<string|int, Conditional>
Return values
$this

setDataValidation()

Set data validation.

public setDataValidation(string $cellCoordinate[, DataValidation|null $dataValidation = null ]) : $this
Parameters
$cellCoordinate : string

Cell coordinate to insert data validation, eg: 'A1'

$dataValidation : DataValidation|null = null
Return values
$this

Set hyperlink.

public setHyperlink(string $cellCoordinate[, Hyperlink|null $hyperlink = null ]) : $this
Parameters
$cellCoordinate : string

Cell coordinate to insert hyperlink, eg: 'A1'

$hyperlink : Hyperlink|null = null
Return values
$this

setMergeCells()

Set merge cells array for the entire sheet. Use instead mergeCells() to merge a single cell range.

public setMergeCells(array<string|int, string> $mergeCells) : $this
Parameters
$mergeCells : array<string|int, string>
Return values
$this

setPageSetup()

Set page setup.

public setPageSetup(PageSetup $pageSetup) : $this
Parameters
$pageSetup : PageSetup
Return values
$this

setPane()

public setPane(string $position, Pane|null $pane) : self
Parameters
$position : string
$pane : Pane|null
Return values
self

setPaneState()

public setPaneState(string $paneState) : self
Parameters
$paneState : string
Return values
self

setPaneTopLeftCell()

public setPaneTopLeftCell(string $paneTopLeftCell) : self
Parameters
$paneTopLeftCell : string
Return values
self

setPrintGridlines()

Set print gridlines.

public setPrintGridlines(bool $printGridLines) : $this
Parameters
$printGridLines : bool

Print gridlines (true/false)

Return values
$this

setRightToLeft()

Set right-to-left.

public setRightToLeft(bool $value) : $this
Parameters
$value : bool

Right-to-left true/false

Return values
$this

setSelectedCell()

Selected cell.

public setSelectedCell(string $coordinate) : $this
Parameters
$coordinate : string

Cell (i.e. A1)

Return values
$this

setSelectedCells()

Select a range of cells.

public setSelectedCells(AddressRange<string|int, CellAddress>|array{0: int, 1: int, 2: int, 3: int}|array{0: int, 1: int}|CellAddress|int|string $coordinate) : $this
Parameters
$coordinate : AddressRange<string|int, CellAddress>|array{0: int, 1: int, 2: int, 3: int}|array{0: int, 1: int}|CellAddress|int|string

A simple string containing a Cell range like 'A1:E10' or passing in an array of [$fromColumnIndex, $fromRow, $toColumnIndex, $toRow] (e.g. [3, 5, 6, 8]), or a CellAddress or AddressRange object.

Return values
$this

setSheetState()

Set sheet state.

public setSheetState(string $value) : $this
Parameters
$value : string

Sheet state (visible, hidden, veryHidden)

Return values
$this

setSheetView()

Set sheet view.

public setSheetView(SheetView $sheetView) : $this
Parameters
$sheetView : SheetView
Return values
$this

setShowGridlines()

Set show gridlines.

public setShowGridlines(bool $showGridLines) : $this
Parameters
$showGridLines : bool

Show gridlines (true/false)

Return values
$this

setShowRowColHeaders()

Set show row and column headers.

public setShowRowColHeaders(bool $showRowColHeaders) : $this
Parameters
$showRowColHeaders : bool

Show row and column headers (true/false)

Return values
$this

setShowSummaryBelow()

Set show summary below.

public setShowSummaryBelow(bool $showSummaryBelow) : $this
Parameters
$showSummaryBelow : bool

Show summary below (true/false)

Return values
$this

setShowSummaryRight()

Set show summary right.

public setShowSummaryRight(bool $showSummaryRight) : $this
Parameters
$showSummaryRight : bool

Show summary right (true/false)

Return values
$this

setTitle()

Set title.

public setTitle(string $title[, bool $updateFormulaCellReferences = true ][, bool $validate = true ]) : $this
Parameters
$title : string

String containing the dimension of this worksheet

$updateFormulaCellReferences : bool = true

Flag indicating whether cell references in formulae should be updated to reflect the new sheet name. This should be left as the default true, unless you are certain that no formula cells on any worksheet contain references to this worksheet

$validate : bool = true

False to skip validation of new title. WARNING: This should only be set at parse time (by Readers), where titles can be assumed to be valid.

Return values
$this

setTopLeftCell()

public setTopLeftCell(string $topLeftCell) : self
Parameters
$topLeftCell : string
Return values
self

setXSplit()

public setXSplit(int $xSplit) : self
Parameters
$xSplit : int
Return values
self

setYSplit()

public setYSplit(int $ySplit) : self
Parameters
$ySplit : int
Return values
self

shrinkRangeToFit()

Accepts a range, returning it as a range that falls within the current highest row and column of the worksheet.

public shrinkRangeToFit(string $range) : string
Parameters
$range : string
Return values
string

Adjusted range value

toArray()

Create array from worksheet.

public toArray([null|bool|float|int|RichText|string $nullValue = null ][, bool $calculateFormulas = true ][, bool $formatData = true ][, bool $returnCellRef = false ][, bool $ignoreHidden = false ]) : array<string|int, mixed>
Parameters
$nullValue : null|bool|float|int|RichText|string = null

Value returned in the array entry if a cell doesn't exist

$calculateFormulas : bool = true

Should formulas be calculated?

$formatData : bool = true

Should formatting be applied to cell values?

$returnCellRef : bool = false

False - Return a simple array of rows and columns indexed by number counting from zero True - Return rows and columns indexed by their actual row and column IDs

$ignoreHidden : bool = false

False - Return values for rows/columns even if they are defined as hidden. True - Don't return values for rows/columns that are defined as hidden.

Return values
array<string|int, mixed>

unfreezePane()

Unfreeze Pane.

public unfreezePane() : $this
Return values
$this

unmergeCells()

Remove merge on a cell range.

public unmergeCells(AddressRange<string|int, CellAddress>|array{0: int, 1: int, 2: int, 3: int}|array{0: int, 1: int}|string $range) : $this
Parameters
$range : AddressRange<string|int, CellAddress>|array{0: int, 1: int, 2: int, 3: int}|array{0: int, 1: int}|string

A simple string containing a Cell range like 'A1:E10' or passing in an array of [$fromColumnIndex, $fromRow, $toColumnIndex, $toRow] (e.g. [3, 5, 6, 8]), or an AddressRange.

Return values
$this

unprotectCells()

Remove protection on a cell or cell range.

public unprotectCells(AddressRange<string|int, CellAddress>|array{0: int, 1: int, 2: int, 3: int}|array{0: int, 1: int}|CellAddress|int|string $range) : $this
Parameters
$range : AddressRange<string|int, CellAddress>|array{0: int, 1: int, 2: int, 3: int}|array{0: int, 1: int}|CellAddress|int|string

A simple string containing a Cell range like 'A1:E10' or passing in an array of [$fromColumnIndex, $fromRow, $toColumnIndex, $toRow] (e.g. [3, 5, 6, 8]), or a CellAddress or AddressRange object.

Return values
$this

usesPanes()

public usesPanes() : bool
Return values
bool

cellToArray()

protected cellToArray(Cell $cell, bool $calculateFormulas, bool $formatData, null|bool|float|int|RichText|string $nullValue) : mixed
Parameters
$cell : Cell
$calculateFormulas : bool
$formatData : bool
$nullValue : null|bool|float|int|RichText|string

value to use when null

Tags
throws
Exception
throws
Exception

compareColumnBreaks()

protected static compareColumnBreaks(string $coordinate1, string $coordinate2) : int
Parameters
$coordinate1 : string
$coordinate2 : string
Return values
int

compareRowBreaks()

protected static compareRowBreaks(string $coordinate1, string $coordinate2) : int
Parameters
$coordinate1 : string
$coordinate2 : string
Return values
int

getTableIndexByName()

protected getTableIndexByName(string $name) : null|int
Parameters
$name : string

the table name to search

Return values
null|int

The index of the located table in the tables collection, or null if not found

buildNullRow()

Prepare a row data filled with null values to deduplicate the memory areas for empty rows.

private buildNullRow(mixed $nullValue, string $minCol, string $maxCol, bool $returnCellRef, bool $ignoreHidden, array<string, bool> &$hiddenColumns) : array<string|int, mixed>
Parameters
$nullValue : mixed

Value returned in the array entry if a cell doesn't exist

$minCol : string

Start column of the range

$maxCol : string

End column of the range

$returnCellRef : bool

False - Return a simple array of rows and columns indexed by number counting from zero True - Return rows and columns indexed by their actual row and column IDs

$ignoreHidden : bool

False - Return values for rows/columns even if they are defined as hidden. True - Don't return values for rows/columns that are defined as hidden.

$hiddenColumns : array<string, bool>
Return values
array<string|int, mixed>

checkSheetCodeName()

Check sheet code name for valid Excel syntax.

private static checkSheetCodeName(string $sheetCodeName) : string
Parameters
$sheetCodeName : string

The string to check

Return values
string

The valid string

checkSheetTitle()

Check sheet title for valid Excel syntax.

private static checkSheetTitle(string $sheetTitle) : string
Parameters
$sheetTitle : string

The string to check

Return values
string

The valid string

clearMergeCellsByColumn()

private clearMergeCellsByColumn(string $firstColumn, string $lastColumn, int $firstRow, int $lastRow, string $upperLeft, string $behaviour) : void
Parameters
$firstColumn : string
$lastColumn : string
$firstRow : int
$lastRow : int
$upperLeft : string
$behaviour : string

clearMergeCellsByRow()

private clearMergeCellsByRow(string $firstColumn, int $lastColumnIndex, int $firstRow, int $lastRow, string $upperLeft, string $behaviour) : void
Parameters
$firstColumn : string
$lastColumnIndex : int
$firstRow : int
$lastRow : int
$upperLeft : string
$behaviour : string

getCellOrNull()

Get an existing cell at a specific coordinate, or null.

private getCellOrNull(string $coordinate) : null|Cell
Parameters
$coordinate : string

Coordinate of the cell, eg: 'A1'

Return values
null|Cell

Cell that was found or null

getWorksheetAndCoordinate()

Get the correct Worksheet and coordinate from a coordinate that may contains reference to another sheet or a named range.

private getWorksheetAndCoordinate(string $coordinate) : Worksheet, 1: string}
Parameters
$coordinate : string
Return values
Worksheet, 1: string}

getXfIndex()

private getXfIndex(string $coordinate) : int|null
Parameters
$coordinate : string
Return values
int|null

removeColumnDimensions()

private removeColumnDimensions(int $pColumnIndex, int $numberOfColumns) : array<string|int, mixed>
Parameters
$pColumnIndex : int
$numberOfColumns : int
Return values
array<string|int, mixed>

removeRowDimensions()

private removeRowDimensions(int $row, int $numberOfRows) : array<string|int, mixed>
Parameters
$row : int
$numberOfRows : int
Return values
array<string|int, mixed>

setSelectedCellsActivePane()

private setSelectedCellsActivePane() : void

validateNamedRange()

private validateNamedRange(string $definedName[, bool $returnNullIfInvalid = false ]) : DefinedName|null
Parameters
$definedName : string
$returnNullIfInvalid : bool = false
Return values
DefinedName|null

        
On this page

Search results