Could not output file to Browser using Zend Framework

Topics: Developer Forum, User Forum
Mar 17, 2011 at 3:28 PM

I am getting this weird problem where I am not being able to output the excel file to the browser (It say file is not in proper format). I can save excel file but not output to the browser. However if I output PDF file then it works. I am using PHPExcel version 1.7.6 and Zend Framework.

Anybody can give me any insight in this issue please.

$excel = new PHPExcel();
$excel->setActiveSheetIndex(0);
$worksheet = $excel->getActiveSheet();
$worksheet->getCell('A1')->setValue('tet');
$worksheet->getCell('B1')->setValue('tet');

header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
header("Cache-Control: no-store, no-cache, must-revalidate");
header("Cache-Control: post-check=0, pre-check=0", false);
header("Pragma: no-cache");
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="Report.xlsx"');
$objWriter = PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
$objWriter->save('php://output'); //THIS DOES NOT WORK WHY?
//$objWriter->save('test.xlsx');  THIS WORKS
$excel->disconnectWorksheets();
unset($excel);

Mar 17, 2011 at 3:40 PM
Edited Mar 17, 2011 at 3:41 PM

Perhaps Zend uses some kind of output buffering?

 

try putting

 

ob_end_clean();

before the call to save(); That should get rid of any output buffering ;)

 

Moreover, you don;t have to send any headers, PHPExcel takes care of that for you, so simply calling save() should do the trick.

Mar 17, 2011 at 3:51 PM

Thank you very much for your reply. Unfortunately the solution that you have provided did not work. Here is my updated code that I tried as per your suggestion. I even tried without header like you have suggested still no luck.

public function test12Action()
{
    $this->_helper->layout->disableLayout();
    $this->_helper->viewRenderer->setNoRender();
    $excel = new PHPExcel();
    $excel->setActiveSheetIndex(0);
    $worksheet = $excel->getActiveSheet();
    $worksheet->getCell('A1')->setValue('tet');
    $worksheet->getCell('B1')->setValue('tet');

    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="myfile.xlsx"');
    header('Cache-Control: max-age=0');
    $objWriter = PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
    ob_end_clean();
    $objWriter->save('php://output');
    $excel->disconnectWorksheets();
    unset($excel);
}

Mar 17, 2011 at 4:00 PM

Hmm, strange. Is there no output at all? If you don't send any headers, does PHPExcel send the right headers? Did you check the php error log? Are you sure the script is finished and not still running? Have you tried adding some debug info after the save() call?

Mar 17, 2011 at 5:05 PM

It sends an output I can even save the excel file. But when I try to open it

Excel cannot open the file myfile.xlsx because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file.

Any idea why this happening.

Mar 17, 2011 at 5:07 PM

not really :(, but if the file is not empty, the answer may be hidden in the file. Could you try opening the file in an plain text editor, perhaps there is some superfluous whitespace in there.

Mar 17, 2011 at 5:16 PM

This is the top few line of output.

 PK    ^q>G�D�X  �     [Content_Types].xml��MN�0���"�%nY ��vAa	�(0����ؖg�w{&i�@�nbE�{��y��d۸l
	m�����X�(���)��7:�B�'};���Ƶ��G����PK    ^q>���J       xl/theme/theme1.xml�Y[s�8~�_��{k6%��L ��i3	�N��Ud�#����=���X��Mgw��,�;������/)'wT*&���=�9�f��X������C��,.2:v�T9�g��S�Д$��)��D���uU�۠���fxo%d
�2v#	��6�n���)��!����,���߄�ɶ9hn$�~��U[��(u~����|s�G߹x�Ҁ!�v���:x�УG6�����j-�Cüu��ʁ��Fv��V�k�'�����#��m¾G�a���	y�J�E�A���ܔ�j�� O�8e��w(�6�D˴E��*,��>y�э��K��&j8�� �B�'4�^gx�)���+�Oh9�qZ��~��U�+��nB�HȢ C��B|�@�1:6�X7[%��,]E��ܸ���� Ā�/!D� iygg:��{x<�k������S��(|iSȑ�)�ؚ@��	g� PK    ^q>�KR"x   �   #   xl/worksheets/_rels/sheet1.xml.relsM�1! {_A��@c�q�� ��p+a!,1�{)-'��y����$�p�(bW�����~ݟAIG�0&_X��|��}4b5&,B�����@e*�x�gi���uE�BO�h�I����PK      ^q>G�D�X  �                   [Content_Types].xmlPK      ^q>�78�   K               �  _rels/.relsPK      ^q>�$�V�   �               �  xl/_rels/workbook.xml.relsPK      ^q>���  ;               �  docProps/app.xmlPK      ^q>۲a[q                 p  docProps/core.xmlPK      ^q>���J                   xl/theme/theme1.xmlPK      ^q>��ٌ   �                �  xl/sharedStrings.xmlPK      ^q>Q�9C�  �  
             I
  xl/styles.xmlPK      ^q>)� -�  �               r  xl/workbook.xmlPK      ^q>�l�  �               6  xl/worksheets/sheet1.xmlPK      ^q>�KR"x   �   #               xl/worksheets/_rels/sheet1.xml.relsPK      �  �    
