[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_OOCalc
  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_OOCalc extends PHPExcel_Reader_Abstract implements PHPExcel_Reader_IReader
  38  {
  39      /**
  40       * Formats
  41       *
  42       * @var array
  43       */
  44      private $styles = array();
  45  
  46      /**
  47       * Create a new PHPExcel_Reader_OOCalc
  48       */
  49      public function __construct()
  50      {
  51          $this->readFilter     = new PHPExcel_Reader_DefaultReadFilter();
  52      }
  53  
  54      /**
  55       * Can the current PHPExcel_Reader_IReader read the file?
  56       *
  57       * @param     string         $pFilename
  58       * @return     boolean
  59       * @throws PHPExcel_Reader_Exception
  60       */
  61      public function canRead($pFilename)
  62      {
  63          // Check if file exists
  64          if (!file_exists($pFilename)) {
  65              throw new PHPExcel_Reader_Exception("Could not open " . $pFilename . " for reading! File does not exist.");
  66          }
  67  
  68          $zipClass = PHPExcel_Settings::getZipClass();
  69  
  70          // Check if zip class exists
  71  //        if (!class_exists($zipClass, false)) {
  72  //            throw new PHPExcel_Reader_Exception($zipClass . " library is not enabled");
  73  //        }
  74  
  75          $mimeType = 'UNKNOWN';
  76          // Load file
  77          $zip = new $zipClass;
  78          if ($zip->open($pFilename) === true) {
  79              // check if it is an OOXML archive
  80              $stat = $zip->statName('mimetype');
  81              if ($stat && ($stat['size'] <= 255)) {
  82                  $mimeType = $zip->getFromName($stat['name']);
  83              } elseif ($stat = $zip->statName('META-INF/manifest.xml')) {
  84                  $xml = simplexml_load_string($this->securityScan($zip->getFromName('META-INF/manifest.xml')), 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions());
  85                  $namespacesContent = $xml->getNamespaces(true);
  86                  if (isset($namespacesContent['manifest'])) {
  87                      $manifest = $xml->children($namespacesContent['manifest']);
  88                      foreach ($manifest as $manifestDataSet) {
  89                          $manifestAttributes = $manifestDataSet->attributes($namespacesContent['manifest']);
  90                          if ($manifestAttributes->{'full-path'} == '/') {
  91                              $mimeType = (string) $manifestAttributes->{'media-type'};
  92                              break;
  93                          }
  94                      }
  95                  }
  96              }
  97  
  98              $zip->close();
  99  
 100              return ($mimeType === 'application/vnd.oasis.opendocument.spreadsheet');
 101          }
 102  
 103          return false;
 104      }
 105  
 106  
 107      /**
 108       * Reads names of the worksheets from a file, without parsing the whole file to a PHPExcel object
 109       *
 110       * @param     string         $pFilename
 111       * @throws     PHPExcel_Reader_Exception
 112       */
 113      public function listWorksheetNames($pFilename)
 114      {
 115          // Check if file exists
 116          if (!file_exists($pFilename)) {
 117              throw new PHPExcel_Reader_Exception("Could not open " . $pFilename . " for reading! File does not exist.");
 118          }
 119  
 120          $zipClass = PHPExcel_Settings::getZipClass();
 121  
 122          $zip = new $zipClass;
 123          if (!$zip->open($pFilename)) {
 124              throw new PHPExcel_Reader_Exception("Could not open " . $pFilename . " for reading! Error opening file.");
 125          }
 126  
 127          $worksheetNames = array();
 128  
 129          $xml = new XMLReader();
 130          $res = $xml->xml($this->securityScanFile('zip://'.realpath($pFilename).'#content.xml'), null, PHPExcel_Settings::getLibXmlLoaderOptions());
 131          $xml->setParserProperty(2, true);
 132  
 133          //    Step into the first level of content of the XML
 134          $xml->read();
 135          while ($xml->read()) {
 136              //    Quickly jump through to the office:body node
 137              while ($xml->name !== 'office:body') {
 138                  if ($xml->isEmptyElement) {
 139                      $xml->read();
 140                  } else {
 141                      $xml->next();
 142                  }
 143              }
 144              //    Now read each node until we find our first table:table node
 145              while ($xml->read()) {
 146                  if ($xml->name == 'table:table' && $xml->nodeType == XMLReader::ELEMENT) {
 147                      //    Loop through each table:table node reading the table:name attribute for each worksheet name
 148                      do {
 149                          $worksheetNames[] = $xml->getAttribute('table:name');
 150                          $xml->next();
 151                      } while ($xml->name == 'table:table' && $xml->nodeType == XMLReader::ELEMENT);
 152                  }
 153              }
 154          }
 155  
 156          return $worksheetNames;
 157      }
 158  
 159      /**
 160       * Return worksheet info (Name, Last Column Letter, Last Column Index, Total Rows, Total Columns)
 161       *
 162       * @param   string     $pFilename
 163       * @throws   PHPExcel_Reader_Exception
 164       */
 165      public function listWorksheetInfo($pFilename)
 166      {
 167          // Check if file exists
 168          if (!file_exists($pFilename)) {
 169              throw new PHPExcel_Reader_Exception("Could not open " . $pFilename . " for reading! File does not exist.");
 170          }
 171  
 172          $worksheetInfo = array();
 173  
 174          $zipClass = PHPExcel_Settings::getZipClass();
 175  
 176          $zip = new $zipClass;
 177          if (!$zip->open($pFilename)) {
 178              throw new PHPExcel_Reader_Exception("Could not open " . $pFilename . " for reading! Error opening file.");
 179          }
 180  
 181          $xml = new XMLReader();
 182          $res = $xml->xml($this->securityScanFile('zip://'.realpath($pFilename).'#content.xml'), null, PHPExcel_Settings::getLibXmlLoaderOptions());
 183          $xml->setParserProperty(2, true);
 184  
 185          //    Step into the first level of content of the XML
 186          $xml->read();
 187          while ($xml->read()) {
 188              //    Quickly jump through to the office:body node
 189              while ($xml->name !== 'office:body') {
 190                  if ($xml->isEmptyElement) {
 191                      $xml->read();
 192                  } else {
 193                      $xml->next();
 194                  }
 195              }
 196                  //    Now read each node until we find our first table:table node
 197              while ($xml->read()) {
 198                  if ($xml->name == 'table:table' && $xml->nodeType == XMLReader::ELEMENT) {
 199                      $worksheetNames[] = $xml->getAttribute('table:name');
 200  
 201                      $tmpInfo = array(
 202                          'worksheetName' => $xml->getAttribute('table:name'),
 203                          'lastColumnLetter' => 'A',
 204                          'lastColumnIndex' => 0,
 205                          'totalRows' => 0,
 206                          'totalColumns' => 0,
 207                      );
 208  
 209                      //    Loop through each child node of the table:table element reading
 210                      $currCells = 0;
 211                      do {
 212                          $xml->read();
 213                          if ($xml->name == 'table:table-row' && $xml->nodeType == XMLReader::ELEMENT) {
 214                              $rowspan = $xml->getAttribute('table:number-rows-repeated');
 215                              $rowspan = empty($rowspan) ? 1 : $rowspan;
 216                              $tmpInfo['totalRows'] += $rowspan;
 217                              $tmpInfo['totalColumns'] = max($tmpInfo['totalColumns'], $currCells);
 218                              $currCells = 0;
 219                              //    Step into the row
 220                              $xml->read();
 221                              do {
 222                                  if ($xml->name == 'table:table-cell' && $xml->nodeType == XMLReader::ELEMENT) {
 223                                      if (!$xml->isEmptyElement) {
 224                                          $currCells++;
 225                                          $xml->next();
 226                                      } else {
 227                                          $xml->read();
 228                                      }
 229                                  } elseif ($xml->name == 'table:covered-table-cell' && $xml->nodeType == XMLReader::ELEMENT) {
 230                                      $mergeSize = $xml->getAttribute('table:number-columns-repeated');
 231                                      $currCells += $mergeSize;
 232                                      $xml->read();
 233                                  }
 234                              } while ($xml->name != 'table:table-row');
 235                          }
 236                      } while ($xml->name != 'table:table');
 237  
 238                      $tmpInfo['totalColumns'] = max($tmpInfo['totalColumns'], $currCells);
 239                      $tmpInfo['lastColumnIndex'] = $tmpInfo['totalColumns'] - 1;
 240                      $tmpInfo['lastColumnLetter'] = PHPExcel_Cell::stringFromColumnIndex($tmpInfo['lastColumnIndex']);
 241                      $worksheetInfo[] = $tmpInfo;
 242                  }
 243              }
 244  
 245  //                foreach ($workbookData->table as $worksheetDataSet) {
 246  //                    $worksheetData = $worksheetDataSet->children($namespacesContent['table']);
 247  //                    $worksheetDataAttributes = $worksheetDataSet->attributes($namespacesContent['table']);
 248  //
 249  //                    $rowIndex = 0;
 250  //                    foreach ($worksheetData as $key => $rowData) {
 251  //                        switch ($key) {
 252  //                            case 'table-row' :
 253  //                                $rowDataTableAttributes = $rowData->attributes($namespacesContent['table']);
 254  //                                $rowRepeats = (isset($rowDataTableAttributes['number-rows-repeated'])) ?
 255  //                                        $rowDataTableAttributes['number-rows-repeated'] : 1;
 256  //                                $columnIndex = 0;
 257  //
 258  //                                foreach ($rowData as $key => $cellData) {
 259  //                                    $cellDataTableAttributes = $cellData->attributes($namespacesContent['table']);
 260  //                                    $colRepeats = (isset($cellDataTableAttributes['number-columns-repeated'])) ?
 261  //                                        $cellDataTableAttributes['number-columns-repeated'] : 1;
 262  //                                    $cellDataOfficeAttributes = $cellData->attributes($namespacesContent['office']);
 263  //                                    if (isset($cellDataOfficeAttributes['value-type'])) {
 264  //                                        $tmpInfo['lastColumnIndex'] = max($tmpInfo['lastColumnIndex'], $columnIndex + $colRepeats - 1);
 265  //                                        $tmpInfo['totalRows'] = max($tmpInfo['totalRows'], $rowIndex + $rowRepeats);
 266  //                                    }
 267  //                                    $columnIndex += $colRepeats;
 268  //                                }
 269  //                                $rowIndex += $rowRepeats;
 270  //                                break;
 271  //                        }
 272  //                    }
 273  //
 274  //                    $tmpInfo['lastColumnLetter'] = PHPExcel_Cell::stringFromColumnIndex($tmpInfo['lastColumnIndex']);
 275  //                    $tmpInfo['totalColumns'] = $tmpInfo['lastColumnIndex'] + 1;
 276  //
 277  //                }
 278  //            }
 279          }
 280  
 281          return $worksheetInfo;
 282      }
 283  
 284      /**
 285       * Loads PHPExcel from file
 286       *
 287       * @param     string         $pFilename
 288       * @return     PHPExcel
 289       * @throws     PHPExcel_Reader_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      private static function identifyFixedStyleValue($styleList, &$styleAttributeValue)
 301      {
 302          $styleAttributeValue = strtolower($styleAttributeValue);
 303          foreach ($styleList as $style) {
 304              if ($styleAttributeValue == strtolower($style)) {
 305                  $styleAttributeValue = $style;
 306                  return true;
 307              }
 308          }
 309          return false;
 310      }
 311  
 312      /**
 313       * Loads PHPExcel from file into PHPExcel instance
 314       *
 315       * @param     string         $pFilename
 316       * @param    PHPExcel    $objPHPExcel
 317       * @return     PHPExcel
 318       * @throws     PHPExcel_Reader_Exception
 319       */
 320      public function loadIntoExisting($pFilename, PHPExcel $objPHPExcel)
 321      {
 322          // Check if file exists
 323          if (!file_exists($pFilename)) {
 324              throw new PHPExcel_Reader_Exception("Could not open " . $pFilename . " for reading! File does not exist.");
 325          }
 326  
 327          $timezoneObj = new DateTimeZone('Europe/London');
 328          $GMT = new DateTimeZone('UTC');
 329  
 330          $zipClass = PHPExcel_Settings::getZipClass();
 331  
 332          $zip = new $zipClass;
 333          if (!$zip->open($pFilename)) {
 334              throw new PHPExcel_Reader_Exception("Could not open " . $pFilename . " for reading! Error opening file.");
 335          }
 336  
 337  //        echo '<h1>Meta Information</h1>';
 338          $xml = simplexml_load_string($this->securityScan($zip->getFromName("meta.xml")), 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions());
 339          $namespacesMeta = $xml->getNamespaces(true);
 340  //        echo '<pre>';
 341  //        print_r($namespacesMeta);
 342  //        echo '</pre><hr />';
 343  
 344          $docProps = $objPHPExcel->getProperties();
 345          $officeProperty = $xml->children($namespacesMeta['office']);
 346          foreach ($officeProperty as $officePropertyData) {
 347              $officePropertyDC = array();
 348              if (isset($namespacesMeta['dc'])) {
 349                  $officePropertyDC = $officePropertyData->children($namespacesMeta['dc']);
 350              }
 351              foreach ($officePropertyDC as $propertyName => $propertyValue) {
 352                  $propertyValue = (string) $propertyValue;
 353                  switch ($propertyName) {
 354                      case 'title':
 355                          $docProps->setTitle($propertyValue);
 356                          break;
 357                      case 'subject':
 358                          $docProps->setSubject($propertyValue);
 359                          break;
 360                      case 'creator':
 361                          $docProps->setCreator($propertyValue);
 362                          $docProps->setLastModifiedBy($propertyValue);
 363                          break;
 364                      case 'date':
 365                          $creationDate = strtotime($propertyValue);
 366                          $docProps->setCreated($creationDate);
 367                          $docProps->setModified($creationDate);
 368                          break;
 369                      case 'description':
 370                          $docProps->setDescription($propertyValue);
 371                          break;
 372                  }
 373              }
 374              $officePropertyMeta = array();
 375              if (isset($namespacesMeta['dc'])) {
 376                  $officePropertyMeta = $officePropertyData->children($namespacesMeta['meta']);
 377              }
 378              foreach ($officePropertyMeta as $propertyName => $propertyValue) {
 379                  $propertyValueAttributes = $propertyValue->attributes($namespacesMeta['meta']);
 380                  $propertyValue = (string) $propertyValue;
 381                  switch ($propertyName) {
 382                      case 'initial-creator':
 383                          $docProps->setCreator($propertyValue);
 384                          break;
 385                      case 'keyword':
 386                          $docProps->setKeywords($propertyValue);
 387                          break;
 388                      case 'creation-date':
 389                          $creationDate = strtotime($propertyValue);
 390                          $docProps->setCreated($creationDate);
 391                          break;
 392                      case 'user-defined':
 393                          $propertyValueType = PHPExcel_DocumentProperties::PROPERTY_TYPE_STRING;
 394                          foreach ($propertyValueAttributes as $key => $value) {
 395                              if ($key == 'name') {
 396                                  $propertyValueName = (string) $value;
 397                              } elseif ($key == 'value-type') {
 398                                  switch ($value) {
 399                                      case 'date':
 400                                          $propertyValue = PHPExcel_DocumentProperties::convertProperty($propertyValue, 'date');
 401                                          $propertyValueType = PHPExcel_DocumentProperties::PROPERTY_TYPE_DATE;
 402                                          break;
 403                                      case 'boolean':
 404                                          $propertyValue = PHPExcel_DocumentProperties::convertProperty($propertyValue, 'bool');
 405                                          $propertyValueType = PHPExcel_DocumentProperties::PROPERTY_TYPE_BOOLEAN;
 406                                          break;
 407                                      case 'float':
 408                                          $propertyValue = PHPExcel_DocumentProperties::convertProperty($propertyValue, 'r4');
 409                                          $propertyValueType = PHPExcel_DocumentProperties::PROPERTY_TYPE_FLOAT;
 410                                          break;
 411                                      default:
 412                                          $propertyValueType = PHPExcel_DocumentProperties::PROPERTY_TYPE_STRING;
 413                                  }
 414                              }
 415                          }
 416                          $docProps->setCustomProperty($propertyValueName, $propertyValue, $propertyValueType);
 417                          break;
 418                  }
 419              }
 420          }
 421  
 422  
 423  //        echo '<h1>Workbook Content</h1>';
 424          $xml = simplexml_load_string($this->securityScan($zip->getFromName("content.xml")), 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions());
 425          $namespacesContent = $xml->getNamespaces(true);
 426  //        echo '<pre>';
 427  //        print_r($namespacesContent);
 428  //        echo '</pre><hr />';
 429  
 430          $workbook = $xml->children($namespacesContent['office']);
 431          foreach ($workbook->body->spreadsheet as $workbookData) {
 432              $workbookData = $workbookData->children($namespacesContent['table']);
 433              $worksheetID = 0;
 434              foreach ($workbookData->table as $worksheetDataSet) {
 435                  $worksheetData = $worksheetDataSet->children($namespacesContent['table']);
 436  //                print_r($worksheetData);
 437  //                echo '<br />';
 438                  $worksheetDataAttributes = $worksheetDataSet->attributes($namespacesContent['table']);
 439  //                print_r($worksheetDataAttributes);
 440  //                echo '<br />';
 441                  if ((isset($this->loadSheetsOnly)) && (isset($worksheetDataAttributes['name'])) &&
 442                      (!in_array($worksheetDataAttributes['name'], $this->loadSheetsOnly))) {
 443                      continue;
 444                  }
 445  
 446  //                echo '<h2>Worksheet '.$worksheetDataAttributes['name'].'</h2>';
 447                  // Create new Worksheet
 448                  $objPHPExcel->createSheet();
 449                  $objPHPExcel->setActiveSheetIndex($worksheetID);
 450                  if (isset($worksheetDataAttributes['name'])) {
 451                      $worksheetName = (string) $worksheetDataAttributes['name'];
 452                      //    Use false for $updateFormulaCellReferences to prevent adjustment of worksheet references in
 453                      //        formula cells... during the load, all formulae should be correct, and we're simply
 454                      //        bringing the worksheet name in line with the formula, not the reverse
 455                      $objPHPExcel->getActiveSheet()->setTitle($worksheetName, false);
 456                  }
 457  
 458                  $rowID = 1;
 459                  foreach ($worksheetData as $key => $rowData) {
 460  //                    echo '<b>'.$key.'</b><br />';
 461                      switch ($key) {
 462                          case 'table-header-rows':
 463                              foreach ($rowData as $key => $cellData) {
 464                                  $rowData = $cellData;
 465                                  break;
 466                              }
 467                          case 'table-row':
 468                              $rowDataTableAttributes = $rowData->attributes($namespacesContent['table']);
 469                              $rowRepeats = (isset($rowDataTableAttributes['number-rows-repeated'])) ? $rowDataTableAttributes['number-rows-repeated'] : 1;
 470                              $columnID = 'A';
 471                              foreach ($rowData as $key => $cellData) {
 472                                  if ($this->getReadFilter() !== null) {
 473                                      if (!$this->getReadFilter()->readCell($columnID, $rowID, $worksheetName)) {
 474                                          continue;
 475                                      }
 476                                  }
 477  
 478  //                                echo '<b>'.$columnID.$rowID.'</b><br />';
 479                                  $cellDataText = (isset($namespacesContent['text'])) ? $cellData->children($namespacesContent['text']) : '';
 480                                  $cellDataOffice = $cellData->children($namespacesContent['office']);
 481                                  $cellDataOfficeAttributes = $cellData->attributes($namespacesContent['office']);
 482                                  $cellDataTableAttributes = $cellData->attributes($namespacesContent['table']);
 483  
 484  //                                echo 'Office Attributes: ';
 485  //                                print_r($cellDataOfficeAttributes);
 486  //                                echo '<br />Table Attributes: ';
 487  //                                print_r($cellDataTableAttributes);
 488  //                                echo '<br />Cell Data Text';
 489  //                                print_r($cellDataText);
 490  //                                echo '<br />';
 491  //
 492                                  $type = $formatting = $hyperlink = null;
 493                                  $hasCalculatedValue = false;
 494                                  $cellDataFormula = '';
 495                                  if (isset($cellDataTableAttributes['formula'])) {
 496                                      $cellDataFormula = $cellDataTableAttributes['formula'];
 497                                      $hasCalculatedValue = true;
 498                                  }
 499  
 500                                  if (isset($cellDataOffice->annotation)) {
 501  //                                    echo 'Cell has comment<br />';
 502                                      $annotationText = $cellDataOffice->annotation->children($namespacesContent['text']);
 503                                      $textArray = array();
 504                                      foreach ($annotationText as $t) {
 505                                          if (isset($t->span)) {
 506                                              foreach ($t->span as $text) {
 507                                                  $textArray[] = (string)$text;
 508                                              }
 509                                          } else {
 510                                              $textArray[] = (string) $t;
 511                                          }
 512                                      }
 513                                      $text = implode("\n", $textArray);
 514  //                                    echo $text, '<br />';
 515                                      $objPHPExcel->getActiveSheet()->getComment($columnID.$rowID)->setText($this->parseRichText($text));
 516  //                                                                    ->setAuthor( $author )
 517                                  }
 518  
 519                                  if (isset($cellDataText->p)) {
 520                                      // Consolidate if there are multiple p records (maybe with spans as well)
 521                                      $dataArray = array();
 522                                      // Text can have multiple text:p and within those, multiple text:span.
 523                                      // text:p newlines, but text:span does not.
 524                                      // Also, here we assume there is no text data is span fields are specified, since
 525                                      // we have no way of knowing proper positioning anyway.
 526                                      foreach ($cellDataText->p as $pData) {
 527                                          if (isset($pData->span)) {
 528                                              // span sections do not newline, so we just create one large string here
 529                                              $spanSection = "";
 530                                              foreach ($pData->span as $spanData) {
 531                                                  $spanSection .= $spanData;
 532                                              }
 533                                              array_push($dataArray, $spanSection);
 534                                          } else {
 535                                              array_push($dataArray, $pData);
 536                                          }
 537                                      }
 538                                      $allCellDataText = implode($dataArray, "\n");
 539  
 540  //                                    echo 'Value Type is '.$cellDataOfficeAttributes['value-type'].'<br />';
 541                                      switch ($cellDataOfficeAttributes['value-type']) {
 542                                          case 'string':
 543                                              $type = PHPExcel_Cell_DataType::TYPE_STRING;
 544                                              $dataValue = $allCellDataText;
 545                                              if (isset($dataValue->a)) {
 546                                                  $dataValue = $dataValue->a;
 547                                                  $cellXLinkAttributes = $dataValue->attributes($namespacesContent['xlink']);
 548                                                  $hyperlink = $cellXLinkAttributes['href'];
 549                                              }
 550                                              break;
 551                                          case 'boolean':
 552                                              $type = PHPExcel_Cell_DataType::TYPE_BOOL;
 553                                              $dataValue = ($allCellDataText == 'TRUE') ? true : false;
 554                                              break;
 555                                          case 'percentage':
 556                                              $type = PHPExcel_Cell_DataType::TYPE_NUMERIC;
 557                                              $dataValue = (float) $cellDataOfficeAttributes['value'];
 558                                              if (floor($dataValue) == $dataValue) {
 559                                                  $dataValue = (integer) $dataValue;
 560                                              }
 561                                              $formatting = PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE_00;
 562                                              break;
 563                                          case 'currency':
 564                                              $type = PHPExcel_Cell_DataType::TYPE_NUMERIC;
 565                                              $dataValue = (float) $cellDataOfficeAttributes['value'];
 566                                              if (floor($dataValue) == $dataValue) {
 567                                                  $dataValue = (integer) $dataValue;
 568                                              }
 569                                              $formatting = PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD_SIMPLE;
 570                                              break;
 571                                          case 'float':
 572                                              $type = PHPExcel_Cell_DataType::TYPE_NUMERIC;
 573                                              $dataValue = (float) $cellDataOfficeAttributes['value'];
 574                                              if (floor($dataValue) == $dataValue) {
 575                                                  if ($dataValue == (integer) $dataValue) {
 576                                                      $dataValue = (integer) $dataValue;
 577                                                  } else {
 578                                                      $dataValue = (float) $dataValue;
 579                                                  }
 580                                              }
 581                                              break;
 582                                          case 'date':
 583                                              $type = PHPExcel_Cell_DataType::TYPE_NUMERIC;
 584                                              $dateObj = new DateTime($cellDataOfficeAttributes['date-value'], $GMT);
 585                                              $dateObj->setTimeZone($timezoneObj);
 586                                              list($year, $month, $day, $hour, $minute, $second) = explode(' ', $dateObj->format('Y m d H i s'));
 587                                              $dataValue = PHPExcel_Shared_Date::FormattedPHPToExcel($year, $month, $day, $hour, $minute, $second);
 588                                              if ($dataValue != floor($dataValue)) {
 589                                                  $formatting = PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX15.' '.PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME4;
 590                                              } else {
 591                                                  $formatting = PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX15;
 592                                              }
 593                                              break;
 594                                          case 'time':
 595                                              $type = PHPExcel_Cell_DataType::TYPE_NUMERIC;
 596                                              $dataValue = PHPExcel_Shared_Date::PHPToExcel(strtotime('01-01-1970 '.implode(':', sscanf($cellDataOfficeAttributes['time-value'], 'PT%dH%dM%dS'))));
 597                                              $formatting = PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME4;
 598                                              break;
 599                                      }
 600  //                                    echo 'Data value is '.$dataValue.'<br />';
 601  //                                    if ($hyperlink !== null) {
 602  //                                        echo 'Hyperlink is '.$hyperlink.'<br />';
 603  //                                    }
 604                                  } else {
 605                                      $type = PHPExcel_Cell_DataType::TYPE_NULL;
 606                                      $dataValue = null;
 607                                  }
 608  
 609                                  if ($hasCalculatedValue) {
 610                                      $type = PHPExcel_Cell_DataType::TYPE_FORMULA;
 611  //                                    echo 'Formula: ', $cellDataFormula, PHP_EOL;
 612                                      $cellDataFormula = substr($cellDataFormula, strpos($cellDataFormula, ':=')+1);
 613                                      $temp = explode('"', $cellDataFormula);
 614                                      $tKey = false;
 615                                      foreach ($temp as &$value) {
 616                                          //    Only replace in alternate array entries (i.e. non-quoted blocks)
 617                                          if ($tKey = !$tKey) {
 618                                              $value = preg_replace('/\[([^\.]+)\.([^\.]+):\.([^\.]+)\]/Ui', '$1!$2:$3', $value);    //  Cell range reference in another sheet
 619                                              $value = preg_replace('/\[([^\.]+)\.([^\.]+)\]/Ui', '$1!$2', $value);       //  Cell reference in another sheet
 620                                              $value = preg_replace('/\[\.([^\.]+):\.([^\.]+)\]/Ui', '$1:$2', $value);    //  Cell range reference
 621                                              $value = preg_replace('/\[\.([^\.]+)\]/Ui', '$1', $value);                  //  Simple cell reference
 622                                              $value = PHPExcel_Calculation::translateSeparator(';', ',', $value, $inBraces);
 623                                          }
 624                                      }
 625                                      unset($value);
 626                                      //    Then rebuild the formula string
 627                                      $cellDataFormula = implode('"', $temp);
 628  //                                    echo 'Adjusted Formula: ', $cellDataFormula, PHP_EOL;
 629                                  }
 630  
 631                                  $colRepeats = (isset($cellDataTableAttributes['number-columns-repeated'])) ? $cellDataTableAttributes['number-columns-repeated'] : 1;
 632                                  if ($type !== null) {
 633                                      for ($i = 0; $i < $colRepeats; ++$i) {
 634                                          if ($i > 0) {
 635                                              ++$columnID;
 636                                          }
 637                                          if ($type !== PHPExcel_Cell_DataType::TYPE_NULL) {
 638                                              for ($rowAdjust = 0; $rowAdjust < $rowRepeats; ++$rowAdjust) {
 639                                                  $rID = $rowID + $rowAdjust;
 640                                                  $objPHPExcel->getActiveSheet()->getCell($columnID.$rID)->setValueExplicit((($hasCalculatedValue) ? $cellDataFormula : $dataValue), $type);
 641                                                  if ($hasCalculatedValue) {
 642  //                                                    echo 'Forumla result is '.$dataValue.'<br />';
 643                                                      $objPHPExcel->getActiveSheet()->getCell($columnID.$rID)->setCalculatedValue($dataValue);
 644                                                  }
 645                                                  if ($formatting !== null) {
 646                                                      $objPHPExcel->getActiveSheet()->getStyle($columnID.$rID)->getNumberFormat()->setFormatCode($formatting);
 647                                                  } else {
 648                                                      $objPHPExcel->getActiveSheet()->getStyle($columnID.$rID)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_GENERAL);
 649                                                  }
 650                                                  if ($hyperlink !== null) {
 651                                                      $objPHPExcel->getActiveSheet()->getCell($columnID.$rID)->getHyperlink()->setUrl($hyperlink);
 652                                                  }
 653                                              }
 654                                          }
 655                                      }
 656                                  }
 657  
 658                                  //    Merged cells
 659                                  if ((isset($cellDataTableAttributes['number-columns-spanned'])) || (isset($cellDataTableAttributes['number-rows-spanned']))) {
 660                                      if (($type !== PHPExcel_Cell_DataType::TYPE_NULL) || (!$this->readDataOnly)) {
 661                                          $columnTo = $columnID;
 662                                          if (isset($cellDataTableAttributes['number-columns-spanned'])) {
 663                                              $columnTo = PHPExcel_Cell::stringFromColumnIndex(PHPExcel_Cell::columnIndexFromString($columnID) + $cellDataTableAttributes['number-columns-spanned'] -2);
 664                                          }
 665                                          $rowTo = $rowID;
 666                                          if (isset($cellDataTableAttributes['number-rows-spanned'])) {
 667                                              $rowTo = $rowTo + $cellDataTableAttributes['number-rows-spanned'] - 1;
 668                                          }
 669                                          $cellRange = $columnID.$rowID.':'.$columnTo.$rowTo;
 670                                          $objPHPExcel->getActiveSheet()->mergeCells($cellRange);
 671                                      }
 672                                  }
 673  
 674                                  ++$columnID;
 675                              }
 676                              $rowID += $rowRepeats;
 677                              break;
 678                      }
 679                  }
 680                  ++$worksheetID;
 681              }
 682          }
 683  
 684          // Return
 685          return $objPHPExcel;
 686      }
 687  
 688      private function parseRichText($is = '')
 689      {
 690          $value = new PHPExcel_RichText();
 691  
 692          $value->createText($is);
 693  
 694          return $value;
 695      }
 696  }