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.15
  • 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

Please note that the extension switches grid display / export depending on the export parameter, so we must implement that parameter at the view level.

Since the url format is configurable in Yii via CUrlManager’s urlFormat , we must take that into account.

<?php
$urlJoin = Yii::app()->urlManager->getUrlFormat() == 'path' ? '?' : '&';
Yii::app()->clientScript->registerScript('search', "
    $('#exportToExcel').click(function(){
        window.location = '". $this->createUrl('admin')  . $urlJoin . "' + $(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 19 comments

#18719 report it
bennouna at 2014/12/17 05:07am
Sample admin view update

Hello all,

The urlFormat property is left to 'get' by several members, so I thought the sample admin view code should reflect that.

I updated the PHP code that outputs the JS script, which listens to the grid export requests (Export button clicks) : the PHP code now checks the urlFormat’s property value in order to correctly construct the url passed to the JS script.

Hope that makes things clearer.

#18718 report it
bennouna at 2014/12/17 04:38am
RE: Multiple grid export on same page

@neophyte

There could be several reasons why this happens. Please post your codes (view and controller), preferrably to the extension forum’s page.

#18711 report it
neophyte at 2014/12/16 10:22am
Multiple grid export on same page

I have multiple grid and each has separate export buttons but when I click on export button of any grid it always exports the data of the grid which comes first in the view.. Did anyone solve this issue

#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