Dataprovider to EExcelView

Question

lets say i have a table named

cars{
'id','name','brand_id', 
}

and another table

brand{
'id','brand_name',
}

I have a situation that i want to generate an Excel report with the following attributes. 'name','brand_name' i.e. SELECT cars.name, brand.brand_name FROM cars INNER JOIN on brand WHERE cars.brand_id = brand.id

So i created a dataprovider like this:

$sql = "SELECT cars.name, brand.brand_name FROM cars INNER JOIN brand on cars.brand_id = brand.id";
$result = Yii::app()->db->createCommand($sql)->queryAll();
$this->render('doc', array('dataprovider' => $result));

Now i want to generate Excel file with result as a dataProvider so i write the following code:

// lets say i am doing this in view page named doc.php

$factory = new CWidgetFactory(); 
Yii::import('ext.eexcelview.EExcelView',true);  
        $widget = $factory->createWidget($this,'EExcelView', array(
            'dataProvider'=>$dataprovider->search(),
            'grid_mode'=>'export',
            'title'=>'Title',
            'creator'=>'TNC',
            'autoWidth'=>false,
            'filename'=>'Report.xlsx',
            'stream'=>false,
            'disablePaging'=>false,
            'exportType'=>'Excel2007',
            'columns'=>array(
                'name',
                'brand_name',),
            'showTableOnEmpty' => false,
        ));

        $widget->init();
        $widget->run();

I have included all the extensions that i have to.. This code is working when i fed the dataProvider field with a single table entry . But the situation arises when i include multiple tables.

acorncom · Accepted Answer · 2013-01-14T17:37:47.183

2

These lines don't actually make a dataprovider:

$result = Yii::app()->db->createCommand($sql)->queryAll();
 $this->render('doc', array('dataprovider' => $result));

You'll want to do something like the following:

$dataprovider = new CSqlDataProvider($sql, array(
    'pagination'=>false,
);
$this->render('doc', array('dataprovider' => $dataprover);

More info here: http://www.yiiframework.com/doc/api/1.1/CSqlDataProvider

edited Jan 14 '13 at 17:37

answered Jan 14 '13 at 11:41

acorncom

5,975
1
19
31

Do you know why i am getting only 10 values? i.e dataprovider reflects only 10 datas. – tnchalise Jan 14 '13 at 12:17
The default for a dataprovider is to paginate every ten pages. My answer I'd updated with how to disable pagination – acorncom Jan 14 '13 at 17:36
I have probably 320 rows and 60 columns.. the pagination is not working for those huge.. It works fine with limited datasheet..? Any solutions..? – tnchalise Jan 15 '13 at 05:36
Can you open a new question about that? Think you'd need to do your data in chunks and might be good to get other folks' thoughts – acorncom Jan 15 '13 at 06:21
@TNC don't change answers given to you by yourself. If you think something should be changed explain in a comment and the author of the answer will change it. – Shadow The GPT Wizard Jan 15 '13 at 07:58
Slight Difference should be made on pagination field. `$count = Yii::app()->db->createCommand('SELECT COUNT(*) FROM cars')->queryScalar(); $dataprovider = new CSqlDataProvider($dataprovider,array('pagination' => array('pageSize' =>$count),));` – tnchalise Jan 15 '13 at 08:00

score 0 · Answer 2 · answered Jan 15 '13 at 07:58

This works for 2 tables, dont know works for more than 2 or not.

$dataProvider = new CArrayDataProvider($dataprovider, array('id' => 'brand', 'sort' => array('attributes' => array('brand_name', ), ), 'pagination' => false));
$this -> render('doc', array('dataprovider' => $dataProvider,));

Dataprovider to EExcelView

2 Answers2