GridView To Excel Export Extension In Yiiframework 2.0

Are you struggling for exporting data to excel?
This tutorial will helpful to create your own extension for exporting data as excel from your gridview data. What you display in gridview (text), It will export as excel.

Install PHP Excel In Yiiframework 2.0

Add below line in composer.json and update composer to install phpoffice excel.

"require": {
        ......
		"phpoffice/phpexcel": "*"
		......
    }

Excel Gridview Class In Yiiframework 2.0

I created ‘ExcelGrid.php’ class from existing class on web and it is extending the basic GridView class.
First You have to download this class. Then add it in your application and change the namespace for this class (based on installation).

‘ExcelGrid.php’ Class Format

<?php
namespace app\components;
//namespace bsource\gridview;//in vendor folder
use Yii;
use Closure;
use yii\i18n\Formatter;
..........

class ExcelGrid extends \yii\grid\GridView
{
	..........
	public function init(){
		parent::init();
	}
	public function run(){
		..........
		parent::run();
	}
	
	public function init_provider(){
		..........
	}
	public function init_excel_sheet(){
		..........
	}
	public function initPHPExcelWriter($writer)
    {
        ..........
    }
	public function generateHeader(){
		..........
	}
	
	public function generateBody()
    {
        ..........
    }
	
    public function generateRow($model, $key, $index)
    {
        ..........
    }
	
	protected function setVisibleColumns()
    {
        ..........
    }
	
	public function getColumnHeader($col)
    {
		..........
    }
	public static function columnName($index)
    {
        ..........
    }
	
	protected function setHttpHeaders()
    {
		header("Cache-Control: no-cache");
		header("Expires: 0");
		header("Pragma: no-cache");
		header("Content-Type: application/{$this->extension}");
        header("Content-Disposition: attachment; filename={$this->filename}.{$this->extension}");
    }
}

Method 1:

Add this class in ‘project/components’ folder and defined namespace as

namespace app\components;

Method 2:

If would you like to add it in vendor folder, follow this method. First you have to creat a folder ‘bsource/gridview’ format and have to add this inside. Namespace like

namespace bsource/gridview;

After created class, you have to autoload this class. Goto file

'vendor/composer/autoload_psr4.php'

and add the below line in return array

'bsource\\gridview\\' => array($vendorDir . '/bsource/gridview'),

Gridview To Excel In Yiiframework 2.0

After completed above steps, Just you have to call ‘ExcelGrid’ widget using namespace to export data as excel in yii2.

Excel.php

<?php
// \app\components\ExcelGrid::widget([ OR 
\bsource\gridview\ExcelGrid::widget([
        'dataProvider' => $dataProvider,
        'filterModel' => $searchModel,
		//'extension'=>'xlsx',
		//'filename'=>'excel',
		'properties' =>[
			//'creator'	=>'',
			//'title' 	=> '',
			//'subject' 	=> '',
			//'category'	=> '',
			//'keywords' 	=> '',
			//'manager' 	=> '',
			//'description'=>'BSOURCECODE',
			//'company'	=>'BSOURCE',
		],
        'columns' => [
            ['class' => 'yii\grid\SerialColumn'],
			'username',
            'createdby',
            'createdon',
        ],
    ]);

Sample Controller.php

<?php
............
class CategoryController extends Controller
    public function actionExcel()
    {
        $searchModel = new categorySearch();
        $dataProvider = $searchModel->search(Yii::$app->request->queryParams);

        return $this->renderPartial('excel', [
            'searchModel' => $searchModel,
            'dataProvider' => $dataProvider,
        ]);
    }
    public function actionIndex()
    {
        $searchModel = new categorySearch();
        $dataProvider = $searchModel->search(Yii::$app->request->queryParams);

        return $this->render('index', [
            'searchModel' => $searchModel,
            'dataProvider' => $dataProvider,
        ]);
    }
............
?>