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