Search moodle.org's
Developer Documentation
Developer Documentation
See Release Notes
Long Term Support Release
- Bug fixes for general core bugs in 4.1.x will end 13 November 2023 (12 months).
- Bug fixes for security issues in 4.1.x will end 10 November 2025 (36 months).
- PHP version: minimum PHP 7.4.0 Note: minimum PHP version has increased since Moodle 4.0. PHP 8.0.x is supported too.
Differences Between: [Versions 310 and 401] [Versions 311 and 401] [Versions 39 and 401] [Versions 400 and 401] [Versions 401 and 402] [Versions 401 and 403]
1 <?php 2 3 namespace PhpOffice\PhpSpreadsheet\Reader; 4 5 use PhpOffice\PhpSpreadsheet\Cell\Coordinate; 6 use PhpOffice\PhpSpreadsheet\Cell\DataType; 7 use PhpOffice\PhpSpreadsheet\Cell\Hyperlink; 8 use PhpOffice\PhpSpreadsheet\DefinedName; 9 use PhpOffice\PhpSpreadsheet\Reader\Security\XmlScanner; 10 use PhpOffice\PhpSpreadsheet\Reader\Xlsx\AutoFilter; 11 use PhpOffice\PhpSpreadsheet\Reader\Xlsx\Chart; 12 use PhpOffice\PhpSpreadsheet\Reader\Xlsx\ColumnAndRowAttributes; 13 use PhpOffice\PhpSpreadsheet\Reader\Xlsx\ConditionalStyles; 14 use PhpOffice\PhpSpreadsheet\Reader\Xlsx\DataValidations; 15 use PhpOffice\PhpSpreadsheet\Reader\Xlsx\Hyperlinks; 16 use PhpOffice\PhpSpreadsheet\Reader\Xlsx\Namespaces; 17 use PhpOffice\PhpSpreadsheet\Reader\Xlsx\PageSetup; 18 use PhpOffice\PhpSpreadsheet\Reader\Xlsx\Properties as PropertyReader; 19 use PhpOffice\PhpSpreadsheet\Reader\Xlsx\SheetViewOptions; 20 use PhpOffice\PhpSpreadsheet\Reader\Xlsx\SheetViews; 21 use PhpOffice\PhpSpreadsheet\Reader\Xlsx\Styles; 22 use PhpOffice\PhpSpreadsheet\Reader\Xlsx\Theme; 23 use PhpOffice\PhpSpreadsheet\Reader\Xlsx\WorkbookView; 24 use PhpOffice\PhpSpreadsheet\ReferenceHelper; 25 use PhpOffice\PhpSpreadsheet\RichText\RichText; 26 use PhpOffice\PhpSpreadsheet\Settings; 27 use PhpOffice\PhpSpreadsheet\Shared\Date; 28 use PhpOffice\PhpSpreadsheet\Shared\Drawing; 29 use PhpOffice\PhpSpreadsheet\Shared\File; 30 use PhpOffice\PhpSpreadsheet\Shared\Font; 31 use PhpOffice\PhpSpreadsheet\Shared\StringHelper; 32 use PhpOffice\PhpSpreadsheet\Spreadsheet; 33 use PhpOffice\PhpSpreadsheet\Style\Color; 34 use PhpOffice\PhpSpreadsheet\Style\Font as StyleFont; 35 use PhpOffice\PhpSpreadsheet\Style\NumberFormat; 36 use PhpOffice\PhpSpreadsheet\Style\Style; 37 use PhpOffice\PhpSpreadsheet\Worksheet\HeaderFooterDrawing; 38 use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet; 39 use SimpleXMLElement; 40 use Throwable; 41 use XMLReader; 42 use ZipArchive; 43 44 class Xlsx extends BaseReader 45 { 46 const INITIAL_FILE = '_rels/.rels'; 47 48 /** 49 * ReferenceHelper instance. 50 * 51 * @var ReferenceHelper 52 */ 53 private $referenceHelper; 54 55 /** 56 * @var ZipArchive 57 */ 58 private $zip; 59 60 /** @var Styles */ 61 private $styleReader; 62 63 /** 64 * Create a new Xlsx Reader instance. 65 */ 66 public function __construct() 67 { 68 parent::__construct(); 69 $this->referenceHelper = ReferenceHelper::getInstance(); 70 $this->securityScanner = XmlScanner::getInstance($this); 71 } 72 73 /** 74 * Can the current IReader read the file? 75 */ 76 public function canRead(string $filename): bool 77 { 78 if (!File::testFileNoThrow($filename, self::INITIAL_FILE)) { 79 return false; 80 } 81 82 $result = false; 83 $this->zip = $zip = new ZipArchive(); 84 85 if ($zip->open($filename) === true) { 86 [$workbookBasename] = $this->getWorkbookBaseName(); 87 $result = !empty($workbookBasename); 88 89 $zip->close(); 90 } 91 92 return $result; 93 } 94 95 /** 96 * @param mixed $value 97 */ 98 public static function testSimpleXml($value): SimpleXMLElement 99 { 100 return ($value instanceof SimpleXMLElement) ? $value : new SimpleXMLElement('<?xml version="1.0" encoding="UTF-8"?><root></root>'); 101 } 102 103 public static function getAttributes(?SimpleXMLElement $value, string $ns = ''): SimpleXMLElement 104 { 105 return self::testSimpleXml($value === null ? $value : $value->attributes($ns)); 106 } 107 108 // Phpstan thinks, correctly, that xpath can return false. 109 // Scrutinizer thinks it can't. 110 // Sigh. 111 private static function xpathNoFalse(SimpleXmlElement $sxml, string $path): array 112 { 113 return self::falseToArray($sxml->xpath($path)); 114 } 115 116 /** 117 * @param mixed $value 118 */ 119 public static function falseToArray($value): array 120 { 121 return is_array($value) ? $value : []; 122 } 123 124 private function loadZip(string $filename, string $ns = ''): SimpleXMLElement 125 { 126 $contents = $this->getFromZipArchive($this->zip, $filename); 127 $rels = simplexml_load_string( 128 $this->securityScanner->scan($contents), 129 'SimpleXMLElement', 130 Settings::getLibXmlLoaderOptions(), 131 $ns 132 ); 133 134 return self::testSimpleXml($rels); 135 } 136 137 // This function is just to identify cases where I'm not sure 138 // why empty namespace is required. 139 private function loadZipNonamespace(string $filename, string $ns): SimpleXMLElement 140 { 141 $contents = $this->getFromZipArchive($this->zip, $filename); 142 $rels = simplexml_load_string( 143 $this->securityScanner->scan($contents), 144 'SimpleXMLElement', 145 Settings::getLibXmlLoaderOptions(), 146 ($ns === '' ? $ns : '') 147 ); 148 149 return self::testSimpleXml($rels); 150 } 151 152 private const REL_TO_MAIN = [ 153 Namespaces::PURL_OFFICE_DOCUMENT => Namespaces::PURL_MAIN, 154 Namespaces::THUMBNAIL => '', 155 ]; 156 157 private const REL_TO_DRAWING = [ 158 Namespaces::PURL_RELATIONSHIPS => Namespaces::PURL_DRAWING, 159 ]; 160 161 private const REL_TO_CHART = [ 162 Namespaces::PURL_RELATIONSHIPS => Namespaces::PURL_CHART, 163 ]; 164 165 /** 166 * Reads names of the worksheets from a file, without parsing the whole file to a Spreadsheet object. 167 * 168 * @param string $filename 169 * 170 * @return array 171 */ 172 public function listWorksheetNames($filename) 173 { 174 File::assertFile($filename, self::INITIAL_FILE); 175 176 $worksheetNames = []; 177 178 $this->zip = $zip = new ZipArchive(); 179 $zip->open($filename); 180 181 // The files we're looking at here are small enough that simpleXML is more efficient than XMLReader 182 $rels = $this->loadZip(self::INITIAL_FILE, Namespaces::RELATIONSHIPS); 183 foreach ($rels->Relationship as $relx) { 184 $rel = self::getAttributes($relx); 185 $relType = (string) $rel['Type']; 186 $mainNS = self::REL_TO_MAIN[$relType] ?? Namespaces::MAIN; 187 if ($mainNS !== '') { 188 $xmlWorkbook = $this->loadZip((string) $rel['Target'], $mainNS); 189 190 if ($xmlWorkbook->sheets) { 191 foreach ($xmlWorkbook->sheets->sheet as $eleSheet) { 192 // Check if sheet should be skipped 193 $worksheetNames[] = (string) self::getAttributes($eleSheet)['name']; 194 } 195 } 196 } 197 } 198 199 $zip->close(); 200 201 return $worksheetNames; 202 } 203 204 /** 205 * Return worksheet info (Name, Last Column Letter, Last Column Index, Total Rows, Total Columns). 206 * 207 * @param string $filename 208 * 209 * @return array 210 */ 211 public function listWorksheetInfo($filename) 212 { 213 File::assertFile($filename, self::INITIAL_FILE); 214 215 $worksheetInfo = []; 216 217 $this->zip = $zip = new ZipArchive(); 218 $zip->open($filename); 219 220 $rels = $this->loadZip(self::INITIAL_FILE, Namespaces::RELATIONSHIPS); 221 foreach ($rels->Relationship as $relx) { 222 $rel = self::getAttributes($relx); 223 $relType = (string) $rel['Type']; 224 $mainNS = self::REL_TO_MAIN[$relType] ?? Namespaces::MAIN; 225 if ($mainNS !== '') { 226 $relTarget = (string) $rel['Target']; 227 $dir = dirname($relTarget); 228 $namespace = dirname($relType); 229 $relsWorkbook = $this->loadZip("$dir/_rels/" . basename($relTarget) . '.rels', ''); 230 231 $worksheets = []; 232 foreach ($relsWorkbook->Relationship as $elex) { 233 $ele = self::getAttributes($elex); 234 if ( 235 ((string) $ele['Type'] === "$namespace/worksheet") || 236 ((string) $ele['Type'] === "$namespace/chartsheet") 237 ) { 238 $worksheets[(string) $ele['Id']] = $ele['Target']; 239 } 240 } 241 242 $xmlWorkbook = $this->loadZip($relTarget, $mainNS); 243 if ($xmlWorkbook->sheets) { 244 $dir = dirname($relTarget); 245 /** @var SimpleXMLElement $eleSheet */ 246 foreach ($xmlWorkbook->sheets->sheet as $eleSheet) { 247 $tmpInfo = [ 248 'worksheetName' => (string) self::getAttributes($eleSheet)['name'], 249 'lastColumnLetter' => 'A', 250 'lastColumnIndex' => 0, 251 'totalRows' => 0, 252 'totalColumns' => 0, 253 ]; 254 255 $fileWorksheet = (string) $worksheets[(string) self::getArrayItem(self::getAttributes($eleSheet, $namespace), 'id')]; 256 $fileWorksheetPath = strpos($fileWorksheet, '/') === 0 ? substr($fileWorksheet, 1) : "$dir/$fileWorksheet"; 257 258 $xml = new XMLReader(); 259 $xml->xml( 260 $this->securityScanner->scanFile( 261 'zip://' . File::realpath($filename) . '#' . $fileWorksheetPath 262 ), 263 null, 264 Settings::getLibXmlLoaderOptions() 265 ); 266 $xml->setParserProperty(2, true); 267 268 $currCells = 0; 269 while ($xml->read()) { 270 if ($xml->localName == 'row' && $xml->nodeType == XMLReader::ELEMENT && $xml->namespaceURI === $mainNS) { 271 $row = $xml->getAttribute('r'); 272 $tmpInfo['totalRows'] = $row; 273 $tmpInfo['totalColumns'] = max($tmpInfo['totalColumns'], $currCells); 274 $currCells = 0; 275 } elseif ($xml->localName == 'c' && $xml->nodeType == XMLReader::ELEMENT && $xml->namespaceURI === $mainNS) { 276 $cell = $xml->getAttribute('r'); 277 $currCells = $cell ? max($currCells, Coordinate::indexesFromString($cell)[0]) : ($currCells + 1); 278 } 279 } 280 $tmpInfo['totalColumns'] = max($tmpInfo['totalColumns'], $currCells); 281 $xml->close(); 282 283 $tmpInfo['lastColumnIndex'] = $tmpInfo['totalColumns'] - 1; 284 $tmpInfo['lastColumnLetter'] = Coordinate::stringFromColumnIndex($tmpInfo['lastColumnIndex'] + 1); 285 286 $worksheetInfo[] = $tmpInfo; 287 } 288 } 289 } 290 } 291 292 $zip->close(); 293 294 return $worksheetInfo; 295 } 296 297 private static function castToBoolean(SimpleXMLElement $c): bool 298 { 299 $value = isset($c->v) ? (string) $c->v : null; 300 if ($value == '0') { 301 return false; 302 } elseif ($value == '1') { 303 return true; 304 } 305 306 return (bool) $c->v; 307 } 308 309 private static function castToError(?SimpleXMLElement $c): ?string 310 { 311 return isset($c, $c->v) ? (string) $c->v : null; 312 } 313 314 private static function castToString(?SimpleXMLElement $c): ?string 315 { 316 return isset($c, $c->v) ? (string) $c->v : null; 317 } 318 319 /** 320 * @param mixed $value 321 * @param mixed $calculatedValue 322 */ 323 private function castToFormula(?SimpleXMLElement $c, string $r, string &$cellDataType, &$value, &$calculatedValue, array &$sharedFormulas, string $castBaseType): void 324 { 325 if ($c === null) { 326 return; 327 } 328 $attr = $c->f->attributes(); 329 $cellDataType = 'f'; 330 $value = "={$c->f}"; 331 $calculatedValue = self::$castBaseType($c); 332 333 // Shared formula? 334 if (isset($attr['t']) && strtolower((string) $attr['t']) == 'shared') { 335 $instance = (string) $attr['si']; 336 337 if (!isset($sharedFormulas[(string) $attr['si']])) { 338 $sharedFormulas[$instance] = ['master' => $r, 'formula' => $value]; 339 } else { 340 $master = Coordinate::indexesFromString($sharedFormulas[$instance]['master']); 341 $current = Coordinate::indexesFromString($r); 342 343 $difference = [0, 0]; 344 $difference[0] = $current[0] - $master[0]; 345 $difference[1] = $current[1] - $master[1]; 346 347 $value = $this->referenceHelper->updateFormulaReferences($sharedFormulas[$instance]['formula'], 'A1', $difference[0], $difference[1]); 348 } 349 } 350 } 351 352 /** 353 * @param string $fileName 354 */ 355 private function fileExistsInArchive(ZipArchive $archive, $fileName = ''): bool 356 { 357 // Root-relative paths 358 if (strpos($fileName, '//') !== false) { 359 $fileName = substr($fileName, strpos($fileName, '//') + 1); 360 } 361 $fileName = File::realpath($fileName); 362 363 // Sadly, some 3rd party xlsx generators don't use consistent case for filenaming 364 // so we need to load case-insensitively from the zip file 365 366 // Apache POI fixes 367 $contents = $archive->locateName($fileName, ZipArchive::FL_NOCASE); 368 if ($contents === false) { 369 $contents = $archive->locateName(substr($fileName, 1), ZipArchive::FL_NOCASE); 370 } 371 372 return $contents !== false; 373 } 374 375 /** 376 * @param string $fileName 377 * 378 * @return string 379 */ 380 private function getFromZipArchive(ZipArchive $archive, $fileName = '') 381 { 382 // Root-relative paths 383 if (strpos($fileName, '//') !== false) { 384 $fileName = substr($fileName, strpos($fileName, '//') + 1); 385 } 386 // Relative paths generated by dirname($filename) when $filename 387 // has no path (i.e.files in root of the zip archive) 388 $fileName = (string) preg_replace('/^\.\//', '', $fileName); 389 $fileName = File::realpath($fileName); 390 391 // Sadly, some 3rd party xlsx generators don't use consistent case for filenaming 392 // so we need to load case-insensitively from the zip file 393 394 // Apache POI fixes 395 $contents = $archive->getFromName($fileName, 0, ZipArchive::FL_NOCASE); 396 if ($contents === false) { 397 $contents = $archive->getFromName(substr($fileName, 1), 0, ZipArchive::FL_NOCASE); 398 } 399 400 return ($contents === false) ? '' : $contents; 401 } 402 403 /** 404 * Loads Spreadsheet from file. 405 */ 406 protected function loadSpreadsheetFromFile(string $filename): Spreadsheet 407 { 408 File::assertFile($filename, self::INITIAL_FILE); 409 410 // Initialisations 411 $excel = new Spreadsheet(); 412 $excel->removeSheetByIndex(0); 413 $addingFirstCellStyleXf = true; 414 $addingFirstCellXf = true; 415 416 $unparsedLoadedData = []; 417 418 $this->zip = $zip = new ZipArchive(); 419 $zip->open($filename); 420 421 // Read the theme first, because we need the colour scheme when reading the styles 422 [$workbookBasename, $xmlNamespaceBase] = $this->getWorkbookBaseName(); 423 $drawingNS = self::REL_TO_DRAWING[$xmlNamespaceBase] ?? Namespaces::DRAWINGML; 424 $chartNS = self::REL_TO_CHART[$xmlNamespaceBase] ?? Namespaces::CHART; 425 $wbRels = $this->loadZip("xl/_rels/{$workbookBasename}.rels", Namespaces::RELATIONSHIPS); 426 $theme = null; 427 $this->styleReader = new Styles(); 428 foreach ($wbRels->Relationship as $relx) { 429 $rel = self::getAttributes($relx); 430 $relTarget = (string) $rel['Target']; 431 if (substr($relTarget, 0, 4) === '/xl/') { 432 $relTarget = substr($relTarget, 4); 433 } 434 switch ($rel['Type']) { 435 case "$xmlNamespaceBase/theme": 436 $themeOrderArray = ['lt1', 'dk1', 'lt2', 'dk2']; 437 $themeOrderAdditional = count($themeOrderArray); 438 439 $xmlTheme = $this->loadZip("xl/{$relTarget}", $drawingNS); 440 $xmlThemeName = self::getAttributes($xmlTheme); 441 $xmlTheme = $xmlTheme->children($drawingNS); 442 $themeName = (string) $xmlThemeName['name']; 443 444 $colourScheme = self::getAttributes($xmlTheme->themeElements->clrScheme); 445 $colourSchemeName = (string) $colourScheme['name']; 446 $colourScheme = $xmlTheme->themeElements->clrScheme->children($drawingNS); 447 448 $themeColours = []; 449 foreach ($colourScheme as $k => $xmlColour) { 450 $themePos = array_search($k, $themeOrderArray); 451 if ($themePos === false) { 452 $themePos = $themeOrderAdditional++; 453 } 454 if (isset($xmlColour->sysClr)) { 455 $xmlColourData = self::getAttributes($xmlColour->sysClr); 456 $themeColours[$themePos] = (string) $xmlColourData['lastClr']; 457 } elseif (isset($xmlColour->srgbClr)) { 458 $xmlColourData = self::getAttributes($xmlColour->srgbClr); 459 $themeColours[$themePos] = (string) $xmlColourData['val']; 460 } 461 } 462 $theme = new Theme($themeName, $colourSchemeName, $themeColours); 463 $this->styleReader->setTheme($theme); 464 465 break; 466 } 467 } 468 469 $rels = $this->loadZip(self::INITIAL_FILE, Namespaces::RELATIONSHIPS); 470 471 $propertyReader = new PropertyReader($this->securityScanner, $excel->getProperties()); 472 foreach ($rels->Relationship as $relx) { 473 $rel = self::getAttributes($relx); 474 $relTarget = (string) $rel['Target']; 475 $relType = (string) $rel['Type']; 476 $mainNS = self::REL_TO_MAIN[$relType] ?? Namespaces::MAIN; 477 switch ($relType) { 478 case Namespaces::CORE_PROPERTIES: 479 $propertyReader->readCoreProperties($this->getFromZipArchive($zip, $relTarget)); 480 481 break; 482 case "$xmlNamespaceBase/extended-properties": 483 $propertyReader->readExtendedProperties($this->getFromZipArchive($zip, $relTarget)); 484 485 break; 486 case "$xmlNamespaceBase/custom-properties": 487 $propertyReader->readCustomProperties($this->getFromZipArchive($zip, $relTarget)); 488 489 break; 490 //Ribbon 491 case Namespaces::EXTENSIBILITY: 492 $customUI = $relTarget; 493 if ($customUI) { 494 $this->readRibbon($excel, $customUI, $zip); 495 } 496 497 break; 498 case "$xmlNamespaceBase/officeDocument": 499 $dir = dirname($relTarget); 500 501 // Do not specify namespace in next stmt - do it in Xpath 502 $relsWorkbook = $this->loadZip("$dir/_rels/" . basename($relTarget) . '.rels', ''); 503 $relsWorkbook->registerXPathNamespace('rel', Namespaces::RELATIONSHIPS); 504 505 $sharedStrings = []; 506 $relType = "rel:Relationship[@Type='" 507 //. Namespaces::SHARED_STRINGS 508 . "$xmlNamespaceBase/sharedStrings" 509 . "']"; 510 $xpath = self::getArrayItem($relsWorkbook->xpath($relType)); 511 512 if ($xpath) { 513 $xmlStrings = $this->loadZip("$dir/$xpath[Target]", $mainNS); 514 if (isset($xmlStrings->si)) { 515 foreach ($xmlStrings->si as $val) { 516 if (isset($val->t)) { 517 $sharedStrings[] = StringHelper::controlCharacterOOXML2PHP((string) $val->t); 518 } elseif (isset($val->r)) { 519 $sharedStrings[] = $this->parseRichText($val); 520 } 521 } 522 } 523 } 524 525 $worksheets = []; 526 $macros = $customUI = null; 527 foreach ($relsWorkbook->Relationship as $elex) { 528 $ele = self::getAttributes($elex); 529 switch ($ele['Type']) { 530 case Namespaces::WORKSHEET: 531 case Namespaces::PURL_WORKSHEET: 532 $worksheets[(string) $ele['Id']] = $ele['Target']; 533 534 break; 535 case Namespaces::CHARTSHEET: 536 if ($this->includeCharts === true) { 537 $worksheets[(string) $ele['Id']] = $ele['Target']; 538 } 539 540 break; 541 // a vbaProject ? (: some macros) 542 case Namespaces::VBA: 543 $macros = $ele['Target']; 544 545 break; 546 } 547 } 548 549 if ($macros !== null) { 550 $macrosCode = $this->getFromZipArchive($zip, 'xl/vbaProject.bin'); //vbaProject.bin always in 'xl' dir and always named vbaProject.bin 551 if ($macrosCode !== false) { 552 $excel->setMacrosCode($macrosCode); 553 $excel->setHasMacros(true); 554 //short-circuit : not reading vbaProject.bin.rel to get Signature =>allways vbaProjectSignature.bin in 'xl' dir 555 $Certificate = $this->getFromZipArchive($zip, 'xl/vbaProjectSignature.bin'); 556 if ($Certificate !== false) { 557 $excel->setMacrosCertificate($Certificate); 558 } 559 } 560 } 561 562 $relType = "rel:Relationship[@Type='" 563 . "$xmlNamespaceBase/styles" 564 . "']"; 565 $xpath = self::getArrayItem(self::xpathNoFalse($relsWorkbook, $relType)); 566 567 if ($xpath === null) { 568 $xmlStyles = self::testSimpleXml(null); 569 } else { 570 $xmlStyles = $this->loadZip("$dir/$xpath[Target]", $mainNS); 571 } 572 573 $palette = self::extractPalette($xmlStyles); 574 $this->styleReader->setWorkbookPalette($palette); 575 $fills = self::extractStyles($xmlStyles, 'fills', 'fill'); 576 $fonts = self::extractStyles($xmlStyles, 'fonts', 'font'); 577 $borders = self::extractStyles($xmlStyles, 'borders', 'border'); 578 $xfTags = self::extractStyles($xmlStyles, 'cellXfs', 'xf'); 579 $cellXfTags = self::extractStyles($xmlStyles, 'cellStyleXfs', 'xf'); 580 581 $styles = []; 582 $cellStyles = []; 583 $numFmts = null; 584 if (/*$xmlStyles && */ $xmlStyles->numFmts[0]) { 585 $numFmts = $xmlStyles->numFmts[0]; 586 } 587 if (isset($numFmts) && ($numFmts !== null)) { 588 $numFmts->registerXPathNamespace('sml', $mainNS); 589 } 590 $this->styleReader->setNamespace($mainNS); 591 if (!$this->readDataOnly/* && $xmlStyles*/) { 592 foreach ($xfTags as $xfTag) { 593 $xf = self::getAttributes($xfTag); 594 $numFmt = null; 595 596 if ($xf['numFmtId']) { 597 if (isset($numFmts)) { 598 $tmpNumFmt = self::getArrayItem($numFmts->xpath("sml:numFmt[@numFmtId=$xf[numFmtId]]")); 599 600 if (isset($tmpNumFmt['formatCode'])) { 601 $numFmt = (string) $tmpNumFmt['formatCode']; 602 } 603 } 604 605 // We shouldn't override any of the built-in MS Excel values (values below id 164) 606 // But there's a lot of naughty homebrew xlsx writers that do use "reserved" id values that aren't actually used 607 // So we make allowance for them rather than lose formatting masks 608 if ( 609 $numFmt === null && 610 (int) $xf['numFmtId'] < 164 && 611 NumberFormat::builtInFormatCode((int) $xf['numFmtId']) !== '' 612 ) { 613 $numFmt = NumberFormat::builtInFormatCode((int) $xf['numFmtId']); 614 } 615 } 616 $quotePrefix = (bool) ($xf['quotePrefix'] ?? false); 617 618 $style = (object) [ 619 'numFmt' => $numFmt ?? NumberFormat::FORMAT_GENERAL, 620 'font' => $fonts[(int) ($xf['fontId'])], 621 'fill' => $fills[(int) ($xf['fillId'])], 622 'border' => $borders[(int) ($xf['borderId'])], 623 'alignment' => $xfTag->alignment, 624 'protection' => $xfTag->protection, 625 'quotePrefix' => $quotePrefix, 626 ]; 627 $styles[] = $style; 628 629 // add style to cellXf collection 630 $objStyle = new Style(); 631 $this->styleReader->readStyle($objStyle, $style); 632 if ($addingFirstCellXf) { 633 $excel->removeCellXfByIndex(0); // remove the default style 634 $addingFirstCellXf = false; 635 } 636 $excel->addCellXf($objStyle); 637 } 638 639 foreach ($cellXfTags as $xfTag) { 640 $xf = self::getAttributes($xfTag); 641 $numFmt = NumberFormat::FORMAT_GENERAL; 642 if ($numFmts && $xf['numFmtId']) { 643 $tmpNumFmt = self::getArrayItem($numFmts->xpath("sml:numFmt[@numFmtId=$xf[numFmtId]]")); 644 if (isset($tmpNumFmt['formatCode'])) { 645 $numFmt = (string) $tmpNumFmt['formatCode']; 646 } elseif ((int) $xf['numFmtId'] < 165) { 647 $numFmt = NumberFormat::builtInFormatCode((int) $xf['numFmtId']); 648 } 649 } 650 651 $quotePrefix = (bool) ($xf['quotePrefix'] ?? false); 652 653 $cellStyle = (object) [ 654 'numFmt' => $numFmt, 655 'font' => $fonts[(int) ($xf['fontId'])], 656 'fill' => $fills[((int) $xf['fillId'])], 657 'border' => $borders[(int) ($xf['borderId'])], 658 'alignment' => $xfTag->alignment, 659 'protection' => $xfTag->protection, 660 'quotePrefix' => $quotePrefix, 661 ]; 662 $cellStyles[] = $cellStyle; 663 664 // add style to cellStyleXf collection 665 $objStyle = new Style(); 666 $this->styleReader->readStyle($objStyle, $cellStyle); 667 if ($addingFirstCellStyleXf) { 668 $excel->removeCellStyleXfByIndex(0); // remove the default style 669 $addingFirstCellStyleXf = false; 670 } 671 $excel->addCellStyleXf($objStyle); 672 } 673 } 674 $this->styleReader->setStyleXml($xmlStyles); 675 $this->styleReader->setNamespace($mainNS); 676 $this->styleReader->setStyleBaseData($theme, $styles, $cellStyles); 677 $dxfs = $this->styleReader->dxfs($this->readDataOnly); 678 $styles = $this->styleReader->styles(); 679 680 $xmlWorkbook = $this->loadZipNoNamespace($relTarget, $mainNS); 681 $xmlWorkbookNS = $this->loadZip($relTarget, $mainNS); 682 683 // Set base date 684 if ($xmlWorkbookNS->workbookPr) { 685 Date::setExcelCalendar(Date::CALENDAR_WINDOWS_1900); 686 $attrs1904 = self::getAttributes($xmlWorkbookNS->workbookPr); 687 if (isset($attrs1904['date1904'])) { 688 if (self::boolean((string) $attrs1904['date1904'])) { 689 Date::setExcelCalendar(Date::CALENDAR_MAC_1904); 690 } 691 } 692 } 693 694 // Set protection 695 $this->readProtection($excel, $xmlWorkbook); 696 697 $sheetId = 0; // keep track of new sheet id in final workbook 698 $oldSheetId = -1; // keep track of old sheet id in final workbook 699 $countSkippedSheets = 0; // keep track of number of skipped sheets 700 $mapSheetId = []; // mapping of sheet ids from old to new 701 702 $charts = $chartDetails = []; 703 704 if ($xmlWorkbookNS->sheets) { 705 /** @var SimpleXMLElement $eleSheet */ 706 foreach ($xmlWorkbookNS->sheets->sheet as $eleSheet) { 707 $eleSheetAttr = self::getAttributes($eleSheet); 708 ++$oldSheetId; 709 710 // Check if sheet should be skipped 711 if (is_array($this->loadSheetsOnly) && !in_array((string) $eleSheetAttr['name'], $this->loadSheetsOnly)) { 712 ++$countSkippedSheets; 713 $mapSheetId[$oldSheetId] = null; 714 715 continue; 716 } 717 718 $sheetReferenceId = (string) self::getArrayItem(self::getAttributes($eleSheet, $xmlNamespaceBase), 'id'); 719 if (isset($worksheets[$sheetReferenceId]) === false) { 720 ++$countSkippedSheets; 721 $mapSheetId[$oldSheetId] = null; 722 723 continue; 724 } 725 // Map old sheet id in original workbook to new sheet id. 726 // They will differ if loadSheetsOnly() is being used 727 $mapSheetId[$oldSheetId] = $oldSheetId - $countSkippedSheets; 728 729 // Load sheet 730 $docSheet = $excel->createSheet(); 731 // Use false for $updateFormulaCellReferences to prevent adjustment of worksheet 732 // references in formula cells... during the load, all formulae should be correct, 733 // and we're simply bringing the worksheet name in line with the formula, not the 734 // reverse 735 $docSheet->setTitle((string) $eleSheetAttr['name'], false, false); 736 737 $fileWorksheet = (string) $worksheets[$sheetReferenceId]; 738 $xmlSheet = $this->loadZipNoNamespace("$dir/$fileWorksheet", $mainNS); 739 $xmlSheetNS = $this->loadZip("$dir/$fileWorksheet", $mainNS); 740 741 $sharedFormulas = []; 742 743 if (isset($eleSheetAttr['state']) && (string) $eleSheetAttr['state'] != '') { 744 $docSheet->setSheetState((string) $eleSheetAttr['state']); 745 } 746 if ($xmlSheetNS) { 747 $xmlSheetMain = $xmlSheetNS->children($mainNS); 748 // Setting Conditional Styles adjusts selected cells, so we need to execute this 749 // before reading the sheet view data to get the actual selected cells 750 if (!$this->readDataOnly && ($xmlSheet->conditionalFormatting)) { 751 (new ConditionalStyles($docSheet, $xmlSheet, $dxfs))->load(); 752 } 753 if (!$this->readDataOnly && $xmlSheet->extLst) { 754 (new ConditionalStyles($docSheet, $xmlSheet, $dxfs))->loadFromExt($this->styleReader); 755 } 756 if (isset($xmlSheetMain->sheetViews, $xmlSheetMain->sheetViews->sheetView)) { 757 $sheetViews = new SheetViews($xmlSheetMain->sheetViews->sheetView, $docSheet); 758 $sheetViews->load(); 759 } 760 761 $sheetViewOptions = new SheetViewOptions($docSheet, $xmlSheet); 762 $sheetViewOptions->load($this->getReadDataOnly(), $this->styleReader); 763 764 (new ColumnAndRowAttributes($docSheet, $xmlSheet)) 765 ->load($this->getReadFilter(), $this->getReadDataOnly()); 766 } 767 768 if ($xmlSheetNS && $xmlSheetNS->sheetData && $xmlSheetNS->sheetData->row) { 769 $cIndex = 1; // Cell Start from 1 770 foreach ($xmlSheetNS->sheetData->row as $row) { 771 $rowIndex = 1; 772 foreach ($row->c as $c) { 773 $cAttr = self::getAttributes($c); 774 $r = (string) $cAttr['r']; 775 if ($r == '') { 776 $r = Coordinate::stringFromColumnIndex($rowIndex) . $cIndex; 777 } 778 $cellDataType = (string) $cAttr['t']; 779 $value = null; 780 $calculatedValue = null; 781 782 // Read cell? 783 if ($this->getReadFilter() !== null) { 784 $coordinates = Coordinate::coordinateFromString($r); 785 786 if (!$this->getReadFilter()->readCell($coordinates[0], (int) $coordinates[1], $docSheet->getTitle())) { 787 if (isset($cAttr->f)) { 788 $this->castToFormula($c, $r, $cellDataType, $value, $calculatedValue, $sharedFormulas, 'castToError'); 789 } 790 ++$rowIndex; 791 792 continue; 793 } 794 } 795 796 // Read cell! 797 switch ($cellDataType) { 798 case 's': 799 if ((string) $c->v != '') { 800 $value = $sharedStrings[(int) ($c->v)]; 801 802 if ($value instanceof RichText) { 803 $value = clone $value; 804 } 805 } else { 806 $value = ''; 807 } 808 809 break; 810 case 'b': 811 if (!isset($c->f)) { 812 if (isset($c->v)) { 813 $value = self::castToBoolean($c); 814 } else { 815 $value = null; 816 $cellDataType = DATATYPE::TYPE_NULL; 817 } 818 } else { 819 // Formula 820 $this->castToFormula($c, $r, $cellDataType, $value, $calculatedValue, $sharedFormulas, 'castToBoolean'); 821 if (isset($c->f['t'])) { 822 $att = $c->f; 823 $docSheet->getCell($r)->setFormulaAttributes($att); 824 } 825 } 826 827 break; 828 case 'inlineStr': 829 if (isset($c->f)) { 830 $this->castToFormula($c, $r, $cellDataType, $value, $calculatedValue, $sharedFormulas, 'castToError'); 831 } else { 832 $value = $this->parseRichText($c->is); 833 } 834 835 break; 836 case 'e': 837 if (!isset($c->f)) { 838 $value = self::castToError($c); 839 } else { 840 // Formula 841 $this->castToFormula($c, $r, $cellDataType, $value, $calculatedValue, $sharedFormulas, 'castToError'); 842 } 843 844 break; 845 default: 846 if (!isset($c->f)) { 847 $value = self::castToString($c); 848 } else { 849 // Formula 850 $this->castToFormula($c, $r, $cellDataType, $value, $calculatedValue, $sharedFormulas, 'castToString'); 851 if (isset($c->f['t'])) { 852 $attributes = $c->f['t']; 853 $docSheet->getCell($r)->setFormulaAttributes(['t' => (string) $attributes]); 854 } 855 } 856 857 break; 858 } 859 860 // read empty cells or the cells are not empty 861 if ($this->readEmptyCells || ($value !== null && $value !== '')) { 862 // Rich text? 863 if ($value instanceof RichText && $this->readDataOnly) { 864 $value = $value->getPlainText(); 865 } 866 867 $cell = $docSheet->getCell($r); 868 // Assign value 869 if ($cellDataType != '') { 870 // it is possible, that datatype is numeric but with an empty string, which result in an error 871 if ($cellDataType === DataType::TYPE_NUMERIC && ($value === '' || $value === null)) { 872 $cellDataType = DataType::TYPE_NULL; 873 } 874 if ($cellDataType !== DataType::TYPE_NULL) { 875 $cell->setValueExplicit($value, $cellDataType); 876 } 877 } else { 878 $cell->setValue($value); 879 } 880 if ($calculatedValue !== null) { 881 $cell->setCalculatedValue($calculatedValue); 882 } 883 884 // Style information? 885 if ($cAttr['s'] && !$this->readDataOnly) { 886 // no style index means 0, it seems 887 $cell->setXfIndex(isset($styles[(int) ($cAttr['s'])]) ? 888 (int) ($cAttr['s']) : 0); 889 } 890 } 891 ++$rowIndex; 892 } 893 ++$cIndex; 894 } 895 } 896 897 $aKeys = ['sheet', 'objects', 'scenarios', 'formatCells', 'formatColumns', 'formatRows', 'insertColumns', 'insertRows', 'insertHyperlinks', 'deleteColumns', 'deleteRows', 'selectLockedCells', 'sort', 'autoFilter', 'pivotTables', 'selectUnlockedCells']; 898 if (!$this->readDataOnly && $xmlSheet && $xmlSheet->sheetProtection) { 899 foreach ($aKeys as $key) { 900 $method = 'set' . ucfirst($key); 901 $docSheet->getProtection()->$method(self::boolean((string) $xmlSheet->sheetProtection[$key])); 902 } 903 } 904 905 if ($xmlSheet) { 906 $this->readSheetProtection($docSheet, $xmlSheet); 907 } 908 909 if ($this->readDataOnly === false) { 910 $this->readAutoFilterTables($xmlSheet, $docSheet, $dir, $fileWorksheet, $zip); 911 } 912 913 if ($xmlSheet && $xmlSheet->mergeCells && $xmlSheet->mergeCells->mergeCell && !$this->readDataOnly) { 914 foreach ($xmlSheet->mergeCells->mergeCell as $mergeCell) { 915 $mergeRef = (string) $mergeCell['ref']; 916 if (strpos($mergeRef, ':') !== false) { 917 $docSheet->mergeCells((string) $mergeCell['ref'], Worksheet::MERGE_CELL_CONTENT_HIDE); 918 } 919 } 920 } 921 922 if ($xmlSheet && !$this->readDataOnly) { 923 $unparsedLoadedData = (new PageSetup($docSheet, $xmlSheet))->load($unparsedLoadedData); 924 } 925 926 if ($xmlSheet !== false && isset($xmlSheet->extLst, $xmlSheet->extLst->ext, $xmlSheet->extLst->ext['uri']) && ($xmlSheet->extLst->ext['uri'] == '{CCE6A557-97BC-4b89-ADB6-D9C93CAAB3DF}')) { 927 // Create dataValidations node if does not exists, maybe is better inside the foreach ? 928 if (!$xmlSheet->dataValidations) { 929 $xmlSheet->addChild('dataValidations'); 930 } 931 932 foreach ($xmlSheet->extLst->ext->children('x14', true)->dataValidations->dataValidation as $item) { 933 $node = self::testSimpleXml($xmlSheet->dataValidations)->addChild('dataValidation'); 934 foreach ($item->attributes() ?? [] as $attr) { 935 $node->addAttribute($attr->getName(), $attr); 936 } 937 $node->addAttribute('sqref', $item->children('xm', true)->sqref); 938 $node->addChild('formula1', $item->formula1->children('xm', true)->f); 939 } 940 } 941 942 if ($xmlSheet && $xmlSheet->dataValidations && !$this->readDataOnly) { 943 (new DataValidations($docSheet, $xmlSheet))->load(); 944 } 945 946 // unparsed sheet AlternateContent 947 if ($xmlSheet && !$this->readDataOnly) { 948 $mc = $xmlSheet->children(Namespaces::COMPATIBILITY); 949 if ($mc->AlternateContent) { 950 foreach ($mc->AlternateContent as $alternateContent) { 951 $alternateContent = self::testSimpleXml($alternateContent); 952 $unparsedLoadedData['sheets'][$docSheet->getCodeName()]['AlternateContents'][] = $alternateContent->asXML(); 953 } 954 } 955 } 956 957 // Add hyperlinks 958 if (!$this->readDataOnly) { 959 $hyperlinkReader = new Hyperlinks($docSheet); 960 // Locate hyperlink relations 961 $relationsFileName = dirname("$dir/$fileWorksheet") . '/_rels/' . basename($fileWorksheet) . '.rels'; 962 if ($zip->locateName($relationsFileName)) { 963 $relsWorksheet = $this->loadZip($relationsFileName, Namespaces::RELATIONSHIPS); 964 $hyperlinkReader->readHyperlinks($relsWorksheet); 965 } 966 967 // Loop through hyperlinks 968 if ($xmlSheetNS && $xmlSheetNS->children($mainNS)->hyperlinks) { 969 $hyperlinkReader->setHyperlinks($xmlSheetNS->children($mainNS)->hyperlinks); 970 } 971 } 972 973 // Add comments 974 $comments = []; 975 $vmlComments = []; 976 if (!$this->readDataOnly) { 977 // Locate comment relations 978 $commentRelations = dirname("$dir/$fileWorksheet") . '/_rels/' . basename($fileWorksheet) . '.rels'; 979 if ($zip->locateName($commentRelations)) { 980 $relsWorksheet = $this->loadZip($commentRelations, Namespaces::RELATIONSHIPS); 981 foreach ($relsWorksheet->Relationship as $elex) { 982 $ele = self::getAttributes($elex); 983 if ($ele['Type'] == Namespaces::COMMENTS) { 984 $comments[(string) $ele['Id']] = (string) $ele['Target']; 985 } 986 if ($ele['Type'] == Namespaces::VML) { 987 $vmlComments[(string) $ele['Id']] = (string) $ele['Target']; 988 } 989 } 990 } 991 992 // Loop through comments 993 foreach ($comments as $relName => $relPath) { 994 // Load comments file 995 $relPath = File::realpath(dirname("$dir/$fileWorksheet") . '/' . $relPath); 996 // okay to ignore namespace - using xpath 997 $commentsFile = $this->loadZip($relPath, ''); 998 999 // Utility variables 1000 $authors = []; 1001 $commentsFile->registerXpathNamespace('com', $mainNS); 1002 $authorPath = self::xpathNoFalse($commentsFile, 'com:authors/com:author'); 1003 foreach ($authorPath as $author) { 1004 $authors[] = (string) $author; 1005 } 1006 1007 // Loop through contents 1008 $contentPath = self::xpathNoFalse($commentsFile, 'com:commentList/com:comment'); 1009 foreach ($contentPath as $comment) { 1010 $commentx = $comment->attributes(); 1011 $commentModel = $docSheet->getComment((string) $commentx['ref']); 1012 if (isset($commentx['authorId'])) { 1013 $commentModel->setAuthor($authors[(int) $commentx['authorId']]); 1014 } 1015 $commentModel->setText($this->parseRichText($comment->children($mainNS)->text)); 1016 } 1017 } 1018 1019 // later we will remove from it real vmlComments 1020 $unparsedVmlDrawings = $vmlComments; 1021 1022 // Loop through VML comments 1023 foreach ($vmlComments as $relName => $relPath) { 1024 // Load VML comments file 1025 $relPath = File::realpath(dirname("$dir/$fileWorksheet") . '/' . $relPath); 1026 1027 try { 1028 // no namespace okay - processed with Xpath 1029 $vmlCommentsFile = $this->loadZip($relPath, ''); 1030 $vmlCommentsFile->registerXPathNamespace('v', Namespaces::URN_VML); 1031 } catch (Throwable $ex) { 1032 //Ignore unparsable vmlDrawings. Later they will be moved from $unparsedVmlDrawings to $unparsedLoadedData 1033 continue; 1034 } 1035 1036 // Locate VML drawings image relations 1037 $drowingImages = []; 1038 $VMLDrawingsRelations = dirname($relPath) . '/_rels/' . basename($relPath) . '.rels'; 1039 if ($zip->locateName($VMLDrawingsRelations)) { 1040 $relsVMLDrawing = $this->loadZip($VMLDrawingsRelations, Namespaces::RELATIONSHIPS); 1041 foreach ($relsVMLDrawing->Relationship as $elex) { 1042 $ele = self::getAttributes($elex); 1043 if ($ele['Type'] == Namespaces::IMAGE) { 1044 $drowingImages[(string) $ele['Id']] = (string) $ele['Target']; 1045 } 1046 } 1047 } 1048 1049 $shapes = self::xpathNoFalse($vmlCommentsFile, '//v:shape'); 1050 foreach ($shapes as $shape) { 1051 $shape->registerXPathNamespace('v', Namespaces::URN_VML); 1052 1053 if (isset($shape['style'])) { 1054 $style = (string) $shape['style']; 1055 $fillColor = strtoupper(substr((string) $shape['fillcolor'], 1)); 1056 $column = null; 1057 $row = null; 1058 $fillImageRelId = null; 1059 $fillImageTitle = ''; 1060 1061 $clientData = $shape->xpath('.//x:ClientData'); 1062 if (is_array($clientData) && !empty($clientData)) { 1063 $clientData = $clientData[0]; 1064 1065 if (isset($clientData['ObjectType']) && (string) $clientData['ObjectType'] == 'Note') { 1066 $temp = $clientData->xpath('.//x:Row'); 1067 if (is_array($temp)) { 1068 $row = $temp[0]; 1069 } 1070 1071 $temp = $clientData->xpath('.//x:Column'); 1072 if (is_array($temp)) { 1073 $column = $temp[0]; 1074 } 1075 } 1076 } 1077 1078 $fillImageRelNode = $shape->xpath('.//v:fill/@o:relid'); 1079 if (is_array($fillImageRelNode) && !empty($fillImageRelNode)) { 1080 $fillImageRelNode = $fillImageRelNode[0]; 1081 1082 if (isset($fillImageRelNode['relid'])) { 1083 $fillImageRelId = (string) $fillImageRelNode['relid']; 1084 } 1085 } 1086 1087 $fillImageTitleNode = $shape->xpath('.//v:fill/@o:title'); 1088 if (is_array($fillImageTitleNode) && !empty($fillImageTitleNode)) { 1089 $fillImageTitleNode = $fillImageTitleNode[0]; 1090 1091 if (isset($fillImageTitleNode['title'])) { 1092 $fillImageTitle = (string) $fillImageTitleNode['title']; 1093 } 1094 } 1095 1096 if (($column !== null) && ($row !== null)) { 1097 // Set comment properties 1098 $comment = $docSheet->getCommentByColumnAndRow($column + 1, $row + 1); 1099 $comment->getFillColor()->setRGB($fillColor); 1100 if (isset($drowingImages[$fillImageRelId])) { 1101 $objDrawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing(); 1102 $objDrawing->setName($fillImageTitle); 1103 $imagePath = str_replace('../', 'xl/', $drowingImages[$fillImageRelId]); 1104 $objDrawing->setPath( 1105 'zip://' . File::realpath($filename) . '#' . $imagePath, 1106 true, 1107 $zip 1108 ); 1109 $comment->setBackgroundImage($objDrawing); 1110 } 1111 1112 // Parse style 1113 $styleArray = explode(';', str_replace(' ', '', $style)); 1114 foreach ($styleArray as $stylePair) { 1115 $stylePair = explode(':', $stylePair); 1116 1117 if ($stylePair[0] == 'margin-left') { 1118 $comment->setMarginLeft($stylePair[1]); 1119 } 1120 if ($stylePair[0] == 'margin-top') { 1121 $comment->setMarginTop($stylePair[1]); 1122 } 1123 if ($stylePair[0] == 'width') { 1124 $comment->setWidth($stylePair[1]); 1125 } 1126 if ($stylePair[0] == 'height') { 1127 $comment->setHeight($stylePair[1]); 1128 } 1129 if ($stylePair[0] == 'visibility') { 1130 $comment->setVisible($stylePair[1] == 'visible'); 1131 } 1132 } 1133 1134 unset($unparsedVmlDrawings[$relName]); 1135 } 1136 } 1137 } 1138 } 1139 1140 // unparsed vmlDrawing 1141 if ($unparsedVmlDrawings) { 1142 foreach ($unparsedVmlDrawings as $rId => $relPath) { 1143 $rId = substr($rId, 3); // rIdXXX 1144 $unparsedVmlDrawing = &$unparsedLoadedData['sheets'][$docSheet->getCodeName()]['vmlDrawings']; 1145 $unparsedVmlDrawing[$rId] = []; 1146 $unparsedVmlDrawing[$rId]['filePath'] = self::dirAdd("$dir/$fileWorksheet", $relPath); 1147 $unparsedVmlDrawing[$rId]['relFilePath'] = $relPath; 1148 $unparsedVmlDrawing[$rId]['content'] = $this->securityScanner->scan($this->getFromZipArchive($zip, $unparsedVmlDrawing[$rId]['filePath'])); 1149 unset($unparsedVmlDrawing); 1150 } 1151 } 1152 1153 // Header/footer images 1154 if ($xmlSheet && $xmlSheet->legacyDrawingHF) { 1155 if ($zip->locateName(dirname("$dir/$fileWorksheet") . '/_rels/' . basename($fileWorksheet) . '.rels')) { 1156 $relsWorksheet = $this->loadZipNoNamespace(dirname("$dir/$fileWorksheet") . '/_rels/' . basename($fileWorksheet) . '.rels', Namespaces::RELATIONSHIPS); 1157 $vmlRelationship = ''; 1158 1159 foreach ($relsWorksheet->Relationship as $ele) { 1160 if ($ele['Type'] == Namespaces::VML) { 1161 $vmlRelationship = self::dirAdd("$dir/$fileWorksheet", $ele['Target']); 1162 } 1163 } 1164 1165 if ($vmlRelationship != '') { 1166 // Fetch linked images 1167 $relsVML = $this->loadZipNoNamespace(dirname($vmlRelationship) . '/_rels/' . basename($vmlRelationship) . '.rels', Namespaces::RELATIONSHIPS); 1168 $drawings = []; 1169 if (isset($relsVML->Relationship)) { 1170 foreach ($relsVML->Relationship as $ele) { 1171 if ($ele['Type'] == Namespaces::IMAGE) { 1172 $drawings[(string) $ele['Id']] = self::dirAdd($vmlRelationship, $ele['Target']); 1173 } 1174 } 1175 } 1176 // Fetch VML document 1177 $vmlDrawing = $this->loadZipNoNamespace($vmlRelationship, ''); 1178 $vmlDrawing->registerXPathNamespace('v', Namespaces::URN_VML); 1179 1180 $hfImages = []; 1181 1182 $shapes = self::xpathNoFalse($vmlDrawing, '//v:shape'); 1183 foreach ($shapes as $idx => $shape) { 1184 $shape->registerXPathNamespace('v', Namespaces::URN_VML); 1185 $imageData = $shape->xpath('//v:imagedata'); 1186 1187 if (empty($imageData)) { 1188 continue; 1189 } 1190 1191 $imageData = $imageData[$idx]; 1192 1193 $imageData = self::getAttributes($imageData, Namespaces::URN_MSOFFICE); 1194 $style = self::toCSSArray((string) $shape['style']); 1195 1196 $hfImages[(string) $shape['id']] = new HeaderFooterDrawing(); 1197 if (isset($imageData['title'])) { 1198 $hfImages[(string) $shape['id']]->setName((string) $imageData['title']); 1199 } 1200 1201 $hfImages[(string) $shape['id']]->setPath('zip://' . File::realpath($filename) . '#' . $drawings[(string) $imageData['relid']], false); 1202 $hfImages[(string) $shape['id']]->setResizeProportional(false); 1203 $hfImages[(string) $shape['id']]->setWidth($style['width']); 1204 $hfImages[(string) $shape['id']]->setHeight($style['height']); 1205 if (isset($style['margin-left'])) { 1206 $hfImages[(string) $shape['id']]->setOffsetX($style['margin-left']); 1207 } 1208 $hfImages[(string) $shape['id']]->setOffsetY($style['margin-top']); 1209 $hfImages[(string) $shape['id']]->setResizeProportional(true); 1210 } 1211 1212 $docSheet->getHeaderFooter()->setImages($hfImages); 1213 } 1214 } 1215 } 1216 } 1217 1218 // TODO: Autoshapes from twoCellAnchors! 1219 $drawingFilename = dirname("$dir/$fileWorksheet") 1220 . '/_rels/' 1221 . basename($fileWorksheet) 1222 . '.rels'; 1223 if (substr($drawingFilename, 0, 7) === 'xl//xl/') { 1224 $drawingFilename = substr($drawingFilename, 4); 1225 } 1226 if ($zip->locateName($drawingFilename)) { 1227 $relsWorksheet = $this->loadZipNoNamespace($drawingFilename, Namespaces::RELATIONSHIPS); 1228 $drawings = []; 1229 foreach ($relsWorksheet->Relationship as $ele) { 1230 if ((string) $ele['Type'] === "$xmlNamespaceBase/drawing") { 1231 $eleTarget = (string) $ele['Target']; 1232 if (substr($eleTarget, 0, 4) === '/xl/') { 1233 $drawings[(string) $ele['Id']] = substr($eleTarget, 1); 1234 } else { 1235 $drawings[(string) $ele['Id']] = self::dirAdd("$dir/$fileWorksheet", $ele['Target']); 1236 } 1237 } 1238 } 1239 1240 if ($xmlSheet->drawing && !$this->readDataOnly) { 1241 $unparsedDrawings = []; 1242 $fileDrawing = null; 1243 foreach ($xmlSheet->drawing as $drawing) { 1244 $drawingRelId = (string) self::getArrayItem(self::getAttributes($drawing, $xmlNamespaceBase), 'id'); 1245 $fileDrawing = $drawings[$drawingRelId]; 1246 $drawingFilename = dirname($fileDrawing) . '/_rels/' . basename($fileDrawing) . '.rels'; 1247 $relsDrawing = $this->loadZipNoNamespace($drawingFilename, $xmlNamespaceBase); 1248 1249 $images = []; 1250 $hyperlinks = []; 1251 if ($relsDrawing && $relsDrawing->Relationship) { 1252 foreach ($relsDrawing->Relationship as $ele) { 1253 $eleType = (string) $ele['Type']; 1254 if ($eleType === Namespaces::HYPERLINK) { 1255 $hyperlinks[(string) $ele['Id']] = (string) $ele['Target']; 1256 } 1257 if ($eleType === "$xmlNamespaceBase/image") { 1258 $images[(string) $ele['Id']] = self::dirAdd($fileDrawing, $ele['Target']); 1259 } elseif ($eleType === "$xmlNamespaceBase/chart") { 1260 if ($this->includeCharts) { 1261 $eleTarget = (string) $ele['Target']; 1262 if (substr($eleTarget, 0, 4) === '/xl/') { 1263 $index = substr($eleTarget, 1); 1264 } else { 1265 $index = self::dirAdd($fileDrawing, $eleTarget); 1266 } 1267 $charts[$index] = [ 1268 'id' => (string) $ele['Id'], 1269 'sheet' => $docSheet->getTitle(), 1270 ]; 1271 } 1272 } 1273 } 1274 } 1275 1276 $xmlDrawing = $this->loadZipNoNamespace($fileDrawing, ''); 1277 $xmlDrawingChildren = $xmlDrawing->children(Namespaces::SPREADSHEET_DRAWING); 1278 1279 if ($xmlDrawingChildren->oneCellAnchor) { 1280 foreach ($xmlDrawingChildren->oneCellAnchor as $oneCellAnchor) { 1281 if ($oneCellAnchor->pic->blipFill) { 1282 /** @var SimpleXMLElement $blip */ 1283 $blip = $oneCellAnchor->pic->blipFill->children(Namespaces::DRAWINGML)->blip; 1284 /** @var SimpleXMLElement $xfrm */ 1285 $xfrm = $oneCellAnchor->pic->spPr->children(Namespaces::DRAWINGML)->xfrm; 1286 /** @var SimpleXMLElement $outerShdw */ 1287 $outerShdw = $oneCellAnchor->pic->spPr->children(Namespaces::DRAWINGML)->effectLst->outerShdw; 1288 /** @var SimpleXMLElement $hlinkClick */ 1289 $hlinkClick = $oneCellAnchor->pic->nvPicPr->cNvPr->children(Namespaces::DRAWINGML)->hlinkClick; 1290 1291 $objDrawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing(); 1292 $objDrawing->setName((string) self::getArrayItem(self::getAttributes($oneCellAnchor->pic->nvPicPr->cNvPr), 'name')); 1293 $objDrawing->setDescription((string) self::getArrayItem(self::getAttributes($oneCellAnchor->pic->nvPicPr->cNvPr), 'descr')); 1294 $embedImageKey = (string) self::getArrayItem( 1295 self::getAttributes($blip, $xmlNamespaceBase), 1296 'embed' 1297 ); 1298 if (isset($images[$embedImageKey])) { 1299 $objDrawing->setPath( 1300 'zip://' . File::realpath($filename) . '#' . 1301 $images[$embedImageKey], 1302 false 1303 ); 1304 } else { 1305 $linkImageKey = (string) self::getArrayItem( 1306 $blip->attributes('http://schemas.openxmlformats.org/officeDocument/2006/relationships'), 1307 'link' 1308 ); 1309 if (isset($images[$linkImageKey])) { 1310 $url = str_replace('xl/drawings/', '', $images[$linkImageKey]); 1311 $objDrawing->setPath($url); 1312 } 1313 } 1314 $objDrawing->setCoordinates(Coordinate::stringFromColumnIndex(((int) $oneCellAnchor->from->col) + 1) . ($oneCellAnchor->from->row + 1)); 1315 1316 $objDrawing->setOffsetX((int) Drawing::EMUToPixels($oneCellAnchor->from->colOff)); 1317 $objDrawing->setOffsetY(Drawing::EMUToPixels($oneCellAnchor->from->rowOff)); 1318 $objDrawing->setResizeProportional(false); 1319 $objDrawing->setWidth(Drawing::EMUToPixels(self::getArrayItem(self::getAttributes($oneCellAnchor->ext), 'cx'))); 1320 $objDrawing->setHeight(Drawing::EMUToPixels(self::getArrayItem(self::getAttributes($oneCellAnchor->ext), 'cy'))); 1321 if ($xfrm) { 1322 $objDrawing->setRotation((int) Drawing::angleToDegrees(self::getArrayItem(self::getAttributes($xfrm), 'rot'))); 1323 } 1324 if ($outerShdw) { 1325 $shadow = $objDrawing->getShadow(); 1326 $shadow->setVisible(true); 1327 $shadow->setBlurRadius(Drawing::EMUToPixels(self::getArrayItem(self::getAttributes($outerShdw), 'blurRad'))); 1328 $shadow->setDistance(Drawing::EMUToPixels(self::getArrayItem(self::getAttributes($outerShdw), 'dist'))); 1329 $shadow->setDirection(Drawing::angleToDegrees(self::getArrayItem(self::getAttributes($outerShdw), 'dir'))); 1330 $shadow->setAlignment((string) self::getArrayItem(self::getAttributes($outerShdw), 'algn')); 1331 $clr = $outerShdw->srgbClr ?? $outerShdw->prstClr; 1332 $shadow->getColor()->setRGB(self::getArrayItem(self::getAttributes($clr), 'val')); 1333 $shadow->setAlpha(self::getArrayItem(self::getAttributes($clr->alpha), 'val') / 1000); 1334 } 1335 1336 $this->readHyperLinkDrawing($objDrawing, $oneCellAnchor, $hyperlinks); 1337 1338 $objDrawing->setWorksheet($docSheet); 1339 } elseif ($this->includeCharts && $oneCellAnchor->graphicFrame) { 1340 // Exported XLSX from Google Sheets positions charts with a oneCellAnchor 1341 $coordinates = Coordinate::stringFromColumnIndex(((int) $oneCellAnchor->from->col) + 1) . ($oneCellAnchor->from->row + 1); 1342 $offsetX = Drawing::EMUToPixels($oneCellAnchor->from->colOff); 1343 $offsetY = Drawing::EMUToPixels($oneCellAnchor->from->rowOff); 1344 $width = Drawing::EMUToPixels(self::getArrayItem(self::getAttributes($oneCellAnchor->ext), 'cx')); 1345 $height = Drawing::EMUToPixels(self::getArrayItem(self::getAttributes($oneCellAnchor->ext), 'cy')); 1346 1347 $graphic = $oneCellAnchor->graphicFrame->children(Namespaces::DRAWINGML)->graphic; 1348 /** @var SimpleXMLElement $chartRef */ 1349 $chartRef = $graphic->graphicData->children(Namespaces::CHART)->chart; 1350 $thisChart = (string) self::getAttributes($chartRef, $xmlNamespaceBase); 1351 1352 $chartDetails[$docSheet->getTitle() . '!' . $thisChart] = [ 1353 'fromCoordinate' => $coordinates, 1354 'fromOffsetX' => $offsetX, 1355 'fromOffsetY' => $offsetY, 1356 'width' => $width, 1357 'height' => $height, 1358 'worksheetTitle' => $docSheet->getTitle(), 1359 'oneCellAnchor' => true, 1360 ]; 1361 } 1362 } 1363 } 1364 if ($xmlDrawingChildren->twoCellAnchor) { 1365 foreach ($xmlDrawingChildren->twoCellAnchor as $twoCellAnchor) { 1366 if ($twoCellAnchor->pic->blipFill) { 1367 $blip = $twoCellAnchor->pic->blipFill->children(Namespaces::DRAWINGML)->blip; 1368 $xfrm = $twoCellAnchor->pic->spPr->children(Namespaces::DRAWINGML)->xfrm; 1369 $outerShdw = $twoCellAnchor->pic->spPr->children(Namespaces::DRAWINGML)->effectLst->outerShdw; 1370 $hlinkClick = $twoCellAnchor->pic->nvPicPr->cNvPr->children(Namespaces::DRAWINGML)->hlinkClick; 1371 $objDrawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing(); 1372 /** @scrutinizer ignore-call */ 1373 $editAs = $twoCellAnchor->attributes(); 1374 if (isset($editAs, $editAs['editAs'])) { 1375 $objDrawing->setEditAs($editAs['editAs']); 1376 } 1377 $objDrawing->setName((string) self::getArrayItem(self::getAttributes($twoCellAnchor->pic->nvPicPr->cNvPr), 'name')); 1378 $objDrawing->setDescription((string) self::getArrayItem(self::getAttributes($twoCellAnchor->pic->nvPicPr->cNvPr), 'descr')); 1379 $embedImageKey = (string) self::getArrayItem( 1380 self::getAttributes($blip, $xmlNamespaceBase), 1381 'embed' 1382 ); 1383 if (isset($images[$embedImageKey])) { 1384 $objDrawing->setPath( 1385 'zip://' . File::realpath($filename) . '#' . 1386 $images[$embedImageKey], 1387 false 1388 ); 1389 } else { 1390 $linkImageKey = (string) self::getArrayItem( 1391 $blip->attributes('http://schemas.openxmlformats.org/officeDocument/2006/relationships'), 1392 'link' 1393 ); 1394 if (isset($images[$linkImageKey])) { 1395 $url = str_replace('xl/drawings/', '', $images[$linkImageKey]); 1396 $objDrawing->setPath($url); 1397 } 1398 } 1399 $objDrawing->setCoordinates(Coordinate::stringFromColumnIndex(((int) $twoCellAnchor->from->col) + 1) . ($twoCellAnchor->from->row + 1)); 1400 1401 $objDrawing->setOffsetX(Drawing::EMUToPixels($twoCellAnchor->from->colOff)); 1402 $objDrawing->setOffsetY(Drawing::EMUToPixels($twoCellAnchor->from->rowOff)); 1403 1404 $objDrawing->setCoordinates2(Coordinate::stringFromColumnIndex(((int) $twoCellAnchor->to->col) + 1) . ($twoCellAnchor->to->row + 1)); 1405 1406 $objDrawing->setOffsetX2(Drawing::EMUToPixels($twoCellAnchor->to->colOff)); 1407 $objDrawing->setOffsetY2(Drawing::EMUToPixels($twoCellAnchor->to->rowOff)); 1408 1409 $objDrawing->setResizeProportional(false); 1410 1411 if ($xfrm) { 1412 $objDrawing->setWidth(Drawing::EMUToPixels(self::getArrayItem(self::getAttributes($xfrm->ext), 'cx'))); 1413 $objDrawing->setHeight(Drawing::EMUToPixels(self::getArrayItem(self::getAttributes($xfrm->ext), 'cy'))); 1414 $objDrawing->setRotation(Drawing::angleToDegrees(self::getArrayItem(self::getAttributes($xfrm), 'rot'))); 1415 } 1416 if ($outerShdw) { 1417 $shadow = $objDrawing->getShadow(); 1418 $shadow->setVisible(true); 1419 $shadow->setBlurRadius(Drawing::EMUToPixels(self::getArrayItem(self::getAttributes($outerShdw), 'blurRad'))); 1420 $shadow->setDistance(Drawing::EMUToPixels(self::getArrayItem(self::getAttributes($outerShdw), 'dist'))); 1421 $shadow->setDirection(Drawing::angleToDegrees(self::getArrayItem(self::getAttributes($outerShdw), 'dir'))); 1422 $shadow->setAlignment((string) self::getArrayItem(self::getAttributes($outerShdw), 'algn')); 1423 $clr = $outerShdw->srgbClr ?? $outerShdw->prstClr; 1424 $shadow->getColor()->setRGB(self::getArrayItem(self::getAttributes($clr), 'val')); 1425 $shadow->setAlpha(self::getArrayItem(self::getAttributes($clr->alpha), 'val') / 1000); 1426 } 1427 1428 $this->readHyperLinkDrawing($objDrawing, $twoCellAnchor, $hyperlinks); 1429 1430 $objDrawing->setWorksheet($docSheet); 1431 } elseif (($this->includeCharts) && ($twoCellAnchor->graphicFrame)) { 1432 $fromCoordinate = Coordinate::stringFromColumnIndex(((int) $twoCellAnchor->from->col) + 1) . ($twoCellAnchor->from->row + 1); 1433 $fromOffsetX = Drawing::EMUToPixels($twoCellAnchor->from->colOff); 1434 $fromOffsetY = Drawing::EMUToPixels($twoCellAnchor->from->rowOff); 1435 $toCoordinate = Coordinate::stringFromColumnIndex(((int) $twoCellAnchor->to->col) + 1) . ($twoCellAnchor->to->row + 1); 1436 $toOffsetX = Drawing::EMUToPixels($twoCellAnchor->to->colOff); 1437 $toOffsetY = Drawing::EMUToPixels($twoCellAnchor->to->rowOff); 1438 $graphic = $twoCellAnchor->graphicFrame->children(Namespaces::DRAWINGML)->graphic; 1439 /** @var SimpleXMLElement $chartRef */ 1440 $chartRef = $graphic->graphicData->children(Namespaces::CHART)->chart; 1441 $thisChart = (string) self::getAttributes($chartRef, $xmlNamespaceBase); 1442 1443 $chartDetails[$docSheet->getTitle() . '!' . $thisChart] = [ 1444 'fromCoordinate' => $fromCoordinate, 1445 'fromOffsetX' => $fromOffsetX, 1446 'fromOffsetY' => $fromOffsetY, 1447 'toCoordinate' => $toCoordinate, 1448 'toOffsetX' => $toOffsetX, 1449 'toOffsetY' => $toOffsetY, 1450 'worksheetTitle' => $docSheet->getTitle(), 1451 ]; 1452 } 1453 } 1454 } 1455 if ($xmlDrawingChildren->absoluteAnchor) { 1456 foreach ($xmlDrawingChildren->absoluteAnchor as $absoluteAnchor) { 1457 if (($this->includeCharts) && ($absoluteAnchor->graphicFrame)) { 1458 $graphic = $absoluteAnchor->graphicFrame->children(Namespaces::DRAWINGML)->graphic; 1459 /** @var SimpleXMLElement $chartRef */ 1460 $chartRef = $graphic->graphicData->children(Namespaces::CHART)->chart; 1461 $thisChart = (string) self::getAttributes($chartRef, $xmlNamespaceBase); 1462 $width = Drawing::EMUToPixels((int) self::getArrayItem(self::getAttributes($absoluteAnchor->ext), 'cx')[0]); 1463 $height = Drawing::EMUToPixels((int) self::getArrayItem(self::getAttributes($absoluteAnchor->ext), 'cy')[0]); 1464 1465 $chartDetails[$docSheet->getTitle() . '!' . $thisChart] = [ 1466 'fromCoordinate' => 'A1', 1467 'fromOffsetX' => 0, 1468 'fromOffsetY' => 0, 1469 'width' => $width, 1470 'height' => $height, 1471 'worksheetTitle' => $docSheet->getTitle(), 1472 ]; 1473 } 1474 } 1475 } 1476 if (empty($relsDrawing) && $xmlDrawing->count() == 0) { 1477 // Save Drawing without rels and children as unparsed 1478 $unparsedDrawings[$drawingRelId] = $xmlDrawing->asXML(); 1479 } 1480 } 1481 1482 // store original rId of drawing files 1483 $unparsedLoadedData['sheets'][$docSheet->getCodeName()]['drawingOriginalIds'] = []; 1484 foreach ($relsWorksheet->Relationship as $ele) { 1485 if ((string) $ele['Type'] === "$xmlNamespaceBase/drawing") { 1486 $drawingRelId = (string) $ele['Id']; 1487 $unparsedLoadedData['sheets'][$docSheet->getCodeName()]['drawingOriginalIds'][(string) $ele['Target']] = $drawingRelId; 1488 if (isset($unparsedDrawings[$drawingRelId])) { 1489 $unparsedLoadedData['sheets'][$docSheet->getCodeName()]['Drawings'][$drawingRelId] = $unparsedDrawings[$drawingRelId]; 1490 } 1491 } 1492 } 1493 1494 // unparsed drawing AlternateContent 1495 $xmlAltDrawing = $this->loadZip((string) $fileDrawing, Namespaces::COMPATIBILITY); 1496 1497 if ($xmlAltDrawing->AlternateContent) { 1498 foreach ($xmlAltDrawing->AlternateContent as $alternateContent) { 1499 $alternateContent = self::testSimpleXml($alternateContent); 1500 $unparsedLoadedData['sheets'][$docSheet->getCodeName()]['drawingAlternateContents'][] = $alternateContent->asXML(); 1501 } 1502 } 1503 } 1504 } 1505 1506 $this->readFormControlProperties($excel, $dir, $fileWorksheet, $docSheet, $unparsedLoadedData); 1507 $this->readPrinterSettings($excel, $dir, $fileWorksheet, $docSheet, $unparsedLoadedData); 1508 1509 // Loop through definedNames 1510 if ($xmlWorkbook->definedNames) { 1511 foreach ($xmlWorkbook->definedNames->definedName as $definedName) { 1512 // Extract range 1513 $extractedRange = (string) $definedName; 1514 if (($spos = strpos($extractedRange, '!')) !== false) { 1515 $extractedRange = substr($extractedRange, 0, $spos) . str_replace('$', '', substr($extractedRange, $spos)); 1516 } else { 1517 $extractedRange = str_replace('$', '', $extractedRange); 1518 } 1519 1520 // Valid range? 1521 if ($extractedRange == '') { 1522 continue; 1523 } 1524 1525 // Some definedNames are only applicable if we are on the same sheet... 1526 if ((string) $definedName['localSheetId'] != '' && (string) $definedName['localSheetId'] == $oldSheetId) { 1527 // Switch on type 1528 switch ((string) $definedName['name']) { 1529 case '_xlnm._FilterDatabase': 1530 if ((string) $definedName['hidden'] !== '1') { 1531 $extractedRange = explode(',', $extractedRange); 1532 foreach ($extractedRange as $range) { 1533 $autoFilterRange = $range; 1534 if (strpos($autoFilterRange, ':') !== false) { 1535 $docSheet->getAutoFilter()->setRange($autoFilterRange); 1536 } 1537 } 1538 } 1539 1540 break; 1541 case '_xlnm.Print_Titles': 1542 // Split $extractedRange 1543 $extractedRange = explode(',', $extractedRange); 1544 1545 // Set print titles 1546 foreach ($extractedRange as $range) { 1547 $matches = []; 1548 $range = str_replace('$', '', $range); 1549 1550 // check for repeating columns, e g. 'A:A' or 'A:D' 1551 if (preg_match('/!?([A-Z]+)\:([A-Z]+)$/', $range, $matches)) { 1552 $docSheet->getPageSetup()->setColumnsToRepeatAtLeft([$matches[1], $matches[2]]); 1553 } elseif (preg_match('/!?(\d+)\:(\d+)$/', $range, $matches)) { 1554 // check for repeating rows, e.g. '1:1' or '1:5' 1555 $docSheet->getPageSetup()->setRowsToRepeatAtTop([$matches[1], $matches[2]]); 1556 } 1557 } 1558 1559 break; 1560 case '_xlnm.Print_Area': 1561 $rangeSets = preg_split("/('?(?:.*?)'?(?:![A-Z0-9]+:[A-Z0-9]+)),?/", $extractedRange, -1, PREG_SPLIT_NO_EMPTY | PREG_SPLIT_DELIM_CAPTURE) ?: []; 1562 $newRangeSets = []; 1563 foreach ($rangeSets as $rangeSet) { 1564 [, $rangeSet] = Worksheet::extractSheetTitle($rangeSet, true); 1565 if (empty($rangeSet)) { 1566 continue; 1567 } 1568 if (strpos($rangeSet, ':') === false) { 1569 $rangeSet = $rangeSet . ':' . $rangeSet; 1570 } 1571 $newRangeSets[] = str_replace('$', '', $rangeSet); 1572 } 1573 if (count($newRangeSets) > 0) { 1574 $docSheet->getPageSetup()->setPrintArea(implode(',', $newRangeSets)); 1575 } 1576 1577 break; 1578 default: 1579 break; 1580 } 1581 } 1582 } 1583 } 1584 1585 // Next sheet id 1586 ++$sheetId; 1587 } 1588 1589 // Loop through definedNames 1590 if ($xmlWorkbook->definedNames) { 1591 foreach ($xmlWorkbook->definedNames->definedName as $definedName) { 1592 // Extract range 1593 $extractedRange = (string) $definedName; 1594 1595 // Valid range? 1596 if ($extractedRange == '') { 1597 continue; 1598 } 1599 1600 // Some definedNames are only applicable if we are on the same sheet... 1601 if ((string) $definedName['localSheetId'] != '') { 1602 // Local defined name 1603 // Switch on type 1604 switch ((string) $definedName['name']) { 1605 case '_xlnm._FilterDatabase': 1606 case '_xlnm.Print_Titles': 1607 case '_xlnm.Print_Area': 1608 break; 1609 default: 1610 if ($mapSheetId[(int) $definedName['localSheetId']] !== null) { 1611 $range = Worksheet::extractSheetTitle((string) $definedName, true); 1612 $scope = $excel->getSheet($mapSheetId[(int) $definedName['localSheetId']]); 1613 if (strpos((string) $definedName, '!') !== false) { 1614 $range[0] = str_replace("''", "'", $range[0]); 1615 $range[0] = str_replace("'", '', $range[0]); 1616 if ($worksheet = $excel->getSheetByName($range[0])) { // @phpstan-ignore-line 1617 $excel->addDefinedName(DefinedName::createInstance((string) $definedName['name'], $worksheet, $extractedRange, true, $scope)); 1618 } else { 1619 $excel->addDefinedName(DefinedName::createInstance((string) $definedName['name'], $scope, $extractedRange, true, $scope)); 1620 } 1621 } else { 1622 $excel->addDefinedName(DefinedName::createInstance((string) $definedName['name'], $scope, $extractedRange, true)); 1623 } 1624 } 1625 1626 break; 1627 } 1628 } elseif (!isset($definedName['localSheetId'])) { 1629 $definedRange = (string) $definedName; 1630 // "Global" definedNames 1631 $locatedSheet = null; 1632 if (strpos((string) $definedName, '!') !== false) { 1633 // Modify range, and extract the first worksheet reference 1634 // Need to split on a comma or a space if not in quotes, and extract the first part. 1635 $definedNameValueParts = preg_split("/[ ,](?=([^']*'[^']*')*[^']*$)/miuU", $definedRange); 1636 // Extract sheet name 1637 [$extractedSheetName] = Worksheet::extractSheetTitle((string) $definedNameValueParts[0], true); // @phpstan-ignore-line 1638 $extractedSheetName = trim($extractedSheetName, "'"); 1639 1640 // Locate sheet 1641 $locatedSheet = $excel->getSheetByName($extractedSheetName); 1642 } 1643 1644 if ($locatedSheet === null && !DefinedName::testIfFormula($definedRange)) { 1645 $definedRange = '#REF!'; 1646 } 1647 $excel->addDefinedName(DefinedName::createInstance((string) $definedName['name'], $locatedSheet, $definedRange, false)); 1648 } 1649 } 1650 } 1651 } 1652 1653 (new WorkbookView($excel))->viewSettings($xmlWorkbook, $mainNS, $mapSheetId, $this->readDataOnly); 1654 1655 break; 1656 } 1657 } 1658 1659 if (!$this->readDataOnly) { 1660 $contentTypes = $this->loadZip('[Content_Types].xml'); 1661 1662 // Default content types 1663 foreach ($contentTypes->Default as $contentType) { 1664 switch ($contentType['ContentType']) { 1665 case 'application/vnd.openxmlformats-officedocument.spreadsheetml.printerSettings': 1666 $unparsedLoadedData['default_content_types'][(string) $contentType['Extension']] = (string) $contentType['ContentType']; 1667 1668 break; 1669 } 1670 } 1671 1672 // Override content types 1673 foreach ($contentTypes->Override as $contentType) { 1674 switch ($contentType['ContentType']) { 1675 case 'application/vnd.openxmlformats-officedocument.drawingml.chart+xml': 1676 if ($this->includeCharts) { 1677 $chartEntryRef = ltrim((string) $contentType['PartName'], '/'); 1678 $chartElements = $this->loadZip($chartEntryRef); 1679 $chartReader = new Chart($chartNS, $drawingNS); 1680 $objChart = $chartReader->readChart($chartElements, basename($chartEntryRef, '.xml')); 1681 if (isset($charts[$chartEntryRef])) { 1682 $chartPositionRef = $charts[$chartEntryRef]['sheet'] . '!' . $charts[$chartEntryRef]['id']; 1683 if (isset($chartDetails[$chartPositionRef])) { 1684 $excel->getSheetByName($charts[$chartEntryRef]['sheet'])->addChart($objChart); // @phpstan-ignore-line 1685 $objChart->setWorksheet($excel->getSheetByName($charts[$chartEntryRef]['sheet'])); 1686 // For oneCellAnchor or absoluteAnchor positioned charts, 1687 // toCoordinate is not in the data. Does it need to be calculated? 1688 if (array_key_exists('toCoordinate', $chartDetails[$chartPositionRef])) { 1689 // twoCellAnchor 1690 $objChart->setTopLeftPosition($chartDetails[$chartPositionRef]['fromCoordinate'], $chartDetails[$chartPositionRef]['fromOffsetX'], $chartDetails[$chartPositionRef]['fromOffsetY']); 1691 $objChart->setBottomRightPosition($chartDetails[$chartPositionRef]['toCoordinate'], $chartDetails[$chartPositionRef]['toOffsetX'], $chartDetails[$chartPositionRef]['toOffsetY']); 1692 } else { 1693 // oneCellAnchor or absoluteAnchor (e.g. Chart sheet) 1694 $objChart->setTopLeftPosition($chartDetails[$chartPositionRef]['fromCoordinate'], $chartDetails[$chartPositionRef]['fromOffsetX'], $chartDetails[$chartPositionRef]['fromOffsetY']); 1695 $objChart->setBottomRightPosition('', $chartDetails[$chartPositionRef]['width'], $chartDetails[$chartPositionRef]['height']); 1696 if (array_key_exists('oneCellAnchor', $chartDetails[$chartPositionRef])) { 1697 $objChart->setOneCellAnchor($chartDetails[$chartPositionRef]['oneCellAnchor']); 1698 } 1699 } 1700 } 1701 } 1702 } 1703 1704 break; 1705 1706 // unparsed 1707 case 'application/vnd.ms-excel.controlproperties+xml': 1708 $unparsedLoadedData['override_content_types'][(string) $contentType['PartName']] = (string) $contentType['ContentType']; 1709 1710 break; 1711 } 1712 } 1713 } 1714 1715 $excel->setUnparsedLoadedData($unparsedLoadedData); 1716 1717 $zip->close(); 1718 1719 return $excel; 1720 } 1721 1722 /** 1723 * @return RichText 1724 */ 1725 private function parseRichText(?SimpleXMLElement $is) 1726 { 1727 $value = new RichText(); 1728 1729 if (isset($is->t)) { 1730 $value->createText(StringHelper::controlCharacterOOXML2PHP((string) $is->t)); 1731 } else { 1732 if (is_object($is->r)) { 1733 /** @var SimpleXMLElement $run */ 1734 foreach ($is->r as $run) { 1735 if (!isset($run->rPr)) { 1736 $value->createText(StringHelper::controlCharacterOOXML2PHP((string) $run->t)); 1737 } else { 1738 $objText = $value->createTextRun(StringHelper::controlCharacterOOXML2PHP((string) $run->t)); 1739 $objFont = $objText->getFont() ?? new StyleFont(); 1740 1741 if (isset($run->rPr->rFont)) { 1742 $attr = $run->rPr->rFont->attributes(); 1743 if (isset($attr['val'])) { 1744 $objFont->setName((string) $attr['val']); 1745 } 1746 } 1747 if (isset($run->rPr->sz)) { 1748 $attr = $run->rPr->sz->attributes(); 1749 if (isset($attr['val'])) { 1750 $objFont->setSize((float) $attr['val']); 1751 } 1752 } 1753 if (isset($run->rPr->color)) { 1754 $objFont->setColor(new Color($this->styleReader->readColor($run->rPr->color))); 1755 } 1756 if (isset($run->rPr->b)) { 1757 $attr = $run->rPr->b->attributes(); 1758 if ( 1759 (isset($attr['val']) && self::boolean((string) $attr['val'])) || 1760 (!isset($attr['val'])) 1761 ) { 1762 $objFont->setBold(true); 1763 } 1764 } 1765 if (isset($run->rPr->i)) { 1766 $attr = $run->rPr->i->attributes(); 1767 if ( 1768 (isset($attr['val']) && self::boolean((string) $attr['val'])) || 1769 (!isset($attr['val'])) 1770 ) { 1771 $objFont->setItalic(true); 1772 } 1773 } 1774 if (isset($run->rPr->vertAlign)) { 1775 $attr = $run->rPr->vertAlign->attributes(); 1776 if (isset($attr['val'])) { 1777 $vertAlign = strtolower((string) $attr['val']); 1778 if ($vertAlign == 'superscript') { 1779 $objFont->setSuperscript(true); 1780 } 1781 if ($vertAlign == 'subscript') { 1782 $objFont->setSubscript(true); 1783 } 1784 } 1785 } 1786 if (isset($run->rPr->u)) { 1787 $attr = $run->rPr->u->attributes(); 1788 if (!isset($attr['val'])) { 1789 $objFont->setUnderline(\PhpOffice\PhpSpreadsheet\Style\Font::UNDERLINE_SINGLE); 1790 } else { 1791 $objFont->setUnderline((string) $attr['val']); 1792 } 1793 } 1794 if (isset($run->rPr->strike)) { 1795 $attr = $run->rPr->strike->attributes(); 1796 if ( 1797 (isset($attr['val']) && self::boolean((string) $attr['val'])) || 1798 (!isset($attr['val'])) 1799 ) { 1800 $objFont->setStrikethrough(true); 1801 } 1802 } 1803 } 1804 } 1805 } 1806 } 1807 1808 return $value; 1809 } 1810 1811 private function readRibbon(Spreadsheet $excel, string $customUITarget, ZipArchive $zip): void 1812 { 1813 $baseDir = dirname($customUITarget); 1814 $nameCustomUI = basename($customUITarget); 1815 // get the xml file (ribbon) 1816 $localRibbon = $this->getFromZipArchive($zip, $customUITarget); 1817 $customUIImagesNames = []; 1818 $customUIImagesBinaries = []; 1819 // something like customUI/_rels/customUI.xml.rels 1820 $pathRels = $baseDir . '/_rels/' . $nameCustomUI . '.rels'; 1821 $dataRels = $this->getFromZipArchive($zip, $pathRels); 1822 if ($dataRels) { 1823 // exists and not empty if the ribbon have some pictures (other than internal MSO) 1824 $UIRels = simplexml_load_string( 1825 $this->securityScanner->scan($dataRels), 1826 'SimpleXMLElement', 1827 Settings::getLibXmlLoaderOptions() 1828 ); 1829 if (false !== $UIRels) { 1830 // we need to save id and target to avoid parsing customUI.xml and "guess" if it's a pseudo callback who load the image 1831 foreach ($UIRels->Relationship as $ele) { 1832 if ((string) $ele['Type'] === Namespaces::SCHEMA_OFFICE_DOCUMENT . '/image') { 1833 // an image ? 1834 $customUIImagesNames[(string) $ele['Id']] = (string) $ele['Target']; 1835 $customUIImagesBinaries[(string) $ele['Target']] = $this->getFromZipArchive($zip, $baseDir . '/' . (string) $ele['Target']); 1836 } 1837 } 1838 } 1839 } 1840 if ($localRibbon) { 1841 $excel->setRibbonXMLData($customUITarget, $localRibbon); 1842 if (count($customUIImagesNames) > 0 && count($customUIImagesBinaries) > 0) { 1843 $excel->setRibbonBinObjects($customUIImagesNames, $customUIImagesBinaries); 1844 } else { 1845 $excel->setRibbonBinObjects(null, null); 1846 } 1847 } else { 1848 $excel->setRibbonXMLData(null, null); 1849 $excel->setRibbonBinObjects(null, null); 1850 } 1851 } 1852 1853 /** 1854 * @param null|array|bool|SimpleXMLElement $array 1855 * @param int|string $key 1856 * 1857 * @return mixed 1858 */ 1859 private static function getArrayItem($array, $key = 0) 1860 { 1861 return ($array === null || is_bool($array)) ? null : ($array[$key] ?? null); 1862 } 1863 1864 /** 1865 * @param null|SimpleXMLElement|string $base 1866 * @param null|SimpleXMLElement|string $add 1867 */ 1868 private static function dirAdd($base, $add): string 1869 { 1870 $base = (string) $base; 1871 $add = (string) $add; 1872 1873 return (string) preg_replace('~[^/]+/\.\./~', '', dirname($base) . "/$add"); 1874 } 1875 1876 private static function toCSSArray(string $style): array 1877 { 1878 $style = self::stripWhiteSpaceFromStyleString($style); 1879 1880 $temp = explode(';', $style); 1881 $style = []; 1882 foreach ($temp as $item) { 1883 $item = explode(':', $item); 1884 1885 if (strpos($item[1], 'px') !== false) { 1886 $item[1] = str_replace('px', '', $item[1]); 1887 } 1888 if (strpos($item[1], 'pt') !== false) { 1889 $item[1] = str_replace('pt', '', $item[1]); 1890 $item[1] = (string) Font::fontSizeToPixels((int) $item[1]); 1891 } 1892 if (strpos($item[1], 'in') !== false) { 1893 $item[1] = str_replace('in', '', $item[1]); 1894 $item[1] = (string) Font::inchSizeToPixels((int) $item[1]); 1895 } 1896 if (strpos($item[1], 'cm') !== false) { 1897 $item[1] = str_replace('cm', '', $item[1]); 1898 $item[1] = (string) Font::centimeterSizeToPixels((int) $item[1]); 1899 } 1900 1901 $style[$item[0]] = $item[1]; 1902 } 1903 1904 return $style; 1905 } 1906 1907 public static function stripWhiteSpaceFromStyleString(string $string): string 1908 { 1909 return trim(str_replace(["\r", "\n", ' '], '', $string), ';'); 1910 } 1911 1912 /** 1913 * @param mixed $value 1914 */ 1915 private static function boolean($value): bool 1916 { 1917 if (is_object($value)) { 1918 $value = (string) $value; 1919 } 1920 if (is_numeric($value)) { 1921 return (bool) $value; 1922 } 1923 1924 return $value === 'true' || $value === 'TRUE'; 1925 } 1926 1927 /** 1928 * @param array $hyperlinks 1929 */ 1930 private function readHyperLinkDrawing(\PhpOffice\PhpSpreadsheet\Worksheet\Drawing $objDrawing, SimpleXMLElement $cellAnchor, $hyperlinks): void 1931 { 1932 $hlinkClick = $cellAnchor->pic->nvPicPr->cNvPr->children(Namespaces::DRAWINGML)->hlinkClick; 1933 1934 if ($hlinkClick->count() === 0) { 1935 return; 1936 } 1937 1938 $hlinkId = (string) self::getAttributes($hlinkClick, Namespaces::SCHEMA_OFFICE_DOCUMENT)['id']; 1939 $hyperlink = new Hyperlink( 1940 $hyperlinks[$hlinkId], 1941 (string) self::getArrayItem(self::getAttributes($cellAnchor->pic->nvPicPr->cNvPr), 'name') 1942 ); 1943 $objDrawing->setHyperlink($hyperlink); 1944 } 1945 1946 private function readProtection(Spreadsheet $excel, SimpleXMLElement $xmlWorkbook): void 1947 { 1948 if (!$xmlWorkbook->workbookProtection) { 1949 return; 1950 } 1951 1952 $excel->getSecurity()->setLockRevision(self::getLockValue($xmlWorkbook->workbookProtection, 'lockRevision')); 1953 $excel->getSecurity()->setLockStructure(self::getLockValue($xmlWorkbook->workbookProtection, 'lockStructure')); 1954 $excel->getSecurity()->setLockWindows(self::getLockValue($xmlWorkbook->workbookProtection, 'lockWindows')); 1955 1956 if ($xmlWorkbook->workbookProtection['revisionsPassword']) { 1957 $excel->getSecurity()->setRevisionsPassword( 1958 (string) $xmlWorkbook->workbookProtection['revisionsPassword'], 1959 true 1960 ); 1961 } 1962 1963 if ($xmlWorkbook->workbookProtection['workbookPassword']) { 1964 $excel->getSecurity()->setWorkbookPassword( 1965 (string) $xmlWorkbook->workbookProtection['workbookPassword'], 1966 true 1967 ); 1968 } 1969 } 1970 1971 private static function getLockValue(SimpleXmlElement $protection, string $key): ?bool 1972 { 1973 $returnValue = null; 1974 $protectKey = $protection[$key]; 1975 if (!empty($protectKey)) { 1976 $protectKey = (string) $protectKey; 1977 $returnValue = $protectKey !== 'false' && (bool) $protectKey; 1978 } 1979 1980 return $returnValue; 1981 } 1982 1983 private function readFormControlProperties(Spreadsheet $excel, string $dir, string $fileWorksheet, Worksheet $docSheet, array &$unparsedLoadedData): void 1984 { 1985 $zip = $this->zip; 1986 if (!$zip->locateName(dirname("$dir/$fileWorksheet") . '/_rels/' . basename($fileWorksheet) . '.rels')) { 1987 return; 1988 } 1989 1990 $filename = dirname("$dir/$fileWorksheet") . '/_rels/' . basename($fileWorksheet) . '.rels'; 1991 $relsWorksheet = $this->loadZipNoNamespace($filename, Namespaces::RELATIONSHIPS); 1992 $ctrlProps = []; 1993 foreach ($relsWorksheet->Relationship as $ele) { 1994 if ((string) $ele['Type'] === Namespaces::SCHEMA_OFFICE_DOCUMENT . '/ctrlProp') { 1995 $ctrlProps[(string) $ele['Id']] = $ele; 1996 } 1997 } 1998 1999 $unparsedCtrlProps = &$unparsedLoadedData['sheets'][$docSheet->getCodeName()]['ctrlProps']; 2000 foreach ($ctrlProps as $rId => $ctrlProp) { 2001 $rId = substr($rId, 3); // rIdXXX 2002 $unparsedCtrlProps[$rId] = []; 2003 $unparsedCtrlProps[$rId]['filePath'] = self::dirAdd("$dir/$fileWorksheet", $ctrlProp['Target']); 2004 $unparsedCtrlProps[$rId]['relFilePath'] = (string) $ctrlProp['Target']; 2005 $unparsedCtrlProps[$rId]['content'] = $this->securityScanner->scan($this->getFromZipArchive($zip, $unparsedCtrlProps[$rId]['filePath'])); 2006 } 2007 unset($unparsedCtrlProps); 2008 } 2009 2010 private function readPrinterSettings(Spreadsheet $excel, string $dir, string $fileWorksheet, Worksheet $docSheet, array &$unparsedLoadedData): void 2011 { 2012 $zip = $this->zip; 2013 if (!$zip->locateName(dirname("$dir/$fileWorksheet") . '/_rels/' . basename($fileWorksheet) . '.rels')) { 2014 return; 2015 } 2016 2017 $filename = dirname("$dir/$fileWorksheet") . '/_rels/' . basename($fileWorksheet) . '.rels'; 2018 $relsWorksheet = $this->loadZipNoNamespace($filename, Namespaces::RELATIONSHIPS); 2019 $sheetPrinterSettings = []; 2020 foreach ($relsWorksheet->Relationship as $ele) { 2021 if ((string) $ele['Type'] === Namespaces::SCHEMA_OFFICE_DOCUMENT . '/printerSettings') { 2022 $sheetPrinterSettings[(string) $ele['Id']] = $ele; 2023 } 2024 } 2025 2026 $unparsedPrinterSettings = &$unparsedLoadedData['sheets'][$docSheet->getCodeName()]['printerSettings']; 2027 foreach ($sheetPrinterSettings as $rId => $printerSettings) { 2028 $rId = substr($rId, 3) . 'ps'; // rIdXXX, add 'ps' suffix to avoid identical resource identifier collision with unparsed vmlDrawing 2029 $unparsedPrinterSettings[$rId] = []; 2030 $unparsedPrinterSettings[$rId]['filePath'] = self::dirAdd("$dir/$fileWorksheet", $printerSettings['Target']); 2031 $unparsedPrinterSettings[$rId]['relFilePath'] = (string) $printerSettings['Target']; 2032 $unparsedPrinterSettings[$rId]['content'] = $this->securityScanner->scan($this->getFromZipArchive($zip, $unparsedPrinterSettings[$rId]['filePath'])); 2033 } 2034 unset($unparsedPrinterSettings); 2035 } 2036 2037 private function getWorkbookBaseName(): array 2038 { 2039 $workbookBasename = ''; 2040 $xmlNamespaceBase = ''; 2041 2042 // check if it is an OOXML archive 2043 $rels = $this->loadZip(self::INITIAL_FILE); 2044 foreach ($rels->children(Namespaces::RELATIONSHIPS)->Relationship as $rel) { 2045 $rel = self::getAttributes($rel); 2046 $type = (string) $rel['Type']; 2047 switch ($type) { 2048 case Namespaces::OFFICE_DOCUMENT: 2049 case Namespaces::PURL_OFFICE_DOCUMENT: 2050 $basename = basename((string) $rel['Target']); 2051 $xmlNamespaceBase = dirname($type); 2052 if (preg_match('/workbook.*\.xml/', $basename)) { 2053 $workbookBasename = $basename; 2054 } 2055 2056 break; 2057 } 2058 } 2059 2060 return [$workbookBasename, $xmlNamespaceBase]; 2061 } 2062 2063 private function readSheetProtection(Worksheet $docSheet, SimpleXMLElement $xmlSheet): void 2064 { 2065 if ($this->readDataOnly || !$xmlSheet->sheetProtection) { 2066 return; 2067 } 2068 2069 $algorithmName = (string) $xmlSheet->sheetProtection['algorithmName']; 2070 $protection = $docSheet->getProtection(); 2071 $protection->setAlgorithm($algorithmName); 2072 2073 if ($algorithmName) { 2074 $protection->setPassword((string) $xmlSheet->sheetProtection['hashValue'], true); 2075 $protection->setSalt((string) $xmlSheet->sheetProtection['saltValue']); 2076 $protection->setSpinCount((int) $xmlSheet->sheetProtection['spinCount']); 2077 } else { 2078 $protection->setPassword((string) $xmlSheet->sheetProtection['password'], true); 2079 } 2080 2081 if ($xmlSheet->protectedRanges->protectedRange) { 2082 foreach ($xmlSheet->protectedRanges->protectedRange as $protectedRange) { 2083 $docSheet->protectCells((string) $protectedRange['sqref'], (string) $protectedRange['password'], true); 2084 } 2085 } 2086 } 2087 2088 private function readAutoFilterTables( 2089 SimpleXMLElement $xmlSheet, 2090 Worksheet $docSheet, 2091 string $dir, 2092 string $fileWorksheet, 2093 ZipArchive $zip 2094 ): void { 2095 if ($xmlSheet && $xmlSheet->autoFilter) { 2096 // In older files, autofilter structure is defined in the worksheet file 2097 (new AutoFilter($docSheet, $xmlSheet))->load(); 2098 } elseif ($xmlSheet && $xmlSheet->tableParts && (int) $xmlSheet->tableParts['count'] > 0) { 2099 // But for Office365, MS decided to make it all just a bit more complicated 2100 $this->readAutoFilterTablesInTablesFile($xmlSheet, $dir, $fileWorksheet, $zip, $docSheet); 2101 } 2102 } 2103 2104 private function readAutoFilterTablesInTablesFile( 2105 SimpleXMLElement $xmlSheet, 2106 string $dir, 2107 string $fileWorksheet, 2108 ZipArchive $zip, 2109 Worksheet $docSheet 2110 ): void { 2111 foreach ($xmlSheet->tableParts->tablePart as $tablePart) { 2112 $relation = self::getAttributes($tablePart, Namespaces::SCHEMA_OFFICE_DOCUMENT); 2113 $tablePartRel = (string) $relation['id']; 2114 $relationsFileName = dirname("$dir/$fileWorksheet") . '/_rels/' . basename($fileWorksheet) . '.rels'; 2115 2116 if ($zip->locateName($relationsFileName)) { 2117 $relsTableReferences = $this->loadZip($relationsFileName, Namespaces::RELATIONSHIPS); 2118 foreach ($relsTableReferences->Relationship as $relationship) { 2119 $relationshipAttributes = self::getAttributes($relationship, ''); 2120 2121 if ((string) $relationshipAttributes['Id'] === $tablePartRel) { 2122 $relationshipFileName = (string) $relationshipAttributes['Target']; 2123 $relationshipFilePath = dirname("$dir/$fileWorksheet") . '/' . $relationshipFileName; 2124 $relationshipFilePath = File::realpath($relationshipFilePath); 2125 2126 if ($this->fileExistsInArchive($this->zip, $relationshipFilePath)) { 2127 $autoFilter = $this->loadZip($relationshipFilePath); 2128 (new AutoFilter($docSheet, $autoFilter))->load(); 2129 } 2130 } 2131 } 2132 } 2133 } 2134 } 2135 2136 private static function extractStyles(?SimpleXMLElement $sxml, string $node1, string $node2): array 2137 { 2138 $array = []; 2139 if ($sxml && $sxml->{$node1}->{$node2}) { 2140 foreach ($sxml->{$node1}->{$node2} as $node) { 2141 $array[] = $node; 2142 } 2143 } 2144 2145 return $array; 2146 } 2147 2148 private static function extractPalette(?SimpleXMLElement $sxml): array 2149 { 2150 $array = []; 2151 if ($sxml && $sxml->colors->indexedColors) { 2152 foreach ($sxml->colors->indexedColors->rgbColor as $node) { 2153 if ($node !== null) { 2154 $attr = $node->attributes(); 2155 if (isset($attr['rgb'])) { 2156 $array[] = (string) $attr['rgb']; 2157 } 2158 } 2159 } 2160 } 2161 2162 return (count($array) === 64) ? $array : []; 2163 } 2164 }
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
