class Worksheet implements IComparable (View source)

Constants

BREAK_NONE

BREAK_ROW

BREAK_COLUMN

SHEETSTATE_VISIBLE

SHEETSTATE_HIDDEN

SHEETSTATE_VERYHIDDEN

SHEET_TITLE_MAXIMUM_LENGTH

Maximum 31 characters allowed for sheet title.

Methods

__construct(Spreadsheet $parent = null, string $pTitle = 'Worksheet')

Create a new worksheet.

disconnectCells()

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

__destruct()

Code to execute when this worksheet is unset().

getCellCollection()

Return the cell collection.

static array
getInvalidCharacters()

Get array of invalid characters for sheet title.

string[]
getCoordinates(bool $sorted = true)

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

getRowDimensions()

Get collection of row dimensions.

getDefaultRowDimension()

Get default row dimension.

getColumnDimensions()

Get collection of column dimensions.

getDefaultColumnDimension()

Get default column dimension.

getDrawingCollection()

Get collection of drawings.

Chart[]
getChartCollection()

Get collection of charts.

addChart(Chart $pChart, null|int $iChartIndex = null)

Add chart.

int
getChartCount()

Return the count of charts on this worksheet.

Chart|false
getChartByIndex(string $index)

Get a chart by its index position.

string[]
getChartNames()

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

Chart|false
getChartByName(string $chartName)

Get a chart by name.

refreshColumnDimensions()

Refresh column dimensions.

refreshRowDimensions()

Refresh row dimensions.

string
calculateWorksheetDimension()

Calculate worksheet dimension.

string
calculateWorksheetDataDimension()

Calculate worksheet data dimension.

Worksheet;
calculateColumnWidths()

Calculate widths for auto-size columns.

getParent()

Get parent.

rebindParent(Spreadsheet $parent)

Re-bind parent.

string
getTitle()

Get title.

setTitle(string $pValue, bool $updateFormulaCellReferences = true, bool $validate = true)

Set title.

string
getSheetState()

Get sheet state.

setSheetState(string $value)

Set sheet state.

getPageSetup()

Get page setup.

setPageSetup(PageSetup $pValue)

Set page setup.

getPageMargins()

Get page margins.

setPageMargins(PageMargins $pValue)

Set page margins.

getHeaderFooter()

Get page header/footer.

setHeaderFooter(HeaderFooter $pValue)

Set page header/footer.

getSheetView()

Get sheet view.

setSheetView(SheetView $pValue)

Set sheet view.

getProtection()

Get Protection.

setProtection(Protection $pValue)

Set Protection.

string
getHighestColumn(string $row = null)

Get highest worksheet column.

string
getHighestDataColumn(string $row = null)

Get highest worksheet column that contains data.

int
getHighestRow(string $column = null)

Get highest worksheet row.

int
getHighestDataRow(string $column = null)

Get highest worksheet row that contains data.

array
getHighestRowAndColumn()

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

setCellValue(string $pCoordinate, mixed $pValue)

Set a cell value.

setCellValueByColumnAndRow(int $columnIndex, int $row, mixed $value)

Set a cell value by using numeric cell coordinates.

setCellValueExplicit(string $pCoordinate, mixed $pValue, string $pDataType)

Set a cell value.

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

Set a cell value by using numeric cell coordinates.

null|Cell
getCell(string $pCoordinate, bool $createIfNotExists = true)

Get cell at a specific coordinate.

null|Cell
getCellByColumnAndRow(int $columnIndex, int $row, bool $createIfNotExists = true)

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

bool
cellExists(string $pCoordinate)

Does the cell at a specific coordinate exist?

bool
cellExistsByColumnAndRow(int $columnIndex, int $row)

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

getRowDimension(int $pRow, bool $create = true)

Get row dimension at a specific row.

getColumnDimension(string $pColumn, bool $create = true)

Get column dimension at a specific column.

getColumnDimensionByColumn(int $columnIndex)

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

Style[]
getStyles()

Get styles.

getStyle(string $pCellCoordinate)

Get style for cell.

getConditionalStyles(string $pCoordinate)

Get conditional styles for a cell.

bool
conditionalStylesExists(string $pCoordinate)

Do conditional styles exist for this cell?

removeConditionalStyles(string $pCoordinate)

