How to generate an Excel document with multiple worksheets from PHP?

Question

I want to generate an MS Excel file from PHP. I know one can do something like this:

header ( "Content-type: application/vnd.ms-excel" );
header ( "Content-Disposition: attachment; filename=foo_bar.xls" );

But it will generate a file with just one Sheet. What I want is generating a file with multiple sheets. How can I do that? Maybe there's a third party library, but I haven't found too much.

Answer 1

Try looking at PHPExcel. This is a simple example that creates an Excel file with two sheets:

<?php
require_once 'PHPExcel.php';
require_once 'PHPExcel/IOFactory.php';

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

// Create a first sheet, representing sales data
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Something');

// Rename sheet
$objPHPExcel->getActiveSheet()->setTitle('Name of Sheet 1');

// Create a new worksheet, after the default sheet
$objPHPExcel->createSheet();

// Add some data to the second sheet, resembling some different data types
$objPHPExcel->setActiveSheetIndex(1);
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'More data');

// Rename 2nd sheet
$objPHPExcel->getActiveSheet()->setTitle('Second sheet');

// Redirect output to a client’s web browser (Excel5)
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="name_of_file.xls"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');

Answer 2

If you mean like have your PHP script create an Excel file, write some stuff to it on any sheet, etc, then offer that up for the client to download, you can just use PHP's built-in COM extension. See: http://us2.php.net/manual/en/class.com.php for all sorts of examples. However, you will need Excel (or a clone like OpenOffice) installed on the server. If you don't, perhaps Mark Baker's answer above will work instead without it.

Answer 3

I ended up having a devil of a time trying to do the same thing until I found this library, which is awesome and produces multiple-worksheet Excel files very easily. Highly recommended.

http://web.burza.hr/blog/php-class-for-exporting-data-in-multiple-worksheets-excel-xml/