[Summary view]

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

   1  <?php
   2  
   3  namespace PhpOffice\PhpSpreadsheet\Calculation\LookupRef;
   4  
   5  use PhpOffice\PhpSpreadsheet\Calculation\Functions;
   6  use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
   7  
   8  class Address
   9  {
  10      public const ADDRESS_ABSOLUTE = 1;
  11      public const ADDRESS_COLUMN_RELATIVE = 2;
  12      public const ADDRESS_ROW_RELATIVE = 3;
  13      public const ADDRESS_RELATIVE = 4;
  14  
  15      public const REFERENCE_STYLE_A1 = true;
  16      public const REFERENCE_STYLE_R1C1 = false;
  17  
  18      /**
  19       * ADDRESS.
  20       *
  21       * Creates a cell address as text, given specified row and column numbers.
  22       *
  23       * Excel Function:
  24       *        =ADDRESS(row, column, [relativity], [referenceStyle], [sheetText])
  25       *
  26       * @param mixed $row Row number (integer) to use in the cell reference
  27       * @param mixed $column Column number (integer) to use in the cell reference
  28       * @param mixed $relativity Integer flag indicating the type of reference to return
  29       *                             1 or omitted    Absolute
  30       *                             2               Absolute row; relative column
  31       *                             3               Relative row; absolute column
  32       *                             4               Relative
  33       * @param mixed $referenceStyle A logical (boolean) value that specifies the A1 or R1C1 reference style.
  34       *                                TRUE or omitted    ADDRESS returns an A1-style reference
  35       *                                FALSE              ADDRESS returns an R1C1-style reference
  36       * @param mixed $sheetName Optional Name of worksheet to use
  37       *
  38       * @return string
  39       */
  40      public static function cell($row, $column, $relativity = 1, $referenceStyle = true, $sheetName = '')
  41      {
  42          $row = Functions::flattenSingleValue($row);
  43          $column = Functions::flattenSingleValue($column);
  44          $relativity = ($relativity === null) ? 1 : Functions::flattenSingleValue($relativity);
  45          $referenceStyle = ($referenceStyle === null) ? true : Functions::flattenSingleValue($referenceStyle);
  46          $sheetName = Functions::flattenSingleValue($sheetName);
  47  
  48          if (($row < 1) || ($column < 1)) {
  49              return Functions::VALUE();
  50          }
  51  
  52          $sheetName = self::sheetName($sheetName);
  53  
  54          if ((!is_bool($referenceStyle)) || $referenceStyle === self::REFERENCE_STYLE_A1) {
  55              return self::formatAsA1($row, $column, $relativity, $sheetName);
  56          }
  57  
  58          return self::formatAsR1C1($row, $column, $relativity, $sheetName);
  59      }
  60  
  61      private static function sheetName(string $sheetName)
  62      {
  63          if ($sheetName > '') {
  64              if (strpos($sheetName, ' ') !== false || strpos($sheetName, '[') !== false) {
  65                  $sheetName = "'{$sheetName}'";
  66              }
  67              $sheetName .= '!';
  68          }
  69  
  70          return $sheetName;
  71      }
  72  
  73      private static function formatAsA1(int $row, int $column, int $relativity, string $sheetName): string
  74      {
  75          $rowRelative = $columnRelative = '$';
  76          if (($relativity == self::ADDRESS_COLUMN_RELATIVE) || ($relativity == self::ADDRESS_RELATIVE)) {
  77              $columnRelative = '';
  78          }
  79          if (($relativity == self::ADDRESS_ROW_RELATIVE) || ($relativity == self::ADDRESS_RELATIVE)) {
  80              $rowRelative = '';
  81          }
  82          $column = Coordinate::stringFromColumnIndex($column);
  83  
  84          return "{$sheetName}{$columnRelative}{$column}{$rowRelative}{$row}";
  85      }
  86  
  87      private static function formatAsR1C1(int $row, int $column, int $relativity, string $sheetName): string
  88      {
  89          if (($relativity == self::ADDRESS_COLUMN_RELATIVE) || ($relativity == self::ADDRESS_RELATIVE)) {
  90              $column = "[{$column}]";
  91          }
  92          if (($relativity == self::ADDRESS_ROW_RELATIVE) || ($relativity == self::ADDRESS_RELATIVE)) {
  93              $row = "[{$row}]";
  94          }
  95  
  96          return "{$sheetName}R{$row}C{$column}";
  97      }
  98  }