| [ Index ] |
PHP Cross Reference of moodle-2.8 |
[Summary view] [Print] [Text view]
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 ##VERSION##, ##DATE## 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 }
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
| Generated: Fri Nov 28 20:29:05 2014 | Cross-referenced by PHPXref 0.7.1 |