I used PHPExcel with success - a helper was used for generating each of the
titles, rows etc.
I cannot remember where I got it from but I did make some modifications:
<?php
App::import('Vendor','PHPExcel',array('file' => 'excel/PHPExcel.php'));
App::import('Vendor','PHPExcelWriter',array('file' =>
'excel/PHPExcel/Writer/Excel5.php'));
class ExcelHelper extends AppHelper {
var $xls;
var $sheet;
var $data;
var $blacklist = array();
function excelHelper() {
$this->xls = new PHPExcel();
$this->sheet = $this->xls->getActiveSheet();
$this->sheet->getDefaultStyle()->getFont()->setName('Verdana');
}
function generate(&$data, $title = 'Report') {
$this->data =& $data;
$this->_title($title);
$this->_headers();
$this->_rows();
$this->_output($title);
return true;
}
function _title($title) {
$this->sheet->setCellValue('A2', $title);
$this->sheet->getStyle('A2')->getFont()->setSize(14);
$this->sheet->getRowDimension('2')->setRowHeight(23);
}
function _headers() {
$i=0;
foreach ($this->data[0] as $field => $value) {
if (!in_array($field,$this->blacklist)) {
$columnName = Inflector::humanize($field);
$this->sheet->setCellValueByColumnAndRow($i++, 4, $columnName);
}
}
$this->sheet->getStyle('A4')->getFont()->setBold(true);
$this->sheet->getStyle('A4')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$this->sheet->getStyle('A4')->getFill()->getStartColor()->setRGB('969696');
$this->sheet->duplicateStyle( $this->sheet->getStyle('A4'),
'B4:'.$this->sheet->getHighestColumn().'4');
for ($j=1; $j<$i; $j++) {
$this->sheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($j))->setAutoSize(true);
}
}
function _rows() {
$i=5;
foreach ($this->data as $row) {
$j=0;
foreach ($row as $field => $value) {
if(!in_array($field,$this->blacklist)) {
$this->sheet->setCellValueByColumnAndRow($j++,$i, $value);
}
}
$i++;
}
}
function _output($title) {
header("Content-type: application/vnd.ms-excel");
header('Content-Disposition: attachment;filename="'.$title.'.xls"');
header('Cache-Control: max-age=0');
$objWriter = new PHPExcel_Writer_Excel5($this->xls);
$objWriter->setTempDir(TMP);
$objWriter->save('php://output');
}
}
?>
and my export function (if it's any help)
function admin_export($job_id) {
$this->layout = 'ajax';
$this->helpers[] = 'Excel';
$users = $this->EventUser->find('all', array(
'contain' => array(
'User' => array(
'AnsProfile',
'conditions' => array('User.status' => 1)),
'Job' => array('AnsRegion'),
'Event'
),
'conditions' => array('EventUser.job_id' => $job_id),
'order' => array(
'User.name',
'EventUser.available DESC')
)
);
// Format the data into a flat array for excel
$export = array();
foreach ($users as $key => $user) {
$export[$key]['event_date'] = $user['Event']['startdate'];
$export[$key]['event_city'] = $user['Event']['city'];
$export[$key]['event_module'] = $user['Event']['module'];
$export[$key]['event_centre'] = $user['Event']['centre'];
$export[$key]['name'] = $user['User']['name'];
if (isset($user['User']['AnsProfile']['initials'])) {
$export[$key]['initials'] = $user['User']['AnsProfile']['initials'];
} else {
$export[$key]['initials'] = '';
}
if (isset($user['User']['AnsProfile']['examiner_number'])) {
$export[$key]['examiner_number'] =
$user['User']['AnsProfile']['examiner_number'];
} else {
$export[$key]['examiner_number'] = '';
}
$export[$key]['available'] =
$this->_yesno($user['EventUser']['available']);
$export[$key]['reason'] = $user['EventUser']['reason'];
$export[$key]['speaking'] = $this->_yesno($user['EventUser']['speaking']);
$export[$key]['writing'] = $this->_yesno($user['EventUser']['writing']);
$export[$key]['marking'] = $this->_yesno($user['EventUser']['marking']);
if (isset($user['User']['AnsProfile']['invigilation'])) {
$export[$key]['invigilation'] = $user['User']['AnsProfile']['invigilation'];
} else {
$export[$key]['invigilation'] = '';
}
if (isset($user['EventUser']['comment'])) {
$export[$key]['comment'] = $user['EventUser']['comment'];
} else {
$export[$key]['comment'] = '';
}
$export[$key]['email'] = $user['User']['email'];
if (isset($user['User']['AnsProfile']['city'])) {
$export[$key]['city'] = $user['User']['AnsProfile']['city'];
} else {
$export[$key]['city'] = '';
}
$export[$key]['region'] = $user['Job']['AnsRegion']['title'];
} // foreach
$this->set('users', $export);
$this->set('region', $user['Job']['AnsRegion']['title']);
$this->Job->recursive = -1;
$this->set('job', $this->Job->findById($job_id));
}
My view
<?php $excel->generate($users, 'All Users for region '.$region.' for Month:
'.$job['Job']['month']."/".$job['Job']['year']); ?>
On 12 May 2011 14:35, abhimanyu bv <[email protected]> wrote:
> Hi all,
> How to generate Excel sheet from cakephp in a format??
>
> --
> Our newest site for the community: CakePHP Video Tutorials
> http://tv.cakephp.org
> Check out the new CakePHP Questions site http://ask.cakephp.org and help
> others with their CakePHP related questions.
>
>
> To unsubscribe from this group, send email to
> [email protected] For more options, visit this group
> at http://groups.google.com/group/cake-php
>
--
Our newest site for the community: CakePHP Video Tutorials
http://tv.cakephp.org
Check out the new CakePHP Questions site http://ask.cakephp.org and help others
with their CakePHP related questions.
To unsubscribe from this group, send email to
[email protected] For more options, visit this group at
http://groups.google.com/group/cake-php
