ILIAS  release_5-2 Revision v5.2.25-18-g3f80b828510
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 extends PHPExcel_Reader_Abstract implements PHPExcel_Reader_IReader
46 {
52  private $_referenceHelper = NULL;
53 
59  private static $_theme = NULL;
60 
61 
65  public function __construct() {
66  $this->_readFilter = new PHPExcel_Reader_DefaultReadFilter();
67  $this->_referenceHelper = PHPExcel_ReferenceHelper::getInstance();
68  }
69 
70 
78  public function canRead($pFilename)
79  {
80  // Check if file exists
81  if (!file_exists($pFilename)) {
82  throw new PHPExcel_Reader_Exception("Could not open " . $pFilename . " for reading! File does not exist.");
83  }
84 
85  $zipClass = PHPExcel_Settings::getZipClass();
86 
87  // Check if zip class exists
88 // if (!class_exists($zipClass, FALSE)) {
89 // throw new PHPExcel_Reader_Exception($zipClass . " library is not enabled");
90 // }
91 
92  $xl = false;
93  // Load file
94  $zip = new $zipClass;
95  if ($zip->open($pFilename) === true) {
96  // check if it is an OOXML archive
97  $rels = simplexml_load_string($this->securityScan($this->_getFromZipArchive($zip, "_rels/.rels")), 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions());
98  if ($rels !== false) {
99  foreach ($rels->Relationship as $rel) {
100  switch ($rel["Type"]) {
101  case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument":
102  if (basename($rel["Target"]) == 'workbook.xml') {
103  $xl = true;
104  }
105  break;
106 
107  }
108  }
109  }
110  $zip->close();
111  }
112 
113  return $xl;
114  }
115 
116 
123  public function listWorksheetNames($pFilename)
124  {
125  // Check if file exists
126  if (!file_exists($pFilename)) {
127  throw new PHPExcel_Reader_Exception("Could not open " . $pFilename . " for reading! File does not exist.");
128  }
129 
130  $worksheetNames = array();
131 
132  $zipClass = PHPExcel_Settings::getZipClass();
133 
134  $zip = new $zipClass;
135  $zip->open($pFilename);
136 
137  // The files we're looking at here are small enough that simpleXML is more efficient than XMLReader
138  $rels = simplexml_load_string(
139  $this->securityScan($this->_getFromZipArchive($zip, "_rels/.rels"), 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions())
140  ); //~ http://schemas.openxmlformats.org/package/2006/relationships");
141  foreach ($rels->Relationship as $rel) {
142  switch ($rel["Type"]) {
143  case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument":
144  $xmlWorkbook = simplexml_load_string(
145  $this->securityScan($this->_getFromZipArchive($zip, "{$rel['Target']}"), 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions())
146  ); //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
147 
148  if ($xmlWorkbook->sheets) {
149  foreach ($xmlWorkbook->sheets->sheet as $eleSheet) {
150  // Check if sheet should be skipped
151  $worksheetNames[] = (string) $eleSheet["name"];
152  }
153  }
154  }
155  }
156 
157  $zip->close();
158 
159  return $worksheetNames;
160  }
161 
162 
169  public function listWorksheetInfo($pFilename)
170  {
171  // Check if file exists
172  if (!file_exists($pFilename)) {
173  throw new PHPExcel_Reader_Exception("Could not open " . $pFilename . " for reading! File does not exist.");
174  }
175 
176  $worksheetInfo = array();
177 
178  $zipClass = PHPExcel_Settings::getZipClass();
179 
180  $zip = new $zipClass;
181  $zip->open($pFilename);
182 
183  $rels = simplexml_load_string($this->securityScan($this->_getFromZipArchive($zip, "_rels/.rels")), 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions()); //~ http://schemas.openxmlformats.org/package/2006/relationships");
184  foreach ($rels->Relationship as $rel) {
185  if ($rel["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument") {
186  $dir = dirname($rel["Target"]);
187  $relsWorkbook = simplexml_load_string($this->securityScan($this->_getFromZipArchive($zip, "$dir/_rels/" . basename($rel["Target"]) . ".rels")), 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions()); //~ http://schemas.openxmlformats.org/package/2006/relationships");
188  $relsWorkbook->registerXPathNamespace("rel", "http://schemas.openxmlformats.org/package/2006/relationships");
189 
190  $worksheets = array();
191  foreach ($relsWorkbook->Relationship as $ele) {
192  if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet") {
193  $worksheets[(string) $ele["Id"]] = $ele["Target"];
194  }
195  }
196 
197  $xmlWorkbook = simplexml_load_string($this->securityScan($this->_getFromZipArchive($zip, "{$rel['Target']}")), 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions()); //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
198  if ($xmlWorkbook->sheets) {
199  $dir = dirname($rel["Target"]);
200  foreach ($xmlWorkbook->sheets->sheet as $eleSheet) {
201  $tmpInfo = array(
202  'worksheetName' => (string) $eleSheet["name"],
203  'lastColumnLetter' => 'A',
204  'lastColumnIndex' => 0,
205  'totalRows' => 0,
206  'totalColumns' => 0,
207  );
208 
209  $fileWorksheet = $worksheets[(string) self::array_item($eleSheet->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "id")];
210 
211  $xml = new XMLReader();
212  $res = $xml->xml($this->securityScanFile('zip://'.PHPExcel_Shared_File::realpath($pFilename).'#'."$dir/$fileWorksheet"), null, PHPExcel_Settings::getLibXmlLoaderOptions());
213  $xml->setParserProperty(2,true);
214 
215  $currCells = 0;
216  while ($xml->read()) {
217  if ($xml->name == 'row' && $xml->nodeType == XMLReader::ELEMENT) {
218  $row = $xml->getAttribute('r');
219  $tmpInfo['totalRows'] = $row;
220  $tmpInfo['totalColumns'] = max($tmpInfo['totalColumns'],$currCells);
221  $currCells = 0;
222  } elseif ($xml->name == 'c' && $xml->nodeType == XMLReader::ELEMENT) {
223  $currCells++;
224  }
225  }
226  $tmpInfo['totalColumns'] = max($tmpInfo['totalColumns'],$currCells);
227  $xml->close();
228 
229  $tmpInfo['lastColumnIndex'] = $tmpInfo['totalColumns'] - 1;
230  $tmpInfo['lastColumnLetter'] = PHPExcel_Cell::stringFromColumnIndex($tmpInfo['lastColumnIndex']);
231 
232  $worksheetInfo[] = $tmpInfo;
233  }
234  }
235  }
236  }
237 
238  $zip->close();
239 
240  return $worksheetInfo;
241  }
242 
243 
244  private static function _castToBool($c) {
245 // echo 'Initial Cast to Boolean', PHP_EOL;
246  $value = isset($c->v) ? (string) $c->v : NULL;
247  if ($value == '0') {
248  return FALSE;
249  } elseif ($value == '1') {
250  return TRUE;
251  } else {
252  return (bool)$c->v;
253  }
254  return $value;
255  } // function _castToBool()
256 
257 
258  private static function _castToError($c) {
259 // echo 'Initial Cast to Error', PHP_EOL;
260  return isset($c->v) ? (string) $c->v : NULL;
261  } // function _castToError()
262 
263 
264  private static function _castToString($c) {
265 // echo 'Initial Cast to String, PHP_EOL;
266  return isset($c->v) ? (string) $c->v : NULL;
267  } // function _castToString()
268 
269 
270  private function _castToFormula($c,$r,&$cellDataType,&$value,&$calculatedValue,&$sharedFormulas,$castBaseType) {
271 // echo 'Formula', PHP_EOL;
272 // echo '$c->f is ', $c->f, PHP_EOL;
273  $cellDataType = 'f';
274  $value = "={$c->f}";
275  $calculatedValue = self::$castBaseType($c);
276 
277  // Shared formula?
278  if (isset($c->f['t']) && strtolower((string)$c->f['t']) == 'shared') {
279 // echo 'SHARED FORMULA', PHP_EOL;
280  $instance = (string)$c->f['si'];
281 
282 // echo 'Instance ID = ', $instance, PHP_EOL;
283 //
284 // echo 'Shared Formula Array:', PHP_EOL;
285 // print_r($sharedFormulas);
286  if (!isset($sharedFormulas[(string)$c->f['si']])) {
287 // echo 'SETTING NEW SHARED FORMULA', PHP_EOL;
288 // echo 'Master is ', $r, PHP_EOL;
289 // echo 'Formula is ', $value, PHP_EOL;
290  $sharedFormulas[$instance] = array( 'master' => $r,
291  'formula' => $value
292  );
293 // echo 'New Shared Formula Array:', PHP_EOL;
294 // print_r($sharedFormulas);
295  } else {
296 // echo 'GETTING SHARED FORMULA', PHP_EOL;
297 // echo 'Master is ', $sharedFormulas[$instance]['master'], PHP_EOL;
298 // echo 'Formula is ', $sharedFormulas[$instance]['formula'], PHP_EOL;
299  $master = PHPExcel_Cell::coordinateFromString($sharedFormulas[$instance]['master']);
300  $current = PHPExcel_Cell::coordinateFromString($r);
301 
302  $difference = array(0, 0);
303  $difference[0] = PHPExcel_Cell::columnIndexFromString($current[0]) - PHPExcel_Cell::columnIndexFromString($master[0]);
304  $difference[1] = $current[1] - $master[1];
305 
306  $value = $this->_referenceHelper->updateFormulaReferences( $sharedFormulas[$instance]['formula'],
307  'A1',
308  $difference[0],
309  $difference[1]
310  );
311 // echo 'Adjusted Formula is ', $value, PHP_EOL;
312  }
313  }
314  }
315 
316 
317  public function _getFromZipArchive($archive, $fileName = '')
318  {
319  // Root-relative paths
320  if (strpos($fileName, '//') !== false)
321  {
322  $fileName = substr($fileName, strpos($fileName, '//') + 1);
323  }
324  $fileName = PHPExcel_Shared_File::realpath($fileName);
325 
326  // Apache POI fixes
327  $contents = $archive->getFromName($fileName);
328  if ($contents === false)
329  {
330  $contents = $archive->getFromName(substr($fileName, 1));
331  }
332 
333  return $contents;
334  }
335 
336 
344  public function load($pFilename)
345  {
346  // Check if file exists
347  if (!file_exists($pFilename)) {
348  throw new PHPExcel_Reader_Exception("Could not open " . $pFilename . " for reading! File does not exist.");
349  }
350 
351  // Initialisations
352  $excel = new PHPExcel;
353  $excel->removeSheetByIndex(0);
354  if (!$this->_readDataOnly) {
355  $excel->removeCellStyleXfByIndex(0); // remove the default style
356  $excel->removeCellXfByIndex(0); // remove the default style
357  }
358 
359  $zipClass = PHPExcel_Settings::getZipClass();
360 
361  $zip = new $zipClass;
362  $zip->open($pFilename);
363 
364  // Read the theme first, because we need the colour scheme when reading the styles
365  $wbRels = simplexml_load_string($this->securityScan($this->_getFromZipArchive($zip, "xl/_rels/workbook.xml.rels")), 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions()); //~ http://schemas.openxmlformats.org/package/2006/relationships");
366  foreach ($wbRels->Relationship as $rel) {
367  switch ($rel["Type"]) {
368  case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/theme":
369  $themeOrderArray = array('lt1','dk1','lt2','dk2');
370  $themeOrderAdditional = count($themeOrderArray);
371 
372  $xmlTheme = simplexml_load_string($this->securityScan($this->_getFromZipArchive($zip, "xl/{$rel['Target']}")), 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions());
373  if (is_object($xmlTheme)) {
374  $xmlThemeName = $xmlTheme->attributes();
375  $xmlTheme = $xmlTheme->children("http://schemas.openxmlformats.org/drawingml/2006/main");
376  $themeName = (string)$xmlThemeName['name'];
377 
378  $colourScheme = $xmlTheme->themeElements->clrScheme->attributes();
379  $colourSchemeName = (string)$colourScheme['name'];
380  $colourScheme = $xmlTheme->themeElements->clrScheme->children("http://schemas.openxmlformats.org/drawingml/2006/main");
381 
382  $themeColours = array();
383  foreach ($colourScheme as $k => $xmlColour) {
384  $themePos = array_search($k,$themeOrderArray);
385  if ($themePos === false) {
386  $themePos = $themeOrderAdditional++;
387  }
388  if (isset($xmlColour->sysClr)) {
389  $xmlColourData = $xmlColour->sysClr->attributes();
390  $themeColours[$themePos] = $xmlColourData['lastClr'];
391  } elseif (isset($xmlColour->srgbClr)) {
392  $xmlColourData = $xmlColour->srgbClr->attributes();
393  $themeColours[$themePos] = $xmlColourData['val'];
394  }
395  }
396  self::$_theme = new PHPExcel_Reader_Excel2007_Theme($themeName,$colourSchemeName,$themeColours);
397  }
398  break;
399  }
400  }
401 
402  $rels = simplexml_load_string($this->securityScan($this->_getFromZipArchive($zip, "_rels/.rels")), 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions()); //~ http://schemas.openxmlformats.org/package/2006/relationships");
403  foreach ($rels->Relationship as $rel) {
404  switch ($rel["Type"]) {
405  case "http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties":
406  $xmlCore = simplexml_load_string($this->securityScan($this->_getFromZipArchive($zip, "{$rel['Target']}")), 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions());
407  if (is_object($xmlCore)) {
408  $xmlCore->registerXPathNamespace("dc", "http://purl.org/dc/elements/1.1/");
409  $xmlCore->registerXPathNamespace("dcterms", "http://purl.org/dc/terms/");
410  $xmlCore->registerXPathNamespace("cp", "http://schemas.openxmlformats.org/package/2006/metadata/core-properties");
411  $docProps = $excel->getProperties();
412  $docProps->setCreator((string) self::array_item($xmlCore->xpath("dc:creator")));
413  $docProps->setLastModifiedBy((string) self::array_item($xmlCore->xpath("cp:lastModifiedBy")));
414  $docProps->setCreated(strtotime(self::array_item($xmlCore->xpath("dcterms:created"))));
415  $docProps->setModified(strtotime(self::array_item($xmlCore->xpath("dcterms:modified"))));
416  $docProps->setTitle((string) self::array_item($xmlCore->xpath("dc:title")));
417  $docProps->setDescription((string) self::array_item($xmlCore->xpath("dc:description")));
418  $docProps->setSubject((string) self::array_item($xmlCore->xpath("dc:subject")));
419  $docProps->setKeywords((string) self::array_item($xmlCore->xpath("cp:keywords")));
420  $docProps->setCategory((string) self::array_item($xmlCore->xpath("cp:category")));
421  }
422  break;
423 
424  case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties":
425  $xmlCore = simplexml_load_string($this->securityScan($this->_getFromZipArchive($zip, "{$rel['Target']}")), 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions());
426  if (is_object($xmlCore)) {
427  $docProps = $excel->getProperties();
428  if (isset($xmlCore->Company))
429  $docProps->setCompany((string) $xmlCore->Company);
430  if (isset($xmlCore->Manager))
431  $docProps->setManager((string) $xmlCore->Manager);
432  }
433  break;
434 
435  case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/custom-properties":
436  $xmlCore = simplexml_load_string($this->securityScan($this->_getFromZipArchive($zip, "{$rel['Target']}")), 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions());
437  if (is_object($xmlCore)) {
438  $docProps = $excel->getProperties();
439  foreach ($xmlCore as $xmlProperty) {
440  $cellDataOfficeAttributes = $xmlProperty->attributes();
441  if (isset($cellDataOfficeAttributes['name'])) {
442  $propertyName = (string) $cellDataOfficeAttributes['name'];
443  $cellDataOfficeChildren = $xmlProperty->children('http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes');
444  $attributeType = $cellDataOfficeChildren->getName();
445  $attributeValue = (string) $cellDataOfficeChildren->{$attributeType};
446  $attributeValue = PHPExcel_DocumentProperties::convertProperty($attributeValue,$attributeType);
447  $attributeType = PHPExcel_DocumentProperties::convertPropertyType($attributeType);
448  $docProps->setCustomProperty($propertyName,$attributeValue,$attributeType);
449  }
450  }
451  }
452  break;
453  //Ribbon
454  case "http://schemas.microsoft.com/office/2006/relationships/ui/extensibility":
455  $customUI = $rel['Target'];
456  if(!is_null($customUI)){
457  $this->_readRibbon($excel, $customUI, $zip);
458  }
459  break;
460  case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument":
461  $dir = dirname($rel["Target"]);
462  $relsWorkbook = simplexml_load_string($this->securityScan($this->_getFromZipArchive($zip, "$dir/_rels/" . basename($rel["Target"]) . ".rels")), 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions()); //~ http://schemas.openxmlformats.org/package/2006/relationships");
463  $relsWorkbook->registerXPathNamespace("rel", "http://schemas.openxmlformats.org/package/2006/relationships");
464 
465  $sharedStrings = array();
466  $xpath = self::array_item($relsWorkbook->xpath("rel:Relationship[@Type='http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings']"));
467  $xmlStrings = simplexml_load_string($this->securityScan($this->_getFromZipArchive($zip, "$dir/$xpath[Target]")), 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions()); //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
468  if (isset($xmlStrings) && isset($xmlStrings->si)) {
469  foreach ($xmlStrings->si as $val) {
470  if (isset($val->t)) {
471  $sharedStrings[] = PHPExcel_Shared_String::ControlCharacterOOXML2PHP( (string) $val->t );
472  } elseif (isset($val->r)) {
473  $sharedStrings[] = $this->_parseRichText($val);
474  }
475  }
476  }
477 
478  $worksheets = array();
479  $macros = $customUI = NULL;
480  foreach ($relsWorkbook->Relationship as $ele) {
481  switch($ele['Type']){
482  case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet":
483  $worksheets[(string) $ele["Id"]] = $ele["Target"];
484  break;
485  // a vbaProject ? (: some macros)
486  case "http://schemas.microsoft.com/office/2006/relationships/vbaProject":
487  $macros = $ele["Target"];
488  break;
489  }
490  }
491 
492  if(!is_null($macros)){
493  $macrosCode = $this->_getFromZipArchive($zip, 'xl/vbaProject.bin');//vbaProject.bin always in 'xl' dir and always named vbaProject.bin
494  if($macrosCode !== false){
495  $excel->setMacrosCode($macrosCode);
496  $excel->setHasMacros(true);
497  //short-circuit : not reading vbaProject.bin.rel to get Signature =>allways vbaProjectSignature.bin in 'xl' dir
498  $Certificate = $this->_getFromZipArchive($zip, 'xl/vbaProjectSignature.bin');
499  if($Certificate !== false)
500  $excel->setMacrosCertificate($Certificate);
501  }
502  }
503  $styles = array();
504  $cellStyles = array();
505  $xpath = self::array_item($relsWorkbook->xpath("rel:Relationship[@Type='http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles']"));
506  $xmlStyles = simplexml_load_string($this->securityScan($this->_getFromZipArchive($zip, "$dir/$xpath[Target]")), 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions()); //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
507  $numFmts = null;
508  if ($xmlStyles && $xmlStyles->numFmts[0]) {
509  $numFmts = $xmlStyles->numFmts[0];
510  }
511  if (isset($numFmts) && ($numFmts !== NULL)) {
512  $numFmts->registerXPathNamespace("sml", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
513  }
514  if (!$this->_readDataOnly && $xmlStyles) {
515  foreach ($xmlStyles->cellXfs->xf as $xf) {
516  $numFmt = PHPExcel_Style_NumberFormat::FORMAT_GENERAL;
517 
518  if ($xf["numFmtId"]) {
519  if (isset($numFmts)) {
520  $tmpNumFmt = self::array_item($numFmts->xpath("sml:numFmt[@numFmtId=$xf[numFmtId]]"));
521 
522  if (isset($tmpNumFmt["formatCode"])) {
523  $numFmt = (string) $tmpNumFmt["formatCode"];
524  }
525  }
526 
527  if ((int)$xf["numFmtId"] < 164) {
528  $numFmt = PHPExcel_Style_NumberFormat::builtInFormatCode((int)$xf["numFmtId"]);
529  }
530  }
531  $quotePrefix = false;
532  if (isset($xf["quotePrefix"])) {
533  $quotePrefix = (boolean) $xf["quotePrefix"];
534  }
535  //$numFmt = str_replace('mm', 'i', $numFmt);
536  //$numFmt = str_replace('h', 'H', $numFmt);
537 
538  $style = (object) array(
539  "numFmt" => $numFmt,
540  "font" => $xmlStyles->fonts->font[intval($xf["fontId"])],
541  "fill" => $xmlStyles->fills->fill[intval($xf["fillId"])],
542  "border" => $xmlStyles->borders->border[intval($xf["borderId"])],
543  "alignment" => $xf->alignment,
544  "protection" => $xf->protection,
545  "quotePrefix" => $quotePrefix,
546  );
547  $styles[] = $style;
548 
549  // add style to cellXf collection
550  $objStyle = new PHPExcel_Style;
551  self::_readStyle($objStyle, $style);
552  $excel->addCellXf($objStyle);
553  }
554 
555  foreach ($xmlStyles->cellStyleXfs->xf as $xf) {
556  $numFmt = PHPExcel_Style_NumberFormat::FORMAT_GENERAL;
557  if ($numFmts && $xf["numFmtId"]) {
558  $tmpNumFmt = self::array_item($numFmts->xpath("sml:numFmt[@numFmtId=$xf[numFmtId]]"));
559  if (isset($tmpNumFmt["formatCode"])) {
560  $numFmt = (string) $tmpNumFmt["formatCode"];
561  } else if ((int)$xf["numFmtId"] < 165) {
562  $numFmt = PHPExcel_Style_NumberFormat::builtInFormatCode((int)$xf["numFmtId"]);
563  }
564  }
565 
566  $cellStyle = (object) array(
567  "numFmt" => $numFmt,
568  "font" => $xmlStyles->fonts->font[intval($xf["fontId"])],
569  "fill" => $xmlStyles->fills->fill[intval($xf["fillId"])],
570  "border" => $xmlStyles->borders->border[intval($xf["borderId"])],
571  "alignment" => $xf->alignment,
572  "protection" => $xf->protection,
573  "quotePrefix" => $quotePrefix,
574  );
575  $cellStyles[] = $cellStyle;
576 
577  // add style to cellStyleXf collection
578  $objStyle = new PHPExcel_Style;
579  self::_readStyle($objStyle, $cellStyle);
580  $excel->addCellStyleXf($objStyle);
581  }
582  }
583 
584  $dxfs = array();
585  if (!$this->_readDataOnly && $xmlStyles) {
586  // Conditional Styles
587  if ($xmlStyles->dxfs) {
588  foreach ($xmlStyles->dxfs->dxf as $dxf) {
589  $style = new PHPExcel_Style(FALSE, TRUE);
590  self::_readStyle($style, $dxf);
591  $dxfs[] = $style;
592  }
593  }
594  // Cell Styles
595  if ($xmlStyles->cellStyles) {
596  foreach ($xmlStyles->cellStyles->cellStyle as $cellStyle) {
597  if (intval($cellStyle['builtinId']) == 0) {
598  if (isset($cellStyles[intval($cellStyle['xfId'])])) {
599  // Set default style
600  $style = new PHPExcel_Style;
601  self::_readStyle($style, $cellStyles[intval($cellStyle['xfId'])]);
602 
603  // normal style, currently not using it for anything
604  }
605  }
606  }
607  }
608  }
609 
610  $xmlWorkbook = simplexml_load_string($this->securityScan($this->_getFromZipArchive($zip, "{$rel['Target']}")), 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions()); //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
611 
612  // Set base date
613  if ($xmlWorkbook->workbookPr) {
614  PHPExcel_Shared_Date::setExcelCalendar(PHPExcel_Shared_Date::CALENDAR_WINDOWS_1900);
615  if (isset($xmlWorkbook->workbookPr['date1904'])) {
616  if (self::boolean((string) $xmlWorkbook->workbookPr['date1904'])) {
617  PHPExcel_Shared_Date::setExcelCalendar(PHPExcel_Shared_Date::CALENDAR_MAC_1904);
618  }
619  }
620  }
621 
622  $sheetId = 0; // keep track of new sheet id in final workbook
623  $oldSheetId = -1; // keep track of old sheet id in final workbook
624  $countSkippedSheets = 0; // keep track of number of skipped sheets
625  $mapSheetId = array(); // mapping of sheet ids from old to new
626 
627 
628  $charts = $chartDetails = array();
629 
630  if ($xmlWorkbook->sheets) {
631  foreach ($xmlWorkbook->sheets->sheet as $eleSheet) {
632  ++$oldSheetId;
633 
634  // Check if sheet should be skipped
635  if (isset($this->_loadSheetsOnly) && !in_array((string) $eleSheet["name"], $this->_loadSheetsOnly)) {
636  ++$countSkippedSheets;
637  $mapSheetId[$oldSheetId] = null;
638  continue;
639  }
640 
641  // Map old sheet id in original workbook to new sheet id.
642  // They will differ if loadSheetsOnly() is being used
643  $mapSheetId[$oldSheetId] = $oldSheetId - $countSkippedSheets;
644 
645  // Load sheet
646  $docSheet = $excel->createSheet();
647  // Use false for $updateFormulaCellReferences to prevent adjustment of worksheet
648  // references in formula cells... during the load, all formulae should be correct,
649  // and we're simply bringing the worksheet name in line with the formula, not the
650  // reverse
651  $docSheet->setTitle((string) $eleSheet["name"],false);
652  $fileWorksheet = $worksheets[(string) self::array_item($eleSheet->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "id")];
653  $xmlSheet = simplexml_load_string($this->securityScan($this->_getFromZipArchive($zip, "$dir/$fileWorksheet")), 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions()); //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
654 
655  $sharedFormulas = array();
656 
657  if (isset($eleSheet["state"]) && (string) $eleSheet["state"] != '') {
658  $docSheet->setSheetState( (string) $eleSheet["state"] );
659  }
660 
661  if (isset($xmlSheet->sheetViews) && isset($xmlSheet->sheetViews->sheetView)) {
662  if (isset($xmlSheet->sheetViews->sheetView['zoomScale'])) {
663  $docSheet->getSheetView()->setZoomScale( intval($xmlSheet->sheetViews->sheetView['zoomScale']) );
664  }
665 
666  if (isset($xmlSheet->sheetViews->sheetView['zoomScaleNormal'])) {
667  $docSheet->getSheetView()->setZoomScaleNormal( intval($xmlSheet->sheetViews->sheetView['zoomScaleNormal']) );
668  }
669 
670  if (isset($xmlSheet->sheetViews->sheetView['view'])) {
671  $docSheet->getSheetView()->setView((string) $xmlSheet->sheetViews->sheetView['view']);
672  }
673 
674  if (isset($xmlSheet->sheetViews->sheetView['showGridLines'])) {
675  $docSheet->setShowGridLines(self::boolean((string)$xmlSheet->sheetViews->sheetView['showGridLines']));
676  }
677 
678  if (isset($xmlSheet->sheetViews->sheetView['showRowColHeaders'])) {
679  $docSheet->setShowRowColHeaders(self::boolean((string)$xmlSheet->sheetViews->sheetView['showRowColHeaders']));
680  }
681 
682  if (isset($xmlSheet->sheetViews->sheetView['rightToLeft'])) {
683  $docSheet->setRightToLeft(self::boolean((string)$xmlSheet->sheetViews->sheetView['rightToLeft']));
684  }
685 
686  if (isset($xmlSheet->sheetViews->sheetView->pane)) {
687  if (isset($xmlSheet->sheetViews->sheetView->pane['topLeftCell'])) {
688  $docSheet->freezePane( (string)$xmlSheet->sheetViews->sheetView->pane['topLeftCell'] );
689  } else {
690  $xSplit = 0;
691  $ySplit = 0;
692 
693  if (isset($xmlSheet->sheetViews->sheetView->pane['xSplit'])) {
694  $xSplit = 1 + intval($xmlSheet->sheetViews->sheetView->pane['xSplit']);
695  }
696 
697  if (isset($xmlSheet->sheetViews->sheetView->pane['ySplit'])) {
698  $ySplit = 1 + intval($xmlSheet->sheetViews->sheetView->pane['ySplit']);
699  }
700 
701  $docSheet->freezePaneByColumnAndRow($xSplit, $ySplit);
702  }
703  }
704 
705  if (isset($xmlSheet->sheetViews->sheetView->selection)) {
706  if (isset($xmlSheet->sheetViews->sheetView->selection['sqref'])) {
707  $sqref = (string)$xmlSheet->sheetViews->sheetView->selection['sqref'];
708  $sqref = explode(' ', $sqref);
709  $sqref = $sqref[0];
710  $docSheet->setSelectedCells($sqref);
711  }
712  }
713 
714  }
715 
716  if (isset($xmlSheet->sheetPr) && isset($xmlSheet->sheetPr->tabColor)) {
717  if (isset($xmlSheet->sheetPr->tabColor['rgb'])) {
718  $docSheet->getTabColor()->setARGB( (string)$xmlSheet->sheetPr->tabColor['rgb'] );
719  }
720  }
721  if (isset($xmlSheet->sheetPr) && isset($xmlSheet->sheetPr['codeName'])) {
722  $docSheet->setCodeName((string) $xmlSheet->sheetPr['codeName']);
723  }
724  if (isset($xmlSheet->sheetPr) && isset($xmlSheet->sheetPr->outlinePr)) {
725  if (isset($xmlSheet->sheetPr->outlinePr['summaryRight']) &&
726  !self::boolean((string) $xmlSheet->sheetPr->outlinePr['summaryRight'])) {
727  $docSheet->setShowSummaryRight(FALSE);
728  } else {
729  $docSheet->setShowSummaryRight(TRUE);
730  }
731 
732  if (isset($xmlSheet->sheetPr->outlinePr['summaryBelow']) &&
733  !self::boolean((string) $xmlSheet->sheetPr->outlinePr['summaryBelow'])) {
734  $docSheet->setShowSummaryBelow(FALSE);
735  } else {
736  $docSheet->setShowSummaryBelow(TRUE);
737  }
738  }
739 
740  if (isset($xmlSheet->sheetPr) && isset($xmlSheet->sheetPr->pageSetUpPr)) {
741  if (isset($xmlSheet->sheetPr->pageSetUpPr['fitToPage']) &&
742  !self::boolean((string) $xmlSheet->sheetPr->pageSetUpPr['fitToPage'])) {
743  $docSheet->getPageSetup()->setFitToPage(FALSE);
744  } else {
745  $docSheet->getPageSetup()->setFitToPage(TRUE);
746  }
747  }
748 
749  if (isset($xmlSheet->sheetFormatPr)) {
750  if (isset($xmlSheet->sheetFormatPr['customHeight']) &&
751  self::boolean((string) $xmlSheet->sheetFormatPr['customHeight']) &&
752  isset($xmlSheet->sheetFormatPr['defaultRowHeight'])) {
753  $docSheet->getDefaultRowDimension()->setRowHeight( (float)$xmlSheet->sheetFormatPr['defaultRowHeight'] );
754  }
755  if (isset($xmlSheet->sheetFormatPr['defaultColWidth'])) {
756  $docSheet->getDefaultColumnDimension()->setWidth( (float)$xmlSheet->sheetFormatPr['defaultColWidth'] );
757  }
758  if (isset($xmlSheet->sheetFormatPr['zeroHeight']) &&
759  ((string)$xmlSheet->sheetFormatPr['zeroHeight'] == '1')) {
760  $docSheet->getDefaultRowDimension()->setZeroHeight(true);
761  }
762  }
763 
764  if (isset($xmlSheet->cols) && !$this->_readDataOnly) {
765  foreach ($xmlSheet->cols->col as $col) {
766  for ($i = intval($col["min"]) - 1; $i < intval($col["max"]); ++$i) {
767  if ($col["style"] && !$this->_readDataOnly) {
768  $docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setXfIndex(intval($col["style"]));
769  }
770  if (self::boolean($col["bestFit"])) {
771  //$docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setAutoSize(TRUE);
772  }
773  if (self::boolean($col["hidden"])) {
774  // echo PHPExcel_Cell::stringFromColumnIndex($i),': HIDDEN COLUMN',PHP_EOL;
775  $docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setVisible(FALSE);
776  }
777  if (self::boolean($col["collapsed"])) {
778  $docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setCollapsed(TRUE);
779  }
780  if ($col["outlineLevel"] > 0) {
781  $docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setOutlineLevel(intval($col["outlineLevel"]));
782  }
783  $docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setWidth(floatval($col["width"]));
784 
785  if (intval($col["max"]) == 16384) {
786  break;
787  }
788  }
789  }
790  }
791 
792  if (isset($xmlSheet->printOptions) && !$this->_readDataOnly) {
793  if (self::boolean((string) $xmlSheet->printOptions['gridLinesSet'])) {
794  $docSheet->setShowGridlines(TRUE);
795  }
796 
797  if (self::boolean((string) $xmlSheet->printOptions['gridLines'])) {
798  $docSheet->setPrintGridlines(TRUE);
799  }
800 
801  if (self::boolean((string) $xmlSheet->printOptions['horizontalCentered'])) {
802  $docSheet->getPageSetup()->setHorizontalCentered(TRUE);
803  }
804  if (self::boolean((string) $xmlSheet->printOptions['verticalCentered'])) {
805  $docSheet->getPageSetup()->setVerticalCentered(TRUE);
806  }
807  }
808 
809  if ($xmlSheet && $xmlSheet->sheetData && $xmlSheet->sheetData->row) {
810  foreach ($xmlSheet->sheetData->row as $row) {
811  if ($row["ht"] && !$this->_readDataOnly) {
812  $docSheet->getRowDimension(intval($row["r"]))->setRowHeight(floatval($row["ht"]));
813  }
814  if (self::boolean($row["hidden"]) && !$this->_readDataOnly) {
815  $docSheet->getRowDimension(intval($row["r"]))->setVisible(FALSE);
816  }
817  if (self::boolean($row["collapsed"])) {
818  $docSheet->getRowDimension(intval($row["r"]))->setCollapsed(TRUE);
819  }
820  if ($row["outlineLevel"] > 0) {
821  $docSheet->getRowDimension(intval($row["r"]))->setOutlineLevel(intval($row["outlineLevel"]));
822  }
823  if ($row["s"] && !$this->_readDataOnly) {
824  $docSheet->getRowDimension(intval($row["r"]))->setXfIndex(intval($row["s"]));
825  }
826 
827  foreach ($row->c as $c) {
828  $r = (string) $c["r"];
829  $cellDataType = (string) $c["t"];
830  $value = null;
831  $calculatedValue = null;
832 
833  // Read cell?
834  if ($this->getReadFilter() !== NULL) {
835  $coordinates = PHPExcel_Cell::coordinateFromString($r);
836 
837  if (!$this->getReadFilter()->readCell($coordinates[0], $coordinates[1], $docSheet->getTitle())) {
838  continue;
839  }
840  }
841 
842  // echo 'Reading cell ', $coordinates[0], $coordinates[1], PHP_EOL;
843  // print_r($c);
844  // echo PHP_EOL;
845  // echo 'Cell Data Type is ', $cellDataType, ': ';
846  //
847  // Read cell!
848  switch ($cellDataType) {
849  case "s":
850  // echo 'String', PHP_EOL;
851  if ((string)$c->v != '') {
852  $value = $sharedStrings[intval($c->v)];
853 
854  if ($value instanceof PHPExcel_RichText) {
855  $value = clone $value;
856  }
857  } else {
858  $value = '';
859  }
860 
861  break;
862  case "b":
863  // echo 'Boolean', PHP_EOL;
864  if (!isset($c->f)) {
865  $value = self::_castToBool($c);
866  } else {
867  // Formula
868  $this->_castToFormula($c,$r,$cellDataType,$value,$calculatedValue,$sharedFormulas,'_castToBool');
869  if (isset($c->f['t'])) {
870  $att = array();
871  $att = $c->f;
872  $docSheet->getCell($r)->setFormulaAttributes($att);
873  }
874  // echo '$calculatedValue = ', $calculatedValue, PHP_EOL;
875  }
876  break;
877  case "inlineStr":
878  // echo 'Inline String', PHP_EOL;
879  $value = $this->_parseRichText($c->is);
880 
881  break;
882  case "e":
883  // echo 'Error', PHP_EOL;
884  if (!isset($c->f)) {
885  $value = self::_castToError($c);
886  } else {
887  // Formula
888  $this->_castToFormula($c,$r,$cellDataType,$value,$calculatedValue,$sharedFormulas,'_castToError');
889  // echo '$calculatedValue = ', $calculatedValue, PHP_EOL;
890  }
891 
892  break;
893 
894  default:
895  // echo 'Default', PHP_EOL;
896  if (!isset($c->f)) {
897  // echo 'Not a Formula', PHP_EOL;
898  $value = self::_castToString($c);
899  } else {
900  // echo 'Treat as Formula', PHP_EOL;
901  // Formula
902  $this->_castToFormula($c,$r,$cellDataType,$value,$calculatedValue,$sharedFormulas,'_castToString');
903  // echo '$calculatedValue = ', $calculatedValue, PHP_EOL;
904  }
905 
906  break;
907  }
908  // echo 'Value is ', $value, PHP_EOL;
909 
910  // Check for numeric values
911  if (is_numeric($value) && $cellDataType != 's') {
912  if ($value == (int)$value) $value = (int)$value;
913  elseif ($value == (float)$value) $value = (float)$value;
914  elseif ($value == (double)$value) $value = (double)$value;
915  }
916 
917  // Rich text?
918  if ($value instanceof PHPExcel_RichText && $this->_readDataOnly) {
919  $value = $value->getPlainText();
920  }
921 
922  $cell = $docSheet->getCell($r);
923  // Assign value
924  if ($cellDataType != '') {
925  $cell->setValueExplicit($value, $cellDataType);
926  } else {
927  $cell->setValue($value);
928  }
929  if ($calculatedValue !== NULL) {
930  $cell->setCalculatedValue($calculatedValue);
931  }
932 
933  // Style information?
934  if ($c["s"] && !$this->_readDataOnly) {
935  // no style index means 0, it seems
936  $cell->setXfIndex(isset($styles[intval($c["s"])]) ?
937  intval($c["s"]) : 0);
938  }
939  }
940  }
941  }
942 
943  $conditionals = array();
944  if (!$this->_readDataOnly && $xmlSheet && $xmlSheet->conditionalFormatting) {
945  foreach ($xmlSheet->conditionalFormatting as $conditional) {
946  foreach ($conditional->cfRule as $cfRule) {
947  if (
948  (
949  (string)$cfRule["type"] == PHPExcel_Style_Conditional::CONDITION_NONE ||
950  (string)$cfRule["type"] == PHPExcel_Style_Conditional::CONDITION_CELLIS ||
951  (string)$cfRule["type"] == PHPExcel_Style_Conditional::CONDITION_CONTAINSTEXT ||
952  (string)$cfRule["type"] == PHPExcel_Style_Conditional::CONDITION_EXPRESSION
953  ) && isset($dxfs[intval($cfRule["dxfId"])])
954  ) {
955  $conditionals[(string) $conditional["sqref"]][intval($cfRule["priority"])] = $cfRule;
956  }
957  }
958  }
959 
960  foreach ($conditionals as $ref => $cfRules) {
961  ksort($cfRules);
962  $conditionalStyles = array();
963  foreach ($cfRules as $cfRule) {
964  $objConditional = new PHPExcel_Style_Conditional();
965  $objConditional->setConditionType((string)$cfRule["type"]);
966  $objConditional->setOperatorType((string)$cfRule["operator"]);
967 
968  if ((string)$cfRule["text"] != '') {
969  $objConditional->setText((string)$cfRule["text"]);
970  }
971 
972  if (count($cfRule->formula) > 1) {
973  foreach ($cfRule->formula as $formula) {
974  $objConditional->addCondition((string)$formula);
975  }
976  } else {
977  $objConditional->addCondition((string)$cfRule->formula);
978  }
979  $objConditional->setStyle(clone $dxfs[intval($cfRule["dxfId"])]);
980  $conditionalStyles[] = $objConditional;
981  }
982 
983  // Extract all cell references in $ref
984  $aReferences = PHPExcel_Cell::extractAllCellReferencesInRange($ref);
985  foreach ($aReferences as $reference) {
986  $docSheet->getStyle($reference)->setConditionalStyles($conditionalStyles);
987  }
988  }
989  }
990 
991  $aKeys = array("sheet", "objects", "scenarios", "formatCells", "formatColumns", "formatRows", "insertColumns", "insertRows", "insertHyperlinks", "deleteColumns", "deleteRows", "selectLockedCells", "sort", "autoFilter", "pivotTables", "selectUnlockedCells");
992  if (!$this->_readDataOnly && $xmlSheet && $xmlSheet->sheetProtection) {
993  foreach ($aKeys as $key) {
994  $method = "set" . ucfirst($key);
995  $docSheet->getProtection()->$method(self::boolean((string) $xmlSheet->sheetProtection[$key]));
996  }
997  }
998 
999  if (!$this->_readDataOnly && $xmlSheet && $xmlSheet->sheetProtection) {
1000  $docSheet->getProtection()->setPassword((string) $xmlSheet->sheetProtection["password"], TRUE);
1001  if ($xmlSheet->protectedRanges->protectedRange) {
1002  foreach ($xmlSheet->protectedRanges->protectedRange as $protectedRange) {
1003  $docSheet->protectCells((string) $protectedRange["sqref"], (string) $protectedRange["password"], true);
1004  }
1005  }
1006  }
1007 
1008  if ($xmlSheet && $xmlSheet->autoFilter && !$this->_readDataOnly) {
1009  $autoFilterRange = (string) $xmlSheet->autoFilter["ref"];
1010  if (strpos($autoFilterRange, ':') !== false) {
1011  $autoFilter = $docSheet->getAutoFilter();
1012  $autoFilter->setRange($autoFilterRange);
1013 
1014  foreach ($xmlSheet->autoFilter->filterColumn as $filterColumn) {
1015  $column = $autoFilter->getColumnByOffset((integer) $filterColumn["colId"]);
1016  // Check for standard filters
1017  if ($filterColumn->filters) {
1018  $column->setFilterType(PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_FILTERTYPE_FILTER);
1019  $filters = $filterColumn->filters;
1020  if ((isset($filters["blank"])) && ($filters["blank"] == 1)) {
1021  $column->createRule()->setRule(
1022  NULL, // Operator is undefined, but always treated as EQUAL
1023  ''
1024  )
1025  ->setRuleType(PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_FILTER);
1026  }
1027  // Standard filters are always an OR join, so no join rule needs to be set
1028  // Entries can be either filter elements
1029  foreach ($filters->filter as $filterRule) {
1030  $column->createRule()->setRule(
1031  NULL, // Operator is undefined, but always treated as EQUAL
1032  (string) $filterRule["val"]
1033  )
1034  ->setRuleType(PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_FILTER);
1035  }
1036  // Or Date Group elements
1037  foreach ($filters->dateGroupItem as $dateGroupItem) {
1038  $column->createRule()->setRule(
1039  NULL, // Operator is undefined, but always treated as EQUAL
1040  array(
1041  'year' => (string) $dateGroupItem["year"],
1042  'month' => (string) $dateGroupItem["month"],
1043  'day' => (string) $dateGroupItem["day"],
1044  'hour' => (string) $dateGroupItem["hour"],
1045  'minute' => (string) $dateGroupItem["minute"],
1046  'second' => (string) $dateGroupItem["second"],
1047  ),
1048  (string) $dateGroupItem["dateTimeGrouping"]
1049  )
1050  ->setRuleType(PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP);
1051  }
1052  }
1053  // Check for custom filters
1054  if ($filterColumn->customFilters) {
1055  $column->setFilterType(PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_FILTERTYPE_CUSTOMFILTER);
1056  $customFilters = $filterColumn->customFilters;
1057  // Custom filters can an AND or an OR join;
1058  // and there should only ever be one or two entries
1059  if ((isset($customFilters["and"])) && ($customFilters["and"] == 1)) {
1060  $column->setJoin(PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_COLUMN_JOIN_AND);
1061  }
1062  foreach ($customFilters->customFilter as $filterRule) {
1063  $column->createRule()->setRule(
1064  (string) $filterRule["operator"],
1065  (string) $filterRule["val"]
1066  )
1067  ->setRuleType(PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_CUSTOMFILTER);
1068  }
1069  }
1070  // Check for dynamic filters
1071  if ($filterColumn->dynamicFilter) {
1072  $column->setFilterType(PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_FILTERTYPE_DYNAMICFILTER);
1073  // We should only ever have one dynamic filter
1074  foreach ($filterColumn->dynamicFilter as $filterRule) {
1075  $column->createRule()->setRule(
1076  NULL, // Operator is undefined, but always treated as EQUAL
1077  (string) $filterRule["val"],
1078  (string) $filterRule["type"]
1079  )
1080  ->setRuleType(PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMICFILTER);
1081  if (isset($filterRule["val"])) {
1082  $column->setAttribute('val',(string) $filterRule["val"]);
1083  }
1084  if (isset($filterRule["maxVal"])) {
1085  $column->setAttribute('maxVal',(string) $filterRule["maxVal"]);
1086  }
1087  }
1088  }
1089  // Check for dynamic filters
1090  if ($filterColumn->top10) {
1091  $column->setFilterType(PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_FILTERTYPE_TOPTENFILTER);
1092  // We should only ever have one top10 filter
1093  foreach ($filterColumn->top10 as $filterRule) {
1094  $column->createRule()->setRule(
1095  (((isset($filterRule["percent"])) && ($filterRule["percent"] == 1))
1096  ? PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_PERCENT
1097  : PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_BY_VALUE
1098  ),
1099  (string) $filterRule["val"],
1100  (((isset($filterRule["top"])) && ($filterRule["top"] == 1))
1101  ? PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_TOP
1102  : PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_BOTTOM
1103  )
1104  )
1105  ->setRuleType(PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_TOPTENFILTER);
1106  }
1107  }
1108  }
1109  }
1110  }
1111 
1112  if ($xmlSheet && $xmlSheet->mergeCells && $xmlSheet->mergeCells->mergeCell && !$this->_readDataOnly) {
1113  foreach ($xmlSheet->mergeCells->mergeCell as $mergeCell) {
1114  $mergeRef = (string) $mergeCell["ref"];
1115  if (strpos($mergeRef,':') !== FALSE) {
1116  $docSheet->mergeCells((string) $mergeCell["ref"]);
1117  }
1118  }
1119  }
1120 
1121  if ($xmlSheet && $xmlSheet->pageMargins && !$this->_readDataOnly) {
1122  $docPageMargins = $docSheet->getPageMargins();
1123  $docPageMargins->setLeft(floatval($xmlSheet->pageMargins["left"]));
1124  $docPageMargins->setRight(floatval($xmlSheet->pageMargins["right"]));
1125  $docPageMargins->setTop(floatval($xmlSheet->pageMargins["top"]));
1126  $docPageMargins->setBottom(floatval($xmlSheet->pageMargins["bottom"]));
1127  $docPageMargins->setHeader(floatval($xmlSheet->pageMargins["header"]));
1128  $docPageMargins->setFooter(floatval($xmlSheet->pageMargins["footer"]));
1129  }
1130 
1131  if ($xmlSheet && $xmlSheet->pageSetup && !$this->_readDataOnly) {
1132  $docPageSetup = $docSheet->getPageSetup();
1133 
1134  if (isset($xmlSheet->pageSetup["orientation"])) {
1135  $docPageSetup->setOrientation((string) $xmlSheet->pageSetup["orientation"]);
1136  }
1137  if (isset($xmlSheet->pageSetup["paperSize"])) {
1138  $docPageSetup->setPaperSize(intval($xmlSheet->pageSetup["paperSize"]));
1139  }
1140  if (isset($xmlSheet->pageSetup["scale"])) {
1141  $docPageSetup->setScale(intval($xmlSheet->pageSetup["scale"]), FALSE);
1142  }
1143  if (isset($xmlSheet->pageSetup["fitToHeight"]) && intval($xmlSheet->pageSetup["fitToHeight"]) >= 0) {
1144  $docPageSetup->setFitToHeight(intval($xmlSheet->pageSetup["fitToHeight"]), FALSE);
1145  }
1146  if (isset($xmlSheet->pageSetup["fitToWidth"]) && intval($xmlSheet->pageSetup["fitToWidth"]) >= 0) {
1147  $docPageSetup->setFitToWidth(intval($xmlSheet->pageSetup["fitToWidth"]), FALSE);
1148  }
1149  if (isset($xmlSheet->pageSetup["firstPageNumber"]) && isset($xmlSheet->pageSetup["useFirstPageNumber"]) &&
1150  self::boolean((string) $xmlSheet->pageSetup["useFirstPageNumber"])) {
1151  $docPageSetup->setFirstPageNumber(intval($xmlSheet->pageSetup["firstPageNumber"]));
1152  }
1153  }
1154 
1155  if ($xmlSheet && $xmlSheet->headerFooter && !$this->_readDataOnly) {
1156  $docHeaderFooter = $docSheet->getHeaderFooter();
1157 
1158  if (isset($xmlSheet->headerFooter["differentOddEven"]) &&
1159  self::boolean((string)$xmlSheet->headerFooter["differentOddEven"])) {
1160  $docHeaderFooter->setDifferentOddEven(TRUE);
1161  } else {
1162  $docHeaderFooter->setDifferentOddEven(FALSE);
1163  }
1164  if (isset($xmlSheet->headerFooter["differentFirst"]) &&
1165  self::boolean((string)$xmlSheet->headerFooter["differentFirst"])) {
1166  $docHeaderFooter->setDifferentFirst(TRUE);
1167  } else {
1168  $docHeaderFooter->setDifferentFirst(FALSE);
1169  }
1170  if (isset($xmlSheet->headerFooter["scaleWithDoc"]) &&
1171  !self::boolean((string)$xmlSheet->headerFooter["scaleWithDoc"])) {
1172  $docHeaderFooter->setScaleWithDocument(FALSE);
1173  } else {
1174  $docHeaderFooter->setScaleWithDocument(TRUE);
1175  }
1176  if (isset($xmlSheet->headerFooter["alignWithMargins"]) &&
1177  !self::boolean((string)$xmlSheet->headerFooter["alignWithMargins"])) {
1178  $docHeaderFooter->setAlignWithMargins(FALSE);
1179  } else {
1180  $docHeaderFooter->setAlignWithMargins(TRUE);
1181  }
1182 
1183  $docHeaderFooter->setOddHeader((string) $xmlSheet->headerFooter->oddHeader);
1184  $docHeaderFooter->setOddFooter((string) $xmlSheet->headerFooter->oddFooter);
1185  $docHeaderFooter->setEvenHeader((string) $xmlSheet->headerFooter->evenHeader);
1186  $docHeaderFooter->setEvenFooter((string) $xmlSheet->headerFooter->evenFooter);
1187  $docHeaderFooter->setFirstHeader((string) $xmlSheet->headerFooter->firstHeader);
1188  $docHeaderFooter->setFirstFooter((string) $xmlSheet->headerFooter->firstFooter);
1189  }
1190 
1191  if ($xmlSheet && $xmlSheet->rowBreaks && $xmlSheet->rowBreaks->brk && !$this->_readDataOnly) {
1192  foreach ($xmlSheet->rowBreaks->brk as $brk) {
1193  if ($brk["man"]) {
1194  $docSheet->setBreak("A$brk[id]", PHPExcel_Worksheet::BREAK_ROW);
1195  }
1196  }
1197  }
1198  if ($xmlSheet && $xmlSheet->colBreaks && $xmlSheet->colBreaks->brk && !$this->_readDataOnly) {
1199  foreach ($xmlSheet->colBreaks->brk as $brk) {
1200  if ($brk["man"]) {
1201  $docSheet->setBreak(PHPExcel_Cell::stringFromColumnIndex((string) $brk["id"]) . "1", PHPExcel_Worksheet::BREAK_COLUMN);
1202  }
1203  }
1204  }
1205 
1206  if ($xmlSheet && $xmlSheet->dataValidations && !$this->_readDataOnly) {
1207  foreach ($xmlSheet->dataValidations->dataValidation as $dataValidation) {
1208  // Uppercase coordinate
1209  $range = strtoupper($dataValidation["sqref"]);
1210  $rangeSet = explode(' ',$range);
1211  foreach($rangeSet as $range) {
1212  $stRange = $docSheet->shrinkRangeToFit($range);
1213 
1214  // Extract all cell references in $range
1215  $aReferences = PHPExcel_Cell::extractAllCellReferencesInRange($stRange);
1216  foreach ($aReferences as $reference) {
1217  // Create validation
1218  $docValidation = $docSheet->getCell($reference)->getDataValidation();
1219  $docValidation->setType((string) $dataValidation["type"]);
1220  $docValidation->setErrorStyle((string) $dataValidation["errorStyle"]);
1221  $docValidation->setOperator((string) $dataValidation["operator"]);
1222  $docValidation->setAllowBlank($dataValidation["allowBlank"] != 0);
1223  $docValidation->setShowDropDown($dataValidation["showDropDown"] == 0);
1224  $docValidation->setShowInputMessage($dataValidation["showInputMessage"] != 0);
1225  $docValidation->setShowErrorMessage($dataValidation["showErrorMessage"] != 0);
1226  $docValidation->setErrorTitle((string) $dataValidation["errorTitle"]);
1227  $docValidation->setError((string) $dataValidation["error"]);
1228  $docValidation->setPromptTitle((string) $dataValidation["promptTitle"]);
1229  $docValidation->setPrompt((string) $dataValidation["prompt"]);
1230  $docValidation->setFormula1((string) $dataValidation->formula1);
1231  $docValidation->setFormula2((string) $dataValidation->formula2);
1232  }
1233  }
1234  }
1235  }
1236 
1237  // Add hyperlinks
1238  $hyperlinks = array();
1239  if (!$this->_readDataOnly) {
1240  // Locate hyperlink relations
1241  if ($zip->locateName(dirname("$dir/$fileWorksheet") . "/_rels/" . basename($fileWorksheet) . ".rels")) {
1242  $relsWorksheet = simplexml_load_string($this->securityScan($this->_getFromZipArchive($zip, dirname("$dir/$fileWorksheet") . "/_rels/" . basename($fileWorksheet) . ".rels")), 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions()); //~ http://schemas.openxmlformats.org/package/2006/relationships");
1243  foreach ($relsWorksheet->Relationship as $ele) {
1244  if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/hyperlink") {
1245  $hyperlinks[(string)$ele["Id"]] = (string)$ele["Target"];
1246  }
1247  }
1248  }
1249 
1250  // Loop through hyperlinks
1251  if ($xmlSheet && $xmlSheet->hyperlinks) {
1252  foreach ($xmlSheet->hyperlinks->hyperlink as $hyperlink) {
1253  // Link url
1254  $linkRel = $hyperlink->attributes('http://schemas.openxmlformats.org/officeDocument/2006/relationships');
1255 
1256  foreach (PHPExcel_Cell::extractAllCellReferencesInRange($hyperlink['ref']) as $cellReference) {
1257  $cell = $docSheet->getCell( $cellReference );
1258  if (isset($linkRel['id'])) {
1259  $hyperlinkUrl = $hyperlinks[ (string)$linkRel['id'] ];
1260  if (isset($hyperlink['location'])) {
1261  $hyperlinkUrl .= '#' . (string) $hyperlink['location'];
1262  }
1263  $cell->getHyperlink()->setUrl($hyperlinkUrl);
1264  } elseif (isset($hyperlink['location'])) {
1265  $cell->getHyperlink()->setUrl( 'sheet://' . (string)$hyperlink['location'] );
1266  }
1267 
1268  // Tooltip
1269  if (isset($hyperlink['tooltip'])) {
1270  $cell->getHyperlink()->setTooltip( (string)$hyperlink['tooltip'] );
1271  }
1272  }
1273  }
1274  }
1275  }
1276 
1277  // Add comments
1278  $comments = array();
1279  $vmlComments = array();
1280  if (!$this->_readDataOnly) {
1281  // Locate comment relations
1282  if ($zip->locateName(dirname("$dir/$fileWorksheet") . "/_rels/" . basename($fileWorksheet) . ".rels")) {
1283  $relsWorksheet = simplexml_load_string($this->securityScan($this->_getFromZipArchive($zip, dirname("$dir/$fileWorksheet") . "/_rels/" . basename($fileWorksheet) . ".rels")), 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions()); //~ http://schemas.openxmlformats.org/package/2006/relationships");
1284  foreach ($relsWorksheet->Relationship as $ele) {
1285  if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/comments") {
1286  $comments[(string)$ele["Id"]] = (string)$ele["Target"];
1287  }
1288  if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/vmlDrawing") {
1289  $vmlComments[(string)$ele["Id"]] = (string)$ele["Target"];
1290  }
1291  }
1292  }
1293 
1294  // Loop through comments
1295  foreach ($comments as $relName => $relPath) {
1296  // Load comments file
1297  $relPath = PHPExcel_Shared_File::realpath(dirname("$dir/$fileWorksheet") . "/" . $relPath);
1298  $commentsFile = simplexml_load_string($this->securityScan($this->_getFromZipArchive($zip, $relPath)), 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions());
1299 
1300  // Utility variables
1301  $authors = array();
1302 
1303  // Loop through authors
1304  foreach ($commentsFile->authors->author as $author) {
1305  $authors[] = (string)$author;
1306  }
1307 
1308  // Loop through contents
1309  foreach ($commentsFile->commentList->comment as $comment) {
1310  if(!empty($comment['authorId']))
1311  $docSheet->getComment( (string)$comment['ref'] )->setAuthor( $authors[(string)$comment['authorId']] );
1312  $docSheet->getComment( (string)$comment['ref'] )->setText( $this->_parseRichText($comment->text) );
1313  }
1314  }
1315 
1316  // Loop through VML comments
1317  foreach ($vmlComments as $relName => $relPath) {
1318  // Load VML comments file
1319  $relPath = PHPExcel_Shared_File::realpath(dirname("$dir/$fileWorksheet") . "/" . $relPath);
1320  $vmlCommentsFile = simplexml_load_string($this->securityScan($this->_getFromZipArchive($zip, $relPath)), 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions());
1321  $vmlCommentsFile->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml');
1322 
1323  $shapes = $vmlCommentsFile->xpath('//v:shape');
1324  foreach ($shapes as $shape) {
1325  $shape->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml');
1326 
1327  if (isset($shape['style'])) {
1328  $style = (string)$shape['style'];
1329  $fillColor = strtoupper( substr( (string)$shape['fillcolor'], 1 ) );
1330  $column = null;
1331  $row = null;
1332 
1333  $clientData = $shape->xpath('.//x:ClientData');
1334  if (is_array($clientData) && !empty($clientData)) {
1335  $clientData = $clientData[0];
1336 
1337  if ( isset($clientData['ObjectType']) && (string)$clientData['ObjectType'] == 'Note' ) {
1338  $temp = $clientData->xpath('.//x:Row');
1339  if (is_array($temp)) $row = $temp[0];
1340 
1341  $temp = $clientData->xpath('.//x:Column');
1342  if (is_array($temp)) $column = $temp[0];
1343  }
1344  }
1345 
1346  if (($column !== NULL) && ($row !== NULL)) {
1347  // Set comment properties
1348  $comment = $docSheet->getCommentByColumnAndRow((string) $column, $row + 1);
1349  $comment->getFillColor()->setRGB( $fillColor );
1350 
1351  // Parse style
1352  $styleArray = explode(';', str_replace(' ', '', $style));
1353  foreach ($styleArray as $stylePair) {
1354  $stylePair = explode(':', $stylePair);
1355 
1356  if ($stylePair[0] == 'margin-left') $comment->setMarginLeft($stylePair[1]);
1357  if ($stylePair[0] == 'margin-top') $comment->setMarginTop($stylePair[1]);
1358  if ($stylePair[0] == 'width') $comment->setWidth($stylePair[1]);
1359  if ($stylePair[0] == 'height') $comment->setHeight($stylePair[1]);
1360  if ($stylePair[0] == 'visibility') $comment->setVisible( $stylePair[1] == 'visible' );
1361 
1362  }
1363  }
1364  }
1365  }
1366  }
1367 
1368  // Header/footer images
1369  if ($xmlSheet && $xmlSheet->legacyDrawingHF && !$this->_readDataOnly) {
1370  if ($zip->locateName(dirname("$dir/$fileWorksheet") . "/_rels/" . basename($fileWorksheet) . ".rels")) {
1371  $relsWorksheet = simplexml_load_string($this->securityScan($this->_getFromZipArchive($zip, dirname("$dir/$fileWorksheet") . "/_rels/" . basename($fileWorksheet) . ".rels")), 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions()); //~ http://schemas.openxmlformats.org/package/2006/relationships");
1372  $vmlRelationship = '';
1373 
1374  foreach ($relsWorksheet->Relationship as $ele) {
1375  if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/vmlDrawing") {
1376  $vmlRelationship = self::dir_add("$dir/$fileWorksheet", $ele["Target"]);
1377  }
1378  }
1379 
1380  if ($vmlRelationship != '') {
1381  // Fetch linked images
1382  $relsVML = simplexml_load_string($this->securityScan($this->_getFromZipArchive($zip, dirname($vmlRelationship) . '/_rels/' . basename($vmlRelationship) . '.rels')), 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions()); //~ http://schemas.openxmlformats.org/package/2006/relationships");
1383  $drawings = array();
1384  foreach ($relsVML->Relationship as $ele) {
1385  if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/image") {
1386  $drawings[(string) $ele["Id"]] = self::dir_add($vmlRelationship, $ele["Target"]);
1387  }
1388  }
1389 
1390  // Fetch VML document
1391  $vmlDrawing = simplexml_load_string($this->securityScan($this->_getFromZipArchive($zip, $vmlRelationship)), 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions());
1392  $vmlDrawing->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml');
1393 
1394  $hfImages = array();
1395 
1396  $shapes = $vmlDrawing->xpath('//v:shape');
1397  foreach ($shapes as $idx => $shape) {
1398  $shape->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml');
1399  $imageData = $shape->xpath('//v:imagedata');
1400  $imageData = $imageData[$idx];
1401 
1402  $imageData = $imageData->attributes('urn:schemas-microsoft-com:office:office');
1403  $style = self::toCSSArray( (string)$shape['style'] );
1404 
1405  $hfImages[ (string)$shape['id'] ] = new PHPExcel_Worksheet_HeaderFooterDrawing();
1406  if (isset($imageData['title'])) {
1407  $hfImages[ (string)$shape['id'] ]->setName( (string)$imageData['title'] );
1408  }
1409 
1410  $hfImages[ (string)$shape['id'] ]->setPath("zip://".PHPExcel_Shared_File::realpath($pFilename)."#" . $drawings[(string)$imageData['relid']], false);
1411  $hfImages[ (string)$shape['id'] ]->setResizeProportional(false);
1412  $hfImages[ (string)$shape['id'] ]->setWidth($style['width']);
1413  $hfImages[ (string)$shape['id'] ]->setHeight($style['height']);
1414  if (isset($style['margin-left'])) {
1415  $hfImages[ (string)$shape['id'] ]->setOffsetX($style['margin-left']);
1416  }
1417  $hfImages[ (string)$shape['id'] ]->setOffsetY($style['margin-top']);
1418  $hfImages[ (string)$shape['id'] ]->setResizeProportional(true);
1419  }
1420 
1421  $docSheet->getHeaderFooter()->setImages($hfImages);
1422  }
1423  }
1424  }
1425 
1426  }
1427 
1428  // TODO: Autoshapes from twoCellAnchors!
1429  if ($zip->locateName(dirname("$dir/$fileWorksheet") . "/_rels/" . basename($fileWorksheet) . ".rels")) {
1430  $relsWorksheet = simplexml_load_string($this->securityScan($this->_getFromZipArchive($zip, dirname("$dir/$fileWorksheet") . "/_rels/" . basename($fileWorksheet) . ".rels")), 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions()); //~ http://schemas.openxmlformats.org/package/2006/relationships");
1431  $drawings = array();
1432  foreach ($relsWorksheet->Relationship as $ele) {
1433  if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/drawing") {
1434  $drawings[(string) $ele["Id"]] = self::dir_add("$dir/$fileWorksheet", $ele["Target"]);
1435  }
1436  }
1437  if ($xmlSheet->drawing && !$this->_readDataOnly) {
1438  foreach ($xmlSheet->drawing as $drawing) {
1439  $fileDrawing = $drawings[(string) self::array_item($drawing->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "id")];
1440  $relsDrawing = simplexml_load_string($this->securityScan($this->_getFromZipArchive($zip, dirname($fileDrawing) . "/_rels/" . basename($fileDrawing) . ".rels")), 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions()); //~ http://schemas.openxmlformats.org/package/2006/relationships");
1441  $images = array();
1442 
1443  if ($relsDrawing && $relsDrawing->Relationship) {
1444  foreach ($relsDrawing->Relationship as $ele) {
1445  if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/image") {
1446  $images[(string) $ele["Id"]] = self::dir_add($fileDrawing, $ele["Target"]);
1447  } elseif ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/chart") {
1448  if ($this->_includeCharts) {
1449  $charts[self::dir_add($fileDrawing, $ele["Target"])] = array('id' => (string) $ele["Id"],
1450  'sheet' => $docSheet->getTitle()
1451  );
1452  }
1453  }
1454  }
1455  }
1456  $xmlDrawing = simplexml_load_string($this->securityScan($this->_getFromZipArchive($zip, $fileDrawing)), 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions())->children("http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing");
1457 
1458  if ($xmlDrawing->oneCellAnchor) {
1459  foreach ($xmlDrawing->oneCellAnchor as $oneCellAnchor) {
1460  if ($oneCellAnchor->pic->blipFill) {
1461  $blip = $oneCellAnchor->pic->blipFill->children("http://schemas.openxmlformats.org/drawingml/2006/main")->blip;
1462  $xfrm = $oneCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->xfrm;
1463  $outerShdw = $oneCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->effectLst->outerShdw;
1464  $objDrawing = new PHPExcel_Worksheet_Drawing;
1465  $objDrawing->setName((string) self::array_item($oneCellAnchor->pic->nvPicPr->cNvPr->attributes(), "name"));
1466  $objDrawing->setDescription((string) self::array_item($oneCellAnchor->pic->nvPicPr->cNvPr->attributes(), "descr"));
1467  $objDrawing->setPath("zip://".PHPExcel_Shared_File::realpath($pFilename)."#" . $images[(string) self::array_item($blip->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "embed")], false);
1468  $objDrawing->setCoordinates(PHPExcel_Cell::stringFromColumnIndex((string) $oneCellAnchor->from->col) . ($oneCellAnchor->from->row + 1));
1469  $objDrawing->setOffsetX(PHPExcel_Shared_Drawing::EMUToPixels($oneCellAnchor->from->colOff));
1470  $objDrawing->setOffsetY(PHPExcel_Shared_Drawing::EMUToPixels($oneCellAnchor->from->rowOff));
1471  $objDrawing->setResizeProportional(false);
1472  $objDrawing->setWidth(PHPExcel_Shared_Drawing::EMUToPixels(self::array_item($oneCellAnchor->ext->attributes(), "cx")));
1473  $objDrawing->setHeight(PHPExcel_Shared_Drawing::EMUToPixels(self::array_item($oneCellAnchor->ext->attributes(), "cy")));
1474  if ($xfrm) {
1475  $objDrawing->setRotation(PHPExcel_Shared_Drawing::angleToDegrees(self::array_item($xfrm->attributes(), "rot")));
1476  }
1477  if ($outerShdw) {
1478  $shadow = $objDrawing->getShadow();
1479  $shadow->setVisible(true);
1480  $shadow->setBlurRadius(PHPExcel_Shared_Drawing::EMUTopixels(self::array_item($outerShdw->attributes(), "blurRad")));
1481  $shadow->setDistance(PHPExcel_Shared_Drawing::EMUTopixels(self::array_item($outerShdw->attributes(), "dist")));
1482  $shadow->setDirection(PHPExcel_Shared_Drawing::angleToDegrees(self::array_item($outerShdw->attributes(), "dir")));
1483  $shadow->setAlignment((string) self::array_item($outerShdw->attributes(), "algn"));
1484  $shadow->getColor()->setRGB(self::array_item($outerShdw->srgbClr->attributes(), "val"));
1485  $shadow->setAlpha(self::array_item($outerShdw->srgbClr->alpha->attributes(), "val") / 1000);
1486  }
1487  $objDrawing->setWorksheet($docSheet);
1488  } else {
1489  // ? Can charts be positioned with a oneCellAnchor ?
1490  $coordinates = PHPExcel_Cell::stringFromColumnIndex((string) $oneCellAnchor->from->col) . ($oneCellAnchor->from->row + 1);
1491  $offsetX = PHPExcel_Shared_Drawing::EMUToPixels($oneCellAnchor->from->colOff);
1492  $offsetY = PHPExcel_Shared_Drawing::EMUToPixels($oneCellAnchor->from->rowOff);
1493  $width = PHPExcel_Shared_Drawing::EMUToPixels(self::array_item($oneCellAnchor->ext->attributes(), "cx"));
1494  $height = PHPExcel_Shared_Drawing::EMUToPixels(self::array_item($oneCellAnchor->ext->attributes(), "cy"));
1495  }
1496  }
1497  }
1498  if ($xmlDrawing->twoCellAnchor) {
1499  foreach ($xmlDrawing->twoCellAnchor as $twoCellAnchor) {
1500  if ($twoCellAnchor->pic->blipFill) {
1501  $blip = $twoCellAnchor->pic->blipFill->children("http://schemas.openxmlformats.org/drawingml/2006/main")->blip;
1502  $xfrm = $twoCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->xfrm;
1503  $outerShdw = $twoCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->effectLst->outerShdw;
1504  $objDrawing = new PHPExcel_Worksheet_Drawing;
1505  $objDrawing->setName((string) self::array_item($twoCellAnchor->pic->nvPicPr->cNvPr->attributes(), "name"));
1506  $objDrawing->setDescription((string) self::array_item($twoCellAnchor->pic->nvPicPr->cNvPr->attributes(), "descr"));
1507  $objDrawing->setPath("zip://".PHPExcel_Shared_File::realpath($pFilename)."#" . $images[(string) self::array_item($blip->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "embed")], false);
1508  $objDrawing->setCoordinates(PHPExcel_Cell::stringFromColumnIndex((string) $twoCellAnchor->from->col) . ($twoCellAnchor->from->row + 1));
1509  $objDrawing->setOffsetX(PHPExcel_Shared_Drawing::EMUToPixels($twoCellAnchor->from->colOff));
1510  $objDrawing->setOffsetY(PHPExcel_Shared_Drawing::EMUToPixels($twoCellAnchor->from->rowOff));
1511  $objDrawing->setResizeProportional(false);
1512 
1513  if ($xfrm) {
1514  $objDrawing->setWidth(PHPExcel_Shared_Drawing::EMUToPixels(self::array_item($xfrm->ext->attributes(), "cx")));
1515  $objDrawing->setHeight(PHPExcel_Shared_Drawing::EMUToPixels(self::array_item($xfrm->ext->attributes(), "cy")));
1516  $objDrawing->setRotation(PHPExcel_Shared_Drawing::angleToDegrees(self::array_item($xfrm->attributes(), "rot")));
1517  }
1518  if ($outerShdw) {
1519  $shadow = $objDrawing->getShadow();
1520  $shadow->setVisible(true);
1521  $shadow->setBlurRadius(PHPExcel_Shared_Drawing::EMUTopixels(self::array_item($outerShdw->attributes(), "blurRad")));
1522  $shadow->setDistance(PHPExcel_Shared_Drawing::EMUTopixels(self::array_item($outerShdw->attributes(), "dist")));
1523  $shadow->setDirection(PHPExcel_Shared_Drawing::angleToDegrees(self::array_item($outerShdw->attributes(), "dir")));
1524  $shadow->setAlignment((string) self::array_item($outerShdw->attributes(), "algn"));
1525  $shadow->getColor()->setRGB(self::array_item($outerShdw->srgbClr->attributes(), "val"));
1526  $shadow->setAlpha(self::array_item($outerShdw->srgbClr->alpha->attributes(), "val") / 1000);
1527  }
1528  $objDrawing->setWorksheet($docSheet);
1529  } elseif(($this->_includeCharts) && ($twoCellAnchor->graphicFrame)) {
1530  $fromCoordinate = PHPExcel_Cell::stringFromColumnIndex((string) $twoCellAnchor->from->col) . ($twoCellAnchor->from->row + 1);
1531  $fromOffsetX = PHPExcel_Shared_Drawing::EMUToPixels($twoCellAnchor->from->colOff);
1532  $fromOffsetY = PHPExcel_Shared_Drawing::EMUToPixels($twoCellAnchor->from->rowOff);
1533  $toCoordinate = PHPExcel_Cell::stringFromColumnIndex((string) $twoCellAnchor->to->col) . ($twoCellAnchor->to->row + 1);
1534  $toOffsetX = PHPExcel_Shared_Drawing::EMUToPixels($twoCellAnchor->to->colOff);
1535  $toOffsetY = PHPExcel_Shared_Drawing::EMUToPixels($twoCellAnchor->to->rowOff);
1536  $graphic = $twoCellAnchor->graphicFrame->children("http://schemas.openxmlformats.org/drawingml/2006/main")->graphic;
1537  $chartRef = $graphic->graphicData->children("http://schemas.openxmlformats.org/drawingml/2006/chart")->chart;
1538  $thisChart = (string) $chartRef->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships");
1539 
1540  $chartDetails[$docSheet->getTitle().'!'.$thisChart] =
1541  array( 'fromCoordinate' => $fromCoordinate,
1542  'fromOffsetX' => $fromOffsetX,
1543  'fromOffsetY' => $fromOffsetY,
1544  'toCoordinate' => $toCoordinate,
1545  'toOffsetX' => $toOffsetX,
1546  'toOffsetY' => $toOffsetY,
1547  'worksheetTitle' => $docSheet->getTitle()
1548  );
1549  }
1550  }
1551  }
1552 
1553  }
1554  }
1555  }
1556 
1557  // Loop through definedNames
1558  if ($xmlWorkbook->definedNames) {
1559  foreach ($xmlWorkbook->definedNames->definedName as $definedName) {
1560  // Extract range
1561  $extractedRange = (string)$definedName;
1562  $extractedRange = preg_replace('/\'(\w+)\'\!/', '', $extractedRange);
1563  if (($spos = strpos($extractedRange,'!')) !== false) {
1564  $extractedRange = substr($extractedRange,0,$spos).str_replace('$', '', substr($extractedRange,$spos));
1565  } else {
1566  $extractedRange = str_replace('$', '', $extractedRange);
1567  }
1568 
1569  // Valid range?
1570  if (stripos((string)$definedName, '#REF!') !== FALSE || $extractedRange == '') {
1571  continue;
1572  }
1573 
1574  // Some definedNames are only applicable if we are on the same sheet...
1575  if ((string)$definedName['localSheetId'] != '' && (string)$definedName['localSheetId'] == $sheetId) {
1576  // Switch on type
1577  switch ((string)$definedName['name']) {
1578 
1579  case '_xlnm._FilterDatabase':
1580  if ((string)$definedName['hidden'] !== '1') {
1581  $extractedRange = explode(',', $extractedRange);
1582  foreach ($extractedRange as $range) {
1583  $autoFilterRange = $range;
1584  if (strpos($autoFilterRange, ':') !== false) {
1585  $docSheet->getAutoFilter()->setRange($autoFilterRange);
1586  }
1587  }
1588  }
1589  break;
1590 
1591  case '_xlnm.Print_Titles':
1592  // Split $extractedRange
1593  $extractedRange = explode(',', $extractedRange);
1594 
1595  // Set print titles
1596  foreach ($extractedRange as $range) {
1597  $matches = array();
1598  $range = str_replace('$', '', $range);
1599 
1600  // check for repeating columns, e g. 'A:A' or 'A:D'
1601  if (preg_match('/!?([A-Z]+)\:([A-Z]+)$/', $range, $matches)) {
1602  $docSheet->getPageSetup()->setColumnsToRepeatAtLeft(array($matches[1], $matches[2]));
1603  }
1604  // check for repeating rows, e.g. '1:1' or '1:5'
1605  elseif (preg_match('/!?(\d+)\:(\d+)$/', $range, $matches)) {
1606  $docSheet->getPageSetup()->setRowsToRepeatAtTop(array($matches[1], $matches[2]));
1607  }
1608  }
1609  break;
1610 
1611  case '_xlnm.Print_Area':
1612  $rangeSets = explode(',', $extractedRange); // FIXME: what if sheetname contains comma?
1613  $newRangeSets = array();
1614  foreach($rangeSets as $rangeSet) {
1615  $range = explode('!', $rangeSet); // FIXME: what if sheetname contains exclamation mark?
1616  $rangeSet = isset($range[1]) ? $range[1] : $range[0];
1617  if (strpos($rangeSet, ':') === FALSE) {
1618  $rangeSet = $rangeSet . ':' . $rangeSet;
1619  }
1620  $newRangeSets[] = str_replace('$', '', $rangeSet);
1621  }
1622  $docSheet->getPageSetup()->setPrintArea(implode(',',$newRangeSets));
1623  break;
1624 
1625  default:
1626  break;
1627  }
1628  }
1629  }
1630  }
1631 
1632  // Next sheet id
1633  ++$sheetId;
1634  }
1635 
1636  // Loop through definedNames
1637  if ($xmlWorkbook->definedNames) {
1638  foreach ($xmlWorkbook->definedNames->definedName as $definedName) {
1639  // Extract range
1640  $extractedRange = (string)$definedName;
1641  $extractedRange = preg_replace('/\'(\w+)\'\!/', '', $extractedRange);
1642  if (($spos = strpos($extractedRange,'!')) !== false) {
1643  $extractedRange = substr($extractedRange,0,$spos).str_replace('$', '', substr($extractedRange,$spos));
1644  } else {
1645  $extractedRange = str_replace('$', '', $extractedRange);
1646  }
1647 
1648  // Valid range?
1649  if (stripos((string)$definedName, '#REF!') !== false || $extractedRange == '') {
1650  continue;
1651  }
1652 
1653  // Some definedNames are only applicable if we are on the same sheet...
1654  if ((string)$definedName['localSheetId'] != '') {
1655  // Local defined name
1656  // Switch on type
1657  switch ((string)$definedName['name']) {
1658 
1659  case '_xlnm._FilterDatabase':
1660  case '_xlnm.Print_Titles':
1661  case '_xlnm.Print_Area':
1662  break;
1663 
1664  default:
1665  if ($mapSheetId[(integer) $definedName['localSheetId']] !== null) {
1666  $range = explode('!', (string)$definedName);
1667  if (count($range) == 2) {
1668  $range[0] = str_replace("''", "'", $range[0]);
1669  $range[0] = str_replace("'", "", $range[0]);
1670  if ($worksheet = $docSheet->getParent()->getSheetByName($range[0])) {
1671  $extractedRange = str_replace('$', '', $range[1]);
1672  $scope = $docSheet->getParent()->getSheet($mapSheetId[(integer) $definedName['localSheetId']]);
1673  $excel->addNamedRange( new PHPExcel_NamedRange((string)$definedName['name'], $worksheet, $extractedRange, true, $scope) );
1674  }
1675  }
1676  }
1677  break;
1678  }
1679  } else if (!isset($definedName['localSheetId'])) {
1680  // "Global" definedNames
1681  $locatedSheet = null;
1682  $extractedSheetName = '';
1683  if (strpos( (string)$definedName, '!' ) !== false) {
1684  // Extract sheet name
1685  $extractedSheetName = PHPExcel_Worksheet::extractSheetTitle( (string)$definedName, true );
1686  $extractedSheetName = $extractedSheetName[0];
1687 
1688  // Locate sheet
1689  $locatedSheet = $excel->getSheetByName($extractedSheetName);
1690 
1691  // Modify range
1692  $range = explode('!', $extractedRange);
1693  $extractedRange = isset($range[1]) ? $range[1] : $range[0];
1694  }
1695 
1696  if ($locatedSheet !== NULL) {
1697  $excel->addNamedRange( new PHPExcel_NamedRange((string)$definedName['name'], $locatedSheet, $extractedRange, false) );
1698  }
1699  }
1700  }
1701  }
1702  }
1703 
1704  if ((!$this->_readDataOnly) || (!empty($this->_loadSheetsOnly))) {
1705  // active sheet index
1706  $activeTab = intval($xmlWorkbook->bookViews->workbookView["activeTab"]); // refers to old sheet index
1707 
1708  // keep active sheet index if sheet is still loaded, else first sheet is set as the active
1709  if (isset($mapSheetId[$activeTab]) && $mapSheetId[$activeTab] !== null) {
1710  $excel->setActiveSheetIndex($mapSheetId[$activeTab]);
1711  } else {
1712  if ($excel->getSheetCount() == 0) {
1713  $excel->createSheet();
1714  }
1715  $excel->setActiveSheetIndex(0);
1716  }
1717  }
1718  break;
1719  }
1720 
1721  }
1722 
1723 
1724  if (!$this->_readDataOnly) {
1725  $contentTypes = simplexml_load_string($this->securityScan($this->_getFromZipArchive($zip, "[Content_Types].xml")), 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions());
1726  foreach ($contentTypes->Override as $contentType) {
1727  switch ($contentType["ContentType"]) {
1728  case "application/vnd.openxmlformats-officedocument.drawingml.chart+xml":
1729  if ($this->_includeCharts) {
1730  $chartEntryRef = ltrim($contentType['PartName'],'/');
1731  $chartElements = simplexml_load_string($this->securityScan($this->_getFromZipArchive($zip, $chartEntryRef)), 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions());
1732  $objChart = PHPExcel_Reader_Excel2007_Chart::readChart($chartElements,basename($chartEntryRef,'.xml'));
1733 
1734 // echo 'Chart ',$chartEntryRef,'<br />';
1735 // var_dump($charts[$chartEntryRef]);
1736 //
1737  if (isset($charts[$chartEntryRef])) {
1738  $chartPositionRef = $charts[$chartEntryRef]['sheet'].'!'.$charts[$chartEntryRef]['id'];
1739 // echo 'Position Ref ',$chartPositionRef,'<br />';
1740  if (isset($chartDetails[$chartPositionRef])) {
1741 // var_dump($chartDetails[$chartPositionRef]);
1742 
1743  $excel->getSheetByName($charts[$chartEntryRef]['sheet'])->addChart($objChart);
1744  $objChart->setWorksheet($excel->getSheetByName($charts[$chartEntryRef]['sheet']));
1745  $objChart->setTopLeftPosition( $chartDetails[$chartPositionRef]['fromCoordinate'],
1746  $chartDetails[$chartPositionRef]['fromOffsetX'],
1747  $chartDetails[$chartPositionRef]['fromOffsetY']
1748  );
1749  $objChart->setBottomRightPosition( $chartDetails[$chartPositionRef]['toCoordinate'],
1750  $chartDetails[$chartPositionRef]['toOffsetX'],
1751  $chartDetails[$chartPositionRef]['toOffsetY']
1752  );
1753  }
1754  }
1755  }
1756  }
1757  }
1758  }
1759 
1760  $zip->close();
1761 
1762  return $excel;
1763  }
1764 
1765 
1766  private static function _readColor($color, $background=FALSE) {
1767  if (isset($color["rgb"])) {
1768  return (string)$color["rgb"];
1769  } else if (isset($color["indexed"])) {
1770  return PHPExcel_Style_Color::indexedColor($color["indexed"]-7,$background)->getARGB();
1771  } else if (isset($color["theme"])) {
1772  if (self::$_theme !== NULL) {
1773  $returnColour = self::$_theme->getColourByIndex((int)$color["theme"]);
1774  if (isset($color["tint"])) {
1775  $tintAdjust = (float) $color["tint"];
1776  $returnColour = PHPExcel_Style_Color::changeBrightness($returnColour, $tintAdjust);
1777  }
1778  return 'FF'.$returnColour;
1779  }
1780  }
1781 
1782  if ($background) {
1783  return 'FFFFFFFF';
1784  }
1785  return 'FF000000';
1786  }
1787 
1788 
1789  private static function _readStyle($docStyle, $style) {
1790  // format code
1791 // if (isset($style->numFmt)) {
1792 // if (isset($style->numFmt['formatCode'])) {
1793 // $docStyle->getNumberFormat()->setFormatCode((string) $style->numFmt['formatCode']);
1794 // } else {
1795  $docStyle->getNumberFormat()->setFormatCode($style->numFmt);
1796 // }
1797 // }
1798 
1799  // font
1800  if (isset($style->font)) {
1801  $docStyle->getFont()->setName((string) $style->font->name["val"]);
1802  $docStyle->getFont()->setSize((string) $style->font->sz["val"]);
1803  if (isset($style->font->b)) {
1804  $docStyle->getFont()->setBold(!isset($style->font->b["val"]) || self::boolean((string) $style->font->b["val"]));
1805  }
1806  if (isset($style->font->i)) {
1807  $docStyle->getFont()->setItalic(!isset($style->font->i["val"]) || self::boolean((string) $style->font->i["val"]));
1808  }
1809  if (isset($style->font->strike)) {
1810  $docStyle->getFont()->setStrikethrough(!isset($style->font->strike["val"]) || self::boolean((string) $style->font->strike["val"]));
1811  }
1812  $docStyle->getFont()->getColor()->setARGB(self::_readColor($style->font->color));
1813 
1814  if (isset($style->font->u) && !isset($style->font->u["val"])) {
1815  $docStyle->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
1816  } else if (isset($style->font->u) && isset($style->font->u["val"])) {
1817  $docStyle->getFont()->setUnderline((string)$style->font->u["val"]);
1818  }
1819 
1820  if (isset($style->font->vertAlign) && isset($style->font->vertAlign["val"])) {
1821  $vertAlign = strtolower((string)$style->font->vertAlign["val"]);
1822  if ($vertAlign == 'superscript') {
1823  $docStyle->getFont()->setSuperScript(true);
1824  }
1825  if ($vertAlign == 'subscript') {
1826  $docStyle->getFont()->setSubScript(true);
1827  }
1828  }
1829  }
1830 
1831  // fill
1832  if (isset($style->fill)) {
1833  if ($style->fill->gradientFill) {
1834  $gradientFill = $style->fill->gradientFill[0];
1835  if(!empty($gradientFill["type"])) {
1836  $docStyle->getFill()->setFillType((string) $gradientFill["type"]);
1837  }
1838  $docStyle->getFill()->setRotation(floatval($gradientFill["degree"]));
1839  $gradientFill->registerXPathNamespace("sml", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
1840  $docStyle->getFill()->getStartColor()->setARGB(self::_readColor( self::array_item($gradientFill->xpath("sml:stop[@position=0]"))->color) );
1841  $docStyle->getFill()->getEndColor()->setARGB(self::_readColor( self::array_item($gradientFill->xpath("sml:stop[@position=1]"))->color) );
1842  } elseif ($style->fill->patternFill) {
1843  $patternType = (string)$style->fill->patternFill["patternType"] != '' ? (string)$style->fill->patternFill["patternType"] : 'solid';
1844  $docStyle->getFill()->setFillType($patternType);
1845  if ($style->fill->patternFill->fgColor) {
1846  $docStyle->getFill()->getStartColor()->setARGB(self::_readColor($style->fill->patternFill->fgColor,true));
1847  } else {
1848  $docStyle->getFill()->getStartColor()->setARGB('FF000000');
1849  }
1850  if ($style->fill->patternFill->bgColor) {
1851  $docStyle->getFill()->getEndColor()->setARGB(self::_readColor($style->fill->patternFill->bgColor,true));
1852  }
1853  }
1854  }
1855 
1856  // border
1857  if (isset($style->border)) {
1858  $diagonalUp = self::boolean((string) $style->border["diagonalUp"]);
1859  $diagonalDown = self::boolean((string) $style->border["diagonalDown"]);
1860  if (!$diagonalUp && !$diagonalDown) {
1861  $docStyle->getBorders()->setDiagonalDirection(PHPExcel_Style_Borders::DIAGONAL_NONE);
1862  } elseif ($diagonalUp && !$diagonalDown) {
1863  $docStyle->getBorders()->setDiagonalDirection(PHPExcel_Style_Borders::DIAGONAL_UP);
1864  } elseif (!$diagonalUp && $diagonalDown) {
1865  $docStyle->getBorders()->setDiagonalDirection(PHPExcel_Style_Borders::DIAGONAL_DOWN);
1866  } else {
1867  $docStyle->getBorders()->setDiagonalDirection(PHPExcel_Style_Borders::DIAGONAL_BOTH);
1868  }
1869  self::_readBorder($docStyle->getBorders()->getLeft(), $style->border->left);
1870  self::_readBorder($docStyle->getBorders()->getRight(), $style->border->right);
1871  self::_readBorder($docStyle->getBorders()->getTop(), $style->border->top);
1872  self::_readBorder($docStyle->getBorders()->getBottom(), $style->border->bottom);
1873  self::_readBorder($docStyle->getBorders()->getDiagonal(), $style->border->diagonal);
1874  }
1875 
1876  // alignment
1877  if (isset($style->alignment)) {
1878  $docStyle->getAlignment()->setHorizontal((string) $style->alignment["horizontal"]);
1879  $docStyle->getAlignment()->setVertical((string) $style->alignment["vertical"]);
1880 
1881  $textRotation = 0;
1882  if ((int)$style->alignment["textRotation"] <= 90) {
1883  $textRotation = (int)$style->alignment["textRotation"];
1884  } else if ((int)$style->alignment["textRotation"] > 90) {
1885  $textRotation = 90 - (int)$style->alignment["textRotation"];
1886  }
1887 
1888  $docStyle->getAlignment()->setTextRotation(intval($textRotation));
1889  $docStyle->getAlignment()->setWrapText(self::boolean((string) $style->alignment["wrapText"]));
1890  $docStyle->getAlignment()->setShrinkToFit(self::boolean((string) $style->alignment["shrinkToFit"]));
1891  $docStyle->getAlignment()->setIndent( intval((string)$style->alignment["indent"]) > 0 ? intval((string)$style->alignment["indent"]) : 0 );
1892  $docStyle->getAlignment()->setReadorder( intval((string)$style->alignment["readingOrder"]) > 0 ? intval((string)$style->alignment["readingOrder"]) : 0 );
1893  }
1894 
1895  // protection
1896  if (isset($style->protection)) {
1897  if (isset($style->protection['locked'])) {
1898  if (self::boolean((string) $style->protection['locked'])) {
1899  $docStyle->getProtection()->setLocked(PHPExcel_Style_Protection::PROTECTION_PROTECTED);
1900  } else {
1901  $docStyle->getProtection()->setLocked(PHPExcel_Style_Protection::PROTECTION_UNPROTECTED);
1902  }
1903  }
1904 
1905  if (isset($style->protection['hidden'])) {
1906  if (self::boolean((string) $style->protection['hidden'])) {
1907  $docStyle->getProtection()->setHidden(PHPExcel_Style_Protection::PROTECTION_PROTECTED);
1908  } else {
1909  $docStyle->getProtection()->setHidden(PHPExcel_Style_Protection::PROTECTION_UNPROTECTED);
1910  }
1911  }
1912  }
1913 
1914  // top-level style settings
1915  if (isset($style->quotePrefix)) {
1916  $docStyle->setQuotePrefix($style->quotePrefix);
1917  }
1918  }
1919 
1920 
1921  private static function _readBorder($docBorder, $eleBorder) {
1922  if (isset($eleBorder["style"])) {
1923  $docBorder->setBorderStyle((string) $eleBorder["style"]);
1924  }
1925  if (isset($eleBorder->color)) {
1926  $docBorder->getColor()->setARGB(self::_readColor($eleBorder->color));
1927  }
1928  }
1929 
1930 
1931  private function _parseRichText($is = null) {
1932  $value = new PHPExcel_RichText();
1933 
1934  if (isset($is->t)) {
1935  $value->createText( PHPExcel_Shared_String::ControlCharacterOOXML2PHP( (string) $is->t ) );
1936  } else {
1937  if(is_object($is->r)) {
1938  foreach ($is->r as $run) {
1939  if (!isset($run->rPr)) {
1940  $objText = $value->createText( PHPExcel_Shared_String::ControlCharacterOOXML2PHP( (string) $run->t ) );
1941 
1942  } else {
1943  $objText = $value->createTextRun( PHPExcel_Shared_String::ControlCharacterOOXML2PHP( (string) $run->t ) );
1944 
1945  if (isset($run->rPr->rFont["val"])) {
1946  $objText->getFont()->setName((string) $run->rPr->rFont["val"]);
1947  }
1948 
1949  if (isset($run->rPr->sz["val"])) {
1950  $objText->getFont()->setSize((string) $run->rPr->sz["val"]);
1951  }
1952 
1953  if (isset($run->rPr->color)) {
1954  $objText->getFont()->setColor( new PHPExcel_Style_Color( self::_readColor($run->rPr->color) ) );
1955  }
1956 
1957  if ((isset($run->rPr->b["val"]) && self::boolean((string) $run->rPr->b["val"])) ||
1958  (isset($run->rPr->b) && !isset($run->rPr->b["val"]))) {
1959  $objText->getFont()->setBold(TRUE);
1960  }
1961 
1962  if ((isset($run->rPr->i["val"]) && self::boolean((string) $run->rPr->i["val"])) ||
1963  (isset($run->rPr->i) && !isset($run->rPr->i["val"]))) {
1964  $objText->getFont()->setItalic(TRUE);
1965  }
1966 
1967  if (isset($run->rPr->vertAlign) && isset($run->rPr->vertAlign["val"])) {
1968  $vertAlign = strtolower((string)$run->rPr->vertAlign["val"]);
1969  if ($vertAlign == 'superscript') {
1970  $objText->getFont()->setSuperScript(TRUE);
1971  }
1972  if ($vertAlign == 'subscript') {
1973  $objText->getFont()->setSubScript(TRUE);
1974  }
1975  }
1976 
1977  if (isset($run->rPr->u) && !isset($run->rPr->u["val"])) {
1978  $objText->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
1979  } else if (isset($run->rPr->u) && isset($run->rPr->u["val"])) {
1980  $objText->getFont()->setUnderline((string)$run->rPr->u["val"]);
1981  }
1982 
1983  if ((isset($run->rPr->strike["val"]) && self::boolean((string) $run->rPr->strike["val"])) ||
1984  (isset($run->rPr->strike) && !isset($run->rPr->strike["val"]))) {
1985  $objText->getFont()->setStrikethrough(TRUE);
1986  }
1987  }
1988  }
1989  }
1990  }
1991 
1992  return $value;
1993  }
1994 
1995  private function _readRibbon($excel, $customUITarget, $zip)
1996  {
1997  $baseDir = dirname($customUITarget);
1998  $nameCustomUI = basename($customUITarget);
1999  // get the xml file (ribbon)
2000  $localRibbon = $this->_getFromZipArchive($zip, $customUITarget);
2001  $customUIImagesNames = array();
2002  $customUIImagesBinaries = array();
2003  // something like customUI/_rels/customUI.xml.rels
2004  $pathRels = $baseDir . '/_rels/' . $nameCustomUI . '.rels';
2005  $dataRels = $this->_getFromZipArchive($zip, $pathRels);
2006  if ($dataRels) {
2007  // exists and not empty if the ribbon have some pictures (other than internal MSO)
2008  $UIRels = simplexml_load_string($this->securityScan($dataRels), 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions());
2009  if ($UIRels) {
2010  // we need to save id and target to avoid parsing customUI.xml and "guess" if it's a pseudo callback who load the image
2011  foreach ($UIRels->Relationship as $ele) {
2012  if ($ele["Type"] == 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/image') {
2013  // an image ?
2014  $customUIImagesNames[(string) $ele['Id']] = (string)$ele['Target'];
2015  $customUIImagesBinaries[(string)$ele['Target']] = $this->_getFromZipArchive($zip, $baseDir . '/' . (string) $ele['Target']);
2016  }
2017  }
2018  }
2019  }
2020  if ($localRibbon) {
2021  $excel->setRibbonXMLData($customUITarget, $localRibbon);
2022  if (count($customUIImagesNames) > 0 && count($customUIImagesBinaries) > 0) {
2023  $excel->setRibbonBinObjects($customUIImagesNames, $customUIImagesBinaries);
2024  } else {
2025  $excel->setRibbonBinObjects(NULL);
2026  }
2027  } else {
2028  $excel->setRibbonXMLData(NULL);
2029  $excel->setRibbonBinObjects(NULL);
2030  }
2031  }
2032 
2033  private static function array_item($array, $key = 0) {
2034  return (isset($array[$key]) ? $array[$key] : null);
2035  }
2036 
2037 
2038  private static function dir_add($base, $add) {
2039  return preg_replace('~[^/]+/\.\./~', '', dirname($base) . "/$add");
2040  }
2041 
2042 
2043  private static function toCSSArray($style) {
2044  $style = str_replace(array("\r","\n"), "", $style);
2045 
2046  $temp = explode(';', $style);
2047  $style = array();
2048  foreach ($temp as $item) {
2049  $item = explode(':', $item);
2050 
2051  if (strpos($item[1], 'px') !== false) {
2052  $item[1] = str_replace('px', '', $item[1]);
2053  }
2054  if (strpos($item[1], 'pt') !== false) {
2055  $item[1] = str_replace('pt', '', $item[1]);
2056  $item[1] = PHPExcel_Shared_Font::fontSizeToPixels($item[1]);
2057  }
2058  if (strpos($item[1], 'in') !== false) {
2059  $item[1] = str_replace('in', '', $item[1]);
2060  $item[1] = PHPExcel_Shared_Font::inchSizeToPixels($item[1]);
2061  }
2062  if (strpos($item[1], 'cm') !== false) {
2063  $item[1] = str_replace('cm', '', $item[1]);
2064  $item[1] = PHPExcel_Shared_Font::centimeterSizeToPixels($item[1]);
2065  }
2066 
2067  $style[$item[0]] = $item[1];
2068  }
2069 
2070  return $style;
2071  }
2072 
2073  private static function boolean($value = NULL)
2074  {
2075  if (is_object($value)) {
2076  $value = (string) $value;
2077  }
2078  if (is_numeric($value)) {
2079  return (bool) $value;
2080  }
2081  return ($value === 'true' || $value === 'TRUE');
2082  }
2083 }
PHPExcel_Settings\getZipClass
static getZipClass()
Return the name of the Zip handler Class that PHPExcel is configured to use (PCLZip or ZipArchive) or...
Definition: Settings.php:141
PHPExcel_Reader_Excel2007\_castToError
static _castToError($c)
Definition: Excel2007.php:258
PHPExcel_Worksheet_AutoFilter_Column_Rule\AUTOFILTER_COLUMN_RULE_TOPTEN_PERCENT
const AUTOFILTER_COLUMN_RULE_TOPTEN_PERCENT
Definition: Rule.php:182
PHPExcel_Shared_Font\centimeterSizeToPixels
static centimeterSizeToPixels($sizeInCm=1)
Calculate an (approximate) pixel size, based on centimeter size.
Definition: Font.php:414
PHPExcel_Worksheet_AutoFilter_Column\AUTOFILTER_FILTERTYPE_TOPTENFILTER
const AUTOFILTER_FILTERTYPE_TOPTENFILTER
Definition: Column.php:45
PHPExcel_Worksheet_AutoFilter_Column_Rule\AUTOFILTER_RULETYPE_CUSTOMFILTER
const AUTOFILTER_RULETYPE_CUSTOMFILTER
Definition: Rule.php:40
PHPExcel_Style_Conditional\CONDITION_CONTAINSTEXT
const CONDITION_CONTAINSTEXT
Definition: Conditional.php:41
$worksheet
$worksheet
Definition: 40duplicateStyle.php:17
$style
$style
Definition: example_012.php:70
PHPExcel_Worksheet_AutoFilter_Column_Rule\AUTOFILTER_COLUMN_RULE_TOPTEN_BOTTOM
const AUTOFILTER_COLUMN_RULE_TOPTEN_BOTTOM
Definition: Rule.php:190
PHPExcel_Reader_Excel2007\load
load($pFilename)
Loads PHPExcel from file.
Definition: Excel2007.php:344
PHPExcel_Reader_Excel2007\_castToBool
static _castToBool($c)
Definition: Excel2007.php:244
PHPExcel_Cell\coordinateFromString
static coordinateFromString($pCoordinateString='A1')
Coordinate from string.
Definition: Cell.php:580
PHPExcel_Worksheet_Drawing
PHPExcel_NamedRange
Definition: NamedRange.php:36
PHPExcel_Style_Borders\DIAGONAL_DOWN
const DIAGONAL_DOWN
Definition: Borders.php:41
PHPExcel_ReferenceHelper\getInstance
static getInstance()
Get an instance of this class.
Definition: ReferenceHelper.php:57
color
if(PHP_SAPI !='cli') color
Definition: langcheck.php:120
PHPExcel_Worksheet_AutoFilter_Column\AUTOFILTER_FILTERTYPE_FILTER
const AUTOFILTER_FILTERTYPE_FILTER
Definition: Column.php:38
PHPExcel_Style_Color\changeBrightness
static changeBrightness($hex, $adjustPercentage)
Adjust the brightness of a color.
Definition: Color.php:300
PHPExcel_Reader_Abstract
Definition: Abstract.php:36
PHPExcel_Style_Color
Definition: Color.php:36
PHPExcel_Shared_Date\setExcelCalendar
static setExcelCalendar($baseDate)
Set the Excel calendar (Windows 1900 or Mac 1904)
Definition: Date.php:91
PHPExcel_Reader_Excel2007\listWorksheetNames
listWorksheetNames($pFilename)
Reads names of the worksheets from a file, without parsing the whole file to a PHPExcel object...
Definition: Excel2007.php:123
PHPExcel_Reader_Excel2007\toCSSArray
static toCSSArray($style)
Definition: Excel2007.php:2043
PHPExcel_Worksheet\BREAK_ROW
const BREAK_ROW
Definition: Worksheet.php:40
PHPExcel_Reader_Excel2007\_readColor
static _readColor($color, $background=FALSE)
Definition: Excel2007.php:1766
PHPExcel_Worksheet\BREAK_COLUMN
const BREAK_COLUMN
Definition: Worksheet.php:41
PHPExcel_Worksheet_AutoFilter_Column_Rule\AUTOFILTER_RULETYPE_DATEGROUP
const AUTOFILTER_RULETYPE_DATEGROUP
Definition: Rule.php:39
string
Add rich text string
The name of the decorator.
Definition: 05featuredemo.inc.php:117
PHPExcel_Shared_Font\inchSizeToPixels
static inchSizeToPixels($sizeInInch=1)
Calculate an (approximate) pixel size, based on inch size.
Definition: Font.php:404
PHPExcel_Style_Conditional
Definition: Conditional.php:36
PHPExcel_Reader_Excel2007\_castToString
static _castToString($c)
Definition: Excel2007.php:264
PHPExcel_Worksheet_AutoFilter_Column_Rule\AUTOFILTER_COLUMN_RULE_TOPTEN_BY_VALUE
const AUTOFILTER_COLUMN_RULE_TOPTEN_BY_VALUE
Definition: Rule.php:181
PHPExcel_Reader_Excel2007\_readStyle
static _readStyle($docStyle, $style)
Definition: Excel2007.php:1789
PHPExcel_Worksheet_AutoFilter_Column\AUTOFILTER_FILTERTYPE_CUSTOMFILTER
const AUTOFILTER_FILTERTYPE_CUSTOMFILTER
Definition: Column.php:39
PHPExcel_RichText
PHPExcel_Reader_DefaultReadFilter
Definition: DefaultReadFilter.php:45
PHPExcel_Style_Protection\PROTECTION_UNPROTECTED
const PROTECTION_UNPROTECTED
Definition: Protection.php:41
PHPExcel_Reader_Excel2007\_parseRichText
_parseRichText($is=null)
Definition: Excel2007.php:1931
PHPExcel_Reader_Excel2007\_getFromZipArchive
_getFromZipArchive($archive, $fileName='')
Definition: Excel2007.php:317
PHPExcel_Style_Font\UNDERLINE_SINGLE
const UNDERLINE_SINGLE
Definition: Font.php:42
PHPExcel_Reader_Abstract\securityScan
securityScan($xml)
Scan theXML for use of <!ENTITY to prevent XXE/XEE attacks.
Definition: Abstract.php:236
PHPExcel_Reader_Excel2007_Theme
Definition: Theme.php:36
PHPExcel_Reader_Excel2007\dir_add
static dir_add($base, $add)
Definition: Excel2007.php:2038
$r
$r
Definition: example_031.php:79
PHPExcel_Reader_Excel2007_Chart\readChart
static readChart($chartElements, $chartName)
Definition: Chart.php:63
PHPExcel_Reader_Abstract\securityScanFile
securityScanFile($filestream)
Scan theXML for use of <!ENTITY to prevent XXE/XEE attacks.
Definition: Abstract.php:251
$column
$column
Definition: 39dropdown.php:62
PHPExcel_Style_Borders\DIAGONAL_UP
const DIAGONAL_UP
Definition: Borders.php:40
PHPExcel_DocumentProperties\convertProperty
static convertProperty($propertyValue, $propertyType)
Definition: DocumentProperties.php:477
PHPExcel_Worksheet_AutoFilter_Column_Rule\AUTOFILTER_RULETYPE_TOPTENFILTER
const AUTOFILTER_RULETYPE_TOPTENFILTER
Definition: Rule.php:42
PHPExcel_Shared_Date\CALENDAR_MAC_1904
const CALENDAR_MAC_1904
Definition: Date.php:41
$baseDir
$baseDir
Definition: autoload_classmap.php:6
PHPExcel_Worksheet_AutoFilter_Column_Rule\AUTOFILTER_COLUMN_RULE_TOPTEN_TOP
const AUTOFILTER_COLUMN_RULE_TOPTEN_TOP
Definition: Rule.php:189
PHPExcel_Reader_Excel2007\_castToFormula
_castToFormula($c, $r, &$cellDataType, &$value, &$calculatedValue, &$sharedFormulas, $castBaseType)
Definition: Excel2007.php:270
PHPExcel_Style_NumberFormat\FORMAT_GENERAL
const FORMAT_GENERAL
Definition: NumberFormat.php:39
PHPExcel_Reader_IReader
Definition: IReader.php:36
PHPExcel_Settings\getLibXmlLoaderOptions
static getLibXmlLoaderOptions()
Get default options for libxml loader.
Definition: Settings.php:381
PHPExcel_Worksheet_AutoFilter_Column\AUTOFILTER_COLUMN_JOIN_AND
const AUTOFILTER_COLUMN_JOIN_AND
Definition: Column.php:64
PHPExcel_Style_Borders\DIAGONAL_BOTH
const DIAGONAL_BOTH
Definition: Borders.php:42
PHPExcel_Reader_Excel2007\_readBorder
static _readBorder($docBorder, $eleBorder)
Definition: Excel2007.php:1921
PHPExcel_Style
PHPExcel_Shared_Font\fontSizeToPixels
static fontSizeToPixels($fontSizeInPoints=11)
Calculate an (approximate) pixel size, based on a font points size.
Definition: Font.php:394
$comment
$comment
Definition: buildRTE.php:83
array
Create styles array
The data for the language used.
Definition: 40duplicateStyle.php:19
PHPExcel_Style_Conditional\CONDITION_CELLIS
const CONDITION_CELLIS
Definition: Conditional.php:40
PHPExcel_Reader_Excel2007\canRead
canRead($pFilename)
Can the current PHPExcel_Reader_IReader read the file?
Definition: Excel2007.php:78
PHPExcel_Style_Protection\PROTECTION_PROTECTED
const PROTECTION_PROTECTED
Definition: Protection.php:40
PHPExcel_Style_Conditional\CONDITION_EXPRESSION
const CONDITION_EXPRESSION
Definition: Conditional.php:42
PHPExcel_Reader_Abstract\getReadFilter
getReadFilter()
Read filter.
Definition: Abstract.php:173
PHPExcel_Cell\extractAllCellReferencesInRange
static extractAllCellReferencesInRange($pRange='A1')
Extract all cell references in range.
Definition: Cell.php:854
PHPExcel_Cell\columnIndexFromString
static columnIndexFromString($pString='A')
Column index from string.
Definition: Cell.php:782
PHPExcel_Reader_Excel2007\array_item
static array_item($array, $key=0)
Definition: Excel2007.php:2033
PHPExcel_Shared_File\realpath
static realpath($pFilename)
Returns canonicalized absolute pathname, also for ZIP archives.
Definition: File.php:102
PHPExcel_Reader_Exception
Definition: Exception.php:36
object
Create new PHPExcel object
obj_idprivate
Definition: 01pharSimple.php:37
PHPExcel_Worksheet_HeaderFooterDrawing
Definition: HeaderFooterDrawing.php:36
PHPExcel_Cell\stringFromColumnIndex
static stringFromColumnIndex($pColumnIndex=0)
String from columnindex.
Definition: Cell.php:825
PHPExcel_Style_Borders\DIAGONAL_NONE
const DIAGONAL_NONE
Definition: Borders.php:39
$objDrawing
$objDrawing
Definition: 04printing.php:70
PHPExcel_Style_Conditional\CONDITION_NONE
const CONDITION_NONE
Definition: Conditional.php:39
PHPExcel_Shared_Drawing\EMUToPixels
static EMUToPixels($pValue=0)
Convert EMU to pixels.
Definition: Drawing.php:54
PHPExcel_Reader_Excel2007\boolean
static boolean($value=NULL)
Definition: Excel2007.php:2073
PHPExcel_Worksheet\extractSheetTitle
static extractSheetTitle($pRange, $returnRange=false)
Extract worksheet title from range.
Definition: Worksheet.php:2654
PHPExcel_Shared_Date\CALENDAR_WINDOWS_1900
const CALENDAR_WINDOWS_1900
constants
Definition: Date.php:40
PHPExcel_Style_Color\indexedColor
static indexedColor($pIndex, $background=FALSE)
Get indexed color.
Definition: Color.php:338
PHPExcel_Worksheet_AutoFilter_Column_Rule\AUTOFILTER_RULETYPE_DYNAMICFILTER
const AUTOFILTER_RULETYPE_DYNAMICFILTER
Definition: Rule.php:41
PHPExcel_Reader_Excel2007\__construct
__construct()
Create a new PHPExcel_Reader_Excel2007 instance.
Definition: Excel2007.php:65
PHPExcel_Reader_Excel2007\$_theme
static $_theme
Definition: Excel2007.php:59
defined
defined( 'APPLICATION_ENV')||define( 'APPLICATION_ENV'
Definition: bootstrap.php:27
PHPExcel_Style_NumberFormat\builtInFormatCode
static builtInFormatCode($pIndex)
Get built-in format code.
Definition: NumberFormat.php:321
PHPExcel_Worksheet_AutoFilter_Column_Rule\AUTOFILTER_RULETYPE_FILTER
const AUTOFILTER_RULETYPE_FILTER
Definition: Rule.php:38
PHPExcel_Reader_Excel2007
PHPExcel_DocumentProperties\convertPropertyType
static convertPropertyType($propertyType)
Definition: DocumentProperties.php:535
PHPExcel_Shared_Drawing\angleToDegrees
static angleToDegrees($pValue=0)
Convert angle to degrees.
Definition: Drawing.php:164
PHPExcel_Reader_Excel2007\$_referenceHelper
$_referenceHelper
Definition: Excel2007.php:52
PHPExcel_Shared_String\ControlCharacterOOXML2PHP
static ControlCharacterOOXML2PHP($value='')
Convert from OpenXML escaped control character to PHP control character.
Definition: String.php:355
PHPExcel_Worksheet_AutoFilter_Column\AUTOFILTER_FILTERTYPE_DYNAMICFILTER
const AUTOFILTER_FILTERTYPE_DYNAMICFILTER
Definition: Column.php:42
PHPExcel_Reader_Excel2007\listWorksheetInfo
listWorksheetInfo($pFilename)
Return worksheet info (Name, Last Column Letter, Last Column Index, Total Rows, Total Columns) ...
Definition: Excel2007.php:169
if
PHPExcel root directory.
Definition: Database.php:30
PHPExcel_Reader_Excel2007\_readRibbon
_readRibbon($excel, $customUITarget, $zip)
Definition: Excel2007.php:1995