Problem Exporting Data From Cgridview To Excel

I implemented the toexcel / phpexcel / eexcelview packages and got a simple menu item I can include in all "admin" views to export data.

It works great except…just found one problem.

One column contained a website url that used ‘-’ in the url. eg( the-web-site.com ) when exported the data was

formatted as "site.com/web/the"

Any clues?

I tried to run it with CSV filter and that produced the same results…so it is a transformation occuring early in the process.

Hi there,

I don’t have data with url so I just added a column with static value “the-website.com” in my columns:


        array(

            'header' => 'url',

            'value' => '"the-web-site.com"',

        ),

And the export is just fine (at least with tlbExcelView that is based anyway on EExcelView). Can you paste your view code?

Hi Bennouna,

Thanks for taking the time to reply and look into this.

I am not the only person with this problem, I saw a comment posted the other day under the extension "toexcel".





karmraj at 2012/12/07 04:33am

Issue in Datetime DataType field Issue in Excel


Hello Community,


I have an issue export data in excel. Whenever i trying to export datetime field record like : 2012-10-12 17:10:10, i am getting this field value like this : 12 17:10:10 /11/2012 .


I think there is an issue in change the format of excel column. Please help me to solve this issue.


Thanks in Advance .



Here is my code from the "admin" form that uses CGridView




<?php

$this->breadcrumbs = array(

    Yii::t('app', 'Websites') => array('admin'),

    Yii::t('app', 'Admin'),

);

if(!isset($this->menu) || $this->menu === array())

$this->menu=array(

array('label'=>Yii::t('app', 'Create') , 'url'=>array('create')),

array('label'=>Yii::t('app', 'Export to Excel') , 'url'=>array('export','format'=>'Excel2007')),

//array('label'=>Yii::t('app', 'Export to PDF') , 'url'=>array('export','format'=>'CSV')),


);




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

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

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

return false;

});

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

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

data: $(this).serialize()

});

return false;

});

");

?>


<h1> <?php echo Yii::t('app', 'Manage'); ?> <?php echo Yii::t('app', 'Websites'); ?> </h1>


<?php echo CHtml::link(Yii::t('app', 'Advanced Search'),'#',array('class'=>'search-button')); ?><div class="search-form" style="display: none">

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

    'model'=>$model,

)); ?>

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

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

	'id' => 'website-grid',

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

	'filter' => $model,

	'columns' => array(

		array(

			'class' => 'CButtonColumn',

		),

       // 'id',

       // 'create_date',	

        array(

                'name'   => 'publisher_id',

                'value'  => 'isset($data->publisher->publisher_name)?$data->publisher->publisher_name:"N/A"',

                'filter' => CHtml::listData(Publisher::model()->findAll(),'id','publisher_name'),

                ),

		'site_name',

	[b]array( 	'name' => 'site_url',

		'type' => 'url',				

		),[/b]

	array(

                'class' => 'JToggleColumn',

		'name' => 'mobile_enabled',

		'filter' => array('0' => Yii::t('app', 'No'), '1' => Yii::t('app', 'Yes')),

                'model' => get_class($model),

                'htmlOptions' => array('style' => 'text-align:center;min-width:60px;')

		),

	array(

                'class' => 'JToggleColumn',

		'name' => 'problem',

		'filter' => array('0' => Yii::t('app', 'No'), '1' => Yii::t('app', 'Yes')),

                'model' => get_class($model),

                'htmlOptions' => array('style' => 'text-align:center;min-width:60px;')

		),

        array(

                	'name'   => 'leadowner_id',

                      'value'  => 'isset($data->leadowner->username)?$data->leadowner->username:"N/A"',

                      'filter' => CHtml::listData(User::model()->findAll(),'id','username'),

                ),

        array(

                	'name'   => 'network_coord_id',

                      'value'  => 'isset($data->networkCoord->username)?$data->networkCoord->username:"N/A"',

                      'filter' => CHtml::listData(User::model()->findAll(),'id','username'),

                ),

      

        array(

              'class' => 'JToggleColumn',

		'name' => 'active',

		'filter' => array('0' => Yii::t('app', 'No'), '1' => Yii::t('app', 'Yes')),

                'model' => get_class($model),

                'htmlOptions' => array('style' => 'text-align:center;min-width:60px;')

		),

        array(

                	'name'   => 'status_id',

                      'value'  => 'isset($data->status->id)?$data->status->id:"N/A"',

                      'filter' => CHtml::listData(WebsiteStatus::model()->findAll(),'id','id'),

                ),

        array(

                	'name'   => 'quality_id',

                      'value'  => 'isset($data->quality->name)?$data->quality->name:"N/A"',

                      'filter' => CHtml::listData(WebsiteQuality::model()->findAll(),'id','name'),

                ),

        array(

                	'name'   => 'category_id',

                      'value'  => 'isset($data->category->category)?$data->category->category:"N/A"',

                      'filter' => CHtml::listData(Category::model()->findAll(),'id','category'),

                ),

        'registered_date',

       

	),

)); ?>



Thanks again for your assistance. I also had a problem generating a PDF…CSV and EXCEL were fine. Something was missing

on my system, I need to go back and look into it.

Bennouna, I can’t take credit, this is from Karmraj…

I think he found something relevant…date translation function that is causing the problem.

@AustinGeek


//date edited francis 

…

//end of date



I don’t find that code in the EExcelView extension in its 0.33 version officially released by phreak, it’s only in a modification proposed in the forum.

I am not sure why that modified code would be necessary. Personally I don’t have any problems exporting urls or formatted dates / datetimes with the standard extension (since it’s what I’ve based my modifications upon).