Xml::loadIntoExisting()   F
last analyzed

Complexity

Conditions 85
Paths > 20000

Size

Total Lines 366
Code Lines 241

Duplication

Lines 0
Ratio 0 %

Code Coverage

Tests 236
CRAP Score 85

Importance

Changes 0
Metric Value
eloc 241
c 0
b 0
f 0
dl 0
loc 366
ccs 236
cts 236
cp 1
rs 0
cc 85
nc 3691555
nop 3
crap 85

How to fix   Long Method    Complexity   

Long Method

Small methods make your code easier to understand, in particular if combined with a good name. Besides, if your method is small, finding a good name is usually much easier.

For example, if you find yourself adding comments to a method's body, this is usually a good sign to extract the commented part to a new method, and use the comment as a starting point when coming up with a good name for this new method.

Commonly applied refactorings include:

1
<?php
2
3
namespace PhpOffice\PhpSpreadsheet\Reader;
4
5
use DateTime;
6
use DateTimeZone;
7
use PhpOffice\PhpSpreadsheet\Cell\AddressHelper;
8
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
9
use PhpOffice\PhpSpreadsheet\Cell\DataType;
10
use PhpOffice\PhpSpreadsheet\DefinedName;
11
use PhpOffice\PhpSpreadsheet\Reader\Security\XmlScanner;
12
use PhpOffice\PhpSpreadsheet\Reader\Xlsx\Namespaces;
13
use PhpOffice\PhpSpreadsheet\Reader\Xml\PageSettings;
14
use PhpOffice\PhpSpreadsheet\Reader\Xml\Properties;
15
use PhpOffice\PhpSpreadsheet\Reader\Xml\Style;
16
use PhpOffice\PhpSpreadsheet\RichText\RichText;
17
use PhpOffice\PhpSpreadsheet\Settings;
18
use PhpOffice\PhpSpreadsheet\Shared\Date;
19
use PhpOffice\PhpSpreadsheet\Shared\File;
20
use PhpOffice\PhpSpreadsheet\Shared\StringHelper;
21
use PhpOffice\PhpSpreadsheet\Spreadsheet;
22
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
23
use SimpleXMLElement;
24
25
/**
26
 * Reader for SpreadsheetML, the XML schema for Microsoft Office Excel 2003.
27
 */
