Read Xlsx file in PhpSpreadsheet

php excel xlsx ziparchive phpspreadsheet
Arya picture Arya · Dec 17, 2018 · Viewed 14.6k times · Source

I want to read an xlsx file that was created in Microsoft Excel, but when I run the following code...

$Source_File = "test.xlsx";
$Spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($Source_File);

...I receive the following error:

Fatal error: Uncaught PhpOffice\PhpSpreadsheet\Reader\Exception: Unable to identify a reader for this file in /var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/IOFactory.php:163
Stack trace:
  #0 /var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/IOFactory.php(93): PhpOffice\PhpSpreadsheet\IOFactory::createReaderForFile('file:///home/ar...')
  #1 /var/www/html/Function_Spreadsheet.php(480): PhpOffice\PhpSpreadsheet\IOFactory::load('file:///home/ar...')
  #2 /var/www/html/Function_Home.php(3747): Spreadsheet_Reader_1('/var/www/html/F...', 3745, Array, Array)
  #3 {main} thrown in /var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/IOFactory.php on line 163

I get the same error if I instead use $Spreadsheet = IOFactory::load($Source_File);

I get the following error if I instead use $Spreadsheet = $reader->load($Source_File);

Warning: ZipArchive::getFromName(): Invalid or uninitialized Zip object in /var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Reader/Xlsx.php on line 311

Warning: ZipArchive::getFromName(): Invalid or uninitialized Zip object in /var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Reader/Xlsx.php on line 313

Notice: Trying to get property 'Relationship' of non-object in /var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Reader/Xlsx.php on line 350

Warning: Invalid argument supplied for foreach() in /var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Reader/Xlsx.php on line 350

Warning: ZipArchive::getFromName(): Invalid or uninitialized Zip object in /var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Reader/Xlsx.php on line 311

Warning: ZipArchive::getFromName(): Invalid or uninitialized Zip object in /var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Reader/Xlsx.php on line 313

Notice: Trying to get property 'Relationship' of non-object in /var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Reader/Xlsx.php on line 397

Warning: Invalid argument supplied for foreach() in /var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Reader/Xlsx.php on line 397

Warning: ZipArchive::getFromName(): Invalid or uninitialized Zip object in /var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Reader/Xlsx.php on line 311

Warning: ZipArchive::getFromName(): Invalid or uninitialized Zip object in /var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Reader/Xlsx.php on line 313

Notice: Trying to get property 'Override' of non-object in /var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Reader/Xlsx.php on line 1855

Warning: Invalid argument supplied for foreach() in /var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Reader/Xlsx.php on line 1855

Warning: ZipArchive::close(): Invalid or uninitialized Zip object in /var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Reader/Xlsx.php on line 1883

The file is readable and open-able by my PHP v7.2 script, in Apache on Ubuntu 18.04. I read several forum posts, which suggest the following, which I have done:

I tried opening the file in LibreOffice and saving it as an xlsx there, but the same error occurs (no error if I save as xls).

I can create a reader $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();, but when I do $Spreadsheet = $reader->load($Source_File); or $Spreadsheet = IOFactory::load($Source_File); I get the same error.

Also, I can create an xls reader that can read xls files. I can also create an xlsx reader, but it will not read the xlsx file, it gives the same error when trying to read the xlsx file. So, why is the error occurring with the xlsx file?

