Subversion Repositories eFlore/Applications.cel

Rev

Details | Last modification | View Log | RSS feed

Rev Author Line No. Line
2388 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 
}