AS3 XLS Library Compatibility Problem

[wmwmwm]

I know you are no longer improving Excel5 writer but, i have a problem with Excel5 xls exporting with phpexcel lib.

There is a library named as3xls on googlecode. As3 flash websites and adobe air applications uses this library to read or write xls files. http://code.google.com/p/as3xls/

We are creating xls files with phpexcel library, but it fails to load with as3xls library. We don't have authority to change client's as3 air software.

When we open our xls with Libre Office and save file as office 97 file again; then it works beautifully.

Thank you.

[wmwmwm]

Any help appreciated.

[redchair123]

@wmwmwm can you post a sample file that you generated from phpexcel?

[wmwmwm]

@Niggler thank you for your response.

Also we are getting error messages when we try to validate excel file with ms excel validator.

A sample file created by PhpExcel (Does not work with as3excel lib.) http://kodz.net/demo/share/sample_data_phpexcel.xls

The same sample file saved as with libre office (Works with as3excel lib.) http://kodz.net/demo/share/sample_data_libreoffice.xls

I am using following code:

    $xl = array();
    $xl[] = array('A' => 'string etc..', 'B' => 'string etc'); // Format example.
    // Excel files that i posted here having different data than this code example.

    $filename = 'file.xls';

    $fileType = 'Excel5';

    $objPHPExcel = new PHPExcel();

    $objPHPExcel->setActiveSheetIndex(0)->setSelectedCell('A1')->setTitle('Sayfa1');

    $objPHPExcel->getActiveSheet()->fromArray($xl, null, 'A1');

    $objPHPExcel->getProperties()->setCreator("Kodz - Turbolist Opencart Modulu")
            ->setLastModifiedBy("Kodz - Turbolist Opencart Modulu")
            ->setTitle("Kodz - Turbolist Opencart Modulu")
            ->setSubject("Kodz - Turbolist Opencart Modulu")
            ->setDescription("Kodz - Turbolist Opencart Modulu tarafindan olusturulmustur.")
            ->setKeywords("PHPExcel php opencart gittigidiyor kodz")
            ->setCategory("Gittigidiyor import dosyasi");

    // Redirect output to a client’s web browser (Excel5)
    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment;filename="' . $filename . '"');
    header('Cache-Control: max-age=0');

    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, $fileType);
    $objWriter->save('php://output');
    exit;

Thank you.

[SheetJSDev]

@wmwmwm @Niggler we've received similar reports with files generated by this library. It's hard to say who is at fault since PHPExcel exploits nonconforming behavior that excel permits but technically is wrong. On the one hand, PHPExcel probably should be fixed, but on the other hand the library that reads files should also be fixed.

Basically, the BIFF5/BIFF8 excel files are really CFB containers where each file is a linked list of data blocks (each block contains a pointer to the next block). I haven't looked into how PHPExcel decides to write the blocks, but it appears that there are chunks "before the file" pointing to the file. The conformant way of deciding the blocks for the files is to start at the directory sector, follow the chunks, then start at the next free block and proceed. However, in the case of blocks that appear logically before the start of the file, that process is not valid. In this case, the file actually starts at block 3 but the chain logically starts at block 1 (so the maximal chain in the container is 1 -> 2 -> 3 -> 4 -> ... -> 166 -> 167 -> EOF but the directory claims that the file is 3 -> 4 -> 5 -> ... -> 167 -> EOF). To fix the writer, the block should start at the logical beginning of the file rather than somewhere in the middle.

Technically this is not a valid file (for example, XLRD will fail), but since Excel parses it one could argue that the parser needs to be fixed.

@sjmachin @cjw296 @Niggler I think this is also related to https://github.com/python-excel/xlrd/issues/66 . runxlrd.py throws the error CompDocError: Workbook corruption: seen[3] == 4 which appears to be the same class of problem as the aforementioned issue. XLRD probably should rebuild the file structure in this case.

[cjw296]

xlrd is highly unlikely to be changed to deal with this bug in PHPExcel, I'd suggest fixing the bug at its source...

[SheetJSDev]

@cjw296 I'd agree if the files in question could not be opened, but if Excel opens the file without breaking a sweat, it's hard to say if PHPExcel is truly doing something wrong or if it is exploiting a corner case that a competent parser should handle.

[cjw296]

You're certainly entitled to that opinion, I refer you to @sjmachin's comments on python-excel/xlrd#66.