[Summary view]

Differences Between: [Versions 310 and 401] [Versions 311 and 401] [Versions 39 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 PhpOffice\PhpSpreadsheet\Shared\Date;
   6  use PhpOffice\PhpSpreadsheet\Shared\XMLWriter;
   7  use PhpOffice\PhpSpreadsheet\Spreadsheet;
   8  use PhpOffice\PhpSpreadsheet\Writer\Exception as WriterException;
   9  use PhpOffice\PhpSpreadsheet\Writer\Xlsx\DefinedNames as DefinedNamesWriter;
  10  
  11  class Workbook extends WriterPart
  12  {
  13      /**
  14       * Write workbook to XML format.
  15       *
  16       * @param bool $recalcRequired Indicate whether formulas should be recalculated before writing
  17       *
  18       * @return string XML Output
  19       */
  20      public function writeWorkbook(Spreadsheet $spreadsheet, $recalcRequired = false)
  21      {
  22          // Create XML writer
  23          if ($this->getParentWriter()->getUseDiskCaching()) {
  24              $objWriter = new XMLWriter(XMLWriter::STORAGE_DISK, $this->getParentWriter()->getDiskCachingDirectory());
  25          } else {
  26              $objWriter = new XMLWriter(XMLWriter::STORAGE_MEMORY);
  27          }
  28  
  29          // XML header
  30          $objWriter->startDocument('1.0', 'UTF-8', 'yes');
  31  
  32          // workbook
  33          $objWriter->startElement('workbook');
  34          $objWriter->writeAttribute('xml:space', 'preserve');
  35          $objWriter->writeAttribute('xmlns', 'http://schemas.openxmlformats.org/spreadsheetml/2006/main');
  36          $objWriter->writeAttribute('xmlns:r', 'http://schemas.openxmlformats.org/officeDocument/2006/relationships');
  37  
  38          // fileVersion
  39          $this->writeFileVersion($objWriter);
  40  
  41          // workbookPr
  42          $this->writeWorkbookPr($objWriter);
  43  
  44          // workbookProtection
  45          $this->writeWorkbookProtection($objWriter, $spreadsheet);
  46  
  47          // bookViews
  48          if ($this->getParentWriter()->getOffice2003Compatibility() === false) {
  49              $this->writeBookViews($objWriter, $spreadsheet);
  50          }
  51  
  52          // sheets
  53          $this->writeSheets($objWriter, $spreadsheet);
  54  
  55          // definedNames
  56          (new DefinedNamesWriter($objWriter, $spreadsheet))->write();
  57  
  58          // calcPr
  59          $this->writeCalcPr($objWriter, $recalcRequired);
  60  
  61          $objWriter->endElement();
  62  
  63          // Return
  64          return $objWriter->getData();
  65      }
  66  
  67      /**
  68       * Write file version.
  69       */
  70      private function writeFileVersion(XMLWriter $objWriter): void
  71      {
  72          $objWriter->startElement('fileVersion');
  73          $objWriter->writeAttribute('appName', 'xl');
  74          $objWriter->writeAttribute('lastEdited', '4');
  75          $objWriter->writeAttribute('lowestEdited', '4');
  76          $objWriter->writeAttribute('rupBuild', '4505');
  77          $objWriter->endElement();
  78      }
  79  
  80      /**
  81       * Write WorkbookPr.
  82       */
  83      private function writeWorkbookPr(XMLWriter $objWriter): void
  84      {
  85          $objWriter->startElement('workbookPr');
  86  
  87          if (Date::getExcelCalendar() === Date::CALENDAR_MAC_1904) {
  88              $objWriter->writeAttribute('date1904', '1');
  89          }
  90  
  91          $objWriter->writeAttribute('codeName', 'ThisWorkbook');
  92  
  93          $objWriter->endElement();
  94      }
  95  
  96      /**
  97       * Write BookViews.
  98       */
  99      private function writeBookViews(XMLWriter $objWriter, Spreadsheet $spreadsheet): void
 100      {
 101          // bookViews
 102          $objWriter->startElement('bookViews');
 103  
 104          // workbookView
 105          $objWriter->startElement('workbookView');
 106  
 107          $objWriter->writeAttribute('activeTab', (string) $spreadsheet->getActiveSheetIndex());
 108          $objWriter->writeAttribute('autoFilterDateGrouping', ($spreadsheet->getAutoFilterDateGrouping() ? 'true' : 'false'));
 109          $objWriter->writeAttribute('firstSheet', (string) $spreadsheet->getFirstSheetIndex());
 110          $objWriter->writeAttribute('minimized', ($spreadsheet->getMinimized() ? 'true' : 'false'));
 111          $objWriter->writeAttribute('showHorizontalScroll', ($spreadsheet->getShowHorizontalScroll() ? 'true' : 'false'));
 112          $objWriter->writeAttribute('showSheetTabs', ($spreadsheet->getShowSheetTabs() ? 'true' : 'false'));
 113          $objWriter->writeAttribute('showVerticalScroll', ($spreadsheet->getShowVerticalScroll() ? 'true' : 'false'));
 114          $objWriter->writeAttribute('tabRatio', (string) $spreadsheet->getTabRatio());
 115          $objWriter->writeAttribute('visibility', $spreadsheet->getVisibility());
 116  
 117          $objWriter->endElement();
 118  
 119          $objWriter->endElement();
 120      }
 121  
 122      /**
 123       * Write WorkbookProtection.
 124       */
 125      private function writeWorkbookProtection(XMLWriter $objWriter, Spreadsheet $spreadsheet): void
 126      {
 127          if ($spreadsheet->getSecurity()->isSecurityEnabled()) {
 128              $objWriter->startElement('workbookProtection');
 129              $objWriter->writeAttribute('lockRevision', ($spreadsheet->getSecurity()->getLockRevision() ? 'true' : 'false'));
 130              $objWriter->writeAttribute('lockStructure', ($spreadsheet->getSecurity()->getLockStructure() ? 'true' : 'false'));
 131              $objWriter->writeAttribute('lockWindows', ($spreadsheet->getSecurity()->getLockWindows() ? 'true' : 'false'));
 132  
 133              if ($spreadsheet->getSecurity()->getRevisionsPassword() != '') {
 134                  $objWriter->writeAttribute('revisionsPassword', $spreadsheet->getSecurity()->getRevisionsPassword());
 135              }
 136  
 137              if ($spreadsheet->getSecurity()->getWorkbookPassword() != '') {
 138                  $objWriter->writeAttribute('workbookPassword', $spreadsheet->getSecurity()->getWorkbookPassword());
 139              }
 140  
 141              $objWriter->endElement();
 142          }
 143      }
 144  
 145      /**
 146       * Write calcPr.
 147       *
 148       * @param bool $recalcRequired Indicate whether formulas should be recalculated before writing
 149       */
 150      private function writeCalcPr(XMLWriter $objWriter, $recalcRequired = true): void
 151      {
 152          $objWriter->startElement('calcPr');
 153  
 154          //    Set the calcid to a higher value than Excel itself will use, otherwise Excel will always recalc
 155          //  If MS Excel does do a recalc, then users opening a file in MS Excel will be prompted to save on exit
 156          //     because the file has changed
 157          $objWriter->writeAttribute('calcId', '999999');
 158          $objWriter->writeAttribute('calcMode', 'auto');
 159          //    fullCalcOnLoad isn't needed if we've recalculating for the save
 160          $objWriter->writeAttribute('calcCompleted', ($recalcRequired) ? '1' : '0');
 161          $objWriter->writeAttribute('fullCalcOnLoad', ($recalcRequired) ? '0' : '1');
 162          $objWriter->writeAttribute('forceFullCalc', ($recalcRequired) ? '0' : '1');
 163  
 164          $objWriter->endElement();
 165      }
 166  
 167      /**
 168       * Write sheets.
 169       */
 170      private function writeSheets(XMLWriter $objWriter, Spreadsheet $spreadsheet): void
 171      {
 172          // Write sheets
 173          $objWriter->startElement('sheets');
 174          $sheetCount = $spreadsheet->getSheetCount();
 175          for ($i = 0; $i < $sheetCount; ++$i) {
 176              // sheet
 177              $this->writeSheet(
 178                  $objWriter,
 179                  $spreadsheet->getSheet($i)->getTitle(),
 180                  ($i + 1),
 181                  ($i + 1 + 3),
 182                  $spreadsheet->getSheet($i)->getSheetState()
 183              );
 184          }
 185  
 186          $objWriter->endElement();
 187      }
 188  
 189      /**
 190       * Write sheet.
 191       *
 192       * @param string $worksheetName Sheet name
 193       * @param int $worksheetId Sheet id
 194       * @param int $relId Relationship ID
 195       * @param string $sheetState Sheet state (visible, hidden, veryHidden)
 196       */
 197      private function writeSheet(XMLWriter $objWriter, $worksheetName, $worksheetId = 1, $relId = 1, $sheetState = 'visible'): void
 198      {
 199          if ($worksheetName != '') {
 200              // Write sheet
 201              $objWriter->startElement('sheet');
 202              $objWriter->writeAttribute('name', $worksheetName);
 203              $objWriter->writeAttribute('sheetId', (string) $worksheetId);
 204              if ($sheetState !== 'visible' && $sheetState != '') {
 205                  $objWriter->writeAttribute('state', $sheetState);
 206              }
 207              $objWriter->writeAttribute('r:id', 'rId' . $relId);
 208              $objWriter->endElement();
 209          } else {
 210              throw new WriterException('Invalid parameters passed.');
 211          }
 212      }
 213  }