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