Pablo Viquez Blog

Mi vida y cosas relacionadas

Skip to: Content | Sidebar | Footer

Export Excel Spreadsheets using Zend Framework

24 August, 2009 (14:04) | PHP, tech, Web Development | By: Pablo Viquez

Last week, I had to allow the user to export a given report into an MS Excel file format.

The application uses Zend Framework 1.9.1 and so far ZF does not support for “Office” formats, so after searching for a nice implementation, I found a PEAR module called Spreadsheet Excel Writter, which looked pretty good, it had very good documentation and the code was clean and well structured so I wanted to give it a shot.

1. PEAR Setup

I used the web interface for the PEAR setup which worked pretty good! (Not covering PEAR installation here).

If you want more info on how to install PEAR, go here: http://pear.php.net/manual/en/installation.getting.php and for the web setup: http://pear.php.net/go-pear

ONCE INSTALLED, REMEMBER TO ADD THE PEAR PATH TO YOUR INCLUDE PATH

2. Following the MVC pattern.

Model View Controller

Model View Controller Diagram

I love the architecture following MVC (Model View Controller) and if I follow the MVC pattern, excel file will go as VIEW, this because the data is being presented an excel file.

Also, the controller does not care which format does the info gets display, the controller should care about manipulating modules, getting the data, controlling the flow of the application not about the presentation layer.

So what did I do?

Zend Framework offers a nice feature called “Context Switch”. This is an action helper that detects requested output and calls the specific view for the required format.

In my case, I wanted to enable this feature to the user controller, which had 3 actions:

  • user/index
  • user/new
  • user/report

Now, I wanted to give the option to export the data as excel only for the “report” action, so I did this on the controller init method:

class UserController extends Zend_Controller_Action
{
    /**
     * Initializes the controller
     *
     * @return void
     */
    public function init()
    {
        // Optional added for consistency
        parent::init();

        // Excel format context
        $excelConfig =
            array(
                'excel' => array(
                    'suffix'  => 'excel',
                    'headers' => array(
                        'Content-type' => 'application/vnd.ms-excel')),
            );

        // Init the Context Switch Action helper
        $contextSwitch = $this->_helper->contextSwitch();

        // Add the new context
        $contextSwitch->setContexts($excelConfig)

        // Set the new context to the reports action
        $contextSwitch->addActionContext('report', 'excel')

        // Initializes the action helper
        $contextSwitch->initContext();
    }

    public function indexAction() { /* ... */ }
    public function newAction() { /* ... */ }
    public function reportAction() { /* ... */ }
}

What this code does is that it tells the controller to search on the parameters (GET/POST) for token “format” and the value “excel”. In other words, If the report action get requested like this:

/user/report?format=excel or /user/report/format/excel instead of loading the view: report.phtml will load the view report.excel.phtml and here is where the magic happens!

In the setup of the Context Switch, the “suffix” is what will be appended to the view file.

The report action, just gets the report and sets a variable on the view so it’s accesible as follow:

class UserController extends Zend_Controller_Action
{
    public function init() { /* ... */ }
    public function indexAction() { /* ... */ }
    public function newAction() { /* ... */ }

    /**
     * Report action
     *
     * @return void
     */
    public function reportAction()
    {
        // Proof of concept
        // Lets say that the result have the following structure
        // 
        // array(
        //     [USER ID] => array(
        //                      'name'  => 'Pablo Víquez',
        //                      'email' => 'email@domain.com',
        //                  ),
        //     [USER_ID] => array(...),
        //     ...
        // )
        $this->view->users = User_Model_Name::getReport();
    }
}

Now the view…

I need to have 2 files for this action:

  • report.phtml – Output will be regular HTML
  • report.excel.phtml – Output will be an MS Excel spreadsheet

report.phtml

This is the HTML format.

<h1>Users report</h1>

<table>
    <thead>
        <tr>
            <th>User Id</th>
            <th>Name</th>
            <th>Email</th>
        </tr>
    </thead>
<!--
    Variable set on the controller, if there's no data to display
    the count will be 0.
-->
<?php if(count($this->users) > 0) : ?>
<?php foreach ($this->users as $userId => $user) : ?>
    <tr>
        <td><?php echo $userId; ?></td>
        <td><?php echo $user['name']; ?></td>
        <td><?php echo $user['email']; ?></td>
    </tr>
