[Summary view]

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

   1  <?php
   2  
   3  namespace PhpOffice\PhpSpreadsheet\Calculation\LookupRef;
   4  
   5  use PhpOffice\PhpSpreadsheet\Calculation\ArrayEnabled;
   6  use PhpOffice\PhpSpreadsheet\Calculation\Information\ExcelError;
   7  use PhpOffice\PhpSpreadsheet\Cell\AddressHelper;
   8  use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
   9  
  10  class Address
  11  {
  12      use ArrayEnabled;
  13  
  14      public const ADDRESS_ABSOLUTE = 1;
  15      public const ADDRESS_COLUMN_RELATIVE = 2;
  16      public const ADDRESS_ROW_RELATIVE = 3;
  17      public const ADDRESS_RELATIVE = 4;
  18  
  19      public const REFERENCE_STYLE_A1 = true;
  20      public const REFERENCE_STYLE_R1C1 = false;
  21  
  22      /**
  23       * ADDRESS.
  24       *
  25       * Creates a cell address as text, given specified row and column numbers.
  26       *
  27       * Excel Function:
  28       *        =ADDRESS(row, column, [relativity], [referenceStyle], [sheetText])
  29       *
  30       * @param mixed $row Row number (integer) to use in the cell reference
  31       *                      Or can be an array of values
  32       * @param mixed $column Column number (integer) to use in the cell reference
  33       *                      Or can be an array of values
  34       * @param mixed $relativity Integer flag indicating the type of reference to return
  35       *                             1 or omitted    Absolute
  36       *                             2               Absolute row; relative column
  37       *                             3               Relative row; absolute column
  38       *                             4               Relative
  39       *                      Or can be an array of values
  40       * @param mixed $referenceStyle A logical (boolean) value that specifies the A1 or R1C1 reference style.
  41       *                                TRUE or omitted    ADDRESS returns an A1-style reference
  42       *                                FALSE              ADDRESS returns an R1C1-style reference
  43       *                      Or can be an array of values
  44       * @param mixed $sheetName Optional Name of worksheet to use
  45       *                      Or can be an array of values
  46       *
  47       * @return array|string
  48       *         If an array of values is passed as the $testValue argument, then the returned result will also be
  49       *            an array with the same dimensions
  50       */
  51      public static function cell($row, $column, $relativity = 1, $referenceStyle = true, $sheetName = '')
  52      {
  53          if (
  54              is_array($row) || is_array($column) ||
  55              is_array($relativity) || is_array($referenceStyle) || is_array($sheetName)
  56          ) {
  57              return self::evaluateArrayArguments(
  58                  [self::class, __FUNCTION__],
  59                  $row,
  60                  $column,
  61                  $relativity,
  62                  $referenceStyle,
  63                  $sheetName
  64              );
  65          }
  66  
  67          $relativity = $relativity ?? 1;
  68          $referenceStyle = $referenceStyle ?? true;
  69  
  70          if (($row < 1) || ($column < 1)) {
  71              return ExcelError::VALUE();
  72          }
  73  
  74          $sheetName = self::sheetName($sheetName);
  75  
  76          if (is_int($referenceStyle)) {
  77              $referenceStyle = (bool) $referenceStyle;
  78          }
  79          if ((!is_bool($referenceStyle)) || $referenceStyle === self::REFERENCE_STYLE_A1) {
  80              return self::formatAsA1($row, $column, $relativity, $sheetName);
  81          }
  82  
  83          return self::formatAsR1C1($row, $column, $relativity, $sheetName);
  84      }
  85  
  86      private static function sheetName(string $sheetName)
  87      {
  88          if ($sheetName > '') {
  89              if (strpos($sheetName, ' ') !== false || strpos($sheetName, '[') !== false) {
  90                  $sheetName = "'{$sheetName}'";
  91              }
  92              $sheetName .= '!';
  93          }
  94  
  95          return $sheetName;
  96      }
  97  
  98      private static function formatAsA1(int $row, int $column, int $relativity, string $sheetName): string
  99      {
 100          $rowRelative = $columnRelative = '$';
 101          if (($relativity == self::ADDRESS_COLUMN_RELATIVE) || ($relativity == self::ADDRESS_RELATIVE)) {
 102              $columnRelative = '';
 103          }
 104          if (($relativity == self::ADDRESS_ROW_RELATIVE) || ($relativity == self::ADDRESS_RELATIVE)) {
 105              $rowRelative = '';
 106          }
 107          $column = Coordinate::stringFromColumnIndex($column);
 108  
 109          return "{$sheetName}{$columnRelative}{$column}{$rowRelative}{$row}";
 110      }
 111  
 112      private static function formatAsR1C1(int $row, int $column, int $relativity, string $sheetName): string
 113      {
 114          if (($relativity == self::ADDRESS_COLUMN_RELATIVE) || ($relativity == self::ADDRESS_RELATIVE)) {
 115              $column = "[{$column}]";
 116          }
 117          if (($relativity == self::ADDRESS_ROW_RELATIVE) || ($relativity == self::ADDRESS_RELATIVE)) {
 118              $row = "[{$row}]";
 119          }
 120          [$rowChar, $colChar] = AddressHelper::getRowAndColumnChars();
 121  
 122          return "{$sheetName}$rowChar{$row}$colChar{$column}";
 123      }
 124  }