Removes conditional styles for a cell.

array
getConditionalStylesCollection()

Get collection of conditional styles.

setConditionalStyles(string $pCoordinate, $pValue)

Set conditional styles.

getStyleByColumnAndRow(int $columnIndex1, int $row1, null|int $columnIndex2 = null, null|int $row2 = null)

Get style for cell by using numeric cell coordinates.

duplicateStyle(Style $pCellStyle, string $pRange)

Duplicate cell style to a range of cells.

duplicateConditionalStyle(array $pCellStyle, string $pRange = '')

Duplicate conditional style to a range of cells.

setBreak(string $pCoordinate, int $pBreak)

Set break on a cell.

setBreakByColumnAndRow(int $columnIndex, int $row, int $break)

Set break on a cell by using numeric cell coordinates.

array[]
getBreaks()

Get breaks.

mergeCells(string $pRange)

Set merge on a cell range.

mergeCellsByColumnAndRow(int $columnIndex1, int $row1, int $columnIndex2, int $row2)

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

unmergeCells(string $pRange)

Remove merge on a cell range.

unmergeCellsByColumnAndRow(int $columnIndex1, int $row1, int $columnIndex2, int $row2)

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

array[]
getMergeCells()

Get merge cells array.

setMergeCells(array $pValue)

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

protectCells(string $pRange, string $pPassword, bool $pAlreadyHashed = false)

Set protection on a cell range.

protectCellsByColumnAndRow(int $columnIndex1, int $row1, int $columnIndex2, int $row2, string $password, bool $alreadyHashed = false)

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

unprotectCells(string $pRange)

Remove protection on a cell range.

unprotectCellsByColumnAndRow(int $columnIndex1, int $row1, int $columnIndex2, int $row2)

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

array[]
getProtectedCells()

Get protected cells.

getAutoFilter()

Get Autofilter.

setAutoFilter(AutoFilter|string $pValue)

Set AutoFilter.

setAutoFilterByColumnAndRow(int $columnIndex1, int $row1, int $columnIndex2, int $row2)

Set Autofilter Range by using numeric cell coordinates.

removeAutoFilter()

Remove autofilter.

string
getFreezePane()

Get Freeze Pane.

freezePane(null|string $cell, null|string $topLeftCell = null)

Freeze Pane.

freezePaneByColumnAndRow(int $columnIndex, int $row)

Freeze Pane by using numeric cell coordinates.

unfreezePane()

Unfreeze Pane.

int
getTopLeftCell()

Get the default position of the right bottom pane.

insertNewRowBefore(int $pBefore, int $pNumRows = 1)

Insert a new row, updating all possible related data.

insertNewColumnBefore(int $pBefore, int $pNumCols = 1)

Insert a new column, updating all possible related data.

insertNewColumnBeforeByIndex(int $beforeColumnIndex, int $pNumCols = 1)

Insert a new column, updating all possible related data.

removeRow(int $pRow, int $pNumRows = 1)

Delete a row, updating all possible related data.

removeColumn(string $pColumn, int $pNumCols = 1)

Remove a column, updating all possible related data.

removeColumnByIndex(int $columnIndex, int $numColumns = 1)

Remove a column, updating all possible related data.

bool
getShowGridlines()

Show gridlines?

setShowGridlines(bool $pValue)

Set show gridlines.

bool
getPrintGridlines()

Print gridlines?

setPrintGridlines(bool $pValue)

Set print gridlines.

bool
getShowRowColHeaders()

Show row and column headers?

setShowRowColHeaders(bool $pValue)

Set show row and column headers.

bool
getShowSummaryBelow()

Show summary below? (Row/Column outlining).

setShowSummaryBelow(bool $pValue)

Set show summary below.

bool
getShowSummaryRight()

Show summary right? (Row/Column outlining).

setShowSummaryRight(bool $pValue)

Set show summary right.

getComments()

Get comments.

setComments(array $pValue)

Set comments array for the entire sheet.

getComment(string $pCellCoordinate)

Get comment for cell.

getCommentByColumnAndRow(int $columnIndex, int $row)

Get comment for cell by using numeric cell coordinates.

string
getActiveCell()

Get active cell.

string
getSelectedCells()

Get selected cells.

setSelectedCell(string $pCoordinate)

