[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 Exception;
   6  use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
   7  use PhpOffice\PhpSpreadsheet\Calculation\Functions;
   8  use PhpOffice\PhpSpreadsheet\Cell\Cell;
   9  use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
  10  
  11  class Indirect
  12  {
  13      /**
  14       * Determine whether cell address is in A1 (true) or R1C1 (false) format.
  15       *
  16       * @param mixed $a1fmt Expect bool Helpers::CELLADDRESS_USE_A1 or CELLADDRESS_USE_R1C1,
  17       *                      but can be provided as numeric which is cast to bool
  18       */
  19      private static function a1Format($a1fmt): bool
  20      {
  21          $a1fmt = Functions::flattenSingleValue($a1fmt);
  22          if ($a1fmt === null) {
  23              return Helpers::CELLADDRESS_USE_A1;
  24          }
  25          if (is_string($a1fmt)) {
  26              throw new Exception(Functions::VALUE());
  27          }
  28  
  29          return (bool) $a1fmt;
  30      }
  31  
  32      /**
  33       * Convert cellAddress to string, verify not null string.
  34       *
  35       * @param array|string $cellAddress
  36       */
  37      private static function validateAddress($cellAddress): string
  38      {
  39          $cellAddress = Functions::flattenSingleValue($cellAddress);
  40          if (!is_string($cellAddress) || !$cellAddress) {
  41              throw new Exception(Functions::REF());
  42          }
  43  
  44          return $cellAddress;
  45      }
  46  
  47      /**
  48       * INDIRECT.
  49       *
  50       * Returns the reference specified by a text string.
  51       * References are immediately evaluated to display their contents.
  52       *
  53       * Excel Function:
  54       *        =INDIRECT(cellAddress, bool) where the bool argument is optional
  55       *
  56       * @param array|string $cellAddress $cellAddress The cell address of the current cell (containing this formula)
  57       * @param mixed $a1fmt Expect bool Helpers::CELLADDRESS_USE_A1 or CELLADDRESS_USE_R1C1,
  58       *                      but can be provided as numeric which is cast to bool
  59       * @param Cell $cell The current cell (containing this formula)
  60       *
  61       * @return array|string An array containing a cell or range of cells, or a string on error
  62       */
  63      public static function INDIRECT($cellAddress, $a1fmt, Cell $cell)
  64      {
  65          try {
  66              $a1 = self::a1Format($a1fmt);
  67              $cellAddress = self::validateAddress($cellAddress);
  68          } catch (Exception $e) {
  69              return $e->getMessage();
  70          }
  71  
  72          [$cellAddress, $worksheet, $sheetName] = Helpers::extractWorksheet($cellAddress, $cell);
  73  
  74          [$cellAddress1, $cellAddress2, $cellAddress] = Helpers::extractCellAddresses($cellAddress, $a1, $cell->getWorkSheet(), $sheetName);
  75  
  76          if (
  77              (!preg_match('/^' . Calculation::CALCULATION_REGEXP_CELLREF . '$/i', $cellAddress1, $matches)) ||
  78              (($cellAddress2 !== null) && (!preg_match('/^' . Calculation::CALCULATION_REGEXP_CELLREF . '$/i', $cellAddress2, $matches)))
  79          ) {
  80              return Functions::REF();
  81          }
  82  
  83          return self::extractRequiredCells($worksheet, $cellAddress);
  84      }
  85  
  86      /**
  87       * Extract range values.
  88       *
  89       * @return mixed Array of values in range if range contains more than one element.
  90       *                  Otherwise, a single value is returned.
  91       */
  92      private static function extractRequiredCells(?Worksheet $worksheet, string $cellAddress)
  93      {
  94          return Calculation::getInstance($worksheet !== null ? $worksheet->getParent() : null)
  95              ->extractCellRange($cellAddress, $worksheet, false);
  96      }
  97  }