Yii 1.1: How to use PHPExcel external library with Yii

26 followers

Trying to learn and help some other programmers to find their solutions on the Yii Forum (I think is a superb way to learn the framework), I was facing one challenge with an external library that a fellow programmer wanted to use -quite good indeed: PHPExcel. And what is PHPExcel?

PHPExcel - OpenXML - Create Excel2007 documents in PHP - Spreadsheet engine

Project providing a set of classes for the PHP programming language, which allow you to write to and read from different file formats, like Excel 2007, PDF, HTML, ... This project is built around Microsoft's OpenXML standard and PHP. Checkout the [Features](http://phpexcel.codeplex.com/wikipage?title=Features) this class set provides, such as setting spreadsheet meta data (author, title, description, ...), multiple worksheets, different fonts and font styles, cell borders, fills, gradients, adding images to your spreadsheet and much, much more!

The Challenge

The fellow programmer (Jack Fiallos) had a problem of using the libraries within Yii. He was surprised that he could easily use the libraries outside of the framework but not within the framework itself. At first we didn't think about it but suddenly we thought about the autoloading features of Yii and looked into the guts of PHPExcel to find out what was causing the problems.

We were right, PHPExcel has an autoloading feature itself that when a classes is called (i.e. PHPExcel_Shared_ZipStreamWrapper) the first part of the name is actually reflecting the folder's path where the class is located (i.e. PHPExcel_Shared_ZipStreamWrapper = PHPExcel/Shared_ZipStreamWrapper.php). Please review the following code (extracted from PHPExcel_Autoloader class):

public static function Load($pObjectName){
    if ((class_exists($pObjectName)) || (strpos($pObjectName, 'PHPExcel') === False)) {
       return false;
    }
    // this is the code that shows what I am saying
    $pObjectFilePath =  PHPEXCEL_ROOT.
       str_replace('_',DIRECTORY_SEPARATOR,$pObjectName).'.php';
 
    if ((file_exists($pObjectFilePath) === false) 
       || (is_readable($pObjectFilePath) === false)) {
        return false;
     }
     require($pObjectFilePath);
}   //  function Load()

First Solution: disabling Yii's Autoload

The workaround to this problem (at least the one I know) is by making use of the spl_autoload_register and spl_autoload_unregister PHP's functions. The following code shows how we got the library working -I assume that you have downloaded the PHPExcel files, unzipped its contents and place them into a phpexcel folder within your application's protected/extensions folder:

// 
// VERY DUMMY TEST CONTROLLER
// FOR THE SAKE OF THE EXAMPLE
// TEST IT AS http : / / <yourapplicationurl> / index.php ? r=test/test
 
class TestController extends Controller{
 
     // no layouts here
     public $layout = '';
 
     public function actionTest()
     {
     //
     // get a reference to the path of PHPExcel classes 
     $phpExcelPath = Yii::getPathOfAlias('ext.phpexcel.Classes');
 
     // Turn off our amazing library autoload 
      spl_autoload_unregister(array('YiiBase','autoload'));        
 
     //
     // making use of our reference, include the main class
     // when we do this, phpExcel has its own autoload registration
     // procedure (PHPExcel_Autoloader::Register();)
    include($phpExcelPath . DIRECTORY_SEPARATOR . 'PHPExcel.php');
 
     // Create new PHPExcel object
     $objPHPExcel = new PHPExcel();
 
     // Set properties
     $objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
    ->setLastModifiedBy("Maarten Balliauw")
    ->setTitle("PDF Test Document")
    ->setSubject("PDF Test Document")
    ->setDescription("Test document for PDF, generated using PHP classes.")
    ->setKeywords("pdf php")
    ->setCategory("Test result file");
 
 
     // Add some data
     $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1', 'Hello')
            ->setCellValue('B2', 'world!')
            ->setCellValue('C1', 'Hello')
            ->setCellValue('D2', 'world!');
 
      // Miscellaneous glyphs, UTF-8
     $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A4', 'Miscellaneous glyphs')
            ->setCellValue('A5', 'éàèùâêîôûëïüÿäöüç');
 
      // Rename sheet
      $objPHPExcel->getActiveSheet()->setTitle('Simple');
 
      // Set active sheet index to the first sheet, 
      // so Excel opens this as the first sheet
     $objPHPExcel->setActiveSheetIndex(0);
 
      // Redirect output to a client’s web browser (Excel2007)
      header('Content-Type: application/pdf');
      header('Content-Disposition: attachment;filename="01simple.pdf"');
      header('Cache-Control: max-age=0');
 
      $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'PDF');
      $objWriter->save('php://output');
      Yii::app()->end();
 
       // 
       // Once we have finished using the library, give back the 
       // power to Yii... 
       spl_autoload_register(array('YiiBase','autoload'));
       }
}

Second solution: patching PhpExcel

The previous solution has an annoying consequence: one can't easily mix Yii's classes within a code that uses PHPExcel. Fortunately, there is a solution with no such side effect.

In "Classes/PHPExcel/Autoloader.php", replace the line

return spl_autoload_register(array('PHPExcel_Autoloader', 'Load'));

with:

return spl_autoload_register(array('PHPExcel_Autoloader', 'Load'), true, true);

See the documentation of spl_autoload_register for a description of these parameters.

Final Words

This post is using PHPExcel external library as an example, but this procedure should be taken into account when we encounter a problem like this. Whenever you find a library that you wish to include in your Yii application, check out its autoloading functions first.

Total 13 comments

