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