Yii 1.1: tlbexcelview

Export to Excel using PHPExcel with automatic formatting, based on EExcelView
22 followers

Another Yii CGridView-to-Excel exporter using PHPExcel, based on EExcelView

Tested with:

  • Yii 1.1.10 -> Yii 1.1.13
  • PHPExcel 1.7.7

Github repository: https://github.com/tellibus/tlbExcelView

Versions

  • Version 1.0.
  • Version 1.1: Adds support for RTL export (leverages setRightToLeft flag)

Installation

  • Create a folder named phpexcel in your /protected/extensions folder
  • Download PHPExcel from http://phpexcel.codeplex.com/
  • Unpack its Classes folder in your /protected/extensions/phpexcel
  • Copy the tlbExcelView.php file in your widgets directory, in this example /protected/components/widgets

Features

  • Automatic formatting of header, body and footer
  • Automatic formatting of numbers
  • Automatic sum in the footer
  • Automatic page formatting (with page header and footer, automatic print area…)
  • Nearly all properties can be overridden.

Example of use

This is an example of use of EExcelView in the controller and view:

Controller

Based on the standard Gii / Giix admin action

<?php public function actionAdmin() {
    $model = new Model('search');
    $model->unsetAttributes();
    if (isset($_GET['Model'])) {
        $model->attributes = $_GET['Model'];
    }
    if (isset($_GET['export'])) {
        $production = 'export';
    } else {
        $production = 'grid';
    }
    $this->render('admin', array('model' => $model, 'production' => $production));
} ?>

admin view

<?php Yii::app()->clientScript->registerScript('search', "
    $('#exportToExcel').click(function(){
        window.location = '". $this->createUrl('admin')  . "?' + $(this).parents('form').serialize() + '&export=true';
        return false;
    });
    $('.search-form form').submit(function(){
        $.fn.yiiGridView.update('some-grid', {
            data: $(this).serialize()
        });
        return false;
    });
"); ?>
…
<div class="search-form" style="display:block">
<?php $this->renderPartial('_search', array('model' => $model)); ?>
</div><!-- search-form -->
 
…
<?php $this->widget('application.components.widgets.tlbExcelView', array(
    'id'                   => 'some-grid',
    'dataProvider'         => $model->search(),
    'grid_mode'            => $production, // Same usage as EExcelView v0.33
    //'template'           => "{summary}\n{items}\n{exportbuttons}\n{pager}",
    'title'                => 'Some title - ' . date('d-m-Y - H-i-s'),
    'creator'              => 'Your Name',
    'subject'              => mb_convert_encoding('Something important with a date in French: ' . utf8_encode(strftime('%e %B %Y')), 'ISO-8859-1', 'UTF-8'),
    'description'          => mb_convert_encoding('Etat de production généré à la demande par l\'administrateur (some text in French).', 'ISO-8859-1', 'UTF-8'),
    'lastModifiedBy'       => 'Some Name',
    'sheetTitle'           => 'Report on ' . date('m-d-Y H-i'),
    'keywords'             => '',
    'category'             => '',
    'landscapeDisplay'     => true, // Default: false
    'A4'                   => true, // Default: false - ie : Letter (PHPExcel default)
    'RTL'                  => false, // Default: false - since v1.1
    'pageFooterText'       => '&RThis is page no. &P of &N pages', // Default: '&RPage &P of &N'
    'automaticSum'         => true, // Default: false
    'decimalSeparator'     => ',', // Default: '.'
    'thousandsSeparator'   => '.', // Default: ','
    //'displayZeros'       => false,
    //'zeroPlaceholder'    => '-',
    'sumLabel'             => 'Column totals:', // Default: 'Totals'
    'borderColor'          => '00FF00', // Default: '000000'
    'bgColor'              => 'FFFF00', // Default: 'FFFFFF'
    'textColor'            => 'FF0000', // Default: '000000'
    'rowHeight'            => 45, // Default: 15
    'headerBorderColor'    => 'FF0000', // Default: '000000'
    'headerBgColor'        => 'CCCCCC', // Default: 'CCCCCC'
    'headerTextColor'      => '0000FF', // Default: '000000'
    'headerHeight'         => 10, // Default: 20
    'footerBorderColor'    => '0000FF', // Default: '000000'
    'footerBgColor'        => '00FFCC', // Default: 'FFFFCC'
    'footerTextColor'      => 'FF00FF', // Default: '0000FF'
    'footerHeight'         => 50, // Default: 20
    'columns'              => $grid // an array of your CGridColumns
)); ?>

_search partial view

Here I've used giix extension, but you may just as well use Gii.

<?php $form = $this->beginWidget('GxActiveForm', array(
    'action' => Yii::app()->createUrl($this->route),
    'method' => 'get',
)); ?>
…
    <div class="row buttons">
        <?php echo GxHtml::submitButton(Yii::t('app', 'Search')); ?>
        <?php echo GxHtml::button(Yii::t('app', 'Export to Excel (xls)'), array('id' => 'exportToExcel')); ?>
    </div>
<?php $this->endWidget(); ?>

Version française

http://tellibus.com/lab/tlbExcelView

Total 16 comments

#16790 report it
Jimuel at 2014/03/27 09:09pm
RE: Export Filtered data grid

@rangganovsky

thanks again for your reply thumbs up for this :)

