Documentation

Worksheet implements IComparable

Interfaces, Classes and Traits

IComparable

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'
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'
$activeCell : string: Active cell. (Only one!).
$autoFilter : AutoFilter: Autofilter Range and selection.
$cachedHighestColumn : int: Cached highest column.
$cachedHighestRow : int: Cached highest row.
$cellCollection : Cells: Collection of cells.
$chartCollection : ArrayObject<int, Chart>: Collection of Chart objects.
$codeName : string: 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 : null|string: 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.
$parent : Spreadsheet|null: Parent spreadsheet.
$printGridlines : bool: Print gridlines?
$protectedCells : array<string|int, string>: 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.
$scrutinizerNullInt : null|int
$scrutinizerNullTable : null|Table
$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 : null|Color: Tab color.
$tableCollection : ArrayObject<int, Table>: Collection of Table objects.
$title : string: Worksheet title.
$topLeftCell : null|string: Default position of the right bottom pane.
__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: Add chart.
addTable() : $this: Add Table.
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?
cellExistsByColumnAndRow() : bool: Cell at a specific coordinate by using numeric cell coordinates exists?
conditionalStylesExists() : bool: Do conditional styles exist for this cell?
copy() : static: Copy worksheet (!= clone!).
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.
freezePaneByColumnAndRow() : $this: Freeze Pane by using numeric cell coordinates.
fromArray() : $this: Fill worksheet from values in array.
garbageCollect() : $this: Run PhpSpreadsheet garbage collector.
getActiveCell() : string: Get active cell.
getAutoFilter() : AutoFilter: Get Autofilter.
getBreaks() : array<string|int, int>: Get breaks.
getCell() : Cell: Get cell at a specific coordinate.
getCellByColumnAndRow() : Cell: Get cell at a specific coordinate by using numeric cell coordinates.
getCellCollection() : Cells: Return the cell collection.
getChartByIndex() : Chart|false: Get a chart by its index position.
getChartByName() : Chart|false: Get a chart by name.
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() : null|string: 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.
getCommentByColumnAndRow() : Comment: Get comment for cell by using numeric cell coordinates.
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() : null|string: 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.
getParent() : Spreadsheet|null: Get parent or null.
getParentOrThrow() : Spreadsheet: Get parent, throw exception if null.
getPrintGridlines() : bool: Print gridlines?
getProtectedCells() : array<string|int, string>: Get 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.
getStyleByColumnAndRow() : Style: Get style for cell by using numeric cell coordinates.
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() : null|string: Get the default position of the right bottom pane.
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.
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.
isTabColorSet() : bool: Tab color set?
mergeCellBehaviour() : array<string|int, mixed>
mergeCells() : $this: Set merge on a cell range.
mergeCellsByColumnAndRow() : $this: Set merge on a cell range by using numeric cell coordinates.
namedRangeToArray() : array<string|int, mixed>: Create array from a range of cells.
nameRequiresQuotes() : bool
protectCells() : $this: Set protection on a cell or cell range.
protectCellsByColumnAndRow() : $this: Set protection on a cell range by using numeric cell coordinates.
rangeToArray() : array<string|int, mixed>: Create array from a range of cells.
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
setAutoFilter() : $this: Set AutoFilter.
setAutoFilterByColumnAndRow() : $this: Set Autofilter Range by using numeric cell coordinates.
setBreak() : $this: Set break on a cell.
setBreakByColumnAndRow() : $this: Set break on a cell by using numeric cell coordinates.
setCellValue() : $this: Set a cell value.
setCellValueByColumnAndRow() : $this: Set a cell value by using numeric cell coordinates.
setCellValueExplicit() : $this: Set a cell value.
setCellValueExplicitByColumnAndRow() : $this: Set a cell value by using numeric cell coordinates.
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.
setPrintGridlines() : $this: Set print gridlines.
setProtection() : $this: Set Protection.
setRightToLeft() : $this: Set right-to-left.
setSelectedCell() : $this: Selected cell.
setSelectedCellByColumnAndRow() : $this: Selected cell by using numeric cell coordinates.
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
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.
unmergeCellsByColumnAndRow() : $this: Remove merge on a cell range by using numeric cell coordinates.
unprotectCells() : $this: Remove protection on a cell or cell range.
unprotectCellsByColumnAndRow() : $this: Remove protection on a cell range by using numeric cell coordinates.
cellToArray() : mixed
compareColumnBreaks() : int
compareRowBreaks() : int
getTableIndexByName() : null|int
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() : array<string|int, mixed>: Get the correct Worksheet and coordinate from a coordinate that may contains reference to another sheet or a named range.
removeColumnDimensions() : array<string|int, mixed>
removeRowDimensions() : array<string|int, mixed>
validateNamedRange() : DefinedName|null

