[Summary view]

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

   1  <?php
   2  
   3  namespace PhpOffice\PhpSpreadsheet\Cell;
   4  
   5  use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
   6  use PhpOffice\PhpSpreadsheet\Calculation\Functions;
   7  use PhpOffice\PhpSpreadsheet\Exception;
   8  
   9  class AddressHelper
  10  {
  11      public const R1C1_COORDINATE_REGEX = '/(R((?:\[-?\d*\])|(?:\d*))?)(C((?:\[-?\d*\])|(?:\d*))?)/i';
  12  
  13      /** @return string[] */
  14      public static function getRowAndColumnChars()
  15      {
  16          $rowChar = 'R';
  17          $colChar = 'C';
  18          if (Functions::getCompatibilityMode() === Functions::COMPATIBILITY_EXCEL) {
  19              $rowColChars = Calculation::localeFunc('*RC');
  20              if (mb_strlen($rowColChars) === 2) {
  21                  $rowChar = mb_substr($rowColChars, 0, 1);
  22                  $colChar = mb_substr($rowColChars, 1, 1);
  23              }
  24          }
  25  
  26          return [$rowChar, $colChar];
  27      }
  28  
  29      /**
  30       * Converts an R1C1 format cell address to an A1 format cell address.
  31       */
  32      public static function convertToA1(
  33          string $address,
  34          int $currentRowNumber = 1,
  35          int $currentColumnNumber = 1,
  36          bool $useLocale = true
  37      ): string {
  38          [$rowChar, $colChar] = $useLocale ? self::getRowAndColumnChars() : ['R', 'C'];
  39          $regex = '/^(' . $rowChar . '(\[?[-+]?\d*\]?))(' . $colChar . '(\[?[-+]?\d*\]?))$/i';
  40          $validityCheck = preg_match($regex, $address, $cellReference);
  41  
  42          if (empty($validityCheck)) {
  43              throw new Exception('Invalid R1C1-format Cell Reference');
  44          }
  45  
  46          $rowReference = $cellReference[2];
  47          //    Empty R reference is the current row
  48          if ($rowReference === '') {
  49              $rowReference = (string) $currentRowNumber;
  50          }
  51          //    Bracketed R references are relative to the current row
  52          if ($rowReference[0] === '[') {
  53              $rowReference = $currentRowNumber + (int) trim($rowReference, '[]');
  54          }
  55          $columnReference = $cellReference[4];
  56          //    Empty C reference is the current column
  57          if ($columnReference === '') {
  58              $columnReference = (string) $currentColumnNumber;
  59          }
  60          //    Bracketed C references are relative to the current column
  61          if (is_string($columnReference) && $columnReference[0] === '[') {
  62              $columnReference = $currentColumnNumber + (int) trim($columnReference, '[]');
  63          }
  64  
  65          if ($columnReference <= 0 || $rowReference <= 0) {
  66              throw new Exception('Invalid R1C1-format Cell Reference, Value out of range');
  67          }
  68          $A1CellReference = Coordinate::stringFromColumnIndex($columnReference) . $rowReference;
  69  
  70          return $A1CellReference;
  71      }
  72  
  73      protected static function convertSpreadsheetMLFormula(string $formula): string
  74      {
  75          $formula = substr($formula, 3);
  76          $temp = explode('"', $formula);
  77          $key = false;
  78          foreach ($temp as &$value) {
  79              //    Only replace in alternate array entries (i.e. non-quoted blocks)
  80              if ($key = !$key) {
  81                  $value = str_replace(['[.', ':.', ']'], ['', ':', ''], $value);
  82              }
  83          }
  84          unset($value);
  85  
  86          return implode('"', $temp);
  87      }
  88  
  89      /**
  90       * Converts a formula that uses R1C1/SpreadsheetXML format cell address to an A1 format cell address.
  91       */
  92      public static function convertFormulaToA1(
  93          string $formula,
  94          int $currentRowNumber = 1,
  95          int $currentColumnNumber = 1
  96      ): string {
  97          if (substr($formula, 0, 3) == 'of:') {
  98              // We have an old-style SpreadsheetML Formula
  99              return self::convertSpreadsheetMLFormula($formula);
 100          }
 101  
 102          //    Convert R1C1 style references to A1 style references (but only when not quoted)
 103          $temp = explode('"', $formula);
 104          $key = false;
 105          foreach ($temp as &$value) {
 106              //    Only replace in alternate array entries (i.e. non-quoted blocks)
 107              if ($key = !$key) {
 108                  preg_match_all(self::R1C1_COORDINATE_REGEX, $value, $cellReferences, PREG_SET_ORDER + PREG_OFFSET_CAPTURE);
 109                  //    Reverse the matches array, otherwise all our offsets will become incorrect if we modify our way
 110                  //        through the formula from left to right. Reversing means that we work right to left.through
 111                  //        the formula
 112                  $cellReferences = array_reverse($cellReferences);
 113                  //    Loop through each R1C1 style reference in turn, converting it to its A1 style equivalent,
 114                  //        then modify the formula to use that new reference
 115                  foreach ($cellReferences as $cellReference) {
 116                      $A1CellReference = self::convertToA1($cellReference[0][0], $currentRowNumber, $currentColumnNumber, false);
 117                      $value = substr_replace($value, $A1CellReference, $cellReference[0][1], strlen($cellReference[0][0]));
 118                  }
 119              }
 120          }
 121          unset($value);
 122  
 123          //    Then rebuild the formula string
 124          return implode('"', $temp);
 125      }
 126  
 127      /**
 128       * Converts an A1 format cell address to an R1C1 format cell address.
 129       * If $currentRowNumber or $currentColumnNumber are provided, then the R1C1 address will be formatted as a relative address.
 130       */
 131      public static function convertToR1C1(
 132          string $address,
 133          ?int $currentRowNumber = null,
 134          ?int $currentColumnNumber = null
 135      ): string {
 136          $validityCheck = preg_match(Coordinate::A1_COORDINATE_REGEX, $address, $cellReference);
 137  
 138          if ($validityCheck === 0) {
 139              throw new Exception('Invalid A1-format Cell Reference');
 140          }
 141  
 142          if ($cellReference['col'][0] === '$') {
 143              // Column must be absolute address
 144              $currentColumnNumber = null;
 145          }
 146          $columnId = Coordinate::columnIndexFromString(ltrim($cellReference['col'], '$'));
 147  
 148          if ($cellReference['row'][0] === '$') {
 149              // Row must be absolute address
 150              $currentRowNumber = null;
 151          }
 152          $rowId = (int) ltrim($cellReference['row'], '$');
 153  
 154          if ($currentRowNumber !== null) {
 155              if ($rowId === $currentRowNumber) {
 156                  $rowId = '';
 157              } else {
 158                  $rowId = '[' . ($rowId - $currentRowNumber) . ']';
 159              }
 160          }
 161  
 162          if ($currentColumnNumber !== null) {
 163              if ($columnId === $currentColumnNumber) {
 164                  $columnId = '';
 165              } else {
 166                  $columnId = '[' . ($columnId - $currentColumnNumber) . ']';
 167              }
 168          }
 169  
 170          $R1C1Address = "R{$rowId}C{$columnId}";
 171  
 172          return $R1C1Address;
 173      }
 174  }