[Summary view]

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

   1  <?php
   2  
   3  namespace PhpOffice\PhpSpreadsheet\Writer\Xlsx;
   4  
   5  use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
   6  use PhpOffice\PhpSpreadsheet\NamedRange;
   7  use PhpOffice\PhpSpreadsheet\Shared\Date;
   8  use PhpOffice\PhpSpreadsheet\Shared\XMLWriter;
   9  use PhpOffice\PhpSpreadsheet\Spreadsheet;
  10  use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
  11  use PhpOffice\PhpSpreadsheet\Writer\Exception as WriterException;
  12  
  13  class Workbook extends WriterPart
  14  {
  15      /**
  16       * Write workbook to XML format.
  17       *
  18       * @param Spreadsheet $spreadsheet
  19       * @param bool $recalcRequired Indicate whether formulas should be recalculated before writing
  20       *
  21       * @throws WriterException
  22       *
  23       * @return string XML Output
  24       */
  25      public function writeWorkbook(Spreadsheet $spreadsheet, $recalcRequired = false)
  26      {
  27          // Create XML writer
  28          if ($this->getParentWriter()->getUseDiskCaching()) {
  29              $objWriter = new XMLWriter(XMLWriter::STORAGE_DISK, $this->getParentWriter()->getDiskCachingDirectory());
  30          } else {
  31              $objWriter = new XMLWriter(XMLWriter::STORAGE_MEMORY);
  32          }
  33  
  34          // XML header
  35          $objWriter->startDocument('1.0', 'UTF-8', 'yes');
  36  
  37          // workbook
  38          $objWriter->startElement('workbook');
  39          $objWriter->writeAttribute('xml:space', 'preserve');
  40          $objWriter->writeAttribute('xmlns', 'http://schemas.openxmlformats.org/spreadsheetml/2006/main');
  41          $objWriter->writeAttribute('xmlns:r', 'http://schemas.openxmlformats.org/officeDocument/2006/relationships');
  42  
  43          // fileVersion
  44          $this->writeFileVersion($objWriter);
  45  
  46          // workbookPr
  47          $this->writeWorkbookPr($objWriter);
  48  
  49          // workbookProtection
  50          $this->writeWorkbookProtection($objWriter, $spreadsheet);
  51  
  52          // bookViews
  53          if ($this->getParentWriter()->getOffice2003Compatibility() === false) {
  54              $this->writeBookViews($objWriter, $spreadsheet);
  55          }
  56  
  57          // sheets
  58          $this->writeSheets($objWriter, $spreadsheet);
  59  
  60          // definedNames
  61          $this->writeDefinedNames($objWriter, $spreadsheet);
  62  
  63          // calcPr
  64          $this->writeCalcPr($objWriter, $recalcRequired);
  65  
  66          $objWriter->endElement();
  67  
  68          // Return
  69          return $objWriter->getData();
  70      }
  71  
  72      /**
  73       * Write file version.
  74       *
  75       * @param XMLWriter $objWriter XML Writer
  76       */
  77      private function writeFileVersion(XMLWriter $objWriter)
  78      {
  79          $objWriter->startElement('fileVersion');
  80          $objWriter->writeAttribute('appName', 'xl');
  81          $objWriter->writeAttribute('lastEdited', '4');
  82          $objWriter->writeAttribute('lowestEdited', '4');
  83          $objWriter->writeAttribute('rupBuild', '4505');
  84          $objWriter->endElement();
  85      }
  86  
  87      /**
  88       * Write WorkbookPr.
  89       *
  90       * @param XMLWriter $objWriter XML Writer
  91       */
  92      private function writeWorkbookPr(XMLWriter $objWriter)
  93      {
  94          $objWriter->startElement('workbookPr');
  95  
  96          if (Date::getExcelCalendar() === Date::CALENDAR_MAC_1904) {
  97              $objWriter->writeAttribute('date1904', '1');
  98          }
  99  
 100          $objWriter->writeAttribute('codeName', 'ThisWorkbook');
 101  
 102          $objWriter->endElement();
 103      }
 104  
 105      /**
 106       * Write BookViews.
 107       *
 108       * @param XMLWriter $objWriter XML Writer
 109       * @param Spreadsheet $spreadsheet
 110       */
 111      private function writeBookViews(XMLWriter $objWriter, Spreadsheet $spreadsheet)
 112      {
 113          // bookViews
 114          $objWriter->startElement('bookViews');
 115  
 116          // workbookView
 117          $objWriter->startElement('workbookView');
 118  
 119          $objWriter->writeAttribute('activeTab', $spreadsheet->getActiveSheetIndex());
 120          $objWriter->writeAttribute('autoFilterDateGrouping', ($spreadsheet->getAutoFilterDateGrouping() ? 'true' : 'false'));
 121          $objWriter->writeAttribute('firstSheet', $spreadsheet->getFirstSheetIndex());
 122          $objWriter->writeAttribute('minimized', ($spreadsheet->getMinimized() ? 'true' : 'false'));
 123          $objWriter->writeAttribute('showHorizontalScroll', ($spreadsheet->getShowHorizontalScroll() ? 'true' : 'false'));
 124          $objWriter->writeAttribute('showSheetTabs', ($spreadsheet->getShowSheetTabs() ? 'true' : 'false'));
 125          $objWriter->writeAttribute('showVerticalScroll', ($spreadsheet->getShowVerticalScroll() ? 'true' : 'false'));
 126          $objWriter->writeAttribute('tabRatio', $spreadsheet->getTabRatio());
 127          $objWriter->writeAttribute('visibility', $spreadsheet->getVisibility());
 128  
 129          $objWriter->endElement();
 130  
 131          $objWriter->endElement();
 132      }
 133  
 134      /**
 135       * Write WorkbookProtection.
 136       *
 137       * @param XMLWriter $objWriter XML Writer
 138       * @param Spreadsheet $spreadsheet
 139       */
 140      private function writeWorkbookProtection(XMLWriter $objWriter, Spreadsheet $spreadsheet)
 141      {
 142          if ($spreadsheet->getSecurity()->isSecurityEnabled()) {
 143              $objWriter->startElement('workbookProtection');
 144              $objWriter->writeAttribute('lockRevision', ($spreadsheet->getSecurity()->getLockRevision() ? 'true' : 'false'));
 145              $objWriter->writeAttribute('lockStructure', ($spreadsheet->getSecurity()->getLockStructure() ? 'true' : 'false'));
 146              $objWriter->writeAttribute('lockWindows', ($spreadsheet->getSecurity()->getLockWindows() ? 'true' : 'false'));
 147  
 148              if ($spreadsheet->getSecurity()->getRevisionsPassword() != '') {
 149                  $objWriter->writeAttribute('revisionsPassword', $spreadsheet->getSecurity()->getRevisionsPassword());
 150              }
 151  
 152              if ($spreadsheet->getSecurity()->getWorkbookPassword() != '') {
 153                  $objWriter->writeAttribute('workbookPassword', $spreadsheet->getSecurity()->getWorkbookPassword());
 154              }
 155  
 156              $objWriter->endElement();
 157          }
 158      }
 159  
 160      /**
 161       * Write calcPr.
 162       *
 163       * @param XMLWriter $objWriter XML Writer
 164       * @param bool $recalcRequired Indicate whether formulas should be recalculated before writing
 165       */
 166      private function writeCalcPr(XMLWriter $objWriter, $recalcRequired = true)
 167      {
 168          $objWriter->startElement('calcPr');
 169  
 170          //    Set the calcid to a higher value than Excel itself will use, otherwise Excel will always recalc
 171          //  If MS Excel does do a recalc, then users opening a file in MS Excel will be prompted to save on exit
 172          //     because the file has changed
 173          $objWriter->writeAttribute('calcId', '999999');
 174          $objWriter->writeAttribute('calcMode', 'auto');
 175          //    fullCalcOnLoad isn't needed if we've recalculating for the save
 176          $objWriter->writeAttribute('calcCompleted', ($recalcRequired) ? 1 : 0);
 177          $objWriter->writeAttribute('fullCalcOnLoad', ($recalcRequired) ? 0 : 1);
 178          $objWriter->writeAttribute('forceFullCalc', ($recalcRequired) ? 0 : 1);
 179  
 180          $objWriter->endElement();
 181      }
 182  
 183      /**
 184       * Write sheets.
 185       *
 186       * @param XMLWriter $objWriter XML Writer
 187       * @param Spreadsheet $spreadsheet
 188       *
 189       * @throws WriterException
 190       */
 191      private function writeSheets(XMLWriter $objWriter, Spreadsheet $spreadsheet)
 192      {
 193          // Write sheets
 194          $objWriter->startElement('sheets');
 195          $sheetCount = $spreadsheet->getSheetCount();
 196          for ($i = 0; $i < $sheetCount; ++$i) {
 197              // sheet
 198              $this->writeSheet(
 199                  $objWriter,
 200                  $spreadsheet->getSheet($i)->getTitle(),
 201                  ($i + 1),
 202                  ($i + 1 + 3),
 203                  $spreadsheet->getSheet($i)->getSheetState()
 204              );
 205          }
 206  
 207          $objWriter->endElement();
 208      }
 209  
 210      /**
 211       * Write sheet.
 212       *
 213       * @param XMLWriter $objWriter XML Writer
 214       * @param string $pSheetname Sheet name
 215       * @param int $pSheetId Sheet id
 216       * @param int $pRelId Relationship ID
 217       * @param string $sheetState Sheet state (visible, hidden, veryHidden)
 218       *
 219       * @throws WriterException
 220       */
 221      private function writeSheet(XMLWriter $objWriter, $pSheetname, $pSheetId = 1, $pRelId = 1, $sheetState = 'visible')
 222      {
 223          if ($pSheetname != '') {
 224              // Write sheet
 225              $objWriter->startElement('sheet');
 226              $objWriter->writeAttribute('name', $pSheetname);
 227              $objWriter->writeAttribute('sheetId', $pSheetId);
 228              if ($sheetState !== 'visible' && $sheetState != '') {
 229                  $objWriter->writeAttribute('state', $sheetState);
 230              }
 231              $objWriter->writeAttribute('r:id', 'rId' . $pRelId);
 232              $objWriter->endElement();
 233          } else {
 234              throw new WriterException('Invalid parameters passed.');
 235          }
 236      }
 237  
 238      /**
 239       * Write Defined Names.
 240       *
 241       * @param XMLWriter $objWriter XML Writer
 242       * @param Spreadsheet $spreadsheet
 243       *
 244       * @throws WriterException
 245       */
 246      private function writeDefinedNames(XMLWriter $objWriter, Spreadsheet $spreadsheet)
 247      {
 248          // Write defined names
 249          $objWriter->startElement('definedNames');
 250  
 251          // Named ranges
 252          if (count($spreadsheet->getNamedRanges()) > 0) {
 253              // Named ranges
 254              $this->writeNamedRanges($objWriter, $spreadsheet);
 255          }
 256  
 257          // Other defined names
 258          $sheetCount = $spreadsheet->getSheetCount();
 259          for ($i = 0; $i < $sheetCount; ++$i) {
 260              // definedName for autoFilter
 261              $this->writeDefinedNameForAutofilter($objWriter, $spreadsheet->getSheet($i), $i);
 262  
 263              // definedName for Print_Titles
 264              $this->writeDefinedNameForPrintTitles($objWriter, $spreadsheet->getSheet($i), $i);
 265  
 266              // definedName for Print_Area
 267              $this->writeDefinedNameForPrintArea($objWriter, $spreadsheet->getSheet($i), $i);
 268          }
 269  
 270          $objWriter->endElement();
 271      }
 272  
 273      /**
 274       * Write named ranges.
 275       *
 276       * @param XMLWriter $objWriter XML Writer
 277       * @param Spreadsheet $spreadsheet
 278       *
 279       * @throws WriterException
 280       */
 281      private function writeNamedRanges(XMLWriter $objWriter, Spreadsheet $spreadsheet)
 282      {
 283          // Loop named ranges
 284          $namedRanges = $spreadsheet->getNamedRanges();
 285          foreach ($namedRanges as $namedRange) {
 286              $this->writeDefinedNameForNamedRange($objWriter, $namedRange);
 287          }
 288      }
 289  
 290      /**
 291       * Write Defined Name for named range.
 292       *
 293       * @param XMLWriter $objWriter XML Writer
 294       * @param NamedRange $pNamedRange
 295       */
 296      private function writeDefinedNameForNamedRange(XMLWriter $objWriter, NamedRange $pNamedRange)
 297      {
 298          // definedName for named range
 299          $objWriter->startElement('definedName');
 300          $objWriter->writeAttribute('name', $pNamedRange->getName());
 301          if ($pNamedRange->getLocalOnly()) {
 302              $objWriter->writeAttribute('localSheetId', $pNamedRange->getScope()->getParent()->getIndex($pNamedRange->getScope()));
 303          }
 304  
 305          // Create absolute coordinate and write as raw text
 306          $range = Coordinate::splitRange($pNamedRange->getRange());
 307          $iMax = count($range);
 308          for ($i = 0; $i < $iMax; ++$i) {
 309              $range[$i][0] = '\'' . str_replace("'", "''", $pNamedRange->getWorksheet()->getTitle()) . '\'!' . Coordinate::absoluteReference($range[$i][0]);
 310              if (isset($range[$i][1])) {
 311                  $range[$i][1] = Coordinate::absoluteReference($range[$i][1]);
 312              }
 313          }
 314          $range = Coordinate::buildRange($range);
 315  
 316          $objWriter->writeRawData($range);
 317  
 318          $objWriter->endElement();
 319      }
 320  
 321      /**
 322       * Write Defined Name for autoFilter.
 323       *
 324       * @param XMLWriter $objWriter XML Writer
 325       * @param Worksheet $pSheet
 326       * @param int $pSheetId
 327       */
 328      private function writeDefinedNameForAutofilter(XMLWriter $objWriter, Worksheet $pSheet, $pSheetId = 0)
 329      {
 330          // definedName for autoFilter
 331          $autoFilterRange = $pSheet->getAutoFilter()->getRange();
 332          if (!empty($autoFilterRange)) {
 333              $objWriter->startElement('definedName');
 334              $objWriter->writeAttribute('name', '_xlnm._FilterDatabase');
 335              $objWriter->writeAttribute('localSheetId', $pSheetId);
 336              $objWriter->writeAttribute('hidden', '1');
 337  
 338              // Create absolute coordinate and write as raw text
 339              $range = Coordinate::splitRange($autoFilterRange);
 340              $range = $range[0];
 341              //    Strip any worksheet ref so we can make the cell ref absolute
 342              [$ws, $range[0]] = Worksheet::extractSheetTitle($range[0], true);
 343  
 344              $range[0] = Coordinate::absoluteCoordinate($range[0]);
 345              $range[1] = Coordinate::absoluteCoordinate($range[1]);
 346              $range = implode(':', $range);
 347  
 348              $objWriter->writeRawData('\'' . str_replace("'", "''", $pSheet->getTitle()) . '\'!' . $range);
 349  
 350              $objWriter->endElement();
 351          }
 352      }
 353  
 354      /**
 355       * Write Defined Name for PrintTitles.
 356       *
 357       * @param XMLWriter $objWriter XML Writer
 358       * @param Worksheet $pSheet
 359       * @param int $pSheetId
 360       */
 361      private function writeDefinedNameForPrintTitles(XMLWriter $objWriter, Worksheet $pSheet, $pSheetId = 0)
 362      {
 363          // definedName for PrintTitles
 364          if ($pSheet->getPageSetup()->isColumnsToRepeatAtLeftSet() || $pSheet->getPageSetup()->isRowsToRepeatAtTopSet()) {
 365              $objWriter->startElement('definedName');
 366              $objWriter->writeAttribute('name', '_xlnm.Print_Titles');
 367              $objWriter->writeAttribute('localSheetId', $pSheetId);
 368  
 369              // Setting string
 370              $settingString = '';
 371  
 372              // Columns to repeat
 373              if ($pSheet->getPageSetup()->isColumnsToRepeatAtLeftSet()) {
 374                  $repeat = $pSheet->getPageSetup()->getColumnsToRepeatAtLeft();
 375  
 376                  $settingString .= '\'' . str_replace("'", "''", $pSheet->getTitle()) . '\'!$' . $repeat[0] . ':$' . $repeat[1];
 377              }
 378  
 379              // Rows to repeat
 380              if ($pSheet->getPageSetup()->isRowsToRepeatAtTopSet()) {
 381                  if ($pSheet->getPageSetup()->isColumnsToRepeatAtLeftSet()) {
 382                      $settingString .= ',';
 383                  }
 384  
 385                  $repeat = $pSheet->getPageSetup()->getRowsToRepeatAtTop();
 386  
 387                  $settingString .= '\'' . str_replace("'", "''", $pSheet->getTitle()) . '\'!$' . $repeat[0] . ':$' . $repeat[1];
 388              }
 389  
 390              $objWriter->writeRawData($settingString);
 391  
 392              $objWriter->endElement();
 393          }
 394      }
 395  
 396      /**
 397       * Write Defined Name for PrintTitles.
 398       *
 399       * @param XMLWriter $objWriter XML Writer
 400       * @param Worksheet $pSheet
 401       * @param int $pSheetId
 402       */
 403      private function writeDefinedNameForPrintArea(XMLWriter $objWriter, Worksheet $pSheet, $pSheetId = 0)
 404      {
 405          // definedName for PrintArea
 406          if ($pSheet->getPageSetup()->isPrintAreaSet()) {
 407              $objWriter->startElement('definedName');
 408              $objWriter->writeAttribute('name', '_xlnm.Print_Area');
 409              $objWriter->writeAttribute('localSheetId', $pSheetId);
 410  
 411              // Print area
 412              $printArea = Coordinate::splitRange($pSheet->getPageSetup()->getPrintArea());
 413  
 414              $chunks = [];
 415              foreach ($printArea as $printAreaRect) {
 416                  $printAreaRect[0] = Coordinate::absoluteReference($printAreaRect[0]);
 417                  $printAreaRect[1] = Coordinate::absoluteReference($printAreaRect[1]);
 418                  $chunks[] = '\'' . str_replace("'", "''", $pSheet->getTitle()) . '\'!' . implode(':', $printAreaRect);
 419              }
 420  
 421              $objWriter->writeRawData(implode(',', $chunks));
 422  
 423              $objWriter->endElement();
 424          }
 425      }
 426  }