PHPOffice /
PhpSpreadsheet
| Total Complexity | 167 |
| Total Lines | 906 |
| Duplicated Lines | 0 % |
| Coverage | 74.81% |
| 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 | 82 | public static function getInstance() |
|
| 31 | { |
||
| 32 | 82 | if (!isset(self::$instance) || (self::$instance === null)) { |
|
| 33 | 40 | self::$instance = new self(); |
|
| 34 | } |
||
| 35 | |||
| 36 | 82 | 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) |
|
| 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 | 12 | public static function cellSort($a, $b) |
|
| 84 | { |
||
| 85 | 12 | $ac = $bc = ''; |
|
| 86 | 12 | $ar = $br = 0; |
|
| 87 | |||
| 88 | 12 | sscanf($a, '%[A-Z]%d', $ac, $ar); |
|
| 89 | 12 | sscanf($b, '%[A-Z]%d', $bc, $br); |
|
| 90 | |||
| 91 | 12 | if ($ar == $br) { |
|
|
introduced
by
|
|||
| 92 | return strcasecmp(strlen($ac) . $ac, strlen($bc) . $bc); |
||
| 93 | } |
||
| 94 | |||
| 95 | 12 | return ($ar < $br) ? -1 : 1; |
|
| 96 | } |
||
| 97 | |||
| 98 | /** |
||
| 99 | * Compare two cell addresses |
||
| 100 | * Intended for use as a Callback function for sorting cell addresses by column and row. |
||
| 101 | * |
||
| 102 | * @param string $a First cell to test (e.g. 'AA1') |
||
| 103 | * @param string $b Second cell to test (e.g. 'Z1') |
||
| 104 | * |
||
| 105 | * @return int |
||
| 106 | */ |
||
| 107 | 12 | public static function cellReverseSort($a, $b) |
|
| 108 | { |
||
| 109 | 12 | $ac = $bc = ''; |
|
| 110 | 12 | $ar = $br = 0; |
|
| 111 | |||
| 112 | 12 | sscanf($a, '%[A-Z]%d', $ac, $ar); |
|
| 113 | 12 | sscanf($b, '%[A-Z]%d', $bc, $br); |
|
| 114 | |||
| 115 | 12 | if ($ar == $br) { |
|
| 116 | return 1 - strcasecmp(strlen($ac) . $ac, strlen($bc) . $bc); |
||
| 117 | } |
||
| 118 | |||
| 119 | 12 | return ($ar < $br) ? 1 : -1; |
|
| 120 | } |
||
| 121 | |||
| 122 | /** |
||
| 123 | * Test whether a cell address falls within a defined range of cells. |
||
| 124 | * |
||
| 125 | * @param string $cellAddress Address of the cell we're testing |
||
| 126 | * @param int $beforeRow Number of the row we're inserting/deleting before |
||
| 127 | * @param int $pNumRows Number of rows to insert/delete (negative values indicate deletion) |
||
| 128 | * @param int $beforeColumnIndex Index number of the column we're inserting/deleting before |
||
| 129 | * @param int $pNumCols Number of columns to insert/delete (negative values indicate deletion) |
||
| 130 | * |
||
| 131 | * @return bool |
||
| 132 | */ |
||
| 133 | 12 | private static function cellAddressInDeleteRange($cellAddress, $beforeRow, $pNumRows, $beforeColumnIndex, $pNumCols) |
|
| 134 | { |
||
| 135 | 12 | list($cellColumn, $cellRow) = Coordinate::coordinateFromString($cellAddress); |
|
| 136 | 12 | $cellColumnIndex = Coordinate::columnIndexFromString($cellColumn); |
|
| 137 | // Is cell within the range of rows/columns if we're deleting |
||
| 138 | 12 | if ($pNumRows < 0 && |
|
| 139 | 12 | ($cellRow >= ($beforeRow + $pNumRows)) && |
|
| 140 | 12 | ($cellRow < $beforeRow)) { |
|
| 141 | return true; |
||
| 142 | 12 | } elseif ($pNumCols < 0 && |
|
| 143 | 12 | ($cellColumnIndex >= ($beforeColumnIndex + $pNumCols)) && |
|
| 144 | 12 | ($cellColumnIndex < $beforeColumnIndex)) { |
|
| 145 | return true; |
||
| 146 | } |
||
| 147 | |||
| 148 | 12 | return false; |
|
| 149 | } |
||
| 150 | |||
| 151 | /** |
||
| 152 | * Update page breaks when inserting/deleting rows/columns. |
||
| 153 | * |
||
| 154 | * @param Worksheet $pSheet The worksheet that we're editing |
||
| 155 | * @param string $pBefore Insert/Delete before this cell address (e.g. 'A1') |
||
| 156 | * @param int $beforeColumnIndex Index number of the column we're inserting/deleting before |
||
| 157 | * @param int $pNumCols Number of columns to insert/delete (negative values indicate deletion) |
||
| 158 | * @param int $beforeRow Number of the row we're inserting/deleting before |
||
| 159 | * @param int $pNumRows Number of rows to insert/delete (negative values indicate deletion) |
||
| 160 | */ |
||
| 161 | 14 | protected function adjustPageBreaks(Worksheet $pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows) |
|
| 162 | { |
||
| 163 | 14 | $aBreaks = $pSheet->getBreaks(); |
|
| 164 | 14 | ($pNumCols > 0 || $pNumRows > 0) ? |
|
| 165 | 14 | uksort($aBreaks, ['self', 'cellReverseSort']) : uksort($aBreaks, ['self', 'cellSort']); |
|
| 166 | |||
| 167 | 14 | foreach ($aBreaks as $key => $value) { |
|
| 168 | if (self::cellAddressInDeleteRange($key, $beforeRow, $pNumRows, $beforeColumnIndex, $pNumCols)) { |
||
| 169 | // If we're deleting, then clear any defined breaks that are within the range |
||
| 170 | // of rows/columns that we're deleting |
||
| 171 | $pSheet->setBreak($key, Worksheet::BREAK_NONE); |
||
| 172 | } else { |
||
| 173 | // Otherwise update any affected breaks by inserting a new break at the appropriate point |
||
| 174 | // and removing the old affected break |
||
| 175 | $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows); |
||
| 176 | if ($key != $newReference) { |
||
| 177 | $pSheet->setBreak($newReference, $value) |
||
| 178 | ->setBreak($key, Worksheet::BREAK_NONE); |
||
| 179 | } |
||
| 180 | } |
||
| 181 | } |
||
| 182 | 14 | } |
|
| 183 | |||
| 184 | /** |
||
| 185 | * Update cell comments when inserting/deleting rows/columns. |
||
| 186 | * |
||
| 187 | * @param Worksheet $pSheet The worksheet that we're editing |
||
| 188 | * @param string $pBefore Insert/Delete before this cell address (e.g. 'A1') |
||
| 189 | * @param int $beforeColumnIndex Index number of the column we're inserting/deleting before |
||
| 190 | * @param int $pNumCols Number of columns to insert/delete (negative values indicate deletion) |
||
| 191 | * @param int $beforeRow Number of the row we're inserting/deleting before |
||
| 192 | * @param int $pNumRows Number of rows to insert/delete (negative values indicate deletion) |
||
| 193 | */ |
||
| 194 | 14 | protected function adjustComments($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows) |
|
| 195 | { |
||
| 196 | 14 | $aComments = $pSheet->getComments(); |
|
| 197 | 14 | $aNewComments = []; // the new array of all comments |
|
| 198 | |||
| 199 | 14 | foreach ($aComments as $key => &$value) { |
|
| 200 | // Any comments inside a deleted range will be ignored |
||
| 201 | 12 | if (!self::cellAddressInDeleteRange($key, $beforeRow, $pNumRows, $beforeColumnIndex, $pNumCols)) { |
|
| 202 | // Otherwise build a new array of comments indexed by the adjusted cell reference |
||
| 203 | 12 | $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows); |
|
| 204 | 12 | $aNewComments[$newReference] = $value; |
|
| 205 | } |
||
| 206 | } |
||
| 207 | // Replace the comments array with the new set of comments |
||
| 208 | 14 | $pSheet->setComments($aNewComments); |
|
| 209 | 14 | } |
|
| 210 | |||
| 211 | /** |
||
| 212 | * Update hyperlinks when inserting/deleting rows/columns. |
||
| 213 | * |
||
| 214 | * @param Worksheet $pSheet The worksheet that we're editing |
||
| 215 | * @param string $pBefore Insert/Delete before this cell address (e.g. 'A1') |
||
| 216 | * @param int $beforeColumnIndex Index number of the column we're inserting/deleting before |
||
| 217 | * @param int $pNumCols Number of columns to insert/delete (negative values indicate deletion) |
||
| 218 | * @param int $beforeRow Number of the row we're inserting/deleting before |
||
| 219 | * @param int $pNumRows Number of rows to insert/delete (negative values indicate deletion) |
||
| 220 | */ |
||
| 221 | 14 | protected function adjustHyperlinks($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows) |
|
| 222 | { |
||
| 223 | 14 | $aHyperlinkCollection = $pSheet->getHyperlinkCollection(); |
|
| 224 | 14 | ($pNumCols > 0 || $pNumRows > 0) ? |
|
| 225 | 14 | uksort($aHyperlinkCollection, ['self', 'cellReverseSort']) : uksort($aHyperlinkCollection, ['self', 'cellSort']); |
|
| 226 | |||
| 227 | 14 | foreach ($aHyperlinkCollection as $key => $value) { |
|
| 228 | 12 | $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows); |
|
| 229 | 12 | if ($key != $newReference) { |
|
| 230 | 12 | $pSheet->setHyperlink($newReference, $value); |
|
| 231 | 12 | $pSheet->setHyperlink($key, null); |
|
| 232 | } |
||
| 233 | } |
||
| 234 | 14 | } |
|
| 235 | |||
| 236 | /** |
||
| 237 | * Update data validations when inserting/deleting rows/columns. |
||
| 238 | * |
||
| 239 | * @param Worksheet $pSheet The worksheet that we're editing |
||
| 240 | * @param string $pBefore Insert/Delete before this cell address (e.g. 'A1') |
||
| 241 | * @param int $beforeColumnIndex Index number of the column we're inserting/deleting before |
||
| 242 | * @param int $pNumCols Number of columns to insert/delete (negative values indicate deletion) |
||
| 243 | * @param int $beforeRow Number of the row we're inserting/deleting before |
||
| 244 | * @param int $pNumRows Number of rows to insert/delete (negative values indicate deletion) |
||
| 245 | */ |
||
| 246 | 14 | protected function adjustDataValidations($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows) |
|
| 257 | } |
||
| 258 | } |
||
| 259 | 14 | } |
|
| 260 | |||
| 261 | /** |
||
| 262 | * Update merged cells when inserting/deleting rows/columns. |
||
| 263 | * |
||
| 264 | * @param Worksheet $pSheet The worksheet that we're editing |
||
| 265 | * @param string $pBefore Insert/Delete before this cell address (e.g. 'A1') |
||
| 266 | * @param int $beforeColumnIndex Index number of the column we're inserting/deleting before |
||
| 267 | * @param int $pNumCols Number of columns to insert/delete (negative values indicate deletion) |
||
| 268 | * @param int $beforeRow Number of the row we're inserting/deleting before |
||
| 269 | * @param int $pNumRows Number of rows to insert/delete (negative values indicate deletion) |
||
| 270 | */ |
||
| 271 | 14 | protected function adjustMergeCells($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows) |
|
| 272 | { |
||
| 273 | 14 | $aMergeCells = $pSheet->getMergeCells(); |
|
| 274 | 14 | $aNewMergeCells = []; // the new array of all merge cells |
|
| 275 | 14 | foreach ($aMergeCells as $key => &$value) { |
|
| 276 | 13 | $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows); |
|
| 277 | 13 | $aNewMergeCells[$newReference] = $newReference; |
|
| 278 | } |
||
| 279 | 14 | $pSheet->setMergeCells($aNewMergeCells); // replace the merge cells array |
|
| 280 | 14 | } |
|
| 281 | |||
| 282 | /** |
||
| 283 | * Update protected cells when inserting/deleting rows/columns. |
||
| 284 | * |
||
| 285 | * @param Worksheet $pSheet The worksheet that we're editing |
||
| 286 | * @param string $pBefore Insert/Delete before this cell address (e.g. 'A1') |
||
| 287 | * @param int $beforeColumnIndex Index number of the column we're inserting/deleting before |
||
| 288 | * @param int $pNumCols Number of columns to insert/delete (negative values indicate deletion) |
||
| 289 | * @param int $beforeRow Number of the row we're inserting/deleting before |
||
| 290 | * @param int $pNumRows Number of rows to insert/delete (negative values indicate deletion) |
||
| 291 | */ |
||
| 292 | 14 | protected function adjustProtectedCells($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows) |
|
| 293 | { |
||
| 294 | 14 | $aProtectedCells = $pSheet->getProtectedCells(); |
|
| 295 | 14 | ($pNumCols > 0 || $pNumRows > 0) ? |
|
| 296 | 14 | uksort($aProtectedCells, ['self', 'cellReverseSort']) : uksort($aProtectedCells, ['self', 'cellSort']); |
|
| 297 | 14 | foreach ($aProtectedCells as $key => $value) { |
|
| 298 | 11 | $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows); |
|
| 299 | 11 | if ($key != $newReference) { |
|
| 300 | 11 | $pSheet->protectCells($newReference, $value, true); |
|
| 301 | 11 | $pSheet->unprotectCells($key); |
|
| 302 | } |
||
| 303 | } |
||
| 304 | 14 | } |
|
| 305 | |||
| 306 | /** |
||
| 307 | * Update column dimensions when inserting/deleting rows/columns. |
||
| 308 | * |
||
| 309 | * @param Worksheet $pSheet The worksheet that we're editing |
||
| 310 | * @param string $pBefore Insert/Delete before this cell address (e.g. 'A1') |
||
| 311 | * @param int $beforeColumnIndex Index number of the column we're inserting/deleting before |
||
| 312 | * @param int $pNumCols Number of columns to insert/delete (negative values indicate deletion) |
||
| 313 | * @param int $beforeRow Number of the row we're inserting/deleting before |
||
| 314 | * @param int $pNumRows Number of rows to insert/delete (negative values indicate deletion) |
||
| 315 | */ |
||
| 316 | 14 | protected function adjustColumnDimensions($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows) |
|
| 328 | } |
||
| 329 | 14 | } |
|
| 330 | |||
| 331 | /** |
||
| 332 | * Update row dimensions when inserting/deleting rows/columns. |
||
| 333 | * |
||
| 334 | * @param Worksheet $pSheet The worksheet that we're editing |
||
| 335 | * @param string $pBefore Insert/Delete before this cell address (e.g. 'A1') |
||
| 336 | * @param int $beforeColumnIndex Index number of the column we're inserting/deleting before |
||
| 337 | * @param int $pNumCols Number of columns to insert/delete (negative values indicate deletion) |
||
| 338 | * @param int $beforeRow Number of the row we're inserting/deleting before |
||
| 339 | * @param int $pNumRows Number of rows to insert/delete (negative values indicate deletion) |
||
| 340 | */ |
||
| 341 | 14 | protected function adjustRowDimensions($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows) |
|
| 342 | { |
||
| 343 | 14 | $aRowDimensions = array_reverse($pSheet->getRowDimensions(), true); |
|
| 344 | 14 | if (!empty($aRowDimensions)) { |
|
| 345 | 2 | foreach ($aRowDimensions as $objRowDimension) { |
|
| 346 | 2 | $newReference = $this->updateCellReference('A' . $objRowDimension->getRowIndex(), $pBefore, $pNumCols, $pNumRows); |
|
| 347 | 2 | list(, $newReference) = Coordinate::coordinateFromString($newReference); |
|
| 348 | 2 | if ($objRowDimension->getRowIndex() != $newReference) { |
|
| 349 | 2 | $objRowDimension->setRowIndex($newReference); |
|
| 350 | } |
||
| 351 | } |
||
| 352 | 2 | $pSheet->refreshRowDimensions(); |
|
| 353 | |||
| 354 | 2 | $copyDimension = $pSheet->getRowDimension($beforeRow - 1); |
|
| 355 | 2 | for ($i = $beforeRow; $i <= $beforeRow - 1 + $pNumRows; ++$i) { |
|
| 356 | 1 | $newDimension = $pSheet->getRowDimension($i); |
|
| 357 | 1 | $newDimension->setRowHeight($copyDimension->getRowHeight()); |
|
| 358 | 1 | $newDimension->setVisible($copyDimension->getVisible()); |
|
| 359 | 1 | $newDimension->setOutlineLevel($copyDimension->getOutlineLevel()); |
|
| 360 | 1 | $newDimension->setCollapsed($copyDimension->getCollapsed()); |
|
| 361 | } |
||
| 362 | } |
||
| 363 | 14 | } |
|
| 364 | |||
| 365 | /** |
||
| 366 | * Insert a new column or row, updating all possible related data. |
||
| 367 | * |
||
| 368 | * @param string $pBefore Insert before this cell address (e.g. 'A1') |
||
| 369 | * @param int $pNumCols Number of columns to insert/delete (negative values indicate deletion) |
||
| 370 | * @param int $pNumRows Number of rows to insert/delete (negative values indicate deletion) |
||
| 371 | * @param Worksheet $pSheet The worksheet that we're editing |
||
| 372 | * |
||
| 373 | * @throws Exception |
||
| 374 | */ |
||
| 375 | 14 | public function insertNewBefore($pBefore, $pNumCols, $pNumRows, Worksheet $pSheet) |
|
| 376 | { |
||
| 377 | 14 | $remove = ($pNumCols < 0 || $pNumRows < 0); |
|
| 378 | 14 | $allCoordinates = $pSheet->getCoordinates(); |
|
| 379 | |||
| 380 | // Get coordinate of $pBefore |
||
| 381 | 14 | list($beforeColumn, $beforeRow) = Coordinate::coordinateFromString($pBefore); |
|
| 382 | 14 | $beforeColumnIndex = Coordinate::columnIndexFromString($beforeColumn); |
|
| 383 | |||
| 384 | // Clear cells if we are removing columns or rows |
||
| 385 | 14 | $highestColumn = $pSheet->getHighestColumn(); |
|
| 386 | 14 | $highestRow = $pSheet->getHighestRow(); |
|
| 387 | |||
| 388 | // 1. Clear column strips if we are removing columns |
||
| 389 | 14 | if ($pNumCols < 0 && $beforeColumnIndex - 2 + $pNumCols > 0) { |
|
| 390 | 11 | for ($i = 1; $i <= $highestRow - 1; ++$i) { |
|
| 391 | 11 | for ($j = $beforeColumnIndex - 1 + $pNumCols; $j <= $beforeColumnIndex - 2; ++$j) { |
|
| 392 | 11 | $coordinate = Coordinate::stringFromColumnIndex($j + 1) . $i; |
|
| 393 | 11 | $pSheet->removeConditionalStyles($coordinate); |
|
| 394 | 11 | if ($pSheet->cellExists($coordinate)) { |
|
| 395 | 11 | $pSheet->getCell($coordinate)->setValueExplicit('', DataType::TYPE_NULL); |
|
| 396 | 11 | $pSheet->getCell($coordinate)->setXfIndex(0); |
|
| 397 | } |
||
| 398 | } |
||
| 399 | } |
||
| 400 | } |
||
| 401 | |||
| 402 | // 2. Clear row strips if we are removing rows |
||
| 403 | 14 | if ($pNumRows < 0 && $beforeRow - 1 + $pNumRows > 0) { |
|
| 404 | 13 | for ($i = $beforeColumnIndex - 1; $i <= Coordinate::columnIndexFromString($highestColumn) - 1; ++$i) { |
|
| 405 | 13 | for ($j = $beforeRow + $pNumRows; $j <= $beforeRow - 1; ++$j) { |
|
| 406 | 13 | $coordinate = Coordinate::stringFromColumnIndex($i + 1) . $j; |
|
| 407 | 13 | $pSheet->removeConditionalStyles($coordinate); |
|
| 408 | 13 | if ($pSheet->cellExists($coordinate)) { |
|
| 409 | 12 | $pSheet->getCell($coordinate)->setValueExplicit('', DataType::TYPE_NULL); |
|
| 410 | 12 | $pSheet->getCell($coordinate)->setXfIndex(0); |
|
| 411 | } |
||
| 412 | } |
||
| 413 | } |
||
| 414 | } |
||
| 415 | |||
| 416 | // Loop through cells, bottom-up, and change cell coordinate |
||
| 417 | 14 | if ($remove) { |
|
| 418 | // It's faster to reverse and pop than to use unshift, especially with large cell collections |
||
| 419 | 14 | $allCoordinates = array_reverse($allCoordinates); |
|
| 420 | } |
||
| 421 | 14 | while ($coordinate = array_pop($allCoordinates)) { |
|
| 422 | 14 | $cell = $pSheet->getCell($coordinate); |
|
| 423 | 14 | $cellIndex = Coordinate::columnIndexFromString($cell->getColumn()); |
|
| 424 | |||
| 425 | 14 | if ($cellIndex - 1 + $pNumCols < 0) { |
|
| 426 | 11 | continue; |
|
| 427 | } |
||
| 428 | |||
| 429 | // New coordinate |
||
| 430 | 14 | $newCoordinate = Coordinate::stringFromColumnIndex($cellIndex + $pNumCols) . ($cell->getRow() + $pNumRows); |
|
| 431 | |||
| 432 | // Should the cell be updated? Move value and cellXf index from one cell to another. |
||
| 433 | 14 | if (($cellIndex >= $beforeColumnIndex) && ($cell->getRow() >= $beforeRow)) { |
|
| 434 | // Update cell styles |
||
| 435 | 14 | $pSheet->getCell($newCoordinate)->setXfIndex($cell->getXfIndex()); |
|
| 436 | |||
| 437 | // Insert this cell at its new location |
||
| 438 | 14 | if ($cell->getDataType() == DataType::TYPE_FORMULA) { |
|
| 439 | // Formula should be adjusted |
||
| 440 | 12 | $pSheet->getCell($newCoordinate) |
|
| 441 | 12 | ->setValue($this->updateFormulaReferences($cell->getValue(), $pBefore, $pNumCols, $pNumRows, $pSheet->getTitle())); |
|
| 442 | } else { |
||
| 443 | // Formula should not be adjusted |
||
| 444 | 14 | $pSheet->getCell($newCoordinate)->setValue($cell->getValue()); |
|
| 445 | } |
||
| 446 | |||
| 447 | // Clear the original cell |
||
| 448 | 14 | $pSheet->getCellCollection()->delete($coordinate); |
|
| 449 | } else { |
||
| 450 | /* We don't need to update styles for rows/columns before our insertion position, |
||
| 451 | but we do still need to adjust any formulae in those cells */ |
||
| 452 | 14 | if ($cell->getDataType() == DataType::TYPE_FORMULA) { |
|
| 453 | // Formula should be adjusted |
||
| 454 | 12 | $cell->setValue($this->updateFormulaReferences($cell->getValue(), $pBefore, $pNumCols, $pNumRows, $pSheet->getTitle())); |
|
| 455 | } |
||
| 456 | } |
||
| 457 | } |
||
| 458 | |||
| 459 | // Duplicate styles for the newly inserted cells |
||
| 460 | 14 | $highestColumn = $pSheet->getHighestColumn(); |
|
| 461 | 14 | $highestRow = $pSheet->getHighestRow(); |
|
| 462 | |||
| 463 | 14 | if ($pNumCols > 0 && $beforeColumnIndex - 2 > 0) { |
|
| 464 | 11 | for ($i = $beforeRow; $i <= $highestRow - 1; ++$i) { |
|
| 465 | // Style |
||
| 466 | 11 | $coordinate = Coordinate::stringFromColumnIndex($beforeColumnIndex - 1) . $i; |
|
| 467 | 11 | if ($pSheet->cellExists($coordinate)) { |
|
| 468 | 11 | $xfIndex = $pSheet->getCell($coordinate)->getXfIndex(); |
|
| 469 | 11 | $conditionalStyles = $pSheet->conditionalStylesExists($coordinate) ? |
|
| 470 | 11 | $pSheet->getConditionalStyles($coordinate) : false; |
|
| 471 | 11 | for ($j = $beforeColumnIndex; $j <= $beforeColumnIndex - 1 + $pNumCols; ++$j) { |
|
| 472 | 11 | $pSheet->getCellByColumnAndRow($j, $i)->setXfIndex($xfIndex); |
|
| 473 | 11 | if ($conditionalStyles) { |
|
| 474 | $cloned = []; |
||
| 475 | foreach ($conditionalStyles as $conditionalStyle) { |
||
| 476 | $cloned[] = clone $conditionalStyle; |
||
| 477 | } |
||
| 478 | $pSheet->setConditionalStyles(Coordinate::stringFromColumnIndex($j) . $i, $cloned); |
||
| 479 | } |
||
| 480 | } |
||
| 481 | } |
||
| 482 | } |
||
| 483 | } |
||
| 484 | |||
| 485 | 14 | if ($pNumRows > 0 && $beforeRow - 1 > 0) { |
|
| 486 | 12 | for ($i = $beforeColumnIndex; $i <= Coordinate::columnIndexFromString($highestColumn); ++$i) { |
|
| 487 | // Style |
||
| 488 | 12 | $coordinate = Coordinate::stringFromColumnIndex($i) . ($beforeRow - 1); |
|
| 489 | 12 | if ($pSheet->cellExists($coordinate)) { |
|
| 490 | 12 | $xfIndex = $pSheet->getCell($coordinate)->getXfIndex(); |
|
| 491 | 12 | $conditionalStyles = $pSheet->conditionalStylesExists($coordinate) ? |
|
| 492 | 12 | $pSheet->getConditionalStyles($coordinate) : false; |
|
| 493 | 12 | for ($j = $beforeRow; $j <= $beforeRow - 1 + $pNumRows; ++$j) { |
|
| 494 | 12 | $pSheet->getCell(Coordinate::stringFromColumnIndex($i) . $j)->setXfIndex($xfIndex); |
|
| 495 | 12 | if ($conditionalStyles) { |
|
| 496 | $cloned = []; |
||
| 497 | foreach ($conditionalStyles as $conditionalStyle) { |
||
| 498 | $cloned[] = clone $conditionalStyle; |
||
| 499 | } |
||
| 500 | $pSheet->setConditionalStyles(Coordinate::stringFromColumnIndex($i) . $j, $cloned); |
||
| 501 | } |
||
| 502 | } |
||
| 503 | } |
||
| 504 | } |
||
| 505 | } |
||
| 506 | |||
| 507 | // Update worksheet: column dimensions |
||
| 508 | 14 | $this->adjustColumnDimensions($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows); |
|
| 509 | |||
| 510 | // Update worksheet: row dimensions |
||
| 511 | 14 | $this->adjustRowDimensions($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows); |
|
| 512 | |||
| 513 | // Update worksheet: page breaks |
||
| 514 | 14 | $this->adjustPageBreaks($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows); |
|
| 515 | |||
| 516 | // Update worksheet: comments |
||
| 517 | 14 | $this->adjustComments($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows); |
|
| 518 | |||
| 519 | // Update worksheet: hyperlinks |
||
| 520 | 14 | $this->adjustHyperlinks($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows); |
|
| 521 | |||
| 522 | // Update worksheet: data validations |
||
| 523 | 14 | $this->adjustDataValidations($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows); |
|
| 524 | |||
| 525 | // Update worksheet: merge cells |
||
| 526 | 14 | $this->adjustMergeCells($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows); |
|
| 527 | |||
| 528 | // Update worksheet: protected cells |
||
| 529 | 14 | $this->adjustProtectedCells($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows); |
|
| 530 | |||
| 531 | // Update worksheet: autofilter |
||
| 532 | 14 | $autoFilter = $pSheet->getAutoFilter(); |
|
| 533 | 14 | $autoFilterRange = $autoFilter->getRange(); |
|
| 534 | 14 | if (!empty($autoFilterRange)) { |
|
| 535 | if ($pNumCols != 0) { |
||
| 536 | $autoFilterColumns = $autoFilter->getColumns(); |
||
| 537 | if (count($autoFilterColumns) > 0) { |
||
| 538 | $column = ''; |
||
| 539 | $row = 0; |
||
| 540 | sscanf($pBefore, '%[A-Z]%d', $column, $row); |
||
| 541 | $columnIndex = Coordinate::columnIndexFromString($column); |
||
| 542 | list($rangeStart, $rangeEnd) = Coordinate::rangeBoundaries($autoFilterRange); |
||
| 543 | if ($columnIndex <= $rangeEnd[0]) { |
||
| 544 | if ($pNumCols < 0) { |
||
| 545 | // If we're actually deleting any columns that fall within the autofilter range, |
||
| 546 | // then we delete any rules for those columns |
||
| 547 | $deleteColumn = $columnIndex + $pNumCols - 1; |
||
| 548 | $deleteCount = abs($pNumCols); |
||
| 549 | for ($i = 1; $i <= $deleteCount; ++$i) { |
||
| 550 | if (isset($autoFilterColumns[Coordinate::stringFromColumnIndex($deleteColumn + 1)])) { |
||
| 551 | $autoFilter->clearColumn(Coordinate::stringFromColumnIndex($deleteColumn + 1)); |
||
| 552 | } |
||
| 553 | ++$deleteColumn; |
||
| 554 | } |
||
| 555 | } |
||
| 556 | $startCol = ($columnIndex > $rangeStart[0]) ? $columnIndex : $rangeStart[0]; |
||
| 557 | |||
| 558 | // Shuffle columns in autofilter range |
||
| 559 | if ($pNumCols > 0) { |
||
| 560 | $startColRef = $startCol; |
||
| 561 | $endColRef = $rangeEnd[0]; |
||
| 562 | $toColRef = $rangeEnd[0] + $pNumCols; |
||
| 563 | |||
| 564 | do { |
||
| 565 | $autoFilter->shiftColumn(Coordinate::stringFromColumnIndex($endColRef), Coordinate::stringFromColumnIndex($toColRef)); |
||
| 566 | --$endColRef; |
||
| 567 | --$toColRef; |
||
| 568 | } while ($startColRef <= $endColRef); |
||
| 569 | } else { |
||
| 570 | // For delete, we shuffle from beginning to end to avoid overwriting |
||
| 571 | $startColID = Coordinate::stringFromColumnIndex($startCol); |
||
| 572 | $toColID = Coordinate::stringFromColumnIndex($startCol + $pNumCols); |
||
| 573 | $endColID = Coordinate::stringFromColumnIndex($rangeEnd[0] + 1); |
||
| 574 | do { |
||
| 575 | $autoFilter->shiftColumn($startColID, $toColID); |
||
| 576 | ++$startColID; |
||
| 577 | ++$toColID; |
||
| 578 | } while ($startColID != $endColID); |
||
| 579 | } |
||
| 580 | } |
||
| 581 | } |
||
| 582 | } |
||
| 583 | $pSheet->setAutoFilter($this->updateCellReference($autoFilterRange, $pBefore, $pNumCols, $pNumRows)); |
||
| 584 | } |
||
| 585 | |||
| 586 | // Update worksheet: freeze pane |
||
| 587 | 14 | if ($pSheet->getFreezePane()) { |
|
| 588 | 1 | $splitCell = $pSheet->getFreezePane(); |
|
| 589 | 1 | $topLeftCell = $pSheet->getTopLeftCell(); |
|
| 590 | |||
| 591 | 1 | $splitCell = $this->updateCellReference($splitCell, $pBefore, $pNumCols, $pNumRows); |
|
| 592 | 1 | $topLeftCell = $this->updateCellReference($topLeftCell, $pBefore, $pNumCols, $pNumRows); |
|
| 593 | |||
| 594 | 1 | $pSheet->freezePane($splitCell, $topLeftCell); |
|
| 595 | } |
||
| 596 | |||
| 597 | // Page setup |
||
| 598 | 14 | if ($pSheet->getPageSetup()->isPrintAreaSet()) { |
|
| 599 | $pSheet->getPageSetup()->setPrintArea($this->updateCellReference($pSheet->getPageSetup()->getPrintArea(), $pBefore, $pNumCols, $pNumRows)); |
||
| 600 | } |
||
| 601 | |||
| 602 | // Update worksheet: drawings |
||
| 603 | 14 | $aDrawings = $pSheet->getDrawingCollection(); |
|
| 604 | 14 | foreach ($aDrawings as $objDrawing) { |
|
| 605 | 12 | $newReference = $this->updateCellReference($objDrawing->getCoordinates(), $pBefore, $pNumCols, $pNumRows); |
|
| 606 | 12 | if ($objDrawing->getCoordinates() != $newReference) { |
|
| 607 | 12 | $objDrawing->setCoordinates($newReference); |
|
| 608 | } |
||
| 609 | } |
||
| 610 | |||
| 611 | // Update workbook: named ranges |
||
| 612 | 14 | if (count($pSheet->getParent()->getNamedRanges()) > 0) { |
|
| 613 | 1 | foreach ($pSheet->getParent()->getNamedRanges() as $namedRange) { |
|
| 614 | 1 | if ($namedRange->getWorksheet()->getHashCode() == $pSheet->getHashCode()) { |
|
| 615 | 1 | $namedRange->setRange($this->updateCellReference($namedRange->getRange(), $pBefore, $pNumCols, $pNumRows)); |
|
| 616 | } |
||
| 617 | } |
||
| 618 | } |
||
| 619 | |||
| 620 | // Garbage collect |
||
| 621 | 14 | $pSheet->garbageCollect(); |
|
| 622 | 14 | } |
|
| 623 | |||
| 624 | /** |
||
| 625 | * Update references within formulas. |
||
| 626 | * |
||
| 627 | * @param string $pFormula Formula to update |
||
| 628 | * @param int $pBefore Insert before this one |
||
| 629 | * @param int $pNumCols Number of columns to insert |
||
| 630 | * @param int $pNumRows Number of rows to insert |
||
| 631 | * @param string $sheetName Worksheet name/title |
||
| 632 | * |
||
| 633 | * @throws Exception |
||
| 634 | * |
||
| 635 | * @return string Updated formula |
||
| 636 | */ |
||
| 637 | 15 | public function updateFormulaReferences($pFormula = '', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0, $sheetName = '') |
|
| 638 | { |
||
| 639 | // Update cell references in the formula |
||
| 640 | 15 | $formulaBlocks = explode('"', $pFormula); |
|
| 641 | 15 | $i = false; |
|
| 642 | 15 | foreach ($formulaBlocks as &$formulaBlock) { |
|
| 643 | // Ignore blocks that were enclosed in quotes (alternating entries in the $formulaBlocks array after the explode) |
||
| 644 | 15 | if ($i = !$i) { |
|
| 645 | 15 | $adjustCount = 0; |
|
| 646 | 15 | $newCellTokens = $cellTokens = []; |
|
| 647 | // Search for row ranges (e.g. 'Sheet1'!3:5 or 3:5) with or without $ absolutes (e.g. $3:5) |
||
| 648 | 15 | $matchCount = preg_match_all('/' . self::REFHELPER_REGEXP_ROWRANGE . '/i', ' ' . $formulaBlock . ' ', $matches, PREG_SET_ORDER); |
|
| 649 | 15 | if ($matchCount > 0) { |
|
| 650 | foreach ($matches as $match) { |
||
| 651 | $fromString = ($match[2] > '') ? $match[2] . '!' : ''; |
||
| 652 | $fromString .= $match[3] . ':' . $match[4]; |
||
| 653 | $modified3 = substr($this->updateCellReference('$A' . $match[3], $pBefore, $pNumCols, $pNumRows), 2); |
||
| 654 | $modified4 = substr($this->updateCellReference('$A' . $match[4], $pBefore, $pNumCols, $pNumRows), 2); |
||
| 655 | |||
| 656 | if ($match[3] . ':' . $match[4] !== $modified3 . ':' . $modified4) { |
||
| 657 | if (($match[2] == '') || (trim($match[2], "'") == $sheetName)) { |
||
| 658 | $toString = ($match[2] > '') ? $match[2] . '!' : ''; |
||
| 659 | $toString .= $modified3 . ':' . $modified4; |
||
| 660 | // 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 |
||
| 661 | $column = 100000; |
||
| 662 | $row = 10000000 + trim($match[3], '$'); |
||
| 663 | $cellIndex = $column . $row; |
||
| 664 | |||
| 665 | $newCellTokens[$cellIndex] = preg_quote($toString, '/'); |
||
| 666 | $cellTokens[$cellIndex] = '/(?<!\d\$\!)' . preg_quote($fromString, '/') . '(?!\d)/i'; |
||
| 667 | ++$adjustCount; |
||
| 668 | } |
||
| 669 | } |
||
| 670 | } |
||
| 671 | } |
||
| 672 | // Search for column ranges (e.g. 'Sheet1'!C:E or C:E) with or without $ absolutes (e.g. $C:E) |
||
| 673 | 15 | $matchCount = preg_match_all('/' . self::REFHELPER_REGEXP_COLRANGE . '/i', ' ' . $formulaBlock . ' ', $matches, PREG_SET_ORDER); |
|
| 674 | 15 | if ($matchCount > 0) { |
|
| 675 | foreach ($matches as $match) { |
||
| 676 | $fromString = ($match[2] > '') ? $match[2] . '!' : ''; |
||
| 677 | $fromString .= $match[3] . ':' . $match[4]; |
||
| 678 | $modified3 = substr($this->updateCellReference($match[3] . '$1', $pBefore, $pNumCols, $pNumRows), 0, -2); |
||
| 679 | $modified4 = substr($this->updateCellReference($match[4] . '$1', $pBefore, $pNumCols, $pNumRows), 0, -2); |
||
| 680 | |||
| 681 | if ($match[3] . ':' . $match[4] !== $modified3 . ':' . $modified4) { |
||
| 682 | if (($match[2] == '') || (trim($match[2], "'") == $sheetName)) { |
||
| 683 | $toString = ($match[2] > '') ? $match[2] . '!' : ''; |
||
| 684 | $toString .= $modified3 . ':' . $modified4; |
||
| 685 | // 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 |
||
| 686 | $column = Coordinate::columnIndexFromString(trim($match[3], '$')) + 100000; |
||
| 687 | $row = 10000000; |
||
| 688 | $cellIndex = $column . $row; |
||
| 689 | |||
| 690 | $newCellTokens[$cellIndex] = preg_quote($toString, '/'); |
||
| 691 | $cellTokens[$cellIndex] = '/(?<![A-Z\$\!])' . preg_quote($fromString, '/') . '(?![A-Z])/i'; |
||
| 692 | ++$adjustCount; |
||
| 693 | } |
||
| 694 | } |
||
| 695 | } |
||
| 696 | } |
||
| 697 | // Search for cell ranges (e.g. 'Sheet1'!A3:C5 or A3:C5) with or without $ absolutes (e.g. $A1:C$5) |
||
| 698 | 15 | $matchCount = preg_match_all('/' . self::REFHELPER_REGEXP_CELLRANGE . '/i', ' ' . $formulaBlock . ' ', $matches, PREG_SET_ORDER); |
|
| 699 | 15 | if ($matchCount > 0) { |
|
| 700 | 13 | foreach ($matches as $match) { |
|
| 701 | 13 | $fromString = ($match[2] > '') ? $match[2] . '!' : ''; |
|
| 702 | 13 | $fromString .= $match[3] . ':' . $match[4]; |
|
| 703 | 13 | $modified3 = $this->updateCellReference($match[3], $pBefore, $pNumCols, $pNumRows); |
|
| 704 | 13 | $modified4 = $this->updateCellReference($match[4], $pBefore, $pNumCols, $pNumRows); |
|
| 705 | |||
| 706 | 13 | if ($match[3] . $match[4] !== $modified3 . $modified4) { |
|
| 707 | 13 | if (($match[2] == '') || (trim($match[2], "'") == $sheetName)) { |
|
| 708 | 13 | $toString = ($match[2] > '') ? $match[2] . '!' : ''; |
|
| 709 | 13 | $toString .= $modified3 . ':' . $modified4; |
|
| 710 | 13 | list($column, $row) = Coordinate::coordinateFromString($match[3]); |
|
| 711 | // 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 |
||
| 712 | 13 | $column = Coordinate::columnIndexFromString(trim($column, '$')) + 100000; |
|
| 713 | 13 | $row = trim($row, '$') + 10000000; |
|
| 714 | 13 | $cellIndex = $column . $row; |
|
| 715 | |||
| 716 | 13 | $newCellTokens[$cellIndex] = preg_quote($toString, '/'); |
|
| 717 | 13 | $cellTokens[$cellIndex] = '/(?<![A-Z]\$\!)' . preg_quote($fromString, '/') . '(?!\d)/i'; |
|
| 718 | 13 | ++$adjustCount; |
|
| 719 | } |
||
| 720 | } |
||
| 721 | } |
||
| 722 | } |
||
| 723 | // Search for cell references (e.g. 'Sheet1'!A3 or C5) with or without $ absolutes (e.g. $A1 or C$5) |
||
| 724 | 15 | $matchCount = preg_match_all('/' . self::REFHELPER_REGEXP_CELLREF . '/i', ' ' . $formulaBlock . ' ', $matches, PREG_SET_ORDER); |
|
| 725 | |||
| 726 | 15 | if ($matchCount > 0) { |
|
| 727 | 15 | foreach ($matches as $match) { |
|
| 728 | 15 | $fromString = ($match[2] > '') ? $match[2] . '!' : ''; |
|
| 729 | 15 | $fromString .= $match[3]; |
|
| 730 | |||
| 731 | 15 | $modified3 = $this->updateCellReference($match[3], $pBefore, $pNumCols, $pNumRows); |
|
| 732 | 15 | if ($match[3] !== $modified3) { |
|
| 733 | 15 | if (($match[2] == '') || (trim($match[2], "'") == $sheetName)) { |
|
| 734 | 15 | $toString = ($match[2] > '') ? $match[2] . '!' : ''; |
|
| 735 | 15 | $toString .= $modified3; |
|
| 736 | 15 | list($column, $row) = Coordinate::coordinateFromString($match[3]); |
|
| 737 | // 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 |
||
| 738 | 15 | $column = Coordinate::columnIndexFromString(trim($column, '$')) + 100000; |
|
| 739 | 15 | $row = trim($row, '$') + 10000000; |
|
| 740 | 15 | $cellIndex = $row . $column; |
|
| 741 | |||
| 742 | 15 | $newCellTokens[$cellIndex] = preg_quote($toString, '/'); |
|
| 743 | 15 | $cellTokens[$cellIndex] = '/(?<![A-Z\$\!])' . preg_quote($fromString, '/') . '(?!\d)/i'; |
|
| 744 | 15 | ++$adjustCount; |
|
| 745 | } |
||
| 746 | } |
||
| 747 | } |
||
| 748 | } |
||
| 749 | 15 | if ($adjustCount > 0) { |
|
| 750 | 15 | if ($pNumCols > 0 || $pNumRows > 0) { |
|
| 751 | 15 | krsort($cellTokens); |
|
| 752 | 15 | krsort($newCellTokens); |
|
| 753 | } else { |
||
| 754 | 12 | ksort($cellTokens); |
|
| 755 | 12 | ksort($newCellTokens); |
|
| 756 | } // Update cell references in the formula |
||
| 757 | 15 | $formulaBlock = str_replace('\\', '', preg_replace($cellTokens, $newCellTokens, $formulaBlock)); |
|
| 758 | } |
||
| 759 | } |
||
| 760 | } |
||
| 761 | 15 | unset($formulaBlock); |
|
| 762 | |||
| 763 | // Then rebuild the formula string |
||
| 764 | 15 | return implode('"', $formulaBlocks); |
|
| 765 | } |
||
| 766 | |||
| 767 | /** |
||
| 768 | * Update cell reference. |
||
| 769 | * |
||
| 770 | * @param string $pCellRange Cell range |
||
| 771 | * @param string $pBefore Insert before this one |
||
| 772 | * @param int $pNumCols Number of columns to increment |
||
| 773 | * @param int $pNumRows Number of rows to increment |
||
| 774 | * |
||
| 775 | * @throws Exception |
||
| 776 | * |
||
| 777 | * @return string Updated cell range |
||
| 778 | */ |
||
| 779 | 17 | public function updateCellReference($pCellRange = 'A1', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0) |
|
| 780 | { |
||
| 781 | // Is it in another worksheet? Will not have to update anything. |
||
| 782 | 17 | if (strpos($pCellRange, '!') !== false) { |
|
| 783 | return $pCellRange; |
||
| 784 | // Is it a range or a single cell? |
||
| 785 | 17 | } elseif (!Coordinate::coordinateIsRange($pCellRange)) { |
|
| 786 | // Single cell |
||
| 787 | 17 | return $this->updateSingleCellReference($pCellRange, $pBefore, $pNumCols, $pNumRows); |
|
| 788 | 13 | } elseif (Coordinate::coordinateIsRange($pCellRange)) { |
|
| 789 | // Range |
||
| 790 | 13 | return $this->updateCellRange($pCellRange, $pBefore, $pNumCols, $pNumRows); |
|
| 791 | } |
||
| 792 | |||
| 793 | // Return original |
||
| 794 | return $pCellRange; |
||
| 795 | } |
||
| 796 | |||
| 797 | /** |
||
| 798 | * Update named formulas (i.e. containing worksheet references / named ranges). |
||
| 799 | * |
||
| 800 | * @param Spreadsheet $spreadsheet Object to update |
||
| 801 | * @param string $oldName Old name (name to replace) |
||
| 802 | * @param string $newName New name |
||
| 803 | */ |
||
| 804 | 41 | public function updateNamedFormulas(Spreadsheet $spreadsheet, $oldName = '', $newName = '') |
|
| 805 | { |
||
| 806 | 41 | if ($oldName == '') { |
|
| 807 | return; |
||
| 808 | } |
||
| 809 | |||
| 810 | 41 | foreach ($spreadsheet->getWorksheetIterator() as $sheet) { |
|
| 811 | 41 | foreach ($sheet->getCoordinates(false) as $coordinate) { |
|
| 812 | 29 | $cell = $sheet->getCell($coordinate); |
|
| 813 | 29 | if (($cell !== null) && ($cell->getDataType() == DataType::TYPE_FORMULA)) { |
|
| 814 | 19 | $formula = $cell->getValue(); |
|
| 815 | 19 | if (strpos($formula, $oldName) !== false) { |
|
| 816 | $formula = str_replace("'" . $oldName . "'!", "'" . $newName . "'!", $formula); |
||
| 817 | $formula = str_replace($oldName . '!', $newName . '!', $formula); |
||
| 818 | 41 | $cell->setValueExplicit($formula, DataType::TYPE_FORMULA); |
|
| 819 | } |
||
| 820 | } |
||
| 821 | } |
||
| 822 | } |
||
| 823 | 41 | } |
|
| 824 | |||
| 825 | /** |
||
| 826 | * Update cell range. |
||
| 827 | * |
||
| 828 | * @param string $pCellRange Cell range (e.g. 'B2:D4', 'B:C' or '2:3') |
||
| 829 | * @param string $pBefore Insert before this one |
||
| 830 | * @param int $pNumCols Number of columns to increment |
||
| 831 | * @param int $pNumRows Number of rows to increment |
||
| 832 | * |
||
| 833 | * @throws Exception |
||
| 834 | * |
||
| 835 | * @return string Updated cell range |
||
| 836 | */ |
||
| 837 | 13 | private function updateCellRange($pCellRange = 'A1:A1', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0) |
|
| 838 | { |
||
| 839 | 13 | if (!Coordinate::coordinateIsRange($pCellRange)) { |
|
| 840 | throw new Exception('Only cell ranges may be passed to this method.'); |
||
| 841 | } |
||
| 842 | |||
| 843 | // Update range |
||
| 844 | 13 | $range = Coordinate::splitRange($pCellRange); |
|
| 845 | 13 | $ic = count($range); |
|
| 846 | 13 | for ($i = 0; $i < $ic; ++$i) { |
|
| 847 | 13 | $jc = count($range[$i]); |
|
| 848 | 13 | for ($j = 0; $j < $jc; ++$j) { |
|
| 849 | 13 | if (ctype_alpha($range[$i][$j])) { |
|
| 850 | $r = Coordinate::coordinateFromString($this->updateSingleCellReference($range[$i][$j] . '1', $pBefore, $pNumCols, $pNumRows)); |
||
| 851 | $range[$i][$j] = $r[0]; |
||
| 852 | 13 | } elseif (ctype_digit($range[$i][$j])) { |
|
| 853 | $r = Coordinate::coordinateFromString($this->updateSingleCellReference('A' . $range[$i][$j], $pBefore, $pNumCols, $pNumRows)); |
||
| 854 | $range[$i][$j] = $r[1]; |
||
| 855 | } else { |
||
| 856 | 13 | $range[$i][$j] = $this->updateSingleCellReference($range[$i][$j], $pBefore, $pNumCols, $pNumRows); |
|
| 857 | } |
||
| 858 | } |
||
| 859 | } |
||
| 860 | |||
| 861 | // Recreate range string |
||
| 862 | 13 | return Coordinate::buildRange($range); |
|
| 863 | } |
||
| 864 | |||
| 865 | /** |
||
| 866 | * Update single cell reference. |
||
| 867 | * |
||
| 868 | * @param string $pCellReference Single cell reference |
||
| 869 | * @param string $pBefore Insert before this one |
||
| 870 | * @param int $pNumCols Number of columns to increment |
||
| 871 | * @param int $pNumRows Number of rows to increment |
||
| 872 | * |
||
| 873 | * @throws Exception |
||
| 874 | * |
||
| 875 | * @return string Updated cell reference |
||
| 876 | */ |
||
| 877 | 17 | private function updateSingleCellReference($pCellReference = 'A1', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0) |
|
| 905 | } |
||
| 906 | |||
| 907 | /** |
||
| 908 | * __clone implementation. Cloning should not be allowed in a Singleton! |
||
| 909 | * |
||
| 910 | * @throws Exception |
||
| 911 | */ |
||
| 912 | final public function __clone() |
||
| 915 | } |
||
| 916 | } |
||
| 917 |
Loading history...
