ILIAS  release_5-4 Revision v5.4.26-12-gabc799a52e6
ReferenceHelper.php
Go to the documentation of this file.
1 <?php
2 
4 
9 
11 {
14  const REFHELPER_REGEXP_CELLREF = '((\w*|\'[^!]*\')!)?(?<![:a-z\$])(\$?[a-z]{1,3}\$?\d+)(?=[^:!\d\'])';
15  const REFHELPER_REGEXP_CELLRANGE = '((\w*|\'[^!]*\')!)?(\$?[a-z]{1,3}\$?\d+):(\$?[a-z]{1,3}\$?\d+)';
16  const REFHELPER_REGEXP_ROWRANGE = '((\w*|\'[^!]*\')!)?(\$?\d+):(\$?\d+)';
17  const REFHELPER_REGEXP_COLRANGE = '((\w*|\'[^!]*\')!)?(\$?[a-z]{1,3}):(\$?[a-z]{1,3})';
18 
24  private static $instance;
25 
31  public static function getInstance()
32  {
33  if (!isset(self::$instance) || (self::$instance === null)) {
34  self::$instance = new self();
35  }
36 
37  return self::$instance;
38  }
39 
43  protected function __construct()
44  {
45  }
46 
56  public static function columnSort($a, $b)
57  {
58  return strcasecmp(strlen($a) . $a, strlen($b) . $b);
59  }
60 
70  public static function columnReverseSort($a, $b)
71  {
72  return -strcasecmp(strlen($a) . $a, strlen($b) . $b);
73  }
74 
84  public static function cellSort($a, $b)
85  {
86  [$ac, $ar] = sscanf($a, '%[A-Z]%d');
87  [$bc, $br] = sscanf($b, '%[A-Z]%d');
88 
89  if ($ar === $br) {
90  return strcasecmp(strlen($ac) . $ac, strlen($bc) . $bc);
91  }
92 
93  return ($ar < $br) ? -1 : 1;
94  }
95 
105  public static function cellReverseSort($a, $b)
106  {
107  [$ac, $ar] = sscanf($a, '%[A-Z]%d');
108  [$bc, $br] = sscanf($b, '%[A-Z]%d');
109 
110  if ($ar === $br) {
111  return -strcasecmp(strlen($ac) . $ac, strlen($bc) . $bc);
112  }
113 
114  return ($ar < $br) ? 1 : -1;
115  }
116 
128  private static function cellAddressInDeleteRange($cellAddress, $beforeRow, $pNumRows, $beforeColumnIndex, $pNumCols)
129  {
130  [$cellColumn, $cellRow] = Coordinate::coordinateFromString($cellAddress);
131  $cellColumnIndex = Coordinate::columnIndexFromString($cellColumn);
132  // Is cell within the range of rows/columns if we're deleting
133  if (
134  $pNumRows < 0 &&
135  ($cellRow >= ($beforeRow + $pNumRows)) &&
136  ($cellRow < $beforeRow)
137  ) {
138  return true;
139  } elseif (
140  $pNumCols < 0 &&
141  ($cellColumnIndex >= ($beforeColumnIndex + $pNumCols)) &&
142  ($cellColumnIndex < $beforeColumnIndex)
143  ) {
144  return true;
145  }
146 
147  return false;
148  }
149 
160  protected function adjustPageBreaks(Worksheet $pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows): void
161  {
162  $aBreaks = $pSheet->getBreaks();
163  ($pNumCols > 0 || $pNumRows > 0) ?
164  uksort($aBreaks, ['self', 'cellReverseSort']) : uksort($aBreaks, ['self', 'cellSort']);
165 
166  foreach ($aBreaks as $key => $value) {
167  if (self::cellAddressInDeleteRange($key, $beforeRow, $pNumRows, $beforeColumnIndex, $pNumCols)) {
168  // If we're deleting, then clear any defined breaks that are within the range
169  // of rows/columns that we're deleting
171  } else {
172  // Otherwise update any affected breaks by inserting a new break at the appropriate point
173  // and removing the old affected break
174  $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
175  if ($key != $newReference) {
176  $pSheet->setBreak($newReference, $value)
177  ->setBreak($key, Worksheet::BREAK_NONE);
178  }
179  }
180  }
181  }
182 
193  protected function adjustComments($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows): void
194  {
195  $aComments = $pSheet->getComments();
196  $aNewComments = []; // the new array of all comments
197 
198  foreach ($aComments as $key => &$value) {
199  // Any comments inside a deleted range will be ignored
200  if (!self::cellAddressInDeleteRange($key, $beforeRow, $pNumRows, $beforeColumnIndex, $pNumCols)) {
201  // Otherwise build a new array of comments indexed by the adjusted cell reference
202  $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
203  $aNewComments[$newReference] = $value;
204  }
205  }
206  // Replace the comments array with the new set of comments
207  $pSheet->setComments($aNewComments);
208  }
209 
220  protected function adjustHyperlinks($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows): void
221  {
222  $aHyperlinkCollection = $pSheet->getHyperlinkCollection();
223  ($pNumCols > 0 || $pNumRows > 0) ?
224  uksort($aHyperlinkCollection, ['self', 'cellReverseSort']) : uksort($aHyperlinkCollection, ['self', 'cellSort']);
225 
226  foreach ($aHyperlinkCollection as $key => $value) {
227  $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
228  if ($key != $newReference) {
229  $pSheet->setHyperlink($newReference, $value);
230  $pSheet->setHyperlink($key, null);
231  }
232  }
233  }
234 
245  protected function adjustDataValidations($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows): void
246  {
247  $aDataValidationCollection = $pSheet->getDataValidationCollection();
248  ($pNumCols > 0 || $pNumRows > 0) ?
249  uksort($aDataValidationCollection, ['self', 'cellReverseSort']) : uksort($aDataValidationCollection, ['self', 'cellSort']);
250 
251  foreach ($aDataValidationCollection as $key => $value) {
252  $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
253  if ($key != $newReference) {
254  $pSheet->setDataValidation($newReference, $value);
255  $pSheet->setDataValidation($key, null);
256  }
257  }
258  }
259 
270  protected function adjustMergeCells($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows): void
271  {
272  $aMergeCells = $pSheet->getMergeCells();
273  $aNewMergeCells = []; // the new array of all merge cells
274  foreach ($aMergeCells as $key => &$value) {
275  $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
276  $aNewMergeCells[$newReference] = $newReference;
277  }
278  $pSheet->setMergeCells($aNewMergeCells); // replace the merge cells array
279  }
280 
291  protected function adjustProtectedCells($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows): void
292  {
293  $aProtectedCells = $pSheet->getProtectedCells();
294  ($pNumCols > 0 || $pNumRows > 0) ?
295  uksort($aProtectedCells, ['self', 'cellReverseSort']) : uksort($aProtectedCells, ['self', 'cellSort']);
296  foreach ($aProtectedCells as $key => $value) {
297  $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
298  if ($key != $newReference) {
299  $pSheet->protectCells($newReference, $value, true);
300  $pSheet->unprotectCells($key);
301  }
302  }
303  }
304 
315  protected function adjustColumnDimensions($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows): void
316  {
317  $aColumnDimensions = array_reverse($pSheet->getColumnDimensions(), true);
318  if (!empty($aColumnDimensions)) {
319  foreach ($aColumnDimensions as $objColumnDimension) {
320  $newReference = $this->updateCellReference($objColumnDimension->getColumnIndex() . '1', $pBefore, $pNumCols, $pNumRows);
321  [$newReference] = Coordinate::coordinateFromString($newReference);
322  if ($objColumnDimension->getColumnIndex() != $newReference) {
323  $objColumnDimension->setColumnIndex($newReference);
324  }
325  }
326  $pSheet->refreshColumnDimensions();
327  }
328  }
329 
340  protected function adjustRowDimensions($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows): void
341  {
342  $aRowDimensions = array_reverse($pSheet->getRowDimensions(), true);
343  if (!empty($aRowDimensions)) {
344  foreach ($aRowDimensions as $objRowDimension) {
345  $newReference = $this->updateCellReference('A' . $objRowDimension->getRowIndex(), $pBefore, $pNumCols, $pNumRows);
346  [, $newReference] = Coordinate::coordinateFromString($newReference);
347  if ($objRowDimension->getRowIndex() != $newReference) {
348  $objRowDimension->setRowIndex($newReference);
349  }
350  }
351  $pSheet->refreshRowDimensions();
352 
353  $copyDimension = $pSheet->getRowDimension($beforeRow - 1);
354  for ($i = $beforeRow; $i <= $beforeRow - 1 + $pNumRows; ++$i) {
355  $newDimension = $pSheet->getRowDimension($i);
356  $newDimension->setRowHeight($copyDimension->getRowHeight());
357  $newDimension->setVisible($copyDimension->getVisible());
358  $newDimension->setOutlineLevel($copyDimension->getOutlineLevel());
359  $newDimension->setCollapsed($copyDimension->getCollapsed());
360  }
361  }
362  }
363 
372  public function insertNewBefore($pBefore, $pNumCols, $pNumRows, Worksheet $pSheet): void
373  {
374  $remove = ($pNumCols < 0 || $pNumRows < 0);
375  $allCoordinates = $pSheet->getCoordinates();
376 
377  // Get coordinate of $pBefore
378  [$beforeColumn, $beforeRow] = Coordinate::indexesFromString($pBefore);
379 
380  // Clear cells if we are removing columns or rows
381  $highestColumn = $pSheet->getHighestColumn();
382  $highestRow = $pSheet->getHighestRow();
383 
384  // 1. Clear column strips if we are removing columns
385  if ($pNumCols < 0 && $beforeColumn - 2 + $pNumCols > 0) {
386  for ($i = 1; $i <= $highestRow - 1; ++$i) {
387  for ($j = $beforeColumn - 1 + $pNumCols; $j <= $beforeColumn - 2; ++$j) {
388  $coordinate = Coordinate::stringFromColumnIndex($j + 1) . $i;
389  $pSheet->removeConditionalStyles($coordinate);
390  if ($pSheet->cellExists($coordinate)) {
391  $pSheet->getCell($coordinate)->setValueExplicit('', DataType::TYPE_NULL);
392  $pSheet->getCell($coordinate)->setXfIndex(0);
393  }
394  }
395  }
396  }
397 
398  // 2. Clear row strips if we are removing rows
399  if ($pNumRows < 0 && $beforeRow - 1 + $pNumRows > 0) {
400  for ($i = $beforeColumn - 1; $i <= Coordinate::columnIndexFromString($highestColumn) - 1; ++$i) {
401  for ($j = $beforeRow + $pNumRows; $j <= $beforeRow - 1; ++$j) {
402  $coordinate = Coordinate::stringFromColumnIndex($i + 1) . $j;
403  $pSheet->removeConditionalStyles($coordinate);
404  if ($pSheet->cellExists($coordinate)) {
405  $pSheet->getCell($coordinate)->setValueExplicit('', DataType::TYPE_NULL);
406  $pSheet->getCell($coordinate)->setXfIndex(0);
407  }
408  }
409  }
410  }
411 
412  // Loop through cells, bottom-up, and change cell coordinate
413  if ($remove) {
414  // It's faster to reverse and pop than to use unshift, especially with large cell collections
415  $allCoordinates = array_reverse($allCoordinates);
416  }
417  while ($coordinate = array_pop($allCoordinates)) {
418  $cell = $pSheet->getCell($coordinate);
419  $cellIndex = Coordinate::columnIndexFromString($cell->getColumn());
420 
421  if ($cellIndex - 1 + $pNumCols < 0) {
422  continue;
423  }
424 
425  // New coordinate
426  $newCoordinate = Coordinate::stringFromColumnIndex($cellIndex + $pNumCols) . ($cell->getRow() + $pNumRows);
427 
428  // Should the cell be updated? Move value and cellXf index from one cell to another.
429  if (($cellIndex >= $beforeColumn) && ($cell->getRow() >= $beforeRow)) {
430  // Update cell styles
431  $pSheet->getCell($newCoordinate)->setXfIndex($cell->getXfIndex());
432 
433  // Insert this cell at its new location
434  if ($cell->getDataType() == DataType::TYPE_FORMULA) {
435  // Formula should be adjusted
436  $pSheet->getCell($newCoordinate)
437  ->setValue($this->updateFormulaReferences($cell->getValue(), $pBefore, $pNumCols, $pNumRows, $pSheet->getTitle()));
438  } else {
439  // Formula should not be adjusted
440  $pSheet->getCell($newCoordinate)->setValue($cell->getValue());
441  }
442 
443  // Clear the original cell
444  $pSheet->getCellCollection()->delete($coordinate);
445  } else {
446  /* We don't need to update styles for rows/columns before our insertion position,
447  but we do still need to adjust any formulae in those cells */
448  if ($cell->getDataType() == DataType::TYPE_FORMULA) {
449  // Formula should be adjusted
450  $cell->setValue($this->updateFormulaReferences($cell->getValue(), $pBefore, $pNumCols, $pNumRows, $pSheet->getTitle()));
451  }
452  }
453  }
454 
455  // Duplicate styles for the newly inserted cells
456  $highestColumn = $pSheet->getHighestColumn();
457  $highestRow = $pSheet->getHighestRow();
458 
459  if ($pNumCols > 0 && $beforeColumn - 2 > 0) {
460  for ($i = $beforeRow; $i <= $highestRow - 1; ++$i) {
461  // Style
462  $coordinate = Coordinate::stringFromColumnIndex($beforeColumn - 1) . $i;
463  if ($pSheet->cellExists($coordinate)) {
464  $xfIndex = $pSheet->getCell($coordinate)->getXfIndex();
465  $conditionalStyles = $pSheet->conditionalStylesExists($coordinate) ?
466  $pSheet->getConditionalStyles($coordinate) : false;
467  for ($j = $beforeColumn; $j <= $beforeColumn - 1 + $pNumCols; ++$j) {
468  $pSheet->getCellByColumnAndRow($j, $i)->setXfIndex($xfIndex);
469  if ($conditionalStyles) {
470  $cloned = [];
471  foreach ($conditionalStyles as $conditionalStyle) {
472  $cloned[] = clone $conditionalStyle;
473  }
475  }
476  }
477  }
478  }
479  }
480 
481  if ($pNumRows > 0 && $beforeRow - 1 > 0) {
482  for ($i = $beforeColumn; $i <= Coordinate::columnIndexFromString($highestColumn); ++$i) {
483  // Style
484  $coordinate = Coordinate::stringFromColumnIndex($i) . ($beforeRow - 1);
485  if ($pSheet->cellExists($coordinate)) {
486  $xfIndex = $pSheet->getCell($coordinate)->getXfIndex();
487  $conditionalStyles = $pSheet->conditionalStylesExists($coordinate) ?
488  $pSheet->getConditionalStyles($coordinate) : false;
489  for ($j = $beforeRow; $j <= $beforeRow - 1 + $pNumRows; ++$j) {
490  $pSheet->getCell(Coordinate::stringFromColumnIndex($i) . $j)->setXfIndex($xfIndex);
491  if ($conditionalStyles) {
492  $cloned = [];
493  foreach ($conditionalStyles as $conditionalStyle) {
494  $cloned[] = clone $conditionalStyle;
495  }
497  }
498  }
499  }
500  }
501  }
502 
503  // Update worksheet: column dimensions
504  $this->adjustColumnDimensions($pSheet, $pBefore, $beforeColumn, $pNumCols, $beforeRow, $pNumRows);
505 
506  // Update worksheet: row dimensions
507  $this->adjustRowDimensions($pSheet, $pBefore, $beforeColumn, $pNumCols, $beforeRow, $pNumRows);
508 
509  // Update worksheet: page breaks
510  $this->adjustPageBreaks($pSheet, $pBefore, $beforeColumn, $pNumCols, $beforeRow, $pNumRows);
511 
512  // Update worksheet: comments
513  $this->adjustComments($pSheet, $pBefore, $beforeColumn, $pNumCols, $beforeRow, $pNumRows);
514 
515  // Update worksheet: hyperlinks
516  $this->adjustHyperlinks($pSheet, $pBefore, $beforeColumn, $pNumCols, $beforeRow, $pNumRows);
517 
518  // Update worksheet: data validations
519  $this->adjustDataValidations($pSheet, $pBefore, $beforeColumn, $pNumCols, $beforeRow, $pNumRows);
520 
521  // Update worksheet: merge cells
522  $this->adjustMergeCells($pSheet, $pBefore, $beforeColumn, $pNumCols, $beforeRow, $pNumRows);
523 
524  // Update worksheet: protected cells
525  $this->adjustProtectedCells($pSheet, $pBefore, $beforeColumn, $pNumCols, $beforeRow, $pNumRows);
526 
527  // Update worksheet: autofilter
528  $autoFilter = $pSheet->getAutoFilter();
529  $autoFilterRange = $autoFilter->getRange();
530  if (!empty($autoFilterRange)) {
531  if ($pNumCols != 0) {
532  $autoFilterColumns = $autoFilter->getColumns();
533  if (count($autoFilterColumns) > 0) {
534  $column = '';
535  $row = 0;
536  sscanf($pBefore, '%[A-Z]%d', $column, $row);
537  $columnIndex = Coordinate::columnIndexFromString($column);
538  [$rangeStart, $rangeEnd] = Coordinate::rangeBoundaries($autoFilterRange);
539  if ($columnIndex <= $rangeEnd[0]) {
540  if ($pNumCols < 0) {
541  // If we're actually deleting any columns that fall within the autofilter range,
542  // then we delete any rules for those columns
543  $deleteColumn = $columnIndex + $pNumCols - 1;
544  $deleteCount = abs($pNumCols);
545  for ($i = 1; $i <= $deleteCount; ++$i) {
546  if (isset($autoFilterColumns[Coordinate::stringFromColumnIndex($deleteColumn + 1)])) {
547  $autoFilter->clearColumn(Coordinate::stringFromColumnIndex($deleteColumn + 1));
548  }
549  ++$deleteColumn;
550  }
551  }
552  $startCol = ($columnIndex > $rangeStart[0]) ? $columnIndex : $rangeStart[0];
553 
554  // Shuffle columns in autofilter range
555  if ($pNumCols > 0) {
556  $startColRef = $startCol;
557  $endColRef = $rangeEnd[0];
558  $toColRef = $rangeEnd[0] + $pNumCols;
559 
560  do {
561  $autoFilter->shiftColumn(Coordinate::stringFromColumnIndex($endColRef), Coordinate::stringFromColumnIndex($toColRef));
562  --$endColRef;
563  --$toColRef;
564  } while ($startColRef <= $endColRef);
565  } else {
566  // For delete, we shuffle from beginning to end to avoid overwriting
567  $startColID = Coordinate::stringFromColumnIndex($startCol);
568  $toColID = Coordinate::stringFromColumnIndex($startCol + $pNumCols);
569  $endColID = Coordinate::stringFromColumnIndex($rangeEnd[0] + 1);
570  do {
571  $autoFilter->shiftColumn($startColID, $toColID);
572  ++$startColID;
573  ++$toColID;
574  } while ($startColID != $endColID);
575  }
576  }
577  }
578  }
579  $pSheet->setAutoFilter($this->updateCellReference($autoFilterRange, $pBefore, $pNumCols, $pNumRows));
580  }
581 
582  // Update worksheet: freeze pane
583  if ($pSheet->getFreezePane()) {
584  $splitCell = $pSheet->getFreezePane();
585  $topLeftCell = $pSheet->getTopLeftCell();
586 
587  $splitCell = $this->updateCellReference($splitCell, $pBefore, $pNumCols, $pNumRows);
588  $topLeftCell = $this->updateCellReference($topLeftCell, $pBefore, $pNumCols, $pNumRows);
589 
590  $pSheet->freezePane($splitCell, $topLeftCell);
591  }
592 
593  // Page setup
594  if ($pSheet->getPageSetup()->isPrintAreaSet()) {
595  $pSheet->getPageSetup()->setPrintArea($this->updateCellReference($pSheet->getPageSetup()->getPrintArea(), $pBefore, $pNumCols, $pNumRows));
596  }
597 
598  // Update worksheet: drawings
599  $aDrawings = $pSheet->getDrawingCollection();
600  foreach ($aDrawings as $objDrawing) {
601  $newReference = $this->updateCellReference($objDrawing->getCoordinates(), $pBefore, $pNumCols, $pNumRows);
602  if ($objDrawing->getCoordinates() != $newReference) {
603  $objDrawing->setCoordinates($newReference);
604  }
605  }
606 
607  // Update workbook: define names
608  if (count($pSheet->getParent()->getDefinedNames()) > 0) {
609  foreach ($pSheet->getParent()->getDefinedNames() as $definedName) {
610  if ($definedName->getWorksheet() !== null && $definedName->getWorksheet()->getHashCode() === $pSheet->getHashCode()) {
611  $definedName->setValue($this->updateCellReference($definedName->getValue(), $pBefore, $pNumCols, $pNumRows));
612  }
613  }
614  }
615 
616  // Garbage collect
617  $pSheet->garbageCollect();
618  }
619 
631  public function updateFormulaReferences($pFormula = '', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0, $sheetName = '')
632  {
633  // Update cell references in the formula
634  $formulaBlocks = explode('"', $pFormula);
635  $i = false;
636  foreach ($formulaBlocks as &$formulaBlock) {
637  // Ignore blocks that were enclosed in quotes (alternating entries in the $formulaBlocks array after the explode)
638  if ($i = !$i) {
639  $adjustCount = 0;
640  $newCellTokens = $cellTokens = [];
641  // Search for row ranges (e.g. 'Sheet1'!3:5 or 3:5) with or without $ absolutes (e.g. $3:5)
642  $matchCount = preg_match_all('/' . self::REFHELPER_REGEXP_ROWRANGE . '/i', ' ' . $formulaBlock . ' ', $matches, PREG_SET_ORDER);
643  if ($matchCount > 0) {
644  foreach ($matches as $match) {
645  $fromString = ($match[2] > '') ? $match[2] . '!' : '';
646  $fromString .= $match[3] . ':' . $match[4];
647  $modified3 = substr($this->updateCellReference('$A' . $match[3], $pBefore, $pNumCols, $pNumRows), 2);
648  $modified4 = substr($this->updateCellReference('$A' . $match[4], $pBefore, $pNumCols, $pNumRows), 2);
649 
650  if ($match[3] . ':' . $match[4] !== $modified3 . ':' . $modified4) {
651  if (($match[2] == '') || (trim($match[2], "'") == $sheetName)) {
652  $toString = ($match[2] > '') ? $match[2] . '!' : '';
653  $toString .= $modified3 . ':' . $modified4;
654  // 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
655  $column = 100000;
656  $row = 10000000 + (int) trim($match[3], '$');
657  $cellIndex = $column . $row;
658 
659  $newCellTokens[$cellIndex] = preg_quote($toString, '/');
660  $cellTokens[$cellIndex] = '/(?<!\d\$\!)' . preg_quote($fromString, '/') . '(?!\d)/i';
661  ++$adjustCount;
662  }
663  }
664  }
665  }
666  // Search for column ranges (e.g. 'Sheet1'!C:E or C:E) with or without $ absolutes (e.g. $C:E)
667  $matchCount = preg_match_all('/' . self::REFHELPER_REGEXP_COLRANGE . '/i', ' ' . $formulaBlock . ' ', $matches, PREG_SET_ORDER);
668  if ($matchCount > 0) {
669  foreach ($matches as $match) {
670  $fromString = ($match[2] > '') ? $match[2] . '!' : '';
671  $fromString .= $match[3] . ':' . $match[4];
672  $modified3 = substr($this->updateCellReference($match[3] . '$1', $pBefore, $pNumCols, $pNumRows), 0, -2);
673  $modified4 = substr($this->updateCellReference($match[4] . '$1', $pBefore, $pNumCols, $pNumRows), 0, -2);
674 
675  if ($match[3] . ':' . $match[4] !== $modified3 . ':' . $modified4) {
676  if (($match[2] == '') || (trim($match[2], "'") == $sheetName)) {
677  $toString = ($match[2] > '') ? $match[2] . '!' : '';
678  $toString .= $modified3 . ':' . $modified4;
679  // 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
680  $column = Coordinate::columnIndexFromString(trim($match[3], '$')) + 100000;
681  $row = 10000000;
682  $cellIndex = $column . $row;
683 
684  $newCellTokens[$cellIndex] = preg_quote($toString, '/');
685  $cellTokens[$cellIndex] = '/(?<![A-Z\$\!])' . preg_quote($fromString, '/') . '(?![A-Z])/i';
686  ++$adjustCount;
687  }
688  }
689  }
690  }
691  // Search for cell ranges (e.g. 'Sheet1'!A3:C5 or A3:C5) with or without $ absolutes (e.g. $A1:C$5)
692  $matchCount = preg_match_all('/' . self::REFHELPER_REGEXP_CELLRANGE . '/i', ' ' . $formulaBlock . ' ', $matches, PREG_SET_ORDER);
693  if ($matchCount > 0) {
694  foreach ($matches as $match) {
695  $fromString = ($match[2] > '') ? $match[2] . '!' : '';
696  $fromString .= $match[3] . ':' . $match[4];
697  $modified3 = $this->updateCellReference($match[3], $pBefore, $pNumCols, $pNumRows);
698  $modified4 = $this->updateCellReference($match[4], $pBefore, $pNumCols, $pNumRows);
699 
700  if ($match[3] . $match[4] !== $modified3 . $modified4) {
701  if (($match[2] == '') || (trim($match[2], "'") == $sheetName)) {
702  $toString = ($match[2] > '') ? $match[2] . '!' : '';
703  $toString .= $modified3 . ':' . $modified4;
704  [$column, $row] = Coordinate::coordinateFromString($match[3]);
705  // 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
706  $column = Coordinate::columnIndexFromString(trim($column, '$')) + 100000;
707  $row = (int) trim($row, '$') + 10000000;
708  $cellIndex = $column . $row;
709 
710  $newCellTokens[$cellIndex] = preg_quote($toString, '/');
711  $cellTokens[$cellIndex] = '/(?<![A-Z]\$\!)' . preg_quote($fromString, '/') . '(?!\d)/i';
712  ++$adjustCount;
713  }
714  }
715  }
716  }
717  // Search for cell references (e.g. 'Sheet1'!A3 or C5) with or without $ absolutes (e.g. $A1 or C$5)
718  $matchCount = preg_match_all('/' . self::REFHELPER_REGEXP_CELLREF . '/i', ' ' . $formulaBlock . ' ', $matches, PREG_SET_ORDER);
719 
720  if ($matchCount > 0) {
721  foreach ($matches as $match) {
722  $fromString = ($match[2] > '') ? $match[2] . '!' : '';
723  $fromString .= $match[3];
724 
725  $modified3 = $this->updateCellReference($match[3], $pBefore, $pNumCols, $pNumRows);
726  if ($match[3] !== $modified3) {
727  if (($match[2] == '') || (trim($match[2], "'") == $sheetName)) {
728  $toString = ($match[2] > '') ? $match[2] . '!' : '';
729  $toString .= $modified3;
730  [$column, $row] = Coordinate::coordinateFromString($match[3]);
731  // 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
732  $column = Coordinate::columnIndexFromString(trim($column, '$')) + 100000;
733  $row = (int) trim($row, '$') + 10000000;
734  $cellIndex = $row . $column;
735 
736  $newCellTokens[$cellIndex] = preg_quote($toString, '/');
737  $cellTokens[$cellIndex] = '/(?<![A-Z\$\!])' . preg_quote($fromString, '/') . '(?!\d)/i';
738  ++$adjustCount;
739  }
740  }
741  }
742  }
743  if ($adjustCount > 0) {
744  if ($pNumCols > 0 || $pNumRows > 0) {
745  krsort($cellTokens);
746  krsort($newCellTokens);
747  } else {
748  ksort($cellTokens);
749  ksort($newCellTokens);
750  } // Update cell references in the formula
751  $formulaBlock = str_replace('\\', '', preg_replace($cellTokens, $newCellTokens, $formulaBlock));
752  }
753  }
754  }
755  unset($formulaBlock);
756 
757  // Then rebuild the formula string
758  return implode('"', $formulaBlocks);
759  }
760 
764  public function updateFormulaReferencesAnyWorksheet(string $formula = '', int $insertColumns = 0, int $insertRows = 0): string
765  {
766  $formula = $this->updateCellReferencesAllWorksheets($formula, $insertColumns, $insertRows);
767 
768  if ($insertColumns !== 0) {
769  $formula = $this->updateColumnRangesAllWorksheets($formula, $insertColumns);
770  }
771 
772  if ($insertRows !== 0) {
773  $formula = $this->updateRowRangesAllWorksheets($formula, $insertRows);
774  }
775 
776  return $formula;
777  }
778 
779  private function updateCellReferencesAllWorksheets(string $formula, int $insertColumns, int $insertRows): string
780  {
781  $splitCount = preg_match_all(
783  $formula,
784  $splitRanges,
785  PREG_OFFSET_CAPTURE
786  );
787 
788  $columnLengths = array_map('strlen', array_column($splitRanges[6], 0));
789  $rowLengths = array_map('strlen', array_column($splitRanges[7], 0));
790  $columnOffsets = array_column($splitRanges[6], 1);
791  $rowOffsets = array_column($splitRanges[7], 1);
792 
793  $columns = $splitRanges[6];
794  $rows = $splitRanges[7];
795 
796  while ($splitCount > 0) {
797  --$splitCount;
798  $columnLength = $columnLengths[$splitCount];
799  $rowLength = $rowLengths[$splitCount];
800  $columnOffset = $columnOffsets[$splitCount];
801  $rowOffset = $rowOffsets[$splitCount];
802  $column = $columns[$splitCount][0];
803  $row = $rows[$splitCount][0];
804 
805  if (!empty($column) && $column[0] !== '$') {
806  $column = Coordinate::stringFromColumnIndex(Coordinate::columnIndexFromString($column) + $insertColumns);
807  $formula = substr($formula, 0, $columnOffset) . $column . substr($formula, $columnOffset + $columnLength);
808  }
809  if (!empty($row) && $row[0] !== '$') {
810  $row += $insertRows;
811  $formula = substr($formula, 0, $rowOffset) . $row . substr($formula, $rowOffset + $rowLength);
812  }
813  }
814 
815  return $formula;
816  }
817 
818  private function updateColumnRangesAllWorksheets(string $formula, int $insertColumns): string
819  {
820  $splitCount = preg_match_all(
822  $formula,
823  $splitRanges,
824  PREG_OFFSET_CAPTURE
825  );
826 
827  $fromColumnLengths = array_map('strlen', array_column($splitRanges[1], 0));
828  $fromColumnOffsets = array_column($splitRanges[1], 1);
829  $toColumnLengths = array_map('strlen', array_column($splitRanges[2], 0));
830  $toColumnOffsets = array_column($splitRanges[2], 1);
831 
832  $fromColumns = $splitRanges[1];
833  $toColumns = $splitRanges[2];
834 
835  while ($splitCount > 0) {
836  --$splitCount;
837  $fromColumnLength = $fromColumnLengths[$splitCount];
838  $toColumnLength = $toColumnLengths[$splitCount];
839  $fromColumnOffset = $fromColumnOffsets[$splitCount];
840  $toColumnOffset = $toColumnOffsets[$splitCount];
841  $fromColumn = $fromColumns[$splitCount][0];
842  $toColumn = $toColumns[$splitCount][0];
843 
844  if (!empty($fromColumn) && $fromColumn[0] !== '$') {
845  $fromColumn = Coordinate::stringFromColumnIndex(Coordinate::columnIndexFromString($fromColumn) + $insertColumns);
846  $formula = substr($formula, 0, $fromColumnOffset) . $fromColumn . substr($formula, $fromColumnOffset + $fromColumnLength);
847  }
848  if (!empty($toColumn) && $toColumn[0] !== '$') {
849  $toColumn = Coordinate::stringFromColumnIndex(Coordinate::columnIndexFromString($toColumn) + $insertColumns);
850  $formula = substr($formula, 0, $toColumnOffset) . $toColumn . substr($formula, $toColumnOffset + $toColumnLength);
851  }
852  }
853 
854  return $formula;
855  }
856 
857  private function updateRowRangesAllWorksheets(string $formula, int $insertRows): string
858  {
859  $splitCount = preg_match_all(
861  $formula,
862  $splitRanges,
863  PREG_OFFSET_CAPTURE
864  );
865 
866  $fromRowLengths = array_map('strlen', array_column($splitRanges[1], 0));
867  $fromRowOffsets = array_column($splitRanges[1], 1);
868  $toRowLengths = array_map('strlen', array_column($splitRanges[2], 0));
869  $toRowOffsets = array_column($splitRanges[2], 1);
870 
871  $fromRows = $splitRanges[1];
872  $toRows = $splitRanges[2];
873 
874  while ($splitCount > 0) {
875  --$splitCount;
876  $fromRowLength = $fromRowLengths[$splitCount];
877  $toRowLength = $toRowLengths[$splitCount];
878  $fromRowOffset = $fromRowOffsets[$splitCount];
879  $toRowOffset = $toRowOffsets[$splitCount];
880  $fromRow = $fromRows[$splitCount][0];
881  $toRow = $toRows[$splitCount][0];
882 
883  if (!empty($fromRow) && $fromRow[0] !== '$') {
884  $fromRow += $insertRows;
885  $formula = substr($formula, 0, $fromRowOffset) . $fromRow . substr($formula, $fromRowOffset + $fromRowLength);
886  }
887  if (!empty($toRow) && $toRow[0] !== '$') {
888  $toRow += $insertRows;
889  $formula = substr($formula, 0, $toRowOffset) . $toRow . substr($formula, $toRowOffset + $toRowLength);
890  }
891  }
892 
893  return $formula;
894  }
895 
906  public function updateCellReference($pCellRange = 'A1', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0)
907  {
908  // Is it in another worksheet? Will not have to update anything.
909  if (strpos($pCellRange, '!') !== false) {
910  return $pCellRange;
911  // Is it a range or a single cell?
912  } elseif (!Coordinate::coordinateIsRange($pCellRange)) {
913  // Single cell
914  return $this->updateSingleCellReference($pCellRange, $pBefore, $pNumCols, $pNumRows);
915  } elseif (Coordinate::coordinateIsRange($pCellRange)) {
916  // Range
917  return $this->updateCellRange($pCellRange, $pBefore, $pNumCols, $pNumRows);
918  }
919 
920  // Return original
921  return $pCellRange;
922  }
923 
931  public function updateNamedFormulas(Spreadsheet $spreadsheet, $oldName = '', $newName = ''): void
932  {
933  if ($oldName == '') {
934  return;
935  }
936 
937  foreach ($spreadsheet->getWorksheetIterator() as $sheet) {
938  foreach ($sheet->getCoordinates(false) as $coordinate) {
939  $cell = $sheet->getCell($coordinate);
940  if (($cell !== null) && ($cell->getDataType() == DataType::TYPE_FORMULA)) {
941  $formula = $cell->getValue();
942  if (strpos($formula, $oldName) !== false) {
943  $formula = str_replace("'" . $oldName . "'!", "'" . $newName . "'!", $formula);
944  $formula = str_replace($oldName . '!', $newName . '!', $formula);
945  $cell->setValueExplicit($formula, DataType::TYPE_FORMULA);
946  }
947  }
948  }
949  }
950  }
951 
962  private function updateCellRange($pCellRange = 'A1:A1', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0)
963  {
964  if (!Coordinate::coordinateIsRange($pCellRange)) {
965  throw new Exception('Only cell ranges may be passed to this method.');
966  }
967 
968  // Update range
969  $range = Coordinate::splitRange($pCellRange);
970  $ic = count($range);
971  for ($i = 0; $i < $ic; ++$i) {
972  $jc = count($range[$i]);
973  for ($j = 0; $j < $jc; ++$j) {
974  if (ctype_alpha($range[$i][$j])) {
975  $r = Coordinate::coordinateFromString($this->updateSingleCellReference($range[$i][$j] . '1', $pBefore, $pNumCols, $pNumRows));
976  $range[$i][$j] = $r[0];
977  } elseif (ctype_digit($range[$i][$j])) {
978  $r = Coordinate::coordinateFromString($this->updateSingleCellReference('A' . $range[$i][$j], $pBefore, $pNumCols, $pNumRows));
979  $range[$i][$j] = $r[1];
980  } else {
981  $range[$i][$j] = $this->updateSingleCellReference($range[$i][$j], $pBefore, $pNumCols, $pNumRows);
982  }
983  }
984  }
985 
986  // Recreate range string
987  return Coordinate::buildRange($range);
988  }
989 
1000  private function updateSingleCellReference($pCellReference = 'A1', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0)
1001  {
1002  if (Coordinate::coordinateIsRange($pCellReference)) {
1003  throw new Exception('Only single cell references may be passed to this method.');
1004  }
1005 
1006  // Get coordinate of $pBefore
1007  [$beforeColumn, $beforeRow] = Coordinate::coordinateFromString($pBefore);
1008 
1009  // Get coordinate of $pCellReference
1010  [$newColumn, $newRow] = Coordinate::coordinateFromString($pCellReference);
1011 
1012  // Verify which parts should be updated
1013  $updateColumn = (($newColumn[0] != '$') && ($beforeColumn[0] != '$') && (Coordinate::columnIndexFromString($newColumn) >= Coordinate::columnIndexFromString($beforeColumn)));
1014  $updateRow = (($newRow[0] != '$') && ($beforeRow[0] != '$') && $newRow >= $beforeRow);
1015 
1016  // Create new column reference
1017  if ($updateColumn) {
1018  $newColumn = Coordinate::stringFromColumnIndex(Coordinate::columnIndexFromString($newColumn) + $pNumCols);
1019  }
1020 
1021  // Create new row reference
1022  if ($updateRow) {
1023  $newRow = (int) $newRow + $pNumRows;
1024  }
1025 
1026  // Return new reference
1027  return $newColumn . $newRow;
1028  }
1029 
1033  final public function __clone()
1034  {
1035  throw new Exception('Cloning a Singleton is not allowed!');
1036  }
1037 }
static coordinateIsRange($coord)
Checks if a coordinate represents a range of cells.
Definition: Coordinate.php:69
removeConditionalStyles($pCoordinate)
Removes conditional styles for a cell.
Definition: Worksheet.php:1467
getHighestRow($column=null)
Get highest worksheet row.
Definition: Worksheet.php:1072
static splitRange($pRange)
Split range into coordinate strings.
Definition: Coordinate.php:140
getWorksheetIterator()
Get worksheet iterator.
static cellAddressInDeleteRange($cellAddress, $beforeRow, $pNumRows, $beforeColumnIndex, $pNumCols)
Test whether a cell address falls within a defined range of cells.
adjustComments($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
Update cell comments when inserting/deleting rows/columns.
conditionalStylesExists($pCoordinate)
Do conditional styles exist for this cell?
Definition: Worksheet.php:1455
updateSingleCellReference($pCellReference='A1', $pBefore='A1', $pNumCols=0, $pNumRows=0)
Update single cell reference.
adjustHyperlinks($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
Update hyperlinks when inserting/deleting rows/columns.
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...
setConditionalStyles($pCoordinate, $pValue)
Set conditional styles.
Definition: Worksheet.php:1492
updateRowRangesAllWorksheets(string $formula, int $insertRows)
getCoordinates($sorted=true)
Get a sorted list of all cell coordinates currently held in the collection by row and column...
Definition: Worksheet.php:484
updateFormulaReferencesAnyWorksheet(string $formula='', int $insertColumns=0, int $insertRows=0)
Update all cell references within a formula, irrespective of worksheet.
adjustPageBreaks(Worksheet $pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
Update page breaks when inserting/deleting rows/columns.
insertNewBefore($pBefore, $pNumCols, $pNumRows, Worksheet $pSheet)
Insert a new column or row, updating all possible related data.
getTopLeftCell()
Get the default position of the right bottom pane.
Definition: Worksheet.php:1995
adjustProtectedCells($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
Update protected cells when inserting/deleting rows/columns.
$r
Definition: example_031.php:79
static columnReverseSort($a, $b)
Compare two column addresses Intended for use as a Callback function for reverse sorting column addre...
adjustMergeCells($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
Update merged cells when inserting/deleting rows/columns.
updateColumnRangesAllWorksheets(string $formula, int $insertColumns)
updateCellReferencesAllWorksheets(string $formula, int $insertColumns, int $insertRows)
updateFormulaReferences($pFormula='', $pBefore='A1', $pNumCols=0, $pNumRows=0, $sheetName='')
Update references within formulas.
static cellSort($a, $b)
Compare two cell addresses Intended for use as a Callback function for sorting cell addresses by colu...
static buildRange(array $pRange)
Build range from coordinate strings.
Definition: Coordinate.php:163
static getInstance()
Get an instance of this class.
static indexesFromString(string $coordinates)
Get indexes from a string coordinates.
Definition: Coordinate.php:52
adjustDataValidations($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
Update data validations when inserting/deleting rows/columns.
$row
$rows
Definition: xhr_table.php:10
getHighestColumn($row=null)
Get highest worksheet column.
Definition: Worksheet.php:1042
freezePane($cell, $topLeftCell=null)
Freeze Pane.
Definition: Worksheet.php:1950
static coordinateFromString($pCoordinateString)
Coordinate from string.
Definition: Coordinate.php:32
updateCellRange($pCellRange='A1:A1', $pBefore='A1', $pNumCols=0, $pNumRows=0)
Update cell range.
getDrawingCollection()
Get collection of drawings.
Definition: Worksheet.php:542
updateCellReference($pCellRange='A1', $pBefore='A1', $pNumCols=0, $pNumRows=0)
Update cell reference.
static rangeBoundaries($pRange)
Calculate range boundaries.
Definition: Coordinate.php:187
$i
Definition: disco.tpl.php:19
getCellCollection()
Return the cell collection.
Definition: Worksheet.php:410
static columnSort($a, $b)
Compare two column addresses Intended for use as a Callback function for sorting column addresses by ...
updateNamedFormulas(Spreadsheet $spreadsheet, $oldName='', $newName='')
Update named formulas (i.e.
adjustRowDimensions($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
Update row dimensions when inserting/deleting rows/columns.
setBreak($pCoordinate, $pBreak)
Set break on a cell.
Definition: Worksheet.php:1609
if(! $in) $columns
Definition: Utf8Test.php:45
static columnIndexFromString($pString)
Column index from string.
Definition: Coordinate.php:265
$key
Definition: croninfo.php:18
__construct()
Create a new ReferenceHelper.
garbageCollect()
Run PhpSpreadsheet garbage collector.
Definition: Worksheet.php:2661
static stringFromColumnIndex($columnIndex)
String from column index.
Definition: Coordinate.php:313
getConditionalStyles($pCoordinate)
Get conditional styles for a cell.
Definition: Worksheet.php:1438