I know it is a lot of non-readable characters.

Mar 17, 2011 at 5:23 PM

hmm, that doesn't really make any sense to me :) Can you open the file with zip? Or perhaps post the zip somewhere where I could download it, to have a closer look at it? Since the file saved to disk is ok, I still think the file contains some extra characters that cause it to be none readable. If you save to disk (instead of stadnard out), is there any visible output that is sent to the browser?

Coordinator
Mar 17, 2011 at 5:24 PM

Is that a leading space character before the initial "PK"?

Mar 17, 2011 at 5:32 PM

nice one, and now the quest starts for that one include file with a space before or after the php tag ;)

Mar 17, 2011 at 6:25 PM

@MarkBaker you are right there is one space in-front. But how come

$objWriter->save('php://output'); //Does not work
//$objWriter->save('test.xlsx'); //This does work.
@Borft unfortunately I cannot put file in public IP however I can email it to you if you send me your email address. Please check the notification that I sent you.

Mar 17, 2011 at 7:20 PM

I discovered one thing if I do this then it works.

public function test12Action()
{
    $response = $this->_response;
    $this->_helper->layout->disableLayout();
    $this->_helper->viewRenderer->setNoRender();
       		
    $excel = new PHPExcel();
    $excel->setActiveSheetIndex(0);
    $worksheet = $excel->getActiveSheet();
    $worksheet->getCell('A1')->setValue('tet');
    $worksheet->getCell('B1')->setValue('tet');

    $response->setHeader('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',true);
    $response->setHeader('Content-Disposition: attachment;filename="myfile.xlsx"',true);
    $response->setHeader('Cache-Control: max-age=0',true);
    ob_end_clean();
    $objWriter = PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
    //$objWriter->save('php://output');
    $objWriter->save('test.xlsx');
    ob_end_clean();
    $bits = @file_get_contents('test.xlsx');
    $response->setBody($bits);
    $excel->disconnectWorksheets();
    unset($excel);
}
Any reason why?

Mar 17, 2011 at 7:34 PM

Finally after working for long hours I solved this issue. Just in case if anybody is curious about the solution here is what I did.

    public function test12Action()
    {
        $response = $this->_response;
        
        $this->_helper->layout->disableLayout();
        $this->_helper->viewRenderer->setNoRender();
               
        $excel = new PHPExcel();
        $excel->setActiveSheetIndex(0);
        $worksheet = $excel->getActiveSheet();
        $worksheet->getCell('A1')->setValue('tet');
        $worksheet->getCell('B1')->setValue('tet');
        
        ob_end_clean();
        
        header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
        header("Cache-Control: no-store, no-cache, must-revalidate");
        header("Cache-Control: post-check=0, pre-check=0", false);
        header("Pragma: no-cache");
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="Report.xlsx"');

        $objWriter = PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
        ob_end_clean();
        
        $objWriter->save('php://output');
        $excel->disconnectWorksheets();
        unset($excel);
        
    }
My special thanks goes to borft and Mark Baker.

Jun 22, 2011 at 6:22 PM

 

nicholasnet
Thanks a lot, helped me a lot your code, develop Zend and not one day already trying tofigure out why not get a file excel. And after searching found your code, you will be extremely helpful thank you!

Oct 24, 2011 at 3:24 AM

@nicholasnet

Thanks for your solution. It works, however, upon opening the Excel file it says part of the file has been corrupted and asks if I would like a validation/repair. After this repair session, the file shows correctly. Any suggestions? Thanks!

Nov 13, 2011 at 1:06 PM
Edited Nov 13, 2011 at 1:07 PM

There is a known problem with certain versions of Zip .. from the manual:

 

Excel 2007 cannot open the file generated by PHPExcel_Writer_2007 on Windows

Excel found unreadable content in '*.xlsx'. Do you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes.”

Some versions of the php_zip extension on Windows contain an error when creating ZIP files. The version that can be found on http://snaps.php.net/win32/php5.2-win32-latest.zip should work at all times.

Alternatively, upgrading to at least PHP 5.2.9 should solve the problem.

If you can’t locate a clean copy of ZipArchive, then you can use the PCLZip library as an alternative when writing Excel2007 files, as described above.

 

Finally, it is worth remembering that Windows, bless it, has a nasty habit of ignoring the mime type you send and looking at the file name to try and work out what the file is supposed to be.

Why? who knows, but remember that when sending Excel 5 and Excel 2007 files not only change the mime type, but change the file name also ...

