Most effective way to create Excel files

Question

Looking for a PHP solution to create Excel files on the fly (Think dynamic reporting).

Now I have seen PHP Excel and I know I can create a CSV file but are these by best options?

I running this script on a Linux system using PHP Excel but it doesn't set all the options

$objPHPExcel->getProperties()->setCreator("Phill");
$objPHPExcel->getProperties()->setLastModifiedBy("Phill");
$objPHPExcel->getProperties()->setTitle("Office 2007 XLSX Test Document");
$objPHPExcel->getProperties()->setSubject("Office 2007 XLSX Test Document");
$objPHPExcel->getProperties()->setDescription("Test document for Office 2007 XLSX");

Also if I give the extension .xls instead of the .xlsx it throws a invalid file and doesn't open. (NOTE: I'm using Open Office to view the generated excel sheet)

Wanted to know if there are any other/better solutions out there?

EDIT:

How I save the file

$file = '/path/to/777/dir/file.xlsx'; // not viewable by public
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$objWriter->save($file);

Solution

There are alternatives to PHPExcel, as listed here

But before you dismiss PHPExcel out of hand, I'd like to know why it isn't working. This is something that normally works without any problems; but your code snippet doesn't show anything about saving the file. How are you saving the file: which writer are you using?

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('/path/to/777/dir/file.xls');

EDIT

or

$file = '/path/to/777/dir/file.xls'; // not viewable by public 
$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel); 
$objWriter->save($file); 

The latest version of Open Office will (I believe) read .xlsx files as well, but it does prefer if they have the correct extension.

Note that the Excel5 Writer doesn't support document properties (though Excel2007 does). There is an active work item for this on the PHPExcel issues list.

Comments

[Mark Baker]

@Phil - you're trying to save using the Excel2007 writer, which creates Exzcel2007 (and Excel2010) .xlsx files. You need to use the Excel5 writer to create BIFF8 .xls files.

[Phill Pafford]

I see, you have two different functions for different formats. Makes sense. BTW not dismissing PHP Excel it's just not setting all the properties as in the example files, but is this due to me reading it in Open Office?

[Phill Pafford]

Thanks Mark, this does help out.