CakePHP Excel Helper

This is a helper for building multi-sheets Excel documents in CakePHP. The hepler was inspired from Yuen Ying’s blog post, but it’s extended to support multiple sheets.

The helper:

<?php
/**
 * This xls helper is based on the one at
 * http://ykyuen.wordpress.com/2009/10/04/cakephp-export-data-to-a-xls-file/
 * actually creates an xml which is openable in Microsoft Excel.
 * Extended by Nik Chankov
 * Originally written by Yuen Ying Kit @ ykyuen.wordpress.com
 *
 */

class XlsHelper extends AppHelper {
    /**
     * set the header of the http response.
     *
     * @param unknown_type $filename
     */

    function setHeader($filename) {
        header("Pragma: public");
        header("Expires: 0");
        header("Content-Type: application/vnd.ms-excel; charset=UTF-8");
        header("Content-Type: application/force-download");
        header("Content-Type: application/download");;
        header("Content-Disposition: inline; filename="".$filename.".xls"");
    }

    /**
     * Return the workbook
     * @param string $content content of the wrkbook
     * @return string
     */

    function workbook($content) {
        $ret = '<?xml version="1.0" encoding="UTF-8"?>'."\n";
        $ret .= '<?mso-application progid="Excel.Sheet"?>'."\n";
        $ret .= '<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
             xmlns:o="urn:schemas-microsoft-com:office:office"
             xmlns:x="urn:schemas-microsoft-com:office:excel"
             xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
             xmlns:html="http://www.w3.org/TR/REC-html40">
            '
."\n";
        $ret .= '<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">'."\n";
        $ret .= "\t".'<Created>'.date('Y').'-'.date('m').'-'.date('d').'T'.date('H').':'.date('i').':'.date('s').'Z</Created>'."\n";
        $ret .= "\t".'<Company>Your company name</Company>'."\n";
        $ret .= "\t".'<Version>1.00</Version>'."\n";
        $ret .= '</DocumentProperties>'."\n";
        $ret .= '<Styles>'."\n";
        $ret .= '<Style ss:ID="Default" ss:Name="Normal">'."\n";
        $ret .= '<Alignment ss:Vertical="Bottom"/>'."\n";
        $ret .= '<Borders/>'."\n";
        $ret .= '<Font/>'."\n";
        $ret .= '<Interior/>'."\n";
        $ret .= '<NumberFormat/>'."\n";
        $ret .= '<Protection/>'."\n";
        $ret .= '</Style>'."\n";
        $ret .= '<Style ss:ID="s23">'."\n";
        $ret .= '<Font x:Family="Swiss" ss:Bold="1"/>'."\n";
        $ret .= '</Style>'."\n";
        $ret .= '</Styles>'."\n";
        $ret .= $content;
        $ret .= '</Workbook>'."\n";
        return $ret;
    }

    /**
     * Add worksheet
     * @param string $name name of the Worksheet
     * @param string $content content of the worlsheet
     * @return string
     */

    function worksheet($name, $content) {
        $ret = "\t".'<Worksheet ss:Name="'.$name.'">'."\n";
        $ret .= "\t\t".'<Table>'."\n";
        $ret .= $content;
        $ret .= "\t\t".'</Table>'."\n";
        $ret .= "\t".'</Worksheet>'."\n";
        return $ret;
    }

    /**
     * Create a row
     * @param string $content content of the worlsheet
     * @return string
     */

    function row($content) {
        $ret = "\t\t\t".'<Row>'."\n";
        $ret .= $content;
        $ret .= "\t\t\t".'</Row>'."\n";
        return $ret;
    }

    /**
     * Create a cell
     * @param mixed $value value of the cell
     * @param string $type type of the cell. For now it can be either String or Number
     * @return string
     */

    function cell($value, $type = 'String') {
        if (is_null($value)) {
            $ret = "\t\t\t\t".'<Cell><Data ss:Type="String"> </Data></Cell>'."\n";
        } else {
            $ret = "\t\t\t\t".'<Cell><Data ss:Type="'.$type.'">'.$value.'</Data></Cell>'."\n";
        }
        return $ret;
    }
   
    /**
     * Create header cell with bold text
     * The function is the same as this->cell, but bolded
     * @param mixed $value value of the cell
     * @param string $type type of the cell. For now it can be either String or Number
     * @return string
     */

    function hcell($value, $type = 'String') {
        if (is_null($value)) {
            $ret = "\t\t\t\t".'<Cell ss:StyleID="s23"><Data ss:Type="String"> </Data></Cell>'."\n";
        } else {
            $ret = "\t\t\t\t".'<Cell ss:StyleID="s23"><Data ss:Type="'.$type.'">'.$value.'</Data></Cell>'."\n";
        }
        return $ret;
    }
}
?>

How to use it.
The helper instead of printing the result on the screen can be used also to store the result in a file. The basic usage can be following:

$worksheets = '';
$rows = '';
foreach($data as $key=>$row){
   $cells = '';
   foreach($row as $cell){
      //creates a cell
      $cells .= $xls->Cell($cell);
   }
   //creates a row
   $rows .= $xls->row($cells);
}
//Creates stylesheet named First Sheet
$worksheets .= $xls->worksheet('First Sheet', $rows);
//Creates stylesheet named Second Sheet
$worksheets .= $xls->worksheet('Second Sheet', $rows);

// If you print the output to the screen set the proper headers
$xls->setHeader('myExcel.xls');
echo $xls->workbook($worksheets);

//Alternatively you can store the content into file
file_put_contents('path-to-storage/myExcel.xls', $worksheets);

There is only one problem: The file is treated properly from Excel, but it’s not working in Open Office. Open Office cannot recognize the Excel XML file format.

10 thoughts on “CakePHP Excel Helper

  1. StevenNo Gravatar

    Open Office can indeed handle Excel XML (At least version 3.1, though I think 3.0 did as well). The trick is to change the file extension to .xml — open the xml file in OO and it will import it correctly. I’m not sure if it’ll work with the helper code, but I regularly open .xlsx files.

  2. Pingback: CakePHP : signets remarquables du 23/11/2009 au 25/11/2009 | Cherry on the...

  3. Pingback: Tutorial and resources Dealing with xls files on CakePHP | blogfreakz.com

  4. jocuri noiNo Gravatar

    I’m impressed, I have to say. Really rarely do I come across a blog that’s both informative and entertaining, and let me tell you, you’ve hit the nail on the head. Your blog is outstanding; the issue is something that not enough people are talking intelligently about. I am really happy that I stumbled across this in my search for something relating to it.

  5. Pingback: Tutorials and resources Dealing with xls files on CakePHP | codeManiac - Snippets, Templates, API and the best developer content

Leave a Reply

Your email address will not be published. Required fields are marked *