Also, I read the source code that the error message points to (IOFactory.php) and found the following location (near line #139) where the error occurs...

//Let's see if we are lucky
if (isset($reader) && $reader->canRead($filename))
{
    return $reader;
}

...and I searched for the definition of canRead, but did not find it anywhere in /vendor/phpoffice/phpspreadsheet/. Where is canRead defined? I think if I could read the definition of canRead, then maybe I will understand what the root cause of the issue is.

UPDATE:

I learned from comments and discussion that canRead() is defined in \PhpSpreadsheet\Reader\Xlsx.php starting around line 65. In canRead(), $zip->open($pFilename) returns an error code, ZipArchive::ER_NOENT, which means "No such file". However, the file exists. So, why is this error occurring?

UPDATE - 2018-12-18

This web page suggests that there are multiple types of xlsx files. So, I ran file test.xlsx, which displayed Microsoft Excel 2007+. Then I opened up the spreadsheet in LibreOffice Calc and saved it as an OOXML type of xlsx file and re-ran file test.xlsx, which displayed Microsoft OOXML. Then I re-ran the PHP script, but got the same error. So, it seems that my xlsx file type is not the issue.

So, I decided to use PHPExcel (even though it is deprecated) to get some necessary work done. When I ran the script using PHPExcel, I received a similar error about canRead() not being able to detect the xlsx file.

So, I continued reading this web page and followed wesood's last suggestion, which was sourced from the accepted answer on this web page. This solution worked for me: In the file /PHPExcel/IOFactory.php, I added PHPExcel_Settings::setZipClass(\PHPExcel_Settings::PCLZIP); immediately before if (isset($reader) && $reader->canRead($filename)).

However, I still want to know how to solve this issue in PhpSpreadsheet. It seems I need to learn more about how pclzip works and if a similar action needs to be done with PhpSpreadsheet.

UPDATE 2019-02-10:

I tried running the script today and it seems that the addition of PHPExcel_Settings::setZipClass(\PHPExcel_Settings::PCLZIP); no longer works. So, I am stuck again...

What am I doing wrong? Any help is welcome!

UPDATE 2019-02-18:

Following recommendations from comments, I tested the script using random XLSX files found via Google search results (e.g., this file), which were either Excel 2007+ or Microsoft OOXML types and the same error displays for PhpSpreadsheet:

Fatal error: Uncaught PhpOffice\PhpSpreadsheet\Reader\Exception: Unable to identify a reader for this file in /var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/IOFactory.php:176 Stack trace: #0 /var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/IOFactory.php(113): PhpOffice\PhpSpreadsheet\IOFactory::createReaderForFile('file:///var/www...') #1 /var/www/html/Function_Spreadsheet.php(798): PhpOffice\PhpSpreadsheet\IOFactory::identify('file:///var/www...') #2 /var/www/html/Function_Home.php(3748): Spreadsheet_Reader_1('/var/www/html/F...', 3746, Array, Array) #3 {main} thrown in /var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/IOFactory.php on line 176

Answer

shazyriver picture shazyriver · Feb 18, 2019

From my understanding, you are missing a piece. Why don't you first create a reader and then load the file.

Try the following code. It can identify the extension and create the reader of that type accordingly.

$inputFileName = "Text.xlsx";

/**  Identify the type of $inputFileName  **/
$inputFileType = \PhpOffice\PhpSpreadsheet\IOFactory::identify($inputFileName);

/**  Create a new Reader of the type that has been identified  **/
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);

/**  Load $inputFileName to a Spreadsheet Object  **/
$spreadsheet = $reader->load($inputFileName);

/**  Convert Spreadsheet Object to an Array for ease of use  **/
$schdeules = $spreadsheet->getActiveSheet()->toArray();

Now you can simply run a foreach loop on the result array.

foreach( $schdeules as $single_schedule )
{               
    echo '<div class="row">';
    foreach( $single_schedule as $single_item )
    {
        echo '<p class="item">' . $single_item . '</p>';
    }
    echo '</div>';
}

This is tested and working code.

Related questions

How to generate an .xlsx using php

I need to generate an Excel file with extension .xlsx. Here is my simple code: $file = "test.xlsx"; header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Disposition: attachment; filename='.$file); $content = "Col1\tCol2\tCol3\t\n"; $content .= "test1\ttest1\…

Read More
Lightweight Excel(xls/xlsx) php library needed

Possible Duplicate: Alternative for PHP_excel A friend would like to use a Php Excel library to read/write files on formats xls and xlsx, he knows about the one called "PhpExcel" but apparently is way too big of a …

Read More
Export MySQL data to Excel in PHP

I'm trying to get my MySQL data to Excel file, but I'm having problems with Excel cells. All my text goes to one cell, I would like to have each row value in separate Excel cell. Here is my code: $…

Read More