diff -ru old/PHPExcel/ReferenceHelper.php new/PHPExcel/ReferenceHelper.php --- old/PHPExcel/ReferenceHelper.php 2015-05-01 00:00:55.000000000 -0700 +++ new/PHPExcel/ReferenceHelper.php 2020-04-28 11:27:21.469999999 -0700 @@ -235,8 +235,10 @@ foreach ($aHyperlinkCollection as $key => $value) { $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows); if ($key != $newReference) { - $pSheet->setHyperlink( $newReference, $value ); $pSheet->setHyperlink( $key, null ); + if ($newReference) { + $pSheet->setHyperlink( $newReference, $value ); + } } } } @@ -260,8 +262,10 @@ foreach ($aDataValidationCollection as $key => $value) { $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows); if ($key != $newReference) { - $pSheet->setDataValidation( $newReference, $value ); $pSheet->setDataValidation( $key, null ); + if ($newReference) { + $pSheet->setDataValidation( $newReference, $value ); + } } } } @@ -282,7 +286,9 @@ $aNewMergeCells = array(); // the new array of all merge cells foreach ($aMergeCells as $key => &$value) { $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows); - $aNewMergeCells[$newReference] = $newReference; + if ($newReference) { + $aNewMergeCells[$newReference] = $newReference; + } } $pSheet->setMergeCells($aNewMergeCells); // replace the merge cells array } @@ -306,8 +312,10 @@ foreach ($aProtectedCells as $key => $value) { $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows); if ($key != $newReference) { - $pSheet->protectCells( $newReference, $value, true ); $pSheet->unprotectCells( $key ); + if ($newReference) { + $pSheet->protectCells( $newReference, $value, true ); + } } } } @@ -432,17 +440,19 @@ $cell = $pSheet->getCell($cellID); $cellIndex = PHPExcel_Cell::columnIndexFromString($cell->getColumn()); - if ($cellIndex-1 + $pNumCols < 0) { + // Don't update cells that are being removed + if ($pNumCols<0 && $cellIndex>=$beforeColumnIndex+$pNumCols && + $cellIndex<$beforeColumnIndex) { continue; } - // New coordinates - $newCoordinates = PHPExcel_Cell::stringFromColumnIndex($cellIndex-1 + $pNumCols) . ($cell->getRow() + $pNumRows); - // Should the cell be updated? Move value and cellXf index from one cell to another. if (($cellIndex >= $beforeColumnIndex) && ($cell->getRow() >= $beforeRow)) { + // New coordinates + $newCoordinates = PHPExcel_Cell::stringFromColumnIndex($cellIndex-1 + $pNumCols) . ($cell->getRow() + $pNumRows); + // Update cell styles $pSheet->getCell($newCoordinates)->setXfIndex($cell->getXfIndex()); @@ -665,8 +675,8 @@ 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],$pBefore,$pNumCols,$pNumRows,true),2); + $modified4 = substr($this->updateCellReference('$A'.$match[4],$pBefore,$pNumCols,$pNumRows,false),2); if ($match[3].':'.$match[4] !== $modified3.':'.$modified4) { if (($match[2] == '') || (trim($match[2],"'") == $sheetName)) { @@ -690,8 +700,8 @@ 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',$pBefore,$pNumCols,$pNumRows,true),0,-2); + $modified4 = substr($this->updateCellReference($match[4].'$1',$pBefore,$pNumCols,$pNumRows,false),0,-2); if ($match[3].':'.$match[4] !== $modified3.':'.$modified4) { if (($match[2] == '') || (trim($match[2],"'") == $sheetName)) { @@ -715,8 +725,8 @@ 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],$pBefore,$pNumCols,$pNumRows,true); + $modified4 = $this->updateCellReference($match[4],$pBefore,$pNumCols,$pNumRows,false); if ($match[3].$match[4] !== $modified3.$modified4) { if (($match[2] == '') || (trim($match[2],"'") == $sheetName)) { @@ -786,17 +796,18 @@ * @param int $pBefore Insert before this one * @param int $pNumCols Number of columns to increment * @param int $pNumRows Number of rows to increment + * @param bool or null $topLeft Whether top/left or bottom/right * @return string Updated cell range * @throws PHPExcel_Exception */ - public function updateCellReference($pCellRange = 'A1', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0) { + public function updateCellReference($pCellRange = 'A1', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0, $topLeft = null) { // Is it in another worksheet? Will not have to update anything. if (strpos($pCellRange, "!") !== false) { return $pCellRange; // Is it a range or a single cell? } elseif (strpos($pCellRange, ':') === false && strpos($pCellRange, ',') === false) { // Single cell - return $this->_updateSingleCellReference($pCellRange, $pBefore, $pNumCols, $pNumRows); + return $this->_updateSingleCellReference($pCellRange, $pBefore, $pNumCols, $pNumRows, $topLeft); } elseif (strpos($pCellRange, ':') !== false || strpos($pCellRange, ',') !== false) { // Range return $this->_updateCellRange($pCellRange, $pBefore, $pNumCols, $pNumRows); @@ -845,6 +856,7 @@ */ private function _updateCellRange($pCellRange = 'A1:A1', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0) { if (strpos($pCellRange,':') !== false || strpos($pCellRange, ',') !== false) { + $needToReindex = false; // Update range $range = PHPExcel_Cell::splitRange($pCellRange); $ic = count($range); @@ -852,16 +864,32 @@ $jc = count($range[$i]); for ($j = 0; $j < $jc; ++$j) { if (ctype_alpha($range[$i][$j])) { - $r = PHPExcel_Cell::coordinateFromString($this->_updateSingleCellReference($range[$i][$j].'1', $pBefore, $pNumCols, $pNumRows)); + $r = PHPExcel_Cell::coordinateFromString($this->_updateSingleCellReference($range[$i][$j].'1', $pBefore, $pNumCols, $pNumRows, $j==0)); $range[$i][$j] = $r[0]; } elseif(ctype_digit($range[$i][$j])) { - $r = PHPExcel_Cell::coordinateFromString($this->_updateSingleCellReference('A'.$range[$i][$j], $pBefore, $pNumCols, $pNumRows)); + $r = PHPExcel_Cell::coordinateFromString($this->_updateSingleCellReference('A'.$range[$i][$j], $pBefore, $pNumCols, $pNumRows, $j==0)); $range[$i][$j] = $r[1]; } else { - $range[$i][$j] = $this->_updateSingleCellReference($range[$i][$j], $pBefore, $pNumCols, $pNumRows); + $range[$i][$j] = $this->_updateSingleCellReference($range[$i][$j], $pBefore, $pNumCols, $pNumRows, $j==0); + } + } + if ($jc == 2) { + $currentRange = $range[$i][0].":".$range[$i][1]; + $dimensions = PHPExcel_Cell::rangeDimension($currentRange); + // If the entire range is being removed, one of the dimensions will be 0 + if ($dimensions[0] == 0 or $dimensions[1] == 0) { + unset($range[$i]); + $needToReindex = true; } } } + if ($needToReindex) { + // If all of the ranges have been removed, an empty string is desirable for many callers + if (!$range) { + return ''; + } + $range = array_values($range); + } // Recreate range string return PHPExcel_Cell::buildRange($range); @@ -877,10 +905,11 @@ * @param int $pBefore Insert before this one * @param int $pNumCols Number of columns to increment * @param int $pNumRows Number of rows to increment + * @param bool or null $topLeft Whether top/left or bottom/right * @return string Updated cell reference * @throws PHPExcel_Exception */ - private function _updateSingleCellReference($pCellReference = 'A1', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0) { + private function _updateSingleCellReference($pCellReference = 'A1', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0, $topLeft = null) { if (strpos($pCellReference, ':') === false && strpos($pCellReference, ',') === false) { // Get coordinates of $pBefore list($beforeColumn, $beforeRow) = PHPExcel_Cell::coordinateFromString( $pBefore ); @@ -889,19 +918,37 @@ list($newColumn, $newRow) = PHPExcel_Cell::coordinateFromString( $pCellReference ); // Verify which parts should be updated - $updateColumn = (($newColumn{0} != '$') && ($beforeColumn{0} != '$') && - PHPExcel_Cell::columnIndexFromString($newColumn) >= PHPExcel_Cell::columnIndexFromString($beforeColumn)); - $updateRow = (($newRow{0} != '$') && ($beforeRow{0} != '$') && - $newRow >= $beforeRow); - - // Create new column reference - if ($updateColumn) { - $newColumn = PHPExcel_Cell::stringFromColumnIndex( PHPExcel_Cell::columnIndexFromString($newColumn) - 1 + $pNumCols ); + if (($newColumn{0} != '$') && ($beforeColumn{0} != '$')) { + // A special case is removing the left/top or bottom/right edge of a range + // $topLeft is null if we aren't adjusting a range at all. + if ($topLeft !== null and $pNumCols < 0 && + PHPExcel_Cell::columnIndexFromString($newColumn) >= PHPExcel_Cell::columnIndexFromString($beforeColumn)+$pNumCols && + PHPExcel_Cell::columnIndexFromString($newColumn) <= PHPExcel_Cell::columnIndexFromString($beforeColumn)-1) { + if ($topLeft) { + $newColumn = PHPExcel_Cell::stringFromColumnIndex( PHPExcel_Cell::columnIndexFromString($beforeColumn) - 1 + $pNumCols ); + } else { + $newColumn = PHPExcel_Cell::stringFromColumnIndex( PHPExcel_Cell::columnIndexFromString($beforeColumn) - 1 + $pNumCols - 1 ); + } + } elseif (PHPExcel_Cell::columnIndexFromString($newColumn) >= PHPExcel_Cell::columnIndexFromString($beforeColumn)) { + // Create new column reference + $newColumn = PHPExcel_Cell::stringFromColumnIndex( PHPExcel_Cell::columnIndexFromString($newColumn) - 1 + $pNumCols ); + } } - // Create new row reference - if ($updateRow) { - $newRow = $newRow + $pNumRows; + if (($newRow{0} != '$') && ($beforeRow{0} != '$')) { + // A special case is removing the left/top or bottom/right edge of a range + // $topLeft is null if we aren't adjusting a range at all. + if ($topLeft !== null and $pNumRows < 0 && + $newRow >= $beforeRow+$pNumRows && + $newRow <= $beforeRow-1) { + if ($topLeft) { + $newRow = $beforeRow + $pNumRows; + } else { + $newRow = $beforeRow + $pNumRows -1; + } + } elseif ($newRow >= $beforeRow) { + $newRow = $newRow + $pNumRows; + } } // Return new reference