ILIAS  eassessment Revision 61809
 All Data Structures Namespaces Files Functions Variables Groups Pages
Excel2007.php
Go to the documentation of this file.
1 <?php
30 if (!defined('PHPEXCEL_ROOT')) {
34  define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../../');
35  require(PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php');
36 }
37 
45 class PHPExcel_Reader_Excel2007 implements PHPExcel_Reader_IReader
46 {
54  private $_readDataOnly = false;
55 
62  private $_loadSheetsOnly = null;
63 
69  private $_readFilter = null;
70 
76  private $_referenceHelper = null;
77 
83  private static $_theme = null;
84 
85 
93  public function getReadDataOnly() {
94  return $this->_readDataOnly;
95  }
96 
106  public function setReadDataOnly($pValue = false) {
107  $this->_readDataOnly = $pValue;
108  return $this;
109  }
110 
118  public function getLoadSheetsOnly()
119  {
120  return $this->_loadSheetsOnly;
121  }
122 
132  public function setLoadSheetsOnly($value = null)
133  {
134  $this->_loadSheetsOnly = is_array($value) ?
135  $value : array($value);
136  return $this;
137  }
138 
145  public function setLoadAllSheets()
146  {
147  $this->_loadSheetsOnly = null;
148  return $this;
149  }
150 
156  public function getReadFilter() {
157  return $this->_readFilter;
158  }
159 
166  public function setReadFilter(PHPExcel_Reader_IReadFilter $pValue) {
167  $this->_readFilter = $pValue;
168  return $this;
169  }
170 
174  public function __construct() {
175  $this->_readFilter = new PHPExcel_Reader_DefaultReadFilter();
176  $this->_referenceHelper = PHPExcel_ReferenceHelper::getInstance();
177  }
178 
185  public function canRead($pFilename)
186  {
187  // Check if zip class exists
188  if (!class_exists('ZipArchive')) {
189  return false;
190  }
191 
192  // Check if file exists
193  if (!file_exists($pFilename)) {
194  throw new Exception("Could not open " . $pFilename . " for reading! File does not exist.");
195  }
196 
197  $xl = false;
198  // Load file
199  $zip = new ZipArchive;
200  if ($zip->open($pFilename) === true) {
201  // check if it is an OOXML archive
202  $rels = simplexml_load_string($this->_getFromZipArchive($zip, "_rels/.rels"));
203  foreach ($rels->Relationship as $rel) {
204  switch ($rel["Type"]) {
205  case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument":
206  if (basename($rel["Target"]) == 'workbook.xml') {
207  $xl = true;
208  }
209  break;
210 
211  }
212  }
213  $zip->close();
214  }
215 
216  return $xl;
217  }
218 
219  private static function _castToBool($c) {
220 // echo 'Initial Cast to Boolean<br />';
221  $value = isset($c->v) ? (string) $c->v : null;
222  if ($value == '0') {
223  return false;
224  } elseif ($value == '1') {
225  return true;
226  } else {
227  return (bool)$c->v;
228  }
229  return $value;
230  } // function _castToBool()
231 
232  private static function _castToError($c) {
233 // echo 'Initial Cast to Error<br />';
234  return isset($c->v) ? (string) $c->v : null;;
235  } // function _castToError()
236 
237  private static function _castToString($c) {
238 // echo 'Initial Cast to String<br />';
239  return isset($c->v) ? (string) $c->v : null;;
240  } // function _castToString()
241 
242  private function _castToFormula($c,$r,&$cellDataType,&$value,&$calculatedValue,&$sharedFormulas,$castBaseType) {
243 // echo '<font color="darkgreen">Formula</font><br />';
244 // echo '$c->f is '.$c->f.'<br />';
245  $cellDataType = 'f';
246  $value = "={$c->f}";
247  $calculatedValue = self::$castBaseType($c);
248 
249  // Shared formula?
250  if (isset($c->f['t']) && strtolower((string)$c->f['t']) == 'shared') {
251 // echo '<font color="darkgreen">SHARED FORMULA</font><br />';
252  $instance = (string)$c->f['si'];
253 
254 // echo 'Instance ID = '.$instance.'<br />';
255 //
256 // echo 'Shared Formula Array:<pre>';
257 // print_r($sharedFormulas);
258 // echo '</pre>';
259  if (!isset($sharedFormulas[(string)$c->f['si']])) {
260 // echo '<font color="darkgreen">SETTING NEW SHARED FORMULA</font><br />';
261 // echo 'Master is '.$r.'<br />';
262 // echo 'Formula is '.$value.'<br />';
263  $sharedFormulas[$instance] = array( 'master' => $r,
264  'formula' => $value
265  );
266 // echo 'New Shared Formula Array:<pre>';
267 // print_r($sharedFormulas);
268 // echo '</pre>';
269  } else {
270 // echo '<font color="darkgreen">GETTING SHARED FORMULA</font><br />';
271 // echo 'Master is '.$sharedFormulas[$instance]['master'].'<br />';
272 // echo 'Formula is '.$sharedFormulas[$instance]['formula'].'<br />';
273  $master = PHPExcel_Cell::coordinateFromString($sharedFormulas[$instance]['master']);
274  $current = PHPExcel_Cell::coordinateFromString($r);
275 
276  $difference = array(0, 0);
277  $difference[0] = PHPExcel_Cell::columnIndexFromString($current[0]) - PHPExcel_Cell::columnIndexFromString($master[0]);
278  $difference[1] = $current[1] - $master[1];
279 
280  $value = $this->_referenceHelper->updateFormulaReferences( $sharedFormulas[$instance]['formula'],
281  'A1',
282  $difference[0],
283  $difference[1]
284  );
285 // echo 'Adjusted Formula is '.$value.'<br />';
286  }
287  }
288  }
289 
290  public function _getFromZipArchive(ZipArchive $archive, $fileName = '')
291  {
292  // Root-relative paths
293  if (strpos($fileName, '//') !== false)
294  {
295  $fileName = substr($fileName, strpos($fileName, '//') + 1);
296  }
297  $fileName = PHPExcel_Shared_File::realpath($fileName);
298 
299  // Apache POI fixes
300  $contents = $archive->getFromName($fileName);
301  if ($contents === false)
302  {
303  $contents = $archive->getFromName(substr($fileName, 1));
304  }
305 
306  return $contents;
307  }
308 
315  public function listWorksheetNames($pFilename)
316  {
317  // Check if file exists
318  if (!file_exists($pFilename)) {
319  throw new Exception("Could not open " . $pFilename . " for reading! File does not exist.");
320  }
321 
322  $worksheetNames = array();
323 
324  $zip = new ZipArchive;
325  $zip->open($pFilename);
326 
327  $rels = simplexml_load_string($this->_getFromZipArchive($zip, "_rels/.rels")); //~ http://schemas.openxmlformats.org/package/2006/relationships");
328  foreach ($rels->Relationship as $rel) {
329  switch ($rel["Type"]) {
330  case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument":
331  $xmlWorkbook = simplexml_load_string($this->_getFromZipArchive($zip, "{$rel['Target']}")); //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
332 
333  if ($xmlWorkbook->sheets) {
334  foreach ($xmlWorkbook->sheets->sheet as $eleSheet) {
335  // Check if sheet should be skipped
336  $worksheetNames[] = (string) $eleSheet["name"];
337  }
338  }
339  }
340  }
341 
342  $zip->close();
343 
344  return $worksheetNames;
345  }
346 
347 
354  public function load($pFilename)
355  {
356  // Check if file exists
357  if (!file_exists($pFilename)) {
358  throw new Exception("Could not open " . $pFilename . " for reading! File does not exist.");
359  }
360 
361  // Initialisations
362  $excel = new PHPExcel;
363  $excel->removeSheetByIndex(0);
364  if (!$this->_readDataOnly) {
365  $excel->removeCellStyleXfByIndex(0); // remove the default style
366  $excel->removeCellXfByIndex(0); // remove the default style
367  }
368  $zip = new ZipArchive;
369  $zip->open($pFilename);
370 
371  // Read the theme first, because we need the colour scheme when reading the styles
372  $wbRels = simplexml_load_string($this->_getFromZipArchive($zip, "xl/_rels/workbook.xml.rels")); //~ http://schemas.openxmlformats.org/package/2006/relationships");
373  foreach ($wbRels->Relationship as $rel) {
374  switch ($rel["Type"]) {
375  case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/theme":
376  $themeOrderArray = array('lt1','dk1','lt2','dk2');
377  $themeOrderAdditional = count($themeOrderArray);
378 
379  $xmlTheme = simplexml_load_string($this->_getFromZipArchive($zip, "xl/{$rel['Target']}"));
380  if (is_object($xmlTheme)) {
381  $xmlThemeName = $xmlTheme->attributes();
382  $xmlTheme = $xmlTheme->children("http://schemas.openxmlformats.org/drawingml/2006/main");
383  $themeName = (string)$xmlThemeName['name'];
384 
385  $colourScheme = $xmlTheme->themeElements->clrScheme->attributes();
386  $colourSchemeName = (string)$colourScheme['name'];
387  $colourScheme = $xmlTheme->themeElements->clrScheme->children("http://schemas.openxmlformats.org/drawingml/2006/main");
388 
389  $themeColours = array();
390  foreach ($colourScheme as $k => $xmlColour) {
391  $themePos = array_search($k,$themeOrderArray);
392  if ($themePos === false) {
393  $themePos = $themeOrderAdditional++;
394  }
395  if (isset($xmlColour->sysClr)) {
396  $xmlColourData = $xmlColour->sysClr->attributes();
397  $themeColours[$themePos] = $xmlColourData['lastClr'];
398  } elseif (isset($xmlColour->srgbClr)) {
399  $xmlColourData = $xmlColour->srgbClr->attributes();
400  $themeColours[$themePos] = $xmlColourData['val'];
401  }
402  }
403  self::$_theme = new PHPExcel_Reader_Excel2007_Theme($themeName,$colourSchemeName,$themeColours);
404  }
405  break;
406  }
407  }
408 
409  $rels = simplexml_load_string($this->_getFromZipArchive($zip, "_rels/.rels")); //~ http://schemas.openxmlformats.org/package/2006/relationships");
410  foreach ($rels->Relationship as $rel) {
411  switch ($rel["Type"]) {
412  case "http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties":
413  $xmlCore = simplexml_load_string($this->_getFromZipArchive($zip, "{$rel['Target']}"));
414  if (is_object($xmlCore)) {
415  $xmlCore->registerXPathNamespace("dc", "http://purl.org/dc/elements/1.1/");
416  $xmlCore->registerXPathNamespace("dcterms", "http://purl.org/dc/terms/");
417  $xmlCore->registerXPathNamespace("cp", "http://schemas.openxmlformats.org/package/2006/metadata/core-properties");
418  $docProps = $excel->getProperties();
419  $docProps->setCreator((string) self::array_item($xmlCore->xpath("dc:creator")));
420  $docProps->setLastModifiedBy((string) self::array_item($xmlCore->xpath("cp:lastModifiedBy")));
421  $docProps->setCreated(strtotime(self::array_item($xmlCore->xpath("dcterms:created"))));
422  $docProps->setModified(strtotime(self::array_item($xmlCore->xpath("dcterms:modified"))));
423  $docProps->setTitle((string) self::array_item($xmlCore->xpath("dc:title")));
424  $docProps->setDescription((string) self::array_item($xmlCore->xpath("dc:description")));
425  $docProps->setSubject((string) self::array_item($xmlCore->xpath("dc:subject")));
426  $docProps->setKeywords((string) self::array_item($xmlCore->xpath("cp:keywords")));
427  $docProps->setCategory((string) self::array_item($xmlCore->xpath("cp:category")));
428  }
429  break;
430 
431  case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties":
432  $xmlCore = simplexml_load_string($this->_getFromZipArchive($zip, "{$rel['Target']}"));
433  if (is_object($xmlCore)) {
434  $docProps = $excel->getProperties();
435  if (isset($xmlCore->Company))
436  $docProps->setCompany((string) $xmlCore->Company);
437  if (isset($xmlCore->Manager))
438  $docProps->setManager((string) $xmlCore->Manager);
439  }
440  break;
441 
442  case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/custom-properties":
443  $xmlCore = simplexml_load_string($this->_getFromZipArchive($zip, "{$rel['Target']}"));
444  if (is_object($xmlCore)) {
445  $docProps = $excel->getProperties();
446  foreach ($xmlCore as $xmlProperty) {
447  $cellDataOfficeAttributes = $xmlProperty->attributes();
448  if (isset($cellDataOfficeAttributes['name'])) {
449  $propertyName = (string) $cellDataOfficeAttributes['name'];
450  $cellDataOfficeChildren = $xmlProperty->children('http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes');
451  $attributeType = $cellDataOfficeChildren->getName();
452  $attributeValue = (string) $cellDataOfficeChildren->{$attributeType};
453  $attributeValue = PHPExcel_DocumentProperties::convertProperty($attributeValue,$attributeType);
454  $attributeType = PHPExcel_DocumentProperties::convertPropertyType($attributeType);
455  $docProps->setCustomProperty($propertyName,$attributeValue,$attributeType);
456  }
457  }
458  }
459  break;
460 
461  case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument":
462  $dir = dirname($rel["Target"]);
463  $relsWorkbook = simplexml_load_string($this->_getFromZipArchive($zip, "$dir/_rels/" . basename($rel["Target"]) . ".rels")); //~ http://schemas.openxmlformats.org/package/2006/relationships");
464  $relsWorkbook->registerXPathNamespace("rel", "http://schemas.openxmlformats.org/package/2006/relationships");
465 
466  $sharedStrings = array();
467  $xpath = self::array_item($relsWorkbook->xpath("rel:Relationship[@Type='http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings']"));
468  $xmlStrings = simplexml_load_string($this->_getFromZipArchive($zip, "$dir/$xpath[Target]")); //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
469  if (isset($xmlStrings) && isset($xmlStrings->si)) {
470  foreach ($xmlStrings->si as $val) {
471  if (isset($val->t)) {
472  $sharedStrings[] = PHPExcel_Shared_String::ControlCharacterOOXML2PHP( (string) $val->t );
473  } elseif (isset($val->r)) {
474  $sharedStrings[] = $this->_parseRichText($val);
475  }
476  }
477  }
478 
479  $worksheets = array();
480  foreach ($relsWorkbook->Relationship as $ele) {
481  if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet") {
482  $worksheets[(string) $ele["Id"]] = $ele["Target"];
483  }
484  }
485 
486  $styles = array();
487  $cellStyles = array();
488  $xpath = self::array_item($relsWorkbook->xpath("rel:Relationship[@Type='http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles']"));
489  $xmlStyles = simplexml_load_string($this->_getFromZipArchive($zip, "$dir/$xpath[Target]")); //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
490  $numFmts = null;
491  if ($xmlStyles && $xmlStyles->numFmts[0]) {
492  $numFmts = $xmlStyles->numFmts[0];
493  }
494  if (isset($numFmts) && !is_null($numFmts)) {
495  $numFmts->registerXPathNamespace("sml", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
496  }
497  if (!$this->_readDataOnly && $xmlStyles) {
498  foreach ($xmlStyles->cellXfs->xf as $xf) {
499  $numFmt = PHPExcel_Style_NumberFormat::FORMAT_GENERAL;
500 
501  if ($xf["numFmtId"]) {
502  if (isset($numFmts)) {
503  $tmpNumFmt = self::array_item($numFmts->xpath("sml:numFmt[@numFmtId=$xf[numFmtId]]"));
504 
505  if (isset($tmpNumFmt["formatCode"])) {
506  $numFmt = (string) $tmpNumFmt["formatCode"];
507  }
508  }
509 
510  if ((int)$xf["numFmtId"] < 164) {
511  $numFmt = PHPExcel_Style_NumberFormat::builtInFormatCode((int)$xf["numFmtId"]);
512  }
513  }
514  //$numFmt = str_replace('mm', 'i', $numFmt);
515  //$numFmt = str_replace('h', 'H', $numFmt);
516 
517  $style = (object) array(
518  "numFmt" => $numFmt,
519  "font" => $xmlStyles->fonts->font[intval($xf["fontId"])],
520  "fill" => $xmlStyles->fills->fill[intval($xf["fillId"])],
521  "border" => $xmlStyles->borders->border[intval($xf["borderId"])],
522  "alignment" => $xf->alignment,
523  "protection" => $xf->protection,
524  );
525  $styles[] = $style;
526 
527  // add style to cellXf collection
528  $objStyle = new PHPExcel_Style;
529  self::_readStyle($objStyle, $style);
530  $excel->addCellXf($objStyle);
531  }
532 
533  foreach ($xmlStyles->cellStyleXfs->xf as $xf) {
534  $numFmt = PHPExcel_Style_NumberFormat::FORMAT_GENERAL;
535  if ($numFmts && $xf["numFmtId"]) {
536  $tmpNumFmt = self::array_item($numFmts->xpath("sml:numFmt[@numFmtId=$xf[numFmtId]]"));
537  if (isset($tmpNumFmt["formatCode"])) {
538  $numFmt = (string) $tmpNumFmt["formatCode"];
539  } else if ((int)$xf["numFmtId"] < 165) {
540  $numFmt = PHPExcel_Style_NumberFormat::builtInFormatCode((int)$xf["numFmtId"]);
541  }
542  }
543 
544  $cellStyle = (object) array(
545  "numFmt" => $numFmt,
546  "font" => $xmlStyles->fonts->font[intval($xf["fontId"])],
547  "fill" => $xmlStyles->fills->fill[intval($xf["fillId"])],
548  "border" => $xmlStyles->borders->border[intval($xf["borderId"])],
549  "alignment" => $xf->alignment,
550  "protection" => $xf->protection,
551  );
552  $cellStyles[] = $cellStyle;
553 
554  // add style to cellStyleXf collection
555  $objStyle = new PHPExcel_Style;
556  self::_readStyle($objStyle, $cellStyle);
557  $excel->addCellStyleXf($objStyle);
558  }
559  }
560 
561  $dxfs = array();
562  if (!$this->_readDataOnly && $xmlStyles) {
563  if ($xmlStyles->dxfs) {
564  foreach ($xmlStyles->dxfs->dxf as $dxf) {
565  $style = new PHPExcel_Style;
566  self::_readStyle($style, $dxf);
567  $dxfs[] = $style;
568  }
569  }
570 
571  if ($xmlStyles->cellStyles)
572  {
573  foreach ($xmlStyles->cellStyles->cellStyle as $cellStyle) {
574  if (intval($cellStyle['builtinId']) == 0) {
575  if (isset($cellStyles[intval($cellStyle['xfId'])])) {
576  // Set default style
577  $style = new PHPExcel_Style;
578  self::_readStyle($style, $cellStyles[intval($cellStyle['xfId'])]);
579 
580  // normal style, currently not using it for anything
581  }
582  }
583  }
584  }
585  }
586 
587  $xmlWorkbook = simplexml_load_string($this->_getFromZipArchive($zip, "{$rel['Target']}")); //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
588 
589  // Set base date
590  if ($xmlWorkbook->workbookPr) {
591  PHPExcel_Shared_Date::setExcelCalendar(PHPExcel_Shared_Date::CALENDAR_WINDOWS_1900);
592  if (isset($xmlWorkbook->workbookPr['date1904'])) {
593  $date1904 = (string)$xmlWorkbook->workbookPr['date1904'];
594  if ($date1904 == "true" || $date1904 == "1") {
595  PHPExcel_Shared_Date::setExcelCalendar(PHPExcel_Shared_Date::CALENDAR_MAC_1904);
596  }
597  }
598  }
599 
600  $sheetId = 0; // keep track of new sheet id in final workbook
601  $oldSheetId = -1; // keep track of old sheet id in final workbook
602  $countSkippedSheets = 0; // keep track of number of skipped sheets
603  $mapSheetId = array(); // mapping of sheet ids from old to new
604 
605  if ($xmlWorkbook->sheets)
606  {
607  foreach ($xmlWorkbook->sheets->sheet as $eleSheet) {
608  ++$oldSheetId;
609 
610  // Check if sheet should be skipped
611  if (isset($this->_loadSheetsOnly) && !in_array((string) $eleSheet["name"], $this->_loadSheetsOnly)) {
612  ++$countSkippedSheets;
613  $mapSheetId[$oldSheetId] = null;
614  continue;
615  }
616 
617  // Map old sheet id in original workbook to new sheet id.
618  // They will differ if loadSheetsOnly() is being used
619  $mapSheetId[$oldSheetId] = $oldSheetId - $countSkippedSheets;
620 
621  // Load sheet
622  $docSheet = $excel->createSheet();
623  $docSheet->setTitle((string) $eleSheet["name"]);
624  $fileWorksheet = $worksheets[(string) self::array_item($eleSheet->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "id")];
625  $xmlSheet = simplexml_load_string($this->_getFromZipArchive($zip, "$dir/$fileWorksheet")); //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
626 
627  $sharedFormulas = array();
628 
629  if (isset($eleSheet["state"]) && (string) $eleSheet["state"] != '') {
630  $docSheet->setSheetState( (string) $eleSheet["state"] );
631  }
632 
633  if (isset($xmlSheet->sheetViews) && isset($xmlSheet->sheetViews->sheetView)) {
634  if (isset($xmlSheet->sheetViews->sheetView['zoomScale'])) {
635  $docSheet->getSheetView()->setZoomScale( intval($xmlSheet->sheetViews->sheetView['zoomScale']) );
636  }
637 
638  if (isset($xmlSheet->sheetViews->sheetView['zoomScaleNormal'])) {
639  $docSheet->getSheetView()->setZoomScaleNormal( intval($xmlSheet->sheetViews->sheetView['zoomScaleNormal']) );
640  }
641 
642  if (isset($xmlSheet->sheetViews->sheetView['showGridLines'])) {
643  $docSheet->setShowGridLines((string)$xmlSheet->sheetViews->sheetView['showGridLines'] ? true : false);
644  }
645 
646  if (isset($xmlSheet->sheetViews->sheetView['showRowColHeaders'])) {
647  $docSheet->setShowRowColHeaders((string)$xmlSheet->sheetViews->sheetView['showRowColHeaders'] ? true : false);
648  }
649 
650  if (isset($xmlSheet->sheetViews->sheetView['rightToLeft'])) {
651  $docSheet->setRightToLeft((string)$xmlSheet->sheetViews->sheetView['rightToLeft'] ? true : false);
652  }
653 
654  if (isset($xmlSheet->sheetViews->sheetView->pane)) {
655  if (isset($xmlSheet->sheetViews->sheetView->pane['topLeftCell'])) {
656  $docSheet->freezePane( (string)$xmlSheet->sheetViews->sheetView->pane['topLeftCell'] );
657  } else {
658  $xSplit = 0;
659  $ySplit = 0;
660 
661  if (isset($xmlSheet->sheetViews->sheetView->pane['xSplit'])) {
662  $xSplit = 1 + intval($xmlSheet->sheetViews->sheetView->pane['xSplit']);
663  }
664 
665  if (isset($xmlSheet->sheetViews->sheetView->pane['ySplit'])) {
666  $ySplit = 1 + intval($xmlSheet->sheetViews->sheetView->pane['ySplit']);
667  }
668 
669  $docSheet->freezePaneByColumnAndRow($xSplit, $ySplit);
670  }
671  }
672 
673  if (isset($xmlSheet->sheetViews->sheetView->selection)) {
674  if (isset($xmlSheet->sheetViews->sheetView->selection['sqref'])) {
675  $sqref = (string)$xmlSheet->sheetViews->sheetView->selection['sqref'];
676  $sqref = explode(' ', $sqref);
677  $sqref = $sqref[0];
678  $docSheet->setSelectedCells($sqref);
679  }
680  }
681 
682  }
683 
684  if (isset($xmlSheet->sheetPr) && isset($xmlSheet->sheetPr->tabColor)) {
685  if (isset($xmlSheet->sheetPr->tabColor['rgb'])) {
686  $docSheet->getTabColor()->setARGB( (string)$xmlSheet->sheetPr->tabColor['rgb'] );
687  }
688  }
689 
690  if (isset($xmlSheet->sheetPr) && isset($xmlSheet->sheetPr->outlinePr)) {
691  if (isset($xmlSheet->sheetPr->outlinePr['summaryRight']) && $xmlSheet->sheetPr->outlinePr['summaryRight'] == false) {
692  $docSheet->setShowSummaryRight(false);
693  } else {
694  $docSheet->setShowSummaryRight(true);
695  }
696 
697  if (isset($xmlSheet->sheetPr->outlinePr['summaryBelow']) && $xmlSheet->sheetPr->outlinePr['summaryBelow'] == false) {
698  $docSheet->setShowSummaryBelow(false);
699  } else {
700  $docSheet->setShowSummaryBelow(true);
701  }
702  }
703 
704  if (isset($xmlSheet->sheetPr) && isset($xmlSheet->sheetPr->pageSetUpPr)) {
705  if (isset($xmlSheet->sheetPr->pageSetUpPr['fitToPage']) && $xmlSheet->sheetPr->pageSetUpPr['fitToPage'] == false) {
706  $docSheet->getPageSetup()->setFitToPage(false);
707  } else {
708  $docSheet->getPageSetup()->setFitToPage(true);
709  }
710  }
711 
712  if (isset($xmlSheet->sheetFormatPr)) {
713  if (isset($xmlSheet->sheetFormatPr['customHeight']) && ((string)$xmlSheet->sheetFormatPr['customHeight'] == '1' || strtolower((string)$xmlSheet->sheetFormatPr['customHeight']) == 'true') && isset($xmlSheet->sheetFormatPr['defaultRowHeight'])) {
714  $docSheet->getDefaultRowDimension()->setRowHeight( (float)$xmlSheet->sheetFormatPr['defaultRowHeight'] );
715  }
716  if (isset($xmlSheet->sheetFormatPr['defaultColWidth'])) {
717  $docSheet->getDefaultColumnDimension()->setWidth( (float)$xmlSheet->sheetFormatPr['defaultColWidth'] );
718  }
719  }
720 
721  if (isset($xmlSheet->cols) && !$this->_readDataOnly) {
722  foreach ($xmlSheet->cols->col as $col) {
723  for ($i = intval($col["min"]) - 1; $i < intval($col["max"]); ++$i) {
724  if ($col["style"] && !$this->_readDataOnly) {
725  $docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setXfIndex(intval($col["style"]));
726  }
727  if ($col["bestFit"]) {
728  //$docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setAutoSize(true);
729  }
730  if ($col["hidden"]) {
731  $docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setVisible(false);
732  }
733  if ($col["collapsed"]) {
734  $docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setCollapsed(true);
735  }
736  if ($col["outlineLevel"] > 0) {
737  $docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setOutlineLevel(intval($col["outlineLevel"]));
738  }
739  $docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setWidth(floatval($col["width"]));
740 
741  if (intval($col["max"]) == 16384) {
742  break;
743  }
744  }
745  }
746  }
747 
748  if (isset($xmlSheet->printOptions) && !$this->_readDataOnly) {
749  if ($xmlSheet->printOptions['gridLinesSet'] == 'true' && $xmlSheet->printOptions['gridLinesSet'] == '1') {
750  $docSheet->setShowGridlines(true);
751  }
752 
753  if ($xmlSheet->printOptions['gridLines'] == 'true' || $xmlSheet->printOptions['gridLines'] == '1') {
754  $docSheet->setPrintGridlines(true);
755  }
756 
757  if ($xmlSheet->printOptions['horizontalCentered']) {
758  $docSheet->getPageSetup()->setHorizontalCentered(true);
759  }
760  if ($xmlSheet->printOptions['verticalCentered']) {
761  $docSheet->getPageSetup()->setVerticalCentered(true);
762  }
763  }
764 
765  if ($xmlSheet && $xmlSheet->sheetData && $xmlSheet->sheetData->row) {
766  foreach ($xmlSheet->sheetData->row as $row) {
767  if ($row["ht"] && !$this->_readDataOnly) {
768  $docSheet->getRowDimension(intval($row["r"]))->setRowHeight(floatval($row["ht"]));
769  }
770  if ($row["hidden"] && !$this->_readDataOnly) {
771  $docSheet->getRowDimension(intval($row["r"]))->setVisible(false);
772  }
773  if ($row["collapsed"]) {
774  $docSheet->getRowDimension(intval($row["r"]))->setCollapsed(true);
775  }
776  if ($row["outlineLevel"] > 0) {
777  $docSheet->getRowDimension(intval($row["r"]))->setOutlineLevel(intval($row["outlineLevel"]));
778  }
779  if ($row["s"] && !$this->_readDataOnly) {
780  $docSheet->getRowDimension(intval($row["r"]))->setXfIndex(intval($row["s"]));
781  }
782 
783  foreach ($row->c as $c) {
784  $r = (string) $c["r"];
785  $cellDataType = (string) $c["t"];
786  $value = null;
787  $calculatedValue = null;
788 
789  // Read cell?
790  if (!is_null($this->getReadFilter())) {
791  $coordinates = PHPExcel_Cell::coordinateFromString($r);
792 
793  if (!$this->getReadFilter()->readCell($coordinates[0], $coordinates[1], $docSheet->getTitle())) {
794  continue;
795  }
796  }
797 
798  // echo '<b>Reading cell '.$coordinates[0].$coordinates[1].'</b><br />';
799  // print_r($c);
800  // echo '<br />';
801  // echo 'Cell Data Type is '.$cellDataType.': ';
802  //
803  // Read cell!
804  switch ($cellDataType) {
805  case "s":
806  // echo 'String<br />';
807  if ((string)$c->v != '') {
808  $value = $sharedStrings[intval($c->v)];
809 
810  if ($value instanceof PHPExcel_RichText) {
811  $value = clone $value;
812  }
813  } else {
814  $value = '';
815  }
816 
817  break;
818  case "b":
819  // echo 'Boolean<br />';
820  if (!isset($c->f)) {
821  $value = self::_castToBool($c);
822  } else {
823  // Formula
824  $this->_castToFormula($c,$r,$cellDataType,$value,$calculatedValue,$sharedFormulas,'_castToBool');
825  if (isset($c->f['t'])) {
826  $att = array();
827  $att = $c->f;
828  $docSheet->getCell($r)->setFormulaAttributes($att);
829  }
830  // echo '$calculatedValue = '.$calculatedValue.'<br />';
831  }
832  break;
833  case "inlineStr":
834  // echo 'Inline String<br />';
835  $value = $this->_parseRichText($c->is);
836 
837  break;
838  case "e":
839  // echo 'Error<br />';
840  if (!isset($c->f)) {
841  $value = self::_castToError($c);
842  } else {
843  // Formula
844  $this->_castToFormula($c,$r,$cellDataType,$value,$calculatedValue,$sharedFormulas,'_castToError');
845  // echo '$calculatedValue = '.$calculatedValue.'<br />';
846  }
847 
848  break;
849 
850  default:
851  // echo 'Default<br />';
852  if (!isset($c->f)) {
853  // echo 'Not a Formula<br />';
854  $value = self::_castToString($c);
855  } else {
856  // echo 'Treat as Formula<br />';
857  // Formula
858  $this->_castToFormula($c,$r,$cellDataType,$value,$calculatedValue,$sharedFormulas,'_castToString');
859  // echo '$calculatedValue = '.$calculatedValue.'<br />';
860  }
861 
862  break;
863  }
864  // echo 'Value is '.$value.'<br />';
865 
866  // Check for numeric values
867  if (is_numeric($value) && $cellDataType != 's') {
868  if ($value == (int)$value) $value = (int)$value;
869  elseif ($value == (float)$value) $value = (float)$value;
870  elseif ($value == (double)$value) $value = (double)$value;
871  }
872 
873  // Rich text?
874  if ($value instanceof PHPExcel_RichText && $this->_readDataOnly) {
875  $value = $value->getPlainText();
876  }
877 
878  $cell = $docSheet->getCell($r);
879  // Assign value
880  if ($cellDataType != '') {
881  $cell->setValueExplicit($value, $cellDataType);
882  } else {
883  $cell->setValue($value);
884  }
885  if (!is_null($calculatedValue)) {
886  $cell->setCalculatedValue($calculatedValue);
887  }
888 
889  // Style information?
890  if ($c["s"] && !$this->_readDataOnly) {
891  // no style index means 0, it seems
892  $cell->setXfIndex(isset($styles[intval($c["s"])]) ?
893  intval($c["s"]) : 0);
894  }
895  }
896  }
897  }
898 
899  $conditionals = array();
900  if (!$this->_readDataOnly && $xmlSheet && $xmlSheet->conditionalFormatting) {
901  foreach ($xmlSheet->conditionalFormatting as $conditional) {
902  foreach ($conditional->cfRule as $cfRule) {
903  if (
904  (
905  (string)$cfRule["type"] == PHPExcel_Style_Conditional::CONDITION_NONE ||
906  (string)$cfRule["type"] == PHPExcel_Style_Conditional::CONDITION_CELLIS ||
907  (string)$cfRule["type"] == PHPExcel_Style_Conditional::CONDITION_CONTAINSTEXT ||
908  (string)$cfRule["type"] == PHPExcel_Style_Conditional::CONDITION_EXPRESSION
909  ) && isset($dxfs[intval($cfRule["dxfId"])])
910  ) {
911  $conditionals[(string) $conditional["sqref"]][intval($cfRule["priority"])] = $cfRule;
912  }
913  }
914  }
915 
916  foreach ($conditionals as $ref => $cfRules) {
917  ksort($cfRules);
918  $conditionalStyles = array();
919  foreach ($cfRules as $cfRule) {
920  $objConditional = new PHPExcel_Style_Conditional();
921  $objConditional->setConditionType((string)$cfRule["type"]);
922  $objConditional->setOperatorType((string)$cfRule["operator"]);
923 
924  if ((string)$cfRule["text"] != '') {
925  $objConditional->setText((string)$cfRule["text"]);
926  }
927 
928  if (count($cfRule->formula) > 1) {
929  foreach ($cfRule->formula as $formula) {
930  $objConditional->addCondition((string)$formula);
931  }
932  } else {
933  $objConditional->addCondition((string)$cfRule->formula);
934  }
935  $objConditional->setStyle(clone $dxfs[intval($cfRule["dxfId"])]);
936  $conditionalStyles[] = $objConditional;
937  }
938 
939  // Extract all cell references in $ref
940  $aReferences = PHPExcel_Cell::extractAllCellReferencesInRange($ref);
941  foreach ($aReferences as $reference) {
942  $docSheet->getStyle($reference)->setConditionalStyles($conditionalStyles);
943  }
944  }
945  }
946 
947  $aKeys = array("sheet", "objects", "scenarios", "formatCells", "formatColumns", "formatRows", "insertColumns", "insertRows", "insertHyperlinks", "deleteColumns", "deleteRows", "selectLockedCells", "sort", "autoFilter", "pivotTables", "selectUnlockedCells");
948  if (!$this->_readDataOnly && $xmlSheet && $xmlSheet->sheetProtection) {
949  foreach ($aKeys as $key) {
950  $method = "set" . ucfirst($key);
951  $docSheet->getProtection()->$method($xmlSheet->sheetProtection[$key] == "true");
952  }
953  }
954 
955  if (!$this->_readDataOnly && $xmlSheet && $xmlSheet->sheetProtection) {
956  $docSheet->getProtection()->setPassword((string) $xmlSheet->sheetProtection["password"], true);
957  if ($xmlSheet->protectedRanges->protectedRange) {
958  foreach ($xmlSheet->protectedRanges->protectedRange as $protectedRange) {
959  $docSheet->protectCells((string) $protectedRange["sqref"], (string) $protectedRange["password"], true);
960  }
961  }
962  }
963 
964  if ($xmlSheet && $xmlSheet->autoFilter && !$this->_readDataOnly) {
965  $docSheet->setAutoFilter((string) $xmlSheet->autoFilter["ref"]);
966  }
967 
968  if ($xmlSheet && $xmlSheet->mergeCells && $xmlSheet->mergeCells->mergeCell && !$this->_readDataOnly) {
969  foreach ($xmlSheet->mergeCells->mergeCell as $mergeCell) {
970  $docSheet->mergeCells((string) $mergeCell["ref"]);
971  }
972  }
973 
974  if ($xmlSheet && $xmlSheet->pageMargins && !$this->_readDataOnly) {
975  $docPageMargins = $docSheet->getPageMargins();
976  $docPageMargins->setLeft(floatval($xmlSheet->pageMargins["left"]));
977  $docPageMargins->setRight(floatval($xmlSheet->pageMargins["right"]));
978  $docPageMargins->setTop(floatval($xmlSheet->pageMargins["top"]));
979  $docPageMargins->setBottom(floatval($xmlSheet->pageMargins["bottom"]));
980  $docPageMargins->setHeader(floatval($xmlSheet->pageMargins["header"]));
981  $docPageMargins->setFooter(floatval($xmlSheet->pageMargins["footer"]));
982  }
983 
984  if ($xmlSheet && $xmlSheet->pageSetup && !$this->_readDataOnly) {
985  $docPageSetup = $docSheet->getPageSetup();
986 
987  if (isset($xmlSheet->pageSetup["orientation"])) {
988  $docPageSetup->setOrientation((string) $xmlSheet->pageSetup["orientation"]);
989  }
990  if (isset($xmlSheet->pageSetup["paperSize"])) {
991  $docPageSetup->setPaperSize(intval($xmlSheet->pageSetup["paperSize"]));
992  }
993  if (isset($xmlSheet->pageSetup["scale"])) {
994  $docPageSetup->setScale(intval($xmlSheet->pageSetup["scale"]), false);
995  }
996  if (isset($xmlSheet->pageSetup["fitToHeight"]) && intval($xmlSheet->pageSetup["fitToHeight"]) >= 0) {
997  $docPageSetup->setFitToHeight(intval($xmlSheet->pageSetup["fitToHeight"]), false);
998  }
999  if (isset($xmlSheet->pageSetup["fitToWidth"]) && intval($xmlSheet->pageSetup["fitToWidth"]) >= 0) {
1000  $docPageSetup->setFitToWidth(intval($xmlSheet->pageSetup["fitToWidth"]), false);
1001  }
1002  if (isset($xmlSheet->pageSetup["firstPageNumber"]) && isset($xmlSheet->pageSetup["useFirstPageNumber"]) &&
1003  ((string)$xmlSheet->pageSetup["useFirstPageNumber"] == 'true' || (string)$xmlSheet->pageSetup["useFirstPageNumber"] == '1')) {
1004  $docPageSetup->setFirstPageNumber(intval($xmlSheet->pageSetup["firstPageNumber"]));
1005  }
1006  }
1007 
1008  if ($xmlSheet && $xmlSheet->headerFooter && !$this->_readDataOnly) {
1009  $docHeaderFooter = $docSheet->getHeaderFooter();
1010 
1011  if (isset($xmlSheet->headerFooter["differentOddEven"]) &&
1012  ((string)$xmlSheet->headerFooter["differentOddEven"] == 'true' || (string)$xmlSheet->headerFooter["differentOddEven"] == '1')) {
1013  $docHeaderFooter->setDifferentOddEven(true);
1014  } else {
1015  $docHeaderFooter->setDifferentOddEven(false);
1016  }
1017  if (isset($xmlSheet->headerFooter["differentFirst"]) &&
1018  ((string)$xmlSheet->headerFooter["differentFirst"] == 'true' || (string)$xmlSheet->headerFooter["differentFirst"] == '1')) {
1019  $docHeaderFooter->setDifferentFirst(true);
1020  } else {
1021  $docHeaderFooter->setDifferentFirst(false);
1022  }
1023  if (isset($xmlSheet->headerFooter["scaleWithDoc"]) &&
1024  ((string)$xmlSheet->headerFooter["scaleWithDoc"] == 'false' || (string)$xmlSheet->headerFooter["scaleWithDoc"] == '0')) {
1025  $docHeaderFooter->setScaleWithDocument(false);
1026  } else {
1027  $docHeaderFooter->setScaleWithDocument(true);
1028  }
1029  if (isset($xmlSheet->headerFooter["alignWithMargins"]) &&
1030  ((string)$xmlSheet->headerFooter["alignWithMargins"] == 'false' || (string)$xmlSheet->headerFooter["alignWithMargins"] == '0')) {
1031  $docHeaderFooter->setAlignWithMargins(false);
1032  } else {
1033  $docHeaderFooter->setAlignWithMargins(true);
1034  }
1035 
1036  $docHeaderFooter->setOddHeader((string) $xmlSheet->headerFooter->oddHeader);
1037  $docHeaderFooter->setOddFooter((string) $xmlSheet->headerFooter->oddFooter);
1038  $docHeaderFooter->setEvenHeader((string) $xmlSheet->headerFooter->evenHeader);
1039  $docHeaderFooter->setEvenFooter((string) $xmlSheet->headerFooter->evenFooter);
1040  $docHeaderFooter->setFirstHeader((string) $xmlSheet->headerFooter->firstHeader);
1041  $docHeaderFooter->setFirstFooter((string) $xmlSheet->headerFooter->firstFooter);
1042  }
1043 
1044  if ($xmlSheet && $xmlSheet->rowBreaks && $xmlSheet->rowBreaks->brk && !$this->_readDataOnly) {
1045  foreach ($xmlSheet->rowBreaks->brk as $brk) {
1046  if ($brk["man"]) {
1047  $docSheet->setBreak("A$brk[id]", PHPExcel_Worksheet::BREAK_ROW);
1048  }
1049  }
1050  }
1051  if ($xmlSheet && $xmlSheet->colBreaks && $xmlSheet->colBreaks->brk && !$this->_readDataOnly) {
1052  foreach ($xmlSheet->colBreaks->brk as $brk) {
1053  if ($brk["man"]) {
1054  $docSheet->setBreak(PHPExcel_Cell::stringFromColumnIndex($brk["id"]) . "1", PHPExcel_Worksheet::BREAK_COLUMN);
1055  }
1056  }
1057  }
1058 
1059  if ($xmlSheet && $xmlSheet->dataValidations && !$this->_readDataOnly) {
1060  foreach ($xmlSheet->dataValidations->dataValidation as $dataValidation) {
1061  // Uppercase coordinate
1062  $range = strtoupper($dataValidation["sqref"]);
1063  $rangeSet = explode(' ',$range);
1064  foreach($rangeSet as $range) {
1065  $stRange = $docSheet->shrinkRangeToFit($range);
1066 
1067  // Extract all cell references in $range
1068  $aReferences = PHPExcel_Cell::extractAllCellReferencesInRange($stRange);
1069  foreach ($aReferences as $reference) {
1070  // Create validation
1071  $docValidation = $docSheet->getCell($reference)->getDataValidation();
1072  $docValidation->setType((string) $dataValidation["type"]);
1073  $docValidation->setErrorStyle((string) $dataValidation["errorStyle"]);
1074  $docValidation->setOperator((string) $dataValidation["operator"]);
1075  $docValidation->setAllowBlank($dataValidation["allowBlank"] != 0);
1076  $docValidation->setShowDropDown($dataValidation["showDropDown"] == 0);
1077  $docValidation->setShowInputMessage($dataValidation["showInputMessage"] != 0);
1078  $docValidation->setShowErrorMessage($dataValidation["showErrorMessage"] != 0);
1079  $docValidation->setErrorTitle((string) $dataValidation["errorTitle"]);
1080  $docValidation->setError((string) $dataValidation["error"]);
1081  $docValidation->setPromptTitle((string) $dataValidation["promptTitle"]);
1082  $docValidation->setPrompt((string) $dataValidation["prompt"]);
1083  $docValidation->setFormula1((string) $dataValidation->formula1);
1084  $docValidation->setFormula2((string) $dataValidation->formula2);
1085  }
1086  }
1087  }
1088  }
1089 
1090  // Add hyperlinks
1091  $hyperlinks = array();
1092  if (!$this->_readDataOnly) {
1093  // Locate hyperlink relations
1094  if ($zip->locateName(dirname("$dir/$fileWorksheet") . "/_rels/" . basename($fileWorksheet) . ".rels")) {
1095  $relsWorksheet = simplexml_load_string($this->_getFromZipArchive($zip, dirname("$dir/$fileWorksheet") . "/_rels/" . basename($fileWorksheet) . ".rels") ); //~ http://schemas.openxmlformats.org/package/2006/relationships");
1096  foreach ($relsWorksheet->Relationship as $ele) {
1097  if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/hyperlink") {
1098  $hyperlinks[(string)$ele["Id"]] = (string)$ele["Target"];
1099  }
1100  }
1101  }
1102 
1103  // Loop through hyperlinks
1104  if ($xmlSheet && $xmlSheet->hyperlinks) {
1105  foreach ($xmlSheet->hyperlinks->hyperlink as $hyperlink) {
1106  // Link url
1107  $linkRel = $hyperlink->attributes('http://schemas.openxmlformats.org/officeDocument/2006/relationships');
1108 
1109  foreach (PHPExcel_Cell::extractAllCellReferencesInRange($hyperlink['ref']) as $cellReference) {
1110  $cell = $docSheet->getCell( $cellReference );
1111  if (isset($linkRel['id'])) {
1112  $cell->getHyperlink()->setUrl( $hyperlinks[ (string)$linkRel['id'] ] );
1113  }
1114  if (isset($hyperlink['location'])) {
1115  $cell->getHyperlink()->setUrl( 'sheet://' . (string)$hyperlink['location'] );
1116  }
1117 
1118  // Tooltip
1119  if (isset($hyperlink['tooltip'])) {
1120  $cell->getHyperlink()->setTooltip( (string)$hyperlink['tooltip'] );
1121  }
1122  }
1123  }
1124  }
1125  }
1126 
1127  // Add comments
1128  $comments = array();
1129  $vmlComments = array();
1130  if (!$this->_readDataOnly) {
1131  // Locate comment relations
1132  if ($zip->locateName(dirname("$dir/$fileWorksheet") . "/_rels/" . basename($fileWorksheet) . ".rels")) {
1133  $relsWorksheet = simplexml_load_string($this->_getFromZipArchive($zip, dirname("$dir/$fileWorksheet") . "/_rels/" . basename($fileWorksheet) . ".rels") ); //~ http://schemas.openxmlformats.org/package/2006/relationships");
1134  foreach ($relsWorksheet->Relationship as $ele) {
1135  if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/comments") {
1136  $comments[(string)$ele["Id"]] = (string)$ele["Target"];
1137  }
1138  if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/vmlDrawing") {
1139  $vmlComments[(string)$ele["Id"]] = (string)$ele["Target"];
1140  }
1141  }
1142  }
1143 
1144  // Loop through comments
1145  foreach ($comments as $relName => $relPath) {
1146  // Load comments file
1147  $relPath = PHPExcel_Shared_File::realpath(dirname("$dir/$fileWorksheet") . "/" . $relPath);
1148  $commentsFile = simplexml_load_string($this->_getFromZipArchive($zip, $relPath) );
1149 
1150  // Utility variables
1151  $authors = array();
1152 
1153  // Loop through authors
1154  foreach ($commentsFile->authors->author as $author) {
1155  $authors[] = (string)$author;
1156  }
1157 
1158  // Loop through contents
1159  foreach ($commentsFile->commentList->comment as $comment) {
1160  $docSheet->getComment( (string)$comment['ref'] )->setAuthor( $authors[(string)$comment['authorId']] );
1161  $docSheet->getComment( (string)$comment['ref'] )->setText( $this->_parseRichText($comment->text) );
1162  }
1163  }
1164 
1165  // Loop through VML comments
1166  foreach ($vmlComments as $relName => $relPath) {
1167  // Load VML comments file
1168  $relPath = PHPExcel_Shared_File::realpath(dirname("$dir/$fileWorksheet") . "/" . $relPath);
1169  $vmlCommentsFile = simplexml_load_string( $this->_getFromZipArchive($zip, $relPath) );
1170  $vmlCommentsFile->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml');
1171 
1172  $shapes = $vmlCommentsFile->xpath('//v:shape');
1173  foreach ($shapes as $shape) {
1174  $shape->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml');
1175 
1176  if (isset($shape['style'])) {
1177  $style = (string)$shape['style'];
1178  $fillColor = strtoupper( substr( (string)$shape['fillcolor'], 1 ) );
1179  $column = null;
1180  $row = null;
1181 
1182  $clientData = $shape->xpath('.//x:ClientData');
1183  if (is_array($clientData) && count($clientData) > 0) {
1184  $clientData = $clientData[0];
1185 
1186  if ( isset($clientData['ObjectType']) && (string)$clientData['ObjectType'] == 'Note' ) {
1187  $temp = $clientData->xpath('.//x:Row');
1188  if (is_array($temp)) $row = $temp[0];
1189 
1190  $temp = $clientData->xpath('.//x:Column');
1191  if (is_array($temp)) $column = $temp[0];
1192  }
1193  }
1194 
1195  if (!is_null($column) && !is_null($row)) {
1196  // Set comment properties
1197  $comment = $docSheet->getCommentByColumnAndRow($column, $row + 1);
1198  $comment->getFillColor()->setRGB( $fillColor );
1199 
1200  // Parse style
1201  $styleArray = explode(';', str_replace(' ', '', $style));
1202  foreach ($styleArray as $stylePair) {
1203  $stylePair = explode(':', $stylePair);
1204 
1205  if ($stylePair[0] == 'margin-left') $comment->setMarginLeft($stylePair[1]);
1206  if ($stylePair[0] == 'margin-top') $comment->setMarginTop($stylePair[1]);
1207  if ($stylePair[0] == 'width') $comment->setWidth($stylePair[1]);
1208  if ($stylePair[0] == 'height') $comment->setHeight($stylePair[1]);
1209  if ($stylePair[0] == 'visibility') $comment->setVisible( $stylePair[1] == 'visible' );
1210 
1211  }
1212  }
1213  }
1214  }
1215  }
1216 
1217  // Header/footer images
1218  if ($xmlSheet && $xmlSheet->legacyDrawingHF && !$this->_readDataOnly) {
1219  if ($zip->locateName(dirname("$dir/$fileWorksheet") . "/_rels/" . basename($fileWorksheet) . ".rels")) {
1220  $relsWorksheet = simplexml_load_string($this->_getFromZipArchive($zip, dirname("$dir/$fileWorksheet") . "/_rels/" . basename($fileWorksheet) . ".rels") ); //~ http://schemas.openxmlformats.org/package/2006/relationships");
1221  $vmlRelationship = '';
1222 
1223  foreach ($relsWorksheet->Relationship as $ele) {
1224  if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/vmlDrawing") {
1225  $vmlRelationship = self::dir_add("$dir/$fileWorksheet", $ele["Target"]);
1226  }
1227  }
1228 
1229  if ($vmlRelationship != '') {
1230  // Fetch linked images
1231  $relsVML = simplexml_load_string($this->_getFromZipArchive($zip, dirname($vmlRelationship) . '/_rels/' . basename($vmlRelationship) . '.rels' )); //~ http://schemas.openxmlformats.org/package/2006/relationships");
1232  $drawings = array();
1233  foreach ($relsVML->Relationship as $ele) {
1234  if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/image") {
1235  $drawings[(string) $ele["Id"]] = self::dir_add($vmlRelationship, $ele["Target"]);
1236  }
1237  }
1238 
1239  // Fetch VML document
1240  $vmlDrawing = simplexml_load_string($this->_getFromZipArchive($zip, $vmlRelationship));
1241  $vmlDrawing->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml');
1242 
1243  $hfImages = array();
1244 
1245  $shapes = $vmlDrawing->xpath('//v:shape');
1246  foreach ($shapes as $shape) {
1247  $shape->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml');
1248  $imageData = $shape->xpath('//v:imagedata');
1249  $imageData = $imageData[0];
1250 
1251  $imageData = $imageData->attributes('urn:schemas-microsoft-com:office:office');
1252  $style = self::toCSSArray( (string)$shape['style'] );
1253 
1254  $hfImages[ (string)$shape['id'] ] = new PHPExcel_Worksheet_HeaderFooterDrawing();
1255  if (isset($imageData['title'])) {
1256  $hfImages[ (string)$shape['id'] ]->setName( (string)$imageData['title'] );
1257  }
1258 
1259  $hfImages[ (string)$shape['id'] ]->setPath("zip://$pFilename#" . $drawings[(string)$imageData['relid']], false);
1260  $hfImages[ (string)$shape['id'] ]->setResizeProportional(false);
1261  $hfImages[ (string)$shape['id'] ]->setWidth($style['width']);
1262  $hfImages[ (string)$shape['id'] ]->setHeight($style['height']);
1263  $hfImages[ (string)$shape['id'] ]->setOffsetX($style['margin-left']);
1264  $hfImages[ (string)$shape['id'] ]->setOffsetY($style['margin-top']);
1265  $hfImages[ (string)$shape['id'] ]->setResizeProportional(true);
1266  }
1267 
1268  $docSheet->getHeaderFooter()->setImages($hfImages);
1269  }
1270  }
1271  }
1272 
1273  }
1274 
1275  // TODO: Make sure drawings and graph are loaded differently!
1276  if ($zip->locateName(dirname("$dir/$fileWorksheet") . "/_rels/" . basename($fileWorksheet) . ".rels")) {
1277  $relsWorksheet = simplexml_load_string($this->_getFromZipArchive($zip, dirname("$dir/$fileWorksheet") . "/_rels/" . basename($fileWorksheet) . ".rels") ); //~ http://schemas.openxmlformats.org/package/2006/relationships");
1278  $drawings = array();
1279  foreach ($relsWorksheet->Relationship as $ele) {
1280  if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/drawing") {
1281  $drawings[(string) $ele["Id"]] = self::dir_add("$dir/$fileWorksheet", $ele["Target"]);
1282  }
1283  }
1284  if ($xmlSheet->drawing && !$this->_readDataOnly) {
1285  foreach ($xmlSheet->drawing as $drawing) {
1286  $fileDrawing = $drawings[(string) self::array_item($drawing->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "id")];
1287  $relsDrawing = simplexml_load_string($this->_getFromZipArchive($zip, dirname($fileDrawing) . "/_rels/" . basename($fileDrawing) . ".rels") ); //~ http://schemas.openxmlformats.org/package/2006/relationships");
1288  $images = array();
1289 
1290  if ($relsDrawing && $relsDrawing->Relationship) {
1291  foreach ($relsDrawing->Relationship as $ele) {
1292  if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/image") {
1293  $images[(string) $ele["Id"]] = self::dir_add($fileDrawing, $ele["Target"]);
1294  }
1295  }
1296  }
1297  $xmlDrawing = simplexml_load_string($this->_getFromZipArchive($zip, $fileDrawing))->children("http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing");
1298 
1299  if ($xmlDrawing->oneCellAnchor) {
1300  foreach ($xmlDrawing->oneCellAnchor as $oneCellAnchor) {
1301  if ($oneCellAnchor->pic->blipFill) {
1302  $blip = $oneCellAnchor->pic->blipFill->children("http://schemas.openxmlformats.org/drawingml/2006/main")->blip;
1303  $xfrm = $oneCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->xfrm;
1304  $outerShdw = $oneCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->effectLst->outerShdw;
1305  $objDrawing = new PHPExcel_Worksheet_Drawing;
1306  $objDrawing->setName((string) self::array_item($oneCellAnchor->pic->nvPicPr->cNvPr->attributes(), "name"));
1307  $objDrawing->setDescription((string) self::array_item($oneCellAnchor->pic->nvPicPr->cNvPr->attributes(), "descr"));
1308  $objDrawing->setPath("zip://$pFilename#" . $images[(string) self::array_item($blip->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "embed")], false);
1309  $objDrawing->setCoordinates(PHPExcel_Cell::stringFromColumnIndex($oneCellAnchor->from->col) . ($oneCellAnchor->from->row + 1));
1310  $objDrawing->setOffsetX(PHPExcel_Shared_Drawing::EMUToPixels($oneCellAnchor->from->colOff));
1311  $objDrawing->setOffsetY(PHPExcel_Shared_Drawing::EMUToPixels($oneCellAnchor->from->rowOff));
1312  $objDrawing->setResizeProportional(false);
1313  $objDrawing->setWidth(PHPExcel_Shared_Drawing::EMUToPixels(self::array_item($oneCellAnchor->ext->attributes(), "cx")));
1314  $objDrawing->setHeight(PHPExcel_Shared_Drawing::EMUToPixels(self::array_item($oneCellAnchor->ext->attributes(), "cy")));
1315  if ($xfrm) {
1316  $objDrawing->setRotation(PHPExcel_Shared_Drawing::angleToDegrees(self::array_item($xfrm->attributes(), "rot")));
1317  }
1318  if ($outerShdw) {
1319  $shadow = $objDrawing->getShadow();
1320  $shadow->setVisible(true);
1321  $shadow->setBlurRadius(PHPExcel_Shared_Drawing::EMUTopixels(self::array_item($outerShdw->attributes(), "blurRad")));
1322  $shadow->setDistance(PHPExcel_Shared_Drawing::EMUTopixels(self::array_item($outerShdw->attributes(), "dist")));
1323  $shadow->setDirection(PHPExcel_Shared_Drawing::angleToDegrees(self::array_item($outerShdw->attributes(), "dir")));
1324  $shadow->setAlignment((string) self::array_item($outerShdw->attributes(), "algn"));
1325  $shadow->getColor()->setRGB(self::array_item($outerShdw->srgbClr->attributes(), "val"));
1326  $shadow->setAlpha(self::array_item($outerShdw->srgbClr->alpha->attributes(), "val") / 1000);
1327  }
1328  $objDrawing->setWorksheet($docSheet);
1329  }
1330  }
1331  }
1332  if ($xmlDrawing->twoCellAnchor) {
1333  foreach ($xmlDrawing->twoCellAnchor as $twoCellAnchor) {
1334  if ($twoCellAnchor->pic->blipFill) {
1335  $blip = $twoCellAnchor->pic->blipFill->children("http://schemas.openxmlformats.org/drawingml/2006/main")->blip;
1336  $xfrm = $twoCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->xfrm;
1337  $outerShdw = $twoCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->effectLst->outerShdw;
1338  $objDrawing = new PHPExcel_Worksheet_Drawing;
1339  $objDrawing->setName((string) self::array_item($twoCellAnchor->pic->nvPicPr->cNvPr->attributes(), "name"));
1340  $objDrawing->setDescription((string) self::array_item($twoCellAnchor->pic->nvPicPr->cNvPr->attributes(), "descr"));
1341  $objDrawing->setPath("zip://$pFilename#" . $images[(string) self::array_item($blip->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "embed")], false);
1342  $objDrawing->setCoordinates(PHPExcel_Cell::stringFromColumnIndex($twoCellAnchor->from->col) . ($twoCellAnchor->from->row + 1));
1343  $objDrawing->setOffsetX(PHPExcel_Shared_Drawing::EMUToPixels($twoCellAnchor->from->colOff));
1344  $objDrawing->setOffsetY(PHPExcel_Shared_Drawing::EMUToPixels($twoCellAnchor->from->rowOff));
1345  $objDrawing->setResizeProportional(false);
1346 
1347  $objDrawing->setWidth(PHPExcel_Shared_Drawing::EMUToPixels(self::array_item($xfrm->ext->attributes(), "cx")));
1348  $objDrawing->setHeight(PHPExcel_Shared_Drawing::EMUToPixels(self::array_item($xfrm->ext->attributes(), "cy")));
1349 
1350  if ($xfrm) {
1351  $objDrawing->setRotation(PHPExcel_Shared_Drawing::angleToDegrees(self::array_item($xfrm->attributes(), "rot")));
1352  }
1353  if ($outerShdw) {
1354  $shadow = $objDrawing->getShadow();
1355  $shadow->setVisible(true);
1356  $shadow->setBlurRadius(PHPExcel_Shared_Drawing::EMUTopixels(self::array_item($outerShdw->attributes(), "blurRad")));
1357  $shadow->setDistance(PHPExcel_Shared_Drawing::EMUTopixels(self::array_item($outerShdw->attributes(), "dist")));
1358  $shadow->setDirection(PHPExcel_Shared_Drawing::angleToDegrees(self::array_item($outerShdw->attributes(), "dir")));
1359  $shadow->setAlignment((string) self::array_item($outerShdw->attributes(), "algn"));
1360  $shadow->getColor()->setRGB(self::array_item($outerShdw->srgbClr->attributes(), "val"));
1361  $shadow->setAlpha(self::array_item($outerShdw->srgbClr->alpha->attributes(), "val") / 1000);
1362  }
1363  $objDrawing->setWorksheet($docSheet);
1364  }
1365  }
1366  }
1367 
1368  }
1369  }
1370  }
1371 
1372  // Loop through definedNames
1373  if ($xmlWorkbook->definedNames) {
1374  foreach ($xmlWorkbook->definedNames->definedName as $definedName) {
1375  // Extract range
1376  $extractedRange = (string)$definedName;
1377  $extractedRange = preg_replace('/\'(\w+)\'\!/', '', $extractedRange);
1378  if (($spos = strpos($extractedRange,'!')) !== false) {
1379  $extractedRange = substr($extractedRange,0,$spos).str_replace('$', '', substr($extractedRange,$spos));
1380  } else {
1381  $extractedRange = str_replace('$', '', $extractedRange);
1382  }
1383 
1384  // Valid range?
1385  if (stripos((string)$definedName, '#REF!') !== false || $extractedRange == '') {
1386  continue;
1387  }
1388 
1389  // Some definedNames are only applicable if we are on the same sheet...
1390  if ((string)$definedName['localSheetId'] != '' && (string)$definedName['localSheetId'] == $sheetId) {
1391  // Switch on type
1392  switch ((string)$definedName['name']) {
1393 
1394  case '_xlnm._FilterDatabase':
1395  $docSheet->setAutoFilter($extractedRange);
1396  break;
1397 
1398  case '_xlnm.Print_Titles':
1399  // Split $extractedRange
1400  $extractedRange = explode(',', $extractedRange);
1401 
1402  // Set print titles
1403  foreach ($extractedRange as $range) {
1404  $matches = array();
1405 
1406  // check for repeating columns, e g. 'A:A' or 'A:D'
1407  if (preg_match('/^([A-Z]+)\:([A-Z]+)$/', $range, $matches)) {
1408  $docSheet->getPageSetup()->setColumnsToRepeatAtLeft(array($matches[1], $matches[2]));
1409  }
1410  // check for repeating rows, e.g. '1:1' or '1:5'
1411  elseif (preg_match('/^(\d+)\:(\d+)$/', $range, $matches)) {
1412  $docSheet->getPageSetup()->setRowsToRepeatAtTop(array($matches[1], $matches[2]));
1413  }
1414  }
1415  break;
1416 
1417  case '_xlnm.Print_Area':
1418  $rangeSets = explode(',', $extractedRange); // FIXME: what if sheetname contains comma?
1419  $newRangeSets = array();
1420  foreach($rangeSets as $rangeSet) {
1421  $range = explode('!', $rangeSet); // FIXME: what if sheetname contains exclamation mark?
1422  $rangeSet = isset($range[1]) ? $range[1] : $range[0];
1423  $newRangeSets[] = str_replace('$', '', $rangeSet);
1424  }
1425  $docSheet->getPageSetup()->setPrintArea(implode(',',$newRangeSets));
1426  break;
1427 
1428  default:
1429  break;
1430  }
1431  }
1432  }
1433  }
1434 
1435  // Next sheet id
1436  ++$sheetId;
1437  }
1438 
1439  // Loop through definedNames
1440  if ($xmlWorkbook->definedNames) {
1441  foreach ($xmlWorkbook->definedNames->definedName as $definedName) {
1442  // Extract range
1443  $extractedRange = (string)$definedName;
1444  $extractedRange = preg_replace('/\'(\w+)\'\!/', '', $extractedRange);
1445  if (($spos = strpos($extractedRange,'!')) !== false) {
1446  $extractedRange = substr($extractedRange,0,$spos).str_replace('$', '', substr($extractedRange,$spos));
1447  } else {
1448  $extractedRange = str_replace('$', '', $extractedRange);
1449  }
1450 
1451  // Valid range?
1452  if (stripos((string)$definedName, '#REF!') !== false || $extractedRange == '') {
1453  continue;
1454  }
1455 
1456  // Some definedNames are only applicable if we are on the same sheet...
1457  if ((string)$definedName['localSheetId'] != '') {
1458  // Local defined name
1459  // Switch on type
1460  switch ((string)$definedName['name']) {
1461 
1462  case '_xlnm._FilterDatabase':
1463  case '_xlnm.Print_Titles':
1464  case '_xlnm.Print_Area':
1465  break;
1466 
1467  default:
1468  $range = explode('!', (string)$definedName);
1469  if (count($range) == 2) {
1470  $range[0] = str_replace("''", "'", $range[0]);
1471  $range[0] = str_replace("'", "", $range[0]);
1472  if ($worksheet = $docSheet->getParent()->getSheetByName($range[0])) {
1473  $extractedRange = str_replace('$', '', $range[1]);
1474  $scope = $docSheet->getParent()->getSheet((string)$definedName['localSheetId']);
1475 
1476  $excel->addNamedRange( new PHPExcel_NamedRange((string)$definedName['name'], $worksheet, $extractedRange, true, $scope) );
1477  }
1478  }
1479  break;
1480  }
1481  } else if (!isset($definedName['localSheetId'])) {
1482  // "Global" definedNames
1483  $locatedSheet = null;
1484  $extractedSheetName = '';
1485  if (strpos( (string)$definedName, '!' ) !== false) {
1486  // Extract sheet name
1487  $extractedSheetName = PHPExcel_Worksheet::extractSheetTitle( (string)$definedName, true );
1488  $extractedSheetName = $extractedSheetName[0];
1489 
1490  // Locate sheet
1491  $locatedSheet = $excel->getSheetByName($extractedSheetName);
1492 
1493  // Modify range
1494  $range = explode('!', $extractedRange);
1495  $extractedRange = isset($range[1]) ? $range[1] : $range[0];
1496  }
1497 
1498  if (!is_null($locatedSheet)) {
1499  $excel->addNamedRange( new PHPExcel_NamedRange((string)$definedName['name'], $locatedSheet, $extractedRange, false) );
1500  }
1501  }
1502  }
1503  }
1504  }
1505 
1506  if (!$this->_readDataOnly) {
1507  // active sheet index
1508  $activeTab = intval($xmlWorkbook->bookViews->workbookView["activeTab"]); // refers to old sheet index
1509 
1510  // keep active sheet index if sheet is still loaded, else first sheet is set as the active
1511  if (isset($mapSheetId[$activeTab]) && $mapSheetId[$activeTab] !== null) {
1512  $excel->setActiveSheetIndex($mapSheetId[$activeTab]);
1513  } else {
1514  if ($excel->getSheetCount() == 0)
1515  {
1516  $excel->createSheet();
1517  }
1518  $excel->setActiveSheetIndex(0);
1519  }
1520  }
1521  break;
1522  }
1523 
1524  }
1525 
1526  $zip->close();
1527 
1528  return $excel;
1529  }
1530 
1531  private static function _readColor($color, $background=false) {
1532  if (isset($color["rgb"])) {
1533  return (string)$color["rgb"];
1534  } else if (isset($color["indexed"])) {
1535  return PHPExcel_Style_Color::indexedColor($color["indexed"],$background)->getARGB();
1536  } else if (isset($color["theme"])) {
1537  if (!is_null(self::$_theme)) {
1538  $returnColour = self::$_theme->getColourByIndex((int)$color["theme"]);
1539  if (isset($color["tint"])) {
1540  $tintAdjust = (float) $color["tint"];
1541  $returnColour = PHPExcel_Style_Color::changeBrightness($returnColour, $tintAdjust);
1542  }
1543  return 'FF'.$returnColour;
1544  }
1545  }
1546 
1547  if ($background) {
1548  return 'FFFFFFFF';
1549  }
1550  return 'FF000000';
1551  }
1552 
1553  private static function _readStyle($docStyle, $style) {
1554  // format code
1555  if (isset($style->numFmt)) {
1556  $docStyle->getNumberFormat()->setFormatCode($style->numFmt);
1557  }
1558 
1559  // font
1560  if (isset($style->font)) {
1561  $docStyle->getFont()->setName((string) $style->font->name["val"]);
1562  $docStyle->getFont()->setSize((string) $style->font->sz["val"]);
1563  if (isset($style->font->b)) {
1564  $docStyle->getFont()->setBold(!isset($style->font->b["val"]) || $style->font->b["val"] == 'true' || $style->font->b["val"] == '1');
1565  }
1566  if (isset($style->font->i)) {
1567  $docStyle->getFont()->setItalic(!isset($style->font->i["val"]) || $style->font->i["val"] == 'true' || $style->font->i["val"] == '1');
1568  }
1569  if (isset($style->font->strike)) {
1570  $docStyle->getFont()->setStrikethrough(!isset($style->font->strike["val"]) || $style->font->strike["val"] == 'true' || $style->font->strike["val"] == '1');
1571  }
1572  $docStyle->getFont()->getColor()->setARGB(self::_readColor($style->font->color));
1573 
1574  if (isset($style->font->u) && !isset($style->font->u["val"])) {
1575  $docStyle->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
1576  } else if (isset($style->font->u) && isset($style->font->u["val"])) {
1577  $docStyle->getFont()->setUnderline((string)$style->font->u["val"]);
1578  }
1579 
1580  if (isset($style->font->vertAlign) && isset($style->font->vertAlign["val"])) {
1581  $vertAlign = strtolower((string)$style->font->vertAlign["val"]);
1582  if ($vertAlign == 'superscript') {
1583  $docStyle->getFont()->setSuperScript(true);
1584  }
1585  if ($vertAlign == 'subscript') {
1586  $docStyle->getFont()->setSubScript(true);
1587  }
1588  }
1589  }
1590 
1591  // fill
1592  if (isset($style->fill)) {
1593  if ($style->fill->gradientFill) {
1594  $gradientFill = $style->fill->gradientFill[0];
1595  if(!empty($gradientFill["type"])) {
1596  $docStyle->getFill()->setFillType((string) $gradientFill["type"]);
1597  }
1598  $docStyle->getFill()->setRotation(floatval($gradientFill["degree"]));
1599  $gradientFill->registerXPathNamespace("sml", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
1600  $docStyle->getFill()->getStartColor()->setARGB(self::_readColor( self::array_item($gradientFill->xpath("sml:stop[@position=0]"))->color) );
1601  $docStyle->getFill()->getEndColor()->setARGB(self::_readColor( self::array_item($gradientFill->xpath("sml:stop[@position=1]"))->color) );
1602  } elseif ($style->fill->patternFill) {
1603  $patternType = (string)$style->fill->patternFill["patternType"] != '' ? (string)$style->fill->patternFill["patternType"] : 'solid';
1604  $docStyle->getFill()->setFillType($patternType);
1605  if ($style->fill->patternFill->fgColor) {
1606  $docStyle->getFill()->getStartColor()->setARGB(self::_readColor($style->fill->patternFill->fgColor,true));
1607  } else {
1608  $docStyle->getFill()->getStartColor()->setARGB('FF000000');
1609  }
1610  if ($style->fill->patternFill->bgColor) {
1611  $docStyle->getFill()->getEndColor()->setARGB(self::_readColor($style->fill->patternFill->bgColor,true));
1612  }
1613  }
1614  }
1615 
1616  // border
1617  if (isset($style->border)) {
1618  $diagonalUp = false;
1619  $diagonalDown = false;
1620  if ($style->border["diagonalUp"] == 'true' || $style->border["diagonalUp"] == 1) {
1621  $diagonalUp = true;
1622  }
1623  if ($style->border["diagonalDown"] == 'true' || $style->border["diagonalDown"] == 1) {
1624  $diagonalDown = true;
1625  }
1626  if ($diagonalUp == false && $diagonalDown == false) {
1627  $docStyle->getBorders()->setDiagonalDirection(PHPExcel_Style_Borders::DIAGONAL_NONE);
1628  } elseif ($diagonalUp == true && $diagonalDown == false) {
1629  $docStyle->getBorders()->setDiagonalDirection(PHPExcel_Style_Borders::DIAGONAL_UP);
1630  } elseif ($diagonalUp == false && $diagonalDown == true) {
1631  $docStyle->getBorders()->setDiagonalDirection(PHPExcel_Style_Borders::DIAGONAL_DOWN);
1632  } elseif ($diagonalUp == true && $diagonalDown == true) {
1633  $docStyle->getBorders()->setDiagonalDirection(PHPExcel_Style_Borders::DIAGONAL_BOTH);
1634  }
1635  self::_readBorder($docStyle->getBorders()->getLeft(), $style->border->left);
1636  self::_readBorder($docStyle->getBorders()->getRight(), $style->border->right);
1637  self::_readBorder($docStyle->getBorders()->getTop(), $style->border->top);
1638  self::_readBorder($docStyle->getBorders()->getBottom(), $style->border->bottom);
1639  self::_readBorder($docStyle->getBorders()->getDiagonal(), $style->border->diagonal);
1640  }
1641 
1642  // alignment
1643  if (isset($style->alignment)) {
1644  $docStyle->getAlignment()->setHorizontal((string) $style->alignment["horizontal"]);
1645  $docStyle->getAlignment()->setVertical((string) $style->alignment["vertical"]);
1646 
1647  $textRotation = 0;
1648  if ((int)$style->alignment["textRotation"] <= 90) {
1649  $textRotation = (int)$style->alignment["textRotation"];
1650  } else if ((int)$style->alignment["textRotation"] > 90) {
1651  $textRotation = 90 - (int)$style->alignment["textRotation"];
1652  }
1653 
1654  $docStyle->getAlignment()->setTextRotation(intval($textRotation));
1655  $docStyle->getAlignment()->setWrapText( (string)$style->alignment["wrapText"] == "true" || (string)$style->alignment["wrapText"] == "1" );
1656  $docStyle->getAlignment()->setShrinkToFit( (string)$style->alignment["shrinkToFit"] == "true" || (string)$style->alignment["shrinkToFit"] == "1" );
1657  $docStyle->getAlignment()->setIndent( intval((string)$style->alignment["indent"]) > 0 ? intval((string)$style->alignment["indent"]) : 0 );
1658  }
1659 
1660  // protection
1661  if (isset($style->protection)) {
1662  if (isset($style->protection['locked'])) {
1663  if ((string)$style->protection['locked'] == 'true') {
1664  $docStyle->getProtection()->setLocked(PHPExcel_Style_Protection::PROTECTION_PROTECTED);
1665  } else {
1666  $docStyle->getProtection()->setLocked(PHPExcel_Style_Protection::PROTECTION_UNPROTECTED);
1667  }
1668  }
1669 
1670  if (isset($style->protection['hidden'])) {
1671  if ((string)$style->protection['hidden'] == 'true') {
1672  $docStyle->getProtection()->setHidden(PHPExcel_Style_Protection::PROTECTION_PROTECTED);
1673  } else {
1674  $docStyle->getProtection()->setHidden(PHPExcel_Style_Protection::PROTECTION_UNPROTECTED);
1675  }
1676  }
1677  }
1678  }
1679 
1680  private static function _readBorder($docBorder, $eleBorder) {
1681  if (isset($eleBorder["style"])) {
1682  $docBorder->setBorderStyle((string) $eleBorder["style"]);
1683  }
1684  if (isset($eleBorder->color)) {
1685  $docBorder->getColor()->setARGB(self::_readColor($eleBorder->color));
1686  }
1687  }
1688 
1689  private function _parseRichText($is = null) {
1690  $value = new PHPExcel_RichText();
1691 
1692  if (isset($is->t)) {
1693  $value->createText( PHPExcel_Shared_String::ControlCharacterOOXML2PHP( (string) $is->t ) );
1694  } else {
1695  foreach ($is->r as $run) {
1696  if (!isset($run->rPr)) {
1697  $objText = $value->createText( PHPExcel_Shared_String::ControlCharacterOOXML2PHP( (string) $run->t ) );
1698 
1699  } else {
1700  $objText = $value->createTextRun( PHPExcel_Shared_String::ControlCharacterOOXML2PHP( (string) $run->t ) );
1701 
1702  if (isset($run->rPr->rFont["val"])) {
1703  $objText->getFont()->setName((string) $run->rPr->rFont["val"]);
1704  }
1705 
1706  if (isset($run->rPr->sz["val"])) {
1707  $objText->getFont()->setSize((string) $run->rPr->sz["val"]);
1708  }
1709 
1710  if (isset($run->rPr->color)) {
1711  $objText->getFont()->setColor( new PHPExcel_Style_Color( self::_readColor($run->rPr->color) ) );
1712  }
1713 
1714  if ( (isset($run->rPr->b["val"]) && ((string) $run->rPr->b["val"] == 'true' || (string) $run->rPr->b["val"] == '1'))
1715  || (isset($run->rPr->b) && !isset($run->rPr->b["val"])) ) {
1716  $objText->getFont()->setBold(true);
1717  }
1718 
1719  if ( (isset($run->rPr->i["val"]) && ((string) $run->rPr->i["val"] == 'true' || (string) $run->rPr->i["val"] == '1'))
1720  || (isset($run->rPr->i) && !isset($run->rPr->i["val"])) ) {
1721  $objText->getFont()->setItalic(true);
1722  }
1723 
1724  if (isset($run->rPr->vertAlign) && isset($run->rPr->vertAlign["val"])) {
1725  $vertAlign = strtolower((string)$run->rPr->vertAlign["val"]);
1726  if ($vertAlign == 'superscript') {
1727  $objText->getFont()->setSuperScript(true);
1728  }
1729  if ($vertAlign == 'subscript') {
1730  $objText->getFont()->setSubScript(true);
1731  }
1732  }
1733 
1734  if (isset($run->rPr->u) && !isset($run->rPr->u["val"])) {
1735  $objText->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
1736  } else if (isset($run->rPr->u) && isset($run->rPr->u["val"])) {
1737  $objText->getFont()->setUnderline((string)$run->rPr->u["val"]);
1738  }
1739 
1740  if ( (isset($run->rPr->strike["val"]) && ((string) $run->rPr->strike["val"] == 'true' || (string) $run->rPr->strike["val"] == '1'))
1741  || (isset($run->rPr->strike) && !isset($run->rPr->strike["val"])) ) {
1742  $objText->getFont()->setStrikethrough(true);
1743  }
1744  }
1745  }
1746  }
1747 
1748  return $value;
1749  }
1750 
1751  private static function array_item($array, $key = 0) {
1752  return (isset($array[$key]) ? $array[$key] : null);
1753  }
1754 
1755  private static function dir_add($base, $add) {
1756  return preg_replace('~[^/]+/\.\./~', '', dirname($base) . "/$add");
1757  }
1758 
1759  private static function toCSSArray($style) {
1760  $style = str_replace(array("\r","\n"), "", $style);
1761 
1762  $temp = explode(';', $style);
1763  $style = array();
1764  foreach ($temp as $item) {
1765  $item = explode(':', $item);
1766 
1767  if (strpos($item[1], 'px') !== false) {
1768  $item[1] = str_replace('px', '', $item[1]);
1769  }
1770  if (strpos($item[1], 'pt') !== false) {
1771  $item[1] = str_replace('pt', '', $item[1]);
1772  $item[1] = PHPExcel_Shared_Font::fontSizeToPixels($item[1]);
1773  }
1774  if (strpos($item[1], 'in') !== false) {
1775  $item[1] = str_replace('in', '', $item[1]);
1776  $item[1] = PHPExcel_Shared_Font::inchSizeToPixels($item[1]);
1777  }
1778  if (strpos($item[1], 'cm') !== false) {
1779  $item[1] = str_replace('cm', '', $item[1]);
1780  $item[1] = PHPExcel_Shared_Font::centimeterSizeToPixels($item[1]);
1781  }
1782 
1783  $style[$item[0]] = $item[1];
1784  }
1785 
1786  return $style;
1787  }
1788 }