PHPOffice /
PhpSpreadsheet
| Total Complexity | 167 |
| Total Lines | 898 |
| Duplicated Lines | 0 % |
| Coverage | 74.94% |
| Changes | 0 | ||
Complex classes like ReferenceHelper often do a lot of different things. To break such a class down, we need to identify a cohesive component within that class. A common approach to find such a component is to look for fields/methods that share the same prefixes, or suffixes.
Once you have determined the fields that belong together, you can apply the Extract Class refactoring. If the component makes sense as a sub-class, Extract Subclass is also a candidate, and is often faster.
While breaking up the class, it is a good idea to analyze how other classes use ReferenceHelper, and based on these observations, apply Extract Interface, too.
| 1 | <?php |
||
| 9 | class ReferenceHelper |
||
| 10 | { |
||
| 11 | /** Constants */ |
||
| 12 | /** Regular Expressions */ |
||
| 13 | const REFHELPER_REGEXP_CELLREF = '((\w*|\'[^!]*\')!)?(?<![:a-z\$])(\$?[a-z]{1,3}\$?\d+)(?=[^:!\d\'])'; |
||
| 14 | const REFHELPER_REGEXP_CELLRANGE = '((\w*|\'[^!]*\')!)?(\$?[a-z]{1,3}\$?\d+):(\$?[a-z]{1,3}\$?\d+)'; |
||
| 15 | const REFHELPER_REGEXP_ROWRANGE = '((\w*|\'[^!]*\')!)?(\$?\d+):(\$?\d+)'; |
||
| 16 | const REFHELPER_REGEXP_COLRANGE = '((\w*|\'[^!]*\')!)?(\$?[a-z]{1,3}):(\$?[a-z]{1,3})'; |
||
| 17 | |||
| 18 | /** |
||
| 19 | * Instance of this class. |
||
| 20 | * |
||
| 21 | * @var ReferenceHelper |
||
| 22 | */ |
||
| 23 | private static $instance; |
||
| 24 | |||
| 25 | /** |
||
| 26 | * Get an instance of this class. |
||
| 27 | * |
||
| 28 | * @return ReferenceHelper |
||
| 29 | */ |
||
| 30 | 59 | public static function getInstance() |
|
| 31 | { |
||
| 32 | 59 | if (!isset(self::$instance) || (self::$instance === null)) { |
|
| 33 | 40 | self::$instance = new self(); |
|
| 34 | } |
||
| 35 | |||
| 36 | 59 | return self::$instance; |
|
| 37 | } |
||
| 38 | |||
| 39 | /** |
||
| 40 | * Create a new ReferenceHelper. |
||
| 41 | */ |
||
| 42 | 40 | protected function __construct() |
|
| 43 | { |
||
| 44 | 40 | } |
|
| 45 | |||
| 46 | /** |
||
| 47 | * Compare two column addresses |
||
| 48 | * Intended for use as a Callback function for sorting column addresses by column. |
||
| 49 | * |
||
| 50 | * @param string $a First column to test (e.g. 'AA') |
||
| 51 | * @param string $b Second column to test (e.g. 'Z') |
||
| 52 | * |
||
| 53 | * @return int |
||
| 54 | */ |
||
| 55 | 1 | public static function columnSort($a, $b) |
|
| 56 | { |
||
| 57 | 1 | return strcasecmp(strlen($a) . $a, strlen($b) . $b); |
|
| 58 | } |
||
| 59 | |||
| 60 | /** |
||
| 61 | * Compare two column addresses |
||
| 62 | * Intended for use as a Callback function for reverse sorting column addresses by column. |
||
| 63 | * |
||
| 64 | * @param string $a First column to test (e.g. 'AA') |
||
| 65 | * @param string $b Second column to test (e.g. 'Z') |
||
| 66 | * |
||
| 67 | * @return int |
||
| 68 | */ |
||
| 69 | 1 | public static function columnReverseSort($a, $b) |
|
| 70 | { |
||
| 71 | 1 | return 1 - strcasecmp(strlen($a) . $a, strlen($b) . $b); |
|
| 72 | } |
||
| 73 | |||
| 74 | /** |
||
| 75 | * Compare two cell addresses |
||
| 76 | * Intended for use as a Callback function for sorting cell addresses by column and row. |
||
| 77 | * |
||
| 78 | * @param string $a First cell to test (e.g. 'AA1') |
||
| 79 | * @param string $b Second cell to test (e.g. 'Z1') |
||
| 80 | * |
||
| 81 | * @return int |
||
| 82 | */ |
||
| 83 | 13 | public static function cellSort($a, $b) |
|
| 84 | { |
||
| 85 | 13 | sscanf($a, '%[A-Z]%d', $ac, $ar); |
|
|
1 ignored issue
–
show
|
|||
| 86 | 13 | sscanf($b, '%[A-Z]%d', $bc, $br); |
|
|
1 ignored issue
–
show
|
|||
| 87 | |||
| 88 | 13 | if ($ar == $br) { |
|
| 89 | return strcasecmp(strlen($ac) . $ac, strlen($bc) . $bc); |
||
| 90 | } |
||
| 91 | |||
| 92 | 13 | return ($ar < $br) ? -1 : 1; |
|
| 93 | } |
||
| 94 | |||
| 95 | /** |
||
| 96 | * Compare two cell addresses |
||
| 97 | * Intended for use as a Callback function for sorting cell addresses by column and row. |
||
| 98 | * |
||
| 99 | * @param string $a First cell to test (e.g. 'AA1') |
||
| 100 | * @param string $b Second cell to test (e.g. 'Z1') |
||
| 101 | * |
||
| 102 | * @return int |
||
| 103 | */ |
||
| 104 | 13 | public static function cellReverseSort($a, $b) |
|
| 105 | { |
||
| 106 | 13 | sscanf($a, '%[A-Z]%d', $ac, $ar); |
|
|
1 ignored issue
–
show
|
|||
| 107 | 13 | sscanf($b, '%[A-Z]%d', $bc, $br); |
|
|
1 ignored issue
–
show
|
|||
| 108 | |||
| 109 | 13 | if ($ar == $br) { |
|
| 110 | return 1 - strcasecmp(strlen($ac) . $ac, strlen($bc) . $bc); |
||
| 111 | } |
||
| 112 | |||
| 113 | 13 | return ($ar < $br) ? 1 : -1; |
|
| 114 | } |
||
| 115 | |||
| 116 | /** |
||
| 117 | * Test whether a cell address falls within a defined range of cells. |
||
| 118 | * |
||
| 119 | * @param string $cellAddress Address of the cell we're testing |
||
| 120 | * @param int $beforeRow Number of the row we're inserting/deleting before |
||
| 121 | * @param int $pNumRows Number of rows to insert/delete (negative values indicate deletion) |
||
| 122 | * @param int $beforeColumnIndex Index number of the column we're inserting/deleting before |
||
| 123 | * @param int $pNumCols Number of columns to insert/delete (negative values indicate deletion) |
||
| 124 | * |
||
| 125 | * @return bool |
||
| 126 | */ |
||
| 127 | 13 | private static function cellAddressInDeleteRange($cellAddress, $beforeRow, $pNumRows, $beforeColumnIndex, $pNumCols) |
|
| 128 | { |
||
| 129 | 13 | list($cellColumn, $cellRow) = Coordinate::coordinateFromString($cellAddress); |
|
| 130 | 13 | $cellColumnIndex = Coordinate::columnIndexFromString($cellColumn); |
|
| 131 | // Is cell within the range of rows/columns if we're deleting |
||
| 132 | 13 | if ($pNumRows < 0 && |
|
| 133 | 13 | ($cellRow >= ($beforeRow + $pNumRows)) && |
|
| 134 | 13 | ($cellRow < $beforeRow)) { |
|
| 135 | return true; |
||
| 136 | 13 | } elseif ($pNumCols < 0 && |
|
| 137 | 13 | ($cellColumnIndex >= ($beforeColumnIndex + $pNumCols)) && |
|
| 138 | 13 | ($cellColumnIndex < $beforeColumnIndex)) { |
|
| 139 | return true; |
||
| 140 | } |
||
| 141 | |||
| 142 | 13 | return false; |
|
| 143 | } |
||
| 144 | |||
| 145 | /** |
||
| 146 | * Update page breaks when inserting/deleting rows/columns. |
||
| 147 | * |
||
| 148 | * @param Worksheet $pSheet The worksheet that we're editing |
||
| 149 | * @param string $pBefore Insert/Delete before this cell address (e.g. 'A1') |
||
| 150 | * @param int $beforeColumnIndex Index number of the column we're inserting/deleting before |
||
| 151 | * @param int $pNumCols Number of columns to insert/delete (negative values indicate deletion) |
||
| 152 | * @param int $beforeRow Number of the row we're inserting/deleting before |
||
| 153 | * @param int $pNumRows Number of rows to insert/delete (negative values indicate deletion) |
||
| 154 | */ |
||
| 155 | 15 | protected function adjustPageBreaks(Worksheet $pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows) |
|
| 173 | } |
||
| 174 | } |
||
| 175 | } |
||
| 176 | 15 | } |
|
| 177 | |||
| 178 | /** |
||
| 179 | * Update cell comments when inserting/deleting rows/columns. |
||
| 180 | * |
||
| 181 | * @param Worksheet $pSheet The worksheet that we're editing |
||
| 182 | * @param string $pBefore Insert/Delete before this cell address (e.g. 'A1') |
||
| 183 | * @param int $beforeColumnIndex Index number of the column we're inserting/deleting before |
||
| 184 | * @param int $pNumCols Number of columns to insert/delete (negative values indicate deletion) |
||
| 185 | * @param int $beforeRow Number of the row we're inserting/deleting before |
||
| 186 | * @param int $pNumRows Number of rows to insert/delete (negative values indicate deletion) |
||
| 187 | */ |
||
| 188 | 15 | protected function adjustComments($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows) |
|
| 203 | 15 | } |
|
| 204 | |||
| 205 | /** |
||
| 206 | * Update hyperlinks when inserting/deleting rows/columns. |
||
| 207 | * |
||
| 208 | * @param Worksheet $pSheet The worksheet that we're editing |
||
| 209 | * @param string $pBefore Insert/Delete before this cell address (e.g. 'A1') |
||
| 210 | * @param int $beforeColumnIndex Index number of the column we're inserting/deleting before |
||
| 211 | * @param int $pNumCols Number of columns to insert/delete (negative values indicate deletion) |
||
| 212 | * @param int $beforeRow Number of the row we're inserting/deleting before |
||
| 213 | * @param int $pNumRows Number of rows to insert/delete (negative values indicate deletion) |
||
| 214 | */ |
||
| 215 | 15 | protected function adjustHyperlinks($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows) |
|
| 216 | { |
||
| 217 | 15 | $aHyperlinkCollection = $pSheet->getHyperlinkCollection(); |
|
| 218 | 15 | ($pNumCols > 0 || $pNumRows > 0) ? |
|
| 219 | 15 | uksort($aHyperlinkCollection, ['self', 'cellReverseSort']) : uksort($aHyperlinkCollection, ['self', 'cellSort']); |
|
| 220 | |||
| 221 | 15 | foreach ($aHyperlinkCollection as $key => $value) { |
|
| 222 | 13 | $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows); |
|
| 223 | 13 | if ($key != $newReference) { |
|
| 224 | 13 | $pSheet->setHyperlink($newReference, $value); |
|
| 225 | 13 | $pSheet->setHyperlink($key, null); |
|
| 226 | } |
||
| 227 | } |
||
| 228 | 15 | } |
|
| 229 | |||
| 230 | /** |
||
| 231 | * Update data validations when inserting/deleting rows/columns. |
||
| 232 | * |
||
| 233 | * @param Worksheet $pSheet The worksheet that we're editing |
||
| 234 | * @param string $pBefore Insert/Delete before this cell address (e.g. 'A1') |
||
| 235 | * @param int $beforeColumnIndex Index number of the column we're inserting/deleting before |
||
| 236 | * @param int $pNumCols Number of columns to insert/delete (negative values indicate deletion) |
||
| 237 | * @param int $beforeRow Number of the row we're inserting/deleting before |
||
| 238 | * @param int $pNumRows Number of rows to insert/delete (negative values indicate deletion) |
||
| 239 | */ |
||
| 240 | 15 | protected function adjustDataValidations($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows) |
|
| 241 | { |
||
| 242 | 15 | $aDataValidationCollection = $pSheet->getDataValidationCollection(); |
|
| 243 | 15 | ($pNumCols > 0 || $pNumRows > 0) ? |
|
| 244 | 15 | uksort($aDataValidationCollection, ['self', 'cellReverseSort']) : uksort($aDataValidationCollection, ['self', 'cellSort']); |
|
| 245 | |||
| 246 | 15 | foreach ($aDataValidationCollection as $key => $value) { |
|
| 247 | $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows); |
||
| 248 | if ($key != $newReference) { |
||
| 249 | $pSheet->setDataValidation($newReference, $value); |
||
| 250 | $pSheet->setDataValidation($key, null); |
||
| 251 | } |
||
| 252 | } |
||
| 253 | 15 | } |
|
| 254 | |||
| 255 | /** |
||
| 256 | * Update merged cells when inserting/deleting rows/columns. |
||
| 257 | * |
||
| 258 | * @param Worksheet $pSheet The worksheet that we're editing |
||
| 259 | * @param string $pBefore Insert/Delete before this cell address (e.g. 'A1') |
||
| 260 | * @param int $beforeColumnIndex Index number of the column we're inserting/deleting before |
||
| 261 | * @param int $pNumCols Number of columns to insert/delete (negative values indicate deletion) |
||
| 262 | * @param int $beforeRow Number of the row we're inserting/deleting before |
||
| 263 | * @param int $pNumRows Number of rows to insert/delete (negative values indicate deletion) |
||
| 264 | */ |
||
| 265 | 15 | protected function adjustMergeCells($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows) |
|
| 266 | { |
||
| 267 | 15 | $aMergeCells = $pSheet->getMergeCells(); |
|
| 268 | 15 | $aNewMergeCells = []; // the new array of all merge cells |
|
| 269 | 15 | foreach ($aMergeCells as $key => &$value) { |
|
| 270 | 14 | $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows); |
|
| 271 | 14 | $aNewMergeCells[$newReference] = $newReference; |
|
| 272 | } |
||
| 273 | 15 | $pSheet->setMergeCells($aNewMergeCells); // replace the merge cells array |
|
| 274 | 15 | } |
|
| 275 | |||
| 276 | /** |
||
| 277 | * Update protected cells when inserting/deleting rows/columns. |
||
| 278 | * |
||
| 279 | * @param Worksheet $pSheet The worksheet that we're editing |
||
| 280 | * @param string $pBefore Insert/Delete before this cell address (e.g. 'A1') |
||
| 281 | * @param int $beforeColumnIndex Index number of the column we're inserting/deleting before |
||
| 282 | * @param int $pNumCols Number of columns to insert/delete (negative values indicate deletion) |
||
| 283 | * @param int $beforeRow Number of the row we're inserting/deleting before |
||
| 284 | * @param int $pNumRows Number of rows to insert/delete (negative values indicate deletion) |
||
| 285 | */ |
||
| 286 | 15 | protected function adjustProtectedCells($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows) |
|
| 296 | } |
||
| 297 | } |
||
| 298 | 15 | } |
|
| 299 | |||
| 300 | /** |
||
| 301 | * Update column dimensions when inserting/deleting rows/columns. |
||
| 302 | * |
||
| 303 | * @param Worksheet $pSheet The worksheet that we're editing |
||
| 304 | * @param string $pBefore Insert/Delete before this cell address (e.g. 'A1') |
||
| 305 | * @param int $beforeColumnIndex Index number of the column we're inserting/deleting before |
||
| 306 | * @param int $pNumCols Number of columns to insert/delete (negative values indicate deletion) |
||
| 307 | * @param int $beforeRow Number of the row we're inserting/deleting before |
||
| 308 | * @param int $pNumRows Number of rows to insert/delete (negative values indicate deletion) |
||
| 309 | */ |
||
| 310 | 15 | protected function adjustColumnDimensions($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows) |
|
| 322 | } |
||
| 323 | 15 | } |
|
| 324 | |||
| 325 | /** |
||
| 326 | * Update row dimensions when inserting/deleting rows/columns. |
||
| 327 | * |
||
| 328 | * @param Worksheet $pSheet The worksheet that we're editing |
||
| 329 | * @param string $pBefore Insert/Delete before this cell address (e.g. 'A1') |
||
| 330 | * @param int $beforeColumnIndex Index number of the column we're inserting/deleting before |
||
| 331 | * @param int $pNumCols Number of columns to insert/delete (negative values indicate deletion) |
||
| 332 | * @param int $beforeRow Number of the row we're inserting/deleting before |
||
| 333 | * @param int $pNumRows Number of rows to insert/delete (negative values indicate deletion) |
||
| 334 | */ |
||
| 335 | 15 | protected function adjustRowDimensions($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows) |
|
| 355 | } |
||
| 356 | } |
||
| 357 | 15 | } |
|
| 358 | |||
| 359 | /** |
||
| 360 | * Insert a new column or row, updating all possible related data. |
||
| 361 | * |
||
| 362 | * @param string $pBefore Insert before this cell address (e.g. 'A1') |
||
| 363 | * @param int $pNumCols Number of columns to insert/delete (negative values indicate deletion) |
||
| 364 | * @param int $pNumRows Number of rows to insert/delete (negative values indicate deletion) |
||
| 365 | * @param Worksheet $pSheet The worksheet that we're editing |
||
| 366 | * |
||
| 367 | * @throws Exception |
||
| 368 | */ |
||
| 369 | 15 | public function insertNewBefore($pBefore, $pNumCols, $pNumRows, Worksheet $pSheet) |
|
| 370 | { |
||
| 371 | 15 | $remove = ($pNumCols < 0 || $pNumRows < 0); |
|
| 372 | 15 | $allCoordinates = $pSheet->getCoordinates(); |
|
| 373 | |||
| 374 | // Get coordinate of $pBefore |
||
| 375 | 15 | list($beforeColumn, $beforeRow) = Coordinate::coordinateFromString($pBefore); |
|
| 376 | 15 | $beforeColumnIndex = Coordinate::columnIndexFromString($beforeColumn); |
|
| 377 | |||
| 378 | // Clear cells if we are removing columns or rows |
||
| 379 | 15 | $highestColumn = $pSheet->getHighestColumn(); |
|
| 380 | 15 | $highestRow = $pSheet->getHighestRow(); |
|
| 381 | |||
| 382 | // 1. Clear column strips if we are removing columns |
||
| 383 | 15 | if ($pNumCols < 0 && $beforeColumnIndex - 2 + $pNumCols > 0) { |
|
| 384 | 12 | for ($i = 1; $i <= $highestRow - 1; ++$i) { |
|
| 385 | 12 | for ($j = $beforeColumnIndex - 1 + $pNumCols; $j <= $beforeColumnIndex - 2; ++$j) { |
|
| 386 | 12 | $coordinate = Coordinate::stringFromColumnIndex($j + 1) . $i; |
|
| 387 | 12 | $pSheet->removeConditionalStyles($coordinate); |
|
| 388 | 12 | if ($pSheet->cellExists($coordinate)) { |
|
| 389 | 12 | $pSheet->getCell($coordinate)->setValueExplicit('', DataType::TYPE_NULL); |
|
| 390 | 12 | $pSheet->getCell($coordinate)->setXfIndex(0); |
|
| 391 | } |
||
| 392 | } |
||
| 393 | } |
||
| 394 | } |
||
| 395 | |||
| 396 | // 2. Clear row strips if we are removing rows |
||
| 397 | 15 | if ($pNumRows < 0 && $beforeRow - 1 + $pNumRows > 0) { |
|
| 398 | 14 | for ($i = $beforeColumnIndex - 1; $i <= Coordinate::columnIndexFromString($highestColumn) - 1; ++$i) { |
|
| 399 | 14 | for ($j = $beforeRow + $pNumRows; $j <= $beforeRow - 1; ++$j) { |
|
| 400 | 14 | $coordinate = Coordinate::stringFromColumnIndex($i + 1) . $j; |
|
| 401 | 14 | $pSheet->removeConditionalStyles($coordinate); |
|
| 402 | 14 | if ($pSheet->cellExists($coordinate)) { |
|
| 403 | 13 | $pSheet->getCell($coordinate)->setValueExplicit('', DataType::TYPE_NULL); |
|
| 404 | 13 | $pSheet->getCell($coordinate)->setXfIndex(0); |
|
| 405 | } |
||
| 406 | } |
||
| 407 | } |
||
| 408 | } |
||
| 409 | |||
| 410 | // Loop through cells, bottom-up, and change cell coordinate |
||
| 411 | 15 | if ($remove) { |
|
| 412 | // It's faster to reverse and pop than to use unshift, especially with large cell collections |
||
| 413 | 15 | $allCoordinates = array_reverse($allCoordinates); |
|
| 414 | } |
||
| 415 | 15 | while ($coordinate = array_pop($allCoordinates)) { |
|
| 416 | 15 | $cell = $pSheet->getCell($coordinate); |
|
| 417 | 15 | $cellIndex = Coordinate::columnIndexFromString($cell->getColumn()); |
|
| 418 | |||
| 419 | 15 | if ($cellIndex - 1 + $pNumCols < 0) { |
|
| 420 | 12 | continue; |
|
| 421 | } |
||
| 422 | |||
| 423 | // New coordinate |
||
| 424 | 15 | $newCoordinate = Coordinate::stringFromColumnIndex($cellIndex + $pNumCols) . ($cell->getRow() + $pNumRows); |
|
| 425 | |||
| 426 | // Should the cell be updated? Move value and cellXf index from one cell to another. |
||
| 427 | 15 | if (($cellIndex >= $beforeColumnIndex) && ($cell->getRow() >= $beforeRow)) { |
|
| 428 | // Update cell styles |
||
| 429 | 15 | $pSheet->getCell($newCoordinate)->setXfIndex($cell->getXfIndex()); |
|
| 430 | |||
| 431 | // Insert this cell at its new location |
||
| 432 | 15 | if ($cell->getDataType() == DataType::TYPE_FORMULA) { |
|
| 433 | // Formula should be adjusted |
||
| 434 | 13 | $pSheet->getCell($newCoordinate) |
|
| 435 | 13 | ->setValue($this->updateFormulaReferences($cell->getValue(), $pBefore, $pNumCols, $pNumRows, $pSheet->getTitle())); |
|
| 436 | } else { |
||
| 437 | // Formula should not be adjusted |
||
| 438 | 15 | $pSheet->getCell($newCoordinate)->setValue($cell->getValue()); |
|
| 439 | } |
||
| 440 | |||
| 441 | // Clear the original cell |
||
| 442 | 15 | $pSheet->getCellCollection()->delete($coordinate); |
|
| 443 | } else { |
||
| 444 | /* We don't need to update styles for rows/columns before our insertion position, |
||
| 445 | but we do still need to adjust any formulae in those cells */ |
||
| 446 | 15 | if ($cell->getDataType() == DataType::TYPE_FORMULA) { |
|
| 447 | // Formula should be adjusted |
||
| 448 | 13 | $cell->setValue($this->updateFormulaReferences($cell->getValue(), $pBefore, $pNumCols, $pNumRows, $pSheet->getTitle())); |
|
| 449 | } |
||
| 450 | } |
||
| 451 | } |
||
| 452 | |||
| 453 | // Duplicate styles for the newly inserted cells |
||
| 454 | 15 | $highestColumn = $pSheet->getHighestColumn(); |
|
| 455 | 15 | $highestRow = $pSheet->getHighestRow(); |
|
| 456 | |||
| 457 | 15 | if ($pNumCols > 0 && $beforeColumnIndex - 2 > 0) { |
|
| 458 | 12 | for ($i = $beforeRow; $i <= $highestRow - 1; ++$i) { |
|
| 459 | // Style |
||
| 460 | 12 | $coordinate = Coordinate::stringFromColumnIndex($beforeColumnIndex - 1) . $i; |
|
| 461 | 12 | if ($pSheet->cellExists($coordinate)) { |
|
| 462 | 12 | $xfIndex = $pSheet->getCell($coordinate)->getXfIndex(); |
|
| 463 | 12 | $conditionalStyles = $pSheet->conditionalStylesExists($coordinate) ? |
|
| 464 | 12 | $pSheet->getConditionalStyles($coordinate) : false; |
|
| 465 | 12 | for ($j = $beforeColumnIndex; $j <= $beforeColumnIndex - 1 + $pNumCols; ++$j) { |
|
| 466 | 12 | $pSheet->getCellByColumnAndRow($j, $i)->setXfIndex($xfIndex); |
|
| 467 | 12 | if ($conditionalStyles) { |
|
| 468 | $cloned = []; |
||
| 469 | foreach ($conditionalStyles as $conditionalStyle) { |
||
| 470 | $cloned[] = clone $conditionalStyle; |
||
| 471 | } |
||
| 472 | $pSheet->setConditionalStyles(Coordinate::stringFromColumnIndex($j) . $i, $cloned); |
||
| 473 | } |
||
| 474 | } |
||
| 475 | } |
||
| 476 | } |
||
| 477 | } |
||
| 478 | |||
| 479 | 15 | if ($pNumRows > 0 && $beforeRow - 1 > 0) { |
|
| 480 | 13 | for ($i = $beforeColumnIndex; $i <= Coordinate::columnIndexFromString($highestColumn); ++$i) { |
|
| 481 | // Style |
||
| 482 | 13 | $coordinate = Coordinate::stringFromColumnIndex($i) . ($beforeRow - 1); |
|
| 483 | 13 | if ($pSheet->cellExists($coordinate)) { |
|
| 484 | 13 | $xfIndex = $pSheet->getCell($coordinate)->getXfIndex(); |
|
| 485 | 13 | $conditionalStyles = $pSheet->conditionalStylesExists($coordinate) ? |
|
| 486 | 13 | $pSheet->getConditionalStyles($coordinate) : false; |
|
| 487 | 13 | for ($j = $beforeRow; $j <= $beforeRow - 1 + $pNumRows; ++$j) { |
|
| 488 | 13 | $pSheet->getCell(Coordinate::stringFromColumnIndex($i) . $j)->setXfIndex($xfIndex); |
|
| 489 | 13 | if ($conditionalStyles) { |
|
| 490 | $cloned = []; |
||
| 491 | foreach ($conditionalStyles as $conditionalStyle) { |
||
| 492 | $cloned[] = clone $conditionalStyle; |
||
| 493 | } |
||
| 494 | $pSheet->setConditionalStyles(Coordinate::stringFromColumnIndex($i) . $j, $cloned); |
||
| 495 | } |
||
| 496 | } |
||
| 497 | } |
||
| 498 | } |
||
| 499 | } |
||
| 500 | |||
| 501 | // Update worksheet: column dimensions |
||
| 502 | 15 | $this->adjustColumnDimensions($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows); |
|
| 503 | |||
| 504 | // Update worksheet: row dimensions |
||
| 505 | 15 | $this->adjustRowDimensions($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows); |
|
| 506 | |||
| 507 | // Update worksheet: page breaks |
||
| 508 | 15 | $this->adjustPageBreaks($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows); |
|
| 509 | |||
| 510 | // Update worksheet: comments |
||
| 511 | 15 | $this->adjustComments($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows); |
|
| 512 | |||
| 513 | // Update worksheet: hyperlinks |
||
| 514 | 15 | $this->adjustHyperlinks($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows); |
|
| 515 | |||
| 516 | // Update worksheet: data validations |
||
| 517 | 15 | $this->adjustDataValidations($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows); |
|
| 518 | |||
| 519 | // Update worksheet: merge cells |
||
| 520 | 15 | $this->adjustMergeCells($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows); |
|
| 521 | |||
| 522 | // Update worksheet: protected cells |
||
| 523 | 15 | $this->adjustProtectedCells($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows); |
|
| 524 | |||
| 525 | // Update worksheet: autofilter |
||
| 526 | 15 | $autoFilter = $pSheet->getAutoFilter(); |
|
| 527 | 15 | $autoFilterRange = $autoFilter->getRange(); |
|
| 528 | 15 | if (!empty($autoFilterRange)) { |
|
| 529 | if ($pNumCols != 0) { |
||
| 530 | $autoFilterColumns = $autoFilter->getColumns(); |
||
| 531 | if (count($autoFilterColumns) > 0) { |
||
| 532 | sscanf($pBefore, '%[A-Z]%d', $column, $row); |
||
| 533 | $columnIndex = Coordinate::columnIndexFromString($column); |
||
| 534 | list($rangeStart, $rangeEnd) = Coordinate::rangeBoundaries($autoFilterRange); |
||
| 535 | if ($columnIndex <= $rangeEnd[0]) { |
||
| 536 | if ($pNumCols < 0) { |
||
| 537 | // If we're actually deleting any columns that fall within the autofilter range, |
||
| 538 | // then we delete any rules for those columns |
||
| 539 | $deleteColumn = $columnIndex + $pNumCols - 1; |
||
| 540 | $deleteCount = abs($pNumCols); |
||
| 541 | for ($i = 1; $i <= $deleteCount; ++$i) { |
||
| 542 | if (isset($autoFilterColumns[Coordinate::stringFromColumnIndex($deleteColumn + 1)])) { |
||
| 543 | $autoFilter->clearColumn(Coordinate::stringFromColumnIndex($deleteColumn + 1)); |
||
| 544 | } |
||
| 545 | ++$deleteColumn; |
||
| 546 | } |
||
| 547 | } |
||
| 548 | $startCol = ($columnIndex > $rangeStart[0]) ? $columnIndex : $rangeStart[0]; |
||
| 549 | |||
| 550 | // Shuffle columns in autofilter range |
||
| 551 | if ($pNumCols > 0) { |
||
| 552 | $startColRef = $startCol; |
||
| 553 | $endColRef = $rangeEnd[0]; |
||
| 554 | $toColRef = $rangeEnd[0] + $pNumCols; |
||
| 555 | |||
| 556 | do { |
||
| 557 | $autoFilter->shiftColumn(Coordinate::stringFromColumnIndex($endColRef), Coordinate::stringFromColumnIndex($toColRef)); |
||
| 558 | --$endColRef; |
||
| 559 | --$toColRef; |
||
| 560 | } while ($startColRef <= $endColRef); |
||
| 561 | } else { |
||
| 562 | // For delete, we shuffle from beginning to end to avoid overwriting |
||
| 563 | $startColID = Coordinate::stringFromColumnIndex($startCol); |
||
| 564 | $toColID = Coordinate::stringFromColumnIndex($startCol + $pNumCols); |
||
| 565 | $endColID = Coordinate::stringFromColumnIndex($rangeEnd[0] + 1); |
||
| 566 | do { |
||
| 567 | $autoFilter->shiftColumn($startColID, $toColID); |
||
| 568 | ++$startColID; |
||
| 569 | ++$toColID; |
||
| 570 | } while ($startColID != $endColID); |
||
| 571 | } |
||
| 572 | } |
||
| 573 | } |
||
| 574 | } |
||
| 575 | $pSheet->setAutoFilter($this->updateCellReference($autoFilterRange, $pBefore, $pNumCols, $pNumRows)); |
||
| 576 | } |
||
| 577 | |||
| 578 | // Update worksheet: freeze pane |
||
| 579 | 15 | if ($pSheet->getFreezePane()) { |
|
| 580 | 1 | $splitCell = $pSheet->getFreezePane(); |
|
| 581 | 1 | $topLeftCell = $pSheet->getTopLeftCell(); |
|
| 582 | |||
| 583 | 1 | $splitCell = $this->updateCellReference($splitCell, $pBefore, $pNumCols, $pNumRows); |
|
| 584 | 1 | $topLeftCell = $this->updateCellReference($topLeftCell, $pBefore, $pNumCols, $pNumRows); |
|
| 585 | |||
| 586 | 1 | $pSheet->freezePane($splitCell, $topLeftCell); |
|
| 587 | } |
||
| 588 | |||
| 589 | // Page setup |
||
| 590 | 15 | if ($pSheet->getPageSetup()->isPrintAreaSet()) { |
|
| 591 | $pSheet->getPageSetup()->setPrintArea($this->updateCellReference($pSheet->getPageSetup()->getPrintArea(), $pBefore, $pNumCols, $pNumRows)); |
||
| 592 | } |
||
| 593 | |||
| 594 | // Update worksheet: drawings |
||
| 595 | 15 | $aDrawings = $pSheet->getDrawingCollection(); |
|
| 596 | 15 | foreach ($aDrawings as $objDrawing) { |
|
| 597 | 13 | $newReference = $this->updateCellReference($objDrawing->getCoordinates(), $pBefore, $pNumCols, $pNumRows); |
|
| 598 | 13 | if ($objDrawing->getCoordinates() != $newReference) { |
|
| 599 | 13 | $objDrawing->setCoordinates($newReference); |
|
| 600 | } |
||
| 601 | } |
||
| 602 | |||
| 603 | // Update workbook: named ranges |
||
| 604 | 15 | if (count($pSheet->getParent()->getNamedRanges()) > 0) { |
|
| 605 | 1 | foreach ($pSheet->getParent()->getNamedRanges() as $namedRange) { |
|
| 606 | 1 | if ($namedRange->getWorksheet()->getHashCode() == $pSheet->getHashCode()) { |
|
| 607 | 1 | $namedRange->setRange($this->updateCellReference($namedRange->getRange(), $pBefore, $pNumCols, $pNumRows)); |
|
| 608 | } |
||
| 609 | } |
||
| 610 | } |
||
| 611 | |||
| 612 | // Garbage collect |
||
| 613 | 15 | $pSheet->garbageCollect(); |
|
| 614 | 15 | } |
|
| 615 | |||
| 616 | /** |
||
| 617 | * Update references within formulas. |
||
| 618 | * |
||
| 619 | * @param string $pFormula Formula to update |
||
| 620 | * @param int $pBefore Insert before this one |
||
| 621 | * @param int $pNumCols Number of columns to insert |
||
| 622 | * @param int $pNumRows Number of rows to insert |
||
| 623 | * @param string $sheetName Worksheet name/title |
||
| 624 | * |
||
| 625 | * @throws Exception |
||
| 626 | * |
||
| 627 | * @return string Updated formula |
||
| 628 | */ |
||
| 629 | 16 | public function updateFormulaReferences($pFormula = '', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0, $sheetName = '') |
|
| 757 | } |
||
| 758 | |||
| 759 | /** |
||
| 760 | * Update cell reference. |
||
| 761 | * |
||
| 762 | * @param string $pCellRange Cell range |
||
| 763 | * @param string $pBefore Insert before this one |
||
| 764 | * @param int $pNumCols Number of columns to increment |
||
| 765 | * @param int $pNumRows Number of rows to increment |
||
| 766 | * |
||
| 767 | * @throws Exception |
||
| 768 | * |
||
| 769 | * @return string Updated cell range |
||
| 770 | */ |
||
| 771 | 18 | public function updateCellReference($pCellRange = 'A1', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0) |
|
| 772 | { |
||
| 773 | // Is it in another worksheet? Will not have to update anything. |
||
| 774 | 18 | if (strpos($pCellRange, '!') !== false) { |
|
| 775 | return $pCellRange; |
||
| 776 | // Is it a range or a single cell? |
||
| 777 | 18 | } elseif (!Coordinate::coordinateIsRange($pCellRange)) { |
|
| 778 | // Single cell |
||
| 779 | 18 | return $this->updateSingleCellReference($pCellRange, $pBefore, $pNumCols, $pNumRows); |
|
| 780 | 14 | } elseif (Coordinate::coordinateIsRange($pCellRange)) { |
|
| 781 | // Range |
||
| 782 | 14 | return $this->updateCellRange($pCellRange, $pBefore, $pNumCols, $pNumRows); |
|
| 783 | } |
||
| 784 | |||
| 785 | // Return original |
||
| 786 | return $pCellRange; |
||
| 787 | } |
||
| 788 | |||
| 789 | /** |
||
| 790 | * Update named formulas (i.e. containing worksheet references / named ranges). |
||
| 791 | * |
||
| 792 | * @param Spreadsheet $spreadsheet Object to update |
||
| 793 | * @param string $oldName Old name (name to replace) |
||
| 794 | * @param string $newName New name |
||
| 795 | */ |
||
| 796 | 38 | public function updateNamedFormulas(Spreadsheet $spreadsheet, $oldName = '', $newName = '') |
|
| 797 | { |
||
| 798 | 38 | if ($oldName == '') { |
|
| 799 | return; |
||
| 800 | } |
||
| 801 | |||
| 802 | 38 | foreach ($spreadsheet->getWorksheetIterator() as $sheet) { |
|
| 803 | 38 | foreach ($sheet->getCoordinates(false) as $coordinate) { |
|
| 804 | 29 | $cell = $sheet->getCell($coordinate); |
|
| 805 | 29 | if (($cell !== null) && ($cell->getDataType() == DataType::TYPE_FORMULA)) { |
|
| 806 | 20 | $formula = $cell->getValue(); |
|
| 807 | 20 | if (strpos($formula, $oldName) !== false) { |
|
| 808 | $formula = str_replace("'" . $oldName . "'!", "'" . $newName . "'!", $formula); |
||
| 809 | $formula = str_replace($oldName . '!', $newName . '!', $formula); |
||
| 810 | 38 | $cell->setValueExplicit($formula, DataType::TYPE_FORMULA); |
|
| 811 | } |
||
| 812 | } |
||
| 813 | } |
||
| 814 | } |
||
| 815 | 38 | } |
|
| 816 | |||
| 817 | /** |
||
| 818 | * Update cell range. |
||
| 819 | * |
||
| 820 | * @param string $pCellRange Cell range (e.g. 'B2:D4', 'B:C' or '2:3') |
||
| 821 | * @param string $pBefore Insert before this one |
||
| 822 | * @param int $pNumCols Number of columns to increment |
||
| 823 | * @param int $pNumRows Number of rows to increment |
||
| 824 | * |
||
| 825 | * @throws Exception |
||
| 826 | * |
||
| 827 | * @return string Updated cell range |
||
| 828 | */ |
||
| 829 | 14 | private function updateCellRange($pCellRange = 'A1:A1', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0) |
|
| 830 | { |
||
| 831 | 14 | if (!Coordinate::coordinateIsRange($pCellRange)) { |
|
| 832 | throw new Exception('Only cell ranges may be passed to this method.'); |
||
| 833 | } |
||
| 834 | |||
| 835 | // Update range |
||
| 836 | 14 | $range = Coordinate::splitRange($pCellRange); |
|
| 837 | 14 | $ic = count($range); |
|
| 838 | 14 | for ($i = 0; $i < $ic; ++$i) { |
|
| 839 | 14 | $jc = count($range[$i]); |
|
| 840 | 14 | for ($j = 0; $j < $jc; ++$j) { |
|
| 841 | 14 | if (ctype_alpha($range[$i][$j])) { |
|
| 842 | $r = Coordinate::coordinateFromString($this->updateSingleCellReference($range[$i][$j] . '1', $pBefore, $pNumCols, $pNumRows)); |
||
| 843 | $range[$i][$j] = $r[0]; |
||
| 844 | 14 | } elseif (ctype_digit($range[$i][$j])) { |
|
| 845 | $r = Coordinate::coordinateFromString($this->updateSingleCellReference('A' . $range[$i][$j], $pBefore, $pNumCols, $pNumRows)); |
||
| 846 | $range[$i][$j] = $r[1]; |
||
| 847 | } else { |
||
| 848 | 14 | $range[$i][$j] = $this->updateSingleCellReference($range[$i][$j], $pBefore, $pNumCols, $pNumRows); |
|
| 849 | } |
||
| 850 | } |
||
| 851 | } |
||
| 852 | |||
| 853 | // Recreate range string |
||
| 854 | 14 | return Coordinate::buildRange($range); |
|
| 855 | } |
||
| 856 | |||
| 857 | /** |
||
| 858 | * Update single cell reference. |
||
| 859 | * |
||
| 860 | * @param string $pCellReference Single cell reference |
||
| 861 | * @param string $pBefore Insert before this one |
||
| 862 | * @param int $pNumCols Number of columns to increment |
||
| 863 | * @param int $pNumRows Number of rows to increment |
||
| 864 | * |
||
| 865 | * @throws Exception |
||
| 866 | * |
||
| 867 | * @return string Updated cell reference |
||
| 868 | */ |
||
| 869 | 18 | private function updateSingleCellReference($pCellReference = 'A1', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0) |
|
| 897 | } |
||
| 898 | |||
| 899 | /** |
||
| 900 | * __clone implementation. Cloning should not be allowed in a Singleton! |
||
| 901 | * |
||
| 902 | * @throws Exception |
||
| 903 | */ |
||
| 904 | final public function __clone() |
||
| 907 | } |
||
| 908 | } |
||
| 909 |
Loading history...
