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 */ 30if (!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_Gnumeric 40 * 41 * @category PHPExcel 42 * @package PHPExcel_Reader 43 * @copyright Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel) 44 */ 45class PHPExcel_Reader_Gnumeric extends PHPExcel_Reader_Abstract implements PHPExcel_Reader_IReader 46{ 47 /** 48 * Formats 49 * 50 * @var array 51 */ 52 private $_styles = array(); 53 54 /** 55 * Shared Expressions 56 * 57 * @var array 58 */ 59 private $_expressions = array(); 60 61 private $_referenceHelper = null; 62 63 64 /** 65 * Create a new PHPExcel_Reader_Gnumeric 66 */ 67 public function __construct() { 68 $this->_readFilter = new PHPExcel_Reader_DefaultReadFilter(); 69 $this->_referenceHelper = PHPExcel_ReferenceHelper::getInstance(); 70 } 71 72 73 /** 74 * Can the current PHPExcel_Reader_IReader read the file? 75 * 76 * @param string $pFilename 77 * @return boolean 78 * @throws PHPExcel_Reader_Exception 79 */ 80 public function canRead($pFilename) 81 { 82 // Check if file exists 83 if (!file_exists($pFilename)) { 84 throw new PHPExcel_Reader_Exception("Could not open " . $pFilename . " for reading! File does not exist."); 85 } 86 87 // Check if gzlib functions are available 88 if (!function_exists('gzread')) { 89 throw new PHPExcel_Reader_Exception("gzlib library is not enabled"); 90 } 91 92 // Read signature data (first 3 bytes) 93 $fh = fopen($pFilename, 'r'); 94 $data = fread($fh, 2); 95 fclose($fh); 96 97 if ($data != chr(0x1F).chr(0x8B)) { 98 return false; 99 } 100 101 return true; 102 } 103 104 105 /** 106 * Reads names of the worksheets from a file, without parsing the whole file to a PHPExcel object 107 * 108 * @param string $pFilename 109 * @throws PHPExcel_Reader_Exception 110 */ 111 public function listWorksheetNames($pFilename) 112 { 113 // Check if file exists 114 if (!file_exists($pFilename)) { 115 throw new PHPExcel_Reader_Exception("Could not open " . $pFilename . " for reading! File does not exist."); 116 } 117 118 $xml = new XMLReader(); 119 $xml->open( 120 'compress.zlib://'.realpath($pFilename), null, PHPExcel_Settings::getLibXmlLoaderOptions() 121 ); 122 $xml->setParserProperty(2,true); 123 124 $worksheetNames = array(); 125 while ($xml->read()) { 126 if ($xml->name == 'gnm:SheetName' && $xml->nodeType == XMLReader::ELEMENT) { 127 $xml->read(); // Move onto the value node 128 $worksheetNames[] = (string) $xml->value; 129 } elseif ($xml->name == 'gnm:Sheets') { 130 // break out of the loop once we've got our sheet names rather than parse the entire file 131 break; 132 } 133 } 134 135 return $worksheetNames; 136 } 137 138 139 /** 140 * Return worksheet info (Name, Last Column Letter, Last Column Index, Total Rows, Total Columns) 141 * 142 * @param string $pFilename 143 * @throws PHPExcel_Reader_Exception 144 */ 145 public function listWorksheetInfo($pFilename) 146 { 147 // Check if file exists 148 if (!file_exists($pFilename)) { 149 throw new PHPExcel_Reader_Exception("Could not open " . $pFilename . " for reading! File does not exist."); 150 } 151 152 $xml = new XMLReader(); 153 $xml->open( 154 'compress.zlib://'.realpath($pFilename), null, PHPExcel_Settings::getLibXmlLoaderOptions() 155 ); 156 $xml->setParserProperty(2,true); 157 158 $worksheetInfo = array(); 159 while ($xml->read()) { 160 if ($xml->name == 'gnm:Sheet' && $xml->nodeType == XMLReader::ELEMENT) { 161 $tmpInfo = array( 162 'worksheetName' => '', 163 'lastColumnLetter' => 'A', 164 'lastColumnIndex' => 0, 165 'totalRows' => 0, 166 'totalColumns' => 0, 167 ); 168 169 while ($xml->read()) { 170 if ($xml->name == 'gnm:Name' && $xml->nodeType == XMLReader::ELEMENT) { 171 $xml->read(); // Move onto the value node 172 $tmpInfo['worksheetName'] = (string) $xml->value; 173 } elseif ($xml->name == 'gnm:MaxCol' && $xml->nodeType == XMLReader::ELEMENT) { 174 $xml->read(); // Move onto the value node 175 $tmpInfo['lastColumnIndex'] = (int) $xml->value; 176 $tmpInfo['totalColumns'] = (int) $xml->value + 1; 177 } elseif ($xml->name == 'gnm:MaxRow' && $xml->nodeType == XMLReader::ELEMENT) { 178 $xml->read(); // Move onto the value node 179 $tmpInfo['totalRows'] = (int) $xml->value + 1; 180 break; 181 } 182 } 183 $tmpInfo['lastColumnLetter'] = PHPExcel_Cell::stringFromColumnIndex($tmpInfo['lastColumnIndex']); 184 $worksheetInfo[] = $tmpInfo; 185 } 186 } 187 188 return $worksheetInfo; 189 } 190 191 192 private function _gzfileGetContents($filename) { 193 $file = @gzopen($filename, 'rb'); 194 if ($file !== false) { 195 $data = ''; 196 while (!gzeof($file)) { 197 $data .= gzread($file, 1024); 198 } 199 gzclose($file); 200 } 201 return $data; 202 } 203 204 205 /** 206 * Loads PHPExcel from file 207 * 208 * @param string $pFilename 209 * @return PHPExcel 210 * @throws PHPExcel_Reader_Exception 211 */ 212 public function load($pFilename) 213 { 214 // Create new PHPExcel 215 $objPHPExcel = new PHPExcel(); 216 217 // Load into this instance 218 return $this->loadIntoExisting($pFilename, $objPHPExcel); 219 } 220 221 222 /** 223 * Loads PHPExcel from file into PHPExcel instance 224 * 225 * @param string $pFilename 226 * @param PHPExcel $objPHPExcel 227 * @return PHPExcel 228 * @throws PHPExcel_Reader_Exception 229 */ 230 public function loadIntoExisting($pFilename, PHPExcel $objPHPExcel) 231 { 232 // Check if file exists 233 if (!file_exists($pFilename)) { 234 throw new PHPExcel_Reader_Exception("Could not open " . $pFilename . " for reading! File does not exist."); 235 } 236 237 $timezoneObj = new DateTimeZone('Europe/London'); 238 $GMT = new DateTimeZone('UTC'); 239 240 $gFileData = $this->_gzfileGetContents($pFilename); 241 242// echo '<pre>'; 243// echo htmlentities($gFileData,ENT_QUOTES,'UTF-8'); 244// echo '</pre><hr />'; 245// 246 $xml = simplexml_load_string($gFileData, 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions()); 247 $namespacesMeta = $xml->getNamespaces(true); 248 249// var_dump($namespacesMeta); 250// 251 $gnmXML = $xml->children($namespacesMeta['gnm']); 252 253 $docProps = $objPHPExcel->getProperties(); 254 // Document Properties are held differently, depending on the version of Gnumeric 255 if (isset($namespacesMeta['office'])) { 256 $officeXML = $xml->children($namespacesMeta['office']); 257 $officeDocXML = $officeXML->{'document-meta'}; 258 $officeDocMetaXML = $officeDocXML->meta; 259 260 foreach($officeDocMetaXML as $officePropertyData) { 261 262 $officePropertyDC = array(); 263 if (isset($namespacesMeta['dc'])) { 264 $officePropertyDC = $officePropertyData->children($namespacesMeta['dc']); 265 } 266 foreach($officePropertyDC as $propertyName => $propertyValue) { 267 $propertyValue = (string) $propertyValue; 268 switch ($propertyName) { 269 case 'title' : 270 $docProps->setTitle(trim($propertyValue)); 271 break; 272 case 'subject' : 273 $docProps->setSubject(trim($propertyValue)); 274 break; 275 case 'creator' : 276 $docProps->setCreator(trim($propertyValue)); 277 $docProps->setLastModifiedBy(trim($propertyValue)); 278 break; 279 case 'date' : 280 $creationDate = strtotime(trim($propertyValue)); 281 $docProps->setCreated($creationDate); 282 $docProps->setModified($creationDate); 283 break; 284 case 'description' : 285 $docProps->setDescription(trim($propertyValue)); 286 break; 287 } 288 } 289 $officePropertyMeta = array(); 290 if (isset($namespacesMeta['meta'])) { 291 $officePropertyMeta = $officePropertyData->children($namespacesMeta['meta']); 292 } 293 foreach($officePropertyMeta as $propertyName => $propertyValue) { 294 $attributes = $propertyValue->attributes($namespacesMeta['meta']); 295 $propertyValue = (string) $propertyValue; 296 switch ($propertyName) { 297 case 'keyword' : 298 $docProps->setKeywords(trim($propertyValue)); 299 break; 300 case 'initial-creator' : 301 $docProps->setCreator(trim($propertyValue)); 302 $docProps->setLastModifiedBy(trim($propertyValue)); 303 break; 304 case 'creation-date' : 305 $creationDate = strtotime(trim($propertyValue)); 306 $docProps->setCreated($creationDate); 307 $docProps->setModified($creationDate); 308 break; 309 case 'user-defined' : 310 list(,$attrName) = explode(':',$attributes['name']); 311 switch ($attrName) { 312 case 'publisher' : 313 $docProps->setCompany(trim($propertyValue)); 314 break; 315 case 'category' : 316 $docProps->setCategory(trim($propertyValue)); 317 break; 318 case 'manager' : 319 $docProps->setManager(trim($propertyValue)); 320 break; 321 } 322 break; 323 } 324 } 325 } 326 } elseif (isset($gnmXML->Summary)) { 327 foreach($gnmXML->Summary->Item as $summaryItem) { 328 $propertyName = $summaryItem->name; 329 $propertyValue = $summaryItem->{'val-string'}; 330 switch ($propertyName) { 331 case 'title' : 332 $docProps->setTitle(trim($propertyValue)); 333 break; 334 case 'comments' : 335 $docProps->setDescription(trim($propertyValue)); 336 break; 337 case 'keywords' : 338 $docProps->setKeywords(trim($propertyValue)); 339 break; 340 case 'category' : 341 $docProps->setCategory(trim($propertyValue)); 342 break; 343 case 'manager' : 344 $docProps->setManager(trim($propertyValue)); 345 break; 346 case 'author' : 347 $docProps->setCreator(trim($propertyValue)); 348 $docProps->setLastModifiedBy(trim($propertyValue)); 349 break; 350 case 'company' : 351 $docProps->setCompany(trim($propertyValue)); 352 break; 353 } 354 } 355 } 356 357 $worksheetID = 0; 358 foreach($gnmXML->Sheets->Sheet as $sheet) { 359 $worksheetName = (string) $sheet->Name; 360// echo '<b>Worksheet: ',$worksheetName,'</b><br />'; 361 if ((isset($this->_loadSheetsOnly)) && (!in_array($worksheetName, $this->_loadSheetsOnly))) { 362 continue; 363 } 364 365 $maxRow = $maxCol = 0; 366 367 // Create new Worksheet 368 $objPHPExcel->createSheet(); 369 $objPHPExcel->setActiveSheetIndex($worksheetID); 370 // Use false for $updateFormulaCellReferences to prevent adjustment of worksheet references in formula 371 // cells... during the load, all formulae should be correct, and we're simply bringing the worksheet 372 // name in line with the formula, not the reverse 373 $objPHPExcel->getActiveSheet()->setTitle($worksheetName,false); 374 375 if ((!$this->_readDataOnly) && (isset($sheet->PrintInformation))) { 376 if (isset($sheet->PrintInformation->Margins)) { 377 foreach($sheet->PrintInformation->Margins->children('gnm',TRUE) as $key => $margin) { 378 $marginAttributes = $margin->attributes(); 379 $marginSize = 72 / 100; // Default 380 switch($marginAttributes['PrefUnit']) { 381 case 'mm' : 382 $marginSize = intval($marginAttributes['Points']) / 100; 383 break; 384 } 385 switch($key) { 386 case 'top' : 387 $objPHPExcel->getActiveSheet()->getPageMargins()->setTop($marginSize); 388 break; 389 case 'bottom' : 390 $objPHPExcel->getActiveSheet()->getPageMargins()->setBottom($marginSize); 391 break; 392 case 'left' : 393 $objPHPExcel->getActiveSheet()->getPageMargins()->setLeft($marginSize); 394 break; 395 case 'right' : 396 $objPHPExcel->getActiveSheet()->getPageMargins()->setRight($marginSize); 397 break; 398 case 'header' : 399 $objPHPExcel->getActiveSheet()->getPageMargins()->setHeader($marginSize); 400 break; 401 case 'footer' : 402 $objPHPExcel->getActiveSheet()->getPageMargins()->setFooter($marginSize); 403 break; 404 } 405 } 406 } 407 } 408 409 foreach($sheet->Cells->Cell as $cell) { 410 $cellAttributes = $cell->attributes(); 411 $row = (int) $cellAttributes->Row + 1; 412 $column = (int) $cellAttributes->Col; 413 414 if ($row > $maxRow) $maxRow = $row; 415 if ($column > $maxCol) $maxCol = $column; 416 417 $column = PHPExcel_Cell::stringFromColumnIndex($column); 418 419 // Read cell? 420 if ($this->getReadFilter() !== NULL) { 421 if (!$this->getReadFilter()->readCell($column, $row, $worksheetName)) { 422 continue; 423 } 424 } 425 426 $ValueType = $cellAttributes->ValueType; 427 $ExprID = (string) $cellAttributes->ExprID; 428// echo 'Cell ',$column,$row,'<br />'; 429// echo 'Type is ',$ValueType,'<br />'; 430// echo 'Value is ',$cell,'<br />'; 431 $type = PHPExcel_Cell_DataType::TYPE_FORMULA; 432 if ($ExprID > '') { 433 if (((string) $cell) > '') { 434 435 $this->_expressions[$ExprID] = array( 'column' => $cellAttributes->Col, 436 'row' => $cellAttributes->Row, 437 'formula' => (string) $cell 438 ); 439// echo 'NEW EXPRESSION ',$ExprID,'<br />'; 440 } else { 441 $expression = $this->_expressions[$ExprID]; 442 443 $cell = $this->_referenceHelper->updateFormulaReferences( $expression['formula'], 444 'A1', 445 $cellAttributes->Col - $expression['column'], 446 $cellAttributes->Row - $expression['row'], 447 $worksheetName 448 ); 449// echo 'SHARED EXPRESSION ',$ExprID,'<br />'; 450// echo 'New Value is ',$cell,'<br />'; 451 } 452 $type = PHPExcel_Cell_DataType::TYPE_FORMULA; 453 } else { 454 switch($ValueType) { 455 case '10' : // NULL 456 $type = PHPExcel_Cell_DataType::TYPE_NULL; 457 break; 458 case '20' : // Boolean 459 $type = PHPExcel_Cell_DataType::TYPE_BOOL; 460 $cell = ($cell == 'TRUE') ? True : False; 461 break; 462 case '30' : // Integer 463 $cell = intval($cell); 464 case '40' : // Float 465 $type = PHPExcel_Cell_DataType::TYPE_NUMERIC; 466 break; 467 case '50' : // Error 468 $type = PHPExcel_Cell_DataType::TYPE_ERROR; 469 break; 470 case '60' : // String 471 $type = PHPExcel_Cell_DataType::TYPE_STRING; 472 break; 473 case '70' : // Cell Range 474 case '80' : // Array 475 } 476 } 477 $objPHPExcel->getActiveSheet()->getCell($column.$row)->setValueExplicit($cell,$type); 478 } 479 480 if ((!$this->_readDataOnly) && (isset($sheet->Objects))) { 481 foreach($sheet->Objects->children('gnm',TRUE) as $key => $comment) { 482 $commentAttributes = $comment->attributes(); 483 // Only comment objects are handled at the moment 484 if ($commentAttributes->Text) { 485 $objPHPExcel->getActiveSheet()->getComment( (string)$commentAttributes->ObjectBound ) 486 ->setAuthor( (string)$commentAttributes->Author ) 487 ->setText($this->_parseRichText((string)$commentAttributes->Text) ); 488 } 489 } 490 } 491// echo '$maxCol=',$maxCol,'; $maxRow=',$maxRow,'<br />'; 492// 493 foreach($sheet->Styles->StyleRegion as $styleRegion) { 494 $styleAttributes = $styleRegion->attributes(); 495 if (($styleAttributes['startRow'] <= $maxRow) && 496 ($styleAttributes['startCol'] <= $maxCol)) { 497 498 $startColumn = PHPExcel_Cell::stringFromColumnIndex((int) $styleAttributes['startCol']); 499 $startRow = $styleAttributes['startRow'] + 1; 500 501 $endColumn = ($styleAttributes['endCol'] > $maxCol) ? $maxCol : (int) $styleAttributes['endCol']; 502 $endColumn = PHPExcel_Cell::stringFromColumnIndex($endColumn); 503 $endRow = ($styleAttributes['endRow'] > $maxRow) ? $maxRow : $styleAttributes['endRow']; 504 $endRow += 1; 505 $cellRange = $startColumn.$startRow.':'.$endColumn.$endRow; 506// echo $cellRange,'<br />'; 507 508 $styleAttributes = $styleRegion->Style->attributes(); 509// var_dump($styleAttributes); 510// echo '<br />'; 511 512 // We still set the number format mask for date/time values, even if _readDataOnly is true 513 if ((!$this->_readDataOnly) || 514 (PHPExcel_Shared_Date::isDateTimeFormatCode((string) $styleAttributes['Format']))) { 515 $styleArray = array(); 516 $styleArray['numberformat']['code'] = (string) $styleAttributes['Format']; 517 // If _readDataOnly is false, we set all formatting information 518 if (!$this->_readDataOnly) { 519 switch($styleAttributes['HAlign']) { 520 case '1' : 521 $styleArray['alignment']['horizontal'] = PHPExcel_Style_Alignment::HORIZONTAL_GENERAL; 522 break; 523 case '2' : 524 $styleArray['alignment']['horizontal'] = PHPExcel_Style_Alignment::HORIZONTAL_LEFT; 525 break; 526 case '4' : 527 $styleArray['alignment']['horizontal'] = PHPExcel_Style_Alignment::HORIZONTAL_RIGHT; 528 break; 529 case '8' : 530 $styleArray['alignment']['horizontal'] = PHPExcel_Style_Alignment::HORIZONTAL_CENTER; 531 break; 532 case '16' : 533 case '64' : 534 $styleArray['alignment']['horizontal'] = PHPExcel_Style_Alignment::HORIZONTAL_CENTER_CONTINUOUS; 535 break; 536 case '32' : 537 $styleArray['alignment']['horizontal'] = PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY; 538 break; 539 } 540 541 switch($styleAttributes['VAlign']) { 542 case '1' : 543 $styleArray['alignment']['vertical'] = PHPExcel_Style_Alignment::VERTICAL_TOP; 544 break; 545 case '2' : 546 $styleArray['alignment']['vertical'] = PHPExcel_Style_Alignment::VERTICAL_BOTTOM; 547 break; 548 case '4' : 549 $styleArray['alignment']['vertical'] = PHPExcel_Style_Alignment::VERTICAL_CENTER; 550 break; 551 case '8' : 552 $styleArray['alignment']['vertical'] = PHPExcel_Style_Alignment::VERTICAL_JUSTIFY; 553 break; 554 } 555 556 $styleArray['alignment']['wrap'] = ($styleAttributes['WrapText'] == '1') ? True : False; 557 $styleArray['alignment']['shrinkToFit'] = ($styleAttributes['ShrinkToFit'] == '1') ? True : False; 558 $styleArray['alignment']['indent'] = (intval($styleAttributes["Indent"]) > 0) ? $styleAttributes["indent"] : 0; 559 560 $RGB = self::_parseGnumericColour($styleAttributes["Fore"]); 561 $styleArray['font']['color']['rgb'] = $RGB; 562 $RGB = self::_parseGnumericColour($styleAttributes["Back"]); 563 $shade = $styleAttributes["Shade"]; 564 if (($RGB != '000000') || ($shade != '0')) { 565 $styleArray['fill']['color']['rgb'] = $styleArray['fill']['startcolor']['rgb'] = $RGB; 566 $RGB2 = self::_parseGnumericColour($styleAttributes["PatternColor"]); 567 $styleArray['fill']['endcolor']['rgb'] = $RGB2; 568 switch($shade) { 569 case '1' : 570 $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_SOLID; 571 break; 572 case '2' : 573 $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR; 574 break; 575 case '3' : 576 $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_GRADIENT_PATH; 577 break; 578 case '4' : 579 $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_DARKDOWN; 580 break; 581 case '5' : 582 $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_DARKGRAY; 583 break; 584 case '6' : 585 $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_DARKGRID; 586 break; 587 case '7' : 588 $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_DARKHORIZONTAL; 589 break; 590 case '8' : 591 $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_DARKTRELLIS; 592 break; 593 case '9' : 594 $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_DARKUP; 595 break; 596 case '10' : 597 $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_DARKVERTICAL; 598 break; 599 case '11' : 600 $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_GRAY0625; 601 break; 602 case '12' : 603 $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_GRAY125; 604 break; 605 case '13' : 606 $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_LIGHTDOWN; 607 break; 608 case '14' : 609 $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_LIGHTGRAY; 610 break; 611 case '15' : 612 $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_LIGHTGRID; 613 break; 614 case '16' : 615 $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_LIGHTHORIZONTAL; 616 break; 617 case '17' : 618 $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_LIGHTTRELLIS; 619 break; 620 case '18' : 621 $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_LIGHTUP; 622 break; 623 case '19' : 624 $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_LIGHTVERTICAL; 625 break; 626 case '20' : 627 $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_MEDIUMGRAY; 628 break; 629 } 630 } 631 632 $fontAttributes = $styleRegion->Style->Font->attributes(); 633// var_dump($fontAttributes); 634// echo '<br />'; 635 $styleArray['font']['name'] = (string) $styleRegion->Style->Font; 636 $styleArray['font']['size'] = intval($fontAttributes['Unit']); 637 $styleArray['font']['bold'] = ($fontAttributes['Bold'] == '1') ? True : False; 638 $styleArray['font']['italic'] = ($fontAttributes['Italic'] == '1') ? True : False; 639 $styleArray['font']['strike'] = ($fontAttributes['StrikeThrough'] == '1') ? True : False; 640 switch($fontAttributes['Underline']) { 641 case '1' : 642 $styleArray['font']['underline'] = PHPExcel_Style_Font::UNDERLINE_SINGLE; 643 break; 644 case '2' : 645 $styleArray['font']['underline'] = PHPExcel_Style_Font::UNDERLINE_DOUBLE; 646 break; 647 case '3' : 648 $styleArray['font']['underline'] = PHPExcel_Style_Font::UNDERLINE_SINGLEACCOUNTING; 649 break; 650 case '4' : 651 $styleArray['font']['underline'] = PHPExcel_Style_Font::UNDERLINE_DOUBLEACCOUNTING; 652 break; 653 default : 654 $styleArray['font']['underline'] = PHPExcel_Style_Font::UNDERLINE_NONE; 655 break; 656 } 657 switch($fontAttributes['Script']) { 658 case '1' : 659 $styleArray['font']['superScript'] = True; 660 break; 661 case '-1' : 662 $styleArray['font']['subScript'] = True; 663 break; 664 } 665 666 if (isset($styleRegion->Style->StyleBorder)) { 667 if (isset($styleRegion->Style->StyleBorder->Top)) { 668 $styleArray['borders']['top'] = self::_parseBorderAttributes($styleRegion->Style->StyleBorder->Top->attributes()); 669 } 670 if (isset($styleRegion->Style->StyleBorder->Bottom)) { 671 $styleArray['borders']['bottom'] = self::_parseBorderAttributes($styleRegion->Style->StyleBorder->Bottom->attributes()); 672 } 673 if (isset($styleRegion->Style->StyleBorder->Left)) { 674 $styleArray['borders']['left'] = self::_parseBorderAttributes($styleRegion->Style->StyleBorder->Left->attributes()); 675 } 676 if (isset($styleRegion->Style->StyleBorder->Right)) { 677 $styleArray['borders']['right'] = self::_parseBorderAttributes($styleRegion->Style->StyleBorder->Right->attributes()); 678 } 679 if ((isset($styleRegion->Style->StyleBorder->Diagonal)) && (isset($styleRegion->Style->StyleBorder->{'Rev-Diagonal'}))) { 680 $styleArray['borders']['diagonal'] = self::_parseBorderAttributes($styleRegion->Style->StyleBorder->Diagonal->attributes()); 681 $styleArray['borders']['diagonaldirection'] = PHPExcel_Style_Borders::DIAGONAL_BOTH; 682 } elseif (isset($styleRegion->Style->StyleBorder->Diagonal)) { 683 $styleArray['borders']['diagonal'] = self::_parseBorderAttributes($styleRegion->Style->StyleBorder->Diagonal->attributes()); 684 $styleArray['borders']['diagonaldirection'] = PHPExcel_Style_Borders::DIAGONAL_UP; 685 } elseif (isset($styleRegion->Style->StyleBorder->{'Rev-Diagonal'})) { 686 $styleArray['borders']['diagonal'] = self::_parseBorderAttributes($styleRegion->Style->StyleBorder->{'Rev-Diagonal'}->attributes()); 687 $styleArray['borders']['diagonaldirection'] = PHPExcel_Style_Borders::DIAGONAL_DOWN; 688 } 689 } 690 if (isset($styleRegion->Style->HyperLink)) { 691 // TO DO 692 $hyperlink = $styleRegion->Style->HyperLink->attributes(); 693 } 694 } 695// var_dump($styleArray); 696// echo '<br />'; 697 $objPHPExcel->getActiveSheet()->getStyle($cellRange)->applyFromArray($styleArray); 698 } 699 } 700 } 701 702 if ((!$this->_readDataOnly) && (isset($sheet->Cols))) { 703 // Column Widths 704 $columnAttributes = $sheet->Cols->attributes(); 705 $defaultWidth = $columnAttributes['DefaultSizePts'] / 5.4; 706 $c = 0; 707 foreach($sheet->Cols->ColInfo as $columnOverride) { 708 $columnAttributes = $columnOverride->attributes(); 709 $column = $columnAttributes['No']; 710 $columnWidth = $columnAttributes['Unit'] / 5.4; 711 $hidden = ((isset($columnAttributes['Hidden'])) && ($columnAttributes['Hidden'] == '1')) ? true : false; 712 $columnCount = (isset($columnAttributes['Count'])) ? $columnAttributes['Count'] : 1; 713 while ($c < $column) { 714 $objPHPExcel->getActiveSheet()->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($c))->setWidth($defaultWidth); 715 ++$c; 716 } 717 while (($c < ($column+$columnCount)) && ($c <= $maxCol)) { 718 $objPHPExcel->getActiveSheet()->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($c))->setWidth($columnWidth); 719 if ($hidden) { 720 $objPHPExcel->getActiveSheet()->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($c))->setVisible(false); 721 } 722 ++$c; 723 } 724 } 725 while ($c <= $maxCol) { 726 $objPHPExcel->getActiveSheet()->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($c))->setWidth($defaultWidth); 727 ++$c; 728 } 729 } 730 731 if ((!$this->_readDataOnly) && (isset($sheet->Rows))) { 732 // Row Heights 733 $rowAttributes = $sheet->Rows->attributes(); 734 $defaultHeight = $rowAttributes['DefaultSizePts']; 735 $r = 0; 736 737 foreach($sheet->Rows->RowInfo as $rowOverride) { 738 $rowAttributes = $rowOverride->attributes(); 739 $row = $rowAttributes['No']; 740 $rowHeight = $rowAttributes['Unit']; 741 $hidden = ((isset($rowAttributes['Hidden'])) && ($rowAttributes['Hidden'] == '1')) ? true : false; 742 $rowCount = (isset($rowAttributes['Count'])) ? $rowAttributes['Count'] : 1; 743 while ($r < $row) { 744 ++$r; 745 $objPHPExcel->getActiveSheet()->getRowDimension($r)->setRowHeight($defaultHeight); 746 } 747 while (($r < ($row+$rowCount)) && ($r < $maxRow)) { 748 ++$r; 749 $objPHPExcel->getActiveSheet()->getRowDimension($r)->setRowHeight($rowHeight); 750 if ($hidden) { 751 $objPHPExcel->getActiveSheet()->getRowDimension($r)->setVisible(false); 752 } 753 } 754 } 755 while ($r < $maxRow) { 756 ++$r; 757 $objPHPExcel->getActiveSheet()->getRowDimension($r)->setRowHeight($defaultHeight); 758 } 759 } 760 761 // Handle Merged Cells in this worksheet 762 if (isset($sheet->MergedRegions)) { 763 foreach($sheet->MergedRegions->Merge as $mergeCells) { 764 if (strpos($mergeCells,':') !== FALSE) { 765 $objPHPExcel->getActiveSheet()->mergeCells($mergeCells); 766 } 767 } 768 } 769 770 $worksheetID++; 771 } 772 773 // Loop through definedNames (global named ranges) 774 if (isset($gnmXML->Names)) { 775 foreach($gnmXML->Names->Name as $namedRange) { 776 $name = (string) $namedRange->name; 777 $range = (string) $namedRange->value; 778 if (stripos($range, '#REF!') !== false) { 779 continue; 780 } 781 782 $range = explode('!',$range); 783 $range[0] = trim($range[0],"'");; 784 if ($worksheet = $objPHPExcel->getSheetByName($range[0])) { 785 $extractedRange = str_replace('$', '', $range[1]); 786 $objPHPExcel->addNamedRange( new PHPExcel_NamedRange($name, $worksheet, $extractedRange) ); 787 } 788 } 789 } 790 791 792 // Return 793 return $objPHPExcel; 794 } 795 796 797 private static function _parseBorderAttributes($borderAttributes) 798 { 799 $styleArray = array(); 800 801 if (isset($borderAttributes["Color"])) { 802 $RGB = self::_parseGnumericColour($borderAttributes["Color"]); 803 $styleArray['color']['rgb'] = $RGB; 804 } 805 806 switch ($borderAttributes["Style"]) { 807 case '0' : 808 $styleArray['style'] = PHPExcel_Style_Border::BORDER_NONE; 809 break; 810 case '1' : 811 $styleArray['style'] = PHPExcel_Style_Border::BORDER_THIN; 812 break; 813 case '2' : 814 $styleArray['style'] = PHPExcel_Style_Border::BORDER_MEDIUM; 815 break; 816 case '4' : 817 $styleArray['style'] = PHPExcel_Style_Border::BORDER_DASHED; 818 break; 819 case '5' : 820 $styleArray['style'] = PHPExcel_Style_Border::BORDER_THICK; 821 break; 822 case '6' : 823 $styleArray['style'] = PHPExcel_Style_Border::BORDER_DOUBLE; 824 break; 825 case '7' : 826 $styleArray['style'] = PHPExcel_Style_Border::BORDER_DOTTED; 827 break; 828 case '9' : 829 $styleArray['style'] = PHPExcel_Style_Border::BORDER_DASHDOT; 830 break; 831 case '10' : 832 $styleArray['style'] = PHPExcel_Style_Border::BORDER_MEDIUMDASHDOT; 833 break; 834 case '11' : 835 $styleArray['style'] = PHPExcel_Style_Border::BORDER_DASHDOTDOT; 836 break; 837 case '12' : 838 $styleArray['style'] = PHPExcel_Style_Border::BORDER_MEDIUMDASHDOTDOT; 839 break; 840 case '13' : 841 $styleArray['style'] = PHPExcel_Style_Border::BORDER_MEDIUMDASHDOTDOT; 842 break; 843 case '3' : 844 $styleArray['style'] = PHPExcel_Style_Border::BORDER_SLANTDASHDOT; 845 break; 846 case '8' : 847 $styleArray['style'] = PHPExcel_Style_Border::BORDER_MEDIUMDASHED; 848 break; 849 } 850 return $styleArray; 851 } 852 853 854 private function _parseRichText($is = '') { 855 $value = new PHPExcel_RichText(); 856 857 $value->createText($is); 858 859 return $value; 860 } 861 862 863 private static function _parseGnumericColour($gnmColour) { 864 list($gnmR,$gnmG,$gnmB) = explode(':',$gnmColour); 865 $gnmR = substr(str_pad($gnmR,4,'0',STR_PAD_RIGHT),0,2); 866 $gnmG = substr(str_pad($gnmG,4,'0',STR_PAD_RIGHT),0,2); 867 $gnmB = substr(str_pad($gnmB,4,'0',STR_PAD_RIGHT),0,2); 868 $RGB = $gnmR.$gnmG.$gnmB; 869// echo 'Excel Colour: ',$RGB,'<br />'; 870 return $RGB; 871 } 872 873} 874