[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\Cell\AddressHelper;
   6  use PhpOffice\PhpSpreadsheet\Cell\Cell;
   7  use PhpOffice\PhpSpreadsheet\DefinedName;
   8  use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
   9  
  10  class Helpers
  11  {
  12      public const CELLADDRESS_USE_A1 = true;
  13  
  14      public const CELLADDRESS_USE_R1C1 = false;
  15  
  16      private static function convertR1C1(string &$cellAddress1, ?string &$cellAddress2, bool $a1, ?int $baseRow = null, ?int $baseCol = null): string
  17      {
  18          if ($a1 === self::CELLADDRESS_USE_R1C1) {
  19              $cellAddress1 = AddressHelper::convertToA1($cellAddress1, $baseRow ?? 1, $baseCol ?? 1);
  20              if ($cellAddress2) {
  21                  $cellAddress2 = AddressHelper::convertToA1($cellAddress2, $baseRow ?? 1, $baseCol ?? 1);
  22              }
  23          }
  24  
  25          return $cellAddress1 . ($cellAddress2 ? ":$cellAddress2" : '');
  26      }
  27  
  28      private static function adjustSheetTitle(string &$sheetTitle, ?string $value): void
  29      {
  30          if ($sheetTitle) {
  31              $sheetTitle .= '!';
  32              if (stripos($value ?? '', $sheetTitle) === 0) {
  33                  $sheetTitle = '';
  34              }
  35          }
  36      }
  37  
  38      public static function extractCellAddresses(string $cellAddress, bool $a1, Worksheet $sheet, string $sheetName = '', ?int $baseRow = null, ?int $baseCol = null): array
  39      {
  40          $cellAddress1 = $cellAddress;
  41          $cellAddress2 = null;
  42          $namedRange = DefinedName::resolveName($cellAddress1, $sheet, $sheetName);
  43          if ($namedRange !== null) {
  44              $workSheet = $namedRange->getWorkSheet();
  45              $sheetTitle = ($workSheet === null) ? '' : $workSheet->getTitle();
  46              $value = (string) preg_replace('/^=/', '', $namedRange->getValue());
  47              self::adjustSheetTitle($sheetTitle, $value);
  48              $cellAddress1 = $sheetTitle . $value;
  49              $cellAddress = $cellAddress1;
  50              $a1 = self::CELLADDRESS_USE_A1;
  51          }
  52          if (strpos($cellAddress, ':') !== false) {
  53              [$cellAddress1, $cellAddress2] = explode(':', $cellAddress);
  54          }
  55          $cellAddress = self::convertR1C1($cellAddress1, $cellAddress2, $a1, $baseRow, $baseCol);
  56  
  57          return [$cellAddress1, $cellAddress2, $cellAddress];
  58      }
  59  
  60      public static function extractWorksheet(string $cellAddress, Cell $cell): array
  61      {
  62          $sheetName = '';
  63          if (strpos($cellAddress, '!') !== false) {
  64              [$sheetName, $cellAddress] = Worksheet::extractSheetTitle($cellAddress, true);
  65              $sheetName = trim($sheetName, "'");
  66          }
  67  
  68          $worksheet = ($sheetName !== '')
  69              ? $cell->getWorksheet()->getParent()->getSheetByName($sheetName)
  70              : $cell->getWorksheet();
  71  
  72          return [$cellAddress, $worksheet, $sheetName];
  73      }
  74  }