[Summary view] [Print] [Text view]

   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  }