ILIAS  release_5-3 Revision v5.3.23-19-g915713cf615
ReferenceHelper.php
Go to the documentation of this file.
1<?php
37{
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
50 private static $_instance;
51
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
68 protected function __construct() {
69 }
70
79 public static function columnSort($a, $b) {
80 return strcasecmp(strlen($a) . $a, strlen($b) . $b);
81 }
82
91 public static function columnReverseSort($a, $b) {
92 return 1 - strcasecmp(strlen($a) . $a, strlen($b) . $b);
93 }
94
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
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
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
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
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)
186 }
187 }
188 }
189 }
190
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
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
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
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
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
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
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
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);
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 {
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
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;
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
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;
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
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
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
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
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
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} != '$') &&
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
919 public final function __clone() {
920 throw new PHPExcel_Exception("Cloning a Singleton is not allowed!");
921 }
922}
$objDrawing
Definition: 04printing.php:70
$column
Definition: 39dropdown.php:62
An exception for terminatinating execution or to throw for unit testing.
static buildRange($pRange)
Build range from coordinate strings.
Definition: Cell.php:682
static stringFromColumnIndex($pColumnIndex=0)
String from columnindex.
Definition: Cell.php:825
static coordinateFromString($pCoordinateString='A1')
Coordinate from string.
Definition: Cell.php:580
static rangeBoundaries($pRange='A1:A1')
Calculate range boundaries.
Definition: Cell.php:707
static splitRange($pRange='A1:A1')
Split range into coordinate strings.
Definition: Cell.php:660
static columnIndexFromString($pString='A')
Column index from string.
Definition: Cell.php:782
_adjustPageBreaks(PHPExcel_Worksheet $pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
Update page breaks when inserting/deleting rows/columns.
__construct()
Create a new PHPExcel_ReferenceHelper.
_updateCellRange($pCellRange='A1:A1', $pBefore='A1', $pNumCols=0, $pNumRows=0)
Update cell range.
_adjustRowDimensions($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
Update row dimensions when inserting/deleting rows/columns.
static cellAddressInDeleteRange($cellAddress, $beforeRow, $pNumRows, $beforeColumnIndex, $pNumCols)
Test whether a cell address falls within a defined range of cells.
_adjustHyperlinks($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
Update hyperlinks when inserting/deleting rows/columns.
updateCellReference($pCellRange='A1', $pBefore='A1', $pNumCols=0, $pNumRows=0)
Update cell reference.
static getInstance()
Get an instance of this class.
_adjustMergeCells($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
Update merged cells when inserting/deleting rows/columns.
updateFormulaReferences($pFormula='', $pBefore='A1', $pNumCols=0, $pNumRows=0, $sheetName='')
Update references within formulas.
_adjustDataValidations($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
Update data validations when inserting/deleting rows/columns.
__clone()
__clone implementation.
static cellSort($a, $b)
Compare two cell addresses Intended for use as a Callback function for sorting cell addresses by colu...
_adjustComments($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
Update cell comments when inserting/deleting rows/columns.
static columnReverseSort($a, $b)
Compare two column addresses Intended for use as a Callback function for reverse sorting column addre...
static columnSort($a, $b)
Compare two column addresses Intended for use as a Callback function for sorting column addresses by ...
updateNamedFormulas(PHPExcel $pPhpExcel, $oldName='', $newName='')
Update named formulas (i.e.
_updateSingleCellReference($pCellReference='A1', $pBefore='A1', $pNumCols=0, $pNumRows=0)
Update single cell reference.
const REFHELPER_REGEXP_CELLREF
Constants
insertNewBefore($pBefore='A1', $pNumCols=0, $pNumRows=0, PHPExcel_Worksheet $pSheet=NULL)
Insert a new column or row, updating all possible related data.
_adjustColumnDimensions($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
Update column dimensions when inserting/deleting rows/columns.
static cellReverseSort($a, $b)
Compare two cell addresses Intended for use as a Callback function for sorting cell addresses by colu...
_adjustProtectedCells($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
Update protected cells when inserting/deleting rows/columns.
getBreaks()
Get breaks.
Definition: Worksheet.php:1675
setBreak($pCell='A1', $pBreak=PHPExcel_Worksheet::BREAK_NONE)
Set break on a cell.
Definition: Worksheet.php:1637
getWorksheetIterator()
Get worksheet iterator.
Definition: PHPExcel.php:839
$key
Definition: croninfo.php:18
$i
Definition: disco.tpl.php:19
$r
Definition: example_031.php:79