Selected cell.

setSelectedCells(string $pCoordinate)

Select a range of cells.

setSelectedCellByColumnAndRow(int $columnIndex, int $row)

Selected cell by using numeric cell coordinates.

bool
getRightToLeft()

Get right-to-left.

setRightToLeft(bool $value)

Set right-to-left.

fromArray(array $source, mixed $nullValue = null, string $startCell = 'A1', bool $strictNullComparison = false)

Fill worksheet from values in array.

array
rangeToArray(string $pRange, mixed $nullValue = null, bool $calculateFormulas = true, bool $formatData = true, bool $returnCellRef = false)

Create array from a range of cells.

array
namedRangeToArray(string $pNamedRange, mixed $nullValue = null, bool $calculateFormulas = true, bool $formatData = true, bool $returnCellRef = false)

Create array from a range of cells.

array
toArray(mixed $nullValue = null, bool $calculateFormulas = true, bool $formatData = true, bool $returnCellRef = false)

Create array from worksheet.

getRowIterator(int $startRow = 1, int $endRow = null)

Get row iterator.

getColumnIterator(string $startColumn = 'A', string $endColumn = null)

Get column iterator.

garbageCollect()

Run PhpSpreadsheet garbage collector.

string
getHashCode()

Get hash code.

static mixed
extractSheetTitle(string $pRange, bool $returnRange = false)

Extract worksheet title from range.

getHyperlink(string $pCellCoordinate)

Get hyperlink.

setHyperlink(string $pCellCoordinate, Hyperlink $pHyperlink = null)

Set hyperlink.

bool
hyperlinkExists(string $pCoordinate)

Hyperlink at a specific coordinate exists?

getHyperlinkCollection()

Get collection of hyperlinks.

getDataValidation(string $pCellCoordinate)

Get data validation.

setDataValidation(string $pCellCoordinate, DataValidation $pDataValidation = null)

Set data validation.

bool
dataValidationExists(string $pCoordinate)

Data validation at a specific coordinate exists?

getDataValidationCollection()

Get collection of data validations.

string
shrinkRangeToFit(string $range)

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

getTabColor()

Get tab color.

resetTabColor()

Reset tab color.

bool
isTabColorSet()

Tab color set?

copy()

Copy worksheet (!= clone!).

__clone()

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

setCodeName(string $pValue, bool $validate = true)

Define the code name of the sheet.

null|string
getCodeName()

Return the code name of the sheet.

bool
hasCodeName()

Sheet has a code name ?

Details

__construct(Spreadsheet $parent = null, string $pTitle = 'Worksheet')

Create a new worksheet.

Parameters

Spreadsheet $parent
string $pTitle

disconnectCells()

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

__destruct()

Code to execute when this worksheet is unset().

Cells getCellCollection()

Return the cell collection.

Return Value

Cells

static array getInvalidCharacters()

Get array of invalid characters for sheet title.

Return Value

array

string[] getCoordinates(bool $sorted = true)

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

Parameters

bool $sorted Also sort the cell collection?

Return Value

string[]

RowDimension[] getRowDimensions()

Get collection of row dimensions.

Return Value

RowDimension[]

RowDimension getDefaultRowDimension()

Get default row dimension.

Return Value

RowDimension

ColumnDimension[] getColumnDimensions()

Get collection of column dimensions.

Return Value

ColumnDimension[]

ColumnDimension getDefaultColumnDimension()

Get default column dimension.

Return Value

ColumnDimension

BaseDrawing[] getDrawingCollection()

Get collection of drawings.

Return Value

BaseDrawing[]

Chart[] getChartCollection()

Get collection of charts.

Return Value

Chart[]

Chart addChart(Chart $pChart, null|int $iChartIndex = null)

Add chart.

Parameters

Chart $pChart
null|int $iChartIndex Index where chart should go (0,1,..., or null for last)

Return Value

Chart

int getChartCount()

Return the count of charts on this worksheet.

Return Value

int The number of charts

Chart|false getChartByIndex(string $index)

Get a chart by its index position.

Parameters

string $index Chart index position

Return Value

Chart|false

string[] getChartNames()

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

Return Value

string[] The names of charts

Chart|false getChartByName(string $chartName)

Get a chart by name.

Parameters

