1<?php 2 3namespace PhpOffice\PhpSpreadsheet\Reader; 4 5use PhpOffice\PhpSpreadsheet\Cell\AddressHelper; 6use PhpOffice\PhpSpreadsheet\Cell\Coordinate; 7use PhpOffice\PhpSpreadsheet\Cell\DataType; 8use PhpOffice\PhpSpreadsheet\DefinedName; 9use PhpOffice\PhpSpreadsheet\Document\Properties; 10use PhpOffice\PhpSpreadsheet\Reader\Security\XmlScanner; 11use PhpOffice\PhpSpreadsheet\Reader\Xml\PageSettings; 12use PhpOffice\PhpSpreadsheet\RichText\RichText; 13use PhpOffice\PhpSpreadsheet\Settings; 14use PhpOffice\PhpSpreadsheet\Shared\Date; 15use PhpOffice\PhpSpreadsheet\Shared\File; 16use PhpOffice\PhpSpreadsheet\Shared\StringHelper; 17use PhpOffice\PhpSpreadsheet\Spreadsheet; 18use PhpOffice\PhpSpreadsheet\Style\Alignment; 19use PhpOffice\PhpSpreadsheet\Style\Border; 20use PhpOffice\PhpSpreadsheet\Style\Borders; 21use PhpOffice\PhpSpreadsheet\Style\Fill; 22use PhpOffice\PhpSpreadsheet\Style\Font; 23use SimpleXMLElement; 24 25/** 26 * Reader for SpreadsheetML, the XML schema for Microsoft Office Excel 2003. 27 */ 28class Xml extends BaseReader 29{ 30 /** 31 * Formats. 32 * 33 * @var array 34 */ 35 protected $styles = []; 36 37 /** 38 * Create a new Excel2003XML Reader instance. 39 */ 40 public function __construct() 41 { 42 parent::__construct(); 43 $this->securityScanner = XmlScanner::getInstance($this); 44 } 45 46 private $fileContents = ''; 47 48 private static $mappings = [ 49 'borderStyle' => [ 50 '1continuous' => Border::BORDER_THIN, 51 '1dash' => Border::BORDER_DASHED, 52 '1dashdot' => Border::BORDER_DASHDOT, 53 '1dashdotdot' => Border::BORDER_DASHDOTDOT, 54 '1dot' => Border::BORDER_DOTTED, 55 '1double' => Border::BORDER_DOUBLE, 56 '2continuous' => Border::BORDER_MEDIUM, 57 '2dash' => Border::BORDER_MEDIUMDASHED, 58 '2dashdot' => Border::BORDER_MEDIUMDASHDOT, 59 '2dashdotdot' => Border::BORDER_MEDIUMDASHDOTDOT, 60 '2dot' => Border::BORDER_DOTTED, 61 '2double' => Border::BORDER_DOUBLE, 62 '3continuous' => Border::BORDER_THICK, 63 '3dash' => Border::BORDER_MEDIUMDASHED, 64 '3dashdot' => Border::BORDER_MEDIUMDASHDOT, 65 '3dashdotdot' => Border::BORDER_MEDIUMDASHDOTDOT, 66 '3dot' => Border::BORDER_DOTTED, 67 '3double' => Border::BORDER_DOUBLE, 68 ], 69 'fillType' => [ 70 'solid' => Fill::FILL_SOLID, 71 'gray75' => Fill::FILL_PATTERN_DARKGRAY, 72 'gray50' => Fill::FILL_PATTERN_MEDIUMGRAY, 73 'gray25' => Fill::FILL_PATTERN_LIGHTGRAY, 74 'gray125' => Fill::FILL_PATTERN_GRAY125, 75 'gray0625' => Fill::FILL_PATTERN_GRAY0625, 76 'horzstripe' => Fill::FILL_PATTERN_DARKHORIZONTAL, // horizontal stripe 77 'vertstripe' => Fill::FILL_PATTERN_DARKVERTICAL, // vertical stripe 78 'reversediagstripe' => Fill::FILL_PATTERN_DARKUP, // reverse diagonal stripe 79 'diagstripe' => Fill::FILL_PATTERN_DARKDOWN, // diagonal stripe 80 'diagcross' => Fill::FILL_PATTERN_DARKGRID, // diagoanl crosshatch 81 'thickdiagcross' => Fill::FILL_PATTERN_DARKTRELLIS, // thick diagonal crosshatch 82 'thinhorzstripe' => Fill::FILL_PATTERN_LIGHTHORIZONTAL, 83 'thinvertstripe' => Fill::FILL_PATTERN_LIGHTVERTICAL, 84 'thinreversediagstripe' => Fill::FILL_PATTERN_LIGHTUP, 85 'thindiagstripe' => Fill::FILL_PATTERN_LIGHTDOWN, 86 'thinhorzcross' => Fill::FILL_PATTERN_LIGHTGRID, // thin horizontal crosshatch 87 'thindiagcross' => Fill::FILL_PATTERN_LIGHTTRELLIS, // thin diagonal crosshatch 88 ], 89 ]; 90 91 public static function xmlMappings(): array 92 { 93 return self::$mappings; 94 } 95 96 /** 97 * Can the current IReader read the file? 98 * 99 * @param string $pFilename 100 * 101 * @return bool 102 */ 103 public function canRead($pFilename) 104 { 105 // Office xmlns:o="urn:schemas-microsoft-com:office:office" 106 // Excel xmlns:x="urn:schemas-microsoft-com:office:excel" 107 // XML Spreadsheet xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" 108 // Spreadsheet component xmlns:c="urn:schemas-microsoft-com:office:component:spreadsheet" 109 // XML schema xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" 110 // XML data type xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" 111 // MS-persist recordset xmlns:rs="urn:schemas-microsoft-com:rowset" 112 // Rowset xmlns:z="#RowsetSchema" 113 // 114 115 $signature = [ 116 '<?xml version="1.0"', 117 '<?mso-application progid="Excel.Sheet"?>', 118 ]; 119 120 // Open file 121 $data = file_get_contents($pFilename); 122 123 // Why? 124 //$data = str_replace("'", '"', $data); // fix headers with single quote 125 126 $valid = true; 127 foreach ($signature as $match) { 128 // every part of the signature must be present 129 if (strpos($data, $match) === false) { 130 $valid = false; 131 132 break; 133 } 134 } 135 136 // Retrieve charset encoding 137 if (preg_match('/<?xml.*encoding=[\'"](.*?)[\'"].*?>/m', $data, $matches)) { 138 $charSet = strtoupper($matches[1]); 139 if (1 == preg_match('/^ISO-8859-\d[\dL]?$/i', $charSet)) { 140 $data = StringHelper::convertEncoding($data, 'UTF-8', $charSet); 141 $data = preg_replace('/(<?xml.*encoding=[\'"]).*?([\'"].*?>)/um', '$1' . 'UTF-8' . '$2', $data, 1); 142 } 143 } 144 $this->fileContents = $data; 145 146 return $valid; 147 } 148 149 /** 150 * Check if the file is a valid SimpleXML. 151 * 152 * @param string $pFilename 153 * 154 * @return false|SimpleXMLElement 155 */ 156 public function trySimpleXMLLoadString($pFilename) 157 { 158 try { 159 $xml = simplexml_load_string( 160 $this->securityScanner->scan($this->fileContents ?: file_get_contents($pFilename)), 161 'SimpleXMLElement', 162 Settings::getLibXmlLoaderOptions() 163 ); 164 } catch (\Exception $e) { 165 throw new Exception('Cannot load invalid XML file: ' . $pFilename, 0, $e); 166 } 167 $this->fileContents = ''; 168 169 return $xml; 170 } 171 172 /** 173 * Reads names of the worksheets from a file, without parsing the whole file to a Spreadsheet object. 174 * 175 * @param string $pFilename 176 * 177 * @return array 178 */ 179 public function listWorksheetNames($pFilename) 180 { 181 File::assertFile($pFilename); 182 if (!$this->canRead($pFilename)) { 183 throw new Exception($pFilename . ' is an Invalid Spreadsheet file.'); 184 } 185 186 $worksheetNames = []; 187 188 $xml = $this->trySimpleXMLLoadString($pFilename); 189 190 $namespaces = $xml->getNamespaces(true); 191 192 $xml_ss = $xml->children($namespaces['ss']); 193 foreach ($xml_ss->Worksheet as $worksheet) { 194 $worksheet_ss = $worksheet->attributes($namespaces['ss']); 195 $worksheetNames[] = (string) $worksheet_ss['Name']; 196 } 197 198 return $worksheetNames; 199 } 200 201 /** 202 * Return worksheet info (Name, Last Column Letter, Last Column Index, Total Rows, Total Columns). 203 * 204 * @param string $pFilename 205 * 206 * @return array 207 */ 208 public function listWorksheetInfo($pFilename) 209 { 210 File::assertFile($pFilename); 211 if (!$this->canRead($pFilename)) { 212 throw new Exception($pFilename . ' is an Invalid Spreadsheet file.'); 213 } 214 215 $worksheetInfo = []; 216 217 $xml = $this->trySimpleXMLLoadString($pFilename); 218 219 $namespaces = $xml->getNamespaces(true); 220 221 $worksheetID = 1; 222 $xml_ss = $xml->children($namespaces['ss']); 223 foreach ($xml_ss->Worksheet as $worksheet) { 224 $worksheet_ss = $worksheet->attributes($namespaces['ss']); 225 226 $tmpInfo = []; 227 $tmpInfo['worksheetName'] = ''; 228 $tmpInfo['lastColumnLetter'] = 'A'; 229 $tmpInfo['lastColumnIndex'] = 0; 230 $tmpInfo['totalRows'] = 0; 231 $tmpInfo['totalColumns'] = 0; 232 233 $tmpInfo['worksheetName'] = "Worksheet_{$worksheetID}"; 234 if (isset($worksheet_ss['Name'])) { 235 $tmpInfo['worksheetName'] = (string) $worksheet_ss['Name']; 236 } 237 238 if (isset($worksheet->Table->Row)) { 239 $rowIndex = 0; 240 241 foreach ($worksheet->Table->Row as $rowData) { 242 $columnIndex = 0; 243 $rowHasData = false; 244 245 foreach ($rowData->Cell as $cell) { 246 if (isset($cell->Data)) { 247 $tmpInfo['lastColumnIndex'] = max($tmpInfo['lastColumnIndex'], $columnIndex); 248 $rowHasData = true; 249 } 250 251 ++$columnIndex; 252 } 253 254 ++$rowIndex; 255 256 if ($rowHasData) { 257 $tmpInfo['totalRows'] = max($tmpInfo['totalRows'], $rowIndex); 258 } 259 } 260 } 261 262 $tmpInfo['lastColumnLetter'] = Coordinate::stringFromColumnIndex($tmpInfo['lastColumnIndex'] + 1); 263 $tmpInfo['totalColumns'] = $tmpInfo['lastColumnIndex'] + 1; 264 265 $worksheetInfo[] = $tmpInfo; 266 ++$worksheetID; 267 } 268 269 return $worksheetInfo; 270 } 271 272 /** 273 * Loads Spreadsheet from file. 274 * 275 * @param string $pFilename 276 * 277 * @return Spreadsheet 278 */ 279 public function load($pFilename) 280 { 281 // Create new Spreadsheet 282 $spreadsheet = new Spreadsheet(); 283 $spreadsheet->removeSheetByIndex(0); 284 285 // Load into this instance 286 return $this->loadIntoExisting($pFilename, $spreadsheet); 287 } 288 289 private static function identifyFixedStyleValue($styleList, &$styleAttributeValue) 290 { 291 $returnValue = false; 292 $styleAttributeValue = strtolower($styleAttributeValue); 293 foreach ($styleList as $style) { 294 if ($styleAttributeValue == strtolower($style)) { 295 $styleAttributeValue = $style; 296 $returnValue = true; 297 298 break; 299 } 300 } 301 302 return $returnValue; 303 } 304 305 protected static function hex2str($hex) 306 { 307 return mb_chr((int) hexdec($hex[1]), 'UTF-8'); 308 } 309 310 /** 311 * Loads from file into Spreadsheet instance. 312 * 313 * @param string $pFilename 314 * 315 * @return Spreadsheet 316 */ 317 public function loadIntoExisting($pFilename, Spreadsheet $spreadsheet) 318 { 319 File::assertFile($pFilename); 320 if (!$this->canRead($pFilename)) { 321 throw new Exception($pFilename . ' is an Invalid Spreadsheet file.'); 322 } 323 324 $xml = $this->trySimpleXMLLoadString($pFilename); 325 326 $namespaces = $xml->getNamespaces(true); 327 328 $docProps = $spreadsheet->getProperties(); 329 if (isset($xml->DocumentProperties[0])) { 330 foreach ($xml->DocumentProperties[0] as $propertyName => $propertyValue) { 331 $stringValue = (string) $propertyValue; 332 switch ($propertyName) { 333 case 'Title': 334 $docProps->setTitle($stringValue); 335 336 break; 337 case 'Subject': 338 $docProps->setSubject($stringValue); 339 340 break; 341 case 'Author': 342 $docProps->setCreator($stringValue); 343 344 break; 345 case 'Created': 346 $creationDate = strtotime($stringValue); 347 $docProps->setCreated($creationDate); 348 349 break; 350 case 'LastAuthor': 351 $docProps->setLastModifiedBy($stringValue); 352 353 break; 354 case 'LastSaved': 355 $lastSaveDate = strtotime($stringValue); 356 $docProps->setModified($lastSaveDate); 357 358 break; 359 case 'Company': 360 $docProps->setCompany($stringValue); 361 362 break; 363 case 'Category': 364 $docProps->setCategory($stringValue); 365 366 break; 367 case 'Manager': 368 $docProps->setManager($stringValue); 369 370 break; 371 case 'Keywords': 372 $docProps->setKeywords($stringValue); 373 374 break; 375 case 'Description': 376 $docProps->setDescription($stringValue); 377 378 break; 379 } 380 } 381 } 382 if (isset($xml->CustomDocumentProperties)) { 383 foreach ($xml->CustomDocumentProperties[0] as $propertyName => $propertyValue) { 384 $propertyAttributes = $propertyValue->attributes($namespaces['dt']); 385 $propertyName = preg_replace_callback('/_x([0-9a-f]{4})_/i', ['self', 'hex2str'], $propertyName); 386 $propertyType = Properties::PROPERTY_TYPE_UNKNOWN; 387 switch ((string) $propertyAttributes) { 388 case 'string': 389 $propertyType = Properties::PROPERTY_TYPE_STRING; 390 $propertyValue = trim($propertyValue); 391 392 break; 393 case 'boolean': 394 $propertyType = Properties::PROPERTY_TYPE_BOOLEAN; 395 $propertyValue = (bool) $propertyValue; 396 397 break; 398 case 'integer': 399 $propertyType = Properties::PROPERTY_TYPE_INTEGER; 400 $propertyValue = (int) $propertyValue; 401 402 break; 403 case 'float': 404 $propertyType = Properties::PROPERTY_TYPE_FLOAT; 405 $propertyValue = (float) $propertyValue; 406 407 break; 408 case 'dateTime.tz': 409 $propertyType = Properties::PROPERTY_TYPE_DATE; 410 $propertyValue = strtotime(trim($propertyValue)); 411 412 break; 413 } 414 $docProps->setCustomProperty($propertyName, $propertyValue, $propertyType); 415 } 416 } 417 418 $this->parseStyles($xml, $namespaces); 419 420 $worksheetID = 0; 421 $xml_ss = $xml->children($namespaces['ss']); 422 423 foreach ($xml_ss->Worksheet as $worksheet) { 424 $worksheet_ss = $worksheet->attributes($namespaces['ss']); 425 426 if ( 427 (isset($this->loadSheetsOnly)) && (isset($worksheet_ss['Name'])) && 428 (!in_array($worksheet_ss['Name'], $this->loadSheetsOnly)) 429 ) { 430 continue; 431 } 432 433 // Create new Worksheet 434 $spreadsheet->createSheet(); 435 $spreadsheet->setActiveSheetIndex($worksheetID); 436 if (isset($worksheet_ss['Name'])) { 437 $worksheetName = (string) $worksheet_ss['Name']; 438 // Use false for $updateFormulaCellReferences to prevent adjustment of worksheet references in 439 // formula cells... during the load, all formulae should be correct, and we're simply bringing 440 // the worksheet name in line with the formula, not the reverse 441 $spreadsheet->getActiveSheet()->setTitle($worksheetName, false, false); 442 } 443 444 // locally scoped defined names 445 if (isset($worksheet->Names[0])) { 446 foreach ($worksheet->Names[0] as $definedName) { 447 $definedName_ss = $definedName->attributes($namespaces['ss']); 448 $name = (string) $definedName_ss['Name']; 449 $definedValue = (string) $definedName_ss['RefersTo']; 450 $convertedValue = AddressHelper::convertFormulaToA1($definedValue); 451 if ($convertedValue[0] === '=') { 452 $convertedValue = substr($convertedValue, 1); 453 } 454 $spreadsheet->addDefinedName(DefinedName::createInstance($name, $spreadsheet->getActiveSheet(), $convertedValue, true)); 455 } 456 } 457 458 $columnID = 'A'; 459 if (isset($worksheet->Table->Column)) { 460 foreach ($worksheet->Table->Column as $columnData) { 461 $columnData_ss = $columnData->attributes($namespaces['ss']); 462 if (isset($columnData_ss['Index'])) { 463 $columnID = Coordinate::stringFromColumnIndex((int) $columnData_ss['Index']); 464 } 465 if (isset($columnData_ss['Width'])) { 466 $columnWidth = $columnData_ss['Width']; 467 $spreadsheet->getActiveSheet()->getColumnDimension($columnID)->setWidth($columnWidth / 5.4); 468 } 469 ++$columnID; 470 } 471 } 472 473 $rowID = 1; 474 if (isset($worksheet->Table->Row)) { 475 $additionalMergedCells = 0; 476 foreach ($worksheet->Table->Row as $rowData) { 477 $rowHasData = false; 478 $row_ss = $rowData->attributes($namespaces['ss']); 479 if (isset($row_ss['Index'])) { 480 $rowID = (int) $row_ss['Index']; 481 } 482 483 $columnID = 'A'; 484 foreach ($rowData->Cell as $cell) { 485 $cell_ss = $cell->attributes($namespaces['ss']); 486 if (isset($cell_ss['Index'])) { 487 $columnID = Coordinate::stringFromColumnIndex((int) $cell_ss['Index']); 488 } 489 $cellRange = $columnID . $rowID; 490 491 if ($this->getReadFilter() !== null) { 492 if (!$this->getReadFilter()->readCell($columnID, $rowID, $worksheetName)) { 493 ++$columnID; 494 495 continue; 496 } 497 } 498 499 if (isset($cell_ss['HRef'])) { 500 $spreadsheet->getActiveSheet()->getCell($cellRange)->getHyperlink()->setUrl((string) $cell_ss['HRef']); 501 } 502 503 if ((isset($cell_ss['MergeAcross'])) || (isset($cell_ss['MergeDown']))) { 504 $columnTo = $columnID; 505 if (isset($cell_ss['MergeAcross'])) { 506 $additionalMergedCells += (int) $cell_ss['MergeAcross']; 507 $columnTo = Coordinate::stringFromColumnIndex(Coordinate::columnIndexFromString($columnID) + $cell_ss['MergeAcross']); 508 } 509 $rowTo = $rowID; 510 if (isset($cell_ss['MergeDown'])) { 511 $rowTo = $rowTo + $cell_ss['MergeDown']; 512 } 513 $cellRange .= ':' . $columnTo . $rowTo; 514 $spreadsheet->getActiveSheet()->mergeCells($cellRange); 515 } 516 517 $hasCalculatedValue = false; 518 $cellDataFormula = ''; 519 if (isset($cell_ss['Formula'])) { 520 $cellDataFormula = $cell_ss['Formula']; 521 $hasCalculatedValue = true; 522 } 523 if (isset($cell->Data)) { 524 $cellData = $cell->Data; 525 $cellValue = (string) $cellData; 526 $type = DataType::TYPE_NULL; 527 $cellData_ss = $cellData->attributes($namespaces['ss']); 528 if (isset($cellData_ss['Type'])) { 529 $cellDataType = $cellData_ss['Type']; 530 switch ($cellDataType) { 531 /* 532 const TYPE_STRING = 's'; 533 const TYPE_FORMULA = 'f'; 534 const TYPE_NUMERIC = 'n'; 535 const TYPE_BOOL = 'b'; 536 const TYPE_NULL = 'null'; 537 const TYPE_INLINE = 'inlineStr'; 538 const TYPE_ERROR = 'e'; 539 */ 540 case 'String': 541 $type = DataType::TYPE_STRING; 542 543 break; 544 case 'Number': 545 $type = DataType::TYPE_NUMERIC; 546 $cellValue = (float) $cellValue; 547 if (floor($cellValue) == $cellValue) { 548 $cellValue = (int) $cellValue; 549 } 550 551 break; 552 case 'Boolean': 553 $type = DataType::TYPE_BOOL; 554 $cellValue = ($cellValue != 0); 555 556 break; 557 case 'DateTime': 558 $type = DataType::TYPE_NUMERIC; 559 $cellValue = Date::PHPToExcel(strtotime($cellValue . ' UTC')); 560 561 break; 562 case 'Error': 563 $type = DataType::TYPE_ERROR; 564 $hasCalculatedValue = false; 565 566 break; 567 } 568 } 569 570 if ($hasCalculatedValue) { 571 $type = DataType::TYPE_FORMULA; 572 $columnNumber = Coordinate::columnIndexFromString($columnID); 573 $cellDataFormula = AddressHelper::convertFormulaToA1($cellDataFormula, $rowID, $columnNumber); 574 } 575 576 $spreadsheet->getActiveSheet()->getCell($columnID . $rowID)->setValueExplicit((($hasCalculatedValue) ? $cellDataFormula : $cellValue), $type); 577 if ($hasCalculatedValue) { 578 $spreadsheet->getActiveSheet()->getCell($columnID . $rowID)->setCalculatedValue($cellValue); 579 } 580 $rowHasData = true; 581 } 582 583 if (isset($cell->Comment)) { 584 $commentAttributes = $cell->Comment->attributes($namespaces['ss']); 585 $author = 'unknown'; 586 if (isset($commentAttributes->Author)) { 587 $author = (string) $commentAttributes->Author; 588 } 589 $node = $cell->Comment->Data->asXML(); 590 $annotation = strip_tags($node); 591 $spreadsheet->getActiveSheet()->getComment($columnID . $rowID)->setAuthor($author)->setText($this->parseRichText($annotation)); 592 } 593 594 if (isset($cell_ss['StyleID'])) { 595 $style = (string) $cell_ss['StyleID']; 596 if ((isset($this->styles[$style])) && (!empty($this->styles[$style]))) { 597 //if (!$spreadsheet->getActiveSheet()->cellExists($columnID . $rowID)) { 598 // $spreadsheet->getActiveSheet()->getCell($columnID . $rowID)->setValue(null); 599 //} 600 $spreadsheet->getActiveSheet()->getStyle($cellRange)->applyFromArray($this->styles[$style]); 601 } 602 } 603 ++$columnID; 604 while ($additionalMergedCells > 0) { 605 ++$columnID; 606 --$additionalMergedCells; 607 } 608 } 609 610 if ($rowHasData) { 611 if (isset($row_ss['Height'])) { 612 $rowHeight = $row_ss['Height']; 613 $spreadsheet->getActiveSheet()->getRowDimension($rowID)->setRowHeight($rowHeight); 614 } 615 } 616 617 ++$rowID; 618 } 619 620 if (isset($namespaces['x'])) { 621 $xmlX = $worksheet->children($namespaces['x']); 622 if (isset($xmlX->WorksheetOptions)) { 623 (new PageSettings($xmlX, $namespaces))->loadPageSettings($spreadsheet); 624 } 625 } 626 } 627 ++$worksheetID; 628 } 629 630 // Globally scoped defined names 631 $activeWorksheet = $spreadsheet->setActiveSheetIndex(0); 632 if (isset($xml->Names[0])) { 633 foreach ($xml->Names[0] as $definedName) { 634 $definedName_ss = $definedName->attributes($namespaces['ss']); 635 $name = (string) $definedName_ss['Name']; 636 $definedValue = (string) $definedName_ss['RefersTo']; 637 $convertedValue = AddressHelper::convertFormulaToA1($definedValue); 638 if ($convertedValue[0] === '=') { 639 $convertedValue = substr($convertedValue, 1); 640 } 641 $spreadsheet->addDefinedName(DefinedName::createInstance($name, $activeWorksheet, $convertedValue)); 642 } 643 } 644 645 // Return 646 return $spreadsheet; 647 } 648 649 protected function parseRichText($is) 650 { 651 $value = new RichText(); 652 653 $value->createText($is); 654 655 return $value; 656 } 657 658 private function parseStyles(SimpleXMLElement $xml, array $namespaces): void 659 { 660 if (!isset($xml->Styles)) { 661 return; 662 } 663 664 foreach ($xml->Styles[0] as $style) { 665 $style_ss = $style->attributes($namespaces['ss']); 666 $styleID = (string) $style_ss['ID']; 667 $this->styles[$styleID] = (isset($this->styles['Default'])) ? $this->styles['Default'] : []; 668 foreach ($style as $styleType => $styleData) { 669 $styleAttributes = $styleData->attributes($namespaces['ss']); 670 switch ($styleType) { 671 case 'Alignment': 672 $this->parseStyleAlignment($styleID, $styleAttributes); 673 674 break; 675 case 'Borders': 676 $this->parseStyleBorders($styleID, $styleData, $namespaces); 677 678 break; 679 case 'Font': 680 $this->parseStyleFont($styleID, $styleAttributes); 681 682 break; 683 case 'Interior': 684 $this->parseStyleInterior($styleID, $styleAttributes); 685 686 break; 687 case 'NumberFormat': 688 $this->parseStyleNumberFormat($styleID, $styleAttributes); 689 690 break; 691 } 692 } 693 } 694 } 695 696 /** 697 * @param string $styleID 698 */ 699 private function parseStyleAlignment($styleID, SimpleXMLElement $styleAttributes): void 700 { 701 $verticalAlignmentStyles = [ 702 Alignment::VERTICAL_BOTTOM, 703 Alignment::VERTICAL_TOP, 704 Alignment::VERTICAL_CENTER, 705 Alignment::VERTICAL_JUSTIFY, 706 ]; 707 $horizontalAlignmentStyles = [ 708 Alignment::HORIZONTAL_GENERAL, 709 Alignment::HORIZONTAL_LEFT, 710 Alignment::HORIZONTAL_RIGHT, 711 Alignment::HORIZONTAL_CENTER, 712 Alignment::HORIZONTAL_CENTER_CONTINUOUS, 713 Alignment::HORIZONTAL_JUSTIFY, 714 ]; 715 716 foreach ($styleAttributes as $styleAttributeKey => $styleAttributeValue) { 717 $styleAttributeValue = (string) $styleAttributeValue; 718 switch ($styleAttributeKey) { 719 case 'Vertical': 720 if (self::identifyFixedStyleValue($verticalAlignmentStyles, $styleAttributeValue)) { 721 $this->styles[$styleID]['alignment']['vertical'] = $styleAttributeValue; 722 } 723 724 break; 725 case 'Horizontal': 726 if (self::identifyFixedStyleValue($horizontalAlignmentStyles, $styleAttributeValue)) { 727 $this->styles[$styleID]['alignment']['horizontal'] = $styleAttributeValue; 728 } 729 730 break; 731 case 'WrapText': 732 $this->styles[$styleID]['alignment']['wrapText'] = true; 733 734 break; 735 case 'Rotate': 736 $this->styles[$styleID]['alignment']['textRotation'] = $styleAttributeValue; 737 738 break; 739 } 740 } 741 } 742 743 private static $borderPositions = ['top', 'left', 'bottom', 'right']; 744 745 /** 746 * @param $styleID 747 */ 748 private function parseStyleBorders($styleID, SimpleXMLElement $styleData, array $namespaces): void 749 { 750 $diagonalDirection = ''; 751 $borderPosition = ''; 752 foreach ($styleData->Border as $borderStyle) { 753 $borderAttributes = $borderStyle->attributes($namespaces['ss']); 754 $thisBorder = []; 755 $style = (string) $borderAttributes->Weight; 756 $style .= strtolower((string) $borderAttributes->LineStyle); 757 $thisBorder['borderStyle'] = self::$mappings['borderStyle'][$style] ?? Border::BORDER_NONE; 758 foreach ($borderAttributes as $borderStyleKey => $borderStyleValue) { 759 switch ($borderStyleKey) { 760 case 'Position': 761 $borderStyleValue = strtolower((string) $borderStyleValue); 762 if (in_array($borderStyleValue, self::$borderPositions)) { 763 $borderPosition = $borderStyleValue; 764 } elseif ($borderStyleValue == 'diagonalleft') { 765 $diagonalDirection = $diagonalDirection ? Borders::DIAGONAL_BOTH : Borders::DIAGONAL_DOWN; 766 } elseif ($borderStyleValue == 'diagonalright') { 767 $diagonalDirection = $diagonalDirection ? Borders::DIAGONAL_BOTH : Borders::DIAGONAL_UP; 768 } 769 770 break; 771 case 'Color': 772 $borderColour = substr($borderStyleValue, 1); 773 $thisBorder['color']['rgb'] = $borderColour; 774 775 break; 776 } 777 } 778 if ($borderPosition) { 779 $this->styles[$styleID]['borders'][$borderPosition] = $thisBorder; 780 } elseif ($diagonalDirection) { 781 $this->styles[$styleID]['borders']['diagonalDirection'] = $diagonalDirection; 782 $this->styles[$styleID]['borders']['diagonal'] = $thisBorder; 783 } 784 } 785 } 786 787 private static $underlineStyles = [ 788 Font::UNDERLINE_NONE, 789 Font::UNDERLINE_DOUBLE, 790 Font::UNDERLINE_DOUBLEACCOUNTING, 791 Font::UNDERLINE_SINGLE, 792 Font::UNDERLINE_SINGLEACCOUNTING, 793 ]; 794 795 private function parseStyleFontUnderline(string $styleID, string $styleAttributeValue): void 796 { 797 if (self::identifyFixedStyleValue(self::$underlineStyles, $styleAttributeValue)) { 798 $this->styles[$styleID]['font']['underline'] = $styleAttributeValue; 799 } 800 } 801 802 private function parseStyleFontVerticalAlign(string $styleID, string $styleAttributeValue): void 803 { 804 if ($styleAttributeValue == 'Superscript') { 805 $this->styles[$styleID]['font']['superscript'] = true; 806 } 807 if ($styleAttributeValue == 'Subscript') { 808 $this->styles[$styleID]['font']['subscript'] = true; 809 } 810 } 811 812 /** 813 * @param $styleID 814 */ 815 private function parseStyleFont(string $styleID, SimpleXMLElement $styleAttributes): void 816 { 817 foreach ($styleAttributes as $styleAttributeKey => $styleAttributeValue) { 818 $styleAttributeValue = (string) $styleAttributeValue; 819 switch ($styleAttributeKey) { 820 case 'FontName': 821 $this->styles[$styleID]['font']['name'] = $styleAttributeValue; 822 823 break; 824 case 'Size': 825 $this->styles[$styleID]['font']['size'] = $styleAttributeValue; 826 827 break; 828 case 'Color': 829 $this->styles[$styleID]['font']['color']['rgb'] = substr($styleAttributeValue, 1); 830 831 break; 832 case 'Bold': 833 $this->styles[$styleID]['font']['bold'] = true; 834 835 break; 836 case 'Italic': 837 $this->styles[$styleID]['font']['italic'] = true; 838 839 break; 840 case 'Underline': 841 $this->parseStyleFontUnderline($styleID, $styleAttributeValue); 842 843 break; 844 case 'VerticalAlign': 845 $this->parseStyleFontVerticalAlign($styleID, $styleAttributeValue); 846 847 break; 848 } 849 } 850 } 851 852 /** 853 * @param $styleID 854 */ 855 private function parseStyleInterior($styleID, SimpleXMLElement $styleAttributes): void 856 { 857 foreach ($styleAttributes as $styleAttributeKey => $styleAttributeValue) { 858 switch ($styleAttributeKey) { 859 case 'Color': 860 $this->styles[$styleID]['fill']['endColor']['rgb'] = substr($styleAttributeValue, 1); 861 $this->styles[$styleID]['fill']['startColor']['rgb'] = substr($styleAttributeValue, 1); 862 863 break; 864 case 'PatternColor': 865 $this->styles[$styleID]['fill']['startColor']['rgb'] = substr($styleAttributeValue, 1); 866 867 break; 868 case 'Pattern': 869 $lcStyleAttributeValue = strtolower((string) $styleAttributeValue); 870 $this->styles[$styleID]['fill']['fillType'] = self::$mappings['fillType'][$lcStyleAttributeValue] ?? Fill::FILL_NONE; 871 872 break; 873 } 874 } 875 } 876 877 /** 878 * @param $styleID 879 */ 880 private function parseStyleNumberFormat($styleID, SimpleXMLElement $styleAttributes): void 881 { 882 $fromFormats = ['\-', '\ ']; 883 $toFormats = ['-', ' ']; 884 885 foreach ($styleAttributes as $styleAttributeKey => $styleAttributeValue) { 886 $styleAttributeValue = str_replace($fromFormats, $toFormats, $styleAttributeValue); 887 switch ($styleAttributeValue) { 888 case 'Short Date': 889 $styleAttributeValue = 'dd/mm/yyyy'; 890 891 break; 892 } 893 894 if ($styleAttributeValue > '') { 895 $this->styles[$styleID]['numberFormat']['formatCode'] = $styleAttributeValue; 896 } 897 } 898 } 899} 900