BREAK_COLUMN


    public
    mixed
    BREAK_COLUMN
    = 2

BREAK_NONE


    public
    mixed
    BREAK_NONE
    = ""

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'

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'

$activeCell

Active cell. (Only one!).


    private
        string
    $activeCell
     = 'A1'

$autoFilter

Autofilter Range and selection.


    private
        AutoFilter
    $autoFilter

$cachedHighestColumn

Cached highest column.


    private
        int
    $cachedHighestColumn
     = 1

$cachedHighestRow

Cached highest row.


    private
        int
    $cachedHighestRow
     = 1

$cellCollection

Collection of cells.


    private
        Cells
    $cellCollection

$chartCollection

Collection of Chart objects.


    private
        ArrayObject<int, Chart>
    $chartCollection

$codeName

CodeName.


    private
        string
    $codeName

$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

$dirty

Dirty flag.


    private
        bool
    $dirty
     = true

$drawingCollection

Collection of drawings.


    private
        ArrayObject<int, BaseDrawing>
    $drawingCollection

$freezePane

Freeze pane.


    private
        null|string
    $freezePane

$hash

Hash.


    private
        string
    $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

$parent

Parent spreadsheet.


    private
        Spreadsheet|null
    $parent

$printGridlines

Print gridlines?


    private
        bool
    $printGridlines
     = false

$protectedCells

Collection of protected cell ranges.


    private
        array<string|int, string>
    $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
     = []

$scrutinizerNullInt


    private
    static    null|int
    $scrutinizerNullInt

$scrutinizerNullTable


    private
    static    null|Table
    $scrutinizerNullTable

$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
        null|Color
    $tabColor

$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
        null|string
    $topLeftCell

__clone()

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


    public
                __clone() : mixed

Return values

mixed —

__construct()

Create a new worksheet.


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

Parameters

$parent : Spreadsheet|null = null
$title : string = 'Worksheet'

Return values

mixed —

__destruct()

Code to execute when this worksheet is unset().


    public
                __destruct() : mixed

Return values

mixed —

addChart()

Add chart.


    public
                addChart(Chart $chart[, null|int $chartIndex = null ]) : Chart

Parameters

$chart : Chart
$chartIndex : null|int = null: Index where chart should go (0,1,..., or null for last)

Return values

Chart —

addTable()

Add Table.


    public
                addTable(Table $table) : $this

Parameters

$table : Table

Return values

$this —

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<string|int, int>|CellAddress|string $coordinate) : bool

Parameters

$coordinate : array<string|int, 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 —

cellExistsByColumnAndRow()

Cell at a specific coordinate by using numeric cell coordinates exists?


    public
                cellExistsByColumnAndRow(int $columnIndex, int $row) : bool

Parameters

$columnIndex : int: Numeric column coordinate of the cell
$row : int: Numeric row coordinate of the cell

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 —

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

Return values

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 $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: Range to extract title from
$returnRange : bool = false: Return range? (see example)

Return values

mixed —

freezePane()

Freeze Pane.


    public
                freezePane(null|array<string|int, int>|CellAddress|string $coordinate[, null|array<string|int, int>|CellAddress|string $topLeftCell = null ]) : $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<string|int, 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<string|int, 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.

