[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 Exception;
   6  use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
   7  use PhpOffice\PhpSpreadsheet\Calculation\Functions;
   8  use PhpOffice\PhpSpreadsheet\Calculation\Information\ExcelError;
   9  use PhpOffice\PhpSpreadsheet\Cell\Cell;
  10  use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
  11  use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
  12  
  13  class Indirect
  14  {
  15      /**
  16       * Determine whether cell address is in A1 (true) or R1C1 (false) format.
  17       *
  18       * @param mixed $a1fmt Expect bool Helpers::CELLADDRESS_USE_A1 or CELLADDRESS_USE_R1C1,
  19       *                      but can be provided as numeric which is cast to bool
  20       */
  21      private static function a1Format($a1fmt): bool
  22      {
  23          $a1fmt = Functions::flattenSingleValue($a1fmt);
  24          if ($a1fmt === null) {
  25              return Helpers::CELLADDRESS_USE_A1;
  26          }
  27          if (is_string($a1fmt)) {
  28              throw new Exception(ExcelError::VALUE());
  29          }
  30  
  31          return (bool) $a1fmt;
  32      }
  33  
  34      /**
  35       * Convert cellAddress to string, verify not null string.
  36       *
  37       * @param array|string $cellAddress
  38       */
  39      private static function validateAddress($cellAddress): string
  40      {
  41          $cellAddress = Functions::flattenSingleValue($cellAddress);
  42          if (!is_string($cellAddress) || !$cellAddress) {
  43              throw new Exception(ExcelError::REF());
  44          }
  45  
  46          return $cellAddress;
  47      }
  48  
  49      /**
  50       * INDIRECT.
  51       *
  52       * Returns the reference specified by a text string.
  53       * References are immediately evaluated to display their contents.
  54       *
  55       * Excel Function:
  56       *        =INDIRECT(cellAddress, bool) where the bool argument is optional
  57       *
  58       * @param array|string $cellAddress $cellAddress The cell address of the current cell (containing this formula)
  59       * @param mixed $a1fmt Expect bool Helpers::CELLADDRESS_USE_A1 or CELLADDRESS_USE_R1C1,
  60       *                      but can be provided as numeric which is cast to bool
  61       * @param Cell $cell The current cell (containing this formula)
  62       *
  63       * @return array|string An array containing a cell or range of cells, or a string on error
  64       */
  65      public static function INDIRECT($cellAddress, $a1fmt, Cell $cell)
  66      {
  67          [$baseCol, $baseRow] = Coordinate::indexesFromString($cell->getCoordinate());
  68  
  69          try {
  70              $a1 = self::a1Format($a1fmt);
  71              $cellAddress = self::validateAddress($cellAddress);
  72          } catch (Exception $e) {
  73              return $e->getMessage();
  74          }
  75  
  76          [$cellAddress, $worksheet, $sheetName] = Helpers::extractWorksheet($cellAddress, $cell);
  77  
  78          if (preg_match('/^' . Calculation::CALCULATION_REGEXP_COLUMNRANGE_RELATIVE . '$/miu', $cellAddress, $matches)) {
  79              $cellAddress = self::handleRowColumnRanges($worksheet, ...explode(':', $cellAddress));
  80          } elseif (preg_match('/^' . Calculation::CALCULATION_REGEXP_ROWRANGE_RELATIVE . '$/miu', $cellAddress, $matches)) {
  81              $cellAddress = self::handleRowColumnRanges($worksheet, ...explode(':', $cellAddress));
  82          }
  83  
  84          try {
  85              [$cellAddress1, $cellAddress2, $cellAddress] = Helpers::extractCellAddresses($cellAddress, $a1, $cell->getWorkSheet(), $sheetName, $baseRow, $baseCol);
  86          } catch (Exception $e) {
  87              return ExcelError::REF();
  88          }
  89  
  90          if (
  91              (!preg_match('/^' . Calculation::CALCULATION_REGEXP_CELLREF . '$/miu', $cellAddress1, $matches)) ||
  92              (($cellAddress2 !== null) && (!preg_match('/^' . Calculation::CALCULATION_REGEXP_CELLREF . '$/miu', $cellAddress2, $matches)))
  93          ) {
  94              return ExcelError::REF();
  95          }
  96  
  97          return self::extractRequiredCells($worksheet, $cellAddress);
  98      }
  99  
 100      /**
 101       * Extract range values.
 102       *
 103       * @return mixed Array of values in range if range contains more than one element.
 104       *                  Otherwise, a single value is returned.
 105       */
 106      private static function extractRequiredCells(?Worksheet $worksheet, string $cellAddress)
 107      {
 108          return Calculation::getInstance($worksheet !== null ? $worksheet->getParent() : null)
 109              ->extractCellRange($cellAddress, $worksheet, false);
 110      }
 111  
 112      private static function handleRowColumnRanges(?Worksheet $worksheet, string $start, string $end): string
 113      {
 114          // Being lazy, we're only checking a single row/column to get the max
 115          if (ctype_digit($start) && $start <= 1048576) {
 116              // Max 16,384 columns for Excel2007
 117              $endColRef = ($worksheet !== null) ? $worksheet->getHighestDataColumn((int) $start) : 'XFD';
 118  
 119              return "A{$start}:{$endColRef}{$end}";
 120          } elseif (ctype_alpha($start) && strlen($start) <= 3) {
 121              // Max 1,048,576 rows for Excel2007
 122              $endRowRef = ($worksheet !== null) ? $worksheet->getHighestDataRow($start) : 1048576;
 123  
 124              return "{$start}1:{$end}{$endRowRef}";
 125          }
 126  
 127          return "{$start}:{$end}";
 128      }
 129  }