Yii Framework 2 : GridView To Excel Export Extension

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).

<?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 static function columnName($index)
    {
        //..........
    }

    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)
    {
        //..........
    }

    public function getColumnHeader($col)
    {
        //..........
    }

    protected function setVisibleColumns()
    {
        //..........
    }

    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]);
    }
    //............
}
?>