Pablo Viquez Blog

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 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();

Tags: Excel Spreadsheets, pear, PHP, Zend Framework