[Summary view]

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

   1  <?php
   2  
   3  namespace PhpOffice\PhpSpreadsheet\Writer\Xlsx;
   4  
   5  use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
   6  use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
   7  use PhpOffice\PhpSpreadsheet\DefinedName;
   8  use PhpOffice\PhpSpreadsheet\Shared\XMLWriter;
   9  use PhpOffice\PhpSpreadsheet\Spreadsheet;
  10  use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
  11  
  12  class DefinedNames
  13  {
  14      private $objWriter;
  15  
  16      private $spreadsheet;
  17  
  18      public function __construct(XMLWriter $objWriter, Spreadsheet $spreadsheet)
  19      {
  20          $this->objWriter = $objWriter;
  21          $this->spreadsheet = $spreadsheet;
  22      }
  23  
  24      public function write(): void
  25      {
  26          // Write defined names
  27          $this->objWriter->startElement('definedNames');
  28  
  29          // Named ranges
  30          if (count($this->spreadsheet->getDefinedNames()) > 0) {
  31              // Named ranges
  32              $this->writeNamedRangesAndFormulae();
  33          }
  34  
  35          // Other defined names
  36          $sheetCount = $this->spreadsheet->getSheetCount();
  37          for ($i = 0; $i < $sheetCount; ++$i) {
  38              // NamedRange for autoFilter
  39              $this->writeNamedRangeForAutofilter($this->spreadsheet->getSheet($i), $i);
  40  
  41              // NamedRange for Print_Titles
  42              $this->writeNamedRangeForPrintTitles($this->spreadsheet->getSheet($i), $i);
  43  
  44              // NamedRange for Print_Area
  45              $this->writeNamedRangeForPrintArea($this->spreadsheet->getSheet($i), $i);
  46          }
  47  
  48          $this->objWriter->endElement();
  49      }
  50  
  51      /**
  52       * Write defined names.
  53       */
  54      private function writeNamedRangesAndFormulae(): void
  55      {
  56          // Loop named ranges
  57          $definedNames = $this->spreadsheet->getDefinedNames();
  58          foreach ($definedNames as $definedName) {
  59              $this->writeDefinedName($definedName);
  60          }
  61      }
  62  
  63      /**
  64       * Write Defined Name for named range.
  65       */
  66      private function writeDefinedName(DefinedName $pDefinedName): void
  67      {
  68          // definedName for named range
  69          $this->objWriter->startElement('definedName');
  70          $this->objWriter->writeAttribute('name', $pDefinedName->getName());
  71          if ($pDefinedName->getLocalOnly() && $pDefinedName->getScope() !== null) {
  72              $this->objWriter->writeAttribute('localSheetId', $pDefinedName->getScope()->getParent()->getIndex($pDefinedName->getScope()));
  73          }
  74  
  75          $definedRange = $pDefinedName->getValue();
  76          $splitCount = preg_match_all(
  77              '/' . Calculation::CALCULATION_REGEXP_CELLREF_RELATIVE . '/mui',
  78              $definedRange,
  79              $splitRanges,
  80              PREG_OFFSET_CAPTURE
  81          );
  82  
  83          $lengths = array_map('strlen', array_column($splitRanges[0], 0));
  84          $offsets = array_column($splitRanges[0], 1);
  85  
  86          $worksheets = $splitRanges[2];
  87          $columns = $splitRanges[6];
  88          $rows = $splitRanges[7];
  89  
  90          while ($splitCount > 0) {
  91              --$splitCount;
  92              $length = $lengths[$splitCount];
  93              $offset = $offsets[$splitCount];
  94              $worksheet = $worksheets[$splitCount][0];
  95              $column = $columns[$splitCount][0];
  96              $row = $rows[$splitCount][0];
  97  
  98              $newRange = '';
  99              if (empty($worksheet)) {
 100                  if (($offset === 0) || ($definedRange[$offset - 1] !== ':')) {
 101                      // We should have a worksheet
 102                      $worksheet = $pDefinedName->getWorksheet()->getTitle();
 103                  }
 104              } else {
 105                  $worksheet = str_replace("''", "'", trim($worksheet, "'"));
 106              }
 107              if (!empty($worksheet)) {
 108                  $newRange = "'" . str_replace("'", "''", $worksheet) . "'!";
 109              }
 110  
 111              if (!empty($column)) {
 112                  $newRange .= $column;
 113              }
 114              if (!empty($row)) {
 115                  $newRange .= $row;
 116              }
 117  
 118              $definedRange = substr($definedRange, 0, $offset) . $newRange . substr($definedRange, $offset + $length);
 119          }
 120  
 121          if (substr($definedRange, 0, 1) === '=') {
 122              $definedRange = substr($definedRange, 1);
 123          }
 124  
 125          $this->objWriter->writeRawData($definedRange);
 126  
 127          $this->objWriter->endElement();
 128      }
 129  
 130      /**
 131       * Write Defined Name for autoFilter.
 132       */
 133      private function writeNamedRangeForAutofilter(Worksheet $pSheet, int $pSheetId = 0): void
 134      {
 135          // NamedRange for autoFilter
 136          $autoFilterRange = $pSheet->getAutoFilter()->getRange();
 137          if (!empty($autoFilterRange)) {
 138              $this->objWriter->startElement('definedName');
 139              $this->objWriter->writeAttribute('name', '_xlnm._FilterDatabase');
 140              $this->objWriter->writeAttribute('localSheetId', $pSheetId);
 141              $this->objWriter->writeAttribute('hidden', '1');
 142  
 143              // Create absolute coordinate and write as raw text
 144              $range = Coordinate::splitRange($autoFilterRange);
 145              $range = $range[0];
 146              //    Strip any worksheet ref so we can make the cell ref absolute
 147              [$ws, $range[0]] = Worksheet::extractSheetTitle($range[0], true);
 148  
 149              $range[0] = Coordinate::absoluteCoordinate($range[0]);
 150              $range[1] = Coordinate::absoluteCoordinate($range[1]);
 151              $range = implode(':', $range);
 152  
 153              $this->objWriter->writeRawData('\'' . str_replace("'", "''", $pSheet->getTitle()) . '\'!' . $range);
 154  
 155              $this->objWriter->endElement();
 156          }
 157      }
 158  
 159      /**
 160       * Write Defined Name for PrintTitles.
 161       */
 162      private function writeNamedRangeForPrintTitles(Worksheet $pSheet, int $pSheetId = 0): void
 163      {
 164          // NamedRange for PrintTitles
 165          if ($pSheet->getPageSetup()->isColumnsToRepeatAtLeftSet() || $pSheet->getPageSetup()->isRowsToRepeatAtTopSet()) {
 166              $this->objWriter->startElement('definedName');
 167              $this->objWriter->writeAttribute('name', '_xlnm.Print_Titles');
 168              $this->objWriter->writeAttribute('localSheetId', $pSheetId);
 169  
 170              // Setting string
 171              $settingString = '';
 172  
 173              // Columns to repeat
 174              if ($pSheet->getPageSetup()->isColumnsToRepeatAtLeftSet()) {
 175                  $repeat = $pSheet->getPageSetup()->getColumnsToRepeatAtLeft();
 176  
 177                  $settingString .= '\'' . str_replace("'", "''", $pSheet->getTitle()) . '\'!$' . $repeat[0] . ':$' . $repeat[1];
 178              }
 179  
 180              // Rows to repeat
 181              if ($pSheet->getPageSetup()->isRowsToRepeatAtTopSet()) {
 182                  if ($pSheet->getPageSetup()->isColumnsToRepeatAtLeftSet()) {
 183                      $settingString .= ',';
 184                  }
 185  
 186                  $repeat = $pSheet->getPageSetup()->getRowsToRepeatAtTop();
 187  
 188                  $settingString .= '\'' . str_replace("'", "''", $pSheet->getTitle()) . '\'!$' . $repeat[0] . ':$' . $repeat[1];
 189              }
 190  
 191              $this->objWriter->writeRawData($settingString);
 192  
 193              $this->objWriter->endElement();
 194          }
 195      }
 196  
 197      /**
 198       * Write Defined Name for PrintTitles.
 199       */
 200      private function writeNamedRangeForPrintArea(Worksheet $pSheet, int $pSheetId = 0): void
 201      {
 202          // NamedRange for PrintArea
 203          if ($pSheet->getPageSetup()->isPrintAreaSet()) {
 204              $this->objWriter->startElement('definedName');
 205              $this->objWriter->writeAttribute('name', '_xlnm.Print_Area');
 206              $this->objWriter->writeAttribute('localSheetId', $pSheetId);
 207  
 208              // Print area
 209              $printArea = Coordinate::splitRange($pSheet->getPageSetup()->getPrintArea());
 210  
 211              $chunks = [];
 212              foreach ($printArea as $printAreaRect) {
 213                  $printAreaRect[0] = Coordinate::absoluteReference($printAreaRect[0]);
 214                  $printAreaRect[1] = Coordinate::absoluteReference($printAreaRect[1]);
 215                  $chunks[] = '\'' . str_replace("'", "''", $pSheet->getTitle()) . '\'!' . implode(':', $printAreaRect);
 216              }
 217  
 218              $this->objWriter->writeRawData(implode(',', $chunks));
 219  
 220              $this->objWriter->endElement();
 221          }
 222      }
 223  }