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

  • Raul

    Hola, when I call the index, in the menu report/index, only open dialog windows with the excel, wihtout data of course..
    How i can have a normal behavior, like a button.. Thanks I thinks will be a great works. 🙂

    • m bala

      I am using two individual actions to display data and generate excel like sample controller. I will try and update to display and excel export data in single action

  • Boki

    thank you

  • Nitish Mallick

    I have done all the steps mentioned above but I am getting a blank xls. can anybody tell me what did i miss ?

  • Reza Mukti

    Hi bscource,

    when i access to /category/excel.. its directly download into excel file. Is possible to display gridView like category/index an then export excel file with query criteria ?

    Thanks in advance

  • Miguel Almeida

    First off all, thank you for this simple “plugin”.

    I have two doubts ant I need your support:
    1) How to set the cell value format as currency? (EUR, €)
    2) How to set the header (with title and image) and footer (with date and page number?

    Thank you for your support.

  • Dvader

    how use this fuckin phpexcel without composer ?

  • Alfredo Aquino

    good day, I want to give a title to the report also the user and date of creation, how can I do it?