php://output: excel5 files corrupt, 2007 not

Topics: Developer Forum
Jul 31, 2008 at 5:23 PM
I've searched the web, browsed the discussions and issue tracker but couldn't find any solution to my problem, so here it is:

I'm trying to replace egroupware's standard CSV-export with a XLS-export. egroupware handles clicks on the export icon so that it reloads the file and sends the CSV as an attachement. I've replaced the CSV-generation with my PHPExcel code to generate my own spreadsheet. Since the document would either end up as part of the HTML document returned or the XLS(X) would contain the HTML output I now "solved" the problem so that I simply "exit" the php interpretation after I've created the XLSX document:
header("Content-type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
header("Content-disposition: attachment; filename=listview-" . date("Y-m-d").".xlsx");
           
include 'PHPExcel/IOFactory.php';
$writer = PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
$writer->save("php://output");
           
[..]

exit;
This works all fine for Excel2007. I can both save the document on the harddisk (without specifying the headers then) or the php output, and it works all fine, Excel 2007 doesn't complain.

Now I tried to do the same for Excel5/97-2003 and changed the code to the following:
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");;
header("Content-disposition: attachment; filename=listview-" . date("Y-m-d").".xls");
header("Content-Transfer-Encoding: binary ");

include 'PHPExcel/Writer/Excel5.php';
$writer = new PHPExcel_Writer_Excel5($excel);
$writer->save("php://output");

[..]

exit;
I used some code from this forum for the headers, but using different headers doesn't change it: Writing the document with Excel5 always causes errors, Excel2007 complains that the document can't be read and it might either be either on a server which doesn't react anymore or write-protected. Now I thought this had to do with wrong headers etc. and so removed them to simply write the document on the harddisk:
include 'PHPExcel/Writer/Excel5.php';
$writer = new PHPExcel_Writer_Excel5($excel);
$writer->save("test.xls");
Still the same error! I compared the document's content with one that I had previously written and saved from Excel2007 as a 97-2003 document and noticed that my document is missing 1-2KB of (meta?) information at the end of the document and has many differences to the "correct" document.

I'm using a German MS Office 2007 to read/write documents on a German WinXP Pro SP2. Server-software is XAMPP 1.6.7 (Apache 2.2.9, PHP 5.2.6), running egroupware 1.6.pre1 using PHPExcel 1.6.2.

Does anyone have an idea what the problem could be? Or are there any discussions on that which I have missed?

Thanks for your time & help,

Robert
Aug 1, 2008 at 12:38 AM
I had a closer look at the documentation of PHPExcel, chapter 6.4 says that the Excel5 converter is based on PEAR Spreadsheet_Excel_Writer - which explains why I get the same errors for 97-2003 documents with both libraries. Anyways, checked the PEAR Spreadsheet_Excel_Writer bug reports out and found http://pear.php.net/bugs/bug.php?id=6584. The following helped me (quoted from linked site):
[..] I investigated this because I had the same problem. The bug correction is the following: add one line in Workbook.php, after line 499 (in _storeWorkbook): if(count($this->_worksheets)==0)$this->addworksheet(); [..]
Now Excel2007 reports that the document is damaged but can be repaired. After clicking yes you get one more error message but then can read the correct document in as an XLS in Excel2007. Works, but is more of a hack than a proper solution. So still trying to figure out more. If I'll solve it I'll let you know.

Aug 8, 2008 at 12:20 AM
Edited Aug 8, 2008 at 12:23 AM

hey rob,
This is wat I'm  doing. Im using version 1.6.1 :

 

step 1: I include the necessary files at the top of the page like this:

include 'PHPExcel/Writer/Excel2007.php';
include 'PHPExcel/Writer/Excel5.php';

step 2:
Create the object and  the headers:

$objPHPExcel = new PHPExcel();
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");;
header("Content-Disposition: attachment;filename=Report.xls");
header("Content-Transfer-Encoding: binary ");

Step 3:

I fetch data through SQL

Step 4: Now I create an Excel5 object like this and send the output to browser
This is the last thing I do:
I set the active sheet index  (  $objPHPExcel->setActiveSheetIndex(0)  )
and this is the code snippet that follows.
$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
$objWriter->save('php://output');


I hope this helps. Cheers

Aug 17, 2008 at 3:25 PM
Thanks for the hint. But tried exactly that and it doesn't work. Really wondering what I'm doing wrong ..
Developer
Aug 17, 2008 at 9:14 PM
Still the same error! I compared the document's content with one that I had previously written and saved from Excel2007 as a 97-2003 document and noticed that my document is missing 1-2KB of (meta?) information at the end of the document and has many differences to the "correct" document.
Ok, this shows that the problem does not have to do with sending the output directly to the client browser.

Since I haven't been able to re-create your error, can you please try to run a small sample script where you simply just create and save an xls file and see if the problem persists?

$PHPExcel = new PHPExcel();
$ws = $PHPExcel->getActiveSheet();
$ws->getCell('A1')->setValue('hello');
$writer = new PHPExcel_Writer_Excel5($PHPExcel);
$writer->save('test.xls');

Does MS Office report the created test.xls as broken when you try to open it?
Oct 10, 2009 at 7:30 PM

In 1.7.0 version I have the some problem.

My Office 2007 throw error in all XLS files, even in this from Tests folder in Stable 1.7.0 package.

All this files must be recovered before can be read.

On php 5.2.8 this code above generate the error too.

On Excel2007 all is OK.

 

The error message (in Polish)

http://dl.getdropbox.com/u/2388751/Excel/Error1.jpg

Broken File:

http://dl.getdropbox.com/u/2388751/Excel/Error1.xls

Developer
Oct 10, 2009 at 8:00 PM

@DeyV: I think you are perhaps opening the file on Windows 7. When I open the file in Excel 2007 on Windows XP, it is not corrupt.

Check this bug:

http://phpexcel.codeplex.com/WorkItem/View.aspx?WorkItemId=10059

Can you try with latest source code instead of PHPExcel 1.7.0:

http://phpexcel.codeplex.com/SourceControl/ListDownloadableCommits.aspx

Oct 10, 2009 at 8:13 PM
Edited Oct 10, 2009 at 8:19 PM

Yes, Its true. It's Windows7

Th'x ! And in phpExcel on trunk version all its OK!!!