Simple Excel spreadsheet helper

by melgior
I made this Excel helper because I wanted to generate reports of data easily, without having to specify column names and other stuff. You just need one line in your view and throw an array with data and a title to the helper and you're done.
Off course, the fact that this is a simple basic helper created in a short amount of time means it has some limitations:
  • The spreadsheet it generates is very basic. It contains a title, a header row with al collumn names and then the data as it is.
  • It doesn't catch errors. If there's an PHP error message along the way, it will be inside the Excel sheet it generates
  • You can only change the colours and fonts inside the helper's code
  • The library used for the Excel file generation requires PHP 5.2 or newer to be installed on your server

Well, if you're still interested and can live with these limitations, then welcome aboard! Installation is very easy and just takes 3 simple steps.

Let's see how we can generate a participants list for an event. In this example, there's a model called Event that is connected to the model Participant. If the participant model has the fields id, firstname, lastname and phone the result will look like this:

Step 1

All the hard work is done by the PHPExcel class. You can download it from the project's website: http://phpexcel.codeplex.com. Create a folder called 'excel' in your Vendors folder. Extract the zip file and copy the contents of the 'Classes' folder to the 'excel' folder. This helper was written for the 1.7.0 version of PHPExcel. Newer versions might not be compatible.

Step 2

Here's the helper class. Store it in a file called excel.php in the views/helpers directory.

Helper Class:

<?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');
    }
}
?>

Step 3

In your view, you just need one line of code. The generate function has two parameters: the array with the data you want in the report and the title. You should include the model name in the data variable, in this example 'Participant'.

View Template:

<?php
    $excel
->generate($event['Participant'], 'Participants list '.$event['Event']['name']);
?>

Here's the controller action for this view. Note that you should use an empty layout since we just want the excel file and no html. You can use the ajax layout for this. It's build into CakePHP, so you don't have to create it.

Controller Class:

<?php 
class EventsController extends AppController {
    
$helpers = array('Report');
    
    function 
participants($id null) {
        
$this->layout 'ajax';
        
$this->set('event'$this->Events->findById($id));
    }
}
?>

If you want to skip some fields, you can include them in the blacklist array. It's better to select the fields you want to display in the controller or in the model, but if for some reason you can't do that you can use this code:

View Template:

<?php
    $excel
->blacklist arary('id','phone');
    
$excel->generate($participants['Participant'],'Participants list '.$event['Event']['name']);
?>

You're done!

This was it, everything should be running fine now. Well, just one more thing then:

Bonus: .xlsx

If you want to use the new Excel 2007 file format, you just need to change a few lines in the helper class. The php_zip and php_xml extensions for PHP should be enabled for this to work. Check to the PHPExcel documentation for more information.

Helper Class:

<?php 
//Change the first two lines to:
App::import('Vendor','PHPExcel',array('file' => 'excel/PHPExcel.php'));
App::import('Vendor','PHPExcelWriter',array('file' => 'excel/PHPExcel/Writer/Excel2007.php'));

//Replace the _output function with this one:
function _output($title) {
    
header('Content-Type: application/vnd.openXMLformats-officedocument.spreadsheetml.sheet');
    
header('Content-Disposition: attachment;filename="'.$title.'.xlsx"');
    
header('Cache-Control: max-age=0');
    
$objWriter = new PHPExcel_Writer_Excel2007($this->xls);
    
$objWriter->save('php://output');
}
?>

Report

More on Helpers

Advertising

