Documentation

Worksheet
in package

Application

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.
$drawingCollection : ArrayObject<int, BaseDrawing>: Collection of drawings.
$freezePane : string|null: Freeze pane.
$hash : int: 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().
__wakeup() : void
addChart() : Chart
addTable() : $this: Add Table.
applyStylesFromArray() : bool
calculateArrays() : void
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
getHashInt() : int
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.
isCellInSpillRange() : bool
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

BREAK_COLUMN


    public
        mixed
    BREAK_COLUMN
    = 2

BREAK_NONE


    public
        mixed
    BREAK_NONE
    = 0

BREAK_ROW


    public
        mixed
    BREAK_ROW
    = 1

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]

$activeCell

Active cell. (Only one!).


    private
        string
    $activeCell
     = 'A1'

$activePane


    private
        string
    $activePane
     = ''

$autoFilter

Autofilter Range and selection.


    private
        AutoFilter
    $autoFilter

$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

$cellCollection

Collection of cells.


    private
        Cells
    $cellCollection

$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
     = []

$columnDimensions

Collection of column dimensions.


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

$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
     = []

$defaultColumnDimension

Default column dimension.


    private
        ColumnDimension
    $defaultColumnDimension

$defaultRowDimension

Default row dimension.


    private
        RowDimension
    $defaultRowDimension

$drawingCollection

Collection of drawings.


    private
        ArrayObject<int, BaseDrawing>
    $drawingCollection

$freezePane

Freeze pane.


    private
        string|null
    $freezePane
     = null

$hash

Hash.


    private
        int
    $hash

$headerFooter

Page header/footer.


    private
        HeaderFooter
    $headerFooter

$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
     = []

$pageMargins

Page margins.


    private
        PageMargins
    $pageMargins

$pageSetup

Page setup.


    private
        PageSetup
    $pageSetup

$panes

Properties of the 4 panes.


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

$paneState


    private
        string
    $paneState
     = ''

$paneTopLeftCell


    private
        string
    $paneTopLeftCell
     = ''

$parent

Parent spreadsheet.


    private
        Spreadsheet|null
    $parent
     = null

$printGridlines

Print gridlines?


    private
        bool
    $printGridlines
     = false

$protectedCells

Collection of protected cell ranges.


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

$protection

Protection.


    private
        Protection
    $protection

$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

$sheetView

Sheet view.


    private
        SheetView
    $sheetView

$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
     = []

$tabColor

Tab color.


    private
        Color|null
    $tabColor
     = null

$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

$xSplit


    private
        int
    $xSplit
     = 0

$ySplit


    private
        int
    $ySplit
     = 0

__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

__wakeup()


    public
                    __wakeup() : void

addChart()


    public
                    addChart(Chart $chart) : Chart

Parameters

$chart : Chart

Return values

Chart

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

calculateArrays()


    public
                    calculateArrays([bool $preCalculateFormulas = true ]) : void

Parameters

$preCalculateFormulas : bool = true

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

getAutoFilter()

Get Autofilter.


    public
                    getAutoFilter() : AutoFilter

Return values

AutoFilter

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

getColumnDimensions()

Get collection of column dimensions.


    public
                    getColumnDimensions() : array<string|int, ColumnDimension>

Return values

array<string|int, 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>

getDefaultColumnDimension()

Get default column dimension.


    public
                    getDefaultColumnDimension() : ColumnDimension

Return values

ColumnDimension

getDefaultRowDimension()

Get default row dimension.


    public
                    getDefaultRowDimension() : RowDimension

Return values

RowDimension

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


    public
                    getHashCode() : string

Return values

string

getHashInt()


    public
                    getHashInt() : int

Return values

int

getHeaderFooter()

Get page header/footer.


    public
                    getHeaderFooter() : HeaderFooter

Return values

HeaderFooter

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

getHyperlink()

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>

getPageMargins()

Get page margins.


    public
                    getPageMargins() : PageMargins

Return values

PageMargins

getPageSetup()

Get page setup.


    public
                    getPageSetup() : PageSetup

Return values

PageSetup

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

getParent()

Get parent or null.


    public
                    getParent() : Spreadsheet|null

Return values

Spreadsheet|null

getParentOrThrow()

Get parent, throw exception if null.


    public
                    getParentOrThrow() : Spreadsheet

Return values

Spreadsheet

getPrintGridlines()

