toexcel Follow @yiiframework

Easily export to excel your data

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 ¶

Yii 1.1 or above
PHPExcel
EExcelView (included)

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

#12054

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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.

#7670

javed alam at 2012/04/07 11:43pm

Formatting cell

Thanks for this awesome extension. i need to know is there any way to format the cell value e.g i want to display a digit as 10000000000 instead of 1.e10 etc.

#7667

zen∞ at 2012/04/06 12:12pm

denis84

Sorry, I forgot to answer that.

You cand always do something like this:

$model = YourModel('search');
$model->type = 1; // This will filter out all the results whose type is 1 
$dataProvider = $model->search(); // Then you just perform the search and obtain the data provider.

Onto the first question. It's clearly a memory issue that should be resolved increasing the memory available. I'm concerned about PHPExcel excesive memory usage, thou, so I think an "iteration over chunks" solution may be on the road to solve this. I'm not an PHPExcel expert, but I'll try to hack more into it and squeeze the better performance out of it.

Aside: if you need many information and you just need to export to csv I think you could easily write a SQL query to do it for you. Is this an acceptable workaroud for you?

Hope I helped a bit.

#7666

denisa84 at 2012/04/06 11:31am

Allowed memory size problem

I already tried that with a bigger value and nothings changed. And what about the second question? about filters... Thanks,

Please login to leave your comment.