Xlsx::listWorksheetInfo()   D
last analyzed

Complexity

Conditions 18
Paths 38

Size

Total Lines 90
Code Lines 62

Duplication

Lines 0
Ratio 0 %

Code Coverage

Tests 66
CRAP Score 18

Importance

Changes 1
Bugs 0 Features 0
Metric Value
eloc 62
c 1
b 0
f 0
dl 0
loc 90
ccs 66
cts 66
cp 1
rs 4.8666
cc 18
nc 38
nop 1
crap 18

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 PhpOffice\PhpSpreadsheet\Calculation\Information\ExcelError;
6
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
7
use PhpOffice\PhpSpreadsheet\Cell\DataType;
8
use PhpOffice\PhpSpreadsheet\Cell\Hyperlink;
9
use PhpOffice\PhpSpreadsheet\Comment;
10
use PhpOffice\PhpSpreadsheet\DefinedName;
11
use PhpOffice\PhpSpreadsheet\Reader\Security\XmlScanner;
12
use PhpOffice\PhpSpreadsheet\Reader\Xlsx\AutoFilter;
13
use PhpOffice\PhpSpreadsheet\Reader\Xlsx\Chart;
14
use PhpOffice\PhpSpreadsheet\Reader\Xlsx\ColumnAndRowAttributes;
15
use PhpOffice\PhpSpreadsheet\Reader\Xlsx\ConditionalStyles;
16
use PhpOffice\PhpSpreadsheet\Reader\Xlsx\DataValidations;
17
use PhpOffice\PhpSpreadsheet\Reader\Xlsx\Hyperlinks;
18
use PhpOffice\PhpSpreadsheet\Reader\Xlsx\Namespaces;
19
use PhpOffice\PhpSpreadsheet\Reader\Xlsx\PageSetup;
20
use PhpOffice\PhpSpreadsheet\Reader\Xlsx\Properties as PropertyReader;
21
use PhpOffice\PhpSpreadsheet\Reader\Xlsx\SharedFormula;
22
use PhpOffice\PhpSpreadsheet\Reader\Xlsx\SheetViewOptions;
23
use PhpOffice\PhpSpreadsheet\Reader\Xlsx\SheetViews;
24
use PhpOffice\PhpSpreadsheet\Reader\Xlsx\Styles;
25
use PhpOffice\PhpSpreadsheet\Reader\Xlsx\TableReader;
26
use PhpOffice\PhpSpreadsheet\Reader\Xlsx\Theme;
27
use PhpOffice\PhpSpreadsheet\Reader\Xlsx\WorkbookView;
28
use PhpOffice\PhpSpreadsheet\ReferenceHelper;
29
use PhpOffice\PhpSpreadsheet\RichText\RichText;
30
use PhpOffice\PhpSpreadsheet\Shared\Date;
31
use PhpOffice\PhpSpreadsheet\Shared\Drawing;
32
use PhpOffice\PhpSpreadsheet\Shared\File;
33
use PhpOffice\PhpSpreadsheet\Shared\Font;
34
use PhpOffice\PhpSpreadsheet\Shared\StringHelper;
35
use PhpOffice\PhpSpreadsheet\Spreadsheet;
36
use PhpOffice\PhpSpreadsheet\Style\Color;
37
use PhpOffice\PhpSpreadsheet\Style\Font as StyleFont;
38
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
39
use PhpOffice\PhpSpreadsheet\Style\Style;
40
use PhpOffice\PhpSpreadsheet\Worksheet\HeaderFooterDrawing;
41
use PhpOffice\PhpSpreadsheet\Worksheet\Table\TableDxfsStyle;
42
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
43
use SimpleXMLElement;
44
use Throwable;
45
use XMLReader;
46
use ZipArchive;
47
48
class Xlsx extends BaseReader
49
{
50
    const INITIAL_FILE = '_rels/.rels';
51
52
    /**
53
     * ReferenceHelper instance.
54
     */
55
    private ReferenceHelper $referenceHelper;
56
57
    private ZipArchive $zip;
58
59
    private Styles $styleReader;
60
61
    /** @var SharedFormula[] */
62
    private array $sharedFormulae = [];
63
64
    private bool $parseHuge = false;
65
66
    /**
67
     * Allow use of LIBXML_PARSEHUGE.
68
     * This option can lead to memory leaks and failures,
69
     * and is not recommended. But some very large spreadsheets
70
     * seem to require it.
71
     */
72
    public function setParseHuge(bool $parseHuge): void
73
    {
74
        $this->parseHuge = $parseHuge;
75
    }
76
77
    /**
78
     * Create a new Xlsx Reader instance.
79
     */
80 744
    public function __construct()
81
    {
82 744
        parent::__construct();
83 744
        $this->referenceHelper = ReferenceHelper::getInstance();
84 744
        $this->securityScanner = XmlScanner::getInstance($this);
85
    }
86
87
    /**
88
     * Can the current IReader read the file?
89
     */
90 34
    public function canRead(string $filename): bool
91
    {
92 34
        if (!File::testFileNoThrow($filename, self::INITIAL_FILE)) {
93 14
            return false;
94
        }
95
96 20
        $result = false;
97 20
        $this->zip = $zip = new ZipArchive();
98
99 20
        if ($zip->open($filename) === true) {
100 20
            [$workbookBasename] = $this->getWorkbookBaseName();
101 20
            $result = !empty($workbookBasename);
102
103 20
            $zip->close();
104
        }
105
106 20
        return $result;
107
    }
108
109 720
    public static function testSimpleXml(mixed $value): SimpleXMLElement
110
    {
111 720
        return ($value instanceof SimpleXMLElement) ? $value : new SimpleXMLElement('<?xml version="1.0" encoding="UTF-8"?><root></root>');
112
    }
113
114 716
    public static function getAttributes(?SimpleXMLElement $value, string $ns = ''): SimpleXMLElement
115
    {
116 716
        return self::testSimpleXml($value === null ? $value : $value->attributes($ns));
117
    }
118
119
    // Phpstan thinks, correctly, that xpath can return false.
120
    /** @return mixed[] */
121 686
    private static function xpathNoFalse(SimpleXMLElement $sxml, string $path): array
122
    {
123 686
        return self::falseToArray($sxml->xpath($path));
124
    }
125
126
    /** @return mixed[] */
127 686
    public static function falseToArray(mixed $value): array
128
    {
129 686
        return is_array($value) ? $value : [];
130
    }
131
132 716
    private function loadZip(string $filename, string $ns = '', bool $replaceUnclosedBr = false): SimpleXMLElement
133
    {
134 716
        $contents = $this->getFromZipArchive($this->zip, $filename);
135 716
        if ($replaceUnclosedBr) {
136 37
            $contents = str_replace('<br>', '<br/>', $contents);
137
        }
138 716
        $rels = @simplexml_load_string(
139 716
            $this->getSecurityScannerOrThrow()->scan($contents),
140 716
            SimpleXMLElement::class,
141 716
            $this->parseHuge ? LIBXML_PARSEHUGE : 0,
142 716
            $ns
143 716
        );
144
145 716
        return self::testSimpleXml($rels);
146
    }
147
148
    // This function is just to identify cases where I'm not sure
149
    // why empty namespace is required.
150 684
    private function loadZipNonamespace(string $filename, string $ns): SimpleXMLElement
151
    {
152 684
        $contents = $this->getFromZipArchive($this->zip, $filename);
153 684
        $rels = simplexml_load_string(
154 684
            $this->getSecurityScannerOrThrow()->scan($contents),
155 684
            SimpleXMLElement::class,
156 684
            $this->parseHuge ? LIBXML_PARSEHUGE : 0,
157 684
            ($ns === '' ? $ns : '')
158 684
        );
159
160 679
        return self::testSimpleXml($rels);
161
    }
162
163
    private const REL_TO_MAIN = [
164
        Namespaces::PURL_OFFICE_DOCUMENT => Namespaces::PURL_MAIN,
165
        Namespaces::THUMBNAIL => '',
166
    ];
167
168
    private const REL_TO_DRAWING = [
169
        Namespaces::PURL_RELATIONSHIPS => Namespaces::PURL_DRAWING,
170
    ];
171
172
    private const REL_TO_CHART = [
173
        Namespaces::PURL_RELATIONSHIPS => Namespaces::PURL_CHART,
174
    ];
175
176
    /**
177
     * Reads names of the worksheets from a file, without parsing the whole file to a Spreadsheet object.
178
     *
179
     * @return string[]
180
     */
181 18
    public function listWorksheetNames(string $filename): array
182
    {
183 18
        File::assertFile($filename, self::INITIAL_FILE);
184
185 15
        $worksheetNames = [];
186
187 15
        $this->zip = $zip = new ZipArchive();
188 15
        $zip->open($filename);
189
190
        //    The files we're looking at here are small enough that simpleXML is more efficient than XMLReader
191 15
        $rels = $this->loadZip(self::INITIAL_FILE, Namespaces::RELATIONSHIPS);
192 15
        foreach ($rels->Relationship as $relx) {
193 15
            $rel = self::getAttributes($relx);
194 15
            $relType = (string) $rel['Type'];
195 15
            $mainNS = self::REL_TO_MAIN[$relType] ?? Namespaces::MAIN;
196 15
            if ($mainNS !== '') {
197 15
                $xmlWorkbook = $this->loadZip((string) $rel['Target'], $mainNS);
198
199 15
                if ($xmlWorkbook->sheets) {
200 15
                    foreach ($xmlWorkbook->sheets->sheet as $eleSheet) {
201
                        // Check if sheet should be skipped
202 15
                        $worksheetNames[] = (string) self::getAttributes($eleSheet)['name'];
203
                    }
204
                }
205
            }
206
        }
207
208 15
        $zip->close();
209
210 15
        return $worksheetNames;
211
    }
212
213
    /**
214
     * Return worksheet info (Name, Last Column Letter, Last Column Index, Total Rows, Total Columns).
215
     *
216
     * @return array<int, array{worksheetName: string, lastColumnLetter: string, lastColumnIndex: int, totalRows: int, totalColumns: int, sheetState: string}>
217
     */
218 19
    public function listWorksheetInfo(string $filename): array
219
    {
220 19
        File::assertFile($filename, self::INITIAL_FILE);
221
222 16
        $worksheetInfo = [];
223
224 16
        $this->zip = $zip = new ZipArchive();
225 16
        $zip->open($filename);
226
227 16
        $rels = $this->loadZip(self::INITIAL_FILE, Namespaces::RELATIONSHIPS);
228 16
        foreach ($rels->Relationship as $relx) {
229 16
            $rel = self::getAttributes($relx);
230 16
            $relType = (string) $rel['Type'];
231 16
            $mainNS = self::REL_TO_MAIN[$relType] ?? Namespaces::MAIN;
232 16
            if ($mainNS !== '') {
233 16
                $relTarget = (string) $rel['Target'];
234 16
                $dir = dirname($relTarget);
235 16
                $namespace = dirname($relType);
236 16
                $relsWorkbook = $this->loadZip("$dir/_rels/" . basename($relTarget) . '.rels', Namespaces::RELATIONSHIPS);
237
238 16
                $worksheets = [];
239 16
                foreach ($relsWorkbook->Relationship as $elex) {
240 16
                    $ele = self::getAttributes($elex);
241
                    if (
242 16
                        ((string) $ele['Type'] === "$namespace/worksheet")
243 16
                        || ((string) $ele['Type'] === "$namespace/chartsheet")
244
                    ) {
245 16
                        $worksheets[(string) $ele['Id']] = $ele['Target'];
246
                    }
247
                }
248
249 16
                $xmlWorkbook = $this->loadZip($relTarget, $mainNS);
250 16
                if ($xmlWorkbook->sheets) {
251 16
                    $dir = dirname($relTarget);
252
253 16
                    foreach ($xmlWorkbook->sheets->sheet as $eleSheet) {
254 16
                        $tmpInfo = [
255 16
                            'worksheetName' => (string) self::getAttributes($eleSheet)['name'],
256 16
                            'lastColumnLetter' => 'A',
257 16
                            'lastColumnIndex' => 0,
258 16
                            'totalRows' => 0,
259 16
                            'totalColumns' => 0,
260 16
                        ];
261 16
                        $sheetState = (string) (self::getAttributes($eleSheet)['state'] ?? Worksheet::SHEETSTATE_VISIBLE);
262 16
                        $tmpInfo['sheetState'] = $sheetState;
263
264 16
                        $fileWorksheet = (string) $worksheets[self::getArrayItemString(self::getAttributes($eleSheet, $namespace), 'id')];
265 16
                        $fileWorksheetPath = str_starts_with($fileWorksheet, '/') ? substr($fileWorksheet, 1) : "$dir/$fileWorksheet";
266
267 16
                        $xml = new XMLReader();
268 16
                        $xml->xml(
269 16
                            $this->getSecurityScannerOrThrow()
270 16
                                ->scan(
271 16
                                    $this->getFromZipArchive(
272 16
                                        $this->zip,
273 16
                                        $fileWorksheetPath
274 16
                                    )
275 16
                                ),
276 16
                            null,
277 16
                            $this->parseHuge ? LIBXML_PARSEHUGE : 0
278 16
                        );
279 16
                        $xml->setParserProperty(2, true);
280
281 16
                        $currCells = 0;
282 16
                        while ($xml->read()) {
283 16
                            if ($xml->localName == 'row' && $xml->nodeType == XMLReader::ELEMENT && $xml->namespaceURI === $mainNS) {
284 16
                                $row = (int) $xml->getAttribute('r');
285 16
                                $tmpInfo['totalRows'] = $row;
286 16
                                $tmpInfo['totalColumns'] = max($tmpInfo['totalColumns'], $currCells);
287 16
                                $currCells = 0;
288 16
                            } elseif ($xml->localName == 'c' && $xml->nodeType == XMLReader::ELEMENT && $xml->namespaceURI === $mainNS) {
289 16
                                $cell = $xml->getAttribute('r');
290 16
                                $currCells = $cell ? max($currCells, Coordinate::indexesFromString($cell)[0]) : ($currCells + 1);
291
                            }
292
                        }
293 16
                        $tmpInfo['totalColumns'] = max($tmpInfo['totalColumns'], $currCells);
294 16
                        $xml->close();
295
296 16
                        $tmpInfo['lastColumnIndex'] = $tmpInfo['totalColumns'] - 1;
297 16
                        $tmpInfo['lastColumnLetter'] = Coordinate::stringFromColumnIndex($tmpInfo['lastColumnIndex'] + 1);
298
299 16
                        $worksheetInfo[] = $tmpInfo;
300
                    }
301
                }
302
            }
303
        }
304
305 16
        $zip->close();
306
307 16
        return $worksheetInfo;
308
    }
309
310 22
    private static function castToBoolean(SimpleXMLElement $c): bool
311
    {
312 22
        $value = isset($c->v) ? (string) $c->v : null;
313 22
        if ($value == '0') {
314 15
            return false;
315 18
        } elseif ($value == '1') {
316 18
            return true;
317
        }
318
319
        return (bool) $c->v;
320
    }
321
322 189
    private static function castToError(?SimpleXMLElement $c): ?string
323
    {
324 189
        return isset($c, $c->v) ? (string) $c->v : null;
325
    }
326
327 540
    private static function castToString(?SimpleXMLElement $c): ?string
328
    {
329 540
        return isset($c, $c->v) ? (string) $c->v : null;
330
    }
331
332 375
    public static function replacePrefixes(string $formula): string
333
    {
334 375
        return str_replace(['_xlfn.', '_xlws.'], '', $formula);
335
    }
336
337 365
    private function castToFormula(?SimpleXMLElement $c, string $r, string &$cellDataType, mixed &$value, mixed &$calculatedValue, string $castBaseType, bool $updateSharedCells = true): void
338
    {
339 365
        if ($c === null) {
340
            return;
341
        }
342 365
        $attr = $c->f->attributes();
343 365
        $cellDataType = DataType::TYPE_FORMULA;
344 365
        $formula = self::replacePrefixes((string) $c->f);
345 365
        $value = "=$formula";
346 365
        $calculatedValue = self::$castBaseType($c);
347
348
        // Shared formula?
349 365
        if (isset($attr['t']) && strtolower((string) $attr['t']) == 'shared') {
350 219
            $instance = (string) $attr['si'];
351
352 219
            if (!isset($this->sharedFormulae[(string) $attr['si']])) {
353 219
                $this->sharedFormulae[$instance] = new SharedFormula($r, $value);
354 218
            } elseif ($updateSharedCells === true) {
355
                // It's only worth the overhead of adjusting the shared formula for this cell if we're actually loading
356
                //     the cell, which may not be the case if we're using a read filter.
357 218
                $master = Coordinate::indexesFromString($this->sharedFormulae[$instance]->master());
358 218
                $current = Coordinate::indexesFromString($r);
359
360 218
                $difference = [0, 0];
361 218
                $difference[0] = $current[0] - $master[0];
362 218
                $difference[1] = $current[1] - $master[1];
363
364 218
                $value = $this->referenceHelper->updateFormulaReferences($this->sharedFormulae[$instance]->formula(), 'A1', $difference[0], $difference[1]);
365
            }
366
        }
367
    }
368
369 671
    private function fileExistsInArchive(ZipArchive $archive, string $fileName = ''): bool
370
    {
371
        // Root-relative paths
372 671
        if (str_contains($fileName, '//')) {
373 1
            $fileName = substr($fileName, strpos($fileName, '//') + 1);
374
        }
375 671
        $fileName = File::realpath($fileName);
376
377
        // Sadly, some 3rd party xlsx generators don't use consistent case for filenaming
378
        //    so we need to load case-insensitively from the zip file
379
380
        // Apache POI fixes
381 671
        $contents = $archive->locateName($fileName, ZipArchive::FL_NOCASE);
382 671
        if ($contents === false) {
383 4
            $contents = $archive->locateName(substr($fileName, 1), ZipArchive::FL_NOCASE);
384
        }
385
386 671
        return $contents !== false;
387
    }
388
389 716
    private function getFromZipArchive(ZipArchive $archive, string $fileName = ''): string
390
    {
391
        // Root-relative paths
392 716
        if (str_contains($fileName, '//')) {
393 2
            $fileName = substr($fileName, strpos($fileName, '//') + 1);
394
        }
395
        // Relative paths generated by dirname($filename) when $filename
396
        // has no path (i.e.files in root of the zip archive)
397 716
        $fileName = (string) preg_replace('/^\.\//', '', $fileName);
398 716
        $fileName = File::realpath($fileName);
399
400
        // Sadly, some 3rd party xlsx generators don't use consistent case for filenaming
401
        //    so we need to load case-insensitively from the zip file
402
403 716
        $contents = $archive->getFromName($fileName, 0, ZipArchive::FL_NOCASE);
404
405
        // Apache POI fixes
406 716
        if ($contents === false) {
407 52
            $contents = $archive->getFromName(substr($fileName, 1), 0, ZipArchive::FL_NOCASE);
408
        }
409
410
        // Has the file been saved with Windoze directory separators rather than unix?
411 716
        if ($contents === false) {
412 49
            $contents = $archive->getFromName(str_replace('/', '\\', $fileName), 0, ZipArchive::FL_NOCASE);
413
        }
414
415 716
        return ($contents === false) ? '' : $contents;
416
    }
417
418
    /**
419
     * Loads Spreadsheet from file.
420
     */
421 689
    protected function loadSpreadsheetFromFile(string $filename): Spreadsheet
422
    {
423 689
        File::assertFile($filename, self::INITIAL_FILE);
424
425
        // Initialisations
426 686
        $excel = $this->newSpreadsheet();
427 686
        $excel->setValueBinder($this->valueBinder);
428 686
        $excel->removeSheetByIndex(0);
429 686
        $addingFirstCellStyleXf = true;
430 686
        $addingFirstCellXf = true;
431
432
        /** @var mixed[][][][] */
433 686
        $unparsedLoadedData = [];
434
435 686
        $this->zip = $zip = new ZipArchive();
436 686
        $zip->open($filename);
437
438
        //    Read the theme first, because we need the colour scheme when reading the styles
439 686
        [$workbookBasename, $xmlNamespaceBase] = $this->getWorkbookBaseName();
440 686
        $drawingNS = self::REL_TO_DRAWING[$xmlNamespaceBase] ?? Namespaces::DRAWINGML;
441 686
        $chartNS = self::REL_TO_CHART[$xmlNamespaceBase] ?? Namespaces::CHART;
442 686
        $wbRels = $this->loadZip("xl/_rels/{$workbookBasename}.rels", Namespaces::RELATIONSHIPS);
443 686
        $theme = null;
444 686
        $this->styleReader = new Styles();
445 686
        foreach ($wbRels->Relationship as $relx) {
446 685
            $rel = self::getAttributes($relx);
447 685
            $relTarget = (string) $rel['Target'];
448 685
            if (str_starts_with($relTarget, '/xl/')) {
449 12
                $relTarget = substr($relTarget, 4);
450
            }
451 685
            switch ($rel['Type']) {
452 685
                case "$xmlNamespaceBase/theme":
453 669
                    if (!$this->fileExistsInArchive($zip, "xl/{$relTarget}")) {
454 3
                        break; // issue3770
455
                    }
456 666
                    $themeOrderArray = ['lt1', 'dk1', 'lt2', 'dk2'];
457 666
                    $themeOrderAdditional = count($themeOrderArray);
458
459 666
                    $xmlTheme = $this->loadZip("xl/{$relTarget}", $drawingNS);
460 666
                    $xmlThemeName = self::getAttributes($xmlTheme);
461 666
                    $xmlTheme = $xmlTheme->children($drawingNS);
462 666
                    $themeName = (string) $xmlThemeName['name'];
463
464 666
                    $colourScheme = self::getAttributes($xmlTheme->themeElements->clrScheme);
465 666
                    $colourSchemeName = (string) $colourScheme['name'];
466 666
                    $excel->getTheme()->setThemeColorName($colourSchemeName);
467 666
                    $colourScheme = $xmlTheme->themeElements->clrScheme->children($drawingNS);
468
469 666
                    $themeColours = [];
470 666
                    foreach ($colourScheme as $k => $xmlColour) {
471 666
                        $themePos = array_search($k, $themeOrderArray);
472 666
                        if ($themePos === false) {
473 666
                            $themePos = $themeOrderAdditional++;
474
                        }
475 666
                        if (isset($xmlColour->sysClr)) {
476 647
                            $xmlColourData = self::getAttributes($xmlColour->sysClr);
477 647
                            $themeColours[$themePos] = (string) $xmlColourData['lastClr'];
478 647
                            $excel->getTheme()->setThemeColor($k, (string) $xmlColourData['lastClr']);
479 666
                        } elseif (isset($xmlColour->srgbClr)) {
480 666
                            $xmlColourData = self::getAttributes($xmlColour->srgbClr);
481 666
                            $themeColours[$themePos] = (string) $xmlColourData['val'];
482 666
                            $excel->getTheme()->setThemeColor($k, (string) $xmlColourData['val']);
483
                        }
484
                    }
485 666
                    $theme = new Theme($themeName, $colourSchemeName, $themeColours);
486 666
                    $this->styleReader->setTheme($theme);
487
488 666
                    $fontScheme = self::getAttributes($xmlTheme->themeElements->fontScheme);
489 666
                    $fontSchemeName = (string) $fontScheme['name'];
490 666
                    $excel->getTheme()->setThemeFontName($fontSchemeName);
491 666
                    $majorFonts = [];
492 666
                    $minorFonts = [];
493 666
                    $fontScheme = $xmlTheme->themeElements->fontScheme->children($drawingNS);
494 666
                    $majorLatin = self::getAttributes($fontScheme->majorFont->latin)['typeface'] ?? '';
495 666
                    $majorEastAsian = self::getAttributes($fontScheme->majorFont->ea)['typeface'] ?? '';
496 666
                    $majorComplexScript = self::getAttributes($fontScheme->majorFont->cs)['typeface'] ?? '';
497 666
                    $minorLatin = self::getAttributes($fontScheme->minorFont->latin)['typeface'] ?? '';
498 666
                    $minorEastAsian = self::getAttributes($fontScheme->minorFont->ea)['typeface'] ?? '';
499 666
                    $minorComplexScript = self::getAttributes($fontScheme->minorFont->cs)['typeface'] ?? '';
500
501 666
                    foreach ($fontScheme->majorFont->font as $xmlFont) {
502 645
                        $fontAttributes = self::getAttributes($xmlFont);
503 645
                        $script = (string) ($fontAttributes['script'] ?? '');
504 645
                        if (!empty($script)) {
505 645
                            $majorFonts[$script] = (string) ($fontAttributes['typeface'] ?? '');
506
                        }
507
                    }
508 666
                    foreach ($fontScheme->minorFont->font as $xmlFont) {
509 645
                        $fontAttributes = self::getAttributes($xmlFont);
510 645
                        $script = (string) ($fontAttributes['script'] ?? '');
511 645
                        if (!empty($script)) {
512 645
                            $minorFonts[$script] = (string) ($fontAttributes['typeface'] ?? '');
513
                        }
514
                    }
515 666
                    $excel->getTheme()->setMajorFontValues($majorLatin, $majorEastAsian, $majorComplexScript, $majorFonts);
516 666
                    $excel->getTheme()->setMinorFontValues($minorLatin, $minorEastAsian, $minorComplexScript, $minorFonts);
517
518 666
                    break;
519
            }
520
        }
521
522 686
        $rels = $this->loadZip(self::INITIAL_FILE, Namespaces::RELATIONSHIPS);
523
524 686
        $propertyReader = new PropertyReader($this->getSecurityScannerOrThrow(), $excel->getProperties());
525 686
        $charts = $chartDetails = [];
526 686
        foreach ($rels->Relationship as $relx) {
527 686
            $rel = self::getAttributes($relx);
528 686
            $relTarget = (string) $rel['Target'];
529
            // issue 3553
530 686
            if ($relTarget[0] === '/') {
531 7
                $relTarget = substr($relTarget, 1);
532
            }
533 686
            $relType = (string) $rel['Type'];
534 686
            $mainNS = self::REL_TO_MAIN[$relType] ?? Namespaces::MAIN;
535
            switch ($relType) {
536 679
                case Namespaces::CORE_PROPERTIES:
537 667
                    $propertyReader->readCoreProperties($this->getFromZipArchive($zip, $relTarget));
538
539 667
                    break;
540 686
                case "$xmlNamespaceBase/extended-properties":
541 658
                    $propertyReader->readExtendedProperties($this->getFromZipArchive($zip, $relTarget));
542
543 658
                    break;
544 686
                case "$xmlNamespaceBase/custom-properties":
545 58
                    $propertyReader->readCustomProperties($this->getFromZipArchive($zip, $relTarget));
546
547 58
                    break;
548
                    //Ribbon
549 686
                case Namespaces::EXTENSIBILITY:
550 2
                    $customUI = $relTarget;
551 2
                    if ($customUI) {
552 2
                        $this->readRibbon($excel, $customUI, $zip);
553
                    }
554
555 2
                    break;
556 686
                case "$xmlNamespaceBase/officeDocument":
557 686
                    $dir = dirname($relTarget);
558
559
                    // Do not specify namespace in next stmt - do it in Xpath
560 686
                    $relsWorkbook = $this->loadZip("$dir/_rels/" . basename($relTarget) . '.rels', Namespaces::RELATIONSHIPS);
561 686
                    $relsWorkbook->registerXPathNamespace('rel', Namespaces::RELATIONSHIPS);
562
563 686
                    $worksheets = [];
564 686
                    $macros = $customUI = null;
565 686
                    foreach ($relsWorkbook->Relationship as $elex) {
566 686
                        $ele = self::getAttributes($elex);
567 686
                        switch ($ele['Type']) {
568 679
                            case Namespaces::WORKSHEET:
569 679
                            case Namespaces::PURL_WORKSHEET:
570 686
                                $worksheets[(string) $ele['Id']] = $ele['Target'];
571
572 686
                                break;
573 679
                            case Namespaces::CHARTSHEET:
574 2
                                if ($this->includeCharts === true) {
575 1
                                    $worksheets[(string) $ele['Id']] = $ele['Target'];
576
                                }
577
578 2
                                break;
579
                                // a vbaProject ? (: some macros)
580 679
                            case Namespaces::VBA:
581 3
                                $macros = $ele['Target'];
582
583 3
                                break;
584
                        }
585
                    }
586
587 686
                    if ($macros !== null) {
588 3
                        $macrosCode = $this->getFromZipArchive($zip, 'xl/vbaProject.bin'); //vbaProject.bin always in 'xl' dir and always named vbaProject.bin
589 3
                        if (!empty($macrosCode)) {
590 3
                            $excel->setMacrosCode($macrosCode);
591 3
                            $excel->setHasMacros(true);
592
                            //short-circuit : not reading vbaProject.bin.rel to get Signature =>allways vbaProjectSignature.bin in 'xl' dir
593 3
                            $Certificate = $this->getFromZipArchive($zip, 'xl/vbaProjectSignature.bin');
594 3
                            $excel->setMacrosCertificate($Certificate);
595
                        }
596
                    }
597
598 686
                    $relType = "rel:Relationship[@Type='"
599 686
                        . "$xmlNamespaceBase/styles"
600 686
                        . "']";
601
                    /** @var ?SimpleXMLElement */
602 686
                    $xpath = self::getArrayItem(self::xpathNoFalse($relsWorkbook, $relType));
603
604 686
                    if ($xpath === null) {
605 1
                        $xmlStyles = self::testSimpleXml(null);
606
                    } else {
607 686
                        $stylesTarget = (string) $xpath['Target'];
608 686
                        $stylesTarget = str_starts_with($stylesTarget, '/') ? substr($stylesTarget, 1) : "$dir/$stylesTarget";
609 686
                        $xmlStyles = $this->loadZip($stylesTarget, $mainNS);
610
                    }
611
612 686
                    $palette = self::extractPalette($xmlStyles);
613 686
                    $this->styleReader->setWorkbookPalette($palette);
614 686
                    $fills = self::extractStyles($xmlStyles, 'fills', 'fill');
615 686
                    $fonts = self::extractStyles($xmlStyles, 'fonts', 'font');
616 686
                    $borders = self::extractStyles($xmlStyles, 'borders', 'border');
617 686
                    $xfTags = self::extractStyles($xmlStyles, 'cellXfs', 'xf');
618 686
                    $cellXfTags = self::extractStyles($xmlStyles, 'cellStyleXfs', 'xf');
619
620 686
                    $styles = [];
621 686
                    $cellStyles = [];
622 686
                    $numFmts = null;
623 686
                    if (/*$xmlStyles && */ $xmlStyles->numFmts[0]) {
624 248
                        $numFmts = $xmlStyles->numFmts[0];
625
                    }
626 686
                    if (isset($numFmts)) {
627
                        /** @var SimpleXMLElement $numFmts */
628 248
                        $numFmts->registerXPathNamespace('sml', $mainNS);
629
                    }
630 686
                    $this->styleReader->setNamespace($mainNS);
631 686
                    if (!$this->readDataOnly/* && $xmlStyles*/) {
632 683
                        foreach ($xfTags as $xfTag) {
633
                            /** @var SimpleXMLElement $xfTag */
634 683
                            $xf = self::getAttributes($xfTag);
635 683
                            $numFmt = null;
636
637 683
                            if ($xf['numFmtId']) {
638 681
                                if (isset($numFmts)) {
639
                                    /** @var ?SimpleXMLElement */
640 248
                                    $tmpNumFmt = self::getArrayItem($numFmts->xpath("sml:numFmt[@numFmtId=$xf[numFmtId]]"));
641
642 248
                                    if (isset($tmpNumFmt['formatCode'])) {
643 247
                                        $numFmt = (string) $tmpNumFmt['formatCode'];
644
                                    }
645
                                }
646
647
                                // We shouldn't override any of the built-in MS Excel values (values below id 164)
648
                                //  But there's a lot of naughty homebrew xlsx writers that do use "reserved" id values that aren't actually used
649
                                //  So we make allowance for them rather than lose formatting masks
650
                                if (
651 681
                                    $numFmt === null
652 681
                                    && (int) $xf['numFmtId'] < 164
653 681
                                    && NumberFormat::builtInFormatCode((int) $xf['numFmtId']) !== ''
654
                                ) {
655 670
                                    $numFmt = NumberFormat::builtInFormatCode((int) $xf['numFmtId']);
656
                                }
657
                            }
658 683
                            $quotePrefix = (bool) (string) ($xf['quotePrefix'] ?? '');
659
660 683
                            $style = (object) [
661 683
                                'numFmt' => $numFmt ?? NumberFormat::FORMAT_GENERAL,
662 683
                                'font' => $fonts[(int) ($xf['fontId'])],
663 683
                                'fill' => $fills[(int) ($xf['fillId'])],
664 683
                                'border' => $borders[(int) ($xf['borderId'])],
665 683
                                'alignment' => $xfTag->alignment,
666 683
                                'protection' => $xfTag->protection,
667 683
                                'quotePrefix' => $quotePrefix,
668 683
                            ];
669 683
                            $styles[] = $style;
670
671
                            // add style to cellXf collection
672 683
                            $objStyle = new Style();
673 683
                            $this->styleReader
674 683
                                ->readStyle($objStyle, $style);
675 683
                            foreach ($this->styleReader->getFontCharsets() as $fontName => $charset) {
676 21
                                $excel->addFontCharset($fontName, $charset);
677
                            }
678 683
                            if ($addingFirstCellXf) {
679 683
                                $excel->removeCellXfByIndex(0); // remove the default style
680 683
                                $addingFirstCellXf = false;
681
                            }
682 683
                            $excel->addCellXf($objStyle);
683
                        }
684
685 683
                        foreach ($cellXfTags as $xfTag) {
686
                            /** @var SimpleXMLElement $xfTag */
687 682
                            $xf = self::getAttributes($xfTag);
688 682
                            $numFmt = NumberFormat::FORMAT_GENERAL;
689 682
                            if ($numFmts && $xf['numFmtId']) {
690
                                /** @var ?SimpleXMLElement */
691 248
                                $tmpNumFmt = self::getArrayItem($numFmts->xpath("sml:numFmt[@numFmtId=$xf[numFmtId]]"));
692 248
                                if (isset($tmpNumFmt['formatCode'])) {
693 29
                                    $numFmt = (string) $tmpNumFmt['formatCode'];
694 246
                                } elseif ((int) $xf['numFmtId'] < 165) {
695 246
                                    $numFmt = NumberFormat::builtInFormatCode((int) $xf['numFmtId']);
696
                                }
697
                            }
698
699 682
                            $quotePrefix = (bool) (string) ($xf['quotePrefix'] ?? '');
700
701 682
                            $cellStyle = (object) [
702 682
                                'numFmt' => $numFmt,
703 682
                                'font' => $fonts[(int) ($xf['fontId'])],
704 682
                                'fill' => $fills[((int) $xf['fillId'])],
705 682
                                'border' => $borders[(int) ($xf['borderId'])],
706 682
                                'alignment' => $xfTag->alignment,
707 682
                                'protection' => $xfTag->protection,
708 682
                                'quotePrefix' => $quotePrefix,
709 682
                            ];
710 682
                            $cellStyles[] = $cellStyle;
711
712
                            // add style to cellStyleXf collection
713 682
                            $objStyle = new Style();
714 682
                            $this->styleReader->readStyle($objStyle, $cellStyle);
715 682
                            if ($addingFirstCellStyleXf) {
716 682
                                $excel->removeCellStyleXfByIndex(0); // remove the default style
717 682
                                $addingFirstCellStyleXf = false;
718
                            }
719 682
                            $excel->addCellStyleXf($objStyle);
720
                        }
721
                    }
722 686
                    $this->styleReader->setStyleXml($xmlStyles);
723 686
                    $this->styleReader->setNamespace($mainNS);
724 686
                    $this->styleReader->setStyleBaseData($theme, $styles, $cellStyles);
725 686
                    $dxfs = $this->styleReader->dxfs($this->readDataOnly);
726 686
                    $tableStyles = $this->styleReader->tableStyles($this->readDataOnly);
727 686
                    $styles = $this->styleReader->styles();
728
729
                    // Read content after setting the styles
730 686
                    $sharedStrings = [];
731 686
                    $relType = "rel:Relationship[@Type='"
732 686
                        //. Namespaces::SHARED_STRINGS
733 686
                        . "$xmlNamespaceBase/sharedStrings"
734 686
                        . "']";
735
                    /** @var ?SimpleXMLElement */
736 686
                    $xpath = self::getArrayItem($relsWorkbook->xpath($relType));
737
738 686
                    if ($xpath) {
739 629
                        $sharedStringsTarget = (string) $xpath['Target'];
740 629
                        $sharedStringsTarget = str_starts_with($sharedStringsTarget, '/') ? substr($sharedStringsTarget, 1) : "$dir/$sharedStringsTarget";
741 629
                        $xmlStrings = $this->loadZip($sharedStringsTarget, $mainNS);
742 627
                        if (isset($xmlStrings->si)) {
743 501
                            foreach ($xmlStrings->si as $val) {
744 501
                                if (isset($val->t)) {
745 497
                                    $sharedStrings[] = StringHelper::controlCharacterOOXML2PHP((string) $val->t);
746 41
                                } elseif (isset($val->r)) {
747 41
                                    $sharedStrings[] = $this->parseRichText($val);
748
                                } else {
749 1
                                    $sharedStrings[] = '';
750
                                }
751
                            }
752
                        }
753
                    }
754
755 684
                    $xmlWorkbook = $this->loadZipNoNamespace($relTarget, $mainNS);
756 679
                    $xmlWorkbookNS = $this->loadZip($relTarget, $mainNS);
757
758
                    // Set base date
759 679
                    $excel->setExcelCalendar(Date::CALENDAR_WINDOWS_1900);
760 679
                    if ($xmlWorkbookNS->workbookPr) {
761 670
                        Date::setExcelCalendar(Date::CALENDAR_WINDOWS_1900);
762 670
                        $attrs1904 = self::getAttributes($xmlWorkbookNS->workbookPr);
763 670
                        if (isset($attrs1904['date1904'])) {
764 14
                            if (self::boolean((string) $attrs1904['date1904'])) {
765 3
                                Date::setExcelCalendar(Date::CALENDAR_MAC_1904);
766 3
                                $excel->setExcelCalendar(Date::CALENDAR_MAC_1904);
767
                            }
768
                        }
769
                    }
770
771
                    // Set protection
772 679
                    $this->readProtection($excel, $xmlWorkbook);
773
774 679
                    $sheetId = 0; // keep track of new sheet id in final workbook
775 679
                    $oldSheetId = -1; // keep track of old sheet id in final workbook
776 679
                    $countSkippedSheets = 0; // keep track of number of skipped sheets
777 679
                    $mapSheetId = []; // mapping of sheet ids from old to new
778
779 679
                    $charts = $chartDetails = [];
780
781 679
                    if ($xmlWorkbookNS->sheets) {
782 679
                        foreach ($xmlWorkbookNS->sheets->sheet as $eleSheet) {
783 679
                            $eleSheetAttr = self::getAttributes($eleSheet);
784 679
                            ++$oldSheetId;
785
786
                            // Check if sheet should be skipped
787 679
                            if (is_array($this->loadSheetsOnly) && !in_array((string) $eleSheetAttr['name'], $this->loadSheetsOnly)) {
788 6
                                ++$countSkippedSheets;
789 6
                                $mapSheetId[$oldSheetId] = null;
790
791 6
                                continue;
792
                            }
793
794 678
                            $sheetReferenceId = self::getArrayItemString(self::getAttributes($eleSheet, $xmlNamespaceBase), 'id');
795 678
                            if (isset($worksheets[$sheetReferenceId]) === false) {
796 1
                                ++$countSkippedSheets;
797 1
                                $mapSheetId[$oldSheetId] = null;
798
799 1
                                continue;
800
                            }
801
                            // Map old sheet id in original workbook to new sheet id.
802
                            // They will differ if loadSheetsOnly() is being used
803 678
                            $mapSheetId[$oldSheetId] = $oldSheetId - $countSkippedSheets;
804
805
                            // Load sheet
806 678
                            $docSheet = $excel->createSheet();
807
                            //    Use false for $updateFormulaCellReferences to prevent adjustment of worksheet
808
                            //        references in formula cells... during the load, all formulae should be correct,
809
                            //        and we're simply bringing the worksheet name in line with the formula, not the
810
                            //        reverse
811 678
                            $docSheet->setTitle((string) $eleSheetAttr['name'], false, false);
812
813 678
                            $fileWorksheet = (string) $worksheets[$sheetReferenceId];
814
                            // issue 3665 adds test for /.
815
                            // This broke XlsxRootZipFilesTest,
816
                            //  but Excel reports an error with that file.
817
                            //  Testing dir for . avoids this problem.
818
                            //  It might be better just to drop the test.
819 678
                            if ($fileWorksheet[0] == '/' && $dir !== '.') {
820 12
                                $fileWorksheet = substr($fileWorksheet, strlen($dir) + 2);
821
                            }
822 678
                            $xmlSheet = $this->loadZipNoNamespace("$dir/$fileWorksheet", $mainNS);
823 678
                            $xmlSheetNS = $this->loadZip("$dir/$fileWorksheet", $mainNS);
824
825
                            // Shared Formula table is unique to each Worksheet, so we need to reset it here
826 678
                            $this->sharedFormulae = [];
827
828 678
                            if (isset($eleSheetAttr['state']) && (string) $eleSheetAttr['state'] != '') {
829 38
                                $docSheet->setSheetState((string) $eleSheetAttr['state']);
830
                            }
831 678
                            if ($xmlSheetNS) {
832 678
                                $xmlSheetMain = $xmlSheetNS->children($mainNS);
833
                                // Setting Conditional Styles adjusts selected cells, so we need to execute this
834
                                //    before reading the sheet view data to get the actual selected cells
835 678
                                if (!$this->readDataOnly && ($xmlSheet->conditionalFormatting)) {
836 221
                                    (new ConditionalStyles($docSheet, $xmlSheet, $dxfs, $this->styleReader))->load();
837
                                }
838 678
                                if (!$this->readDataOnly && $xmlSheet->extLst) {
839 200
                                    (new ConditionalStyles($docSheet, $xmlSheet, $dxfs, $this->styleReader))->loadFromExt();
840
                                }
841 678
                                if (isset($xmlSheetMain->sheetViews, $xmlSheetMain->sheetViews->sheetView)) {
842 675
                                    $sheetViews = new SheetViews($xmlSheetMain->sheetViews->sheetView, $docSheet);
843 675
                                    $sheetViews->load();
844
                                }
845
846 678
                                $sheetViewOptions = new SheetViewOptions($docSheet, $xmlSheetNS);
847 678
                                $sheetViewOptions->load($this->readDataOnly, $this->styleReader);
848
849 678
                                (new ColumnAndRowAttributes($docSheet, $xmlSheetNS))
850 678
                                    ->load($this->getReadFilter(), $this->readDataOnly, $this->ignoreRowsWithNoCells);
851
                            }
852
853 678
                            $holdSelectedCells = $docSheet->getSelectedCells();
854 678
                            if ($xmlSheetNS && $xmlSheetNS->sheetData && $xmlSheetNS->sheetData->row) {
855 634
                                $cIndex = 1; // Cell Start from 1
856 634
                                foreach ($xmlSheetNS->sheetData->row as $row) {
857 634
                                    $rowIndex = 1;
858 634
                                    foreach ($row->c as $c) {
859 633
                                        $cAttr = self::getAttributes($c);
860 633
                                        $r = (string) $cAttr['r'];
861 633
                                        if ($r == '') {
862 2
                                            $r = Coordinate::stringFromColumnIndex($rowIndex) . $cIndex;
863
                                        }
864 633
                                        $cellDataType = (string) $cAttr['t'];
865 633
                                        $originalCellDataTypeNumeric = $cellDataType === '';
866 633
                                        $value = null;
867 633
                                        $calculatedValue = null;
868
869
                                        // Read cell?
870 633
                                        $coordinates = Coordinate::coordinateFromString($r);
871
872 633
                                        if (!$this->getReadFilter()->readCell($coordinates[0], (int) $coordinates[1], $docSheet->getTitle())) {
873
                                            // Normally, just testing for the f attribute should identify this cell as containing a formula
874
                                            // that we need to read, even though it is outside of the filter range, in case it is a shared formula.
875
                                            // But in some cases, this attribute isn't set; so we need to delve a level deeper and look at
876
                                            // whether or not the cell has a child formula element that is shared.
877 4
                                            if (isset($cAttr->f) || (isset($c->f, $c->f->attributes()['t']) && strtolower((string) $c->f->attributes()['t']) === 'shared')) {
878
                                                $this->castToFormula($c, $r, $cellDataType, $value, $calculatedValue, 'castToError', false);
879
                                            }
880 4
                                            ++$rowIndex;
881
882 4
                                            continue;
883
                                        }
884
885
                                        // Read cell!
886 633
                                        $useFormula = isset($c->f)
887 633
                                            && ((string) $c->f !== '' || (isset($c->f->attributes()['t']) && strtolower((string) $c->f->attributes()['t']) === 'shared'));
888
                                        switch ($cellDataType) {
889 22
                                            case DataType::TYPE_STRING:
890 499
                                                if ((string) $c->v != '') {
891 499
                                                    $value = $sharedStrings[(int) ($c->v)];
892
893 499
                                                    if ($value instanceof RichText) {
894 37
                                                        $value = clone $value;
895
                                                    }
896
                                                } else {
897 16
                                                    $value = '';
898
                                                }
899
900 499
                                                break;
901 18
                                            case DataType::TYPE_BOOL:
902 22
                                                if (!$useFormula) {
903 16
                                                    if (isset($c->v)) {
904 16
                                                        $value = self::castToBoolean($c);
905
                                                    } else {
906 1
                                                        $value = null;
907 1
                                                        $cellDataType = DataType::TYPE_NULL;
908
                                                    }
909
                                                } else {
910
                                                    // Formula
911 6
                                                    $this->castToFormula($c, $r, $cellDataType, $value, $calculatedValue, 'castToBoolean');
912 6
                                                    self::storeFormulaAttributes($c->f, $docSheet, $r);
913
                                                }
914
915 22
                                                break;
916 18
                                            case DataType::TYPE_STRING2:
917 223
                                                if ($useFormula) {
918 221
                                                    $this->castToFormula($c, $r, $cellDataType, $value, $calculatedValue, 'castToString');
919 221
                                                    self::storeFormulaAttributes($c->f, $docSheet, $r);
920
                                                } else {
921 3
                                                    $value = self::castToString($c);
922
                                                }
923
924 223
                                                break;
925 18
                                            case DataType::TYPE_INLINE:
926 14
                                                if ($useFormula) {
927
                                                    $this->castToFormula($c, $r, $cellDataType, $value, $calculatedValue, 'castToError');
928
                                                    self::storeFormulaAttributes($c->f, $docSheet, $r);
929
                                                } else {
930 14
                                                    $value = $this->parseRichText($c->is);
931
                                                }
932
933 14
                                                break;
934 18
                                            case DataType::TYPE_ERROR:
935 189
                                                if (!$useFormula) {
936
                                                    $value = self::castToError($c);
937
                                                } else {
938
                                                    // Formula
939 189
                                                    $this->castToFormula($c, $r, $cellDataType, $value, $calculatedValue, 'castToError');
940 189
                                                    $eattr = $c->attributes();
941 189
                                                    if (isset($eattr['vm'])) {
942 1
                                                        if ($calculatedValue === ExcelError::VALUE()) {
943 1
                                                            $calculatedValue = ExcelError::SPILL();
944
                                                        }
945
                                                    }
946
                                                }
947
948 189
                                                break;
949
                                            default:
950 529
                                                if (!$useFormula) {
951 521
                                                    $value = self::castToString($c);
952 521
                                                    if (is_numeric($value)) {
953 495
                                                        $value += 0;
954 495
                                                        $cellDataType = DataType::TYPE_NUMERIC;
955
                                                    }
956
                                                } else {
957
                                                    // Formula
958 343
                                                    $this->castToFormula($c, $r, $cellDataType, $value, $calculatedValue, 'castToString');
959 343
                                                    if (is_numeric($calculatedValue)) {
960 340
                                                        $calculatedValue += 0;
961
                                                    }
962 343
                                                    self::storeFormulaAttributes($c->f, $docSheet, $r);
963
                                                }
964
965 529
                                                break;
966
                                        }
967
968
                                        // read empty cells or the cells are not empty
969 633
                                        if ($this->readEmptyCells || ($value !== null && $value !== '')) {
970
                                            // Rich text?
971 633
                                            if ($value instanceof RichText && $this->readDataOnly) {
972 1
                                                $value = $value->getPlainText();
973
                                            }
974
975 633
                                            $cell = $docSheet->getCell($r);
976
                                            // Assign value
977 633
                                            if ($cellDataType != '') {
978
                                                // it is possible, that datatype is numeric but with an empty string, which result in an error
979 625
                                                if ($cellDataType === DataType::TYPE_NUMERIC && ($value === '' || $value === null)) {
980 1
                                                    $cellDataType = DataType::TYPE_NULL;
981
                                                }
982 625
                                                if ($cellDataType !== DataType::TYPE_NULL) {
983 625
                                                    $cell->setValueExplicit($value, $cellDataType);
984
                                                }
985
                                            } else {
986 297
                                                $cell->setValue($value);
987
                                            }
988 633
                                            if ($calculatedValue !== null) {
989 356
                                                $cell->setCalculatedValue($calculatedValue, $originalCellDataTypeNumeric);
990
                                            }
991
992
                                            // Style information?
993 633
                                            if (!$this->readDataOnly) {
994 630
                                                $cAttrS = (int) ($cAttr['s'] ?? 0);
995
                                                // no style index means 0, it seems
996 630
                                                $cAttrS = isset($styles[$cAttrS]) ? $cAttrS : 0;
997 630
                                                $cell->setXfIndex($cAttrS);
998
                                                // issue 3495
999 630
                                                if ($cellDataType === DataType::TYPE_FORMULA && $styles[$cAttrS]->quotePrefix === true) { //* @phpstan-ignore-line
1000 2
                                                    $holdSelected = $docSheet->getSelectedCells();
1001 2
                                                    $cell->getStyle()->setQuotePrefix(false);
1002 2
                                                    $docSheet->setSelectedCells($holdSelected);
1003
                                                }
1004
                                            }
1005
                                        }
1006 633
                                        ++$rowIndex;
1007
                                    }
1008 634
                                    ++$cIndex;
1009
                                }
1010
                            }
1011 678
                            $docSheet->setSelectedCells($holdSelectedCells);
1012 678
                            if (!$this->readDataOnly && $xmlSheetNS && $xmlSheetNS->ignoredErrors) {
1013 4
                                foreach ($xmlSheetNS->ignoredErrors->ignoredError as $ignoredError) {
1014 4
                                    $this->processIgnoredErrors($ignoredError, $docSheet);
1015
                                }
1016
                            }
1017
1018 678
                            if (!$this->readDataOnly && $xmlSheetNS && $xmlSheetNS->sheetProtection) {
1019 68
                                $protAttr = $xmlSheetNS->sheetProtection->attributes() ?? [];
1020 68
                                foreach ($protAttr as $key => $value) {
1021 68
                                    $method = 'set' . ucfirst($key);
1022 68
                                    $docSheet->getProtection()->$method(self::boolean((string) $value));
1023
                                }
1024
                            }
1025
1026 678
                            if ($xmlSheet) {
1027 668
                                $this->readSheetProtection($docSheet, $xmlSheet);
1028
                            }
1029
1030 678
                            if ($this->readDataOnly === false) {
1031 675
                                $this->readAutoFilter($xmlSheetNS, $docSheet);
1032 675
                                $this->readBackgroundImage($xmlSheetNS, $docSheet, dirname("$dir/$fileWorksheet") . '/_rels/' . basename($fileWorksheet) . '.rels');
1033
                            }
1034
1035 678
                            $this->readTables($xmlSheetNS, $docSheet, $dir, $fileWorksheet, $zip, $mainNS, $tableStyles, $dxfs);
1036
1037 678
                            if ($xmlSheetNS && $xmlSheetNS->mergeCells && $xmlSheetNS->mergeCells->mergeCell && !$this->readDataOnly) {
1038 67
                                foreach ($xmlSheetNS->mergeCells->mergeCell as $mergeCellx) {
1039 67
                                    $mergeCell = $mergeCellx->attributes();
1040 67
                                    $mergeRef = (string) ($mergeCell['ref'] ?? '');
1041 67
                                    if (str_contains($mergeRef, ':')) {
1042 67
                                        $docSheet->mergeCells($mergeRef, Worksheet::MERGE_CELL_CONTENT_HIDE);
1043
                                    }
1044
                                }
1045
                            }
1046
1047 678
                            if ($xmlSheet && !$this->readDataOnly) {
1048 665
                                $unparsedLoadedData = (new PageSetup($docSheet, $xmlSheet))->load($unparsedLoadedData);
1049
                            }
1050
1051 678
                            if (isset($xmlSheet->extLst->ext)) {
1052 200
                                foreach ($xmlSheet->extLst->ext as $extlst) {
1053 200
                                    $extAttrs = $extlst->attributes() ?? [];
1054 200
                                    $extUri = (string) ($extAttrs['uri'] ?? '');
1055 200
                                    if ($extUri !== '{CCE6A557-97BC-4b89-ADB6-D9C93CAAB3DF}') {
1056 194
                                        continue;
1057
                                    }
1058
                                    // Create dataValidations node if does not exists, maybe is better inside the foreach ?
1059 6
                                    if (!$xmlSheet->dataValidations) {
1060 1
                                        $xmlSheet->addChild('dataValidations');
1061
                                    }
1062
1063 6
                                    foreach ($extlst->children(Namespaces::DATA_VALIDATIONS1)->dataValidations->dataValidation as $item) {
1064 6
                                        $item = self::testSimpleXml($item);
1065 6
                                        $node = self::testSimpleXml($xmlSheet->dataValidations)->addChild('dataValidation');
1066 6
                                        foreach ($item->attributes() ?? [] as $attr) {
1067 6
                                            $node->addAttribute($attr->getName(), $attr);
1068
                                        }
1069 6
                                        $node->addAttribute('sqref', $item->children(Namespaces::DATA_VALIDATIONS2)->sqref);
1070 6
                                        if (isset($item->formula1)) {
1071 6
                                            $childNode = $node->addChild('formula1');
1072 6
                                            if ($childNode !== null) { // null should never happen
1073
                                                // see https://github.com/phpstan/phpstan/issues/8236
1074 6
                                                $childNode[0] = (string) $item->formula1->children(Namespaces::DATA_VALIDATIONS2)->f; // @phpstan-ignore-line
1075
                                            }
1076
                                        }
1077
                                    }
1078
                                }
1079
                            }
1080
1081 678
                            if ($xmlSheet && $xmlSheet->dataValidations && !$this->readDataOnly) {
1082 22
                                (new DataValidations($docSheet, $xmlSheet))->load();
1083
                            }
1084
1085
                            // unparsed sheet AlternateContent
1086 678
                            if ($xmlSheet && !$this->readDataOnly) {
1087 665
                                $mc = $xmlSheet->children(Namespaces::COMPATIBILITY);
1088 665
                                if ($mc->AlternateContent) {
1089 4
                                    foreach ($mc->AlternateContent as $alternateContent) {
1090 4
                                        $alternateContent = self::testSimpleXml($alternateContent);
1091
                                        /** @var mixed[][][][] $unparsedLoadedData */
1092 4
                                        $unparsedLoadedData['sheets'][$docSheet->getCodeName()]['AlternateContents'][] = $alternateContent->asXML();
1093
                                    }
1094
                                }
1095
                            }
1096
1097
                            // Add hyperlinks
1098 678
                            if (!$this->readDataOnly) {
1099 675
                                $hyperlinkReader = new Hyperlinks($docSheet);
1100
                                // Locate hyperlink relations
1101 675
                                $relationsFileName = dirname("$dir/$fileWorksheet") . '/_rels/' . basename($fileWorksheet) . '.rels';
1102 675
                                if ($zip->locateName($relationsFileName) !== false) {
1103 556
                                    $relsWorksheet = $this->loadZip($relationsFileName, Namespaces::RELATIONSHIPS);
1104 556
                                    $hyperlinkReader->readHyperlinks($relsWorksheet);
1105
                                }
1106
1107
                                // Loop through hyperlinks
1108 675
                                if ($xmlSheetNS && $xmlSheetNS->children($mainNS)->hyperlinks) {
1109 19
                                    $hyperlinkReader->setHyperlinks($xmlSheetNS->children($mainNS)->hyperlinks);
1110
                                }
1111
                            }
1112
1113
                            // Add comments
1114 678
                            $comments = [];
1115 678
                            $vmlComments = [];
1116 678
                            if (!$this->readDataOnly) {
1117
                                // Locate comment relations
1118 675
                                $commentRelations = dirname("$dir/$fileWorksheet") . '/_rels/' . basename($fileWorksheet) . '.rels';
1119 675
                                if ($zip->locateName($commentRelations) !== false) {
1120 556
                                    $relsWorksheet = $this->loadZip($commentRelations, Namespaces::RELATIONSHIPS);
1121 556
                                    foreach ($relsWorksheet->Relationship as $elex) {
1122 397
                                        $ele = self::getAttributes($elex);
1123 397
                                        if ($ele['Type'] == Namespaces::COMMENTS) {
1124 34
                                            $comments[(string) $ele['Id']] = (string) $ele['Target'];
1125
                                        }
1126 397
                                        if ($ele['Type'] == Namespaces::VML) {
1127 37
                                            $vmlComments[(string) $ele['Id']] = (string) $ele['Target'];
1128
                                        }
1129
                                    }
1130
                                }
1131
1132
                                // Loop through comments
1133 675
                                foreach ($comments as $relName => $relPath) {
1134
                                    // Load comments file
1135 34
                                    $relPath = File::realpath(dirname("$dir/$fileWorksheet") . '/' . $relPath);
1136
                                    // okay to ignore namespace - using xpath
1137 34
                                    $commentsFile = $this->loadZip($relPath, '');
1138
1139
                                    // Utility variables
1140 34
                                    $authors = [];
1141 34
                                    $commentsFile->registerXpathNamespace('com', $mainNS);
1142 34
                                    $authorPath = self::xpathNoFalse($commentsFile, 'com:authors/com:author');
1143 34
                                    foreach ($authorPath as $author) {
1144
                                        /** @var SimpleXMLElement $author */
1145 34
                                        $authors[] = (string) $author;
1146
                                    }
1147
1148
                                    // Loop through contents
1149 34
                                    $contentPath = self::xpathNoFalse($commentsFile, 'com:commentList/com:comment');
1150 34
                                    foreach ($contentPath as $comment) {
1151
                                        /** @var SimpleXMLElement $comment */
1152 34
                                        $commentx = $comment->attributes();
1153
                                        /** @var array{ref: scalar, authorId?: scalar}  $commentx */
1154 34
                                        $commentModel = $docSheet->getComment((string) $commentx['ref']);
1155 34
                                        if (isset($commentx['authorId'])) {
1156 34
                                            $commentModel->setAuthor($authors[(int) $commentx['authorId']]);
1157
                                        }
1158
                                        /** @var SimpleXMLElement */
1159 34
                                        $temp = $comment->children($mainNS);
1160 34
                                        $commentModel->setText($this->parseRichText($temp->text));
1161
                                    }
1162
                                }
1163
1164
                                // later we will remove from it real vmlComments
1165 675
                                $unparsedVmlDrawings = $vmlComments;
1166 675
                                $vmlDrawingContents = [];
1167
1168
                                // Loop through VML comments
1169 675
                                foreach ($vmlComments as $relName => $relPath) {
1170
                                    // Load VML comments file
1171 37
                                    $relPath = File::realpath(dirname("$dir/$fileWorksheet") . '/' . $relPath);
1172
1173
                                    try {
1174
                                        // no namespace okay - processed with Xpath
1175 37
                                        $vmlCommentsFile = $this->loadZip($relPath, '', true);
1176 37
                                        $vmlCommentsFile->registerXPathNamespace('v', Namespaces::URN_VML);
1177
                                    } catch (Throwable) {
1178
                                        //Ignore unparsable vmlDrawings. Later they will be moved from $unparsedVmlDrawings to $unparsedLoadedData
1179
                                        continue;
1180
                                    }
1181
1182
                                    // Locate VML drawings image relations
1183 37
                                    $drowingImages = [];
1184 37
                                    $VMLDrawingsRelations = dirname($relPath) . '/_rels/' . basename($relPath) . '.rels';
1185 37
                                    $vmlDrawingContents[$relName] = $this->getSecurityScannerOrThrow()->scan($this->getFromZipArchive($zip, $relPath));
1186 37
                                    if ($zip->locateName($VMLDrawingsRelations) !== false) {
1187 17
                                        $relsVMLDrawing = $this->loadZip($VMLDrawingsRelations, Namespaces::RELATIONSHIPS);
1188 17
                                        foreach ($relsVMLDrawing->Relationship as $elex) {
1189 8
                                            $ele = self::getAttributes($elex);
1190 8
                                            if ($ele['Type'] == Namespaces::IMAGE) {
1191 8
                                                $drowingImages[(string) $ele['Id']] = (string) $ele['Target'];
1192
                                            }
1193
                                        }
1194
                                    }
1195
1196 37
                                    $shapes = self::xpathNoFalse($vmlCommentsFile, '//v:shape');
1197 37
                                    foreach ($shapes as $shape) {
1198
                                        /** @var SimpleXMLElement $shape */
1199 36
                                        $shape->registerXPathNamespace('v', Namespaces::URN_VML);
1200
1201 36
                                        if (isset($shape['style'])) {
1202 36
                                            $style = (string) $shape['style'];
1203 36
                                            $fillColor = strtoupper(substr((string) $shape['fillcolor'], 1));
1204 36
                                            $column = null;
1205 36
                                            $row = null;
1206 36
                                            $textHAlign = null;
1207 36
                                            $fillImageRelId = null;
1208 36
                                            $fillImageTitle = '';
1209
1210 36
                                            $clientData = $shape->xpath('.//x:ClientData');
1211 36
                                            $textboxDirection = '';
1212 36
                                            $textboxPath = $shape->xpath('.//v:textbox');
1213 36
                                            $textbox = (string) ($textboxPath[0]['style'] ?? '');
1214 36
                                            if (preg_match('/rtl/i', $textbox) === 1) {
1215 1
                                                $textboxDirection = Comment::TEXTBOX_DIRECTION_RTL;
1216 35
                                            } elseif (preg_match('/ltr/i', $textbox) === 1) {
1217 1
                                                $textboxDirection = Comment::TEXTBOX_DIRECTION_LTR;
1218
                                            }
1219 36
                                            if (is_array($clientData) && !empty($clientData)) {
1220
                                                /** @var SimpleXMLElement */
1221 35
                                                $clientData = $clientData[0];
1222
1223 35
                                                if (isset($clientData['ObjectType']) && (string) $clientData['ObjectType'] == 'Note') {
1224 33
                                                    $temp = $clientData->xpath('.//x:Row');
1225 33
                                                    if (is_array($temp)) {
1226 33
                                                        $row = $temp[0];
1227
                                                    }
1228
1229 33
                                                    $temp = $clientData->xpath('.//x:Column');
1230 33
                                                    if (is_array($temp)) {
1231 33
                                                        $column = $temp[0];
1232
                                                    }
1233 33
                                                    $temp = $clientData->xpath('.//x:TextHAlign');
1234 33
                                                    if (!empty($temp)) {
1235 2
                                                        $textHAlign = strtolower($temp[0]);
1236
                                                    }
1237
                                                }
1238
                                            }
1239 36
                                            $rowx = (string) $row;
1240 36
                                            $colx = (string) $column;
1241 36
                                            if (is_numeric($rowx) && is_numeric($colx) && $textHAlign !== null) {
1242 2
                                                $docSheet->getComment([1 + (int) $colx, 1 + (int) $rowx], false)->setAlignment((string) $textHAlign);
1243
                                            }
1244 36
                                            if (is_numeric($rowx) && is_numeric($colx) && $textboxDirection !== '') {
1245 2
                                                $docSheet->getComment([1 + (int) $colx, 1 + (int) $rowx], false)->setTextboxDirection($textboxDirection);
1246
                                            }
1247
1248 36
                                            $fillImageRelNode = $shape->xpath('.//v:fill/@o:relid');
1249 36
                                            if (is_array($fillImageRelNode) && !empty($fillImageRelNode)) {
1250
                                                /** @var SimpleXMLElement */
1251 5
                                                $fillImageRelNode = $fillImageRelNode[0];
1252
1253 5
                                                if (isset($fillImageRelNode['relid'])) {
1254 5
                                                    $fillImageRelId = (string) $fillImageRelNode['relid'];
1255
                                                }
1256
                                            }
1257
1258 36
                                            $fillImageTitleNode = $shape->xpath('.//v:fill/@o:title');
1259 36
                                            if (is_array($fillImageTitleNode) && !empty($fillImageTitleNode)) {
1260
                                                /** @var SimpleXMLElement */
1261 3
                                                $fillImageTitleNode = $fillImageTitleNode[0];
1262
1263 3
                                                if (isset($fillImageTitleNode['title'])) {
1264 3
                                                    $fillImageTitle = (string) $fillImageTitleNode['title'];
1265
                                                }
1266
                                            }
1267
1268 36
                                            if (($column !== null) && ($row !== null)) {
1269
                                                // Set comment properties
1270 33
                                                $comment = $docSheet->getComment([(int) $column + 1, (int) $row + 1]);
1271 33
                                                $comment->getFillColor()->setRGB($fillColor);
1272 33
                                                if (isset($drowingImages[$fillImageRelId])) {
1273 5
                                                    $objDrawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
1274 5
                                                    $objDrawing->setName($fillImageTitle);
1275 5
                                                    $imagePath = str_replace(['../', '/xl/'], 'xl/', $drowingImages[$fillImageRelId]);
1276 5
                                                    $objDrawing->setPath(
1277 5
                                                        'zip://' . File::realpath($filename) . '#' . $imagePath,
1278 5
                                                        true,
1279 5
                                                        $zip
1280 5
                                                    );
1281 5
                                                    $comment->setBackgroundImage($objDrawing);
1282
                                                }
1283
1284
                                                // Parse style
1285 33
                                                $styleArray = explode(';', str_replace(' ', '', $style));
1286 33
                                                foreach ($styleArray as $stylePair) {
1287 33
                                                    $stylePair = explode(':', $stylePair);
1288
1289 33
                                                    if ($stylePair[0] == 'margin-left') {
1290 29
                                                        $comment->setMarginLeft($stylePair[1]);
1291
                                                    }
1292 33
                                                    if ($stylePair[0] == 'margin-top') {
1293 29
                                                        $comment->setMarginTop($stylePair[1]);
1294
                                                    }
1295 33
                                                    if ($stylePair[0] == 'width') {
1296 29
                                                        $comment->setWidth($stylePair[1]);
1297
                                                    }
1298 33
                                                    if ($stylePair[0] == 'height') {
1299 29
                                                        $comment->setHeight($stylePair[1]);
1300
                                                    }
1301 33
                                                    if ($stylePair[0] == 'visibility') {
1302 33
                                                        $comment->setVisible($stylePair[1] == 'visible');
1303
                                                    }
1304
                                                }
1305
1306 33
                                                unset($unparsedVmlDrawings[$relName]);
1307
                                            }
1308
                                        }
1309
                                    }
1310
                                }
1311
1312
                                // unparsed vmlDrawing
1313 675
                                if ($unparsedVmlDrawings) {
1314 6
                                    foreach ($unparsedVmlDrawings as $rId => $relPath) {
1315
                                        /** @var mixed[][][] $unparsedLoadedData */
1316 6
                                        $rId = substr($rId, 3); // rIdXXX
1317
                                        /** @var mixed[][] */
1318 6
                                        $unparsedVmlDrawing = &$unparsedLoadedData['sheets'][$docSheet->getCodeName()]['vmlDrawings'];
1319 6
                                        $unparsedVmlDrawing[$rId] = [];
1320 6
                                        $unparsedVmlDrawing[$rId]['filePath'] = self::dirAdd("$dir/$fileWorksheet", $relPath);
1321 6
                                        $unparsedVmlDrawing[$rId]['relFilePath'] = $relPath;
1322 6
                                        $unparsedVmlDrawing[$rId]['content'] = $this->getSecurityScannerOrThrow()->scan($this->getFromZipArchive($zip, $unparsedVmlDrawing[$rId]['filePath']));
1323 6
                                        unset($unparsedVmlDrawing);
1324
                                    }
1325
                                }
1326
1327
                                // Header/footer images
1328 675
                                if ($xmlSheetNS && $xmlSheetNS->legacyDrawingHF) {
1329 2
                                    $vmlHfRid = '';
1330 2
                                    $vmlHfRidAttr = $xmlSheetNS->legacyDrawingHF->attributes(Namespaces::SCHEMA_OFFICE_DOCUMENT);
1331 2
                                    if ($vmlHfRidAttr !== null && isset($vmlHfRidAttr['id'])) {
1332 2
                                        $vmlHfRid = (string) $vmlHfRidAttr['id'][0];
1333
                                    }
1334 2
                                    if ($zip->locateName(dirname("$dir/$fileWorksheet") . '/_rels/' . basename($fileWorksheet) . '.rels') !== false) {
1335 2
                                        $relsWorksheet = $this->loadZipNoNamespace(dirname("$dir/$fileWorksheet") . '/_rels/' . basename($fileWorksheet) . '.rels', Namespaces::RELATIONSHIPS);
1336 2
                                        $vmlRelationship = '';
1337
1338 2
                                        foreach ($relsWorksheet->Relationship as $ele) {
1339 2
                                            if ((string) $ele['Type'] == Namespaces::VML && (string) $ele['Id'] === $vmlHfRid) {
1340 2
                                                $vmlRelationship = self::dirAdd("$dir/$fileWorksheet", $ele['Target']);
1341
1342 2
                                                break;
1343
                                            }
1344
                                        }
1345
1346 2
                                        if ($vmlRelationship != '') {
1347
                                            // Fetch linked images
1348 2
                                            $relsVML = $this->loadZipNoNamespace(dirname($vmlRelationship) . '/_rels/' . basename($vmlRelationship) . '.rels', Namespaces::RELATIONSHIPS);
1349 2
                                            $drawings = [];
1350 2
                                            if (isset($relsVML->Relationship)) {
1351 2
                                                foreach ($relsVML->Relationship as $ele) {
1352 2
                                                    if ($ele['Type'] == Namespaces::IMAGE) {
1353 2
                                                        $drawings[(string) $ele['Id']] = self::dirAdd($vmlRelationship, $ele['Target']);
1354
                                                    }
1355
                                                }
1356
                                            }
1357
                                            // Fetch VML document
1358 2
                                            $vmlDrawing = $this->loadZipNoNamespace($vmlRelationship, '');
1359 2
                                            $vmlDrawing->registerXPathNamespace('v', Namespaces::URN_VML);
1360
1361 2
                                            $hfImages = [];
1362
1363 2
                                            $shapes = self::xpathNoFalse($vmlDrawing, '//v:shape');
1364 2
                                            foreach ($shapes as $idx => $shape) {
1365
                                                /** @var SimpleXMLElement $shape */
1366 2
                                                $shape->registerXPathNamespace('v', Namespaces::URN_VML);
1367 2
                                                $imageData = $shape->xpath('//v:imagedata');
1368
1369 2
                                                if (empty($imageData)) {
1370
                                                    continue;
1371
                                                }
1372
1373 2
                                                $imageData = $imageData[$idx];
1374
1375 2
                                                $imageData = self::getAttributes($imageData, Namespaces::URN_MSOFFICE);
1376
                                                /** @var array{width: int, height: int, margin-left?: int, margin-top: int} */
1377 2
                                                $style = self::toCSSArray((string) $shape['style']);
1378
1379 2
                                                if (array_key_exists((string) $imageData['relid'], $drawings)) {
1380 2
                                                    $shapeId = (string) $shape['id'];
1381 2
                                                    $hfImages[$shapeId] = new HeaderFooterDrawing();
1382 2
                                                    if (isset($imageData['title'])) {
1383 2
                                                        $hfImages[$shapeId]->setName((string) $imageData['title']);
1384
                                                    }
1385
1386 2
                                                    $hfImages[$shapeId]->setPath('zip://' . File::realpath($filename) . '#' . $drawings[(string) $imageData['relid']], false, $zip);
1387 2
                                                    $hfImages[$shapeId]->setResizeProportional(false);
1388 2
                                                    $hfImages[$shapeId]->setWidth($style['width']);
1389 2
                                                    $hfImages[$shapeId]->setHeight($style['height']);
1390 2
                                                    if (isset($style['margin-left'])) {
1391 2
                                                        $hfImages[$shapeId]->setOffsetX($style['margin-left']);
1392
                                                    }
1393 2
                                                    $hfImages[$shapeId]->setOffsetY($style['margin-top']);
1394 2
                                                    $hfImages[$shapeId]->setResizeProportional(true);
1395
                                                }
1396
                                            }
1397
1398 2
                                            $docSheet->getHeaderFooter()->setImages($hfImages);
1399
                                        }
1400
                                    }
1401
                                }
1402
                            }
1403
1404
                            // TODO: Autoshapes from twoCellAnchors!
1405 678
                            $drawingFilename = dirname("$dir/$fileWorksheet")
1406 678
                                . '/_rels/'
1407 678
                                . basename($fileWorksheet)
1408 678
                                . '.rels';
1409 678
                            if (str_starts_with($drawingFilename, 'xl//xl/')) {
1410
                                $drawingFilename = substr($drawingFilename, 4);
1411
                            }
1412 678
                            if (str_starts_with($drawingFilename, '/xl//xl/')) {
1413
                                $drawingFilename = substr($drawingFilename, 5);
1414
                            }
1415 678
                            if ($zip->locateName($drawingFilename) !== false) {
1416 559
                                $relsWorksheet = $this->loadZip($drawingFilename, Namespaces::RELATIONSHIPS);
1417 559
                                $drawings = [];
1418 559
                                foreach ($relsWorksheet->Relationship as $elex) {
1419 399
                                    $ele = self::getAttributes($elex);
1420 399
                                    if ((string) $ele['Type'] === "$xmlNamespaceBase/drawing") {
1421 130
                                        $eleTarget = (string) $ele['Target'];
1422 130
                                        if (str_starts_with($eleTarget, '/xl/')) {
1423 4
                                            $drawings[(string) $ele['Id']] = substr($eleTarget, 1);
1424
                                        } else {
1425 127
                                            $drawings[(string) $ele['Id']] = self::dirAdd("$dir/$fileWorksheet", $ele['Target']);
1426
                                        }
1427
                                    }
1428
                                }
1429
1430 559
                                if ($xmlSheetNS->drawing && !$this->readDataOnly) {
1431 129
                                    $unparsedDrawings = [];
1432 129
                                    $fileDrawing = null;
1433 129
                                    foreach ($xmlSheetNS->drawing as $drawing) {
1434 129
                                        $drawingRelId = self::getArrayItemString(self::getAttributes($drawing, $xmlNamespaceBase), 'id');
1435 129
                                        $fileDrawing = $drawings[$drawingRelId];
1436 129
                                        $drawingFilename = dirname($fileDrawing) . '/_rels/' . basename($fileDrawing) . '.rels';
1437 129
                                        $relsDrawing = $this->loadZip($drawingFilename, Namespaces::RELATIONSHIPS);
1438
1439 129
                                        $images = [];
1440 129
                                        $hyperlinks = [];
1441 129
                                        if ($relsDrawing && $relsDrawing->Relationship) {
1442 109
                                            foreach ($relsDrawing->Relationship as $elex) {
1443 109
                                                $ele = self::getAttributes($elex);
1444 109
                                                $eleType = (string) $ele['Type'];
1445 109
                                                if ($eleType === Namespaces::HYPERLINK) {
1446 3
                                                    $hyperlinks[(string) $ele['Id']] = (string) $ele['Target'];
1447
                                                }
1448 109
                                                if ($eleType === "$xmlNamespaceBase/image") {
1449 59
                                                    $eleTarget = (string) $ele['Target'];
1450 59
                                                    if (str_starts_with($eleTarget, '/xl/')) {
1451 1
                                                        $eleTarget = substr($eleTarget, 1);
1452 1
                                                        $images[(string) $ele['Id']] = $eleTarget;
1453
                                                    } else {
1454 58
                                                        $images[(string) $ele['Id']] = self::dirAdd($fileDrawing, $eleTarget);
1455
                                                    }
1456 74
                                                } elseif ($eleType === "$xmlNamespaceBase/chart") {
1457 70
                                                    if ($this->includeCharts) {
1458 69
                                                        $eleTarget = (string) $ele['Target'];
1459 69
                                                        if (str_starts_with($eleTarget, '/xl/')) {
1460 3
                                                            $index = substr($eleTarget, 1);
1461
                                                        } else {
1462 67
                                                            $index = self::dirAdd($fileDrawing, $eleTarget);
1463
                                                        }
1464 69
                                                        $charts[$index] = [
1465 69
                                                            'id' => (string) $ele['Id'],
1466 69
                                                            'sheet' => $docSheet->getTitle(),
1467 69
                                                        ];
1468
                                                    }
1469
                                                }
1470
                                            }
1471
                                        }
1472
1473 129
                                        $xmlDrawing = $this->loadZipNoNamespace($fileDrawing, '');
1474 129
                                        $xmlDrawingChildren = $xmlDrawing->children(Namespaces::SPREADSHEET_DRAWING);
1475
1476 129
                                        if ($xmlDrawingChildren->oneCellAnchor) {
1477 23
                                            foreach ($xmlDrawingChildren->oneCellAnchor as $oneCellAnchor) {
1478 23
                                                $oneCellAnchor = self::testSimpleXml($oneCellAnchor);
1479 23
                                                if ($oneCellAnchor->pic->blipFill) {
1480 16
                                                    $objDrawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
1481 16
                                                    $blip = $oneCellAnchor->pic->blipFill->children(Namespaces::DRAWINGML)->blip;
1482 16
                                                    if (isset($blip, $blip->alphaModFix)) {
1483 1
                                                        $temp = (string) $blip->alphaModFix->attributes()->amt;
1484 1
                                                        if (is_numeric($temp)) {
1485 1
                                                            $objDrawing->setOpacity((int) $temp);
1486
                                                        }
1487
                                                    }
1488 16
                                                    $xfrm = $oneCellAnchor->pic->spPr->children(Namespaces::DRAWINGML)->xfrm;
1489 16
                                                    $outerShdw = $oneCellAnchor->pic->spPr->children(Namespaces::DRAWINGML)->effectLst->outerShdw;
1490
1491 16
                                                    $objDrawing->setName(self::getArrayItemString(self::getAttributes($oneCellAnchor->pic->nvPicPr->cNvPr), 'name'));
1492 16
                                                    $objDrawing->setDescription(self::getArrayItemString(self::getAttributes($oneCellAnchor->pic->nvPicPr->cNvPr), 'descr'));
1493 16
                                                    $embedImageKey = self::getArrayItemString(
1494 16
                                                        self::getAttributes($blip, $xmlNamespaceBase),
1495 16
                                                        'embed'
1496 16
                                                    );
1497 16
                                                    if (isset($images[$embedImageKey])) {
1498 16
                                                        $objDrawing->setPath(
1499 16
                                                            'zip://' . File::realpath($filename) . '#'
1500 16
                                                            . $images[$embedImageKey],
1501 16
                                                            false,
1502 16
                                                            $zip
1503 16
                                                        );
1504
                                                    } else {
1505
                                                        $linkImageKey = self::getArrayItemString(
1506
                                                            $blip->attributes('http://schemas.openxmlformats.org/officeDocument/2006/relationships'),
1507
                                                            'link'
1508
                                                        );
1509
                                                        if (isset($images[$linkImageKey])) {
1510
                                                            $url = str_replace('xl/drawings/', '', $images[$linkImageKey]);
1511
                                                            $objDrawing->setPath($url, false);
1512
                                                        }
1513
                                                        if ($objDrawing->getPath() === '') {
1514
                                                            continue;
1515
                                                        }
1516
                                                    }
1517 16
                                                    $objDrawing->setCoordinates(Coordinate::stringFromColumnIndex(((int) $oneCellAnchor->from->col) + 1) . ($oneCellAnchor->from->row + 1));
1518
1519 16
                                                    $objDrawing->setOffsetX((int) Drawing::EMUToPixels($oneCellAnchor->from->colOff));
1520 16
                                                    $objDrawing->setOffsetY(Drawing::EMUToPixels($oneCellAnchor->from->rowOff));
1521 16
                                                    $objDrawing->setResizeProportional(false);
1522 16
                                                    $objDrawing->setWidth(Drawing::EMUToPixels(self::getArrayItemIntOrSxml(self::getAttributes($oneCellAnchor->ext), 'cx')));
1523 16
                                                    $objDrawing->setHeight(Drawing::EMUToPixels(self::getArrayItemIntOrSxml(self::getAttributes($oneCellAnchor->ext), 'cy')));
1524 16
                                                    if ($xfrm) {
1525 16
                                                        $objDrawing->setRotation((int) Drawing::angleToDegrees(self::getArrayItemIntOrSxml(self::getAttributes($xfrm), 'rot')));
1526 16
                                                        $objDrawing->setFlipVertical((bool) self::getArrayItem(self::getAttributes($xfrm), 'flipV'));
1527 16
                                                        $objDrawing->setFlipHorizontal((bool) self::getArrayItem(self::getAttributes($xfrm), 'flipH'));
1528
                                                    }
1529 16
                                                    if ($outerShdw) {
1530 3
                                                        $shadow = $objDrawing->getShadow();
1531 3
                                                        $shadow->setVisible(true);
1532 3
                                                        $shadow->setBlurRadius(Drawing::EMUToPixels(self::getArrayItemIntOrSxml(self::getAttributes($outerShdw), 'blurRad')));
1533 3
                                                        $shadow->setDistance(Drawing::EMUToPixels(self::getArrayItemIntOrSxml(self::getAttributes($outerShdw), 'dist')));
1534 3
                                                        $shadow->setDirection(Drawing::angleToDegrees(self::getArrayItemIntOrSxml(self::getAttributes($outerShdw), 'dir')));
1535 3
                                                        $shadow->setAlignment(self::getArrayItemString(self::getAttributes($outerShdw), 'algn'));
1536 3
                                                        $clr = $outerShdw->srgbClr ?? $outerShdw->prstClr;
1537 3
                                                        $shadow->getColor()->setRGB(self::getArrayItemString(self::getAttributes($clr), 'val'));
1538 3
                                                        if ($clr->alpha) {
1539 3
                                                            $alpha = StringHelper::convertToString(self::getArrayItem(self::getAttributes($clr->alpha), 'val'));
1540 3
                                                            if (is_numeric($alpha)) {
1541 3
                                                                $alpha = (int) ($alpha / 1000);
1542 3
                                                                $shadow->setAlpha($alpha);
1543
                                                            }
1544
                                                        }
1545
                                                    }
1546
1547 16
                                                    $this->readHyperLinkDrawing($objDrawing, $oneCellAnchor, $hyperlinks);
1548
1549 16
                                                    $objDrawing->setWorksheet($docSheet);
1550 7
                                                } elseif ($this->includeCharts && $oneCellAnchor->graphicFrame) {
1551
                                                    // Exported XLSX from Google Sheets positions charts with a oneCellAnchor
1552 4
                                                    $coordinates = Coordinate::stringFromColumnIndex(((int) $oneCellAnchor->from->col) + 1) . ($oneCellAnchor->from->row + 1);
1553 4
                                                    $offsetX = Drawing::EMUToPixels($oneCellAnchor->from->colOff);
1554 4
                                                    $offsetY = Drawing::EMUToPixels($oneCellAnchor->from->rowOff);
1555 4
                                                    $width = Drawing::EMUToPixels(self::getArrayItemIntOrSxml(self::getAttributes($oneCellAnchor->ext), 'cx'));
1556 4
                                                    $height = Drawing::EMUToPixels(self::getArrayItemIntOrSxml(self::getAttributes($oneCellAnchor->ext), 'cy'));
1557
1558 4
                                                    $graphic = $oneCellAnchor->graphicFrame->children(Namespaces::DRAWINGML)->graphic;
1559 4
                                                    $chartRef = $graphic->graphicData->children(Namespaces::CHART)->chart;
1560 4
                                                    $thisChart = (string) self::getAttributes($chartRef, $xmlNamespaceBase);
1561
1562 4
                                                    $chartDetails[$docSheet->getTitle() . '!' . $thisChart] = [
1563 4
                                                        'fromCoordinate' => $coordinates,
1564 4
                                                        'fromOffsetX' => $offsetX,
1565 4
                                                        'fromOffsetY' => $offsetY,
1566 4
                                                        'width' => $width,
1567 4
                                                        'height' => $height,
1568 4
                                                        'worksheetTitle' => $docSheet->getTitle(),
1569 4
                                                        'oneCellAnchor' => true,
1570 4
                                                    ];
1571
                                                }
1572
                                            }
1573
                                        }
1574 129
                                        if ($xmlDrawingChildren->twoCellAnchor) {
1575 92
                                            foreach ($xmlDrawingChildren->twoCellAnchor as $twoCellAnchor) {
1576 92
                                                $twoCellAnchor = self::testSimpleXml($twoCellAnchor);
1577 92
                                                if ($twoCellAnchor->pic->blipFill) {
1578 45
                                                    $objDrawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
1579 45
                                                    $blip = $twoCellAnchor->pic->blipFill->children(Namespaces::DRAWINGML)->blip;
1580 45
                                                    if (isset($blip, $blip->alphaModFix)) {
1581 3
                                                        $temp = (string) $blip->alphaModFix->attributes()->amt;
1582 3
                                                        if (is_numeric($temp)) {
1583 3
                                                            $objDrawing->setOpacity((int) $temp);
1584
                                                        }
1585
                                                    }
1586 45
                                                    if (isset($twoCellAnchor->pic->blipFill->children(Namespaces::DRAWINGML)->srcRect)) {
1587 7
                                                        $objDrawing->setSrcRect($twoCellAnchor->pic->blipFill->children(Namespaces::DRAWINGML)->srcRect->attributes());
1588
                                                    }
1589 45
                                                    $xfrm = $twoCellAnchor->pic->spPr->children(Namespaces::DRAWINGML)->xfrm;
1590 45
                                                    $outerShdw = $twoCellAnchor->pic->spPr->children(Namespaces::DRAWINGML)->effectLst->outerShdw;
1591 45
                                                    $editAs = $twoCellAnchor->attributes();
1592 45
                                                    if (isset($editAs, $editAs['editAs'])) {
1593 40
                                                        $objDrawing->setEditAs($editAs['editAs']);
1594
                                                    }
1595 45
                                                    $objDrawing->setName((string) self::getArrayItemString(self::getAttributes($twoCellAnchor->pic->nvPicPr->cNvPr), 'name'));
1596 45
                                                    $objDrawing->setDescription(self::getArrayItemString(self::getAttributes($twoCellAnchor->pic->nvPicPr->cNvPr), 'descr'));
1597 45
                                                    $embedImageKey = self::getArrayItemString(
1598 45
                                                        self::getAttributes($blip, $xmlNamespaceBase),
1599 45
                                                        'embed'
1600 45
                                                    );
1601 45
                                                    if (isset($images[$embedImageKey])) {
1602 42
                                                        $objDrawing->setPath(
1603 42
                                                            'zip://' . File::realpath($filename) . '#'
1604 42
                                                            . $images[$embedImageKey],
1605 42
                                                            false,
1606 42
                                                            $zip
1607 42
                                                        );
1608
                                                    } else {
1609 3
                                                        $linkImageKey = self::getArrayItemString(
1610 3
                                                            $blip->attributes('http://schemas.openxmlformats.org/officeDocument/2006/relationships'),
1611 3
                                                            'link'
1612 3
                                                        );
1613 3
                                                        if (isset($images[$linkImageKey])) {
1614 3
                                                            $url = str_replace('xl/drawings/', '', $images[$linkImageKey]);
1615 3
                                                            $objDrawing->setPath($url, false);
1616
                                                        }
1617 2
                                                        if ($objDrawing->getPath() === '') {
1618 1
                                                            continue;
1619
                                                        }
1620
                                                    }
1621 43
                                                    $objDrawing->setCoordinates(Coordinate::stringFromColumnIndex(((int) $twoCellAnchor->from->col) + 1) . ($twoCellAnchor->from->row + 1));
1622
1623 43
                                                    $objDrawing->setOffsetX(Drawing::EMUToPixels($twoCellAnchor->from->colOff));
1624 43
                                                    $objDrawing->setOffsetY(Drawing::EMUToPixels($twoCellAnchor->from->rowOff));
1625
1626 43
                                                    $objDrawing->setCoordinates2(Coordinate::stringFromColumnIndex(((int) $twoCellAnchor->to->col) + 1) . ($twoCellAnchor->to->row + 1));
1627
1628 43
                                                    $objDrawing->setOffsetX2(Drawing::EMUToPixels($twoCellAnchor->to->colOff));
1629 43
                                                    $objDrawing->setOffsetY2(Drawing::EMUToPixels($twoCellAnchor->to->rowOff));
1630
1631 43
                                                    $objDrawing->setResizeProportional(false);
1632
1633 43
                                                    if ($xfrm) {
1634 43
                                                        $objDrawing->setWidth(Drawing::EMUToPixels(self::getArrayItemIntOrSxml(self::getAttributes($xfrm->ext), 'cx')));
1635 43
                                                        $objDrawing->setHeight(Drawing::EMUToPixels(self::getArrayItemIntOrSxml(self::getAttributes($xfrm->ext), 'cy')));
1636 43
                                                        $objDrawing->setRotation(Drawing::angleToDegrees(self::getArrayItemIntOrSxml(self::getAttributes($xfrm), 'rot')));
1637 43
                                                        $objDrawing->setFlipVertical((bool) self::getArrayItem(self::getAttributes($xfrm), 'flipV'));
1638 43
                                                        $objDrawing->setFlipHorizontal((bool) self::getArrayItem(self::getAttributes($xfrm), 'flipH'));
1639
                                                    }
1640 43
                                                    if ($outerShdw) {
1641 1
                                                        $shadow = $objDrawing->getShadow();
1642 1
                                                        $shadow->setVisible(true);
1643 1
                                                        $shadow->setBlurRadius(Drawing::EMUToPixels(self::getArrayItemIntOrSxml(self::getAttributes($outerShdw), 'blurRad')));
1644 1
                                                        $shadow->setDistance(Drawing::EMUToPixels(self::getArrayItemIntOrSxml(self::getAttributes($outerShdw), 'dist')));
1645 1
                                                        $shadow->setDirection(Drawing::angleToDegrees(self::getArrayItemIntOrSxml(self::getAttributes($outerShdw), 'dir')));
1646 1
                                                        $shadow->setAlignment(self::getArrayItemString(self::getAttributes($outerShdw), 'algn'));
1647 1
                                                        $clr = $outerShdw->srgbClr ?? $outerShdw->prstClr;
1648 1
                                                        $shadow->getColor()->setRGB(self::getArrayItemString(self::getAttributes($clr), 'val'));
1649 1
                                                        if ($clr->alpha) {
1650 1
                                                            $alpha = StringHelper::convertToString(self::getArrayItem(self::getAttributes($clr->alpha), 'val'));
1651 1
                                                            if (is_numeric($alpha)) {
1652 1
                                                                $alpha = (int) ($alpha / 1000);
1653 1
                                                                $shadow->setAlpha($alpha);
1654
                                                            }
1655
                                                        }
1656
                                                    }
1657
1658 43
                                                    $this->readHyperLinkDrawing($objDrawing, $twoCellAnchor, $hyperlinks);
1659
1660 43
                                                    $objDrawing->setWorksheet($docSheet);
1661 71
                                                } elseif (($this->includeCharts) && ($twoCellAnchor->graphicFrame)) {
1662 65
                                                    $fromCoordinate = Coordinate::stringFromColumnIndex(((int) $twoCellAnchor->from->col) + 1) . ($twoCellAnchor->from->row + 1);
1663 65
                                                    $fromOffsetX = Drawing::EMUToPixels($twoCellAnchor->from->colOff);
1664 65
                                                    $fromOffsetY = Drawing::EMUToPixels($twoCellAnchor->from->rowOff);
1665 65
                                                    $toCoordinate = Coordinate::stringFromColumnIndex(((int) $twoCellAnchor->to->col) + 1) . ($twoCellAnchor->to->row + 1);
1666 65
                                                    $toOffsetX = Drawing::EMUToPixels($twoCellAnchor->to->colOff);
1667 65
                                                    $toOffsetY = Drawing::EMUToPixels($twoCellAnchor->to->rowOff);
1668 65
                                                    $graphic = $twoCellAnchor->graphicFrame->children(Namespaces::DRAWINGML)->graphic;
1669 65
                                                    $chartRef = $graphic->graphicData->children(Namespaces::CHART)->chart;
1670 65
                                                    $thisChart = (string) self::getAttributes($chartRef, $xmlNamespaceBase);
1671
1672 65
                                                    $chartDetails[$docSheet->getTitle() . '!' . $thisChart] = [
1673 65
                                                        'fromCoordinate' => $fromCoordinate,
1674 65
                                                        'fromOffsetX' => $fromOffsetX,
1675 65
                                                        'fromOffsetY' => $fromOffsetY,
1676 65
                                                        'toCoordinate' => $toCoordinate,
1677 65
                                                        'toOffsetX' => $toOffsetX,
1678 65
                                                        'toOffsetY' => $toOffsetY,
1679 65
                                                        'worksheetTitle' => $docSheet->getTitle(),
1680 65
                                                    ];
1681
                                                }
1682
                                            }
1683
                                        }
1684 128
                                        if ($xmlDrawingChildren->absoluteAnchor) {
1685 1
                                            foreach ($xmlDrawingChildren->absoluteAnchor as $absoluteAnchor) {
1686 1
                                                if (($this->includeCharts) && ($absoluteAnchor->graphicFrame)) {
1687 1
                                                    $graphic = $absoluteAnchor->graphicFrame->children(Namespaces::DRAWINGML)->graphic;
1688 1
                                                    $chartRef = $graphic->graphicData->children(Namespaces::CHART)->chart;
1689 1
                                                    $thisChart = (string) self::getAttributes($chartRef, $xmlNamespaceBase);
1690 1
                                                    $width = Drawing::EMUToPixels((int) self::getArrayItemString(self::getAttributes($absoluteAnchor->ext), 'cx')[0]);
1691 1
                                                    $height = Drawing::EMUToPixels((int) self::getArrayItemString(self::getAttributes($absoluteAnchor->ext), 'cy')[0]);
1692
1693 1
                                                    $chartDetails[$docSheet->getTitle() . '!' . $thisChart] = [
1694 1
                                                        'fromCoordinate' => 'A1',
1695 1
                                                        'fromOffsetX' => 0,
1696 1
                                                        'fromOffsetY' => 0,
1697 1
                                                        'width' => $width,
1698 1
                                                        'height' => $height,
1699 1
                                                        'worksheetTitle' => $docSheet->getTitle(),
1700 1
                                                    ];
1701
                                                }
1702
                                            }
1703
                                        }
1704 128
                                        if (empty($relsDrawing) && $xmlDrawing->count() == 0) {
1705
                                            // Save Drawing without rels and children as unparsed
1706 25
                                            $unparsedDrawings[$drawingRelId] = $xmlDrawing->asXML();
1707
                                        }
1708
                                    }
1709
1710
                                    // store original rId of drawing files
1711
                                    /** @var mixed[][][][] $unparsedLoadedData */
1712 128
                                    $unparsedLoadedData['sheets'][$docSheet->getCodeName()]['drawingOriginalIds'] = [];
1713 128
                                    foreach ($relsWorksheet->Relationship as $elex) {
1714 128
                                        $ele = self::getAttributes($elex);
1715 128
                                        if ((string) $ele['Type'] === "$xmlNamespaceBase/drawing") {
1716 128
                                            $drawingRelId = (string) $ele['Id'];
1717 128
                                            $unparsedLoadedData['sheets'][$docSheet->getCodeName()]['drawingOriginalIds'][(string) $ele['Target']] = $drawingRelId;
1718 128
                                            if (isset($unparsedDrawings[$drawingRelId])) {
1719 25
                                                $unparsedLoadedData['sheets'][$docSheet->getCodeName()]['Drawings'][$drawingRelId] = $unparsedDrawings[$drawingRelId];
1720
                                            }
1721
                                        }
1722
                                    }
1723 128
                                    if ($xmlSheet->legacyDrawing && !$this->readDataOnly) {
1724 13
                                        foreach ($xmlSheet->legacyDrawing as $drawing) {
1725 13
                                            $drawingRelId = self::getArrayItemString(self::getAttributes($drawing, $xmlNamespaceBase), 'id');
1726 13
                                            if (isset($vmlDrawingContents[$drawingRelId])) {
1727 13
                                                if (self::onlyNoteVml($vmlDrawingContents[$drawingRelId]) === false) {
1728 5
                                                    $unparsedLoadedData['sheets'][$docSheet->getCodeName()]['legacyDrawing'] = $vmlDrawingContents[$drawingRelId];
1729
                                                }
1730
                                            }
1731
                                        }
1732
                                    }
1733
1734
                                    // unparsed drawing AlternateContent
1735 128
                                    $xmlAltDrawing = $this->loadZip((string) $fileDrawing, Namespaces::COMPATIBILITY);
1736
1737 128
                                    if ($xmlAltDrawing->AlternateContent) {
1738 4
                                        foreach ($xmlAltDrawing->AlternateContent as $alternateContent) {
1739 4
                                            $alternateContent = self::testSimpleXml($alternateContent);
1740
                                            /** @var mixed[][][][][] $unparsedLoadedData */
1741 4
                                            $unparsedLoadedData['sheets'][$docSheet->getCodeName()]['drawingAlternateContents'][] = $alternateContent->asXML();
1742
                                        }
1743
                                    }
1744
                                }
1745
                            }
1746
1747
                            /** @var mixed[][][][] $unparsedLoadedData */
1748 677
                            $this->readFormControlProperties($excel, $dir, $fileWorksheet, $docSheet, $unparsedLoadedData);
1749 677
                            $this->readPrinterSettings($excel, $dir, $fileWorksheet, $docSheet, $unparsedLoadedData);
1750
1751
                            // Loop through definedNames
1752 677
                            if ($xmlWorkbook->definedNames) {
1753 368
                                foreach ($xmlWorkbook->definedNames->definedName as $definedName) {
1754
                                    // Extract range
1755 107
                                    $extractedRange = (string) $definedName;
1756 107
                                    if (($spos = strpos($extractedRange, '!')) !== false) {
1757 88
                                        $extractedRange = substr($extractedRange, 0, $spos) . str_replace('$', '', substr($extractedRange, $spos));
1758
                                    } else {
1759 34
                                        $extractedRange = str_replace('$', '', $extractedRange);
1760
                                    }
1761
1762
                                    // Valid range?
1763 107
                                    if ($extractedRange == '') {
1764
                                        continue;
1765
                                    }
1766
1767
                                    // Some definedNames are only applicable if we are on the same sheet...
1768 107
                                    if ((string) $definedName['localSheetId'] != '' && (string) $definedName['localSheetId'] == $oldSheetId) {
1769
                                        // Switch on type
1770 50
                                        switch ((string) $definedName['name']) {
1771 50
                                            case '_xlnm._FilterDatabase':
1772 20
                                                if ((string) $definedName['hidden'] !== '1') {
1773
                                                    $extractedRange = explode(',', $extractedRange);
1774
                                                    foreach ($extractedRange as $range) {
1775
                                                        $autoFilterRange = $range;
1776
                                                        if (str_contains($autoFilterRange, ':')) {
1777
                                                            $docSheet->getAutoFilter()->setRange($autoFilterRange);
1778
                                                        }
1779
                                                    }
1780
                                                }
1781
1782 20
                                                break;
1783 30
                                            case '_xlnm.Print_Titles':
1784
                                                // Split $extractedRange
1785 3
                                                $extractedRange = explode(',', $extractedRange);
1786
1787
                                                // Set print titles
1788 3
                                                foreach ($extractedRange as $range) {
1789 3
                                                    $matches = [];
1790 3
                                                    $range = str_replace('$', '', $range);
1791
1792
                                                    // check for repeating columns, e g. 'A:A' or 'A:D'
1793 3
                                                    if (preg_match('/!?([A-Z]+)\:([A-Z]+)$/', $range, $matches)) {
1794
                                                        $docSheet->getPageSetup()->setColumnsToRepeatAtLeft([$matches[1], $matches[2]]);
1795 3
                                                    } elseif (preg_match('/!?(\d+)\:(\d+)$/', $range, $matches)) {
1796
                                                        // check for repeating rows, e.g. '1:1' or '1:5'
1797 3
                                                        $docSheet->getPageSetup()->setRowsToRepeatAtTop([(int) $matches[1], (int) $matches[2]]);
1798
                                                    }
1799
                                                }
1800
1801 3
                                                break;
1802 29
                                            case '_xlnm.Print_Area':
1803 11
                                                $rangeSets = preg_split("/('?(?:.*?)'?(?:![A-Z0-9]+:[A-Z0-9]+)),?/", $extractedRange, -1, PREG_SPLIT_NO_EMPTY | PREG_SPLIT_DELIM_CAPTURE) ?: [];
1804 11
                                                $newRangeSets = [];
1805 11
                                                foreach ($rangeSets as $rangeSet) {
1806 11
                                                    [, $rangeSet] = Worksheet::extractSheetTitle($rangeSet, true);
1807 11
                                                    if (empty($rangeSet)) {
1808
                                                        continue;
1809
                                                    }
1810 11
                                                    if (!str_contains($rangeSet, ':')) {
1811
                                                        $rangeSet = $rangeSet . ':' . $rangeSet;
1812
                                                    }
1813 11
                                                    $newRangeSets[] = str_replace('$', '', $rangeSet);
1814
                                                }
1815 11
                                                if (count($newRangeSets) > 0) {
1816 11
                                                    $docSheet->getPageSetup()->setPrintArea(implode(',', $newRangeSets));
1817
                                                }
1818
1819 11
                                                break;
1820
                                            default:
1821 19
                                                break;
1822
                                        }
1823
                                    }
1824
                                }
1825
                            }
1826
1827
                            // Next sheet id
1828 677
                            ++$sheetId;
1829
                        }
1830
1831
                        // Loop through definedNames
1832 678
                        if ($xmlWorkbook->definedNames) {
1833 368
                            foreach ($xmlWorkbook->definedNames->definedName as $definedName) {
1834
                                // Extract range
1835 107
                                $extractedRange = (string) $definedName;
1836
1837
                                // Valid range?
1838 107
                                if ($extractedRange == '') {
1839
                                    continue;
1840
                                }
1841
1842
                                // Some definedNames are only applicable if we are on the same sheet...
1843 107
                                if ((string) $definedName['localSheetId'] != '') {
1844
                                    // Local defined name
1845
                                    // Switch on type
1846 50
                                    switch ((string) $definedName['name']) {
1847 50
                                        case '_xlnm._FilterDatabase':
1848 30
                                        case '_xlnm.Print_Titles':
1849 29
                                        case '_xlnm.Print_Area':
1850 33
                                            break;
1851
                                        default:
1852 19
                                            if ($mapSheetId[(int) $definedName['localSheetId']] !== null) {
1853 19
                                                $range = Worksheet::extractSheetTitle($extractedRange, true);
1854 19
                                                $scope = $excel->getSheet($mapSheetId[(int) $definedName['localSheetId']]);
1855 19
                                                if (str_contains((string) $definedName, '!')) {
1856 19
                                                    $range[0] = str_replace("''", "'", $range[0]);
1857 19
                                                    $range[0] = str_replace("'", '', $range[0]);
1858 19
                                                    if ($worksheet = $excel->getSheetByName($range[0])) {
1859 19
                                                        $excel->addDefinedName(DefinedName::createInstance((string) $definedName['name'], $worksheet, $extractedRange, true, $scope));
1860
                                                    } else {
1861 14
                                                        $excel->addDefinedName(DefinedName::createInstance((string) $definedName['name'], $scope, $extractedRange, true, $scope));
1862
                                                    }
1863
                                                } else {
1864
                                                    $excel->addDefinedName(DefinedName::createInstance((string) $definedName['name'], $scope, $extractedRange, true));
1865
                                                }
1866
                                            }
1867
1868 19
                                            break;
1869
                                    }
1870 77
                                } elseif (!isset($definedName['localSheetId'])) {
1871
                                    // "Global" definedNames
1872 77
                                    $locatedSheet = null;
1873 77
                                    if (str_contains((string) $definedName, '!')) {
1874
                                        // Modify range, and extract the first worksheet reference
1875
                                        // Need to split on a comma or a space if not in quotes, and extract the first part.
1876 57
                                        $definedNameValueParts = preg_split("/[ ,](?=([^']*'[^']*')*[^']*$)/miuU", $extractedRange);
1877 57
                                        if (is_array($definedNameValueParts)) {
1878
                                            // Extract sheet name
1879 57
                                            [$extractedSheetName] = Worksheet::extractSheetTitle((string) $definedNameValueParts[0], true, true);
1880
1881
                                            // Locate sheet
1882 57
                                            $locatedSheet = $excel->getSheetByName("$extractedSheetName");
1883
                                        }
1884
                                    }
1885
1886 77
                                    if ($locatedSheet === null && !DefinedName::testIfFormula($extractedRange)) {
1887 2
                                        $extractedRange = '#REF!';
1888
                                    }
1889 77
                                    $excel->addDefinedName(DefinedName::createInstance((string) $definedName['name'], $locatedSheet, $extractedRange, false));
1890
                                }
1891
                            }
1892
                        }
1893
                    }
1894
1895 678
                    (new WorkbookView($excel))->viewSettings($xmlWorkbook, $mainNS, $mapSheetId, $this->readDataOnly);
1896
1897 677
                    break;
1898
            }
1899
        }
1900
1901 677
        if (!$this->readDataOnly) {
1902 674
            $contentTypes = $this->loadZip('[Content_Types].xml');
1903
1904
            // Default content types
1905 674
            foreach ($contentTypes->Default as $contentType) {
1906 672
                switch ($contentType['ContentType']) {
1907 672
                    case 'application/vnd.openxmlformats-officedocument.spreadsheetml.printerSettings':
1908 291
                        $unparsedLoadedData['default_content_types'][(string) $contentType['Extension']] = (string) $contentType['ContentType'];
1909
1910 291
                        break;
1911
                }
1912
            }
1913
1914
            // Override content types
1915 674
            foreach ($contentTypes->Override as $contentType) {
1916 673
                switch ($contentType['ContentType']) {
1917 673
                    case 'application/vnd.openxmlformats-officedocument.drawingml.chart+xml':
1918 71
                        if ($this->includeCharts) {
1919 69
                            $chartEntryRef = ltrim((string) $contentType['PartName'], '/');
1920 69
                            $chartElements = $this->loadZip($chartEntryRef);
1921 69
                            $chartReader = new Chart($chartNS, $drawingNS);
1922 69
                            $objChart = $chartReader->readChart($chartElements, basename($chartEntryRef, '.xml'));
1923 69
                            if (isset($charts[$chartEntryRef])) {
1924 69
                                $chartPositionRef = $charts[$chartEntryRef]['sheet'] . '!' . $charts[$chartEntryRef]['id'];
1925 69
                                if (isset($chartDetails[$chartPositionRef]) && $excel->getSheetByName($charts[$chartEntryRef]['sheet']) !== null) {
1926 69
                                    $excel->getSheetByName($charts[$chartEntryRef]['sheet'])->addChart($objChart);
1927 69
                                    $objChart->setWorksheet($excel->getSheetByName($charts[$chartEntryRef]['sheet']));
1928
                                    // For oneCellAnchor or absoluteAnchor positioned charts,
1929
                                    //     toCoordinate is not in the data. Does it need to be calculated?
1930 69
                                    if (array_key_exists('toCoordinate', $chartDetails[$chartPositionRef])) {
1931
                                        // twoCellAnchor
1932 65
                                        $objChart->setTopLeftPosition($chartDetails[$chartPositionRef]['fromCoordinate'], $chartDetails[$chartPositionRef]['fromOffsetX'], $chartDetails[$chartPositionRef]['fromOffsetY']);
1933 65
                                        $objChart->setBottomRightPosition($chartDetails[$chartPositionRef]['toCoordinate'], $chartDetails[$chartPositionRef]['toOffsetX'], $chartDetails[$chartPositionRef]['toOffsetY']);
1934
                                    } else {
1935
                                        // oneCellAnchor or absoluteAnchor (e.g. Chart sheet)
1936 5
                                        $objChart->setTopLeftPosition($chartDetails[$chartPositionRef]['fromCoordinate'], $chartDetails[$chartPositionRef]['fromOffsetX'], $chartDetails[$chartPositionRef]['fromOffsetY']);
1937 5
                                        $objChart->setBottomRightPosition('', $chartDetails[$chartPositionRef]['width'], $chartDetails[$chartPositionRef]['height']);
1938 5
                                        if (array_key_exists('oneCellAnchor', $chartDetails[$chartPositionRef])) {
1939 4
                                            $objChart->setOneCellAnchor($chartDetails[$chartPositionRef]['oneCellAnchor']);
1940
                                        }
1941
                                    }
1942
                                }
1943
                            }
1944
                        }
1945
1946 71
                        break;
1947
1948
                        // unparsed
1949 673
                    case 'application/vnd.ms-excel.controlproperties+xml':
1950 4
                        $unparsedLoadedData['override_content_types'][(string) $contentType['PartName']] = (string) $contentType['ContentType'];
1951
1952 4
                        break;
1953
                }
1954
            }
1955
        }
1956
1957
        /** @var array<array<array<array<string>|string>>> $unparsedLoadedData */
1958 677
        $excel->setUnparsedLoadedData($unparsedLoadedData);
1959
1960 677
        $zip->close();
1961
1962 677
        return $excel;
1963
    }
1964
1965 77
    private function parseRichText(?SimpleXMLElement $is): RichText
1966
    {
1967 77
        $value = new RichText();
1968
1969 77
        if (isset($is->t)) {
1970 21
            $value->createText(StringHelper::controlCharacterOOXML2PHP((string) $is->t));
1971 57
        } elseif ($is !== null) {
1972 57
            if (is_object($is->r)) {
1973 57
                foreach ($is->r as $run) {
1974 54
                    if (!isset($run->rPr)) {
1975 37
                        $value->createText(StringHelper::controlCharacterOOXML2PHP((string) $run->t));
1976
                    } else {
1977 50
                        $objText = $value->createTextRun(StringHelper::controlCharacterOOXML2PHP((string) $run->t));
1978 50
                        $objFont = $objText->getFont() ?? new StyleFont();
1979
1980 50
                        if (isset($run->rPr->rFont)) {
1981 50
                            $attr = $run->rPr->rFont->attributes();
1982 50
                            if (isset($attr['val'])) {
1983 50
                                $objFont->setName((string) $attr['val']);
1984
                            }
1985
                        }
1986 50
                        if (isset($run->rPr->sz)) {
1987 50
                            $attr = $run->rPr->sz->attributes();
1988 50
                            if (isset($attr['val'])) {
1989 50
                                $objFont->setSize((float) $attr['val']);
1990
                            }
1991
                        }
1992 50
                        if (isset($run->rPr->color)) {
1993 48
                            $objFont->setColor(new Color($this->styleReader->readColor($run->rPr->color)));
1994
                        }
1995 50
                        if (isset($run->rPr->b)) {
1996 43
                            $attr = $run->rPr->b->attributes();
1997
                            if (
1998 43
                                (isset($attr['val']) && self::boolean((string) $attr['val']))
1999 43
                                || (!isset($attr['val']))
2000
                            ) {
2001 41
                                $objFont->setBold(true);
2002
                            }
2003
                        }
2004 50
                        if (isset($run->rPr->i)) {
2005 16
                            $attr = $run->rPr->i->attributes();
2006
                            if (
2007 16
                                (isset($attr['val']) && self::boolean((string) $attr['val']))
2008 16
                                || (!isset($attr['val']))
2009
                            ) {
2010 9
                                $objFont->setItalic(true);
2011
                            }
2012
                        }
2013 50
                        if (isset($run->rPr->vertAlign)) {
2014
                            $attr = $run->rPr->vertAlign->attributes();
2015
                            if (isset($attr['val'])) {
2016
                                $vertAlign = strtolower((string) $attr['val']);
2017
                                if ($vertAlign == 'superscript') {
2018
                                    $objFont->setSuperscript(true);
2019
                                }
2020
                                if ($vertAlign == 'subscript') {
2021
                                    $objFont->setSubscript(true);
2022
                                }
2023
                            }
2024
                        }
2025 50
                        if (isset($run->rPr->u)) {
2026 12
                            $attr = $run->rPr->u->attributes();
2027 12
                            if (!isset($attr['val'])) {
2028 1
                                $objFont->setUnderline(StyleFont::UNDERLINE_SINGLE);
2029
                            } else {
2030 11
                                $objFont->setUnderline((string) $attr['val']);
2031
                            }
2032
                        }
2033 50
                        if (isset($run->rPr->strike)) {
2034 11
                            $attr = $run->rPr->strike->attributes();
2035
                            if (
2036 11
                                (isset($attr['val']) && self::boolean((string) $attr['val']))
2037 11
                                || (!isset($attr['val']))
2038
                            ) {
2039
                                $objFont->setStrikethrough(true);
2040
                            }
2041
                        }
2042
                    }
2043
                }
2044
            }
2045
        }
2046
2047 77
        return $value;
2048
    }
2049
2050 2
    private function readRibbon(Spreadsheet $excel, string $customUITarget, ZipArchive $zip): void
2051
    {
2052 2
        $baseDir = dirname($customUITarget);
2053 2
        $nameCustomUI = basename($customUITarget);
2054
        // get the xml file (ribbon)
2055 2
        $localRibbon = $this->getFromZipArchive($zip, $customUITarget);
2056 2
        $customUIImagesNames = [];
2057 2
        $customUIImagesBinaries = [];
2058
        // something like customUI/_rels/customUI.xml.rels
2059 2
        $pathRels = $baseDir . '/_rels/' . $nameCustomUI . '.rels';
2060 2
        $dataRels = $this->getFromZipArchive($zip, $pathRels);
2061 2
        if ($dataRels) {
2062
            // exists and not empty if the ribbon have some pictures (other than internal MSO)
2063
            $UIRels = simplexml_load_string(
2064
                $this->getSecurityScannerOrThrow()
2065
                    ->scan($dataRels),
2066
                SimpleXMLElement::class,
2067
                $this->parseHuge ? LIBXML_PARSEHUGE : 0
2068
            );
2069
            if (false !== $UIRels) {
2070
                // we need to save id and target to avoid parsing customUI.xml and "guess" if it's a pseudo callback who load the image
2071
                foreach ($UIRels->Relationship as $ele) {
2072
                    if ((string) $ele['Type'] === Namespaces::SCHEMA_OFFICE_DOCUMENT . '/image') {
2073
                        // an image ?
2074
                        $customUIImagesNames[(string) $ele['Id']] = (string) $ele['Target'];
2075
                        $customUIImagesBinaries[(string) $ele['Target']] = $this->getFromZipArchive($zip, $baseDir . '/' . (string) $ele['Target']);
2076
                    }
2077
                }
2078
            }
2079
        }
2080 2
        if ($localRibbon) {
2081 2
            $excel->setRibbonXMLData($customUITarget, $localRibbon);
2082 2
            if (count($customUIImagesNames) > 0 && count($customUIImagesBinaries) > 0) {
2083
                $excel->setRibbonBinObjects($customUIImagesNames, $customUIImagesBinaries);
2084
            } else {
2085 2
                $excel->setRibbonBinObjects(null, null);
2086
            }
2087
        } else {
2088
            $excel->setRibbonXMLData(null, null);
2089
            $excel->setRibbonBinObjects(null, null);
2090
        }
2091
    }
2092
2093
    /** @param null|bool|mixed[]|SimpleXMLElement $array */
2094 702
    private static function getArrayItem(null|array|bool|SimpleXMLElement $array, int|string $key = 0): mixed
2095
    {
2096 702
        return ($array === null || is_bool($array)) ? null : ($array[$key] ?? null);
2097
    }
2098
2099
    /** @param null|bool|mixed[]|SimpleXMLElement $array */
2100 694
    private static function getArrayItemString(null|array|bool|SimpleXMLElement $array, int|string $key = 0): string
2101
    {
2102 694
        $retVal = self::getArrayItem($array, $key);
2103
2104 694
        return StringHelper::convertToString($retVal, false);
2105
    }
2106
2107
    /** @param null|bool|mixed[]|SimpleXMLElement $array */
2108 60
    private static function getArrayItemIntOrSxml(null|array|bool|SimpleXMLElement $array, int|string $key = 0): int|SimpleXMLElement
2109
    {
2110 60
        $retVal = self::getArrayItem($array, $key);
2111
2112 60
        return (is_int($retVal) || $retVal instanceof SimpleXMLElement) ? $retVal : 0;
2113
    }
2114
2115 370
    private static function dirAdd(null|SimpleXMLElement|string $base, null|SimpleXMLElement|string $add): string
2116
    {
2117 370
        $base = (string) $base;
2118 370
        $add = (string) $add;
2119
2120 370
        return (string) preg_replace('~[^/]+/\.\./~', '', dirname($base) . "/$add");
2121
    }
2122
2123
    /** @return mixed[] */
2124 2
    private static function toCSSArray(string $style): array
2125
    {
2126 2
        $style = self::stripWhiteSpaceFromStyleString($style);
2127
2128 2
        $temp = explode(';', $style);
2129 2
        $style = [];
2130 2
        foreach ($temp as $item) {
2131 2
            $item = explode(':', $item);
2132
2133 2
            if (str_contains($item[1], 'px')) {
2134 1
                $item[1] = str_replace('px', '', $item[1]);
2135
            }
2136 2
            if (str_contains($item[1], 'pt')) {
2137 2
                $item[1] = str_replace('pt', '', $item[1]);
2138 2
                $item[1] = (string) Font::fontSizeToPixels((int) $item[1]);
2139
            }
2140 2
            if (str_contains($item[1], 'in')) {
2141
                $item[1] = str_replace('in', '', $item[1]);
2142
                $item[1] = (string) Font::inchSizeToPixels((int) $item[1]);
2143
            }
2144 2
            if (str_contains($item[1], 'cm')) {
2145
                $item[1] = str_replace('cm', '', $item[1]);
2146
                $item[1] = (string) Font::centimeterSizeToPixels((int) $item[1]);
2147
            }
2148
2149 2
            $style[$item[0]] = $item[1];
2150
        }
2151
2152 2
        return $style;
2153
    }
2154
2155 5
    public static function stripWhiteSpaceFromStyleString(string $string): string
2156
    {
2157 5
        return trim(str_replace(["\r", "\n", ' '], '', $string), ';');
2158
    }
2159
2160 89
    private static function boolean(string $value): bool
2161
    {
2162 89
        if (is_numeric($value)) {
2163 69
            return (bool) $value;
2164
        }
2165
2166 33
        return $value === 'true' || $value === 'TRUE';
2167
    }
2168
2169
    /** @param string[] $hyperlinks */
2170 57
    private function readHyperLinkDrawing(\PhpOffice\PhpSpreadsheet\Worksheet\Drawing $objDrawing, SimpleXMLElement $cellAnchor, array $hyperlinks): void
2171
    {
2172 57
        $hlinkClick = $cellAnchor->pic->nvPicPr->cNvPr->children(Namespaces::DRAWINGML)->hlinkClick;
2173
2174 57
        if ($hlinkClick->count() === 0) {
2175 55
            return;
2176
        }
2177
2178 2
        $hlinkId = (string) self::getAttributes($hlinkClick, Namespaces::SCHEMA_OFFICE_DOCUMENT)['id'];
2179 2
        $hyperlink = new Hyperlink(
2180 2
            $hyperlinks[$hlinkId],
2181 2
            self::getArrayItemString(self::getAttributes($cellAnchor->pic->nvPicPr->cNvPr), 'name')
2182 2
        );
2183 2
        $objDrawing->setHyperlink($hyperlink);
2184
    }
2185
2186 679
    private function readProtection(Spreadsheet $excel, SimpleXMLElement $xmlWorkbook): void
2187
    {
2188 679
        if (!$xmlWorkbook->workbookProtection) {
2189 659
            return;
2190
        }
2191
2192 24
        $excel->getSecurity()->setLockRevision(self::getLockValue($xmlWorkbook->workbookProtection, 'lockRevision'));
2193 24
        $excel->getSecurity()->setLockStructure(self::getLockValue($xmlWorkbook->workbookProtection, 'lockStructure'));
2194 24
        $excel->getSecurity()->setLockWindows(self::getLockValue($xmlWorkbook->workbookProtection, 'lockWindows'));
2195
2196 24
        if ($xmlWorkbook->workbookProtection['revisionsPassword']) {
2197 1
            $excel->getSecurity()->setRevisionsPassword(
2198 1
                (string) $xmlWorkbook->workbookProtection['revisionsPassword'],
2199 1
                true
2200 1
            );
2201
        }
2202
2203 24
        if ($xmlWorkbook->workbookProtection['workbookPassword']) {
2204 2
            $excel->getSecurity()->setWorkbookPassword(
2205 2
                (string) $xmlWorkbook->workbookProtection['workbookPassword'],
2206 2
                true
2207 2
            );
2208
        }
2209
    }
2210
2211 24
    private static function getLockValue(SimpleXMLElement $protection, string $key): ?bool
2212
    {
2213 24
        $returnValue = null;
2214 24
        $protectKey = $protection[$key];
2215 24
        if (!empty($protectKey)) {
2216 10
            $protectKey = (string) $protectKey;
2217 10
            $returnValue = $protectKey !== 'false' && (bool) $protectKey;
2218
        }
2219
2220 24
        return $returnValue;
2221
    }
2222
2223
    /** @param mixed[][][][] $unparsedLoadedData */
2224 677
    private function readFormControlProperties(Spreadsheet $excel, string $dir, string $fileWorksheet, Worksheet $docSheet, array &$unparsedLoadedData): void
2225
    {
2226 677
        $zip = $this->zip;
2227 677
        if ($zip->locateName(dirname("$dir/$fileWorksheet") . '/_rels/' . basename($fileWorksheet) . '.rels') === false) {
2228 342
            return;
2229
        }
2230
2231 558
        $filename = dirname("$dir/$fileWorksheet") . '/_rels/' . basename($fileWorksheet) . '.rels';
2232 558
        $relsWorksheet = $this->loadZipNoNamespace($filename, Namespaces::RELATIONSHIPS);
2233 558
        $ctrlProps = [];
2234 558
        foreach ($relsWorksheet->Relationship as $ele) {
2235 393
            if ((string) $ele['Type'] === Namespaces::SCHEMA_OFFICE_DOCUMENT . '/ctrlProp') {
2236 4
                $ctrlProps[(string) $ele['Id']] = $ele;
2237
            }
2238
        }
2239
2240
        /** @var mixed[][] */
2241 558
        $unparsedCtrlProps = &$unparsedLoadedData['sheets'][$docSheet->getCodeName()]['ctrlProps'];
2242 558
        foreach ($ctrlProps as $rId => $ctrlProp) {
2243 4
            $rId = substr($rId, 3); // rIdXXX
2244 4
            $unparsedCtrlProps[$rId] = [];
2245 4
            $unparsedCtrlProps[$rId]['filePath'] = self::dirAdd("$dir/$fileWorksheet", $ctrlProp['Target']);
2246 4
            $unparsedCtrlProps[$rId]['relFilePath'] = (string) $ctrlProp['Target'];
2247 4
            $unparsedCtrlProps[$rId]['content'] = $this->getSecurityScannerOrThrow()->scan($this->getFromZipArchive($zip, $unparsedCtrlProps[$rId]['filePath']));
2248
        }
2249 558
        unset($unparsedCtrlProps);
2250
    }
2251
2252
    /** @param mixed[][][][] $unparsedLoadedData */
2253 677
    private function readPrinterSettings(Spreadsheet $excel, string $dir, string $fileWorksheet, Worksheet $docSheet, array &$unparsedLoadedData): void
2254
    {
2255 677
        if ($this->readDataOnly) {
2256 3
            return;
2257
        }
2258 674
        $zip = $this->zip;
2259 674
        if ($zip->locateName(dirname("$dir/$fileWorksheet") . '/_rels/' . basename($fileWorksheet) . '.rels') === false) {
2260 341
            return;
2261
        }
2262
2263 555
        $filename = dirname("$dir/$fileWorksheet") . '/_rels/' . basename($fileWorksheet) . '.rels';
2264 555
        $relsWorksheet = $this->loadZipNoNamespace($filename, Namespaces::RELATIONSHIPS);
2265 555
        $sheetPrinterSettings = [];
2266 555
        foreach ($relsWorksheet->Relationship as $ele) {
2267 391
            if ((string) $ele['Type'] === Namespaces::SCHEMA_OFFICE_DOCUMENT . '/printerSettings') {
2268 283
                $sheetPrinterSettings[(string) $ele['Id']] = $ele;
2269
            }
2270
        }
2271
2272
        /** @var mixed[][] */
2273 555
        $unparsedPrinterSettings = &$unparsedLoadedData['sheets'][$docSheet->getCodeName()]['printerSettings'];
2274 555
        foreach ($sheetPrinterSettings as $rId => $printerSettings) {
2275 283
            $rId = substr($rId, 3); // rIdXXX
2276 283
            if (!str_ends_with($rId, 'ps')) {
2277 283
                $rId = $rId . 'ps'; // rIdXXX, add 'ps' suffix to avoid identical resource identifier collision with unparsed vmlDrawing
2278
            }
2279 283
            $unparsedPrinterSettings[$rId] = [];
2280 283
            $target = (string) str_replace('/xl/', '../', (string) $printerSettings['Target']);
2281 283
            $unparsedPrinterSettings[$rId]['filePath'] = self::dirAdd("$dir/$fileWorksheet", $target);
2282 283
            $unparsedPrinterSettings[$rId]['relFilePath'] = $target;
2283 283
            $unparsedPrinterSettings[$rId]['content'] = $this->getSecurityScannerOrThrow()->scan($this->getFromZipArchive($zip, $unparsedPrinterSettings[$rId]['filePath']));
2284
        }
2285 555
        unset($unparsedPrinterSettings);
2286
    }
2287
2288
    /** @return array{string, string} */
2289 690
    private function getWorkbookBaseName(): array
2290
    {
2291 690
        $workbookBasename = '';
2292 690
        $xmlNamespaceBase = '';
2293
2294
        // check if it is an OOXML archive
2295 690
        $rels = $this->loadZip(self::INITIAL_FILE);
2296 690
        foreach ($rels->children(Namespaces::RELATIONSHIPS)->Relationship as $rel) {
2297 690
            $rel = self::getAttributes($rel);
2298 690
            $type = (string) $rel['Type'];
2299
            switch ($type) {
2300 682
                case Namespaces::OFFICE_DOCUMENT:
2301 669
                case Namespaces::PURL_OFFICE_DOCUMENT:
2302 690
                    $basename = basename((string) $rel['Target']);
2303 690
                    $xmlNamespaceBase = dirname($type);
2304 690
                    if (preg_match('/workbook.*\.xml/', $basename)) {
2305 690
                        $workbookBasename = $basename;
2306
                    }
2307
2308 690
                    break;
2309
            }
2310
        }
2311
2312 690
        return [$workbookBasename, $xmlNamespaceBase];
2313
    }
2314
2315 668
    private function readSheetProtection(Worksheet $docSheet, SimpleXMLElement $xmlSheet): void
2316
    {
2317 668
        if ($this->readDataOnly || !$xmlSheet->sheetProtection) {
2318 611
            return;
2319
        }
2320
2321 67
        $algorithmName = (string) $xmlSheet->sheetProtection['algorithmName'];
2322 67
        $protection = $docSheet->getProtection();
2323 67
        $protection->setAlgorithm($algorithmName);
2324
2325 67
        if ($algorithmName) {
2326 2
            $protection->setPassword((string) $xmlSheet->sheetProtection['hashValue'], true);
2327 2
            $protection->setSalt((string) $xmlSheet->sheetProtection['saltValue']);
2328 2
            $protection->setSpinCount((int) $xmlSheet->sheetProtection['spinCount']);
2329
        } else {
2330 66
            $protection->setPassword((string) $xmlSheet->sheetProtection['password'], true);
2331
        }
2332
2333 67
        if ($xmlSheet->protectedRanges->protectedRange) {
2334 4
            foreach ($xmlSheet->protectedRanges->protectedRange as $protectedRange) {
2335 4
                $docSheet->protectCells((string) $protectedRange['sqref'], (string) $protectedRange['password'], true, (string) $protectedRange['name'], (string) $protectedRange['securityDescriptor']);
2336
            }
2337
        }
2338
    }
2339
2340 675
    private function readAutoFilter(
2341
        SimpleXMLElement $xmlSheet,
2342
        Worksheet $docSheet
2343
    ): void {
2344 675
        if ($xmlSheet && $xmlSheet->autoFilter) {
2345 18
            (new AutoFilter($docSheet, $xmlSheet))->load();
2346
        }
2347
    }
2348
2349 675
    private function readBackgroundImage(
2350
        SimpleXMLElement $xmlSheet,
2351
        Worksheet $docSheet,
2352
        string $relsName
2353
    ): void {
2354 675
        if ($xmlSheet && $xmlSheet->picture) {
2355 1
            $id = (string) self::getArrayItemString(self::getAttributes($xmlSheet->picture, Namespaces::SCHEMA_OFFICE_DOCUMENT), 'id');
2356 1
            $rels = $this->loadZip($relsName);
2357 1
            foreach ($rels->Relationship as $rel) {
2358 1
                $attrs = $rel->attributes() ?? [];
2359 1
                $rid = (string) ($attrs['Id'] ?? '');
2360 1
                $target = (string) ($attrs['Target'] ?? '');
2361 1
                if ($rid === $id && substr($target, 0, 2) === '..') {
2362 1
                    $target = 'xl' . substr($target, 2);
2363 1
                    $content = $this->getFromZipArchive($this->zip, $target);
2364 1
                    $docSheet->setBackgroundImage($content);
2365
                }
2366
            }
2367
        }
2368
    }
2369
2370
    /**
2371
     * @param TableDxfsStyle[] $tableStyles
2372
     * @param Style[] $dxfs
2373
     */
2374 678
    private function readTables(
2375
        SimpleXMLElement $xmlSheet,
2376
        Worksheet $docSheet,
2377
        string $dir,
2378
        string $fileWorksheet,
2379
        ZipArchive $zip,
2380
        string $namespaceTable,
2381
        array $tableStyles,
2382
        array $dxfs
2383
    ): void {
2384 678
        if ($xmlSheet && $xmlSheet->tableParts) {
2385
            /** @var array{count: scalar} */
2386 37
            $attributes = $xmlSheet->tableParts->attributes() ?? ['count' => 0];
2387 37
            if (((int) $attributes['count']) > 0) {
2388 33
                $this->readTablesInTablesFile($xmlSheet, $dir, $fileWorksheet, $zip, $docSheet, $namespaceTable, $tableStyles, $dxfs);
2389
            }
2390
        }
2391
    }
2392
2393
    /**
2394
     * @param TableDxfsStyle[] $tableStyles
2395
     * @param Style[] $dxfs
2396
     */
2397 33
    private function readTablesInTablesFile(
2398
        SimpleXMLElement $xmlSheet,
2399
        string $dir,
2400
        string $fileWorksheet,
2401
        ZipArchive $zip,
2402
        Worksheet $docSheet,
2403
        string $namespaceTable,
2404
        array $tableStyles,
2405
        array $dxfs
2406
    ): void {
2407 33
        foreach ($xmlSheet->tableParts->tablePart as $tablePart) {
2408 33
            $relation = self::getAttributes($tablePart, Namespaces::SCHEMA_OFFICE_DOCUMENT);
2409 33
            $tablePartRel = (string) $relation['id'];
2410 33
            $relationsFileName = dirname("$dir/$fileWorksheet") . '/_rels/' . basename($fileWorksheet) . '.rels';
2411
2412 33
            if ($zip->locateName($relationsFileName) !== false) {
2413 33
                $relsTableReferences = $this->loadZip($relationsFileName, Namespaces::RELATIONSHIPS);
2414 33
                foreach ($relsTableReferences->Relationship as $relationship) {
2415 33
                    $relationshipAttributes = self::getAttributes($relationship, '');
2416
2417 33
                    if ((string) $relationshipAttributes['Id'] === $tablePartRel) {
2418 33
                        $relationshipFileName = (string) $relationshipAttributes['Target'];
2419 33
                        $relationshipFilePath = dirname("$dir/$fileWorksheet") . '/' . $relationshipFileName;
2420 33
                        $relationshipFilePath = File::realpath($relationshipFilePath);
2421
2422 33
                        if ($this->fileExistsInArchive($this->zip, $relationshipFilePath)) {
2423 33
                            $tableXml = $this->loadZip($relationshipFilePath, $namespaceTable);
2424 33
                            (new TableReader($docSheet, $tableXml))->load($tableStyles, $dxfs);
2425
                        }
2426
                    }
2427
                }
2428
            }
2429
        }
2430
    }
2431
2432
    /** @return mixed[] */
2433 686
    private static function extractStyles(?SimpleXMLElement $sxml, string $node1, string $node2): array
2434
    {
2435 686
        $array = [];
2436 686
        if ($sxml && $sxml->{$node1}->{$node2}) {
2437
            /** @var SimpleXMLElement */
2438 686
            $temp = $sxml->{$node1}->{$node2};
2439 686
            foreach ($temp as $node) {
2440 686
                $array[] = $node;
2441
            }
2442
        }
2443
2444 686
        return $array;
2445
    }
2446
2447
    /** @return string[] */
2448 686
    private static function extractPalette(?SimpleXMLElement $sxml): array
2449
    {
2450 686
        $array = [];
2451 686
        if ($sxml && $sxml->colors->indexedColors) {
2452 16
            foreach ($sxml->colors->indexedColors->rgbColor as $node) {
2453 16
                $attr = $node->attributes();
2454 16
                if (isset($attr['rgb'])) {
2455 16
                    $array[] = (string) $attr['rgb'];
2456
                }
2457
            }
2458
        }
2459
2460 686
        return $array;
2461
    }
2462
2463 4
    private function processIgnoredErrors(SimpleXMLElement $xml, Worksheet $sheet): void
2464
    {
2465 4
        $cellCollection = $sheet->getCellCollection();
2466 4
        $attributes = self::getAttributes($xml);
2467 4
        $sqref = (string) ($attributes['sqref'] ?? '');
2468 4
        $numberStoredAsText = (string) ($attributes['numberStoredAsText'] ?? '');
2469 4
        $formula = (string) ($attributes['formula'] ?? '');
2470 4
        $formulaRange = (string) ($attributes['formulaRange'] ?? '');
2471 4
        $twoDigitTextYear = (string) ($attributes['twoDigitTextYear'] ?? '');
2472 4
        $evalError = (string) ($attributes['evalError'] ?? '');
2473 4
        if (!empty($sqref)) {
2474 4
            $explodedSqref = explode(' ', $sqref);
2475 4
            $pattern1 = '/^([A-Z]{1,3})([0-9]{1,7})(:([A-Z]{1,3})([0-9]{1,7}))?$/';
2476 4
            foreach ($explodedSqref as $sqref1) {
2477 4
                if (preg_match($pattern1, $sqref1, $matches) === 1) {
2478 4
                    $firstRow = $matches[2];
2479 4
                    $firstCol = $matches[1];
2480 4
                    if (array_key_exists(3, $matches)) {
2481 3
                        $lastCol = $matches[4];
2482 3
                        $lastRow = $matches[5];
2483
                    } else {
2484 3
                        $lastCol = $firstCol;
2485 3
                        $lastRow = $firstRow;
2486
                    }
2487 4
                    ++$lastCol;
2488 4
                    for ($row = $firstRow; $row <= $lastRow; ++$row) {
2489 4
                        for ($col = $firstCol; $col !== $lastCol; ++$col) {
2490
                            /** @var string $col */
2491 4
                            if (!$cellCollection->has2("$col$row")) {
2492 1
                                continue;
2493
                            }
2494 4
                            if ($numberStoredAsText === '1') {
2495 4
                                $sheet->getCell("$col$row")->getIgnoredErrors()->setNumberStoredAsText(true);
2496
                            }
2497 4
                            if ($formula === '1') {
2498 1
                                $sheet->getCell("$col$row")->getIgnoredErrors()->setFormula(true);
2499
                            }
2500 4
                            if ($formulaRange === '1') {
2501 1
                                $sheet->getCell("$col$row")->getIgnoredErrors()->setFormulaRange(true);
2502
                            }
2503 4
                            if ($twoDigitTextYear === '1') {
2504 1
                                $sheet->getCell("$col$row")->getIgnoredErrors()->setTwoDigitTextYear(true);
2505
                            }
2506 4
                            if ($evalError === '1') {
2507 1
                                $sheet->getCell("$col$row")->getIgnoredErrors()->setEvalError(true);
2508
                            }
2509
                        }
2510
                    }
2511
                }
2512
            }
2513
        }
2514
    }
2515
2516 364
    private static function storeFormulaAttributes(SimpleXMLElement $f, Worksheet $docSheet, string $r): void
2517
    {
2518 364
        $formulaAttributes = [];
2519 364
        $attributes = $f->attributes();
2520 364
        if (isset($attributes['t'])) {
2521 247
            $formulaAttributes['t'] = (string) $attributes['t'];
2522
        }
2523 364
        if (isset($attributes['ref'])) {
2524 247
            $formulaAttributes['ref'] = (string) $attributes['ref'];
2525
        }
2526 364
        if (!empty($formulaAttributes)) {
2527 247
            $docSheet->getCell($r)->setFormulaAttributes($formulaAttributes);
2528
        }
2529
    }
2530
2531 13
    private static function onlyNoteVml(string $data): bool
2532
    {
2533 13
        $data = str_replace('<br>', '<br/>', $data);
2534
2535
        try {
2536 13
            $sxml = @simplexml_load_string($data);
2537
        } catch (Throwable) {
2538
            $sxml = false;
2539
        }
2540
2541 13
        if ($sxml === false) {
2542 1
            return false;
2543
        }
2544 12
        $shapes = $sxml->children(Namespaces::URN_VML);
2545 12
        foreach ($shapes->shape as $shape) {
2546 12
            $clientData = $shape->children(Namespaces::URN_EXCEL);
2547 12
            if (!isset($clientData->ClientData)) {
2548
                return false;
2549
            }
2550 12
            $attrs = $clientData->ClientData->attributes();
2551 12
            if (!isset($attrs['ObjectType'])) {
2552
                return false;
2553
            }
2554 12
            $objectType = (string) $attrs['ObjectType'];
2555 12
            if ($objectType !== 'Note') {
2556 4
                return false;
2557
            }
2558
        }
2559
2560 9
        return true;
2561
    }
2562
}
2563