Print gridlines?


    public
                    getPrintGridlines() : bool

Return values

bool

getProtectedCellRanges()

Get protected cells.


    public
                    getProtectedCellRanges() : array<string|int, ProtectedRange>

Return values

array<string|int, ProtectedRange>

getProtectedCells()

Get password for protected cells.


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

Return values

array<string|int, string>

getProtection()

Get Protection.


    public
                    getProtection() : Protection

Return values

Protection

getRightToLeft()

Get right-to-left.


    public
                    getRightToLeft() : bool

Return values

bool

getRowBreaks()

Get row breaks.


    public
                    getRowBreaks() : array<string|int, PageBreak>

Return values

array<string|int, PageBreak>

getRowDimension()

Get row dimension at a specific row.


    public
                    getRowDimension(int $row) : RowDimension

Parameters

$row : int: Numeric index of the row

Return values

RowDimension

getRowDimensions()

Get collection of row dimensions.


    public
                    getRowDimensions() : array<string|int, RowDimension>

Return values

array<string|int, 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)

getSheetView()

Get sheet view.


    public
                    getSheetView() : SheetView

Return values

SheetView

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>

getTabColor()

Get tab color.


    public
                    getTabColor() : Color

Return values

Color

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

isCellInSpillRange()


    public
                    isCellInSpillRange(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 ][, bool $reduceArrays = 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.
$reduceArrays : bool = false

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 ][, bool $reduceArrays = 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.
$reduceArrays : bool = false

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 ][, bool $reduceArrays = 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.
$reduceArrays : bool = false

Return values

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

rebindParent()

Re-bind parent.


    public
                    rebindParent(Spreadsheet $parent) : $this

Parameters

$parent : Spreadsheet

Return values

$this

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.

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

setHeaderFooter()

Set page header/footer.


    public
                    setHeaderFooter(HeaderFooter $headerFooter) : $this

Parameters

$headerFooter : HeaderFooter

Return values

$this

setHyperlink()

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

setPageMargins()

Set page margins.


    public
                    setPageMargins(PageMargins $pageMargins) : $this

Parameters

$pageMargins : PageMargins

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

setProtection()

Set Protection.


    public
                    setProtection(Protection $protection) : $this

Parameters

$protection : Protection

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 ][, bool $reduceArrays = 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.
$reduceArrays : bool = false

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

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

columnDimensionCompare()


    private
            static        columnDimensionCompare(ColumnDimension $a, ColumnDimension $b) : int

Parameters

$a : ColumnDimension
$b : ColumnDimension

Return values

int

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

Worksheet in package Application

Table of Contents

Constants

Properties

Methods

Constants

BREAK_COLUMN

BREAK_NONE

BREAK_ROW

BREAK_ROW_MAX_COLUMN

MERGE_CELL_CONTENT_EMPTY

MERGE_CELL_CONTENT_HIDE

MERGE_CELL_CONTENT_MERGE

PANE_FROZEN

PANE_FROZENSPLIT

PANE_SPLIT

SHEET_TITLE_MAXIMUM_LENGTH

SHEETSTATE_HIDDEN

SHEETSTATE_VERYHIDDEN

SHEETSTATE_VISIBLE

SHEET_NAME_REQUIRES_NO_QUOTES

VALIDFROZENSTATE

VALIDPANESTATE

Properties

$activeCell

$activePane

$autoFilter

$backgroundExtension

$backgroundImage

$backgroundMime

$cachedHighestColumn

$cachedHighestRow

$cellCollection

$cellCollectionInitialized

$chartCollection

$codeName

$columnBreaks

$columnDimensions

$comments

$conditionalStylesCollection

$dataValidationCollection

$defaultColumnDimension

$defaultRowDimension

$drawingCollection

$freezePane

$hash

$headerFooter

$hyperlinkCollection

$invalidCharacters

$mergeCells

$pageMargins

$pageSetup

$panes

$paneState

$paneTopLeftCell

$parent

$printGridlines

$protectedCells

$protection

$rightToLeft

$rowBreaks

$rowDimensions

$selectedCells

$sheetState

$sheetView

$showGridlines

$showRowColHeaders

$showSummaryBelow

$showSummaryRight

$styles

$tabColor

$tableCollection

$title

$topLeftCell

$xSplit

$ySplit

Methods

__clone()

__construct()

Parameters

Worksheet
in package

Application