Phpspreadsheet: xls file cause the exception during open by Xls reader

[odyswise]

This is:

- [x] a bug report
- [ ] a feature request
- [ ] **not** a usage question (ask them on https://stackoverflow.com/questions/tagged/phpspreadsheet or https://gitter.im/PHPOffice/PhpSpreadsheet)

What is the expected behavior?

Excel file opened for reading/write
I tried to use deprecated PhpExcel classes and the file opens successfully!

What is the current behavior?

Trying to open test excel file cause the exception:

Fatal error: Uncaught PhpOffice\PhpSpreadsheet\Reader\Exception: Parameter data is empty. in /home/.../vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Shared/OLERead.php:331 Stack trace: #0 /home/.../vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Shared/OLERead.php(281): PhpOffice\PhpSpreadsheet\Shared\OLERead::getInt4d('\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00...', 116) #1 /home/.../vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Shared/OLERead.php(187): PhpOffice\PhpSpreadsheet\Shared\OLERead->readPropertySets() #2 /home/.../vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Reader/Xls.php(1363): PhpOffice\PhpSpreadsheet\Shared\OLERead->read('/tmp/phpk3bjPS') #3 /home/.../vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Reader/Xls.php(628): PhpOffice\PhpSpreadsheet\Reader\ in /home/.../vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Shared/OLERead.php on line 331

What are the steps to reproduce?

Please provide a Minimal, Complete, and Verifiable example of code that exhibits the issue without relying on an external Excel file or a web server:

<?php

require __DIR__ . '/vendor/autoload.php';

// Create new Spreadsheet object
$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xls();
  $spreadsheet = $reader->load($_FILES['EXP_FILE']['tmp_name']);
  var_dump($spreadsheet->getSheet(0)->getCell('A1')->getValue());die;

Which versions of PhpSpreadsheet and PHP are affected?

PhpSpreadsheet 1.1 php 7.1

test.zip

[PowerKiKi]

I can confirm PHPExcel 1.8.1 can read that file, but not PhpSpreadsheet:

<?php

require __DIR__ . '/vendor/autoload.php';
require __DIR__ . '/../PHPExcel/Classes/PHPExcel.php';

\PHPExcel_IOFactory::load('test.xls');

$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xls();
$reader->load('test.xls');

[chorry]

From what i've found, example spreadsheet in "test.zip" has a sequence of null-bytes being parsed for property sets.
Due 4b4831be07249743364a7335178fb44b4b3eeca7 such sequence is considered malformed, therefore
OLERead::getInt4d will throw ReaderException

[odyswise]

This file was generated 3rd party software (Scanlister). I understand this file may have mistakes in structure, but LibreOffice, OpenOffice, MS Office opened this files without any errors. I think such files should be supported by PhpSpreadsheet also.

[minorbug]

Agree with @odyswise.

[ltressens]

Affected by this bug too. Agreed.

[ChadHydro]

Also affected by this bug

[Surely]

Also affected by this bug

[mythxxx]

+1. cannot parse XLS files previously parsed by phpexcel

[ChadHydro]

Could any of the devs comment on the priority of this issue? This is unfortunately affecting a critical production system. Would appreciate any insight you could provide. Thanks.

[bkcrisler]

I have had to resort to using PHPExcel instead.

On Jun 20, 2018, at 8:14 PM, Chad Hydro [e-mail] wrote:

Could any of the devs comment on the priority of this issue? This is unfortunately affecting a critical production system. Would appreciate any insight you could provide. Thanks.

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub, or mute the thread.

[inalto]

Same here, I reverted to phpexcel

[ltressens]

I have something ugly to handle this case like :

try {
    // use PHPSpreadsheet
}
catch (\Exception $e)
{
   // use PHPExcel
}

I hope a fix is on its way

[maakun12]

affected by this bug

[replay42]

also affected

[diego3]

also affected by this....

[Raftor74]

I have the same bug.
Here, a fix, that help me:

use PhpOffice\PhpSpreadsheet\Reader\Xls as ExcelReader;
$oldEncoding = "";
$filepath = "test.xls";
if (function_exists("mb_internal_encoding"))
{
    $oldEncoding = mb_internal_encoding();
    mb_internal_encoding('latin1');
    $reader = new PhpOffice\PhpSpreadsheet\Reader\Xls;
    $spreadsheet = $reader->load($filepath);
    mb_internal_encoding($oldEncoding);
}

// Example action
$maxRow = $spreadsheet->getActiveSheet()->getHighestRow();
print_r($maxRow);

[PowerKiKi]

The faulty commit may have been identified. Would any of you be able to partially revert that commit, test it and submit a PR ?

[corexz]

also affected, it was nightmare till I found @odyswise comment , everythig work fine after I copy the data to another file and save it, after that code woks fine , thanks @odyswise .

please fix this bug it is nasty and on try catch clause it gives NULL.

[PowerKiKi]

@Quix0r you wrote the commit that seems to be the source of the issue. Would you share your opinion on this ?

[mirazasx]

Is this resolved somehow? I'm still experiencing the same issue had to use the old PHPExcel to be able to read files.

[corexz]

Is this resolved somehow? I'm still experiencing the same issue had to use the old PHPExcel to be able to read files.

you can provide sample file to the users to fill out then upload it, that will save your day :)

[PowerKiKi]

Should be fixed in 1.5.0

[mirazasx]

I can't provide an example since it contains sensitive personal data and if I open with LibreOffice and save it then it works. Documents I'm recieving is auto generated by the thirdpart software, some kind of dialer.

I have found out that it doesn't work well with PHPExcel too, but there I had to change one line my self then it works as it should. I'm pretty sure that's not the right fix but it works for me for now.
I have updated _Excel5.php line 3744_ to:
if (!$this->readDataOnly && !$emptyCell && isset($this->mapCellXfIndex[$xfIndex])) { // add style information $cell->setXfIndex($this->mapCellXfIndex[$xfIndex]); }
It was failing when _index_ at _mapCellXfIndex_ wasn't defined, so after I added _isset(....)_ then it works for me.
Please let me know if there any way I can assist you in order to find and fix this bug :)

[Quix0r]

Sorry to cause some mess (?) which was never intended. I'm now with a new employer and PHPExcel (!) is still used here ...

[GianinaSalomo]

Updating to version 1.5.0 worked for me, but I started getting "undefined index: 0" warnings (like.. a lot...) ... to avoid this, I had to set the reader as setReadDataOnly

[PowerKiKi]

@GianinaSalomo thanks for reporting back. Maybe you could create a separate issue, linking to this one, with more details about the warnings ?

[garrettburke]

I had the same issue today trying to load a .xls file ... turns out the file wasn't an .xls, it was actually a tab delimited text file, someone just saved it as .xls

Solved it with the following.

$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Csv');
$reader->setDelimiter("\t");
$spreadsheet = $reader->load($file);