[Summary view]

Differences Between: [Versions 311 and 402] [Versions 311 and 403]

   1  <?php
   2  
   3  namespace PhpOffice\PhpSpreadsheet\Writer\Ods;
   4  
   5  use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
   6  use PhpOffice\PhpSpreadsheet\DefinedName;
   7  
   8  class Formula
   9  {
  10      private $definedNames = [];
  11  
  12      /**
  13       * @param DefinedName[] $definedNames
  14       */
  15      public function __construct(array $definedNames)
  16      {
  17          foreach ($definedNames as $definedName) {
  18              $this->definedNames[] = $definedName->getName();
  19          }
  20      }
  21  
  22      public function convertFormula(string $formula, string $worksheetName = ''): string
  23      {
  24          $formula = $this->convertCellReferences($formula, $worksheetName);
  25          $formula = $this->convertDefinedNames($formula);
  26  
  27          if (substr($formula, 0, 1) !== '=') {
  28              $formula = '=' . $formula;
  29          }
  30  
  31          return 'of:' . $formula;
  32      }
  33  
  34      private function convertDefinedNames(string $formula): string
  35      {
  36          $splitCount = preg_match_all(
  37              '/' . Calculation::CALCULATION_REGEXP_DEFINEDNAME . '/mui',
  38              $formula,
  39              $splitRanges,
  40              PREG_OFFSET_CAPTURE
  41          );
  42  
  43          $lengths = array_map('strlen', array_column($splitRanges[0], 0));
  44          $offsets = array_column($splitRanges[0], 1);
  45          $values = array_column($splitRanges[0], 0);
  46  
  47          while ($splitCount > 0) {
  48              --$splitCount;
  49              $length = $lengths[$splitCount];
  50              $offset = $offsets[$splitCount];
  51              $value = $values[$splitCount];
  52  
  53              if (in_array($value, $this->definedNames, true)) {
  54                  $formula = substr($formula, 0, $offset) . '$$' . $value . substr($formula, $offset + $length);
  55              }
  56          }
  57  
  58          return $formula;
  59      }
  60  
  61      private function convertCellReferences(string $formula, string $worksheetName): string
  62      {
  63          $splitCount = preg_match_all(
  64              '/' . Calculation::CALCULATION_REGEXP_CELLREF_RELATIVE . '/mui',
  65              $formula,
  66              $splitRanges,
  67              PREG_OFFSET_CAPTURE
  68          );
  69  
  70          $lengths = array_map('strlen', array_column($splitRanges[0], 0));
  71          $offsets = array_column($splitRanges[0], 1);
  72  
  73          $worksheets = $splitRanges[2];
  74          $columns = $splitRanges[6];
  75          $rows = $splitRanges[7];
  76  
  77          // Replace any commas in the formula with semi-colons for Ods
  78          // If by chance there are commas in worksheet names, then they will be "fixed" again in the loop
  79          //    because we've already extracted worksheet names with our preg_match_all()
  80          $formula = str_replace(',', ';', $formula);
  81          while ($splitCount > 0) {
  82              --$splitCount;
  83              $length = $lengths[$splitCount];
  84              $offset = $offsets[$splitCount];
  85              $worksheet = $worksheets[$splitCount][0];
  86              $column = $columns[$splitCount][0];
  87              $row = $rows[$splitCount][0];
  88  
  89              $newRange = '';
  90              if (empty($worksheet)) {
  91                  if (($offset === 0) || ($formula[$offset - 1] !== ':')) {
  92                      // We need a worksheet
  93                      $worksheet = $worksheetName;
  94                  }
  95              } else {
  96                  $worksheet = str_replace("''", "'", trim($worksheet, "'"));
  97              }
  98              if (!empty($worksheet)) {
  99                  $newRange = "['" . str_replace("'", "''", $worksheet) . "'";
 100              } elseif (substr($formula, $offset - 1, 1) !== ':') {
 101                  $newRange = '[';
 102              }
 103              $newRange .= '.';
 104  
 105              if (!empty($column)) {
 106                  $newRange .= $column;
 107              }
 108              if (!empty($row)) {
 109                  $newRange .= $row;
 110              }
 111              // close the wrapping [] unless this is the first part of a range
 112              $newRange .= substr($formula, $offset + $length, 1) !== ':' ? ']' : '';
 113  
 114              $formula = substr($formula, 0, $offset) . $newRange . substr($formula, $offset + $length);
 115          }
 116  
 117          return $formula;
 118      }
 119  }