Return values

$this —

freezePaneByColumnAndRow()

Freeze Pane by using numeric cell coordinates.


    public
                freezePaneByColumnAndRow(int $columnIndex, int $row) : $this

Parameters

$columnIndex : int: Numeric column coordinate of the cell
$row : int: Numeric row coordinate of the cell

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'

getAutoFilter()

Get Autofilter.


    public
                getAutoFilter() : AutoFilter

Return values

AutoFilter —

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<string|int, int>|CellAddress|string $coordinate) : Cell

Parameters

$coordinate : array<string|int, 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.

getCellByColumnAndRow()

Get cell at a specific coordinate by using numeric cell coordinates.


    public
                getCellByColumnAndRow(int $columnIndex, int $row) : Cell

Parameters

$columnIndex : int: Numeric column coordinate of the cell
$row : int: Numeric row coordinate of the cell

Return values

Cell —

Cell that was found/created or null 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 —

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() : null|string

Return values

null|string —

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 $endColumn = null ]) : ColumnIterator

Parameters

$startColumn : string = 'A': The column address at which to start iterating
$endColumn : string = null: The column address at which to stop iterating

Return values

ColumnIterator —

getComment()

Get comment for cell.


    public
                getComment(array<string|int, int>|CellAddress|string $cellCoordinate) : Comment

Parameters

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

Comment —

getCommentByColumnAndRow()

Get comment for cell by using numeric cell coordinates.


    public
                getCommentByColumnAndRow(int $columnIndex, int $row) : Comment

Parameters

$columnIndex : int: Numeric column coordinate of the cell
$row : int: Numeric row coordinate of the cell

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() : null|string

Return values

null|string —

getHashCode()

Get hash code.


    public
                getHashCode() : string

Return values

string —

Hash code

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 —

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 —

getProtectedCells()

Get 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 $endRow = null ]) : RowIterator

Parameters

$startRow : int = 1: The row number at which to start iterating
$endRow : int = 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|array<string|int, int>|CellAddress|int|string $cellCoordinate) : Style

Parameters

$cellCoordinate : AddressRange|array<string|int, 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 —

getStyleByColumnAndRow()

Get style for cell by using numeric cell coordinates.


    public
                getStyleByColumnAndRow(int $columnIndex1, int $row1[, null|int $columnIndex2 = null ][, null|int $row2 = null ]) : Style

Parameters

$columnIndex1 : int: Numeric column coordinate of the cell
$row1 : int: Numeric row coordinate of the cell
$columnIndex2 : null|int = null: Numeric column coordinate of the range cell
$row2 : null|int = null: Numeric row coordinate of the range cell

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() : null|string

Return values

null|string —

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 —

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) : 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: 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) : 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: Possible Flag Values are: CellIterator::TREAT_NULL_VALUE_AS_EMPTY_CELL CellIterator::TREAT_EMPTY_STRING_AS_EMPTY_CELL

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|array<string|int, int>|string $range[, string $behaviour = self::MERGE_CELL_CONTENT_EMPTY ]) : $this

Parameters

$range : AddressRange|array<string|int, 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 —

mergeCellsByColumnAndRow()

Set merge on a cell range by using numeric cell coordinates.


    public
                mergeCellsByColumnAndRow(int $columnIndex1, int $row1, int $columnIndex2, int $row2[, string $behaviour = self::MERGE_CELL_CONTENT_EMPTY ]) : $this

Parameters

$columnIndex1 : int: Numeric column coordinate of the first cell
$row1 : int: Numeric row coordinate of the first cell
$columnIndex2 : int: Numeric column coordinate of the last cell
$row2 : int: Numeric row coordinate of the last cell
$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[, mixed $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 : mixed = 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|array<string|int, int>|CellAddress|int|string $range, string $password[, bool $alreadyHashed = false ]) : $this

Parameters

