CGridview and phpexcel

Yii 1.1 General Discussion
1

Hi…

Does anyone can give me an example how to export data from dataProvider CGridView using phpexcel, thank you

My Controller




public function actionAdmin()

	{	


		$model=new Department('search');

				

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

		{

			$dataProvider= $model->search();

			$dataProvider->pagination= false; // for retrive all modules

            $data = $dataProvider->data();

            foreach ($data as $model)

            {

                $dudul = array($model);

            }

			$xls = new JPhpExcel('UTF-8', false, 'test');

			$xls->addArray($dudul);

			$xls->generateXML('my-test');


		}

		

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

			'model'=>$model,

		));

	}

My View




<?php $eek=$this->beginWidget('CActiveForm', array(

	'action'=>Yii::app()->createUrl($this->route),

	'method'=>'get',

)); ?>

<div class="row buttons">

		<?php echo CHtml::submitButton('export'); ?>

</div>

<?php $this->endWidget(); ?>


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

	'id'=>'department-grid',

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

	'filter'=>$model,

	'columns'=>array(

		array(

            'header'=>'No',

            'value'=>'$this->grid->dataProvider->pagination->currentPage*$this->grid->dataProvider->pagination->pageSize+$row+1'

        ),

		'department_name',

		'employee',

		array(

			'class'=>'CButtonColumn',

			

		),

	),

)); ?>

the output is




CException

Description


CActiveDataProvider does not have a method named "data".

Source File


C:\xampp\htdocs\yii\framework\base\CComponent.php(266)


00254:     public function __call($name,$parameters)

00255:     {

00256:         if($this->_m!==null)

00257:         {

00258:             foreach($this->_m as $object)

00259:             {

00260:                 if($object->getEnabled() && method_exists($object,$name))

00261:                     return call_user_func_array(array($object,$name),$parameters);

00262:             }

00263:         }

00264:         if(class_exists('Closure', false) && $this->$name instanceof Closure)

00265:             return call_user_func_array($this->$name, $parameters);

00266: throw new CException(Yii::t('yii','{class} does not have a method named "{name}".',

00267:             array('{class}'=>get_class($this), '{name}'=>$name)));

00268:     }

00269: 

00270:     /**

00271:      * Returns the named behavior object.

00272:      * The name 'asa' stands for 'as a'.

00273:      * @param string the behavior name

00274:      * @return IBehavior the behavior object, or null if the behavior does not exist

00275:      * @since 1.0.2

00276:      */

00277:     public function asa($behavior)

00278:     {


Stack Trace


#0 [internal function]: CComponent->__call('data', Array)

#1 C:\xampp\htdocs\diklat\protected\controllers\DepartmentController.php(175): CActiveDataProvider->data()

#2 C:\xampp\htdocs\yii\framework\web\actions\CInlineAction.php(50): DepartmentController->actionAdmin()

#3 C:\xampp\htdocs\yii\framework\web\CController.php(300): CInlineAction->run()

#4 C:\xampp\htdocs\yii\framework\web\filters\CFilterChain.php(133): CController->runAction(Object(CInlineAction))

#5 C:\xampp\htdocs\yii\framework\web\filters\CFilter.php(41): CFilterChain->run()

#6 C:\xampp\htdocs\yii\framework\web\CController.php(1049): CFilter->filter(Object(CFilterChain))

#7 C:\xampp\htdocs\yii\framework\web\filters\CInlineFilter.php(59): CController->filterAccessControl(Object(CFilterChain))

#8 C:\xampp\htdocs\yii\framework\web\filters\CFilterChain.php(130): CInlineFilter->filter(Object(CFilterChain))

#9 C:\xampp\htdocs\yii\framework\web\CController.php(283): CFilterChain->run()

#10 C:\xampp\htdocs\yii\framework\web\CController.php(257): CController->runActionWithFilters(Object(CInlineAction), Array)

#11 C:\xampp\htdocs\yii\framework\web\CWebApplication.php(324): CController->run('admin')

#12 C:\xampp\htdocs\yii\framework\web\CWebApplication.php(121): CWebApplication->runController('Department...')

#13 C:\xampp\htdocs\yii\framework\base\CApplication.php(135): CWebApplication->processRequest()

#14 C:\xampp\htdocs\diklat\index.php(13): CApplication->run()

#15 {main}
2

$dataProvider->data() → $dataProvider->data

3

Dear Samdark,

Thank you very much, it’s work but another problem comes out :)

