Write spreadsheet with looping method - Excel found unreadable content

Topics: Developer Forum, Project Management Forum, User Forum
Jul 28, 2009 at 5:00 PM

I'm trying to write data into excel file with looping method, everything seem fine...no errors, able to download the excel file. However, it prompt up a message "Excel found unreadable content in...Do you want to recover the contents for this workbook?...". Here is the code:

error_reporting(E_ALL);

/** Include path **/
set_include_path(get_include_path() . PATH_SEPARATOR . '../Classes/');

/** PHPExcel */
include 'PHPExcel.php';

/** PHPExcel_IOFactory */
include 'PHPExcel/IOFactory.php';

// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

 

for($i=0; $i<20; $i++){
	  $objPHPExcel->getActiveSheet()->setCellValue('A'.($i+4), 'A'.($i+4));
	  $objPHPExcel->getActiveSheet()->setCellValue('B'.($i+4), 'B'.($i+4));
	  $objPHPExcel->getActiveSheet()->setCellValue('C'.($i+4), 'C'.($i+4));
	  $objPHPExcel->getActiveSheet()->setCellValue('D'.($i+4), 'D'.($i+4));
	  $objPHPExcel->getActiveSheet()->setCellValue('E'.($i+4), 'E'.($i+4));
	  $objPHPExcel->getActiveSheet()->setCellValue('F'.($i+4), 'F'.($i+4));
	  $objPHPExcel->getActiveSheet()->setCellValue('G'.($i+4), 'G'.($i+4));
	  $objPHPExcel->getActiveSheet()->setCellValue('H'.($i+4), 'H'.($i+4));
	  $objPHPExcel->getActiveSheet()->setCellValue('I'.($i+4), 'I'.($i+4));
}
$objPHPExcel->getActiveSheet()->setTitle('Simple');
$objPHPExcel->setActiveSheetIndex(0);

header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="test-'.date('dMY').'.xls"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');

Any ideas??

 

 

Developer
Jul 28, 2009 at 6:33 PM

This is usually happening because there is some newlines or spaces before your opening <?php tag of after your closing ?> tag.

There should not be any because this space will be prepended / appended to the file contents. In turn file will be considered corrupt by Microsoft Office Excel when you try to open it.

Jul 29, 2009 at 1:32 AM

I am sure there is no spaces or newlines before or after the <?php and ?> tag. It works perfectly if without the looping and the format set on each cell shown properly.

I just found out it works perfectly on Excel 2007 with the following codes:

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="fuso-'.date('dMY').'.xlsx"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');

Any helps will be greatly appreciated. Thanks.

Developer
Jul 29, 2009 at 7:08 AM

Then I don't know what the problem is. I'm afraid I will have to see the corrupt Excel file. Can you send the corrupt file to me? (erik at phpexcel dot net)

Developer
Jul 29, 2009 at 4:45 PM

[file received]

I just tried to open the xls file in MS Office Excel 2007, and it opened with no problems. Can you try to save it with a different filename? I have observed that if one opens a corrupt file in MS Office Excel then it somehow remembers this, so even after I fix the file, the program still says it is corrupt. Maybe that is what happened.

Jul 29, 2009 at 5:21 PM

Nope, it still the same problem after i renamed the file with different filename...just weired.

The only solution for me right now is to use Excel2007 instead of Excel5, and save it as xls...it works although it prompt up the message "the file you trying to open , test.xls, is in a different format than specified by the file extension. Verify that the file is not corrupted...."

Anyway, thanks for your helps. I really appreciated.

Jul 29, 2009 at 6:35 PM

Hello, i have the same problem.

Developer
Jul 30, 2009 at 6:57 AM

Can you provide some information what platform / program you are opening the xls file on?

Also, check if it is happening with latest source code. Plus, is this happening with all generated xls files, no matter content?

Jul 30, 2009 at 7:54 AM

I'm using MS Office 2007 that running on Windows 7. Xampp 1.7.1 with PHP5.2.9.

Okay, i had downloaded the latest source code 28266 and i do a test run on those sample scripts with Excel5 inside the Tests folder. And here are the result i get:

14excel5.php, corrupt Excel file
27imagesexcel5.php, corrupt Excel file

And, yes this only happen with all generated xls files.

Developer
Jul 30, 2009 at 3:20 PM

>> I'm using MS Office 2007 that running on Windows 7. Xampp 1.7.1 with PHP5.2.9.

I'm beginning to think this is specific to Windows 7. One other user had reported problems with corrupt Excel files by Excel5 writer on Windows 7.

Maarten, do you have access to Windows 7 to test output by Excel5 writer?

Sep 25, 2009 at 7:53 PM

I am using Windows 7 with Office 2003 and my PHP and excel generator script is running on a Debian server. When I download the file in Excel5 format, I receive a corrupt file warning. If I try in 2007 format, my Excel 2003 can convert and display it without issue.

Is the Windows 7 problem specific to generating the file on IIS within Windows 7 or just opening the file on Windows 7? I am gonna run a virtual machine booting to XP and see if the result is the same.

Sep 25, 2009 at 8:04 PM

I can in fact confirm that this error occurs when running Windows 7 (in this case, build 7100) and using Office 2003 (although by reports, 2007 is also affected).

The file can be generated on ANY computer, running either linux or windows as a web server and the outcome will be Excel reporting the file is corrupt JUST BY TRYING TO OPEN IT WHEN RUNNING EXCEL IN WINDOWS 7.