$range : AddressRange|array<string|int, 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

Return values

$this —

protectCellsByColumnAndRow()

Set protection on a cell range by using numeric cell coordinates.


    public
                protectCellsByColumnAndRow(int $columnIndex1, int $row1, int $columnIndex2, int $row2, string $password[, bool $alreadyHashed = false ]) : $this

Parameters

$columnIndex1 : int: Numeric column coordinate of the first cell
$row1 : int: Numeric row coordinate of the first cell
$columnIndex2 : int: Numeric column coordinate of the last cell
$row2 : int: Numeric row coordinate of the last cell
$password : string: Password to unlock the protection
$alreadyHashed : bool = false: If the password has already been hashed, set this to true

Return values

$this —

rangeToArray()

Create array from a range of cells.


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

Parameters

$range : string
$nullValue : mixed = 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> —

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<string|int, int>|CellAddress|string $cellCoordinate) : $this

Parameters

$cellCoordinate : array<string|int, 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 —

setAutoFilter()

Set AutoFilter.


    public
                setAutoFilter(AddressRange|array<string|int, int>|AutoFilter|string $autoFilterOrRange) : $this

Parameters

$autoFilterOrRange : AddressRange|array<string|int, 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 —

setAutoFilterByColumnAndRow()

Set Autofilter Range by using numeric cell coordinates.


    public
                setAutoFilterByColumnAndRow(int $columnIndex1, int $row1, int $columnIndex2, int $row2) : $this

Parameters

$columnIndex1 : int: Numeric column coordinate of the first cell
$row1 : int: Numeric row coordinate of the first cell
$columnIndex2 : int: Numeric column coordinate of the second cell
$row2 : int: Numeric row coordinate of the second cell

Return values

$this —

setBreak()

Set break on a cell.


    public
                setBreak(array<string|int, int>|CellAddress|string $coordinate, int $break[, int $max = -1 ]) : $this

Parameters

$coordinate : array<string|int, 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 —

setBreakByColumnAndRow()

Set break on a cell by using numeric cell coordinates.


    public
                setBreakByColumnAndRow(int $columnIndex, int $row, int $break) : $this

Parameters

$columnIndex : int: Numeric column coordinate of the cell
$row : int: Numeric row coordinate of the cell
$break : int: Break type (type of Worksheet::BREAK_*)

Return values

$this —

setCellValue()

Set a cell value.


    public
                setCellValue(array<string|int, int>|CellAddress|string $coordinate, mixed $value[, null|IValueBinder $binder = null ]) : $this

Parameters

$coordinate : array<string|int, 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 —

setCellValueByColumnAndRow()

Set a cell value by using numeric cell coordinates.


    public
                setCellValueByColumnAndRow(int $columnIndex, int $row, mixed $value[, null|IValueBinder $binder = null ]) : $this

Parameters

$columnIndex : int: Numeric column coordinate of the cell
$row : int: Numeric row coordinate of the cell
$value : mixed: Value of 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<string|int, int>|CellAddress|string $coordinate, mixed $value, string $dataType) : $this

Parameters

$coordinate : array<string|int, 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 —

setCellValueExplicitByColumnAndRow()

Set a cell value by using numeric cell coordinates.


    public
                setCellValueExplicitByColumnAndRow(int $columnIndex, int $row, mixed $value, string $dataType) : $this

Parameters

$columnIndex : int: Numeric column coordinate of the cell
$row : int: Numeric row coordinate of the cell
$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 —

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 —

setSelectedCellByColumnAndRow()

Selected cell by using numeric cell coordinates.


    public
                setSelectedCellByColumnAndRow(int $columnIndex, int $row) : $this

Parameters

$columnIndex : int: Numeric column coordinate of the cell
$row : int: Numeric row coordinate of the cell

Return values

$this —

setSelectedCells()

Select a range of cells.


    public
                setSelectedCells(AddressRange|array<string|int, int>|CellAddress|int|string $coordinate) : $this

Parameters

