Worksheet implements IComparable
Interfaces, Classes and Traits
Table of Contents
- 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
Constants
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'
Properties
$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
Methods
__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
Tags
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
Tags
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
Tags
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
Tags
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
Tags
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
Tags
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
Tags
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
Tags
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_*)
Tags
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
Tags
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.
Tags
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.
Tags
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
Tags
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
Tags
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
Tags
Return values
$this —cellToArray()
protected
cellToArray(Cell $cell, bool $calculateFormulas, bool $formatData, mixed $nullValue) : mixed
Parameters
- $cell : Cell
- $calculateFormulas : bool
- $formatData : bool
- $nullValue : mixed
Tags
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