fogbound.net




Fri, 21 Oct 2011

Using PHPExcel with Yii

— SjG @ 2:18 pm

I was running into problems with conflicting autoloaders when I tried to use PHPExcel with the Yii framework. There are instructions out on the web on how to resolve this, but they are only useful if you’re not creating instances of Yii active record models while you’re using PHPExcel. That’s fine if you’re using Yii to create an Excel spreadsheet from already instantiated Yii classes, but my need is to read an Excel spreadsheet and import its data into my database.

It turns out that this is fairly straightforward as well. The trick is in realizing that PHPExcel is registering its autoloader when it’s instantiated, so you don’t need to register it yourself.

public function actionImport()
  {
  $message = '';
  if (!empty($_POST))
     {
    $file = CUploadedFile::getInstanceByName('import');
    $spec = Yii::app()->basePath.'/data/imports/'.$file->name;
    $file->saveAs($spec);
    spl_autoload_unregister(array('YiiBase','autoload'));
    require(Yii::app()->basePath.'/extensions/phpexcel/Classes/PHPExcel.php');
    spl_autoload_register(array('YiiBase', 'autoload'));
    try {
         $inputFileType = PHPExcel_IOFactory::identify($spec); 
         $objReader = PHPExcel_IOFactory::createReader($inputFileType);  
         if ($inputFileType != 'CSV')
            {
            $objReader->setReadDataOnly(true);
            }
        $objPHPExcel = $objReader->load($spec); 
        $objWorksheet = $objPHPExcel->setActiveSheetIndex(0);
        $highestRow = $objWorksheet->getHighestRow();
        for ($row = 1;$row < $highestRow+1; $row++)
             {
             $myObjThing = new MyObject; // Yii AR model
             $myObjThing->someField = $objWorksheet->getCellByColumnAndRow(1, $row)->getValue();
             $myObjThing->otherField = $objWorksheet->getCellByColumnAndRow(5, $row)->getValue();
             $myObjThing->save(false);
             $myObjThing->detachBehaviors(); // PHP < 5.3 memory management
             unset($myObjThing);
             }
        }
    catch (Exception $e)
       {
       $message = 'There was a problem handling your file. Technical details: '.$e->getMessage();
       }
    if (! empty($message))
       {
       Yii::app()->user->setFlash('error',$message);
       }
    }		
  $this->render('import');
  }

Pretend WordPress didn’t hose the formatting on that …


7 responses to “Using PHPExcel with Yii”

  1. Hi, I’m having the same problem here, I use phpexcel4yii extension to load the PHPExcel object, but when I try to create an AR object, it seems just stopped / crashed, any suggestion?

  2. SjG says:

    Have you tried using code like the example above? I didn’t use the phpexcell4yii extension, I just put the standard PHPHExcel distribution in my extensions directory, and load it as in the code above. It works for me.

  3. Yusuf Yahaya says:

    Thanks a lot for the post. I really helped me out. though i had to make changes for it to work for me.
    The changes i made was to re-register spl_autoload_register before the for-loop.

  4. Allan Bicol says:

    Thanks a lot for the post.

  5. eiji says:

    thanks for tutorial..But i have problem to make view page of this action import..can u help me?

  6. SjG says:

    Something like this should work:

    < ?php echo CHtml::beginForm('', 'post', array('enctype'=>'multipart/form-data')); ?>
    < ?php echo CHtml::label('File to Import', 'fti'); ?> 
    < ?php echo CHtml::fileField('import', '', array('id'=>'fti')); ?>
    < ?php echo CHtml::submitButton('Import'); ?> 
    < ?php echo CHtml::endForm(); ?>

  7. Fab says:

    I realize this is an old post, but i’m having issues getting this working. I have the form in view an code for import in defaultcontroller. When I upload the spreadsheet, nothing happens.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.