$coordinate : AddressRange|array<string|int, 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 —

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([mixed $nullValue = null ][, bool $calculateFormulas = true ][, bool $formatData = true ][, bool $returnCellRef = false ][, bool $ignoreHidden = false ]) : array<string|int, mixed>

Parameters

$nullValue : mixed = 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|array<string|int, int>|string $range) : $this

Parameters

$range : AddressRange|array<string|int, 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 —

unmergeCellsByColumnAndRow()

Remove merge on a cell range by using numeric cell coordinates.


    public
                unmergeCellsByColumnAndRow(int $columnIndex1, int $row1, int $columnIndex2, int $row2) : $this

Parameters

$columnIndex1 : int: Numeric column coordinate of the first cell
$row1 : int: Numeric row coordinate of the first cell
$columnIndex2 : int: Numeric column coordinate of the last cell
$row2 : int: Numeric row coordinate of the last cell

Return values

$this —

unprotectCells()

Remove protection on a cell or cell range.


    public
                unprotectCells(AddressRange|array<string|int, int>|CellAddress|int|string $range) : $this

Parameters

$range : AddressRange|array<string|int, 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 —

unprotectCellsByColumnAndRow()

Remove protection on a cell range by using numeric cell coordinates.


    public
                unprotectCellsByColumnAndRow(int $columnIndex1, int $row1, int $columnIndex2, int $row2) : $this

Parameters

$columnIndex1 : int: Numeric column coordinate of the first cell
$row1 : int: Numeric row coordinate of the first cell
$columnIndex2 : int: Numeric column coordinate of the last cell
$row2 : int: Numeric row coordinate of the last cell

Return values

$this —

cellToArray()


    protected
                cellToArray(Cell $cell, bool $calculateFormulas, bool $formatData, mixed $nullValue) : mixed

Parameters

$cell : Cell
$calculateFormulas : bool
$formatData : bool
$nullValue : mixed

Return values

mixed —

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

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

Return values

void —

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

Return values

void —

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

Parameters

$coordinate : string

Return values

array<string|int, mixed> —

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> —

validateNamedRange()


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

Parameters

$definedName : string
$returnNullIfInvalid : bool = false

Return values

DefinedName|null —

Worksheet implements IComparable

Interfaces, Classes and Traits

Table of Contents

Constants

BREAK_COLUMN

BREAK_NONE

BREAK_ROW

BREAK_ROW_MAX_COLUMN

MERGE_CELL_CONTENT_EMPTY

MERGE_CELL_CONTENT_HIDE

MERGE_CELL_CONTENT_MERGE

SHEET_TITLE_MAXIMUM_LENGTH

SHEETSTATE_HIDDEN

SHEETSTATE_VERYHIDDEN

SHEETSTATE_VISIBLE

SHEET_NAME_REQUIRES_NO_QUOTES

Properties

$activeCell

$autoFilter

$cachedHighestColumn

$cachedHighestRow

$cellCollection

$chartCollection

$codeName

$columnBreaks

$columnDimensions

$comments

$conditionalStylesCollection

$dataValidationCollection

$defaultColumnDimension

$defaultRowDimension

$dirty

$drawingCollection

$freezePane

$hash

$headerFooter

$hyperlinkCollection

$invalidCharacters

$mergeCells

$pageMargins

$pageSetup

$parent

$printGridlines

$protectedCells

$protection

$rightToLeft

$rowBreaks

$rowDimensions

$scrutinizerNullInt

$scrutinizerNullTable

$selectedCells

$sheetState

$sheetView

$showGridlines

$showRowColHeaders

$showSummaryBelow

$showSummaryRight

$styles

$tabColor

$tableCollection

$title

$topLeftCell

Methods

__clone()

Return values

__construct()

Parameters

Return values

__destruct()

Return values

addChart()

Parameters

Return values

addTable()

Parameters

Return values

calculateColumnWidths()

Return values

calculateWorksheetDataDimension()

Return values