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    1.8.0, 2014-03-02
 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: }
457: