Generate Excel spreadsheets in PHP

DOWNLOAD THE SOURCE CODE

For better or for worse, the Microsoft Office file formats still dominate when it comes to distributing documents or spreadsheets. Fortunately for PHP developers looking to work with Excel spreadsheets there is a free library called PHPExcel that allows Excel spreadsheets, from a number of different version of Excel including version 5, 2003 and 2007, to be created, modified and saved with only a few lines of code.

This tutorial will show you how to create an Excel spreadsheet in PHP, that is then sent as a download to the end user.

Download a copy of PHPExcel from here. Extract the archive and add the folder location to the PHP.INI include_path option.

For this demo we need to reference two files from the PHPExcel library: PHPExcel.php and IOFactory.php.

require_once 'PHPExcel.php';

require_once '/PHPExcel/IOFactory.php';

The spreadsheet itself is represented by the PHPExcel class. Here we create a new instance, which essentially gives us an in-memory spreadsheet.

$objPHPExcel = new PHPExcel();

He we are setting some of the properties of the spreadsheet. You can see the screenshot below to see how these properties show up in Excel. Notice how each of the functions returns a reference to the PHPExcel_DocumentProperties object that they are modifying, allowing you to call the functions sequentially.

$objPHPExcel->getProperties()

->setCreator("Matthew Casperson")

->setLastModifiedBy("Matthew Casperson")

->setTitle("Office 2007 XLSX Test Document")

->setSubject("Office 2007 XLSX Test Document")

->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.");

Here we set the initial values of some cells. And just like we did with the spreadsheet properties above, the functions can be called sequentially.

$objPHPExcel->setActiveSheetIndex(0)

->setCellValue('A1', 'Hello')

->setCellValue('B2', 'world!')

->setCellValue('C1', 'Hello')

->setCellValue('D2', 'world!');

Because this PHP page will be producing an Excel spreadsheet (as opposed to simply provding a link to one) we need to set the headers to reflect the data that is being sent. Here we set 3 headers:

1. Content-Type, which defines the type of data that is being sent
2. Content-Disposition, which defines the file name
3. Cache-Control, which prevents the browser from saving the file in the cache

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

header('Content-Disposition: attachment;filename="test.xlsx"');

header('Cache-Control: max-age=0');

The final step is to convert the PHPExcel object into an actual Excel spreadsheet file. Here we create a Excel2007 writer, and then save the PHPExcel object to the special filename php://output, which has the same effect as echoing or printing the data.

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');

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

Excel is still one of the best ways to present structured data to users in a format that they can work with, without having to write any code themselves (as would be the case with JSON or XML). The PHPExcel library makes working with the Excel format painless, and it gives developers the option to manipulate Excel data in a modern, object oriented language without the pain of the COM or .NET interface (something anyone who has tried to manage memory allocations with these APIs will appreciate).

Return to the Tutorial Index

Creating PDF documents with PHP

This tutorial steps you through the process of creating PDF documents with PHP.