[Summary view] [Print] [Text view]

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