See Release Notes
Long Term Support Release
<?php namespace PhpOffice\PhpSpreadsheet\Worksheet; use ArrayObject; use PhpOffice\PhpSpreadsheet\Calculation\Calculation;> use PhpOffice\PhpSpreadsheet\Calculation\Functions; use PhpOffice\PhpSpreadsheet\Cell\Cell; > use PhpOffice\PhpSpreadsheet\Cell\AddressRange;use PhpOffice\PhpSpreadsheet\Cell\Coordinate;> use PhpOffice\PhpSpreadsheet\Cell\CellAddress; use PhpOffice\PhpSpreadsheet\Cell\DataType; > use PhpOffice\PhpSpreadsheet\Cell\CellRange;use PhpOffice\PhpSpreadsheet\Cell\DataValidation; use PhpOffice\PhpSpreadsheet\Cell\Hyperlink;> use PhpOffice\PhpSpreadsheet\Cell\IValueBinder;use PhpOffice\PhpSpreadsheet\Chart\Chart; use PhpOffice\PhpSpreadsheet\Collection\Cells; use PhpOffice\PhpSpreadsheet\Collection\CellsFactory; use PhpOffice\PhpSpreadsheet\Comment;> use PhpOffice\PhpSpreadsheet\DefinedName;use PhpOffice\PhpSpreadsheet\Exception; use PhpOffice\PhpSpreadsheet\IComparable;< use PhpOffice\PhpSpreadsheet\NamedRange;use PhpOffice\PhpSpreadsheet\ReferenceHelper; use PhpOffice\PhpSpreadsheet\RichText\RichText; use PhpOffice\PhpSpreadsheet\Shared; use PhpOffice\PhpSpreadsheet\Spreadsheet;> use PhpOffice\PhpSpreadsheet\Style\Alignment;use PhpOffice\PhpSpreadsheet\Style\Color; use PhpOffice\PhpSpreadsheet\Style\Conditional; use PhpOffice\PhpSpreadsheet\Style\NumberFormat; use PhpOffice\PhpSpreadsheet\Style\Style; class Worksheet implements IComparable { // Break types< const BREAK_NONE = 0; < const BREAK_ROW = 1; < const BREAK_COLUMN = 2;> public const BREAK_NONE = 0; > public const BREAK_ROW = 1; > public const BREAK_COLUMN = 2; > // Maximum column for row break > public const BREAK_ROW_MAX_COLUMN = 16383;// Sheet state< const SHEETSTATE_VISIBLE = 'visible'; < const SHEETSTATE_HIDDEN = 'hidden'; < const SHEETSTATE_VERYHIDDEN = 'veryHidden';> public const SHEETSTATE_VISIBLE = 'visible'; > public const SHEETSTATE_HIDDEN = 'hidden'; > public const SHEETSTATE_VERYHIDDEN = 'veryHidden'; > > public const MERGE_CELL_CONTENT_EMPTY = 'empty'; > public const MERGE_CELL_CONTENT_HIDE = 'hide'; > public const MERGE_CELL_CONTENT_MERGE = 'merge'; > > protected const SHEET_NAME_REQUIRES_NO_QUOTES = '/^[_\p{L}][_\p{L}\p{N}]*$/mui';/** * Maximum 31 characters allowed for sheet title. * * @var int */ const SHEET_TITLE_MAXIMUM_LENGTH = 31; /** * Invalid characters in sheet title. * * @var array */ private static $invalidCharacters = ['*', ':', '/', '\\', '?', '[', ']']; /** * Parent spreadsheet. *< * @var Spreadsheet> * @var ?Spreadsheet*/ private $parent; /** * Collection of cells. * * @var Cells */ private $cellCollection; /** * Collection of row dimensions. * * @var RowDimension[] */ private $rowDimensions = []; /** * Default row dimension. * * @var RowDimension */ private $defaultRowDimension; /** * Collection of column dimensions. * * @var ColumnDimension[] */ private $columnDimensions = []; /** * Default column dimension. * * @var ColumnDimension */ private $defaultColumnDimension; /** * Collection of drawings. *< * @var BaseDrawing[]> * @var ArrayObject<int, BaseDrawing>*/ private $drawingCollection; /** * Collection of Chart objects. *< * @var Chart[]> * @var ArrayObject<int, Chart> > */ > private $chartCollection; > > /** > * Collection of Table objects. > * > * @var ArrayObject<int, Table>*/< private $chartCollection = [];> private $tableCollection;/** * Worksheet title. * * @var string */ private $title; /** * Sheet state. * * @var string */ private $sheetState; /** * Page setup. * * @var PageSetup */ private $pageSetup; /** * Page margins. * * @var PageMargins */ private $pageMargins; /** * Page header/footer. * * @var HeaderFooter */ private $headerFooter; /** * Sheet view. * * @var SheetView */ private $sheetView; /** * Protection. * * @var Protection */ private $protection; /** * Collection of styles. * * @var Style[] */ private $styles = []; /** * Conditional styles. Indexed by cell coordinate, e.g. 'A1'. * * @var array */ private $conditionalStylesCollection = []; /**< * Is the current cell collection sorted already?> * Collection of row breaks.*< * @var bool> * @var PageBreak[]*/< private $cellCollectionIsSorted = false;> private $rowBreaks = [];/**< * Collection of breaks.> * Collection of column breaks.*< * @var array> * @var PageBreak[]*/< private $breaks = [];> private $columnBreaks = [];/** * Collection of merged cell ranges. *< * @var array> * @var string[]*/ private $mergeCells = []; /** * Collection of protected cell ranges. *< * @var array> * @var string[]*/ private $protectedCells = []; /** * Autofilter Range and selection. * * @var AutoFilter */ private $autoFilter; /** * Freeze pane. * * @var null|string */ private $freezePane; /** * Default position of the right bottom pane. * * @var null|string */ private $topLeftCell; /** * Show gridlines? * * @var bool */ private $showGridlines = true; /** * Print gridlines? * * @var bool */ private $printGridlines = false; /** * Show row and column headers? * * @var bool */ private $showRowColHeaders = true; /** * Show summary below? (Row/Column outline). * * @var bool */ private $showSummaryBelow = true; /** * Show summary right? (Row/Column outline). * * @var bool */ private $showSummaryRight = true; /** * Collection of comments. * * @var Comment[] */ private $comments = []; /** * Active cell. (Only one!). * * @var string */ private $activeCell = 'A1'; /** * Selected cells. * * @var string */ private $selectedCells = 'A1'; /** * Cached highest column. *< * @var string> * @var int*/< private $cachedHighestColumn = 'A';> private $cachedHighestColumn = 1;/** * Cached highest row. * * @var int */ private $cachedHighestRow = 1; /** * Right-to-left? * * @var bool */ private $rightToLeft = false; /** * Hyperlinks. Indexed by cell coordinate, e.g. 'A1'. * * @var array */ private $hyperlinkCollection = []; /** * Data validation objects. Indexed by cell coordinate, e.g. 'A1'. * * @var array */ private $dataValidationCollection = []; /** * Tab color. *< * @var Color> * @var null|Color*/ private $tabColor; /** * Dirty flag. * * @var bool */ private $dirty = true; /** * Hash. * * @var string */ private $hash; /** * CodeName. * * @var string */ private $codeName; /** * Create a new worksheet. *< * @param Spreadsheet $parent < * @param string $pTitle> * @param string $title*/< public function __construct(Spreadsheet $parent = null, $pTitle = 'Worksheet')> public function __construct(?Spreadsheet $parent = null, $title = 'Worksheet'){ // Set parent and title $this->parent = $parent;< $this->setTitle($pTitle, false);> $this->setTitle($title, false);// setTitle can change $pTitle $this->setCodeName($this->getTitle()); $this->setSheetState(self::SHEETSTATE_VISIBLE); $this->cellCollection = CellsFactory::getInstance($this); // Set page setup $this->pageSetup = new PageSetup(); // Set page margins $this->pageMargins = new PageMargins(); // Set page header/footer $this->headerFooter = new HeaderFooter(); // Set sheet view $this->sheetView = new SheetView(); // Drawing collection< $this->drawingCollection = new \ArrayObject();> $this->drawingCollection = new ArrayObject();// Chart collection< $this->chartCollection = new \ArrayObject();> $this->chartCollection = new ArrayObject();// Protection $this->protection = new Protection(); // Default row dimension $this->defaultRowDimension = new RowDimension(null); // Default column dimension $this->defaultColumnDimension = new ColumnDimension(null);< $this->autoFilter = new AutoFilter(null, $this);> // AutoFilter > $this->autoFilter = new AutoFilter('', $this); > // Table collection > $this->tableCollection = new ArrayObject();} /** * Disconnect all cells from this Worksheet object, * typically so that the worksheet object can be unset. */< public function disconnectCells()> public function disconnectCells(): void{ if ($this->cellCollection !== null) { $this->cellCollection->unsetWorksheetCells();> // @phpstan-ignore-next-line$this->cellCollection = null; } // detach ourself from the workbook, so that it can then delete this worksheet successfully $this->parent = null; } /** * Code to execute when this worksheet is unset(). */ public function __destruct() { Calculation::getInstance($this->parent)->clearCalculationCacheForWorksheet($this->title); $this->disconnectCells();> $this->rowDimensions = [];} /** * Return the cell collection. * * @return Cells */ public function getCellCollection() { return $this->cellCollection; } /** * Get array of invalid characters for sheet title. * * @return array */ public static function getInvalidCharacters() { return self::$invalidCharacters; } /** * Check sheet code name for valid Excel syntax. *< * @param string $pValue The string to check < * < * @throws Exception> * @param string $sheetCodeName The string to check* * @return string The valid string */< private static function checkSheetCodeName($pValue)> private static function checkSheetCodeName($sheetCodeName){< $CharCount = Shared\StringHelper::countCharacters($pValue); < if ($CharCount == 0) {> $charCount = Shared\StringHelper::countCharacters($sheetCodeName); > if ($charCount == 0) {throw new Exception('Sheet code name cannot be empty.'); } // Some of the printable ASCII characters are invalid: * : / \ ? [ ] and first and last characters cannot be a "'"< if ((str_replace(self::$invalidCharacters, '', $pValue) !== $pValue) || < (Shared\StringHelper::substring($pValue, -1, 1) == '\'') || < (Shared\StringHelper::substring($pValue, 0, 1) == '\'')) {> if ( > (str_replace(self::$invalidCharacters, '', $sheetCodeName) !== $sheetCodeName) || > (Shared\StringHelper::substring($sheetCodeName, -1, 1) == '\'') || > (Shared\StringHelper::substring($sheetCodeName, 0, 1) == '\'') > ) {throw new Exception('Invalid character found in sheet code name'); } // Enforce maximum characters allowed for sheet title< if ($CharCount > self::SHEET_TITLE_MAXIMUM_LENGTH) {> if ($charCount > self::SHEET_TITLE_MAXIMUM_LENGTH) {throw new Exception('Maximum ' . self::SHEET_TITLE_MAXIMUM_LENGTH . ' characters allowed in sheet code name.'); }< return $pValue;> return $sheetCodeName;} /** * Check sheet title for valid Excel syntax. *< * @param string $pValue The string to check < * < * @throws Exception> * @param string $sheetTitle The string to check* * @return string The valid string */< private static function checkSheetTitle($pValue)> private static function checkSheetTitle($sheetTitle){ // Some of the printable ASCII characters are invalid: * : / \ ? [ ]< if (str_replace(self::$invalidCharacters, '', $pValue) !== $pValue) {> if (str_replace(self::$invalidCharacters, '', $sheetTitle) !== $sheetTitle) {throw new Exception('Invalid character found in sheet title'); } // Enforce maximum characters allowed for sheet title< if (Shared\StringHelper::countCharacters($pValue) > self::SHEET_TITLE_MAXIMUM_LENGTH) {> if (Shared\StringHelper::countCharacters($sheetTitle) > self::SHEET_TITLE_MAXIMUM_LENGTH) {throw new Exception('Maximum ' . self::SHEET_TITLE_MAXIMUM_LENGTH . ' characters allowed in sheet title.'); }< return $pValue;> return $sheetTitle;} /** * Get a sorted list of all cell coordinates currently held in the collection by row and column. * * @param bool $sorted Also sort the cell collection? * * @return string[] */ public function getCoordinates($sorted = true) { if ($this->cellCollection == null) { return []; } if ($sorted) { return $this->cellCollection->getSortedCoordinates(); } return $this->cellCollection->getCoordinates(); } /** * Get collection of row dimensions. * * @return RowDimension[] */ public function getRowDimensions() { return $this->rowDimensions; } /** * Get default row dimension. * * @return RowDimension */ public function getDefaultRowDimension() { return $this->defaultRowDimension; } /** * Get collection of column dimensions. * * @return ColumnDimension[] */ public function getColumnDimensions() { return $this->columnDimensions; } /** * Get default column dimension. * * @return ColumnDimension */ public function getDefaultColumnDimension() { return $this->defaultColumnDimension; } /** * Get collection of drawings. *< * @return BaseDrawing[]> * @return ArrayObject<int, BaseDrawing>*/ public function getDrawingCollection() { return $this->drawingCollection; } /** * Get collection of charts. *< * @return Chart[]> * @return ArrayObject<int, Chart>*/ public function getChartCollection() { return $this->chartCollection; } /** * Add chart. *< * @param Chart $pChart < * @param null|int $iChartIndex Index where chart should go (0,1,..., or null for last)> * @param null|int $chartIndex Index where chart should go (0,1,..., or null for last)* * @return Chart */< public function addChart(Chart $pChart, $iChartIndex = null)> public function addChart(Chart $chart, $chartIndex = null){< $pChart->setWorksheet($this); < if ($iChartIndex === null) { < $this->chartCollection[] = $pChart;> $chart->setWorksheet($this); > if ($chartIndex === null) { > $this->chartCollection[] = $chart;} else { // Insert the chart at the requested index< array_splice($this->chartCollection, $iChartIndex, 0, [$pChart]);> // @phpstan-ignore-next-line > array_splice(/** @scrutinizer ignore-type */ $this->chartCollection, $chartIndex, 0, [$chart]);}< return $pChart;> return $chart;} /** * Return the count of charts on this worksheet. * * @return int The number of charts */ public function getChartCount() { return count($this->chartCollection); } /** * Get a chart by its index position. *< * @param string $index Chart index position> * @param ?string $index Chart index position* * @return Chart|false */ public function getChartByIndex($index) { $chartCount = count($this->chartCollection); if ($chartCount == 0) { return false; } if ($index === null) { $index = --$chartCount; } if (!isset($this->chartCollection[$index])) { return false; } return $this->chartCollection[$index]; } /** * Return an array of the names of charts on this worksheet. * * @return string[] The names of charts */ public function getChartNames() { $chartNames = []; foreach ($this->chartCollection as $chart) { $chartNames[] = $chart->getName(); } return $chartNames; } /** * Get a chart by name. * * @param string $chartName Chart name * * @return Chart|false */ public function getChartByName($chartName) {< $chartCount = count($this->chartCollection); < if ($chartCount == 0) { < return false; < }foreach ($this->chartCollection as $index => $chart) { if ($chart->getName() == $chartName) {< return $this->chartCollection[$index];> return $chart;} } return false; } /** * Refresh column dimensions. *< * @return Worksheet> * @return $this*/ public function refreshColumnDimensions() {< $currentColumnDimensions = $this->getColumnDimensions();$newColumnDimensions = [];< < foreach ($currentColumnDimensions as $objColumnDimension) {> foreach ($this->getColumnDimensions() as $objColumnDimension) {$newColumnDimensions[$objColumnDimension->getColumnIndex()] = $objColumnDimension; } $this->columnDimensions = $newColumnDimensions; return $this; } /** * Refresh row dimensions. *< * @return Worksheet> * @return $this*/ public function refreshRowDimensions() {< $currentRowDimensions = $this->getRowDimensions();$newRowDimensions = [];< < foreach ($currentRowDimensions as $objRowDimension) {> foreach ($this->getRowDimensions() as $objRowDimension) {$newRowDimensions[$objRowDimension->getRowIndex()] = $objRowDimension; } $this->rowDimensions = $newRowDimensions; return $this; } /** * Calculate worksheet dimension. * * @return string String containing the dimension of this worksheet */ public function calculateWorksheetDimension() { // Return return 'A1:' . $this->getHighestColumn() . $this->getHighestRow(); } /** * Calculate worksheet data dimension. * * @return string String containing the dimension of this worksheet that actually contain data */ public function calculateWorksheetDataDimension() { // Return return 'A1:' . $this->getHighestDataColumn() . $this->getHighestDataRow(); } /** * Calculate widths for auto-size columns. *< * @return Worksheet;> * @return $this*/ public function calculateColumnWidths() { // initialize $autoSizes array $autoSizes = []; foreach ($this->getColumnDimensions() as $colDimension) { if ($colDimension->getAutoSize()) { $autoSizes[$colDimension->getColumnIndex()] = -1; } } // There is only something to do if there are some auto-size columns if (!empty($autoSizes)) { // build list of cells references that participate in a merge $isMergeCell = []; foreach ($this->getMergeCells() as $cells) { foreach (Coordinate::extractAllCellReferencesInRange($cells) as $cellReference) { $isMergeCell[$cellReference] = true; } }> $autoFilterIndentRanges = (new AutoFit($this))->getAutoFilterIndentRanges(); // loop through all cells in the worksheet >foreach ($this->getCoordinates(false) as $coordinate) {< $cell = $this->getCell($coordinate, false);> $cell = $this->getCellOrNull($coordinate); >if ($cell !== null && isset($autoSizes[$this->cellCollection->getCurrentColumn()])) { //Determine if cell is in merge range $isMerged = isset($isMergeCell[$this->cellCollection->getCurrentCoordinate()]); //By default merged cells should be ignored $isMergedButProceed = false;< //The only exception is if it's a merge range value cell of a 'vertical' randge (1 column wide)> //The only exception is if it's a merge range value cell of a 'vertical' range (1 column wide)if ($isMerged && $cell->isMergeRangeValueCell()) {< $range = $cell->getMergeRange();> $range = (string) $cell->getMergeRange();$rangeBoundaries = Coordinate::rangeDimension($range);< if ($rangeBoundaries[0] == 1) {> if ($rangeBoundaries[0] === 1) {$isMergedButProceed = true; } }< // Determine width if cell does not participate in a merge or does and is a value cell of 1-column wide range> // Determine width if cell is not part of a merge or does and is a value cell of 1-column wide rangeif (!$isMerged || $isMergedButProceed) {> // Determine if we need to make an adjustment for the first row in an AutoFilter range that // Calculated value > // has a column filter dropdown // To formatted string > $filterAdjustment = false; $cellValue = NumberFormat::toFormattedString( > if (!empty($autoFilterIndentRanges)) { $cell->getCalculatedValue(), > foreach ($autoFilterIndentRanges as $autoFilterFirstRowRange) { $this->getParent()->getCellXfByIndex($cell->getXfIndex())->getNumberFormat()->getFormatCode() > if ($cell->isInRange($autoFilterFirstRowRange)) { ); > $filterAdjustment = true; > $autoSizes[$this->cellCollection->getCurrentColumn()] = max( > break; (float) $autoSizes[$this->cellCollection->getCurrentColumn()], > } (float) Shared\Font::calculateColumnWidth( > } $this->getParent()->getCellXfByIndex($cell->getXfIndex())->getFont(), > } $cellValue, > $this->getParent()->getCellXfByIndex($cell->getXfIndex())->getAlignment()->getTextRotation(), > $indentAdjustment = $cell->getStyle()->getAlignment()->getIndent(); $this->getParent()->getDefaultStyle()->getFont() > $indentAdjustment += (int) ($cell->getStyle()->getAlignment()->getHorizontal() === Alignment::HORIZONTAL_CENTER); ) >< $this->getParent()->getCellXfByIndex($cell->getXfIndex())->getNumberFormat()->getFormatCode()> (string) $this->getParentOrThrow()->getCellXfByIndex($cell->getXfIndex()) > ->getNumberFormat()->getFormatCode()}> if ($cellValue !== null && $cellValue !== '') {< (float) $autoSizes[$this->cellCollection->getCurrentColumn()], < (float) Shared\Font::calculateColumnWidth( < $this->getParent()->getCellXfByIndex($cell->getXfIndex())->getFont(),> $autoSizes[$this->cellCollection->getCurrentColumn()], > round( > Shared\Font::calculateColumnWidth( > $this->getParentOrThrow()->getCellXfByIndex($cell->getXfIndex())->getFont(),< $this->getParent()->getCellXfByIndex($cell->getXfIndex())->getAlignment()->getTextRotation(), < $this->getParent()->getDefaultStyle()->getFont()> (int) $this->getParentOrThrow()->getCellXfByIndex($cell->getXfIndex()) > ->getAlignment()->getTextRotation(), > $this->getParentOrThrow()->getDefaultStyle()->getFont(), > $filterAdjustment, > $indentAdjustment > ), > 3if ($width == -1) {> }$width = $this->getDefaultColumnDimension()->getWidth(); } $this->getColumnDimension($columnIndex)->setWidth($width); } } return $this; } /**< * Get parent. < * < * @return Spreadsheet> * Get parent or null.*/< public function getParent()> public function getParent(): ?Spreadsheet{ return $this->parent; } /**> * Get parent, throw exception if null. * Re-bind parent. > */ * > public function getParentOrThrow(): Spreadsheet * @param Spreadsheet $parent > { * > if ($this->parent !== null) { * @return Worksheet > return $this->parent; */ > } public function rebindParent(Spreadsheet $parent) > { > throw new Exception('Sheet does not have a parent.'); if ($this->parent !== null) { > } $namedRanges = $this->parent->getNamedRanges(); > foreach ($namedRanges as $namedRange) { > /**< * @param Spreadsheet $parent < * < * @return Worksheet> * @return $this< $namedRanges = $this->parent->getNamedRanges(); < foreach ($namedRanges as $namedRange) { < $parent->addNamedRange($namedRange);> $definedNames = $this->parent->getDefinedNames(); > foreach ($definedNames as $definedName) { > $parent->addDefinedName($definedName);} $this->parent = $parent; return $this; } /** * Get title. * * @return string */ public function getTitle() { return $this->title; } /** * Set title. *< * @param string $pValue String containing the dimension of this worksheet> * @param string $title String containing the dimension of this worksheet* @param 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 * @param 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 Worksheet> * @return $this*/< public function setTitle($pValue, $updateFormulaCellReferences = true, $validate = true)> public function setTitle($title, $updateFormulaCellReferences = true, $validate = true){ // Is this a 'rename' or not?< if ($this->getTitle() == $pValue) {> if ($this->getTitle() == $title) {return $this; } // Old title $oldTitle = $this->getTitle(); if ($validate) { // Syntax check< self::checkSheetTitle($pValue);> self::checkSheetTitle($title);if ($this->parent) { // Is there already such sheet name?< if ($this->parent->sheetNameExists($pValue)) {> if ($this->parent->sheetNameExists($title)) {// Use name, but append with lowest possible integer< if (Shared\StringHelper::countCharacters($pValue) > 29) { < $pValue = Shared\StringHelper::substring($pValue, 0, 29);> if (Shared\StringHelper::countCharacters($title) > 29) { > $title = Shared\StringHelper::substring($title, 0, 29);} $i = 1;< while ($this->parent->sheetNameExists($pValue . ' ' . $i)) {> while ($this->parent->sheetNameExists($title . ' ' . $i)) {++$i; if ($i == 10) {< if (Shared\StringHelper::countCharacters($pValue) > 28) { < $pValue = Shared\StringHelper::substring($pValue, 0, 28);> if (Shared\StringHelper::countCharacters($title) > 28) { > $title = Shared\StringHelper::substring($title, 0, 28);} } elseif ($i == 100) {< if (Shared\StringHelper::countCharacters($pValue) > 27) { < $pValue = Shared\StringHelper::substring($pValue, 0, 27);> if (Shared\StringHelper::countCharacters($title) > 27) { > $title = Shared\StringHelper::substring($title, 0, 27);} } }< $pValue .= " $i";> $title .= " $i";} } } // Set title< $this->title = $pValue;> $this->title = $title;$this->dirty = true; if ($this->parent && $this->parent->getCalculationEngine()) { // New title $newTitle = $this->getTitle(); $this->parent->getCalculationEngine() ->renameCalculationCacheForWorksheet($oldTitle, $newTitle); if ($updateFormulaCellReferences) {< ReferenceHelper::getInstance()->updateNamedFormulas($this->parent, $oldTitle, $newTitle);> ReferenceHelper::getInstance()->updateNamedFormulae($this->parent, $oldTitle, $newTitle);} } return $this; } /** * Get sheet state. * * @return string Sheet state (visible, hidden, veryHidden) */ public function getSheetState() { return $this->sheetState; } /** * Set sheet state. * * @param string $value Sheet state (visible, hidden, veryHidden) *< * @return Worksheet> * @return $this*/ public function setSheetState($value) { $this->sheetState = $value; return $this; } /** * Get page setup. * * @return PageSetup */ public function getPageSetup() { return $this->pageSetup; } /** * Set page setup. *< * @param PageSetup $pValue < * < * @return Worksheet> * @return $this*/< public function setPageSetup(PageSetup $pValue)> public function setPageSetup(PageSetup $pageSetup){< $this->pageSetup = $pValue;> $this->pageSetup = $pageSetup;return $this; } /** * Get page margins. * * @return PageMargins */ public function getPageMargins() { return $this->pageMargins; } /** * Set page margins. *< * @param PageMargins $pValue < * < * @return Worksheet> * @return $this*/< public function setPageMargins(PageMargins $pValue)> public function setPageMargins(PageMargins $pageMargins){< $this->pageMargins = $pValue;> $this->pageMargins = $pageMargins;return $this; } /** * Get page header/footer. * * @return HeaderFooter */ public function getHeaderFooter() { return $this->headerFooter; } /** * Set page header/footer. *< * @param HeaderFooter $pValue < * < * @return Worksheet> * @return $this*/< public function setHeaderFooter(HeaderFooter $pValue)> public function setHeaderFooter(HeaderFooter $headerFooter){< $this->headerFooter = $pValue;> $this->headerFooter = $headerFooter;return $this; } /** * Get sheet view. * * @return SheetView */ public function getSheetView() { return $this->sheetView; } /** * Set sheet view. *< * @param SheetView $pValue < * < * @return Worksheet> * @return $this*/< public function setSheetView(SheetView $pValue)> public function setSheetView(SheetView $sheetView){< $this->sheetView = $pValue;> $this->sheetView = $sheetView;return $this; } /** * Get Protection. * * @return Protection */ public function getProtection() { return $this->protection; } /** * Set Protection. *< * @param Protection $pValue < * < * @return Worksheet> * @return $this*/< public function setProtection(Protection $pValue)> public function setProtection(Protection $protection){< $this->protection = $pValue;> $this->protection = $protection;$this->dirty = true; return $this; } /** * Get highest worksheet column. *< * @param string $row Return the data highest column for the specified row,> * @param null|int|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 string Highest column name */ public function getHighestColumn($row = null) {< if ($row == null) { < return $this->cachedHighestColumn;> if ($row === null) { > return Coordinate::stringFromColumnIndex($this->cachedHighestColumn);} return $this->getHighestDataColumn($row); } /** * Get highest worksheet column that contains data. *< * @param string $row Return the highest data column for the specified row,> * @param null|int|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 string Highest column name that contains data */ public function getHighestDataColumn($row = null) { return $this->cellCollection->getHighestColumn($row); } /** * Get highest worksheet row. *< * @param string $column Return the highest data row for the specified column,> * @param null|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 int Highest row number */ public function getHighestRow($column = null) {< if ($column == null) {> if ($column === null) {return $this->cachedHighestRow; } return $this->getHighestDataRow($column); } /** * Get highest worksheet row that contains data. *< * @param string $column Return the highest data row for the specified column,> * @param null|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 int Highest row number that contains data */ public function getHighestDataRow($column = null) { return $this->cellCollection->getHighestRow($column); } /** * Get highest worksheet column and highest row that have cell records. * * @return array Highest column name and highest row number */ public function getHighestRowAndColumn() { return $this->cellCollection->getHighestRowAndColumn(); } /** * Set a cell value. *< * @param string $pCoordinate Coordinate of the cell, eg: 'A1' < * @param mixed $pValue Value of the cell> * @param array<int>|CellAddress|string $coordinate Coordinate of the cell as a string, eg: 'C5'; > * or as an array of [$columnIndex, $row] (e.g. [3, 5]), or a CellAddress object. > * @param mixed $value Value for the cell > * @param null|IValueBinder $binder Value Binder to override the currently set Value Binder*< * @return Worksheet> * @return $this*/< public function setCellValue($pCoordinate, $pValue)> public function setCellValue($coordinate, $value, ?IValueBinder $binder = null){< $this->getCell($pCoordinate)->setValue($pValue);> $cellAddress = Functions::trimSheetFromCellReference(Validations::validateCellAddress($coordinate)); > $this->getCell($cellAddress)->setValue($value, $binder);return $this; } /** * Set a cell value by using numeric cell coordinates. *> * @deprecated 1.23.0 * @param int $columnIndex Numeric column coordinate of the cell > * Use the setCellValue() method with a cell address such as 'C5' instead;, * @param int $row Numeric row coordinate of the cell > * or passing in an array of [$columnIndex, $row] (e.g. [3, 5]), or a CellAddress object. * @param mixed $value Value of the cell > * @see Worksheet::setCellValue() * > ** @return Worksheet> * @param null|IValueBinder $binder Value Binder to override the currently set Value Binder< * @return Worksheet> * @return $this< public function setCellValueByColumnAndRow($columnIndex, $row, $value)> public function setCellValueByColumnAndRow($columnIndex, $row, $value, ?IValueBinder $binder = null){< $this->getCellByColumnAndRow($columnIndex, $row)->setValue($value);> $this->getCell(Coordinate::stringFromColumnIndex($columnIndex) . $row)->setValue($value, $binder);return $this; } /** * Set a cell value. *< * @param string $pCoordinate Coordinate of the cell, eg: 'A1' < * @param mixed $pValue Value of the cell < * @param string $pDataType Explicit data type, see DataType::TYPE_*> * @param array<int>|CellAddress|string $coordinate Coordinate of the cell as a string, eg: 'C5'; > * or as an array of [$columnIndex, $row] (e.g. [3, 5]), or a CellAddress object. > * @param mixed $value Value of the cell > * @param string $dataType 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. > * > * @see DataType*< * @return Worksheet> * @return $this*/< public function setCellValueExplicit($pCoordinate, $pValue, $pDataType)> public function setCellValueExplicit($coordinate, $value, $dataType){< // Set value < $this->getCell($pCoordinate)->setValueExplicit($pValue, $pDataType);> $cellAddress = Functions::trimSheetFromCellReference(Validations::validateCellAddress($coordinate)); > $this->getCell($cellAddress)->setValueExplicit($value, $dataType);return $this; } /** * Set a cell value by using numeric cell coordinates. *> * @deprecated 1.23.0 * @param int $columnIndex Numeric column coordinate of the cell > * Use the setCellValueExplicit() method with a cell address such as 'C5' instead;, * @param int $row Numeric row coordinate of the cell > * or passing in an array of [$columnIndex, $row] (e.g. [3, 5]), or a CellAddress object. * @param mixed $value Value of the cell > * @see Worksheet::setCellValueExplicit() * @param string $dataType Explicit data type, see DataType::TYPE_* > **> * Note that PhpSpreadsheet does not validate that the value and datatype are consistent, in using this * @return Worksheet > * method, then it is your responsibility as an end-user developer to validate that the value and */ > * the datatype match. public function setCellValueExplicitByColumnAndRow($columnIndex, $row, $value, $dataType) > * If you do mismatch value and datatpe, then the value you enter may be changed to match the datatype { > * that you specify.< * @return Worksheet> * @see DataType > * > * @return $this< $this->getCellByColumnAndRow($columnIndex, $row)->setValueExplicit($value, $dataType);> $this->getCell(Coordinate::stringFromColumnIndex($columnIndex) . $row)->setValueExplicit($value, $dataType);return $this; } /** * Get cell at a specific coordinate. *< * @param string $pCoordinate Coordinate of the cell, eg: 'A1' < * @param bool $createIfNotExists Flag indicating whether a new cell should be created if it doesn't < * already exist, or a null should be returned instead> * @param array<int>|CellAddress|string $coordinate Coordinate of the cell as a string, eg: 'C5'; > * or as an array of [$columnIndex, $row] (e.g. [3, 5]), or a CellAddress object.*< * @throws Exception> * @return 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. > */ > public function getCell($coordinate): Cell > { > $cellAddress = Functions::trimSheetFromCellReference(Validations::validateCellAddress($coordinate)); > > // Shortcut for increased performance for the vast majority of simple cases > if ($this->cellCollection->has($cellAddress)) { > /** @var Cell $cell */ > $cell = $this->cellCollection->get($cellAddress); > > return $cell; > } > > /** @var Worksheet $sheet */ > [$sheet, $finalCoordinate] = $this->getWorksheetAndCoordinate($cellAddress); > $cell = $sheet->cellCollection->get($finalCoordinate); > > return $cell ?? $sheet->createNewCell($finalCoordinate); > } > > /** > * Get the correct Worksheet and coordinate from a coordinate that may > * contains reference to another sheet or a named range.*< * @return null|Cell Cell that was found/created or null> * @return array{0: Worksheet, 1: string}*/< public function getCell($pCoordinate, $createIfNotExists = true)> private function getWorksheetAndCoordinate(string $coordinate): array{< // Uppercase coordinate < $pCoordinateUpper = strtoupper($pCoordinate); < < // Check cell collection < if ($this->cellCollection->has($pCoordinateUpper)) { < return $this->cellCollection->get($pCoordinateUpper); < }> $sheet = null; > $finalCoordinate = null;// Worksheet reference?< if (strpos($pCoordinate, '!') !== false) { < $worksheetReference = self::extractSheetTitle($pCoordinate, true);> if (strpos($coordinate, '!') !== false) { > $worksheetReference = self::extractSheetTitle($coordinate, true);< return $this->parent->getSheetByName($worksheetReference[0])->getCell(strtoupper($worksheetReference[1]), $createIfNotExists); < }> $sheet = $this->getParentOrThrow()->getSheetByName($worksheetReference[0]); > $finalCoordinate = strtoupper($worksheetReference[1]);> if ($sheet === null) { // Named range? > throw new Exception('Sheet not found for name: ' . $worksheetReference[0]); if ((!preg_match('/^' . Calculation::CALCULATION_REGEXP_CELLREF . '$/i', $pCoordinate, $matches)) && > } (preg_match('/^' . Calculation::CALCULATION_REGEXP_NAMEDRANGE . '$/i', $pCoordinate, $matches))) { > } elseif ( $namedRange = NamedRange::resolveRange($pCoordinate, $this); > !preg_match('/^' . Calculation::CALCULATION_REGEXP_CELLREF . '$/i', $coordinate) && if ($namedRange !== null) { > preg_match('/^' . Calculation::CALCULATION_REGEXP_DEFINEDNAME . '$/iu', $coordinate) $pCoordinate = $namedRange->getRange(); > ) {< if ((!preg_match('/^' . Calculation::CALCULATION_REGEXP_CELLREF . '$/i', $pCoordinate, $matches)) && < (preg_match('/^' . Calculation::CALCULATION_REGEXP_NAMEDRANGE . '$/i', $pCoordinate, $matches))) { < $namedRange = NamedRange::resolveRange($pCoordinate, $this);> $namedRange = $this->validateNamedRange($coordinate, true);< $pCoordinate = $namedRange->getRange();> $sheet = $namedRange->getWorksheet(); > if ($sheet === null) { > throw new Exception('Sheet not found for named range: ' . $namedRange->getName()); > }< return $namedRange->getWorksheet()->getCell($pCoordinate, $createIfNotExists);> /** @phpstan-ignore-next-line */ > $cellCoordinate = ltrim(substr($namedRange->getValue(), strrpos($namedRange->getValue(), '!')), '!'); > $finalCoordinate = str_replace('$', '', $cellCoordinate); > }if (Coordinate::coordinateIsRange($pCoordinate)) {> throw new Exception('Cell coordinate can not be a range of cells.'); > if ($sheet === null || $finalCoordinate === null) { } elseif (strpos($pCoordinate, '$') !== false) { > $sheet = $this; throw new Exception('Cell coordinate must not be absolute.'); > $finalCoordinate = strtoupper($coordinate);< if (Coordinate::coordinateIsRange($pCoordinate)) { < throw new Exception('Cell coordinate can not be a range of cells.'); < } elseif (strpos($pCoordinate, '$') !== false) {> if (Coordinate::coordinateIsRange($finalCoordinate)) { > throw new Exception('Cell coordinate string can not be a range of cells.'); > } elseif (strpos($finalCoordinate, '$') !== false) {< // Create new cell object, if required < return $createIfNotExists ? $this->createNewCell($pCoordinateUpper) : null;> return [$sheet, $finalCoordinate];/**< * Get cell at a specific coordinate by using numeric cell coordinates.> * Get an existing cell at a specific coordinate, or null.*< * @param int $columnIndex Numeric column coordinate of the cell < * @param int $row Numeric row coordinate of the cell < * @param bool $createIfNotExists Flag indicating whether a new cell should be created if it doesn't < * already exist, or a null should be returned instead> * @param string $coordinate Coordinate of the cell, eg: 'A1'*< * @return null|Cell Cell that was found/created or null> * @return null|Cell Cell that was found or null*/< public function getCellByColumnAndRow($columnIndex, $row, $createIfNotExists = true)> private function getCellOrNull($coordinate): ?Cell{< $columnLetter = Coordinate::stringFromColumnIndex($columnIndex); < $coordinate = $columnLetter . $row; <> // Check cell collectionif ($this->cellCollection->has($coordinate)) { return $this->cellCollection->get($coordinate); }< // Create new cell object, if required < return $createIfNotExists ? $this->createNewCell($coordinate) : null;> return null; > } > > /** > * Get cell at a specific coordinate by using numeric cell coordinates. > * > * @deprecated 1.23.0 > * Use the getCell() method with a cell address such as 'C5' instead;, > * or passing in an array of [$columnIndex, $row] (e.g. [3, 5]), or a CellAddress object. > * @see Worksheet::getCell() > * > * @param int $columnIndex Numeric column coordinate of the cell > * @param int $row Numeric row coordinate of the cell > * > * @return 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. > */ > public function getCellByColumnAndRow($columnIndex, $row): Cell > { > return $this->getCell(Coordinate::stringFromColumnIndex($columnIndex) . $row);} /** * Create a new cell at the specified coordinate. *< * @param string $pCoordinate Coordinate of the cell> * @param string $coordinate Coordinate of the cell* * @return 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 private function createNewCell($pCoordinate) > * 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 $cell = new Cell(null, DataType::TYPE_NULL, $this); > * the active cell has changed.< private function createNewCell($pCoordinate)> public function createNewCell($coordinate): Cell$this->cellCollectionIsSorted = false;> [$column, $row, $columnString] = Coordinate::indexesFromString($coordinate);< $this->cellCollection->add($pCoordinate, $cell); < $this->cellCollectionIsSorted = false;> $this->cellCollection->add($coordinate, $cell);< $aCoordinates = Coordinate::coordinateFromString($pCoordinate); < if (Coordinate::columnIndexFromString($this->cachedHighestColumn) < Coordinate::columnIndexFromString($aCoordinates[0])) { < $this->cachedHighestColumn = $aCoordinates[0];> if ($column > $this->cachedHighestColumn) { > $this->cachedHighestColumn = $column;}< if ($aCoordinates[1] > $this->cachedHighestRow) { < $this->cachedHighestRow = $aCoordinates[1];> if ($row > $this->cachedHighestRow) { > $this->cachedHighestRow = $row;} // Cell needs appropriate xfIndex from dimensions records // but don't create dimension records if they don't already exist< $rowDimension = $this->getRowDimension($aCoordinates[1], false); < $columnDimension = $this->getColumnDimension($aCoordinates[0], false);> $rowDimension = $this->rowDimensions[$row] ?? null; > $columnDimension = $this->columnDimensions[$columnString] ?? null;< if ($rowDimension !== null && $rowDimension->getXfIndex() > 0) {> if ($rowDimension !== null) { > $rowXf = (int) $rowDimension->getXfIndex(); > if ($rowXf > 0) {// then there is a row dimension with explicit style, assign it to the cell< $cell->setXfIndex($rowDimension->getXfIndex()); < } elseif ($columnDimension !== null && $columnDimension->getXfIndex() > 0) {> $cell->setXfIndex($rowXf); > } > } elseif ($columnDimension !== null) { > $colXf = (int) $columnDimension->getXfIndex(); > if ($colXf > 0) {// then there is a column dimension, assign it to the cell< $cell->setXfIndex($columnDimension->getXfIndex());> $cell->setXfIndex($colXf); > }} return $cell; } /** * Does the cell at a specific coordinate exist? *< * @param string $pCoordinate Coordinate of the cell eg: 'A1' < * < * @throws Exception < * < * @return bool> * @param array<int>|CellAddress|string $coordinate Coordinate of the cell as a string, eg: 'C5'; > * or as an array of [$columnIndex, $row] (e.g. [3, 5]), or a CellAddress object.*/< public function cellExists($pCoordinate)> public function cellExists($coordinate): bool{< // Worksheet reference? < if (strpos($pCoordinate, '!') !== false) { < $worksheetReference = self::extractSheetTitle($pCoordinate, true); < < return $this->parent->getSheetByName($worksheetReference[0])->cellExists(strtoupper($worksheetReference[1])); < } < < // Named range? < if ((!preg_match('/^' . Calculation::CALCULATION_REGEXP_CELLREF . '$/i', $pCoordinate, $matches)) && < (preg_match('/^' . Calculation::CALCULATION_REGEXP_NAMEDRANGE . '$/i', $pCoordinate, $matches))) { < $namedRange = NamedRange::resolveRange($pCoordinate, $this); < if ($namedRange !== null) { < $pCoordinate = $namedRange->getRange(); < if ($this->getHashCode() != $namedRange->getWorksheet()->getHashCode()) { < if (!$namedRange->getLocalOnly()) { < return $namedRange->getWorksheet()->cellExists($pCoordinate); < } < < throw new Exception('Named range ' . $namedRange->getName() . ' is not accessible from within sheet ' . $this->getTitle()); < } < } else { < return false; < } < } < < // Uppercase coordinate < $pCoordinate = strtoupper($pCoordinate); < < if (Coordinate::coordinateIsRange($pCoordinate)) { < throw new Exception('Cell coordinate can not be a range of cells.'); < } elseif (strpos($pCoordinate, '$') !== false) { < throw new Exception('Cell coordinate must not be absolute.'); < }> $cellAddress = Validations::validateCellAddress($coordinate); > /** @var Worksheet $sheet */ > [$sheet, $finalCoordinate] = $this->getWorksheetAndCoordinate($cellAddress);< // Cell exists? < return $this->cellCollection->has($pCoordinate);> return $sheet->cellCollection->has($finalCoordinate);} /** * Cell at a specific coordinate by using numeric cell coordinates exists? *> * @deprecated 1.23.0 * @param int $columnIndex Numeric column coordinate of the cell > * Use the cellExists() method with a cell address such as 'C5' instead;, * @param int $row Numeric row coordinate of the cell > * or passing in an array of [$columnIndex, $row] (e.g. [3, 5]), or a CellAddress object. * > * @see Worksheet::cellExists() * @return bool > *< * < * @return bool< public function cellExistsByColumnAndRow($columnIndex, $row)> public function cellExistsByColumnAndRow($columnIndex, $row): boolreturn $this->cellExists(Coordinate::stringFromColumnIndex($columnIndex) . $row); } /** * Get row dimension at a specific row. *< * @param int $pRow Numeric index of the row < * @param bool $create < * < * @return RowDimension> * @param int $row Numeric index of the row*/< public function getRowDimension($pRow, $create = true)> public function getRowDimension(int $row): RowDimension{< // Found < $found = null; <// Get row dimension< if (!isset($this->rowDimensions[$pRow])) { < if (!$create) { < return null;> if (!isset($this->rowDimensions[$row])) { > $this->rowDimensions[$row] = new RowDimension($row); > > $this->cachedHighestRow = max($this->cachedHighestRow, $row);}< $this->rowDimensions[$pRow] = new RowDimension($pRow);< $this->cachedHighestRow = max($this->cachedHighestRow, $pRow);> return $this->rowDimensions[$row];}< return $this->rowDimensions[$pRow];> public function rowDimensionExists(int $row): bool > { > return isset($this->rowDimensions[$row]);} /** * Get column dimension at a specific column. *< * @param string $pColumn String index of the column eg: 'A' < * @param bool $create < * < * @return ColumnDimension> * @param string $column String index of the column eg: 'A'*/< public function getColumnDimension($pColumn, $create = true)> public function getColumnDimension(string $column): ColumnDimension{ // Uppercase coordinate< $pColumn = strtoupper($pColumn);> $column = strtoupper($column);// Fetch dimensions< if (!isset($this->columnDimensions[$pColumn])) { < if (!$create) { < return null; < } < $this->columnDimensions[$pColumn] = new ColumnDimension($pColumn);> if (!isset($this->columnDimensions[$column])) { > $this->columnDimensions[$column] = new ColumnDimension($column);< if (Coordinate::columnIndexFromString($this->cachedHighestColumn) < Coordinate::columnIndexFromString($pColumn)) { < $this->cachedHighestColumn = $pColumn;> $columnIndex = Coordinate::columnIndexFromString($column); > if ($this->cachedHighestColumn < $columnIndex) { > $this->cachedHighestColumn = $columnIndex;} }< return $this->columnDimensions[$pColumn];> return $this->columnDimensions[$column];} /** * Get column dimension at a specific column by using numeric cell coordinates. * * @param int $columnIndex Numeric column coordinate of the cell< * < * @return ColumnDimension*/< public function getColumnDimensionByColumn($columnIndex)> public function getColumnDimensionByColumn(int $columnIndex): ColumnDimension{ return $this->getColumnDimension(Coordinate::stringFromColumnIndex($columnIndex)); } /** * Get styles. * * @return Style[] */ public function getStyles() { return $this->styles; } /** * Get style for cell. *< * @param string $pCellCoordinate Cell coordinate (or range) to get style for, eg: 'A1'> * @param AddressRange|array<int>|CellAddress|int|string $cellCoordinate > * 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. > */ > public function getStyle($cellCoordinate): Style > { > $cellCoordinate = Validations::validateCellOrCellRange($cellCoordinate); > > // set this sheet as active > $this->getParentOrThrow()->setActiveSheetIndex($this->getParentOrThrow()->getIndex($this)); > > // set cell coordinate as active > $this->setSelectedCells($cellCoordinate); > > return $this->getParentOrThrow()->getCellXfSupervisor(); > } > > /** > * Get style for cell by using numeric cell coordinates. > * > * @deprecated 1.23.0 > * Use the getStyle() method with a cell address range such as 'C5:F8' instead;, > * or passing in an array of [$fromColumnIndex, $fromRow, $toColumnIndex, $toRow] (e.g. [3, 5, 6, 8]), > * or an AddressRange object. > * @see Worksheet::getStyle()*< * @throws Exception> * @param int $columnIndex1 Numeric column coordinate of the cell > * @param int $row1 Numeric row coordinate of the cell > * @param null|int $columnIndex2 Numeric column coordinate of the range cell > * @param null|int $row2 Numeric row coordinate of the range cell* * @return Style */< public function getStyle($pCellCoordinate)> public function getStyleByColumnAndRow($columnIndex1, $row1, $columnIndex2 = null, $row2 = null){< // set this sheet as active < $this->parent->setActiveSheetIndex($this->parent->getIndex($this));> if ($columnIndex2 !== null && $row2 !== null) { > $cellRange = new CellRange( > CellAddress::fromColumnAndRow($columnIndex1, $row1), > CellAddress::fromColumnAndRow($columnIndex2, $row2) > );< // set cell coordinate as active < $this->setSelectedCells($pCellCoordinate);> return $this->getStyle($cellRange); > }< return $this->parent->getCellXfSupervisor();> return $this->getStyle(CellAddress::fromColumnAndRow($columnIndex1, $row1));} /** * Get conditional styles for a cell. *< * @param string $pCoordinate eg: 'A1'> * @param string $coordinate 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 Conditional[] */< public function getConditionalStyles($pCoordinate)> public function getConditionalStyles(string $coordinate): array > { > $coordinate = strtoupper($coordinate); > if (strpos($coordinate, ':') !== false) { > return $this->conditionalStylesCollection[$coordinate] ?? []; > } > > $cell = $this->getCell($coordinate); > foreach (array_keys($this->conditionalStylesCollection) as $conditionalRange) { > if ($cell->isInRange($conditionalRange)) { > return $this->conditionalStylesCollection[$conditionalRange]; > } > } > > return []; > } > > public function getConditionalRange(string $coordinate): ?string{< $pCoordinate = strtoupper($pCoordinate); < if (!isset($this->conditionalStylesCollection[$pCoordinate])) { < $this->conditionalStylesCollection[$pCoordinate] = [];> $coordinate = strtoupper($coordinate); > $cell = $this->getCell($coordinate); > foreach (array_keys($this->conditionalStylesCollection) as $conditionalRange) { > if ($cell->isInRange($conditionalRange)) { > return $conditionalRange; > }}< return $this->conditionalStylesCollection[$pCoordinate];> return null;} /** * Do conditional styles exist for this cell? *< * @param string $pCoordinate eg: 'A1' < * < * @return bool < */ < public function conditionalStylesExists($pCoordinate) < { < return isset($this->conditionalStylesCollection[strtoupper($pCoordinate)]);> * @param string $coordinate 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. > */ > public function conditionalStylesExists($coordinate): bool > { > $coordinate = strtoupper($coordinate); > if (strpos($coordinate, ':') !== false) { > return isset($this->conditionalStylesCollection[$coordinate]); > } > > $cell = $this->getCell($coordinate); > foreach (array_keys($this->conditionalStylesCollection) as $conditionalRange) { > if ($cell->isInRange($conditionalRange)) { > return true; > } > } > > return false;} /** * Removes conditional styles for a cell. *< * @param string $pCoordinate eg: 'A1'> * @param string $coordinate eg: 'A1'*< * @return Worksheet> * @return $this*/< public function removeConditionalStyles($pCoordinate)> public function removeConditionalStyles($coordinate){< unset($this->conditionalStylesCollection[strtoupper($pCoordinate)]);> unset($this->conditionalStylesCollection[strtoupper($coordinate)]);return $this; } /** * Get collection of conditional styles. * * @return array */ public function getConditionalStylesCollection() { return $this->conditionalStylesCollection; } /** * Set conditional styles. *< * @param string $pCoordinate eg: 'A1' < * @param $pValue Conditional[]> * @param string $coordinate eg: 'A1' > * @param Conditional[] $styles*< * @return Worksheet> * @return $this*/< public function setConditionalStyles($pCoordinate, $pValue)> public function setConditionalStyles($coordinate, $styles){< $this->conditionalStylesCollection[strtoupper($pCoordinate)] = $pValue;> $this->conditionalStylesCollection[strtoupper($coordinate)] = $styles;return $this; } /**< * Get style for cell by using numeric cell coordinates. < * < * @param int $columnIndex1 Numeric column coordinate of the cell < * @param int $row1 Numeric row coordinate of the cell < * @param null|int $columnIndex2 Numeric column coordinate of the range cell < * @param null|int $row2 Numeric row coordinate of the range cell < * < * @return Style < */ < public function getStyleByColumnAndRow($columnIndex1, $row1, $columnIndex2 = null, $row2 = null) < { < if ($columnIndex2 !== null && $row2 !== null) { < $cellRange = Coordinate::stringFromColumnIndex($columnIndex1) . $row1 . ':' . Coordinate::stringFromColumnIndex($columnIndex2) . $row2; < < return $this->getStyle($cellRange); < } < < return $this->getStyle(Coordinate::stringFromColumnIndex($columnIndex1) . $row1); < } < < /*** Duplicate cell style to a range of cells. * * Please note that this will overwrite existing cell styles for cells in range! *< * @param Style $pCellStyle Cell style to duplicate < * @param string $pRange Range of cells (i.e. "A1:B10"), or just one cell (i.e. "A1") < * < * @throws Exception> * @param Style $style Cell style to duplicate > * @param string $range Range of cells (i.e. "A1:B10"), or just one cell (i.e. "A1")*< * @return Worksheet> * @return $this*/< public function duplicateStyle(Style $pCellStyle, $pRange)> public function duplicateStyle(Style $style, $range){ // Add the style to the workbook if necessary< $workbook = $this->parent; < if ($existingStyle = $this->parent->getCellXfByHashCode($pCellStyle->getHashCode())) {> $workbook = $this->getParentOrThrow(); > if ($existingStyle = $workbook->getCellXfByHashCode($style->getHashCode())) {// there is already such cell Xf in our collection $xfIndex = $existingStyle->getIndex(); } else { // we don't have such a cell Xf, need to add< $workbook->addCellXf($pCellStyle); < $xfIndex = $pCellStyle->getIndex();> $workbook->addCellXf($style); > $xfIndex = $style->getIndex();} // Calculate range outer borders< [$rangeStart, $rangeEnd] = Coordinate::rangeBoundaries($pRange . ':' . $pRange);> [$rangeStart, $rangeEnd] = Coordinate::rangeBoundaries($range . ':' . $range);// Make sure we can loop upwards on rows and columns if ($rangeStart[0] > $rangeEnd[0] && $rangeStart[1] > $rangeEnd[1]) { $tmp = $rangeStart; $rangeStart = $rangeEnd; $rangeEnd = $tmp; } // Loop through cells and apply styles for ($col = $rangeStart[0]; $col <= $rangeEnd[0]; ++$col) { for ($row = $rangeStart[1]; $row <= $rangeEnd[1]; ++$row) { $this->getCell(Coordinate::stringFromColumnIndex($col) . $row)->setXfIndex($xfIndex); } } return $this; } /** * Duplicate conditional style to a range of cells. * * Please note that this will overwrite existing cell styles for cells in range! *< * @param Conditional[] $pCellStyle Cell style to duplicate < * @param string $pRange Range of cells (i.e. "A1:B10"), or just one cell (i.e. "A1")> * @param Conditional[] $styles Cell style to duplicate > * @param string $range Range of cells (i.e. "A1:B10"), or just one cell (i.e. "A1")*< * @throws Exception < * < * @return Worksheet> * @return $this*/< public function duplicateConditionalStyle(array $pCellStyle, $pRange = '')> public function duplicateConditionalStyle(array $styles, $range = ''){< foreach ($pCellStyle as $cellStyle) {> foreach ($styles as $cellStyle) {if (!($cellStyle instanceof Conditional)) { throw new Exception('Style is not a conditional style'); } } // Calculate range outer borders< [$rangeStart, $rangeEnd] = Coordinate::rangeBoundaries($pRange . ':' . $pRange);> [$rangeStart, $rangeEnd] = Coordinate::rangeBoundaries($range . ':' . $range);// Make sure we can loop upwards on rows and columns if ($rangeStart[0] > $rangeEnd[0] && $rangeStart[1] > $rangeEnd[1]) { $tmp = $rangeStart; $rangeStart = $rangeEnd; $rangeEnd = $tmp; } // Loop through cells and apply styles for ($col = $rangeStart[0]; $col <= $rangeEnd[0]; ++$col) { for ($row = $rangeStart[1]; $row <= $rangeEnd[1]; ++$row) {< $this->setConditionalStyles(Coordinate::stringFromColumnIndex($col) . $row, $pCellStyle);> $this->setConditionalStyles(Coordinate::stringFromColumnIndex($col) . $row, $styles);} } return $this; } /** * Set break on a cell. *< * @param string $pCoordinate Cell coordinate (e.g. A1) < * @param int $pBreak Break type (type of Worksheet::BREAK_*) < * < * @throws Exception> * @param array<int>|CellAddress|string $coordinate Coordinate of the cell as a string, eg: 'C5'; > * or as an array of [$columnIndex, $row] (e.g. [3, 5]), or a CellAddress object. > * @param int $break Break type (type of Worksheet::BREAK_*)*< * @return Worksheet> * @return $this*/< public function setBreak($pCoordinate, $pBreak)> public function setBreak($coordinate, $break, int $max = -1){< // Uppercase coordinate < $pCoordinate = strtoupper($pCoordinate);> $cellAddress = Functions::trimSheetFromCellReference(Validations::validateCellAddress($coordinate));< if ($pCoordinate != '') { < if ($pBreak == self::BREAK_NONE) { < if (isset($this->breaks[$pCoordinate])) { < unset($this->breaks[$pCoordinate]); < } < } else { < $this->breaks[$pCoordinate] = $pBreak; < } < } else { < throw new Exception('No cell coordinate specified.');> if ($break === self::BREAK_NONE) { > unset($this->rowBreaks[$cellAddress], $this->columnBreaks[$cellAddress]); > } elseif ($break === self::BREAK_ROW) { > $this->rowBreaks[$cellAddress] = new PageBreak($break, $cellAddress, $max); > } elseif ($break === self::BREAK_COLUMN) { > $this->columnBreaks[$cellAddress] = new PageBreak($break, $cellAddress, $max);} return $this; } /** * Set break on a cell by using numeric cell coordinates. *> * @deprecated 1.23.0 * @param int $columnIndex Numeric column coordinate of the cell > * Use the setBreak() method with a cell address such as 'C5' instead;, * @param int $row Numeric row coordinate of the cell > * or passing in an array of [$columnIndex, $row] (e.g. [3, 5]), or a CellAddress object. * @param int $break Break type (type of Worksheet::BREAK_*) > * @see Worksheet::setBreak() * > *< * @return Worksheet> * @return $this*/ public function setBreakByColumnAndRow($columnIndex, $row, $break) { return $this->setBreak(Coordinate::stringFromColumnIndex($columnIndex) . $row, $break); } /** * Get breaks. *< * @return array[]> * @return int[]*/ public function getBreaks() {< return $this->breaks;> $breaks = []; > foreach ($this->rowBreaks as $break) { > $breaks[$break->getCoordinate()] = self::BREAK_ROW; > } > foreach ($this->columnBreaks as $break) { > $breaks[$break->getCoordinate()] = self::BREAK_COLUMN; > } > > return $breaks;} /**< * Set merge on a cell range.> * Get row breaks.*< * @param string $pRange Cell range (e.g. A1:E1)> * @return PageBreak[] > */ > public function getRowBreaks() > { > return $this->rowBreaks; > } > > /** > * Get row breaks. > * > * @return PageBreak[] > */ > public function getColumnBreaks() > { > return $this->columnBreaks; > } > > /** > * Set merge on a cell range.*< * @throws Exception> * @param AddressRange|array<int>|string $range 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. > * @param string $behaviour 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 Worksheet> * @return $this*/< public function mergeCells($pRange)> public function mergeCells($range, $behaviour = self::MERGE_CELL_CONTENT_EMPTY){< // Uppercase coordinate < $pRange = strtoupper($pRange);> $range = Functions::trimSheetFromCellReference(Validations::validateCellRange($range)); > > if (strpos($range, ':') === false) { > $range .= ":{$range}"; > }< if (strpos($pRange, ':') !== false) { < $this->mergeCells[$pRange] = $pRange;> if (preg_match('/^([A-Z]+)(\\d+):([A-Z]+)(\\d+)$/', $range, $matches) !== 1) { > throw new Exception('Merge must be on a valid range of cells.'); > }< // make sure cells are created> $this->mergeCells[$range] = $range; > $firstRow = (int) $matches[2]; > $lastRow = (int) $matches[4]; > $firstColumn = $matches[1]; > $lastColumn = $matches[3]; > $firstColumnIndex = Coordinate::columnIndexFromString($firstColumn); > $lastColumnIndex = Coordinate::columnIndexFromString($lastColumn); > $numberRows = $lastRow - $firstRow; > $numberColumns = $lastColumnIndex - $firstColumnIndex;< // get the cells in the range < $aReferences = Coordinate::extractAllCellReferencesInRange($pRange);> if ($numberRows === 1 && $numberColumns === 1) { > return $this; > }// create upper left cell if it does not already exist< $upperLeft = $aReferences[0];> $upperLeft = "{$firstColumn}{$firstRow}";if (!$this->cellExists($upperLeft)) { $this->getCell($upperLeft)->setValueExplicit(null, DataType::TYPE_NULL); }> if ($behaviour !== self::MERGE_CELL_CONTENT_HIDE) {// Blank out the rest of the cells in the range (if they exist)< $count = count($aReferences); < for ($i = 1; $i < $count; ++$i) { < if ($this->cellExists($aReferences[$i])) { < $this->getCell($aReferences[$i])->setValueExplicit(null, DataType::TYPE_NULL); < } < }> if ($numberRows > $numberColumns) { > $this->clearMergeCellsByColumn($firstColumn, $lastColumn, $firstRow, $lastRow, $upperLeft, $behaviour);} else {< throw new Exception('Merge must be set on a range of cells.');> $this->clearMergeCellsByRow($firstColumn, $lastColumnIndex, $firstRow, $lastRow, $upperLeft, $behaviour); > }} return $this; }> private function clearMergeCellsByColumn(string $firstColumn, string $lastColumn, int $firstRow, int $lastRow, string $upperLeft, string $behaviour): void /** > { * Set merge on a cell range by using numeric cell coordinates. > $leftCellValue = ($behaviour === self::MERGE_CELL_CONTENT_MERGE) * > ? [$this->getCell($upperLeft)->getFormattedValue()] * @param int $columnIndex1 Numeric column coordinate of the first cell > : []; * @param int $row1 Numeric row coordinate of the first cell > * @param int $columnIndex2 Numeric column coordinate of the last cell > foreach ($this->getColumnIterator($firstColumn, $lastColumn) as $column) { * @param int $row2 Numeric row coordinate of the last cell > $iterator = $column->getCellIterator($firstRow); * > $iterator->setIterateOnlyExistingCells(true); * @throws Exception > foreach ($iterator as $cell) { * > if ($cell !== null) { * @return Worksheet > $row = $cell->getRow(); */ > if ($row > $lastRow) { public function mergeCellsByColumnAndRow($columnIndex1, $row1, $columnIndex2, $row2) > break; { > } $cellRange = Coordinate::stringFromColumnIndex($columnIndex1) . $row1 . ':' . Coordinate::stringFromColumnIndex($columnIndex2) . $row2; > $leftCellValue = $this->mergeCellBehaviour($cell, $upperLeft, $behaviour, $leftCellValue); > } return $this->mergeCells($cellRange); > } } > } > /** > if ($behaviour === self::MERGE_CELL_CONTENT_MERGE) { * Remove merge on a cell range. > $this->getCell($upperLeft)->setValueExplicit(implode(' ', $leftCellValue), DataType::TYPE_STRING); * > } * @param string $pRange Cell range (e.g. A1:E1) > } * > * @throws Exception > private function clearMergeCellsByRow(string $firstColumn, int $lastColumnIndex, int $firstRow, int $lastRow, string $upperLeft, string $behaviour): void * > { * @return Worksheet > $leftCellValue = ($behaviour === self::MERGE_CELL_CONTENT_MERGE) */ > ? [$this->getCell($upperLeft)->getFormattedValue()] public function unmergeCells($pRange) > : []; { > // Uppercase coordinate > foreach ($this->getRowIterator($firstRow, $lastRow) as $row) { $pRange = strtoupper($pRange); > $iterator = $row->getCellIterator($firstColumn); > $iterator->setIterateOnlyExistingCells(true); if (strpos($pRange, ':') !== false) { > foreach ($iterator as $cell) { if (isset($this->mergeCells[$pRange])) { > if ($cell !== null) { unset($this->mergeCells[$pRange]); > $column = $cell->getColumn(); } else { > $columnIndex = Coordinate::columnIndexFromString($column); throw new Exception('Cell range ' . $pRange . ' not known as merged.'); > if ($columnIndex > $lastColumnIndex) { } > break; } else { > } throw new Exception('Merge can only be removed from a range of cells.'); > $leftCellValue = $this->mergeCellBehaviour($cell, $upperLeft, $behaviour, $leftCellValue); } > } > } return $this; > } } > > if ($behaviour === self::MERGE_CELL_CONTENT_MERGE) { /** > $this->getCell($upperLeft)->setValueExplicit(implode(' ', $leftCellValue), DataType::TYPE_STRING); * Remove merge on a cell range by using numeric cell coordinates. > } * > } * @param int $columnIndex1 Numeric column coordinate of the first cell > * @param int $row1 Numeric row coordinate of the first cell > public function mergeCellBehaviour(Cell $cell, string $upperLeft, string $behaviour, array $leftCellValue): array * @param int $columnIndex2 Numeric column coordinate of the last cell > { * @param int $row2 Numeric row coordinate of the last cell > if ($cell->getCoordinate() !== $upperLeft) { * > Calculation::getInstance($cell->getWorksheet()->getParentOrThrow())->flushInstance(); * @throws Exception > if ($behaviour === self::MERGE_CELL_CONTENT_MERGE) { * > $cellValue = $cell->getFormattedValue(); * @return Worksheet > if ($cellValue !== '') { */ > $leftCellValue[] = $cellValue; public function unmergeCellsByColumnAndRow($columnIndex1, $row1, $columnIndex2, $row2) > } { > } $cellRange = Coordinate::stringFromColumnIndex($columnIndex1) . $row1 . ':' . Coordinate::stringFromColumnIndex($columnIndex2) . $row2; > $cell->setValueExplicit(null, DataType::TYPE_NULL); > } return $this->unmergeCells($cellRange); > } > return $leftCellValue; > } /** >* Get merge cells array.> * @deprecated 1.23.0 * > * Use the mergeCells() method with a cell address range such as 'C5:F8' instead;, * @return array[] > * or passing in an array of [$fromColumnIndex, $fromRow, $toColumnIndex, $toRow] (e.g. [3, 5, 6, 8]), */ > * or an AddressRange object. public function getMergeCells() > * @see Worksheet::mergeCells() { > *< * < * @throws Exception < * < * @return Worksheet < */ < public function mergeCellsByColumnAndRow($columnIndex1, $row1, $columnIndex2, $row2) < { < $cellRange = Coordinate::stringFromColumnIndex($columnIndex1) . $row1 . ':' . Coordinate::stringFromColumnIndex($columnIndex2) . $row2;> * @param string $behaviour 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 $this > */ > public function mergeCellsByColumnAndRow($columnIndex1, $row1, $columnIndex2, $row2, $behaviour = self::MERGE_CELL_CONTENT_EMPTY) > { > $cellRange = new CellRange( > CellAddress::fromColumnAndRow($columnIndex1, $row1), > CellAddress::fromColumnAndRow($columnIndex2, $row2) > );< return $this->mergeCells($cellRange);> return $this->mergeCells($cellRange, $behaviour);< * @param string $pRange Cell range (e.g. A1:E1) < * < * @throws Exception> * @param AddressRange|array<int>|string $range 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 Worksheet> * @return $this< public function unmergeCells($pRange)> public function unmergeCells($range)< // Uppercase coordinate < $pRange = strtoupper($pRange);> $range = Functions::trimSheetFromCellReference(Validations::validateCellRange($range));< if (strpos($pRange, ':') !== false) { < if (isset($this->mergeCells[$pRange])) { < unset($this->mergeCells[$pRange]);> if (strpos($range, ':') !== false) { > if (isset($this->mergeCells[$range])) { > unset($this->mergeCells[$range]);< throw new Exception('Cell range ' . $pRange . ' not known as merged.');> throw new Exception('Cell range ' . $range . ' not known as merged.');*> * @deprecated 1.23.0 * @param string $pRange Cell (e.g. A1) or cell range (e.g. A1:E1) > * Use the unmergeCells() method with a cell address range such as 'C5:F8' instead;, * @param string $pPassword Password to unlock the protection > * or passing in an array of [$fromColumnIndex, $fromRow, $toColumnIndex, $toRow] (e.g. [3, 5, 6, 8]), * @param bool $pAlreadyHashed If the password has already been hashed, set this to true > * or an AddressRange object. * > * @see Worksheet::unmergeCells() * @return Worksheet > *< * @throws Exception < * < * @return Worksheet> * @return $this< $cellRange = Coordinate::stringFromColumnIndex($columnIndex1) . $row1 . ':' . Coordinate::stringFromColumnIndex($columnIndex2) . $row2;> $cellRange = new CellRange( > CellAddress::fromColumnAndRow($columnIndex1, $row1), > CellAddress::fromColumnAndRow($columnIndex2, $row2) > );< * @return array[]> * @return string[]< * @param array $pValue> * @param string[] $mergeCells< * @return Worksheet> * @return $this< public function setMergeCells(array $pValue)> public function setMergeCells(array $mergeCells)< $this->mergeCells = $pValue;> $this->mergeCells = $mergeCells;< * Set protection on a cell range.> * Set protection on a cell or cell range.< * @param string $pRange Cell (e.g. A1) or cell range (e.g. A1:E1) < * @param string $pPassword Password to unlock the protection < * @param bool $pAlreadyHashed If the password has already been hashed, set this to true> * @param AddressRange|array<int>|CellAddress|int|string $range 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. > * @param string $password Password to unlock the protection > * @param bool $alreadyHashed If the password has already been hashed, set this to true< * @return Worksheet> * @return $this< public function protectCells($pRange, $pPassword, $pAlreadyHashed = false)> public function protectCells($range, $password, $alreadyHashed = false)< // Uppercase coordinate < $pRange = strtoupper($pRange);> $range = Functions::trimSheetFromCellReference(Validations::validateCellOrCellRange($range));< if (!$pAlreadyHashed) { < $pPassword = Shared\PasswordHasher::hashPassword($pPassword);> if (!$alreadyHashed) { > $password = Shared\PasswordHasher::hashPassword($password);< $this->protectedCells[$pRange] = $pPassword;> $this->protectedCells[$range] = $password;* @param int $row2 Numeric row coordinate of the last cell> * @deprecated 1.23.0 * @param string $password Password to unlock the protection > * Use the protectCells() method with a cell address range such as 'C5:F8' instead;, * @param bool $alreadyHashed If the password has already been hashed, set this to true > * or passing in an array of [$fromColumnIndex, $fromRow, $toColumnIndex, $toRow] (e.g. [3, 5, 6, 8]), * > * or an AddressRange object. * @return Worksheet > * @see Worksheet::protectCells() */ > *< * @return Worksheet> * @return $this{< $cellRange = Coordinate::stringFromColumnIndex($columnIndex1) . $row1 . ':' . Coordinate::stringFromColumnIndex($columnIndex2) . $row2;> $cellRange = new CellRange( > CellAddress::fromColumnAndRow($columnIndex1, $row1), > CellAddress::fromColumnAndRow($columnIndex2, $row2) > );return $this->protectCells($cellRange, $password, $alreadyHashed); } /**< * Remove protection on a cell range. < * < * @param string $pRange Cell (e.g. A1) or cell range (e.g. A1:E1)> * Remove protection on a cell or cell range.*< * @throws Exception> * @param AddressRange|array<int>|CellAddress|int|string $range 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 Worksheet> * @return $this*/< public function unprotectCells($pRange)> public function unprotectCells($range){< // Uppercase coordinate < $pRange = strtoupper($pRange);> $range = Functions::trimSheetFromCellReference(Validations::validateCellOrCellRange($range));< if (isset($this->protectedCells[$pRange])) { < unset($this->protectedCells[$pRange]);> if (isset($this->protectedCells[$range])) { > unset($this->protectedCells[$range]);} else {< throw new Exception('Cell range ' . $pRange . ' not known as protected.');> throw new Exception('Cell range ' . $range . ' not known as protected.');} return $this; } /** * Remove protection on a cell range by using numeric cell coordinates. *> * @deprecated 1.23.0 * @param int $columnIndex1 Numeric column coordinate of the first cell > * Use the unprotectCells() method with a cell address range such as 'C5:F8' instead;, * @param int $row1 Numeric row coordinate of the first cell > * or passing in an array of [$fromColumnIndex, $fromRow, $toColumnIndex, $toRow] (e.g. [3, 5, 6, 8]), * @param int $columnIndex2 Numeric column coordinate of the last cell > * or an AddressRange object. * @param int $row2 Numeric row coordinate of the last cell > * @see Worksheet::unprotectCells() * > *< * @throws Exception < * < * @return Worksheet> * @return $this*/ public function unprotectCellsByColumnAndRow($columnIndex1, $row1, $columnIndex2, $row2) {< $cellRange = Coordinate::stringFromColumnIndex($columnIndex1) . $row1 . ':' . Coordinate::stringFromColumnIndex($columnIndex2) . $row2;> $cellRange = new CellRange( > CellAddress::fromColumnAndRow($columnIndex1, $row1), > CellAddress::fromColumnAndRow($columnIndex2, $row2) > );return $this->unprotectCells($cellRange); } /** * Get protected cells. *< * @return array[]> * @return string[]*/ public function getProtectedCells() { return $this->protectedCells; } /** * Get Autofilter. * * @return AutoFilter */ public function getAutoFilter() { return $this->autoFilter; } /** * Set AutoFilter. *< * @param AutoFilter|string $pValue> * @param AddressRange|array<int>|AutoFilter|string $autoFilterOrRange* 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.< * @throws Exception < * < * @return Worksheet> * @return $this*/< public function setAutoFilter($pValue)> public function setAutoFilter($autoFilterOrRange){< if (is_string($pValue)) { < $this->autoFilter->setRange($pValue); < } elseif (is_object($pValue) && ($pValue instanceof AutoFilter)) { < $this->autoFilter = $pValue;> if (is_object($autoFilterOrRange) && ($autoFilterOrRange instanceof AutoFilter)) { > $this->autoFilter = $autoFilterOrRange; > } else { > $cellRange = Functions::trimSheetFromCellReference(Validations::validateCellRange($autoFilterOrRange)); > > $this->autoFilter->setRange($cellRange);} return $this; } /** * Set Autofilter Range by using numeric cell coordinates. *> * @deprecated 1.23.0 * @param int $columnIndex1 Numeric column coordinate of the first cell > * Use the setAutoFilter() method with a cell address range such as 'C5:F8' instead;, * @param int $row1 Numeric row coordinate of the first cell > * or passing in an array of [$fromColumnIndex, $fromRow, $toColumnIndex, $toRow] (e.g. [3, 5, 6, 8]), * @param int $columnIndex2 Numeric column coordinate of the second cell > * or an AddressRange object or AutoFilter object. * @param int $row2 Numeric row coordinate of the second cell > * @see Worksheet::setAutoFilter() * > *< * @throws Exception < * < * @return Worksheet> * @return $this*/ public function setAutoFilterByColumnAndRow($columnIndex1, $row1, $columnIndex2, $row2) {< return $this->setAutoFilter( < Coordinate::stringFromColumnIndex($columnIndex1) . $row1 < . ':' . < Coordinate::stringFromColumnIndex($columnIndex2) . $row2> $cellRange = new CellRange( > CellAddress::fromColumnAndRow($columnIndex1, $row1), > CellAddress::fromColumnAndRow($columnIndex2, $row2));> } > return $this->setAutoFilter($cellRange);/** * Remove autofilter.> */ * > public function removeAutoFilter(): self * @return Worksheet > { */ > $this->autoFilter->setRange(''); public function removeAutoFilter() > { > return $this; $this->autoFilter->setRange(null); > } > return $this; > /** } > * Get collection of Tables.< * @return Worksheet> * @return ArrayObject<int, Table>< public function removeAutoFilter()> public function getTableCollection()< $this->autoFilter->setRange(null);> return $this->tableCollection; > } > > /** > * Add Table. > * > * @return $this > */ > public function addTable(Table $table): self > { > $table->setWorksheet($this); > $this->tableCollection[] = $table; > > return $this; > } > > /** > * @return string[] array of Table names > */ > public function getTableNames(): array > { > $tableNames = []; > > foreach ($this->tableCollection as $table) { > /** @var Table $table */ > $tableNames[] = $table->getName(); > } > > return $tableNames; > } > > /** @var null|Table */ > private static $scrutinizerNullTable; > > /** @var null|int */ > private static $scrutinizerNullInt; > > /** > * @param string $name the table name to search > * > * @return null|Table The table from the tables collection, or null if not found > */ > public function getTableByName(string $name): ?Table > { > $tableIndex = $this->getTableIndexByName($name); > > return ($tableIndex === null) ? self::$scrutinizerNullTable : $this->tableCollection[$tableIndex]; > } > > /** > * @param string $name the table name to search > * > * @return null|int The index of the located table in the tables collection, or null if not found > */ > protected function getTableIndexByName(string $name): ?int > { > $name = Shared\StringHelper::strToUpper($name); > foreach ($this->tableCollection as $index => $table) { > /** @var Table $table */ > if (Shared\StringHelper::strToUpper($table->getName()) === $name) { > return $index; > } > } > > return self::$scrutinizerNullInt; > } > > /** > * Remove Table by name. > * > * @param string $name Table name > * > * @return $this > */ > public function removeTableByName(string $name): self > { > $tableIndex = $this->getTableIndexByName($name); > > if ($tableIndex !== null) { > unset($this->tableCollection[$tableIndex]); > } > > return $this; > } > > /** > * Remove collection of Tables. > */ > public function removeTableCollection(): self > { > $this->tableCollection = new ArrayObject();*< * @return string> * @return null|string*/ public function getFreezePane() { return $this->freezePane; } /** * 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) *< * @param null|string $cell Position of the split < * @param null|string $topLeftCell default position of the right bottom pane < * < * @throws Exception < * < * @return Worksheet < */ < public function freezePane($cell, $topLeftCell = null) < { < if (is_string($cell) && Coordinate::coordinateIsRange($cell)) {> * @param null|array<int>|CellAddress|string $coordinate 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. > * @param null|array<int>|CellAddress|string $topLeftCell 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 $this > */ > public function freezePane($coordinate, $topLeftCell = null) > { > $cellAddress = ($coordinate !== null) > ? Functions::trimSheetFromCellReference(Validations::validateCellAddress($coordinate)) > : null; > if ($cellAddress !== null && Coordinate::coordinateIsRange($cellAddress)) {throw new Exception('Freeze pane can not be set on a range of cells.'); }> $topLeftCell = ($topLeftCell !== null) > ? Functions::trimSheetFromCellReference(Validations::validateCellAddress($topLeftCell)) if ($cell !== null && $topLeftCell === null) { > : null;< if ($cell !== null && $topLeftCell === null) { < $coordinate = Coordinate::coordinateFromString($cell);> if ($cellAddress !== null && $topLeftCell === null) { > $coordinate = Coordinate::coordinateFromString($cellAddress);}< $this->freezePane = $cell;> $this->freezePane = $cellAddress; > $this->topLeftCell = $topLeftCell; > > return $this; > } > > public function setTopLeftCell(string $topLeftCell): self > {$this->topLeftCell = $topLeftCell; return $this; } /** * Freeze Pane by using numeric cell coordinates. *> * @deprecated 1.23.0 * @param int $columnIndex Numeric column coordinate of the cell > * Use the freezePane() method with a cell address such as 'C5' instead;, * @param int $row Numeric row coordinate of the cell > * or passing in an array of [$columnIndex, $row] (e.g. [3, 5]), or a CellAddress object. * > * @see Worksheet::freezePane() * @return Worksheet > *< * @return Worksheet> * @return $thispublic function freezePaneByColumnAndRow($columnIndex, $row) { return $this->freezePane(Coordinate::stringFromColumnIndex($columnIndex) . $row); } /** * Unfreeze Pane. *< * @return Worksheet> * @return $this*/ public function unfreezePane() { return $this->freezePane(null); } /** * Get the default position of the right bottom pane. *< * @return int> * @return null|string*/ public function getTopLeftCell() { return $this->topLeftCell; } /** * Insert a new row, updating all possible related data. *< * @param int $pBefore Insert before this one < * @param int $pNumRows Number of rows to insert> * @param int $before Insert before this one > * @param int $numberOfRows Number of rows to insert*< * @throws Exception < * < * @return Worksheet> * @return $this*/< public function insertNewRowBefore($pBefore, $pNumRows = 1)> public function insertNewRowBefore($before, $numberOfRows = 1){< if ($pBefore >= 1) {> if ($before >= 1) {$objReferenceHelper = ReferenceHelper::getInstance();< $objReferenceHelper->insertNewBefore('A' . $pBefore, 0, $pNumRows, $this);> $objReferenceHelper->insertNewBefore('A' . $before, 0, $numberOfRows, $this);} else { throw new Exception('Rows can only be inserted before at least row 1.'); } return $this; } /** * Insert a new column, updating all possible related data. *< * @param string $pBefore Insert before this one, eg: 'A' < * @param int $pNumCols Number of columns to insert < * < * @throws Exception> * @param string $before Insert before this one, eg: 'A' > * @param int $numberOfColumns Number of columns to insert*< * @return Worksheet> * @return $this*/< public function insertNewColumnBefore($pBefore, $pNumCols = 1)> public function insertNewColumnBefore($before, $numberOfColumns = 1){< if (!is_numeric($pBefore)) {> if (!is_numeric($before)) {$objReferenceHelper = ReferenceHelper::getInstance();< $objReferenceHelper->insertNewBefore($pBefore . '1', $pNumCols, 0, $this);> $objReferenceHelper->insertNewBefore($before . '1', $numberOfColumns, 0, $this);} else { throw new Exception('Column references should not be numeric.'); } return $this; } /** * Insert a new column, updating all possible related data. * * @param int $beforeColumnIndex Insert before this one (numeric column coordinate of the cell)< * @param int $pNumCols Number of columns to insert> * @param int $numberOfColumns Number of columns to insert*< * @throws Exception < * < * @return Worksheet> * @return $this*/< public function insertNewColumnBeforeByIndex($beforeColumnIndex, $pNumCols = 1)> public function insertNewColumnBeforeByIndex($beforeColumnIndex, $numberOfColumns = 1){ if ($beforeColumnIndex >= 1) {< return $this->insertNewColumnBefore(Coordinate::stringFromColumnIndex($beforeColumnIndex), $pNumCols);> return $this->insertNewColumnBefore(Coordinate::stringFromColumnIndex($beforeColumnIndex), $numberOfColumns);} throw new Exception('Columns can only be inserted before at least column A (1).'); } /** * Delete a row, updating all possible related data. *< * @param int $pRow Remove starting with this one < * @param int $pNumRows Number of rows to remove < * < * @throws Exception> * @param int $row Remove starting with this one > * @param int $numberOfRows Number of rows to remove*< * @return Worksheet> * @return $this*/< public function removeRow($pRow, $pNumRows = 1)> public function removeRow($row, $numberOfRows = 1){< if ($pRow >= 1) { < for ($r = 0; $r < $pNumRows; ++$r) { < $this->getCellCollection()->removeRow($pRow + $r);> if ($row < 1) { > throw new Exception('Rows to be deleted should at least start from row 1.');}> $holdRowDimensions = $this->removeRowDimensions($row, $numberOfRows);$highestRow = $this->getHighestDataRow();> $removedRowsCounter = 0; $objReferenceHelper = ReferenceHelper::getInstance(); > $objReferenceHelper->insertNewBefore('A' . ($pRow + $pNumRows), 0, -$pNumRows, $this); > for ($r = 0; $r < $numberOfRows; ++$r) { for ($r = 0; $r < $pNumRows; ++$r) { > if ($row + $r <= $highestRow) { $this->getCellCollection()->removeRow($highestRow); > $this->getCellCollection()->removeRow($row + $r); --$highestRow; > ++$removedRowsCounter; } > } } else { > } throw new Exception('Rows to be deleted should at least start from row 1.'); >< $objReferenceHelper->insertNewBefore('A' . ($pRow + $pNumRows), 0, -$pNumRows, $this); < for ($r = 0; $r < $pNumRows; ++$r) {> $objReferenceHelper->insertNewBefore('A' . ($row + $numberOfRows), 0, -$numberOfRows, $this); > for ($r = 0; $r < $removedRowsCounter; ++$r) {< } else { < throw new Exception('Rows to be deleted should at least start from row 1.'); < }> > $this->rowDimensions = $holdRowDimensions;/**> private function removeRowDimensions(int $row, int $numberOfRows): array * Remove a column, updating all possible related data. > { * > $highRow = $row + $numberOfRows - 1; * @param string $pColumn Remove starting with this one, eg: 'A' > $holdRowDimensions = []; * @param int $pNumCols Number of columns to remove > foreach ($this->rowDimensions as $rowDimension) { * > $num = $rowDimension->getRowIndex(); * @throws Exception > if ($num < $row) { * > $holdRowDimensions[$num] = $rowDimension; * @return Worksheet > } elseif ($num > $highRow) { */ > $num -= $numberOfRows; public function removeColumn($pColumn, $pNumCols = 1) > $cloneDimension = clone $rowDimension; { > $cloneDimension->setRowIndex(/** @scrutinizer ignore-type */ $num); if (is_numeric($pColumn)) { > $holdRowDimensions[$num] = $cloneDimension; throw new Exception('Column references should not be numeric.'); > } } > } > $highestColumn = $this->getHighestDataColumn(); > return $holdRowDimensions; $highestColumnIndex = Coordinate::columnIndexFromString($highestColumn); > } $pColumnIndex = Coordinate::columnIndexFromString($pColumn); >< * @param string $pColumn Remove starting with this one, eg: 'A' < * @param int $pNumCols Number of columns to remove < * < * @throws Exception> * @param string $column Remove starting with this one, eg: 'A' > * @param int $numberOfColumns Number of columns to remove< * @return Worksheet> * @return $this< public function removeColumn($pColumn, $pNumCols = 1)> public function removeColumn($column, $numberOfColumns = 1)< if (is_numeric($pColumn)) {> if (is_numeric($column)) {< $pColumnIndex = Coordinate::columnIndexFromString($pColumn);> $pColumnIndex = Coordinate::columnIndexFromString($column); > > $holdColumnDimensions = $this->removeColumnDimensions($pColumnIndex, $numberOfColumns); > > $column = Coordinate::stringFromColumnIndex($pColumnIndex + $numberOfColumns); > $objReferenceHelper = ReferenceHelper::getInstance(); > $objReferenceHelper->insertNewBefore($column . '1', -$numberOfColumns, 0, $this); > > $this->columnDimensions = $holdColumnDimensions;< $pColumn = Coordinate::stringFromColumnIndex($pColumnIndex + $pNumCols); < $objReferenceHelper = ReferenceHelper::getInstance(); < $objReferenceHelper->insertNewBefore($pColumn . '1', -$pNumCols, 0, $this); << for ($c = 0, $n = min($maxPossibleColumnsToBeRemoved, $pNumCols); $c < $n; ++$c) {> for ($c = 0, $n = min($maxPossibleColumnsToBeRemoved, $numberOfColumns); $c < $n; ++$c) {$highestColumn = Coordinate::stringFromColumnIndex(Coordinate::columnIndexFromString($highestColumn) - 1); } $this->garbageCollect(); return $this; }> private function removeColumnDimensions(int $pColumnIndex, int $numberOfColumns): array /** > { * Remove a column, updating all possible related data. > $highCol = $pColumnIndex + $numberOfColumns - 1; * > $holdColumnDimensions = []; * @param int $columnIndex Remove starting with this one (numeric column coordinate of the cell) > foreach ($this->columnDimensions as $columnDimension) { * @param int $numColumns Number of columns to remove > $num = $columnDimension->getColumnNumeric(); * > if ($num < $pColumnIndex) { * @throws Exception > $str = $columnDimension->getColumnIndex(); * > $holdColumnDimensions[$str] = $columnDimension; * @return Worksheet > } elseif ($num > $highCol) { */ > $cloneDimension = clone $columnDimension; public function removeColumnByIndex($columnIndex, $numColumns = 1) > $cloneDimension->setColumnNumeric($num - $numberOfColumns); { > $str = $cloneDimension->getColumnIndex(); if ($columnIndex >= 1) { > $holdColumnDimensions[$str] = $cloneDimension; return $this->removeColumn(Coordinate::stringFromColumnIndex($columnIndex), $numColumns); > } } > } > throw new Exception('Columns to be deleted should at least start from column A (1)'); > return $holdColumnDimensions; } > } >< * @throws Exception < * < * @return Worksheet> * @return $this< * < * @return bool< public function getShowGridlines()> public function getShowGridlines(): bool{ return $this->showGridlines; } /** * Set show gridlines. *< * @param bool $pValue Show gridlines (true/false)> * @param bool $showGridLines Show gridlines (true/false)*< * @return Worksheet> * @return $this*/< public function setShowGridlines($pValue)> public function setShowGridlines(bool $showGridLines): self{< $this->showGridlines = $pValue;> $this->showGridlines = $showGridLines;return $this; } /** * Print gridlines?< * < * @return bool*/< public function getPrintGridlines()> public function getPrintGridlines(): bool{ return $this->printGridlines; } /** * Set print gridlines. *< * @param bool $pValue Print gridlines (true/false)> * @param bool $printGridLines Print gridlines (true/false)*< * @return Worksheet> * @return $this*/< public function setPrintGridlines($pValue)> public function setPrintGridlines(bool $printGridLines): self{< $this->printGridlines = $pValue;> $this->printGridlines = $printGridLines;return $this; } /** * Show row and column headers?< * < * @return bool*/< public function getShowRowColHeaders()> public function getShowRowColHeaders(): bool{ return $this->showRowColHeaders; } /** * Set show row and column headers. *< * @param bool $pValue Show row and column headers (true/false)> * @param bool $showRowColHeaders Show row and column headers (true/false)*< * @return Worksheet> * @return $this*/< public function setShowRowColHeaders($pValue)> public function setShowRowColHeaders(bool $showRowColHeaders): self{< $this->showRowColHeaders = $pValue;> $this->showRowColHeaders = $showRowColHeaders;return $this; } /** * Show summary below? (Row/Column outlining).< * < * @return bool*/< public function getShowSummaryBelow()> public function getShowSummaryBelow(): bool{ return $this->showSummaryBelow; } /** * Set show summary below. *< * @param bool $pValue Show summary below (true/false)> * @param bool $showSummaryBelow Show summary below (true/false)*< * @return Worksheet> * @return $this*/< public function setShowSummaryBelow($pValue)> public function setShowSummaryBelow(bool $showSummaryBelow): self{< $this->showSummaryBelow = $pValue;> $this->showSummaryBelow = $showSummaryBelow;return $this; } /** * Show summary right? (Row/Column outlining).< * < * @return bool*/< public function getShowSummaryRight()> public function getShowSummaryRight(): bool{ return $this->showSummaryRight; } /** * Set show summary right. *< * @param bool $pValue Show summary right (true/false)> * @param bool $showSummaryRight Show summary right (true/false)*< * @return Worksheet> * @return $this*/< public function setShowSummaryRight($pValue)> public function setShowSummaryRight(bool $showSummaryRight): self{< $this->showSummaryRight = $pValue;> $this->showSummaryRight = $showSummaryRight;return $this; } /** * Get comments. * * @return Comment[] */ public function getComments() { return $this->comments; } /** * Set comments array for the entire sheet. *< * @param Comment[] $pValue> * @param Comment[] $comments*< * @return Worksheet> * @return $this*/< public function setComments(array $pValue)> public function setComments(array $comments): self{< $this->comments = $pValue;> $this->comments = $comments;return $this; } /**< * Get comment for cell.> * Remove comment from cell.*< * @param string $pCellCoordinate Cell coordinate to get comment for, eg: 'A1'> * @param array<int>|CellAddress|string $cellCoordinate Coordinate of the cell as a string, eg: 'C5'; > * or as an array of [$columnIndex, $row] (e.g. [3, 5]), or a CellAddress object.*< * @throws Exception> * @return $this > */ > public function removeComment($cellCoordinate): self > { > $cellAddress = Functions::trimSheetFromCellReference(Validations::validateCellAddress($cellCoordinate)); > > if (Coordinate::coordinateIsRange($cellAddress)) { > throw new Exception('Cell coordinate string can not be a range of cells.'); > } elseif (strpos($cellAddress, '$') !== false) { > throw new Exception('Cell coordinate string must not be absolute.'); > } elseif ($cellAddress == '') { > throw new Exception('Cell coordinate can not be zero-length string.'); > } > // Check if we have a comment for this cell and delete it > if (isset($this->comments[$cellAddress])) { > unset($this->comments[$cellAddress]); > } > > return $this; > } > > /** > * Get comment for cell.*< * @return Comment> * @param array<int>|CellAddress|string $cellCoordinate Coordinate of the cell as a string, eg: 'C5'; > * or as an array of [$columnIndex, $row] (e.g. [3, 5]), or a CellAddress object.*/< public function getComment($pCellCoordinate)> public function getComment($cellCoordinate): Comment{< // Uppercase coordinate < $pCellCoordinate = strtoupper($pCellCoordinate);> $cellAddress = Functions::trimSheetFromCellReference(Validations::validateCellAddress($cellCoordinate));< if (Coordinate::coordinateIsRange($pCellCoordinate)) {> if (Coordinate::coordinateIsRange($cellAddress)) {throw new Exception('Cell coordinate string can not be a range of cells.');< } elseif (strpos($pCellCoordinate, '$') !== false) {> } elseif (strpos($cellAddress, '$') !== false) {throw new Exception('Cell coordinate string must not be absolute.');< } elseif ($pCellCoordinate == '') {> } elseif ($cellAddress == '') {throw new Exception('Cell coordinate can not be zero-length string.'); } // Check if we already have a comment for this cell.< if (isset($this->comments[$pCellCoordinate])) { < return $this->comments[$pCellCoordinate];> if (isset($this->comments[$cellAddress])) { > return $this->comments[$cellAddress];} // If not, create a new comment. $newComment = new Comment();< $this->comments[$pCellCoordinate] = $newComment;> $this->comments[$cellAddress] = $newComment;return $newComment; } /** * Get comment for cell by using numeric cell coordinates. *> * @deprecated 1.23.0 * @param int $columnIndex Numeric column coordinate of the cell > * Use the getComment() method with a cell address such as 'C5' instead;, * @param int $row Numeric row coordinate of the cell > * or passing in an array of [$columnIndex, $row] (e.g. [3, 5]), or a CellAddress object. * > * @see Worksheet::getComment() * @return Comment > *< * < * @return Comment< public function getCommentByColumnAndRow($columnIndex, $row)> public function getCommentByColumnAndRow($columnIndex, $row): Commentreturn $this->getComment(Coordinate::stringFromColumnIndex($columnIndex) . $row); } /** * Get active cell. * * @return string Example: 'A1' */ public function getActiveCell() { return $this->activeCell; } /** * Get selected cells. * * @return string */ public function getSelectedCells() { return $this->selectedCells; } /** * Selected cell. *< * @param string $pCoordinate Cell (i.e. A1)> * @param string $coordinate Cell (i.e. A1)*< * @return Worksheet> * @return $this*/< public function setSelectedCell($pCoordinate)> public function setSelectedCell($coordinate){< return $this->setSelectedCells($pCoordinate);> return $this->setSelectedCells($coordinate);} /** * Select a range of cells. *< * @param string $pCoordinate Cell range, examples: 'A1', 'B2:G5', 'A:C', '3:6'> * @param AddressRange|array<int>|CellAddress|int|string $coordinate 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 Worksheet> * @return $this*/< public function setSelectedCells($pCoordinate)> public function setSelectedCells($coordinate){< // Uppercase coordinate < $pCoordinate = strtoupper($pCoordinate); < < // Convert 'A' to 'A:A' < $pCoordinate = preg_replace('/^([A-Z]+)$/', '${1}:${1}', $pCoordinate); < < // Convert '1' to '1:1' < $pCoordinate = preg_replace('/^(\d+)$/', '${1}:${1}', $pCoordinate); < < // Convert 'A:C' to 'A1:C1048576' < $pCoordinate = preg_replace('/^([A-Z]+):([A-Z]+)$/', '${1}1:${2}1048576', $pCoordinate); < < // Convert '1:3' to 'A1:XFD3' < $pCoordinate = preg_replace('/^(\d+):(\d+)$/', 'A${1}:XFD${2}', $pCoordinate);> if (is_string($coordinate)) { > $coordinate = Validations::definedNameToCoordinate($coordinate, $this); > } > $coordinate = Validations::validateCellOrCellRange($coordinate);< if (Coordinate::coordinateIsRange($pCoordinate)) { < [$first] = Coordinate::splitRange($pCoordinate);> if (Coordinate::coordinateIsRange($coordinate)) { > [$first] = Coordinate::splitRange($coordinate);$this->activeCell = $first[0]; } else {< $this->activeCell = $pCoordinate;> $this->activeCell = $coordinate;}< $this->selectedCells = $pCoordinate;> $this->selectedCells = $coordinate;return $this; } /** * Selected cell by using numeric cell coordinates. *> * @deprecated 1.23.0 * @param int $columnIndex Numeric column coordinate of the cell > * Use the setSelectedCells() method with a cell address such as 'C5' instead;, * @param int $row Numeric row coordinate of the cell > * or passing in an array of [$columnIndex, $row] (e.g. [3, 5]), or a CellAddress object. * > * @see Worksheet::setSelectedCells() * @throws Exception > *< * @throws Exception < * < * @return Worksheet> * @return $thispublic function setSelectedCellByColumnAndRow($columnIndex, $row) { return $this->setSelectedCells(Coordinate::stringFromColumnIndex($columnIndex) . $row); } /** * Get right-to-left. * * @return bool */ public function getRightToLeft() { return $this->rightToLeft; } /** * Set right-to-left. * * @param bool $value Right-to-left true/false *< * @return Worksheet> * @return $this*/ public function setRightToLeft($value) { $this->rightToLeft = $value; return $this; } /** * Fill worksheet from values in array. * * @param array $source Source array * @param mixed $nullValue Value in source array that stands for blank cell * @param string $startCell Insert array starting from this cell address as the top left coordinate * @param bool $strictNullComparison Apply strict comparison when testing for null values in the array *< * @throws Exception < * < * @return Worksheet> * @return $this*/ public function fromArray(array $source, $nullValue = null, $startCell = 'A1', $strictNullComparison = false) { // Convert a 1-D array to 2-D (for ease of looping) if (!is_array(end($source))) { $source = [$source]; } // start coordinate [$startColumn, $startRow] = Coordinate::coordinateFromString($startCell); // Loop through $source foreach ($source as $rowData) { $currentColumn = $startColumn; foreach ($rowData as $cellValue) { if ($strictNullComparison) { if ($cellValue !== $nullValue) { // Set cell value $this->getCell($currentColumn . $startRow)->setValue($cellValue); } } else { if ($cellValue != $nullValue) { // Set cell value $this->getCell($currentColumn . $startRow)->setValue($cellValue); } } ++$currentColumn; } ++$startRow; } return $this; } /** * Create array from a range of cells. *< * @param string $pRange Range of cells (i.e. "A1:B10"), or just one cell (i.e. "A1")> * @param string $range Range of cells (i.e. "A1:B10"), or just one cell (i.e. "A1")* @param mixed $nullValue Value returned in the array entry if a cell doesn't exist * @param bool $calculateFormulas Should formulas be calculated? * @param bool $formatData Should formatting be applied to cell values? * @param 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 array */< public function rangeToArray($pRange, $nullValue = null, $calculateFormulas = true, $formatData = true, $returnCellRef = false)> public function rangeToArray($range, $nullValue = null, $calculateFormulas = true, $formatData = true, $returnCellRef = false){ // Returnvalue $returnValue = []; // Identify the range that we need to extract from the worksheet< [$rangeStart, $rangeEnd] = Coordinate::rangeBoundaries($pRange);> [$rangeStart, $rangeEnd] = Coordinate::rangeBoundaries($range);$minCol = Coordinate::stringFromColumnIndex($rangeStart[0]); $minRow = $rangeStart[1]; $maxCol = Coordinate::stringFromColumnIndex($rangeEnd[0]); $maxRow = $rangeEnd[1]; ++$maxCol; // Loop through rows $r = -1; for ($row = $minRow; $row <= $maxRow; ++$row) { $rRef = $returnCellRef ? $row : ++$r; $c = -1; // Loop through columns in the current row for ($col = $minCol; $col != $maxCol; ++$col) { $cRef = $returnCellRef ? $col : ++$c; // Using getCell() will create a new cell if it doesn't already exist. We don't want that to happen // so we test and retrieve directly against cellCollection< if ($this->cellCollection->has($col . $row)) { < // Cell exists$cell = $this->cellCollection->get($col . $row);> //if ($this->cellCollection->has($col . $row)) { if ($cell->getValue() !== null) { > if ($cell !== null) { if ($cell->getValue() instanceof RichText) { > // Cell exists$returnValue[$rRef][$cRef] = $cell->getValue()->getPlainText(); } else { if ($calculateFormulas) { $returnValue[$rRef][$cRef] = $cell->getCalculatedValue(); } else { $returnValue[$rRef][$cRef] = $cell->getValue(); } } if ($formatData) {< $style = $this->parent->getCellXfByIndex($cell->getXfIndex());> $style = $this->getParentOrThrow()->getCellXfByIndex($cell->getXfIndex());$returnValue[$rRef][$cRef] = NumberFormat::toFormattedString( $returnValue[$rRef][$cRef],< ($style && $style->getNumberFormat()) ? $style->getNumberFormat()->getFormatCode() : NumberFormat::FORMAT_GENERAL> $style->getNumberFormat()->getFormatCode() ?? NumberFormat::FORMAT_GENERAL); } } else { // Cell holds a NULL $returnValue[$rRef][$cRef] = $nullValue; } } else { // Cell doesn't exist $returnValue[$rRef][$cRef] = $nullValue; } } } // Return return $returnValue; }> private function validateNamedRange(string $definedName, bool $returnNullIfInvalid = false): ?DefinedName /** > { * Create array from a range of cells. > $namedRange = DefinedName::resolveName($definedName, $this); * > if ($namedRange === null) { * @param string $pNamedRange Name of the Named Range > if ($returnNullIfInvalid) { * @param mixed $nullValue Value returned in the array entry if a cell doesn't exist > return null; * @param bool $calculateFormulas Should formulas be calculated? > } * @param bool $formatData Should formatting be applied to cell values? > * @param bool $returnCellRef False - Return a simple array of rows and columns indexed by number counting from zero > throw new Exception('Named Range ' . $definedName . ' does not exist.'); * True - Return rows and columns indexed by their actual row and column IDs > } * > * @throws Exception > if ($namedRange->isFormula()) { * > if ($returnNullIfInvalid) { * @return array > return null; */ > } public function namedRangeToArray($pNamedRange, $nullValue = null, $calculateFormulas = true, $formatData = true, $returnCellRef = false) > { > throw new Exception('Defined Named ' . $definedName . ' is a formula, not a range or cell.'); $namedRange = NamedRange::resolveRange($pNamedRange, $this); > } if ($namedRange !== null) { > $pWorkSheet = $namedRange->getWorksheet(); > if ($namedRange->getLocalOnly()) { $pCellRange = $namedRange->getRange(); > $worksheet = $namedRange->getWorksheet(); > if ($worksheet === null || $this->getHashCode() !== $worksheet->getHashCode()) { return $pWorkSheet->rangeToArray($pCellRange, $nullValue, $calculateFormulas, $formatData, $returnCellRef); > if ($returnNullIfInvalid) { } > return null; > } throw new Exception('Named Range ' . $pNamedRange . ' does not exist.'); > } > throw new Exception( > 'Named range ' . $definedName . ' is not accessible from within sheet ' . $this->getTitle() /** > ); * Create array from worksheet. > } * > } * @param mixed $nullValue Value returned in the array entry if a cell doesn't exist > * @param bool $calculateFormulas Should formulas be calculated? > return $namedRange; * @param bool $formatData Should formatting be applied to cell values? > } * @param bool $returnCellRef False - Return a simple array of rows and columns indexed by number counting from zero >< * @param string $pNamedRange Name of the Named Range> * @param string $definedName The Named Range that should be returned< * @throws Exception < *< public function namedRangeToArray($pNamedRange, $nullValue = null, $calculateFormulas = true, $formatData = true, $returnCellRef = false)> public function namedRangeToArray(string $definedName, $nullValue = null, $calculateFormulas = true, $formatData = true, $returnCellRef = false)< $namedRange = NamedRange::resolveRange($pNamedRange, $this);> $retVal = []; > $namedRange = $this->validateNamedRange($definedName);< $pWorkSheet = $namedRange->getWorksheet(); < $pCellRange = $namedRange->getRange(); < < return $pWorkSheet->rangeToArray($pCellRange, $nullValue, $calculateFormulas, $formatData, $returnCellRef);> $cellRange = ltrim(substr($namedRange->getValue(), (int) strrpos($namedRange->getValue(), '!')), '!'); > $cellRange = str_replace('$', '', $cellRange); > $workSheet = $namedRange->getWorksheet(); > if ($workSheet !== null) { > $retVal = $workSheet->rangeToArray($cellRange, $nullValue, $calculateFormulas, $formatData, $returnCellRef); > }< throw new Exception('Named Range ' . $pNamedRange . ' does not exist.');> return $retVal;$maxCol = $this->getHighestColumn(); $maxRow = $this->getHighestRow(); // Return return $this->rangeToArray('A1:' . $maxCol . $maxRow, $nullValue, $calculateFormulas, $formatData, $returnCellRef); } /** * Get row iterator. * * @param int $startRow The row number at which to start iterating * @param int $endRow The row number at which to stop iterating * * @return RowIterator */ public function getRowIterator($startRow = 1, $endRow = null) { return new RowIterator($this, $startRow, $endRow); } /** * Get column iterator. * * @param string $startColumn The column address at which to start iterating * @param string $endColumn The column address at which to stop iterating * * @return ColumnIterator */ public function getColumnIterator($startColumn = 'A', $endColumn = null) { return new ColumnIterator($this, $startColumn, $endColumn); } /** * Run PhpSpreadsheet garbage collector. *< * @return Worksheet> * @return $this*/ public function garbageCollect() { // Flush cache $this->cellCollection->get('A1'); // Lookup highest column and highest row if cells are cleaned $colRow = $this->cellCollection->getHighestRowAndColumn(); $highestRow = $colRow['row']; $highestColumn = Coordinate::columnIndexFromString($colRow['column']); // Loop through column dimensions foreach ($this->columnDimensions as $dimension) { $highestColumn = max($highestColumn, Coordinate::columnIndexFromString($dimension->getColumnIndex())); } // Loop through row dimensions foreach ($this->rowDimensions as $dimension) { $highestRow = max($highestRow, $dimension->getRowIndex()); } // Cache values if ($highestColumn < 1) {< $this->cachedHighestColumn = 'A';> $this->cachedHighestColumn = 1;} else {< $this->cachedHighestColumn = Coordinate::stringFromColumnIndex($highestColumn);> $this->cachedHighestColumn = $highestColumn;} $this->cachedHighestRow = $highestRow; // Return return $this; } /** * Get hash code. * * @return string Hash code */ public function getHashCode() { if ($this->dirty) { $this->hash = md5($this->title . $this->autoFilter . ($this->protection->isProtectionEnabled() ? 't' : 'f') . __CLASS__); $this->dirty = false; } return $this->hash; } /** * Extract worksheet title from range. * * 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']; * @param string $pRange Range to extract title from > * Example: extractSheetTitle("A1:C3", true) ==> ['', 'A1:C3']< * @param string $pRange Range to extract title from> * @param string $range Range to extract title from* * @return mixed */< public static function extractSheetTitle($pRange, $returnRange = false)> public static function extractSheetTitle($range, $returnRange = false){> if (empty($range)) { // Sheet title included? > return $returnRange ? [null, null] : null; if (($sep = strrpos($pRange, '!')) === false) { > } return $returnRange ? ['', $pRange] : ''; >< if (($sep = strrpos($pRange, '!')) === false) { < return $returnRange ? ['', $pRange] : '';> if (($sep = strrpos($range, '!')) === false) { > return $returnRange ? ['', $range] : '';if ($returnRange) {< return [substr($pRange, 0, $sep), substr($pRange, $sep + 1)];> return [substr($range, 0, $sep), substr($range, $sep + 1)];}< return substr($pRange, $sep + 1);> return substr($range, $sep + 1);} /** * Get hyperlink. *< * @param string $pCellCoordinate Cell coordinate to get hyperlink for, eg: 'A1'> * @param string $cellCoordinate Cell coordinate to get hyperlink for, eg: 'A1'* * @return Hyperlink */< public function getHyperlink($pCellCoordinate)> public function getHyperlink($cellCoordinate){ // return hyperlink if we already have one< if (isset($this->hyperlinkCollection[$pCellCoordinate])) { < return $this->hyperlinkCollection[$pCellCoordinate];> if (isset($this->hyperlinkCollection[$cellCoordinate])) { > return $this->hyperlinkCollection[$cellCoordinate];} // else create hyperlink< $this->hyperlinkCollection[$pCellCoordinate] = new Hyperlink();> $this->hyperlinkCollection[$cellCoordinate] = new Hyperlink();< return $this->hyperlinkCollection[$pCellCoordinate];> return $this->hyperlinkCollection[$cellCoordinate];} /** * Set hyperlink. *< * @param string $pCellCoordinate Cell coordinate to insert hyperlink, eg: 'A1' < * @param null|Hyperlink $pHyperlink> * @param string $cellCoordinate Cell coordinate to insert hyperlink, eg: 'A1'*< * @return Worksheet> * @return $this*/< public function setHyperlink($pCellCoordinate, Hyperlink $pHyperlink = null)> public function setHyperlink($cellCoordinate, ?Hyperlink $hyperlink = null){< if ($pHyperlink === null) { < unset($this->hyperlinkCollection[$pCellCoordinate]);> if ($hyperlink === null) { > unset($this->hyperlinkCollection[$cellCoordinate]);} else {< $this->hyperlinkCollection[$pCellCoordinate] = $pHyperlink;> $this->hyperlinkCollection[$cellCoordinate] = $hyperlink;} return $this; } /** * Hyperlink at a specific coordinate exists? *< * @param string $pCoordinate eg: 'A1'> * @param string $coordinate eg: 'A1'* * @return bool */< public function hyperlinkExists($pCoordinate)> public function hyperlinkExists($coordinate){< return isset($this->hyperlinkCollection[$pCoordinate]);> return isset($this->hyperlinkCollection[$coordinate]);} /** * Get collection of hyperlinks. * * @return Hyperlink[] */ public function getHyperlinkCollection() { return $this->hyperlinkCollection; } /** * Get data validation. *< * @param string $pCellCoordinate Cell coordinate to get data validation for, eg: 'A1'> * @param string $cellCoordinate Cell coordinate to get data validation for, eg: 'A1'* * @return DataValidation */< public function getDataValidation($pCellCoordinate)> public function getDataValidation($cellCoordinate){ // return data validation if we already have one< if (isset($this->dataValidationCollection[$pCellCoordinate])) { < return $this->dataValidationCollection[$pCellCoordinate];> if (isset($this->dataValidationCollection[$cellCoordinate])) { > return $this->dataValidationCollection[$cellCoordinate];} // else create data validation< $this->dataValidationCollection[$pCellCoordinate] = new DataValidation();> $this->dataValidationCollection[$cellCoordinate] = new DataValidation();< return $this->dataValidationCollection[$pCellCoordinate];> return $this->dataValidationCollection[$cellCoordinate];} /** * Set data validation. *< * @param string $pCellCoordinate Cell coordinate to insert data validation, eg: 'A1' < * @param null|DataValidation $pDataValidation> * @param string $cellCoordinate Cell coordinate to insert data validation, eg: 'A1'*< * @return Worksheet> * @return $this*/< public function setDataValidation($pCellCoordinate, DataValidation $pDataValidation = null)> public function setDataValidation($cellCoordinate, ?DataValidation $dataValidation = null){< if ($pDataValidation === null) { < unset($this->dataValidationCollection[$pCellCoordinate]);> if ($dataValidation === null) { > unset($this->dataValidationCollection[$cellCoordinate]);} else {< $this->dataValidationCollection[$pCellCoordinate] = $pDataValidation;> $this->dataValidationCollection[$cellCoordinate] = $dataValidation;} return $this; } /** * Data validation at a specific coordinate exists? *< * @param string $pCoordinate eg: 'A1'> * @param string $coordinate eg: 'A1'* * @return bool */< public function dataValidationExists($pCoordinate)> public function dataValidationExists($coordinate){< return isset($this->dataValidationCollection[$pCoordinate]);> return isset($this->dataValidationCollection[$coordinate]);} /** * Get collection of data validations. * * @return DataValidation[] */ public function getDataValidationCollection() { return $this->dataValidationCollection; } /** * Accepts a range, returning it as a range that falls within the current highest row and column of the worksheet. * * @param string $range * * @return string Adjusted range value */ public function shrinkRangeToFit($range) { $maxCol = $this->getHighestColumn(); $maxRow = $this->getHighestRow(); $maxCol = Coordinate::columnIndexFromString($maxCol); $rangeBlocks = explode(' ', $range); foreach ($rangeBlocks as &$rangeSet) { $rangeBoundaries = Coordinate::getRangeBoundaries($rangeSet); if (Coordinate::columnIndexFromString($rangeBoundaries[0][0]) > $maxCol) { $rangeBoundaries[0][0] = Coordinate::stringFromColumnIndex($maxCol); } if ($rangeBoundaries[0][1] > $maxRow) { $rangeBoundaries[0][1] = $maxRow; } if (Coordinate::columnIndexFromString($rangeBoundaries[1][0]) > $maxCol) { $rangeBoundaries[1][0] = Coordinate::stringFromColumnIndex($maxCol); } if ($rangeBoundaries[1][1] > $maxRow) { $rangeBoundaries[1][1] = $maxRow; } $rangeSet = $rangeBoundaries[0][0] . $rangeBoundaries[0][1] . ':' . $rangeBoundaries[1][0] . $rangeBoundaries[1][1]; } unset($rangeSet); return implode(' ', $rangeBlocks); } /** * Get tab color. * * @return Color */ public function getTabColor() { if ($this->tabColor === null) { $this->tabColor = new Color(); } return $this->tabColor; } /** * Reset tab color. *< * @return Worksheet> * @return $this*/ public function resetTabColor() { $this->tabColor = null;< unset($this->tabColor);return $this; } /** * Tab color set? * * @return bool */ public function isTabColorSet() { return $this->tabColor !== null; } /** * Copy worksheet (!= clone!). *< * @return Worksheet> * @return static*/ public function copy() { return clone $this; } /**> * Returns a boolean true if the specified row contains no cells. By default, this means that no cell records * Implement PHP __clone to create a deep clone, not just a shallow copy. > * exist in the collection for this row. false will be returned otherwise. */ > * This rule can be modified by passing a $definitionOfEmptyFlags value: public function __clone() > * 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. foreach ($this as $key => $val) { > * 2 - CellIterator::TREAT_EMPTY_STRING_AS_EMPTY_CELL If the only cells in the collection are empty if ($key == 'parent') { > * string value cells, then the row will be considered empty. continue; > * 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. if (is_object($val) || (is_array($val))) { > * if ($key == 'cellCollection') { > * @param int $definitionOfEmptyFlags $newCollection = $this->cellCollection->cloneCellCollection($this); > * Possible Flag Values are: $this->cellCollection = $newCollection; > * CellIterator::TREAT_NULL_VALUE_AS_EMPTY_CELL } elseif ($key == 'drawingCollection') { > * CellIterator::TREAT_EMPTY_STRING_AS_EMPTY_CELL $currentCollection = $this->drawingCollection; > */ $this->drawingCollection = new ArrayObject(); > public function isEmptyRow(int $rowId, int $definitionOfEmptyFlags = 0): bool foreach ($currentCollection as $item) { > { if (is_object($item)) { > try { $newDrawing = clone $item; > $iterator = new RowIterator($this, $rowId, $rowId); $newDrawing->setWorksheet($this); > $iterator->seek($rowId); } > $row = $iterator->current(); } > } catch (Exception $e) { } elseif (($key == 'autoFilter') && ($this->autoFilter instanceof AutoFilter)) { > return true; $newAutoFilter = clone $this->autoFilter; > } $this->autoFilter = $newAutoFilter; > $this->autoFilter->setParent($this); > return $row->isEmpty($definitionOfEmptyFlags); } else { > } $this->{$key} = unserialize(serialize($val)); > } > /** } > * 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. } > * 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. * Define the code name of the sheet. > * 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. * @param string $pValue Same rule as Title minus space not allowed (but, like Excel, change > * 3 - CellIterator::TREAT_NULL_VALUE_AS_EMPTY_CELL | CellIterator::TREAT_EMPTY_STRING_AS_EMPTY_CELL * silently space to underscore) > * If the only cells in the collection are null value or empty string value cells, then the column * @param bool $validate False to skip validation of new title. WARNING: This should only be set > * will be considered empty. * at parse time (by Readers), where titles can be assumed to be valid. > * * > * @param int $definitionOfEmptyFlags * @throws Exception > * Possible Flag Values are: * > * CellIterator::TREAT_NULL_VALUE_AS_EMPTY_CELL * @return Worksheet > * CellIterator::TREAT_EMPTY_STRING_AS_EMPTY_CELL */ > */ public function setCodeName($pValue, $validate = true) > public function isEmptyColumn(string $columnId, int $definitionOfEmptyFlags = 0): bool { > { // Is this a 'rename' or not? > try { if ($this->getCodeName() == $pValue) { > $iterator = new ColumnIterator($this, $columnId, $columnId); return $this; > $iterator->seek($columnId); } > $column = $iterator->current(); > } catch (Exception $e) { if ($validate) { > return true; $pValue = str_replace(' ', '_', $pValue); //Excel does this automatically without flinching, we are doing the same > } > // Syntax check > return $column->isEmpty($definitionOfEmptyFlags); // throw an exception if not valid > } self::checkSheetCodeName($pValue); > > /**// We use the same code that setTitle to find a valid codeName else not using a space (Excel don't like) but a '_'> // @phpstan-ignore-next-line< * @param string $pValue Same rule as Title minus space not allowed (but, like Excel, change> * @param string $codeName Same rule as Title minus space not allowed (but, like Excel, change< * @throws Exception < * < * @return Worksheet> * @return $this< public function setCodeName($pValue, $validate = true)> public function setCodeName($codeName, $validate = true)< if ($this->getCodeName() == $pValue) {> if ($this->getCodeName() == $codeName) {< $pValue = str_replace(' ', '_', $pValue); //Excel does this automatically without flinching, we are doing the same> $codeName = str_replace(' ', '_', $codeName); //Excel does this automatically without flinching, we are doing the same< self::checkSheetCodeName($pValue);> self::checkSheetCodeName($codeName);< if ($this->getParent()) {> if ($this->parent !== null) {< if ($this->getParent()->sheetCodeNameExists($pValue)) {> if ($this->parent->sheetCodeNameExists($codeName)) {< if (Shared\StringHelper::countCharacters($pValue) > 29) { < $pValue = Shared\StringHelper::substring($pValue, 0, 29);> if (Shared\StringHelper::countCharacters($codeName) > 29) { > $codeName = Shared\StringHelper::substring($codeName, 0, 29);< while ($this->getParent()->sheetCodeNameExists($pValue . '_' . $i)) {> while ($this->getParentOrThrow()->sheetCodeNameExists($codeName . '_' . $i)) {< if (Shared\StringHelper::countCharacters($pValue) > 28) { < $pValue = Shared\StringHelper::substring($pValue, 0, 28);> if (Shared\StringHelper::countCharacters($codeName) > 28) { > $codeName = Shared\StringHelper::substring($codeName, 0, 28);} } elseif ($i == 100) {< if (Shared\StringHelper::countCharacters($pValue) > 27) { < $pValue = Shared\StringHelper::substring($pValue, 0, 27);> if (Shared\StringHelper::countCharacters($codeName) > 27) { > $codeName = Shared\StringHelper::substring($codeName, 0, 27);} } }< $pValue .= '_' . $i; // ok, we have a valid name> $codeName .= '_' . $i; // ok, we have a valid name} } }< $this->codeName = $pValue;> $this->codeName = $codeName;return $this; } /** * Return the code name of the sheet. * * @return null|string */ public function getCodeName() { return $this->codeName; } /** * Sheet has a code name ? * * @return bool */ public function hasCodeName() { return $this->codeName !== null;> } } > } > public static function nameRequiresQuotes(string $sheetName): bool > { > return preg_match(self::SHEET_NAME_REQUIRES_NO_QUOTES, $sheetName) !== 1;