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