Subversion Repositories eFlore/Applications.cel

Rev

Details | Last modification | View Log | RSS feed

Rev Author Line No. Line
2390 jpm 1
<?php
2
/**
3
 * PHPExcel
4
 *
5
 * Copyright (c) 2006 - 2013 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 - 2013 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 - 2013 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
		$objWriter->writeAttribute('calcId', 			'124519');
198
		$objWriter->writeAttribute('calcMode', 			'auto');
199
		//	fullCalcOnLoad isn't needed if we've recalculating for the save
200
		$objWriter->writeAttribute('fullCalcOnLoad', 	($recalcRequired) ? '0' : '1');
201
 
202
		$objWriter->endElement();
203
	}
204
 
205
	/**
206
	 * Write sheets
207
	 *
208
	 * @param 	PHPExcel_Shared_XMLWriter 	$objWriter 		XML Writer
209
	 * @param 	PHPExcel					$pPHPExcel
210
	 * @throws 	PHPExcel_Writer_Exception
211
	 */
212
	private function _writeSheets(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel $pPHPExcel = null)
213
	{
214
		// Write sheets
215
		$objWriter->startElement('sheets');
216
		$sheetCount = $pPHPExcel->getSheetCount();
217
		for ($i = 0; $i < $sheetCount; ++$i) {
218
			// sheet
219
			$this->_writeSheet(
220
				$objWriter,
221
				$pPHPExcel->getSheet($i)->getTitle(),
222
				($i + 1),
223
				($i + 1 + 3),
224
				$pPHPExcel->getSheet($i)->getSheetState()
225
			);
226
		}
227
 
228
		$objWriter->endElement();
229
	}
230
 
231
	/**
232
	 * Write sheet
233
	 *
234
	 * @param 	PHPExcel_Shared_XMLWriter 	$objWriter 		XML Writer
235
	 * @param 	string 						$pSheetname 		Sheet name
236
	 * @param 	int							$pSheetId	 		Sheet id
237
	 * @param 	int							$pRelId				Relationship ID
238
	 * @param   string                      $sheetState         Sheet state (visible, hidden, veryHidden)
239
	 * @throws 	PHPExcel_Writer_Exception
240
	 */
241
	private function _writeSheet(PHPExcel_Shared_XMLWriter $objWriter = null, $pSheetname = '', $pSheetId = 1, $pRelId = 1, $sheetState = 'visible')
242
	{
243
		if ($pSheetname != '') {
244
			// Write sheet
245
			$objWriter->startElement('sheet');
246
			$objWriter->writeAttribute('name', 		$pSheetname);
247
			$objWriter->writeAttribute('sheetId', 	$pSheetId);
248
			if ($sheetState != 'visible' && $sheetState != '') {
249
				$objWriter->writeAttribute('state', $sheetState);
250
			}
251
			$objWriter->writeAttribute('r:id', 		'rId' . $pRelId);
252
			$objWriter->endElement();
253
		} else {
254
			throw new PHPExcel_Writer_Exception("Invalid parameters passed.");
255
		}
256
	}
257
 
258
	/**
259
	 * Write Defined Names
260
	 *
261
	 * @param 	PHPExcel_Shared_XMLWriter	$objWriter 		XML Writer
262
	 * @param 	PHPExcel					$pPHPExcel
263
	 * @throws 	PHPExcel_Writer_Exception
264
	 */
265
	private function _writeDefinedNames(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel $pPHPExcel = null)
266
	{
267
		// Write defined names
268
		$objWriter->startElement('definedNames');
269
 
270
		// Named ranges
271
		if (count($pPHPExcel->getNamedRanges()) > 0) {
272
			// Named ranges
273
			$this->_writeNamedRanges($objWriter, $pPHPExcel);
274
		}
275
 
276
		// Other defined names
277
		$sheetCount = $pPHPExcel->getSheetCount();
278
		for ($i = 0; $i < $sheetCount; ++$i) {
279
			// definedName for autoFilter
280
			$this->_writeDefinedNameForAutofilter($objWriter, $pPHPExcel->getSheet($i), $i);
281
 
282
			// definedName for Print_Titles
283
			$this->_writeDefinedNameForPrintTitles($objWriter, $pPHPExcel->getSheet($i), $i);
284
 
285
			// definedName for Print_Area
286
			$this->_writeDefinedNameForPrintArea($objWriter, $pPHPExcel->getSheet($i), $i);
287
		}
288
 
289
		$objWriter->endElement();
290
	}