<?php endforeach; ?>
<?php endif; ?>
</table>

report.excel.phtml


// Change error reporting for compatibility
// Spreadsheet Excel Writter was built using PHP4,
// so there's a lot of DEPRECATED notices
error_reporting(E_ERROR | E_WARNING | E_PARSE);


/**
 * PEAR package
 *
 * @link http://pear.php.net/package/Spreadsheet_Excel_Writer
 * @see PEAR/Spreadsheet/Excel/Writer.php
 */
require_once 'Spreadsheet/Excel/Writer.php';


// Lets define some custom colors codes
define('CUSTOM_DARK_BLUE', 20);
define('CUSTOM_BLUE', 21);
define('CUSTOM_LIGHT_BLUE', 22);
define('CUSTOM_YELLOW', 23);
define('CUSTOM_GREEN', 24);


// First, we create a Workbook
$workbook = new Spreadsheet_Excel_Writer();


// Add one sheet, called: Users Report
$worksheet = &$workbook->addWorksheet('Users Report');


// Create the custom colors on our new workbook
// This function takes 4 params:
//    - Code index [1 to 64]
//    - RGB colors (0-255)
$workbook->setCustomColor(CUSTOM_DARK_BLUE, 31, 73, 125);
$workbook->setCustomColor(CUSTOM_BLUE, 0, 112, 192);
$workbook->setCustomColor(CUSTOM_LIGHT_BLUE, 184, 204, 228);
$workbook->setCustomColor(CUSTOM_YELLOW, 255, 192, 0);
$workbook->setCustomColor(CUSTOM_GREEN, 0, 176, 80);


// Lets hide gridlines
$worksheet->hideScreenGridlines();


// Lets create some custom styles
$formatHeader = &$workbook->addFormat();
$formatHeader =
    &$workbook->addFormat(
        array('Size'    => 16,
              'VAlign'  => 'vcenter',
              'HAlign'  => 'center',
              'Bold'    => 1,
              'Color'   => 'white',
              'FgColor' => CUSTOM_DARK_BLUE));


$formatReportHeader =
    &$workbook->addFormat(
        array('Size'     => 9,
              'VAlign'   => 'bottom',
              'HAlign'   => 'center',
              'Bold'     => 1,
              'FgColor'  => CUSTOM_LIGHT_BLUE,
              'TextWrap' => true));

$formatData =
    &$workbook->addFormat(
        array(
            'Size'   => 8,
            'HAlign' => 'center',
            'VAlign' => 'vcenter'));

/**
 * First, format the worksheet, adding the headers
 * and row/columns custom sizes
 */

// Create a nice header with a dark blue background
// The function setRow takes 3 parameters:
//    - row index
//    - row height
//    - Format to apply to row [Optional]
$worksheet->setRow(0, 11, $formatHeader);
$worksheet->setRow(1, 46, $formatHeader);
$worksheet->setRow(2, 11, $formatHeader);


// Set the size of the columns
// The function setColumn takes 5 params:
//     - First column
//     - Last column
//     - Column Width
//     - Format [Optional, default = 0]
//     - Hidden [Optional, default = 0]
$worksheet->setColumn(0, 0, 7); // User Id, shrink it to 7
$worksheet->setColumn(1, 1, 12); // Name, set the width to 12
$worksheet->setColumn(1, 1, 15); // Email, set the width to 15


/**
 * 
 * Once we have the format ready, add the text to the spreadsheet
 * 
 */
// Write a text header
$worksheet->write(1, 1, 'Users report', $formatHeader);


// Create the header for the data starting @ row 4
$indexCol = 0;
$indexRow = 4;
$worksheet->write($indexRow, $indexCol++, 'User Id', $formatReportHeader);
$worksheet->write($indexRow, $indexCol++, 'Name', $formatReportHeader);
$worksheet->write($indexRow, $indexCol++, 'Email', $formatReportHeader);


$indexRow++;   // Advance to the next row
$indexCol = 0; // Start @ column 0

