CGridview and phpexcel

thura747 · September 28, 2012, 10:56am

ClaCS:

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

Hi ClaCS,

Thank you for your solutions. This is very nice code and save my time. but I’ve an another issues.

how to report the relationship data. e.g. I keep users.id in my project tbl as user_id. Now, the report exported the project.user_id only. I want to export the username at report. How can I?
and some of the tbl’s field keep the data by csv or jason code. For those fields are relationship with other tables. how could I export the really data from relative tbl at report.

Regards

Thu Ra

seletar6 · December 7, 2012, 5:08am

ClaCS:

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

Nice solution ClaCS! Thanks a lot.

iwansusanto · August 20, 2013, 9:40am

ClaCS:

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

Good Job

RakhiKasat · December 3, 2013, 10:03am

Hello,

Did u find any solution for this problem ??

I am also facing the same.why xls contain xml data ??

like :

<Table>

<Row>

<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">januar</Data></Cell>

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

thank you.

jetbee94 · July 10, 2014, 5:49am

How can u set the width for each column using this extension?

SujithPD · July 10, 2019, 7:47am

Hi Sir,
Your code is excellent. But i have an help with you. how can add relation data to this excel file when exporting?
for example; I stored a staff_id on table and it have a relation on this model. how can i convert this staff_id in to his name using this relation.

Is this possible ?

rackycz · September 26, 2019, 8:31am

Hi. Have you seen my tutorial? I did something similar few years ago.
https://www.yiiframework.com/wiki/462/yii-for-beginners-2
… search for “excel” word