string $chartName Chart name

Return Value

Chart|false

Worksheet refreshColumnDimensions()

Refresh column dimensions.

Return Value

Worksheet

Worksheet refreshRowDimensions()

Refresh row dimensions.

Return Value

Worksheet

string calculateWorksheetDimension()

Calculate worksheet dimension.

Return Value

string String containing the dimension of this worksheet

string calculateWorksheetDataDimension()

Calculate worksheet data dimension.

Return Value

string String containing the dimension of this worksheet that actually contain data

Worksheet; calculateColumnWidths()

Calculate widths for auto-size columns.

Return Value

Worksheet;

Spreadsheet getParent()

Get parent.

Return Value

Spreadsheet

Worksheet rebindParent(Spreadsheet $parent)

Re-bind parent.

Parameters

Spreadsheet $parent

Return Value

Worksheet

string getTitle()

Get title.

Return Value

string

Worksheet setTitle(string $pValue, bool $updateFormulaCellReferences = true, bool $validate = true)

Set title.

Parameters

string $pValue String containing the dimension of this worksheet
bool $updateFormulaCellReferences 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
bool $validate 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 Value

Worksheet

string getSheetState()

Get sheet state.

Return Value

string Sheet state (visible, hidden, veryHidden)

Worksheet setSheetState(string $value)

Set sheet state.

Parameters

string $value Sheet state (visible, hidden, veryHidden)

Return Value

Worksheet

PageSetup getPageSetup()

Get page setup.

Return Value

PageSetup

Worksheet setPageSetup(PageSetup $pValue)

Set page setup.

Parameters

PageSetup $pValue

Return Value

Worksheet

PageMargins getPageMargins()

Get page margins.

Return Value

PageMargins

Worksheet setPageMargins(PageMargins $pValue)

Set page margins.

Parameters

PageMargins $pValue

Return Value

Worksheet

HeaderFooter getHeaderFooter()

Get page header/footer.

Return Value

HeaderFooter

Worksheet setHeaderFooter(HeaderFooter $pValue)

Set page header/footer.

Parameters

HeaderFooter $pValue

Return Value

Worksheet

SheetView getSheetView()

Get sheet view.

Return Value

SheetView

Worksheet setSheetView(SheetView $pValue)

Set sheet view.

Parameters

SheetView $pValue

Return Value

Worksheet

Protection getProtection()

Get Protection.

Return Value

Protection

Worksheet setProtection(Protection $pValue)

Set Protection.

Parameters

Protection $pValue

Return Value

Worksheet

string getHighestColumn(string $row = null)

Get highest worksheet column.

Parameters

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

Return Value

string Highest column name

string getHighestDataColumn(string $row = null)

Get highest worksheet column that contains data.

Parameters

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

Return Value

string Highest column name that contains data

int getHighestRow(string $column = null)

Get highest worksheet row.

Parameters

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

Return Value

int Highest row number

int getHighestDataRow(string $column = null)

Get highest worksheet row that contains data.

Parameters

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

Return Value

int Highest row number that contains data

array getHighestRowAndColumn()

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

Return Value

array Highest column name and highest row number

Worksheet setCellValue(string $pCoordinate, mixed $pValue)

Set a cell value.

Parameters

string $pCoordinate Coordinate of the cell, eg: 'A1'
mixed $pValue Value of the cell

Return Value

Worksheet

Worksheet setCellValueByColumnAndRow(int $columnIndex, int $row, mixed $value)

Set a cell value by using numeric cell coordinates.

Parameters

int $columnIndex Numeric column coordinate of the cell
int $row Numeric row coordinate of the cell
mixed $value Value of the cell

Return Value

Worksheet

Worksheet setCellValueExplicit(string $pCoordinate, mixed $pValue, string $pDataType)

Set a cell value.

Parameters

string $pCoordinate Coordinate of the cell, eg: 'A1'
mixed $pValue Value of the cell
string $pDataType Explicit data type, see DataType::TYPE_*

Return Value

Worksheet

Worksheet setCellValueExplicitByColumnAndRow(int $columnIndex, int $row, mixed $value, string $dataType)

Set a cell value by using numeric cell coordinates.

Parameters

int $columnIndex Numeric column coordinate of the cell
int $row Numeric row coordinate of the cell
mixed $value Value of the cell
string $dataType Explicit data type, see DataType::TYPE_*

