[Summary view]

Differences Between: [Versions 311 and 403] [Versions 400 and 403] [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 as ActualWorksheet;
  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()->getParentOrThrow()->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(ActualWorksheet $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]] = ActualWorksheet::extractSheetTitle($range[0], true);
 116  
 117              $range[0] = Coordinate::absoluteCoordinate($range[0]);
 118              if (count($range) > 1) {
 119                  $range[1] = Coordinate::absoluteCoordinate($range[1]);
 120              }
 121              $range = implode(':', $range);
 122  
 123              $this->objWriter->writeRawData('\'' . str_replace("'", "''", $worksheet->getTitle()) . '\'!' . $range);
 124  
 125              $this->objWriter->endElement();
 126          }
 127      }
 128  
 129      /**
 130       * Write Defined Name for PrintTitles.
 131       */
 132      private function writeNamedRangeForPrintTitles(ActualWorksheet $worksheet, int $worksheetId = 0): void
 133      {
 134          // NamedRange for PrintTitles
 135          if ($worksheet->getPageSetup()->isColumnsToRepeatAtLeftSet() || $worksheet->getPageSetup()->isRowsToRepeatAtTopSet()) {
 136              $this->objWriter->startElement('definedName');
 137              $this->objWriter->writeAttribute('name', '_xlnm.Print_Titles');
 138              $this->objWriter->writeAttribute('localSheetId', "$worksheetId");
 139  
 140              // Setting string
 141              $settingString = '';
 142  
 143              // Columns to repeat
 144              if ($worksheet->getPageSetup()->isColumnsToRepeatAtLeftSet()) {
 145                  $repeat = $worksheet->getPageSetup()->getColumnsToRepeatAtLeft();
 146  
 147                  $settingString .= '\'' . str_replace("'", "''", $worksheet->getTitle()) . '\'!$' . $repeat[0] . ':$' . $repeat[1];
 148              }
 149  
 150              // Rows to repeat
 151              if ($worksheet->getPageSetup()->isRowsToRepeatAtTopSet()) {
 152                  if ($worksheet->getPageSetup()->isColumnsToRepeatAtLeftSet()) {
 153                      $settingString .= ',';
 154                  }
 155  
 156                  $repeat = $worksheet->getPageSetup()->getRowsToRepeatAtTop();
 157  
 158                  $settingString .= '\'' . str_replace("'", "''", $worksheet->getTitle()) . '\'!$' . $repeat[0] . ':$' . $repeat[1];
 159              }
 160  
 161              $this->objWriter->writeRawData($settingString);
 162  
 163              $this->objWriter->endElement();
 164          }
 165      }
 166  
 167      /**
 168       * Write Defined Name for PrintTitles.
 169       */
 170      private function writeNamedRangeForPrintArea(ActualWorksheet $worksheet, int $worksheetId = 0): void
 171      {
 172          // NamedRange for PrintArea
 173          if ($worksheet->getPageSetup()->isPrintAreaSet()) {
 174              $this->objWriter->startElement('definedName');
 175              $this->objWriter->writeAttribute('name', '_xlnm.Print_Area');
 176              $this->objWriter->writeAttribute('localSheetId', "$worksheetId");
 177  
 178              // Print area
 179              $printArea = Coordinate::splitRange($worksheet->getPageSetup()->getPrintArea());
 180  
 181              $chunks = [];
 182              foreach ($printArea as $printAreaRect) {
 183                  $printAreaRect[0] = Coordinate::absoluteReference($printAreaRect[0]);
 184                  $printAreaRect[1] = Coordinate::absoluteReference($printAreaRect[1]);
 185                  $chunks[] = '\'' . str_replace("'", "''", $worksheet->getTitle()) . '\'!' . implode(':', $printAreaRect);
 186              }
 187  
 188              $this->objWriter->writeRawData(implode(',', $chunks));
 189  
 190              $this->objWriter->endElement();
 191          }
 192      }
 193  
 194      private function getDefinedRange(DefinedName $definedName): string
 195      {
 196          $definedRange = $definedName->getValue();
 197          $splitCount = preg_match_all(
 198              '/' . Calculation::CALCULATION_REGEXP_CELLREF_RELATIVE . '/mui',
 199              $definedRange,
 200              $splitRanges,
 201              PREG_OFFSET_CAPTURE
 202          );
 203  
 204          $lengths = array_map('strlen', array_column($splitRanges[0], 0));
 205          $offsets = array_column($splitRanges[0], 1);
 206  
 207          $worksheets = $splitRanges[2];
 208          $columns = $splitRanges[6];
 209          $rows = $splitRanges[7];
 210  
 211          while ($splitCount > 0) {
 212              --$splitCount;
 213              $length = $lengths[$splitCount];
 214              $offset = $offsets[$splitCount];
 215              $worksheet = $worksheets[$splitCount][0];
 216              $column = $columns[$splitCount][0];
 217              $row = $rows[$splitCount][0];
 218  
 219              $newRange = '';
 220              if (empty($worksheet)) {
 221                  if (($offset === 0) || ($definedRange[$offset - 1] !== ':')) {
 222                      // We should have a worksheet
 223                      $ws = $definedName->getWorksheet();
 224                      $worksheet = ($ws === null) ? null : $ws->getTitle();
 225                  }
 226              } else {
 227                  $worksheet = str_replace("''", "'", trim($worksheet, "'"));
 228              }
 229  
 230              if (!empty($worksheet)) {
 231                  $newRange = "'" . str_replace("'", "''", $worksheet) . "'!";
 232              }
 233              $newRange = "{$newRange}{$column}{$row}";
 234  
 235              $definedRange = substr($definedRange, 0, $offset) . $newRange . substr($definedRange, $offset + $length);
 236          }
 237  
 238          if (substr($definedRange, 0, 1) === '=') {
 239              $definedRange = substr($definedRange, 1);
 240          }
 241  
 242          return $definedRange;
 243      }
 244  }