[EXTENSION]eexcelview

I am opening an extension by request for my first widget :rolleyes:

http://www.yiiframework.com/extension/eexcelview/

About the question:

As this extension is not realy a view, it does not need to be in a view file. You could(or you’d better) put its code in the controller class. So when you display a gridview which you want to save to a xls file with a button, you could call the controller and in its action you can create EExcelView class and it will call the save file dialog with action.xls (filename will be the action name).

For example(in controller):


public function actionTest($export = false)

{     

  if($export)

  {

    $this->widget('EExcelView', array(

       'dataProvider'=> new CActiveDataProvider(NetClientType),

    ));

    Yii::app()->end();

  } else

  $this->render('view', compact('params'....));

}



with this code you could achieve what you want by adding a link with parameter ‘export’=>true.

I will soon add new features to the class including specifying filename of the downloaded file.

Hi

Thnx for opening a post to the extension ;) Always is necesary

Oh I see … but when I saw this code




$this->widget('EExcelView', array(

     'dataProvider'=> $dataprovider,

     'title'=>'Title',

     'autoWidth'=>false,

      ..... other options 

));

I thought it will be in a view. Until now, I didn’t know that the code ( $this->widget(…) ) could be on a controller :huh:

so …

I’ve adapted the code but I get the following error




YiiBase::include(NetClientType.php) [<a href='function.YiiBase-include'>function.YiiBase-include</a>]: failed to open stream: No such file or directory



I guess… I have to pass something like $model->search() in the dataProvider property

How Can I pass the dataprovider of the grid to the action to export the data??

is necessary render??


$this->render('view', compact('params'....));



sorry … what does mean the ‘compact’ parameter??

Regards

you dont need to render a view, since you just create a file and send it to the user.

I dont know why you get that error, may be because of autoloading conflict.

Show me the code of your controller/action so i can help you.

btw compact(‘param’) i just a way of writing array(‘param’=>$param)

It’s realy quite a basic ext still, so it needs more work :) but thanks for using it.

…my code :)

view





<?php    $this->widget('zii.widgets.CMenu', array(

                       'encodeLabel'=>false,

                       'htmlOptions'=>array(

                           'class'=>'actions'),

                           'items'=>array(

                                        array(

                                           'label'=>'<img align="absmiddle" alt = "'.Yii::t('internationalization','Create'). '" src = "'.bUrl().'/images/icons/application_add.png" />'. ' ' . Yii::t('internationalization', 'Create'),

                                           'url'=>array('Lectivo/create'),

                                        ),

                                        array(

                                           'label'=>'<img align="absmiddle" alt = "'.Yii::t('internationalization','Excel'). '" src = "'.bUrl().'/images/icons/disk.png" />'. ' ' . Yii::t('internationalization', 'Excel'),

                                           'url'=>array('Lectivo/toExcel'),

                                        ),

                                        array(

                                        )

                ));

?>


<?php


$pageSize=Yii::app()->user->getState('pageSize',Yii::app()->params['defaultPageSize']);

             

    $this->widget('zii.widgets.grid.CGridView', array(

	'id'=>'lectivo-grid',

	'dataProvider'=>$model->search(),

        'cssFile'=> bUrl().'/css/gridview/gridview.css',

        'filter'=>$model,

        'pager' => array(

                'nextPageLabel' => 'Siguiente',

                'prevPageLabel' => 'Anterior',

                'firstPageLabel' => 'Primero',

                'lastPageLabel' => 'Último',

                'header' => '',

                'maxButtonCount' => 5,

                'cssFile' => bUrl().'/css/pagination/pagination.css'

        ),

	    'columns'=>array(

        

        array(

            'class'=>'CCheckBoxColumn',

        ),

		

        array(

                        'name' => 'id_lectivo',

                        'header' => 'ID',

                        'filter'=>false,

                        'htmlOptions'=>array('align'=>'center')

        ),

        

		'nombre',

		'abreviatura',

		

        array(

                        'name' => 'fec_inicio',

                        'value' => 'UtilFechas::aFechaNormal($data->fec_inicio)',

                        'htmlOptions'=>array('align'=>'center','style'=>'width: 103px;'),

                        'filter'=>$this->widget('zii.widgets.jui.CJuiDatepicker', array(

                            'model'=>$model,

                            'attribute'=>'fec_inicio',

                            'language'=>'es',

                            'htmlOptions'=>array('style'=>'width: 80px;'),

                            'options' => array(

                                'dateFormat' => 'yy-mm-dd',

                                'changeYear' => true,

                                'showOn' => 'button',

                                'buttonImage' => bUrl() . '/images/icons/calendar.png',

                                'buttonImageOnly' => true

                            )

                        ), true),

        ),

		

        array(

                        'name' => 'fec_fin',

                        'value' => 'UtilFechas::aFechaNormal($data->fec_fin)',

                        'htmlOptions'=>array('align'=>'center','style'=>'width: 103px;'),

                        'filter'=>$this->widget('zii.widgets.jui.CJuiDatepicker', array(

                            'model'=>$model,

                            'attribute'=>'fec_fin',

                            'language'=>'es',

                            'htmlOptions'=>array('style'=>'width: 80px;'),

                            'options' => array(

                                'dateFormat' => 'yy-mm-dd',

                                'changeYear' => true,

                                'showOn' => 'button',

                                'buttonImage' => bUrl() . '/images/icons/calendar.png',

                                'buttonImageOnly' => true

                            )

                        ), true),

        ),

		

        array(

                        'name' => 'estado',

                        'value' => '$data->estado',

                        'htmlOptions'=>array('align'=>'center'),

                        'filter'=>array('1'=>1,'0'=>0),

        ),


        array(

                'class'=>'CButtonColumn',

                'header'=>CHtml::dropDownList('pageSize',

                    $pageSize,

                    array(10=>10,20=>20,50=>50),

                    array('onchange'=>"$.fn.yiiGridView.update('lectivo-grid',{ data:{pageSize: $(this).val() }})",)

                    ),

        ),

	),

    'afterAjaxUpdate'=>'function(){

    	jQuery("#'.CHtml::activeId($model, 'fec_inicio').'").datepicker({

                    dateFormat: \'yy-mm-dd\',

                    changeYear: true,

                    showOn: \'button\',

                    buttonImage:\''.bUrl().'/images/icons/calendar.png\',

                    buttonImageOnly: true

               });$.datepicker.setDefaults($.datepicker.regional["es"]);

		jQuery("#'.CHtml::activeId($model, 'fec_fin').'").datepicker({

                    dateFormat: \'yy-mm-dd\',

                    changeYear: true,

                    showOn: \'button\',

                    buttonImage:\''.bUrl().'/images/icons/calendar.png\',

                    buttonImageOnly: true

               });$.datepicker.setDefaults($.datepicker.regional["es"]);

		    }',

));


    ?>



