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