[Summary view]

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

   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       * @param XMLWriter $objWriter XML Writer
  71       */
  72      private function writeFileVersion(XMLWriter $objWriter): void
  73      {
  74          $objWriter->startElement('fileVersion');
  75          $objWriter->writeAttribute('appName', 'xl');
  76          $objWriter->writeAttribute('lastEdited', '4');
  77          $objWriter->writeAttribute('lowestEdited', '4');
  78          $objWriter->writeAttribute('rupBuild', '4505');
  79          $objWriter->endElement();
  80      }
  81  
  82      /**
  83       * Write WorkbookPr.
  84       *
  85       * @param XMLWriter $objWriter XML Writer
  86       */
  87      private function writeWorkbookPr(XMLWriter $objWriter): void
  88      {
  89          $objWriter->startElement('workbookPr');
  90  
  91          if (Date::getExcelCalendar() === Date::CALENDAR_MAC_1904) {
  92              $objWriter->writeAttribute('date1904', '1');
  93          }
  94  
  95          $objWriter->writeAttribute('codeName', 'ThisWorkbook');
  96  
  97          $objWriter->endElement();
  98      }
  99  
 100      /**
 101       * Write BookViews.
 102       *
 103       * @param XMLWriter $objWriter XML Writer
 104       */
 105      private function writeBookViews(XMLWriter $objWriter, Spreadsheet $spreadsheet): void
 106      {
 107          // bookViews
 108          $objWriter->startElement('bookViews');
 109  
 110          // workbookView
 111          $objWriter->startElement('workbookView');
 112  
 113          $objWriter->writeAttribute('activeTab', $spreadsheet->getActiveSheetIndex());
 114          $objWriter->writeAttribute('autoFilterDateGrouping', ($spreadsheet->getAutoFilterDateGrouping() ? 'true' : 'false'));
 115          $objWriter->writeAttribute('firstSheet', $spreadsheet->getFirstSheetIndex());
 116          $objWriter->writeAttribute('minimized', ($spreadsheet->getMinimized() ? 'true' : 'false'));
 117          $objWriter->writeAttribute('showHorizontalScroll', ($spreadsheet->getShowHorizontalScroll() ? 'true' : 'false'));
 118          $objWriter->writeAttribute('showSheetTabs', ($spreadsheet->getShowSheetTabs() ? 'true' : 'false'));
 119          $objWriter->writeAttribute('showVerticalScroll', ($spreadsheet->getShowVerticalScroll() ? 'true' : 'false'));
 120          $objWriter->writeAttribute('tabRatio', $spreadsheet->getTabRatio());
 121          $objWriter->writeAttribute('visibility', $spreadsheet->getVisibility());
 122  
 123          $objWriter->endElement();
 124  
 125          $objWriter->endElement();
 126      }
 127  
 128      /**
 129       * Write WorkbookProtection.
 130       *
 131       * @param XMLWriter $objWriter XML Writer
 132       */
 133      private function writeWorkbookProtection(XMLWriter $objWriter, Spreadsheet $spreadsheet): void
 134      {
 135          if ($spreadsheet->getSecurity()->isSecurityEnabled()) {
 136              $objWriter->startElement('workbookProtection');
 137              $objWriter->writeAttribute('lockRevision', ($spreadsheet->getSecurity()->getLockRevision() ? 'true' : 'false'));
 138              $objWriter->writeAttribute('lockStructure', ($spreadsheet->getSecurity()->getLockStructure() ? 'true' : 'false'));
 139              $objWriter->writeAttribute('lockWindows', ($spreadsheet->getSecurity()->getLockWindows() ? 'true' : 'false'));
 140  
 141              if ($spreadsheet->getSecurity()->getRevisionsPassword() != '') {
 142                  $objWriter->writeAttribute('revisionsPassword', $spreadsheet->getSecurity()->getRevisionsPassword());
 143              }
 144  
 145              if ($spreadsheet->getSecurity()->getWorkbookPassword() != '') {
 146                  $objWriter->writeAttribute('workbookPassword', $spreadsheet->getSecurity()->getWorkbookPassword());
 147              }
 148  
 149              $objWriter->endElement();
 150          }
 151      }
 152  
 153      /**
 154       * Write calcPr.
 155       *
 156       * @param XMLWriter $objWriter XML Writer
 157       * @param bool $recalcRequired Indicate whether formulas should be recalculated before writing
 158       */
 159      private function writeCalcPr(XMLWriter $objWriter, $recalcRequired = true): void
 160      {
 161          $objWriter->startElement('calcPr');
 162  
 163          //    Set the calcid to a higher value than Excel itself will use, otherwise Excel will always recalc
 164          //  If MS Excel does do a recalc, then users opening a file in MS Excel will be prompted to save on exit
 165          //     because the file has changed
 166          $objWriter->writeAttribute('calcId', '999999');
 167          $objWriter->writeAttribute('calcMode', 'auto');
 168          //    fullCalcOnLoad isn't needed if we've recalculating for the save
 169          $objWriter->writeAttribute('calcCompleted', ($recalcRequired) ? 1 : 0);
 170          $objWriter->writeAttribute('fullCalcOnLoad', ($recalcRequired) ? 0 : 1);
 171          $objWriter->writeAttribute('forceFullCalc', ($recalcRequired) ? 0 : 1);
 172  
 173          $objWriter->endElement();
 174      }
 175  
 176      /**
 177       * Write sheets.
 178       *
 179       * @param XMLWriter $objWriter XML Writer
 180       */
 181      private function writeSheets(XMLWriter $objWriter, Spreadsheet $spreadsheet): void
 182      {
 183          // Write sheets
 184          $objWriter->startElement('sheets');
 185          $sheetCount = $spreadsheet->getSheetCount();
 186          for ($i = 0; $i < $sheetCount; ++$i) {
 187              // sheet
 188              $this->writeSheet(
 189                  $objWriter,
 190                  $spreadsheet->getSheet($i)->getTitle(),
 191                  ($i + 1),
 192                  ($i + 1 + 3),
 193                  $spreadsheet->getSheet($i)->getSheetState()
 194              );
 195          }
 196  
 197          $objWriter->endElement();
 198      }
 199  
 200      /**
 201       * Write sheet.
 202       *
 203       * @param XMLWriter $objWriter XML Writer
 204       * @param string $pSheetname Sheet name
 205       * @param int $pSheetId Sheet id
 206       * @param int $pRelId Relationship ID
 207       * @param string $sheetState Sheet state (visible, hidden, veryHidden)
 208       */
 209      private function writeSheet(XMLWriter $objWriter, $pSheetname, $pSheetId = 1, $pRelId = 1, $sheetState = 'visible'): void
 210      {
 211          if ($pSheetname != '') {
 212              // Write sheet
 213              $objWriter->startElement('sheet');
 214              $objWriter->writeAttribute('name', $pSheetname);
 215              $objWriter->writeAttribute('sheetId', $pSheetId);
 216              if ($sheetState !== 'visible' && $sheetState != '') {
 217                  $objWriter->writeAttribute('state', $sheetState);
 218              }
 219              $objWriter->writeAttribute('r:id', 'rId' . $pRelId);
 220              $objWriter->endElement();
 221          } else {
 222              throw new WriterException('Invalid parameters passed.');
 223          }
 224      }
 225  }