Context Navigation

← Previous Revision
Latest Revision
Next Revision →
Blame
Revision Log

Excel2007.php @ 1

Last change on this file since 1 was 1, checked in by dungnv, 11 years ago

File size: 90.5 KB

Line
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_Reader
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	/** PHPExcel root directory */
30	if (!defined('PHPEXCEL_ROOT')) {
31	/**
32	* @ignore
33	*/
34	define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../../');
35	require(PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php');
36	}
37
38	/**
39	* PHPExcel_Reader_Excel2007
40	*
41	* @category PHPExcel
42	* @package PHPExcel_Reader
43	* @copyright Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)
44	*/
45	class PHPExcel_Reader_Excel2007 extends PHPExcel_Reader_Abstract implements PHPExcel_Reader_IReader
46	{
47	/**
48	* PHPExcel_ReferenceHelper instance
49	*
50	* @var PHPExcel_ReferenceHelper
51	*/
52	private $_referenceHelper = NULL;
53
54	/**
55	* PHPExcel_Reader_Excel2007_Theme instance
56	*
57	* @var PHPExcel_Reader_Excel2007_Theme
58	*/
59	private static $_theme = NULL;
60
61
62	/**
63	* Create a new PHPExcel_Reader_Excel2007 instance
64	*/
65	public function __construct() {
66	$this->_readFilter = new PHPExcel_Reader_DefaultReadFilter();
67	$this->_referenceHelper = PHPExcel_ReferenceHelper::getInstance();
68	}
69
70
71	/**
72	* Can the current PHPExcel_Reader_IReader read the file?
73	*
74	* @param string $pFilename
75	* @return boolean
76	* @throws PHPExcel_Reader_Exception
77	*/
78	public function canRead($pFilename)
79	{
80	// Check if file exists
81	if (!file_exists($pFilename)) {
82	throw new PHPExcel_Reader_Exception("Could not open " . $pFilename . " for reading! File does not exist.");
83	}
84
85	$zipClass = PHPExcel_Settings::getZipClass();
86
87	// Check if zip class exists
88	// if (!class_exists($zipClass, FALSE)) {
89	// throw new PHPExcel_Reader_Exception($zipClass . " library is not enabled");
90	// }
91
92	$xl = false;
93	// Load file
94	$zip = new $zipClass;
95	if ($zip->open($pFilename) === true) {
96	// check if it is an OOXML archive
97	$rels = simplexml_load_string($this->_getFromZipArchive($zip, "_rels/.rels"), 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions());
98	if ($rels !== false) {
99	foreach ($rels->Relationship as $rel) {
100	switch ($rel["Type"]) {
101	case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument":
102	if (basename($rel["Target"]) == 'workbook.xml') {
103	$xl = true;
104	}
105	break;
106
107	}
108	}
109	}
110	$zip->close();
111	}
112
113	return $xl;
114	}
115
116
117	/**
118	* Reads names of the worksheets from a file, without parsing the whole file to a PHPExcel object
119	*
120	* @param string $pFilename
121	* @throws PHPExcel_Reader_Exception
122	*/
123	public function listWorksheetNames($pFilename)
124	{
125	// Check if file exists
126	if (!file_exists($pFilename)) {
127	throw new PHPExcel_Reader_Exception("Could not open " . $pFilename . " for reading! File does not exist.");
128	}
129
130	$worksheetNames = array();
131
132	$zipClass = PHPExcel_Settings::getZipClass();
133
134	$zip = new $zipClass;
135	$zip->open($pFilename);
136
137	// The files we're looking at here are small enough that simpleXML is more efficient than XMLReader
138	$rels = simplexml_load_string(
139	$this->_getFromZipArchive($zip, "_rels/.rels", 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions())
140	); //~ http://schemas.openxmlformats.org/package/2006/relationships");
141	foreach ($rels->Relationship as $rel) {
142	switch ($rel["Type"]) {
143	case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument":
144	$xmlWorkbook = simplexml_load_string(
145	$this->_getFromZipArchive($zip, "{$rel['Target']}", 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions())
146	); //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
147
148	if ($xmlWorkbook->sheets) {
149	foreach ($xmlWorkbook->sheets->sheet as $eleSheet) {
150	// Check if sheet should be skipped
151	$worksheetNames[] = (string) $eleSheet["name"];
152	}
153	}
154	}
155	}
156
157	$zip->close();
158
159	return $worksheetNames;
160	}
161
162
163	/**
164	* Return worksheet info (Name, Last Column Letter, Last Column Index, Total Rows, Total Columns)
165	*
166	* @param string $pFilename
167	* @throws PHPExcel_Reader_Exception
168	*/
169	public function listWorksheetInfo($pFilename)
170	{
171	// Check if file exists
172	if (!file_exists($pFilename)) {
173	throw new PHPExcel_Reader_Exception("Could not open " . $pFilename . " for reading! File does not exist.");
174	}
175
176	$worksheetInfo = array();
177
178	$zipClass = PHPExcel_Settings::getZipClass();
179
180	$zip = new $zipClass;
181	$zip->open($pFilename);
182
183	$rels = simplexml_load_string($this->_getFromZipArchive($zip, "_rels/.rels"), 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions()); //~ http://schemas.openxmlformats.org/package/2006/relationships");
184	foreach ($rels->Relationship as $rel) {
185	if ($rel["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument") {
186	$dir = dirname($rel["Target"]);
187	$relsWorkbook = simplexml_load_string($this->_getFromZipArchive($zip, "$dir/_rels/" . basename($rel["Target"]) . ".rels"), 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions()); //~ http://schemas.openxmlformats.org/package/2006/relationships");
188	$relsWorkbook->registerXPathNamespace("rel", "http://schemas.openxmlformats.org/package/2006/relationships");
189
190	$worksheets = array();
191	foreach ($relsWorkbook->Relationship as $ele) {
192	if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet") {
193	$worksheets[(string) $ele["Id"]] = $ele["Target"];
194	}
195	}
196
197	$xmlWorkbook = simplexml_load_string($this->_getFromZipArchive($zip, "{$rel['Target']}"), 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions()); //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
198	if ($xmlWorkbook->sheets) {
199	$dir = dirname($rel["Target"]);
200	foreach ($xmlWorkbook->sheets->sheet as $eleSheet) {
201	$tmpInfo = array(
202	'worksheetName' => (string) $eleSheet["name"],
203	'lastColumnLetter' => 'A',
204	'lastColumnIndex' => 0,
205	'totalRows' => 0,
206	'totalColumns' => 0,
207	);
208
209	$fileWorksheet = $worksheets[(string) self::array_item($eleSheet->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "id")];
210
211	$xml = new XMLReader();
212	$res = $xml->open('zip://'.PHPExcel_Shared_File::realpath($pFilename).'#'."$dir/$fileWorksheet", null, PHPExcel_Settings::getLibXmlLoaderOptions());
213	$xml->setParserProperty(2,true);
214
215	$currCells = 0;
216	while ($xml->read()) {
217	if ($xml->name == 'row' && $xml->nodeType == XMLReader::ELEMENT) {
218	$row = $xml->getAttribute('r');
219	$tmpInfo['totalRows'] = $row;
220	$tmpInfo['totalColumns'] = max($tmpInfo['totalColumns'],$currCells);
221	$currCells = 0;
222	} elseif ($xml->name == 'c' && $xml->nodeType == XMLReader::ELEMENT) {
223	$currCells++;
224	}
225	}
226	$tmpInfo['totalColumns'] = max($tmpInfo['totalColumns'],$currCells);
227	$xml->close();
228
229	$tmpInfo['lastColumnIndex'] = $tmpInfo['totalColumns'] - 1;
230	$tmpInfo['lastColumnLetter'] = PHPExcel_Cell::stringFromColumnIndex($tmpInfo['lastColumnIndex']);
231
232	$worksheetInfo[] = $tmpInfo;
233	}
234	}
235	}
236	}
237
238	$zip->close();
239
240	return $worksheetInfo;
241	}
242
243
244	private static function _castToBool($c) {
245	// echo 'Initial Cast to Boolean', PHP_EOL;
246	$value = isset($c->v) ? (string) $c->v : NULL;
247	if ($value == '0') {
248	return FALSE;
249	} elseif ($value == '1') {
250	return TRUE;
251	} else {
252	return (bool)$c->v;
253	}
254	return $value;
255	} // function _castToBool()
256
257
258	private static function _castToError($c) {
259	// echo 'Initial Cast to Error', PHP_EOL;
260	return isset($c->v) ? (string) $c->v : NULL;
261	} // function _castToError()
262
263
264	private static function _castToString($c) {
265	// echo 'Initial Cast to String, PHP_EOL;
266	return isset($c->v) ? (string) $c->v : NULL;
267	} // function _castToString()
268
269
270	private function _castToFormula($c,$r,&$cellDataType,&$value,&$calculatedValue,&$sharedFormulas,$castBaseType) {
271	// echo 'Formula', PHP_EOL;
272	// echo '$c->f is ', $c->f, PHP_EOL;
273	$cellDataType = 'f';
274	$value = "={$c->f}";
275	$calculatedValue = self::$castBaseType($c);
276
277	// Shared formula?
278	if (isset($c->f['t']) && strtolower((string)$c->f['t']) == 'shared') {
279	// echo 'SHARED FORMULA', PHP_EOL;
280	$instance = (string)$c->f['si'];
281
282	// echo 'Instance ID = ', $instance, PHP_EOL;
283	//
284	// echo 'Shared Formula Array:', PHP_EOL;
285	// print_r($sharedFormulas);
286	if (!isset($sharedFormulas[(string)$c->f['si']])) {
287	// echo 'SETTING NEW SHARED FORMULA', PHP_EOL;
288	// echo 'Master is ', $r, PHP_EOL;
289	// echo 'Formula is ', $value, PHP_EOL;
290	$sharedFormulas[$instance] = array( 'master' => $r,
291	'formula' => $value
292	);
293	// echo 'New Shared Formula Array:', PHP_EOL;
294	// print_r($sharedFormulas);
295	} else {
296	// echo 'GETTING SHARED FORMULA', PHP_EOL;
297	// echo 'Master is ', $sharedFormulas[$instance]['master'], PHP_EOL;
298	// echo 'Formula is ', $sharedFormulas[$instance]['formula'], PHP_EOL;
299	$master = PHPExcel_Cell::coordinateFromString($sharedFormulas[$instance]['master']);
300	$current = PHPExcel_Cell::coordinateFromString($r);
301
302	$difference = array(0, 0);
303	$difference[0] = PHPExcel_Cell::columnIndexFromString($current[0]) - PHPExcel_Cell::columnIndexFromString($master[0]);
304	$difference[1] = $current[1] - $master[1];
305
306	$value = $this->_referenceHelper->updateFormulaReferences( $sharedFormulas[$instance]['formula'],
307	'A1',
308	$difference[0],
309	$difference[1]
310	);
311	// echo 'Adjusted Formula is ', $value, PHP_EOL;
312	}
313	}
314	}
315
316
317	public function _getFromZipArchive($archive, $fileName = '')
318	{
319	// Root-relative paths
320	if (strpos($fileName, '//') !== false)
321	{
322	$fileName = substr($fileName, strpos($fileName, '//') + 1);
323	}
324	$fileName = PHPExcel_Shared_File::realpath($fileName);
325
326	// Apache POI fixes
327	$contents = $archive->getFromName($fileName);
328	if ($contents === false)
329	{
330	$contents = $archive->getFromName(substr($fileName, 1));
331	}
332
333	return $contents;
334	}
335
336
337	/**
338	* Loads PHPExcel from file
339	*
340	* @param string $pFilename
341	* @throws PHPExcel_Reader_Exception
342	*/
343	public function load($pFilename)
344	{
345	// Check if file exists
346	if (!file_exists($pFilename)) {
347	throw new PHPExcel_Reader_Exception("Could not open " . $pFilename . " for reading! File does not exist.");
348	}
349
350	// Initialisations
351	$excel = new PHPExcel;
352	$excel->removeSheetByIndex(0);
353	if (!$this->_readDataOnly) {
354	$excel->removeCellStyleXfByIndex(0); // remove the default style
355	$excel->removeCellXfByIndex(0); // remove the default style
356	}
357
358	$zipClass = PHPExcel_Settings::getZipClass();
359
360	$zip = new $zipClass;
361	$zip->open($pFilename);
362
363	// Read the theme first, because we need the colour scheme when reading the styles
364	$wbRels = simplexml_load_string($this->_getFromZipArchive($zip, "xl/_rels/workbook.xml.rels"), 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions()); //~ http://schemas.openxmlformats.org/package/2006/relationships");
365	foreach ($wbRels->Relationship as $rel) {
366	switch ($rel["Type"]) {
367	case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/theme":
368	$themeOrderArray = array('lt1','dk1','lt2','dk2');
369	$themeOrderAdditional = count($themeOrderArray);
370
371	$xmlTheme = simplexml_load_string($this->_getFromZipArchive($zip, "xl/{$rel['Target']}"), 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions());
372	if (is_object($xmlTheme)) {
373	$xmlThemeName = $xmlTheme->attributes();
374	$xmlTheme = $xmlTheme->children("http://schemas.openxmlformats.org/drawingml/2006/main");
375	$themeName = (string)$xmlThemeName['name'];
376
377	$colourScheme = $xmlTheme->themeElements->clrScheme->attributes();
378	$colourSchemeName = (string)$colourScheme['name'];
379	$colourScheme = $xmlTheme->themeElements->clrScheme->children("http://schemas.openxmlformats.org/drawingml/2006/main");
380
381	$themeColours = array();
382	foreach ($colourScheme as $k => $xmlColour) {
383	$themePos = array_search($k,$themeOrderArray);
384	if ($themePos === false) {
385	$themePos = $themeOrderAdditional++;
386	}
387	if (isset($xmlColour->sysClr)) {
388	$xmlColourData = $xmlColour->sysClr->attributes();
389	$themeColours[$themePos] = $xmlColourData['lastClr'];
390	} elseif (isset($xmlColour->srgbClr)) {
391	$xmlColourData = $xmlColour->srgbClr->attributes();
392	$themeColours[$themePos] = $xmlColourData['val'];
393	}
394	}
395	self::$_theme = new PHPExcel_Reader_Excel2007_Theme($themeName,$colourSchemeName,$themeColours);
396	}
397	break;
398	}
399	}
400
401	$rels = simplexml_load_string($this->_getFromZipArchive($zip, "_rels/.rels"), 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions()); //~ http://schemas.openxmlformats.org/package/2006/relationships");
402	foreach ($rels->Relationship as $rel) {
403	switch ($rel["Type"]) {
404	case "http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties":
405	$xmlCore = simplexml_load_string($this->_getFromZipArchive($zip, "{$rel['Target']}"), 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions());
406	if (is_object($xmlCore)) {
407	$xmlCore->registerXPathNamespace("dc", "http://purl.org/dc/elements/1.1/");
408	$xmlCore->registerXPathNamespace("dcterms", "http://purl.org/dc/terms/");
409	$xmlCore->registerXPathNamespace("cp", "http://schemas.openxmlformats.org/package/2006/metadata/core-properties");
410	$docProps = $excel->getProperties();
411	$docProps->setCreator((string) self::array_item($xmlCore->xpath("dc:creator")));
412	$docProps->setLastModifiedBy((string) self::array_item($xmlCore->xpath("cp:lastModifiedBy")));
413	$docProps->setCreated(strtotime(self::array_item($xmlCore->xpath("dcterms:created")))); //! respect xsi:type
414	$docProps->setModified(strtotime(self::array_item($xmlCore->xpath("dcterms:modified")))); //! respect xsi:type
415	$docProps->setTitle((string) self::array_item($xmlCore->xpath("dc:title")));
416	$docProps->setDescription((string) self::array_item($xmlCore->xpath("dc:description")));
417	$docProps->setSubject((string) self::array_item($xmlCore->xpath("dc:subject")));
418	$docProps->setKeywords((string) self::array_item($xmlCore->xpath("cp:keywords")));
419	$docProps->setCategory((string) self::array_item($xmlCore->xpath("cp:category")));
420	}
421	break;
422
423	case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties":
424	$xmlCore = simplexml_load_string($this->_getFromZipArchive($zip, "{$rel['Target']}"), 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions());
425	if (is_object($xmlCore)) {
426	$docProps = $excel->getProperties();
427	if (isset($xmlCore->Company))
428	$docProps->setCompany((string) $xmlCore->Company);
429	if (isset($xmlCore->Manager))
430	$docProps->setManager((string) $xmlCore->Manager);
431	}
432	break;
433
434	case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/custom-properties":
435	$xmlCore = simplexml_load_string($this->_getFromZipArchive($zip, "{$rel['Target']}"), 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions());
436	if (is_object($xmlCore)) {
437	$docProps = $excel->getProperties();
438	foreach ($xmlCore as $xmlProperty) {
439	$cellDataOfficeAttributes = $xmlProperty->attributes();
440	if (isset($cellDataOfficeAttributes['name'])) {
441	$propertyName = (string) $cellDataOfficeAttributes['name'];
442	$cellDataOfficeChildren = $xmlProperty->children('http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes');
443	$attributeType = $cellDataOfficeChildren->getName();
444	$attributeValue = (string) $cellDataOfficeChildren->{$attributeType};
445	$attributeValue = PHPExcel_DocumentProperties::convertProperty($attributeValue,$attributeType);
446	$attributeType = PHPExcel_DocumentProperties::convertPropertyType($attributeType);
447	$docProps->setCustomProperty($propertyName,$attributeValue,$attributeType);
448	}
449	}
450	}
451	break;
452	//Ribbon
453	case "http://schemas.microsoft.com/office/2006/relationships/ui/extensibility":
454	$customUI = $rel['Target'];
455	if(!is_null($customUI)){
456	$this->_readRibbon($excel, $customUI, $zip);
457	}
458	break;
459	case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument":
460	$dir = dirname($rel["Target"]);
461	$relsWorkbook = simplexml_load_string($this->_getFromZipArchive($zip, "$dir/_rels/" . basename($rel["Target"]) . ".rels"), 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions()); //~ http://schemas.openxmlformats.org/package/2006/relationships");
462	$relsWorkbook->registerXPathNamespace("rel", "http://schemas.openxmlformats.org/package/2006/relationships");
463
464	$sharedStrings = array();
465	$xpath = self::array_item($relsWorkbook->xpath("rel:Relationship[@Type='http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings']"));
466	$xmlStrings = simplexml_load_string($this->_getFromZipArchive($zip, "$dir/$xpath[Target]"), 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions()); //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
467	if (isset($xmlStrings) && isset($xmlStrings->si)) {
468	foreach ($xmlStrings->si as $val) {
469	if (isset($val->t)) {
470	$sharedStrings[] = PHPExcel_Shared_String::ControlCharacterOOXML2PHP( (string) $val->t );
471	} elseif (isset($val->r)) {
472	$sharedStrings[] = $this->_parseRichText($val);
473	}
474	}
475	}
476
477	$worksheets = array();
478	$macros = $customUI = NULL;
479	foreach ($relsWorkbook->Relationship as $ele) {
480	switch($ele['Type']){
481	case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet":
482	$worksheets[(string) $ele["Id"]] = $ele["Target"];
483	break;
484	// a vbaProject ? (: some macros)
485	case "http://schemas.microsoft.com/office/2006/relationships/vbaProject":
486	$macros = $ele["Target"];
487	break;
488	}
489	}
490
491	if(!is_null($macros)){
492	$macrosCode = $this->_getFromZipArchive($zip, 'xl/vbaProject.bin');//vbaProject.bin always in 'xl' dir and always named vbaProject.bin
493	if($macrosCode !== false){
494	$excel->setMacrosCode($macrosCode);
495	$excel->setHasMacros(true);
496	//short-circuit : not reading vbaProject.bin.rel to get Signature =>allways vbaProjectSignature.bin in 'xl' dir
497	$Certificate = $this->_getFromZipArchive($zip, 'xl/vbaProjectSignature.bin');
498	if($Certificate !== false)
499	$excel->setMacrosCertificate($Certificate);
500	}
501	}
502	$styles = array();
503	$cellStyles = array();
504	$xpath = self::array_item($relsWorkbook->xpath("rel:Relationship[@Type='http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles']"));
505	$xmlStyles = simplexml_load_string($this->_getFromZipArchive($zip, "$dir/$xpath[Target]"), 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions()); //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
506	$numFmts = null;
507	if ($xmlStyles && $xmlStyles->numFmts[0]) {
508	$numFmts = $xmlStyles->numFmts[0];
509	}
510	if (isset($numFmts) && ($numFmts !== NULL)) {
511	$numFmts->registerXPathNamespace("sml", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
512	}
513	if (!$this->_readDataOnly && $xmlStyles) {
514	foreach ($xmlStyles->cellXfs->xf as $xf) {
515	$numFmt = PHPExcel_Style_NumberFormat::FORMAT_GENERAL;
516
517	if ($xf["numFmtId"]) {
518	if (isset($numFmts)) {
519	$tmpNumFmt = self::array_item($numFmts->xpath("sml:numFmt[@numFmtId=$xf[numFmtId]]"));
520
521	if (isset($tmpNumFmt["formatCode"])) {
522	$numFmt = (string) $tmpNumFmt["formatCode"];
523	}
524	}
525
526	if ((int)$xf["numFmtId"] < 164) {
527	$numFmt = PHPExcel_Style_NumberFormat::builtInFormatCode((int)$xf["numFmtId"]);
528	}
529	}
530	$quotePrefix = false;
531	if (isset($xf["quotePrefix"])) {
532	$quotePrefix = (boolean) $xf["quotePrefix"];
533	}
534	//$numFmt = str_replace('mm', 'i', $numFmt);
535	//$numFmt = str_replace('h', 'H', $numFmt);
536
537	$style = (object) array(
538	"numFmt" => $numFmt,
539	"font" => $xmlStyles->fonts->font[intval($xf["fontId"])],
540	"fill" => $xmlStyles->fills->fill[intval($xf["fillId"])],
541	"border" => $xmlStyles->borders->border[intval($xf["borderId"])],
542	"alignment" => $xf->alignment,
543	"protection" => $xf->protection,
544	"quotePrefix" => $quotePrefix,
545	);
546	$styles[] = $style;
547
548	// add style to cellXf collection
549	$objStyle = new PHPExcel_Style;
550	self::_readStyle($objStyle, $style);
551	$excel->addCellXf($objStyle);
552	}
553
554	foreach ($xmlStyles->cellStyleXfs->xf as $xf) {
555	$numFmt = PHPExcel_Style_NumberFormat::FORMAT_GENERAL;
556	if ($numFmts && $xf["numFmtId"]) {
557	$tmpNumFmt = self::array_item($numFmts->xpath("sml:numFmt[@numFmtId=$xf[numFmtId]]"));
558	if (isset($tmpNumFmt["formatCode"])) {
559	$numFmt = (string) $tmpNumFmt["formatCode"];
560	} else if ((int)$xf["numFmtId"] < 165) {
561	$numFmt = PHPExcel_Style_NumberFormat::builtInFormatCode((int)$xf["numFmtId"]);
562	}
563	}
564
565	$cellStyle = (object) array(
566	"numFmt" => $numFmt,
567	"font" => $xmlStyles->fonts->font[intval($xf["fontId"])],
568	"fill" => $xmlStyles->fills->fill[intval($xf["fillId"])],
569	"border" => $xmlStyles->borders->border[intval($xf["borderId"])],
570	"alignment" => $xf->alignment,
571	"protection" => $xf->protection,
572	"quotePrefix" => $quotePrefix,
573	);
574	$cellStyles[] = $cellStyle;
575
576	// add style to cellStyleXf collection
577	$objStyle = new PHPExcel_Style;
578	self::_readStyle($objStyle, $cellStyle);
579	$excel->addCellStyleXf($objStyle);
580	}
581	}
582
583	$dxfs = array();
584	if (!$this->_readDataOnly && $xmlStyles) {
585	// Conditional Styles
586	if ($xmlStyles->dxfs) {
587	foreach ($xmlStyles->dxfs->dxf as $dxf) {
588	$style = new PHPExcel_Style(FALSE, TRUE);
589	self::_readStyle($style, $dxf);
590	$dxfs[] = $style;
591	}
592	}
593	// Cell Styles
594	if ($xmlStyles->cellStyles) {
595	foreach ($xmlStyles->cellStyles->cellStyle as $cellStyle) {
596	if (intval($cellStyle['builtinId']) == 0) {
597	if (isset($cellStyles[intval($cellStyle['xfId'])])) {
598	// Set default style
599	$style = new PHPExcel_Style;
600	self::_readStyle($style, $cellStyles[intval($cellStyle['xfId'])]);
601
602	// normal style, currently not using it for anything
603	}
604	}
605	}
606	}
607	}
608
609	$xmlWorkbook = simplexml_load_string($this->_getFromZipArchive($zip, "{$rel['Target']}"), 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions()); //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
610
611	// Set base date
612	if ($xmlWorkbook->workbookPr) {
613	PHPExcel_Shared_Date::setExcelCalendar(PHPExcel_Shared_Date::CALENDAR_WINDOWS_1900);
614	if (isset($xmlWorkbook->workbookPr['date1904'])) {
615	if (self::boolean((string) $xmlWorkbook->workbookPr['date1904'])) {
616	PHPExcel_Shared_Date::setExcelCalendar(PHPExcel_Shared_Date::CALENDAR_MAC_1904);
617	}
618	}
619	}
620
621	$sheetId = 0; // keep track of new sheet id in final workbook
622	$oldSheetId = -1; // keep track of old sheet id in final workbook
623	$countSkippedSheets = 0; // keep track of number of skipped sheets
624	$mapSheetId = array(); // mapping of sheet ids from old to new
625
626
627	$charts = $chartDetails = array();
628
629	if ($xmlWorkbook->sheets) {
630	foreach ($xmlWorkbook->sheets->sheet as $eleSheet) {
631	++$oldSheetId;
632
633	// Check if sheet should be skipped
634	if (isset($this->_loadSheetsOnly) && !in_array((string) $eleSheet["name"], $this->_loadSheetsOnly)) {
635	++$countSkippedSheets;
636	$mapSheetId[$oldSheetId] = null;
637	continue;
638	}
639
640	// Map old sheet id in original workbook to new sheet id.
641	// They will differ if loadSheetsOnly() is being used
642	$mapSheetId[$oldSheetId] = $oldSheetId - $countSkippedSheets;
643
644	// Load sheet
645	$docSheet = $excel->createSheet();
646	// Use false for $updateFormulaCellReferences to prevent adjustment of worksheet
647	// references in formula cells... during the load, all formulae should be correct,
648	// and we're simply bringing the worksheet name in line with the formula, not the
649	// reverse
650	$docSheet->setTitle((string) $eleSheet["name"],false);
651	$fileWorksheet = $worksheets[(string) self::array_item($eleSheet->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "id")];
652	$xmlSheet = simplexml_load_string($this->_getFromZipArchive($zip, "$dir/$fileWorksheet"), 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions()); //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
653
654	$sharedFormulas = array();
655
656	if (isset($eleSheet["state"]) && (string) $eleSheet["state"] != '') {
657	$docSheet->setSheetState( (string) $eleSheet["state"] );
658	}
659
660	if (isset($xmlSheet->sheetViews) && isset($xmlSheet->sheetViews->sheetView)) {
661	if (isset($xmlSheet->sheetViews->sheetView['zoomScale'])) {
662	$docSheet->getSheetView()->setZoomScale( intval($xmlSheet->sheetViews->sheetView['zoomScale']) );
663	}
664
665	if (isset($xmlSheet->sheetViews->sheetView['zoomScaleNormal'])) {
666	$docSheet->getSheetView()->setZoomScaleNormal( intval($xmlSheet->sheetViews->sheetView['zoomScaleNormal']) );
667	}
668
669	if (isset($xmlSheet->sheetViews->sheetView['view'])) {
670	$docSheet->getSheetView()->setView((string) $xmlSheet->sheetViews->sheetView['view']);
671	}
672
673	if (isset($xmlSheet->sheetViews->sheetView['showGridLines'])) {
674	$docSheet->setShowGridLines(self::boolean((string)$xmlSheet->sheetViews->sheetView['showGridLines']));
675	}
676
677	if (isset($xmlSheet->sheetViews->sheetView['showRowColHeaders'])) {
678	$docSheet->setShowRowColHeaders(self::boolean((string)$xmlSheet->sheetViews->sheetView['showRowColHeaders']));
679	}
680
681	if (isset($xmlSheet->sheetViews->sheetView['rightToLeft'])) {
682	$docSheet->setRightToLeft(self::boolean((string)$xmlSheet->sheetViews->sheetView['rightToLeft']));
683	}
684
685	if (isset($xmlSheet->sheetViews->sheetView->pane)) {
686	if (isset($xmlSheet->sheetViews->sheetView->pane['topLeftCell'])) {
687	$docSheet->freezePane( (string)$xmlSheet->sheetViews->sheetView->pane['topLeftCell'] );
688	} else {
689	$xSplit = 0;
690	$ySplit = 0;
691
692	if (isset($xmlSheet->sheetViews->sheetView->pane['xSplit'])) {
693	$xSplit = 1 + intval($xmlSheet->sheetViews->sheetView->pane['xSplit']);
694	}
695
696	if (isset($xmlSheet->sheetViews->sheetView->pane['ySplit'])) {
697	$ySplit = 1 + intval($xmlSheet->sheetViews->sheetView->pane['ySplit']);
698	}
699
700	$docSheet->freezePaneByColumnAndRow($xSplit, $ySplit);
701	}
702	}
703
704	if (isset($xmlSheet->sheetViews->sheetView->selection)) {
705	if (isset($xmlSheet->sheetViews->sheetView->selection['sqref'])) {
706	$sqref = (string)$xmlSheet->sheetViews->sheetView->selection['sqref'];
707	$sqref = explode(' ', $sqref);
708	$sqref = $sqref[0];
709	$docSheet->setSelectedCells($sqref);
710	}
711	}
712
713	}
714
715	if (isset($xmlSheet->sheetPr) && isset($xmlSheet->sheetPr->tabColor)) {
716	if (isset($xmlSheet->sheetPr->tabColor['rgb'])) {
717	$docSheet->getTabColor()->setARGB( (string)$xmlSheet->sheetPr->tabColor['rgb'] );
718	}
719	}
720	if (isset($xmlSheet->sheetPr) && isset($xmlSheet->sheetPr['codeName'])) {
721	$docSheet->setCodeName((string) $xmlSheet->sheetPr['codeName']);
722	}
723	if (isset($xmlSheet->sheetPr) && isset($xmlSheet->sheetPr->outlinePr)) {
724	if (isset($xmlSheet->sheetPr->outlinePr['summaryRight']) &&
725	!self::boolean((string) $xmlSheet->sheetPr->outlinePr['summaryRight'])) {
726	$docSheet->setShowSummaryRight(FALSE);
727	} else {
728	$docSheet->setShowSummaryRight(TRUE);
729	}
730
731	if (isset($xmlSheet->sheetPr->outlinePr['summaryBelow']) &&
732	!self::boolean((string) $xmlSheet->sheetPr->outlinePr['summaryBelow'])) {
733	$docSheet->setShowSummaryBelow(FALSE);
734	} else {
735	$docSheet->setShowSummaryBelow(TRUE);
736	}
737	}
738
739	if (isset($xmlSheet->sheetPr) && isset($xmlSheet->sheetPr->pageSetUpPr)) {
740	if (isset($xmlSheet->sheetPr->pageSetUpPr['fitToPage']) &&
741	!self::boolean((string) $xmlSheet->sheetPr->pageSetUpPr['fitToPage'])) {
742	$docSheet->getPageSetup()->setFitToPage(FALSE);
743	} else {
744	$docSheet->getPageSetup()->setFitToPage(TRUE);
745	}
746	}
747
748	if (isset($xmlSheet->sheetFormatPr)) {
749	if (isset($xmlSheet->sheetFormatPr['customHeight']) &&
750	self::boolean((string) $xmlSheet->sheetFormatPr['customHeight']) &&
751	isset($xmlSheet->sheetFormatPr['defaultRowHeight'])) {
752	$docSheet->getDefaultRowDimension()->setRowHeight( (float)$xmlSheet->sheetFormatPr['defaultRowHeight'] );
753	}
754	if (isset($xmlSheet->sheetFormatPr['defaultColWidth'])) {
755	$docSheet->getDefaultColumnDimension()->setWidth( (float)$xmlSheet->sheetFormatPr['defaultColWidth'] );
756	}
757	if (isset($xmlSheet->sheetFormatPr['zeroHeight']) &&
758	((string)$xmlSheet->sheetFormatPr['zeroHeight'] == '1')) {
759	$docSheet->getDefaultRowDimension()->setzeroHeight(true);
760	}
761	}
762
763	if (isset($xmlSheet->cols) && !$this->_readDataOnly) {
764	foreach ($xmlSheet->cols->col as $col) {
765	for ($i = intval($col["min"]) - 1; $i < intval($col["max"]); ++$i) {
766	if ($col["style"] && !$this->_readDataOnly) {
767	$docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setXfIndex(intval($col["style"]));
768	}
769	if (self::boolean($col["bestFit"])) {
770	//$docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setAutoSize(TRUE);
771	}
772	if (self::boolean($col["hidden"])) {
773	// echo PHPExcel_Cell::stringFromColumnIndex($i),': HIDDEN COLUMN',PHP_EOL;
774	$docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setVisible(FALSE);
775	}
776	if (self::boolean($col["collapsed"])) {
777	$docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setCollapsed(TRUE);
778	}
779	if ($col["outlineLevel"] > 0) {
780	$docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setOutlineLevel(intval($col["outlineLevel"]));
781	}
782	$docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setWidth(floatval($col["width"]));
783
784	if (intval($col["max"]) == 16384) {
785	break;
786	}
787	}
788	}
789	}
790
791	if (isset($xmlSheet->printOptions) && !$this->_readDataOnly) {
792	if (self::boolean((string) $xmlSheet->printOptions['gridLinesSet'])) {
793	$docSheet->setShowGridlines(TRUE);
794	}
795
796	if (self::boolean((string) $xmlSheet->printOptions['gridLines'])) {
797	$docSheet->setPrintGridlines(TRUE);
798	}
799
800	if (self::boolean((string) $xmlSheet->printOptions['horizontalCentered'])) {
801	$docSheet->getPageSetup()->setHorizontalCentered(TRUE);
802	}
803	if (self::boolean((string) $xmlSheet->printOptions['verticalCentered'])) {
804	$docSheet->getPageSetup()->setVerticalCentered(TRUE);
805	}
806	}
807
808	if ($xmlSheet && $xmlSheet->sheetData && $xmlSheet->sheetData->row) {
809	foreach ($xmlSheet->sheetData->row as $row) {
810	if ($row["ht"] && !$this->_readDataOnly) {
811	$docSheet->getRowDimension(intval($row["r"]))->setRowHeight(floatval($row["ht"]));
812	}
813	if (self::boolean($row["hidden"]) && !$this->_readDataOnly) {
814	$docSheet->getRowDimension(intval($row["r"]))->setVisible(FALSE);
815	}
816	if (self::boolean($row["collapsed"])) {
817	$docSheet->getRowDimension(intval($row["r"]))->setCollapsed(TRUE);
818	}
819	if ($row["outlineLevel"] > 0) {
820	$docSheet->getRowDimension(intval($row["r"]))->setOutlineLevel(intval($row["outlineLevel"]));
821	}
822	if ($row["s"] && !$this->_readDataOnly) {
823	$docSheet->getRowDimension(intval($row["r"]))->setXfIndex(intval($row["s"]));
824	}
825
826	foreach ($row->c as $c) {
827	$r = (string) $c["r"];
828	$cellDataType = (string) $c["t"];
829	$value = null;
830	$calculatedValue = null;
831
832	// Read cell?
833	if ($this->getReadFilter() !== NULL) {
834	$coordinates = PHPExcel_Cell::coordinateFromString($r);
835
836	if (!$this->getReadFilter()->readCell($coordinates[0], $coordinates[1], $docSheet->getTitle())) {
837	continue;
838	}
839	}
840
841	// echo 'Reading cell ', $coordinates[0], $coordinates[1], PHP_EOL;
842	// print_r($c);
843	// echo PHP_EOL;
844	// echo 'Cell Data Type is ', $cellDataType, ': ';
845	//
846	// Read cell!
847	switch ($cellDataType) {
848	case "s":
849	// echo 'String', PHP_EOL;
850	if ((string)$c->v != '') {
851	$value = $sharedStrings[intval($c->v)];
852
853	if ($value instanceof PHPExcel_RichText) {
854	$value = clone $value;
855	}
856	} else {
857	$value = '';
858	}
859
860	break;
861	case "b":
862	// echo 'Boolean', PHP_EOL;
863	if (!isset($c->f)) {
864	$value = self::_castToBool($c);
865	} else {
866	// Formula
867	$this->_castToFormula($c,$r,$cellDataType,$value,$calculatedValue,$sharedFormulas,'_castToBool');
868	if (isset($c->f['t'])) {
869	$att = array();
870	$att = $c->f;
871	$docSheet->getCell($r)->setFormulaAttributes($att);
872	}
873	// echo '$calculatedValue = ', $calculatedValue, PHP_EOL;
874	}
875	break;
876	case "inlineStr":
877	// echo 'Inline String', PHP_EOL;
878	$value = $this->_parseRichText($c->is);
879
880	break;
881	case "e":
882	// echo 'Error', PHP_EOL;
883	if (!isset($c->f)) {
884	$value = self::_castToError($c);
885	} else {
886	// Formula
887	$this->_castToFormula($c,$r,$cellDataType,$value,$calculatedValue,$sharedFormulas,'_castToError');
888	// echo '$calculatedValue = ', $calculatedValue, PHP_EOL;
889	}
890
891	break;
892
893	default:
894	// echo 'Default', PHP_EOL;
895	if (!isset($c->f)) {
896	// echo 'Not a Formula', PHP_EOL;
897	$value = self::_castToString($c);
898	} else {
899	// echo 'Treat as Formula', PHP_EOL;
900	// Formula
901	$this->_castToFormula($c,$r,$cellDataType,$value,$calculatedValue,$sharedFormulas,'_castToString');
902	// echo '$calculatedValue = ', $calculatedValue, PHP_EOL;
903	}
904
905	break;
906	}
907	// echo 'Value is ', $value, PHP_EOL;
908
909	// Check for numeric values
910	if (is_numeric($value) && $cellDataType != 's') {
911	if ($value == (int)$value) $value = (int)$value;
912	elseif ($value == (float)$value) $value = (float)$value;
913	elseif ($value == (double)$value) $value = (double)$value;
914	}
915
916	// Rich text?
917	if ($value instanceof PHPExcel_RichText && $this->_readDataOnly) {
918	$value = $value->getPlainText();
919	}
920
921	$cell = $docSheet->getCell($r);
922	// Assign value
923	if ($cellDataType != '') {
924	$cell->setValueExplicit($value, $cellDataType);
925	} else {
926	$cell->setValue($value);
927	}
928	if ($calculatedValue !== NULL) {
929	$cell->setCalculatedValue($calculatedValue);
930	}
931
932	// Style information?
933	if ($c["s"] && !$this->_readDataOnly) {
934	// no style index means 0, it seems
935	$cell->setXfIndex(isset($styles[intval($c["s"])]) ?
936	intval($c["s"]) : 0);
937	}
938	}
939	}
940	}
941
942	$conditionals = array();
943	if (!$this->_readDataOnly && $xmlSheet && $xmlSheet->conditionalFormatting) {
944	foreach ($xmlSheet->conditionalFormatting as $conditional) {
945	foreach ($conditional->cfRule as $cfRule) {
946	if (
947	(
948	(string)$cfRule["type"] == PHPExcel_Style_Conditional::CONDITION_NONE \|\|
949	(string)$cfRule["type"] == PHPExcel_Style_Conditional::CONDITION_CELLIS \|\|
950	(string)$cfRule["type"] == PHPExcel_Style_Conditional::CONDITION_CONTAINSTEXT \|\|
951	(string)$cfRule["type"] == PHPExcel_Style_Conditional::CONDITION_EXPRESSION
952	) && isset($dxfs[intval($cfRule["dxfId"])])
953	) {
954	$conditionals[(string) $conditional["sqref"]][intval($cfRule["priority"])] = $cfRule;
955	}
956	}
957	}
958
959	foreach ($conditionals as $ref => $cfRules) {
960	ksort($cfRules);
961	$conditionalStyles = array();
962	foreach ($cfRules as $cfRule) {
963	$objConditional = new PHPExcel_Style_Conditional();
964	$objConditional->setConditionType((string)$cfRule["type"]);
965	$objConditional->setOperatorType((string)$cfRule["operator"]);
966
967	if ((string)$cfRule["text"] != '') {
968	$objConditional->setText((string)$cfRule["text"]);
969	}
970
971	if (count($cfRule->formula) > 1) {
972	foreach ($cfRule->formula as $formula) {
973	$objConditional->addCondition((string)$formula);
974	}
975	} else {
976	$objConditional->addCondition((string)$cfRule->formula);
977	}
978	$objConditional->setStyle(clone $dxfs[intval($cfRule["dxfId"])]);
979	$conditionalStyles[] = $objConditional;
980	}
981
982	// Extract all cell references in $ref
983	$aReferences = PHPExcel_Cell::extractAllCellReferencesInRange($ref);
984	foreach ($aReferences as $reference) {
985	$docSheet->getStyle($reference)->setConditionalStyles($conditionalStyles);
986	}
987	}
988	}
989
990	$aKeys = array("sheet", "objects", "scenarios", "formatCells", "formatColumns", "formatRows", "insertColumns", "insertRows", "insertHyperlinks", "deleteColumns", "deleteRows", "selectLockedCells", "sort", "autoFilter", "pivotTables", "selectUnlockedCells");
991	if (!$this->_readDataOnly && $xmlSheet && $xmlSheet->sheetProtection) {
992	foreach ($aKeys as $key) {
993	$method = "set" . ucfirst($key);
994	$docSheet->getProtection()->$method(self::boolean((string) $xmlSheet->sheetProtection[$key]));
995	}
996	}
997
998	if (!$this->_readDataOnly && $xmlSheet && $xmlSheet->sheetProtection) {
999	$docSheet->getProtection()->setPassword((string) $xmlSheet->sheetProtection["password"], TRUE);
1000	if ($xmlSheet->protectedRanges->protectedRange) {
1001	foreach ($xmlSheet->protectedRanges->protectedRange as $protectedRange) {
1002	$docSheet->protectCells((string) $protectedRange["sqref"], (string) $protectedRange["password"], true);
1003	}
1004	}
1005	}
1006
1007	if ($xmlSheet && $xmlSheet->autoFilter && !$this->_readDataOnly) {
1008	$autoFilter = $docSheet->getAutoFilter();
1009	$autoFilter->setRange((string) $xmlSheet->autoFilter["ref"]);
1010	foreach ($xmlSheet->autoFilter->filterColumn as $filterColumn) {
1011	$column = $autoFilter->getColumnByOffset((integer) $filterColumn["colId"]);
1012	// Check for standard filters
1013	if ($filterColumn->filters) {
1014	$column->setFilterType(PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_FILTERTYPE_FILTER);
1015	$filters = $filterColumn->filters;
1016	if ((isset($filters["blank"])) && ($filters["blank"] == 1)) {
1017	$column->createRule()->setRule(
1018	NULL, // Operator is undefined, but always treated as EQUAL
1019	''
1020	)
1021	->setRuleType(PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_FILTER);
1022	}
1023	// Standard filters are always an OR join, so no join rule needs to be set
1024	// Entries can be either filter elements
1025	foreach ($filters->filter as $filterRule) {
1026	$column->createRule()->setRule(
1027	NULL, // Operator is undefined, but always treated as EQUAL
1028	(string) $filterRule["val"]
1029	)
1030	->setRuleType(PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_FILTER);
1031	}
1032	// Or Date Group elements
1033	foreach ($filters->dateGroupItem as $dateGroupItem) {
1034	$column->createRule()->setRule(
1035	NULL, // Operator is undefined, but always treated as EQUAL
1036	array(
1037	'year' => (string) $dateGroupItem["year"],
1038	'month' => (string) $dateGroupItem["month"],
1039	'day' => (string) $dateGroupItem["day"],
1040	'hour' => (string) $dateGroupItem["hour"],
1041	'minute' => (string) $dateGroupItem["minute"],
1042	'second' => (string) $dateGroupItem["second"],
1043	),
1044	(string) $dateGroupItem["dateTimeGrouping"]
1045	)
1046	->setRuleType(PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP);
1047	}
1048	}
1049	// Check for custom filters
1050	if ($filterColumn->customFilters) {
1051	$column->setFilterType(PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_FILTERTYPE_CUSTOMFILTER);
1052	$customFilters = $filterColumn->customFilters;
1053	// Custom filters can an AND or an OR join;
1054	// and there should only ever be one or two entries
1055	if ((isset($customFilters["and"])) && ($customFilters["and"] == 1)) {
1056	$column->setJoin(PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_COLUMN_JOIN_AND);
1057	}
1058	foreach ($customFilters->customFilter as $filterRule) {
1059	$column->createRule()->setRule(
1060	(string) $filterRule["operator"],
1061	(string) $filterRule["val"]
1062	)
1063	->setRuleType(PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_CUSTOMFILTER);
1064	}
1065	}
1066	// Check for dynamic filters
1067	if ($filterColumn->dynamicFilter) {
1068	$column->setFilterType(PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_FILTERTYPE_DYNAMICFILTER);
1069	// We should only ever have one dynamic filter
1070	foreach ($filterColumn->dynamicFilter as $filterRule) {
1071	$column->createRule()->setRule(
1072	NULL, // Operator is undefined, but always treated as EQUAL
1073	(string) $filterRule["val"],
1074	(string) $filterRule["type"]
1075	)
1076	->setRuleType(PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMICFILTER);
1077	if (isset($filterRule["val"])) {
1078	$column->setAttribute('val',(string) $filterRule["val"]);
1079	}
1080	if (isset($filterRule["maxVal"])) {
1081	$column->setAttribute('maxVal',(string) $filterRule["maxVal"]);
1082	}
1083	}
1084	}
1085	// Check for dynamic filters
1086	if ($filterColumn->top10) {
1087	$column->setFilterType(PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_FILTERTYPE_TOPTENFILTER);
1088	// We should only ever have one top10 filter
1089	foreach ($filterColumn->top10 as $filterRule) {
1090	$column->createRule()->setRule(
1091	(((isset($filterRule["percent"])) && ($filterRule["percent"] == 1))
1092	? PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_PERCENT
1093	: PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_BY_VALUE
1094	),
1095	(string) $filterRule["val"],
1096	(((isset($filterRule["top"])) && ($filterRule["top"] == 1))
1097	? PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_TOP
1098	: PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_BOTTOM
1099	)
1100	)
1101	->setRuleType(PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_TOPTENFILTER);
1102	}
1103	}
1104	}
1105	}
1106
1107	if ($xmlSheet && $xmlSheet->mergeCells && $xmlSheet->mergeCells->mergeCell && !$this->_readDataOnly) {
1108	foreach ($xmlSheet->mergeCells->mergeCell as $mergeCell) {
1109	$mergeRef = (string) $mergeCell["ref"];
1110	if (strpos($mergeRef,':') !== FALSE) {
1111	$docSheet->mergeCells((string) $mergeCell["ref"]);
1112	}
1113	}
1114	}
1115
1116	if ($xmlSheet && $xmlSheet->pageMargins && !$this->_readDataOnly) {
1117	$docPageMargins = $docSheet->getPageMargins();
1118	$docPageMargins->setLeft(floatval($xmlSheet->pageMargins["left"]));
1119	$docPageMargins->setRight(floatval($xmlSheet->pageMargins["right"]));
1120	$docPageMargins->setTop(floatval($xmlSheet->pageMargins["top"]));
1121	$docPageMargins->setBottom(floatval($xmlSheet->pageMargins["bottom"]));
1122	$docPageMargins->setHeader(floatval($xmlSheet->pageMargins["header"]));
1123	$docPageMargins->setFooter(floatval($xmlSheet->pageMargins["footer"]));
1124	}
1125
1126	if ($xmlSheet && $xmlSheet->pageSetup && !$this->_readDataOnly) {
1127	$docPageSetup = $docSheet->getPageSetup();
1128
1129	if (isset($xmlSheet->pageSetup["orientation"])) {
1130	$docPageSetup->setOrientation((string) $xmlSheet->pageSetup["orientation"]);
1131	}
1132	if (isset($xmlSheet->pageSetup["paperSize"])) {
1133	$docPageSetup->setPaperSize(intval($xmlSheet->pageSetup["paperSize"]));
1134	}
1135	if (isset($xmlSheet->pageSetup["scale"])) {
1136	$docPageSetup->setScale(intval($xmlSheet->pageSetup["scale"]), FALSE);
1137	}
1138	if (isset($xmlSheet->pageSetup["fitToHeight"]) && intval($xmlSheet->pageSetup["fitToHeight"]) >= 0) {
1139	$docPageSetup->setFitToHeight(intval($xmlSheet->pageSetup["fitToHeight"]), FALSE);
1140	}
1141	if (isset($xmlSheet->pageSetup["fitToWidth"]) && intval($xmlSheet->pageSetup["fitToWidth"]) >= 0) {
1142	$docPageSetup->setFitToWidth(intval($xmlSheet->pageSetup["fitToWidth"]), FALSE);
1143	}
1144	if (isset($xmlSheet->pageSetup["firstPageNumber"]) && isset($xmlSheet->pageSetup["useFirstPageNumber"]) &&
1145	self::boolean((string) $xmlSheet->pageSetup["useFirstPageNumber"])) {
1146	$docPageSetup->setFirstPageNumber(intval($xmlSheet->pageSetup["firstPageNumber"]));
1147	}
1148	}
1149
1150	if ($xmlSheet && $xmlSheet->headerFooter && !$this->_readDataOnly) {
1151	$docHeaderFooter = $docSheet->getHeaderFooter();
1152
1153	if (isset($xmlSheet->headerFooter["differentOddEven"]) &&
1154	self::boolean((string)$xmlSheet->headerFooter["differentOddEven"])) {
1155	$docHeaderFooter->setDifferentOddEven(TRUE);
1156	} else {
1157	$docHeaderFooter->setDifferentOddEven(FALSE);
1158	}
1159	if (isset($xmlSheet->headerFooter["differentFirst"]) &&
1160	self::boolean((string)$xmlSheet->headerFooter["differentFirst"])) {
1161	$docHeaderFooter->setDifferentFirst(TRUE);
1162	} else {
1163	$docHeaderFooter->setDifferentFirst(FALSE);
1164	}
1165	if (isset($xmlSheet->headerFooter["scaleWithDoc"]) &&
1166	!self::boolean((string)$xmlSheet->headerFooter["scaleWithDoc"])) {
1167	$docHeaderFooter->setScaleWithDocument(FALSE);
1168	} else {
1169	$docHeaderFooter->setScaleWithDocument(TRUE);
1170	}
1171	if (isset($xmlSheet->headerFooter["alignWithMargins"]) &&
1172	!self::boolean((string)$xmlSheet->headerFooter["alignWithMargins"])) {
1173	$docHeaderFooter->setAlignWithMargins(FALSE);
1174	} else {
1175	$docHeaderFooter->setAlignWithMargins(TRUE);
1176	}
1177
1178	$docHeaderFooter->setOddHeader((string) $xmlSheet->headerFooter->oddHeader);
1179	$docHeaderFooter->setOddFooter((string) $xmlSheet->headerFooter->oddFooter);
1180	$docHeaderFooter->setEvenHeader((string) $xmlSheet->headerFooter->evenHeader);
1181	$docHeaderFooter->setEvenFooter((string) $xmlSheet->headerFooter->evenFooter);
1182	$docHeaderFooter->setFirstHeader((string) $xmlSheet->headerFooter->firstHeader);
1183	$docHeaderFooter->setFirstFooter((string) $xmlSheet->headerFooter->firstFooter);
1184	}
1185
1186	if ($xmlSheet && $xmlSheet->rowBreaks && $xmlSheet->rowBreaks->brk && !$this->_readDataOnly) {
1187	foreach ($xmlSheet->rowBreaks->brk as $brk) {
1188	if ($brk["man"]) {
1189	$docSheet->setBreak("A$brk[id]", PHPExcel_Worksheet::BREAK_ROW);
1190	}
1191	}
1192	}
1193	if ($xmlSheet && $xmlSheet->colBreaks && $xmlSheet->colBreaks->brk && !$this->_readDataOnly) {
1194	foreach ($xmlSheet->colBreaks->brk as $brk) {
1195	if ($brk["man"]) {
1196	$docSheet->setBreak(PHPExcel_Cell::stringFromColumnIndex((string) $brk["id"]) . "1", PHPExcel_Worksheet::BREAK_COLUMN);
1197	}
1198	}
1199	}
1200
1201	if ($xmlSheet && $xmlSheet->dataValidations && !$this->_readDataOnly) {
1202	foreach ($xmlSheet->dataValidations->dataValidation as $dataValidation) {
1203	// Uppercase coordinate
1204	$range = strtoupper($dataValidation["sqref"]);
1205	$rangeSet = explode(' ',$range);
1206	foreach($rangeSet as $range) {
1207	$stRange = $docSheet->shrinkRangeToFit($range);
1208
1209	// Extract all cell references in $range
1210	$aReferences = PHPExcel_Cell::extractAllCellReferencesInRange($stRange);
1211	foreach ($aReferences as $reference) {
1212	// Create validation
1213	$docValidation = $docSheet->getCell($reference)->getDataValidation();
1214	$docValidation->setType((string) $dataValidation["type"]);
1215	$docValidation->setErrorStyle((string) $dataValidation["errorStyle"]);
1216	$docValidation->setOperator((string) $dataValidation["operator"]);
1217	$docValidation->setAllowBlank($dataValidation["allowBlank"] != 0);
1218	$docValidation->setShowDropDown($dataValidation["showDropDown"] == 0);
1219	$docValidation->setShowInputMessage($dataValidation["showInputMessage"] != 0);
1220	$docValidation->setShowErrorMessage($dataValidation["showErrorMessage"] != 0);
1221	$docValidation->setErrorTitle((string) $dataValidation["errorTitle"]);
1222	$docValidation->setError((string) $dataValidation["error"]);
1223	$docValidation->setPromptTitle((string) $dataValidation["promptTitle"]);
1224	$docValidation->setPrompt((string) $dataValidation["prompt"]);
1225	$docValidation->setFormula1((string) $dataValidation->formula1);
1226	$docValidation->setFormula2((string) $dataValidation->formula2);
1227	}
1228	}
1229	}
1230	}
1231
1232	// Add hyperlinks
1233	$hyperlinks = array();
1234	if (!$this->_readDataOnly) {
1235	// Locate hyperlink relations
1236	if ($zip->locateName(dirname("$dir/$fileWorksheet") . "/_rels/" . basename($fileWorksheet) . ".rels")) {
1237	$relsWorksheet = simplexml_load_string($this->_getFromZipArchive($zip, dirname("$dir/$fileWorksheet") . "/_rels/" . basename($fileWorksheet) . ".rels") , 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions()); //~ http://schemas.openxmlformats.org/package/2006/relationships");
1238	foreach ($relsWorksheet->Relationship as $ele) {
1239	if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/hyperlink") {
1240	$hyperlinks[(string)$ele["Id"]] = (string)$ele["Target"];
1241	}
1242	}
1243	}
1244
1245	// Loop through hyperlinks
1246	if ($xmlSheet && $xmlSheet->hyperlinks) {
1247	foreach ($xmlSheet->hyperlinks->hyperlink as $hyperlink) {
1248	// Link url
1249	$linkRel = $hyperlink->attributes('http://schemas.openxmlformats.org/officeDocument/2006/relationships');
1250
1251	foreach (PHPExcel_Cell::extractAllCellReferencesInRange($hyperlink['ref']) as $cellReference) {
1252	$cell = $docSheet->getCell( $cellReference );
1253	if (isset($linkRel['id'])) {
1254	$hyperlinkUrl = $hyperlinks[ (string)$linkRel['id'] ];
1255	if (isset($hyperlink['location'])) {
1256	$hyperlinkUrl .= '#' . (string) $hyperlink['location'];
1257	}
1258	$cell->getHyperlink()->setUrl($hyperlinkUrl);
1259	} elseif (isset($hyperlink['location'])) {
1260	$cell->getHyperlink()->setUrl( 'sheet://' . (string)$hyperlink['location'] );
1261	}
1262
1263	// Tooltip
1264	if (isset($hyperlink['tooltip'])) {
1265	$cell->getHyperlink()->setTooltip( (string)$hyperlink['tooltip'] );
1266	}
1267	}
1268	}
1269	}
1270	}
1271
1272	// Add comments
1273	$comments = array();
1274	$vmlComments = array();
1275	if (!$this->_readDataOnly) {
1276	// Locate comment relations
1277	if ($zip->locateName(dirname("$dir/$fileWorksheet") . "/_rels/" . basename($fileWorksheet) . ".rels")) {
1278	$relsWorksheet = simplexml_load_string($this->_getFromZipArchive($zip, dirname("$dir/$fileWorksheet") . "/_rels/" . basename($fileWorksheet) . ".rels") , 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions()); //~ http://schemas.openxmlformats.org/package/2006/relationships");
1279	foreach ($relsWorksheet->Relationship as $ele) {
1280	if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/comments") {
1281	$comments[(string)$ele["Id"]] = (string)$ele["Target"];
1282	}
1283	if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/vmlDrawing") {
1284	$vmlComments[(string)$ele["Id"]] = (string)$ele["Target"];
1285	}
1286	}
1287	}
1288
1289	// Loop through comments
1290	foreach ($comments as $relName => $relPath) {
1291	// Load comments file
1292	$relPath = PHPExcel_Shared_File::realpath(dirname("$dir/$fileWorksheet") . "/" . $relPath);
1293	$commentsFile = simplexml_load_string($this->_getFromZipArchive($zip, $relPath) , 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions());
1294
1295	// Utility variables
1296	$authors = array();
1297
1298	// Loop through authors
1299	foreach ($commentsFile->authors->author as $author) {
1300	$authors[] = (string)$author;
1301	}
1302
1303	// Loop through contents
1304	foreach ($commentsFile->commentList->comment as $comment) {
1305	$docSheet->getComment( (string)$comment['ref'] )->setAuthor( $authors[(string)$comment['authorId']] );
1306	$docSheet->getComment( (string)$comment['ref'] )->setText( $this->_parseRichText($comment->text) );
1307	}
1308	}
1309
1310	// Loop through VML comments
1311	foreach ($vmlComments as $relName => $relPath) {
1312	// Load VML comments file
1313	$relPath = PHPExcel_Shared_File::realpath(dirname("$dir/$fileWorksheet") . "/" . $relPath);
1314	$vmlCommentsFile = simplexml_load_string( $this->_getFromZipArchive($zip, $relPath) , 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions());
1315	$vmlCommentsFile->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml');
1316
1317	$shapes = $vmlCommentsFile->xpath('//v:shape');
1318	foreach ($shapes as $shape) {
1319	$shape->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml');
1320
1321	if (isset($shape['style'])) {
1322	$style = (string)$shape['style'];
1323	$fillColor = strtoupper( substr( (string)$shape['fillcolor'], 1 ) );
1324	$column = null;
1325	$row = null;
1326
1327	$clientData = $shape->xpath('.//x:ClientData');
1328	if (is_array($clientData) && !empty($clientData)) {
1329	$clientData = $clientData[0];
1330
1331	if ( isset($clientData['ObjectType']) && (string)$clientData['ObjectType'] == 'Note' ) {
1332	$temp = $clientData->xpath('.//x:Row');
1333	if (is_array($temp)) $row = $temp[0];
1334
1335	$temp = $clientData->xpath('.//x:Column');
1336	if (is_array($temp)) $column = $temp[0];
1337	}
1338	}
1339
1340	if (($column !== NULL) && ($row !== NULL)) {
1341	// Set comment properties
1342	$comment = $docSheet->getCommentByColumnAndRow((string) $column, $row + 1);
1343	$comment->getFillColor()->setRGB( $fillColor );
1344
1345	// Parse style
1346	$styleArray = explode(';', str_replace(' ', '', $style));
1347	foreach ($styleArray as $stylePair) {
1348	$stylePair = explode(':', $stylePair);
1349
1350	if ($stylePair[0] == 'margin-left') $comment->setMarginLeft($stylePair[1]);
1351	if ($stylePair[0] == 'margin-top') $comment->setMarginTop($stylePair[1]);
1352	if ($stylePair[0] == 'width') $comment->setWidth($stylePair[1]);
1353	if ($stylePair[0] == 'height') $comment->setHeight($stylePair[1]);
1354	if ($stylePair[0] == 'visibility') $comment->setVisible( $stylePair[1] == 'visible' );
1355
1356	}
1357	}
1358	}
1359	}
1360	}
1361
1362	// Header/footer images
1363	if ($xmlSheet && $xmlSheet->legacyDrawingHF && !$this->_readDataOnly) {
1364	if ($zip->locateName(dirname("$dir/$fileWorksheet") . "/_rels/" . basename($fileWorksheet) . ".rels")) {
1365	$relsWorksheet = simplexml_load_string($this->_getFromZipArchive($zip, dirname("$dir/$fileWorksheet") . "/_rels/" . basename($fileWorksheet) . ".rels") , 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions()); //~ http://schemas.openxmlformats.org/package/2006/relationships");
1366	$vmlRelationship = '';
1367
1368	foreach ($relsWorksheet->Relationship as $ele) {
1369	if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/vmlDrawing") {
1370	$vmlRelationship = self::dir_add("$dir/$fileWorksheet", $ele["Target"]);
1371	}
1372	}
1373
1374	if ($vmlRelationship != '') {
1375	// Fetch linked images
1376	$relsVML = simplexml_load_string($this->_getFromZipArchive($zip, dirname($vmlRelationship) . '/_rels/' . basename($vmlRelationship) . '.rels' ), 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions()); //~ http://schemas.openxmlformats.org/package/2006/relationships");
1377	$drawings = array();
1378	foreach ($relsVML->Relationship as $ele) {
1379	if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/image") {
1380	$drawings[(string) $ele["Id"]] = self::dir_add($vmlRelationship, $ele["Target"]);
1381	}
1382	}
1383
1384	// Fetch VML document
1385	$vmlDrawing = simplexml_load_string($this->_getFromZipArchive($zip, $vmlRelationship), 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions());
1386	$vmlDrawing->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml');
1387
1388	$hfImages = array();
1389
1390	$shapes = $vmlDrawing->xpath('//v:shape');
1391	foreach ($shapes as $idx => $shape) {
1392	$shape->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml');
1393	$imageData = $shape->xpath('//v:imagedata');
1394	$imageData = $imageData[$idx];
1395
1396	$imageData = $imageData->attributes('urn:schemas-microsoft-com:office:office');
1397	$style = self::toCSSArray( (string)$shape['style'] );
1398
1399	$hfImages[ (string)$shape['id'] ] = new PHPExcel_Worksheet_HeaderFooterDrawing();
1400	if (isset($imageData['title'])) {
1401	$hfImages[ (string)$shape['id'] ]->setName( (string)$imageData['title'] );
1402	}
1403
1404	$hfImages[ (string)$shape['id'] ]->setPath("zip://".PHPExcel_Shared_File::realpath($pFilename)."#" . $drawings[(string)$imageData['relid']], false);
1405	$hfImages[ (string)$shape['id'] ]->setResizeProportional(false);
1406	$hfImages[ (string)$shape['id'] ]->setWidth($style['width']);
1407	$hfImages[ (string)$shape['id'] ]->setHeight($style['height']);
1408	if (isset($style['margin-left'])) {
1409	$hfImages[ (string)$shape['id'] ]->setOffsetX($style['margin-left']);
1410	}
1411	$hfImages[ (string)$shape['id'] ]->setOffsetY($style['margin-top']);
1412	$hfImages[ (string)$shape['id'] ]->setResizeProportional(true);
1413	}
1414
1415	$docSheet->getHeaderFooter()->setImages($hfImages);
1416	}
1417	}
1418	}
1419
1420	}
1421
1422	// TODO: Autoshapes from twoCellAnchors!
1423	if ($zip->locateName(dirname("$dir/$fileWorksheet") . "/_rels/" . basename($fileWorksheet) . ".rels")) {
1424	$relsWorksheet = simplexml_load_string($this->_getFromZipArchive($zip, dirname("$dir/$fileWorksheet") . "/_rels/" . basename($fileWorksheet) . ".rels") , 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions()); //~ http://schemas.openxmlformats.org/package/2006/relationships");
1425	$drawings = array();
1426	foreach ($relsWorksheet->Relationship as $ele) {
1427	if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/drawing") {
1428	$drawings[(string) $ele["Id"]] = self::dir_add("$dir/$fileWorksheet", $ele["Target"]);
1429	}
1430	}
1431	if ($xmlSheet->drawing && !$this->_readDataOnly) {
1432	foreach ($xmlSheet->drawing as $drawing) {
1433	$fileDrawing = $drawings[(string) self::array_item($drawing->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "id")];
1434	$relsDrawing = simplexml_load_string($this->_getFromZipArchive($zip, dirname($fileDrawing) . "/_rels/" . basename($fileDrawing) . ".rels") , 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions()); //~ http://schemas.openxmlformats.org/package/2006/relationships");
1435	$images = array();
1436
1437	if ($relsDrawing && $relsDrawing->Relationship) {
1438	foreach ($relsDrawing->Relationship as $ele) {
1439	if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/image") {
1440	$images[(string) $ele["Id"]] = self::dir_add($fileDrawing, $ele["Target"]);
1441	} elseif ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/chart") {
1442	if ($this->_includeCharts) {
1443	$charts[self::dir_add($fileDrawing, $ele["Target"])] = array('id' => (string) $ele["Id"],
1444	'sheet' => $docSheet->getTitle()
1445	);
1446	}
1447	}
1448	}
1449	}
1450	$xmlDrawing = simplexml_load_string($this->_getFromZipArchive($zip, $fileDrawing), 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions())->children("http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing");
1451
1452	if ($xmlDrawing->oneCellAnchor) {
1453	foreach ($xmlDrawing->oneCellAnchor as $oneCellAnchor) {
1454	if ($oneCellAnchor->pic->blipFill) {
1455	$blip = $oneCellAnchor->pic->blipFill->children("http://schemas.openxmlformats.org/drawingml/2006/main")->blip;
1456	$xfrm = $oneCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->xfrm;
1457	$outerShdw = $oneCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->effectLst->outerShdw;
1458	$objDrawing = new PHPExcel_Worksheet_Drawing;
1459	$objDrawing->setName((string) self::array_item($oneCellAnchor->pic->nvPicPr->cNvPr->attributes(), "name"));
1460	$objDrawing->setDescription((string) self::array_item($oneCellAnchor->pic->nvPicPr->cNvPr->attributes(), "descr"));
1461	$objDrawing->setPath("zip://".PHPExcel_Shared_File::realpath($pFilename)."#" . $images[(string) self::array_item($blip->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "embed")], false);
1462	$objDrawing->setCoordinates(PHPExcel_Cell::stringFromColumnIndex((string) $oneCellAnchor->from->col) . ($oneCellAnchor->from->row + 1));
1463	$objDrawing->setOffsetX(PHPExcel_Shared_Drawing::EMUToPixels($oneCellAnchor->from->colOff));
1464	$objDrawing->setOffsetY(PHPExcel_Shared_Drawing::EMUToPixels($oneCellAnchor->from->rowOff));
1465	$objDrawing->setResizeProportional(false);
1466	$objDrawing->setWidth(PHPExcel_Shared_Drawing::EMUToPixels(self::array_item($oneCellAnchor->ext->attributes(), "cx")));
1467	$objDrawing->setHeight(PHPExcel_Shared_Drawing::EMUToPixels(self::array_item($oneCellAnchor->ext->attributes(), "cy")));
1468	if ($xfrm) {
1469	$objDrawing->setRotation(PHPExcel_Shared_Drawing::angleToDegrees(self::array_item($xfrm->attributes(), "rot")));
1470	}
1471	if ($outerShdw) {
1472	$shadow = $objDrawing->getShadow();
1473	$shadow->setVisible(true);
1474	$shadow->setBlurRadius(PHPExcel_Shared_Drawing::EMUTopixels(self::array_item($outerShdw->attributes(), "blurRad")));
1475	$shadow->setDistance(PHPExcel_Shared_Drawing::EMUTopixels(self::array_item($outerShdw->attributes(), "dist")));
1476	$shadow->setDirection(PHPExcel_Shared_Drawing::angleToDegrees(self::array_item($outerShdw->attributes(), "dir")));
1477	$shadow->setAlignment((string) self::array_item($outerShdw->attributes(), "algn"));
1478	$shadow->getColor()->setRGB(self::array_item($outerShdw->srgbClr->attributes(), "val"));
1479	$shadow->setAlpha(self::array_item($outerShdw->srgbClr->alpha->attributes(), "val") / 1000);
1480	}
1481	$objDrawing->setWorksheet($docSheet);
1482	} else {
1483	// ? Can charts be positioned with a oneCellAnchor ?
1484	$coordinates = PHPExcel_Cell::stringFromColumnIndex((string) $oneCellAnchor->from->col) . ($oneCellAnchor->from->row + 1);
1485	$offsetX = PHPExcel_Shared_Drawing::EMUToPixels($oneCellAnchor->from->colOff);
1486	$offsetY = PHPExcel_Shared_Drawing::EMUToPixels($oneCellAnchor->from->rowOff);
1487	$width = PHPExcel_Shared_Drawing::EMUToPixels(self::array_item($oneCellAnchor->ext->attributes(), "cx"));
1488	$height = PHPExcel_Shared_Drawing::EMUToPixels(self::array_item($oneCellAnchor->ext->attributes(), "cy"));
1489	}
1490	}
1491	}
1492	if ($xmlDrawing->twoCellAnchor) {
1493	foreach ($xmlDrawing->twoCellAnchor as $twoCellAnchor) {
1494	if ($twoCellAnchor->pic->blipFill) {
1495	$blip = $twoCellAnchor->pic->blipFill->children("http://schemas.openxmlformats.org/drawingml/2006/main")->blip;
1496	$xfrm = $twoCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->xfrm;
1497	$outerShdw = $twoCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->effectLst->outerShdw;
1498	$objDrawing = new PHPExcel_Worksheet_Drawing;
1499	$objDrawing->setName((string) self::array_item($twoCellAnchor->pic->nvPicPr->cNvPr->attributes(), "name"));
1500	$objDrawing->setDescription((string) self::array_item($twoCellAnchor->pic->nvPicPr->cNvPr->attributes(), "descr"));
1501	$objDrawing->setPath("zip://".PHPExcel_Shared_File::realpath($pFilename)."#" . $images[(string) self::array_item($blip->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "embed")], false);
1502	$objDrawing->setCoordinates(PHPExcel_Cell::stringFromColumnIndex((string) $twoCellAnchor->from->col) . ($twoCellAnchor->from->row + 1));
1503	$objDrawing->setOffsetX(PHPExcel_Shared_Drawing::EMUToPixels($twoCellAnchor->from->colOff));
1504	$objDrawing->setOffsetY(PHPExcel_Shared_Drawing::EMUToPixels($twoCellAnchor->from->rowOff));
1505	$objDrawing->setResizeProportional(false);
1506
1507	$objDrawing->setWidth(PHPExcel_Shared_Drawing::EMUToPixels(self::array_item($xfrm->ext->attributes(), "cx")));
1508	$objDrawing->setHeight(PHPExcel_Shared_Drawing::EMUToPixels(self::array_item($xfrm->ext->attributes(), "cy")));
1509
1510	if ($xfrm) {
1511	$objDrawing->setRotation(PHPExcel_Shared_Drawing::angleToDegrees(self::array_item($xfrm->attributes(), "rot")));
1512	}
1513	if ($outerShdw) {
1514	$shadow = $objDrawing->getShadow();
1515	$shadow->setVisible(true);
1516	$shadow->setBlurRadius(PHPExcel_Shared_Drawing::EMUTopixels(self::array_item($outerShdw->attributes(), "blurRad")));
1517	$shadow->setDistance(PHPExcel_Shared_Drawing::EMUTopixels(self::array_item($outerShdw->attributes(), "dist")));
1518	$shadow->setDirection(PHPExcel_Shared_Drawing::angleToDegrees(self::array_item($outerShdw->attributes(), "dir")));
1519	$shadow->setAlignment((string) self::array_item($outerShdw->attributes(), "algn"));
1520	$shadow->getColor()->setRGB(self::array_item($outerShdw->srgbClr->attributes(), "val"));
1521	$shadow->setAlpha(self::array_item($outerShdw->srgbClr->alpha->attributes(), "val") / 1000);
1522	}
1523	$objDrawing->setWorksheet($docSheet);
1524	} elseif(($this->_includeCharts) && ($twoCellAnchor->graphicFrame)) {
1525	$fromCoordinate = PHPExcel_Cell::stringFromColumnIndex((string) $twoCellAnchor->from->col) . ($twoCellAnchor->from->row + 1);
1526	$fromOffsetX = PHPExcel_Shared_Drawing::EMUToPixels($twoCellAnchor->from->colOff);
1527	$fromOffsetY = PHPExcel_Shared_Drawing::EMUToPixels($twoCellAnchor->from->rowOff);
1528	$toCoordinate = PHPExcel_Cell::stringFromColumnIndex((string) $twoCellAnchor->to->col) . ($twoCellAnchor->to->row + 1);
1529	$toOffsetX = PHPExcel_Shared_Drawing::EMUToPixels($twoCellAnchor->to->colOff);
1530	$toOffsetY = PHPExcel_Shared_Drawing::EMUToPixels($twoCellAnchor->to->rowOff);
1531	$graphic = $twoCellAnchor->graphicFrame->children("http://schemas.openxmlformats.org/drawingml/2006/main")->graphic;
1532	$chartRef = $graphic->graphicData->children("http://schemas.openxmlformats.org/drawingml/2006/chart")->chart;
1533	$thisChart = (string) $chartRef->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships");
1534
1535	$chartDetails[$docSheet->getTitle().'!'.$thisChart] =
1536	array( 'fromCoordinate' => $fromCoordinate,
1537	'fromOffsetX' => $fromOffsetX,
1538	'fromOffsetY' => $fromOffsetY,
1539	'toCoordinate' => $toCoordinate,
1540	'toOffsetX' => $toOffsetX,
1541	'toOffsetY' => $toOffsetY,
1542	'worksheetTitle' => $docSheet->getTitle()
1543	);
1544	}
1545	}
1546	}
1547
1548	}
1549	}
1550	}
1551
1552	// Loop through definedNames
1553	if ($xmlWorkbook->definedNames) {
1554	foreach ($xmlWorkbook->definedNames->definedName as $definedName) {
1555	// Extract range
1556	$extractedRange = (string)$definedName;
1557	$extractedRange = preg_replace('/\'(\w+)\'\!/', '', $extractedRange);
1558	if (($spos = strpos($extractedRange,'!')) !== false) {
1559	$extractedRange = substr($extractedRange,0,$spos).str_replace('$', '', substr($extractedRange,$spos));
1560	} else {
1561	$extractedRange = str_replace('$', '', $extractedRange);
1562	}
1563
1564	// Valid range?
1565	if (stripos((string)$definedName, '#REF!') !== FALSE \|\| $extractedRange == '') {
1566	continue;
1567	}
1568
1569	// Some definedNames are only applicable if we are on the same sheet...
1570	if ((string)$definedName['localSheetId'] != '' && (string)$definedName['localSheetId'] == $sheetId) {
1571	// Switch on type
1572	switch ((string)$definedName['name']) {
1573
1574	case '_xlnm._FilterDatabase':
1575	if ((string)$definedName['hidden'] !== '1') {
1576	$docSheet->getAutoFilter()->setRange($extractedRange);
1577	}
1578	break;
1579
1580	case '_xlnm.Print_Titles':
1581	// Split $extractedRange
1582	$extractedRange = explode(',', $extractedRange);
1583
1584	// Set print titles
1585	foreach ($extractedRange as $range) {
1586	$matches = array();
1587	$range = str_replace('$', '', $range);
1588
1589	// check for repeating columns, e g. 'A:A' or 'A:D'
1590	if (preg_match('/!?([A-Z]+)\:([A-Z]+)$/', $range, $matches)) {
1591	$docSheet->getPageSetup()->setColumnsToRepeatAtLeft(array($matches[1], $matches[2]));
1592	}
1593	// check for repeating rows, e.g. '1:1' or '1:5'
1594	elseif (preg_match('/!?(\d+)\:(\d+)$/', $range, $matches)) {
1595	$docSheet->getPageSetup()->setRowsToRepeatAtTop(array($matches[1], $matches[2]));
1596	}
1597	}
1598	break;
1599
1600	case '_xlnm.Print_Area':
1601	$rangeSets = explode(',', $extractedRange); // FIXME: what if sheetname contains comma?
1602	$newRangeSets = array();
1603	foreach($rangeSets as $rangeSet) {
1604	$range = explode('!', $rangeSet); // FIXME: what if sheetname contains exclamation mark?
1605	$rangeSet = isset($range[1]) ? $range[1] : $range[0];
1606	if (strpos($rangeSet, ':') === FALSE) {
1607	$rangeSet = $rangeSet . ':' . $rangeSet;
1608	}
1609	$newRangeSets[] = str_replace('$', '', $rangeSet);
1610	}
1611	$docSheet->getPageSetup()->setPrintArea(implode(',',$newRangeSets));
1612	break;
1613
1614	default:
1615	break;
1616	}
1617	}
1618	}
1619	}
1620
1621	// Next sheet id
1622	++$sheetId;
1623	}
1624
1625	// Loop through definedNames
1626	if ($xmlWorkbook->definedNames) {
1627	foreach ($xmlWorkbook->definedNames->definedName as $definedName) {
1628	// Extract range
1629	$extractedRange = (string)$definedName;
1630	$extractedRange = preg_replace('/\'(\w+)\'\!/', '', $extractedRange);
1631	if (($spos = strpos($extractedRange,'!')) !== false) {
1632	$extractedRange = substr($extractedRange,0,$spos).str_replace('$', '', substr($extractedRange,$spos));
1633	} else {
1634	$extractedRange = str_replace('$', '', $extractedRange);
1635	}
1636
1637	// Valid range?
1638	if (stripos((string)$definedName, '#REF!') !== false \|\| $extractedRange == '') {
1639	continue;
1640	}
1641
1642	// Some definedNames are only applicable if we are on the same sheet...
1643	if ((string)$definedName['localSheetId'] != '') {
1644	// Local defined name
1645	// Switch on type
1646	switch ((string)$definedName['name']) {
1647
1648	case '_xlnm._FilterDatabase':
1649	case '_xlnm.Print_Titles':
1650	case '_xlnm.Print_Area':
1651	break;
1652
1653	default:
1654	if ($mapSheetId[(integer) $definedName['localSheetId']] !== null) {
1655	$range = explode('!', (string)$definedName);
1656	if (count($range) == 2) {
1657	$range[0] = str_replace("''", "'", $range[0]);
1658	$range[0] = str_replace("'", "", $range[0]);
1659	if ($worksheet = $docSheet->getParent()->getSheetByName($range[0])) {
1660	$extractedRange = str_replace('$', '', $range[1]);
1661	$scope = $docSheet->getParent()->getSheet($mapSheetId[(integer) $definedName['localSheetId']]);
1662	$excel->addNamedRange( new PHPExcel_NamedRange((string)$definedName['name'], $worksheet, $extractedRange, true, $scope) );
1663	}
1664	}
1665	}
1666	break;
1667	}
1668	} else if (!isset($definedName['localSheetId'])) {
1669	// "Global" definedNames
1670	$locatedSheet = null;
1671	$extractedSheetName = '';
1672	if (strpos( (string)$definedName, '!' ) !== false) {
1673	// Extract sheet name
1674	$extractedSheetName = PHPExcel_Worksheet::extractSheetTitle( (string)$definedName, true );
1675	$extractedSheetName = $extractedSheetName[0];
1676
1677	// Locate sheet
1678	$locatedSheet = $excel->getSheetByName($extractedSheetName);
1679
1680	// Modify range
1681	$range = explode('!', $extractedRange);
1682	$extractedRange = isset($range[1]) ? $range[1] : $range[0];
1683	}
1684
1685	if ($locatedSheet !== NULL) {
1686	$excel->addNamedRange( new PHPExcel_NamedRange((string)$definedName['name'], $locatedSheet, $extractedRange, false) );
1687	}
1688	}
1689	}
1690	}
1691	}
1692
1693	if ((!$this->_readDataOnly) \|\| (!empty($this->_loadSheetsOnly))) {
1694	// active sheet index
1695	$activeTab = intval($xmlWorkbook->bookViews->workbookView["activeTab"]); // refers to old sheet index
1696
1697	// keep active sheet index if sheet is still loaded, else first sheet is set as the active
1698	if (isset($mapSheetId[$activeTab]) && $mapSheetId[$activeTab] !== null) {
1699	$excel->setActiveSheetIndex($mapSheetId[$activeTab]);
1700	} else {
1701	if ($excel->getSheetCount() == 0) {
1702	$excel->createSheet();
1703	}
1704	$excel->setActiveSheetIndex(0);
1705	}
1706	}
1707	break;
1708	}
1709
1710	}
1711
1712
1713	if (!$this->_readDataOnly) {
1714	$contentTypes = simplexml_load_string($this->_getFromZipArchive($zip, "[Content_Types].xml"), 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions());
1715	foreach ($contentTypes->Override as $contentType) {
1716	switch ($contentType["ContentType"]) {
1717	case "application/vnd.openxmlformats-officedocument.drawingml.chart+xml":
1718	if ($this->_includeCharts) {
1719	$chartEntryRef = ltrim($contentType['PartName'],'/');
1720	$chartElements = simplexml_load_string($this->_getFromZipArchive($zip, $chartEntryRef), 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions());
1721	$objChart = PHPExcel_Reader_Excel2007_Chart::readChart($chartElements,basename($chartEntryRef,'.xml'));
1722
1723	// echo 'Chart ',$chartEntryRef,'<br />';
1724	// var_dump($charts[$chartEntryRef]);
1725	//
1726	if (isset($charts[$chartEntryRef])) {
1727	$chartPositionRef = $charts[$chartEntryRef]['sheet'].'!'.$charts[$chartEntryRef]['id'];
1728	// echo 'Position Ref ',$chartPositionRef,'<br />';
1729	if (isset($chartDetails[$chartPositionRef])) {
1730	// var_dump($chartDetails[$chartPositionRef]);
1731
1732	$excel->getSheetByName($charts[$chartEntryRef]['sheet'])->addChart($objChart);
1733	$objChart->setWorksheet($excel->getSheetByName($charts[$chartEntryRef]['sheet']));
1734	$objChart->setTopLeftPosition( $chartDetails[$chartPositionRef]['fromCoordinate'],
1735	$chartDetails[$chartPositionRef]['fromOffsetX'],
1736	$chartDetails[$chartPositionRef]['fromOffsetY']
1737	);
1738	$objChart->setBottomRightPosition( $chartDetails[$chartPositionRef]['toCoordinate'],
1739	$chartDetails[$chartPositionRef]['toOffsetX'],
1740	$chartDetails[$chartPositionRef]['toOffsetY']
1741	);
1742	}
1743	}
1744	}
1745	}
1746	}
1747	}
1748
1749	$zip->close();
1750
1751	return $excel;
1752	}
1753
1754
1755	private static function _readColor($color, $background=FALSE) {
1756	if (isset($color["rgb"])) {
1757	return (string)$color["rgb"];
1758	} else if (isset($color["indexed"])) {
1759	return PHPExcel_Style_Color::indexedColor($color["indexed"]-7,$background)->getARGB();
1760	} else if (isset($color["theme"])) {
1761	if (self::$_theme !== NULL) {
1762	$returnColour = self::$_theme->getColourByIndex((int)$color["theme"]);
1763	if (isset($color["tint"])) {
1764	$tintAdjust = (float) $color["tint"];
1765	$returnColour = PHPExcel_Style_Color::changeBrightness($returnColour, $tintAdjust);
1766	}
1767	return 'FF'.$returnColour;
1768	}
1769	}
1770
1771	if ($background) {
1772	return 'FFFFFFFF';
1773	}
1774	return 'FF000000';
1775	}
1776
1777
1778	private static function _readStyle($docStyle, $style) {
1779	// format code
1780	// if (isset($style->numFmt)) {
1781	// if (isset($style->numFmt['formatCode'])) {
1782	// $docStyle->getNumberFormat()->setFormatCode((string) $style->numFmt['formatCode']);
1783	// } else {
1784	$docStyle->getNumberFormat()->setFormatCode($style->numFmt);
1785	// }
1786	// }
1787
1788	// font
1789	if (isset($style->font)) {
1790	$docStyle->getFont()->setName((string) $style->font->name["val"]);
1791	$docStyle->getFont()->setSize((string) $style->font->sz["val"]);
1792	if (isset($style->font->b)) {
1793	$docStyle->getFont()->setBold(!isset($style->font->b["val"]) \|\| self::boolean((string) $style->font->b["val"]));
1794	}
1795	if (isset($style->font->i)) {
1796	$docStyle->getFont()->setItalic(!isset($style->font->i["val"]) \|\| self::boolean((string) $style->font->i["val"]));
1797	}
1798	if (isset($style->font->strike)) {
1799	$docStyle->getFont()->setStrikethrough(!isset($style->font->strike["val"]) \|\| self::boolean((string) $style->font->strike["val"]));
1800	}
1801	$docStyle->getFont()->getColor()->setARGB(self::_readColor($style->font->color));
1802
1803	if (isset($style->font->u) && !isset($style->font->u["val"])) {
1804	$docStyle->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
1805	} else if (isset($style->font->u) && isset($style->font->u["val"])) {
1806	$docStyle->getFont()->setUnderline((string)$style->font->u["val"]);
1807	}
1808
1809	if (isset($style->font->vertAlign) && isset($style->font->vertAlign["val"])) {
1810	$vertAlign = strtolower((string)$style->font->vertAlign["val"]);
1811	if ($vertAlign == 'superscript') {
1812	$docStyle->getFont()->setSuperScript(true);
1813	}
1814	if ($vertAlign == 'subscript') {
1815	$docStyle->getFont()->setSubScript(true);
1816	}
1817	}
1818	}
1819
1820	// fill
1821	if (isset($style->fill)) {
1822	if ($style->fill->gradientFill) {
1823	$gradientFill = $style->fill->gradientFill[0];
1824	if(!empty($gradientFill["type"])) {
1825	$docStyle->getFill()->setFillType((string) $gradientFill["type"]);
1826	}
1827	$docStyle->getFill()->setRotation(floatval($gradientFill["degree"]));
1828	$gradientFill->registerXPathNamespace("sml", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
1829	$docStyle->getFill()->getStartColor()->setARGB(self::_readColor( self::array_item($gradientFill->xpath("sml:stop[@position=0]"))->color) );
1830	$docStyle->getFill()->getEndColor()->setARGB(self::_readColor( self::array_item($gradientFill->xpath("sml:stop[@position=1]"))->color) );
1831	} elseif ($style->fill->patternFill) {
1832	$patternType = (string)$style->fill->patternFill["patternType"] != '' ? (string)$style->fill->patternFill["patternType"] : 'solid';
1833	$docStyle->getFill()->setFillType($patternType);
1834	if ($style->fill->patternFill->fgColor) {
1835	$docStyle->getFill()->getStartColor()->setARGB(self::_readColor($style->fill->patternFill->fgColor,true));
1836	} else {
1837	$docStyle->getFill()->getStartColor()->setARGB('FF000000');
1838	}
1839	if ($style->fill->patternFill->bgColor) {
1840	$docStyle->getFill()->getEndColor()->setARGB(self::_readColor($style->fill->patternFill->bgColor,true));
1841	}
1842	}
1843	}
1844
1845	// border
1846	if (isset($style->border)) {
1847	$diagonalUp = self::boolean((string) $style->border["diagonalUp"]);
1848	$diagonalDown = self::boolean((string) $style->border["diagonalDown"]);
1849	if (!$diagonalUp && !$diagonalDown) {
1850	$docStyle->getBorders()->setDiagonalDirection(PHPExcel_Style_Borders::DIAGONAL_NONE);
1851	} elseif ($diagonalUp && !$diagonalDown) {
1852	$docStyle->getBorders()->setDiagonalDirection(PHPExcel_Style_Borders::DIAGONAL_UP);
1853	} elseif (!$diagonalUp && $diagonalDown) {
1854	$docStyle->getBorders()->setDiagonalDirection(PHPExcel_Style_Borders::DIAGONAL_DOWN);
1855	} else {
1856	$docStyle->getBorders()->setDiagonalDirection(PHPExcel_Style_Borders::DIAGONAL_BOTH);
1857	}
1858	self::_readBorder($docStyle->getBorders()->getLeft(), $style->border->left);
1859	self::_readBorder($docStyle->getBorders()->getRight(), $style->border->right);
1860	self::_readBorder($docStyle->getBorders()->getTop(), $style->border->top);
1861	self::_readBorder($docStyle->getBorders()->getBottom(), $style->border->bottom);
1862	self::_readBorder($docStyle->getBorders()->getDiagonal(), $style->border->diagonal);
1863	}
1864
1865	// alignment
1866	if (isset($style->alignment)) {
1867	$docStyle->getAlignment()->setHorizontal((string) $style->alignment["horizontal"]);
1868	$docStyle->getAlignment()->setVertical((string) $style->alignment["vertical"]);
1869
1870	$textRotation = 0;
1871	if ((int)$style->alignment["textRotation"] <= 90) {
1872	$textRotation = (int)$style->alignment["textRotation"];
1873	} else if ((int)$style->alignment["textRotation"] > 90) {
1874	$textRotation = 90 - (int)$style->alignment["textRotation"];
1875	}
1876
1877	$docStyle->getAlignment()->setTextRotation(intval($textRotation));
1878	$docStyle->getAlignment()->setWrapText(self::boolean((string) $style->alignment["wrapText"]));
1879	$docStyle->getAlignment()->setShrinkToFit(self::boolean((string) $style->alignment["shrinkToFit"]));
1880	$docStyle->getAlignment()->setIndent( intval((string)$style->alignment["indent"]) > 0 ? intval((string)$style->alignment["indent"]) : 0 );
1881	}
1882
1883	// protection
1884	if (isset($style->protection)) {
1885	if (isset($style->protection['locked'])) {
1886	if (self::boolean((string) $style->protection['locked'])) {
1887	$docStyle->getProtection()->setLocked(PHPExcel_Style_Protection::PROTECTION_PROTECTED);
1888	} else {
1889	$docStyle->getProtection()->setLocked(PHPExcel_Style_Protection::PROTECTION_UNPROTECTED);
1890	}
1891	}
1892
1893	if (isset($style->protection['hidden'])) {
1894	if (self::boolean((string) $style->protection['hidden'])) {
1895	$docStyle->getProtection()->setHidden(PHPExcel_Style_Protection::PROTECTION_PROTECTED);
1896	} else {
1897	$docStyle->getProtection()->setHidden(PHPExcel_Style_Protection::PROTECTION_UNPROTECTED);
1898	}
1899	}
1900	}
1901
1902	// top-level style settings
1903	if (isset($style->quotePrefix)) {
1904	$docStyle->setQuotePrefix($style->quotePrefix);
1905	}
1906	}
1907
1908
1909	private static function _readBorder($docBorder, $eleBorder) {
1910	if (isset($eleBorder["style"])) {
1911	$docBorder->setBorderStyle((string) $eleBorder["style"]);
1912	}
1913	if (isset($eleBorder->color)) {
1914	$docBorder->getColor()->setARGB(self::_readColor($eleBorder->color));
1915	}
1916	}
1917
1918
1919	private function _parseRichText($is = null) {
1920	$value = new PHPExcel_RichText();
1921
1922	if (isset($is->t)) {
1923	$value->createText( PHPExcel_Shared_String::ControlCharacterOOXML2PHP( (string) $is->t ) );
1924	} else {
1925	foreach ($is->r as $run) {
1926	if (!isset($run->rPr)) {
1927	$objText = $value->createText( PHPExcel_Shared_String::ControlCharacterOOXML2PHP( (string) $run->t ) );
1928
1929	} else {
1930	$objText = $value->createTextRun( PHPExcel_Shared_String::ControlCharacterOOXML2PHP( (string) $run->t ) );
1931
1932	if (isset($run->rPr->rFont["val"])) {
1933	$objText->getFont()->setName((string) $run->rPr->rFont["val"]);
1934	}
1935
1936	if (isset($run->rPr->sz["val"])) {
1937	$objText->getFont()->setSize((string) $run->rPr->sz["val"]);
1938	}
1939
1940	if (isset($run->rPr->color)) {
1941	$objText->getFont()->setColor( new PHPExcel_Style_Color( self::_readColor($run->rPr->color) ) );
1942	}
1943
1944	if ((isset($run->rPr->b["val"]) && self::boolean((string) $run->rPr->b["val"])) \|\|
1945	(isset($run->rPr->b) && !isset($run->rPr->b["val"]))) {
1946	$objText->getFont()->setBold(TRUE);
1947	}
1948
1949	if ((isset($run->rPr->i["val"]) && self::boolean((string) $run->rPr->i["val"])) \|\|
1950	(isset($run->rPr->i) && !isset($run->rPr->i["val"]))) {
1951	$objText->getFont()->setItalic(TRUE);
1952	}
1953
1954	if (isset($run->rPr->vertAlign) && isset($run->rPr->vertAlign["val"])) {
1955	$vertAlign = strtolower((string)$run->rPr->vertAlign["val"]);
1956	if ($vertAlign == 'superscript') {
1957	$objText->getFont()->setSuperScript(TRUE);
1958	}
1959	if ($vertAlign == 'subscript') {
1960	$objText->getFont()->setSubScript(TRUE);
1961	}
1962	}
1963
1964	if (isset($run->rPr->u) && !isset($run->rPr->u["val"])) {
1965	$objText->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
1966	} else if (isset($run->rPr->u) && isset($run->rPr->u["val"])) {
1967	$objText->getFont()->setUnderline((string)$run->rPr->u["val"]);
1968	}
1969
1970	if ((isset($run->rPr->strike["val"]) && self::boolean((string) $run->rPr->strike["val"])) \|\|
1971	(isset($run->rPr->strike) && !isset($run->rPr->strike["val"]))) {
1972	$objText->getFont()->setStrikethrough(TRUE);
1973	}
1974	}
1975	}
1976	}
1977
1978	return $value;
1979	}
1980
1981	private function _readRibbon($excel, $customUITarget, $zip)
1982	{
1983	$baseDir = dirname($customUITarget);
1984	$nameCustomUI = basename($customUITarget);
1985	// get the xml file (ribbon)
1986	$localRibbon = $this->_getFromZipArchive($zip, $customUITarget);
1987	$customUIImagesNames = array();
1988	$customUIImagesBinaries = array();
1989	// something like customUI/_rels/customUI.xml.rels
1990	$pathRels = $baseDir . '/_rels/' . $nameCustomUI . '.rels';
1991	$dataRels = $this->_getFromZipArchive($zip, $pathRels);
1992	if ($dataRels) {
1993	// exists and not empty if the ribbon have some pictures (other than internal MSO)
1994	$UIRels = simplexml_load_string($dataRels, 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions());
1995	if ($UIRels) {
1996	// we need to save id and target to avoid parsing customUI.xml and "guess" if it's a pseudo callback who load the image
1997	foreach ($UIRels->Relationship as $ele) {
1998	if ($ele["Type"] == 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/image') {
1999	// an image ?
2000	$customUIImagesNames[(string) $ele['Id']] = (string)$ele['Target'];
2001	$customUIImagesBinaries[(string)$ele['Target']] = $this->_getFromZipArchive($zip, $baseDir . '/' . (string) $ele['Target']);
2002	}
2003	}
2004	}
2005	}
2006	if ($localRibbon) {
2007	$excel->setRibbonXMLData($customUITarget, $localRibbon);
2008	if (count($customUIImagesNames) > 0 && count($customUIImagesBinaries) > 0) {
2009	$excel->setRibbonBinObjects($customUIImagesNames, $customUIImagesBinaries);
2010	} else {
2011	$excel->setRibbonBinObjects(NULL);
2012	}
2013	} else {
2014	$excel->setRibbonXMLData(NULL);
2015	$excel->setRibbonBinObjects(NULL);
2016	}
2017	}
2018
2019	private static function array_item($array, $key = 0) {
2020	return (isset($array[$key]) ? $array[$key] : null);
2021	}
2022
2023
2024	private static function dir_add($base, $add) {
2025	return preg_replace('~[^/]+/\.\./~', '', dirname($base) . "/$add");
2026	}
2027
2028
2029	private static function toCSSArray($style) {
2030	$style = str_replace(array("\r","\n"), "", $style);
2031
2032	$temp = explode(';', $style);
2033	$style = array();
2034	foreach ($temp as $item) {
2035	$item = explode(':', $item);
2036
2037	if (strpos($item[1], 'px') !== false) {
2038	$item[1] = str_replace('px', '', $item[1]);
2039	}
2040	if (strpos($item[1], 'pt') !== false) {
2041	$item[1] = str_replace('pt', '', $item[1]);
2042	$item[1] = PHPExcel_Shared_Font::fontSizeToPixels($item[1]);
2043	}
2044	if (strpos($item[1], 'in') !== false) {
2045	$item[1] = str_replace('in', '', $item[1]);
2046	$item[1] = PHPExcel_Shared_Font::inchSizeToPixels($item[1]);
2047	}
2048	if (strpos($item[1], 'cm') !== false) {
2049	$item[1] = str_replace('cm', '', $item[1]);
2050	$item[1] = PHPExcel_Shared_Font::centimeterSizeToPixels($item[1]);
2051	}
2052
2053	$style[$item[0]] = $item[1];
2054	}
2055
2056	return $style;
2057	}
2058
2059	private static function boolean($value = NULL)
2060	{
2061	if (is_object($value)) {
2062	$value = (string) $value;
2063	}
2064	if (is_numeric($value)) {
2065	return (bool) $value;
2066	}
2067	return ($value === 'true' \|\| $value === 'TRUE');
2068	}
2069	}

Note: See TracBrowser for help on using the repository browser.

Context Navigation

source: sourcecode/application/libraries/PHPExcel/Reader/Excel2007.php @ 1

Download in other formats: