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