PHPExcel and CakePHP

Topics: Developer Forum, User Forum
Apr 14, 2009 at 10:09 PM
Edited Apr 14, 2009 at 10:11 PM
So it seems that CakePHP causes problems when trying to use PHPExcel.  If I run the following function outside of cakePHP it works fine.  The instant i place it in cake, when excel trys to open the file it says "Excel cannot open the file'MyDB.xlsx' because the file format or file extension is not valid.  Verify the file has not been corrupted and that the extension matches the format of the file."  Now with the one not living inside of cake I'm able to unzip the file and see all the xml files inside.  The one that comes from cake has a corrupt zip file. 

My question, does anyone know how to debug that?  I know it's not my php install's fault because the script works outside of cakePHP.  Any feedback would be greatly appreaciated.

<pre>

                require_once 'PHPExcel.php';

                /** PHPExcel_Cell_AdvancedValueBinder */
                require_once 'PHPExcel/Cell/AdvancedValueBinder.php';

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

                // Set value binder
                PHPExcel_Cell::setValueBinder( new PHPExcel_Cell_AdvancedValueBinder() );

                // Create new PHPExcel object
                $objPHPExcel = new PHPExcel();
                $objPHPExcel->getProperties()->setTitle("My DB Export");
                $objPHPExcel->getProperties()->setSubject("My DB Export");
                $objPHPExcel->getProperties()->setKeywords("My DB office 2007 openxml php");

                // Add some data
                $objPHPExcel->setActiveSheetIndex(0);

                $hfer = '&C&HExported from My DB Unclassified/FOUO';
                $objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddHeader($hfer);
                $objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddFooter($hfer);

                $objPHPExcel->getActiveSheet()->setCellValue('A1','MaitrePylos');

                // redirect output to client browser

                header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
                header('Content-Disposition: attachment;filename="MyDB.xlsx"');
                header('Cache-Control: max-age=0');

                $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
                $objWriter->save('php://output');
</pre>

Apr 14, 2009 at 11:00 PM
I didn't implement in my project, but the following piece of code seems to work:

<?php
//the constant VENDORS should be the path we want
//but cake was setting it incorrectly
$vendorPath = APP . 'vendors'. DS;
ini_set('include_path', ini_get('include_path') . PATH_SEPARATOR . $vendorPath);

App::import('Vendor', 'phpexcel', array('file' => 'PHPExcel.php'));
App::import('Vendor', 'phpexcelwriter', array('file' => 'PHPExcel'.DS.'Writer'.DS.'Excel2007.php')); // loads PHPExcel/Writer/Excel2007.php

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

/* 
 * Your code goes here
 */
?>

Hope this helps
Apr 14, 2009 at 11:33 PM
Don't get me wrong, the code runs just fine, but the file it outputs is a corrupt zip file.
Apr 15, 2009 at 1:57 AM
did some more investigating.  The file is created fine, but when it gets sent to the browser it gets truncated.  So it's either a timeout issue, or a buffer issue..
Aug 27, 2009 at 7:03 AM

Hi, analogrithems

Not sure you have solved the problem or not. After a little trial-and-error, I found the problem is the header() part. After adding the following, it seemed to work ok.

header('Content-Length: ' . filesize('filepath/filename'));

Good luck,

Tony

 

Apr 22, 2010 at 2:57 PM

I just wanted to state that I have tried to implement PHPExcel in CakePHP and am having the exact same issue.  There is a CakePHP helper that uses PHPExcel so I know it can work, but I have no idea why the author's installation works and not mine.  I am running the same version of CakePHP - 1.2.4.  Any suggestions?

Apr 22, 2010 at 6:23 PM

Alright this was my solution:

There was a space after a closing ?> tag in one of my component files that was adding a space to the beginning of my view.  This space was throwing off the output and corrupting the file.

Feb 9, 2011 at 10:26 AM

bbdanzig, could you explain what spaces in what component files ?? I seem to be facing the same issue.

Mar 21, 2011 at 8:52 PM

Thank tou bbdanzig, I had the same trouble.

If I included/required a specific file (that contained only a class) the file had problems. If I saved the file in Excel5 format it would be corrupt when opened and in 2007 format it wouldn't open at all.

After reading some other ppl having the same problem, I opened the 2007 version in Notepad... to my surprise the beginning of the file had 3 spaces before any content. Upon deleting them, the file was OK.

So I started too look in my class file for the spaces but they were nowhere to be found. It was only until I read your last reply that I realised to look after the ?> . It would seem that they were taken as html and included in the xls.

So... thank you again :)