[Summary view]

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

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