I ony get the last row and the result is not the same with CGridView (I have relation tables) :(

Can you suggest me the better solution than I had made …?

4

Shame on me ;D

$dudul = array($model) --> $dudul[] = $model , now I got all rows

But still problem with the relation :(

5

hi!

Do you solve your problem?? Your gridview has a pagination and/or filter?? Because the code exports all gridview to excel

6

bega your code was realy nice, i have been searching alot and your post is the most usefull information that I find. but i have problems implementing by my own please help.

bega your code was realy nice, i have been searching alot and your post is the most usefull information that I could find. but i have problems implementing by my own. please help.

this is my controller. just the actionAdmin




	/**

 	* Manages all models.

 	*/

	public function actionAdmin()

	{

		$model=new Internacional('search');

		$model->unsetAttributes();  // clear any default values

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

		{

			$model->attributes=$_GET['Internacional']; // why do you use yt1 instead of Department?

                    	$dataProvider->pagination= false; // for retrive all modules

			$data = $dataProvider->data;

			foreach ($data as $model)

			{

				$dudul[] = $mode;

			}

                    	$xls = new JPhpExcel('UTF-8', false, 'test');

                    	$xls->addArray($dudul);

                    	$xls->generateXML('my-test');


            	}




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

			'model'=>$model,

		));

	}

and this is my full view




<?php

$this->breadcrumbs=array(

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

	'Manage',

);


$this->menu=array(

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

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

	array('label'=>'Exportar y descargar', 'url'=>array('excel'))

);


Yii::app()->clientScript->registerScript('search', "

$('.search-button').click(function(){

	$('.search-form').toggle();

	return false;

});

$('.search-form form').submit(function(){

	$.fn.yiiGridView.update('internacional-grid', {

		data: $(this).serialize()

	});

	return false;

});

");

?>


<h2 class="art-postmetadataheader2">Manage Internacionals</h2>


<p>

You may optionally enter a comparison operator (<b>&lt;</b>, <b>&lt;=</b>, <b>&gt;</b>, <b>&gt;=</b>, <b>&lt;&gt;</b>

or <b>=</b>) at the beginning of each of your search values to specify how the comparison should be done.

</p>


<?php echo CHtml::link('Advanced Search','#',array('class'=>'search-button')); ?>

<div class="search-form" style="display:none">

<?php $this->renderPartial('_search',array(

	'model'=>$model,

)); ?>

</div><!-- search-form -->


<?php $eek=$this->beginWidget('CActiveForm', array(

    	'action'=>Yii::app()->createUrl($this->route), //why this ->route?  :-S

    	'method'=>'get',

)); ?>

<div class="row buttons">

            	<?php echo CHtml::submitButton('export'); ?>

</div>

<?php $this->endWidget(); ?>


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

	'id'=>'internacional-grid',

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

	'filter'=>$model,

	'columns'=>array(

		'id_internacional',

		'num_confirmacion',

		'pais',

		'cargo',

		'nombres',

		'apellidos',

		/*

		'conyuge',

		'sexo',

		'no_pasaporte',

		'tipo_pasaporte',

		'fecha_nacimiento',

		'foto',

		'email',

		'comitiva',

		'arribo_fecha',

		'arribo_vuelo',

		'arribo_hora',

		'arribo_aerolinea',

		'hotel',

		'partida_fecha',

		'partida_vuelo',

		'partida_hora',

		'partida_aerolinea',

		'llenado_por',

		'control',

		'control_i',

		'control_ip',

		'usuario_a',

		*/

		array(

			'class'=>'CButtonColumn',

		),

	),

)); ?>
7

Hi Clacs,

Did u manage to do the export? I tried the same code as bega and i too got the entire gridview exported. I want to export just the filtered values. Do u know how to do this?

thanks in advance

8

Hi! Yes, I did

I used the phpexcel extension and a $_SESSION var to pass the filtered data

controller.php




public function actionExcel() {


        $d = $_SESSION['Lectivo-excel'];


        $data = array();


        $data[]=array_keys($d->data[0]->attributes);//headers: cols name


        foreach ($d->data as $item) {

            $data[] = $item->attributes;

        }


        Yii::import('application.extensions.phpexcel.JPhpExcel');

        $xls = new JPhpExcel('UTF-8', false, 'test');

        $xls->addArray($data);

        $xls->generateXML('filename'); //export into a .xls file

    }

model.php modify the search function




public function search()

	{

		$criteria=new CDbCriteria;


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

		$criteria->compare('nombre',$this->nombre,true);

		$criteria->compare('abreviatura',$this->abreviatura,true);

		$criteria->compare('fec_inicio',$this->fec_inicio,true);

		$criteria->compare('fec_fin',$this->fec_fin,true);

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


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

                        'pagination'=>array('pageSize'=> Yii::app()->user->getState('pageSize',

                                                                        Yii::app()->params['defaultPageSize']),),

			'criteria'=>$criteria,

		));


        $_SESSION['Lectivo-excel']=$data; // get all data and filtered data <img src='http://www.yiiframework.com/forum/public/style_emoticons/default/smile.gif' class='bbc_emoticon' alt=':)' />


        return $data;

	}