Return Value

Worksheet

null|Cell getCell(string $pCoordinate, bool $createIfNotExists = true)

Get cell at a specific coordinate.

Parameters

string $pCoordinate Coordinate of the cell, eg: 'A1'
bool $createIfNotExists Flag indicating whether a new cell should be created if it doesn't already exist, or a null should be returned instead

Return Value

null|Cell Cell that was found/created or null

Exceptions

Exception

null|Cell getCellByColumnAndRow(int $columnIndex, int $row, bool $createIfNotExists = true)

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

Parameters

int $columnIndex Numeric column coordinate of the cell
int $row Numeric row coordinate of the cell
bool $createIfNotExists Flag indicating whether a new cell should be created if it doesn't already exist, or a null should be returned instead

Return Value

null|Cell Cell that was found/created or null

bool cellExists(string $pCoordinate)

Does the cell at a specific coordinate exist?

Parameters

string $pCoordinate Coordinate of the cell eg: 'A1'

Return Value

bool

Exceptions

Exception

bool cellExistsByColumnAndRow(int $columnIndex, int $row)

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

Parameters

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

Return Value

bool

RowDimension getRowDimension(int $pRow, bool $create = true)

Get row dimension at a specific row.

Parameters

int $pRow Numeric index of the row
bool $create

Return Value

RowDimension

ColumnDimension getColumnDimension(string $pColumn, bool $create = true)

Get column dimension at a specific column.

Parameters

string $pColumn String index of the column eg: 'A'
bool $create

Return Value

ColumnDimension

ColumnDimension getColumnDimensionByColumn(int $columnIndex)

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

Parameters

int $columnIndex Numeric column coordinate of the cell

Return Value

ColumnDimension

Style[] getStyles()

Get styles.

Return Value

Style[]

Style getStyle(string $pCellCoordinate)

Get style for cell.

Parameters

string $pCellCoordinate Cell coordinate (or range) to get style for, eg: 'A1'

Return Value

Style

Exceptions

Exception

Conditional[] getConditionalStyles(string $pCoordinate)

Get conditional styles for a cell.

Parameters

string $pCoordinate eg: 'A1'

Return Value

Conditional[]

bool conditionalStylesExists(string $pCoordinate)

Do conditional styles exist for this cell?

Parameters

string $pCoordinate eg: 'A1'

Return Value

bool

Worksheet removeConditionalStyles(string $pCoordinate)

Removes conditional styles for a cell.

Parameters

string $pCoordinate eg: 'A1'

Return Value

Worksheet

array getConditionalStylesCollection()

Get collection of conditional styles.

Return Value

array

Worksheet setConditionalStyles(string $pCoordinate, $pValue)

Set conditional styles.

Parameters

string $pCoordinate eg: 'A1'
$pValue Conditional[]

Return Value

Worksheet

Style getStyleByColumnAndRow(int $columnIndex1, int $row1, null|int $columnIndex2 = null, null|int $row2 = null)

Get style for cell by using numeric cell coordinates.

Parameters

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

Return Value

Style

Worksheet duplicateStyle(Style $pCellStyle, string $pRange)

Duplicate cell style to a range of cells.

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

Parameters

Style $pCellStyle Cell style to duplicate
string $pRange Range of cells (i.e. "A1:B10"), or just one cell (i.e. "A1")

Return Value

Worksheet

Exceptions

Exception

Worksheet duplicateConditionalStyle(array $pCellStyle, string $pRange = '')

Duplicate conditional style to a range of cells.

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

Parameters

array $pCellStyle Cell style to duplicate
string $pRange Range of cells (i.e. "A1:B10"), or just one cell (i.e. "A1")

Return Value

Worksheet

Exceptions

Exception

Worksheet setBreak(string $pCoordinate, int $pBreak)

Set break on a cell.

Parameters

string $pCoordinate Cell coordinate (e.g. A1)
int $pBreak Break type (type of Worksheet::BREAK_*)

Return Value

Worksheet

Exceptions

Exception

Worksheet setBreakByColumnAndRow(int $columnIndex, int $row, int $break)

Set break on a cell by using numeric cell coordinates.

Parameters

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

Return Value

Worksheet

