XLSX reader - Exception : You must specify a worksheet or a scope for a Named Range

[vpratfr]

This is a bug report

What is the expected behavior?

Read a simple XLSX file (no formula inside) without exception

What is the current behavior?

The XLSX reader crashes with the message

PhpOffice\PhpSpreadsheet\Exception : You must specify a worksheet or a scope for a Named Range
 /home/vagrant/code/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/NamedRange.php:21
 /home/vagrant/code/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/DefinedName.php:97
 /home/vagrant/code/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Reader/Xlsx.php:1398

What are the steps to reproduce?

// This works properly, file is open
$reader = new Xls();
$reader->load(storage_path('default.xls'));

// This fails to open with the above exception
$reader = new Xlsx();
$reader->load(storage_path('default.xlsx'));

test.zip

The above zip file contains the XLS and XLSX files that exhibit the behaviour. The XLS file is simply the XLSX file saved as XLS (no change in the spreadsheet itself).

Which versions of PhpSpreadsheet and PHP are affected?

Package 1.15.0
PHP 7.4

I have this problem... Solutions?? Return to PHPExcel-1.8 ?

$archivo = $_FILES["archivoCoe"]["tmp_name"];
$spreadsheet = IOFactory::load($archivo);
$codigo = $spreadsheet->getSheet(0)->getCell('A8')->getValue();

[jonothedev]

Is it possible you could tag this in a release so it fixes Laravel Excel package as they have explicitly included this package as "phpoffice/phpspreadsheet": "^1.14", and tagging would bump this fix.

[kevsharp81]

If you feel comfortable go into NamedRange.php in the source code and comment lines 20,21,22 out. Save the file and this should then work. I was having the same issue some where its not setting $worksheet or $scope variables. There should be logic in this code file that doesn't default them to null but instead sets them to something else a default value.

[alexxxxkkkk]

I have the same issue but only when filling the loadSheetsOnly array.

Example:

<?php

use PhpOffice\PhpSpreadsheet\IOFactory;

$reader = IOFactory::createReaderForFile('example.xlsx');

$reader->setLoadSheetsOnly(['Sheet 1', 'Sheet 2']);

$spreadsheet = $reader->load('example.xlsx');

It throws the error: "PhpOffice\PhpSpreadsheet\Exception: You must specify a worksheet or a scope for a Named Range" due to loading only specific sheets. (Xlsx file contains 3 sheets ('Sheet 1', 'Sheet 2', 'Sheet 3'))

PhpOffice\PhpSpreadsheet\Exception: You must specify a worksheet or a scope for a Named Range

vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/NamedRange.php:21
vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/DefinedName.php:97
vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Reader/Xlsx.php:1399

PhpSpreadsheet - 1.15.0

[kevsharp81]

Hi Alex, Ah, that's what I'm trying to do only load certain spreadsheets. I Found commenting those lines out in NamedRange.php did it and it's not effected the running of the code. It's probably not passing down the worksheet for some reason to that bit of code. Definitely sounds like a bug to me. Kevin

…

________________________________ From: alexxxxkkkk <notifications@github.com> Sent: 10 December 2020 17:01 To: PHPOffice/PhpSpreadsheet <PhpSpreadsheet@noreply.github.com> Cc: kevsharp81 <kevsharp81@hotmail.com>; Comment <comment@noreply.github.com> Subject: Re: [PHPOffice/PhpSpreadsheet] XLSX reader - Exception : You must specify a worksheet or a scope for a Named Range (#1686) I have the same issue but only when filling the loadSheetsOnly array. Example: <?php use PhpOffice\PhpSpreadsheet\IOFactory; $reader = IOFactory::createReaderForFile('example.xlsx'); $reader->setLoadSheetsOnly(['Sheet 1', 'Sheet 2']); $spreadsheet = $reader->load('example.xlsx'); It throws the error: "PhpOffice\PhpSpreadsheet\Exception: You must specify a worksheet or a scope for a Named Range" due to loading only specific sheets. (Xlsx file contains 3 sheets ('Sheet 1', 'Sheet 2', 'Sheet 3')) PhpOffice\PhpSpreadsheet\Exception: You must specify a worksheet or a scope for a Named Range vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/NamedRange.php:21 vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/DefinedName.php:97 vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Reader/Xlsx.php:1399 PhpSpreadsheet - 1.15.0 — You are receiving this because you commented. Reply to this email directly, view it on GitHub<#1686 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/ABVHMDDJK5P7UMLO7MY5GQDSUD5IHANCNFSM4S2EGSAQ>. [https://ipmcdn.avast.com/images/icons/icon-envelope-tick-green-avg-v1.png]<http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail> Virus-free. www.avg.com<http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>