view.php




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

                       'encodeLabel'=>false,

                       'htmlOptions'=>array(

                           'class'=>'actions'),

                           'items'=>array(

                                        ...,


                                         array(

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

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

                                        ),


                                       ... ,

                                        ),

                                        )

                ));

?></div></div>

copy the file attached into your extensions folder

I hope to help you

if somebody find a best way to do this … plz share it ;)

regards!

1727

phpexcel.zip

9

Thanku very very very very much clacs…:) you’re code helped a lot… it works beautifully!!! :) :)

10

Clacs there is jus one more help i need from u. The thing is in my cgrid i dont have a ll the fields displayed from the db. But when i export to excel all the fields from the db get exported. can u please tell me how i can choose the fields from the db which i want to be exported.

Thanks again. :)

11

Hey clacs l managed to do that on my own.I just had to change the query in the model.

but now there is another problem, i have used relations for my grd, can u tell me how to incorporate these relations in my excel export?

Example: in my grid product_names get displayed but in my excel product_id is displayed. How do i display the product_names in the excel?

Thanks

12

Thanks ClacS. $_SESSION trick worked wonders for exporting filtered data only.

@Bega -> This is what i did to download/export relations -




$issueDataProvider = $_SESSION['Filtered_Excel'];

$i = 0;

        

        //fix column header. 

        //Could have used something like this - $data[]=array_keys($issueDataProvider->data[0]->attributes);. 

        //But that would return all attributes which i do not want

        $data[$i]['issue_id'] = 'Issue Id';

        $data[$i]['summary'] = 'Summary';

        $data[$i]['type'] = 'Issue Type';

        $data[$i]['status'] = 'Status';

        $data[$i]['priority'] = 'Priority';

        $data[$i]['assigned_to'] = 'Assigned To';

        $i++;

        

        //populate data array with the required data elements

        foreach($issueDataProvider->data as $issue)

        {

            $data[$i]['issue_id'] = $issue['id'];

            $data[$i]['summary'] = $issue['summary'];

            $data[$i]['type'] = $issue->getTypeText();

            $data[$i]['status'] = $issue->getStatusText();

            $data[$i]['priority'] = $issue['priority'];

            $data[$i]['assigned_to'] = $issue->assigned_to->username;

            $i++;

        }


        $xls = new JPhpExcel('UTF-8', false, 'test');

        $xls->addArray($data);

        $xls->generateXML('test_file');
13

why export to excel only meta data

14

Guys,

I have this issue.

i work this script on a linux platform. and after the file I save in my linux desktop and i tried to open it with the open office calc.

the contain of the file that created is changing into form xml.

sorry for my stupidity. but anyone care to explain to me. what went wrong?

thank You so much.

for example:

<?xml version="1.0" encoding="UTF-8"?>

<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40">

<Worksheet ss:Name="test">

<Table>

<Row>

<Cell><Data ss:Type="String">No</Data></Cell>

<Cell><Data ss:Type="String">User Id</Data></Cell>

<Cell><Data ss:Type="String">User Name</Data></Cell>

<Cell><Data ss:Type="String">User Alias</Data></Cell>

<Cell><Data ss:Type="String">User Password</Data></Cell>

<Cell><Data ss:Type="String">Role Description</Data></Cell>

<Cell><Data ss:Type="String">User Status</Data></Cell>

<Cell><Data ss:Type="String">Date Created</Data></Cell>

<Cell><Data ss:Type="String">Last Updated date</Data></Cell>

</Row>

<Row>

<Cell><Data ss:Type="String">1</Data></Cell>

<Cell><Data ss:Type="String">1</Data></Cell>

<Cell><Data ss:Type="String">januar</Data></Cell>

<Cell><Data ss:Type="String">ID - xxxx - xxxx</Data></Cell>

<Cell><Data ss:Type="String">xxxxxxxx</Data></Cell>

thank you so much in advance.

15

the same happen to me. so i work with ms excel, i don’t know if works with 2003 but with 2007 works fine. i think that calc do not suport the format.

16 

Yii::import('application.extensions.phpexcel.JPhpExcel');

export to pdf?

Thank!

17

this is amazing, thank you so much, i was looking for this for a long while,i mean writing filtered data to excel . This worked perfect for me!

18

i Follow this command and but it say undefined variable : Filtered_Excel

What parameter that i must enter to substitute this Filtered_Excel ?

Is there any setting for the model ?

Thanks for your help

19

I already have the solution… I get it from video tutorial in espanol language from gustalh

http://www.youtube.com/watch?v=JiETkyuJk7Y

I already post it to my blog too : http://www.dedyisn.net/scripting/make-report-of-database-to-excel-on-yii-framework/

20

Hello guys

I’m using this fantastic solution to export a cgridview, but I wanted to ask … As you can hide columns in excel?

I would not want all the columns of the table but only some, is that possible?

Thanks for your help and sorry for my English :)

Ciao Giuseppe