#14449 report it
zyphers at 2013/08/12 02:45pm
How do you use PHPExcel with PDF library in Yii?

Now, your solution works.

However, I still have problems when I try to include PDF library for PHPExcel to use it. If you download PHPExcel, look at example file: 01simple-download-pdf. You'll understand what I try to do. I can do it using TCPDF without inclue in Yii. But when I try to put it together it doesn't work. Hoever, your example seems to heve PDF include but I don't know how. If you have some time, please guide me.

Thanks.

#13022 report it
marcovtwout at 2013/04/29 04:09am
Yii-PHPExcel extension

I used softark's advice to create an extension: https://github.com/marcovtwout/yii-phpexcel

#12590 report it
softark at 2013/03/30 02:12pm
@lennartvdd

I think that it is an issue of Yii (or its autoloader), not of PHPExcel. The fix should be made on Yii's side.

#12578 report it
lennartvdd at 2013/03/29 11:22am
Proposed implementing this as a fix in the library

Thanks for this great wiki pointing me in the right direction and the tip from softark! I came accross this problem as well, and I suspect many will follow when trying to use this with Yii.

I've used your inspiring ideas to propose a more structural fix for Yii in the PHPExcel library itself. See: workitem #19543

Hopefully they'll pick it up (You can help by voting the worklist item up). This fix could be extended for use in other frameworks/situations as well.

#8432 report it
softark at 2012/06/03 02:20am
The order of the 2 autoloaders is the key point.
// unregister Yii's autoloader
spl_autoload_unregister(array('YiiBase', 'autoload'));
// register PHPExcel's autoloader ... PHPExcel.php will do it
$phpExcelPath = Yii::getPathOfAlias('ext.phpexcel');
include($phpExcelPath . DIRECTORY_SEPARATOR . 'PHPExcel.php');
// register Yii's autoloader again
spl_autoload_register(array('YiiBase', 'autoload'));
 
// This requires Yii's autoloader
$models = SomeModel::model()->findAll();
 
// This requires PHPExcel's autoloader
$objReader = PHPExcel_IOFactory::createReader('Excel5');

In this way we can have both Yii's autoloader and PHPExcel's autoloader working fine at the same time.

The key point is the order of the two autoloaders.

Yii's autoloader throws an exception when it has failed to find the file, and thus deprives another autoloader of a chance to work. But PHPExcel's autoloader will just return false on failure, and gives another autoloader a chance.

There's no need to tweak a single line in PHPExcel.php.

(tested with Yii 1.1.10 and PHPExcel 1.7.7)

#6628 report it
retomi at 2012/01/23 06:16am
Resolving Yii Autoloader problems with the PHPExcel Autoloader

Look at this solution worked fine for me: http://www.mrsoundless.com/post/2011/04/23/PHPExcel-in-Yii.aspx

#5710 report it
pligor at 2011/11/05 04:09pm
Instead of...

Instead of a code similar as below

public static function loadPHPExcel() {
        spl_autoload_unregister(array('YiiBase','autoload'));   //Turn off our amazing library autoload         
        Yii::import('ext.phpexcel.PligorPHPExcel',true);    //include("$phpExcelPath/PHPExcel.php");    
    }
 
    public static function unLoadPHPExcel() {
        spl_autoload_register(array('YiiBase','autoload')); //give back the power to Yii
    }

Could someone take advantage of the method register auto loader: http://www.yiiframework.com/doc/api/1.1/YiiBase#registerAutoloader-detail ??

I cannot get how this autoloading works exactly. Thank you!

#5585 report it
Tyr84 at 2011/10/21 07:46pm
O nice

Tnx for that !! So you need put : spl_autoload_register(array('YiiBase','autoload')); at the end ofo code. I love yii :)

#5174 report it
pligor at 2011/09/21 06:43am
Another approach - buggy (?)

has anyone used this post to integrate PHPExcel inside Yii framework ?

http://www.yiiframework.com/forum/index.php?/topic/18486-new-way-to-use-phpexcel-with-yii-framework/page__p__90988__hl__phpexcel#entry90988

I tried it and it seems buggy. I cannot get Yii to take back the control of autoloading

#5064 report it
pligor at 2011/09/12 04:10am
Read Filter

One important class that accompanies PHPExcel and you should write your own is the ExcelReadFilter class which implements PHPExcel_Reader_IReadFilter.

Because of the whole confusion with the autoload I failed to use it as a component in Yii.

Instead I created a new php file inside the same directory as PHPExcel.php

So its code looks like this:

<?php
require 'PHPExcel.php';
 
class ExcelReadFilter implements PHPExcel_Reader_IReadFilter {
    //class code in here
}

I named the file MyPHPExcel.php and currently I am importing MyPHPExcel.php instead of PHPExcel.php.

If you have any more neat solutions please share (:

#5063 report it
pligor at 2011/09/12 04:02am
Import instead of Include

I tried, instead of include($phpExcelPath . DIRECTORY_SEPARATOR . 'PHPExcel.php');, this line:

Yii::import('ext.phpexcel.PHPExcel',true);

and worked well

#2273 report it
homebru at 2010/12/05 03:53pm
Correction Needed

The new version of PHPExcel no longer ships with a 'Classes' folder so your very first functional line:

$phpExcelPath = Yii::getPathOfAlias('ext.phpexcel.Classes');

Needs to be changed to:

$phpExcelPath = Yii::getPathOfAlias('ext.phpexcel');

Thanks for your article - it got me up and running!

Leave a comment

Please to leave your comment.

Write new article