CakePHP, meet PHPExcel

I've been working on a project at work that uses the CakePHP framework. I needed to be able to suck data out of the database and write it into an Excel file (not CSV, XLS or XLSX), and was utterly stumped as to how to do this. Not being overly familiar with MVC or object-oriented PHP outside of Cake itself, I wanted to find a ready-made solution that could plug into the existing framework.

I found a few things around the web such as this and this. Both relied on the PHPExcel class and were helpers for CakePHP's views. In both cases, on running the script the end result was the created Excel file being provided as a file download in the browser.

This was not what I wanted - I needed to be able to save the file to disk and then email it. I couldn't find any guidance on how to integrate PHPExcel with Cake in the controller, so I had to make it up as I went along. As it turned out, it was pretty easy and not really anything new, but I guess some people could find this daunting (as I did). So here's a handy guide to creating an Excel file with PHPExcel in your CakePHP controller. I highly recommend downloading and reading the PHPExcel documentation to do the more detailed bits (e.g. formatting) of the spreadsheet.

This assumes you are passing an array to your function in the following format:

$array[0]['header1']['value1']
$array[0]['header2']['value2']
$array[0]['header3']['value3']
$array[1]['header1']['value1']
$array[1]['header2']['value2']
$array[1]['header3']['value3']
etc

This also assumes that you have downloaded PHPExcel and saved it in the 'vendors' directory (typically one level up from the CakePHP 'app' folder).

public function excel_write($data) {
require_once('vendors/excel/PHPExcel.php');

This loads the PHPExcel plugin.  Next, we need to instantiate a new instance of the PHPExcel
$objPHPExcel = new PHPExcel();

In my code, I spent the next few lines setting top-level formatting info and meta-data. e.g.:

$objPHPExcel->getProperties()->setCreator("Joe Bloggs");
$objPHPExcel->getDefaultStyle()->getFont()->setName('Arial');
$objPHPExcel->getDefaultStyle()->getFont()->setSize(8);
$objPHPExcel->getActiveSheet()->getStyle('A1:C1')->getFont()->setBold(true);

This last line sets cells A1, B1 and C1 to bold type as it will be our header row.

Now we can set our headers.

$col = 0;
foreach ($data[0] as $header => $value) {
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col,1, $header);
        $col++;
}

The method setCellValueByColumnAndRow allows you to set the value of a cell using the index of the column and the row number. Column A is 0, B is 1, etc. The arguments of this method are column index, row number, cell value.

Now we can enter our main rows.

$i = 2;
foreach ($data as $datarow) {
$col = 0;
foreach ($datarowas $value) {
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col,$i,$value);
$col++;
}
$i++;
}

We start at row ($i) 2 because row 1 contains the headers we just set. We loop through the top level of the array and within that loop each array element and write them into the appropriate column.

Finally, we instantiate the PHPExcel writer (I have used the Excel 2007 writer) and save the file to the specified location.

$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$objWriter->save('path/to/filename.xls');

} //don't forget to finish your function :-)

Et voila! Your CakePHP & PHPExcel-created file has been written and saved to your network location, sitting there ready for you to act on as you wish. Enjoy!