array[] getBreaks()

Get breaks.

Return Value

array[]

Worksheet mergeCells(string $pRange)

Set merge on a cell range.

Parameters

string $pRange Cell range (e.g. A1:E1)

Return Value

Worksheet

Exceptions

Exception

Worksheet mergeCellsByColumnAndRow(int $columnIndex1, int $row1, int $columnIndex2, int $row2)

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

Parameters

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

Return Value

Worksheet

Exceptions

Exception

Worksheet unmergeCells(string $pRange)

Remove merge on a cell range.

Parameters

string $pRange Cell range (e.g. A1:E1)

Return Value

Worksheet

Exceptions

Exception

Worksheet unmergeCellsByColumnAndRow(int $columnIndex1, int $row1, int $columnIndex2, int $row2)

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

Parameters

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

Return Value

Worksheet

Exceptions

Exception

array[] getMergeCells()

Get merge cells array.

Return Value

array[]

Worksheet setMergeCells(array $pValue)

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

Parameters

array $pValue

Return Value

Worksheet

Worksheet protectCells(string $pRange, string $pPassword, bool $pAlreadyHashed = false)

Set protection on a cell range.

Parameters

string $pRange Cell (e.g. A1) or cell range (e.g. A1:E1)
string $pPassword Password to unlock the protection
bool $pAlreadyHashed If the password has already been hashed, set this to true

Return Value

Worksheet

Worksheet protectCellsByColumnAndRow(int $columnIndex1, int $row1, int $columnIndex2, int $row2, string $password, bool $alreadyHashed = false)

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

Parameters

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

Return Value

Worksheet

Worksheet unprotectCells(string $pRange)

Remove protection on a cell range.

Parameters

string $pRange Cell (e.g. A1) or cell range (e.g. A1:E1)

Return Value

Worksheet

Exceptions

Exception

Worksheet unprotectCellsByColumnAndRow(int $columnIndex1, int $row1, int $columnIndex2, int $row2)

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

Parameters

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

Return Value

Worksheet

Exceptions

Exception

array[] getProtectedCells()

Get protected cells.

Return Value

array[]

AutoFilter getAutoFilter()

Get Autofilter.

Return Value

AutoFilter

Worksheet setAutoFilter(AutoFilter|string $pValue)

Set AutoFilter.

Parameters

AutoFilter|string $pValue A simple string containing a Cell range like 'A1:E10' is permitted for backward compatibility

Return Value

Worksheet

Exceptions

Exception

Worksheet setAutoFilterByColumnAndRow(int $columnIndex1, int $row1, int $columnIndex2, int $row2)

Set Autofilter Range by using numeric cell coordinates.

Parameters

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

Return Value

Worksheet

Exceptions

Exception

Worksheet removeAutoFilter()

Remove autofilter.

Return Value

Worksheet

string getFreezePane()

Get Freeze Pane.

Return Value

string

Worksheet freezePane(null|string $cell, null|string $topLeftCell = null)

Freeze Pane.

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

null|string $cell Position of the split
null|string $topLeftCell default position of the right bottom pane

Return Value

Worksheet

Exceptions

Exception

Worksheet freezePaneByColumnAndRow(int $columnIndex, int $row)

Freeze Pane by using numeric cell coordinates.

Parameters

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

Return Value

Worksheet

Worksheet unfreezePane()

Unfreeze Pane.

Return Value

Worksheet

int getTopLeftCell()

Get the default position of the right bottom pane.

Return Value

int

Worksheet insertNewRowBefore(int $pBefore, int $pNumRows = 1)

Insert a new row, updating all possible related data.

Parameters

int $pBefore Insert before this one
int $pNumRows Number of rows to insert

Return Value

Worksheet

Exceptions

Exception

Worksheet insertNewColumnBefore(int $pBefore, int $pNumCols = 1)

Insert a new column, updating all possible related data.

Parameters

int $pBefore Insert before this one, eg: 'A'
int $pNumCols Number of columns to insert

Return Value

Worksheet

Exceptions

Exception

Worksheet insertNewColumnBeforeByIndex(int $beforeColumnIndex, int $pNumCols = 1)

Insert a new column, updating all possible related data.

Parameters

int $beforeColumnIndex Insert before this one (numeric column coordinate of the cell)
int $pNumCols Number of columns to insert

