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.

Share it:
  • Facebook
  • Twitter
  • Digg
  • StumbleUpon
  • del.icio.us
  • Google Bookmarks
  • Yahoo! Buzz
  • Add to favorites
  • Identi.ca

10 thoughts on “CakePHP Excel Helper

  1. 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. 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 *