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