1<?php
2
3namespace PhpOffice\PhpSpreadsheet\Reader;
4
5use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
6use PhpOffice\PhpSpreadsheet\Cell\DataType;
7use PhpOffice\PhpSpreadsheet\NamedRange;
8use PhpOffice\PhpSpreadsheet\Reader\Security\XmlScanner;
9use PhpOffice\PhpSpreadsheet\ReferenceHelper;
10use PhpOffice\PhpSpreadsheet\RichText\RichText;
11use PhpOffice\PhpSpreadsheet\Settings;
12use PhpOffice\PhpSpreadsheet\Shared\Date;
13use PhpOffice\PhpSpreadsheet\Shared\File;
14use PhpOffice\PhpSpreadsheet\Spreadsheet;
15use PhpOffice\PhpSpreadsheet\Style\Alignment;
16use PhpOffice\PhpSpreadsheet\Style\Border;
17use PhpOffice\PhpSpreadsheet\Style\Borders;
18use PhpOffice\PhpSpreadsheet\Style\Fill;
19use PhpOffice\PhpSpreadsheet\Style\Font;
20use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
21use XMLReader;
22
23class Gnumeric extends BaseReader
24{
25    /**
26     * Shared Expressions.
27     *
28     * @var array
29     */
30    private $expressions = [];
31
32    private $referenceHelper;
33
34    /**
35     * Create a new Gnumeric.
36     */
37    public function __construct()
38    {
39        parent::__construct();
40        $this->referenceHelper = ReferenceHelper::getInstance();
41        $this->securityScanner = XmlScanner::getInstance($this);
42    }
43
44    /**
45     * Can the current IReader read the file?
46     *
47     * @param string $pFilename
48     *
49     * @throws Exception
50     *
51     * @return bool
52     */
53    public function canRead($pFilename)
54    {
55        File::assertFile($pFilename);
56
57        // Check if gzlib functions are available
58        if (!function_exists('gzread')) {
59            throw new Exception('gzlib library is not enabled');
60        }
61
62        // Read signature data (first 3 bytes)
63        $fh = fopen($pFilename, 'r');
64        $data = fread($fh, 2);
65        fclose($fh);
66
67        return $data == chr(0x1F) . chr(0x8B);
68    }
69
70    /**
71     * Reads names of the worksheets from a file, without parsing the whole file to a Spreadsheet object.
72     *
73     * @param string $pFilename
74     *
75     * @return array
76     */
77    public function listWorksheetNames($pFilename)
78    {
79        File::assertFile($pFilename);
80
81        $xml = new XMLReader();
82        $xml->xml($this->securityScanner->scanFile('compress.zlib://' . realpath($pFilename)), null, Settings::getLibXmlLoaderOptions());
83        $xml->setParserProperty(2, true);
84
85        $worksheetNames = [];
86        while ($xml->read()) {
87            if ($xml->name == 'gnm:SheetName' && $xml->nodeType == XMLReader::ELEMENT) {
88                $xml->read(); //    Move onto the value node
89                $worksheetNames[] = (string) $xml->value;
90            } elseif ($xml->name == 'gnm:Sheets') {
91                //    break out of the loop once we've got our sheet names rather than parse the entire file
92                break;
93            }
94        }
95
96        return $worksheetNames;
97    }
98
99    /**
100     * Return worksheet info (Name, Last Column Letter, Last Column Index, Total Rows, Total Columns).
101     *
102     * @param string $pFilename
103     *
104     * @return array
105     */
106    public function listWorksheetInfo($pFilename)
107    {
108        File::assertFile($pFilename);
109
110        $xml = new XMLReader();
111        $xml->xml($this->securityScanner->scanFile('compress.zlib://' . realpath($pFilename)), null, Settings::getLibXmlLoaderOptions());
112        $xml->setParserProperty(2, true);
113
114        $worksheetInfo = [];
115        while ($xml->read()) {
116            if ($xml->name == 'gnm:Sheet' && $xml->nodeType == XMLReader::ELEMENT) {
117                $tmpInfo = [
118                    'worksheetName' => '',
119                    'lastColumnLetter' => 'A',
120                    'lastColumnIndex' => 0,
121                    'totalRows' => 0,
122                    'totalColumns' => 0,
123                ];
124
125                while ($xml->read()) {
126                    if ($xml->name == 'gnm:Name' && $xml->nodeType == XMLReader::ELEMENT) {
127                        $xml->read(); //    Move onto the value node
128                        $tmpInfo['worksheetName'] = (string) $xml->value;
129                    } elseif ($xml->name == 'gnm:MaxCol' && $xml->nodeType == XMLReader::ELEMENT) {
130                        $xml->read(); //    Move onto the value node
131                        $tmpInfo['lastColumnIndex'] = (int) $xml->value;
132                        $tmpInfo['totalColumns'] = (int) $xml->value + 1;
133                    } elseif ($xml->name == 'gnm:MaxRow' && $xml->nodeType == XMLReader::ELEMENT) {
134                        $xml->read(); //    Move onto the value node
135                        $tmpInfo['totalRows'] = (int) $xml->value + 1;
136
137                        break;
138                    }
139                }
140                $tmpInfo['lastColumnLetter'] = Coordinate::stringFromColumnIndex($tmpInfo['lastColumnIndex'] + 1);
141                $worksheetInfo[] = $tmpInfo;
142            }
143        }
144
145        return $worksheetInfo;
146    }
147
148    /**
149     * @param string $filename
150     *
151     * @return string
152     */
153    private function gzfileGetContents($filename)
154    {
155        $file = @gzopen($filename, 'rb');
156        $data = '';
157        if ($file !== false) {
158            while (!gzeof($file)) {
159                $data .= gzread($file, 1024);
160            }
161            gzclose($file);
162        }
163
164        return $data;
165    }
166
167    /**
168     * Loads Spreadsheet from file.
169     *
170     * @param string $pFilename
171     *
172     * @throws Exception
173     *
174     * @return Spreadsheet
175     */
176    public function load($pFilename)
177    {
178        // Create new Spreadsheet
179        $spreadsheet = new Spreadsheet();
180
181        // Load into this instance
182        return $this->loadIntoExisting($pFilename, $spreadsheet);
183    }
184
185    /**
186     * Loads from file into Spreadsheet instance.
187     *
188     * @param string $pFilename
189     * @param Spreadsheet $spreadsheet
190     *
191     * @throws Exception
192     *
193     * @return Spreadsheet
194     */
195    public function loadIntoExisting($pFilename, Spreadsheet $spreadsheet)
196    {
197        File::assertFile($pFilename);
198
199        $gFileData = $this->gzfileGetContents($pFilename);
200
201        $xml = simplexml_load_string($this->securityScanner->scan($gFileData), 'SimpleXMLElement', Settings::getLibXmlLoaderOptions());
202        $namespacesMeta = $xml->getNamespaces(true);
203
204        $gnmXML = $xml->children($namespacesMeta['gnm']);
205
206        $docProps = $spreadsheet->getProperties();
207        //    Document Properties are held differently, depending on the version of Gnumeric
208        if (isset($namespacesMeta['office'])) {
209            $officeXML = $xml->children($namespacesMeta['office']);
210            $officeDocXML = $officeXML->{'document-meta'};
211            $officeDocMetaXML = $officeDocXML->meta;
212
213            foreach ($officeDocMetaXML as $officePropertyData) {
214                $officePropertyDC = [];
215                if (isset($namespacesMeta['dc'])) {
216                    $officePropertyDC = $officePropertyData->children($namespacesMeta['dc']);
217                }
218                foreach ($officePropertyDC as $propertyName => $propertyValue) {
219                    $propertyValue = (string) $propertyValue;
220                    switch ($propertyName) {
221                        case 'title':
222                            $docProps->setTitle(trim($propertyValue));
223
224                            break;
225                        case 'subject':
226                            $docProps->setSubject(trim($propertyValue));
227
228                            break;
229                        case 'creator':
230                            $docProps->setCreator(trim($propertyValue));
231                            $docProps->setLastModifiedBy(trim($propertyValue));
232
233                            break;
234                        case 'date':
235                            $creationDate = strtotime(trim($propertyValue));
236                            $docProps->setCreated($creationDate);
237                            $docProps->setModified($creationDate);
238
239                            break;
240                        case 'description':
241                            $docProps->setDescription(trim($propertyValue));
242
243                            break;
244                    }
245                }
246                $officePropertyMeta = [];
247                if (isset($namespacesMeta['meta'])) {
248                    $officePropertyMeta = $officePropertyData->children($namespacesMeta['meta']);
249                }
250                foreach ($officePropertyMeta as $propertyName => $propertyValue) {
251                    $attributes = $propertyValue->attributes($namespacesMeta['meta']);
252                    $propertyValue = (string) $propertyValue;
253                    switch ($propertyName) {
254                        case 'keyword':
255                            $docProps->setKeywords(trim($propertyValue));
256
257                            break;
258                        case 'initial-creator':
259                            $docProps->setCreator(trim($propertyValue));
260                            $docProps->setLastModifiedBy(trim($propertyValue));
261
262                            break;
263                        case 'creation-date':
264                            $creationDate = strtotime(trim($propertyValue));
265                            $docProps->setCreated($creationDate);
266                            $docProps->setModified($creationDate);
267
268                            break;
269                        case 'user-defined':
270                            [, $attrName] = explode(':', $attributes['name']);
271                            switch ($attrName) {
272                                case 'publisher':
273                                    $docProps->setCompany(trim($propertyValue));
274
275                                    break;
276                                case 'category':
277                                    $docProps->setCategory(trim($propertyValue));
278
279                                    break;
280                                case 'manager':
281                                    $docProps->setManager(trim($propertyValue));
282
283                                    break;
284                            }
285
286                            break;
287                    }
288                }
289            }
290        } elseif (isset($gnmXML->Summary)) {
291            foreach ($gnmXML->Summary->Item as $summaryItem) {
292                $propertyName = $summaryItem->name;
293                $propertyValue = $summaryItem->{'val-string'};
294                switch ($propertyName) {
295                    case 'title':
296                        $docProps->setTitle(trim($propertyValue));
297
298                        break;
299                    case 'comments':
300                        $docProps->setDescription(trim($propertyValue));
301
302                        break;
303                    case 'keywords':
304                        $docProps->setKeywords(trim($propertyValue));
305
306                        break;
307                    case 'category':
308                        $docProps->setCategory(trim($propertyValue));
309
310                        break;
311                    case 'manager':
312                        $docProps->setManager(trim($propertyValue));
313
314                        break;
315                    case 'author':
316                        $docProps->setCreator(trim($propertyValue));
317                        $docProps->setLastModifiedBy(trim($propertyValue));
318
319                        break;
320                    case 'company':
321                        $docProps->setCompany(trim($propertyValue));
322
323                        break;
324                }
325            }
326        }
327
328        $worksheetID = 0;
329        foreach ($gnmXML->Sheets->Sheet as $sheet) {
330            $worksheetName = (string) $sheet->Name;
331            if ((isset($this->loadSheetsOnly)) && (!in_array($worksheetName, $this->loadSheetsOnly))) {
332                continue;
333            }
334
335            $maxRow = $maxCol = 0;
336
337            // Create new Worksheet
338            $spreadsheet->createSheet();
339            $spreadsheet->setActiveSheetIndex($worksheetID);
340            //    Use false for $updateFormulaCellReferences to prevent adjustment of worksheet references in formula
341            //        cells... during the load, all formulae should be correct, and we're simply bringing the worksheet
342            //        name in line with the formula, not the reverse
343            $spreadsheet->getActiveSheet()->setTitle($worksheetName, false, false);
344
345            if ((!$this->readDataOnly) && (isset($sheet->PrintInformation))) {
346                if (isset($sheet->PrintInformation->Margins)) {
347                    foreach ($sheet->PrintInformation->Margins->children('gnm', true) as $key => $margin) {
348                        $marginAttributes = $margin->attributes();
349                        $marginSize = 72 / 100; //    Default
350                        switch ($marginAttributes['PrefUnit']) {
351                            case 'mm':
352                                $marginSize = (int) ($marginAttributes['Points']) / 100;
353
354                                break;
355                        }
356                        switch ($key) {
357                            case 'top':
358                                $spreadsheet->getActiveSheet()->getPageMargins()->setTop($marginSize);
359
360                                break;
361                            case 'bottom':
362                                $spreadsheet->getActiveSheet()->getPageMargins()->setBottom($marginSize);
363
364                                break;
365                            case 'left':
366                                $spreadsheet->getActiveSheet()->getPageMargins()->setLeft($marginSize);
367
368                                break;
369                            case 'right':
370                                $spreadsheet->getActiveSheet()->getPageMargins()->setRight($marginSize);
371
372                                break;
373                            case 'header':
374                                $spreadsheet->getActiveSheet()->getPageMargins()->setHeader($marginSize);
375
376                                break;
377                            case 'footer':
378                                $spreadsheet->getActiveSheet()->getPageMargins()->setFooter($marginSize);
379
380                                break;
381                        }
382                    }
383                }
384            }
385
386            foreach ($sheet->Cells->Cell as $cell) {
387                $cellAttributes = $cell->attributes();
388                $row = (int) $cellAttributes->Row + 1;
389                $column = (int) $cellAttributes->Col;
390
391                if ($row > $maxRow) {
392                    $maxRow = $row;
393                }
394                if ($column > $maxCol) {
395                    $maxCol = $column;
396                }
397
398                $column = Coordinate::stringFromColumnIndex($column + 1);
399
400                // Read cell?
401                if ($this->getReadFilter() !== null) {
402                    if (!$this->getReadFilter()->readCell($column, $row, $worksheetName)) {
403                        continue;
404                    }
405                }
406
407                $ValueType = $cellAttributes->ValueType;
408                $ExprID = (string) $cellAttributes->ExprID;
409                $type = DataType::TYPE_FORMULA;
410                if ($ExprID > '') {
411                    if (((string) $cell) > '') {
412                        $this->expressions[$ExprID] = [
413                            'column' => $cellAttributes->Col,
414                            'row' => $cellAttributes->Row,
415                            'formula' => (string) $cell,
416                        ];
417                    } else {
418                        $expression = $this->expressions[$ExprID];
419
420                        $cell = $this->referenceHelper->updateFormulaReferences(
421                            $expression['formula'],
422                            'A1',
423                            $cellAttributes->Col - $expression['column'],
424                            $cellAttributes->Row - $expression['row'],
425                            $worksheetName
426                        );
427                    }
428                    $type = DataType::TYPE_FORMULA;
429                } else {
430                    switch ($ValueType) {
431                        case '10':        //    NULL
432                            $type = DataType::TYPE_NULL;
433
434                            break;
435                        case '20':        //    Boolean
436                            $type = DataType::TYPE_BOOL;
437                            $cell = $cell == 'TRUE';
438
439                            break;
440                        case '30':        //    Integer
441                            $cell = (int) $cell;
442                            // Excel 2007+ doesn't differentiate between integer and float, so set the value and dropthru to the next (numeric) case
443                            // no break
444                        case '40':        //    Float
445                            $type = DataType::TYPE_NUMERIC;
446
447                            break;
448                        case '50':        //    Error
449                            $type = DataType::TYPE_ERROR;
450
451                            break;
452                        case '60':        //    String
453                            $type = DataType::TYPE_STRING;
454
455                            break;
456                        case '70':        //    Cell Range
457                        case '80':        //    Array
458                    }
459                }
460                $spreadsheet->getActiveSheet()->getCell($column . $row)->setValueExplicit($cell, $type);
461            }
462
463            if ((!$this->readDataOnly) && (isset($sheet->Objects))) {
464                foreach ($sheet->Objects->children('gnm', true) as $key => $comment) {
465                    $commentAttributes = $comment->attributes();
466                    //    Only comment objects are handled at the moment
467                    if ($commentAttributes->Text) {
468                        $spreadsheet->getActiveSheet()->getComment((string) $commentAttributes->ObjectBound)->setAuthor((string) $commentAttributes->Author)->setText($this->parseRichText((string) $commentAttributes->Text));
469                    }
470                }
471            }
472            foreach ($sheet->Styles->StyleRegion as $styleRegion) {
473                $styleAttributes = $styleRegion->attributes();
474                if (($styleAttributes['startRow'] <= $maxRow) &&
475                    ($styleAttributes['startCol'] <= $maxCol)) {
476                    $startColumn = Coordinate::stringFromColumnIndex((int) $styleAttributes['startCol'] + 1);
477                    $startRow = $styleAttributes['startRow'] + 1;
478
479                    $endColumn = ($styleAttributes['endCol'] > $maxCol) ? $maxCol : (int) $styleAttributes['endCol'];
480                    $endColumn = Coordinate::stringFromColumnIndex($endColumn + 1);
481                    $endRow = ($styleAttributes['endRow'] > $maxRow) ? $maxRow : $styleAttributes['endRow'];
482                    $endRow += 1;
483                    $cellRange = $startColumn . $startRow . ':' . $endColumn . $endRow;
484
485                    $styleAttributes = $styleRegion->Style->attributes();
486
487                    //    We still set the number format mask for date/time values, even if readDataOnly is true
488                    if ((!$this->readDataOnly) ||
489                        (Date::isDateTimeFormatCode((string) $styleAttributes['Format']))) {
490                        $styleArray = [];
491                        $styleArray['numberFormat']['formatCode'] = (string) $styleAttributes['Format'];
492                        //    If readDataOnly is false, we set all formatting information
493                        if (!$this->readDataOnly) {
494                            switch ($styleAttributes['HAlign']) {
495                                case '1':
496                                    $styleArray['alignment']['horizontal'] = Alignment::HORIZONTAL_GENERAL;
497
498                                    break;
499                                case '2':
500                                    $styleArray['alignment']['horizontal'] = Alignment::HORIZONTAL_LEFT;
501
502                                    break;
503                                case '4':
504                                    $styleArray['alignment']['horizontal'] = Alignment::HORIZONTAL_RIGHT;
505
506                                    break;
507                                case '8':
508                                    $styleArray['alignment']['horizontal'] = Alignment::HORIZONTAL_CENTER;
509
510                                    break;
511                                case '16':
512                                case '64':
513                                    $styleArray['alignment']['horizontal'] = Alignment::HORIZONTAL_CENTER_CONTINUOUS;
514
515                                    break;
516                                case '32':
517                                    $styleArray['alignment']['horizontal'] = Alignment::HORIZONTAL_JUSTIFY;
518
519                                    break;
520                            }
521
522                            switch ($styleAttributes['VAlign']) {
523                                case '1':
524                                    $styleArray['alignment']['vertical'] = Alignment::VERTICAL_TOP;
525
526                                    break;
527                                case '2':
528                                    $styleArray['alignment']['vertical'] = Alignment::VERTICAL_BOTTOM;
529
530                                    break;
531                                case '4':
532                                    $styleArray['alignment']['vertical'] = Alignment::VERTICAL_CENTER;
533
534                                    break;
535                                case '8':
536                                    $styleArray['alignment']['vertical'] = Alignment::VERTICAL_JUSTIFY;
537
538                                    break;
539                            }
540
541                            $styleArray['alignment']['wrapText'] = $styleAttributes['WrapText'] == '1';
542                            $styleArray['alignment']['shrinkToFit'] = $styleAttributes['ShrinkToFit'] == '1';
543                            $styleArray['alignment']['indent'] = ((int) ($styleAttributes['Indent']) > 0) ? $styleAttributes['indent'] : 0;
544
545                            $RGB = self::parseGnumericColour($styleAttributes['Fore']);
546                            $styleArray['font']['color']['rgb'] = $RGB;
547                            $RGB = self::parseGnumericColour($styleAttributes['Back']);
548                            $shade = $styleAttributes['Shade'];
549                            if (($RGB != '000000') || ($shade != '0')) {
550                                $styleArray['fill']['color']['rgb'] = $styleArray['fill']['startColor']['rgb'] = $RGB;
551                                $RGB2 = self::parseGnumericColour($styleAttributes['PatternColor']);
552                                $styleArray['fill']['endColor']['rgb'] = $RGB2;
553                                switch ($shade) {
554                                    case '1':
555                                        $styleArray['fill']['fillType'] = Fill::FILL_SOLID;
556
557                                        break;
558                                    case '2':
559                                        $styleArray['fill']['fillType'] = Fill::FILL_GRADIENT_LINEAR;
560
561                                        break;
562                                    case '3':
563                                        $styleArray['fill']['fillType'] = Fill::FILL_GRADIENT_PATH;
564
565                                        break;
566                                    case '4':
567                                        $styleArray['fill']['fillType'] = Fill::FILL_PATTERN_DARKDOWN;
568
569                                        break;
570                                    case '5':
571                                        $styleArray['fill']['fillType'] = Fill::FILL_PATTERN_DARKGRAY;
572
573                                        break;
574                                    case '6':
575                                        $styleArray['fill']['fillType'] = Fill::FILL_PATTERN_DARKGRID;
576
577                                        break;
578                                    case '7':
579                                        $styleArray['fill']['fillType'] = Fill::FILL_PATTERN_DARKHORIZONTAL;
580
581                                        break;
582                                    case '8':
583                                        $styleArray['fill']['fillType'] = Fill::FILL_PATTERN_DARKTRELLIS;
584
585                                        break;
586                                    case '9':
587                                        $styleArray['fill']['fillType'] = Fill::FILL_PATTERN_DARKUP;
588
589                                        break;
590                                    case '10':
591                                        $styleArray['fill']['fillType'] = Fill::FILL_PATTERN_DARKVERTICAL;
592
593                                        break;
594                                    case '11':
595                                        $styleArray['fill']['fillType'] = Fill::FILL_PATTERN_GRAY0625;
596
597                                        break;
598                                    case '12':
599                                        $styleArray['fill']['fillType'] = Fill::FILL_PATTERN_GRAY125;
600
601                                        break;
602                                    case '13':
603                                        $styleArray['fill']['fillType'] = Fill::FILL_PATTERN_LIGHTDOWN;
604
605                                        break;
606                                    case '14':
607                                        $styleArray['fill']['fillType'] = Fill::FILL_PATTERN_LIGHTGRAY;
608
609                                        break;
610                                    case '15':
611                                        $styleArray['fill']['fillType'] = Fill::FILL_PATTERN_LIGHTGRID;
612
613                                        break;
614                                    case '16':
615                                        $styleArray['fill']['fillType'] = Fill::FILL_PATTERN_LIGHTHORIZONTAL;
616
617                                        break;
618                                    case '17':
619                                        $styleArray['fill']['fillType'] = Fill::FILL_PATTERN_LIGHTTRELLIS;
620
621                                        break;
622                                    case '18':
623                                        $styleArray['fill']['fillType'] = Fill::FILL_PATTERN_LIGHTUP;
624
625                                        break;
626                                    case '19':
627                                        $styleArray['fill']['fillType'] = Fill::FILL_PATTERN_LIGHTVERTICAL;
628
629                                        break;
630                                    case '20':
631                                        $styleArray['fill']['fillType'] = Fill::FILL_PATTERN_MEDIUMGRAY;
632
633                                        break;
634                                }
635                            }
636
637                            $fontAttributes = $styleRegion->Style->Font->attributes();
638                            $styleArray['font']['name'] = (string) $styleRegion->Style->Font;
639                            $styleArray['font']['size'] = (int) ($fontAttributes['Unit']);
640                            $styleArray['font']['bold'] = $fontAttributes['Bold'] == '1';
641                            $styleArray['font']['italic'] = $fontAttributes['Italic'] == '1';
642                            $styleArray['font']['strikethrough'] = $fontAttributes['StrikeThrough'] == '1';
643                            switch ($fontAttributes['Underline']) {
644                                case '1':
645                                    $styleArray['font']['underline'] = Font::UNDERLINE_SINGLE;
646
647                                    break;
648                                case '2':
649                                    $styleArray['font']['underline'] = Font::UNDERLINE_DOUBLE;
650
651                                    break;
652                                case '3':
653                                    $styleArray['font']['underline'] = Font::UNDERLINE_SINGLEACCOUNTING;
654
655                                    break;
656                                case '4':
657                                    $styleArray['font']['underline'] = Font::UNDERLINE_DOUBLEACCOUNTING;
658
659                                    break;
660                                default:
661                                    $styleArray['font']['underline'] = Font::UNDERLINE_NONE;
662
663                                    break;
664                            }
665                            switch ($fontAttributes['Script']) {
666                                case '1':
667                                    $styleArray['font']['superscript'] = true;
668
669                                    break;
670                                case '-1':
671                                    $styleArray['font']['subscript'] = true;
672
673                                    break;
674                            }
675
676                            if (isset($styleRegion->Style->StyleBorder)) {
677                                if (isset($styleRegion->Style->StyleBorder->Top)) {
678                                    $styleArray['borders']['top'] = self::parseBorderAttributes($styleRegion->Style->StyleBorder->Top->attributes());
679                                }
680                                if (isset($styleRegion->Style->StyleBorder->Bottom)) {
681                                    $styleArray['borders']['bottom'] = self::parseBorderAttributes($styleRegion->Style->StyleBorder->Bottom->attributes());
682                                }
683                                if (isset($styleRegion->Style->StyleBorder->Left)) {
684                                    $styleArray['borders']['left'] = self::parseBorderAttributes($styleRegion->Style->StyleBorder->Left->attributes());
685                                }
686                                if (isset($styleRegion->Style->StyleBorder->Right)) {
687                                    $styleArray['borders']['right'] = self::parseBorderAttributes($styleRegion->Style->StyleBorder->Right->attributes());
688                                }
689                                if ((isset($styleRegion->Style->StyleBorder->Diagonal)) && (isset($styleRegion->Style->StyleBorder->{'Rev-Diagonal'}))) {
690                                    $styleArray['borders']['diagonal'] = self::parseBorderAttributes($styleRegion->Style->StyleBorder->Diagonal->attributes());
691                                    $styleArray['borders']['diagonalDirection'] = Borders::DIAGONAL_BOTH;
692                                } elseif (isset($styleRegion->Style->StyleBorder->Diagonal)) {
693                                    $styleArray['borders']['diagonal'] = self::parseBorderAttributes($styleRegion->Style->StyleBorder->Diagonal->attributes());
694                                    $styleArray['borders']['diagonalDirection'] = Borders::DIAGONAL_UP;
695                                } elseif (isset($styleRegion->Style->StyleBorder->{'Rev-Diagonal'})) {
696                                    $styleArray['borders']['diagonal'] = self::parseBorderAttributes($styleRegion->Style->StyleBorder->{'Rev-Diagonal'}->attributes());
697                                    $styleArray['borders']['diagonalDirection'] = Borders::DIAGONAL_DOWN;
698                                }
699                            }
700                            if (isset($styleRegion->Style->HyperLink)) {
701                                //    TO DO
702                                $hyperlink = $styleRegion->Style->HyperLink->attributes();
703                            }
704                        }
705                        $spreadsheet->getActiveSheet()->getStyle($cellRange)->applyFromArray($styleArray);
706                    }
707                }
708            }
709
710            if ((!$this->readDataOnly) && (isset($sheet->Cols))) {
711                //    Column Widths
712                $columnAttributes = $sheet->Cols->attributes();
713                $defaultWidth = $columnAttributes['DefaultSizePts'] / 5.4;
714                $c = 0;
715                foreach ($sheet->Cols->ColInfo as $columnOverride) {
716                    $columnAttributes = $columnOverride->attributes();
717                    $column = $columnAttributes['No'];
718                    $columnWidth = $columnAttributes['Unit'] / 5.4;
719                    $hidden = (isset($columnAttributes['Hidden'])) && ($columnAttributes['Hidden'] == '1');
720                    $columnCount = (isset($columnAttributes['Count'])) ? $columnAttributes['Count'] : 1;
721                    while ($c < $column) {
722                        $spreadsheet->getActiveSheet()->getColumnDimension(Coordinate::stringFromColumnIndex($c + 1))->setWidth($defaultWidth);
723                        ++$c;
724                    }
725                    while (($c < ($column + $columnCount)) && ($c <= $maxCol)) {
726                        $spreadsheet->getActiveSheet()->getColumnDimension(Coordinate::stringFromColumnIndex($c + 1))->setWidth($columnWidth);
727                        if ($hidden) {
728                            $spreadsheet->getActiveSheet()->getColumnDimension(Coordinate::stringFromColumnIndex($c + 1))->setVisible(false);
729                        }
730                        ++$c;
731                    }
732                }
733                while ($c <= $maxCol) {
734                    $spreadsheet->getActiveSheet()->getColumnDimension(Coordinate::stringFromColumnIndex($c + 1))->setWidth($defaultWidth);
735                    ++$c;
736                }
737            }
738
739            if ((!$this->readDataOnly) && (isset($sheet->Rows))) {
740                //    Row Heights
741                $rowAttributes = $sheet->Rows->attributes();
742                $defaultHeight = $rowAttributes['DefaultSizePts'];
743                $r = 0;
744
745                foreach ($sheet->Rows->RowInfo as $rowOverride) {
746                    $rowAttributes = $rowOverride->attributes();
747                    $row = $rowAttributes['No'];
748                    $rowHeight = $rowAttributes['Unit'];
749                    $hidden = (isset($rowAttributes['Hidden'])) && ($rowAttributes['Hidden'] == '1');
750                    $rowCount = (isset($rowAttributes['Count'])) ? $rowAttributes['Count'] : 1;
751                    while ($r < $row) {
752                        ++$r;
753                        $spreadsheet->getActiveSheet()->getRowDimension($r)->setRowHeight($defaultHeight);
754                    }
755                    while (($r < ($row + $rowCount)) && ($r < $maxRow)) {
756                        ++$r;
757                        $spreadsheet->getActiveSheet()->getRowDimension($r)->setRowHeight($rowHeight);
758                        if ($hidden) {
759                            $spreadsheet->getActiveSheet()->getRowDimension($r)->setVisible(false);
760                        }
761                    }
762                }
763                while ($r < $maxRow) {
764                    ++$r;
765                    $spreadsheet->getActiveSheet()->getRowDimension($r)->setRowHeight($defaultHeight);
766                }
767            }
768
769            //    Handle Merged Cells in this worksheet
770            if (isset($sheet->MergedRegions)) {
771                foreach ($sheet->MergedRegions->Merge as $mergeCells) {
772                    if (strpos($mergeCells, ':') !== false) {
773                        $spreadsheet->getActiveSheet()->mergeCells($mergeCells);
774                    }
775                }
776            }
777
778            ++$worksheetID;
779        }
780
781        //    Loop through definedNames (global named ranges)
782        if (isset($gnmXML->Names)) {
783            foreach ($gnmXML->Names->Name as $namedRange) {
784                $name = (string) $namedRange->name;
785                $range = (string) $namedRange->value;
786                if (stripos($range, '#REF!') !== false) {
787                    continue;
788                }
789
790                $range = Worksheet::extractSheetTitle($range, true);
791                $range[0] = trim($range[0], "'");
792                if ($worksheet = $spreadsheet->getSheetByName($range[0])) {
793                    $extractedRange = str_replace('$', '', $range[1]);
794                    $spreadsheet->addNamedRange(new NamedRange($name, $worksheet, $extractedRange));
795                }
796            }
797        }
798
799        // Return
800        return $spreadsheet;
801    }
802
803    private static function parseBorderAttributes($borderAttributes)
804    {
805        $styleArray = [];
806        if (isset($borderAttributes['Color'])) {
807            $styleArray['color']['rgb'] = self::parseGnumericColour($borderAttributes['Color']);
808        }
809
810        switch ($borderAttributes['Style']) {
811            case '0':
812                $styleArray['borderStyle'] = Border::BORDER_NONE;
813
814                break;
815            case '1':
816                $styleArray['borderStyle'] = Border::BORDER_THIN;
817
818                break;
819            case '2':
820                $styleArray['borderStyle'] = Border::BORDER_MEDIUM;
821
822                break;
823            case '3':
824                $styleArray['borderStyle'] = Border::BORDER_SLANTDASHDOT;
825
826                break;
827            case '4':
828                $styleArray['borderStyle'] = Border::BORDER_DASHED;
829
830                break;
831            case '5':
832                $styleArray['borderStyle'] = Border::BORDER_THICK;
833
834                break;
835            case '6':
836                $styleArray['borderStyle'] = Border::BORDER_DOUBLE;
837
838                break;
839            case '7':
840                $styleArray['borderStyle'] = Border::BORDER_DOTTED;
841
842                break;
843            case '8':
844                $styleArray['borderStyle'] = Border::BORDER_MEDIUMDASHED;
845
846                break;
847            case '9':
848                $styleArray['borderStyle'] = Border::BORDER_DASHDOT;
849
850                break;
851            case '10':
852                $styleArray['borderStyle'] = Border::BORDER_MEDIUMDASHDOT;
853
854                break;
855            case '11':
856                $styleArray['borderStyle'] = Border::BORDER_DASHDOTDOT;
857
858                break;
859            case '12':
860                $styleArray['borderStyle'] = Border::BORDER_MEDIUMDASHDOTDOT;
861
862                break;
863            case '13':
864                $styleArray['borderStyle'] = Border::BORDER_MEDIUMDASHDOTDOT;
865
866                break;
867        }
868
869        return $styleArray;
870    }
871
872    private function parseRichText($is)
873    {
874        $value = new RichText();
875        $value->createText($is);
876
877        return $value;
878    }
879
880    private static function parseGnumericColour($gnmColour)
881    {
882        [$gnmR, $gnmG, $gnmB] = explode(':', $gnmColour);
883        $gnmR = substr(str_pad($gnmR, 4, '0', STR_PAD_RIGHT), 0, 2);
884        $gnmG = substr(str_pad($gnmG, 4, '0', STR_PAD_RIGHT), 0, 2);
885        $gnmB = substr(str_pad($gnmB, 4, '0', STR_PAD_RIGHT), 0, 2);
886
887        return $gnmR . $gnmG . $gnmB;
888    }
889}
890