Comments

  • anuragtrivediphp posted on 03/26/11 12:22:54 PM
    Hi ,

    I am facing following error after using is above mention excel helper.



    Notice (8): Undefined offset: 0 [APP/vendors/excel/PHPExcel/Cell/DefaultValueBinder.php, line 86]

    Code | Context

    $pValue = array(
    "id" => "1",
    "company_name" => "",
    "BatchNo" => "Batch1",
    "StepName" => "ChromatographyA",
    "ProductID" => "P001",
    "ProductName" => "ProductA",
    "Name" => "Buffer1",
    "RecordNo" => "RM-01",
    "ForOperation" => "Equilibration",
    "LotNo" => "L0001"
    )

    returnPHPExcel_Cell_DataType::TYPE_STRING;
    }elseif($pValue{0}==='='&&strlen($pValue)>1){

    PHPExcel_Cell_DefaultValueBinder::dataTypeForValue() - APP/vendors/excel/PHPExcel/Cell/DefaultValueBinder.php, line 86
    PHPExcel_Cell_DataType::dataTypeForValue() - APP/vendors/excel/PHPExcel/Cell/DataType.php, line 72
    PHPExcel_Cell_DefaultValueBinder::bindValue() - APP/vendors/excel/PHPExcel/Cell/DefaultValueBinder.php, line 63
    PHPExcel_Cell::setValue() - APP/vendors/excel/PHPExcel/Cell.php, line 219
    PHPExcel_Worksheet::setCellValueByColumnAndRow() - APP/vendors/excel/PHPExcel/Worksheet.php, line 881
    ExcelHelper::_rows() - APP/views/helpers/excel.php, line 56
    ExcelHelper::generate() - APP/views/helpers/excel.php, line 22
    include - APP/views/visualizes/excel.ctp, line 3
    View::_render() - CORE/cake/libs/view/view.php, line 665
    View::render() - CORE/cake/libs/view/view.php, line 375
    Controller::render() - CORE/cake/libs/controller/controller.php, line 808
    Dispatcher::_invoke() - CORE/cake/dispatcher.php, line 229
    Dispatcher::dispatch() - CORE/cake/dispatcher.php, line 193
    require - APP/webroot/index.php, line 87
    [main] - CORE/index.php, line 72

    Warning (2): preg_match() expects parameter 2 to be string, array given [APP/vendors/excel/PHPExcel/Cell/DefaultValueBinder.php, line 95]

    Code | Context

    $pValue = array(
    "id" => "1",
    "company_name" => "",
    "BatchNo" => "Batch1",
    "StepName" => "ChromatographyA",
    "ProductID" => "P001",
    "ProductName" => "ProductA",
    "Name" => "Buffer1",
    "RecordNo" => "RM-01",
    "ForOperation" => "Equilibration",
    "LotNo" => "L0001"
    )

    preg_match - [internal], line ??
    PHPExcel_Cell_DefaultValueBinder::dataTypeForValue() - APP/vendors/excel/PHPExcel/Cell/DefaultValueBinder.php, line 95
    PHPExcel_Cell_DataType::dataTypeForValue() - APP/vendors/excel/PHPExcel/Cell/DataType.php, line 72
    PHPExcel_Cell_DefaultValueBinder::bindValue() - APP/vendors/excel/PHPExcel/Cell/DefaultValueBinder.php, line 63
    PHPExcel_Cell::setValue() - APP/vendors/excel/PHPExcel/Cell.php, line 219
    PHPExcel_Worksheet::setCellValueByColumnAndRow() - APP/vendors/excel/PHPExcel/Worksheet.php, line 881
    ExcelHelper::_rows() - APP/views/helpers/excel.php, line 56
    ExcelHelper::generate() - APP/views/helpers/excel.php, line 22
    include - APP/views/visualizes/excel.ctp, line 3
    View::_render() - CORE/cake/libs/view/view.php, line 665
    View::render() - CORE/cake/libs/view/view.php, line 375
    Controller::render() - CORE/cake/libs/controller/controller.php, line 808
    Dispatcher::_invoke() - CORE/cake/dispatcher.php, line 229
    Dispatcher::dispatch() - CORE/cake/dispatcher.php, line 193
    require - APP/webroot/index.php, line 87
    [main] - CORE/index.php, line 72

    Warning (2): iconv_substr() expects parameter 1 to be string, array given [APP/vendors/excel/PHPExcel/Shared/String.php, line 566]

    Code | Context

    $pValue = array(
    "id" => "1",
    "company_name" => "",
    "BatchNo" => "Batch1",
    "StepName" => "ChromatographyA",
    "ProductID" => "P001",
    "ProductName" => "ProductA",
    "Name" => "Buffer1",
    "RecordNo" => "RM-01",
    "ForOperation" => "Equilibration",
    "LotNo" => "L0001"
    )
    $pStart = 0
    $pLength = 32767

    iconv_substr - [internal], line ??
    PHPExcel_Shared_String::Substring() - APP/vendors/excel/PHPExcel/Shared/String.php, line 566
    PHPExcel_Cell_DataType::checkString() - APP/vendors/excel/PHPExcel/Cell/DataType.php, line 89
    PHPExcel_Cell::setValueExplicit() - APP/vendors/excel/PHPExcel/Cell.php, line 242
    PHPExcel_Cell_DefaultValueBinder::bindValue() - APP/vendors/excel/PHPExcel/Cell/DefaultValueBinder.php, line 63
    PHPExcel_Cell::setValue() - APP/vendors/excel/PHPExcel/Cell.php, line 219
    PHPExcel_Worksheet::setCellValueByColumnAndRow() - APP/vendors/excel/PHPExcel/Worksheet.php, line 881
    ExcelHelper::_rows() - APP/views/helpers/excel.php, line 56
    ExcelHelper::generate() - APP/views/helpers/excel.php, line 22
    include - APP/views/visualizes/excel.ctp, line 3
    View::_render() - CORE/cake/libs/view/view.php, line 665
    View::render() - CORE/cake/libs/view/view.php, line 375
    Controller::render() - CORE/cake/libs/controller/controller.php, line 808
    Dispatcher::_invoke() - CORE/cake/dispatcher.php, line 229
    Dispatcher::dispatch() - CORE/cake/dispatcher.php, line 193
    require - APP/webroot/index.php, line 87
    [main] - CORE/index.php, line 72
  • freeman2501 posted on 10/29/10 01:21:57 AM
    Nice helper. Good job! We used to offer CSV exports of DB data to our clients, but Mickysoft Excel seems to dislike CSV files more and more so we encountered more and more issues with clients who could not import the CSV correctly in their Ms Excel. Guess we will switch to a direct xls export thanks to this helper.

    [quote] I am using PHPExcel 1.7.0 and have loaded the GD, XML, and ZIP PHP extensions. I have even tried several variations of the example, but still get this error:

    Invalid argument supplied for foreach() APP\views\helpers\excel.php, line 35 and line 52
    Those lines are as follows:

    foreach ($this->data[0] as $field => $value) { 
    foreach ($this->data as $row) {

    What am I doing wrong?
    [end quote]
    The example data which is pushed in the helper is the related Participants data (Event hasMany Participants) so the array will be without a Model key:


    $data[0][key][value];
    $data[1][key][value];

    If you do a find on your main model your data will also contain a Model key:


    $data[0][Modelname][key][value];
    $data[1][Modelname][key][value];

    This causes the error if you push that data array to the helper. Just rework the array and get rid of the Modelname key or tweak the helper.
  • srumjant posted on 07/29/10 06:04:50 AM
    Hi,
    Firstly, it is greate helper, but I would like to change my current xls file online.
    So is there any way to load xls file and change its content with this helper?

    Edit:
    Ok, I managed to make it by myself. Here is code. Hope it will be helpful in the future .. :)

    Helper Class:

    <?php 
    class ExcelHelper extends AppHelper 
    {
        
        var 
    $xls;
        var 
    $reader;
        var 
    $sheet;
        var 
    $data;
        var 
    $blacklist = array();
        
        function 
    excelHelper() 
        {
            
        }
        
        function 
    loadFile($file)
        {
            
    $this->reader = new PHPExcel_Reader_Excel5();
            
    $this->xls $this->reader->load("{$file}");
            
            
    $this->xls->setActiveSheetIndex(0);
            
    $this->sheet $this->xls->getActiveSheet();
            
    $this->sheet->getDefaultStyle()->getFont()->setName('Verdana');
        }
        
        function 
    newFile()
        {
            
    $this->xls = new PHPExcel();
            
    $this->sheet $this->xls->getActiveSheet();
            
    $this->sheet->getDefaultStyle()->getFont()->setName('Verdana');
        }
        
        function 
    changeCell($value null$cell null)
        {
            
    $this->sheet->setCellValue($cell$value);   
        }
        
        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 in view

    View Template:


    if (!file_exists('file.xls')) {
           exit('Please run file.xls first.\n');
        }
        //$excel->newFile(); //Used for creating new xls file.
        $excel->loadFile('file.xls');
        $excel->changeCell('some text', 'A1');
        $excel->_output('newFilename');
  • bbdanzig posted on 04/22/10 12:17:30 PM
    I was wondering if you had to do anything additional to set up PHPExcel to work with CakePHP. I am unable to output the excel files directly to the browser when using CakePHP, however, this works fine outside of CakePHP...any ideas? Thanks.

    Alright this was my solution:

    There was a space after a closing ?> tag in one of my component files that was adding a space to the beginning of my view. This space was throwing off the output and corrupting the file.
    • JohnMaxim posted on 02/09/11 07:52:43 AM
      [quote] I was wondering if you had to do anything additional to set up PHPExcel to work with CakePHP. I am unable to output the excel files directly to the browser when using CakePHP, however, this works fine outside of CakePHP...any ideas? Thanks.

      Alright this was my solution:

      There was a space after a closing ?> tag in one of my component files that was adding a space to the beginning of my view. This space was throwing off the output and corrupting the file.
      [end quote] Hi can you explain more where the space you talked about ? I think I am facing the same thing. It says my file is not compatible or corrupted.
  • bbdanzig posted on 04/21/10 10:40:25 PM
    I was wondering if you had to do anything additional to set up PHPExcel to work with CakePHP. I am unable to output the excel files directly to the browser when using CakePHP, however, this works fine outside of CakePHP...any ideas? Thanks.
  • marcko_23 posted on 02/03/10 02:31:20 PM
    Invalid argument supplied for foreach() APP\views\helpers\excel.php, line 35 and line 52
    function _headers() {
    $i=0;
    foreach ($this->data[0] as $field => $value) {
    if (!in_array($field,$this->blacklist)) {

    function _rows() {
    $i=5;
    foreach ($this->data as $row) {
    $j=0;
  • joshkraemer posted on 02/01/10 09:33:13 PM
    I am using PHPExcel 1.7.0 and have loaded the GD, XML, and ZIP PHP extensions. I have even tried several variations of the example, but still get this error:

    Invalid argument supplied for foreach() APP\views\helpers\excel.php, line 35 and line 52
    Those lines are as follows:

    foreach ($this->data[0] as $field => $value) { 
    foreach ($this->data as $row) {

    What am I doing wrong?
  • ADmad posted on 08/23/09 05:52:01 PM
    The helper's is very basic and rigid (non-configurable) but you have already stated that in your article so i guess that's fine. What's missing is api doc blocks in the helper file. Also please consider updating the code in future to make it more configurable.
login to post a comment.