#16788 report it
rangganovsky at 2014/03/27 09:54am
RE:Export to excel by filtered

@jimuel

i'm glad to hear that, yes about the feature to add a Header, it is also my concern.

i have told bennouna about this and also have raised an issue on Github here

you can always, if you want to.. fork the project from Github repository, and try to add that feature, and hopefully you can share it with us all.

as i never get a chance to do that, so hopefully we can use this feature soon.

Regards, Rangganovsky

#16776 report it
Jimuel at 2014/03/26 10:24pm
Export to excel by filtered

@rangganovsky thanks for reply I already fixed it. I didnt notice that the export button should be inside the form of advanced search.

But my other concern is that how can I add a Title header above the header it self?

#16730 report it
rangganovsky at 2014/03/23 08:49am
RE:Export to excel by filtered

@Jimuel,

so did you mean you can successfully filtered your grid, but you can't export the filtered data to your excel, am i right?

what urlFormat are you use here? are you using 'Path' mode?

can you post your call #exportToExcel code?

i was in your positions back then, but then bennouna gives me some tips, you can see my comments and bennouna comment below, perhaps the problems lie in urlFormat you are using and it doesn't match with the #exportToExcel code.

#16710 report it
Jimuel at 2014/03/22 02:59am
Export to excel by filtered

good day guys i already followed this steps and successfully filtered my in cgridview but when I export it to excel it always gives me back all my data to be filtered. any idea the codes are the same an I dont know the solution or problem to this. :(

#15231 report it
bennouna at 2013/10/20 04:24am
RE: Export Filtered data grid

@rangganovsky

Please use tlbExcelView’s page in the forum for support requests.

Your urlFormat property must be set to 'path'. Otherwise, you should have

$('#exportToExcel').click(function(){
        window.location = '". $this->createUrl('admin')  . "&' + $(this).parents('form').serialize() + '&export=true';
        return false;
    });
#15229 report it
rangganovsky at 2013/10/19 09:50pm
Export Filtered data grid

Hi,

i notice that if i used your suggestion to call exportToExcel :

$('#exportToExcel').click(function(){
        window.location = '". $this->createUrl('admin')  . "?' + $(this).parents('form').serialize() + '&export=true';

the error saying that can't find request admin?YourModelName will be shown. so i modified it to be like this :

$('#exportToExcel').click(function(){
        window.location = '". $this->createUrl('admin')  . "' + $(this).serialize() + '&export=true';
        return false;
    });

now i'm able to export (using my modified function), but if i tried to filter it, when i do some filter through the built in search function (the grid was succesfuly filtered though) but when export it to excel, it is still display the whole data without filter applied. am i missing something here?

Thanks, razril

#14782 report it
aliabdzad at 2013/09/09 08:06am
a problem

please help me to do this my friends ;) i want to get excel file from my informtion after i had searched it ... when i submit the button the excel file has all of the information but i want just the searched result information

#14636 report it
aliabdzad at 2013/08/31 04:07am
tanx

i needed it ;) ... tanx alot

#12211 report it
bennouna at 2013/03/06 12:30pm
RE: can't open excel file after downloaded

@banamlehsb

This error shouldn't happen if you follow the tutorial steps.

However, you can post your running code in the forum's extension page.

#12210 report it
banamlehsb at 2013/03/06 12:10pm
can't open excel file after downloaded.

When i downloaded excel file. i can't open it. Excel noticed that: "Excel cannot open the file ... because the file format or file extension is not valid. ...". What's wrong?

#11788 report it
Daniel at 2013/02/04 04:43am
tlbexcelview+groupgridview = perfect

Hi,

I got an issue when combining this extension with the groupgridview.

The merging and extraRows are not implemented yet. Hence, the display become a bit messy

Could you help me with this?

Cheers,

Daniel

#10930 report it
bennouna at 2012/12/04 09:00am
RE: How to set the jquery script

@Wiwit Iwan SEP

Please explain a little more your issue and post the relevant parts of your code in the extension forum thread.

Cheers

#10925 report it
Wiwit Iwan SEP at 2012/12/03 11:15pm
How to set the jquery script

Can anyone explain how to set the jquery scipt on view? it give me error " The system is unable to find the requested action "bonus?r=ledgers_detail" " bonus is my action on Ledgers_detail controller

#10736 report it
bennouna at 2012/11/20 06:16am
Re: How to display row number?

@Daniel I've just tried it and it works well.

If you remove that column, do you see the pagination? Make sure:

  1. You set the enablePagination property to true in your tlbExcelView widget call (the default value is false)

  2. You actually have a CPagination component attached to your dataprovider.

If you don't see what I'm talking about, please use the extension forum thread to post the relevant parts of your current code (model + view).

#10730 report it
Daniel at 2012/11/19 09:38pm
How to display row number?

Great extension. It solved a lot of my headache. But, one issue, I have a column like this one,

array(
            'header' => 'No',
            'value' => '$this->grid->dataProvider->pagination->currentPage * $this->grid->dataProvider->pagination->pageSize + $row + 1', // row is zero based
            'htmlOptions' => array('style' => 'text-align: center; width: 50px;'),
        ),

Just to display the row number. But this extension give me an error:

Property "tlbExcelView.grid" is not defined.

Any help on this?

Cheers,

Daniel

Leave a comment

Please to leave your comment.

Create extension