See Release Notes
Long Term Support Release
<?php namespace PhpOffice\PhpSpreadsheet;> use PhpOffice\PhpSpreadsheet\Calculation\Calculation;use PhpOffice\PhpSpreadsheet\Cell\Coordinate; use PhpOffice\PhpSpreadsheet\Cell\DataType;> use PhpOffice\PhpSpreadsheet\Style\Conditional; use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet; > use PhpOffice\PhpSpreadsheet\Worksheet\AutoFilter; > use PhpOffice\PhpSpreadsheet\Worksheet\Table;class ReferenceHelper { /** Constants */ /** Regular Expressions */ const REFHELPER_REGEXP_CELLREF = '((\w*|\'[^!]*\')!)?(?<![:a-z\$])(\$?[a-z]{1,3}\$?\d+)(?=[^:!\d\'])'; const REFHELPER_REGEXP_CELLRANGE = '((\w*|\'[^!]*\')!)?(\$?[a-z]{1,3}\$?\d+):(\$?[a-z]{1,3}\$?\d+)'; const REFHELPER_REGEXP_ROWRANGE = '((\w*|\'[^!]*\')!)?(\$?\d+):(\$?\d+)'; const REFHELPER_REGEXP_COLRANGE = '((\w*|\'[^!]*\')!)?(\$?[a-z]{1,3}):(\$?[a-z]{1,3})'; /** * Instance of this class. *< * @var ReferenceHelper> * @var ?ReferenceHelper*/ private static $instance; /**> * @var CellReferenceHelper * Get an instance of this class. > */ * > private $cellReferenceHelper; * @return ReferenceHelper > */ > /**public static function getInstance() {< if (!isset(self::$instance) || (self::$instance === null)) {> if (self::$instance === null) {self::$instance = new self(); } return self::$instance; } /** * Create a new ReferenceHelper. */ protected function __construct() { } /** * Compare two column addresses * Intended for use as a Callback function for sorting column addresses by column. * * @param string $a First column to test (e.g. 'AA') * @param string $b Second column to test (e.g. 'Z') * * @return int */ public static function columnSort($a, $b) { return strcasecmp(strlen($a) . $a, strlen($b) . $b); } /** * Compare two column addresses * Intended for use as a Callback function for reverse sorting column addresses by column. * * @param string $a First column to test (e.g. 'AA') * @param string $b Second column to test (e.g. 'Z') * * @return int */ public static function columnReverseSort($a, $b) {< return 1 - strcasecmp(strlen($a) . $a, strlen($b) . $b);> return -strcasecmp(strlen($a) . $a, strlen($b) . $b);}> /** @var int */ /** > private static $scrutinizer0 = 0; * Compare two cell addresses >* Intended for use as a Callback function for sorting cell addresses by column and row. * * @param string $a First cell to test (e.g. 'AA1') * @param string $b Second cell to test (e.g. 'Z1') * * @return int */ public static function cellSort($a, $b) {< [$ac, $ar] = sscanf($a, '%[A-Z]%d'); < [$bc, $br] = sscanf($b, '%[A-Z]%d');> $ac = $bc = ''; > $ar = self::$scrutinizer0; > $br = 0; > sscanf($a, '%[A-Z]%d', $ac, $ar); > sscanf($b, '%[A-Z]%d', $bc, $br);> $ac = (string) $ac; if ($ar === $br) { > $bc = (string) $bc;return strcasecmp(strlen($ac) . $ac, strlen($bc) . $bc); } return ($ar < $br) ? -1 : 1; } /** * Compare two cell addresses * Intended for use as a Callback function for sorting cell addresses by column and row. * * @param string $a First cell to test (e.g. 'AA1') * @param string $b Second cell to test (e.g. 'Z1') * * @return int */ public static function cellReverseSort($a, $b) {< [$ac, $ar] = sscanf($a, '%[A-Z]%d'); < [$bc, $br] = sscanf($b, '%[A-Z]%d');> $ac = $bc = ''; > $ar = self::$scrutinizer0; > $br = 0; > sscanf($a, '%[A-Z]%d', $ac, $ar); > sscanf($b, '%[A-Z]%d', $bc, $br);> $ac = (string) $ac; if ($ar === $br) { > $bc = (string) $bc;< return 1 - strcasecmp(strlen($ac) . $ac, strlen($bc) . $bc);> return -strcasecmp(strlen($ac) . $ac, strlen($bc) . $bc);} return ($ar < $br) ? 1 : -1; } /**< * Test whether a cell address falls within a defined range of cells. < * < * @param string $cellAddress Address of the cell we're testing < * @param int $beforeRow Number of the row we're inserting/deleting before < * @param int $pNumRows Number of rows to insert/delete (negative values indicate deletion) < * @param int $beforeColumnIndex Index number of the column we're inserting/deleting before < * @param int $pNumCols Number of columns to insert/delete (negative values indicate deletion) < * < * @return bool < */ < private static function cellAddressInDeleteRange($cellAddress, $beforeRow, $pNumRows, $beforeColumnIndex, $pNumCols) < { < [$cellColumn, $cellRow] = Coordinate::coordinateFromString($cellAddress); < $cellColumnIndex = Coordinate::columnIndexFromString($cellColumn); < // Is cell within the range of rows/columns if we're deleting < if ($pNumRows < 0 && < ($cellRow >= ($beforeRow + $pNumRows)) && < ($cellRow < $beforeRow)) { < return true; < } elseif ($pNumCols < 0 && < ($cellColumnIndex >= ($beforeColumnIndex + $pNumCols)) && < ($cellColumnIndex < $beforeColumnIndex)) { < return true; < } < < return false; < } < < /*** Update page breaks when inserting/deleting rows/columns. *< * @param Worksheet $pSheet The worksheet that we're editing < * @param string $pBefore Insert/Delete before this cell address (e.g. 'A1') < * @param int $beforeColumnIndex Index number of the column we're inserting/deleting before < * @param int $pNumCols Number of columns to insert/delete (negative values indicate deletion) < * @param int $beforeRow Number of the row we're inserting/deleting before < * @param int $pNumRows Number of rows to insert/delete (negative values indicate deletion) < */ < protected function adjustPageBreaks(Worksheet $pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows) < { < $aBreaks = $pSheet->getBreaks(); < ($pNumCols > 0 || $pNumRows > 0) ? < uksort($aBreaks, ['self', 'cellReverseSort']) : uksort($aBreaks, ['self', 'cellSort']);> * @param Worksheet $worksheet The worksheet that we're editing > * @param int $numberOfColumns Number of columns to insert/delete (negative values indicate deletion) > * @param int $numberOfRows Number of rows to insert/delete (negative values indicate deletion) > */ > protected function adjustPageBreaks(Worksheet $worksheet, $numberOfColumns, $numberOfRows): void > { > $aBreaks = $worksheet->getBreaks(); > ($numberOfColumns > 0 || $numberOfRows > 0) > ? uksort($aBreaks, [self::class, 'cellReverseSort']) > : uksort($aBreaks, [self::class, 'cellSort']);< foreach ($aBreaks as $key => $value) { < if (self::cellAddressInDeleteRange($key, $beforeRow, $pNumRows, $beforeColumnIndex, $pNumCols)) {> foreach ($aBreaks as $cellAddress => $value) { > if ($this->cellReferenceHelper->cellAddressInDeleteRange($cellAddress) === true) {// If we're deleting, then clear any defined breaks that are within the range // of rows/columns that we're deleting< $pSheet->setBreak($key, Worksheet::BREAK_NONE);> $worksheet->setBreak($cellAddress, Worksheet::BREAK_NONE);} else { // Otherwise update any affected breaks by inserting a new break at the appropriate point // and removing the old affected break< $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows); < if ($key != $newReference) { < $pSheet->setBreak($newReference, $value) < ->setBreak($key, Worksheet::BREAK_NONE);> $newReference = $this->updateCellReference($cellAddress); > if ($cellAddress !== $newReference) { > $worksheet->setBreak($newReference, $value) > ->setBreak($cellAddress, Worksheet::BREAK_NONE);} } } } /** * Update cell comments when inserting/deleting rows/columns. *< * @param Worksheet $pSheet The worksheet that we're editing < * @param string $pBefore Insert/Delete before this cell address (e.g. 'A1') < * @param int $beforeColumnIndex Index number of the column we're inserting/deleting before < * @param int $pNumCols Number of columns to insert/delete (negative values indicate deletion) < * @param int $beforeRow Number of the row we're inserting/deleting before < * @param int $pNumRows Number of rows to insert/delete (negative values indicate deletion)> * @param Worksheet $worksheet The worksheet that we're editing*/< protected function adjustComments($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)> protected function adjustComments($worksheet): void{< $aComments = $pSheet->getComments();> $aComments = $worksheet->getComments();$aNewComments = []; // the new array of all comments< foreach ($aComments as $key => &$value) {> foreach ($aComments as $cellAddress => &$value) {// Any comments inside a deleted range will be ignored< if (!self::cellAddressInDeleteRange($key, $beforeRow, $pNumRows, $beforeColumnIndex, $pNumCols)) {> if ($this->cellReferenceHelper->cellAddressInDeleteRange($cellAddress) === false) {// Otherwise build a new array of comments indexed by the adjusted cell reference< $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);> $newReference = $this->updateCellReference($cellAddress);$aNewComments[$newReference] = $value; } } // Replace the comments array with the new set of comments< $pSheet->setComments($aNewComments);> $worksheet->setComments($aNewComments);} /** * Update hyperlinks when inserting/deleting rows/columns. *< * @param Worksheet $pSheet The worksheet that we're editing < * @param string $pBefore Insert/Delete before this cell address (e.g. 'A1') < * @param int $beforeColumnIndex Index number of the column we're inserting/deleting before < * @param int $pNumCols Number of columns to insert/delete (negative values indicate deletion) < * @param int $beforeRow Number of the row we're inserting/deleting before < * @param int $pNumRows Number of rows to insert/delete (negative values indicate deletion)> * @param Worksheet $worksheet The worksheet that we're editing > * @param int $numberOfColumns Number of columns to insert/delete (negative values indicate deletion) > * @param int $numberOfRows Number of rows to insert/delete (negative values indicate deletion)*/< protected function adjustHyperlinks($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)> protected function adjustHyperlinks($worksheet, $numberOfColumns, $numberOfRows): void{< $aHyperlinkCollection = $pSheet->getHyperlinkCollection(); < ($pNumCols > 0 || $pNumRows > 0) ? < uksort($aHyperlinkCollection, ['self', 'cellReverseSort']) : uksort($aHyperlinkCollection, ['self', 'cellSort']); < < foreach ($aHyperlinkCollection as $key => $value) { < $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows); < if ($key != $newReference) { < $pSheet->setHyperlink($newReference, $value); < $pSheet->setHyperlink($key, null);> $aHyperlinkCollection = $worksheet->getHyperlinkCollection(); > ($numberOfColumns > 0 || $numberOfRows > 0) > ? uksort($aHyperlinkCollection, [self::class, 'cellReverseSort']) > : uksort($aHyperlinkCollection, [self::class, 'cellSort']); > > foreach ($aHyperlinkCollection as $cellAddress => $value) { > $newReference = $this->updateCellReference($cellAddress); > if ($this->cellReferenceHelper->cellAddressInDeleteRange($cellAddress) === true) { > $worksheet->setHyperlink($cellAddress, null); > } elseif ($cellAddress !== $newReference) { > $worksheet->setHyperlink($newReference, $value); > $worksheet->setHyperlink($cellAddress, null);} } } /**< * Update data validations when inserting/deleting rows/columns.> * Update conditional formatting styles when inserting/deleting rows/columns.*< * @param Worksheet $pSheet The worksheet that we're editing < * @param string $pBefore Insert/Delete before this cell address (e.g. 'A1') < * @param int $beforeColumnIndex Index number of the column we're inserting/deleting before < * @param int $pNumCols Number of columns to insert/delete (negative values indicate deletion) < * @param int $beforeRow Number of the row we're inserting/deleting before < * @param int $pNumRows Number of rows to insert/delete (negative values indicate deletion)> * @param Worksheet $worksheet The worksheet that we're editing > * @param int $numberOfColumns Number of columns to insert/delete (negative values indicate deletion) > * @param int $numberOfRows Number of rows to insert/delete (negative values indicate deletion)*/< protected function adjustDataValidations($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)> protected function adjustConditionalFormatting($worksheet, $numberOfColumns, $numberOfRows): void{< $aDataValidationCollection = $pSheet->getDataValidationCollection(); < ($pNumCols > 0 || $pNumRows > 0) ? < uksort($aDataValidationCollection, ['self', 'cellReverseSort']) : uksort($aDataValidationCollection, ['self', 'cellSort']); < < foreach ($aDataValidationCollection as $key => $value) { < $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows); < if ($key != $newReference) { < $pSheet->setDataValidation($newReference, $value); < $pSheet->setDataValidation($key, null);> $aStyles = $worksheet->getConditionalStylesCollection(); > ($numberOfColumns > 0 || $numberOfRows > 0) > ? uksort($aStyles, [self::class, 'cellReverseSort']) > : uksort($aStyles, [self::class, 'cellSort']); > > foreach ($aStyles as $cellAddress => $cfRules) { > $worksheet->removeConditionalStyles($cellAddress); > $newReference = $this->updateCellReference($cellAddress); > > foreach ($cfRules as &$cfRule) { > /** @var Conditional $cfRule */ > $conditions = $cfRule->getConditions(); > foreach ($conditions as &$condition) { > if (is_string($condition)) { > $condition = $this->updateFormulaReferences( > $condition, > $this->cellReferenceHelper->beforeCellAddress(), > $numberOfColumns, > $numberOfRows, > $worksheet->getTitle(), > true > ); > } > } > $cfRule->setConditions($conditions); > } > $worksheet->setConditionalStyles($newReference, $cfRules); > } > } > > /** > * Update data validations when inserting/deleting rows/columns. > * > * @param Worksheet $worksheet The worksheet that we're editing > * @param int $numberOfColumns Number of columns to insert/delete (negative values indicate deletion) > * @param int $numberOfRows Number of rows to insert/delete (negative values indicate deletion) > */ > protected function adjustDataValidations(Worksheet $worksheet, $numberOfColumns, $numberOfRows): void > { > $aDataValidationCollection = $worksheet->getDataValidationCollection(); > ($numberOfColumns > 0 || $numberOfRows > 0) > ? uksort($aDataValidationCollection, [self::class, 'cellReverseSort']) > : uksort($aDataValidationCollection, [self::class, 'cellSort']); > > foreach ($aDataValidationCollection as $cellAddress => $dataValidation) { > $newReference = $this->updateCellReference($cellAddress); > if ($cellAddress !== $newReference) { > $dataValidation->setSqref($newReference); > $worksheet->setDataValidation($newReference, $dataValidation); > $worksheet->setDataValidation($cellAddress, null);} } } /** * Update merged cells when inserting/deleting rows/columns. *< * @param Worksheet $pSheet The worksheet that we're editing < * @param string $pBefore Insert/Delete before this cell address (e.g. 'A1') < * @param int $beforeColumnIndex Index number of the column we're inserting/deleting before < * @param int $pNumCols Number of columns to insert/delete (negative values indicate deletion) < * @param int $beforeRow Number of the row we're inserting/deleting before < * @param int $pNumRows Number of rows to insert/delete (negative values indicate deletion)> * @param Worksheet $worksheet The worksheet that we're editing*/< protected function adjustMergeCells($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)> protected function adjustMergeCells(Worksheet $worksheet): void{< $aMergeCells = $pSheet->getMergeCells();> $aMergeCells = $worksheet->getMergeCells();$aNewMergeCells = []; // the new array of all merge cells< foreach ($aMergeCells as $key => &$value) { < $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);> foreach ($aMergeCells as $cellAddress => &$value) { > $newReference = $this->updateCellReference($cellAddress);$aNewMergeCells[$newReference] = $newReference; }< $pSheet->setMergeCells($aNewMergeCells); // replace the merge cells array> $worksheet->setMergeCells($aNewMergeCells); // replace the merge cells array} /** * Update protected cells when inserting/deleting rows/columns. *< * @param Worksheet $pSheet The worksheet that we're editing < * @param string $pBefore Insert/Delete before this cell address (e.g. 'A1') < * @param int $beforeColumnIndex Index number of the column we're inserting/deleting before < * @param int $pNumCols Number of columns to insert/delete (negative values indicate deletion) < * @param int $beforeRow Number of the row we're inserting/deleting before < * @param int $pNumRows Number of rows to insert/delete (negative values indicate deletion) < */ < protected function adjustProtectedCells($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows) < { < $aProtectedCells = $pSheet->getProtectedCells(); < ($pNumCols > 0 || $pNumRows > 0) ? < uksort($aProtectedCells, ['self', 'cellReverseSort']) : uksort($aProtectedCells, ['self', 'cellSort']); < foreach ($aProtectedCells as $key => $value) { < $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows); < if ($key != $newReference) { < $pSheet->protectCells($newReference, $value, true); < $pSheet->unprotectCells($key);> * @param Worksheet $worksheet The worksheet that we're editing > * @param int $numberOfColumns Number of columns to insert/delete (negative values indicate deletion) > * @param int $numberOfRows Number of rows to insert/delete (negative values indicate deletion) > */ > protected function adjustProtectedCells(Worksheet $worksheet, $numberOfColumns, $numberOfRows): void > { > $aProtectedCells = $worksheet->getProtectedCells(); > ($numberOfColumns > 0 || $numberOfRows > 0) > ? uksort($aProtectedCells, [self::class, 'cellReverseSort']) > : uksort($aProtectedCells, [self::class, 'cellSort']); > foreach ($aProtectedCells as $cellAddress => $value) { > $newReference = $this->updateCellReference($cellAddress); > if ($cellAddress !== $newReference) { > $worksheet->protectCells($newReference, $value, true); > $worksheet->unprotectCells($cellAddress);} } } /** * Update column dimensions when inserting/deleting rows/columns. *< * @param Worksheet $pSheet The worksheet that we're editing < * @param string $pBefore Insert/Delete before this cell address (e.g. 'A1') < * @param int $beforeColumnIndex Index number of the column we're inserting/deleting before < * @param int $pNumCols Number of columns to insert/delete (negative values indicate deletion) < * @param int $beforeRow Number of the row we're inserting/deleting before < * @param int $pNumRows Number of rows to insert/delete (negative values indicate deletion)> * @param Worksheet $worksheet The worksheet that we're editing*/< protected function adjustColumnDimensions($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)> protected function adjustColumnDimensions(Worksheet $worksheet): void{< $aColumnDimensions = array_reverse($pSheet->getColumnDimensions(), true);> $aColumnDimensions = array_reverse($worksheet->getColumnDimensions(), true);if (!empty($aColumnDimensions)) { foreach ($aColumnDimensions as $objColumnDimension) {< $newReference = $this->updateCellReference($objColumnDimension->getColumnIndex() . '1', $pBefore, $pNumCols, $pNumRows);> $newReference = $this->updateCellReference($objColumnDimension->getColumnIndex() . '1');[$newReference] = Coordinate::coordinateFromString($newReference);< if ($objColumnDimension->getColumnIndex() != $newReference) {> if ($objColumnDimension->getColumnIndex() !== $newReference) {$objColumnDimension->setColumnIndex($newReference); } }< $pSheet->refreshColumnDimensions();> > $worksheet->refreshColumnDimensions();} } /** * Update row dimensions when inserting/deleting rows/columns. *< * @param Worksheet $pSheet The worksheet that we're editing < * @param string $pBefore Insert/Delete before this cell address (e.g. 'A1') < * @param int $beforeColumnIndex Index number of the column we're inserting/deleting before < * @param int $pNumCols Number of columns to insert/delete (negative values indicate deletion)> * @param Worksheet $worksheet The worksheet that we're editing* @param int $beforeRow Number of the row we're inserting/deleting before< * @param int $pNumRows Number of rows to insert/delete (negative values indicate deletion)> * @param int $numberOfRows Number of rows to insert/delete (negative values indicate deletion)*/< protected function adjustRowDimensions($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)> protected function adjustRowDimensions(Worksheet $worksheet, $beforeRow, $numberOfRows): void{< $aRowDimensions = array_reverse($pSheet->getRowDimensions(), true);> $aRowDimensions = array_reverse($worksheet->getRowDimensions(), true);if (!empty($aRowDimensions)) { foreach ($aRowDimensions as $objRowDimension) {< $newReference = $this->updateCellReference('A' . $objRowDimension->getRowIndex(), $pBefore, $pNumCols, $pNumRows);> $newReference = $this->updateCellReference('A' . $objRowDimension->getRowIndex());[, $newReference] = Coordinate::coordinateFromString($newReference);< if ($objRowDimension->getRowIndex() != $newReference) { < $objRowDimension->setRowIndex($newReference);> $newRoweference = (int) $newReference; > if ($objRowDimension->getRowIndex() !== $newRoweference) { > $objRowDimension->setRowIndex($newRoweference);} }< $pSheet->refreshRowDimensions();< $copyDimension = $pSheet->getRowDimension($beforeRow - 1); < for ($i = $beforeRow; $i <= $beforeRow - 1 + $pNumRows; ++$i) { < $newDimension = $pSheet->getRowDimension($i);> $worksheet->refreshRowDimensions(); > > $copyDimension = $worksheet->getRowDimension($beforeRow - 1); > for ($i = $beforeRow; $i <= $beforeRow - 1 + $numberOfRows; ++$i) { > $newDimension = $worksheet->getRowDimension($i);$newDimension->setRowHeight($copyDimension->getRowHeight()); $newDimension->setVisible($copyDimension->getVisible()); $newDimension->setOutlineLevel($copyDimension->getOutlineLevel()); $newDimension->setCollapsed($copyDimension->getCollapsed()); } } } /** * Insert a new column or row, updating all possible related data. *< * @param string $pBefore Insert before this cell address (e.g. 'A1') < * @param int $pNumCols Number of columns to insert/delete (negative values indicate deletion) < * @param int $pNumRows Number of rows to insert/delete (negative values indicate deletion) < * @param Worksheet $pSheet The worksheet that we're editing < * < * @throws Exception < */ < public function insertNewBefore($pBefore, $pNumCols, $pNumRows, Worksheet $pSheet) < { < $remove = ($pNumCols < 0 || $pNumRows < 0); < $allCoordinates = $pSheet->getCoordinates();> * @param string $beforeCellAddress Insert before this cell address (e.g. 'A1') > * @param int $numberOfColumns Number of columns to insert/delete (negative values indicate deletion) > * @param int $numberOfRows Number of rows to insert/delete (negative values indicate deletion) > * @param Worksheet $worksheet The worksheet that we're editing > */ > public function insertNewBefore( > string $beforeCellAddress, > int $numberOfColumns, > int $numberOfRows, > Worksheet $worksheet > ): void { > $remove = ($numberOfColumns < 0 || $numberOfRows < 0); > > if ( > $this->cellReferenceHelper === null || > $this->cellReferenceHelper->refreshRequired($beforeCellAddress, $numberOfColumns, $numberOfRows) > ) { > $this->cellReferenceHelper = new CellReferenceHelper($beforeCellAddress, $numberOfColumns, $numberOfRows); > }< // Get coordinate of $pBefore < [$beforeColumn, $beforeRow] = Coordinate::coordinateFromString($pBefore); < $beforeColumnIndex = Coordinate::columnIndexFromString($beforeColumn);> // Get coordinate of $beforeCellAddress > [$beforeColumn, $beforeRow] = Coordinate::indexesFromString($beforeCellAddress);// Clear cells if we are removing columns or rows< $highestColumn = $pSheet->getHighestColumn(); < $highestRow = $pSheet->getHighestRow();> $highestColumn = $worksheet->getHighestColumn(); > $highestRow = $worksheet->getHighestRow();// 1. Clear column strips if we are removing columns< if ($pNumCols < 0 && $beforeColumnIndex - 2 + $pNumCols > 0) { < for ($i = 1; $i <= $highestRow - 1; ++$i) { < for ($j = $beforeColumnIndex - 1 + $pNumCols; $j <= $beforeColumnIndex - 2; ++$j) { < $coordinate = Coordinate::stringFromColumnIndex($j + 1) . $i; < $pSheet->removeConditionalStyles($coordinate); < if ($pSheet->cellExists($coordinate)) { < $pSheet->getCell($coordinate)->setValueExplicit('', DataType::TYPE_NULL); < $pSheet->getCell($coordinate)->setXfIndex(0); < } < } < }> if ($numberOfColumns < 0 && $beforeColumn - 2 + $numberOfColumns > 0) { > $this->clearColumnStrips($highestRow, $beforeColumn, $numberOfColumns, $worksheet);} // 2. Clear row strips if we are removing rows< if ($pNumRows < 0 && $beforeRow - 1 + $pNumRows > 0) { < for ($i = $beforeColumnIndex - 1; $i <= Coordinate::columnIndexFromString($highestColumn) - 1; ++$i) { < for ($j = $beforeRow + $pNumRows; $j <= $beforeRow - 1; ++$j) { < $coordinate = Coordinate::stringFromColumnIndex($i + 1) . $j; < $pSheet->removeConditionalStyles($coordinate); < if ($pSheet->cellExists($coordinate)) { < $pSheet->getCell($coordinate)->setValueExplicit('', DataType::TYPE_NULL); < $pSheet->getCell($coordinate)->setXfIndex(0);> if ($numberOfRows < 0 && $beforeRow - 1 + $numberOfRows > 0) { > $this->clearRowStrips($highestColumn, $beforeColumn, $beforeRow, $numberOfRows, $worksheet);}> } > // Find missing coordinates. This is important when inserting column before the last column } > $cellCollection = $worksheet->getCellCollection(); } > $missingCoordinates = array_filter( > array_map(function ($row) use ($highestColumn) { // Loop through cells, bottom-up, and change cell coordinate > return $highestColumn . $row; if ($remove) { > }, range(1, $highestRow)), // It's faster to reverse and pop than to use unshift, especially with large cell collections > function ($coordinate) use ($cellCollection) { $allCoordinates = array_reverse($allCoordinates); > return $cellCollection->has($coordinate) === false;}> ); while ($coordinate = array_pop($allCoordinates)) { > $cell = $pSheet->getCell($coordinate); > // Create missing cells with null values $cellIndex = Coordinate::columnIndexFromString($cell->getColumn()); > if (!empty($missingCoordinates)) { > foreach ($missingCoordinates as $coordinate) { if ($cellIndex - 1 + $pNumCols < 0) { > $worksheet->createNewCell($coordinate);< // Loop through cells, bottom-up, and change cell coordinate> $allCoordinates = $worksheet->getCoordinates();}> > // Loop through cells, bottom-up, and change cell coordinate< $cell = $pSheet->getCell($coordinate);> $cell = $worksheet->getCell($coordinate);< if ($cellIndex - 1 + $pNumCols < 0) {> if ($cellIndex - 1 + $numberOfColumns < 0) {< $newCoordinate = Coordinate::stringFromColumnIndex($cellIndex + $pNumCols) . ($cell->getRow() + $pNumRows);> $newCoordinate = Coordinate::stringFromColumnIndex($cellIndex + $numberOfColumns) . ($cell->getRow() + $numberOfRows);// Should the cell be updated? Move value and cellXf index from one cell to another.< if (($cellIndex >= $beforeColumnIndex) && ($cell->getRow() >= $beforeRow)) {> if (($cellIndex >= $beforeColumn) && ($cell->getRow() >= $beforeRow)) {// Update cell styles< $pSheet->getCell($newCoordinate)->setXfIndex($cell->getXfIndex());> $worksheet->getCell($newCoordinate)->setXfIndex($cell->getXfIndex());// Insert this cell at its new location< if ($cell->getDataType() == DataType::TYPE_FORMULA) {> if ($cell->getDataType() === DataType::TYPE_FORMULA) {// Formula should be adjusted< $pSheet->getCell($newCoordinate) < ->setValue($this->updateFormulaReferences($cell->getValue(), $pBefore, $pNumCols, $pNumRows, $pSheet->getTitle()));> $worksheet->getCell($newCoordinate) > ->setValue($this->updateFormulaReferences($cell->getValue(), $beforeCellAddress, $numberOfColumns, $numberOfRows, $worksheet->getTitle()));} else { // Formula should not be adjusted< $pSheet->getCell($newCoordinate)->setValue($cell->getValue());> $worksheet->getCell($newCoordinate)->setValueExplicit($cell->getValue(), $cell->getDataType());} // Clear the original cell< $pSheet->getCellCollection()->delete($coordinate);> $worksheet->getCellCollection()->delete($coordinate);} else { /* We don't need to update styles for rows/columns before our insertion position, but we do still need to adjust any formulae in those cells */< if ($cell->getDataType() == DataType::TYPE_FORMULA) {> if ($cell->getDataType() === DataType::TYPE_FORMULA) {// Formula should be adjusted< $cell->setValue($this->updateFormulaReferences($cell->getValue(), $pBefore, $pNumCols, $pNumRows, $pSheet->getTitle()));> $cell->setValue($this->updateFormulaReferences($cell->getValue(), $beforeCellAddress, $numberOfColumns, $numberOfRows, $worksheet->getTitle()));} } } // Duplicate styles for the newly inserted cells< $highestColumn = $pSheet->getHighestColumn(); < $highestRow = $pSheet->getHighestRow();> $highestColumn = $worksheet->getHighestColumn(); > $highestRow = $worksheet->getHighestRow();< if ($pNumCols > 0 && $beforeColumnIndex - 2 > 0) { < for ($i = $beforeRow; $i <= $highestRow - 1; ++$i) { < // Style < $coordinate = Coordinate::stringFromColumnIndex($beforeColumnIndex - 1) . $i; < if ($pSheet->cellExists($coordinate)) { < $xfIndex = $pSheet->getCell($coordinate)->getXfIndex(); < $conditionalStyles = $pSheet->conditionalStylesExists($coordinate) ? < $pSheet->getConditionalStyles($coordinate) : false; < for ($j = $beforeColumnIndex; $j <= $beforeColumnIndex - 1 + $pNumCols; ++$j) { < $pSheet->getCellByColumnAndRow($j, $i)->setXfIndex($xfIndex); < if ($conditionalStyles) { < $cloned = []; < foreach ($conditionalStyles as $conditionalStyle) { < $cloned[] = clone $conditionalStyle; < } < $pSheet->setConditionalStyles(Coordinate::stringFromColumnIndex($j) . $i, $cloned); < } < } < } < }> if ($numberOfColumns > 0 && $beforeColumn - 2 > 0) { > $this->duplicateStylesByColumn($worksheet, $beforeColumn, $beforeRow, $highestRow, $numberOfColumns);}< if ($pNumRows > 0 && $beforeRow - 1 > 0) { < for ($i = $beforeColumnIndex; $i <= Coordinate::columnIndexFromString($highestColumn); ++$i) { < // Style < $coordinate = Coordinate::stringFromColumnIndex($i) . ($beforeRow - 1); < if ($pSheet->cellExists($coordinate)) { < $xfIndex = $pSheet->getCell($coordinate)->getXfIndex(); < $conditionalStyles = $pSheet->conditionalStylesExists($coordinate) ? < $pSheet->getConditionalStyles($coordinate) : false; < for ($j = $beforeRow; $j <= $beforeRow - 1 + $pNumRows; ++$j) { < $pSheet->getCell(Coordinate::stringFromColumnIndex($i) . $j)->setXfIndex($xfIndex); < if ($conditionalStyles) { < $cloned = []; < foreach ($conditionalStyles as $conditionalStyle) { < $cloned[] = clone $conditionalStyle; < } < $pSheet->setConditionalStyles(Coordinate::stringFromColumnIndex($i) . $j, $cloned); < } < } < } < }> if ($numberOfRows > 0 && $beforeRow - 1 > 0) { > $this->duplicateStylesByRow($worksheet, $beforeColumn, $beforeRow, $highestColumn, $numberOfRows);} // Update worksheet: column dimensions< $this->adjustColumnDimensions($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);> $this->adjustColumnDimensions($worksheet);// Update worksheet: row dimensions< $this->adjustRowDimensions($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);> $this->adjustRowDimensions($worksheet, $beforeRow, $numberOfRows);// Update worksheet: page breaks< $this->adjustPageBreaks($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);> $this->adjustPageBreaks($worksheet, $numberOfColumns, $numberOfRows);// Update worksheet: comments< $this->adjustComments($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);> $this->adjustComments($worksheet);// Update worksheet: hyperlinks< $this->adjustHyperlinks($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);> $this->adjustHyperlinks($worksheet, $numberOfColumns, $numberOfRows); > > // Update worksheet: conditional formatting styles > $this->adjustConditionalFormatting($worksheet, $numberOfColumns, $numberOfRows);// Update worksheet: data validations< $this->adjustDataValidations($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);> $this->adjustDataValidations($worksheet, $numberOfColumns, $numberOfRows);// Update worksheet: merge cells< $this->adjustMergeCells($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);> $this->adjustMergeCells($worksheet);// Update worksheet: protected cells< $this->adjustProtectedCells($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);> $this->adjustProtectedCells($worksheet, $numberOfColumns, $numberOfRows);// Update worksheet: autofilter< $autoFilter = $pSheet->getAutoFilter(); < $autoFilterRange = $autoFilter->getRange(); < if (!empty($autoFilterRange)) { < if ($pNumCols != 0) { < $autoFilterColumns = $autoFilter->getColumns(); < if (count($autoFilterColumns) > 0) { < $column = ''; < $row = 0; < sscanf($pBefore, '%[A-Z]%d', $column, $row); < $columnIndex = Coordinate::columnIndexFromString($column); < [$rangeStart, $rangeEnd] = Coordinate::rangeBoundaries($autoFilterRange); < if ($columnIndex <= $rangeEnd[0]) { < if ($pNumCols < 0) { < // If we're actually deleting any columns that fall within the autofilter range, < // then we delete any rules for those columns < $deleteColumn = $columnIndex + $pNumCols - 1; < $deleteCount = abs($pNumCols); < for ($i = 1; $i <= $deleteCount; ++$i) { < if (isset($autoFilterColumns[Coordinate::stringFromColumnIndex($deleteColumn + 1)])) { < $autoFilter->clearColumn(Coordinate::stringFromColumnIndex($deleteColumn + 1)); < } < ++$deleteColumn; < } < } < $startCol = ($columnIndex > $rangeStart[0]) ? $columnIndex : $rangeStart[0];> $this->adjustAutoFilter($worksheet, $beforeCellAddress, $numberOfColumns);< // Shuffle columns in autofilter range < if ($pNumCols > 0) { < $startColRef = $startCol; < $endColRef = $rangeEnd[0]; < $toColRef = $rangeEnd[0] + $pNumCols; < < do { < $autoFilter->shiftColumn(Coordinate::stringFromColumnIndex($endColRef), Coordinate::stringFromColumnIndex($toColRef)); < --$endColRef; < --$toColRef; < } while ($startColRef <= $endColRef); < } else { < // For delete, we shuffle from beginning to end to avoid overwriting < $startColID = Coordinate::stringFromColumnIndex($startCol); < $toColID = Coordinate::stringFromColumnIndex($startCol + $pNumCols); < $endColID = Coordinate::stringFromColumnIndex($rangeEnd[0] + 1); < do { < $autoFilter->shiftColumn($startColID, $toColID); < ++$startColID; < ++$toColID; < } while ($startColID != $endColID); < } < } < } < } < $pSheet->setAutoFilter($this->updateCellReference($autoFilterRange, $pBefore, $pNumCols, $pNumRows)); < }> // Update worksheet: table > $this->adjustTable($worksheet, $beforeCellAddress, $numberOfColumns);// Update worksheet: freeze pane< if ($pSheet->getFreezePane()) { < $splitCell = $pSheet->getFreezePane(); < $topLeftCell = $pSheet->getTopLeftCell();> if ($worksheet->getFreezePane()) { > $splitCell = $worksheet->getFreezePane(); > $topLeftCell = $worksheet->getTopLeftCell() ?? '';< $splitCell = $this->updateCellReference($splitCell, $pBefore, $pNumCols, $pNumRows); < $topLeftCell = $this->updateCellReference($topLeftCell, $pBefore, $pNumCols, $pNumRows);> $splitCell = $this->updateCellReference($splitCell); > $topLeftCell = $this->updateCellReference($topLeftCell);< $pSheet->freezePane($splitCell, $topLeftCell);> $worksheet->freezePane($splitCell, $topLeftCell);} // Page setup< if ($pSheet->getPageSetup()->isPrintAreaSet()) { < $pSheet->getPageSetup()->setPrintArea($this->updateCellReference($pSheet->getPageSetup()->getPrintArea(), $pBefore, $pNumCols, $pNumRows));> if ($worksheet->getPageSetup()->isPrintAreaSet()) { > $worksheet->getPageSetup()->setPrintArea( > $this->updateCellReference($worksheet->getPageSetup()->getPrintArea()) > );} // Update worksheet: drawings< $aDrawings = $pSheet->getDrawingCollection();> $aDrawings = $worksheet->getDrawingCollection();foreach ($aDrawings as $objDrawing) {< $newReference = $this->updateCellReference($objDrawing->getCoordinates(), $pBefore, $pNumCols, $pNumRows);> $newReference = $this->updateCellReference($objDrawing->getCoordinates());if ($objDrawing->getCoordinates() != $newReference) { $objDrawing->setCoordinates($newReference); }> if ($objDrawing->getCoordinates2() !== '') { } > $newReference = $this->updateCellReference($objDrawing->getCoordinates2()); > if ($objDrawing->getCoordinates2() != $newReference) { // Update workbook: named ranges > $objDrawing->setCoordinates2($newReference);< < // Update workbook: named ranges < if (count($pSheet->getParent()->getNamedRanges()) > 0) { < foreach ($pSheet->getParent()->getNamedRanges() as $namedRange) { < if ($namedRange->getWorksheet()->getHashCode() == $pSheet->getHashCode()) { < $namedRange->setRange($this->updateCellReference($namedRange->getRange(), $pBefore, $pNumCols, $pNumRows));}> > // Update workbook: define names // Garbage collect > if (count($worksheet->getParentOrThrow()->getDefinedNames()) > 0) { $pSheet->garbageCollect(); > $this->updateDefinedNames($worksheet, $beforeCellAddress, $numberOfColumns, $numberOfRows);< $pSheet->garbageCollect();> $worksheet->garbageCollect();/** * Update references within formulas. *< * @param string $pFormula Formula to update < * @param string $pBefore Insert before this one < * @param int $pNumCols Number of columns to insert < * @param int $pNumRows Number of rows to insert < * @param string $sheetName Worksheet name/title < * < * @throws Exception> * @param string $formula Formula to update > * @param string $beforeCellAddress Insert before this one > * @param int $numberOfColumns Number of columns to insert > * @param int $numberOfRows Number of rows to insert > * @param string $worksheetName Worksheet name/title* * @return string Updated formula */< public function updateFormulaReferences($pFormula = '', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0, $sheetName = '') < {> public function updateFormulaReferences( > $formula = '', > $beforeCellAddress = 'A1', > $numberOfColumns = 0, > $numberOfRows = 0, > $worksheetName = '', > bool $includeAbsoluteReferences = false > ) { > if ( > $this->cellReferenceHelper === null || > $this->cellReferenceHelper->refreshRequired($beforeCellAddress, $numberOfColumns, $numberOfRows) > ) { > $this->cellReferenceHelper = new CellReferenceHelper($beforeCellAddress, $numberOfColumns, $numberOfRows); > } >// Update cell references in the formula< $formulaBlocks = explode('"', $pFormula);> $formulaBlocks = explode('"', $formula);$i = false; foreach ($formulaBlocks as &$formulaBlock) { // Ignore blocks that were enclosed in quotes (alternating entries in the $formulaBlocks array after the explode)< if ($i = !$i) {> $i = $i === false; > if ($i) {$adjustCount = 0; $newCellTokens = $cellTokens = []; // Search for row ranges (e.g. 'Sheet1'!3:5 or 3:5) with or without $ absolutes (e.g. $3:5)< $matchCount = preg_match_all('/' . self::REFHELPER_REGEXP_ROWRANGE . '/i', ' ' . $formulaBlock . ' ', $matches, PREG_SET_ORDER);> $matchCount = preg_match_all('/' . self::REFHELPER_REGEXP_ROWRANGE . '/mui', ' ' . $formulaBlock . ' ', $matches, PREG_SET_ORDER);if ($matchCount > 0) { foreach ($matches as $match) { $fromString = ($match[2] > '') ? $match[2] . '!' : ''; $fromString .= $match[3] . ':' . $match[4];< $modified3 = substr($this->updateCellReference('$A' . $match[3], $pBefore, $pNumCols, $pNumRows), 2); < $modified4 = substr($this->updateCellReference('$A' . $match[4], $pBefore, $pNumCols, $pNumRows), 2);> $modified3 = substr($this->updateCellReference('$A' . $match[3], $includeAbsoluteReferences), 2); > $modified4 = substr($this->updateCellReference('$A' . $match[4], $includeAbsoluteReferences), 2);if ($match[3] . ':' . $match[4] !== $modified3 . ':' . $modified4) {< if (($match[2] == '') || (trim($match[2], "'") == $sheetName)) {> if (($match[2] == '') || (trim($match[2], "'") == $worksheetName)) {$toString = ($match[2] > '') ? $match[2] . '!' : ''; $toString .= $modified3 . ':' . $modified4; // Max worksheet size is 1,048,576 rows by 16,384 columns in Excel 2007, so our adjustments need to be at least one digit more $column = 100000;< $row = 10000000 + trim($match[3], '$');> $row = 10000000 + (int) trim($match[3], '$');$cellIndex = $column . $row; $newCellTokens[$cellIndex] = preg_quote($toString, '/'); $cellTokens[$cellIndex] = '/(?<!\d\$\!)' . preg_quote($fromString, '/') . '(?!\d)/i'; ++$adjustCount; } } } } // Search for column ranges (e.g. 'Sheet1'!C:E or C:E) with or without $ absolutes (e.g. $C:E)< $matchCount = preg_match_all('/' . self::REFHELPER_REGEXP_COLRANGE . '/i', ' ' . $formulaBlock . ' ', $matches, PREG_SET_ORDER);> $matchCount = preg_match_all('/' . self::REFHELPER_REGEXP_COLRANGE . '/mui', ' ' . $formulaBlock . ' ', $matches, PREG_SET_ORDER);if ($matchCount > 0) { foreach ($matches as $match) { $fromString = ($match[2] > '') ? $match[2] . '!' : ''; $fromString .= $match[3] . ':' . $match[4];< $modified3 = substr($this->updateCellReference($match[3] . '$1', $pBefore, $pNumCols, $pNumRows), 0, -2); < $modified4 = substr($this->updateCellReference($match[4] . '$1', $pBefore, $pNumCols, $pNumRows), 0, -2);> $modified3 = substr($this->updateCellReference($match[3] . '$1', $includeAbsoluteReferences), 0, -2); > $modified4 = substr($this->updateCellReference($match[4] . '$1', $includeAbsoluteReferences), 0, -2);if ($match[3] . ':' . $match[4] !== $modified3 . ':' . $modified4) {< if (($match[2] == '') || (trim($match[2], "'") == $sheetName)) {> if (($match[2] == '') || (trim($match[2], "'") == $worksheetName)) {$toString = ($match[2] > '') ? $match[2] . '!' : ''; $toString .= $modified3 . ':' . $modified4; // Max worksheet size is 1,048,576 rows by 16,384 columns in Excel 2007, so our adjustments need to be at least one digit more $column = Coordinate::columnIndexFromString(trim($match[3], '$')) + 100000; $row = 10000000; $cellIndex = $column . $row; $newCellTokens[$cellIndex] = preg_quote($toString, '/'); $cellTokens[$cellIndex] = '/(?<![A-Z\$\!])' . preg_quote($fromString, '/') . '(?![A-Z])/i'; ++$adjustCount; } } } } // Search for cell ranges (e.g. 'Sheet1'!A3:C5 or A3:C5) with or without $ absolutes (e.g. $A1:C$5)< $matchCount = preg_match_all('/' . self::REFHELPER_REGEXP_CELLRANGE . '/i', ' ' . $formulaBlock . ' ', $matches, PREG_SET_ORDER);> $matchCount = preg_match_all('/' . self::REFHELPER_REGEXP_CELLRANGE . '/mui', ' ' . $formulaBlock . ' ', $matches, PREG_SET_ORDER);if ($matchCount > 0) { foreach ($matches as $match) { $fromString = ($match[2] > '') ? $match[2] . '!' : ''; $fromString .= $match[3] . ':' . $match[4];< $modified3 = $this->updateCellReference($match[3], $pBefore, $pNumCols, $pNumRows); < $modified4 = $this->updateCellReference($match[4], $pBefore, $pNumCols, $pNumRows);> $modified3 = $this->updateCellReference($match[3], $includeAbsoluteReferences); > $modified4 = $this->updateCellReference($match[4], $includeAbsoluteReferences);if ($match[3] . $match[4] !== $modified3 . $modified4) {< if (($match[2] == '') || (trim($match[2], "'") == $sheetName)) {> if (($match[2] == '') || (trim($match[2], "'") == $worksheetName)) {$toString = ($match[2] > '') ? $match[2] . '!' : ''; $toString .= $modified3 . ':' . $modified4; [$column, $row] = Coordinate::coordinateFromString($match[3]); // Max worksheet size is 1,048,576 rows by 16,384 columns in Excel 2007, so our adjustments need to be at least one digit more $column = Coordinate::columnIndexFromString(trim($column, '$')) + 100000;< $row = trim($row, '$') + 10000000;> $row = (int) trim($row, '$') + 10000000;$cellIndex = $column . $row; $newCellTokens[$cellIndex] = preg_quote($toString, '/'); $cellTokens[$cellIndex] = '/(?<![A-Z]\$\!)' . preg_quote($fromString, '/') . '(?!\d)/i'; ++$adjustCount; } } } } // Search for cell references (e.g. 'Sheet1'!A3 or C5) with or without $ absolutes (e.g. $A1 or C$5)< $matchCount = preg_match_all('/' . self::REFHELPER_REGEXP_CELLREF . '/i', ' ' . $formulaBlock . ' ', $matches, PREG_SET_ORDER);> $matchCount = preg_match_all('/' . self::REFHELPER_REGEXP_CELLREF . '/mui', ' ' . $formulaBlock . ' ', $matches, PREG_SET_ORDER);if ($matchCount > 0) { foreach ($matches as $match) { $fromString = ($match[2] > '') ? $match[2] . '!' : ''; $fromString .= $match[3];< $modified3 = $this->updateCellReference($match[3], $pBefore, $pNumCols, $pNumRows);> $modified3 = $this->updateCellReference($match[3], $includeAbsoluteReferences);if ($match[3] !== $modified3) {< if (($match[2] == '') || (trim($match[2], "'") == $sheetName)) {> if (($match[2] == '') || (trim($match[2], "'") == $worksheetName)) {$toString = ($match[2] > '') ? $match[2] . '!' : ''; $toString .= $modified3; [$column, $row] = Coordinate::coordinateFromString($match[3]);> $columnAdditionalIndex = $column[0] === '$' ? 1 : 0; // Max worksheet size is 1,048,576 rows by 16,384 columns in Excel 2007, so our adjustments need to be at least one digit more > $rowAdditionalIndex = $row[0] === '$' ? 1 : 0;$column = Coordinate::columnIndexFromString(trim($column, '$')) + 100000;< $row = trim($row, '$') + 10000000; < $cellIndex = $row . $column;> $row = (int) trim($row, '$') + 10000000; > $cellIndex = $row . $rowAdditionalIndex . $column . $columnAdditionalIndex;$newCellTokens[$cellIndex] = preg_quote($toString, '/'); $cellTokens[$cellIndex] = '/(?<![A-Z\$\!])' . preg_quote($fromString, '/') . '(?!\d)/i'; ++$adjustCount; } } } } if ($adjustCount > 0) {< if ($pNumCols > 0 || $pNumRows > 0) {> if ($numberOfColumns > 0 || $numberOfRows > 0) {krsort($cellTokens); krsort($newCellTokens); } else { ksort($cellTokens); ksort($newCellTokens); } // Update cell references in the formula< $formulaBlock = str_replace('\\', '', preg_replace($cellTokens, $newCellTokens, $formulaBlock));> $formulaBlock = str_replace('\\', '', (string) preg_replace($cellTokens, $newCellTokens, $formulaBlock));} } } unset($formulaBlock); // Then rebuild the formula string return implode('"', $formulaBlocks); } /**> * Update all cell references within a formula, irrespective of worksheet. * Update cell reference. > */ * > public function updateFormulaReferencesAnyWorksheet(string $formula = '', int $numberOfColumns = 0, int $numberOfRows = 0): string * @param string $pCellRange Cell range > { * @param string $pBefore Insert before this one > $formula = $this->updateCellReferencesAllWorksheets($formula, $numberOfColumns, $numberOfRows); * @param int $pNumCols Number of columns to increment > * @param int $pNumRows Number of rows to increment > if ($numberOfColumns !== 0) { * > $formula = $this->updateColumnRangesAllWorksheets($formula, $numberOfColumns); * @throws Exception > } * > * @return string Updated cell range > if ($numberOfRows !== 0) { */ > $formula = $this->updateRowRangesAllWorksheets($formula, $numberOfRows); public function updateCellReference($pCellRange = 'A1', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0) > } { > // Is it in another worksheet? Will not have to update anything. > return $formula; if (strpos($pCellRange, '!') !== false) { > } return $pCellRange; > // Is it a range or a single cell? > private function updateCellReferencesAllWorksheets(string $formula, int $numberOfColumns, int $numberOfRows): string } elseif (!Coordinate::coordinateIsRange($pCellRange)) { > { // Single cell > $splitCount = preg_match_all( return $this->updateSingleCellReference($pCellRange, $pBefore, $pNumCols, $pNumRows); > '/' . Calculation::CALCULATION_REGEXP_CELLREF_RELATIVE . '/mui', } elseif (Coordinate::coordinateIsRange($pCellRange)) { > $formula, // Range > $splitRanges, return $this->updateCellRange($pCellRange, $pBefore, $pNumCols, $pNumRows); > PREG_OFFSET_CAPTURE } > ); > // Return original > $columnLengths = array_map('strlen', array_column($splitRanges[6], 0)); return $pCellRange; > $rowLengths = array_map('strlen', array_column($splitRanges[7], 0)); } > $columnOffsets = array_column($splitRanges[6], 1); > $rowOffsets = array_column($splitRanges[7], 1); /** > * Update named formulas (i.e. containing worksheet references / named ranges). > $columns = $splitRanges[6]; * > $rows = $splitRanges[7]; * @param Spreadsheet $spreadsheet Object to update > * @param string $oldName Old name (name to replace) > while ($splitCount > 0) { * @param string $newName New name > --$splitCount; */ > $columnLength = $columnLengths[$splitCount]; public function updateNamedFormulas(Spreadsheet $spreadsheet, $oldName = '', $newName = '') > $rowLength = $rowLengths[$splitCount]; { > $columnOffset = $columnOffsets[$splitCount]; if ($oldName == '') { > $rowOffset = $rowOffsets[$splitCount]; return; > $column = $columns[$splitCount][0]; } > $row = $rows[$splitCount][0]; > foreach ($spreadsheet->getWorksheetIterator() as $sheet) { > if (!empty($column) && $column[0] !== '$') { foreach ($sheet->getCoordinates(false) as $coordinate) { > $column = Coordinate::stringFromColumnIndex(Coordinate::columnIndexFromString($column) + $numberOfColumns); $cell = $sheet->getCell($coordinate); > $formula = substr($formula, 0, $columnOffset) . $column . substr($formula, $columnOffset + $columnLength); if (($cell !== null) && ($cell->getDataType() == DataType::TYPE_FORMULA)) { > } $formula = $cell->getValue(); > if (!empty($row) && $row[0] !== '$') { if (strpos($formula, $oldName) !== false) { > $row = (int) $row + $numberOfRows; $formula = str_replace("'" . $oldName . "'!", "'" . $newName . "'!", $formula); > $formula = substr($formula, 0, $rowOffset) . $row . substr($formula, $rowOffset + $rowLength); $formula = str_replace($oldName . '!', $newName . '!', $formula); > } $cell->setValueExplicit($formula, DataType::TYPE_FORMULA); > } } > } > return $formula; } > } } > } > private function updateColumnRangesAllWorksheets(string $formula, int $numberOfColumns): string > { /** > $splitCount = preg_match_all( * Update cell range. > '/' . Calculation::CALCULATION_REGEXP_COLUMNRANGE_RELATIVE . '/mui', * > $formula, * @param string $pCellRange Cell range (e.g. 'B2:D4', 'B:C' or '2:3') > $splitRanges, * @param string $pBefore Insert before this one > PREG_OFFSET_CAPTURE * @param int $pNumCols Number of columns to increment > ); * @param int $pNumRows Number of rows to increment > * > $fromColumnLengths = array_map('strlen', array_column($splitRanges[1], 0)); * @throws Exception > $fromColumnOffsets = array_column($splitRanges[1], 1); * > $toColumnLengths = array_map('strlen', array_column($splitRanges[2], 0)); * @return string Updated cell range > $toColumnOffsets = array_column($splitRanges[2], 1); */ > private function updateCellRange($pCellRange = 'A1:A1', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0) > $fromColumns = $splitRanges[1]; { > $toColumns = $splitRanges[2]; if (!Coordinate::coordinateIsRange($pCellRange)) { > throw new Exception('Only cell ranges may be passed to this method.'); > while ($splitCount > 0) { } > --$splitCount; > $fromColumnLength = $fromColumnLengths[$splitCount]; // Update range > $toColumnLength = $toColumnLengths[$splitCount]; $range = Coordinate::splitRange($pCellRange); > $fromColumnOffset = $fromColumnOffsets[$splitCount]; $ic = count($range); > $toColumnOffset = $toColumnOffsets[$splitCount]; for ($i = 0; $i < $ic; ++$i) { > $fromColumn = $fromColumns[$splitCount][0]; $jc = count($range[$i]); > $toColumn = $toColumns[$splitCount][0]; for ($j = 0; $j < $jc; ++$j) { > if (ctype_alpha($range[$i][$j])) { > if (!empty($fromColumn) && $fromColumn[0] !== '$') { $r = Coordinate::coordinateFromString($this->updateSingleCellReference($range[$i][$j] . '1', $pBefore, $pNumCols, $pNumRows)); > $fromColumn = Coordinate::stringFromColumnIndex(Coordinate::columnIndexFromString($fromColumn) + $numberOfColumns); $range[$i][$j] = $r[0]; > $formula = substr($formula, 0, $fromColumnOffset) . $fromColumn . substr($formula, $fromColumnOffset + $fromColumnLength); } elseif (ctype_digit($range[$i][$j])) { > } $r = Coordinate::coordinateFromString($this->updateSingleCellReference('A' . $range[$i][$j], $pBefore, $pNumCols, $pNumRows)); > if (!empty($toColumn) && $toColumn[0] !== '$') { $range[$i][$j] = $r[1]; > $toColumn = Coordinate::stringFromColumnIndex(Coordinate::columnIndexFromString($toColumn) + $numberOfColumns); } else { > $formula = substr($formula, 0, $toColumnOffset) . $toColumn . substr($formula, $toColumnOffset + $toColumnLength); $range[$i][$j] = $this->updateSingleCellReference($range[$i][$j], $pBefore, $pNumCols, $pNumRows); > } } > } } > } > return $formula; > } // Recreate range string > return Coordinate::buildRange($range); > private function updateRowRangesAllWorksheets(string $formula, int $numberOfRows): string } > { > $splitCount = preg_match_all( /** > '/' . Calculation::CALCULATION_REGEXP_ROWRANGE_RELATIVE . '/mui', * Update single cell reference. > $formula, * > $splitRanges, * @param string $pCellReference Single cell reference > PREG_OFFSET_CAPTURE * @param string $pBefore Insert before this one > ); * @param int $pNumCols Number of columns to increment > * @param int $pNumRows Number of rows to increment > $fromRowLengths = array_map('strlen', array_column($splitRanges[1], 0)); * > $fromRowOffsets = array_column($splitRanges[1], 1); * @throws Exception > $toRowLengths = array_map('strlen', array_column($splitRanges[2], 0)); * > $toRowOffsets = array_column($splitRanges[2], 1); * @return string Updated cell reference > */ > $fromRows = $splitRanges[1]; private function updateSingleCellReference($pCellReference = 'A1', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0) > $toRows = $splitRanges[2]; { > if (Coordinate::coordinateIsRange($pCellReference)) { > while ($splitCount > 0) { throw new Exception('Only single cell references may be passed to this method.'); > --$splitCount; } > $fromRowLength = $fromRowLengths[$splitCount]; > $toRowLength = $toRowLengths[$splitCount]; // Get coordinate of $pBefore > $fromRowOffset = $fromRowOffsets[$splitCount]; [$beforeColumn, $beforeRow] = Coordinate::coordinateFromString($pBefore); > $toRowOffset = $toRowOffsets[$splitCount]; > $fromRow = $fromRows[$splitCount][0]; // Get coordinate of $pCellReference > $toRow = $toRows[$splitCount][0]; [$newColumn, $newRow] = Coordinate::coordinateFromString($pCellReference); > > if (!empty($fromRow) && $fromRow[0] !== '$') { // Verify which parts should be updated > $fromRow = (int) $fromRow + $numberOfRows; $updateColumn = (($newColumn[0] != '$') && ($beforeColumn[0] != '$') && (Coordinate::columnIndexFromString($newColumn) >= Coordinate::columnIndexFromString($beforeColumn))); > $formula = substr($formula, 0, $fromRowOffset) . $fromRow . substr($formula, $fromRowOffset + $fromRowLength); $updateRow = (($newRow[0] != '$') && ($beforeRow[0] != '$') && $newRow >= $beforeRow); > } > if (!empty($toRow) && $toRow[0] !== '$') { // Create new column reference > $toRow = (int) $toRow + $numberOfRows; if ($updateColumn) { > $formula = substr($formula, 0, $toRowOffset) . $toRow . substr($formula, $toRowOffset + $toRowLength); $newColumn = Coordinate::stringFromColumnIndex(Coordinate::columnIndexFromString($newColumn) + $pNumCols); > } } > } > // Create new row reference > return $formula; if ($updateRow) { > } $newRow = $newRow + $pNumRows; > } > /**< * @param string $pCellRange Cell range < * @param string $pBefore Insert before this one < * @param int $pNumCols Number of columns to increment < * @param int $pNumRows Number of rows to increment < * < * @throws Exception> * @param string $cellReference Cell address or range of addresses< public function updateCellReference($pCellRange = 'A1', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0)> private function updateCellReference($cellReference = 'A1', bool $includeAbsoluteReferences = false)< if (strpos($pCellRange, '!') !== false) { < return $pCellRange;> if (strpos($cellReference, '!') !== false) { > return $cellReference; > }< } elseif (!Coordinate::coordinateIsRange($pCellRange)) {> if (!Coordinate::coordinateIsRange($cellReference)) {< return $this->updateSingleCellReference($pCellRange, $pBefore, $pNumCols, $pNumRows); < } elseif (Coordinate::coordinateIsRange($pCellRange)) { < // Range < return $this->updateCellRange($pCellRange, $pBefore, $pNumCols, $pNumRows);> return $this->cellReferenceHelper->updateCellReference($cellReference, $includeAbsoluteReferences);< // Return original < return $pCellRange;> // Range > return $this->updateCellRange($cellReference, $includeAbsoluteReferences);< * Update named formulas (i.e. containing worksheet references / named ranges).> * Update named formulae (i.e. containing worksheet references / named ranges).< public function updateNamedFormulas(Spreadsheet $spreadsheet, $oldName = '', $newName = '')> public function updateNamedFormulae(Spreadsheet $spreadsheet, $oldName = '', $newName = ''): void< if (($cell !== null) && ($cell->getDataType() == DataType::TYPE_FORMULA)) {> if ($cell->getDataType() === DataType::TYPE_FORMULA) {> private function updateDefinedNames(Worksheet $worksheet, string $beforeCellAddress, int $numberOfColumns, int $numberOfRows): void > { > foreach ($worksheet->getParentOrThrow()->getDefinedNames() as $definedName) { > if ($definedName->isFormula() === false) { > $this->updateNamedRange($definedName, $worksheet, $beforeCellAddress, $numberOfColumns, $numberOfRows); > } else { > $this->updateNamedFormula($definedName, $worksheet, $beforeCellAddress, $numberOfColumns, $numberOfRows); > } > } > } > > private function updateNamedRange(DefinedName $definedName, Worksheet $worksheet, string $beforeCellAddress, int $numberOfColumns, int $numberOfRows): void > { > $cellAddress = $definedName->getValue(); > $asFormula = ($cellAddress[0] === '='); > if ($definedName->getWorksheet() !== null && $definedName->getWorksheet()->getHashCode() === $worksheet->getHashCode()) { > if ($asFormula === true) { > $formula = $this->updateFormulaReferences($cellAddress, $beforeCellAddress, $numberOfColumns, $numberOfRows, $worksheet->getTitle()); > $definedName->setValue($formula); > } else { > $definedName->setValue($this->updateCellReference(ltrim($cellAddress, '='))); > } > } > } > > private function updateNamedFormula(DefinedName $definedName, Worksheet $worksheet, string $beforeCellAddress, int $numberOfColumns, int $numberOfRows): void > { > if ($definedName->getWorksheet() !== null && $definedName->getWorksheet()->getHashCode() === $worksheet->getHashCode()) { > $formula = $definedName->getValue(); > $formula = $this->updateFormulaReferences($formula, $beforeCellAddress, $numberOfColumns, $numberOfRows, $worksheet->getTitle()); > $definedName->setValue($formula); > } > } >< * @param string $pCellRange Cell range (e.g. 'B2:D4', 'B:C' or '2:3') < * @param string $pBefore Insert before this one < * @param int $pNumCols Number of columns to increment < * @param int $pNumRows Number of rows to increment < * < * @throws Exception> * @param string $cellRange Cell range (e.g. 'B2:D4', 'B:C' or '2:3')< private function updateCellRange($pCellRange = 'A1:A1', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0)> private function updateCellRange(string $cellRange = 'A1:A1', bool $includeAbsoluteReferences = false): string< if (!Coordinate::coordinateIsRange($pCellRange)) {> if (!Coordinate::coordinateIsRange($cellRange)) {< $range = Coordinate::splitRange($pCellRange);> $range = Coordinate::splitRange($cellRange);< $r = Coordinate::coordinateFromString($this->updateSingleCellReference($range[$i][$j] . '1', $pBefore, $pNumCols, $pNumRows)); < $range[$i][$j] = $r[0];> $range[$i][$j] = Coordinate::coordinateFromString( > $this->cellReferenceHelper->updateCellReference($range[$i][$j] . '1', $includeAbsoluteReferences) > )[0];< $r = Coordinate::coordinateFromString($this->updateSingleCellReference('A' . $range[$i][$j], $pBefore, $pNumCols, $pNumRows)); < $range[$i][$j] = $r[1];> $range[$i][$j] = Coordinate::coordinateFromString( > $this->cellReferenceHelper->updateCellReference('A' . $range[$i][$j], $includeAbsoluteReferences) > )[1];< $range[$i][$j] = $this->updateSingleCellReference($range[$i][$j], $pBefore, $pNumCols, $pNumRows);> $range[$i][$j] = $this->cellReferenceHelper->updateCellReference($range[$i][$j], $includeAbsoluteReferences);< /** < * Update single cell reference. < * < * @param string $pCellReference Single cell reference < * @param string $pBefore Insert before this one < * @param int $pNumCols Number of columns to increment < * @param int $pNumRows Number of rows to increment < * < * @throws Exception < * < * @return string Updated cell reference < */ < private function updateSingleCellReference($pCellReference = 'A1', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0)> private function clearColumnStrips(int $highestRow, int $beforeColumn, int $numberOfColumns, Worksheet $worksheet): void< if (Coordinate::coordinateIsRange($pCellReference)) { < throw new Exception('Only single cell references may be passed to this method.');> $startColumnId = Coordinate::stringFromColumnIndex($beforeColumn + $numberOfColumns); > $endColumnId = Coordinate::stringFromColumnIndex($beforeColumn); > > for ($row = 1; $row <= $highestRow - 1; ++$row) { > for ($column = $startColumnId; $column !== $endColumnId; ++$column) { > $coordinate = $column . $row; > $this->clearStripCell($worksheet, $coordinate); > } > } > } > > private function clearRowStrips(string $highestColumn, int $beforeColumn, int $beforeRow, int $numberOfRows, Worksheet $worksheet): void > { > $startColumnId = Coordinate::stringFromColumnIndex($beforeColumn); > ++$highestColumn; > > for ($column = $startColumnId; $column !== $highestColumn; ++$column) { > for ($row = $beforeRow + $numberOfRows; $row <= $beforeRow - 1; ++$row) { > $coordinate = $column . $row; > $this->clearStripCell($worksheet, $coordinate); > } > }< // Get coordinate of $pBefore < [$beforeColumn, $beforeRow] = Coordinate::coordinateFromString($pBefore);> private function clearStripCell(Worksheet $worksheet, string $coordinate): void > { > $worksheet->removeConditionalStyles($coordinate); > $worksheet->setHyperlink($coordinate); > $worksheet->setDataValidation($coordinate); > $worksheet->removeComment($coordinate);< // Get coordinate of $pCellReference < [$newColumn, $newRow] = Coordinate::coordinateFromString($pCellReference);> if ($worksheet->cellExists($coordinate)) { > $worksheet->getCell($coordinate)->setValueExplicit(null, DataType::TYPE_NULL); > $worksheet->getCell($coordinate)->setXfIndex(0); > } > }< // Verify which parts should be updated < $updateColumn = (($newColumn[0] != '$') && ($beforeColumn[0] != '$') && (Coordinate::columnIndexFromString($newColumn) >= Coordinate::columnIndexFromString($beforeColumn))); < $updateRow = (($newRow[0] != '$') && ($beforeRow[0] != '$') && $newRow >= $beforeRow);> private function adjustAutoFilter(Worksheet $worksheet, string $beforeCellAddress, int $numberOfColumns): void > { > $autoFilter = $worksheet->getAutoFilter(); > $autoFilterRange = $autoFilter->getRange(); > if (!empty($autoFilterRange)) { > if ($numberOfColumns !== 0) { > $autoFilterColumns = $autoFilter->getColumns(); > if (count($autoFilterColumns) > 0) { > $column = ''; > $row = 0; > sscanf($beforeCellAddress, '%[A-Z]%d', $column, $row); > $columnIndex = Coordinate::columnIndexFromString((string) $column); > [$rangeStart, $rangeEnd] = Coordinate::rangeBoundaries($autoFilterRange); > if ($columnIndex <= $rangeEnd[0]) { > if ($numberOfColumns < 0) { > $this->adjustAutoFilterDeleteRules($columnIndex, $numberOfColumns, $autoFilterColumns, $autoFilter); > } > $startCol = ($columnIndex > $rangeStart[0]) ? $columnIndex : $rangeStart[0];< // Create new column reference < if ($updateColumn) { < $newColumn = Coordinate::stringFromColumnIndex(Coordinate::columnIndexFromString($newColumn) + $pNumCols);> // Shuffle columns in autofilter range > if ($numberOfColumns > 0) { > $this->adjustAutoFilterInsert($startCol, $numberOfColumns, $rangeEnd[0], $autoFilter); > } else { > $this->adjustAutoFilterDelete($startCol, $numberOfColumns, $rangeEnd[0], $autoFilter); > } > } > }< // Create new row reference < if ($updateRow) { < $newRow = $newRow + $pNumRows;> $worksheet->setAutoFilter( > $this->updateCellReference($autoFilterRange) > );> } > > private function adjustAutoFilterDeleteRules(int $columnIndex, int $numberOfColumns, array $autoFilterColumns, AutoFilter $autoFilter): void > { > // If we're actually deleting any columns that fall within the autofilter range, > // then we delete any rules for those columns > $deleteColumn = $columnIndex + $numberOfColumns - 1; > $deleteCount = abs($numberOfColumns);< // Return new reference < return $newColumn . $newRow;> for ($i = 1; $i <= $deleteCount; ++$i) { > $columnName = Coordinate::stringFromColumnIndex($deleteColumn + 1); > if (isset($autoFilterColumns[$columnName])) { > $autoFilter->clearColumn($columnName); > } > ++$deleteColumn; > } > } > > private function adjustAutoFilterInsert(int $startCol, int $numberOfColumns, int $rangeEnd, AutoFilter $autoFilter): void > { > $startColRef = $startCol; > $endColRef = $rangeEnd; > $toColRef = $rangeEnd + $numberOfColumns; > > do { > $autoFilter->shiftColumn(Coordinate::stringFromColumnIndex($endColRef), Coordinate::stringFromColumnIndex($toColRef)); > --$endColRef; > --$toColRef; > } while ($startColRef <= $endColRef); > } > > private function adjustAutoFilterDelete(int $startCol, int $numberOfColumns, int $rangeEnd, AutoFilter $autoFilter): void > { > // For delete, we shuffle from beginning to end to avoid overwriting > $startColID = Coordinate::stringFromColumnIndex($startCol); > $toColID = Coordinate::stringFromColumnIndex($startCol + $numberOfColumns); > $endColID = Coordinate::stringFromColumnIndex($rangeEnd + 1); > > do { > $autoFilter->shiftColumn($startColID, $toColID); > ++$startColID; > ++$toColID; > } while ($startColID !== $endColID); > } > > private function adjustTable(Worksheet $worksheet, string $beforeCellAddress, int $numberOfColumns): void > { > $tableCollection = $worksheet->getTableCollection(); > > foreach ($tableCollection as $table) { > $tableRange = $table->getRange(); > if (!empty($tableRange)) { > if ($numberOfColumns !== 0) { > $tableColumns = $table->getColumns(); > if (count($tableColumns) > 0) { > $column = ''; > $row = 0; > sscanf($beforeCellAddress, '%[A-Z]%d', $column, $row); > $columnIndex = Coordinate::columnIndexFromString((string) $column); > [$rangeStart, $rangeEnd] = Coordinate::rangeBoundaries($tableRange); > if ($columnIndex <= $rangeEnd[0]) { > if ($numberOfColumns < 0) { > $this->adjustTableDeleteRules($columnIndex, $numberOfColumns, $tableColumns, $table); > } > $startCol = ($columnIndex > $rangeStart[0]) ? $columnIndex : $rangeStart[0]; > > // Shuffle columns in table range > if ($numberOfColumns > 0) { > $this->adjustTableInsert($startCol, $numberOfColumns, $rangeEnd[0], $table); > } else { > $this->adjustTableDelete($startCol, $numberOfColumns, $rangeEnd[0], $table); > } > } > } > } > > $table->setRange($this->updateCellReference($tableRange)); > } > } > } > > private function adjustTableDeleteRules(int $columnIndex, int $numberOfColumns, array $tableColumns, Table $table): void > { > // If we're actually deleting any columns that fall within the table range, > // then we delete any rules for those columns > $deleteColumn = $columnIndex + $numberOfColumns - 1; > $deleteCount = abs($numberOfColumns); > > for ($i = 1; $i <= $deleteCount; ++$i) { > $columnName = Coordinate::stringFromColumnIndex($deleteColumn + 1); > if (isset($tableColumns[$columnName])) { > $table->clearColumn($columnName); > } > ++$deleteColumn; > } > } > > private function adjustTableInsert(int $startCol, int $numberOfColumns, int $rangeEnd, Table $table): void > { > $startColRef = $startCol; > $endColRef = $rangeEnd; > $toColRef = $rangeEnd + $numberOfColumns; > > do { > $table->shiftColumn(Coordinate::stringFromColumnIndex($endColRef), Coordinate::stringFromColumnIndex($toColRef)); > --$endColRef; > --$toColRef; > } while ($startColRef <= $endColRef); > } > > private function adjustTableDelete(int $startCol, int $numberOfColumns, int $rangeEnd, Table $table): void > { > // For delete, we shuffle from beginning to end to avoid overwriting > $startColID = Coordinate::stringFromColumnIndex($startCol); > $toColID = Coordinate::stringFromColumnIndex($startCol + $numberOfColumns); > $endColID = Coordinate::stringFromColumnIndex($rangeEnd + 1); > > do { > $table->shiftColumn($startColID, $toColID); > ++$startColID; > ++$toColID; > } while ($startColID !== $endColID); > } > > private function duplicateStylesByColumn(Worksheet $worksheet, int $beforeColumn, int $beforeRow, int $highestRow, int $numberOfColumns): void > { > $beforeColumnName = Coordinate::stringFromColumnIndex($beforeColumn - 1); > for ($i = $beforeRow; $i <= $highestRow - 1; ++$i) { > // Style > $coordinate = $beforeColumnName . $i; > if ($worksheet->cellExists($coordinate)) { > $xfIndex = $worksheet->getCell($coordinate)->getXfIndex(); > for ($j = $beforeColumn; $j <= $beforeColumn - 1 + $numberOfColumns; ++$j) { > $worksheet->getCell([$j, $i])->setXfIndex($xfIndex); > } > } > } > } > > private function duplicateStylesByRow(Worksheet $worksheet, int $beforeColumn, int $beforeRow, string $highestColumn, int $numberOfRows): void > { > $highestColumnIndex = Coordinate::columnIndexFromString($highestColumn); > for ($i = $beforeColumn; $i <= $highestColumnIndex; ++$i) { > // Style > $coordinate = Coordinate::stringFromColumnIndex($i) . ($beforeRow - 1); > if ($worksheet->cellExists($coordinate)) { > $xfIndex = $worksheet->getCell($coordinate)->getXfIndex(); > for ($j = $beforeRow; $j <= $beforeRow - 1 + $numberOfRows; ++$j) { > $worksheet->getCell(Coordinate::stringFromColumnIndex($i) . $j)->setXfIndex($xfIndex); > } > } > }< * < * @throws Exception