I used the exact same test file on Windows 7 with Office 2003 and Windows XP with Office 2003. The Windows XP version opened the file without any error message. The Windows 7 version displays a message about the file being corrupt.

I wonder if this might have something to do with quirky MIME type settings or maybe a registry / permissions issue? At this point, practically anything that has changed between XP and 7 could be causing it. I wonder if anyone is able to reproduce the problem on a build of 7 closer to the final release version? My version (7100) is the release candidate, rather than the final build.

Developer
Sep 25, 2009 at 9:31 PM

@coolbloke1324:

This is really bad news. Your post has convinced me that there is a problem with xls files generated by PHPExcel_Writer_Excel5. There is also this issue talking about problems when opening the files in Excel on Windows 7:

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

If others can verify this problem I would like to hear. I don't have access to a Windows 7 machine so I can't do any tests which is really annoying me. I must try to find a solution.

Sep 29, 2009 at 1:16 PM

I do not believe this to be a problem with PHPExcel Writer... other users of Windows 7 have also been experiencing this problem with files not generated with this script: http://www.google.com/support/forum/p/affiliatenetwork/thread?tid=2b5983fcbe55ea27&hl=en

Developer
Sep 29, 2009 at 2:20 PM

@coolbloke1324:

Thanks for the link. Can you say if this is happening with all Excel files generated by PHPExcel? Or is it only with some files? Can you for example try to generate an empty file with PHPExcel and see if it can be opened on Windows 7.

Sep 29, 2009 at 3:08 PM

I can indeed. Give me a few minutes and I'll run the tests for you. Would you like to have logmein access to a windows 7 virtual machine? That way you can run all the tests you need?

 

Developer
Sep 29, 2009 at 3:24 PM

>> Would you like to have logmein access to a windows 7 virtual machine?

If possible, this will be a great help! I would like to try to open Excel files in Microsoft Office Excel on Windows 7, making gradual changes to the file in a HEX editor until the error is found.

I will send you a message with my IP address. Thank you for your offer.

Sep 29, 2009 at 3:27 PM

Hey ya,

OK, I'll create a logmein account and load it onto the Windows 7 VM... after that if I can email you the U&P to access the logmein account that will be the best way to proceed.

Could you drop me a message with your email?

Thanks.

Developer
Sep 29, 2009 at 3:30 PM

Great! I sent you PM.

Sep 29, 2009 at 3:56 PM

I'm installing the virtual machine for you now... gonna take about an hour... Will email you when ready!

Developer
Sep 29, 2009 at 10:38 PM

Using the environment Windows 7 / MS Office Excel 2003 environment you set up for me, I can verify the bug.

All files by PHPExcel_Writer_Excel5 are corrupt when opening in Windows 7 so this is a critical issue.

For reference, here are some screen shots:

When trying to open the xls file, the error message says:

"The document is corrupt and cannot be opened. To try and repair it, use the Open and Repair command in the Open dialog (File menu) and select Extract Data when prompted."


After clicking ok, you see the message:

"Errors were detected in '42.xls', but Microsoft Office Excel was able to open the file by making the repairs listed below. Save the file to make these repairs permanent."

Apart from that, everything look ok in the file.

Some tests indicate that the PEAR::OLE package is responsible for the errors. PHPExcel_Writer_Excel5 is based on PEAR::Spreadsheet_Excel_Writer which uses PEAR::OLE, so PEAR::Spreadsheet_Excel_Writer is probably also affected.

Will re-open work item 10059
http://phpexcel.codeplex.com/WorkItem/View.aspx?WorkItemId=10059

Developer
Sep 29, 2009 at 11:21 PM

Should now be fixed. See patch at

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

Or just download source code tomorrow from here:

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

 

Special thanks to @coolbloke1324 for providing the test environment via virtual machine!

 

Jan 13, 2010 at 8:58 AM

Hi koyama,

Is it possible that this issue is still not fixed when generating excels in format Excel2007 ?

We were using a old version of PHPExcel, and I detected that there was a problem with generating excels in Excel5 and Excel2007 on a Windows 7 machine.

I saw this post and upgraded to the latest version, and now I can create my excels in Excel5 but still not in Excel2007. I tried also with the simple example tests and the problem is the same.

My configuration: php 5.2.8, windows 7 build 7600

 

Thx

Developer
Jan 14, 2010 at 12:02 AM
mgx wrote:

Hi koyama,

Is it possible that this issue is still not fixed when generating excels in format Excel2007 ?

We were using a old version of PHPExcel, and I detected that there was a problem with generating excels in Excel5 and Excel2007 on a Windows 7 machine.

I saw this post and upgraded to the latest version, and now I can create my excels in Excel5 but still not in Excel2007. I tried also with the simple example tests and the problem is the same.

My configuration: php 5.2.8, windows 7 build 7600

 

Thx

Your issue is different from the one discussed here.

The problem you are experiencing is because of this:

PHP 5.2.8 on Windows has a bug in php_zip.dll. There is no bug with PHP 5.2.6, PHP 5.2.9 and later work. Users have reported that one can replace php_zip.dll from e.g. PHP 5.2.6 and things should be working.

There is a long thread here:

http://phpexcel.codeplex.com/Thread/View.aspx?ThreadId=42854

Look at posts by tamaskoczka and JLoima. I have not tried myself.

 

Jan 14, 2010 at 9:24 AM

Thx koyama. The solution in the thread works!