vendor/phpoffice/phpexcel/Classes/PHPExcel/Writer/Excel2007/Workbook.php

   1 <?php
   2 /**
   3  * PHPExcel
   4  *
   5  * Copyright (c) 2006 - 2014 PHPExcel
   6  *
   7  * This library is free software; you can redistribute it and/or
   8  * modify it under the terms of the GNU Lesser General Public
   9  * License as published by the Free Software Foundation; either
  10  * version 2.1 of the License, or (at your option) any later version.
  11  *
  12  * This library is distributed in the hope that it will be useful,
  13  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  14  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
  15  * Lesser General Public License for more details.
  16  *
  17  * You should have received a copy of the GNU Lesser General Public
  18  * License along with this library; if not, write to the Free Software
  19  * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
  20  *
  21  * @category   PHPExcel
  22  * @package    PHPExcel_Writer_Excel2007
  23  * @copyright  Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)
  24  * @license    http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt    LGPL
  25  * @version    ##VERSION##, ##DATE##
  26  */
  27
  28
  29 /**
  30  * PHPExcel_Writer_Excel2007_Workbook
  31  *
  32  * @category   PHPExcel
  33  * @package    PHPExcel_Writer_Excel2007
  34  * @copyright  Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)
  35  */
  36 class PHPExcel_Writer_Excel2007_Workbook extends PHPExcel_Writer_Excel2007_WriterPart
  37 {
  38         /**
  39          * Write workbook to XML format
  40          *
  41          * @param       PHPExcel        $pPHPExcel
  42          * @param       boolean         $recalcRequired Indicate whether formulas should be recalculated before writing
  43          * @return      string          XML Output
  44          * @throws      PHPExcel_Writer_Exception
  45          */
  46         public function writeWorkbook(PHPExcel $pPHPExcel = null, $recalcRequired = FALSE)
  47         {
  48                 // Create XML writer
  49                 $objWriter = null;
  50                 if ($this->getParentWriter()->getUseDiskCaching()) {
  51                         $objWriter = new PHPExcel_Shared_XMLWriter(PHPExcel_Shared_XMLWriter::STORAGE_DISK, $this->getParentWriter()->getDiskCachingDirectory());
  52                 } else {
  53                         $objWriter = new PHPExcel_Shared_XMLWriter(PHPExcel_Shared_XMLWriter::STORAGE_MEMORY);
  54                 }
  55
  56                 // XML header
  57                 $objWriter->startDocument('1.0','UTF-8','yes');
  58
  59                 // workbook
  60                 $objWriter->startElement('workbook');
  61                 $objWriter->writeAttribute('xml:space', 'preserve');
  62                 $objWriter->writeAttribute('xmlns', 'http://schemas.openxmlformats.org/spreadsheetml/2006/main');
  63                 $objWriter->writeAttribute('xmlns:r', 'http://schemas.openxmlformats.org/officeDocument/2006/relationships');
  64
  65                         // fileVersion
  66                         $this->_writeFileVersion($objWriter);
  67
  68                         // workbookPr
  69                         $this->_writeWorkbookPr($objWriter);
  70
  71                         // workbookProtection
  72                         $this->_writeWorkbookProtection($objWriter, $pPHPExcel);
  73
  74                         // bookViews
  75                         if ($this->getParentWriter()->getOffice2003Compatibility() === false) {
  76                                 $this->_writeBookViews($objWriter, $pPHPExcel);
  77                         }
  78
  79                         // sheets
  80                         $this->_writeSheets($objWriter, $pPHPExcel);
  81
  82                         // definedNames
  83                         $this->_writeDefinedNames($objWriter, $pPHPExcel);
  84
  85                         // calcPr
  86                         $this->_writeCalcPr($objWriter,$recalcRequired);
  87
  88                 $objWriter->endElement();
  89
  90                 // Return
  91                 return $objWriter->getData();
  92         }
  93
  94         /**
  95          * Write file version
  96          *
  97          * @param       PHPExcel_Shared_XMLWriter $objWriter            XML Writer
  98          * @throws      PHPExcel_Writer_Exception
  99          */
 100         private function _writeFileVersion(PHPExcel_Shared_XMLWriter $objWriter = null)
 101         {
 102                 $objWriter->startElement('fileVersion');
 103                 $objWriter->writeAttribute('appName', 'xl');
 104                 $objWriter->writeAttribute('lastEdited', '4');
 105                 $objWriter->writeAttribute('lowestEdited', '4');
 106                 $objWriter->writeAttribute('rupBuild', '4505');
 107                 $objWriter->endElement();
 108         }
 109
 110         /**
 111          * Write WorkbookPr
 112          *
 113          * @param       PHPExcel_Shared_XMLWriter $objWriter            XML Writer
 114          * @throws      PHPExcel_Writer_Exception
 115          */
 116         private function _writeWorkbookPr(PHPExcel_Shared_XMLWriter $objWriter = null)
 117         {
 118                 $objWriter->startElement('workbookPr');
 119
 120                 if (PHPExcel_Shared_Date::getExcelCalendar() == PHPExcel_Shared_Date::CALENDAR_MAC_1904) {
 121                         $objWriter->writeAttribute('date1904', '1');
 122                 }
 123
 124                 $objWriter->writeAttribute('codeName', 'ThisWorkbook');
 125
 126                 $objWriter->endElement();
 127         }
 128
 129         /**
 130          * Write BookViews
 131          *
 132          * @param       PHPExcel_Shared_XMLWriter       $objWriter              XML Writer
 133          * @param       PHPExcel                                        $pPHPExcel
 134          * @throws      PHPExcel_Writer_Exception
 135          */
 136         private function _writeBookViews(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel $pPHPExcel = null)
 137         {
 138                 // bookViews
 139                 $objWriter->startElement('bookViews');
 140
 141                         // workbookView
 142                         $objWriter->startElement('workbookView');
 143
 144                         $objWriter->writeAttribute('activeTab', $pPHPExcel->getActiveSheetIndex());
 145                         $objWriter->writeAttribute('autoFilterDateGrouping', '1');
 146                         $objWriter->writeAttribute('firstSheet', '0');
 147                         $objWriter->writeAttribute('minimized', '0');
 148                         $objWriter->writeAttribute('showHorizontalScroll', '1');
 149                         $objWriter->writeAttribute('showSheetTabs', '1');
 150                         $objWriter->writeAttribute('showVerticalScroll', '1');
 151                         $objWriter->writeAttribute('tabRatio', '600');
 152                         $objWriter->writeAttribute('visibility', 'visible');
 153
 154                         $objWriter->endElement();
 155
 156                 $objWriter->endElement();
 157         }
 158
 159         /**
 160          * Write WorkbookProtection
 161          *
 162          * @param       PHPExcel_Shared_XMLWriter       $objWriter              XML Writer
 163          * @param       PHPExcel                                        $pPHPExcel
 164          * @throws      PHPExcel_Writer_Exception
 165          */
 166         private function _writeWorkbookProtection(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel $pPHPExcel = null)
 167         {
 168                 if ($pPHPExcel->getSecurity()->isSecurityEnabled()) {
 169                         $objWriter->startElement('workbookProtection');
 170                         $objWriter->writeAttribute('lockRevision',              ($pPHPExcel->getSecurity()->getLockRevision() ? 'true' : 'false'));
 171                         $objWriter->writeAttribute('lockStructure',     ($pPHPExcel->getSecurity()->getLockStructure() ? 'true' : 'false'));
 172                         $objWriter->writeAttribute('lockWindows',               ($pPHPExcel->getSecurity()->getLockWindows() ? 'true' : 'false'));
 173
 174                         if ($pPHPExcel->getSecurity()->getRevisionsPassword() != '') {
 175                                 $objWriter->writeAttribute('revisionsPassword', $pPHPExcel->getSecurity()->getRevisionsPassword());
 176                         }
 177
 178                         if ($pPHPExcel->getSecurity()->getWorkbookPassword() != '') {
 179                                 $objWriter->writeAttribute('workbookPassword',  $pPHPExcel->getSecurity()->getWorkbookPassword());
 180                         }
 181
 182                         $objWriter->endElement();
 183                 }
 184         }
 185
 186         /**
 187          * Write calcPr
 188          *
 189          * @param       PHPExcel_Shared_XMLWriter       $objWriter              XML Writer
 190          * @param       boolean                                         $recalcRequired Indicate whether formulas should be recalculated before writing
 191          * @throws      PHPExcel_Writer_Exception
 192          */
 193         private function _writeCalcPr(PHPExcel_Shared_XMLWriter $objWriter = null, $recalcRequired = TRUE)
 194         {
 195                 $objWriter->startElement('calcPr');
 196
 197                 //      Set the calcid to a higher value than Excel itself will use, otherwise Excel will always recalc
 198         //  If MS Excel does do a recalc, then users opening a file in MS Excel will be prompted to save on exit
 199         //     because the file has changed
 200                 $objWriter->writeAttribute('calcId',                    '999999');
 201                 $objWriter->writeAttribute('calcMode',                  'auto');
 202                 //      fullCalcOnLoad isn't needed if we've recalculating for the save
 203                 $objWriter->writeAttribute('calcCompleted',     ($recalcRequired) ? 1 : 0);
 204                 $objWriter->writeAttribute('fullCalcOnLoad',    ($recalcRequired) ? 0 : 1);
 205
 206                 $objWriter->endElement();
 207         }
 208
 209         /**
 210          * Write sheets
 211          *
 212          * @param       PHPExcel_Shared_XMLWriter       $objWriter              XML Writer
 213          * @param       PHPExcel                                        $pPHPExcel
 214          * @throws      PHPExcel_Writer_Exception
 215          */
 216         private function _writeSheets(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel $pPHPExcel = null)
 217         {
 218                 // Write sheets
 219                 $objWriter->startElement('sheets');
 220                 $sheetCount = $pPHPExcel->getSheetCount();
 221                 for ($i = 0; $i < $sheetCount; ++$i) {
 222                         // sheet
 223                         $this->_writeSheet(
 224                                 $objWriter,
 225                                 $pPHPExcel->getSheet($i)->getTitle(),
 226                                 ($i + 1),
 227                                 ($i + 1 + 3),
 228                                 $pPHPExcel->getSheet($i)->getSheetState()
 229                         );
 230                 }
 231
 232                 $objWriter->endElement();
 233         }
 234
 235         /**
 236          * Write sheet
 237          *
 238          * @param       PHPExcel_Shared_XMLWriter       $objWriter              XML Writer
 239          * @param       string                                          $pSheetname             Sheet name
 240          * @param       int                                                     $pSheetId                       Sheet id
 241          * @param       int                                                     $pRelId                         Relationship ID
 242          * @param   string                      $sheetState         Sheet state (visible, hidden, veryHidden)
 243          * @throws      PHPExcel_Writer_Exception
 244          */
 245         private function _writeSheet(PHPExcel_Shared_XMLWriter $objWriter = null, $pSheetname = '', $pSheetId = 1, $pRelId = 1, $sheetState = 'visible')
 246         {
 247                 if ($pSheetname != '') {
 248                         // Write sheet
 249                         $objWriter->startElement('sheet');
 250                         $objWriter->writeAttribute('name',              $pSheetname);
 251                         $objWriter->writeAttribute('sheetId',   $pSheetId);
 252                         if ($sheetState != 'visible' && $sheetState != '') {
 253                                 $objWriter->writeAttribute('state', $sheetState);
 254                         }
 255                         $objWriter->writeAttribute('r:id',              'rId' . $pRelId);
 256                         $objWriter->endElement();
 257                 } else {
 258                         throw new PHPExcel_Writer_Exception("Invalid parameters passed.");
 259                 }
 260         }
 261
 262         /**
 263          * Write Defined Names
 264          *
 265          * @param       PHPExcel_Shared_XMLWriter       $objWriter              XML Writer
 266          * @param       PHPExcel                                        $pPHPExcel
 267          * @throws      PHPExcel_Writer_Exception
 268          */
 269         private function _writeDefinedNames(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel $pPHPExcel = null)
 270         {
 271                 // Write defined names
 272                 $objWriter->startElement('definedNames');
 273
 274                 // Named ranges
 275                 if (count($pPHPExcel->getNamedRanges()) > 0) {
 276                         // Named ranges
 277                         $this->_writeNamedRanges($objWriter, $pPHPExcel);
 278                 }
 279
 280                 // Other defined names
 281                 $sheetCount = $pPHPExcel->getSheetCount();
 282                 for ($i = 0; $i < $sheetCount; ++$i) {
 283                         // definedName for autoFilter
 284                         $this->_writeDefinedNameForAutofilter($objWriter, $pPHPExcel->getSheet($i), $i);
 285
 286                         // definedName for Print_Titles
 287                         $this->_writeDefinedNameForPrintTitles($objWriter, $pPHPExcel->getSheet($i), $i);
 288
 289                         // definedName for Print_Area
 290                         $this->_writeDefinedNameForPrintArea($objWriter, $pPHPExcel->getSheet($i), $i);
 291                 }
 292
 293                 $objWriter->endElement();
 294         }
 295
 296         /**
 297          * Write named ranges
 298          *
 299          * @param       PHPExcel_Shared_XMLWriter       $objWriter              XML Writer
 300          * @param       PHPExcel                                        $pPHPExcel
 301          * @throws      PHPExcel_Writer_Exception
 302          */
 303         private function _writeNamedRanges(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel $pPHPExcel)
 304         {
 305                 // Loop named ranges
 306                 $namedRanges = $pPHPExcel->getNamedRanges();
 307                 foreach ($namedRanges as $namedRange) {
 308                         $this->_writeDefinedNameForNamedRange($objWriter, $namedRange);
 309                 }
 310         }
 311
 312         /**
 313          * Write Defined Name for named range
 314          *
 315          * @param       PHPExcel_Shared_XMLWriter       $objWriter              XML Writer
 316          * @param       PHPExcel_NamedRange                     $pNamedRange
 317          * @throws      PHPExcel_Writer_Exception
 318          */
 319         private function _writeDefinedNameForNamedRange(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_NamedRange $pNamedRange)
 320         {
 321                 // definedName for named range
 322                 $objWriter->startElement('definedName');
 323                 $objWriter->writeAttribute('name',                      $pNamedRange->getName());
 324                 if ($pNamedRange->getLocalOnly()) {
 325                         $objWriter->writeAttribute('localSheetId',      $pNamedRange->getScope()->getParent()->getIndex($pNamedRange->getScope()));
 326                 }
 327
 328                 // Create absolute coordinate and write as raw text
 329                 $range = PHPExcel_Cell::splitRange($pNamedRange->getRange());
 330                 for ($i = 0; $i < count($range); $i++) {
 331                         $range[$i][0] = '\'' . str_replace("'", "''", $pNamedRange->getWorksheet()->getTitle()) . '\'!' . PHPExcel_Cell::absoluteReference($range[$i][0]);
 332                         if (isset($range[$i][1])) {
 333                                 $range[$i][1] = PHPExcel_Cell::absoluteReference($range[$i][1]);
 334                         }
 335                 }
 336                 $range = PHPExcel_Cell::buildRange($range);
 337
 338                 $objWriter->writeRawData($range);
 339
 340                 $objWriter->endElement();
 341         }
 342
 343         /**
 344          * Write Defined Name for autoFilter
 345          *
 346          * @param       PHPExcel_Shared_XMLWriter       $objWriter              XML Writer
 347          * @param       PHPExcel_Worksheet                      $pSheet
 348          * @param       int                                                     $pSheetId
 349          * @throws      PHPExcel_Writer_Exception
 350          */
 351         private function _writeDefinedNameForAutofilter(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null, $pSheetId = 0)
 352         {
 353                 // definedName for autoFilter
 354                 $autoFilterRange = $pSheet->getAutoFilter()->getRange();
 355                 if (!empty($autoFilterRange)) {
 356                         $objWriter->startElement('definedName');
 357                         $objWriter->writeAttribute('name',                      '_xlnm._FilterDatabase');
 358                         $objWriter->writeAttribute('localSheetId',      $pSheetId);
 359                         $objWriter->writeAttribute('hidden',            '1');
 360
 361                         // Create absolute coordinate and write as raw text
 362                         $range = PHPExcel_Cell::splitRange($autoFilterRange);
 363                         $range = $range[0];
 364                         //      Strip any worksheet ref so we can make the cell ref absolute
 365                         if (strpos($range[0],'!') !== false) {
 366                                 list($ws,$range[0]) = explode('!',$range[0]);
 367                         }
 368
 369                         $range[0] = PHPExcel_Cell::absoluteCoordinate($range[0]);
 370                         $range[1] = PHPExcel_Cell::absoluteCoordinate($range[1]);
 371                         $range = implode(':', $range);
 372
 373                         $objWriter->writeRawData('\'' . str_replace("'", "''", $pSheet->getTitle()) . '\'!' . $range);
 374
 375                         $objWriter->endElement();
 376                 }
 377         }
 378
 379         /**
 380          * Write Defined Name for PrintTitles
 381          *
 382          * @param       PHPExcel_Shared_XMLWriter       $objWriter              XML Writer
 383          * @param       PHPExcel_Worksheet                      $pSheet
 384          * @param       int                                                     $pSheetId
 385          * @throws      PHPExcel_Writer_Exception
 386          */
 387         private function _writeDefinedNameForPrintTitles(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null, $pSheetId = 0)
 388         {
 389                 // definedName for PrintTitles
 390                 if ($pSheet->getPageSetup()->isColumnsToRepeatAtLeftSet() || $pSheet->getPageSetup()->isRowsToRepeatAtTopSet()) {
 391                         $objWriter->startElement('definedName');
 392                         $objWriter->writeAttribute('name',                      '_xlnm.Print_Titles');
 393                         $objWriter->writeAttribute('localSheetId',      $pSheetId);
 394
 395                         // Setting string
 396                         $settingString = '';
 397
 398                         // Columns to repeat
 399                         if ($pSheet->getPageSetup()->isColumnsToRepeatAtLeftSet()) {
 400                                 $repeat = $pSheet->getPageSetup()->getColumnsToRepeatAtLeft();
 401
 402                                 $settingString .= '\'' . str_replace("'", "''", $pSheet->getTitle()) . '\'!$' . $repeat[0] . ':$' . $repeat[1];
 403                         }
 404
 405                         // Rows to repeat
 406                         if ($pSheet->getPageSetup()->isRowsToRepeatAtTopSet()) {
 407                                 if ($pSheet->getPageSetup()->isColumnsToRepeatAtLeftSet()) {
 408                                         $settingString .= ',';
 409                                 }
 410
 411                                 $repeat = $pSheet->getPageSetup()->getRowsToRepeatAtTop();
 412
 413                                 $settingString .= '\'' . str_replace("'", "''", $pSheet->getTitle()) . '\'!$' . $repeat[0] . ':$' . $repeat[1];
 414                         }
 415
 416                         $objWriter->writeRawData($settingString);
 417
 418                         $objWriter->endElement();
 419                 }
 420         }
 421
 422         /**
 423          * Write Defined Name for PrintTitles
 424          *
 425          * @param       PHPExcel_Shared_XMLWriter       $objWriter              XML Writer
 426          * @param       PHPExcel_Worksheet                      $pSheet
 427          * @param       int                                                     $pSheetId
 428          * @throws      PHPExcel_Writer_Exception
 429          */
 430         private function _writeDefinedNameForPrintArea(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null, $pSheetId = 0)
 431         {
 432                 // definedName for PrintArea
 433                 if ($pSheet->getPageSetup()->isPrintAreaSet()) {
 434                         $objWriter->startElement('definedName');
 435                         $objWriter->writeAttribute('name',                      '_xlnm.Print_Area');
 436                         $objWriter->writeAttribute('localSheetId',      $pSheetId);
 437
 438                         // Setting string
 439                         $settingString = '';
 440
 441                         // Print area
 442                         $printArea = PHPExcel_Cell::splitRange($pSheet->getPageSetup()->getPrintArea());
 443
 444                         $chunks = array();
 445                         foreach ($printArea as $printAreaRect) {
 446                                 $printAreaRect[0] = PHPExcel_Cell::absoluteReference($printAreaRect[0]);
 447                                 $printAreaRect[1] = PHPExcel_Cell::absoluteReference($printAreaRect[1]);
 448                                 $chunks[] = '\'' . str_replace("'", "''", $pSheet->getTitle()) . '\'!' . implode(':', $printAreaRect);
 449                         }
 450
 451                         $objWriter->writeRawData(implode(',', $chunks));
 452
 453                         $objWriter->endElement();
 454                 }
 455         }
 456 }