[Summary view]

Differences Between: [Versions 311 and 402] [Versions 400 and 402] [Versions 401 and 402]

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