Yii 1.1: toexcel

Easily export to excel your data
51 followers

Based on this wonderful extension: EExcelView

I decided to make a wrapper so everyone could easily export data to excel without needing to write some complex PHPExcel statements.

This extensions is a behavior you can attach to any controller so you can fetch some data from your models and export it to excel (and other formats) using the extension EExcelView.

As all the extensions (except PHPExcel) are included in this download you can consider this as a self-contained excel exporter. I tried to make sure that everything worked fine and tested with many configurations, but still issues could arise so I beg you comment so we can make the code better, both this extension and EExcelView

Requirements

Usage

In your controller/action

public function actionTest()
{
    // Load data
    $model = Classroom::model()->findAll();
 
    // Export it
    $this->toExcel($model);
}

This will generate an excel file with all the records of, in this case, Sala(Classroom) model with proper header and format. There are many options to allow many customizations so I will put some examples of use later

Instalation

  • Put PHPExcel so its folder looks like /protected/extensions/phpexcel/Classes/PHPExcel.php (in practice you can put it anywhere and then pass the pathAlias as a param to EExcelView, but for the sake of simplicity just place it there)

  • Put EExcelView so its folder looks like /protected/extensions/eexcelview/EExcelView.php

**Note that I uploaded an improved version so you rather download that instead of the version posted in the extension page: ** Download here

  • Download EExcelBehavior and install it the same way as before, so it looks like /protected/extensions/eexcelview/EExcelBehavior.php

(Both extensions are included in the download package, except for PHPExcel, wich is huge)

Then, attach the behavior to the desired controller

public TestController extends CControler
{
    ...
 
    public function behaviors()
    {
        return array(
            'eexcelview'=>array(
                'class'=>'ext.eexcelview.EExcelBehavior',
            ),
        );
    }
    ...
}

That's all :)

Examples of use

Filter data and export just the ID to Excel5 (.xls)

public function actionTest()
{
    // Load data (scoped)
    $model = Classroom::model()->notFull()->findAll();
 
        // Export it
    $this->toExcel($model,
        array(
            'id'
        ),
        'Test File',
        array(
            'creator' => 'Zen',
        ),
        'Excel5'
    );
}

Get some data from a CActiveDataProvider and export relations with correct headers titles to Excel2007 (xlsx)

public function actionTest()
{
    // Load data with a CActiveDataProvider (note that we can easily apply conditions over the result set)
    $model = new CActiveDataProvider('Classroom');
 
    // Export it (note the way we define columns, the same as in CGridView, thanks to EExcelView)
    $this->toExcel($model,
        array(
            'id',
            'name',
            'building.name::Building', // Note the custom header
        ),
        'Test File',
        array(
            'creator' => 'Zen',
        ),
        'Excel2007' // This is the default value, so you can omit it. You can export to CSV, PDF or HTML too
    );
}

Hope it helps you as much as it's helping me in my personal projects :)

Total 20 comments

#17637 report it
pramkimpam at 2014/07/09 04:04pm
how to convert to pdf

how to convert to pdf?? when I change to PDF it make error said "PDF Rendering library has not been defined."

#16376 report it
Giris at 2014/02/16 05:43pm
Changing Value

As like overriding the header values, is there any way to change the value of data while passing it to the extension.

