Yii 1.1: excel-sheet-view

a grid view exported in a spreadsheet
16 followers

My wish is you never have to work with spreadsheets in your life! But if you do then this extension is for you. Inspired by CGridView, ExcelSheetView creates an excel spreadsheet from a data provider by using the power of PHPExcel library. ExcelSheetView has CBaseListView as its parent class. This widget comes along with a component similar to CDataColumn and it is called ExcelColumn

Download the zip file from the right sidebar. This file must be extracted inside "protected". My convention is that you have a "widgets" subfolder inside "protected" folder. This is why you see the alias 'application.widgets......' a lot inside the source code. If you want to use another location then you should change all these aliases inside the source code.

Usage

MAKE SURE you apply this hack: click here to your PHPExcel downloaded library before you use this extension

Afterwards you only have to import the php excel library only once as you see at the top of the ExcelSheetView class php file like this:

Yii::import('ext.phpexcel.PHPExcel',true);

If you are not familiar with the PHPExcel library then this is the home page of the PHPExcel project: PHPExcel

Also this wiki is a good place to start: How to use PHPExcel external library with Yii

Note: The subdirectory where the excel file will be written is the alias application.files Filename is depended on the title you set the widget attribute. The extension is xls.

Sample Code

So below we have an example of the widget. Assume we are inside a controller action. First of all we need a data provider. Let's choose a simple ArrayDataProvider

$rawData = array(
            array(
                'idCol' => 1,
                'columnName' => 'George',
                'columnSurname' => 'Pligor',
            ),
            array(
                'idCol' => 2,
                'columnName' => 'No',
                'columnSurname' => 'Name',
            ),
        );
$dataProvider = new CArrayDataProvider($rawData, array(
            'id' => $this->action->id,
            'keyField' => 'idCol',
        ));

We will add some column configuration as well to display the power of ExcelColumn

$columns = array(
            array(
                'name' => 'columnName',
                'header' => 'Column Name',
                'footer' => 'george',
            ),
            array(
                'name' => 'columnSurname',
                'header' => 'Column Sur Name',
            ),
        );

Now assume we are inside the view:

$properties = array(
    'id' => $this->action->id .'-grid',
    'dataProvider' => $dataProvider,
    'columns' => isset($columns) ? $columns : array(),
    'showTableOnEmpty' => false,
    'title' => 'sample',
);
$this->widget('application.widgets.Excel.ExcelSheetView', $properties);

So finally you should be able to see something similar to this:

Total 2 Results. Please Click Here To Download The File: sample.xls

And by clicking sample.xls a new excel file will be downloaded from the server! Well done!

Feel free to share any comments, remarks or notes you may have on this extension!

Total 10 comments

#9848 report it
Abhishek Shah at 2012/09/15 12:58am
thanx.

Thanx for your help.

#9843 report it
pligor at 2012/09/14 11:22am
setting up phpexcel is mentioned in another wiki
#9841 report it
Abhishek Shah at 2012/09/14 08:33am
I have downloaded phpecel latest coe from site u suggested

I have downloaded ,In that in phpexcel,folder name classes,PHPexcel was there .I have copied it,in phpexcel/PHPExel.still it is not working.

#9839 report it
pligor at 2012/09/14 07:41am
obvious

You don't have the PHPExcel library in a folder phpexcel inside the extensions directory

#9838 report it
Abhishek Shah at 2012/09/14 07:14am
include(PHPExcel_Worksheet.php): failed to open stream: No such file or directory

I have already mensioned in above comment,error occure,no file name include(PHPExcel_Worksheet.php): failed to open stream: No such file or directory

#9836 report it
pligor at 2012/09/14 07:04am
replace one line

Well.. I haven't touched that code for a long time but try to replace the line

Yii::import('ext.phpexcel.PligorPHPExcel',true);

with

Yii::import('ext.phpexcel.PHPExcel',true);

Follow all the steps and tell me if it worked

#9835 report it
Abhishek Shah at 2012/09/14 06:44am
pligorphpexcel not found

I have downloaded phpexcel,in your excelsheet-view,u have called,pligorphpexcel,whic is not in phpexcel,so it's displaying error,when I have done phpexcel instead of pligorphpexcel,error is include(PHPExcel_Worksheet.php): failed to open stream: No such file or directory .Help me how to solve it.

#7568 report it
pligor at 2012/03/30 02:31pm
Come on...

I shared all this code and you couldn't do half an hour of homework? Anyways I adapted the source code so that it is depended only on itself and now shall everybody be happy :)

#7441 report it
robregonm at 2012/03/22 10:24pm
Unuseful extension

This extension is totally unuseful as is, the authour should provide a minimum version of the CHtmlEx class at least. When implementing the developer needs to "guess" what the author tries to do in certain implementations of the functions.

#6311 report it
priest at 2011/12/28 03:05am
I can not find CHtmlEx class in zip !

I can not find CHtmlEx class in zip !

Leave a comment

Please to leave your comment.

Create extension