eg:

 

 

		 private function outputExcel5(){
		 	$objWriter = new PHPExcel_Writer_Excel5($this->xls);

		 	ob_end_clean();
		 	header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
		 	header("Cache-Control: no-store, no-cache, must-revalidate");
		 	header("Cache-Control: post-check=0, pre-check=0", false);
		 	header("Pragma: no-cache");
		 	header('Content-Type: application/vnd.ms-excel');
		 	header('Content-Disposition: attachment;filename="Report.xls"');

		 	$objWriter->save('php://output');
		 }

		 private function outputExcel2007(){
		 	$objWriter = new PHPExcel_Writer_Excel2007($this->xls);

		 	ob_end_clean();
		 	header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
		 	header("Cache-Control: no-store, no-cache, must-revalidate");
		 	header("Cache-Control: post-check=0, pre-check=0", false);
		 	header("Pragma: no-cache");
		 	header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
		 	header('Content-Disposition: attachment;filename="Report.xlsx"');

		 	$objWriter->save('php://output');
		 }
Mar 6, 2012 at 10:18 PM

I'm having the same issues.  I tried to use the code above, just to get anything working.  My version:

			$excel = new PHPExcel();
			$excel->setActiveSheetIndex(0);
			$worksheet = $excel->getActiveSheet();
			$worksheet->getCell('A1')->setValue('tet');
			$worksheet->getCell('B1')->setValue('tet');
			
			ob_end_clean();
			
			header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
			header("Cache-Control: no-store, no-cache, must-revalidate");
			header("Cache-Control: post-check=0, pre-check=0", false);
			header("Pragma: no-cache");
			header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
			header('Content-Disposition: attachment;filename="Report.xlsx"');
	
			$objWriter = PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
			ob_end_clean();
			
			$objWriter->save('php://output');
			$excel->disconnectWorksheets();
			unset($excel);
For some reason having the two end cleans works better than removing one.  With them both in I at least get Excel trying to recover it.  Removing either one results in file format or file extension error and no open of the spreadsheet.

PHP 5.3.3 on Red Hat.  Using Firephp.

            $excel = new PHPExcel();
            $excel->setActiveSheetIndex(0);
            $worksheet = $excel->getActiveSheet();
            $worksheet->getCell('A1')->setValue('tet');
            $worksheet->getCell('B1')->setValue('tet');
           
            ob_end_clean();
           
            header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
            header("Cache-Control: no-store, no-cache, must-revalidate");
            header("Cache-Control: post-check=0, pre-check=0", false);
            header("Pragma: no-cache");
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            header('Content-Disposition: attachment;filename="Report.xlsx"');
   
            $objWriter = PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
            ob_end_clean();
           
            $objWriter->save('php://output');
            $excel->disconnectWorksheets();
            unset($excel);
May 15, 2012 at 7:45 PM

ob_end_clean(); works great for me. 

Feb 18, 2015 at 12:37 AM
If useful for someone my problem was my editor software (PSPad). In program settings, uncheck the Ident. bytes in UTF-8 coding. And everything works for me.
Aug 28, 2015 at 8:15 PM
I am having the same problem. I am able to save the file in csv and pdf format. The users can download the pdf file no problems, but the xls file is not saving or downloading correctly. Parts of the data is missing from the file and the "We found a problem with some content..." message pops up.

I have this in one file:
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

$objWriter->save(str_replace('.csv', '.xls', $inputFileName));


$objPHPExcel->getActiveSheet()->getStyle('A1:'.$col)->applyFromArray($styleArray);
unset($styleArray);
$objPHPExcel->getActiveSheet()->setShowGridlines(FALSE);

$objWriterpdf = PHPExcel_IOFactory::createWriter($objPHPExcel, 'PDF');

//You need to include and print to PDF the entire worksheets contained in the workbook

$objWriterpdf->writeAllSheets();

//You need to assign a filename to the PDF file (write.pdf for example)
$objWriterpdf->save(str_replace('.csv', '.pdf', $inputFileName));
and this in another:
$filedate =date('mdY');
$filename='dash'.$_GET["site"].'-'.$filedate.'.xls';
$rows=$_GET["rows"];
if ($_GET["site"]>'')
   parsing_csv('c:/webroot/dashExport/dash'.$_GET["site"].'-', 'I'.$rows,$_GET['site'] ); 
header('Content-disposition: attachment; filename=dash'.$_GET["site"].'-'.$filedate.'.xls');
header('Content-type: application/vnd.ms-excel');
readfile($filename);
The download is executed when I call in the main file using this line:
<input type="button" value="" title="Export to Excel" class="button_add" onClick="window.location.href='../dashExport/downloadExcelone.php?site=Global&rows=<?php echo $rowcnt;?>&consortium=<?php echo $consortium;?> &research=<?php echo $research;?>  '"  /> 
Can anyone help me?