Yii 1.1: eexcelview

A simple extension that exports dataprovider data to excel table.
76 followers

EExcelView on github

Forum thread

This is my first extension ever :). It is a wrapper of PHPExcel that extends from CGrivView. The main idea is to easily export already defined grids to excel files. You can use the same array of parameters that the grid uses plus an aditional specific params, which are:

Configuraiton

Document properties

  • creator - The creator of the document
  • title - Title of the excel document
  • subject, description, category

Other params

  • libPath - path to PHPExcel, defaults to 'ext.phpexcel.Classes.PHPExcel'
  • autoWidth - whether to auto extend cells for the content(default true)
  • exportType - the type of the export, all possible types of PHPExcel lib(Excel5, Excel2007,PDF, HTML)
  • disablePaging - if set to true, it will export all data (default true)
  • filename - the full path of the filename to export to. If null it will stream to the browser
  • stream - true/false stream to browser or save to file.
  • grid_mode - Whether to display grid or to export it to selected format. Possible values(grid, export)
  • grid_mode_var - defaults to 'grid_mode' GET var for the grid mode

Requirements

Yii 1.1 or above and PHPExcel library

Usage

The usage is like using CGridView:

$this->widget('EExcelView', array(
     'dataProvider'=> $dataprovider,
     'title'=>'Title',
     'autoWidth'=>false,
      ..... other options 
));

Note

The path of the PHPExcel library is hardcoded to: application.extensions.phpexcel.Classes.PHPExcel

Asuming that you have extracted the lib under extensions folder. I will change that later.

ver 0.32

  • Fixed the CButtonColumn bug "Property "CButtonColumn"."name" is not defined"...
  • Fixed the issues with multiple level of buffering(le top)
  • Fixed export buttons column bug(le top)
  • Added documetation of all properties in the extension page.

ver 0.31

Mainly bug fix release. I also modified the extension to be able to run in console mode(although its a widget). I realy needed something to send me reports in xls. Here is the code snippet:

$factory = new CWidgetFactory();    
        $widget = $factory->createWidget($this, 'EExcelView', array(
            'dataProvider'=>$provider,
            'grid_mode'=>'export',
            'title'=>'Title',
            'filename'=>'report.xlsx',
            'stream'=>false,
            'exportType'=>'Excel2007',
            'columns'=>array(
                'col1',
                'col2',
            ),
        ));
 
        $widget->init();
        $widget->run();

ver 0.3

Major change EExceView now can fully replace CGridView. There is an optional parameter 'gridmode'(default "grid") and when not set it acts just like normal grid. If gridmode="export" it exports the grid in the selected "exportType"(default "Excel5"). The path to PHPExcel can now be customized via "libPath" variable. If the lib is not found a warning is generated and export is disabled.

  • Added: "gridmode" possible values(grid, export). When set to grid acts like normal grid.
  • Added: "libPath" property, defaults to "application.extensions.phpexcel.Classes.PHPExcel". The location of PHP Excel lib.
  • Added: "grid_mode_var" the GET variable name which can be used to change the grid mode. defaults to "grid_mode"
  • Added: new template item "exportbuttons" which renders export links. Example: .... 'template'=>"{summary}\n{items}\n{exportbuttons}\n{pager}" ....
  • Added: config for export buttons. "exportButtonsCSS"(default="summary") css class for the div which wraps the buttons. "exportButtons" - array of exportTypes, for each item it renders link which exports the grid. If you specify an array you can customize the caption of the link. All export type have default captions. Example: array('Excel5'=>array('caption'=>'*.xls'), PDF). Property exportText which defines the text before the links, deault = "Export to: ".
  • Added: renders CLinkColumns. Just the text of the links.
  • Added: support for footer, just like normal footer.
  • Added: callback functions called after processing of the cell.$cell is the php excel cell object. Refer to php excel documentation for help. $data is the same var the grids uses for expressions. $value is the text the extension had put in the cell :
    1. onRenderHeaderCell(PHPExcel_Cell $cell, string $value)
    2. onRenderDataCell(PHPExcel_Cell $cell, $data, string $value)
    3. onRenderFooterCell(PHPExcel_Cell $cell, string $value)

Thanks to all who have written me with ideas how to extend this extension. Sorry by I've been realy busy lately so I didnt have time to bring this earlier. I have tested the extension but if you find any bug or something you'd like me to add, feel free to write. Happy yiister :)

ver 0.2

  • Fixed: Error when saving to filename.
  • Fixed: bug in column indexing when > 26
  • Added: support for CSV
  • Added: new param "stream"(default true) when set to true streams to browser else save to local filename
  • Added: get real column headers
  • Added: get title from page title when not specified

Total 20 comments

#16945 report it
phreak at 2014/04/15 01:42pm
try this

Amjad Khan try to set grid_mode to 'grid' and see if the grid is rendered normally. Do you have any errors in the log.

#16828 report it
Amjad Khan at 2014/04/01 08:25am
I can get the downloaded file in Excel

I use the following action in my controller

public function actionExcel() {
        $model = new News('search');
        $factory = new CWidgetFactory();
        $widget = $factory->createWidget($this, 'EExcelView', array(
            'dataProvider' => $model->search(),
            'grid_mode' => 'export',
            'title' => 'Title',
            'filename' => 'report.xlsx',
            'stream' => false,
            'exportType' => 'Excel2007',
            'columns' => array(
                'pkreportid',
                array(
                    'header' => 'News Paper Name',
                    'name' => 'fknewspaperid',
                    'value' => '$data->Newspapername->newspapername',
                ),
                'headline',
                'pageno',
                'reportername',
                'city',
                array(
                    'header' => 'Date',
                    'name' => 'reportdate',
                ),
                array(
                    'header' => 'News Analyst',
                    'name' => 'fkuserid',
                    'value' => '$data->User->username',
                ),
                /*
                  'fkcityid',
                  'reportinggaps',
                  'bridginggapsinfo',
                  'publicbody',
                  'fklawid',
                 */
                array(
                    'class' => 'bootstrap.widgets.TbButtonColumn',
                ),
            ),
        ));
        $widget->init();
        $widget->run();
        exit;
    }