Return Value

Worksheet

Exceptions

Exception

Worksheet removeRow(int $pRow, int $pNumRows = 1)

Delete a row, updating all possible related data.

Parameters

int $pRow Remove starting with this one
int $pNumRows Number of rows to remove

Return Value

Worksheet

Exceptions

Exception

Worksheet removeColumn(string $pColumn, int $pNumCols = 1)

Remove a column, updating all possible related data.

Parameters

string $pColumn Remove starting with this one, eg: 'A'
int $pNumCols Number of columns to remove

Return Value

Worksheet

Exceptions

Exception

Worksheet removeColumnByIndex(int $columnIndex, int $numColumns = 1)

Remove a column, updating all possible related data.

Parameters

int $columnIndex Remove starting with this one (numeric column coordinate of the cell)
int $numColumns Number of columns to remove

Return Value

Worksheet

Exceptions

Exception

bool getShowGridlines()

Show gridlines?

Return Value

bool

Worksheet setShowGridlines(bool $pValue)

Set show gridlines.

Parameters

bool $pValue Show gridlines (true/false)

Return Value

Worksheet

bool getPrintGridlines()

Print gridlines?

Return Value

bool

Worksheet setPrintGridlines(bool $pValue)

Set print gridlines.

Parameters

bool $pValue Print gridlines (true/false)

Return Value

Worksheet

bool getShowRowColHeaders()

Show row and column headers?

Return Value

bool

Worksheet setShowRowColHeaders(bool $pValue)

Set show row and column headers.

Parameters

bool $pValue Show row and column headers (true/false)

Return Value

Worksheet

bool getShowSummaryBelow()

Show summary below? (Row/Column outlining).

Return Value

bool

Worksheet setShowSummaryBelow(bool $pValue)

Set show summary below.

Parameters

bool $pValue Show summary below (true/false)

Return Value

Worksheet

bool getShowSummaryRight()

Show summary right? (Row/Column outlining).

Return Value

bool

Worksheet setShowSummaryRight(bool $pValue)

Set show summary right.

Parameters

bool $pValue Show summary right (true/false)

Return Value

Worksheet

Comment[] getComments()

Get comments.

Return Value

Comment[]

Worksheet setComments(array $pValue)

Set comments array for the entire sheet.

Parameters

array $pValue

Return Value

Worksheet

Comment getComment(string $pCellCoordinate)

Get comment for cell.

Parameters

string $pCellCoordinate Cell coordinate to get comment for, eg: 'A1'

Return Value

Comment

Exceptions

Exception

Comment getCommentByColumnAndRow(int $columnIndex, int $row)

Get comment for cell by using numeric cell coordinates.

Parameters

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

Return Value

Comment

string getActiveCell()

Get active cell.

Return Value

string Example: 'A1'

string getSelectedCells()

Get selected cells.

Return Value

string

Worksheet setSelectedCell(string $pCoordinate)

Selected cell.

Parameters

string $pCoordinate Cell (i.e. A1)

Return Value

Worksheet

Worksheet setSelectedCells(string $pCoordinate)

Select a range of cells.

Parameters

string $pCoordinate Cell range, examples: 'A1', 'B2:G5', 'A:C', '3:6'

Return Value

Worksheet

Worksheet setSelectedCellByColumnAndRow(int $columnIndex, int $row)

Selected cell by using numeric cell coordinates.

Parameters

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

Return Value

Worksheet

Exceptions

Exception

bool getRightToLeft()

Get right-to-left.

Return Value

bool

Worksheet setRightToLeft(bool $value)

Set right-to-left.

Parameters

bool $value Right-to-left true/false

Return Value

Worksheet

Worksheet fromArray(array $source, mixed $nullValue = null, string $startCell = 'A1', bool $strictNullComparison = false)

Fill worksheet from values in array.

Parameters

array $source Source array
mixed $nullValue Value in source array that stands for blank cell
string $startCell Insert array starting from this cell address as the top left coordinate
bool $strictNullComparison Apply strict comparison when testing for null values in the array

Return Value

Worksheet

Exceptions

Exception

array rangeToArray(string $pRange, mixed $nullValue = null, bool $calculateFormulas = true, bool $formatData = true, bool $returnCellRef = false)

