vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Writer/Xlsx/Workbook.php

   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', '1');
 121         $objWriter->writeAttribute('firstSheet', '0');
 122         $objWriter->writeAttribute('minimized', '0');
 123         $objWriter->writeAttribute('showHorizontalScroll', '1');
 124         $objWriter->writeAttribute('showSheetTabs', '1');
 125         $objWriter->writeAttribute('showVerticalScroll', '1');
 126         $objWriter->writeAttribute('tabRatio', '600');
 127         $objWriter->writeAttribute('visibility', 'visible');
 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
 179         $objWriter->endElement();
 180     }
 181
 182     /**
 183      * Write sheets.
 184      *
 185      * @param XMLWriter $objWriter XML Writer
 186      * @param Spreadsheet $spreadsheet
 187      *
 188      * @throws WriterException
 189      */
 190     private function writeSheets(XMLWriter $objWriter, Spreadsheet $spreadsheet)
 191     {
 192         // Write sheets
 193         $objWriter->startElement('sheets');
 194         $sheetCount = $spreadsheet->getSheetCount();
 195         for ($i = 0; $i < $sheetCount; ++$i) {
 196             // sheet
 197             $this->writeSheet(
 198                 $objWriter,
 199                 $spreadsheet->getSheet($i)->getTitle(),
 200                 ($i + 1),
 201                 ($i + 1 + 3),
 202                 $spreadsheet->getSheet($i)->getSheetState()
 203             );
 204         }
 205
 206         $objWriter->endElement();
 207     }
 208
 209     /**
 210      * Write sheet.
 211      *
 212      * @param XMLWriter $objWriter XML Writer
 213      * @param string $pSheetname Sheet name
 214      * @param int $pSheetId Sheet id
 215      * @param int $pRelId Relationship ID
 216      * @param string $sheetState Sheet state (visible, hidden, veryHidden)
 217      *
 218      * @throws WriterException
 219      */
 220     private function writeSheet(XMLWriter $objWriter, $pSheetname, $pSheetId = 1, $pRelId = 1, $sheetState = 'visible')
 221     {
 222         if ($pSheetname != '') {
 223             // Write sheet
 224             $objWriter->startElement('sheet');
 225             $objWriter->writeAttribute('name', $pSheetname);
 226             $objWriter->writeAttribute('sheetId', $pSheetId);
 227             if ($sheetState != 'visible' && $sheetState != '') {
 228                 $objWriter->writeAttribute('state', $sheetState);
 229             }
 230             $objWriter->writeAttribute('r:id', 'rId' . $pRelId);
 231             $objWriter->endElement();
 232         } else {
 233             throw new WriterException('Invalid parameters passed.');
 234         }
 235     }
 236
 237     /**
 238      * Write Defined Names.
 239      *
 240      * @param XMLWriter $objWriter XML Writer
 241      * @param Spreadsheet $spreadsheet
 242      *
 243      * @throws WriterException
 244      */
 245     private function writeDefinedNames(XMLWriter $objWriter, Spreadsheet $spreadsheet)
 246     {
 247         // Write defined names
 248         $objWriter->startElement('definedNames');
 249
 250         // Named ranges
 251         if (count($spreadsheet->getNamedRanges()) > 0) {
 252             // Named ranges
 253             $this->writeNamedRanges($objWriter, $spreadsheet);
 254         }
 255
 256         // Other defined names
 257         $sheetCount = $spreadsheet->getSheetCount();
 258         for ($i = 0; $i < $sheetCount; ++$i) {
 259             // definedName for autoFilter
 260             $this->writeDefinedNameForAutofilter($objWriter, $spreadsheet->getSheet($i), $i);
 261
 262             // definedName for Print_Titles
 263             $this->writeDefinedNameForPrintTitles($objWriter, $spreadsheet->getSheet($i), $i);
 264
 265             // definedName for Print_Area
 266             $this->writeDefinedNameForPrintArea($objWriter, $spreadsheet->getSheet($i), $i);
 267         }
 268
 269         $objWriter->endElement();
 270     }
 271
 272     /**
 273      * Write named ranges.
 274      *
 275      * @param XMLWriter $objWriter XML Writer
 276      * @param Spreadsheet $spreadsheet
 277      *
 278      * @throws WriterException
 279      */
 280     private function writeNamedRanges(XMLWriter $objWriter, Spreadsheet $spreadsheet)
 281     {
 282         // Loop named ranges
 283         $namedRanges = $spreadsheet->getNamedRanges();
 284         foreach ($namedRanges as $namedRange) {
 285             $this->writeDefinedNameForNamedRange($objWriter, $namedRange);
 286         }
 287     }
 288
 289     /**
 290      * Write Defined Name for named range.
 291      *
 292      * @param XMLWriter $objWriter XML Writer
 293      * @param NamedRange $pNamedRange
 294      */
 295     private function writeDefinedNameForNamedRange(XMLWriter $objWriter, NamedRange $pNamedRange)
 296     {
 297         // definedName for named range
 298         $objWriter->startElement('definedName');
 299         $objWriter->writeAttribute('name', $pNamedRange->getName());
 300         if ($pNamedRange->getLocalOnly()) {
 301             $objWriter->writeAttribute('localSheetId', $pNamedRange->getScope()->getParent()->getIndex($pNamedRange->getScope()));
 302         }
 303
 304         // Create absolute coordinate and write as raw text
 305         $range = Coordinate::splitRange($pNamedRange->getRange());
 306         $iMax = count($range);
 307         for ($i = 0; $i < $iMax; ++$i) {
 308             $range[$i][0] = '\'' . str_replace("'", "''", $pNamedRange->getWorksheet()->getTitle()) . '\'!' . Coordinate::absoluteReference($range[$i][0]);
 309             if (isset($range[$i][1])) {
 310                 $range[$i][1] = Coordinate::absoluteReference($range[$i][1]);
 311             }
 312         }
 313         $range = Coordinate::buildRange($range);
 314
 315         $objWriter->writeRawData($range);
 316
 317         $objWriter->endElement();
 318     }
 319
 320     /**
 321      * Write Defined Name for autoFilter.
 322      *
 323      * @param XMLWriter $objWriter XML Writer
 324      * @param Worksheet $pSheet
 325      * @param int $pSheetId
 326      */
 327     private function writeDefinedNameForAutofilter(XMLWriter $objWriter, Worksheet $pSheet, $pSheetId = 0)
 328     {
 329         // definedName for autoFilter
 330         $autoFilterRange = $pSheet->getAutoFilter()->getRange();
 331         if (!empty($autoFilterRange)) {
 332             $objWriter->startElement('definedName');
 333             $objWriter->writeAttribute('name', '_xlnm._FilterDatabase');
 334             $objWriter->writeAttribute('localSheetId', $pSheetId);
 335             $objWriter->writeAttribute('hidden', '1');
 336
 337             // Create absolute coordinate and write as raw text
 338             $range = Coordinate::splitRange($autoFilterRange);
 339             $range = $range[0];
 340             //    Strip any worksheet ref so we can make the cell ref absolute
 341             if (strpos($range[0], '!') !== false) {
 342                 list($ws, $range[0]) = explode('!', $range[0]);
 343             }
 344
 345             $range[0] = Coordinate::absoluteCoordinate($range[0]);
 346             $range[1] = Coordinate::absoluteCoordinate($range[1]);
 347             $range = implode(':', $range);
 348
 349             $objWriter->writeRawData('\'' . str_replace("'", "''", $pSheet->getTitle()) . '\'!' . $range);
 350
 351             $objWriter->endElement();
 352         }
 353     }
 354
 355     /**
 356      * Write Defined Name for PrintTitles.
 357      *
 358      * @param XMLWriter $objWriter XML Writer
 359      * @param Worksheet $pSheet
 360      * @param int $pSheetId
 361      */
 362     private function writeDefinedNameForPrintTitles(XMLWriter $objWriter, Worksheet $pSheet, $pSheetId = 0)
 363     {
 364         // definedName for PrintTitles
 365         if ($pSheet->getPageSetup()->isColumnsToRepeatAtLeftSet() || $pSheet->getPageSetup()->isRowsToRepeatAtTopSet()) {
 366             $objWriter->startElement('definedName');
 367             $objWriter->writeAttribute('name', '_xlnm.Print_Titles');
 368             $objWriter->writeAttribute('localSheetId', $pSheetId);
 369
 370             // Setting string
 371             $settingString = '';
 372
 373             // Columns to repeat
 374             if ($pSheet->getPageSetup()->isColumnsToRepeatAtLeftSet()) {
 375                 $repeat = $pSheet->getPageSetup()->getColumnsToRepeatAtLeft();
 376
 377                 $settingString .= '\'' . str_replace("'", "''", $pSheet->getTitle()) . '\'!$' . $repeat[0] . ':$' . $repeat[1];
 378             }
 379
 380             // Rows to repeat
 381             if ($pSheet->getPageSetup()->isRowsToRepeatAtTopSet()) {
 382                 if ($pSheet->getPageSetup()->isColumnsToRepeatAtLeftSet()) {
 383                     $settingString .= ',';
 384                 }
 385
 386                 $repeat = $pSheet->getPageSetup()->getRowsToRepeatAtTop();
 387
 388                 $settingString .= '\'' . str_replace("'", "''", $pSheet->getTitle()) . '\'!$' . $repeat[0] . ':$' . $repeat[1];
 389             }
 390
 391             $objWriter->writeRawData($settingString);
 392
 393             $objWriter->endElement();
 394         }
 395     }
 396
 397     /**
 398      * Write Defined Name for PrintTitles.
 399      *
 400      * @param XMLWriter $objWriter XML Writer
 401      * @param Worksheet $pSheet
 402      * @param int $pSheetId
 403      */
 404     private function writeDefinedNameForPrintArea(XMLWriter $objWriter, Worksheet $pSheet, $pSheetId = 0)
 405     {
 406         // definedName for PrintArea
 407         if ($pSheet->getPageSetup()->isPrintAreaSet()) {
 408             $objWriter->startElement('definedName');
 409             $objWriter->writeAttribute('name', '_xlnm.Print_Area');
 410             $objWriter->writeAttribute('localSheetId', $pSheetId);
 411
 412             // Print area
 413             $printArea = Coordinate::splitRange($pSheet->getPageSetup()->getPrintArea());
 414
 415             $chunks = [];
 416             foreach ($printArea as $printAreaRect) {
 417                 $printAreaRect[0] = Coordinate::absoluteReference($printAreaRect[0]);
 418                 $printAreaRect[1] = Coordinate::absoluteReference($printAreaRect[1]);
 419                 $chunks[] = '\'' . str_replace("'", "''", $pSheet->getTitle()) . '\'!' . implode(':', $printAreaRect);
 420             }
 421
 422             $objWriter->writeRawData(implode(',', $chunks));
 423
 424             $objWriter->endElement();
 425         }
 426     }
 427 }