[Summary view]

   1  <?php
   2  
   3  namespace PhpOffice\PhpSpreadsheet\Reader\Ods;
   4  
   5  use DOMElement;
   6  use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
   7  use PhpOffice\PhpSpreadsheet\Spreadsheet;
   8  
   9  abstract class BaseReader
  10  {
  11      /**
  12       * @var Spreadsheet
  13       */
  14      protected $spreadsheet;
  15  
  16      /**
  17       * @var string
  18       */
  19      protected $tableNs;
  20  
  21      public function __construct(Spreadsheet $spreadsheet, string $tableNs)
  22      {
  23          $this->spreadsheet = $spreadsheet;
  24          $this->tableNs = $tableNs;
  25      }
  26  
  27      abstract public function read(DOMElement $workbookData): void;
  28  
  29      protected function convertToExcelAddressValue(string $openOfficeAddress): string
  30      {
  31          $excelAddress = $openOfficeAddress;
  32  
  33          // Cell range 3-d reference
  34          // As we don't support 3-d ranges, we're just going to take a quick and dirty approach
  35          //  and assume that the second worksheet reference is the same as the first
  36          $excelAddress = preg_replace('/\$?([^\.]+)\.([^\.]+):\$?([^\.]+)\.([^\.]+)/miu', '$1!$2:$4', $excelAddress);
  37          // Cell range reference in another sheet
  38          $excelAddress = preg_replace('/\$?([^\.]+)\.([^\.]+):\.([^\.]+)/miu', '$1!$2:$3', $excelAddress ?? '');
  39          // Cell reference in another sheet
  40          $excelAddress = preg_replace('/\$?([^\.]+)\.([^\.]+)/miu', '$1!$2', $excelAddress ?? '');
  41          // Cell range reference
  42          $excelAddress = preg_replace('/\.([^\.]+):\.([^\.]+)/miu', '$1:$2', $excelAddress ?? '');
  43          // Simple cell reference
  44          $excelAddress = preg_replace('/\.([^\.]+)/miu', '$1', $excelAddress ?? '');
  45  
  46          return $excelAddress ?? '';
  47      }
  48  
  49      protected function convertToExcelFormulaValue(string $openOfficeFormula): string
  50      {
  51          $temp = explode('"', $openOfficeFormula);
  52          $tKey = false;
  53          foreach ($temp as &$value) {
  54              // @var string $value
  55              // Only replace in alternate array entries (i.e. non-quoted blocks)
  56              if ($tKey = !$tKey) {
  57                  // Cell range reference in another sheet
  58                  $value = preg_replace('/\[\$?([^\.]+)\.([^\.]+):\.([^\.]+)\]/miu', '$1!$2:$3', $value);
  59                  // Cell reference in another sheet
  60                  $value = preg_replace('/\[\$?([^\.]+)\.([^\.]+)\]/miu', '$1!$2', $value ?? '');
  61                  // Cell range reference
  62                  $value = preg_replace('/\[\.([^\.]+):\.([^\.]+)\]/miu', '$1:$2', $value ?? '');
  63                  // Simple cell reference
  64                  $value = preg_replace('/\[\.([^\.]+)\]/miu', '$1', $value ?? '');
  65                  // Convert references to defined names/formulae
  66                  $value = str_replace('$$', '', $value ?? '');
  67  
  68                  $value = Calculation::translateSeparator(';', ',', $value, $inBraces);
  69              }
  70          }
  71  
  72          // Then rebuild the formula string
  73          $excelFormula = implode('"', $temp);
  74  
  75          return $excelFormula;
  76      }
  77  }