Create array from a range of cells.

Parameters

string $pRange Range of cells (i.e. "A1:B10"), or just one cell (i.e. "A1")
mixed $nullValue Value returned in the array entry if a cell doesn't exist
bool $calculateFormulas Should formulas be calculated?
bool $formatData Should formatting be applied to cell values?
bool $returnCellRef 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

Return Value

array

array namedRangeToArray(string $pNamedRange, mixed $nullValue = null, bool $calculateFormulas = true, bool $formatData = true, bool $returnCellRef = false)

Create array from a range of cells.

Parameters

string $pNamedRange Name of the Named Range
mixed $nullValue Value returned in the array entry if a cell doesn't exist
bool $calculateFormulas Should formulas be calculated?
bool $formatData Should formatting be applied to cell values?
bool $returnCellRef 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

Return Value

array

Exceptions

Exception

array toArray(mixed $nullValue = null, bool $calculateFormulas = true, bool $formatData = true, bool $returnCellRef = false)

Create array from worksheet.

Parameters

mixed $nullValue Value returned in the array entry if a cell doesn't exist
bool $calculateFormulas Should formulas be calculated?
bool $formatData Should formatting be applied to cell values?
bool $returnCellRef 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

Return Value

array

RowIterator getRowIterator(int $startRow = 1, int $endRow = null)

Get row iterator.

Parameters

int $startRow The row number at which to start iterating
int $endRow The row number at which to stop iterating

Return Value

RowIterator

ColumnIterator getColumnIterator(string $startColumn = 'A', string $endColumn = null)

Get column iterator.

Parameters

string $startColumn The column address at which to start iterating
string $endColumn The column address at which to stop iterating

Return Value

ColumnIterator

Worksheet garbageCollect()

Run PhpSpreadsheet garbage collector.

Return Value

Worksheet

string getHashCode()

Get hash code.

Return Value

string Hash code

static mixed extractSheetTitle(string $pRange, bool $returnRange = false)

Extract worksheet title from range.

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

Parameters

string $pRange Range to extract title from
bool $returnRange Return range? (see example)

Return Value

mixed

Get hyperlink.

Parameters

string $pCellCoordinate Cell coordinate to get hyperlink for, eg: 'A1'

Return Value

Hyperlink

Set hyperlink.

Parameters

string $pCellCoordinate Cell coordinate to insert hyperlink, eg: 'A1'
Hyperlink $pHyperlink

Return Value

Worksheet

bool hyperlinkExists(string $pCoordinate)

Hyperlink at a specific coordinate exists?

Parameters

string $pCoordinate eg: 'A1'

Return Value

bool

Hyperlink[] getHyperlinkCollection()

Get collection of hyperlinks.

Return Value

Hyperlink[]

DataValidation getDataValidation(string $pCellCoordinate)

Get data validation.

Parameters

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

Return Value

DataValidation

Worksheet setDataValidation(string $pCellCoordinate, DataValidation $pDataValidation = null)

Set data validation.

Parameters

string $pCellCoordinate Cell coordinate to insert data validation, eg: 'A1'
DataValidation $pDataValidation

Return Value

Worksheet

bool dataValidationExists(string $pCoordinate)

Data validation at a specific coordinate exists?

Parameters

string $pCoordinate eg: 'A1'

Return Value

bool

DataValidation[] getDataValidationCollection()

Get collection of data validations.

Return Value

DataValidation[]

string shrinkRangeToFit(string $range)

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

Parameters

string $range

Return Value

string Adjusted range value

Color getTabColor()

Get tab color.

Return Value

Color

Worksheet resetTabColor()

Reset tab color.

Return Value

Worksheet

bool isTabColorSet()

Tab color set?

Return Value

bool

Worksheet copy()

Copy worksheet (!= clone!).

Return Value

Worksheet

__clone()

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

Worksheet setCodeName(string $pValue, bool $validate = true)

Define the code name of the sheet.

Parameters

string $pValue Same rule as Title minus space not allowed (but, like Excel, change silently space to underscore)
bool $validate 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 Value

Worksheet

Exceptions

Exception

null|string getCodeName()

Return the code name of the sheet.

Return Value

null|string

bool hasCodeName()

Sheet has a code name ?

Return Value

bool