[Summary view]

Differences Between: [Versions 400 and 401]

   1  <?php
   2  
   3  namespace PhpOffice\PhpSpreadsheet\Calculation\LookupRef;
   4  
   5  use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
   6  use PhpOffice\PhpSpreadsheet\Calculation\Information\ExcelError;
   7  use PhpOffice\PhpSpreadsheet\Cell\Cell;
   8  use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
   9  use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
  10  
  11  class RowColumnInformation
  12  {
  13      /**
  14       * Test if cellAddress is null or whitespace string.
  15       *
  16       * @param null|array|string $cellAddress A reference to a range of cells
  17       */
  18      private static function cellAddressNullOrWhitespace($cellAddress): bool
  19      {
  20          return $cellAddress === null || (!is_array($cellAddress) && trim($cellAddress) === '');
  21      }
  22  
  23      private static function cellColumn(?Cell $cell): int
  24      {
  25          return ($cell !== null) ? (int) Coordinate::columnIndexFromString($cell->getColumn()) : 1;
  26      }
  27  
  28      /**
  29       * COLUMN.
  30       *
  31       * Returns the column number of the given cell reference
  32       *     If the cell reference is a range of cells, COLUMN returns the column numbers of each column
  33       *        in the reference as a horizontal array.
  34       *     If cell reference is omitted, and the function is being called through the calculation engine,
  35       *        then it is assumed to be the reference of the cell in which the COLUMN function appears;
  36       *        otherwise this function returns 1.
  37       *
  38       * Excel Function:
  39       *        =COLUMN([cellAddress])
  40       *
  41       * @param null|array|string $cellAddress A reference to a range of cells for which you want the column numbers
  42       *
  43       * @return int|int[]
  44       */
  45      public static function COLUMN($cellAddress = null, ?Cell $cell = null)
  46      {
  47          if (self::cellAddressNullOrWhitespace($cellAddress)) {
  48              return self::cellColumn($cell);
  49          }
  50  
  51          if (is_array($cellAddress)) {
  52              foreach ($cellAddress as $columnKey => $value) {
  53                  $columnKey = (string) preg_replace('/[^a-z]/i', '', $columnKey);
  54  
  55                  return (int) Coordinate::columnIndexFromString($columnKey);
  56              }
  57  
  58              return self::cellColumn($cell);
  59          }
  60  
  61          $cellAddress = $cellAddress ?? '';
  62          if ($cell != null) {
  63              [,, $sheetName] = Helpers::extractWorksheet($cellAddress, $cell);
  64              [,, $cellAddress] = Helpers::extractCellAddresses($cellAddress, true, $cell->getWorksheet(), $sheetName);
  65          }
  66          [, $cellAddress] = Worksheet::extractSheetTitle($cellAddress, true);
  67          if (strpos($cellAddress, ':') !== false) {
  68              [$startAddress, $endAddress] = explode(':', $cellAddress);
  69              $startAddress = (string) preg_replace('/[^a-z]/i', '', $startAddress);
  70              $endAddress = (string) preg_replace('/[^a-z]/i', '', $endAddress);
  71  
  72              return range(
  73                  (int) Coordinate::columnIndexFromString($startAddress),
  74                  (int) Coordinate::columnIndexFromString($endAddress)
  75              );
  76          }
  77  
  78          $cellAddress = (string) preg_replace('/[^a-z]/i', '', $cellAddress);
  79  
  80          return (int) Coordinate::columnIndexFromString($cellAddress);
  81      }
  82  
  83      /**
  84       * COLUMNS.
  85       *
  86       * Returns the number of columns in an array or reference.
  87       *
  88       * Excel Function:
  89       *        =COLUMNS(cellAddress)
  90       *
  91       * @param null|array|string $cellAddress An array or array formula, or a reference to a range of cells
  92       *                                          for which you want the number of columns
  93       *
  94       * @return int|string The number of columns in cellAddress, or a string if arguments are invalid
  95       */
  96      public static function COLUMNS($cellAddress = null)
  97      {
  98          if (self::cellAddressNullOrWhitespace($cellAddress)) {
  99              return 1;
 100          }
 101          if (!is_array($cellAddress)) {
 102              return ExcelError::VALUE();
 103          }
 104  
 105          reset($cellAddress);
 106          $isMatrix = (is_numeric(key($cellAddress)));
 107          [$columns, $rows] = Calculation::getMatrixDimensions($cellAddress);
 108  
 109          if ($isMatrix) {
 110              return $rows;
 111          }
 112  
 113          return $columns;
 114      }
 115  
 116      private static function cellRow(?Cell $cell): int
 117      {
 118          return ($cell !== null) ? $cell->getRow() : 1;
 119      }
 120  
 121      /**
 122       * ROW.
 123       *
 124       * Returns the row number of the given cell reference
 125       *     If the cell reference is a range of cells, ROW returns the row numbers of each row in the reference
 126       *        as a vertical array.
 127       *     If cell reference is omitted, and the function is being called through the calculation engine,
 128       *        then it is assumed to be the reference of the cell in which the ROW function appears;
 129       *        otherwise this function returns 1.
 130       *
 131       * Excel Function:
 132       *        =ROW([cellAddress])
 133       *
 134       * @param null|array|string $cellAddress A reference to a range of cells for which you want the row numbers
 135       *
 136       * @return int|mixed[]|string
 137       */
 138      public static function ROW($cellAddress = null, ?Cell $cell = null)
 139      {
 140          if (self::cellAddressNullOrWhitespace($cellAddress)) {
 141              return self::cellRow($cell);
 142          }
 143  
 144          if (is_array($cellAddress)) {
 145              foreach ($cellAddress as $rowKey => $rowValue) {
 146                  foreach ($rowValue as $columnKey => $cellValue) {
 147                      return (int) preg_replace('/\D/', '', $rowKey);
 148                  }
 149              }
 150  
 151              return self::cellRow($cell);
 152          }
 153  
 154          $cellAddress = $cellAddress ?? '';
 155          if ($cell !== null) {
 156              [,, $sheetName] = Helpers::extractWorksheet($cellAddress, $cell);
 157              [,, $cellAddress] = Helpers::extractCellAddresses($cellAddress, true, $cell->getWorksheet(), $sheetName);
 158          }
 159          [, $cellAddress] = Worksheet::extractSheetTitle($cellAddress, true);
 160          if (strpos($cellAddress, ':') !== false) {
 161              [$startAddress, $endAddress] = explode(':', $cellAddress);
 162              $startAddress = (string) preg_replace('/\D/', '', $startAddress);
 163              $endAddress = (string) preg_replace('/\D/', '', $endAddress);
 164  
 165              return array_map(
 166                  function ($value) {
 167                      return [$value];
 168                  },
 169                  range($startAddress, $endAddress)
 170              );
 171          }
 172          [$cellAddress] = explode(':', $cellAddress);
 173  
 174          return (int) preg_replace('/\D/', '', $cellAddress);
 175      }
 176  
 177      /**
 178       * ROWS.
 179       *
 180       * Returns the number of rows in an array or reference.
 181       *
 182       * Excel Function:
 183       *        =ROWS(cellAddress)
 184       *
 185       * @param null|array|string $cellAddress An array or array formula, or a reference to a range of cells
 186       *                                          for which you want the number of rows
 187       *
 188       * @return int|string The number of rows in cellAddress, or a string if arguments are invalid
 189       */
 190      public static function ROWS($cellAddress = null)
 191      {
 192          if (self::cellAddressNullOrWhitespace($cellAddress)) {
 193              return 1;
 194          }
 195          if (!is_array($cellAddress)) {
 196              return ExcelError::VALUE();
 197          }
 198  
 199          reset($cellAddress);
 200          $isMatrix = (is_numeric(key($cellAddress)));
 201          [$columns, $rows] = Calculation::getMatrixDimensions($cellAddress);
 202  
 203          if ($isMatrix) {
 204              return $columns;
 205          }
 206  
 207          return $rows;
 208      }
 209  }