[Summary view]

Differences Between: [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  use PhpOffice\PhpSpreadsheet\Shared\XMLWriter;
   8  use PhpOffice\PhpSpreadsheet\Spreadsheet;
   9  use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
  10  
  11  class NamedExpressions
  12  {
  13      /** @var XMLWriter */
  14      private $objWriter;
  15  
  16      /** @var Spreadsheet */
  17      private $spreadsheet;
  18  
  19      /** @var Formula */
  20      private $formulaConvertor;
  21  
  22      public function __construct(XMLWriter $objWriter, Spreadsheet $spreadsheet, Formula $formulaConvertor)
  23      {
  24          $this->objWriter = $objWriter;
  25          $this->spreadsheet = $spreadsheet;
  26          $this->formulaConvertor = $formulaConvertor;
  27      }
  28  
  29      public function write(): string
  30      {
  31          $this->objWriter->startElement('table:named-expressions');
  32          $this->writeExpressions();
  33          $this->objWriter->endElement();
  34  
  35          return '';
  36      }
  37  
  38      private function writeExpressions(): void
  39      {
  40          $definedNames = $this->spreadsheet->getDefinedNames();
  41  
  42          foreach ($definedNames as $definedName) {
  43              if ($definedName->isFormula()) {
  44                  $this->objWriter->startElement('table:named-expression');
  45                  $this->writeNamedFormula($definedName, $this->spreadsheet->getActiveSheet());
  46              } else {
  47                  $this->objWriter->startElement('table:named-range');
  48                  $this->writeNamedRange($definedName);
  49              }
  50  
  51              $this->objWriter->endElement();
  52          }
  53      }
  54  
  55      private function writeNamedFormula(DefinedName $definedName, Worksheet $defaultWorksheet): void
  56      {
  57          $title = ($definedName->getWorksheet() !== null) ? $definedName->getWorksheet()->getTitle() : $defaultWorksheet->getTitle();
  58          $this->objWriter->writeAttribute('table:name', $definedName->getName());
  59          $this->objWriter->writeAttribute(
  60              'table:expression',
  61              $this->formulaConvertor->convertFormula($definedName->getValue(), $title)
  62          );
  63          $this->objWriter->writeAttribute('table:base-cell-address', $this->convertAddress(
  64              $definedName,
  65              "'" . $title . "'!\$A\$1"
  66          ));
  67      }
  68  
  69      private function writeNamedRange(DefinedName $definedName): void
  70      {
  71          $baseCell = '$A$1';
  72          $ws = $definedName->getWorksheet();
  73          if ($ws !== null) {
  74              $baseCell = "'" . $ws->getTitle() . "'!$baseCell";
  75          }
  76          $this->objWriter->writeAttribute('table:name', $definedName->getName());
  77          $this->objWriter->writeAttribute('table:base-cell-address', $this->convertAddress(
  78              $definedName,
  79              $baseCell
  80          ));
  81          $this->objWriter->writeAttribute('table:cell-range-address', $this->convertAddress($definedName, $definedName->getValue()));
  82      }
  83  
  84      private function convertAddress(DefinedName $definedName, string $address): string
  85      {
  86          $splitCount = preg_match_all(
  87              '/' . Calculation::CALCULATION_REGEXP_CELLREF_RELATIVE . '/mui',
  88              $address,
  89              $splitRanges,
  90              PREG_OFFSET_CAPTURE
  91          );
  92  
  93          $lengths = array_map('strlen', array_column($splitRanges[0], 0));
  94          $offsets = array_column($splitRanges[0], 1);
  95  
  96          $worksheets = $splitRanges[2];
  97          $columns = $splitRanges[6];
  98          $rows = $splitRanges[7];
  99  
 100          while ($splitCount > 0) {
 101              --$splitCount;
 102              $length = $lengths[$splitCount];
 103              $offset = $offsets[$splitCount];
 104              $worksheet = $worksheets[$splitCount][0];
 105              $column = $columns[$splitCount][0];
 106              $row = $rows[$splitCount][0];
 107  
 108              $newRange = '';
 109              if (empty($worksheet)) {
 110                  if (($offset === 0) || ($address[$offset - 1] !== ':')) {
 111                      // We need a worksheet
 112                      $ws = $definedName->getWorksheet();
 113                      if ($ws !== null) {
 114                          $worksheet = $ws->getTitle();
 115                      }
 116                  }
 117              } else {
 118                  $worksheet = str_replace("''", "'", trim($worksheet, "'"));
 119              }
 120              if (!empty($worksheet)) {
 121                  $newRange = "'" . str_replace("'", "''", $worksheet) . "'.";
 122              }
 123  
 124              if (!empty($column)) {
 125                  $newRange .= $column;
 126              }
 127              if (!empty($row)) {
 128                  $newRange .= $row;
 129              }
 130  
 131              $address = substr($address, 0, $offset) . $newRange . substr($address, $offset + $length);
 132          }
 133  
 134          if (substr($address, 0, 1) === '=') {
 135              $address = substr($address, 1);
 136          }
 137  
 138          return $address;
 139      }
 140  }