I have implemented PHPExcel within my CakePHP application, this is my helper:
<?php
App::uses('AppHelper', 'Helper');
/**
* Helper for working with PHPExcel class.
* PHPExcel has to be in the vendors directory.
*/
class PhpExcelHelper extends AppHelper {
/**
* Instance of PHPExcel class
* @var object
*/
public $xls;
/**
* Pointer to actual row
* @var int
*/
protected $row = 1;
/**
* Internal table params
* @var array
*/
protected $tableParams;
/**
* Constructor
*/
public function __construct(View $view, $settings = array()) {
parent::__construct($view, $settings);
}
/**
* Create new worksheet
*/
public function createWorksheet() {
$this->loadEssentials();
$this->xls = new PHPExcel();
}
/**
* Create new worksheet from existing file
*/
public function loadWorksheet($path) {
$this->loadEssentials();
$this->xls = PHPExcel_IOFactory::load($path);
}
/**
* Set row pointer
*/
public function setRow($to) {
$this->row = (int)$to;
}
/**
* Set default font
*/
public function setDefaultFont($name, $size) {
$this->xls->getDefaultStyle()->getFont()->setName($name);
$this->xls->getDefaultStyle()->getFont()->setSize($size);
}
/**
* Start table
* inserts table header and sets table params
* Possible keys for data:
* label - table heading
* width - "auto" or units
* filter - true to set excel filter for column
* wrap - true to wrap text in column
* Possible keys for params:
* offset - column offset (numeric or text)
* font - font name
* size - font size
* bold - true for bold text
* italic - true for italic text
*
*/
public function addTableHeader($data, $params = array()) {
// offset
if (array_key_exists('offset', $params))
$offset = is_numeric($params['offset']) ? (int)$params['offset'] : PHPExcel_Cell::columnIndexFromString($params['offset']);
// font name
if (array_key_exists('font', $params))
$this->xls->getActiveSheet()->getStyle($this->row)->getFont()->setName($params['font_name']);
// font size
if (array_key_exists('size', $params))
$this->xls->getActiveSheet()->getStyle($this->row)->getFont()->setSize($params['font_size']);
// bold
if (array_key_exists('bold', $params))
$this->xls->getActiveSheet()->getStyle($this->row)->getFont()->setBold($params['bold']);
// italic
if (array_key_exists('italic', $params))
$this->xls->getActiveSheet()->getStyle($this->row)->getFont()->setItalic($params['italic']);
// set internal params that need to be processed after data are inserted
$this->tableParams = array(
'header_row' => $this->row,
'offset' => $offset,
'row_count' => 0,
'auto_width' => array(),
'filter' => array(),
'wrap' => array()
);
foreach ($data as $d) {
// set label
$this->xls->getActiveSheet()->setCellValueByColumnAndRow($offset, $this->row, $d['label']);
// set width
if (array_key_exists('width', $d)) {
if ($d['width'] == 'auto')
$this->tableParams['auto_width'][] = $offset;
else
$this->xls->getActiveSheet()->getColumnDimensionByColumn($offset)->setWidth((float)$d['width']);
}
// filter
if (array_key_exists('filter', $d) && $d['filter'])
$this->tableParams['filter'][] = $offset;
// wrap
if (array_key_exists('wrap', $d) && $d['wrap'])
$this->tableParams['wrap'][] = $offset;
$offset++;
}
$this->row++;
}
/**
* Write array of data to actual row
*/
public function addTableRow($data) {
$offset = $this->tableParams['offset'];
foreach ($data as $d) {
$this->xls->getActiveSheet()->setCellValueByColumnAndRow($offset++, $this->row, $d);
}
$this->row++;
$this->tableParams['row_count']++;
}
/**
* End table
* sets params and styles that required data to be inserted
*/
public function addTableFooter() {
// auto width
foreach ($this->tableParams['auto_width'] as $col)
$this->xls->getActiveSheet()->getColumnDimensionByColumn($col)->setAutoSize(true);
// filter (has to be set for whole range)
if (count($this->tableParams['filter']))
$this->xls->getActiveSheet()->setAutoFilter(PHPExcel_Cell::stringFromColumnIndex($this->tableParams['filter'][0]).($this->tableParams['header_row']).':'.PHPExcel_Cell::stringFromColumnIndex($this->tableParams['filter'][count($this->tableParams['filter']) - 1]).($this->tableParams['header_row'] + $this->tableParams['row_count']));
// wrap
foreach ($this->tableParams['wrap'] as $col)
$this->xls->getActiveSheet()->getStyle(PHPExcel_Cell::stringFromColumnIndex($col).($this->tableParams['header_row'] + 1).':'.PHPExcel_Cell::stringFromColumnIndex($col).($this->tableParams['header_row'] + $this->tableParams['row_count']))->getAlignment()->setWrapText(true);
}
/**
* Write array of data to actual row starting from column defined by offset
* Offset can be textual or numeric representation
*/
public function addData($data, $offset = 0) {
// solve textual representation
if (!is_numeric($offset))
$offset = PHPExcel_Cell::columnIndexFromString($offset);
foreach ($data as $d) {
$this->xls->getActiveSheet()->setCellValueByColumnAndRow($offset++, $this->row, $d);
}
$this->row++;
}
/**
* Output file to browser
*/
public function output($filename = 'export.xlsx') {
// set layout
$this->View->layout = '';
// headers
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="'.$filename.'"');
header('Cache-Control: max-age=0');
// writer
$objWriter = PHPExcel_IOFactory::createWriter($this->xls, 'Excel2007');
$objWriter->save('php://output');
// clear memory
$this->xls->disconnectWorksheets();
}
/**
* Load vendor classes
*/
protected function loadEssentials() {
// load vendor class
App::import('Vendor', 'PHPExcel/Classes/PHPExcel');
if (!class_exists('PHPExcel')) {
throw new CakeException('Vendor class PHPExcel not found!');
}
}
}
And this is my controller:
public $helpers = array('PhpExcel');
...
public function excel() {
$this->set('participants', $this->Participant->find('all'));
}
This is my view:
<?php
$this->PhpExcel->createWorksheet();
$this->PhpExcel->setDefaultFont('Calibri', 12);
// define table cells
$table = array(
array('label' => __('id'), 'width' => 'auto', 'filter' => true),
array('label' => __('Förnamn'), 'width' => 'auto', 'filter' => true),
array('label' => __('Efternamn'), 'width' => 'auto', 'filter' => true),
array('label' => __('E-postadress'), 'width' => 'auto', 'filter' => true),
array('label' => __('Mobiltelefon'), 'width' => 'auto', 'filter' => true),
array('label' => __('Specialkost'), 'width' => 'auto', 'filter' => true),
array('label' => __('Enhet'), 'width' => 'auto', 'filter' => true),
array('label' => __('Seminarium'), 'width' => 'auto', 'filter' => true),
array('label' => __('Utanför Stockholm'), 'width' => 'auto', 'filter' => true),
array('label' => __('Dela rum'), 'width' => 'auto', 'filter' => true),
array('label' => __('Transfer'), 'width' => 'auto', 'filter' => true),
array('label' => __('Bara där på dagen'), 'width' => 'auto', 'filter' => true),
array('label' => __('Låt'), 'width' => 'auto', 'filter' => true),
array('label' => __('Lärare som blivit hyllad'), 'width' => 'auto', 'filter' => true),
array('label' => __('Kommentar'), 'width' => 'auto', 'filter' => true),
);
// heading
$this->PhpExcel->addTableHeader($table, array('name' => 'Cambria', 'bold' => true));
foreach ($participants as $d) {
$this->PhpExcel->addTableRow(array(
$d['Participant']['id'],
$d['Participant']['f_name'],
$d['Participant']['l_name'],
$d['Participant']['email'],
$d['Participant']['mobile_phone'],
$d['Participant']['special'],
$d['Participant']['school'],
$d['Participant']['seminarium_id'],
$d['Participant']['outside_sthlm'],
$d['Participant']['share_room'],
$d['Participant']['transfer'],
$d['Participant']['only_day'],
$d['Participant']['song'],
$d['Participant']['teacher'],
$d['Participant']['comments']
));
}
$this->PhpExcel->addTableFooter();
$this->PhpExcel->output();
$this->PhpExcel->exit();
?>
When I try to download this in firefox I get the right file extension, xlsx but when trying to download with safari it gives me report.xlsx.html ? and the file becomes useless unless you rename it to report.xlsx, How come?
First of all, you should set your 'layout' to 'false' to prevent the view being loaded in the default (HTML) layout of your website.
Also set the response-type to the type for Excel (as others have mentioned. You can do so via the response-object (http://book.cakephp.org/2.0/en/controllers/request-response.html#dealing-with-content-types)
And finally, do not exit(); in your helper. If you're not rendering your view in a layout, you don't have to exit after outputting the result
In your controller;
public $helpers = array('PhpExcel');
...
public function excel() {
// disable the layout
$this->layout = false;
// Add/define XLS contenttype
$this->response->type(array('xls' => 'application/vnd.ms-excel'));
// Set the response Content-Type to xls
$this->response->type('xls');
$this->set('participants', $this->Participant->find('all'));
}
note As Mark Baker mentioned, XLS and XLSX use a different mime-type, my example uses the mime type for 'classic' XLS, if you're outputting XLSX, modify the mime type accordingly