28
class Xml extends BaseReader
29
{
30
    public const NAMESPACES_SS = 'urn:schemas-microsoft-com:office:spreadsheet';
31
32
    /**
33
     * Formats.
34
     */
35
    protected array $styles = [];
36
37
    /**
38
     * Create a new Excel2003XML Reader instance.
39
     */
40 64
    public function __construct()
41
    {
42 64
        parent::__construct();
43 64
        $this->securityScanner = XmlScanner::getInstance($this);
44
    }
45
46
    private string $fileContents = '';
47
48 38
    public static function xmlMappings(): array
49
    {
50 38
        return array_merge(
51 38
            Style\Fill::FILL_MAPPINGS,
52 38
            Style\Border::BORDER_MAPPINGS
53 38
        );
54
    }
55
56
    /**
57
     * Can the current IReader read the file?
58
     */
59 45
    public function canRead(string $filename): bool
60
    {
61
        //    Office                    xmlns:o="urn:schemas-microsoft-com:office:office"
62
        //    Excel                    xmlns:x="urn:schemas-microsoft-com:office:excel"
63
        //    XML Spreadsheet            xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
64
        //    Spreadsheet component    xmlns:c="urn:schemas-microsoft-com:office:component:spreadsheet"
65
        //    XML schema                 xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882"
66
        //    XML data type            xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882"
67
        //    MS-persist recordset    xmlns:rs="urn:schemas-microsoft-com:rowset"
68
        //    Rowset                    xmlns:z="#RowsetSchema"
69
        //
70
71 45
        $signature = [
72 45
            '<?xml version="1.0"',
73 45
            'xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet',
74 45
        ];
75
76
        // Open file
77 45
        $data = file_get_contents($filename) ?: '';
78
79
        // Why?
80
        //$data = str_replace("'", '"', $data); // fix headers with single quote
81
82 45
        $valid = true;
83 45
        foreach ($signature as $match) {
84
            // every part of the signature must be present
85 45
            if (!str_contains($data, $match)) {
86 16
                $valid = false;
87
88 16
                break;
89
            }
90
        }
91
92
        //    Retrieve charset encoding
93 45
        if (preg_match('/<?xml.*encoding=[\'"](.*?)[\'"].*?>/m', $data, $matches)) {
94 10
            $charSet = strtoupper($matches[1]);
95 10
            if (preg_match('/^ISO-8859-\d[\dL]?$/i', $charSet) === 1) {
96 1
                $data = StringHelper::convertEncoding($data, 'UTF-8', $charSet);
97 1
                $data = (string) preg_replace('/(<?xml.*encoding=[\'"]).*?([\'"].*?>)/um', '$1' . 'UTF-8' . '$2', $data, 1);
98
            }
99
        }
100 45
        $this->fileContents = $data;
101
102 45
        return $valid;
103
    }
104
105
    /**
106
     * Check if the file is a valid SimpleXML.
107
     *
108
     * @return false|SimpleXMLElement
109
     */
110 37
    public function trySimpleXMLLoadString(string $filename): SimpleXMLElement|bool
111
    {
112
        try {
113 37
            $xml = simplexml_load_string(
114 37
                $this->getSecurityScannerOrThrow()->scan($this->fileContents ?: file_get_contents($filename)),
115 37
                'SimpleXMLElement',
116 37
                Settings::getLibXmlLoaderOptions()
117 37
            );
118 1
        } catch (\Exception $e) {
119 1
            throw new Exception('Cannot load invalid XML file: ' . $filename, 0, $e);
120
        }
121 36
        $this->fileContents = '';
122
123 36
        return $xml;
124
    }
125
126
    /**
127
     * Reads names of the worksheets from a file, without parsing the whole file to a Spreadsheet object.
128
     */
129 4
    public function listWorksheetNames(string $filename): array
130
    {
131 4
        File::assertFile($filename);
132 4
        if (!$this->canRead($filename)) {
133 2
            throw new Exception($filename . ' is an Invalid Spreadsheet file.');
134
        }
135
136 2
        $worksheetNames = [];
137
138 2
        $xml = $this->trySimpleXMLLoadString($filename);
139 2
        if ($xml === false) {
140 1
            throw new Exception("Problem reading {$filename}");
141
        }
142
143 1
        $xml_ss = $xml->children(self::NAMESPACES_SS);
144 1
        foreach ($xml_ss->Worksheet as $worksheet) {
145 1
            $worksheet_ss = self::getAttributes($worksheet, self::NAMESPACES_SS);
146 1
            $worksheetNames[] = (string) $worksheet_ss['Name'];
147
        }
148
149 1
        return $worksheetNames;
150
    }
151
152
    /**
153
     * Return worksheet info (Name, Last Column Letter, Last Column Index, Total Rows, Total Columns).
154
     */
155 4
    public function listWorksheetInfo(string $filename): array
156
    {
157 4
        File::assertFile($filename);
158 4
        if (!$this->canRead($filename)) {
159 2
            throw new Exception($filename . ' is an Invalid Spreadsheet file.');
160
        }
161
162 2
        $worksheetInfo = [];
163
164 2
        $xml = $this->trySimpleXMLLoadString($filename);
165 2
        if ($xml === false) {
166 1
            throw new Exception("Problem reading {$filename}");
167
        }
168
169 1
        $worksheetID = 1;
170 1
        $xml_ss = $xml->children(self::NAMESPACES_SS);
171 1
        foreach ($xml_ss->Worksheet as $worksheet) {
172 1
            $worksheet_ss = self::getAttributes($worksheet, self::NAMESPACES_SS);
173
174 1
            $tmpInfo = [];
175 1
            $tmpInfo['worksheetName'] = '';
176 1
            $tmpInfo['lastColumnLetter'] = 'A';
177 1
            $tmpInfo['lastColumnIndex'] = 0;
178 1
            $tmpInfo['totalRows'] = 0;
179 1
            $tmpInfo['totalColumns'] = 0;
180
181 1
            $tmpInfo['worksheetName'] = "Worksheet_{$worksheetID}";
182 1
            if (isset($worksheet_ss['Name'])) {
183 1
                $tmpInfo['worksheetName'] = (string) $worksheet_ss['Name'];
184
            }
185
186 1
            if (isset($worksheet->Table->Row)) {
187 1
                $rowIndex = 0;
188
189 1
                foreach ($worksheet->Table->Row as $rowData) {
190 1
                    $columnIndex = 0;
191 1
                    $rowHasData = false;
192
193 1
                    foreach ($rowData->Cell as $cell) {
194 1
                        if (isset($cell->Data)) {
195 1
                            $tmpInfo['lastColumnIndex'] = max($tmpInfo['lastColumnIndex'], $columnIndex);
196 1
                            $rowHasData = true;
197
                        }
198
199 1
                        ++$columnIndex;
200
                    }
201
202 1
                    ++$rowIndex;
203
204 1
                    if ($rowHasData) {
205 1
                        $tmpInfo['totalRows'] = max($tmpInfo['totalRows'], $rowIndex);
206
                    }
207
                }
208
            }
209
210 1
            $tmpInfo['lastColumnLetter'] = Coordinate::stringFromColumnIndex($tmpInfo['lastColumnIndex'] + 1);
211 1
            $tmpInfo['totalColumns'] = $tmpInfo['lastColumnIndex'] + 1;
212
213 1
            $worksheetInfo[] = $tmpInfo;
214 1
            ++$worksheetID;
215
        }
216
217 1
        return $worksheetInfo;
218
    }
219
220
    /**
221
     * Loads Spreadsheet from string.
222
     */
223 7
    public function loadSpreadsheetFromString(string $contents): Spreadsheet
224
    {
225
        // Create new Spreadsheet
226 7
        $spreadsheet = new Spreadsheet();
227 7
        $spreadsheet->removeSheetByIndex(0);
228
229
        // Load into this instance
230 7
        return $this->loadIntoExisting($contents, $spreadsheet, true);
231
    }
232
233
    /**
234
     * Loads Spreadsheet from file.
235
     */
236 27
    protected function loadSpreadsheetFromFile(string $filename): Spreadsheet
237
    {
238
        // Create new Spreadsheet
239 27
        $spreadsheet = new Spreadsheet();
240 27
        $spreadsheet->removeSheetByIndex(0);
241
242
        // Load into this instance
243 27
        return $this->loadIntoExisting($filename, $spreadsheet);
244
    }
245
246
    /**
247
     * Loads from file or contents into Spreadsheet instance.
248
     *
249
     * @param string $filename file name if useContents is false else file contents
250
     */
251 34
    public function loadIntoExisting(string $filename, Spreadsheet $spreadsheet, bool $useContents = false): Spreadsheet
252
    {
253 34
        if ($useContents) {
254 7
            $this->fileContents = $filename;
255
        } else {
256 27
            File::assertFile($filename);
257 27
            if (!$this->canRead($filename)) {
258 2
                throw new Exception($filename . ' is an Invalid Spreadsheet file.');
259
            }
260
        }
261
262 32
        $xml = $this->trySimpleXMLLoadString($filename);
263 32
        if ($xml === false) {
264 1
            throw new Exception("Problem reading {$filename}");
265
        }
266
267 31
        $namespaces = $xml->getNamespaces(true);
268
269 31
        (new Properties($spreadsheet))->readProperties($xml, $namespaces);
270
271 31
        $this->styles = (new Style())->parseStyles($xml, $namespaces);
272 31
        if (isset($this->styles['Default'])) {
273 27
            $spreadsheet->getCellXfCollection()[0]->applyFromArray($this->styles['Default']);
274
        }
275
276 31
        $worksheetID = 0;
277 31
        $xml_ss = $xml->children(self::NAMESPACES_SS);
278
279
        /** @var null|SimpleXMLElement $worksheetx */
280 31
        foreach ($xml_ss->Worksheet as $worksheetx) {
281 31
            $worksheet = $worksheetx ?? new SimpleXMLElement('<xml></xml>');
282 31
            $worksheet_ss = self::getAttributes($worksheet, self::NAMESPACES_SS);
283
284
            if (
285 31
                isset($this->loadSheetsOnly, $worksheet_ss['Name'])
286 31
                && (!in_array($worksheet_ss['Name'], $this->loadSheetsOnly))
287
            ) {
288 2
                continue;
289
            }
290
291
            // Create new Worksheet
292 30
            $spreadsheet->createSheet();
293 30
            $spreadsheet->setActiveSheetIndex($worksheetID);
294 30
            $worksheetName = '';
295 30
            if (isset($worksheet_ss['Name'])) {
296 30
                $worksheetName = (string) $worksheet_ss['Name'];
297
                //    Use false for $updateFormulaCellReferences to prevent adjustment of worksheet references in
298
                //        formula cells... during the load, all formulae should be correct, and we're simply bringing
299
                //        the worksheet name in line with the formula, not the reverse
300 30
                $spreadsheet->getActiveSheet()->setTitle($worksheetName, false, false);
301
            }
302 30
            if (isset($worksheet_ss['Protected'])) {
303 2
                $protection = (string) $worksheet_ss['Protected'] === '1';
304 2
                $spreadsheet->getActiveSheet()->getProtection()->setSheet($protection);
305
            }
306
307
            // locally scoped defined names
308 30
            if (isset($worksheet->Names[0])) {
309 2
                foreach ($worksheet->Names[0] as $definedName) {
310 2
                    $definedName_ss = self::getAttributes($definedName, self::NAMESPACES_SS);
311 2
                    $name = (string) $definedName_ss['Name'];
312 2
                    $definedValue = (string) $definedName_ss['RefersTo'];
313 2
                    $convertedValue = AddressHelper::convertFormulaToA1($definedValue);
314 2
                    if ($convertedValue[0] === '=') {
315 2
                        $convertedValue = substr($convertedValue, 1);
316
                    }
317 2
                    $spreadsheet->addDefinedName(DefinedName::createInstance($name, $spreadsheet->getActiveSheet(), $convertedValue, true));
318
                }
319
            }
320
321 30
            $columnID = 'A';
322 30
            if (isset($worksheet->Table->Column)) {
323 17
                foreach ($worksheet->Table->Column as $columnData) {
324 17
                    $columnData_ss = self::getAttributes($columnData, self::NAMESPACES_SS);
325 17
                    $colspan = 0;
326 17
                    if (isset($columnData_ss['Span'])) {
327 12
                        $spanAttr = (string) $columnData_ss['Span'];
328 12
                        if (is_numeric($spanAttr)) {
329 12
                            $colspan = max(0, (int) $spanAttr);
330
                        }
331
                    }
332 17
                    if (isset($columnData_ss['Index'])) {
333 14
                        $columnID = Coordinate::stringFromColumnIndex((int) $columnData_ss['Index']);
334
                    }
335 17
                    $columnWidth = null;
336 17
                    if (isset($columnData_ss['Width'])) {
337 16
                        $columnWidth = $columnData_ss['Width'];
338
                    }
339 17
                    $columnVisible = null;
340 17
                    if (isset($columnData_ss['Hidden'])) {
341 11
                        $columnVisible = ((string) $columnData_ss['Hidden']) !== '1';
342
                    }
343 17
                    while ($colspan >= 0) {
344 17
                        if (isset($columnWidth)) {
345 16
                            $spreadsheet->getActiveSheet()->getColumnDimension($columnID)->setWidth($columnWidth / 5.4);
346
                        }
347 17
                        if (isset($columnVisible)) {
348 11
                            $spreadsheet->getActiveSheet()->getColumnDimension($columnID)->setVisible($columnVisible);
349
                        }
350 17
                        ++$columnID;
351 17
                        --$colspan;
352
                    }
353
                }
354
            }
355
356 30
            $rowID = 1;
357 30
            if (isset($worksheet->Table->Row)) {
358 29
                $additionalMergedCells = 0;
359 29
                foreach ($worksheet->Table->Row as $rowData) {
360 29
                    $rowHasData = false;
361 29
                    $row_ss = self::getAttributes($rowData, self::NAMESPACES_SS);
362 29
                    if (isset($row_ss['Index'])) {
363 5
                        $rowID = (int) $row_ss['Index'];
364
                    }
365 29
                    if (isset($row_ss['Hidden'])) {
366 10
                        $rowVisible = ((string) $row_ss['Hidden']) !== '1';
367 10
                        $spreadsheet->getActiveSheet()->getRowDimension($rowID)->setVisible($rowVisible);
368
                    }
369
370 29
                    $columnID = 'A';
371 29
                    foreach ($rowData->Cell as $cell) {
372 29
                        $cell_ss = self::getAttributes($cell, self::NAMESPACES_SS);
373 29
                        if (isset($cell_ss['Index'])) {
374 14
                            $columnID = Coordinate::stringFromColumnIndex((int) $cell_ss['Index']);
375
                        }
376 29
                        $cellRange = $columnID . $rowID;
377
378 29
                        if ($this->getReadFilter() !== null) {
379 29
                            if (!$this->getReadFilter()->readCell($columnID, $rowID, $worksheetName)) {
380 1
                                ++$columnID;
381
382 1
                                continue;
383
                            }
384
                        }
385
386 29
                        if (isset($cell_ss['HRef'])) {
387 13
                            $spreadsheet->getActiveSheet()->getCell($cellRange)->getHyperlink()->setUrl((string) $cell_ss['HRef']);
388
                        }
389
390 29
                        if ((isset($cell_ss['MergeAcross'])) || (isset($cell_ss['MergeDown']))) {
391 9
                            $columnTo = $columnID;
392 9
                            if (isset($cell_ss['MergeAcross'])) {
393 9
                                $additionalMergedCells += (int) $cell_ss['MergeAcross'];
394 9
                                $columnTo = Coordinate::stringFromColumnIndex((int) (Coordinate::columnIndexFromString($columnID) + $cell_ss['MergeAcross']));
395
                            }
396 9
                            $rowTo = $rowID;
397 9
                            if (isset($cell_ss['MergeDown'])) {
398 9
                                $rowTo = $rowTo + $cell_ss['MergeDown'];
399
                            }
400 9
                            $cellRange .= ':' . $columnTo . $rowTo;
401 9
                            $spreadsheet->getActiveSheet()->mergeCells($cellRange, Worksheet::MERGE_CELL_CONTENT_HIDE);
402
                        }
403
404 29
                        $hasCalculatedValue = false;
405 29
                        $cellDataFormula = '';
406 29
                        if (isset($cell_ss['Formula'])) {
407 18
                            $cellDataFormula = $cell_ss['Formula'];
408 18
                            $hasCalculatedValue = true;
409
                        }
410 29
                        if (isset($cell->Data)) {
411 29
                            $cellData = $cell->Data;
412 29
                            $cellValue = (string) $cellData;
413 29
                            $type = DataType::TYPE_NULL;
414 29
                            $cellData_ss = self::getAttributes($cellData, self::NAMESPACES_SS);
415 29
                            if (isset($cellData_ss['Type'])) {
416 29
                                $cellDataType = $cellData_ss['Type'];
417
                                switch ($cellDataType) {
418
                                    /*
419
                                    const TYPE_STRING        = 's';
420
                                    const TYPE_FORMULA        = 'f';
421
                                    const TYPE_NUMERIC        = 'n';
422
                                    const TYPE_BOOL            = 'b';
423
                                    const TYPE_NULL            = 'null';
424
                                    const TYPE_INLINE        = 'inlineStr';
425
                                    const TYPE_ERROR        = 'e';
426
                                    */
427 29
                                    case 'String':
428 25
                                        $type = DataType::TYPE_STRING;
429
430 25
                                        break;
431 16
                                    case 'Number':
432 16
                                        $type = DataType::TYPE_NUMERIC;
433 16
                                        $cellValue = (float) $cellValue;
434 16
                                        if (floor($cellValue) == $cellValue) {
435 16
                                            $cellValue = (int) $cellValue;
436
                                        }
437
438 16
                                        break;
439 11
                                    case 'Boolean':
440 9
                                        $type = DataType::TYPE_BOOL;
441 9
                                        $cellValue = ($cellValue != 0);
442
443 9
                                        break;
444 11
                                    case 'DateTime':
445 11
                                        $type = DataType::TYPE_NUMERIC;
446 11
                                        $dateTime = new DateTime($cellValue, new DateTimeZone('UTC'));
447 11
                                        $cellValue = Date::PHPToExcel($dateTime);
448
449 11
                                        break;
450 9
                                    case 'Error':
451 9
                                        $type = DataType::TYPE_ERROR;
452 9
                                        $hasCalculatedValue = false;
453
454 9
                                        break;
455
                                }
456
                            }
457
458 29
                            $originalType = $type;
459 29
                            if ($hasCalculatedValue) {
460 18
                                $type = DataType::TYPE_FORMULA;
461 18
                                $columnNumber = Coordinate::columnIndexFromString($columnID);
462 18
                                $cellDataFormula = AddressHelper::convertFormulaToA1($cellDataFormula, $rowID, $columnNumber);
463
                            }
464
465 29
                            $spreadsheet->getActiveSheet()->getCell($columnID . $rowID)->setValueExplicit((($hasCalculatedValue) ? $cellDataFormula : $cellValue), $type);
466 29
                            if ($hasCalculatedValue) {
467 18
                                $spreadsheet->getActiveSheet()->getCell($columnID . $rowID)->setCalculatedValue($cellValue, $originalType === DataType::TYPE_NUMERIC);
468
                            }
469 29
                            $rowHasData = true;
470
                        }
471
472 29
                        if (isset($cell->Comment)) {
473 10
                            $this->parseCellComment($cell->Comment, $spreadsheet, $columnID, $rowID);
474
                        }
475
476 29
                        if (isset($cell_ss['StyleID'])) {
477 17
                            $style = (string) $cell_ss['StyleID'];
478 17
                            if ((isset($this->styles[$style])) && (!empty($this->styles[$style]))) {
479
                                //if (!$spreadsheet->getActiveSheet()->cellExists($columnID . $rowID)) {
480
                                //    $spreadsheet->getActiveSheet()->getCell($columnID . $rowID)->setValue(null);
481
                                //}
482 17
                                $spreadsheet->getActiveSheet()->getStyle($cellRange)
483 17
                                    ->applyFromArray($this->styles[$style]);
484
                            }
485
                        }
486 29
                        ++$columnID;
487 29
                        while ($additionalMergedCells > 0) {
488 9
                            ++$columnID;
489 9
                            --$additionalMergedCells;
490
                        }
491
                    }
492
493 29
                    if ($rowHasData) {
494 29
                        if (isset($row_ss['Height'])) {
495 12
                            $rowHeight = $row_ss['Height'];
496 12
                            $spreadsheet->getActiveSheet()->getRowDimension($rowID)->setRowHeight((float) $rowHeight);
497
                        }
498
                    }
499
500 29
                    ++$rowID;
501
                }
502
            }
503
504 30
            $dataValidations = new Xml\DataValidations();
505 30
            $dataValidations->loadDataValidations($worksheet, $spreadsheet);
506 30
            $xmlX = $worksheet->children(Namespaces::URN_EXCEL);
507 30
            if (isset($xmlX->WorksheetOptions)) {
508 27
                if (isset($xmlX->WorksheetOptions->FreezePanes)) {
509 4
                    $freezeRow = $freezeColumn = 1;
510 4
                    if (isset($xmlX->WorksheetOptions->SplitHorizontal)) {
511 4
                        $freezeRow = (int) $xmlX->WorksheetOptions->SplitHorizontal + 1;
512
                    }
513 4
                    if (isset($xmlX->WorksheetOptions->SplitVertical)) {
514 4
                        $freezeColumn = (int) $xmlX->WorksheetOptions->SplitVertical + 1;
515
                    }
516 4
                    $leftTopRow = (string) $xmlX->WorksheetOptions->TopRowBottomPane;
517 4
                    $leftTopColumn = (string) $xmlX->WorksheetOptions->LeftColumnRightPane;
518 4
                    if (is_numeric($leftTopRow) && is_numeric($leftTopColumn)) {
519 4
                        $leftTopCoordinate = Coordinate::stringFromColumnIndex((int) $leftTopColumn + 1) . (string) ($leftTopRow + 1);
520 4
                        $spreadsheet->getActiveSheet()->freezePane(Coordinate::stringFromColumnIndex($freezeColumn) . (string) $freezeRow, $leftTopCoordinate, !isset($xmlX->WorksheetOptions->FrozenNoSplit));
521
                    } else {
522 4
                        $spreadsheet->getActiveSheet()->freezePane(Coordinate::stringFromColumnIndex($freezeColumn) . (string) $freezeRow, null, !isset($xmlX->WorksheetOptions->FrozenNoSplit));
523
                    }
524 26
                } elseif (isset($xmlX->WorksheetOptions->SplitVertical) || isset($xmlX->WorksheetOptions->SplitHorizontal)) {
525 1
                    if (isset($xmlX->WorksheetOptions->SplitHorizontal)) {
526 1
                        $ySplit = (int) $xmlX->WorksheetOptions->SplitHorizontal;
527 1
                        $spreadsheet->getActiveSheet()->setYSplit($ySplit);
528
                    }
529 1
                    if (isset($xmlX->WorksheetOptions->SplitVertical)) {
530 1
                        $xSplit = (int) $xmlX->WorksheetOptions->SplitVertical;
531 1
                        $spreadsheet->getActiveSheet()->setXSplit($xSplit);
532
                    }
533 1
                    if (isset($xmlX->WorksheetOptions->LeftColumnVisible) || isset($xmlX->WorksheetOptions->TopRowVisible)) {
534 1
                        $leftTopColumn = $leftTopRow = 1;
535 1
                        if (isset($xmlX->WorksheetOptions->LeftColumnVisible)) {
536 1
                            $leftTopColumn = 1 + (int) $xmlX->WorksheetOptions->LeftColumnVisible;
537
                        }
538 1
                        if (isset($xmlX->WorksheetOptions->TopRowVisible)) {
539 1
                            $leftTopRow = 1 + (int) $xmlX->WorksheetOptions->TopRowVisible;
540
                        }
541 1
                        $leftTopCoordinate = Coordinate::stringFromColumnIndex($leftTopColumn) . "$leftTopRow";
542 1
                        $spreadsheet->getActiveSheet()->setTopLeftCell($leftTopCoordinate);
543
                    }
544
545 1
                    $leftTopColumn = $leftTopRow = 1;
546 1
                    if (isset($xmlX->WorksheetOptions->LeftColumnRightPane)) {
547 1
                        $leftTopColumn = 1 + (int) $xmlX->WorksheetOptions->LeftColumnRightPane;
548
                    }
549 1
                    if (isset($xmlX->WorksheetOptions->TopRowBottomPane)) {
550 1
                        $leftTopRow = 1 + (int) $xmlX->WorksheetOptions->TopRowBottomPane;
551
                    }
552 1
                    $leftTopCoordinate = Coordinate::stringFromColumnIndex($leftTopColumn) . "$leftTopRow";
553 1
                    $spreadsheet->getActiveSheet()->setPaneTopLeftCell($leftTopCoordinate);
554
                }
555 27
                (new PageSettings($xmlX))->loadPageSettings($spreadsheet);
556 27
                if (isset($xmlX->WorksheetOptions->TopRowVisible, $xmlX->WorksheetOptions->LeftColumnVisible)) {
557 2
                    $leftTopRow = (string) $xmlX->WorksheetOptions->TopRowVisible;
558 2
                    $leftTopColumn = (string) $xmlX->WorksheetOptions->LeftColumnVisible;
559 2
                    if (is_numeric($leftTopRow) && is_numeric($leftTopColumn)) {
560 2
                        $leftTopCoordinate = Coordinate::stringFromColumnIndex((int) $leftTopColumn + 1) . (string) ($leftTopRow + 1);
561 2
                        $spreadsheet->getActiveSheet()->setTopLeftCell($leftTopCoordinate);
562
                    }
563
                }
564 27
                $rangeCalculated = false;
565 27
                if (isset($xmlX->WorksheetOptions->Panes->Pane->RangeSelection)) {
566 13
                    if (1 === preg_match('/^R(\d+)C(\d+):R(\d+)C(\d+)$/', (string) $xmlX->WorksheetOptions->Panes->Pane->RangeSelection, $selectionMatches)) {
567 13
                        $selectedCell = Coordinate::stringFromColumnIndex((int) $selectionMatches[2])
568 13
                            . $selectionMatches[1]
569 13
                            . ':'
570 13
                            . Coordinate::stringFromColumnIndex((int) $selectionMatches[4])
571 13
                            . $selectionMatches[3];
572 13
                        $spreadsheet->getActiveSheet()->setSelectedCells($selectedCell);
573 13
                        $rangeCalculated = true;
574
                    }
575
                }
576 27
                if (!$rangeCalculated) {
577 26
                    if (isset($xmlX->WorksheetOptions->Panes->Pane->ActiveRow)) {
578 21
                        $activeRow = (string) $xmlX->WorksheetOptions->Panes->Pane->ActiveRow;
579
                    } else {
580 7
                        $activeRow = 0;
581
                    }
582 26
                    if (isset($xmlX->WorksheetOptions->Panes->Pane->ActiveCol)) {
583 15
                        $activeColumn = (string) $xmlX->WorksheetOptions->Panes->Pane->ActiveCol;
584
                    } else {
585 13
                        $activeColumn = 0;
586
                    }
587 26
                    if (is_numeric($activeRow) && is_numeric($activeColumn)) {
588 26
                        $selectedCell = Coordinate::stringFromColumnIndex((int) $activeColumn + 1) . (string) ($activeRow + 1);
589 26
                        $spreadsheet->getActiveSheet()->setSelectedCells($selectedCell);
590
                    }
591
                }
592
            }
593 30
            ++$worksheetID;
594
        }
595
596
        // Globally scoped defined names
597 31
        $activeSheetIndex = 0;
598 31
        if (isset($xml->ExcelWorkbook->ActiveSheet)) {
599 2
            $activeSheetIndex = (int) (string) $xml->ExcelWorkbook->ActiveSheet;
600
        }
601 31
        $activeWorksheet = $spreadsheet->setActiveSheetIndex($activeSheetIndex);
602 30
        if (isset($xml->Names[0])) {
603 10
            foreach ($xml->Names[0] as $definedName) {
604 10
                $definedName_ss = self::getAttributes($definedName, self::NAMESPACES_SS);
605 10
                $name = (string) $definedName_ss['Name'];
606 10
                $definedValue = (string) $definedName_ss['RefersTo'];
607 10
                $convertedValue = AddressHelper::convertFormulaToA1($definedValue);
608 10
                if ($convertedValue[0] === '=') {
609 10
                    $convertedValue = substr($convertedValue, 1);
610
                }
611 10
                $spreadsheet->addDefinedName(DefinedName::createInstance($name, $activeWorksheet, $convertedValue));
612
            }
613
        }
614
615
        // Return
616 30
        return $spreadsheet;
617
    }
618
619 10
    protected function parseCellComment(
620
        SimpleXMLElement $comment,
621
        Spreadsheet $spreadsheet,
622
        string $columnID,
623
        int $rowID
624
    ): void {
625 10
        $commentAttributes = $comment->attributes(self::NAMESPACES_SS);
626 10
        $author = 'unknown';
627 10
        if (isset($commentAttributes->Author)) {
628 9
            $author = (string) $commentAttributes->Author;
629
        }
630
631 10
        $node = $comment->Data->asXML();
632 10
        $annotation = strip_tags((string) $node);
633 10
        $spreadsheet->getActiveSheet()->getComment($columnID . $rowID)
634 10
            ->setAuthor($author)
635 10
            ->setText($this->parseRichText($annotation));
636
    }
637
638 10
    protected function parseRichText(string $annotation): RichText
639
    {
640 10
        $value = new RichText();
641
642 10
        $value->createText($annotation);
643
644 10
        return $value;
645
    }
646
647 33
    private static function getAttributes(?SimpleXMLElement $simple, string $node): SimpleXMLElement
648
    {
649 33
        return ($simple === null)
650
            ? new SimpleXMLElement('<xml></xml>')
651 33
            : ($simple->attributes($node) ?? new SimpleXMLElement('<xml></xml>'));
652
    }
653
}
654