Add support for reading protected (RC4 encrypted) .xls files

[trvrnrth]

These changes make it possible to read in .xls files with protection enabled.

The decryption functions and objects implemented here are are based on the source of Spreadsheet-ParseExcel:
http://search.cpan.org/~jmcnamara/Spreadsheet-ParseExcel/

[MarkBaker]

Thanks for this Trevor... I'll look at what I can do to get it working on 32-bit and Windows boxes, but it's a feature that a lot of people have been asking for

[trvrnrth]

Hi Mark,

Not sure if you made any progress but I had a bit of time this evening so had a poke around and tracked the problem down to the rotate in the Excel MD5 lib. This stems from the lower max int size on 32 bit, and the bit shift was obviously not working as expected when given floats instead. Oddly, the remaining bit operations still seem to work just fine but I'll not complain about that.

I've also done some general clean up to make the whole thing a bit more maintainable. As a point of interest I adapted the code from http://stackoverflow.com/a/1699199 originally although by this point the info there probably isn't of much use. It's annoying that the built in md5/hash functions don't expose the context as that would have saved a lot of hassle but such as life.

I should note that I have only tested this on php-5.5.6-nts-Win32-VC11-x86/x64 on a 64 bit windows 7 machine as that's what happens to be on my lap. I'm hopeful that this does indeed work for other versions and linux 32 bit, and that I've not knackered it up for linux 64 bit, but I'll admit to not actually having checked. Let me know if I have and I can take another look.

Anyway, enough talk, the commit is at https://github.com/trvrnrth/PHPExcel/commit/ee3ff2ebbdf6de53f2c0b31a8e409d81b4f95939

Cheers,
Trevor

[MarkBaker]

On 15/11/2013 22:56, Trevor North wrote:

Hi Mark,

Not sure if you made any progress but I had a bit of time this evening
so had a poke around and tracked the problem down to the rotate in the
Excel MD5 lib. This stems from the lower max int size on 32 bit, and
the bit shift was obviously not working as expected when given floats
instead. Oddly, the remaining bit operations still seem to work just
fine but I'll not complain about that.

I've also done some general clean up to make the whole thing a bit
more maintainable. As a point of interest I adapted the code from
http://stackoverflow.com/a/1699199 originally although by this point
the info there probably isn't of much use. It's annoying that the
built in md5/hash functions don't expose the context as that would
have saved a lot of hassle but such as life.

I should note that I have only tested this on
php-5.5.6-nts-Win32-VC11-x86/x64 on a 64 bit windows 7 machine as
that's what happens to be on my lap. I'm hopeful that this does indeed
work for other versions and linux 32 bit, and that I've not knackered
it up for linux 64 bit, but I'll admit to not actually having checked.
Let me know if I have and I can take another look.

Cheers,
Trevor

—
Reply to this email directly or view it on GitHub
#261 (comment).

Sorry I haven't got back to you sooner, but it's been a hectic couple of
weeks in work with a major "go live": but I'm planning to take another
look tonight, and this wekend.

I'd already noted that the problem stemmed from that addition leading to
an implicit float conversion, but hadn't worked out an alternative
approach; but I'll do some additional testing now on both Windows and
32-bit Linux. Thanks for the work you've done already; and hopefully
this should be the last major new change to the code before I start
prepping for a new release - I really do want to get this feature in,
because I know that so many people will appreciate it.

Mark Baker

[trvrnrth]

I hear you on the busy. I've just found time to get back to the project that we need this for and found that I had indeed broken things for 64 bit. On the plus side the fix is straightforward and at https://github.com/trvrnrth/PHPExcel/commit/ba3ac2075b75419986744dfa629d836cec05beb7. With a bit of luck that'll be that for the reader.

[goosehub]

I found this pull request through https://stackoverflow.com/questions/19655690/how-to-read-password-protected-excel-files-using-phpexcel. I'm not clear on the result. I see this referenced commit, c7a6431, appears to exist in master, but I'm not sure how to use the repo to pass a password to a password protected xls file.

In short, does this repo support opening password protected xls files, either in Master or in this branch? If so, where is an example, documentation on this feature. If not, how should I start on forking this repo so I can build the feature and possibly make a pull request back to the repo.

[trvrnrth]

The functionality did make it into master yes. I never implemented the ability to use anything other than the default password for "read only" protected sheets (VelvetSweatshop) but I imagine it'd be possible to pass a different password in via the reader as an option or something and then make use of that.

[goosehub]

@trvrnrth Oh good. I'll have no problem changing the password in Excel5.php, as every file I'll receive will have the same password.

Could you provide documentation or an example of how to open password protected files? This is what I have now, even after changing the password.

$excel_object = PHPExcel_IOFactory::load($xlsx_filepath);
$sheet_data = $excel_object->getActiveSheet()->toArray(null, false, true, true);

I'm getting

Unexpected file pass record length

[trvrnrth]

@goosehub You know, I honestly couldn't tell you anymore it was so long ago now. The project I used the library for is no longer maintained by me. What I can tell you is that at the time this worked well for the read-only excel 5 files we had to deal with but we never had a use case for handling those with an actual password. I borrowed the approach from the perl Spreadsheet-ParseExcel lib so it might be worth you seeing if that can read your file first.

[goosehub]

Yeah, it's not even getting to the $this->verifyPassword('VelvetSweatshop', ... (which I've updated to my password) line because it's failing at

    $length = self::getInt2d($this->data, $this->pos + 2);

    if ($length != 54) {
        throw new PHPExcel_Reader_Exception('Unexpected file pass record length');
    }

And if I remove that exception, it fails at the verifyPassword line and throws this exception

    throw new PHPExcel_Reader_Exception('Decryption password incorrect');

And removing that exception just causes PHP errors.

I'm guessing getting it to work will be non trivial. I'm waiting for a potential other option to get the data we need, but if that falls through, I'll probably have to make this work somehow.

[PowerKiKi]

If you happen to create a PR for that, please target PhpSpreadsheet, instead of PHPExcel.

[goosehub]

@PowerKiKi so I fork PhpSpreadsheet and build the functionality into that? I assume both repos are able to parse xls files?

[PowerKiKi]

Yep, it would be best. PhpSpreadsheet is PHPExcel renamed, it's mostly the same code base, so same features. You should also cover you work with unit tests, so that the feature doesn't break again in the future.