this code is generated by gii-CRUD generator and I add some modifications The dataprovider of the grid is $model->search() for the filter

Controller


public function actiontoExcel() {


        $this->widget('application.extensions.EExcelView', array(

           'dataProvider'=> $DATA

        ));

        Yii::app()->end();

}

In this case how to pass the $DATA value from the view to export it to excel??

and I get the error


Fatal error: Call to a member function search() on a non-object in C:\wamp\www\kelvin\protected\controllers\LectivoController.php on line 72

I try in the controler with




  $model=new Lectivo('search');


       $this->widget('application.extensions.EExcelView', array(

       'dataProvider'=> $model->search(),

        ));

        Yii::app()->end();



and the excel file exported not show the Grid, only the page without js and css

I’m still without export to excel :(

How can I fix this??

regards

I guess there is a small bug in the extension.

In EExcelView.php Line 92:




$objWriter = PHPExcel_IOFactory::createWriter($this->objPHPExcel, $this->exportType);

if($this->filename)

	$objWriter->save($filename);//calling local variable filename

else //output to browser

{

	header($this->mimeTypes[$this->exportType]);

	$objWriter->save('php://output');			

}




It checks if "$this->filename" is defined but uses $filename for function call. This results in exception throw when called since $filename is null.

Yes it’s a bug :) 10x for pointing that out. I’m currently really busy, but soon will make some changes to the extension.

This extension assumes that the number of columns will be less than 27.

I wrote a function to remove this assumption.

Following are the changes I made:

In EExcelView.php, Replace all occurences of chr(ord(‘A’)+$n) with $this->columnName($n+1)

Add the following function to the class EExcelView in EExcelView.php

public function columnName($index)

