No worksheets in loaded file

[s21825]

I'm not really sure how to explain this without being able to attach the offending file but I'll try.

I'm loading a spreadsheet with the following code:

// ...
$type = PHPExcel_IOFactory::identify($path);
$objReader = PHPExcel_IOFactory::createReader($type);
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load($path);
$objPHPExcel->setActiveSheetIndex(1); // <-- throws a 'Active sheet index is out of bounds'
// ...

The file has a .xlsx extension but it gets identified as 'Excel5' and when the file gets loaded, it has no worksheets in it (should have 8). When I try to load it as 'Excel2007' I get a bunch of warnings/notices and it fails. Unfortunately I don't know the origin of the file, but I can load it in OpenOffice/Excel without any issues.

Is there a recommended way to share the file with someone who might be able to look at this?

[MarkBaker]

If you do a "Save As" in OpenOffice or in MS Excel, what format does it show as the selected default in the "Save as Type" box

[s21825]

In OpenOffice is says "Microsoft Excel 2007/2010 XML Format" is the current type. I'll have to check what Excel says when I get back to my Windows box.

[s21825]

I've just checked on Excel 2010 and the default save as type is "Excel Workbook" for the file.

[s21825]

In case it helps, here are the warnings etc that come up when I try to import as Excel2007 directly instead of using PHPExcel_IOFactory::identify() to determine the type:

Warning (2): ZipArchive::getFromName(): Invalid or unitialized Zip object [APP/Vendor/PHPExcel/Classes/PHPExcel/Reader/Excel2007.php, line 428] Warning (2): ZipArchive::getFromName() [http://php.net/ziparchive.getfromname]: Invalid or unitialized Zip object [APP/Vendor/PHPExcel/Classes/PHPExcel/Reader/Excel2007.php, line 431] Notice (8): Trying to get property of non-object [APP/Vendor/PHPExcel/Classes/PHPExcel/Reader/Excel2007.php, line 502] Warning (2): Invalid argument supplied for foreach() [APP/Vendor/PHPExcel/Classes/PHPExcel/Reader/Excel2007.php, line 502] Warning (2): ZipArchive::getFromName() [http://php.net/ziparchive.getfromname]: Invalid or unitialized Zip object [APP/Vendor/PHPExcel/Classes/PHPExcel/Reader/Excel2007.php, line 428] Warning (2): ZipArchive::getFromName() [http://php.net/ziparchive.getfromname]: Invalid or unitialized Zip object [APP/Vendor/PHPExcel/Classes/PHPExcel/Reader/Excel2007.php, line 431] Notice (8): Trying to get property of non-object [APP/Vendor/PHPExcel/Classes/PHPExcel/Reader/Excel2007.php, line 539] Warning (2): Invalid argument supplied for foreach() [APP/Vendor/PHPExcel/Classes/PHPExcel/Reader/Excel2007.php, line 539] Warning (2): ZipArchive::close() [http://php.net/ziparchive.close]: Invalid or unitialized Zip object [APP/Vendor/PHPExcel/Classes/PHPExcel/Reader/Excel2007.php, line 1839]

[MarkBaker]

The file can be shared via the File Uploads "issue" on the codeplex site: https://phpexcel.codeplex.com/workitem/10749

[s21825]

Thanks Mark. Here is the link:

https://www.codeplex.com/Download/AttachmentDownload.ashx?ProjectName=phpexcel&WorkItemId=10749&FileAttachmentId=7040

I really appreciate your help with this!

[MarkBaker]

Quick scan of the file: it's actually an xlsb format rather than an xlsx format. Unlike xlsx, which is a zipped collection of xml files; xlsb is a binary format, and not currently supported by PHPExcel. We probably should support it, and I do have the format documentation available, but haven't ever looked through it to see how simple or complex it actually is.

[s21825]

Thanks Mark! That makes sense.

I understand adding support for a whole new format may be a large undertaking. Do you have any thoughts on how someone could detect this format with PHP and then take appropriate action?

It would be awesome if the PHPExcel_IOFactory::identify() method could detect that format and maybe PHPExcel_IOFactory::createReader() could throw an "Unsupported type" exception or something if someone tries to create a reader for that format.

Could you direct me to the documentation of that binary file type?

[SheetJSDev]

@MarkBaker xlsb is a zipped collection of files, some of which are binary (but some elements, as specified by OPC, are still xml). It shouldn't be too hard to translate most of the xml logic (and rip the rest from xls).

@s21825 [MS-XLSB] is the spec you are looking for. It's a much easier read if you go through [MS-XLS] and ECMA-376 first, as it ends up drawing concepts from both: for example, formulae are represented as Ptgs like in XLS but the actual worksheet elements seem to match XLSX more closely

[savdeep]

@MarkBaker @s21825 @SheetJSDev , hey, may i know that did we added support for xlsb in phpexcel?

[px751]

up, any ETA for phpExcel supporting XLSB? :+1: