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