I got the blank html page, i want to get Excel sheet, please help

#14911 report it
semprul at 2013/09/20 04:10am
@albertski

i used CAvtiveDataProvider as data provider..

#14899 report it
albertski at 2013/09/19 09:20am
@semprul

Just tried this and it worked:

array(
  'header'=>'Number',
  'value'=>'$row+1',
);

I am using a DataProvider and it seems like you are not. I think you will need to use a DataProvider or if you are look if there is an issue there.

#14894 report it
semprul at 2013/09/18 11:44pm
@albertski

this is what i mean: i want to add collumn that contain row number. the ++$row not working as the cgridview does...

'columns'=>array(
                array(
                    'header'=>'No.',
                    'value'=>'++$row', ////// ---> this not work 
                ),
        'nup',
                array(
                    'header'=>'Name',
                    'value'=>'$data->nup->name',
                ),
                'time1',
        'time2',
#14892 report it
albertski at 2013/09/18 10:22pm
Perhaps the best way is to do it outside of this class

@semprul Not sure what you are trying to do but perhaps its better you should handle that outside of the excel class.

Not sure if this is good but someone posted this:

$yourprovider->getItemCount()
$yourprovider->getTotalItemCount(); //(idepended of pagination)
or you can get it by count($yourprovider->->getData())
#14891 report it
semprul at 2013/09/18 09:45pm
how to export row count/number

hi @albertski, how to export row count/number? i used ++$row like cgridview, but it said "Undefined variable: row"

#13970 report it
albertski at 2013/07/09 09:42pm
Alternating row colors Github
#13966 report it
acorncom at 2013/07/09 05:28pm
@albertski

Could you offer those changes on Github? Would make it far easier for us to upgrade our version of the extension long-term :-)

#13962 report it
albertski at 2013/07/09 04:52pm
Alternating row colors

I made an update to allow changing the background color on the rows.

Added towards the end of renderRow()

$cell = $this->objPHPExcel->getActiveSheet()->setCellValue($this->columnName($a).($row+2) , strip_tags($value), true);    
 
  $worksheet = $this->objPHPExcel->getActiveSheet();
  $highest_column = $worksheet->getHighestColumn();
  $cell_row = $cell->getRow();
  $cell_column = $cell->getColumn();
 
  if(is_float($row/2)) {
    $color = $this->row_color;
  }
  else {
    $color = $this->row_alternate_color;
  }
 
  $worksheet->getStyle('A' . $cell_row . ':' . $highest_column . $cell_row)->applyFromArray(array(
   'fill' => array(
     'type' => PHPExcel_Style_Fill::FILL_SOLID,
     'color' => array('rgb' => $color)
   )
 )
);
#12378 report it
semprul at 2013/03/17 10:20am
export button?

hi,,, how to use export button? when i use this extension, they will download the file directly when i reload the page.. there is no export button, they auto-download

#12305 report it
Thureos at 2013/03/12 11:03am
Actually it is

But for the other formats, like xls.

#12301 report it
phreak at 2013/03/12 06:21am
zip

I'm not 100% sure, but thinnk that Excel2007 format is already zipped.

#12237 report it
Thureos at 2013/03/08 04:24pm
Any plans for compressing the output?

It will be a nice idea it can zip compress the output, what do you think? Will work with that.

Thanks!

#12209 report it
banamlehsb at 2013/03/06 12:09pm
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?

#12078 report it
FlyngThunderGod at 2013/02/26 12:03pm
Thank You

thank you for this very nice extension.

no problem with the hardcoded path for phpexcel

#10833 report it
le_top at 2012/11/26 05:48pm
Modification

Hi Please check https://www.dropbox.com/s/afxbl8t0z1i5op0/EExcelView.zip .

Following your update I noticed that my version has some extra stuff (for extra configuration, translation, ...). You'll have to change 'MyCGridView' in 'CGridView'.

#10126 report it
xNicox at 2012/10/05 07:34am
encoding problem

Thanks for the tip phreak . I'll try it.

I suposse that the rule is: - all files saved in utf-8 - in layouts/main.php check that "charset=utf-8" is added to content-type meta keyword.

any other ??

#10123 report it
phreak at 2012/10/05 04:50am
encoding problem

Well almost all of my reports are in cyrillic characters so I dont think there's problem with encoding. You said that there are files in ANSI, all your files should be in utf8. Could you convert everythink in utf8 an try again ?

#10113 report it
xNicox at 2012/10/04 06:39pm
encoding problem

Hi, I have a problem with the encoding or charset. The browser let me download the file, but when I try to open with excel, it says that the format is incorrecte. I assume is an encoding problem. I can use prefectly the same program in another infrastructure ( linux). My test is in windows xp, Spanish languaje. Can anybodiy give a clue ? I have some php files saved as ANSI and other as UTF-8. My /layouts/main.php file is saved as UTF-8 and in charset META field is charset= utf-8. The browser detect UTF-8 .... I'm lost .. Best Regards

Leave a comment

Please to leave your comment.

Create extension