Ex: Changing 'Name' or modifying it a bit. array( 'id', 'name', 'building.name::Building', // Note the custom header ),

Please let me know how to achieve this. THanks.

#15495 report it
fouss at 2013/11/15 03:39am
Great!

Thank you, that's great! I got error "failed to delete buffer. No buffer to delete" but solve it by replacing "ob_end_clean()" with "if (ob_get_contents()) ob_end_clean()" in line 257 of EExcelView.php

masterfouss

#12054 report it
bardoo at 2013/02/24 05:59am
can't find the file!

i have a new bee question : where is the folder that should contain the exported file ?

#12053 report it
bardoo at 2013/02/24 05:33am
can't find the file!

i have a new bee question : where is the folder that should contain the exported file ?

#11531 report it
AustinGeek at 2013/01/18 12:29pm
date reformatting

Thanks Karmraj !

Problem is definitely in EExcelview. I just don't understand why a varchar column with url in it is being interpreted as a date.

Need to study it a bit and find out how that column is being processed by that particular logic.

I am also having a big problem with a simple export to CSV or EXCEL format (2007 in particular) It uses 128M and blows up! The data was 1000 rows of 15-20 columns (no big text fields).

I don't know if there is a way to tune phpexcel

#11008 report it
karmraj at 2012/12/10 06:19am
Check EExcelView file of extension

@AustinGeek : Please check out EExcelView extension file. After line no 202, you can see following code.

//date edited francis  
$dateF= explode("-", $value);
$c1=count($dateF);

if($c1==3 && $dateF[0]<9000 && $dateF[1]<13 && $dateF[2]<32)//{}
$value=$dateF[2].'/'.$dateF[1].'/'.$dateF[0];
//end of date

This code use for format the date field value which you need to export in excel. And in your issue this code interpret your URL value as a date field value that why it convert your URL value as "/" saperated value.

Refere this logic.

#10987 report it
AustinGeek at 2012/12/08 10:36am
dashes and slashes....

Great / Simple Extension:

One problem I found the other day.

I have a url in a column that I am exporting....it does the same thing as karmraj described with his date issue.

"the-web-times.com" is exported to excel (and csv) as "times.com\web\the"

I looked thru the code and didn't see anything that would trigger this type of transformation. I also contacted Bennounna and asked him about it.

Any ideas Zen?

Thanks !

#10968 report it
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 .

#10756 report it
Wiseon3 at 2012/11/21 02:08pm
Blank page problem

@AustinGeek most likely the problem lies in the memory usage, as creating an excel sheet uses quite a lot of memory, even for a small number of rows, due to the fact that it keeps a lot of info, regarding formatting for each cell. Try increasing your memory_limit from php.

#10755 report it
AustinGeek at 2012/11/21 10:51am
Great Extension
  • I got this to work pretty easily. Just used the $model on the first attempt. Later expanded the output array to include foreign keys for other tables. I am experiencing a slow response with 4 foreign key fields to tables for things like "status" or "category". I have a problem when the user does not filter the data by at least one column, the controller seems to timeout and I am left with a blank page. Works fine if user selects all rows were status = 'Active' or any other column.

I only have 1500 rows in the table...so that data set is trivial in size. I am not a MySQL guy, but have 25 years of experience with designing Oracle applications. All my tables have primary keys and none of the tables used for lookup are large (500 rows max, 10 rows average).

Any suggestions?

Thanks!

#10749 report it
Kollipara Rama Krishna at 2012/11/21 06:38am
Error

include(PHPExcel_Cell.php): failed to open stream: No such file or directory

I am getting this error please help me.

#9750 report it
Abhishek Shah at 2012/09/08 02:37am
pagination

I have done 10 out 100 recoeds,but now,I want next 10 records,means in gridview suppose I have ,20-30 id,10 number of records,It displays only first 10, do not know ,How to get.

#9730 report it
Abhishek Shah at 2012/09/07 06:11am
pagination

In my gridview ,only for examole,10 out of 100 records are there,I want to export that 10 values only,not all values by findall,can u tell me how I can achieve it.

#9323 report it
Abhishek Shah at 2012/08/03 05:31am
how to get excel at particular path

I do not get,where it save excel file,how I can export excel to particular path.

#9199 report it
lifeinthefridge at 2012/07/27 02:03pm
cgridview search data

One of the previous comments asked about filtered data, however I still cannot get it to work with that solution. In my controller I have

$model = PackagingMetric::model()->search();

However, the csv being exported only contains the default records of the model from the first page of cgridview.

Any help would be greatly appreciated.

#8584 report it
deeptibaghel at 2012/06/13 01:38am
string format

i also want to display a digit as 10000000000 instead of 1.e10 and also stop the leading zeroes getting truncated as it is a UPC field, can someone help please.

#8412 report it
codejunior at 2012/05/31 11:47pm
I FOUND THIS PROBLEM

I found this problew

Fatal error: Call to a member function getAttributeLabel() on a non-object in /WEBSITE/www/yii/library/admin/protected/extensions/eexcelview/EExcelView.php on line 96

how to fix it?

#8178 report it
Sumic at 2012/05/15 01:26am
bug fix with get relations data

EExcelView.php line 141: to $value=$this->evaluateExpression($column->value ,array('row'=>$row,'data'=>self::$data[$row]));

#7707 report it
yiisus at 2012/04/11 01:27am
Thanks. Now about date

Thank you zenbaku

Using 'field:raw:label' did the trick.

By the way, do you know how to set a cell to be a date? I have '3/3/2012' strings that I'm trying to set as date, but using 'field:date:label' will break.

Thanks again.

Leave a comment

Please to leave your comment.

Create extension