291
 
292
	/**
293
	 * Write named ranges
294
	 *
295
	 * @param 	PHPExcel_Shared_XMLWriter	$objWriter 		XML Writer
296
	 * @param 	PHPExcel					$pPHPExcel
297
	 * @throws 	PHPExcel_Writer_Exception
298
	 */
299
	private function _writeNamedRanges(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel $pPHPExcel)
300
	{
301
		// Loop named ranges
302
		$namedRanges = $pPHPExcel->getNamedRanges();
303
		foreach ($namedRanges as $namedRange) {
304
			$this->_writeDefinedNameForNamedRange($objWriter, $namedRange);
305
		}
306
	}
307
 
308
	/**
309
	 * Write Defined Name for named range
310
	 *
311
	 * @param 	PHPExcel_Shared_XMLWriter	$objWriter 		XML Writer
312
	 * @param 	PHPExcel_NamedRange			$pNamedRange
313
	 * @throws 	PHPExcel_Writer_Exception
314
	 */
315
	private function _writeDefinedNameForNamedRange(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_NamedRange $pNamedRange)
316
	{
317
		// definedName for named range
318
		$objWriter->startElement('definedName');
319
		$objWriter->writeAttribute('name',			$pNamedRange->getName());
320
		if ($pNamedRange->getLocalOnly()) {
321
			$objWriter->writeAttribute('localSheetId',	$pNamedRange->getScope()->getParent()->getIndex($pNamedRange->getScope()));
322
		}
323
 
324
		// Create absolute coordinate and write as raw text
325
		$range = PHPExcel_Cell::splitRange($pNamedRange->getRange());
326
		for ($i = 0; $i < count($range); $i++) {
327
			$range[$i][0] = '\'' . str_replace("'", "''", $pNamedRange->getWorksheet()->getTitle()) . '\'!' . PHPExcel_Cell::absoluteReference($range[$i][0]);
328
			if (isset($range[$i][1])) {
329
				$range[$i][1] = PHPExcel_Cell::absoluteReference($range[$i][1]);
330
			}
331
		}
332
		$range = PHPExcel_Cell::buildRange($range);
333
 
334
		$objWriter->writeRawData($range);
335
 
336
		$objWriter->endElement();
337
	}
338
 
339
	/**
340
	 * Write Defined Name for autoFilter
341
	 *
342
	 * @param 	PHPExcel_Shared_XMLWriter	$objWriter 		XML Writer
343
	 * @param 	PHPExcel_Worksheet			$pSheet
344
	 * @param 	int							$pSheetId
345
	 * @throws 	PHPExcel_Writer_Exception
346
	 */
347
	private function _writeDefinedNameForAutofilter(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null, $pSheetId = 0)
348
	{
349
		// definedName for autoFilter
350
		$autoFilterRange = $pSheet->getAutoFilter()->getRange();
351
		if (!empty($autoFilterRange)) {
352
			$objWriter->startElement('definedName');
353
			$objWriter->writeAttribute('name',			'_xlnm._FilterDatabase');
354
			$objWriter->writeAttribute('localSheetId',	$pSheetId);
355
			$objWriter->writeAttribute('hidden',		'1');
356
 
357
			// Create absolute coordinate and write as raw text
358
			$range = PHPExcel_Cell::splitRange($autoFilterRange);
359
			$range = $range[0];
360
			//	Strip any worksheet ref so we can make the cell ref absolute
361
			if (strpos($range[0],'!') !== false) {
362
				list($ws,$range[0]) = explode('!',$range[0]);
363
			}
364
 
365
			$range[0] = PHPExcel_Cell::absoluteCoordinate($range[0]);
366
			$range[1] = PHPExcel_Cell::absoluteCoordinate($range[1]);
367
			$range = implode(':', $range);
368
 
369
			$objWriter->writeRawData('\'' . str_replace("'", "''", $pSheet->getTitle()) . '\'!' . $range);
370
 
371
			$objWriter->endElement();
372
		}
373
	}
