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