// Print the report data
if(count($this->users) == 0) {
    // No data
    $worksheet->write(
        $indexRow,
        $indexCol, 
        'No data to display',
        $formatData);

} else {
    // Write the data
    foreach ($this->users as $userId => $user) {
        $worksheet->writeNumber(
            $indexRow,
            $indexCol++,
            $userId,
            $formatData);

        $worksheet->write(
            $indexRow, 
            $indexCol++, 
            $user['name'], 
            $formatData);

        $worksheet->write(
            $indexRow, 
            $indexCol++, 
            $user['email'], 
            $formatData);

        // Advance to the next row
        $indexRow++;
    }
}


/**
 *
 * Response with the excel file
 *
 */

// Sends HTTP headers for the Excel file.
$workbook->send('report.xls');

// Calls finalization methods.
// This method should always be the last one to be called on every workbook
$workbook->close();
  • Pingback: Pablo Viquez’s Blog: Export Excel Spreadsheets using Zend Framework | Webs Developer

  • http://www.bryanz.com Bryan Zarzuela

    In your first example, I think you meant parent::init().

    Either way, you don’t need to call that since you’re inheriting from Zend_Controller_Action. init() is stubbed out for you to write your own initialization code for your Action Controller.

    • julietta

      julietta
      hello all
      i need some clarification about the path who must be insert the report.exce.phtml and report.phtml ,as must copy the file report.excel.phtml on application/reader/controller or copy on view

      please many thanks

      br

  • Pingback: Zend Framework University — Blog — Export Excel Spreadsheets using Zend Framework and Spreadsheet_Excel_Writer

  • filip

    For exporting data to Excel I recommend GemBox.Spreadsheet. I’ve been using it for years and it’s very easy to work with and very fast. Definitely worth a look.
    http://www.gemboxsoftware.com/GBSpreadsheet.htm

  • http://www.pabloviquez.com Pablo Viquez

    @ Bryan, thanks for the heads up, just updated the code, and yes, it’s not required since the parent method is empty. I actually though on not putting it, however at the end I did it in order to be consistent. Thanks

  • http://www.pabloviquez.com Pablo Viquez

    @filip, GemBox.Spreadsheet does not apply since it’s for .NET and not PHP. The license is $480 so it’s pretty expensive for something you can do for free using an open source implementation (the PEAR module for example)

  • http://pro-sense.at RobertP

    Nice. I’m not sure, if I like the placement of the excel-generation inside the view-layer, but I guess it is the better way… I use Spreadsheet_Excel_Writer from within a generation-class placed in the library/ which appears somehow more naturaly to me, but as I said – it might be better to stick to good old MVC…

  • http://www.quesucede.com Brett Kromkamp

    I have developed a topic map-based site/application with the Zend Framework: http://www.quesucede.com… the Zend Framework proved to be extremely productive and relatively simple to use.

  • Pingback: Federico Cargnelutti (fedecarg) 's status on Friday, 09-Oct-09 11:03:18 UTC - Identi.ca

  • http://davidmintz.org/ David Mintz

    Primero que nada, un saludo fraternal desde Nueva York, de parte de este servidor, gringo pero hispanohablante. Este ejemplo fue una gran ayuda, que me estaba costando trabajando entender la sintáxis de addContext() versus addActionContext() para crear un llamado custom context. La documentación oficial es un poco opaco (en nuestra humilde opinion por supuesto).

    El caso mío es sencillo: quiero que el servidor, cuando reciba un ajax request, devuela javascript para ser evaluado por el cliente (a la manera de Prototype).

    $contextSwitch->addContext(‘js’,array(
    ‘suffix’ => ‘js’,
    ‘headers’ => array(‘Content-type’ => ‘application/javascript’)
    ))
    ->addActionContext(‘ajaxtest’,'js’)
    ->initContext();

  • http://davidmintz.org/ David Mintz

    ligera corrección a lo antedicho:; “me estaba costando trabajo entender…”

  • Pingback: Export Excel Spreadsheets using Zend Framework at Bells of Freedom

  • Dan

    Hi, as an employee if I want the same report (report.excel.phtml) use by all Controllers, without having to copy the same in each view of the Controllers DocumentController, PeopleController, etc …
    The data is passed to the view Through an array.

  • Deepak Ratra

    Hi Everybody,

    This Code is working find for me.
    I have a query that i am generating xls at run time. I just want to know that
    can we set the downloaded excel file as Read only mode through above library??