374
 
375
	/**
376
	 * Write Defined Name for PrintTitles
377
	 *
378
	 * @param 	PHPExcel_Shared_XMLWriter	$objWriter 		XML Writer
379
	 * @param 	PHPExcel_Worksheet			$pSheet
380
	 * @param 	int							$pSheetId
381
	 * @throws 	PHPExcel_Writer_Exception
382
	 */
383
	private function _writeDefinedNameForPrintTitles(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null, $pSheetId = 0)
384
	{
385
		// definedName for PrintTitles
386
		if ($pSheet->getPageSetup()->isColumnsToRepeatAtLeftSet() || $pSheet->getPageSetup()->isRowsToRepeatAtTopSet()) {
387
			$objWriter->startElement('definedName');
388
			$objWriter->writeAttribute('name',			'_xlnm.Print_Titles');
389
			$objWriter->writeAttribute('localSheetId',	$pSheetId);
390
 
391
			// Setting string
392
			$settingString = '';
393
 
394
			// Columns to repeat
395
			if ($pSheet->getPageSetup()->isColumnsToRepeatAtLeftSet()) {
396
				$repeat = $pSheet->getPageSetup()->getColumnsToRepeatAtLeft();
397
 
398
				$settingString .= '\'' . str_replace("'", "''", $pSheet->getTitle()) . '\'!$' . $repeat[0] . ':$' . $repeat[1];
399
			}
400
 
401
			// Rows to repeat
402
			if ($pSheet->getPageSetup()->isRowsToRepeatAtTopSet()) {
403
				if ($pSheet->getPageSetup()->isColumnsToRepeatAtLeftSet()) {
404
					$settingString .= ',';
405
				}
406
 
407
				$repeat = $pSheet->getPageSetup()->getRowsToRepeatAtTop();
408
 
409
				$settingString .= '\'' . str_replace("'", "''", $pSheet->getTitle()) . '\'!$' . $repeat[0] . ':$' . $repeat[1];
410
			}
411
 
412
			$objWriter->writeRawData($settingString);
413
 
414
			$objWriter->endElement();
415
		}
416
	}
417
 
418
	/**
419
	 * Write Defined Name for PrintTitles
420
	 *
421
	 * @param 	PHPExcel_Shared_XMLWriter	$objWriter 		XML Writer
422
	 * @param 	PHPExcel_Worksheet			$pSheet
423
	 * @param 	int							$pSheetId
424
	 * @throws 	PHPExcel_Writer_Exception
425
	 */
426
	private function _writeDefinedNameForPrintArea(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null, $pSheetId = 0)
427
	{
428
		// definedName for PrintArea
429
		if ($pSheet->getPageSetup()->isPrintAreaSet()) {
430
			$objWriter->startElement('definedName');
431
			$objWriter->writeAttribute('name',			'_xlnm.Print_Area');
432
			$objWriter->writeAttribute('localSheetId',	$pSheetId);
433
 
434
			// Setting string
435
			$settingString = '';
436
 
437
			// Print area
438
			$printArea = PHPExcel_Cell::splitRange($pSheet->getPageSetup()->getPrintArea());
439
 
440
			$chunks = array();
441
			foreach ($printArea as $printAreaRect) {
442
				$printAreaRect[0] = PHPExcel_Cell::absoluteReference($printAreaRect[0]);
443
				$printAreaRect[1] = PHPExcel_Cell::absoluteReference($printAreaRect[1]);
444
				$chunks[] = '\'' . str_replace("'", "''", $pSheet->getTitle()) . '\'!' . implode(':', $printAreaRect);
445
			}
446
 
447
			$objWriter->writeRawData(implode(',', $chunks));
448
 
449
			$objWriter->endElement();
450
		}
451
	}
452
}