[Summary view]

Differences Between: [Versions 311 and 400] [Versions 311 and 401] [Versions 311 and 402] [Versions 311 and 403]

   1  <?php
   2  
   3  namespace PhpOffice\PhpSpreadsheet\Cell;
   4  
   5  use PhpOffice\PhpSpreadsheet\Exception;
   6  
   7  class AddressHelper
   8  {
   9      /**
  10       * Converts an R1C1 format cell address to an A1 format cell address.
  11       */
  12      public static function convertToA1(
  13          string $address,
  14          int $currentRowNumber = 1,
  15          int $currentColumnNumber = 1
  16      ): string {
  17          $validityCheck = preg_match('/^(R(\[?-?\d*\]?))(C(\[?-?\d*\]?))$/i', $address, $cellReference);
  18  
  19          if ($validityCheck === 0) {
  20              throw new Exception('Invalid R1C1-format Cell Reference');
  21          }
  22  
  23          $rowReference = $cellReference[2];
  24          //    Empty R reference is the current row
  25          if ($rowReference === '') {
  26              $rowReference = (string) $currentRowNumber;
  27          }
  28          //    Bracketed R references are relative to the current row
  29          if ($rowReference[0] === '[') {
  30              $rowReference = $currentRowNumber + trim($rowReference, '[]');
  31          }
  32          $columnReference = $cellReference[4];
  33          //    Empty C reference is the current column
  34          if ($columnReference === '') {
  35              $columnReference = (string) $currentColumnNumber;
  36          }
  37          //    Bracketed C references are relative to the current column
  38          if (is_string($columnReference) && $columnReference[0] === '[') {
  39              $columnReference = $currentColumnNumber + trim($columnReference, '[]');
  40          }
  41  
  42          if ($columnReference <= 0 || $rowReference <= 0) {
  43              throw new Exception('Invalid R1C1-format Cell Reference, Value out of range');
  44          }
  45          $A1CellReference = Coordinate::stringFromColumnIndex($columnReference) . $rowReference;
  46  
  47          return $A1CellReference;
  48      }
  49  
  50      /**
  51       * Converts a formula that uses R1C1 format cell address to an A1 format cell address.
  52       */
  53      public static function convertFormulaToA1(
  54          string $formula,
  55          int $currentRowNumber = 1,
  56          int $currentColumnNumber = 1
  57      ): string {
  58          if (substr($formula, 0, 3) == 'of:') {
  59              $formula = substr($formula, 3);
  60              $temp = explode('"', $formula);
  61              $key = false;
  62              foreach ($temp as &$value) {
  63                  //    Only replace in alternate array entries (i.e. non-quoted blocks)
  64                  if ($key = !$key) {
  65                      $value = str_replace(['[.', '.', ']'], '', $value);
  66                  }
  67              }
  68          } else {
  69              //    Convert R1C1 style references to A1 style references (but only when not quoted)
  70              $temp = explode('"', $formula);
  71              $key = false;
  72              foreach ($temp as &$value) {
  73                  //    Only replace in alternate array entries (i.e. non-quoted blocks)
  74                  if ($key = !$key) {
  75                      preg_match_all('/(R(\[?-?\d*\]?))(C(\[?-?\d*\]?))/', $value, $cellReferences, PREG_SET_ORDER + PREG_OFFSET_CAPTURE);
  76                      //    Reverse the matches array, otherwise all our offsets will become incorrect if we modify our way
  77                      //        through the formula from left to right. Reversing means that we work right to left.through
  78                      //        the formula
  79                      $cellReferences = array_reverse($cellReferences);
  80                      //    Loop through each R1C1 style reference in turn, converting it to its A1 style equivalent,
  81                      //        then modify the formula to use that new reference
  82                      foreach ($cellReferences as $cellReference) {
  83                          $A1CellReference = self::convertToA1($cellReference[0][0], $currentRowNumber, $currentColumnNumber);
  84                          $value = substr_replace($value, $A1CellReference, $cellReference[0][1], strlen($cellReference[0][0]));
  85                      }
  86                  }
  87              }
  88          }
  89          unset($value);
  90          //    Then rebuild the formula string
  91          $formula = implode('"', $temp);
  92  
  93          return $formula;
  94      }
  95  
  96      /**
  97       * Converts an A1 format cell address to an R1C1 format cell address.
  98       * If $currentRowNumber or $currentColumnNumber are provided, then the R1C1 address will be formatted as a relative address.
  99       */
 100      public static function convertToR1C1(
 101          string $address,
 102          ?int $currentRowNumber = null,
 103          ?int $currentColumnNumber = null
 104      ): string {
 105          $validityCheck = preg_match('/^\$?([A-Z]{1,3})\$?(\d{1,7})$/i', $address, $cellReference);
 106  
 107          if ($validityCheck === 0) {
 108              throw new Exception('Invalid A1-format Cell Reference');
 109          }
 110  
 111          $columnId = Coordinate::columnIndexFromString($cellReference[1]);
 112          $rowId = (int) $cellReference[2];
 113  
 114          if ($currentRowNumber !== null) {
 115              if ($rowId === $currentRowNumber) {
 116                  $rowId = '';
 117              } else {
 118                  $rowId = '[' . ($rowId - $currentRowNumber) . ']';
 119              }
 120          }
 121  
 122          if ($currentColumnNumber !== null) {
 123              if ($columnId === $currentColumnNumber) {
 124                  $columnId = '';
 125              } else {
 126                  $columnId = '[' . ($columnId - $currentColumnNumber) . ']';
 127              }
 128          }
 129  
 130          $R1C1Address = "R{$rowId}C{$columnId}";
 131  
 132          return $R1C1Address;
 133      }
 134  }