Yii CActiveDataProvider To Excel

Download PHPExcel from http://www.codeplex.com/PHPExcel and extract it. Now you will get the following folder structure /Classes /Documentation /Tests ... and more files or folder. Rename "Classes" to "phpexcel" and paste it into "protected/extension" folder. Download yii extension http://www.yiiframework.com/extension/eexcelview/files/EExcelView0.33.zip and include it into extension. Yii CActiveDataProvider To Excel. Add the "excel" extension into extension folder to download the sql data to excel. Using this tutorial We can download in excel file using CActiveDataprovider. It is very simple and useful.

Folder Directory

extensions/EExcelView
extensions/phpexcel

CGridview To Export Excel

config/main.php


	'import'=>array(
		'application.models.*',
		'application.components.*',
        'ext.EExcelView.*',
	),

admin.php


<?php
$this->widget('EExcelView', array(
     'dataProvider'=> $model->search(),
	'columns'=>array(
		//'employee_id',
		'emp_number',
		'emp_firstname',
		'emp_lastname',
		'emp_middle_name',
		//'emp_nick_name',
		array(
            'name'=>'job_title_code',
            'value'=>'$data->jobTitleCode->job_title'
        ),
		array(
			'class'=>'CButtonColumn',
		),
	),     
     'title'=>'Title',
     'autoWidth'=>false,
     'template'=>"{summary}\n{items}\n{exportbuttons}\n{pager}", 
));
?>

Note

convert gridview format data to excel file using method2. 
Assign Header to excel. 
Data from relation table

CActiveDataProvider To Excel

If your dirctory file have EExcelBehavior.php, use the below method also.
extensions/EExcelView
extensions/EExcelBehavior.php
extensions/phpexcel

<?php
    public function behaviors()
    {
        return array(
            'eexcelview'=>array(
                'class'=>'ext.eexcelview.EExcelBehavior',
            ),
        );
    }

Method 1


    public function actionUserreports_1(){
        /** To download excel format **/
       $criteria=new CDbCriteria;
       // condition
       $user = new CActiveDataProvider('User',array('criteria'=>$criteria));                                
       $this->toExcel($user,
               array('username','firstname','lastname','email','companyname',
              'User Type'=>'usertype', //assign header
              'usersubscription.configdescription',//data from relation
              'Created By'=>'createdby0.username'
               ),
              'User Reports',// Excel Name
               array( 'creator' => 'bsourcecode',   ),
              'Excel2007' // This is the default value, so you can omit it. 
           );                
    }

Method 2


   public function actionUserreports_2(){
	$model=new User('search');
	$model->unsetAttributes();  // clear any default values
	if(isset($_GET['User']))
		$model->attributes=$_GET['User'];
       
       $this->toExcel($model->search(),
              array('username','firstname','lastname','email','companyname',
              'User Type'=>'usertype',
              'usersubscription.configdescription',
              'Created By'=>'createdby0.username'
              ),
              'User Reports',
               array( 'creator' => 'bsourcecode',   ),
              'Excel2007' // This is the default value, so you can omit it. 
             );                

    }
?>
  • cyrilm

    Nice tutorial
    But i’m wondering where these excels are saved ?

  • mbala

    When you call this action, excel file will download with data. The download path based on your system.

  • As a Newbie, I’m constantly exploring online for posts that can help me. Thank you

  • viorakis

    Hi,

    I used this extension to create some reports. But I have the following problem: the date fields from the database are saved as text rows (not date) in excel. Can you help me to export date field as date in excel?

    Thanks,

    Viorel

    • mbala

      Hi Viorakis,
      I am also faced this problem. We have to change some code in extension file for this project.

      File :EExcelView
      Function :renderRow($row)
      Try to understand the following codes
      public function renderRow($row)
      {
      ……………………….
      ……………………….
      elseif($column->name!==null) {
      //$value=$data[$row][$column->name];
      $value= CHtml::value($data[$row], $column->name);
      $value=$value===null ? “” : $column->grid->getFormatter()->format($value,’raw’);
      }

      if($column->name==’date’ || $column->name==’modified_date’){
      $value=date(“d-m-Y”,strtotime($value));
      }
      $a++;
      ……………………
      …………………..

  • Muhammad Riza

    hello sir, your tutorial is awesome but this tutorial not work for me. in browser just white screen
    please help me, is there something wrong ?

    this my main.php
    ‘import’=>array(
    ‘application.models.*’,
    ‘application.extensions.*’,
    ‘application.components.*’,
    ‘application.extensions.eexcelview.*’

    this my view/export.php
    widget(//’zii.widgets.grid.CGridView’,
    ‘EExcelView’,array(
    ‘dataProvider’=>$model->search(),
    ‘columns’=>array(
    //’id’,
    array(‘header’=>’No’,’value’=>’++$row’,),
    //’Nilai Kepuasan’,
    array(‘name’=>’message’,’header’=>’Kritik dan Saran’),
    array(‘name’=>’satisfact01.name’,’header’=>’Kepuasan’),
    array(‘name’=>’user01.username’,’header’=>’Customer Service’),
    array(‘name’=>’lokasi’),
    array(‘name’=>’create_date’,’header’=>’Tanggal’,/* ‘value’=>”date(‘dd-MM-yyyy’,CDateTimeParser::parse($data->create_date, ‘dd-MM-yyyy’))”, */
    ‘value’=>’Yii::app()->dateFormatter->format(“dd-MM-yyyy”,$data->create_date)’,
    ),
    array(
    ‘class’=>’CButtonColumn’,

    ),
    ),
    ‘title’=>’Customer Satisfaction’,
    ‘autowidth’=>false,
    ‘template’=>”{summary}n{items}n{exportbuttons}n{pager}”
    )); ?>

    this my method in my controller
    public function behaviors()
    {
    return array (

    ‘eexcelview’=>array(‘class’=>’ext.eexcelview.EExcelBehavior’, ),
    );
    }

    i’m using method 2 in my controller
    public function actionExport()
    {
    $model=new Satisfact(‘search’);
    //$model->unsetAttributes();
    if(isset($_get[‘Satisfact’]))
    $model->attributes=$_GET[‘Satisfact’];
    $this->toExcel($model->search(),
    array(‘id’, ‘satisfy_val’,’message’,’user_id’,’lokasi’,’create_date’,’usersubscription.configdescription’,’User Type’=>’usertype’,
    ),
    ‘Satisfact Report’,
    array(‘creator’=> ‘Follow me @alfarizymr’),
    ‘Excel2007’);
    }

    thank you sir