12

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

Arya
  • 566
  • 2
  • 9
  • 22
  • 1
    The code you are looking for is: https://github.com/PHPOffice/PhpSpreadsheet/blob/develop/src/PhpSpreadsheet/Reader/Xlsx.php#L69 – Martin Dec 17 '18 at 21:49
  • I can only presume, based on what you've said and by looking at the GitHub code, that the file you are trying to load isn't in the xlsx format that phpsreadsheet thinks it should be – Martin Dec 17 '18 at 21:52
  • Are you able to open your test file `Test.xlsx` with 7-zip to verify that it is a ZIP acrhive? You're looking for a file named `workbook.xml` inside the archive – Martin Dec 17 '18 at 21:53
  • @MartinParkin - what other xlsx format is there? Also, yes, I am able to open the xlsx file as a zip. If I change the extension to .zip, then I can extract it and see the xml code. – Arya Dec 17 '18 at 21:54
  • You said that you can create the Xlsx reader yourself and read the file but that you receive the same error. That can't be the case - what error do you get if you try to read the file directly with the Xlsx reader? – Martin Dec 17 '18 at 22:03
  • I need to clarify my post then: I can create an xlsx reader myself, but it cannot read an xlsx file, it gives the same error. Also, I can create an xls reader myself, and I can read an xls file with no error. – Arya Dec 17 '18 at 22:07
  • 1
    It cannot give the same error - it doesn't use the same code. The `load` function is at line 389 in the same file I referenced earlier. Could you check again to see what error the `load` function gives? – Martin Dec 17 '18 at 22:09
  • Yes, I just updated my post to show the other error message I get. If I use `IOFactory::load($Source_File);` or `\PhpOffice\PhpSpreadsheet\IOFactory::load($Source_File);`, then I get the same error, but if I use `$reader->load($Source_File);` then I get a different error message. – Arya Dec 17 '18 at 22:18
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/185387/discussion-between-martin-parkin-and-arya). – Martin Dec 17 '18 at 22:30
  • 1
    Please add the master tag to the question whenever you ask one. People are usually watching main tags. I have added the php tag. If you would have added it earlier i would have seen it earlier. One of the reasons your question did not have the needed visibility is the absence of php tag. – Shahbaz A. Feb 18 '19 at 08:10
  • @shazyriver: Ok. I too just added `xlsx` as a tag. – Arya Feb 18 '19 at 22:13
  • I'm getting this same error when trying to read in a `xlsx` file. Came here hoping to find an answer, hopefully soon.... – dmikester1 Mar 29 '19 at 17:36
  • @dmikester1 What is your computer setup? Maybe this will help us narrow down the cause of the issue, such as the OS, PHP version, one of the zip libraries, etc.... – Arya Mar 29 '19 at 21:48
  • OK, the answer to mine was very simple. Maybe it won't be as simple for you. But I was manually grabbing the extension from the file as `xlxs`. I learned if I switched it to `Xlxs`, it worked just fine. – dmikester1 Mar 30 '19 at 02:30
  • @dmikester1 Will you please explain more about "grabbing the extension from the file"? For example, did you change the extension of the file from `xlsx` to `Xlsx` before you read from it or did you use `$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader("Xlsx");` (which did not work for me) or something else? Perhaps you can clarify your solution as an answer. – Arya Mar 30 '19 at 06:59

5 Answers5

25

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.

Shahbaz A.
  • 4,047
  • 4
  • 34
  • 55
  • 1
    I get the following error with `$inputFileType = \PhpOffice\PhpSpreadsheet\IOFactory::identify($inputFileName);`: `Fatal error: Uncaught PhpOffice\PhpSpreadsheet\Reader\Exception: Unable to identify a reader for this file` Do you have any thoughts on why this error occurs? The XLSX input file I am using is a `Microsoft Excel 2007+` file type. – Arya Feb 18 '19 at 08:01
  • Then probably there is some error in your file. Why don't you try with a sample file ? Here is the github url of standard samples https://github.com/PHPOffice/PhpSpreadsheet/tree/master/samples/Reader/sampleData – Shahbaz A. Feb 18 '19 at 08:05
  • If it works for the sample then you can check what is the problem with your file and the difference between your file and working file. – Shahbaz A. Feb 18 '19 at 08:06
  • None of the samples at the github link you provided are an XLSX file, I only see CSV, TSV, and XLS files. Is there an XLSX file that I am not noticing? – Arya Feb 18 '19 at 19:15
  • I downloaded a couple other XLSX files [http://file-examples.com/wp-content/uploads/2017/02/file_example_XLSX_10.xlsx] that were either `Excel 2007+` or `Microsoft OOXML` and the same error still displays for all of them. So, it seems the issue is not the XLSX file. Perhaps it is a configuration on my system, but what could it be? – Arya Feb 18 '19 at 19:24
  • I am [unable to read excel file](https://stackoverflow.com/q/64906846/6854117) – Moeez Nov 19 '20 at 07:13
3

I had the same issue, after adding .xlsx files to a git repository on my Mac.
The problem was that git auto-converted the line endings.

The solution was to add these lines to the .gitattributes file:

*.xls   binary
*.xlsx  binary
lewis
  • 33
  • 4
2

I ran into this same exact error when trying to load in an XLSX file. For me personally, I discovered a very easy fix that fixed my issue. I was manually grabbing the extension off the filename as xlsx. I noticed some other code of mine using the old PHP Spreadsheet library was taking in the extension Xls. So I tried loading in Xlsx and it worked perfectly.

Here is the code I am using to correctly load in the extension. It simply grabs all the characters after the last period and then captilizes the first character of that substring. ucfirst simply uppercases the first letter of the string passed into it. substr returns a substring where the first parameter is the string to grab from and the second parameter is what index to start the substring at in the given string. And finally strrpos finds the last occurrence of a substring in the given string.

https://www.php.net/manual/en/function.ucfirst.php

https://www.php.net/manual/en/function.strrpos

https://www.php.net/manual/en/function.substr.php

$inputFileType = ucfirst(substr($cccFile, strrpos($cccFile, '.') + 1));

/**  Create a new Reader of the type defined in $inputFileType  **/
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);

Once, I added in the ucfirst command, it solved the issue for me.

dmikester1
  • 1,374
  • 11
  • 55
  • 113
0

Use this. It will show the .xlsx

   $inputFileName = public_path('asset/docs/Filename.xlsx');
    
   /** Load $inputFileName to a Spreadsheet Object  **/
   $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($inputFileName);
   $writer = IOFactory::createWriter($spreadsheet, 'Html');
   $message = $writer->save('php://output');
Biswa
  • 479
  • 7
  • 11
0

I got similar issue in my Drupal page where I used "private://..." as file path. getFromZipArchive in PHPExcel (should be similar in PhpSpreadsheet) cannot read file path scheme like "private://...". After translate it to local path, it works.

In your case, you have path scheme like "file:///home/ar...". So use "/home/ar..." instead.

Cameron Wong
  • 68
  • 10