{

--&#036;index;


if(&#036;index &gt;= 0 &amp;&amp; &#036;index &lt; 26)


{


	return chr(ord('A')+&#036;index);


}


else if (&#036;index &gt; 25)


{


	return (&#036;this-&gt;columnName(&#036;index/26)).(&#036;this-&gt;columnName(&#036;index%26+1));


}


else


{


	throw new Exception(&quot;Invalid Column # &quot;.(&#036;index+1));


}

}

10x Abdul, I will add you function to the class, and will include it in the next release :)

Also, while streaming to browser, name of generated file defaults to name of the action.

I would suggest adding a parameter to set name of the generated file (I have used the parameter "title" in the following example)

This can be done by adding

header(‘Content-Disposition:attachment; filename="’.$this->title.’"’);

after

header($this->mimeTypes[$this->exportType]);

As I said in the comments of the extension, here is a little modified class of yours

Added support for CSV and the filename to download is defined by the title of the application, if set, or the pagetitle

Also fixed a bug about PDF and HTML that I said also in the comments

Hope it helps to improve it a bit

By the way, great extension

Cheers

Gustavo

As i said before

I’m explaining what i did for ‘improve’ this extension. (I hope someone find this useful. :rolleyes: )

Please find attached the modified PHP file. This one includes what Abdul Rehman and Gustavo pointed out.

In the admin.php view:




<?php

$this->breadcrumbs=array(

	'Cantones'=>array('index'),

	'Manage',

);

// This array contains all the fields that WON'T be exported. I.e: Primary Keys and so on.

$items = array('canton_id');

$serialized = rawurlencode(serialize($items));

$this->menu=array(

	array('label'=>'List', 'url'=>array('index')),

	array('label'=>'Crete', 'url'=>array('create')),

	array('label'=>'Export to Excel', 'url'=>array('excel',

						    'title'=>'Cantones',

						    'model'=>'Canton',

						    'items'=>$serialized,))

);


...




In the ModelController:





...


public function accessRules()

	{

		

                ...


		'actions'=>array('excel'),

				'users'=>array('*'),

			),

			

                ...

	

        }

public function actionExcel($title,$model,$items)

	{     

		$this->widget('application.extensions.phpexcel.EExcelView', array(

	       'dataProvider'=> new CActiveDataProvider($model,array('pagination'=>false)),

	       'title'=>$title,

	 	   'model'=>$model,

	 	   'items'=>$items,

	 	));

	    Yii::app()->end();

	}


...




I guess that what I’ve said so far does not required any explanation, the following does. If I’m wrong, please let me know.

As we don’t want to export foreign keys but human readable information, I’m including functions (one per each foreign key in my current model) to retrieve the latter value:




public function getProvince_id($province_id){

		$data = Province::model()->findByPK($province_id);

		return $data->name;

	}



This is being validated here (rederRow() function):





...

// You don't want to copy this.. It's part of the attached file.

$model = new $this->model;

$method = 'get'.$column->name;

if(method_exists($model,'get'.$column->name))

	$value = $model->$method($data[$row][$column->name]);

else 

	$value = $data[$row][$column->name];


...




This last section might be conceptually wrong but it works…

I’m super newbie in yii as well so if you can improve this extension, please do it and let the community know.

cheers!

tried the last version and code and i am getting this error: Fatal error: Class ‘CException’ not found in /Users/mikaelbortenheim/Sites/PHP/bentsen.client.sysedata.no/yii1.1.7-26apr-dev/framework/YiiBase.php on line 311….

Hello

I made a new modification

incorporate relationships

the code is adapted from code zii

hopefully useful

how to use:

controller:




	/*excel*/

	public function actionStockActualExcel()

	{


        	$criteria= $_SESSION['IngresoDetalles-criteria'];

        	$sort=$_SESSION['IngresoDetalles-sort'];

        	$model=$_SESSION['IngresoDetalles-clase'];

        	$columnas=$_SESSION['IngresoDetalles-columnas'];




     	$this->widget('application.extensions.EExcelView.EExcelView',

                    	array(

                    	'dataProvider'=> new CActiveDataProvider($model,array('pagination'=>false,'criteria'=>$criteria,'sort'=>$sort,)),

                    	'columns'=> $columnas

                    	)

                	);

     	Yii::app()->end();

	}

	/*view*/

	public function actionStockActual()

	{

		$detalle=new IngresoDetalles('search');


		if(isset($_GET['IngresoDetalles']))

			$detalle->attributes=$_GET['IngresoDetalles'];


            	/*columns to show*/

            	$columnas= array('idIngresoDetalle',

                              	array(  'name'=>'idIngreso', 

                                      	'value'=>'$data->ingreso->programa->nombre',

                                      	'sortable'=>'true',

                                      	'header'=>'Programa',),

                              	array(  'name'=>'idProducto',

                                      	'value'=>'$data->producto->nombre',

                                      	'sortable'=>'true',

                                      	'header'=>'Producto',),

                             	'fecha_vto',

                             	'lote',

                             	'cantidad:raw:Cantidad Inicial',

                             	'cantidad_actual',	);


            	/*use in actionStockActualExcel*/

            	$_SESSION['IngresoDetalles-columnas']=$columnas;


		$this->render('stockActual',array(

                        	'detalle'=>$detalle,

                        	'columnas'=>$columnas

		));

	}




model IngresoDetalles.php





	public function search()

	{

		/*se usa en Stock Actual*/

            	$criteria=new CDbCriteria;

            	$sort=new CSort();

            	$sort->defaultOrder='producto.nombre';

            	

            	$criteria->with=array('ingreso.programa','producto');


	           $criteria->compare('idIngresoDetalle',$this->idIngresoDetalle);

           	.................


		$data= new CActiveDataProvider(get_class($this), array(

			'criteria'=>$criteria,

                    	'sort'=>$sort,

		));


            	/*excel, see in controller actionStockActualExcel*/

            	$_SESSION['IngresoDetalles-criteria']=$criteria;

            	$_SESSION['IngresoDetalles-sort']=$sort;

            	$_SESSION['IngresoDetalles-clase']=get_class($this);


            	return $data;




view stockActual.php




<?php

        	$this->menu=array(  array('label'=>'Export to Excel',  'url'=>array('stockActualExcel')), );


/* add  buttons to Columns*/

$columnas[]=array('class'=>'CButtonColumn');


$this->widget('zii.widgets.grid.CGridView', array(

	'id'=>'detalle-grid',

	'dataProvider'=>$detalle->search(),

	'filter'=>$detalle,

 	'columns'=> $columnas,

)); ?>




Just released new version with all your suggestions and some bug fixes. 10x to all :rolleyes:

I have a fatal error!

Friends,

It is possible to make this extension to create the report using a SQL function with, for example:




    public function getKmParcialAcumulado() {

        $veiculo = $this->veiculo;

        $sql = "SELECT kmparc FROM abastecimento

                WHERE veiculo = {$veiculo}

                ORDER BY kmparc DESC LIMIT 0,1";

        $command = Yii::app()->getDb()->createCommand($sql);

        return $command->queryScalar();

    }



I wish he did the query for this query and not the gridview. Can I do this extension, or is there another that can do this.

Thanks,

Adriano Silva

Hello,

I am new to Yii but I am trying to implement this awesome extension!Thank you for contributing it!

However I am trying to use it and I can’t seem to make it work…

I have tried to use the comments in this forum and this is what I have done:

I have added in the protected\views\employee\admin.php of the default application this code




$items = serialize(array('id','departmentId','firstName','lastName','email','ext',));

$this->menu=array(

	array('label'=>'Index Employees'), 'url'=>array('index')),

	array('label'=>'Create Employee'), 'url'=>array('create')),

	array('label'=>'Export to Excel','url'=>array('excel','title'=>'export','model'=>'Employee','items'=>$items ,)),

);



In the accessRules of protected\controllers\EmployeeController.php , I edited the array


array('allow', // allow admin user to perform 'admin' and 'delete' and 'excel' actions

				'actions'=>array('admin','delete','excel'),

				'users'=>array('@'),

			),

and added the actionExcel in the same file




public function actionExcel($title,$model,$items)

		{

			$this->widget('application.extensions.phpexcel.EExcelView' , array(

			'dataProvider' =>  new CActiveDataProvider ($model , array('pagination'=>false)),

			'title'=>$title,

			'model'=>$model,

			'items'=>$items,

			));

	Yii::app()->end();

		}



What do I have to do so that it does not return the CException ‘Property “EExcelView.model” is not defined.’?

Thank you for the help

@GIUNz




public function actionExcel($title,$model,$items)

                {

                        $this->widget('application.extensions.phpexcel.EExcelView' , array(

                        'dataProvider' =>  new CActiveDataProvider ($model , array('pagination'=>false)),

                        'title'=>$title,

                       

                        'items'=>$items,

                        ));

        Yii::app()->end();

                }



Please remove "model" from your code and your error will solve. like above code…

Fatal error: Class ‘CException’ not found in i am getting this error… i have also downloaded PHPExcel class from phpexcel site but still it shows fatal error… any suggestion?

Hi,

I tried to install and use this extension on my localhost, it’s working fine. But when I upload the script and use it on the server I got this following error.




PHP Error


ob_end_clean(): failed to delete buffer. No buffer to delete. 


139                 ob_end_clean();

140                 header('Cache-Control: must-revalidate, post-check=0, pre-check=0');

141                 header('Pragma: public');

142                 header('Content-type: '.$this->mimeTypes[$this->exportType]['Content-type']);

143                 header('Content-Dis



If i try to add @ infront of ob_end_clean();




@ob_end_clean;



or performing a check before doing ob_end_clean:




if (ob_get_length() > 0) {

    ob_end_clean();

  }



the PHP error is gone but the controller is not working, and I receive this error on my browser:




File not found

      

          Firefox can't find the file at hxxp://website-name.com/admin/product/export.

      

  Check the file name for capitalization or other typing errors.

  Check to see if the file was moved, renamed or deleted.



what’s weird is the controller is there, and If I delete the widget call, the controller is working.

Does anybody encounter this issue or can advice what should I do?

It’s working ok on my local install, does it related to server settings?

thanks