1: <?php
2: 3: 4: 5: 6: 7: 8: 9: 10: 11: 12: 13: 14: 15: 16: 17: 18: 19: 20: 21: 22: 23: 24: 25: 26:
27:
28:
29: 30: 31: 32: 33: 34: 35:
36: class PHPExcel_ReferenceHelper
37: {
38:
39:
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: 47: 48: 49:
50: private static $_instance;
51:
52: 53: 54: 55: 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: 67:
68: protected function __construct() {
69: }
70:
71: 72: 73: 74: 75: 76: 77: 78:
79: public static function columnSort($a, $b) {
80: return strcasecmp(strlen($a) . $a, strlen($b) . $b);
81: }
82:
83: 84: 85: 86: 87: 88: 89: 90:
91: public static function columnReverseSort($a, $b) {
92: return 1 - strcasecmp(strlen($a) . $a, strlen($b) . $b);
93: }
94:
95: 96: 97: 98: 99: 100: 101: 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: 115: 116: 117: 118: 119: 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: 133: 134: 135: 136: 137: 138: 139: 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:
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: 159: 160: 161: 162: 163: 164: 165: 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:
177:
178: $pSheet->setBreak($key, PHPExcel_Worksheet::BREAK_NONE);
179: } else {
180:
181:
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: 193: 194: 195: 196: 197: 198: 199: 200:
201: protected function _adjustComments($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
202: {
203: $aComments = $pSheet->getComments();
204: $aNewComments = array();
205:
206: foreach ($aComments as $key => &$value) {
207:
208: if (!self::cellAddressInDeleteRange($key, $beforeRow, $pNumRows, $beforeColumnIndex, $pNumCols)) {
209:
210: $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
211: $aNewComments[$newReference] = $value;
212: }
213: }
214:
215: $pSheet->setComments($aNewComments);
216: }
217:
218: 219: 220: 221: 222: 223: 224: 225: 226: 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: 246: 247: 248: 249: 250: 251: 252: 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: 271: 272: 273: 274: 275: 276: 277: 278:
279: protected function _adjustMergeCells($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
280: {
281: $aMergeCells = $pSheet->getMergeCells();
282: $aNewMergeCells = array();
283: foreach ($aMergeCells as $key => &$value) {
284: $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
285: $aNewMergeCells[$newReference] = $newReference;
286: }
287: $pSheet->setMergeCells($aNewMergeCells);
288: }
289:
290: 291: 292: 293: 294: 295: 296: 297: 298: 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: 317: 318: 319: 320: 321: 322: 323: 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: 342: 343: 344: 345: 346: 347: 348: 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: 376: 377: 378: 379: 380: 381: 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:
389: $beforeColumn = 'A';
390: $beforeRow = 1;
391: list($beforeColumn, $beforeRow) = PHPExcel_Cell::coordinateFromString($pBefore);
392: $beforeColumnIndex = PHPExcel_Cell::columnIndexFromString($beforeColumn);
393:
394:
395: $highestColumn = $pSheet->getHighestColumn();
396: $highestRow = $pSheet->getHighestRow();
397:
398:
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:
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:
427: if($remove) {
428:
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:
440: $newCoordinates = PHPExcel_Cell::stringFromColumnIndex($cellIndex-1 + $pNumCols) . ($cell->getRow() + $pNumRows);
441:
442:
443: if (($cellIndex >= $beforeColumnIndex) &&
444: ($cell->getRow() >= $beforeRow)) {
445:
446:
447: $pSheet->getCell($newCoordinates)->setXfIndex($cell->getXfIndex());
448:
449:
450: if ($cell->getDataType() == PHPExcel_Cell_DataType::TYPE_FORMULA) {
451:
452: $pSheet->getCell($newCoordinates)
453: ->setValue($this->updateFormulaReferences($cell->getValue(),
454: $pBefore, $pNumCols, $pNumRows, $pSheet->getTitle()));
455: } else {
456:
457: $pSheet->getCell($newCoordinates)->setValue($cell->getValue());
458: }
459:
460:
461: $pSheet->getCellCacheController()->deleteCacheData($cellID);
462:
463: } else {
464: 465:
466: if ($cell->getDataType() == PHPExcel_Cell_DataType::TYPE_FORMULA) {
467:
468: $cell->setValue($this->updateFormulaReferences($cell->getValue(),
469: $pBefore, $pNumCols, $pNumRows, $pSheet->getTitle()));
470: }
471:
472: }
473: }
474:
475:
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:
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:
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:
527: $this->_adjustColumnDimensions($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
528:
529:
530: $this->_adjustRowDimensions($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
531:
532:
533: $this->_adjustPageBreaks($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
534:
535:
536: $this->_adjustComments($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
537:
538:
539: $this->_adjustHyperlinks($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
540:
541:
542: $this->_adjustDataValidations($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
543:
544:
545: $this->_adjustMergeCells($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
546:
547:
548: $this->_adjustProtectedCells($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
549:
550:
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:
563:
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:
576: if ($pNumCols > 0) {
577:
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:
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:
609: if ($pSheet->getFreezePane() != '') {
610: $pSheet->freezePane( $this->updateCellReference($pSheet->getFreezePane(), $pBefore, $pNumCols, $pNumRows) );
611: }
612:
613:
614: if ($pSheet->getPageSetup()->isPrintAreaSet()) {
615: $pSheet->getPageSetup()->setPrintArea( $this->updateCellReference($pSheet->getPageSetup()->getPrintArea(), $pBefore, $pNumCols, $pNumRows) );
616: }
617:
618:
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:
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:
639: $pSheet->garbageCollect();
640: }
641:
642: 643: 644: 645: 646: 647: 648: 649: 650: 651: 652:
653: public function updateFormulaReferences($pFormula = '', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0, $sheetName = '') {
654:
655: $formulaBlocks = explode('"',$pFormula);
656: $i = false;
657: foreach($formulaBlocks as &$formulaBlock) {
658:
659: if ($i = !$i) {
660: $adjustCount = 0;
661: $newCellTokens = $cellTokens = array();
662:
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:
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:
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:
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:
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:
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:
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:
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: }
772: $formulaBlock = str_replace('\\','',preg_replace($cellTokens,$newCellTokens,$formulaBlock));
773: }
774: }
775: }
776: unset($formulaBlock);
777:
778:
779: return implode('"',$formulaBlocks);
780: }
781:
782: 783: 784: 785: 786: 787: 788: 789: 790: 791:
792: public function updateCellReference($pCellRange = 'A1', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0) {
793:
794: if (strpos($pCellRange, "!") !== false) {
795: return $pCellRange;
796:
797: } elseif (strpos($pCellRange, ':') === false && strpos($pCellRange, ',') === false) {
798:
799: return $this->_updateSingleCellReference($pCellRange, $pBefore, $pNumCols, $pNumRows);
800: } elseif (strpos($pCellRange, ':') !== false || strpos($pCellRange, ',') !== false) {
801:
802: return $this->_updateCellRange($pCellRange, $pBefore, $pNumCols, $pNumRows);
803: } else {
804:
805: return $pCellRange;
806: }
807: }
808:
809: 810: 811: 812: 813: 814: 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: 838: 839: 840: 841: 842: 843: 844: 845:
846: private function _updateCellRange($pCellRange = 'A1:A1', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0) {
847: if (strpos($pCellRange,':') !== false || strpos($pCellRange, ',') !== false) {
848:
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:
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: 875: 876: 877: 878: 879: 880: 881: 882:
883: private function _updateSingleCellReference($pCellReference = 'A1', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0) {
884: if (strpos($pCellReference, ':') === false && strpos($pCellReference, ',') === false) {
885:
886: list($beforeColumn, $beforeRow) = PHPExcel_Cell::coordinateFromString( $pBefore );
887:
888:
889: list($newColumn, $newRow) = PHPExcel_Cell::coordinateFromString( $pCellReference );
890:
891:
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:
898: if ($updateColumn) {
899: $newColumn = PHPExcel_Cell::stringFromColumnIndex( PHPExcel_Cell::columnIndexFromString($newColumn) - 1 + $pNumCols );
900: }
901:
902:
903: if ($updateRow) {
904: $newRow = $newRow + $pNumRows;
905: }
906:
907:
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: 916: 917: 918:
919: public final function __clone() {
920: throw new PHPExcel_Exception("Cloning a Singleton is not allowed!");
921: }
922: }
923: