[Summary view] [Print] [Text view]

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