Re: Generate Excel from cakephp

[Renato de Freitas Freire]

Try this:

----- xls helper (views/helpers/xls.php) -----
<?php
/**
 * This xls helper is based on the one at
 * http://bakery.cakephp.org/articles/view/excel-xls-helper
 *
 * The difference compared with the original one is this helper
 * actually creates an xml which is openable in Microsoft Excel.
 *
 * 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("Cache-Control: must-revalidate, post-check=0, pre-check=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\"");
    }

    /**
     * add the xml header for the .xls file.
     *
     */
    function addXmlHeader() {
        echo "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n";
        echo "<Workbook
xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"\n";
        echo "
 xmlns:x=\"urn:schemas-microsoft-com:office:excel\"\n";
        echo "
 xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"\n";
        echo "          xmlns:html=\"http://www.w3.org/TR/REC-html40\";>\n";
        return;
    }

    /**
     * add the worksheet name for the .xls.
     * it has to be added otherwise the xml format is incomplete.
     *
     * @param unknown_type $workSheetName
     */
    function setWorkSheetName($workSheetName) {
     echo "\t<Worksheet ss:Name=\"".$workSheetName."\">\n";
        echo "\t\t<Table>\n";
        return;
    }


function closeWorkSheet() {
        echo "\t\t</Table>\n";
        echo "\t</Worksheet>\n";
        return;
}

    /**
     * add the footer to the end of xml.
     * it has to be added otherwise the xml format is incomplete.
     *
     */
    function addXmlFooter() {
        //echo "\t\t</Table>\n";
        //echo "\t</Worksheet>\n";
        echo "</Workbook>\n";
        return;
    }

    /**
     * move to the next row in the .xls.
     * must be used with closeRow() in pair.
     *
     */
    function openRow() {
     echo "\t\t\t<Row>\n";
     return;
    }

    /**
     * end the row in the .xls.
     * must be used with openRow() in pair.
     *
     */
    function closeRow() {
     echo "\t\t\t</Row>\n";
     return;
    }

    /**
     * Write the content of a cell in number format
     *
     * @param unknown_type $Value
     */
    function writeNumber($Value) {
     if (is_null($Value)) {
     echo "\t\t\t\t<Cell><Data ss:Type=\"String\"> </Data></Cell>\n";
     } else {
     echo "\t\t\t\t<Cell><Data
ss:Type=\"Number\">".addslashes($Value)."</Data></Cell>\n";
     }
        return;
    }

    /**
     * Write the content of a cell in string format
     *
     * @param unknown_type $Value
     */
function writeString($Value) {
     if (is_null($Value)) {
     echo "\t\t\t\t<Cell><Data ss:Type=\"String\"> </Data></Cell>\n";
     } else {
     echo "\t\t\t\t<Cell><Data ss:Type=\"String\">".str_replace("\r\n","
",utf8_encode($Value))."</Data></Cell>\n";
     }
        return;
    }

    function writeColumn($header) {
     $i = 2;
     foreach($header as $h) {
     echo "\t\t<Column ss:Index=\"".$i."\" ss:AutoFitWidth=\"0\"
ss:Width=\"110\"/> ";
     $i++;
    }
    }
}
?>
----- xls helper -----


----- view (views/controller-name/action-name.php -----
<?php
$blacklist = array();

    /**
     * Export all records in .xls format
     * with the help of the xlsHelper
     */

//input the export file name
$xls->setHeader('File_'.date('Y_m_d'));

    $xls->addXmlHeader();
    $xls->setWorkSheetName('Worksheet');

    //1st row for columns name
    $xls->writeColumn($header);
    $xls->openRow();
    foreach($header as $h) {
if (!in_array($h,$blacklist)) {
     $xls->writeString($h);
    }
}
    $xls->closeRow();

    //rows for data
    foreach ($data as $row):
    $xls->openRow();
    foreach($row["Model"] as $field => $value) {
if (!in_array($field,$blacklist)) {
    $xls->writeString($value);
}
    }

    $xls->closeRow();
    endforeach;
$xls->closeWorkSheet();

    $xls->addXmlFooter();
    exit();
?>
----- view -----


In your controller you add the helper:
var $helpers = array('Html', 'Form','Javascript','xls');

And the action to export:

function export_to_excel() {
        $this->layout = null;

        $data = $this->Model->find("all");

        foreach($data[0]["Model"] as $field => $value) {
        $header[] = $field;
        }

        $this->set('data', $data);
        $this->set('header',$header);
}

And there you go...
A functional export to native excel (xml 2003+) helper in cake php...

hope this help you...


--
Renato Freire
ren...@morfer.org




On Fri, May 13, 2011 at 3:01 AM, abhimanyu bv <vmabhi...@gmail.com> wrote:

> @bujanga,
>
> following the steps in the link you provided, output is rendered as table
> not as excel cells. I want to render data as excel.
>
> --
> 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
> cake-php+unsubscr...@googlegroups.com 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
cake-php+unsubscr...@googlegroups.com For more options, visit this group at 
http://groups.google.com/group/cake-php