| [ 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_Excel2003XML 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_Excel2003XML extends PHPExcel_Reader_Abstract implements PHPExcel_Reader_IReader 46 { 47 /** 48 * Formats 49 * 50 * @var array 51 */ 52 private $_styles = array(); 53 54 /** 55 * Character set used in the file 56 * 57 * @var string 58 */ 59 private $_charSet = 'UTF-8'; 60 61 62 /** 63 * Create a new PHPExcel_Reader_Excel2003XML 64 */ 65 public function __construct() { 66 $this->_readFilter = new PHPExcel_Reader_DefaultReadFilter(); 67 } 68 69 70 /** 71 * Can the current PHPExcel_Reader_IReader read the file? 72 * 73 * @param string $pFilename 74 * @return boolean 75 * @throws PHPExcel_Reader_Exception 76 */ 77 public function canRead($pFilename) 78 { 79 80 // Office xmlns:o="urn:schemas-microsoft-com:office:office" 81 // Excel xmlns:x="urn:schemas-microsoft-com:office:excel" 82 // XML Spreadsheet xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" 83 // Spreadsheet component xmlns:c="urn:schemas-microsoft-com:office:component:spreadsheet" 84 // XML schema xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" 85 // XML data type xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" 86 // MS-persist recordset xmlns:rs="urn:schemas-microsoft-com:rowset" 87 // Rowset xmlns:z="#RowsetSchema" 88 // 89 90 $signature = array( 91 '<?xml version="1.0"', 92 '<?mso-application progid="Excel.Sheet"?>' 93 ); 94 95 // Open file 96 $this->_openFile($pFilename); 97 $fileHandle = $this->_fileHandle; 98 99 // Read sample data (first 2 KB will do) 100 $data = fread($fileHandle, 2048); 101 fclose($fileHandle); 102 103 $valid = true; 104 foreach($signature as $match) { 105 // every part of the signature must be present 106 if (strpos($data, $match) === false) { 107 $valid = false; 108 break; 109 } 110 } 111 112 // Retrieve charset encoding 113 if(preg_match('/<?xml.*encoding=[\'"](.*?)[\'"].*?>/um',$data,$matches)) { 114 $this->_charSet = strtoupper($matches[1]); 115 } 116 // echo 'Character Set is ',$this->_charSet,'<br />'; 117 118 return $valid; 119 } 120 121 122 /** 123 * Reads names of the worksheets from a file, without parsing the whole file to a PHPExcel object 124 * 125 * @param string $pFilename 126 * @throws PHPExcel_Reader_Exception 127 */ 128 public function listWorksheetNames($pFilename) 129 { 130 // Check if file exists 131 if (!file_exists($pFilename)) { 132 throw new PHPExcel_Reader_Exception("Could not open " . $pFilename . " for reading! File does not exist."); 133 } 134 if (!$this->canRead($pFilename)) { 135 throw new PHPExcel_Reader_Exception($pFilename . " is an Invalid Spreadsheet file."); 136 } 137 138 $worksheetNames = array(); 139 140 $xml = simplexml_load_file($pFilename, 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions()); 141 $namespaces = $xml->getNamespaces(true); 142 143 $xml_ss = $xml->children($namespaces['ss']); 144 foreach($xml_ss->Worksheet as $worksheet) { 145 $worksheet_ss = $worksheet->attributes($namespaces['ss']); 146 $worksheetNames[] = self::_convertStringEncoding((string) $worksheet_ss['Name'],$this->_charSet); 147 } 148 149 return $worksheetNames; 150 } 151 152 153 /** 154 * Return worksheet info (Name, Last Column Letter, Last Column Index, Total Rows, Total Columns) 155 * 156 * @param string $pFilename 157 * @throws PHPExcel_Reader_Exception 158 */ 159 public function listWorksheetInfo($pFilename) 160 { 161 // Check if file exists 162 if (!file_exists($pFilename)) { 163 throw new PHPExcel_Reader_Exception("Could not open " . $pFilename . " for reading! File does not exist."); 164 } 165 166 $worksheetInfo = array(); 167 168 $xml = simplexml_load_file($pFilename, 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions()); 169 $namespaces = $xml->getNamespaces(true); 170 171 $worksheetID = 1; 172 $xml_ss = $xml->children($namespaces['ss']); 173 foreach($xml_ss->Worksheet as $worksheet) { 174 $worksheet_ss = $worksheet->attributes($namespaces['ss']); 175 176 $tmpInfo = array(); 177 $tmpInfo['worksheetName'] = ''; 178 $tmpInfo['lastColumnLetter'] = 'A'; 179 $tmpInfo['lastColumnIndex'] = 0; 180 $tmpInfo['totalRows'] = 0; 181 $tmpInfo['totalColumns'] = 0; 182 183 if (isset($worksheet_ss['Name'])) { 184 $tmpInfo['worksheetName'] = (string) $worksheet_ss['Name']; 185 } else { 186 $tmpInfo['worksheetName'] = "Worksheet_{$worksheetID}"; 187 } 188 189 if (isset($worksheet->Table->Row)) { 190 $rowIndex = 0; 191 192 foreach($worksheet->Table->Row as $rowData) { 193 $columnIndex = 0; 194 $rowHasData = false; 195 196 foreach($rowData->Cell as $cell) { 197 if (isset($cell->Data)) { 198 $tmpInfo['lastColumnIndex'] = max($tmpInfo['lastColumnIndex'], $columnIndex); 199 $rowHasData = true; 200 } 201 202 ++$columnIndex; 203 } 204 205 ++$rowIndex; 206 207 if ($rowHasData) { 208 $tmpInfo['totalRows'] = max($tmpInfo['totalRows'], $rowIndex); 209 } 210 } 211 } 212 213 $tmpInfo['lastColumnLetter'] = PHPExcel_Cell::stringFromColumnIndex($tmpInfo['lastColumnIndex']); 214 $tmpInfo['totalColumns'] = $tmpInfo['lastColumnIndex'] + 1; 215 216 $worksheetInfo[] = $tmpInfo; 217 ++$worksheetID; 218 } 219 220 return $worksheetInfo; 221 } 222 223 224 /** 225 * Loads PHPExcel from file 226 * 227 * @param string $pFilename 228 * @return PHPExcel 229 * @throws PHPExcel_Reader_Exception 230 */ 231 public function load($pFilename) 232 { 233 // Create new PHPExcel 234 $objPHPExcel = new PHPExcel(); 235 236 // Load into this instance 237 return $this->loadIntoExisting($pFilename, $objPHPExcel); 238 } 239 240 241 private static function identifyFixedStyleValue($styleList,&$styleAttributeValue) { 242 $styleAttributeValue = strtolower($styleAttributeValue); 243 foreach($styleList as $style) { 244 if ($styleAttributeValue == strtolower($style)) { 245 $styleAttributeValue = $style; 246 return true; 247 } 248 } 249 return false; 250 } 251 252 253 /** 254 * pixel units to excel width units(units of 1/256th of a character width) 255 * @param pxs 256 * @return 257 */ 258 private static function _pixel2WidthUnits($pxs) { 259 $UNIT_OFFSET_MAP = array(0, 36, 73, 109, 146, 182, 219); 260 261 $widthUnits = 256 * ($pxs / 7); 262 $widthUnits += $UNIT_OFFSET_MAP[($pxs % 7)]; 263 return $widthUnits; 264 } 265 266 267 /** 268 * excel width units(units of 1/256th of a character width) to pixel units 269 * @param widthUnits 270 * @return 271 */ 272 private static function _widthUnits2Pixel($widthUnits) { 273 $pixels = ($widthUnits / 256) * 7; 274 $offsetWidthUnits = $widthUnits % 256; 275 $pixels += round($offsetWidthUnits / (256 / 7)); 276 return $pixels; 277 } 278 279 280 private static function _hex2str($hex) { 281 return chr(hexdec($hex[1])); 282 } 283 284 285 /** 286 * Loads PHPExcel from file into PHPExcel instance 287 * 288 * @param string $pFilename 289 * @param PHPExcel $objPHPExcel 290 * @return PHPExcel 291 * @throws PHPExcel_Reader_Exception 292 */ 293 public function loadIntoExisting($pFilename, PHPExcel $objPHPExcel) 294 { 295 $fromFormats = array('\-', '\ '); 296 $toFormats = array('-', ' '); 297 298 $underlineStyles = array ( 299 PHPExcel_Style_Font::UNDERLINE_NONE, 300 PHPExcel_Style_Font::UNDERLINE_DOUBLE, 301 PHPExcel_Style_Font::UNDERLINE_DOUBLEACCOUNTING, 302 PHPExcel_Style_Font::UNDERLINE_SINGLE, 303 PHPExcel_Style_Font::UNDERLINE_SINGLEACCOUNTING 304 ); 305 $verticalAlignmentStyles = array ( 306 PHPExcel_Style_Alignment::VERTICAL_BOTTOM, 307 PHPExcel_Style_Alignment::VERTICAL_TOP, 308 PHPExcel_Style_Alignment::VERTICAL_CENTER, 309 PHPExcel_Style_Alignment::VERTICAL_JUSTIFY 310 ); 311 $horizontalAlignmentStyles = array ( 312 PHPExcel_Style_Alignment::HORIZONTAL_GENERAL, 313 PHPExcel_Style_Alignment::HORIZONTAL_LEFT, 314 PHPExcel_Style_Alignment::HORIZONTAL_RIGHT, 315 PHPExcel_Style_Alignment::HORIZONTAL_CENTER, 316 PHPExcel_Style_Alignment::HORIZONTAL_CENTER_CONTINUOUS, 317 PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY 318 ); 319 320 $timezoneObj = new DateTimeZone('Europe/London'); 321 $GMT = new DateTimeZone('UTC'); 322 323 324 // Check if file exists 325 if (!file_exists($pFilename)) { 326 throw new PHPExcel_Reader_Exception("Could not open " . $pFilename . " for reading! File does not exist."); 327 } 328 329 if (!$this->canRead($pFilename)) { 330 throw new PHPExcel_Reader_Exception($pFilename . " is an Invalid Spreadsheet file."); 331 } 332 333 $xml = simplexml_load_file($pFilename, 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions()); 334 $namespaces = $xml->getNamespaces(true); 335 336 $docProps = $objPHPExcel->getProperties(); 337 if (isset($xml->DocumentProperties[0])) { 338 foreach($xml->DocumentProperties[0] as $propertyName => $propertyValue) { 339 switch ($propertyName) { 340 case 'Title' : 341 $docProps->setTitle(self::_convertStringEncoding($propertyValue,$this->_charSet)); 342 break; 343 case 'Subject' : 344 $docProps->setSubject(self::_convertStringEncoding($propertyValue,$this->_charSet)); 345 break; 346 case 'Author' : 347 $docProps->setCreator(self::_convertStringEncoding($propertyValue,$this->_charSet)); 348 break; 349 case 'Created' : 350 $creationDate = strtotime($propertyValue); 351 $docProps->setCreated($creationDate); 352 break; 353 case 'LastAuthor' : 354 $docProps->setLastModifiedBy(self::_convertStringEncoding($propertyValue,$this->_charSet)); 355 break; 356 case 'LastSaved' : 357 $lastSaveDate = strtotime($propertyValue); 358 $docProps->setModified($lastSaveDate); 359 break; 360 case 'Company' : 361 $docProps->setCompany(self::_convertStringEncoding($propertyValue,$this->_charSet)); 362 break; 363 case 'Category' : 364 $docProps->setCategory(self::_convertStringEncoding($propertyValue,$this->_charSet)); 365 break; 366 case 'Manager' : 367 $docProps->setManager(self::_convertStringEncoding($propertyValue,$this->_charSet)); 368 break; 369 case 'Keywords' : 370 $docProps->setKeywords(self::_convertStringEncoding($propertyValue,$this->_charSet)); 371 break; 372 case 'Description' : 373 $docProps->setDescription(self::_convertStringEncoding($propertyValue,$this->_charSet)); 374 break; 375 } 376 } 377 } 378 if (isset($xml->CustomDocumentProperties)) { 379 foreach($xml->CustomDocumentProperties[0] as $propertyName => $propertyValue) { 380 $propertyAttributes = $propertyValue->attributes($namespaces['dt']); 381 $propertyName = preg_replace_callback('/_x([0-9a-z]{4})_/','PHPExcel_Reader_Excel2003XML::_hex2str',$propertyName); 382 $propertyType = PHPExcel_DocumentProperties::PROPERTY_TYPE_UNKNOWN; 383 switch((string) $propertyAttributes) { 384 case 'string' : 385 $propertyType = PHPExcel_DocumentProperties::PROPERTY_TYPE_STRING; 386 $propertyValue = trim($propertyValue); 387 break; 388 case 'boolean' : 389 $propertyType = PHPExcel_DocumentProperties::PROPERTY_TYPE_BOOLEAN; 390 $propertyValue = (bool) $propertyValue; 391 break; 392 case 'integer' : 393 $propertyType = PHPExcel_DocumentProperties::PROPERTY_TYPE_INTEGER; 394 $propertyValue = intval($propertyValue); 395 break; 396 case 'float' : 397 $propertyType = PHPExcel_DocumentProperties::PROPERTY_TYPE_FLOAT; 398 $propertyValue = floatval($propertyValue); 399 break; 400 case 'dateTime.tz' : 401 $propertyType = PHPExcel_DocumentProperties::PROPERTY_TYPE_DATE; 402 $propertyValue = strtotime(trim($propertyValue)); 403 break; 404 } 405 $docProps->setCustomProperty($propertyName,$propertyValue,$propertyType); 406 } 407 } 408 409 foreach($xml->Styles[0] as $style) { 410 $style_ss = $style->attributes($namespaces['ss']); 411 $styleID = (string) $style_ss['ID']; 412 // echo 'Style ID = '.$styleID.'<br />'; 413 if ($styleID == 'Default') { 414 $this->_styles['Default'] = array(); 415 } else { 416 $this->_styles[$styleID] = $this->_styles['Default']; 417 } 418 foreach ($style as $styleType => $styleData) { 419 $styleAttributes = $styleData->attributes($namespaces['ss']); 420 // echo $styleType.'<br />'; 421 switch ($styleType) { 422 case 'Alignment' : 423 foreach($styleAttributes as $styleAttributeKey => $styleAttributeValue) { 424 // echo $styleAttributeKey.' = '.$styleAttributeValue.'<br />'; 425 $styleAttributeValue = (string) $styleAttributeValue; 426 switch ($styleAttributeKey) { 427 case 'Vertical' : 428 if (self::identifyFixedStyleValue($verticalAlignmentStyles,$styleAttributeValue)) { 429 $this->_styles[$styleID]['alignment']['vertical'] = $styleAttributeValue; 430 } 431 break; 432 case 'Horizontal' : 433 if (self::identifyFixedStyleValue($horizontalAlignmentStyles,$styleAttributeValue)) { 434 $this->_styles[$styleID]['alignment']['horizontal'] = $styleAttributeValue; 435 } 436 break; 437 case 'WrapText' : 438 $this->_styles[$styleID]['alignment']['wrap'] = true; 439 break; 440 } 441 } 442 break; 443 case 'Borders' : 444 foreach($styleData->Border as $borderStyle) { 445 $borderAttributes = $borderStyle->attributes($namespaces['ss']); 446 $thisBorder = array(); 447 foreach($borderAttributes as $borderStyleKey => $borderStyleValue) { 448 // echo $borderStyleKey.' = '.$borderStyleValue.'<br />'; 449 switch ($borderStyleKey) { 450 case 'LineStyle' : 451 $thisBorder['style'] = PHPExcel_Style_Border::BORDER_MEDIUM; 452 // $thisBorder['style'] = $borderStyleValue; 453 break; 454 case 'Weight' : 455 // $thisBorder['style'] = $borderStyleValue; 456 break; 457 case 'Position' : 458 $borderPosition = strtolower($borderStyleValue); 459 break; 460 case 'Color' : 461 $borderColour = substr($borderStyleValue,1); 462 $thisBorder['color']['rgb'] = $borderColour; 463 break; 464 } 465 } 466 if (!empty($thisBorder)) { 467 if (($borderPosition == 'left') || ($borderPosition == 'right') || ($borderPosition == 'top') || ($borderPosition == 'bottom')) { 468 $this->_styles[$styleID]['borders'][$borderPosition] = $thisBorder; 469 } 470 } 471 } 472 break; 473 case 'Font' : 474 foreach($styleAttributes as $styleAttributeKey => $styleAttributeValue) { 475 // echo $styleAttributeKey.' = '.$styleAttributeValue.'<br />'; 476 $styleAttributeValue = (string) $styleAttributeValue; 477 switch ($styleAttributeKey) { 478 case 'FontName' : 479 $this->_styles[$styleID]['font']['name'] = $styleAttributeValue; 480 break; 481 case 'Size' : 482 $this->_styles[$styleID]['font']['size'] = $styleAttributeValue; 483 break; 484 case 'Color' : 485 $this->_styles[$styleID]['font']['color']['rgb'] = substr($styleAttributeValue,1); 486 break; 487 case 'Bold' : 488 $this->_styles[$styleID]['font']['bold'] = true; 489 break; 490 case 'Italic' : 491 $this->_styles[$styleID]['font']['italic'] = true; 492 break; 493 case 'Underline' : 494 if (self::identifyFixedStyleValue($underlineStyles,$styleAttributeValue)) { 495 $this->_styles[$styleID]['font']['underline'] = $styleAttributeValue; 496 } 497 break; 498 } 499 } 500 break; 501 case 'Interior' : 502 foreach($styleAttributes as $styleAttributeKey => $styleAttributeValue) { 503 // echo $styleAttributeKey.' = '.$styleAttributeValue.'<br />'; 504 switch ($styleAttributeKey) { 505 case 'Color' : 506 $this->_styles[$styleID]['fill']['color']['rgb'] = substr($styleAttributeValue,1); 507 break; 508 } 509 } 510 break; 511 case 'NumberFormat' : 512 foreach($styleAttributes as $styleAttributeKey => $styleAttributeValue) { 513 // echo $styleAttributeKey.' = '.$styleAttributeValue.'<br />'; 514 $styleAttributeValue = str_replace($fromFormats,$toFormats,$styleAttributeValue); 515 switch ($styleAttributeValue) { 516 case 'Short Date' : 517 $styleAttributeValue = 'dd/mm/yyyy'; 518 break; 519 } 520 if ($styleAttributeValue > '') { 521 $this->_styles[$styleID]['numberformat']['code'] = $styleAttributeValue; 522 } 523 } 524 break; 525 case 'Protection' : 526 foreach($styleAttributes as $styleAttributeKey => $styleAttributeValue) { 527 // echo $styleAttributeKey.' = '.$styleAttributeValue.'<br />'; 528 } 529 break; 530 } 531 } 532 // print_r($this->_styles[$styleID]); 533 // echo '<hr />'; 534 } 535 // echo '<hr />'; 536 537 $worksheetID = 0; 538 $xml_ss = $xml->children($namespaces['ss']); 539 540 foreach($xml_ss->Worksheet as $worksheet) { 541 $worksheet_ss = $worksheet->attributes($namespaces['ss']); 542 543 if ((isset($this->_loadSheetsOnly)) && (isset($worksheet_ss['Name'])) && 544 (!in_array($worksheet_ss['Name'], $this->_loadSheetsOnly))) { 545 continue; 546 } 547 548 // echo '<h3>Worksheet: ',$worksheet_ss['Name'],'<h3>'; 549 // 550 // Create new Worksheet 551 $objPHPExcel->createSheet(); 552 $objPHPExcel->setActiveSheetIndex($worksheetID); 553 if (isset($worksheet_ss['Name'])) { 554 $worksheetName = self::_convertStringEncoding((string) $worksheet_ss['Name'],$this->_charSet); 555 // Use false for $updateFormulaCellReferences to prevent adjustment of worksheet references in 556 // formula cells... during the load, all formulae should be correct, and we're simply bringing 557 // the worksheet name in line with the formula, not the reverse 558 $objPHPExcel->getActiveSheet()->setTitle($worksheetName,false); 559 } 560 561 $columnID = 'A'; 562 if (isset($worksheet->Table->Column)) { 563 foreach($worksheet->Table->Column as $columnData) { 564 $columnData_ss = $columnData->attributes($namespaces['ss']); 565 if (isset($columnData_ss['Index'])) { 566 $columnID = PHPExcel_Cell::stringFromColumnIndex($columnData_ss['Index']-1); 567 } 568 if (isset($columnData_ss['Width'])) { 569 $columnWidth = $columnData_ss['Width']; 570 // echo '<b>Setting column width for '.$columnID.' to '.$columnWidth.'</b><br />'; 571 $objPHPExcel->getActiveSheet()->getColumnDimension($columnID)->setWidth($columnWidth / 5.4); 572 } 573 ++$columnID; 574 } 575 } 576 577 $rowID = 1; 578 if (isset($worksheet->Table->Row)) { 579 foreach($worksheet->Table->Row as $rowData) { 580 $rowHasData = false; 581 $row_ss = $rowData->attributes($namespaces['ss']); 582 if (isset($row_ss['Index'])) { 583 $rowID = (integer) $row_ss['Index']; 584 } 585 // echo '<b>Row '.$rowID.'</b><br />'; 586 587 $columnID = 'A'; 588 foreach($rowData->Cell as $cell) { 589 590 $cell_ss = $cell->attributes($namespaces['ss']); 591 if (isset($cell_ss['Index'])) { 592 $columnID = PHPExcel_Cell::stringFromColumnIndex($cell_ss['Index']-1); 593 } 594 $cellRange = $columnID.$rowID; 595 596 if ($this->getReadFilter() !== NULL) { 597 if (!$this->getReadFilter()->readCell($columnID, $rowID, $worksheetName)) { 598 continue; 599 } 600 } 601 602 if ((isset($cell_ss['MergeAcross'])) || (isset($cell_ss['MergeDown']))) { 603 $columnTo = $columnID; 604 if (isset($cell_ss['MergeAcross'])) { 605 $columnTo = PHPExcel_Cell::stringFromColumnIndex(PHPExcel_Cell::columnIndexFromString($columnID) + $cell_ss['MergeAcross'] -1); 606 } 607 $rowTo = $rowID; 608 if (isset($cell_ss['MergeDown'])) { 609 $rowTo = $rowTo + $cell_ss['MergeDown']; 610 } 611 $cellRange .= ':'.$columnTo.$rowTo; 612 $objPHPExcel->getActiveSheet()->mergeCells($cellRange); 613 } 614 615 $cellIsSet = $hasCalculatedValue = false; 616 $cellDataFormula = ''; 617 if (isset($cell_ss['Formula'])) { 618 $cellDataFormula = $cell_ss['Formula']; 619 // added this as a check for array formulas 620 if (isset($cell_ss['ArrayRange'])) { 621 $cellDataCSEFormula = $cell_ss['ArrayRange']; 622 // echo "found an array formula at ".$columnID.$rowID."<br />"; 623 } 624 $hasCalculatedValue = true; 625 } 626 if (isset($cell->Data)) { 627 $cellValue = $cellData = $cell->Data; 628 $type = PHPExcel_Cell_DataType::TYPE_NULL; 629 $cellData_ss = $cellData->attributes($namespaces['ss']); 630 if (isset($cellData_ss['Type'])) { 631 $cellDataType = $cellData_ss['Type']; 632 switch ($cellDataType) { 633 /* 634 const TYPE_STRING = 's'; 635 const TYPE_FORMULA = 'f'; 636 const TYPE_NUMERIC = 'n'; 637 const TYPE_BOOL = 'b'; 638 const TYPE_NULL = 'null'; 639 const TYPE_INLINE = 'inlineStr'; 640 const TYPE_ERROR = 'e'; 641 */ 642 case 'String' : 643 $cellValue = self::_convertStringEncoding($cellValue,$this->_charSet); 644 $type = PHPExcel_Cell_DataType::TYPE_STRING; 645 break; 646 case 'Number' : 647 $type = PHPExcel_Cell_DataType::TYPE_NUMERIC; 648 $cellValue = (float) $cellValue; 649 if (floor($cellValue) == $cellValue) { 650 $cellValue = (integer) $cellValue; 651 } 652 break; 653 case 'Boolean' : 654 $type = PHPExcel_Cell_DataType::TYPE_BOOL; 655 $cellValue = ($cellValue != 0); 656 break; 657 case 'DateTime' : 658 $type = PHPExcel_Cell_DataType::TYPE_NUMERIC; 659 $cellValue = PHPExcel_Shared_Date::PHPToExcel(strtotime($cellValue)); 660 break; 661 case 'Error' : 662 $type = PHPExcel_Cell_DataType::TYPE_ERROR; 663 break; 664 } 665 } 666 667 if ($hasCalculatedValue) { 668 // echo 'FORMULA<br />'; 669 $type = PHPExcel_Cell_DataType::TYPE_FORMULA; 670 $columnNumber = PHPExcel_Cell::columnIndexFromString($columnID); 671 if (substr($cellDataFormula,0,3) == 'of:') { 672 $cellDataFormula = substr($cellDataFormula,3); 673 // echo 'Before: ',$cellDataFormula,'<br />'; 674 $temp = explode('"',$cellDataFormula); 675 $key = false; 676 foreach($temp as &$value) { 677 // Only replace in alternate array entries (i.e. non-quoted blocks) 678 if ($key = !$key) { 679 $value = str_replace(array('[.','.',']'),'',$value); 680 } 681 } 682 } else { 683 // Convert R1C1 style references to A1 style references (but only when not quoted) 684 // echo 'Before: ',$cellDataFormula,'<br />'; 685 $temp = explode('"',$cellDataFormula); 686 $key = false; 687 foreach($temp as &$value) { 688 // Only replace in alternate array entries (i.e. non-quoted blocks) 689 if ($key = !$key) { 690 preg_match_all('/(R(\[?-?\d*\]?))(C(\[?-?\d*\]?))/',$value, $cellReferences,PREG_SET_ORDER+PREG_OFFSET_CAPTURE); 691 // Reverse the matches array, otherwise all our offsets will become incorrect if we modify our way 692 // through the formula from left to right. Reversing means that we work right to left.through 693 // the formula 694 $cellReferences = array_reverse($cellReferences); 695 // Loop through each R1C1 style reference in turn, converting it to its A1 style equivalent, 696 // then modify the formula to use that new reference 697 foreach($cellReferences as $cellReference) { 698 $rowReference = $cellReference[2][0]; 699 // Empty R reference is the current row 700 if ($rowReference == '') $rowReference = $rowID; 701 // Bracketed R references are relative to the current row 702 if ($rowReference{0} == '[') $rowReference = $rowID + trim($rowReference,'[]'); 703 $columnReference = $cellReference[4][0]; 704 // Empty C reference is the current column 705 if ($columnReference == '') $columnReference = $columnNumber; 706 // Bracketed C references are relative to the current column 707 if ($columnReference{0} == '[') $columnReference = $columnNumber + trim($columnReference,'[]'); 708 $A1CellReference = PHPExcel_Cell::stringFromColumnIndex($columnReference-1).$rowReference; 709 $value = substr_replace($value,$A1CellReference,$cellReference[0][1],strlen($cellReference[0][0])); 710 } 711 } 712 } 713 } 714 unset($value); 715 // Then rebuild the formula string 716 $cellDataFormula = implode('"',$temp); 717 // echo 'After: ',$cellDataFormula,'<br />'; 718 } 719 720 // echo 'Cell '.$columnID.$rowID.' is a '.$type.' with a value of '.(($hasCalculatedValue) ? $cellDataFormula : $cellValue).'<br />'; 721 // 722 $objPHPExcel->getActiveSheet()->getCell($columnID.$rowID)->setValueExplicit((($hasCalculatedValue) ? $cellDataFormula : $cellValue),$type); 723 if ($hasCalculatedValue) { 724 // echo 'Formula result is '.$cellValue.'<br />'; 725 $objPHPExcel->getActiveSheet()->getCell($columnID.$rowID)->setCalculatedValue($cellValue); 726 } 727 $cellIsSet = $rowHasData = true; 728 } 729 730 if (isset($cell->Comment)) { 731 // echo '<b>comment found</b><br />'; 732 $commentAttributes = $cell->Comment->attributes($namespaces['ss']); 733 $author = 'unknown'; 734 if (isset($commentAttributes->Author)) { 735 $author = (string)$commentAttributes->Author; 736 // echo 'Author: ',$author,'<br />'; 737 } 738 $node = $cell->Comment->Data->asXML(); 739 // $annotation = str_replace('html:','',substr($node,49,-10)); 740 // echo $annotation,'<br />'; 741 $annotation = strip_tags($node); 742 // echo 'Annotation: ',$annotation,'<br />'; 743 $objPHPExcel->getActiveSheet()->getComment( $columnID.$rowID ) 744 ->setAuthor(self::_convertStringEncoding($author ,$this->_charSet)) 745 ->setText($this->_parseRichText($annotation) ); 746 } 747 748 if (($cellIsSet) && (isset($cell_ss['StyleID']))) { 749 $style = (string) $cell_ss['StyleID']; 750 // echo 'Cell style for '.$columnID.$rowID.' is '.$style.'<br />'; 751 if ((isset($this->_styles[$style])) && (!empty($this->_styles[$style]))) { 752 // echo 'Cell '.$columnID.$rowID.'<br />'; 753 // print_r($this->_styles[$style]); 754 // echo '<br />'; 755 if (!$objPHPExcel->getActiveSheet()->cellExists($columnID.$rowID)) { 756 $objPHPExcel->getActiveSheet()->getCell($columnID.$rowID)->setValue(NULL); 757 } 758 $objPHPExcel->getActiveSheet()->getStyle($cellRange)->applyFromArray($this->_styles[$style]); 759 } 760 } 761 ++$columnID; 762 } 763 764 if ($rowHasData) { 765 if (isset($row_ss['StyleID'])) { 766 $rowStyle = $row_ss['StyleID']; 767 } 768 if (isset($row_ss['Height'])) { 769 $rowHeight = $row_ss['Height']; 770 // echo '<b>Setting row height to '.$rowHeight.'</b><br />'; 771 $objPHPExcel->getActiveSheet()->getRowDimension($rowID)->setRowHeight($rowHeight); 772 } 773 } 774 775 ++$rowID; 776 } 777 } 778 ++$worksheetID; 779 } 780 781 // Return 782 return $objPHPExcel; 783 } 784 785 786 private static function _convertStringEncoding($string,$charset) { 787 if ($charset != 'UTF-8') { 788 return PHPExcel_Shared_String::ConvertEncoding($string,'UTF-8',$charset); 789 } 790 return $string; 791 } 792 793 794 private function _parseRichText($is = '') { 795 $value = new PHPExcel_RichText(); 796 797 $value->createText(self::_convertStringEncoding($is,$this->_charSet)); 798 799 return $value; 800 } 801 802 }
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 |