[Summary view]

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

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